DBA Data[Home] [Help]

APPS.RMA_RCV_DIAGNOSTICS dependencies on OE_ORDER_LINES_ALL

Line 10: l_line_num oe_order_lines_all.line_number%TYPE := p_line_num;

6: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
7:
8: l_operating_id rcv_shipment_headers.receipt_num%TYPE := p_operating_id;
9: l_rma_number rcv_shipment_headers.organization_id%TYPE := p_rma_number;
10: l_line_num oe_order_lines_all.line_number%TYPE := p_line_num;
11: l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
12: l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
13:
14: BEGIN

Line 18: ' oe_order_lines_all sol,' ||

14: BEGIN
15:
16: p_sql(1) := 'select soh.* ' ||
17: ' from oe_order_headers_all soh,' ||
18: ' oe_order_lines_all sol,' ||
19: ' rcv_shipment_headers rsh,' ||
20: ' rcv_shipment_lines rsl' ||
21: ' where soh.order_number ='||''''||l_rma_number||'''' ||
22: ' and soh.org_id = '||l_operating_id ||

Line 30: ' from oe_order_lines_all sol ' ||

26: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
27: ' and rsh.organization_id = '|| l_organization_id ||
28: ' and exists (' ||
29: ' select 1 ' ||
30: ' from oe_order_lines_all sol ' ||
31: ' where sol.line_category_code = ''RETURN'' ' ||
32: ' and sol.header_id = soh.header_id ) ';
33:
34:

Line 37: ' from oe_order_lines_all sol , oe_order_headers_all soh,rcv_shipment_headers rsh,' ||

33:
34:
35:
36: p_sql(2) := ' select sol.* ' ||
37: ' from oe_order_lines_all sol , oe_order_headers_all soh,rcv_shipment_headers rsh,' ||
38: ' rcv_shipment_lines rsl ' ||
39: ' where soh.order_number ='||''''||l_rma_number||'''' ||
40: ' and sol.line_number = '|| l_line_num ||
41: ' and soh.org_id = '||l_operating_id ||

Line 52: ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh,

48:
49:
50:
51: p_sql(3) := ' select distinct msi.* ' ||
52: ' from mtl_system_items msi , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh,
53: rcv_shipment_lines rsl' ||
54: ' where soh.order_number ='||''''||l_rma_number||'''' ||
55: ' and sol.line_number = '|| l_line_num ||
56: ' and soh.org_id = '||l_operating_id ||

Line 69: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

65:
66:
67:
68: p_sql(4) := ' select rsh.* ' ||
69: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
70: ' where soh.order_number ='||''''||l_rma_number||'''' ||
71: ' and sol.line_number = '|| l_line_num ||
72: ' and soh.org_id = '||l_operating_id ||
73: ' and rsl.oe_order_header_id = soh.header_id ' ||

Line 82: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||

78: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
79: ' and rsh.organization_id = '||l_organization_id ;
80:
81: p_sql(5) := ' select rsl.* ' ||
82: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol ' ||
83: ' where soh.order_number ='||''''||l_rma_number||'''' ||
84: ' and sol.line_number = '|| l_line_num ||
85: ' and soh.org_id = '||l_operating_id ||
86: ' and rsl.oe_order_header_id = soh.header_id ' ||

Line 97: ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol , rcv_shipment_headers rsh' ||

93:
94:
95:
96: p_sql(6) := ' select rt.* ' ||
97: ' from rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol , rcv_shipment_headers rsh' ||
98: ' where soh.order_number ='||''''||l_rma_number||'''' ||
99: ' and sol.line_number = '|| l_line_num ||
100: ' and soh.org_id = '||l_operating_id ||
101: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 113: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '

109:
110:
111:
112: p_sql(7) := ' select rhi.* ' ||
113: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol '
114: ||
115: ' where soh.order_number ='||''''||l_rma_number||'''' ||
116: ' and sol.line_number = '|| l_line_num ||
117: ' and soh.org_id = '||l_operating_id ||

Line 126: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||

122: ' and rhi.header_interface_id = rti.header_interface_id ';
123:
124:
125: p_sql(8) := ' select rti.* ' ||
126: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
127: ' where soh.order_number ='||''''||l_rma_number||'''' ||
128: ' and sol.line_number = '|| l_line_num ||
129: ' and soh.org_id = '||l_operating_id ||
130: ' and (rti.oe_order_header_id = soh.header_id ' ||

Line 142: ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||

138:
139:
140:
141: p_sql(9) := ' select pie.* ' ||
142: ' from po_interface_errors pie , oe_order_headers_all soh , oe_order_lines_all sol ' ||
143: ' where soh.order_number ='||''''||l_rma_number||'''' ||
144: ' and sol.line_number = '|| l_line_num ||
145: ' and soh.org_id = '||l_operating_id ||
146: ' and sol.header_id = soh.header_id ' ||

Line 166: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '

162: ' and pie.interface_header_id = rhi.header_interface_id) ) ';
163:
164:
165: p_sql(10) := ' select distinct ood.* ' ||
166: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol '
167: ||
168: ' , rcv_shipment_headers rsh' ||
169: ' where soh.order_number ='||''''||l_rma_number||'''' ||
170: ' and sol.line_number = '|| l_line_num ||

Line 185: ' from mtl_parameters mp , rcv_shipment_lines rsl , rcv_shipment_headers rsh, oe_order_lines_all sol,

181:
182:
183:
184: p_sql(11) := ' select distinct mp.* ' ||
185: ' from mtl_parameters mp , rcv_shipment_lines rsl , rcv_shipment_headers rsh, oe_order_lines_all sol,
186: oe_order_headers_all soh ' ||
187: ' where rsl.oe_order_line_id = sol.line_id' ||
188: ' and sol.line_category_code = ''RETURN'' ' ||
189: ' and soh.header_id = sol.header_id ' ||

Line 200: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,

196: ' and rsh.organization_id = ' || l_organization_id ;
197:
198:
199: p_sql(12) := ' select mmt.* ' ||
200: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,
201: rcv_shipment_headers rsh' ||
202: ' where soh.order_number ='||''''||l_rma_number||'''' ||
203: ' and sol.line_number = '|| l_line_num ||
204: ' and soh.org_id = '||l_operating_id ||

Line 217: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'

213:
214:
215: p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'||
216: ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
217: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
218: ||
219: ' rcv_transactions rt, rcv_shipment_headers rsh ' ||
220: ' where soh.order_number ='||''''||l_rma_number||'''' ||
221: ' and sol.line_number = '|| l_line_num ||

Line 236: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,

232:
233:
234:
235: /*p_sql(14) := 'select distinct mtrl.* ' ||
236: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
237: rcv_shipment_headers rsh ' ||
238: ' where soh.order_number ='||''''||l_rma_number||'''' ||
239: ' and sol.line_number = '|| l_line_num ||
240: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 252: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,

248: ' and rt.shipment_header_id = rt.shipment_header_id' ||
249: ' and soh.org_id = 2222 ';*/
250:
251: p_sql(14) := 'select distinct mtrl.* ' ||
252: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
253: rcv_shipment_headers rsh ' ||
254: ' where soh.order_number ='||''''||l_rma_number||'''' ||
255: ' and sol.line_number = '|| l_line_num ||
256: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 272: ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh

268:
269:
270:
271: p_sql(15) := ' select mmtt.* ' ||
272: ' from mtl_material_transactions_temp mmtt , oe_order_lines_all sol , oe_order_headers_all soh, rcv_shipment_headers rsh
273: ' ||
274: ' where soh.order_number ='||''''||l_rma_number||'''' ||
275: ' and sol.line_number = '|| l_line_num ||
276: ' and soh.org_id = '||l_operating_id ||

Line 290: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||

286: ' and rsh.organization_id = '|| l_organization_id ||
287: ' and rt.shipment_header_id = rsh.shipment_header_id) ';
288:
289: p_sql(16) := ' select lsn.* ' ||
290: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||
291: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
292: ' where soh.order_number ='||''''||l_rma_number||'''' ||
293: ' and sol.line_number = '|| l_line_num ||
294: ' and soh.org_id = '||l_operating_id ||

Line 306: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||

302: ' order by sol.line_id desc , lsn.lot_serial_id ';
303:
304:
305: p_sql(17) := ' select distinct msn.* ' ||
306: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
307: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
308: ' where soh.order_number ='||''''||l_rma_number||'''' ||
309: ' and sol.line_number = '|| l_line_num ||
310: ' and soh.org_id = '||l_operating_id ||

Line 342: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||

338: ' ) ) order by msn.inventory_item_id , msn.serial_number ';
339:
340:
341: p_sql(18) := ' select msnt.* ' ||
342: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
343: ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||
344: ' where soh.order_number ='||''''||l_rma_number||'''' ||
345: ' and sol.line_number = '|| l_line_num ||
346: ' and soh.org_id = '||l_operating_id ||

Line 364: ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||

360: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
361:
362:
363: p_sql(19) := ' select msni.* ' ||
364: ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||
365: ' oe_order_headers_all soh , mtl_serial_numbers_interface msni , rcv_transactions rt, ' ||
366: ' rcv_shipment_headers rsh ' ||
367: ' where soh.order_number ='||''''||l_rma_number||'''' ||
368: ' and sol.line_number = '|| l_line_num ||

Line 387: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||

383: ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) ) ';
384:
385:
386: p_sql(20) := ' select mut.* ' ||
387: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||
388: ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||
389: ' where soh.order_number ='||''''||l_rma_number||'''' ||
390: ' and sol.line_number = '|| l_line_num ||
391: ' and soh.org_id = '||l_operating_id ||

Line 410: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||

406:
407:
408:
409: p_sql(21) := ' select rst.* ' ||
410: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
411: ' rcv_shipment_headers rsh' ||
412: ' where soh.order_number ='||''''||l_rma_number||'''' ||
413: ' and sol.line_number = '|| l_line_num ||
414: ' and soh.org_id = '||l_operating_id ||

Line 426: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||

422: ' and rst.shipment_line_id = rsl.shipment_line_id ';
423:
424:
425: p_sql(22) := ' select distinct rsi.* ' ||
426: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
427: ' rcv_shipment_headers rsh' ||
428: ' where soh.order_number ='||''''||l_rma_number||'''' ||
429: ' and sol.line_number = '|| l_line_num ||
430: ' and soh.org_id = '||l_operating_id ||

Line 444: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||

440:
441:
442:
443: p_sql(23) := ' select distinct mln.* ' ||
444: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol ' ||
445: ' where soh.order_number ='||''''||l_rma_number||'''' ||
446: ' and sol.line_number = '|| l_line_num ||
447: ' and soh.org_id = '||l_operating_id ||
448: ' and sol.header_id = soh.header_id ' ||

Line 483: ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'

479: ' mln.lot_number ';
480:
481:
482: p_sql(24) := ' select mtln.* ' ||
483: ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all'
484: ||
485: ' soh ' ||
486: ' where soh.order_number ='||''''||l_rma_number||'''' ||
487: ' and sol.line_number = '|| l_line_num ||

Line 510: ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||

506:
507:
508: p_sql(25):= ' select mtli.* ' ||
509: ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , ' ||
510: ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||
511: ' where soh.order_number ='||''''||l_rma_number||'''' ||
512: ' and sol.line_number = '|| l_line_num ||
513: ' and soh.org_id = '||l_operating_id ||
514: ' and sol.header_id = soh.header_id ' ||

Line 526: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||

522: ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
523:
524:
525: p_sql(26) := ' select mtlt.* ' ||
526: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
527: ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||
528: ' where soh.order_number ='||''''||l_rma_number||'''' ||
529: ' and sol.line_number = '|| l_line_num ||
530: ' and soh.org_id = '||l_operating_id ||

Line 543: ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||

539: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
540:
541: p_sql(27) := ' select rlt.* ' ||
542: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh, ' ||
543: ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||
544: ' where soh.order_number ='||''''||l_rma_number||'''' ||
545: ' and sol.line_number = '|| l_line_num ||
546: ' and soh.org_id = '||l_operating_id ||
547: ' and rsl.oe_order_header_id = soh.header_id ' ||

Line 591: ' oe_order_lines_all sol,' ||

587: BEGIN
588:
589: p_sql(1) := 'select distinct soh.* ' ||
590: ' from oe_order_headers_all soh,' ||
591: ' oe_order_lines_all sol,' ||
592: ' rcv_shipment_headers rsh,' ||
593: ' rcv_shipment_lines rsl' ||
594: ' where soh.header_id = sol.header_id' ||
595: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 601: ' from oe_order_lines_all sol ' ||

597: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
598: ' and rsh.organization_id = '|| l_organization_id ||
599: ' and exists (' ||
600: ' select 1 ' ||
601: ' from oe_order_lines_all sol ' ||
602: ' where sol.line_category_code = ''RETURN'' ' ||
603: ' and sol.header_id = soh.header_id ) ';
604:
605:

Line 608: ' from oe_order_lines_all sol , rcv_shipment_headers rsh,' ||

604:
605:
606:
607: p_sql(2) := ' select distinct sol.* ' ||
608: ' from oe_order_lines_all sol , rcv_shipment_headers rsh,' ||
609: ' rcv_shipment_lines rsl ' ||
610: ' where sol.line_category_code = ''RETURN'' ' ||
611: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
612: ' and rsl.oe_order_line_id = sol.line_id' ||

Line 619: ' from mtl_system_items msi , oe_order_lines_all sol, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||

615:
616:
617:
618: p_sql(3) := ' select distinct msi.* ' ||
619: ' from mtl_system_items msi , oe_order_lines_all sol, rcv_shipment_headers rsh, rcv_shipment_lines rsl ' ||
620: ' where sol.line_category_code = ''RETURN'' ' ||
621: ' and sol.line_id = rsl.oe_order_line_id' ||
622: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
623: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||

Line 630: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_lines_all sol ' ||

626: ' and msi.organization_id = sol.ship_from_org_id ';
627:
628:
629: p_sql(4) := ' select distinct rsh.* ' ||
630: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl , oe_order_lines_all sol ' ||
631: ' where rsl.oe_order_header_id = sol.header_id ' ||
632: ' and rsl.oe_order_line_id = sol.line_id ' ||
633: ' and sol.line_category_code = ''RETURN'' ' ||
634: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 639: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_lines_all sol ' ||

635: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
636: ' and rsh.organization_id = '||l_organization_id ;
637:
638: p_sql(5) := ' select distinct rsl.* ' ||
639: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl , oe_order_lines_all sol ' ||
640: ' where rsl.oe_order_header_id = sol.header_id ' ||
641: ' and rsl.oe_order_line_id = sol.line_id ' ||
642: ' and sol.line_category_code = ''RETURN''' ||
643: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 650: ' from rcv_transactions rt , oe_order_lines_all sol , rcv_shipment_headers rsh' ||

646:
647:
648:
649: p_sql(6) := ' select rt.* ' ||
650: ' from rcv_transactions rt , oe_order_lines_all sol , rcv_shipment_headers rsh' ||
651: ' where rt.oe_order_line_id = sol.line_id ' ||
652: ' and rt.shipment_header_id = rsh.shipment_header_id' ||
653: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
654: ' and sol.line_category_code = ''RETURN'' ' ||

Line 659: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_lines_all sol ' ||

655: ' and rsh.organization_id = ' || l_organization_id ;
656:
657:
658: p_sql(7) := ' select distinct rhi.* ' ||
659: ' from rcv_headers_interface rhi , rcv_transactions_interface rti , oe_order_lines_all sol ' ||
660: ' where rti.oe_order_header_id = sol.header_id ' ||
661: ' and rti.oe_order_line_id = sol.line_id ' ||
662: ' and rhi.header_interface_id = rti.header_interface_id ' ||
663: ' and rhi.receipt_num = '||''''||l_receipt_number||'''' ||

Line 669: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||

665: ' and rhi.ship_to_organization_id = ' || l_organization_id ;
666:
667:
668: p_sql(8) := ' select rti.* ' ||
669: ' from rcv_transactions_interface rti , oe_order_headers_all soh , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
670: ' where rti.oe_order_header_id = soh.header_id ' ||
671: ' and rti.oe_order_line_id = sol.line_id ' ||
672: ' and sol.header_id = soh.header_id' ||
673: ' and rti.shipment_header_id = rsh.shipment_header_id' ||

Line 681: ' from po_interface_errors pie , oe_order_lines_all sol, rcv_transactions_interface rti, rcv_shipment_headers rsh' ||

677:
678:
679:
680: p_sql(9) := ' select distinct pie.* ' ||
681: ' from po_interface_errors pie , oe_order_lines_all sol, rcv_transactions_interface rti, rcv_shipment_headers rsh' ||
682: ' where sol.line_category_code = ''RETURN'' ' ||
683: ' and rsh.shipment_header_id = rti.shipment_header_id' ||
684: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||
685: ' and rsh.organization_id = ' || l_organization_id ||

Line 690: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||

686: ' and pie.interface_line_id = rti.interface_transaction_id' ||
687: ' and rti.oe_order_line_id = sol.line_id' ;
688:
689: p_sql(10) := ' select distinct ood.* ' ||
690: ' from org_organization_definitions ood , rcv_shipment_lines rsl , oe_order_lines_all sol, rcv_shipment_headers rsh ' ||
691: ' where rsl.oe_order_line_id = sol.line_id ' ||
692: ' and sol.line_category_code = ''RETURN'' ' ||
693: ' and ood.organization_id = rsl.to_organization_id ' ||
694: ' and rsl.shipment_header_id = rsh.shipment_header_id '||

Line 701: ' from mtl_parameters mp , rcv_shipment_lines rsl , rcv_shipment_headers rsh, oe_order_lines_all sol ' ||

697:
698:
699:
700: p_sql(11) := ' select distinct mp.* ' ||
701: ' from mtl_parameters mp , rcv_shipment_lines rsl , rcv_shipment_headers rsh, oe_order_lines_all sol ' ||
702: ' where rsl.oe_order_line_id = sol.line_id' ||
703: ' and sol.line_category_code = ''RETURN'' ' ||
704: ' and rsl.shipment_header_id = rsh.shipment_header_id'||
705: ' and mp.organization_id = rsl.to_organization_id '||

Line 711: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,

707: ' and rsh.organization_id = ' || l_organization_id ;
708:
709:
710: p_sql(12) := ' select mmt.* ' ||
711: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt ,
712: rcv_shipment_headers rsh' ||
713: ' where sol.header_id = soh.header_id ' ||
714: ' and sol.line_category_code = ''RETURN'' ' ||
715: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 725: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'

721:
722:
723: p_sql(13) := ' select distinct mtt.transaction_type_id , mtt.transaction_type_name , mtt.transaction_source_type_id ,'||
724: ' mtt.transaction_action_id , mtt.user_defined_flag , mtt.disable_date ' ||
725: ' from mtl_transaction_types mtt , mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh ,'
726: ||
727: ' rcv_transactions rt, rcv_shipment_headers rsh ' ||
728: ' where sol.header_id = soh.header_id ' ||
729: ' and sol.line_category_code = ''RETURN'' ' ||

Line 741: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,

737:
738:
739:
740: /*p_sql(14) := 'select distinct mtrl.* ' ||
741: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
742: rcv_shipment_headers rsh ' ||
743: ' where rt.oe_order_header_id = soh.header_id ' ||
744: ' and rt.oe_order_line_id = sol.line_id ' ||
745: ' and sol.header_id = soh.header_id ' ||

Line 754: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,

750: ' and rsh.organization_id = '|| l_organization_id ||
751: ' and rt.shipment_header_id = rsh.shipment_header_id' ;*/
752:
753: p_sql(14) := 'select distinct mtrl.* ' ||
754: ' from mtl_txn_request_lines mtrl , rcv_transactions rt , oe_order_headers_all soh , oe_order_lines_all sol,
755: rcv_shipment_headers rsh ' ||
756: ' where rt.oe_order_header_id = soh.header_id ' ||
757: ' and rt.oe_order_line_id = sol.line_id ' ||
758: ' and sol.header_id = soh.header_id ' ||

Line 777: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||

773: ' and rsh.organization_id = '|| l_organization_id ;
774:
775:
776: p_sql(16) := ' select lsn.* ' ||
777: ' from oe_lot_serial_numbers lsn , oe_order_lines_all sol , oe_order_headers_all soh,' ||
778: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
779: ' where sol.line_category_code = ''RETURN'' ' ||
780: ' and sol.header_id = soh.header_id ' ||
781: ' and lsn.line_id = sol.line_id ' ||

Line 790: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||

786: ' order by sol.line_id desc , lsn.lot_serial_id ';
787:
788:
789: p_sql(17) := ' select distinct msn.* ' ||
790: ' from mtl_serial_numbers msn , oe_order_lines_all sol , oe_order_headers_all soh ,' ||
791: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
792: ' where sol.header_id = soh.header_id ' ||
793: ' and sol.line_category_code = ''RETURN'' ' ||
794: ' and rsl.oe_order_line_id = sol.line_id' ||

Line 823: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||

819: ' ) ) order by msn.inventory_item_id , msn.serial_number ';
820:
821:
822: p_sql(18) := ' select msnt.* ' ||
823: ' from mtl_serial_numbers_temp msnt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
824: ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||
825: ' where sol.header_id = soh.header_id ' ||
826: ' and sol.line_category_code = ''RETURN'' ' ||
827: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 842: ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||

838: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id ) ) ';
839:
840:
841: p_sql(19) := ' select msni.* ' ||
842: ' from mtl_transactions_interface mti , oe_order_lines_all sol , ' ||
843: ' oe_order_headers_all soh , mtl_serial_numbers_interface msni , rcv_transactions rt, ' ||
844: ' rcv_shipment_headers rsh ' ||
845: ' where sol.header_id = soh.header_id ' ||
846: ' and sol.line_category_code = ''RETURN'' ' ||

Line 862: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||

858: ' and msni.transaction_interface_id = mtln.serial_transaction_temp_id ) ) ';
859:
860:
861: p_sql(20) := ' select mut.* ' ||
862: ' from mtl_material_transactions mmt , oe_order_lines_all sol , oe_order_headers_all soh , ' ||
863: ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||
864: ' where sol.header_id = soh.header_id ' ||
865: ' and sol.line_category_code = ''RETURN'' ' ||
866: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 882: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||

878:
879:
880:
881: p_sql(21) := ' select rst.* ' ||
882: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
883: ' rcv_shipment_headers rsh' ||
884: ' where rsl.oe_order_header_id = soh.header_id ' ||
885: ' and rsl.oe_order_line_id = sol.line_id ' ||
886: ' and sol.header_id = soh.header_id ' ||

Line 895: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||

891: ' and rst.shipment_line_id = rsl.shipment_line_id ';
892:
893:
894: p_sql(22) := ' select distinct rsi.* ' ||
895: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , oe_order_headers_all soh , oe_order_lines_all sol,' ||
896: ' rcv_shipment_headers rsh' ||
897: ' where rsl.oe_order_header_id = soh.header_id ' ||
898: ' and rsl.oe_order_line_id = sol.line_id ' ||
899: ' and sol.header_id = soh.header_id ' ||

Line 910: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol' ||

906:
907:
908:
909: p_sql(23) := ' select distinct mln.* ' ||
910: ' from mtl_lot_numbers mln , oe_order_headers_all soh , oe_order_lines_all sol' ||
911: ' where sol.header_id = soh.header_id ' ||
912: ' and sol.line_category_code = ''RETURN'' ' ||
913: ' and (exists (' ||
914: ' select 1 ' ||

Line 943: ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||

939: ' order by mtln.organization_id , mtln.inventory_item_id , mtln.transaction_id ';
940:
941: p_sql(25):= ' select mtli.* ' ||
942: ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti , ' ||
943: ' oe_order_lines_all sol , oe_order_headers_all soh , rcv_transactions rt, rcv_shipment_headers rsh ' ||
944: ' where sol.header_id = soh.header_id ' ||
945: ' and sol.line_category_code = ''RETURN'' ' ||
946: ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
947: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 956: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||

952: ' and mti.rcv_transaction_id = rt.transaction_id order by mtli.transaction_interface_id ';
953:
954:
955: p_sql(26) := ' select mtlt.* ' ||
956: ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt , oe_order_lines_all sol , ' ||
957: ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||
958: ' where sol.header_id = soh.header_id ' ||
959: ' and sol.line_category_code = ''RETURN'' ' ||
960: ' and rt.oe_order_header_id = soh.header_id ' ||

Line 970: ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||

966: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
967:
968: p_sql(27) := ' select rlt.* ' ||
969: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , oe_order_headers_all soh , ' ||
970: ' oe_order_lines_all sol , rcv_shipment_headers rsh' ||
971: ' where rsl.oe_order_header_id = soh.header_id ' ||
972: ' and rsl.oe_order_line_id = sol.line_id ' ||
973: ' and sol.header_id = soh.header_id ' ||
974: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||