Important Oracle Apps OM Back to Back orders queries

Important Oracle Apps OM Back to Back orders queries

Back to Back order Requisition:

When the order line status moves to PO-ReqRequested (flow_status_code PO_REQ_REQUESTED). OM will insert a record in the PO requisitions interface table.

Checking the data in the PO requisitions interface table:

SELECT *

  FROM po_requisitions_interface_all

 WHERE interface_source_line_id = &order_line_id

       AND interface_source_code = 'CTO';

Relation between Sales Order Line and Purchase Order Line:

Check the table MTL_RESERVATIONS, after PO created, the system will automatically reserve the Sales Order Line to Purchase Order Line.

DEMAND_SOURCE_HEADER_ID = Sales Order Header Id
DEMAND_SOURCE_LINE_ID = Sales Order Line Id

SUPPLY_SOURCE_HEADER_ID = Purchase Order Header Id
SUPPLY_SOURCE_LINE_ID = Purchase Order Line Id

When the PO is received into inventory, the reservation is automatically transferred into Inventory, and it now looks like any other reservation from a sales order to on-hand stock.
At that time, SUPPLY_SOURCE_HEADER_ID = Purchase Order Header Id, but SUPPLY_SOURCE_LINE_ID = Null, as the reservation is transferred into Inventory.

The following SQL could be used as a reference, but only when Sales Order not picked or shipped.

SELECT UNIQUE

       TO_CHAR (res.demand_source_line_id) line,

       TO_CHAR (rcv.transaction_date, 'DD-MON-RR_HH24:MI:SS') trans_date,

       rcv.location_id loc_id,

       rcv.organization_id org_id,

       rcv.oe_order_header_id oe_head_id,

       rcv.oe_order_line_id oe_line_id,

       poh.segment1 po_number,

       poh.po_header_id po_head_id,

       rcv.po_line_id po_line_id,

       rcv.po_line_location_id line_loc_id,

       rcv.po_unit_price unit_price,

       rcv.primary_unit_of_measure uom,

       rcv.quantity qty

  FROM rcv_transactions rcv, mtl_reservations res, po_headers_all poh

 WHERE     res.demand_source_line_id = &so_line_id

       AND res.demand_source_type_id = 2

       AND res.supply_source_type_id IN (1, 13)

       AND res.supply_source_header_id = poh.po_header_id

       AND poh.po_header_id = rcv.po_header_id(+);