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.RMA_DIAGNOSTICS SQL Statements

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

Line: 11


p_sql(1) := ' select distinct soh.* ' ||
' from oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id ='|| l_operating_id ||
 ' and soh.header_id = sol.header_id ' ||
' and sol.line_category_code = ''RETURN'' ' ;

Line: 19


p_sql(2) := ' select sol.* ' ||
' from oe_order_lines_all sol , oe_order_headers_all soh ' ||
' where order_number = '||''''||l_rma_number||'''' ||
' and sol.line_category_code = ''RETURN'' ' ||
' and sol.header_id = soh.header_id  '  ||
' and soh.org_id ='|| l_operating_id ;

Line: 27


p_sql(3) := ' select distinct msi.* ' ||
' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||
' where order_number = '||''''||l_rma_number||'''' ||
 ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and msi.inventory_item_id = sol.inventory_item_id ' ||
   ' and msi.organization_id = sol.ship_from_org_id  ' ||
   ' and soh.org_id ='|| l_operating_id ;

Line: 37


p_sql(4) := ' select rsh.* ' ||
' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where soh.order_number = '||''''||l_rma_number||'''' ||
    ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id  '||
      ' and soh.org_id ='|| l_operating_id ||
      ' union all' ||
    '  select rsh.*  '||
    'from rcv_shipment_headers rsh, oe_order_headers_all soh , rcv_transactions_interface rti '||
' WHERE rsh.shipment_header_id = rti.shipment_header_id'||
' AND rti.oe_order_header_id = soh.header_id' ||
' and soh.order_number = '||''''||l_rma_number||'''' ||
  ' and soh.org_id ='|| l_operating_id ;

Line: 54


p_sql(5) := ' select rsl.* ' ||
' from rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where order_number = '||''''||l_rma_number||'''' ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN''  ' ||
   ' and soh.org_id ='|| l_operating_id ;

Line: 66


p_sql(6) := ' select rt.* ' ||
' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where order_number = '||''''||l_rma_number||'''' ||
    ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN''  '||
   ' and soh.org_id ='|| l_operating_id ;

Line: 77


p_sql(7) := ' select rhi.* ' ||
' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
||
 ' where order_number = '||''''||l_rma_number||'''' ||
 ' and (rti.oe_order_header_id = soh.header_id ' ||
   ' or rti.oe_order_line_id = sol.line_id ) ' ||
       ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rhi.header_interface_id = rti.header_interface_id  ' ||
    ' and soh.org_id ='|| l_operating_id ;

Line: 89


p_sql(8) := ' select rti.* ' ||
' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where order_number = '||''''||l_rma_number||'''' ||
   ' and (rti.oe_order_header_id = soh.header_id ' ||
   ' or rti.oe_order_line_id = sol.line_id ) ' ||
       ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN''  '||
   ' and soh.org_id ='|| l_operating_id ;

Line: 100


p_sql(9) := ' select pie.* ' ||
' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
   ' where order_number = '||''''||l_rma_number||'''' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and soh.org_id ='|| l_operating_id ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions_interface rti ' ||
          ' where (rti.oe_order_header_id = soh.header_id ' ||
         ' or rti.oe_order_line_id = sol.line_id ) ' ||
               ' and pie.interface_line_id = rti.interface_transaction_id ' ||
           ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
          ' where (rti.oe_order_header_id = soh.header_id ' ||
         ' or rti.oe_order_line_id = sol.line_id ) ' ||
               ' and rhi.header_interface_id = rti.header_interface_id ' ||
           ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
           ' and pie.interface_header_id = rhi.header_interface_id) )  ';

Line: 122


p_sql(10) := ' select distinct ood.* ' ||
' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
||
    ' where order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id ='|| l_operating_id ||
 ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and ood.organization_id = rsl.to_organization_id  ';

Line: 134


p_sql(11) := ' select distinct mp.* ' ||
' from mtl_parameters mp , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
   ' where order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id ='|| l_operating_id ||
 ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mp.organization_id = rsl.to_organization_id  ';

Line: 145


p_sql(12) := ' select mmt.* ' ||
' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ' ||
    ' where order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id ='|| l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id  ';

Line: 156


p_sql(13) := ' 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 , oe_order_lines_all sol , oe_order_headers_all soh ,'
||
  ' rcv_transactions rt ' ||
' where order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id ='|| l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
   ' and mtt.transaction_type_id = mmt.transaction_type_id  ';

Line: 172


/*p_sql(14) := ' select distinct mtrl.* ' ||
' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where order_number = '||''''||l_rma_number||'''' ||
 ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
   ' and mtrl.reference_id = rt.oe_order_line_id ' ||
 ' and soh.org_id ='|| l_operating_id;*/

Line: 183


 p_sql(14) := ' select distinct mtrl.* ' ||
' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where order_number = '||''''||l_rma_number||'''' ||
 ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
 ' and soh.org_id ='|| l_operating_id||
 ' AND mtrl.inventory_item_id  = sol.inventory_item_id'||
 ' AND mtrl.organization_id=rt.organization_id ' ||
 ' and nvl(mtrl.revision,0)=nvl(sol.item_revision,0) ' ||' and mtrl.line_status=7'||
 ' and mtrl.transaction_type_id=15';

Line: 196


p_sql(15) := ' select mmtt.* ' ||
' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh ' ||
 ' where order_number = '||''''||l_rma_number||'''' ||
  ' and soh.org_id ='|| l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and exists (' ||
   ' select 1 ' ||
    ' from rcv_transactions rt ' ||
      ' where rt.oe_order_header_id = soh.header_id ' ||
     ' and rt.oe_order_line_id = sol.line_id ' ||
       ' and mmtt.rcv_transaction_id = rt.transaction_id)  ';

Line: 209


p_sql(16) := ' select lsn.* ' ||
' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
 ' where order_number = '||''''||l_rma_number||'''' ||
  ' and soh.org_id ='|| l_operating_id ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and lsn.line_id = sol.line_id order by sol.line_id desc , lsn.lot_serial_id  ';

Line: 218


p_sql(17) := ' select distinct msn.* ' ||
' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
 ' where order_number = '||''''||l_rma_number||'''' ||
  ' and soh.org_id ='|| l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
          ' where rt.oe_order_header_id = soh.header_id ' ||
         ' and rt.oe_order_line_id = sol.line_id ' ||
           ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
           ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
           ' or exists (' ||
       ' select 3 ' ||
        ' from mtl_material_transactions ommt ' ||
          ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
         ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from oe_lot_serial_numbers lsn ' ||
          ' where lsn.line_id = sol.line_id ' ||
         ' and sol.ordered_item_id = msn.inventory_item_id ' ||
           ' and sol.ship_from_org_id = msn.current_organization_id ' ||
           ' and msn.serial_number  between lsn.from_serial_number  and nvl(lsn.to_serial_number ,' ||
           ' lsn.from_serial_number) ) ) order by msn.inventory_item_id , msn.serial_number  ';

Line: 246


p_sql(18) := ' select msnt.* ' ||
' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
  ' oe_order_headers_all soh , rcv_transactions rt ' ||
 ' where order_number = '||''''||l_rma_number||'''' ||
  ' and soh.org_id ='|| l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
   ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
   ' or exists (' ||
       ' select 2 ' ||
        ' from mtl_transaction_lots_temp mtlt ' ||
          ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
         ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) )  ';

Line: 263


p_sql(19) := ' select msni.* ' ||
' from mtl_transactions_interface mti , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
  ' mtl_serial_numbers_interface msni , rcv_transactions rt ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mti.rcv_transaction_id = rt.transaction_id ' ||
   ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
   ' or exists (' ||
       ' select 3 ' ||
        ' from mtl_transaction_lots_interface mtln ' ||
          ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
         ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) )' ;

Line: 280


p_sql(20) := ' select mut.* ' ||
' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,' ||
  ' mtl_unit_transactions mut ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
   ' and (mut.transaction_id = mmt.transaction_id ' ||
   ' or exists (' ||
       ' select 1 ' ||
        ' from mtl_transaction_lot_numbers mtln ' ||
          ' where mtln.transaction_id = mmt.transaction_id ' ||
         ' and mut.transaction_id = mtln.serial_transaction_id ) )  ';

Line: 299


        p_sql(21) := ' select rst.* ' ||
' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rst.shipment_line_id = rsl.shipment_line_id  ';

Line: 310


            p_sql(22) := ' select distinct rsi.* ' ||
' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsi.item_id = rsl.item_id ' ||
   ' and rsi.organization_id = rsl.to_organization_id  ';

Line: 322


        p_sql(23) := ' select distinct mln.* ' ||
' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
 ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln ' ||
          ' where rt.oe_order_header_id = soh.header_id ' ||
         ' and rt.oe_order_line_id = sol.line_id ' ||
           ' 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 ) ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
          ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
         ' and ommt.transaction_id = mtln.transaction_id ' ||
           ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
           ' and mln.organization_id = ommt.organization_id ' ||
           ' and mln.lot_number = mtln.lot_number ) ' ||
           ' or exists (' ||
       ' select 3 ' ||
        ' from oe_lot_serial_numbers lsn ' ||
          ' where lsn.line_id = sol.line_id ' ||
         ' and sol.ordered_item_id = mln.inventory_item_id ' ||
           ' and sol.ship_from_org_id = mln.organization_id ' ||
           ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
           ' mln.lot_number  ';

Line: 356


    p_sql(24) := ' select mtln.* ' ||
' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
||
  ' soh ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mmt.transaction_id = mtln.transaction_id ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions rt ' ||
          ' where rt.oe_order_line_id = sol.line_id ' ||
         ' and mmt.rcv_transaction_id = rt.transaction_id ) ' ||
           ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
,' ||
       ' mtln.transaction_id  ';

Line: 376


p_sql(25):= ' select mtli.* ' ||
' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , oe_order_lines_all sol ,' ||
  ' oe_order_headers_all soh , rcv_transactions rt ' ||
' where soh.order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id  ';

Line: 389


    p_sql(26) := ' select mtlt.* ' ||
' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
  ' oe_order_headers_all soh , rcv_transactions rt ' ||
' where soh.order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
   ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number  ';

Line: 401


    p_sql(27) := ' select rlt.* ' ||
' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num  ';

Line: 411


   p_sql(28) := ' SELECT DISTINCT rp.* ' ||
                ' FROM    rcv_parameters rp, oe_order_headers_all soh , oe_order_lines_all sol ' ||
                ' where soh.order_number ='||''''||l_rma_number||'''' ||
                ' and soh.org_id = '||l_operating_id ||
                ' and soh.header_id = sol.header_id ' ||
                ' and sol.ship_from_org_id = rp.organization_id ' ;

Line: 418


   p_sql(29) := ' SELECT DISTINCT psp.* ' ||
                ' FROM    po_system_parameters_all psp, oe_order_headers_all soh ' ||
                ' where soh.order_number ='||''''||l_rma_number||'''' ||
                ' and soh.org_id = '||l_operating_id ||
                ' and soh.org_id = psp.org_id ' ;

Line: 424


       p_sql(30) := ' SELECT  fsp.* ' ||
' FROM    financials_system_params_all fsp, oe_order_headers_all soh ' ||
' where fsp.org_id  = soh.org_id '      ||
' and soh.order_number ='||''''||l_rma_number||'''' ||
' and soh.org_id = '||l_operating_id ;

Line: 443


p_sql(1) := ' select soh.* ' ||
' from oe_order_headers_all soh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and exists (' ||
   ' select 1 ' ||
    ' from oe_order_lines_all sol ' ||
      ' where sol.line_category_code = ''RETURN'' ' ||
     ' and sol.header_id = soh.header_id )  ';

Line: 454


p_sql(2) := ' select sol.* ' ||
' from oe_order_lines_all sol , oe_order_headers_all soh ' ||
 ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
 ' and sol.line_category_code = ''RETURN''' ||
 ' and sol.header_id = soh.header_id  ';

Line: 463


p_sql(3) := ' select distinct msi.* ' ||
' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||
 ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and msi.inventory_item_id = sol.inventory_item_id ' ||
   ' and msi.organization_id = sol.ship_from_org_id  ';

Line: 475


p_sql(4) := ' select rsh.* ' ||
' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where soh.order_number = '||''''||l_rma_number||'''' ||
    ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
    ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id  '||
      ' and soh.org_id ='|| l_operating_id ||
      ' union all' ||
    '  select rsh.*  '||
    'from rcv_shipment_headers rsh, oe_order_headers_all soh , oe_order_lines_all sol,rcv_transactions_interface rti '||
' WHERE rsh.shipment_header_id = rti.shipment_header_id'||
' AND rti.oe_order_header_id = soh.header_id' ||
' and soh.header_id = sol.header_id'||
' and rti.oe_order_line_id = sol.line_id' ||
' and soh.order_number = '||''''||l_rma_number||'''' ||
 ' and sol.line_number = '|| l_line_num ||
' and soh.org_id ='|| l_operating_id ;

Line: 497


p_sql(5) := ' select rsl.* ' ||
' from rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN''  ';

Line: 508


p_sql(6) := ' select rt.* ' ||
' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN''  ';

Line: 521


p_sql(7) := ' select rhi.* ' ||
' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and (rti.oe_order_header_id = soh.header_id ' ||
   ' or rti.oe_order_line_id = sol.line_id ) ' ||
       ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rhi.header_interface_id = rti.header_interface_id  ';

Line: 534


p_sql(8) := ' select rti.* ' ||
' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and (rti.oe_order_header_id = soh.header_id ' ||
   ' or rti.oe_order_line_id = sol.line_id ) ' ||
       ' and sol.header_id = soh.header_id' ||
      ' and sol.line_category_code = ''RETURN''  ';

Line: 545


p_sql(9) := ' select pie.* ' ||
' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions_interface rti ' ||
          ' where (rti.oe_order_header_id = soh.header_id ' ||
         ' or rti.oe_order_line_id = sol.line_id ) ' ||
               ' and pie.interface_line_id = rti.interface_transaction_id ' ||
           ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
          ' where (rti.oe_order_header_id = soh.header_id ' ||
         ' or rti.oe_order_line_id = sol.line_id ) ' ||
               ' and rhi.header_interface_id = rti.header_interface_id ' ||
           ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
           ' and pie.interface_header_id = rhi.header_interface_id) )  ';

Line: 568


p_sql(10) := ' select distinct ood.* ' ||
' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and ood.organization_id = rsl.to_organization_id  ';

Line: 581


p_sql(11) := ' select distinct mp.* ' ||
' from mtl_parameters mp , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mp.organization_id = rsl.to_organization_id  ';

Line: 594


p_sql(12) := ' select mmt.* ' ||
' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id  ';

Line: 607


p_sql(13) := ' 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 , oe_order_lines_all sol , oe_order_headers_all soh ,'
||
  ' rcv_transactions rt ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
   ' and mtt.transaction_type_id = mmt.transaction_type_id  ';

Line: 624


/*p_sql(14) := ' select distinct mtrl.* ' ||
' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
   ' and soh.org_id = '||l_operating_id ||
 ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
   ' and mtrl.reference_id = rt.oe_order_line_id ' ;*/

Line: 636


p_sql(14) := ' select distinct mtrl.* ' ||
 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol ' ||
   ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and rt.oe_order_header_id = soh.header_id ' ||
    ' and rt.oe_order_line_id = sol.line_id ' ||
    ' and sol.header_id = soh.header_id' ||
    ' and sol.line_number = '|| l_line_num ||
    ' and sol.line_category_code = ''RETURN'' ' ||
 ' and soh.org_id ='|| l_operating_id||
 ' AND mtrl.organization_id=rt.organization_id'||
 ' AND mtrl.inventory_item_id  = sol.inventory_item_id ' ||
 ' and Nvl(mtrl.revision,0)=Nvl(sol.item_revision,0) '||'and mtrl.line_status=7'||
 ' and mtrl.transaction_type_id=15';

Line: 650


p_sql(15) := ' select mmtt.* ' ||
' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and exists (' ||
   ' select 1 ' ||
    ' from rcv_transactions rt ' ||
      ' where rt.oe_order_header_id = soh.header_id ' ||
     ' and rt.oe_order_line_id = sol.line_id ' ||
       ' and mmtt.rcv_transaction_id = rt.transaction_id)  ';

Line: 664


p_sql(16) := ' select lsn.* ' ||
' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and lsn.line_id = sol.line_id order by sol.line_id desc , lsn.lot_serial_id  ';

Line: 674


p_sql(17) := ' select distinct msn.* ' ||
' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
          ' where rt.oe_order_header_id = soh.header_id ' ||
         ' and rt.oe_order_line_id = sol.line_id ' ||
           ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
           ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
           ' or exists (' ||
       ' select 3 ' ||
        ' from mtl_material_transactions ommt ' ||
          ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
         ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from oe_lot_serial_numbers lsn ' ||
          ' where lsn.line_id = sol.line_id ' ||
         ' and sol.ordered_item_id = msn.inventory_item_id ' ||
           ' and sol.ship_from_org_id = msn.current_organization_id ' ||
           ' and msn.serial_number  between lsn.from_serial_number  and nvl(lsn.to_serial_number ,
lsn.from_serial_number)' ||
           ' ) ) order by msn.inventory_item_id , msn.serial_number  ';

Line: 705


p_sql(18) := ' select msnt.* ' ||
             ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
' oe_order_headers_all soh , rcv_transactions rt ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
' and soh.org_id = '||l_operating_id ||
' and sol.header_id = soh.header_id' ||
' and sol.line_number = '|| l_line_num ||
' and sol.line_category_code = ''RETURN'' ' ||
' and rt.oe_order_header_id = soh.header_id ' ||
' and rt.oe_order_line_id = sol.line_id ' ||
' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
' or exists (' ||
' select 2 ' ||
' from mtl_transaction_lots_temp mtlt ' ||
' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) )  ';

Line: 723


p_sql(19) := ' select msni.* ' ||                                                                               ' from
mtl_transactions_interface mti , oe_order_lines_all sol , oe_order_headers_all soh , mtl_serial_numbers_interface' ||  '
msni , rcv_transactions rt ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
' and soh.org_id = '||l_operating_id ||
' and sol.header_id = soh.header_id ' ||
' and sol.line_category_code = ''RETURN'' ' ||
' and rt.oe_order_header_id = soh.header_id ' ||
' and rt.oe_order_line_id = sol.line_id ' ||
' and sol.line_number = '|| l_line_num ||
' and mti.rcv_transaction_id = rt.transaction_id ' ||
' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
' or exists (' ||
' select 3 ' ||
' from mtl_transaction_lots_interface mtln ' ||
' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
         ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) )  ';

Line: 741


p_sql(20) := ' select mut.* ' ||
' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,' ||
  ' mtl_unit_transactions mut ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
   ' and (mut.transaction_id = mmt.transaction_id ' ||
   ' or exists (' ||
       ' select 1 ' ||
        ' from mtl_transaction_lot_numbers mtln ' ||
          ' where mtln.transaction_id = mmt.transaction_id ' ||
         ' and mut.transaction_id = mtln.serial_transaction_id ) )  ';

Line: 762


        p_sql(21) := ' select rst.* ' ||
' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rst.shipment_line_id = rsl.shipment_line_id  ';

Line: 775


            p_sql(22) := ' select distinct rsi.* ' ||
' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id' ||
   ' and sol.line_number = '|| l_line_num ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsi.item_id = rsl.item_id ' ||
   ' and rsi.organization_id = rsl.to_organization_id  ';

Line: 788


        p_sql(23) := ' select distinct mln.* ' ||
' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
 ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln ' ||
          ' where rt.oe_order_header_id = soh.header_id ' ||
         ' and rt.oe_order_line_id = sol.line_id ' ||
           ' 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 ) ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
          ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
         ' and ommt.transaction_id = mtln.transaction_id ' ||
           ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
           ' and mln.organization_id = ommt.organization_id ' ||
           ' and mln.lot_number = mtln.lot_number ) ' ||
           ' or exists (' ||
       ' select 3 ' ||
        ' from oe_lot_serial_numbers lsn ' ||
          ' where lsn.line_id = sol.line_id ' ||
         ' and sol.ordered_item_id = mln.inventory_item_id ' ||
           ' and sol.ship_from_org_id = mln.organization_id ' ||
           ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
           ' mln.lot_number  ';

Line: 823


    p_sql(24) := ' select mtln.* ' ||
' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
||
  ' soh ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mmt.transaction_id = mtln.transaction_id ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions rt ' ||
          ' where rt.oe_order_line_id = sol.line_id ' ||
         ' and mmt.rcv_transaction_id = rt.transaction_id ) ' ||
           ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
,' ||
       ' mtln.transaction_id  ';

Line: 844


p_sql(25):= ' select mtli.* ' ||
' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , oe_order_lines_all sol ,' ||
  ' oe_order_headers_all soh , rcv_transactions rt ' ||
' where soh.order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id  ';

Line: 858


    p_sql(26) := ' select mtlt.* ' ||
' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol ,' ||
  ' oe_order_headers_all soh , rcv_transactions rt ' ||
' where soh.order_number = '||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
   ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number  ';

Line: 871


 p_sql(27) := ' select rlt.* ' ||
' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
 ' and sol.line_number = '|| l_line_num ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num  ';

Line: 882


      p_sql(28) := ' SELECT DISTINCT rp.* ' ||
                ' FROM    rcv_parameters rp, oe_order_headers_all soh, oe_order_lines_all sol ' ||
                ' where soh.order_number ='||''''||l_rma_number||'''' ||
                ' and soh.org_id = '||l_operating_id ||
                ' and soh.header_id = sol.header_id ' ||
                ' and sol.ship_from_org_id = rp.organization_id ' ;

Line: 889


   p_sql(29) := ' SELECT DISTINCT psp.* ' ||
                ' FROM    po_system_parameters_all psp, oe_order_headers_all soh ' ||
                ' where soh.order_number ='||''''||l_rma_number||'''' ||
                ' and soh.org_id = '||l_operating_id ||
                ' and soh.org_id = psp.org_id ' ;

Line: 895


       p_sql(30) := ' SELECT  fsp.* ' ||
' FROM    financials_system_params_all fsp, oe_order_headers_all soh ' ||
' where fsp.org_id  = soh.org_id '        ||
' and soh.order_number ='||''''||l_rma_number||'''' ||
' and soh.org_id = '||l_operating_id ;

Line: 915


p_sql(1) := 'select distinct soh.* ' ||
             ' from oe_order_headers_all soh, oe_order_lines_all sol' ||
      ' where soh.order_number ='||''''||l_rma_number||'''' ||
      ' and soh.org_id = '||l_operating_id ||
      ' and exists (' ||
      ' select 1 ' ||
       ' from oe_order_lines_all sol, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
       ' where sol.line_category_code = ''RETURN'' ' ||
       ' and sol.header_id = soh.header_id '||
       ' AND sol.line_id = rsl.oe_order_line_id' ||
       ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
        ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
       ' and rsh.organization_id ='|| l_organization_id ||
       ')' ;

Line: 933


p_sql(2) := ' select sol.* ' ||
' from oe_order_lines_all sol , oe_order_headers_all soh,rcv_shipment_headers rsh,' ||
  ' rcv_shipment_lines rsl ' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
   ' and rsl.oe_order_line_id = sol.line_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id ='|| l_organization_id ;

Line: 947


p_sql(3) := ' select distinct msi.* ' ||
' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and msi.inventory_item_id = sol.inventory_item_id ' ||
   ' and msi.organization_id = sol.ship_from_org_id  ';

Line: 957


p_sql(4) := ' select rsh.* ' ||
' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '||l_organization_id ;

Line: 969


p_sql(5) := ' select rsl.* ' ||
' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN''' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = ' || l_organization_id ;

Line: 983


p_sql(6) := ' select rt.* ' ||
' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol , rcv_shipment_headers rsh' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id' ||
   ' and rt.shipment_header_id = rsh.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
     ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsh.organization_id = ' || l_organization_id ;

Line: 998


p_sql(7) := ' select rhi.* ' ||
' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and (rti.oe_order_header_id = soh.header_id ' ||
   ' or rti.oe_order_line_id = sol.line_id ) ' ||
       ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rhi.header_interface_id = rti.header_interface_id  ';

Line: 1010


p_sql(8) := ' select rti.* ' ||
' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and (rti.oe_order_header_id = soh.header_id ' ||
   ' or rti.oe_order_line_id = sol.line_id ) ' ||
       ' and sol.header_id = soh.header_id' ||
   ' and rti.shipment_header_id = rsh.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
     ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsh.organization_id = ' || l_organization_id ;

Line: 1025


p_sql(9) := ' select pie.* ' ||
' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions_interface rti ' ||
          ' where (rti.oe_order_header_id = soh.header_id ' ||
         ' or rti.oe_order_line_id = sol.line_id ) ' ||
               ' and pie.interface_line_id = rti.interface_transaction_id ' ||
           ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from rcv_headers_interface rhi , rcv_transactions_interface rti ' ||
          ' where (rti.oe_order_header_id = soh.header_id ' ||
         ' or rti.oe_order_line_id = sol.line_id ) ' ||
               ' and rhi.header_interface_id = rti.header_interface_id ' ||
           ' and pie.table_name = ''RCV_HEADERS_INTERFACE'' ' ||
           ' and pie.interface_header_id = rhi.header_interface_id) )  ';

Line: 1047


p_sql(10) := ' select distinct ood.* ' ||
' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and ood.organization_id = rsl.to_organization_id  ';

Line: 1059


p_sql(11) := ' select distinct ood.* ' ||
' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and ood.organization_id = rsl.to_organization_id  ';

Line: 1071


p_sql(12) := ' select mmt.* ' ||
' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,
rcv_shipment_headers rsh' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rt.shipment_header_id = rsh.shipment_header_id' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id  ';

Line: 1086


p_sql(13) := ' 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 , oe_order_lines_all sol , oe_order_headers_all soh ,'
||
  ' rcv_transactions rt, rcv_shipment_headers rsh ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
      ' and rt.shipment_header_id = rt.shipment_header_id' ||
   ' and mtt.transaction_type_id = mmt.transaction_type_id  ';

Line: 1105


/*p_sql(14) := 'select distinct mtrl.* ' ||
' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
rcv_shipment_headers rsh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mtrl.reference = ''ORDER_LINE_ID'' ' ||
   ' and mtrl.reference_id = rt.oe_order_line_id ' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rt.shipment_header_id = rt.shipment_header_id' ||
   ' and soh.org_id = 2222 ';*/

Line: 1120


p_sql(14) := 'select distinct mtrl.* ' ||
 ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
 rcv_shipment_headers rsh ' ||
   ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and rt.oe_order_header_id = soh.header_id ' ||
    ' and rt.oe_order_line_id = sol.line_id ' ||
    ' and sol.header_id = soh.header_id ' ||
    ' and sol.line_category_code = ''RETURN'' ' ||
     ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
    ' and rsh.organization_id = '|| l_organization_id ||
    ' and rt.shipment_header_id = rt.shipment_header_id' ||
   ' and soh.org_id ='|| l_operating_id||
   ' AND mtrl.organization_id=rt.organization_id'||
   ' AND mtrl.inventory_item_id  = sol.inventory_item_id ' ||
 ' and mtrl.revision=sol.item_revision ' ||' and mtrl.line_status=7'||
 ' and mtrl.transaction_type_id=15';

Line: 1139


p_sql(15) := ' select mmtt.* ' ||
' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh
' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and exists (' ||
   ' select 1 ' ||
    ' from rcv_transactions rt ' ||
      ' where rt.oe_order_header_id = soh.header_id ' ||
     ' and rt.oe_order_line_id = sol.line_id ' ||
       ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
        ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
       ' and rsh.organization_id = '|| l_organization_id ||
       ' and rt.shipment_header_id = rsh.shipment_header_id)  ';

Line: 1156


p_sql(16) := ' select lsn.* ' ||
' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||
  ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and sol.header_id = soh.header_id ' ||
   ' and lsn.line_id = sol.line_id ' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
   ' and rsl.oe_order_line_id = sol.line_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' order by sol.line_id desc , lsn.lot_serial_id  ';

Line: 1171


p_sql(17) := ' select distinct msn.* ' ||
' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
  ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsl.oe_order_line_id = sol.line_id' ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions rt , mtl_material_transactions mmt ' ||
          ' where rt.oe_order_header_id = soh.header_id ' ||
         ' and rt.oe_order_line_id = sol.line_id ' ||
           ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
           ' and msn.last_transaction_id = mmt.transaction_id ) ' ||
           ' or exists (' ||
       ' select 3 ' ||
        ' from mtl_material_transactions ommt ' ||
          ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
         ' and msn.last_transaction_id = ommt.transaction_id ) ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from oe_lot_serial_numbers lsn ' ||
          ' where lsn.line_id = sol.line_id ' ||
         ' and sol.ordered_item_id = msn.inventory_item_id ' ||
           ' and sol.ship_from_org_id = msn.current_organization_id ' ||
           ' and msn.serial_number  between lsn.from_serial_number  and nvl(lsn.to_serial_number ,
lsn.from_serial_number)'
||
           ' ) ) order by msn.inventory_item_id , msn.serial_number  ';

Line: 1206


p_sql(18) := ' select msnt.* ' ||
' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
  ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmtt.rcv_transaction_id = rt.transaction_id' ||
   ' and rsh.shipment_header_id = rt.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
   ' or exists (' ||
       ' select 2 ' ||
        ' from mtl_transaction_lots_temp mtlt ' ||
          ' where msnt.transaction_temp_id = mtlt.serial_transaction_temp_id ' ||
         ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) )  ';

Line: 1227


p_sql(19) := ' select msni.* ' ||
  ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||
  ' oe_order_headers_all soh , mtl_serial_numbers_interface msni , rcv_transactions rt, ' ||
  ' rcv_shipment_headers rsh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mti.rcv_transaction_id = rt.transaction_id ' ||
   ' and rsh.shipment_header_id = rt.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and (msni.transaction_interface_id = mti.transaction_interface_id ' ||
   ' or exists (' ||
       ' select 3 ' ||
        ' from mtl_transaction_lots_interface mtln ' ||
          ' where mtln.transaction_interface_id = mti.transaction_interface_id ' ||
         ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) )  ';

Line: 1249


p_sql(20) := ' select mut.* ' ||
' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||
  ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
   ' and rsh.shipment_header_id = rt.shipment_header_id' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and (mut.transaction_id = mmt.transaction_id ' ||
   ' or exists (' ||
       ' select 1 ' ||
        ' from mtl_transaction_lot_numbers mtln ' ||
          ' where mtln.transaction_id = mmt.transaction_id ' ||
         ' and mut.transaction_id = mtln.serial_transaction_id ) )  ';

Line: 1271


        p_sql(21) := ' select rst.* ' ||
' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
  ' rcv_shipment_headers rsh' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rst.shipment_line_id = rsl.shipment_line_id  ';

Line: 1286


            p_sql(22) := ' select distinct rsi.* ' ||
' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
  ' rcv_shipment_headers rsh' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rsi.item_id = rsl.item_id ' ||
   ' and rsi.organization_id = rsl.to_organization_id  ';

Line: 1303


        p_sql(23) := ' select distinct mln.* ' ||
' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
 ' where soh.order_number ='||''''||l_rma_number||'''' ||
 ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln ' ||
          ' where rt.oe_order_header_id = soh.header_id ' ||
         ' and rt.oe_order_line_id = sol.line_id ' ||
           ' 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 ) ' ||
           ' or exists (' ||
       ' select 2 ' ||
        ' from mtl_material_transactions ommt , mtl_transaction_lot_numbers mtln ' ||
          ' where ommt.trx_source_line_id = sol.reference_line_id ' ||
         ' and ommt.transaction_id = mtln.transaction_id ' ||
           ' and mln.inventory_item_id = ommt.inventory_item_id ' ||
           ' and mln.organization_id = ommt.organization_id ' ||
           ' and mln.lot_number = mtln.lot_number ) ' ||
           ' or exists (' ||
       ' select 3 ' ||
        ' from oe_lot_serial_numbers lsn ' ||
          ' where lsn.line_id = sol.line_id ' ||
         ' and sol.ordered_item_id = mln.inventory_item_id ' ||
           ' and sol.ship_from_org_id = mln.organization_id ' ||
           ' and lsn.lot_number = mln.lot_number ) ) order by mln.organization_id , mln.inventory_item_id ,' ||
           ' mln.lot_number  ';

Line: 1337


    p_sql(24) := ' select mtln.* ' ||
' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
||
  ' soh ' ||
' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mmt.transaction_id = mtln.transaction_id ' ||
   ' and (exists (' ||
   ' select 1 ' ||
        ' from rcv_transactions rt, rcv_shipment_headers rsh ' ||
          ' where rt.oe_order_line_id = sol.line_id ' ||
         ' and mmt.rcv_transaction_id = rt.transaction_id' ||
           ' and rsh.shipment_header_id = rt.shipment_header_id' ||
            ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
           '       and rsh.organization_id = '|| l_organization_id ||
' ) ' ||
           ' or mmt.trx_source_line_id = sol.reference_line_id ) order by mtln.organization_id , mtln.inventory_item_id
,' ||
       ' mtln.transaction_id  ';

Line: 1362


p_sql(25):= ' select mtli.* ' ||
' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , ' ||
  ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||
  ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rt.shipment_header_id = rsh.shipment_header_id' ||
   ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id  ';

Line: 1378


    p_sql(26) := ' select mtlt.* ' ||
' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
  ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and sol.header_id = soh.header_id ' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rt.oe_order_header_id = soh.header_id ' ||
   ' and rt.oe_order_line_id = sol.line_id ' ||
   ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rt.shipment_header_id = rsh.shipment_header_id' ||
   ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number  ';

Line: 1393


    p_sql(27) := ' select rlt.* ' ||
' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , ' ||
  ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||
       ' where soh.order_number ='||''''||l_rma_number||'''' ||
  ' and soh.org_id = '||l_operating_id ||
   ' and rsl.oe_order_header_id = soh.header_id ' ||
   ' and rsl.oe_order_line_id = sol.line_id ' ||
   ' and sol.header_id = soh.header_id ' ||
    ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
   ' and rsh.organization_id = '|| l_organization_id ||
   ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
   ' and sol.line_category_code = ''RETURN'' ' ||
   ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num  ';

Line: 1407


   p_sql(28) := ' SELECT DISTINCT rp.* ' ||
                ' FROM    rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh, oe_order_lines_all sol
' ||
                ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
                ' and rsh.organization_id = '|| l_organization_id ||
                ' AND rsh.organization_id = rp.organization_id '  ;

Line: 1414


    p_sql(29) := ' SELECT DISTINCT psp.* ' ||
                ' FROM    po_system_parameters_all psp, oe_order_headers_all soh ' ||
                ' where soh.order_number ='||''''||l_rma_number||'''' ||
                ' and soh.org_id = '||l_operating_id ||
                ' and soh.org_id = psp.org_id ' ;

Line: 1420


       p_sql(30) := ' SELECT  fsp.* ' ||
' FROM    financials_system_params_all fsp, oe_order_headers_all soh ' ||
' where fsp.org_id  = soh.org_id '     ||
' and soh.order_number ='||''''||l_rma_number||'''' ||
' and soh.org_id = '||l_operating_id ;