eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IO_DIAGNOSTICS3 SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 17


    p_sql(1) := ' SELECT  prh.* ' ||
' FROM    po_requisition_headers_all prh, ' ||
' po_requisition_lines_all prl ' ||
        ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
' and prh.org_id = ' || l_ou_id ||
    ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
    ' AND prl.source_type_code      = ''INVENTORY''   ';

Line: 30


        p_sql(2) := ' SELECT  prl.* ' ||
' FROM    po_requisition_headers_all prh, ' ||
' po_requisition_lines_all prl, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND prl.source_type_code      = ''INVENTORY'' ' ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' ORDER BY prl.requisition_line_id   ';

Line: 51


    p_sql(3) := ' SELECT  prd.* ' ||
' FROM    po_requisition_headers_all prh, ' ||
' po_requisition_lines_all prl, ' ||
        ' po_req_distributions_all prd, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND prl.requisition_line_id   = prd.requisition_line_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND prl.source_type_code      = ''INVENTORY''    ';

Line: 72


        p_sql(4) := ' SELECT  oel.* ' ||
' FROM    oe_order_lines_all oel, ' ||
' po_requisition_lines_all prl,         ' ||
        ' po_requisition_headers_all prh,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prl.requisition_line_id     = oel.source_document_line_id ' ||
    ' AND oel.source_document_type_id = 10 ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND prl.source_type_code      = ''INVENTORY''' ||
    ' ORDER BY oel.line_id   ';

Line: 91


    p_sql(5) := ' SELECT  wsh.* ' ||
' FROM    wsh_delivery_details wsh , ' ||
' wsh_delivery_assignments wda , ' ||
        ' wsh_new_deliveries wnd , ' ||
        ' oe_order_lines_all sol , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND prl.requisition_header_id   = prh.requisition_header_id ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND wsh.source_line_id          = sol.line_id ' ||
    ' AND wsh.delivery_detail_id      = wda.delivery_detail_id ' ||
    ' AND wda.delivery_id             = wnd.delivery_id ' ||
    ' UNION ALL ' ||
' SELECT  wsh.* ' ||
' FROM    wsh_delivery_details wsh , ' ||
' mtl_transactions_interface mti , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' oe_order_lines_all sol, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND prl.requisition_header_id   = prh.requisition_header_id ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND mti.trx_source_line_id      = sol.line_id ' ||
    ' AND mti.picking_line_id         = wsh.delivery_detail_id   ';

Line: 136


        p_sql(6) := ' SELECT  rhi.* ' ||
' FROM    rcv_headers_interface rhi,' ||
' rcv_shipment_headers rsh ' ||
        ' WHERE   rhi.receipt_header_id = rsh.shipment_header_id ' ||
' and rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
        ' and rsh.ship_to_org_id = ' || l_org_id ;

Line: 144


            p_sql(7) := ' SELECT DISTINCT rti.*' ||
' FROM    rcv_transactions_interface rti ' ||
' where rti.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rti.to_organization_id = ' || l_org_id;

Line: 150


    p_sql(8) := ' SELECT DISTINCT pie.* ' ||
' FROM    po_interface_errors pie ' ||
 ' WHERE   pie.interface_transaction_id IN ' ||
 ' ( SELECT DISTINCT rti.interface_transaction_id ' ||
 ' FROM    rcv_transactions_interface rti' ||
         ' where rti.shipment_num =  ' || '''' || l_shipment_num || '''' ||
         ' and rti.to_organization_id = ' || l_org_id ||
         ' OR pie.interface_line_id IN ' ||
         ' ( SELECT DISTINCT rti.interface_transaction_id' ||
      ' FROM    rcv_transactions_interface rti ' ||
         ' where rti.shipment_num =  ' || '''' || l_shipment_num || '''' ||
         ' and rti.to_organization_id = ' || l_org_id || '))';

Line: 168


             p_sql(9) := ' SELECT DISTINCT rsh.* ' ||
' FROM    rcv_shipment_headers rsh , ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND rsh.receipt_source_code   = ''INTERNAL ORDER''  ';

Line: 186


        p_sql(10) := ' SELECT  rsl.* ' ||
' FROM    rcv_shipment_headers rsh, ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND prl.source_type_code = ''INVENTORY''' ||
    ' AND rsl.source_document_code  = ''REQ''   ';

Line: 202


        p_sql(11) := ' SELECT  rt.* ' ||
' FROM    rcv_transactions rt , ' ||
' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl ,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id' ||
    ' AND prl.source_type_code = ''INVENTORY'' ' ||
    ' AND rt.requisition_line_id    = prl.requisition_line_id     ' ||
    ' AND rt.shipment_line_id       = rsl.shipment_line_id   ';

Line: 220


        p_sql(12) := ' SELECT  ms.* ' ||
' FROM    mtl_supply ms , ' ||
' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl ,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id' ||
    ' AND ms.shipment_header_id      = rsh.shipment_header_id ';

Line: 242


        p_sql(13) := ' SELECT  rs.* ' ||
' FROM    rcv_supply rs , ' ||
' po_requisition_headers_all prh ,' ||
        ' po_requisition_lines_all prl,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND prl.source_type_code = ''INVENTORY'' ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND rs.shipment_header_id      = rsh.shipment_header_id ';

Line: 259


        p_sql(14) := ' SELECT  mtrl.* ' ||
' FROM    mtl_txn_request_lines mtrl,  ' ||
' rcv_shipment_headers rsh,  ' ||
        ' rcv_shipment_lines rsl,  ' ||
        ' po_requisition_headers_all prh,  ' ||
        ' po_requisition_lines_all prl  ' ||
        ' WHERE   rsh.shipment_num =   '|| '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id =  l_org_id  ' ||
    ' and prh.segment1 =  '|| '''' || l_req_num || '''' ||
    ' and prh.org_id = '|| l_ou_id ||
    ' AND prh.requisition_header_id = prl.requisition_header_id  ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id  ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id  ' ||
    ' AND prl.source_type_code = ''INVENTORY''' ||
' and mtrl.inventory_item_id=rsl.item_id ' ||
    ' and nvl(mtrl.revision,0)=nvl(prl.item_revision,0) ' ||
    ' and mtrl.organization_id=rsl.to_organization_id ' ||
    ' and mtrl.transaction_type_id=52'||
' and mtrl.line_status=7';

Line: 279


	/*' SELECT  mtrl.* ' ||
' FROM    mtl_txn_request_lines mtrl, ' ||
' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl, ' ||
        ' po_requisition_headers_all prh, ' ||
        ' po_requisition_lines_all prl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||
    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND prl.source_type_code = ''INVENTORY'' ' ||
    ' AND mtrl.reference_id         = rsl.shipment_line_id ' ||
    ' AND mtrl.reference            = ''SHIPMENT_LINE_ID''     ' ||
    ' AND rsl.source_document_code  = ''REQ''  '; */

Line: 297


        p_sql(15) := ' SELECT  MTI.* ' ||
' FROM    MTL_TRANSACTIONS_INTERFACE MTI, ' ||
' PO_REQUISITION_LINES_ALL PRL, ' ||
        ' PO_REQUISITION_HEADERS_ALL PRH, ' ||
        ' OE_ORDER_LINES_ALL SOL, ' ||
        ' RCV_SHIPMENT_HEADERS RSH, ' ||
        ' RCV_SHIPMENT_LINES RSL ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND PRH.REQUISITION_HEADER_ID   = PRL.REQUISITION_HEADER_ID ' ||
    ' AND RSH.SHIPMENT_HEADER_ID      = RSL.SHIPMENT_HEADER_ID ' ||
    ' AND RSL.REQUISITION_LINE_ID     = PRL.REQUISITION_LINE_ID ' ||
    ' AND prl.source_type_code = ''INVENTORY'' ' ||
    ' AND SOL.SOURCE_DOCUMENT_TYPE_ID = 10 ' ||
    ' AND SOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID ' ||
    ' AND MTI.TRX_SOURCE_LINE_ID      = SOL.LINE_ID ' ||
    ' AND MTI.SOURCE_CODE             = ''ORDER ENTRY''   ';

Line: 318


        p_sql(16) := ' SELECT  mmtt.* ' ||
' FROM    mtl_material_transactions_temp mmtt , ' ||
' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl,' ||
        ' rcv_transactions rt ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||
    ' AND prh.segment1 = ''229''  ' ||
    ' and prh.requisition_header_id = prl.requisition_header_id  ' ||

    ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
    ' and rt.requisition_line_id = prl.requisition_line_id  ' ||
    ' and mmtt.rcv_transaction_id = rt.transaction_id      ' ||
    ' UNION ALL' ||
' SELECT  mmtt.* ' ||
' FROM    mtl_material_transactions_temp mmtt , ' ||
' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl,' ||
        ' oe_order_lines_all sol   ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||
    ' and prh.requisition_header_id = prl.requisition_header_id  ' ||
    ' and prl.source_type_code = ''INVENTORY''    ' ||
    ' and prh.requisition_header_id = prl.requisition_header_id ' ||

    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' and sol.source_document_type_id = 10  ' ||
    ' and sol.source_document_line_id = prl.requisition_line_id' ||
    ' and mmtt.trx_source_line_id = sol.line_id ';

Line: 364


        p_sql(17) := ' SELECT  mmt.* ' ||
' FROM    mtl_material_transactions mmt , ' ||
' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl,' ||
        ' rcv_transactions rt ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||
    ' AND prh.segment1 = ''229''  ' ||
    ' and prh.requisition_header_id = prl.requisition_header_id  ' ||

    ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' and rt.requisition_line_id = prl.requisition_line_id  ' ||
    ' and mmt.rcv_transaction_id = rt.transaction_id      ' ||
    ' UNION ALL' ||
' SELECT  mmt.* ' ||
' FROM    mtl_material_transactions mmt , ' ||
' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl,' ||
        ' oe_order_lines_all sol   ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||
    ' and prh.requisition_header_id = prl.requisition_header_id  ' ||
    ' and prl.source_type_code = ''INVENTORY''    ' ||

    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' and sol.source_document_type_id = 10  ' ||
    ' and sol.source_document_line_id = prl.requisition_line_id' ||
    ' and mmt.trx_source_line_id = sol.line_id ' ||
    ' and mmt.transaction_action_id = 21';

Line: 408


        p_sql(18) := ' SELECT  mr.* ' ||
' FROM    mtl_reservations mr , ' ||
' oe_order_lines_all sol , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id   = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND mr.demand_source_line_id    = sol.line_id ' ||
    ' AND mr.demand_source_type_id    = 8 ' ||
    ' UNION ALL ' ||
' SELECT  mr.* ' ||
' FROM    mtl_reservations mr , ' ||
' mtl_transactions_interface mti , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' oe_order_lines_all sol ,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id   = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND mti.trx_source_line_id      = sol.line_id ' ||
    ' AND mr.demand_source_line_id    = mti.trx_source_line_id   ';

Line: 449


        p_sql(19) := ' SELECT  md.* ' ||
' FROM    mtl_demand md , ' ||
' oe_order_lines_all sol , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id   = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND prh.type_lookup_code        = ''INTERNAL'' ' ||
    ' AND md.demand_source_line       = sol.line_id ' ||
    ' AND md.demand_source_type       = 8 ' ||
    ' UNION ALL ' ||
' SELECT  md.* ' ||
' FROM    mtl_demand md , ' ||
' mtl_transactions_interface mti , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' oe_order_lines_all sol , ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id   = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND prh.type_lookup_code        = ''INTERNAL'' ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND mti.trx_source_line_id      = sol.line_id ' ||
    ' AND md.demand_source_line       = mti.source_line_id   ';

Line: 497


        p_sql(20) := ' select distinct msn.*   ' ||
' from mtl_serial_numbers msn , ' ||
 ' mtl_material_transactions mmt , ' ||
      ' po_requisition_lines_all prl , ' ||
      ' po_requisition_headers_all prh ,' ||
      ' rcv_shipment_headers rsh, ' ||
      ' rcv_shipment_lines rsl ,' ||
      ' rcv_transactions rt' ||
      ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
   ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id  ' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND rt.requisition_line_id      = prl.requisition_line_id ' ||
    ' and mmt.rcv_transaction_id = rt.transaction_id  ' ||
    ' and mmt.transaction_id = msn.last_transaction_id' ||
    ' UNION ALL' ||
' select distinct msn.*    ' ||
 ' from mtl_serial_numbers msn , ' ||
         ' mtl_material_transactions mmt , ' ||
              ' po_requisition_lines_all prl , ' ||
              ' po_requisition_headers_all prh ,' ||
              ' oe_order_lines_all sol,' ||
              ' rcv_shipment_headers rsh, ' ||
              ' rcv_shipment_lines rsl ' ||
              ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
        ' and rsh.ship_to_org_id = ' || l_org_id ||
          ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
          ' and prh.org_id = ' || l_ou_id ||

          ' and prh.requisition_header_id = prl.requisition_header_id  ' ||
          ' and prl.source_type_code = ''INVENTORY''' ||
          ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
          ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
          ' and sol.source_document_type_id = 10   ' ||
          ' and sol.source_document_line_id = prl.requisition_line_id   ' ||
          ' and mmt.trx_source_line_id = sol.line_id   ' ||
          ' and mmt.rcv_transaction_id is null ' ||
          ' and mmt.transaction_id = msn.last_transaction_id  ';

Line: 542


              p_sql(21) := ' select DISTINCT msnt.*' ||
' from    po_requisition_lines_all prl ,' ||
' po_requisition_headers_all prh ,' ||
        ' mtl_serial_numbers_temp msnt ,' ||
        ' mtl_system_items msi,' ||
        ' rcv_transactions_interface rti,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id  ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
    ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
        ' )' ||
       ' and rti.interface_transaction_id = msnt.transaction_temp_id' ||
    ' and msi.inventory_item_id           = rti.item_id' ||
    ' and msi.organization_id             = rti.to_organization_id' ||
    ' and msi.serial_number_control_code <> 1' ||
    ' and msi.lot_control_code       = 1' ||
    ' UNION ALL' ||
' select DISTINCT msnt.*' ||
' from    po_requisition_lines_all prl ,' ||
' po_requisition_headers_all prh ,' ||
        ' mtl_serial_numbers_temp msnt ,' ||
        ' mtl_transaction_lots_temp mtlt,' ||
        ' mtl_system_items msi,' ||
        ' rcv_transactions_interface rti,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
    ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
        ' )' ||
       ' and rti.interface_transaction_id = mtlt.transaction_temp_id' ||
    ' and mtlt.SERIAL_TRANSACTION_TEMP_ID = msnt.transaction_temp_id' ||
    ' and msi.inventory_item_id           = rti.item_id' ||
    ' and msi.organization_id             = rti.to_organization_id' ||
    ' and msi.serial_number_control_code <> 1' ||
    ' and msi.lot_control_code       <> 1' ||
    ' UNION ALL' ||
' select DISTINCT msnt.*' ||
' from    po_requisition_lines_all prl,' ||
' po_requisition_headers_all prh,' ||
        ' mtl_serial_numbers_temp msnt,' ||
        ' mtl_system_items msi,' ||
        ' oe_order_lines_all sol,' ||
        ' mtl_material_transactions_temp mmtt,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' and sol.source_document_line_id = prl.requisition_line_id' ||
    ' and sol.source_document_type_id = 10' ||
    ' and mmtt.trx_source_line_id = sol.line_id' ||
    ' and msnt.transaction_TEMP_id = mmtt.transaction_TEMP_id' ||
    ' and msi.inventory_item_id           = mmtt.inventory_item_id' ||
    ' and msi.organization_id             = mmtt.organization_id' ||
    ' and msi.serial_number_control_code <> 1' ||
    ' and msi.lot_control_code       = 1' ||
    ' UNION ALL' ||
' select DISTINCT msnt.*' ||
' from    po_requisition_lines_all prl,' ||
' po_requisition_headers_all prh,' ||
        ' mtl_serial_numbers_temp msnt,' ||
        ' mtl_transaction_lots_temp mtlt,' ||
        ' mtl_system_items msi,' ||
        ' oe_order_lines_all sol,' ||
        ' mtl_material_transactions_temp mmtt,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id' ||
    ' and sol.source_document_line_id = prl.requisition_line_id' ||
    ' and sol.source_document_type_id = 10' ||
    ' and mmtt.trx_source_line_id = sol.line_id' ||
    ' and mmtt.transaction_TEMP_id = mtlt.transaction_TEMP_id' ||
    ' and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id' ||
    ' and msi.inventory_item_id           = mmtt.inventory_item_id' ||
    ' and msi.organization_id             = mmtt.organization_id' ||
    ' and msi.serial_number_control_code <> 1' ||
    ' and msi.lot_control_code       <> 1 ';

Line: 654


        p_sql(22) := ' select distinct msni.*    ' ||
  ' from rcv_transactions_interface rti ,' ||
  ' po_requisition_lines_all prl , ' ||
       ' po_requisition_headers_all prh , ' ||
       ' mtl_serial_numbers_interface msni ,' ||
       ' mtl_system_items msi,' ||
       ' rcv_shipment_headers rsh, ' ||
       ' rcv_shipment_lines rsl ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id' ||
    ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
    ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
        ' )' ||
       ' and rti.interface_transaction_id = msni.product_transaction_id' ||
    ' and msi.inventory_item_id = rti.item_id' ||
    ' and msi.organization_id = rti.to_organization_id' ||
    ' and msi.serial_number_control_code <> 1' ||
    ' and msi.lot_control_code = 1' ||
    ' UNION ALL' ||
' select distinct msni.*    ' ||
  ' from rcv_transactions_interface rti ,' ||
  ' po_requisition_lines_all prl , ' ||
       ' po_requisition_headers_all prh , ' ||
       ' mtl_serial_numbers_interface msni ,' ||
       ' mtl_transaction_lots_interface mtli,' ||
       ' mtl_system_items msi,' ||
       ' rcv_shipment_headers rsh, ' ||
       ' rcv_shipment_lines rsl ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' and prh.requisition_header_id = prl.requisition_header_id' ||
    ' and prl.source_type_code = ''INVENTORY''' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id' ||
    ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
    ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
        ' )' ||
       ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
    ' and mtli.serial_transaction_temp_id = msni.transaction_interface_id ' ||
    ' and msi.inventory_item_id = rti.item_id' ||
    ' and msi.organization_id = rti.to_organization_id' ||
    ' and msi.serial_number_control_code <> 1' ||
    ' and msi.lot_control_code <> 1 ';

Line: 709


        p_sql(23) := ' select distinct mut.*       ' ||
  ' from mtl_material_transactions mmt ,    ' ||
    ' po_requisition_lines_all prl ,    ' ||
    ' mtl_unit_transactions mut ,       ' ||
         ' mtl_system_items msi,   ' ||
         ' rcv_transactions rt,   ' ||
         ' rcv_shipment_headers rsh,    ' ||
         ' rcv_shipment_lines rsl            ' ||
         ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
      ' AND rsh.shipment_header_id  = rsl.shipment_header_id    ' ||
      ' AND rsl.requisition_line_id = prl.requisition_line_id   ' ||

      ' AND rt.requisition_line_id = prl.requisition_line_id   ' ||
      ' and prl.source_type_code =   ''INVENTORY''' ||
      ' and mmt.rcv_transaction_id = rt.transaction_id       ' ||
      ' and mmt.transaction_id = mut.transaction_id       ' ||
      ' and msi.inventory_item_id = mmt.inventory_item_id       ' ||
      ' and msi.organization_id = mmt.organization_id       ' ||
      ' and msi.serial_number_control_code <> 1        ' ||
      ' and msi.lot_control_code = 1       ' ||
      ' union all       ' ||
      ' select distinct mut.*   ' ||
  ' from mtl_material_transactions mmt ,   ' ||
     ' po_requisition_lines_all prl ,   ' ||
    ' mtl_unit_transactions mut ,       ' ||
         ' mtl_system_items msi ,    ' ||
         ' rcv_transactions rt ,    ' ||
         ' mtl_transaction_lot_numbers mtln,   ' ||
         ' rcv_shipment_headers rsh,    ' ||
         ' rcv_shipment_lines rsl            ' ||
         ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
      ' AND rsh.shipment_header_id  = rsl.shipment_header_id    ' ||
      ' AND rsl.requisition_line_id = prl.requisition_line_id   ' ||

      ' and prl.source_type_code =   ''INVENTORY''' ||
      ' and rt.requisition_line_id = prl.requisition_line_id       ' ||
      ' and mmt.rcv_transaction_id = rt.transaction_id       ' ||
      ' and mtln.transaction_id = mmt.transaction_id       ' ||
      ' and mut.transaction_id = mtln.serial_transaction_id       ' ||
      ' and msi.inventory_item_id = mmt.inventory_item_id       ' ||
      ' and msi.organization_id = mmt.organization_id   ' ||
      ' and msi.serial_number_control_code <> 1   ' ||
      ' and msi.lot_control_code <> 1   ' ||
      ' union all   ' ||
      ' select distinct mut.*       ' ||
  ' from mtl_material_transactions mmt ,    ' ||
     ' po_requisition_lines_all prl ,           ' ||
    ' mtl_unit_transactions mut ,       ' ||
         ' mtl_system_items msi ,    ' ||
         ' oe_order_lines_all sol,   ' ||
         ' rcv_shipment_headers rsh,    ' ||
         ' rcv_shipment_lines rsl            ' ||
         ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
      ' AND rsh.shipment_header_id  = rsl.shipment_header_id    ' ||
      ' AND rsl.requisition_line_id = prl.requisition_line_id   ' ||

      ' and prl.source_type_code =   ''INVENTORY''' ||
      ' and sol.source_document_line_id = prl.requisition_line_id    ' ||
      ' and sol.source_document_type_id = 10   ' ||
      ' and mmt.trx_source_line_id = sol.line_id        ' ||
      ' and mut.transaction_id = mmt.transaction_id   ' ||
      ' and msi.inventory_item_id = mmt.inventory_item_id   ' ||
      ' and msi.organization_id = mmt.organization_id   ' ||
      ' and msi.serial_number_control_code <> 1   ' ||
      ' and msi.lot_control_code = 1   ' ||
      ' union all   ' ||
      ' select distinct mut.*   ' ||
  ' from mtl_material_transactions mmt ,    ' ||
     ' po_requisition_lines_all prl ,           ' ||
    ' mtl_unit_transactions mut ,       ' ||
         ' mtl_system_items msi ,    ' ||
         ' oe_order_lines_all sol ,    ' ||
         ' mtl_transaction_lot_numbers mtln,   ' ||
         ' rcv_shipment_headers rsh,    ' ||
         ' rcv_shipment_lines rsl            ' ||
         ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
  ' and rsh.ship_to_org_id = ' || l_org_id ||
      ' AND rsh.shipment_header_id  = rsl.shipment_header_id    ' ||
      ' AND rsl.requisition_line_id = prl.requisition_line_id   ' ||
      ' and prl.source_type_code =   ''INVENTORY''      ' ||

      ' and sol.source_document_line_id = prl.requisition_line_id   ' ||
      ' and sol.source_document_type_id = 10   ' ||
       ' and mmt.trx_source_line_id = sol.line_id   ' ||
       ' and mtln.transaction_id = mmt.transaction_id   ' ||
       ' and mut.transaction_id = mtln.serial_transaction_id   ' ||
       ' and msi.inventory_item_id = mmt.inventory_item_id   ' ||
       ' and msi.organization_id = mmt.organization_id   ' ||
       ' and msi.serial_number_control_code <> 1   ' ||
       ' and msi.lot_control_code <> 1         ';

Line: 808


           p_sql(24) := ' SELECT  rss.* ' ||
' FROM    rcv_serials_supply rss , ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' rcv_shipment_headers rsh ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND rss.shipment_line_id      = rsl.shipment_line_id ' ||
    ' AND rsl.source_document_code  = ''REQ'' ' ||
    ' ORDER BY rss.supply_type_code , ' ||
' rss.serial_num   ';

Line: 828


            p_sql(25) := ' SELECT  rst.* ' ||
' FROM    rcv_serial_transactions rst , ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' rcv_shipment_headers rsh ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND rsl.source_document_code  = ''REQ'' ' ||
    ' AND rst.shipment_line_id      = rsl.shipment_line_id ' ||
    ' ORDER BY rst.serial_transaction_type , ' ||
' rst.serial_num   ';

Line: 848


            p_sql(26) := ' SELECT  rsi.* ' ||
' FROM    rcv_serials_interface rsi , ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' rcv_shipment_headers rsh ,' ||
        ' rcv_transactions_interface rti' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id' ||

    ' AND rsl.source_document_code  = ''REQ'' ' ||
    ' AND rsi.item_id               = rsl.item_id ' ||
    ' AND rsi.organization_id       = rsl.to_organization_id ' ||
    ' AND rsi.interface_transaction_id = rti.interface_transaction_id' ||
    ' AND rti.shipment_line_id = rsl.shipment_line_id  ';

Line: 873


        p_sql(27) := ' select distinct  mln.* ' ||
 ' from mtl_lot_numbers mln ,' ||
 ' mtl_transaction_lot_numbers mtln ,' ||
  ' mtl_material_transactions mmt ,' ||
       ' po_requisition_lines_all prl ,' ||
       ' rcv_transactions rt,' ||
       ' rcv_shipment_headers rsh,' ||
       ' rcv_shipment_lines rsl ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || '''' || l_org_id || '''' ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and rt.requisition_line_id = prl.requisition_line_id' ||
     ' and prl.source_type_code = ''INVENTORY''' ||
     ' and mmt.rcv_transaction_id = rt.transaction_id' ||
     ' and mmt.transaction_id = mtln.transaction_id  ' ||
     ' and mln.inventory_item_id = mmt.inventory_item_id  ' ||
     ' and mln.organization_id = mmt.organization_id   ' ||
     ' and mln.lot_number = mtln.lot_number ' ||
     ' UNION ALL' ||
     ' select distinct  mln.* ' ||
 ' from mtl_lot_numbers mln , ' ||
 ' mtl_transaction_lot_numbers mtln ,' ||
  ' mtl_material_transactions mmt ,   ' ||
       ' po_requisition_lines_all prl ,  ' ||
       ' oe_order_lines_all sol,' ||
       ' rcv_shipment_headers rsh, ' ||
       ' rcv_shipment_lines rsl           ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || '''' || l_org_id || '''' ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and prl.source_type_code = ''INVENTORY''' ||
     ' and sol.source_document_line_id = prl.requisition_line_id ' ||
     ' and sol.source_document_type_id = 10  ' ||
     ' and mmt.transaction_id = mtln.transaction_id   ' ||
     ' and mmt.trx_source_line_id = sol.line_id' ||
     ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
     ' and mln.organization_id = mmt.organization_id   ' ||
     ' and mln.lot_number = mtln.lot_number    ';

Line: 917


         p_sql(28) := ' select distinct mtln.*' ||
    ' from mtl_transaction_lot_numbers mtln ,' ||
 ' mtl_material_transactions mmt ,   ' ||
  ' po_requisition_lines_all prl ,   ' ||
       ' rcv_transactions rt,' ||
       ' rcv_shipment_headers rsh,   ' ||
       ' rcv_shipment_lines rsl          ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || l_org_id ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id   ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and prl.source_type_code = ''INVENTORY''   ' ||
     ' and rt.requisition_line_id = prl.requisition_line_id' ||
     ' and mmt.rcv_transaction_id = rt.transaction_id' ||
    ' and mmt.transaction_id = mtln.transaction_id' ||
    ' UNION ALL' ||
    ' select distinct mtln.*     ' ||
 ' from mtl_transaction_lot_numbers mtln ,   ' ||
 ' mtl_material_transactions mmt ,   ' ||
  ' po_requisition_lines_all prl ,   ' ||
       ' oe_order_lines_all sol,' ||
       ' rcv_shipment_headers rsh,   ' ||
       ' rcv_shipment_lines rsl          ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || l_org_id ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id   ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and prl.source_type_code = ''INVENTORY''   ' ||
     ' and sol.source_document_line_id = prl.requisition_line_id     ' ||
     ' and sol.source_document_type_id = 10     ' ||
   ' and mmt.trx_source_line_id = sol.line_id     ' ||
   ' and mmt.transaction_id = mtln.transaction_id   ';

Line: 955


       p_sql(29) := ' select distinct mtli.*     ' ||
 ' from mtl_transaction_lots_interface mtli ,   ' ||
 ' mtl_transactions_interface mti ,   ' ||
  ' po_requisition_lines_all prl ,   ' ||
       ' rcv_shipment_headers rsh,   ' ||
       ' rcv_shipment_lines rsl          ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || l_org_id ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id   ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and prl.source_type_code = ''INVENTORY''   ' ||
     ' and mti.requisition_line_id = prl.requisition_line_id  ' ||
     ' and mti.transaction_interface_id = mtli.transaction_interface_id' ||
    ' UNION ALL' ||
    ' select distinct mtli.*     ' ||
 ' from mtl_transaction_lots_interface mtli ,   ' ||
 ' rcv_transactions_interface rti ,   ' ||
  ' po_requisition_lines_all prl ,   ' ||
       ' rcv_shipment_headers rsh,   ' ||
       ' rcv_shipment_lines rsl          ' ||
       ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || l_org_id ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id   ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and prl.source_type_code = ''INVENTORY''   ' ||
     ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
     ' and mtli.product_code =''RCV''   ' ||
     ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)   ';

Line: 991


         p_sql(30) := ' select distinct mtlt.*     ' ||
        ' from mtl_transaction_lots_temp mtlt ,' ||
 ' rcv_transactions_interface rti,   ' ||
   ' po_requisition_lines_all prl ,   ' ||
        ' rcv_shipment_headers rsh,   ' ||
        ' rcv_shipment_lines rsl          ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || l_org_id ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id   ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||

     ' and prl.source_type_code = ''INVENTORY''   ' ||
     ' and rti.interface_transaction_id = mtlt.product_transaction_id' ||
     ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)         ' ||
     ' UNION ALL' ||
     ' select distinct mtlt.*     ' ||
   ' from mtl_transaction_lots_temp mtlt ,' ||
   ' mtl_material_transactions_temp mmtt,  ' ||
   ' po_requisition_lines_all prl ,   ' ||
        ' oe_order_lines_all sol,' ||
        ' rcv_shipment_headers rsh,   ' ||
        ' rcv_shipment_lines rsl          ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
 ' and rsh.ship_to_org_id = ' || l_org_id ||
     ' AND rsh.shipment_header_id  = rsl.shipment_header_id   ' ||
     ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
     ' and prl.source_type_code = ''INVENTORY''   ' ||

     ' and sol.source_document_line_id = prl.requisition_line_id' ||
     ' and sol.source_document_type_id = 10     ' ||
    ' and mmtt.trx_source_line_id = sol.line_id   ';

Line: 1024


        p_sql(31) := ' SELECT  rls.* ' ||
' FROM    rcv_lots_supply rls , ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' oe_order_lines_all sol , ' ||
        ' rcv_shipment_headers rsh ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id   = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id      = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id     = prl.requisition_line_id ' ||
    ' AND rsl.shipment_line_id        = rls.shipment_line_id ' ||
    ' AND sol.source_document_type_id = 10 ' ||
    ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
    ' AND rsl.source_document_code    = ''REQ''   ';

Line: 1048


        p_sql(32) := ' SELECT  rlt.* ' ||
' FROM    rcv_lot_transactions rlt , ' ||
' rcv_shipment_lines rsl , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
        ' rcv_shipment_headers rsh ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND rsl.shipment_line_id      = rlt.shipment_line_id ' ||
    ' AND rsl.source_document_code  = ''REQ'' ' ||
    ' and prl.source_type_code = ''INVENTORY''  ';

Line: 1068


        p_sql(33) := ' SELECT  rli.* ' ||
' FROM    rcv_lots_interface rli , ' ||
' rcv_transactions_interface rti , ' ||
        ' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl ,' ||
        ' rcv_shipment_headers rsh,' ||
        ' rcv_shipment_lines rsl' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' and prl.source_type_code = ''INVENTORY''   ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id     ' ||
    ' AND (rti.shipment_header_id     = rsh.shipment_header_id OR' ||
    ' rti.shipment_num           = rsh.shipment_num)' ||
         ' AND rti.interface_transaction_id = rli.interface_transaction_id   ';

Line: 1090


        p_sql(34) := ' SELECT DISTINCT msi.* ' ||
' FROM    po_requisition_headers_all prh, ' ||
' po_requisition_lines_all prl, ' ||
        ' mtl_system_items msi, ' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
    ' AND prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'') ' ||
    ' AND prl.item_id                     = msi.inventory_item_id ' ||
    ' AND prl.destination_organization_id = msi.organization_id   ';

Line: 1109


        p_sql(35) := ' select distinct  mtt.transaction_type_id ,   ' ||
' mtt.transaction_type_name ,  ' ||
 ' mtt.transaction_source_type_id ,   ' ||
                 ' mtt.transaction_action_id ,   ' ||
                 ' mtt.user_defined_flag ,   ' ||
                 ' mtt.disable_date   ' ||
                 ' from mtl_transaction_types mtt ,  ' ||
  ' mtl_material_transactions mmt ,   ' ||
       ' po_requisition_lines_all prl ,   ' ||
       ' po_requisition_headers_all prh ,' ||
       ' rcv_transactions rt,' ||
       ' rcv_shipment_headers rsh, ' ||
       ' rcv_shipment_lines rsl ' ||
       ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id' ||
    ' AND rt.requisition_line_id     = prl.requisition_line_id' ||
    ' and mmt.rcv_transaction_id = rt.transaction_id  ' ||
    ' and mmt.transaction_type_id = mtt.transaction_type_id' ||
    ' UNION ALL' ||
    ' select distinct  mtt.transaction_type_id ,   ' ||
        ' mtt.transaction_type_name ,  ' ||
                 ' mtt.transaction_source_type_id ,   ' ||
                 ' mtt.transaction_action_id ,   ' ||
                 ' mtt.user_defined_flag ,   ' ||
                 ' mtt.disable_date    ' ||
                 ' from mtl_transaction_types mtt ,  ' ||
         ' mtl_material_transactions mmt ,   ' ||
              ' po_requisition_lines_all prl ,   ' ||
              ' po_requisition_headers_all prh,' ||
              ' oe_order_lines_all sol,' ||
              ' rcv_shipment_headers rsh, ' ||
              ' rcv_shipment_lines rsl ' ||
             ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id    = rsl.shipment_header_id' ||
    ' and sol.source_document_line_id = prl.requisition_line_id   ' ||
    ' and sol.source_document_type_id = 10   ' ||
    ' and mmt.trx_source_line_id = sol.line_id   ' ||
    ' and mmt.transaction_type_id = mtt.transaction_type_id ';

Line: 1161


        p_sql(36) := ' SELECT  ood.* ' ||
' FROM    org_organization_definitions ood ' ||
' WHERE   exists ' ||
' (SELECT 1 ' ||
        ' FROM    po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
                ' financials_system_params_all fsp , ' ||
                ' rcv_shipment_headers rsh, ' ||
                ' rcv_shipment_lines rsl ' ||
                ' WHERE   rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
        ' and rsh.ship_to_org_id = ' || l_org_id ||
            ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
            ' and prh.org_id = ' || l_ou_id ||

            ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
            ' AND rsh.shipment_header_id    = rsl.shipment_header_id ' ||
            ' AND rsl.requisition_line_id   = prl.requisition_line_id ' ||
            ' AND prh.type_lookup_code in (''INTERNAL'',''PURCHASE'') ' ||
            ' AND (prl.destination_organization_id = ood.organization_id ' ||
            ' OR prl.source_organization_id       = ood.organization_id ' ||
             ' OR (prh.org_id                      = fsp.org_id ' ||
             ' AND ood.organization_id              = fsp.inventory_organization_id ) ) ' ||
            ' )   ';

Line: 1186


            p_sql(37) := ' SELECT DISTINCT mp.* ' ||
' FROM    mtl_parameters mp , ' ||
' po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl ,' ||
        ' rcv_shipment_headers rsh, ' ||
        ' rcv_shipment_lines rsl ' ||
        ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
' and rsh.ship_to_org_id = ' || l_org_id ||
    ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
    ' and prh.org_id = ' || l_ou_id ||

    ' AND prh.requisition_header_id        = prl.requisition_header_id ' ||
    ' AND rsh.shipment_header_id           = rsl.shipment_header_id ' ||
    ' AND rsl.requisition_line_id          = prl.requisition_line_id ' ||
    ' AND prh.type_lookup_code             = ''INTERNAL'' ' ||
    ' AND (prl.destination_organization_id = mp.organization_id ' ||
    ' OR prl.source_organization_id       = mp.organization_id )   ';

Line: 1205


         p_sql(38) := ' SELECT  miop.* ' ||
' FROM    mtl_interorg_parameters miop ' ||
' WHERE   exists ' ||
' (SELECT 1 ' ||
        ' FROM    po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl, ' ||
                ' rcv_shipment_headers rsh, ' ||
                ' rcv_shipment_lines rsl ' ||
                ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
        ' and rsh.ship_to_org_id = ' || l_org_id ||
            ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
            ' and prh.org_id = ' || l_ou_id ||

            ' AND prh.requisition_header_id        = prl.requisition_header_id ' ||
            ' AND rsh.shipment_header_id           = rsl.shipment_header_id ' ||
            ' AND rsl.requisition_line_id          = prl.requisition_line_id ' ||
            ' AND prh.type_lookup_code             = ''INTERNAL'' ' ||
            ' AND (prl.destination_organization_id = miop.to_organization_id ' ||
            ' AND prl.source_organization_id       = miop.from_organization_id )' ||
            ' )   ';

Line: 1227


            p_sql(39) := ' SELECT  rp.* ' ||
' FROM    rcv_parameters rp ' ||
' WHERE   exists ' ||
' (SELECT 1 ' ||
        ' FROM    po_requisition_headers_all prh , ' ||
        ' po_requisition_lines_all prl , ' ||
                ' financials_system_params_all fsp, ' ||
                ' rcv_shipment_headers rsh, ' ||
                ' rcv_shipment_lines rsl ' ||
                ' where rsh.shipment_num =  ' || '''' || l_shipment_num || '''' ||
        ' and rsh.ship_to_org_id = ' || l_org_id ||
            ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
            ' and prh.org_id = ' || l_ou_id ||

            ' AND prh.requisition_header_id        = prl.requisition_header_id ' ||
            ' AND rsh.shipment_header_id           = rsl.shipment_header_id ' ||
            ' AND rsl.requisition_line_id          = prl.requisition_line_id ' ||
            ' AND prh.type_lookup_code             = ''INTERNAL'' ' ||
            ' AND (prl.destination_organization_id = rp.organization_id ' ||
            ' OR prl.source_organization_id       = rp.organization_id ' ||
             ' OR (prh.org_id                      = fsp.org_id ' ||
             ' AND rp.organization_id               = fsp.inventory_organization_id ) ) ' ||
            ' )  ';

Line: 1251


            p_sql(40) := ' SELECT  lookup_code , ' ||
' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''MTL_LOT_CONTROL''   ';

Line: 1259


    p_sql(41) := ' SELECT  lookup_code , ' ||
' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''MTL_LOT_GENERATION''   ';

Line: 1268


    p_sql(42) := ' SELECT  lookup_code , ' ||
' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''MTL_LOT_UNIQUENESS''   ';

Line: 1277


    p_sql(43) := ' SELECT  lookup_type , ' ||
' lookup_code , ' ||
        ' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''MTL_SERIAL_NUMBER''   ';

Line: 1287


    p_sql(44) := ' SELECT  lookup_type , ' ||
' lookup_code , ' ||
        ' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''MTL_SERIAL_NUMBER_TYPE''   ';

Line: 1297


    p_sql(45) := ' SELECT  lookup_type , ' ||
' lookup_code , ' ||
        ' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''MTL_SERIAL_GENERATION''   ';

Line: 1307


    p_sql(46) := ' SELECT  lookup_type , ' ||
' lookup_code , ' ||
        ' meaning , ' ||
        ' enabled_flag , ' ||
        ' start_date_active , ' ||
        ' end_date_active ' ||
        ' FROM    mfg_lookups ' ||
' WHERE   lookup_type = ''SERIAL_NUM_STATUS''    ';