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.IOT_DIAGNOSTICS dependencies on RCV_SHIPMENT_HEADERS

Line 6: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;

2: /* $Header: INVDIOT1B.pls 120.0.12000000.1 2007/08/09 06:49:17 ssadasiv noship $ */
3:
4: PROCEDURE shipment_num_sql(p_org_id IN NUMBER, p_shipment_num IN VARCHAR2, p_receipt_num IN VARCHAR2, p_sql IN OUT
5: NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
6: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
7: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
8: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
9: l_line_num rcv_shipment_lines.line_num%TYPE := NULL;
10:

Line 7: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;

3:
4: PROCEDURE shipment_num_sql(p_org_id IN NUMBER, p_shipment_num IN VARCHAR2, p_receipt_num IN VARCHAR2, p_sql IN OUT
5: NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
6: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
7: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
8: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
9: l_line_num rcv_shipment_lines.line_num%TYPE := NULL;
10:
11: BEGIN

Line 8: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;

4: PROCEDURE shipment_num_sql(p_org_id IN NUMBER, p_shipment_num IN VARCHAR2, p_receipt_num IN VARCHAR2, p_sql IN OUT
5: NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
6: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
7: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
8: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
9: l_line_num rcv_shipment_lines.line_num%TYPE := NULL;
10:
11: BEGIN
12:

Line 18: ' from rcv_shipment_headers rsh ' ||

14: ' from rcv_headers_interface rhi ' ||
15: ' where rhi.shipment_num = ' || '''' || l_shipment_num || '''' ||
16: ' or exists (' ||
17: ' select 1 ' ||
18: ' from rcv_shipment_headers rsh ' ||
19: ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
20: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
21: ' and rhi.receipt_header_id = rsh.shipment_header_id) ';
22:

Line 33: ' from rcv_shipment_headers rsh ' ||

29: ' where rti.header_interface_id = rhi.header_interface_id ' ||
30: ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
31: ' or exists (' ||
32: ' select 1 ' ||
33: ' from rcv_shipment_headers rsh ' ||
34: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
35: ' and rsh.ship_to_org_id = ' || l_org_id ||
36: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
37: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||

Line 40: ' from rcv_shipment_headers rsh ' ||

36: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
37: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
38: ' or exists (' ||
39: ' select 2 ' ||
40: ' from rcv_shipment_headers rsh ' ||
41: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
42: ' and rsh.ship_to_org_id = ' || l_org_id ||
43: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
44: ' and (rti.shipment_header_id = rsh.shipment_header_id ' ||

Line 57: ' from rcv_shipment_headers rsh ' ||

53: ' from rcv_headers_interface rhi ' ||
54: ' where (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
55: ' or exists (' ||
56: ' select 1 ' ||
57: ' from rcv_shipment_headers rsh ' ||
58: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
59: ' and rsh.ship_to_org_id = ' || l_org_id ||
60: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ' ||
61: ' and ((pie.interface_header_id = rhi.header_interface_id ' ||

Line 71: ' from rcv_transactions_interface rti , rcv_shipment_headers rsh ' ||

67: ' and pie.interface_line_id = rti.interface_transaction_id ' ||
68: ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'' ) ) ) ' ||
69: ' or exists (' ||
70: ' select 2 ' ||
71: ' from rcv_transactions_interface rti , rcv_shipment_headers rsh ' ||
72: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
73: ' and rsh.ship_to_org_id = ' || l_org_id ||
74: ' and rti.shipment_header_id = rsh.shipment_header_id ' ||
75: ' and pie.interface_line_id = rti.interface_transaction_id ' ||

Line 81: ' from rcv_shipment_headers rsh' ||

77:
78:
79:
80: p_sql(4):= ' select distinct rsh.* ' ||
81: ' from rcv_shipment_headers rsh' ||
82: ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
83: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
84: ' and rsh.ship_to_org_id = ' || l_org_id ||
85: ' order by rsh.shipment_header_id ';

Line 90: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl ' ||

86:
87:
88:
89: p_sql(5):= ' select distinct rsl.* ' ||
90: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl ' ||
91: ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
92: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
93: ' and rsh.ship_to_org_id = ' || l_org_id ||
94: ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||

Line 101: ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

97:
98:
99:
100: p_sql(6):= ' select distinct rt.* ' ||
101: ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
102: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
103: ' and rsh.ship_to_org_id = ' || l_org_id ||
104: ' and rt.shipment_header_id = rsh.shipment_header_id' ||
105: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 111: ' from mtl_supply ms , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

107:
108:
109:
110: p_sql(7):= ' select distinct ms.* ' ||
111: ' from mtl_supply ms , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
112: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
113: ' and rsh.ship_to_org_id = ' || l_org_id ||
114: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
115: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 122: ' from rcv_supply rs , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

118:
119:
120:
121: p_sql(8):= ' select distinct rs.* ' ||
122: ' from rcv_supply rs , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
123: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
124: ' and rsh.ship_to_org_id = ' || l_org_id ||
125: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
126: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 140: ' from rcv_shipment_headers rsh' ||

136:
137: p_sql(10):= ' select distinct mmt.*' ||
138: ' from mtl_material_transactions mmt' ||
139: ' where exists (select 1' ||
140: ' from rcv_shipment_headers rsh' ||
141: ' where mmt.shipment_number = rsh.shipment_num' ||
142: ' and rsh.shipment_num= ' || '''' || l_shipment_num || '''' ||
143: ' and rsh.ship_to_org_id = ' || l_org_id ||
144: ' )' ||

Line 147: ' rcv_shipment_headers rsh' ||

143: ' and rsh.ship_to_org_id = ' || l_org_id ||
144: ' )' ||
145: ' or exists (select 2 ' ||
146: ' from rcv_transactions rt, ' ||
147: ' rcv_shipment_headers rsh' ||
148: ' where rt.transaction_id = mmt.rcv_transaction_id' ||
149: ' and rt.shipment_header_id = rsh.shipment_header_id' ||
150: ' and rsh.shipment_num= ' || '''' || l_shipment_num || '''' ||
151: ' and rsh.ship_to_org_id = ' || l_org_id ||') ';

Line 184: ' from rcv_shipment_headers rsh ' ||

180: ' where rti.header_interface_id = rhi.header_interface_id ' ||
181: ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
182: ' or exists (' ||
183: ' select 1 ' ||
184: ' from rcv_shipment_headers rsh ' ||
185: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
186: ' and rsh.ship_to_org_id = ' || l_org_id ||
187: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
188: ' or exists (' ||

Line 190: ' from rcv_shipment_headers rsh ' ||

186: ' and rsh.ship_to_org_id = ' || l_org_id ||
187: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
188: ' or exists (' ||
189: ' select 2 ' ||
190: ' from rcv_shipment_headers rsh ' ||
191: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
192: ' and rsh.ship_to_org_id = ' || l_org_id ||
193: ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
194: ' and mtli.product_transaction_id = rti.interface_transaction_id ';

Line 206: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

202:
203:
204:
205: p_sql(15):= ' select distinct rls.* ' ||
206: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
207: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
208: ' and rsh.ship_to_org_id = ' || l_org_id ||
209: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
210: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 218: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

214:
215:
216:
217: p_sql(16):= ' select distinct rlt.* ' ||
218: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
219: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
220: ' and rsh.ship_to_org_id = ' || l_org_id ||
221: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
222: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 229: ' from rcv_lots_interface rli , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

225:
226:
227:
228: p_sql(17):= ' select distinct rli.* ' ||
229: ' from rcv_lots_interface rli , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
230: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
231: ' and rsh.ship_to_org_id = ' || l_org_id ||
232: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
233: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 241: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rt ' ||

237: ' order by rli.item_id , rli.lot_num ';
238:
239:
240: p_sql(18):=' select distinct rss.* ' ||
241: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rt ' ||
242: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
243: ' and rsh.ship_to_org_id = ' || l_org_id ||
244: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
245: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 254: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

250:
251:
252:
253: p_sql(19):= ' select distinct rst.* ' ||
254: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
255: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
256: ' and rsh.ship_to_org_id = ' || l_org_id ||
257: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
258: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 266: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

262:
263:
264:
265: p_sql(20):= ' select distinct rsi.* ' ||
266: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
267: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
268: ' and rsh.ship_to_org_id = ' || l_org_id ||
269: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
270: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 279: ' from mtl_serial_numbers msn , mtl_material_transactions mmt , rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

275:
276:
277:
278: p_sql(21):= ' select distinct msn.* ' ||
279: ' from mtl_serial_numbers msn , mtl_material_transactions mmt , rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
280: ' where msn.last_transaction_id = mmt.transaction_id ' ||
281: ' and mmt.shipment_number = rsh.shipment_num' ||
282: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
283: ' and rsh.ship_to_org_id = ' || l_org_id ||

Line 340: ' from rcv_shipment_headers rsh ' ||

336: ' where rti.header_interface_id = rhi.header_interface_id ' ||
337: ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
338: ' or exists (' ||
339: ' select 1 ' ||
340: ' from rcv_shipment_headers rsh ' ||
341: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
342: ' and rsh.ship_to_org_id = ' || l_org_id ||
343: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
344: ' or exists (' ||

Line 346: ' from rcv_shipment_headers rsh ' ||

342: ' and rsh.ship_to_org_id = ' || l_org_id ||
343: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
344: ' or exists (' ||
345: ' select 2 ' ||
346: ' from rcv_shipment_headers rsh ' ||
347: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
348: ' and rsh.ship_to_org_id = ' || l_org_id ||
349: ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
350: ' and msni.product_transaction_id = rti.interface_transaction_id ';

Line 355: ' from mtl_unit_transactions mut , mtl_material_transactions mmt , rcv_transactions rt, rcv_shipment_headers rsh,

351:
352:
353:
354: p_sql(24):= ' select distinct mut.* ' ||
355: ' from mtl_unit_transactions mut , mtl_material_transactions mmt , rcv_transactions rt, rcv_shipment_headers rsh,
356: rcv_shipment_lines rsl' ||
357: ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
358: ' and rsh.ship_to_org_id = ' || l_org_id ||
359: ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||

Line 398: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

394: ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
395: ' and (ood.organization_id = mmt.organization_id or ood.organization_id = mmt.transfer_organization_id )
396: )' ||
397: ' OR exists (SELECT 1 ' ||
398: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
399: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
400: ' and rsh.ship_to_org_id = ' || l_org_id ||
401: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
402: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 485: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;

481: p_shipment_line_num IN NUMBER, p_receipt_num IN VARCHAR2, p_sql IN OUT NOCOPY
482: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
483:
484: l_line_num rcv_shipment_lines.line_num%TYPE := p_shipment_line_num;
485: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
486: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
487: l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;
488:
489:

Line 486: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;

482: INV_DIAG_RCV_PO_COMMON.sqls_list) IS
483:
484: l_line_num rcv_shipment_lines.line_num%TYPE := p_shipment_line_num;
485: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
486: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
487: l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;
488:
489:
490: BEGIN

Line 487: l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;

483:
484: l_line_num rcv_shipment_lines.line_num%TYPE := p_shipment_line_num;
485: l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
486: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
487: l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;
488:
489:
490: BEGIN
491:

Line 498: ' from rcv_shipment_headers rsh ' ||

494: ' from rcv_headers_interface rhi ' ||
495: ' where rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
496: ' or exists (' ||
497: ' select 1 ' ||
498: ' from rcv_shipment_headers rsh ' ||
499: ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
500: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
501: ' and rhi.receipt_header_id = rsh.shipment_header_id) ';
502:

Line 513: ' from rcv_shipment_headers rsh ' ||

509: ' where rti.header_interface_id = rhi.header_interface_id ' ||
510: ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
511: ' or exists (' ||
512: ' select 1 ' ||
513: ' from rcv_shipment_headers rsh ' ||
514: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
515: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
516: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
517: ' or exists (' ||

Line 519: ' from rcv_shipment_headers rsh ' ||

515: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
516: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
517: ' or exists (' ||
518: ' select 2 ' ||
519: ' from rcv_shipment_headers rsh ' ||
520: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
521: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
522: ' and (rti.shipment_header_id = rsh.shipment_header_id ' ||
523: ' OR rti.shipment_num = nvl(' || '''' || l_shipment_num || '''' || ',rsh.shipment_num) ) ) ) ';

Line 535: ' from rcv_shipment_headers rsh ' ||

531: ' from rcv_headers_interface rhi ' ||
532: ' where (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
533: ' or exists (' ||
534: ' select 1 ' ||
535: ' from rcv_shipment_headers rsh ' ||
536: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
537: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ' ||
538: ' and ((pie.interface_header_id = rhi.header_interface_id ' ||
539: ' and pie.table_name = ''RCV_HEADERS_INTERFACE'') ' ||

Line 548: ' from rcv_transactions_interface rti , rcv_shipment_headers rsh ' ||

544: ' and pie.interface_line_id = rti.interface_transaction_id ' ||
545: ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'' ) ) ) ' ||
546: ' or exists (' ||
547: ' select 2 ' ||
548: ' from rcv_transactions_interface rti , rcv_shipment_headers rsh ' ||
549: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
550: ' and rti.shipment_header_id = rsh.shipment_header_id ' ||
551: ' and pie.interface_line_id = rti.interface_transaction_id ' ||
552: ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ';

Line 557: ' from rcv_shipment_headers rsh' ||

553:
554:
555:
556: p_sql(4):= ' select distinct rsh.* ' ||
557: ' from rcv_shipment_headers rsh' ||
558: ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
559: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
560: ' order by rsh.shipment_header_id ';
561:

Line 565: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl ' ||

561:
562:
563:
564: p_sql(5):= ' select distinct rsl.* ' ||
565: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl ' ||
566: ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
567: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
568: ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
569: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 575: ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

571:
572:
573:
574: p_sql(6):= ' select distinct rt.* ' ||
575: ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
576: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
577: ' and rt.shipment_header_id = rsh.shipment_header_id' ||
578: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
579: ' order by rt.shipment_header_id , rt.shipment_line_id , rt.transaction_id ';

Line 584: ' from mtl_supply ms , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

580:
581:
582:
583: p_sql(7):= ' select distinct ms.* ' ||
584: ' from mtl_supply ms , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
585: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
586: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
587: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
588: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 594: ' from rcv_supply rs , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

590:
591:
592:
593: p_sql(8):= ' select distinct rs.* ' ||
594: ' from rcv_supply rs , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
595: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
596: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
597: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
598: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 611: ' from rcv_shipment_headers rsh, ' ||

607:
608: p_sql(10):= ' select distinct mmt.*' ||
609: ' from mtl_material_transactions mmt' ||
610: ' where exists (select 1' ||
611: ' from rcv_shipment_headers rsh, ' ||
612: ' rcv_shipment_lines rsl' ||
613: ' where mmt.shipment_number = rsh.shipment_num' ||
614: ' and rsh.shipment_num= ' || '''' || l_shipment_num || '''' ||
615: ' and rsh.ship_to_org_id = '|| l_org_id ||

Line 620: ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

616: ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
617: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
618: ' )' ||
619: ' or exists (select 2 ' ||
620: ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
621: ' where rt.transaction_id = mmt.rcv_transaction_id' ||
622: ' and rt.shipment_header_id = rsh.shipment_header_id' ||
623: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
624: ' and rsh.ship_to_org_id = ' || l_org_id ||

Line 659: ' from rcv_shipment_headers rsh ' ||

655: ' where rti.header_interface_id = rhi.header_interface_id ' ||
656: ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
657: ' or exists (' ||
658: ' select 1 ' ||
659: ' from rcv_shipment_headers rsh ' ||
660: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
661: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
662: ' or exists (' ||
663: ' select 2 ' ||

Line 664: ' from rcv_shipment_headers rsh ' ||

660: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
661: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
662: ' or exists (' ||
663: ' select 2 ' ||
664: ' from rcv_shipment_headers rsh ' ||
665: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
666: ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
667: ' and mtli.product_transaction_id = rti.interface_transaction_id ';
668:

Line 678: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

674: ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
675:
676:
677: p_sql(15):= ' select distinct rls.* ' ||
678: ' from rcv_lots_supply rls , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
679: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
680: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
681: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
682: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 689: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

685:
686:
687:
688: p_sql(16):= ' select distinct rlt.* ' ||
689: ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
690: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
691: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
692: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
693: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 699: ' from rcv_lots_interface rli , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

695:
696:
697:
698: p_sql(17):= ' select distinct rli.* ' ||
699: ' from rcv_lots_interface rli , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
700: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
701: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
702: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
703: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 710: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rt ' ||

706: ' order by rli.item_id , rli.lot_num ';
707:
708:
709: p_sql(18):= ' select distinct rss.* ' ||
710: ' from rcv_serials_supply rss , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rt ' ||
711: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
712: ' and rsh.ship_to_org_id = ' || l_org_id ||
713: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
714: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||

Line 723: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

719:
720:
721:
722: p_sql(19):= ' select distinct rst.* ' ||
723: ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
724: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
725: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
726: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
727: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 734: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||

730:
731:
732:
733: p_sql(20):= ' select distinct rsi.* ' ||
734: ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
735: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
736: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
737: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
738: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 745: ' from mtl_serial_numbers msn , mtl_material_transactions mmt , rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

741: ' order by rsi.organization_id , rsi.item_id , rsi.fm_serial_num ';
742:
743:
744: p_sql(21):= ' select distinct msn.* ' ||
745: ' from mtl_serial_numbers msn , mtl_material_transactions mmt , rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
746: ' where msn.last_transaction_id = mmt.transaction_id ' ||
747: ' and mmt.shipment_number = rsh.shipment_num' ||
748: ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
749: ' and rsh.receipt_source_code = ''INVENTORY'' ' ||

Line 804: ' from rcv_shipment_headers rsh ' ||

800: ' where rti.header_interface_id = rhi.header_interface_id ' ||
801: ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
802: ' or exists (' ||
803: ' select 1 ' ||
804: ' from rcv_shipment_headers rsh ' ||
805: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
806: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
807: ' or exists (' ||
808: ' select 2 ' ||

Line 809: ' from rcv_shipment_headers rsh ' ||

805: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
806: ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
807: ' or exists (' ||
808: ' select 2 ' ||
809: ' from rcv_shipment_headers rsh ' ||
810: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
811: ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
812: ' and msni.product_transaction_id = rti.interface_transaction_id ';
813:

Line 817: ' from mtl_unit_transactions mut , mtl_material_transactions mmt , rcv_transactions rt, rcv_shipment_headers rsh,

813:
814:
815:
816: p_sql(24):= ' select distinct mut.* ' ||
817: ' from mtl_unit_transactions mut , mtl_material_transactions mmt , rcv_transactions rt, rcv_shipment_headers rsh,
818: rcv_shipment_lines rsl' ||
819: ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
820: ' and rsh.ship_to_org_id = ' || l_org_id ||
821: ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||

Line 836: ' from mtl_material_transactions mmt , mtl_system_items msi , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

832:
833:
834:
835: p_sql(25):= ' select distinct msi.* ' ||
836: ' from mtl_material_transactions mmt , mtl_system_items msi , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
837: ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
838: ' and mmt.shipment_number = rsh.shipment_num' ||
839: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
840: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||

Line 856: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||

852: ' from mtl_transaction_types mtt , mtl_material_transactions mmt ' ||
853: ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
854: ' and mtt.transaction_type_id = mmt.transaction_type_id' ||
855: ' and exists ( SELECT 1' ||
856: ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
857: ' where mmt.shipment_number = rsh.shipment_num' ||
858: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
859: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num))' ||
860: ' order by mtt.transaction_type_id ';

Line 871: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||

867: ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
868: ' and (ood.organization_id = mmt.organization_id or ood.organization_id = mmt.transfer_organization_id )
869: )' ||
870: ' OR exists (SELECT 1 ' ||
871: ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
872: ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
873: ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
874: ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
875: ' and (rsh.organization_id = ood.organization_id or rsh.ship_to_org_id = ood.organization_id)' ||