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_RCV_DIAGNOSTICS dependencies on RCV_SHIPMENT_HEADERS

Line 8: l_operating_id rcv_shipment_headers.receipt_num%TYPE := p_operating_id;

4: PROCEDURE rma_line_receipt_sql(p_operating_id IN NUMBER,p_rma_number IN VARCHAR2,p_line_num IN NUMBER,
5: p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
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;

Line 9: l_rma_number rcv_shipment_headers.organization_id%TYPE := p_rma_number;

5: p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
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:

Line 11: l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;

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
15:

Line 12: l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;

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
15:
16: p_sql(1) := 'select soh.* ' ||

Line 19: ' rcv_shipment_headers rsh,' ||

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 ||
23: ' and soh.header_id = sol.header_id' ||

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 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 168: ' , rcv_shipment_headers rsh' ||

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 ||
171: ' and soh.org_id = '||l_operating_id ||
172: ' and rsl.oe_order_header_id = soh.header_id ' ||

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 201: rcv_shipment_headers rsh' ||

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 ||
205: ' and sol.header_id = soh.header_id ' ||

Line 219: ' rcv_transactions rt, rcv_shipment_headers rsh ' ||

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 ||
222: ' and soh.org_id = '||l_operating_id ||
223: ' and sol.header_id = soh.header_id ' ||

Line 237: rcv_shipment_headers rsh ' ||

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 ' ||
241: ' and rt.oe_order_line_id = sol.line_id ' ||

Line 253: rcv_shipment_headers rsh ' ||

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 ' ||
257: ' and rt.oe_order_line_id = sol.line_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 291: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

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 ||
295: ' and sol.line_category_code = ''RETURN'' ' ||

Line 307: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

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 ||
311: ' and sol.header_id = soh.header_id ' ||

Line 343: ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||

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 ||
347: ' and sol.header_id = soh.header_id ' ||

Line 366: ' rcv_shipment_headers rsh ' ||

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 ||
369: ' and soh.org_id = '||l_operating_id ||
370: ' and sol.header_id = soh.header_id ' ||

Line 388: ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||

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 ||
392: ' and sol.header_id = soh.header_id ' ||

Line 411: ' rcv_shipment_headers rsh' ||

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 ||
415: ' and rsl.oe_order_header_id = soh.header_id ' ||

Line 427: ' rcv_shipment_headers rsh' ||

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 ||
431: ' and rsl.oe_order_header_id = soh.header_id ' ||

Line 453: ' rcv_shipment_headers rsh ' ||

449: ' and sol.line_category_code = ''RETURN'' ' ||
450: ' and (exists (' ||
451: ' select 1 ' ||
452: ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln, ' ||
453: ' rcv_shipment_headers rsh ' ||
454: ' where rt.oe_order_header_id = soh.header_id ' ||
455: ' and rt.oe_order_line_id = sol.line_id ' ||
456: ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
457: ' and mmt.transaction_id = mtln.transaction_id ' ||

Line 494: ' from rcv_transactions rt, rcv_shipment_headers rsh ' ||

490: ' and sol.line_category_code = ''RETURN'' ' ||
491: ' and mmt.transaction_id = mtln.transaction_id ' ||
492: ' and (exists (' ||
493: ' select 1 ' ||
494: ' from rcv_transactions rt, rcv_shipment_headers rsh ' ||
495: ' where rt.oe_order_line_id = sol.line_id ' ||
496: ' and mmt.rcv_transaction_id = rt.transaction_id' ||
497: ' and rsh.shipment_header_id = rt.shipment_header_id' ||
498: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||

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 527: ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||

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 ||
531: ' and sol.header_id = soh.header_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 558: ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh ' ||

554: ' and rlt.shipment_line_id = rsl.shipment_line_id ' ||
555: ' order by rlt.shipment_line_id , rlt.lot_num ';
556:
557: p_sql(28) := ' SELECT DISTINCT rp.* ' ||
558: ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh ' ||
559: ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
560: ' and rsh.organization_id = '|| l_organization_id ||
561: ' AND rsh.organization_id = rp.organization_id ' ;
562:

Line 584: l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;

580:
581: PROCEDURE receipt_sql(p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
582: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
583:
584: l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
585: l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
586:
587: BEGIN
588:

Line 585: l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;

581: PROCEDURE receipt_sql(p_receipt_number IN NUMBER, p_org_id IN NUMBER, p_sql IN OUT NOCOPY
582: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
583:
584: l_receipt_number rcv_shipment_headers.receipt_num%TYPE := p_receipt_number;
585: l_organization_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
586:
587: BEGIN
588:
589: p_sql(1) := 'select distinct soh.* ' ||

Line 592: ' rcv_shipment_headers rsh,' ||

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' ||
596: ' and rsl.oe_order_line_id = sol.line_id' ||

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 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 712: rcv_shipment_headers rsh' ||

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 ' ||
716: ' and rt.oe_order_line_id = sol.line_id' ||

Line 727: ' rcv_transactions rt, rcv_shipment_headers rsh ' ||

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'' ' ||
730: ' and rt.oe_order_header_id = soh.header_id ' ||
731: ' and rt.oe_order_line_id = sol.line_id ' ||

Line 742: rcv_shipment_headers rsh ' ||

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 ' ||
746: ' and sol.line_category_code = ''RETURN'' ' ||

Line 755: rcv_shipment_headers rsh ' ||

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 ' ||
759: ' and sol.line_category_code = ''RETURN'' ' ||

Line 768: 'from mtl_material_transactions_temp mmtt , rcv_shipment_headers rsh, rcv_transactions rt'||

764: ' and nvl(mtrl.revision,0)=nvl(sol.item_revision,0) ' ||' and mtrl.line_status=7'||
765: ' and mtrl.transaction_type_id=15';
766:
767: p_sql(15) := 'select mmtt.* '||
768: 'from mtl_material_transactions_temp mmtt , rcv_shipment_headers rsh, rcv_transactions rt'||
769: ' WHERE mmtt.rcv_transaction_id = rt.transaction_id ' ||
770: ' and rt.shipment_header_id = rsh.shipment_header_id '||
771: ' AND rt.organization_id = rsh.organization_id '||
772: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||

Line 778: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

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 ' ||
782: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 791: ' rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

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' ||
795: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 824: ' oe_order_headers_all soh , rcv_transactions rt , rcv_shipment_headers rsh' ||

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 ' ||
828: ' and rt.oe_order_line_id = sol.line_id ' ||

Line 844: ' rcv_shipment_headers rsh ' ||

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'' ' ||
847: ' and rt.oe_order_header_id = soh.header_id ' ||
848: ' and rt.oe_order_line_id = sol.line_id ' ||

Line 863: ' rcv_transactions rt , mtl_unit_transactions mut ,rcv_shipment_headers rsh' ||

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 ' ||
867: ' and rt.oe_order_line_id = sol.line_id ' ||

Line 883: ' rcv_shipment_headers rsh' ||

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 ' ||
887: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||

Line 896: ' rcv_shipment_headers rsh' ||

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 ' ||
900: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||

Line 916: rcv_shipment_headers rsh ' ||

912: ' and sol.line_category_code = ''RETURN'' ' ||
913: ' and (exists (' ||
914: ' select 1 ' ||
915: ' from mtl_material_transactions mmt , rcv_transactions rt , mtl_transaction_lot_numbers mtln,
916: rcv_shipment_headers rsh ' ||
917: ' where rt.oe_order_header_id = soh.header_id ' ||
918: ' and rt.oe_order_line_id = sol.line_id ' ||
919: ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
920: ' and mmt.transaction_id = mtln.transaction_id ' ||

Line 933: rcv_shipment_headers rsh' ||

929:
930:
931: p_sql(24) := ' select mtln.* ' ||
932: ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt, rcv_transactions rt,
933: rcv_shipment_headers rsh' ||
934: ' where mmt.transaction_id = mtln.transaction_id ' ||
935: ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
936: ' AND rt.shipment_header_id = rsh.shipment_header_id' ||
937: ' and rsh.receipt_num = '||''''||l_receipt_number||'''' ||

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 957: ' oe_order_headers_all soh , rcv_transactions rt,rcv_shipment_headers rsh ' ||

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 ' ||
961: ' and rt.oe_order_line_id = sol.line_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||'''' ||

Line 981: ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh ' ||

977: ' and sol.line_category_code = ''RETURN'' ' ||
978: ' and rlt.shipment_line_id = rsl.shipment_line_id order by rlt.shipment_line_id , rlt.lot_num ';
979:
980: p_sql(28) := ' SELECT DISTINCT rp.* ' ||
981: ' FROM rcv_parameters rp, rcv_shipment_headers rsh, oe_order_headers_all soh ' ||
982: ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
983: ' and rsh.organization_id = '|| l_organization_id ||
984: ' AND rsh.organization_id = rp.organization_id ' ;
985:

Line 988: ' FROM po_system_parameters_all psp, oe_order_headers_all soh, rcv_shipment_headers rsh, '||

984: ' AND rsh.organization_id = rp.organization_id ' ;
985:
986:
987: p_sql(29) := ' SELECT DISTINCT psp.* ' ||
988: ' FROM po_system_parameters_all psp, oe_order_headers_all soh, rcv_shipment_headers rsh, '||
989: ' rcv_shipment_lines rsl ' ||
990: ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
991: ' and rsh.organization_id = '|| l_organization_id ||
992: ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||

Line 997: ' FROM financials_system_params_all fsp, oe_order_headers_all soh, rcv_shipment_headers rsh, '||

993: ' and rsl.oe_order_header_id = soh.header_id ' ||
994: ' and soh.org_id = psp.org_id ' ;
995:
996: p_sql(30) := ' SELECT DISTINCT fsp.* ' ||
997: ' FROM financials_system_params_all fsp, oe_order_headers_all soh, rcv_shipment_headers rsh, '||
998: ' rcv_shipment_lines rsl ' ||
999: ' where rsh.receipt_num = '||''''||l_receipt_number||'''' ||
1000: ' and rsh.organization_id = '|| l_organization_id ||
1001: ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||