I'm trying to create a BI Report which queries sales order lines details, including shipping method and carrier name. But I'm unable to find the correct joins for this. Till now I have an alternative query which retrieves carrier_name for the sales order lines for me, but only retrieves sales order lines which have status as "Closed" or "Awaiting Billing". The Oracle Fusion version is release 22A (11.13.22.01.0)
select dfla.source_order_number, wdd.sales_order_line_number ,wcv.carrier_name
FROM wsh_new_deliveries wnd,
wsh_carriers_v wcv,
wsh_delivery_assignments wda,
wsh_delivery_details wdd,
doo_fulfill_lines_all dfla
where wcv.carrier_id = wnd.carrier_id
AND wda.DELIVERY_ID = wnd.DELIVERY_ID
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND dfla.fulfill_line_id=wdd.source_shipment_id