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

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

7:
8: PROCEDURE build_rcv_sql(p_org_id IN NUMBER,p_receipt_num IN VARCHAR2,p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
9:
10: -- Initialize Local Variables.
11: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
12: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
13:
14: BEGIN
15:

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

8: PROCEDURE build_rcv_sql(p_org_id IN NUMBER,p_receipt_num IN VARCHAR2,p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
9:
10: -- Initialize Local Variables.
11: l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
12: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
13:
14: BEGIN
15:
16: p_sql(1) := ' SELECT distinct ph.* ' ||' FROM po_headers_all ph,rcv_shipment_lines rsl,rcv_shipment_headers rsh ' ||

Line 16: p_sql(1) := ' SELECT distinct ph.* ' ||' FROM po_headers_all ph,rcv_shipment_lines rsl,rcv_shipment_headers rsh ' ||

12: l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
13:
14: BEGIN
15:
16: p_sql(1) := ' SELECT distinct ph.* ' ||' FROM po_headers_all ph,rcv_shipment_lines rsl,rcv_shipment_headers rsh ' ||
17: ' WHERE rsh.shipment_header_id=rsl.shipment_header_id' ||
18: ' and rsl.po_header_id=ph.po_header_id' ||
19: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
20: ' AND rsh.ship_to_org_id ='||l_org_id;

Line 23: ' rcv_shipment_headers rsh ' ||' WHERE pl.po_line_id=rsl.po_line_id' ||

19: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
20: ' AND rsh.ship_to_org_id ='||l_org_id;
21:
22: p_sql(2) := ' SELECT distinct pl.* ' ||' FROM po_lines_all pl,rcv_shipment_lines rsl, ' ||
23: ' rcv_shipment_headers rsh ' ||' WHERE pl.po_line_id=rsl.po_line_id' ||
24: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||' AND rsh.receipt_num='||''''||l_receipt_num||'''' ||
25: ' AND rsh.ship_to_org_id ='||l_org_id;
26:
27: p_sql(3) := ' SELECT distinct pll.* ' ||' FROM po_line_locations_all pll , ' ||

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

24: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||' AND rsh.receipt_num='||''''||l_receipt_num||'''' ||
25: ' AND rsh.ship_to_org_id ='||l_org_id;
26:
27: p_sql(3) := ' SELECT distinct pll.* ' ||' FROM po_line_locations_all pll , ' ||
28: ' rcv_shipment_lines rsl, ' ||' rcv_shipment_headers rsh' ||
29: ' WHERE rsl.po_line_location_id= pll.line_location_id' ||
30: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
31: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
32: ' AND rsh.ship_to_org_id ='||l_org_id;

Line 36: ' rcv_shipment_headers rsh ' ||' WHERE pll.line_location_id = pd.line_location_id' ||

32: ' AND rsh.ship_to_org_id ='||l_org_id;
33:
34: p_sql(4) := ' SELECT distinct pd.* ' ||' FROM po_line_locations_all pll , ' ||
35: ' po_distributions_all pd,' ||' rcv_shipment_lines rsl, ' ||
36: ' rcv_shipment_headers rsh ' ||' WHERE pll.line_location_id = pd.line_location_id' ||
37: ' and rsl.po_line_location_id=pll.line_location_id' ||
38: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
39: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||' AND rsh.ship_to_org_id ='||l_org_id;
40:

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

39: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||' AND rsh.ship_to_org_id ='||l_org_id;
40:
41: p_sql(5) := ' SELECT distinct gcc.* ' ||' FROM gl_code_combinations gcc , ' ||
42: ' po_line_locations_all pll , ' ||' po_distributions_all pd ,' ||
43: ' rcv_shipment_lines rsl, ' ||' rcv_shipment_headers rsh' ||
44: ' WHERE gcc.summary_flag = ''N'' ' ||' AND gcc.template_id is null ' ||
45: ' AND pll.line_location_id = pd.line_location_id' ||
46: ' AND pll.line_location_id = rsl.po_line_location_id ' ||
47: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||

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

50: ' and gcc.code_combination_id in (pd.accrual_account_id '||
51: ', pd.budget_account_id , pd.VARIANCE_ACCOUNT_ID , pd.code_combination_id) ';
52:
53: p_sql(6) := ' SELECT distinct rrsl.* ' ||' FROM rcv_receiving_sub_ledger rrsl , ' ||
54: ' rcv_transactions rt , ' ||' rcv_shipment_headers rsh ' ||
55: ' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||' AND rsh.ship_to_org_id ='||l_org_id ||
56: ' AND rt.shipment_header_id = rsh.shipment_header_id ' ||
57: ' AND rrsl.rcv_transaction_id = rt.transaction_id ';
58:

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

57: ' AND rrsl.rcv_transaction_id = rt.transaction_id ';
58:
59: /*p_sql(7) := ' SELECT distinct id.* ' ||' FROM ap_invoice_distributions_all id , ' ||
60: ' po_line_locations_all pll , ' ||' po_distributions_all pd ,' ||
61: ' rcv_shipment_lines rsl,rcv_shipment_headers rsh ' ||
62: ' WHERE pll.line_location_id = pd.line_location_id' ||
63: ' and pll.line_location_id = rsl.po_line_location_id' ||
64: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
65: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||

Line 69: ' po_line_locations_all pll , rcv_shipment_headers rsh,' ||

65: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
66: ' AND rsh.ship_to_org_id ='||l_org_id ||
67: ' AND id.po_distribution_id = pd.po_distribution_id ';*/
68: p_sql(7) := ' SELECT distinct id.* ' ||' FROM ap_invoice_lines_all id , ' ||
69: ' po_line_locations_all pll , rcv_shipment_headers rsh,' ||
70: ' rcv_transactions rt'||
71: ' WHERE pll.line_location_id = rt.po_line_location_id' ||
72: ' and rsh.shipment_header_id=rt.shipment_header_id ' ||
73: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||

Line 80: ' rcv_shipment_headers rsh' ||' WHERE pll.line_location_id = pd.line_location_id' ||

76:
77: p_sql(8) := ' SELECT distinct ai.* ' ||' FROM ap_invoices_all ai , ' ||
78: ' ap_invoice_distributions_all id , ' ||' po_line_locations_all pll , ' ||
79: ' po_distributions_all pd ,' ||' rcv_shipment_lines rsl, ' ||
80: ' rcv_shipment_headers rsh' ||' WHERE pll.line_location_id = pd.line_location_id' ||
81: ' and pll.line_location_id = rsl.po_line_location_id' ||
82: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
83: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
84: ' AND rsh.ship_to_org_id ='||l_org_id ||

Line 90: ' rcv_shipment_headers rsh ' ||' WHERE ph.po_header_id = rsl.po_header_id' ||

86: ' AND ai.invoice_id = id.invoice_id ';
87:
88: p_sql(9) := ' SELECT distinct ili.* ' ||' FROM ap_invoice_lines_interface ili , ' ||
89: ' po_headers_all ph,' ||' rcv_shipment_lines rsl, ' ||
90: ' rcv_shipment_headers rsh ' ||' WHERE ph.po_header_id = rsl.po_header_id' ||
91: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
92: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
93: ' AND rsh.ship_to_org_id ='||l_org_id ||
94: ' AND ili.po_header_id = ph.po_header_id ';

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

94: ' AND ili.po_header_id = ph.po_header_id ';
95:
96: p_sql(10) := ' SELECT distinct ihi.* ' ||' FROM ap_invoices_interface ihi , ' ||
97: ' ap_invoice_lines_interface ili , ' ||' po_headers_all ph,' ||
98: ' rcv_shipment_lines rsl, ' ||' rcv_shipment_headers rsh ' ||
99: ' WHERE ph.po_header_id = rsl.po_header_id' ||
100: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
101: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
102: ' AND rsh.ship_to_org_id ='||l_org_id ||

Line 107: ' rcv_shipment_headers rsh ' ||' WHERE rsh.shipment_header_id =rsl.shipment_header_id ' ||

103: ' AND ili.po_header_id = ph.po_header_id ' ||
104: ' AND ihi.invoice_id = ili.invoice_id ';
105:
106: p_sql(11) := ' SELECT DISTINCT rsh.* ' ||' FROM rcv_shipment_lines rsl , ' ||
107: ' rcv_shipment_headers rsh ' ||' WHERE rsh.shipment_header_id =rsl.shipment_header_id ' ||
108: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
109: ' AND rsh.ship_to_org_id ='||l_org_id ||
110: ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
111: ' ORDER BY rsh.shipment_header_id ';

Line 114: ' rcv_shipment_headers rsh ' ||' WHERE rsh.shipment_header_id =rsl.shipment_header_id ' ||

110: ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
111: ' ORDER BY rsh.shipment_header_id ';
112:
113: p_sql(12) := ' SELECT DISTINCT rsl.* ' ||' FROM rcv_shipment_lines rsl , ' ||
114: ' rcv_shipment_headers rsh ' ||' WHERE rsh.shipment_header_id =rsl.shipment_header_id ' ||
115: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
116: ' AND rsh.ship_to_org_id ='||l_org_id ||
117: ' AND rsl.shipment_header_id = rsh.shipment_header_id ';
118:

Line 120: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||

116: ' AND rsh.ship_to_org_id ='||l_org_id ||
117: ' AND rsl.shipment_header_id = rsh.shipment_header_id ';
118:
119: p_sql(13) := ' SELECT distinct rt.* ' ||' FROM rcv_transactions rt , ' ||
120: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
121: ' AND rsh.ship_to_org_id ='||l_org_id ||
122: ' AND rt.shipment_header_id=rsh.shipment_header_id ';
123:
124: p_sql(14) := ' SELECT distinct ms.* ' ||' FROM mtl_supply ms , ' ||

Line 125: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||

121: ' AND rsh.ship_to_org_id ='||l_org_id ||
122: ' AND rt.shipment_header_id=rsh.shipment_header_id ';
123:
124: p_sql(14) := ' SELECT distinct ms.* ' ||' FROM mtl_supply ms , ' ||
125: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
126: ' AND rsh.ship_to_org_id ='||l_org_id ||' AND ms.shipment_header_id=rsh.shipment_header_id ';
127:
128: p_sql(15) := ' SELECT distinct rs.* ' ||' FROM rcv_supply rs , ' ||
129: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||

Line 129: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||

125: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
126: ' AND rsh.ship_to_org_id ='||l_org_id ||' AND ms.shipment_header_id=rsh.shipment_header_id ';
127:
128: p_sql(15) := ' SELECT distinct rs.* ' ||' FROM rcv_supply rs , ' ||
129: ' rcv_shipment_headers rsh ' ||' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
130: ' AND rsh.ship_to_org_id ='||l_org_id ||' AND rs.shipment_header_id=rsh.shipment_header_id ';
131:
132: p_sql(16) := ' SELECT distinct rhi.* ' ||' FROM rcv_headers_interface rhi ' ||
133: ' WHERE receipt_num= '||''''||l_receipt_num||'''' ||' OR exists ' ||

Line 136: ' rcv_shipment_headers rsh ' ||

132: p_sql(16) := ' SELECT distinct rhi.* ' ||' FROM rcv_headers_interface rhi ' ||
133: ' WHERE receipt_num= '||''''||l_receipt_num||'''' ||' OR exists ' ||
134: ' (SELECT 1'||
135: ' FROM rcv_shipment_lines rsl , ' ||
136: ' rcv_shipment_headers rsh ' ||
137: ' WHERE rsh.receipt_num = '||''''||l_receipt_num||'''' ||
138: ' AND rsh.ship_to_org_id ='||l_org_id ||
139: ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
140: ' AND rsh.shipment_header_id = rhi.receipt_header_id' ||

Line 145: ' rcv_shipment_headers rsh ' ||

141: ' ) ' ||
142: ' OR exists ' ||
143: ' (SELECT 2 ' ||
144: ' FROM rcv_transactions_interface rti , ' ||
145: ' rcv_shipment_headers rsh ' ||
146: ' WHERE rsh.shipment_header_id =rti.shipment_header_id ' ||
147: ' AND rsh.receipt_num = '||''''||l_receipt_num||'''' ||
148: ' AND rsh.ship_to_org_id ='||l_org_id ||
149: ' AND rhi.header_interface_id = rti.header_interface_id' ||

Line 154: ' FROM rcv_shipment_headers rsh ' ||

150: ' ) ';
151:
152: p_sql(17) := ' SELECT DISTINCT rti.* ' ||' FROM rcv_transactions_interface rti ' ||
153: ' WHERE exists ' ||' (SELECT 1'||
154: ' FROM rcv_shipment_headers rsh ' ||
155: ' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
156: ' AND rsh.ship_to_org_id ='||l_org_id ||
157: ' AND rti.shipment_header_id = rsh.shipment_header_id' ||
158: ' ) ';

Line 161: ' rcv_shipment_headers rsh'||' WHERE rsh.receipt_num='||''''||l_receipt_num||'''' ||

157: ' AND rti.shipment_header_id = rsh.shipment_header_id' ||
158: ' ) ';
159:
160: p_sql(18) := 'SELECT DISTINCT pie.* '||' FROM po_interface_errors pie , '||
161: ' rcv_shipment_headers rsh'||' WHERE rsh.receipt_num='||''''||l_receipt_num||'''' ||
162: ' AND rsh.ship_to_org_id='||l_org_id||' AND ( '||
163: ' EXISTS (SELECT 1'||' FROM rcv_transactions_interface rti'||
164: ' WHERE pie.interface_line_id = rti.interface_transaction_id'||
165: ' AND rsh.shipment_header_id=rti.shipment_header_id )'||

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

168: ' WHERE pie.interface_header_id = rhi.header_interface_id '||
169: ' AND rsh.shipment_header_id = rhi.header_interface_id))';
170:
171: p_sql(19) := ' SELECT DISTINCT msi.* ' ||' FROM mtl_system_items msi , ' ||
172: ' rcv_shipment_headers rsh,' ||' rcv_shipment_lines rsl ' ||
173: ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
174: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
175: ' AND rsh.ship_to_org_id ='||l_org_id||'and msi.inventory_item_id = rsl.item_id ' ||
176: ' AND msi.organization_id = rsl.to_organization_id ';

Line 179: ' FROM mtl_material_transactions mmt ,rcv_transactions rt,rcv_shipment_headers rsh ,' ||

175: ' AND rsh.ship_to_org_id ='||l_org_id||'and msi.inventory_item_id = rsl.item_id ' ||
176: ' AND msi.organization_id = rsl.to_organization_id ';
177:
178: p_sql(20) := ' SELECT distinct mmt.* ' ||
179: ' FROM mtl_material_transactions mmt ,rcv_transactions rt,rcv_shipment_headers rsh ,' ||
180: ' po_headers_all ph ' ||' WHERE mmt.transaction_source_id = ph.po_header_id ' ||
181: ' AND mmt.transaction_source_type_id = 1'||
182: ' and rsh.shipment_header_id=rt.shipment_header_id ' ||
183: ' and rt.transaction_id=mmt.rcv_transaction_id' ||

Line 197: ' rcv_shipment_headers rsh ' ||

193: ' WHERE exists ' ||
194: ' (SELECT 1'||
195: ' FROM mtl_material_transactions mmt , ' ||
196: ' rcv_transactions rt,' ||
197: ' rcv_shipment_headers rsh ' ||
198: ' WHERE mmt.rcv_transaction_id =rt.transaction_id ' ||
199: ' AND rt.shipment_header_id =rsh.shipment_header_id ' ||
200: ' AND mmt.transaction_source_type_id = 1'||
201: ' AND mtt.transaction_type_id = mmt.transaction_type_id ' ||

Line 214: ' rcv_shipment_headers rsh ' ||

210: ' AND mmtt.transaction_type_id = mtt.transaction_type_id ' ||
211: ' AND (ph.po_header_id in ' ||
212: ' (SELECT DISTINCT po_header_id ' ||
213: ' FROM rcv_shipment_lines rsl, ' ||
214: ' rcv_shipment_headers rsh ' ||
215: ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
216: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
217: ' AND rsh.ship_to_org_id ='||l_org_id ||' ))' ||' ) ';
218:

Line 222: ' rcv_shipment_headers rsh ' ||

218:
219: /*p_sql(22) := ' SELECT DISTINCT mol.* ' ||' FROM mtl_txn_request_lines mol , ' ||
220: ' rcv_transactions rt , ' ||
221: ' rcv_shipment_lines rsl , ' ||
222: ' rcv_shipment_headers rsh ' ||
223: ' WHERE mol.reference_id = decode(mol.reference ,''SHIPMENT_LINE_ID'' , rt.shipment_line_id ,''PO_LINE_LOCATION_ID'' , rt.po_line_location_id , ''ORDER_LINE_ID'' , rt.oe_order_line_id) ' ||
224: ' AND rt.shipment_line_id = rsl.shipment_line_id ' ||
225: ' AND mol.organization_id = rt.organization_id ' ||
226: ' AND mol.inventory_item_id = rsl.item_id ' ||

Line 232: ' rcv_shipment_headers rsh ' ||

228: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
229: ' AND rsh.ship_to_org_id ='||l_org_id;*/
230: p_sql(22) := ' SELECT DISTINCT mol.* ' ||' FROM mtl_txn_request_lines mol , ' ||
231: ' rcv_shipment_lines rsl , ' ||
232: ' rcv_shipment_headers rsh ' ||
233: ' WHERE mol.organization_id = rsl.to_organization_id ' ||
234: ' AND mol.inventory_item_id = rsl.item_id ' ||
235: ' and nvl(mol.revision,0)=nvl(rsl.item_revision,0) ' ||' and mol.line_status=7'||
236: ' and mol.transaction_type_id=18'||

Line 248: ' rcv_shipment_headers rsh ' ||

244: ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
245: ' AND (ph.po_header_id in ' ||
246: ' (SELECT DISTINCT po_header_id ' ||
247: ' FROM rcv_shipment_lines rsl, ' ||
248: ' rcv_shipment_headers rsh ' ||
249: ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
250: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
251: ' AND rsh.ship_to_org_id ='||l_org_id ||
252: ' )) ';

Line 260: ' rcv_shipment_headers rsh' ||

256: ' po_line_locations_all pll , ' ||
257: ' po_headers_all ph , ' ||
258: ' financials_system_params_all fsp,' ||
259: ' rcv_shipment_lines rsl, ' ||
260: ' rcv_shipment_headers rsh' ||
261: ' WHERE pll.po_header_id = ph.po_header_id ' ||
262: ' AND fsp.org_id = ph.org_id ' ||
263: ' AND ood.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id) ' ||
264: ' AND pll.line_location_id = rsl.po_line_location_id' ||

Line 275: ' rcv_shipment_headers rsh ' ||

271: ' po_line_locations_all pll , ' ||
272: ' po_headers_all ph , ' ||
273: ' financials_system_params_all fsp,' ||
274: ' rcv_shipment_lines rsl, ' ||
275: ' rcv_shipment_headers rsh ' ||
276: ' WHERE pll.po_header_id = ph.po_header_id ' ||
277: ' AND fsp.org_id = ph.org_id ' ||
278: ' AND mp.organization_id in (fsp.inventory_organization_id , pll.ship_to_organization_id) ' ||
279: ' AND pll.line_location_id = rsl.po_line_location_id' ||

Line 299: ' rcv_shipment_headers rsh ' ||

295: ' OR rp.organization_id = pll.ship_to_organization_id) ' ||
296: ' AND (pll.line_location_id in ' ||
297: ' (SELECT DISTINCT rsl.po_line_location_id ' ||
298: ' FROM rcv_shipment_lines rsl, ' ||
299: ' rcv_shipment_headers rsh ' ||
300: ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
301: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
302: ' AND rsh.ship_to_org_id ='||l_org_id ||
303: ' ))';

Line 310: ' rcv_shipment_headers rsh' ||

306: p_sql(27):= ' SELECT distinct psp.* ' ||
307: ' FROM po_system_parameters_all psp , ' ||
308: ' po_headers_all ph,' ||
309: ' rcv_shipment_lines rsl, ' ||
310: ' rcv_shipment_headers rsh' ||
311: ' WHERE psp.org_id = ph.org_id ' ||
312: ' AND ph.po_header_id = rsl.po_header_id' ||
313: ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
314: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||

Line 321: ' rcv_shipment_headers rsh' ||

317: p_sql(28) := ' SELECT distinct fsp.* ' ||
318: ' FROM financials_system_params_all fsp , ' ||
319: ' po_headers_all ph, ' ||
320: ' rcv_shipment_lines rsl, ' ||
321: ' rcv_shipment_headers rsh' ||
322: ' WHERE fsp.org_id = ph.org_id ' ||
323: ' and ph.po_header_id = rsl.po_header_id' ||
324: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
325: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||

Line 332: ' rcv_shipment_headers rsh, ' ||

328: p_sql(29) := ' SELECT distinct msn.* ' ||
329: ' FROM mtl_serial_numbers msn , ' ||
330: ' mtl_unit_transactions mut , ' ||
331: ' rcv_transactions rt ,' ||
332: ' rcv_shipment_headers rsh, ' ||
333: ' mtl_material_transactions mmt ' ||
334: ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
335: ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
336: ' AND mmt.transaction_source_type_id = 1'||

Line 348: ' rcv_shipment_headers rsh, ' ||

344: ' SELECT distinct msn.* ' ||
345: ' FROM mtl_serial_numbers msn , ' ||
346: ' mtl_unit_transactions mut , ' ||
347: ' rcv_transactions rt ,' ||
348: ' rcv_shipment_headers rsh, ' ||
349: ' mtl_material_transactions mmt, ' ||
350: ' mtl_transaction_lot_numbers mtln ' ||
351: ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
352: ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||

Line 367: ' rcv_shipment_headers rsh' ||

363: ' FROM mtl_serial_numbers_temp msnt , ' ||
364: ' mtl_material_transactions_temp mmtt, ' ||
365: ' po_headers_all ph,' ||
366: ' rcv_shipment_lines rsl,' ||
367: ' rcv_shipment_headers rsh' ||
368: ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
369: ' AND msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
370: ' AND rsl.po_header_id=ph.po_header_id' ||
371: ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||

Line 381: ' rcv_shipment_headers rsh ' ||

377: ' mtl_material_transactions_temp mmtt , ' ||
378: ' po_headers_all ph , ' ||
379: ' mtl_transaction_lots_temp mtln,' ||
380: ' rcv_shipment_lines rsl,' ||
381: ' rcv_shipment_headers rsh ' ||
382: ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
383: ' AND mtln.transaction_temp_id = mmtt.transaction_temp_id ' ||
384: ' AND msnt.transaction_temp_id = mtln.serial_transaction_temp_id ' ||
385: ' AND ph.po_header_id = rsl.po_header_id' ||

Line 393: ' rcv_shipment_headers rsh ' ||

389:
390: p_sql(31) := ' SELECT distinct msni.* ' ||
391: ' FROM mtl_serial_numbers_interface msni , ' ||
392: ' rcv_transactions_interface rti ,' ||
393: ' rcv_shipment_headers rsh ' ||
394: ' WHERE rsh.receipt_num ='||''''||l_receipt_num||'''' ||
395: ' AND rsh.ship_to_org_id ='||l_org_id ||
396: ' AND rti.shipment_header_id =rsh.shipment_header_id ' ||
397: ' AND msni.product_transaction_id = rti.interface_transaction_id';

Line 402: ' rcv_shipment_headers rsh, ' ||

398:
399: p_sql(32):=' SELECT distinct mut.* ' ||
400: ' FROM mtl_unit_transactions mut , ' ||
401: ' rcv_transactions rt ,' ||
402: ' rcv_shipment_headers rsh, ' ||
403: ' mtl_material_transactions mmt ' ||
404: ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
405: ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
406: ' AND mmt.transaction_source_type_id = 1'||

Line 414: ' rcv_shipment_headers rsh, ' ||

410: ' UNION ALL ' ||
411: ' SELECT mut.* ' ||
412: ' FROM mtl_unit_transactions mut, ' ||
413: ' rcv_transactions rt ,' ||
414: ' rcv_shipment_headers rsh, ' ||
415: ' mtl_material_transactions mmt , ' ||
416: ' mtl_transaction_lot_numbers mtln ' ||
417: ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
418: ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||

Line 429: ' rcv_shipment_headers rsh ' ||

425:
426: p_sql(33):=' SELECT distinct rss.* ' ||
427: ' FROM rcv_serials_supply rss , ' ||
428: ' rcv_shipment_lines rsl, ' ||
429: ' rcv_shipment_headers rsh ' ||
430: ' WHERE rss.shipment_line_id = rsl.shipment_line_id ' ||
431: ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
432: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
433: ' AND rsh.ship_to_org_id ='||l_org_id;

Line 438: ' rcv_shipment_headers rsh ' ||

434:
435: p_sql(34):=' SELECT distinct rst.* ' ||
436: ' FROM rcv_serial_transactions rst , ' ||
437: ' rcv_shipment_lines rsl , ' ||
438: ' rcv_shipment_headers rsh ' ||
439: ' WHERE rst.shipment_line_id = rsl.shipment_line_id ' ||
440: ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
441: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
442: ' AND rsh.ship_to_org_id ='||l_org_id;

Line 447: ' rcv_shipment_headers rsh ' ||

443:
444: p_sql(35):=' SELECT distinct rsi.* ' ||
445: ' FROM rcv_serials_interface rsi , ' ||
446: ' rcv_transactions_interface rti , ' ||
447: ' rcv_shipment_headers rsh ' ||
448: ' WHERE rti.shipment_header_id = rsh.shipment_header_id ' ||
449: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
450: ' AND rsh.ship_to_org_id ='||l_org_id ||
451: ' AND rsi.interface_transaction_id = rti.interface_transaction_id ';

Line 457: ' rcv_shipment_headers rsh, ' ||

453: p_sql(36):=' SELECT distinct mln.* ' ||
454: ' FROM mtl_lot_numbers mln , ' ||
455: ' mtl_transaction_lot_numbers mtln , ' ||
456: ' rcv_transactions rt ,' ||
457: ' rcv_shipment_headers rsh, ' ||
458: ' mtl_material_transactions mmt ' ||
459: ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
460: ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
461: ' AND mmt.transaction_source_type_id = 1'||

Line 472: ' rcv_shipment_headers rsh, ' ||

468:
469: p_sql(37):=' SELECT distinct mtln.* ' ||
470: ' FROM mtl_transaction_lot_numbers mtln , ' ||
471: ' rcv_transactions rt ,' ||
472: ' rcv_shipment_headers rsh, ' ||
473: ' mtl_material_transactions mmt ' ||
474: ' WHERE mmt.rcv_transaction_id = rt.transaction_id ' ||
475: ' AND rsh.shipment_header_id =rt.shipment_header_id ' ||
476: ' AND mmt.transaction_source_type_id = 1'||

Line 484: ' rcv_shipment_headers rsh ' ||

480:
481: p_sql(38):=' SELECT distinct mtli.* ' ||
482: ' FROM mtl_transaction_lots_interface mtli , ' ||
483: ' rcv_transactions_interface rti ,' ||
484: ' rcv_shipment_headers rsh ' ||
485: ' WHERE rti.shipment_header_id = rsh.shipment_header_id ' ||
486: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
487: ' AND rsh.ship_to_org_id ='||l_org_id ||
488: ' AND mtli.product_transaction_id = RTI.interface_transaction_id';

Line 500: ' rcv_shipment_headers rsh ' ||

496: ' AND mmtt.transaction_temp_id = mtlt.transaction_temp_id ' ||
497: ' AND (ph.po_header_id in ' ||
498: ' (SELECT DISTINCT po_header_id ' ||
499: ' FROM rcv_shipment_lines rsl, ' ||
500: ' rcv_shipment_headers rsh ' ||
501: ' WHERE rsh.shipment_header_id=rsl.shipment_header_id ' ||
502: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
503: ' AND rsh.ship_to_org_id ='||l_org_id ||' ))';
504:

Line 508: ' rcv_shipment_headers rsh ' ||

504:
505: p_sql(40):=' SELECT distinct rls.* ' ||
506: ' FROM rcv_lots_supply rls , ' ||
507: ' rcv_shipment_lines rsl , ' ||
508: ' rcv_shipment_headers rsh ' ||
509: ' WHERE rsl.shipment_line_id = rls.shipment_line_id ' ||
510: ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
511: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
512: ' AND rsh.ship_to_org_id ='||l_org_id;

Line 517: ' rcv_shipment_headers rsh ' ||

513:
514: p_sql(41):=' SELECT distinct rlt.* ' ||
515: ' FROM rcv_lot_transactions rlt , ' ||
516: ' rcv_shipment_lines rsl , ' ||
517: ' rcv_shipment_headers rsh ' ||
518: ' WHERE rsl.shipment_line_id = rlt.shipment_line_id ' ||
519: ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
520: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
521: ' AND rsh.ship_to_org_id ='||l_org_id;

Line 526: ' rcv_shipment_headers rsh ' ||

522:
523: p_sql(42):=' SELECT distinct rli.* ' ||
524: ' FROM rcv_lots_interface rli , ' ||
525: ' rcv_transactions_interface rti,' ||
526: ' rcv_shipment_headers rsh ' ||
527: ' WHERE rti.interface_transaction_id = rli.interface_transaction_id ' ||
528: ' AND rti.shipment_header_id =rsh.shipment_header_id ' ||
529: ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
530: ' AND rsh.ship_to_org_id ='||l_org_id;