Simple Query to connect Receiving Transaction table with Oracle Order line table for Drop Ship Sales Order.

Query to connect Receiving Transaction table with Oracle Order line table for Drop Ship Sales Order.

 SELECT l.line_id,l.header_id,rt.transaction_id,
              odss.line_location_id, odss.po_header_id, odss.requisition_line_id,
              rt.po_header_id,rt.po_line_id,l.flow_status_code,NVL(l.shipped_quantity,0),
              l.ordered_quantity
       FROM   oe_order_lines_all     l,
              oe_drop_ship_sources   odss,
               rcv_transactions       rt
       where  l.line_id = odss.line_id
       and    l.source_type_code = 'EXTERNAL'
       and    odss.po_header_id = rt.po_header_id
       and    odss.po_line_id = rt.po_line_id
       and    l.open_flag = 'Y'               
       and    l.flow_status_code = 'AWAITING_RECEIPT'
       and    odss.line_location_id = rt.po_line_location_id
       and    rt.transaction_id = &Enter_Receiving_transaction_id

Hope that helps