Here is the SQL Query to get Back2Back Sales Orders and their corresponding Supply Order details.
SELECT sh.SUPPLY_ORDER_REFERENCE_NUMBER “Sales Order”,
sl.SUPPLY_ORDER_REF_LINE_NUMBER “SOLine_Num”,
sh.SUPPLY_ORDER_NUMBER,
sh.status_code hdr_status,
sh.SUPPLY_ORDER_SOURCE,
sl.LINE_NUMBER “Supply_Lines”,
sl.EXEC_SYSTEM_ITEM_NUMBER “Item”,
sl.quantity Requested_Qty,
stl.status_code status,
–stl.quantity tl_qty , stl.available_quantity avail_qty ,
sl.need_by_date Requested_Delivery_Date,
sl.supply_type supply_type,
sl.DESTINATION_ORGANIZATION_CODE Supply_Destination, –sl.DESTINATION_BU_ID , sl.DESTINATION_ORGANIZATION_ID ,
sl.MOVEMENT_REQUEST_FLAG,
sl.contract_manufacturing_flag CM_FLAG,
sl.back_to_back_flag B2B_FLAG,
sl.CONFIG_ITEM_FLAG,
sl.OUTSIDE_PROCESSING_FLAG OSP_FLAG,
DECODE (stld.document_type,
‘ORA_PR’, ‘Purchase Requisition’,
‘ORA_PO’, ‘Purchase Order’,
‘ORA_RSV’, ‘Reservation’,
‘ORA_RCPT’, ‘Receipt’,
‘ORA_TO’, ‘Transfer Order’,
‘ORA_WO’, ‘Work Order’,
‘ORA_SH’, ‘Shipment’) “Document Type”,
stld.document_number doc_num,
stld.document_line_number doc_line,
stld.document_line_quantity doc_qty,
–stld.document_net_quantity doc_net_qty ,
stld.document_status doc_status
FROM fusion.dos_supply_headers sh,
fusion.dos_supply_lines sl,
fusion.dos_supply_tracking_lines stl,
fusion.dos_supply_tracking_line_docs stld
WHERE sl.header_id = sh.header_id
AND stl.line_id = sl.line_id
AND stld.tracking_line_id(+) = stl.tracking_line_id
— AND SUPPLY_ORDER_REFERENCE_NUMBER = ‘999’
ORDER BY sh.header_id DESC, sl.line_number, stl.tracking_line_id, stld.creation_date
Here is the sample report output
