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(+);

No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...