[Home] [Help]
PACKAGE BODY: APPS.INV_DIAG_RCV_RCV_COMMON
Source
1 PACKAGE BODY INV_DIAG_RCV_RCV_COMMON AS
2 /* $Header: INVDPO3B.pls 120.0.12000000.1 2007/08/09 06:50:13 ssadasiv noship $ */
3
4 -----------------------------------------------------------------
5 -- Procedure to Build sqls for Receipt Number and Org combination
6 -----------------------------------------------------------------
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
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;
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 , ' ||
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;
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
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 ' ||
48 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
49 ' AND rsh.ship_to_org_id ='||l_org_id ||
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
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||'''' ||
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||'''' ||
74 ' AND rsh.ship_to_org_id ='||l_org_id ||
75 ' AND id.rcv_transaction_id = rt.transaction_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 ||
85 ' AND id.po_distribution_id = pd.po_distribution_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 ';
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 ||
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 ';
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
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 , ' ||
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 ' ||
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' ||
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' ||
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 ' ) ';
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 )'||
166 ' OR EXISTS '||
167 ' (SELECT 2 FROM rcv_headers_interface rhi'||
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 ';
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' ||
184 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
185 ' AND rsh.ship_to_org_id ='||l_org_id;
186
187 p_sql(21) := ' SELECT distinct mtt.transaction_type_id , ' ||' mtt.transaction_type_name , ' ||
188 ' mtt.transaction_source_type_id , ' ||
189 ' mtt.transaction_action_id , ' ||
190 ' mtt.user_defined_flag , ' ||
191 ' mtt.disable_date ' ||
192 ' FROM mtl_transaction_types mtt ' ||
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 ' ||
202 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
203 ' AND rsh.ship_to_org_id ='||l_org_id ||
204 ' ) ' ||
205 ' OR exists ' ||
206 ' (SELECT 2 ' ||
207 ' FROM mtl_material_transactions_temp mmtt , ' ||
208 ' po_headers_all ph ' ||
209 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
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
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 ' ||
227 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
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'||
237 ' AND rsl.shipment_header_id = rsh.shipment_header_id ' ||
238 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
239 ' AND rsh.ship_to_org_id ='||l_org_id;
240
241 p_sql(23) := ' SELECT distinct mmtt.* ' ||
242 ' FROM mtl_material_transactions_temp mmtt , ' ||
243 ' po_headers_all ph ' ||
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 ' )) ';
253
254 p_sql(24) := ' SELECT DISTINCT ood.* ' ||
255 ' FROM org_organization_definitions ood , ' ||
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' ||
265 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
266 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
267 ' AND rsh.ship_to_org_id ='||l_org_id;
268
269 p_sql(25) := ' SELECT DISTINCT mp.* ' ||
270 ' FROM mtl_parameters mp , ' ||
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' ||
280 ' AND rsh.shipment_header_id=rsl.shipment_header_id ' ||
281 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
282 ' AND rsh.ship_to_org_id ='||l_org_id;
283
284
285 p_sql(26) := ' SELECT DISTINCT rp.* ' ||
286 ' FROM rcv_parameters rp , ' ||
287 ' po_line_locations_all pll ,' ||
288 ' po_lines_all pl , ' ||
289 ' po_headers_all ph, ' ||
290 ' financials_system_params_all fsp ' ||
291 ' WHERE pl.po_header_id = ph.po_header_id ' ||
292 ' AND pll.po_line_id = pl.po_line_id ' ||
293 ' AND fsp.org_id = ph.org_id ' ||
294 ' AND (rp.organization_id = fsp.inventory_organization_id ' ||
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 ' ))';
304
305
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||'''' ||
315 ' AND rsh.ship_to_org_id ='||l_org_id;
316
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||'''' ||
326 ' AND rsh.ship_to_org_id ='||l_org_id;
327
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'||
337 ' AND mut.transaction_id = mmt.transaction_id ' ||
338 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
339 ' AND msn.current_organization_id = mut.organization_id ' ||
340 ' AND msn.serial_number = mut.serial_number ' ||
341 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
342 ' AND rsh.ship_to_org_id ='||l_org_id ||
343 ' UNION ALL ' ||
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 ' ||
353 ' AND mmt.transaction_source_type_id = 1'||
354 ' AND mtln.transaction_id = mmt.transaction_id ' ||
355 ' AND mut.transaction_id = mtln.serial_transaction_id ' ||
356 ' AND msn.inventory_item_id = mut.inventory_item_id ' ||
357 ' AND msn.current_organization_id = mut.organization_id ' ||
358 ' AND msn.serial_number = mut.serial_number ' ||
359 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
360 ' AND rsh.ship_to_org_id ='||l_org_id;
361
362 p_sql(30):=' SELECT distinct msnt.* ' ||
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 ' ||
372 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
373 ' AND rsh.ship_to_org_id ='||l_org_id ||
374 ' UNION ALL ' ||
375 ' SELECT msnt.* ' ||
376 ' FROM mtl_serial_numbers_temp msnt, ' ||
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' ||
386 ' and rsh.shipment_header_id=rsl.shipment_header_id ' ||
387 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
388 ' AND rsh.ship_to_org_id ='||l_org_id;
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';
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'||
407 ' AND mut.transaction_id = mmt.transaction_id ' ||
408 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
409 ' AND rsh.ship_to_org_id ='||l_org_id ||
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 ' ||
419 ' AND mmt.transaction_source_type_id = 1'||
420 ' AND mtln.transaction_id = mmt.transaction_id ' ||
421 ' AND mut.transaction_id = mtln.serial_transaction_id ' ||
422 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
423 ' AND rsh.ship_to_org_id ='||l_org_id ;
424
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;
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;
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 ';
452
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'||
462 ' AND mtln.transaction_id = mmt.transaction_id ' ||
463 ' AND mtln.lot_number = mln.lot_number ' ||
464 ' AND mtln.inventory_item_id = mln.inventory_item_id ' ||
465 ' AND mtln.organization_id = mln.organization_id ' ||
466 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
467 ' AND rsh.ship_to_org_id ='||l_org_id;
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'||
477 ' AND mtln.transaction_id = mmt.transaction_id ' ||
478 ' AND rsh.receipt_num ='||''''||l_receipt_num||'''' ||
479 ' AND rsh.ship_to_org_id ='||l_org_id;
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';
489
490 p_sql(39):=' SELECT distinct mtlt.* ' ||
491 ' FROM mtl_transaction_lots_temp mtlt , ' ||
492 ' mtl_material_transactions_temp mmtt ,' ||
493 ' po_headers_all ph ' ||
494 ' WHERE mmtt.transaction_source_id = ph.po_header_id ' ||
495 ' AND mmtt.transaction_source_type_id = 1 ' ||
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
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;
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;
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;
531
532 RETURN;
533 END; -- END build_rcv_sql
534
535
536 ----------------------------------------------------
537 -- Procedure to build the sqls for the lookup codes
538 ----------------------------------------------------
539 PROCEDURE build_lookup_codes(p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list)
540 IS
541
542 BEGIN
543
544 p_sql(100) := ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
545 ' from mfg_lookups ' ||' where lookup_type = ''MTL_SERIAL_NUMBER'' ';
546
547 p_sql(101) := ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
548 ' from mfg_lookups ' ||' where lookup_type = ''MTL_SERIAL_NUMBER_TYPE'' ';
549
550 p_sql(102) := ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
551 ' from mfg_lookups ' ||' where lookup_type = ''MTL_SERIAL_GENERATION'' ';
552
553 p_sql(103) := ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
554 ' from mfg_lookups ' ||' where lookup_type = ''SERIAL_NUM_STATUS'' ';
555
556 p_sql(104) := ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
557 ' from mfg_lookups ' ||' where lookup_type = ''MTL_LOT_CONTROL'' ';
558
559 p_sql(105) := ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
560 ' from mfg_lookups ' ||' where lookup_type = ''MTL_LOT_GENERATION'' ';
561
562 p_sql(106) := ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
563 ' from mfg_lookups ' ||' where lookup_type = ''MTL_LOT_UNIQUENESS'' ';
564
565 END; -- END build_lookup_codes
566
567 END INV_DIAG_RCV_RCV_COMMON;