[Home] [Help]
PACKAGE BODY: APPS.IO_DIAGNOSTICS2
Source
1 PACKAGE BODY io_diagnostics2 AS
2 /* $Header: INVDIO2B.pls 120.0.12000000.1 2007/08/09 06:48:29 ssadasiv noship $ */
3
4 PROCEDURE receipt_shipment_sql(p_shipment_num IN VARCHAR2, p_receipt_num IN VARCHAR2, p_org_id IN NUMBER, p_sql IN OUT
5 NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
6 l_ou_id po_requisition_headers_all.org_id%TYPE := NULL;
7 l_req_num po_requisition_headers_all.segment1%TYPE := NULL;
8 l_line_num po_requisition_lines_all.line_num%TYPE := NULL;
9 l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
10 l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
11 l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
12
13 BEGIN
14
15 p_sql(1) := ' SELECT DISTINCT prh.* ' ||
16 ' FROM po_requisition_headers_all prh, ' ||
17 ' po_requisition_lines_all prl ' ||
18 ' WHERE prh.requisition_header_id = prl.requisition_header_id ' ||
19 ' AND prl.source_type_code = ''INVENTORY'' ' ||
20 ' AND requisition_line_id in ' ||
21 ' (SELECT requisition_line_id ' ||
22 ' FROM rcv_shipment_lines rsl, ' ||
23 ' rcv_shipment_headers rsh ' ||
24 ' WHERE rsh.shipment_header_id = rsl.shipment_header_id ' ||
25 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
26 ' and rsh.ship_to_org_id = ' || l_org_id ||
27 ' ) ';
28
29
30 p_sql(2) := ' SELECT DISTINCT prl.* ' ||
31 ' FROM po_requisition_lines_all prl ' ||
32 ' WHERE prl.requisition_line_id in ' ||
33 ' (SELECT requisition_line_id ' ||
34 ' FROM rcv_shipment_lines rsl, ' ||
35 ' rcv_shipment_headers rsh ' ||
36 ' WHERE rsh.shipment_header_id = rsl.shipment_header_id ' ||
37 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
38 ' and rsh.ship_to_org_id = ' || l_org_id ||
39 ' ) ' ||
40 ' AND prl.source_type_code = ''INVENTORY'' ' ||
41 ' ORDER BY prl.requisition_line_id ';
42
43
44
45 p_sql(3) := ' SELECT DISTINCT prd.* ' ||
46 ' FROM po_req_distributions_all prd , ' ||
47 ' po_requisition_lines_all prl ' ||
48 ' WHERE prl.requisition_line_id = prd.requisition_line_id ' ||
49 ' AND prl.source_type_code = ''INVENTORY'' ' ||
50 ' AND prl.requisition_line_id in ' ||
51 ' (SELECT requisition_line_id ' ||
52 ' FROM rcv_shipment_lines rsl, ' ||
53 ' rcv_shipment_headers rsh ' ||
54 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
55 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
56 ' and rsh.ship_to_org_id = ' || l_org_id ||
57 ' ) ';
58
59
60
61 p_sql(4) := ' SELECT DISTINCT oel.* ' ||
62 ' FROM oe_order_lines_all oel, ' ||
63 ' po_requisition_lines_all prl ' ||
64 ' WHERE oel.source_document_type_id = 10 ' ||
65 ' AND oel.source_document_line_id = prl.requisition_line_id ' ||
66 ' AND prl.requisition_line_id in ' ||
67 ' (SELECT requisition_line_id ' ||
68 ' FROM rcv_shipment_lines rsl, ' ||
69 ' rcv_shipment_headers rsh ' ||
70 ' WHERE rsh.shipment_header_id = rsl.shipment_header_id ' ||
71 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
72 ' and rsh.ship_to_org_id = ' || l_org_id ||
73 ' )' ||
74 ' ORDER BY oel.line_id ';
75
76
77
78
79 p_sql(5) := ' SELECT DISTINCT wsh.* ' ||
80 ' FROM wsh_delivery_details wsh , ' ||
81 ' wsh_delivery_assignments wda , ' ||
82 ' wsh_new_deliveries wnd , ' ||
83 ' oe_order_lines_all sol , ' ||
84 ' po_requisition_lines_all prl , ' ||
85 ' rcv_shipment_headers rsh, ' ||
86 ' rcv_shipment_lines rsl ' ||
87 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
88 ' and rsh.ship_to_org_id = ' || l_org_id ||
89 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
90 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
91 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
92 ' AND sol.source_document_type_id = 10 ' ||
93 ' AND wsh.source_line_id = sol.line_id ' ||
94 ' AND wsh.delivery_detail_id = wda.delivery_detail_id ' ||
95 ' AND wda.delivery_id = wnd.delivery_id ' ||
96 ' UNION ALL ' ||
97 ' SELECT DISTINCT wsh.* ' ||
98 ' FROM wsh_delivery_details wsh , ' ||
99 ' mtl_transactions_interface mti , ' ||
100 ' po_requisition_lines_all prl , ' ||
101 ' oe_order_lines_all sol, ' ||
102 ' rcv_shipment_headers rsh, ' ||
103 ' rcv_shipment_lines rsl ' ||
104 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
105 ' and rsh.ship_to_org_id = ' || l_org_id ||
106 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
107 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
108 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
109 ' AND sol.source_document_type_id = 10 ' ||
110 ' AND mti.trx_source_line_id = sol.line_id ' ||
111 ' AND mti.picking_line_id = wsh.delivery_detail_id ';
112
113
114
115 p_sql(6) := ' SELECT DISTINCT rhi.* ' ||
116 ' FROM rcv_headers_interface rhi,' ||
117 ' rcv_shipment_headers rsh ' ||
118 ' WHERE rhi.receipt_header_id = rsh.shipment_header_id ' ||
119 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
120 ' and rsh.ship_to_org_id = ' || l_org_id ||
121 ' UNION ALL' ||
122 ' SELECT DISTINCT rhi.* ' ||
123 ' FROM rcv_headers_interface rhi ' ||
124 ' where rhi.shipment_num = ' || '''' || l_shipment_num || '''' ||
125 ' and rhi.ship_to_organization_id = ' || l_org_id ;
126
127
128
129 p_sql(7) := ' SELECT DISTINCT rti.* ' ||
130 ' FROM rcv_transactions_interface rti ' ||
131 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
132 ' and rti.to_organization_id = ' || l_org_id ;
133
134
135
136 p_sql(8) := ' SELECT DISTINCT pie.* ' ||
137 ' FROM po_interface_errors pie ' ||
138 ' WHERE pie.interface_transaction_id IN ' ||
139 ' ( SELECT DISTINCT rti.interface_transaction_id ' ||
140 ' FROM rcv_transactions_interface rti ' ||
141 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
142 ' and rti.to_organization_id = ' || l_org_id || ')' ||
143 ' OR pie.interface_line_id IN ' ||
144 ' ( SELECT DISTINCT rti.interface_transaction_id ' ||
145 ' FROM rcv_transactions_interface rti ' ||
146 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
147 ' and rti.to_organization_id = ' || l_org_id || ')';
148
149
150
151 p_sql(9) := ' SELECT DISTINCT rsh.* ' ||
152 ' FROM rcv_shipment_headers rsh ' ||
153 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
154 ' and rsh.ship_to_org_id = ' || l_org_id ;
155
156
157
158
159
160 p_sql(10) := ' SELECT DISTINCT rsl.*' ||
161 ' FROM rcv_shipment_headers rsh, ' ||
162 ' rcv_shipment_lines rsl ' ||
163 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
164 ' and rsh.ship_to_org_id = ' || l_org_id ||
165 ' AND rsh.shipment_header_id = rsl.shipment_header_id ';
166
167
168
169
170 p_sql(11) := ' SELECT DISTINCT rt.* ' ||
171 ' FROM rcv_shipment_headers rsh, ' ||
172 ' rcv_shipment_lines rsl, ' ||
173 ' rcv_transactions rt ' ||
174 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
175 ' and rsh.ship_to_org_id = ' || l_org_id ||
176 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
177 ' AND rsh.shipment_header_id = rt.shipment_header_id ' ||
178 ' AND rsl.shipment_header_id = rt.shipment_header_id ';
179
180
181
182
183 p_sql(12) := ' SELECT DISTINCT ms.* ' ||
184 ' FROM mtl_supply ms , ' ||
185 ' rcv_shipment_headers rsh, ' ||
186 ' rcv_shipment_lines rsl ' ||
187 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
188 ' and rsh.ship_to_org_id = ' || l_org_id ||
189 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
190 ' AND ms.req_line_id = rsl.requisition_line_id ' ||
191 ' AND ms.shipment_header_id = rsl.shipment_header_id ';
192
193
194
195
196
197 p_sql(13) := ' SELECT DISTINCT rs.* ' ||
198 ' FROM rcv_supply rs , ' ||
199 ' rcv_shipment_headers rsh, ' ||
200 ' rcv_shipment_lines rsl ' ||
201 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
202 ' and rsh.ship_to_org_id = ' || l_org_id ||
203 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
204 ' AND rsl.requisition_line_id = rs.req_line_id ' ||
205 ' AND rs.shipment_header_id = rsl.shipment_header_id ';
206
207
208
209 p_sql(14) := ' SELECT DISTINCT mtrl.* ' ||
210 ' FROM mtl_txn_request_lines mtrl, ' ||
211 ' rcv_shipment_headers rsh, ' ||
212 ' rcv_shipment_lines rsl, ' ||
213 ' po_requisition_lines_all prl ' ||
214 ' where rsh.shipment_num = '|| '''' || l_shipment_num || '''' ||
215 ' and rsh.ship_to_org_id = '|| l_org_id ||
216 ' AND rsh.receipt_source_code = ''INTERNAL ORDER'' ' ||
217 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
218 ' and mtrl.inventory_item_id=rsl.item_id ' ||
219 ' and nvl(mtrl.revision,0)=nvl(prl.item_revision,0) ' ||
220 ' and mtrl.organization_id=rsl.to_organization_id ' ||
221 ' and mtrl.transaction_type_id=52'||
222 ' and mtrl.line_status=7';
223
224 /* ' SELECT DISTINCT mtrl.* ' ||
225 ' FROM mtl_txn_request_lines mtrl, ' ||
226 ' rcv_shipment_headers rsh, ' ||
227 ' rcv_shipment_lines rsl, ' ||
228 ' po_requisition_lines_all prl ' ||
229 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
230 ' and rsh.ship_to_org_id = ' || l_org_id ||
231 ' AND rsh.receipt_source_code = ''INTERNAL ORDER'' ' ||
232 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
233 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
234 ' AND mtrl.reference = ''SHIPMENT_LINE_ID'' ' ||
235 ' AND mtrl.reference_id = rsl.shipment_line_id '; */
236
237
238
239 p_sql(15) := ' SELECT DISTINCT mti.* ' ||
240 ' FROM mtl_transactions_interface mti, ' ||
241 ' po_requisition_lines_all prl, ' ||
242 ' rcv_shipment_headers rsh, ' ||
243 ' rcv_shipment_lines rsl, ' ||
244 ' oe_order_lines_all sol ' ||
245 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
246 ' and rsh.ship_to_org_id = ' || l_org_id ||
247 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
248 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
249 ' AND sol.source_document_type_id = 10 ' ||
250 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
251 ' AND mti.trx_source_line_id = sol.line_id ' ||
252 ' AND mti.source_code = ''ORDER ENTRY'' ';
253
254
255
256 p_sql(16) := ' SELECT DISTINCT mmtt.* ' ||
257 ' FROM mtl_material_transactions_temp mmtt , ' ||
258 ' po_requisition_lines_all prl , ' ||
259 ' rcv_shipment_lines rsl, ' ||
260 ' rcv_shipment_headers rsh,' ||
261 ' rcv_transactions rt' ||
262 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
263 ' and rsh.ship_to_org_id = ' || l_org_id ||
264 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
265 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
266 ' AND rt.requisition_line_id = prl.requisition_line_id' ||
267 ' AND mmtt.rcv_transaction_id = rt.transaction_id ' ||
268 ' UNION ALL' ||
269 ' SELECT DISTINCT mmtt.* ' ||
270 ' FROM mtl_material_transactions_temp mmtt , ' ||
271 ' po_requisition_lines_all prl , ' ||
272 ' rcv_shipment_lines rsl, ' ||
273 ' rcv_shipment_headers rsh,' ||
274 ' oe_order_lines_all sol' ||
275 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
276 ' and rsh.ship_to_org_id = ' || l_org_id ||
277 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
278 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
279 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
280 ' AND sol.source_document_type_id = 10 ' ||
281 ' AND mmtt.trx_source_line_id = sol.line_id ';
282
283
284
285 p_sql(17) := ' SELECT DISTINCT mmt.* ' ||
286 ' FROM mtl_material_transactions mmt , ' ||
287 ' po_requisition_lines_all prl , ' ||
288 ' rcv_shipment_lines rsl, ' ||
289 ' rcv_shipment_headers rsh,' ||
290 ' rcv_transactions rt' ||
291 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
292 ' and rsh.ship_to_org_id = ' || l_org_id ||
293 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
294 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
295 ' AND rt.requisition_line_id = prl.requisition_line_id' ||
296 ' AND mmt.rcv_transaction_id = rt.transaction_id ' ||
297 ' UNION ALL' ||
298 ' SELECT DISTINCT mmt.* ' ||
299 ' FROM mtl_material_transactions mmt, ' ||
300 ' po_requisition_lines_all prl , ' ||
301 ' rcv_shipment_lines rsl, ' ||
302 ' rcv_shipment_headers rsh,' ||
303 ' oe_order_lines_all sol' ||
304 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
305 ' and rsh.ship_to_org_id = ' || l_org_id ||
306 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
307 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
308 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
309 ' AND sol.source_document_type_id = 10 ' ||
310 ' AND mmt.trx_source_line_id = sol.line_id ' ||
311 ' and mmt.transaction_action_id = 21';
312
313
314 p_sql(18) := ' SELECT DISTINCT mr.* ' ||
315 ' FROM mtl_reservations mr , ' ||
316 ' oe_order_lines_all sol , ' ||
317 ' po_requisition_lines_all prl , ' ||
318 ' rcv_shipment_headers rsh, ' ||
319 ' rcv_shipment_lines rsl ' ||
320 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
321 ' and rsh.ship_to_org_id = ' || l_org_id ||
322 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
323 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
324 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
325 ' AND sol.source_document_type_id = 10 ' ||
326 ' AND mr.demand_source_line_id = sol.line_id ' ||
327 ' AND mr.demand_source_type_id = 8 ' ||
328 ' UNION ALL ' ||
329 ' SELECT DISTINCT mr.* ' ||
330 ' FROM mtl_reservations mr , ' ||
331 ' mtl_transactions_interface mti , ' ||
332 ' po_requisition_lines_all prl , ' ||
333 ' oe_order_lines_all sol ,' ||
334 ' rcv_shipment_headers rsh, ' ||
335 ' rcv_shipment_lines rsl ' ||
336 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
337 ' and rsh.ship_to_org_id = ' || l_org_id ||
338 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
339 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
340 ' AND sol.source_document_type_id = 10 ' ||
341 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
342 ' AND mti.trx_source_line_id = sol.line_id ' ||
343 ' AND mr.demand_source_line_id = mti.trx_source_line_id ';
344
345
346
347 p_sql(19) := ' SELECT DISTINCT md.* ' ||
348 ' FROM mtl_demand md , ' ||
352 ' rcv_shipment_lines rsl ' ||
349 ' oe_order_lines_all sol , ' ||
350 ' po_requisition_lines_all prl , ' ||
351 ' rcv_shipment_headers rsh, ' ||
353 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
354 ' and rsh.ship_to_org_id = ' || l_org_id ||
355 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
356 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
357 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
358 ' AND sol.source_document_type_id = 10 ' ||
359 ' AND md.demand_source_line = sol.line_id ' ||
360 ' AND md.demand_source_type = 8 ' ||
361 ' UNION ALL ' ||
362 ' SELECT DISTINCT md.* ' ||
363 ' FROM mtl_demand md , ' ||
364 ' mtl_transactions_interface mti , ' ||
365 ' po_requisition_lines_all prl , ' ||
366 ' oe_order_lines_all sol , ' ||
367 ' rcv_shipment_headers rsh, ' ||
368 ' rcv_shipment_lines rsl ' ||
369 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
370 ' and rsh.ship_to_org_id = ' || l_org_id ||
371 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
372 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
373 ' AND sol.source_document_type_id = 10 ' ||
374 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
375 ' AND mti.trx_source_line_id = sol.line_id ' ||
376 ' AND md.demand_source_line = mti.source_line_id ';
377
378
379
380 p_sql(20) := ' SELECT DISTINCT msn.* ' ||
381 ' FROM mtl_serial_numbers msn , ' ||
382 ' mtl_material_transactions mmt , ' ||
383 ' po_requisition_lines_all prl , ' ||
384 ' rcv_shipment_headers rsh, ' ||
385 ' rcv_shipment_lines rsl,' ||
386 ' rcv_transactions rt' ||
387 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
388 ' and rsh.ship_to_org_id = ' || l_org_id ||
389 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
390 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
391 ' AND rt.requisition_line_id = prl.requisition_line_id' ||
392 ' AND mmt.rcv_transaction_id = rt.transaction_id ' ||
393 ' AND mmt.transaction_id = msn.last_transaction_id' ||
394 ' UNION ALL' ||
395 ' SELECT DISTINCT msn.* ' ||
396 ' FROM mtl_serial_numbers msn , ' ||
397 ' mtl_material_transactions mmt , ' ||
398 ' po_requisition_lines_all prl , ' ||
399 ' rcv_shipment_headers rsh, ' ||
400 ' rcv_shipment_lines rsl,' ||
401 ' oe_order_lines_all sol ' ||
402 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
403 ' and rsh.ship_to_org_id = ' || l_org_id ||
404 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
405 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
406 ' AND sol.source_document_type_id = 10 ' ||
407 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
408 ' AND mmt.trx_source_line_id = sol.line_id' ||
409 ' AND mmt.transaction_id = msn.last_transaction_id ';
410
411
412
413 p_sql(21) := ' select DISTINCT msnt.*' ||
414 ' from mtl_serial_numbers_temp msnt ,' ||
415 ' mtl_system_items msi,' ||
416 ' rcv_transactions_interface rti, ' ||
417 ' rcv_shipment_headers rsh, ' ||
418 ' rcv_shipment_lines rsl ' ||
419 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
420 ' and rsh.ship_to_org_id = ' || l_org_id ||
421 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
422 ' and rti.shipment_num = rsh.shipment_num' ||
423 ' and rti.interface_transaction_id = msnt.transaction_temp_id' ||
424 ' and msi.inventory_item_id = rti.item_id' ||
425 ' and msi.organization_id = rti.to_organization_id' ||
426 ' and msi.serial_number_control_code <> 1' ||
427 ' and msi.lot_control_code = 1' ||
428 ' UNION ALL' ||
429 ' select DISTINCT msnt.*' ||
430 ' from po_requisition_headers_all prh ,' ||
431 ' mtl_serial_numbers_temp msnt ,' ||
432 ' mtl_transaction_lots_temp mtlt,' ||
433 ' mtl_system_items msi,' ||
434 ' rcv_transactions_interface rti, ' ||
435 ' rcv_shipment_headers rsh, ' ||
436 ' rcv_shipment_lines rsl ' ||
437 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
438 ' and rsh.ship_to_org_id = ' || l_org_id ||
439 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
440 ' and rti.shipment_num = rsh.shipment_num' ||
441 ' and rti.interface_transaction_id = mtlt.transaction_temp_id' ||
442 ' and mtlt.SERIAL_TRANSACTION_TEMP_ID = msnt.transaction_temp_id' ||
443 ' and msi.inventory_item_id = rti.item_id' ||
444 ' and msi.organization_id = rti.to_organization_id' ||
445 ' and msi.serial_number_control_code <> 1' ||
446 ' and msi.lot_control_code <> 1' ||
447 ' UNION ALL' ||
448 ' select DISTINCT msnt.*' ||
449 ' from po_requisition_lines_all prl, ' ||
450 ' mtl_serial_numbers_temp msnt,' ||
451 ' mtl_system_items msi,' ||
452 ' oe_order_lines_all sol,' ||
453 ' mtl_material_transactions_temp mmtt,' ||
454 ' rcv_shipment_headers rsh, ' ||
455 ' rcv_shipment_lines rsl ' ||
456 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
457 ' and rsh.ship_to_org_id = ' || l_org_id ||
458 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
459 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
460 ' and prl.source_type_code = ''INVENTORY'' ' ||
461 ' and sol.source_document_line_id = prl.requisition_line_id' ||
462 ' and sol.source_document_type_id = 10' ||
463 ' and mmtt.trx_source_line_id = sol.line_id' ||
464 ' and msnt.transaction_TEMP_id = mmtt.transaction_TEMP_id' ||
468 ' and msi.lot_control_code = 1' ||
465 ' and msi.inventory_item_id = mmtt.inventory_item_id' ||
466 ' and msi.organization_id = mmtt.organization_id' ||
467 ' and msi.serial_number_control_code <> 1' ||
469 ' UNION ALL' ||
470 ' select DISTINCT msnt.*' ||
471 ' from po_requisition_lines_all prl,' ||
472 ' mtl_serial_numbers_temp msnt,' ||
473 ' mtl_transaction_lots_temp mtlt,' ||
474 ' mtl_system_items msi,' ||
475 ' oe_order_lines_all sol,' ||
476 ' mtl_material_transactions_temp mmtt,' ||
477 ' rcv_shipment_headers rsh, ' ||
478 ' rcv_shipment_lines rsl ' ||
479 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
480 ' and rsh.ship_to_org_id = ' || l_org_id ||
481 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
482 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
483 ' and prl.source_type_code = ''INVENTORY'' ' ||
484 ' and sol.source_document_line_id = prl.requisition_line_id' ||
485 ' and sol.source_document_type_id = 10' ||
486 ' and mmtt.trx_source_line_id = sol.line_id' ||
487 ' and mmtt.transaction_TEMP_id = mtlt.transaction_TEMP_id' ||
488 ' and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id' ||
489 ' and msi.inventory_item_id = mmtt.inventory_item_id' ||
490 ' and msi.organization_id = mmtt.organization_id' ||
491 ' and msi.serial_number_control_code <> 1' ||
492 ' and msi.lot_control_code <> 1 ';
493
494
495 p_sql(22) := ' SELECT DISTINCT msni.* ' ||
496 ' FROM mtl_transactions_interface mti , ' ||
497 ' po_requisition_lines_all prl , ' ||
498 ' mtl_serial_numbers_interface msni , ' ||
499 ' mtl_system_items msi ,' ||
500 ' rcv_shipment_headers rsh, ' ||
501 ' rcv_shipment_lines rsl ' ||
502 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
503 ' and rsh.ship_to_org_id = ' || l_org_id ||
504 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
505 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
506 ' AND prl.source_type_code = ''INVENTORY'' ' ||
507 ' AND mti.requisition_line_id = prl.requisition_line_id' ||
508 ' AND msni.transaction_interface_id = mti.transaction_interface_id ' ||
509 ' AND msi.inventory_item_id = mti.inventory_item_id ' ||
510 ' AND msi.organization_id = mti.organization_id ' ||
511 ' AND msi.serial_number_control_code <> 1 ' ||
512 ' and msi.lot_control_code = 1' ||
513 ' UNION ALL' ||
514 ' SELECT DISTINCT msni.* ' ||
515 ' FROM mtl_transactions_interface mti , ' ||
516 ' po_requisition_lines_all prl , ' ||
517 ' mtl_serial_numbers_interface msni , ' ||
518 ' mtl_transaction_lots_interface mtli,' ||
519 ' mtl_system_items msi ,' ||
520 ' rcv_shipment_headers rsh, ' ||
521 ' rcv_shipment_lines rsl ' ||
522 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
523 ' and rsh.ship_to_org_id = ' || l_org_id ||
524 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
525 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
526 ' AND prl.source_type_code = ''INVENTORY'' ' ||
527 ' AND mti.requisition_line_id = prl.requisition_line_id' ||
528 ' AND mtli.transaction_interface_id = mti.transaction_interface_id ' ||
529 ' AND msni.transaction_interface_id = mtli.serial_transaction_temp_id' ||
530 ' AND msi.inventory_item_id = mti.inventory_item_id ' ||
531 ' AND msi.organization_id = mti.organization_id ' ||
532 ' AND msi.serial_number_control_code <> 1 ' ||
533 ' and msi.lot_control_code <> 1 ';
534
535
536
537 p_sql(23) := ' select distinct mut.* ' ||
538 ' from mtl_material_transactions mmt , ' ||
539 ' po_requisition_lines_all prl , ' ||
540 ' mtl_unit_transactions mut , ' ||
541 ' mtl_system_items msi,' ||
542 ' rcv_transactions rt,' ||
543 ' rcv_shipment_headers rsh, ' ||
544 ' rcv_shipment_lines rsl ' ||
545 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
546 ' and rsh.ship_to_org_id = ' || l_org_id ||
547 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
548 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
549 ' AND rt.requisition_line_id = prl.requisition_line_id' ||
550 ' and prl.source_type_code = ''INVENTORY'' ' ||
551 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
552 ' and mmt.transaction_id = mut.transaction_id ' ||
553 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
554 ' and msi.organization_id = mmt.organization_id ' ||
555 ' and msi.serial_number_control_code <> 1 ' ||
556 ' and msi.lot_control_code = 1 ' ||
557 ' union all ' ||
558 ' select distinct mut.*' ||
559 ' from mtl_material_transactions mmt ,' ||
560 ' po_requisition_lines_all prl ,' ||
561 ' mtl_unit_transactions mut , ' ||
562 ' mtl_system_items msi , ' ||
563 ' rcv_transactions rt , ' ||
564 ' mtl_transaction_lot_numbers mtln,' ||
565 ' rcv_shipment_headers rsh, ' ||
566 ' rcv_shipment_lines rsl ' ||
567 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
568 ' and rsh.ship_to_org_id = ' || l_org_id ||
569 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
570 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
571 ' and prl.source_type_code = ''INVENTORY'' ' ||
572 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
573 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
574 ' and mtln.transaction_id = mmt.transaction_id ' ||
575 ' and mut.transaction_id = mtln.serial_transaction_id ' ||
576 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
580 ' union all' ||
577 ' and msi.organization_id = mmt.organization_id' ||
578 ' and msi.serial_number_control_code <> 1' ||
579 ' and msi.lot_control_code <> 1' ||
581 ' select distinct mut.* ' ||
582 ' from mtl_material_transactions mmt , ' ||
583 ' po_requisition_lines_all prl , ' ||
584 ' mtl_unit_transactions mut , ' ||
585 ' mtl_system_items msi , ' ||
586 ' oe_order_lines_all sol,' ||
587 ' rcv_shipment_headers rsh, ' ||
588 ' rcv_shipment_lines rsl ' ||
589 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
590 ' and rsh.ship_to_org_id = ' || l_org_id ||
591 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
592 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
593 ' and prl.source_type_code = ''INVENTORY'' ' ||
594 ' and sol.source_document_line_id = prl.requisition_line_id' ||
595 ' and sol.source_document_type_id = 10' ||
596 ' and mmt.trx_source_line_id = sol.line_id ' ||
597 ' and mut.transaction_id = mmt.transaction_id' ||
598 ' and msi.inventory_item_id = mmt.inventory_item_id' ||
599 ' and msi.organization_id = mmt.organization_id' ||
600 ' and msi.serial_number_control_code <> 1' ||
601 ' and msi.lot_control_code = 1' ||
602 ' union all' ||
603 ' select distinct mut.*' ||
604 ' from mtl_material_transactions mmt , ' ||
605 ' po_requisition_lines_all prl , ' ||
606 ' mtl_unit_transactions mut , ' ||
607 ' mtl_system_items msi , ' ||
608 ' oe_order_lines_all sol , ' ||
609 ' mtl_transaction_lot_numbers mtln,' ||
610 ' rcv_shipment_headers rsh, ' ||
611 ' rcv_shipment_lines rsl ' ||
612 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
613 ' and rsh.ship_to_org_id = ' || l_org_id ||
614 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
615 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
616 ' and prl.source_type_code = ''INVENTORY'' ' ||
617 ' and sol.source_document_line_id = prl.requisition_line_id' ||
618 ' and sol.source_document_type_id = 10' ||
619 ' and mmt.trx_source_line_id = sol.line_id' ||
620 ' and mtln.transaction_id = mmt.transaction_id' ||
621 ' and mut.transaction_id = mtln.serial_transaction_id' ||
622 ' and msi.inventory_item_id = mmt.inventory_item_id' ||
623 ' and msi.organization_id = mmt.organization_id' ||
624 ' and msi.serial_number_control_code <> 1' ||
625 ' and msi.lot_control_code <> 1 ';
626
627
628
629 p_sql(24) := ' SELECT DISTINCT rss.* ' ||
630 ' FROM rcv_serials_supply rss , ' ||
631 ' rcv_shipment_lines rsl , ' ||
632 ' rcv_shipment_headers rsh ' ||
633 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
634 ' and rsh.ship_to_org_id = ' || l_org_id ||
635 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
636 ' AND rss.shipment_line_id = rsl.shipment_line_id ' ||
637 ' ORDER BY rss.supply_type_code , ' ||
638 ' rss.serial_num ';
639
640 p_sql(25) := ' SELECT DISTINCT rst.* ' ||
641 ' FROM rcv_serial_transactions rst , ' ||
642 ' rcv_shipment_headers rsh, ' ||
643 ' rcv_shipment_lines rsl ' ||
644 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
645 ' and rsh.ship_to_org_id = ' || l_org_id ||
646 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
647 ' AND rsl.shipment_line_id = rst.shipment_line_id ' ||
648 ' AND rsl.source_document_code = ''REQ'' ' ||
649 ' ORDER BY rst.serial_transaction_type , ' ||
650 ' rst.serial_num ';
651
652
653 p_sql(26) := ' SELECT DISTINCT rsi.* ' ||
654 ' FROM rcv_serials_interface rsi , ' ||
655 ' rcv_shipment_headers rsh, ' ||
656 ' rcv_shipment_lines rsl ,' ||
657 ' rcv_transactions_interface rti' ||
658 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
659 ' and rsh.ship_to_org_id = ' || l_org_id ||
660 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
661 ' AND rsl.source_document_code = ''REQ'' ' ||
662 ' AND rti.shipment_line_id = rsl.shipment_line_id' ||
663 ' AND rsi.item_id = rsl.item_id ' ||
664 ' AND rsi.organization_id = rsl.to_organization_id ' ||
665 ' AND rsi.interface_transaction_id = rti.interface_transaction_id ';
666
667
668
669 p_sql(27) := ' select distinct mln.* ' ||
670 ' from mtl_lot_numbers mln , ' ||
671 ' mtl_transaction_lot_numbers mtln , ' ||
672 ' mtl_material_transactions mmt , ' ||
673 ' po_requisition_lines_all prl , ' ||
674 ' rcv_transactions rt,' ||
675 ' rcv_shipment_headers rsh, ' ||
676 ' rcv_shipment_lines rsl ' ||
677 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
678 ' and rsh.ship_to_org_id = ' || l_org_id ||
679 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
680 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
681 ' and rt.requisition_line_id = prl.requisition_line_id' ||
682 ' and prl.source_type_code = ''INVENTORY'' ' ||
683 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
684 ' and mmt.transaction_id = mtln.transaction_id ' ||
685 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
686 ' and mln.organization_id = mmt.organization_id ' ||
687 ' and mln.lot_number = mtln.lot_number ' ||
688 ' UNION ALL' ||
689 ' select distinct mln.* ' ||
690 ' from mtl_lot_numbers mln , ' ||
691 ' mtl_transaction_lot_numbers mtln , ' ||
692 ' mtl_material_transactions mmt , ' ||
693 ' po_requisition_lines_all prl , ' ||
694 ' oe_order_lines_all sol,' ||
695 ' rcv_shipment_headers rsh, ' ||
696 ' rcv_shipment_lines rsl ' ||
700 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
697 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
698 ' and rsh.ship_to_org_id = ' || l_org_id ||
699 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
701 ' and prl.source_type_code = ''INVENTORY'' ' ||
702 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
703 ' and sol.source_document_type_id = 10 ' ||
704 ' and mmt.transaction_id = mtln.transaction_id ' ||
705 ' and mmt.trx_source_line_id = sol.line_id' ||
706 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
707 ' and mln.organization_id = mmt.organization_id ' ||
708 ' and mln.lot_number = mtln.lot_number ';
709
710
711 p_sql(28) := ' select distinct mtln.* ' ||
712 ' from mtl_transaction_lot_numbers mtln , ' ||
713 ' mtl_material_transactions mmt , ' ||
714 ' po_requisition_lines_all prl , ' ||
715 ' rcv_transactions rt,' ||
716 ' rcv_shipment_headers rsh, ' ||
717 ' rcv_shipment_lines rsl ' ||
718 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
719 ' and rsh.ship_to_org_id = ' || l_org_id ||
720 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
721 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
722 ' and prl.source_type_code = ''INVENTORY'' ' ||
723 ' and rt.requisition_line_id = prl.requisition_line_id' ||
724 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
725 ' and mmt.transaction_id = mtln.transaction_id' ||
726 ' UNION ALL' ||
727 ' select distinct mtln.* ' ||
728 ' from mtl_transaction_lot_numbers mtln , ' ||
729 ' mtl_material_transactions mmt , ' ||
730 ' po_requisition_lines_all prl , ' ||
731 ' oe_order_lines_all sol,' ||
732 ' rcv_shipment_headers rsh, ' ||
733 ' rcv_shipment_lines rsl ' ||
734 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
735 ' and rsh.ship_to_org_id = ' || l_org_id ||
736 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
737 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
738 ' and prl.source_type_code = ''INVENTORY'' ' ||
739 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
740 ' and sol.source_document_type_id = 10 ' ||
741 ' and mmt.trx_source_line_id = sol.line_id ' ||
742 ' and mmt.transaction_id = mtln.transaction_id ';
743
744
745
746
747 p_sql(29) := ' select distinct mtli.* ' ||
748 ' from mtl_transaction_lots_interface mtli , ' ||
749 ' mtl_transactions_interface mti , ' ||
750 ' po_requisition_lines_all prl , ' ||
751 ' rcv_shipment_headers rsh, ' ||
752 ' rcv_shipment_lines rsl ' ||
753 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
754 ' and rsh.ship_to_org_id = ' || l_org_id ||
755 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
756 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
757 ' and prl.source_type_code = ''INVENTORY'' ' ||
758 ' and mti.requisition_line_id = prl.requisition_line_id' ||
759 ' and mti.transaction_interface_id = mtli.transaction_interface_id' ||
760 ' UNION ALL' ||
761 ' select distinct mtli.* ' ||
762 ' from mtl_transaction_lots_interface mtli , ' ||
763 ' rcv_transactions_interface rti , ' ||
764 ' po_requisition_lines_all prl , ' ||
765 ' rcv_shipment_headers rsh, ' ||
766 ' rcv_shipment_lines rsl ' ||
767 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
768 ' and rsh.ship_to_org_id = ' || l_org_id ||
769 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
770 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
771 ' and prl.source_type_code = ''INVENTORY'' ' ||
772 ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
773 ' and mtli.product_code =''RCV'' ' ||
774 ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99) ';
775
776
777
778 p_sql(30) := ' select distinct mtlt.* ' ||
779 ' from mtl_transaction_lots_temp mtlt ,' ||
780 ' rcv_transactions_interface rti, ' ||
781 ' po_requisition_lines_all prl , ' ||
782 ' rcv_shipment_headers rsh, ' ||
783 ' rcv_shipment_lines rsl ' ||
784 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
785 ' and rsh.ship_to_org_id = ' || l_org_id ||
786 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
787 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
788 ' and prl.source_type_code = ''INVENTORY'' ' ||
789 ' and rti.interface_transaction_id = mtlt.product_transaction_id' ||
790 ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99) ' ||
791 ' UNION ALL' ||
792 ' select distinct mtlt.* ' ||
793 ' from mtl_transaction_lots_temp mtlt ,' ||
794 ' mtl_material_transactions_temp mmtt,' ||
795 ' po_requisition_lines_all prl , ' ||
796 ' oe_order_lines_all sol,' ||
797 ' rcv_shipment_headers rsh, ' ||
798 ' rcv_shipment_lines rsl ' ||
799 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
800 ' and rsh.ship_to_org_id = ' || l_org_id ||
801 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
802 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
803 ' and prl.source_type_code = ''INVENTORY'' ' ||
804 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
805 ' and sol.source_document_type_id = 10 ' ||
806 ' and mmtt.trx_source_line_id = sol.line_id ';
807
808
809 p_sql(31) := ' SELECT DISTINCT rls.* ' ||
810 ' FROM rcv_lots_supply rls , ' ||
811 ' rcv_shipment_headers rsh, ' ||
812 ' rcv_shipment_lines rsl' ||
813 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
814 ' and rsh.ship_to_org_id = ' || l_org_id ||
818
815 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
816 ' AND rsl.source_document_code = ''REQ'' ' ||
817 ' AND rsl.shipment_line_id = rls.shipment_line_id ';
819
820
821
822 p_sql(32) := ' SELECT DISTINCT rlt.* ' ||
823 ' FROM rcv_lot_transactions rlt ,' ||
824 ' rcv_shipment_headers rsh, ' ||
825 ' rcv_shipment_lines rsl ' ||
826 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
827 ' and rsh.ship_to_org_id = ' || l_org_id ||
828 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
829 ' AND rsl.shipment_line_id = rlt.shipment_line_id ' ||
830 ' AND rsl.source_document_code = ''REQ'' ';
831
832
833 p_sql(33) := ' SELECT DISTINCT rli.* ' ||
834 ' FROM rcv_lots_interface rli , ' ||
835 ' rcv_transactions_interface rti , ' ||
836 ' po_requisition_headers_all prh , ' ||
837 ' po_requisition_lines_all prl ,' ||
838 ' rcv_shipment_headers rsh, ' ||
839 ' rcv_shipment_lines rsl ' ||
840 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
841 ' and rsh.ship_to_org_id = ' || l_org_id ||
842 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
843 ' AND (rti.shipment_header_id = rsl.shipment_header_id OR ' ||
844 ' rti.shipment_num = rsh.shipment_num)' ||
845 ' AND rti.interface_transaction_id = rli.interface_transaction_id ';
846
847
848
849 p_sql(34) := ' SELECT DISTINCT msi.* ' ||
850 ' FROM mtl_system_items msi, ' ||
851 ' rcv_shipment_lines rsl, ' ||
852 ' rcv_shipment_headers rsh ' ||
853 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
854 ' and rsh.ship_to_org_id = ' || l_org_id ||
855 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
856 ' AND rsl.item_id = msi.inventory_item_id ' ||
857 ' AND rsh.ship_to_org_id = msi.organization_id ';
858
859
860
861 p_sql(35) := ' select distinct mtt.transaction_type_id , ' ||
862 ' mtt.transaction_type_name , ' ||
863 ' mtt.transaction_source_type_id , ' ||
864 ' mtt.transaction_action_id , ' ||
865 ' mtt.user_defined_flag , ' ||
866 ' mtt.disable_date ' ||
867 ' from mtl_transaction_types mtt , ' ||
868 ' mtl_material_transactions mmt , ' ||
869 ' po_requisition_lines_all prl , ' ||
870 ' po_requisition_headers_all prh ,' ||
871 ' rcv_transactions rt,' ||
872 ' rcv_shipment_headers rsh, ' ||
873 ' rcv_shipment_lines rsl ' ||
874 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
875 ' and rsh.ship_to_org_id = ' || l_org_id ||
876 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
877 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
878 ' and prl.source_type_code = ''INVENTORY'' ' ||
879 ' and rt.requisition_line_id = prl.requisition_line_id' ||
880 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
881 ' and mmt.transaction_type_id = mtt.transaction_type_id' ||
882 ' UNION ALL' ||
883 ' select distinct mtt.transaction_type_id , ' ||
884 ' mtt.transaction_type_name , ' ||
885 ' mtt.transaction_source_type_id , ' ||
886 ' mtt.transaction_action_id , ' ||
887 ' mtt.user_defined_flag , ' ||
888 ' mtt.disable_date ' ||
889 ' from mtl_transaction_types mtt , ' ||
890 ' mtl_material_transactions mmt , ' ||
891 ' po_requisition_lines_all prl , ' ||
892 ' po_requisition_headers_all prh,' ||
893 ' oe_order_lines_all sol,' ||
894 ' rcv_shipment_headers rsh, ' ||
895 ' rcv_shipment_lines rsl ' ||
896 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
897 ' and rsh.ship_to_org_id = ' || l_org_id ||
898 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
899 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
900 ' and prl.source_type_code = ''INVENTORY'' ' ||
901 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
902 ' and sol.source_document_type_id = 10 ' ||
903 ' and mmt.trx_source_line_id = sol.line_id ' ||
904 ' and mmt.transaction_type_id = mtt.transaction_type_id ';
905
906
907 p_sql(36) := ' SELECT DISTINCT ood.* ' ||
908 ' FROM org_organization_definitions ood, ' ||
909 ' rcv_shipment_lines rsl, ' ||
910 ' rcv_shipment_headers rsh ' ||
911 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
912 ' and rsh.ship_to_org_id = ' || l_org_id ||
913 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
914 ' AND (rsl.to_organization_id = ood.organization_id ' ||
915 ' OR rsl.from_organization_id = ood.organization_id) ';
916
917
918 p_sql(37) := ' SELECT DISTINCT mp.* ' ||
919 ' FROM mtl_parameters mp, ' ||
920 ' po_requisition_lines_all prl ,' ||
921 ' rcv_shipment_lines rsl, ' ||
922 ' rcv_shipment_headers rsh ' ||
923 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
924 ' and rsh.ship_to_org_id = ' || l_org_id ||
925 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
926 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
927 ' AND (rsl.from_organization_id = mp.organization_id ' ||
928 ' OR rsl.to_organization_id = mp.organization_id ) ';
929
930
931
932 p_sql(38) := ' SELECT DISTINCT miop.* ' ||
933 ' FROM mtl_interorg_parameters miop ' ||
934 ' WHERE exists ' ||
935 ' (SELECT DISTINCT 1 ' ||
936 ' FROM rcv_shipment_lines rsl, ' ||
937 ' rcv_shipment_headers rsh ' ||
941 ' AND rsl.from_organization_id = miop.from_organization_id ' ||
938 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
939 ' and rsh.ship_to_org_id = ' || l_org_id ||
940 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
942 ' AND rsl.to_organization_id = miop.to_organization_id' ||
943 ' ) ';
944
945
946 p_sql(39) := ' SELECT DISTINCT rp.* ' ||
947 ' FROM rcv_parameters rp ' ||
948 ' WHERE exists ' ||
949 ' (SELECT DISTINCT 1 ' ||
950 ' FROM po_requisition_lines_all prl , ' ||
951 ' rcv_shipment_lines rsl, ' ||
952 ' rcv_shipment_headers rsh ' ||
953 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
954 ' and rsh.ship_to_org_id = ' || l_org_id ||
955 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
956 ' AND (rsl.to_organization_id = rp.organization_id ' ||
957 ' OR rsl.from_organization_id = rp.organization_id )' ||
958 ' ) ';
959
960
961
962 p_sql(40) := ' SELECT DISTINCT lookup_code , ' ||
963 ' meaning , ' ||
964 ' enabled_flag , ' ||
965 ' start_date_active , ' ||
966 ' end_date_active ' ||
967 ' FROM mfg_lookups ' ||
968 ' WHERE lookup_type = ''MTL_LOT_CONTROL'' ';
969
970
971
972 p_sql(41) := ' SELECT DISTINCT lookup_code , ' ||
973 ' meaning , ' ||
974 ' enabled_flag , ' ||
975 ' start_date_active , ' ||
976 ' end_date_active ' ||
977 ' FROM mfg_lookups ' ||
978 ' WHERE lookup_type = ''MTL_LOT_GENERATION'' ';
979
980
981
982 p_sql(42) := ' SELECT DISTINCT lookup_code , ' ||
983 ' meaning , ' ||
984 ' enabled_flag , ' ||
985 ' start_date_active , ' ||
986 ' end_date_active ' ||
987 ' FROM mfg_lookups ' ||
988 ' WHERE lookup_type = ''MTL_LOT_UNIQUENESS'' ';
989
990
991
992 p_sql(43) := ' SELECT DISTINCT lookup_type , ' ||
993 ' lookup_code , ' ||
994 ' meaning , ' ||
995 ' enabled_flag , ' ||
996 ' start_date_active , ' ||
997 ' end_date_active ' ||
998 ' FROM mfg_lookups ' ||
999 ' WHERE lookup_type = ''MTL_SERIAL_NUMBER'' ';
1000
1001
1002 p_sql(44) := ' SELECT DISTINCT lookup_type , ' ||
1003 ' lookup_code , ' ||
1004 ' meaning , ' ||
1005 ' enabled_flag , ' ||
1006 ' start_date_active , ' ||
1007 ' end_date_active ' ||
1008 ' FROM mfg_lookups ' ||
1009 ' WHERE lookup_type = ''MTL_SERIAL_NUMBER_TYPE'' ';
1010
1011
1012 p_sql(45) := ' SELECT DISTINCT lookup_type , ' ||
1013 ' lookup_code , ' ||
1014 ' meaning , ' ||
1015 ' enabled_flag , ' ||
1016 ' start_date_active , ' ||
1017 ' end_date_active ' ||
1018 ' FROM mfg_lookups ' ||
1019 ' WHERE lookup_type = ''MTL_SERIAL_GENERATION'' ';
1020
1021
1022 p_sql(46) := ' SELECT DISTINCT lookup_type , ' ||
1023 ' lookup_code , ' ||
1024 ' meaning , ' ||
1025 ' enabled_flag , ' ||
1026 ' start_date_active , ' ||
1027 ' end_date_active ' ||
1028 ' FROM mfg_lookups ' ||
1029 ' WHERE lookup_type = ''SERIAL_NUM_STATUS'' ';
1030
1031
1032 RETURN;
1033 END;
1034
1035
1036 PROCEDURE req_line_receipt_shipment_sql(p_ou_id IN NUMBER, p_req_num IN VARCHAR2, p_line_num IN NUMBER, p_shipment_num
1037 IN VARCHAR2, p_receipt_num IN VARCHAR2, p_org_id IN NUMBER, p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
1038 l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;
1039 l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;
1040 l_line_num po_requisition_lines_all.line_num%TYPE := Nvl(p_line_num,-99);
1041 l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
1042 l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
1043 l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;
1044 l_count NUMBER := 0;
1045
1046 BEGIN
1047
1048
1049
1050
1051
1052 p_sql(1) := ' SELECT prh.* ' ||
1053 ' FROM po_requisition_headers_all prh, ' ||
1054 ' po_requisition_lines_all prl ' ||
1055 ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
1056 ' and prh.org_id = ' || l_ou_id ||
1057 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
1058 ' AND prl.source_type_code = ''INVENTORY'' ';
1059
1060
1061
1062
1063
1064
1065 p_sql(2) := ' SELECT prl.* ' ||
1066 ' FROM po_requisition_headers_all prh, ' ||
1067 ' po_requisition_lines_all prl, ' ||
1068 ' rcv_shipment_headers rsh, ' ||
1069 ' rcv_shipment_lines rsl ' ||
1070 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1071 ' and rsh.ship_to_org_id = ' || l_org_id ||
1072 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1073 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1074 ' AND prl.source_type_code = ''INVENTORY'' ' ||
1075 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1076 ' and prh.org_id = ' || l_ou_id ||
1077 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1078 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1079 ' ORDER BY prl.requisition_line_id ';
1080
1081
1082
1083
1084
1085
1086 p_sql(3) := ' SELECT prd.* ' ||
1087 ' FROM po_requisition_headers_all prh, ' ||
1088 ' po_requisition_lines_all prl, ' ||
1092 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1089 ' po_req_distributions_all prd, ' ||
1090 ' rcv_shipment_headers rsh, ' ||
1091 ' rcv_shipment_lines rsl ' ||
1093 ' and rsh.ship_to_org_id = ' || l_org_id ||
1094 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1095 ' and prh.org_id = ' || l_ou_id ||
1096 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1097 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1098 ' AND prl.requisition_line_id = prd.requisition_line_id ' ||
1099 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1100 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1101 ' AND prl.source_type_code = ''INVENTORY'' ';
1102
1103
1104
1105
1106
1107 p_sql(4) := ' SELECT oel.* ' ||
1108 ' FROM oe_order_lines_all oel, ' ||
1109 ' po_requisition_lines_all prl, ' ||
1110 ' po_requisition_headers_all prh,' ||
1111 ' rcv_shipment_headers rsh, ' ||
1112 ' rcv_shipment_lines rsl ' ||
1113 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1114 ' and rsh.ship_to_org_id = ' || l_org_id ||
1115 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1116 ' and prh.org_id = ' || l_ou_id ||
1117 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1118 ' AND prl.requisition_line_id = oel.source_document_line_id ' ||
1119 ' AND oel.source_document_type_id = 10 ' ||
1120 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1121 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1122 ' AND prl.source_type_code = ''INVENTORY''' ||
1123 ' ORDER BY oel.line_id ';
1124
1125
1126 p_sql(5) := ' SELECT wsh.* ' ||
1127 ' FROM wsh_delivery_details wsh , ' ||
1128 ' wsh_delivery_assignments wda , ' ||
1129 ' wsh_new_deliveries wnd , ' ||
1130 ' oe_order_lines_all sol , ' ||
1131 ' po_requisition_lines_all prl , ' ||
1132 ' po_requisition_headers_all prh , ' ||
1133 ' rcv_shipment_headers rsh, ' ||
1134 ' rcv_shipment_lines rsl ' ||
1135 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1136 ' and rsh.ship_to_org_id = ' || l_org_id ||
1137 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1138 ' and prh.org_id = ' || l_ou_id ||
1139 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1140 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1141 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1142 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
1143 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1144 ' AND sol.source_document_type_id = 10 ' ||
1145 ' AND wsh.source_line_id = sol.line_id ' ||
1146 ' AND wsh.delivery_detail_id = wda.delivery_detail_id ' ||
1147 ' AND wda.delivery_id = wnd.delivery_id ' ||
1148 ' UNION ALL ' ||
1149 ' SELECT wsh.* ' ||
1150 ' FROM wsh_delivery_details wsh , ' ||
1151 ' mtl_transactions_interface mti , ' ||
1152 ' po_requisition_lines_all prl , ' ||
1153 ' po_requisition_headers_all prh , ' ||
1154 ' oe_order_lines_all sol, ' ||
1155 ' rcv_shipment_headers rsh, ' ||
1156 ' rcv_shipment_lines rsl ' ||
1157 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1158 ' and rsh.ship_to_org_id = ' || l_org_id ||
1159 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1160 ' and prh.org_id = ' || l_ou_id ||
1161 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1162 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1163 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1164 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
1165 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1166 ' AND sol.source_document_type_id = 10 ' ||
1167 ' AND mti.trx_source_line_id = sol.line_id ' ||
1168 ' AND mti.picking_line_id = wsh.delivery_detail_id ';
1169
1170
1171 p_sql(6) := ' SELECT rhi.* ' ||
1172 ' FROM rcv_headers_interface rhi,' ||
1173 ' rcv_shipment_headers rsh ' ||
1174 ' WHERE rhi.receipt_header_id = rsh.shipment_header_id ' ||
1175 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1176 ' and rsh.ship_to_org_id = ' || l_org_id ;
1177
1178
1179 p_sql(7) := ' SELECT DISTINCT rti.*' ||
1180 ' FROM rcv_transactions_interface rti ' ||
1181 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
1182 ' and rti.to_organization_id = ' || l_org_id;
1183
1184
1185 p_sql(8) := ' SELECT DISTINCT pie.* ' ||
1186 ' FROM po_interface_errors pie ' ||
1187 ' WHERE pie.interface_transaction_id IN ' ||
1188 ' ( SELECT DISTINCT rti.interface_transaction_id ' ||
1189 ' FROM rcv_transactions_interface rti' ||
1190 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
1191 ' and rti.to_organization_id = ' || l_org_id ||
1192 ' OR pie.interface_line_id IN ' ||
1193 ' ( SELECT DISTINCT rti.interface_transaction_id' ||
1194 ' FROM rcv_transactions_interface rti ' ||
1195 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
1196 ' and rti.to_organization_id = ' || l_org_id || '))';
1197
1198 p_sql(9) := ' SELECT DISTINCT rsh.* ' ||
1199 ' FROM rcv_shipment_headers rsh , ' ||
1200 ' rcv_shipment_lines rsl , ' ||
1201 ' po_requisition_headers_all prh , ' ||
1202 ' po_requisition_lines_all prl ' ||
1203 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1204 ' and rsh.ship_to_org_id = ' || l_org_id ||
1205 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1206 ' and prh.org_id = ' || l_ou_id ||
1210 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1207 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1208 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1209 ' AND prl.source_type_code = ''INVENTORY''' ||
1211 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1212 ' AND rsh.receipt_source_code = ''INTERNAL ORDER'' ';
1213
1214
1215
1216 p_sql(10) := ' SELECT rsl.* ' ||
1217 ' FROM rcv_shipment_headers rsh, ' ||
1218 ' rcv_shipment_lines rsl , ' ||
1219 ' po_requisition_headers_all prh , ' ||
1220 ' po_requisition_lines_all prl ' ||
1221 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1222 ' and rsh.ship_to_org_id = ' || l_org_id ||
1223 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1224 ' and prh.org_id = ' || l_ou_id ||
1225 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1226 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1227 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1228 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1229 ' AND prl.source_type_code = ''INVENTORY''' ||
1230 ' AND rsl.source_document_code = ''REQ'' ';
1231
1232 p_sql(11) := ' SELECT rt.* ' ||
1233 ' FROM rcv_transactions rt , ' ||
1234 ' po_requisition_headers_all prh , ' ||
1235 ' po_requisition_lines_all prl ,' ||
1236 ' rcv_shipment_headers rsh, ' ||
1237 ' rcv_shipment_lines rsl ' ||
1238 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1239 ' and rsh.ship_to_org_id = ' || l_org_id ||
1240 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1241 ' and prh.org_id = ' || l_ou_id ||
1242 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1243 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1244 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1245 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1246 ' AND prl.source_type_code = ''INVENTORY'' ' ||
1247 ' AND rt.requisition_line_id = prl.requisition_line_id ' ||
1248 ' AND rt.shipment_line_id = rsl.shipment_line_id ';
1249
1250 p_sql(12) := ' SELECT ms.* ' ||
1251 ' FROM mtl_supply ms , ' ||
1252 ' po_requisition_headers_all prh , ' ||
1253 ' po_requisition_lines_all prl ,' ||
1254 ' rcv_shipment_headers rsh, ' ||
1255 ' rcv_shipment_lines rsl ' ||
1256 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1257 ' and rsh.ship_to_org_id = ' || l_org_id ||
1258 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1259 ' and prh.org_id = ' || l_ou_id ||
1260 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1261 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1262 ' AND prl.source_type_code = ''INVENTORY''' ||
1263 ' AND rsh.shipment_header_id = rsl.shipment_header_id' ||
1264 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1265 ' AND ms.shipment_header_id = rsh.shipment_header_id ';
1266
1267
1268
1269
1270
1271
1272 p_sql(13) := ' SELECT rs.* ' ||
1273 ' FROM rcv_supply rs , ' ||
1274 ' po_requisition_headers_all prh ,' ||
1275 ' po_requisition_lines_all prl,' ||
1276 ' rcv_shipment_headers rsh, ' ||
1277 ' rcv_shipment_lines rsl ' ||
1278 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1279 ' and rsh.ship_to_org_id = ' || l_org_id ||
1280 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1281 ' and prh.org_id = ' || l_ou_id ||
1282 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1283 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1284 ' AND prl.source_type_code = ''INVENTORY'' ' ||
1285 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1286 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1287 ' AND rs.shipment_header_id = rsh.shipment_header_id ';
1288
1289 p_sql(14) := ' SELECT mtrl.* ' ||
1290 ' FROM mtl_txn_request_lines mtrl, ' ||
1291 ' rcv_shipment_headers rsh, ' ||
1292 ' rcv_shipment_lines rsl, ' ||
1293 ' po_requisition_headers_all prh, ' ||
1294 ' po_requisition_lines_all prl ' ||
1295 ' WHERE rsh.shipment_num = '|| '''' || l_shipment_num || '''' ||
1296 ' and rsh.ship_to_org_id = '|| l_org_id ||
1297 ' and prh.segment1 = '|| '''' || l_req_num || '''' ||
1298 ' and prh.org_id = '|| l_ou_id ||
1299 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1300 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1301 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1302 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1303 ' AND prl.source_type_code = ''INVENTORY'' ' ||
1304 ' and nvl(mtrl.revision,0)=nvl(prl.item_revision,0) ' ||
1305 ' and mtrl.revision=rsl.item_revision ' ||
1306 ' and mtrl.organization_id=rsl.to_organization_id ' ||
1307 ' and mtrl.transaction_type_id=52'||
1308 ' and mtrl.line_status=7';
1309
1310 /*' SELECT mtrl.* ' ||
1311 ' FROM mtl_txn_request_lines mtrl, ' ||
1312 ' rcv_shipment_headers rsh, ' ||
1313 ' rcv_shipment_lines rsl, ' ||
1314 ' po_requisition_headers_all prh, ' ||
1315 ' po_requisition_lines_all prl ' ||
1316 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1317 ' and rsh.ship_to_org_id = ' || l_org_id ||
1318 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1319 ' and prh.org_id = ' || l_ou_id ||
1320 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1321 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1322 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1326 ' AND mtrl.reference = ''SHIPMENT_LINE_ID'' ' ||
1323 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1324 ' AND prl.source_type_code = ''INVENTORY'' ' ||
1325 ' AND mtrl.reference_id = rsl.shipment_line_id ' ||
1327 ' AND rsl.source_document_code = ''REQ'' '; */
1328
1329 p_sql(15) := ' SELECT MTI.* ' ||
1330 ' FROM MTL_TRANSACTIONS_INTERFACE MTI, ' ||
1331 ' PO_REQUISITION_LINES_ALL PRL, ' ||
1332 ' PO_REQUISITION_HEADERS_ALL PRH, ' ||
1333 ' OE_ORDER_LINES_ALL SOL, ' ||
1334 ' RCV_SHIPMENT_HEADERS RSH, ' ||
1335 ' RCV_SHIPMENT_LINES RSL ' ||
1336 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1337 ' and rsh.ship_to_org_id = ' || l_org_id ||
1338 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1339 ' and prh.org_id = ' || l_ou_id ||
1340 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1341 ' AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID ' ||
1342 ' AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID ' ||
1343 ' AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID ' ||
1344 ' AND prl.source_type_code = ''INVENTORY'' ' ||
1345 ' AND SOL.SOURCE_DOCUMENT_TYPE_ID = 10 ' ||
1346 ' AND SOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID ' ||
1347 ' AND MTI.TRX_SOURCE_LINE_ID = SOL.LINE_ID ' ||
1348 ' AND MTI.SOURCE_CODE = ''ORDER ENTRY'' ';
1349
1350 p_sql(16) := ' SELECT mmtt.* ' ||
1351 ' FROM mtl_material_transactions_temp mmtt , ' ||
1352 ' po_requisition_lines_all prl , ' ||
1353 ' po_requisition_headers_all prh, ' ||
1354 ' rcv_shipment_headers rsh, ' ||
1355 ' rcv_shipment_lines rsl,' ||
1356 ' rcv_transactions rt ' ||
1357 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1358 ' and rsh.ship_to_org_id = ' || l_org_id ||
1359 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1360 ' and prh.org_id = ' || l_ou_id ||
1361 ' AND prh.segment1 = ''229'' ' ||
1362 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1363 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1364 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
1365 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1366 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1367 ' and prl.source_type_code = ''INVENTORY''' ||
1368 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1369 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
1370 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
1371 ' UNION ALL' ||
1372 ' SELECT mmtt.* ' ||
1373 ' FROM mtl_material_transactions_temp mmtt , ' ||
1374 ' po_requisition_lines_all prl , ' ||
1375 ' po_requisition_headers_all prh, ' ||
1376 ' rcv_shipment_headers rsh, ' ||
1377 ' rcv_shipment_lines rsl,' ||
1378 ' oe_order_lines_all sol ' ||
1379 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1380 ' and rsh.ship_to_org_id = ' || l_org_id ||
1381 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1382 ' and prh.org_id = ' || l_ou_id ||
1383 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1384 ' and prl.source_type_code = ''INVENTORY'' ' ||
1385 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1386 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1387 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1388 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1389 ' and sol.source_document_type_id = 10 ' ||
1390 ' and sol.source_document_line_id = prl.requisition_line_id' ||
1391 ' and mmtt.trx_source_line_id = sol.line_id ';
1392
1393
1394
1395
1396 p_sql(17) := ' SELECT mmt.* ' ||
1397 ' FROM mtl_material_transactions mmt , ' ||
1398 ' po_requisition_lines_all prl , ' ||
1399 ' po_requisition_headers_all prh, ' ||
1400 ' rcv_shipment_headers rsh, ' ||
1401 ' rcv_shipment_lines rsl,' ||
1402 ' rcv_transactions rt ' ||
1403 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1404 ' and rsh.ship_to_org_id = ' || l_org_id ||
1405 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1406 ' and prh.org_id = ' || l_ou_id ||
1407 ' AND prh.segment1 = ''229'' ' ||
1408 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1409 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1410 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
1411 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1412 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1413 ' and prl.source_type_code = ''INVENTORY''' ||
1414 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
1415 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1416 ' UNION ALL' ||
1417 ' SELECT mmt.* ' ||
1418 ' FROM mtl_material_transactions mmt , ' ||
1419 ' po_requisition_lines_all prl , ' ||
1420 ' po_requisition_headers_all prh, ' ||
1421 ' rcv_shipment_headers rsh, ' ||
1422 ' rcv_shipment_lines rsl,' ||
1423 ' oe_order_lines_all sol ' ||
1424 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1425 ' and rsh.ship_to_org_id = ' || l_org_id ||
1426 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1427 ' and prh.org_id = ' || l_ou_id ||
1428 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1429 ' and prl.source_type_code = ''INVENTORY'' ' ||
1430 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1431 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1432 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1433 ' and sol.source_document_type_id = 10 ' ||
1437
1434 ' and sol.source_document_line_id = prl.requisition_line_id' ||
1435 ' and mmt.trx_source_line_id = sol.line_id ' ||
1436 ' and mmt.transaction_action_id = 21';
1438
1439
1440 p_sql(18) := ' SELECT mr.* ' ||
1441 ' FROM mtl_reservations mr , ' ||
1442 ' oe_order_lines_all sol , ' ||
1443 ' po_requisition_lines_all prl , ' ||
1444 ' po_requisition_headers_all prh, ' ||
1445 ' rcv_shipment_headers rsh, ' ||
1446 ' rcv_shipment_lines rsl ' ||
1447 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1448 ' and rsh.ship_to_org_id = ' || l_org_id ||
1449 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1450 ' and prh.org_id = ' || l_ou_id ||
1451 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1452 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1453 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1454 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1455 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1456 ' AND sol.source_document_type_id = 10 ' ||
1457 ' AND mr.demand_source_line_id = sol.line_id ' ||
1458 ' AND mr.demand_source_type_id = 8 ' ||
1459 ' UNION ALL ' ||
1460 ' SELECT mr.* ' ||
1461 ' FROM mtl_reservations mr , ' ||
1462 ' mtl_transactions_interface mti , ' ||
1463 ' po_requisition_lines_all prl , ' ||
1464 ' po_requisition_headers_all prh , ' ||
1465 ' oe_order_lines_all sol ,' ||
1466 ' rcv_shipment_headers rsh, ' ||
1467 ' rcv_shipment_lines rsl ' ||
1468 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1469 ' and rsh.ship_to_org_id = ' || l_org_id ||
1470 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1471 ' and prh.org_id = ' || l_ou_id ||
1472 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1473 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1474 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1475 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1476 ' AND sol.source_document_type_id = 10 ' ||
1477 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1478 ' AND mti.trx_source_line_id = sol.line_id ' ||
1479 ' AND mr.demand_source_line_id = mti.trx_source_line_id ';
1480
1481 p_sql(19) := ' SELECT md.* ' ||
1482 ' FROM mtl_demand md , ' ||
1483 ' oe_order_lines_all sol , ' ||
1484 ' po_requisition_lines_all prl , ' ||
1485 ' po_requisition_headers_all prh , ' ||
1486 ' rcv_shipment_headers rsh, ' ||
1487 ' rcv_shipment_lines rsl ' ||
1488 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1489 ' and rsh.ship_to_org_id = ' || l_org_id ||
1490 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1491 ' and prh.org_id = ' || l_ou_id ||
1492 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1493 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1494 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1495 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1496 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1497 ' AND sol.source_document_type_id = 10 ' ||
1498 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
1499 ' AND md.demand_source_line = sol.line_id ' ||
1500 ' AND md.demand_source_type = 8 ' ||
1501 ' UNION ALL ' ||
1502 ' SELECT md.* ' ||
1503 ' FROM mtl_demand md , ' ||
1504 ' mtl_transactions_interface mti , ' ||
1505 ' po_requisition_lines_all prl , ' ||
1506 ' po_requisition_headers_all prh , ' ||
1507 ' oe_order_lines_all sol , ' ||
1508 ' rcv_shipment_headers rsh, ' ||
1509 ' rcv_shipment_lines rsl ' ||
1510 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1511 ' and rsh.ship_to_org_id = ' || l_org_id ||
1512 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1513 ' and prh.org_id = ' || l_ou_id ||
1514 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1515 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1516 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1517 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1518 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
1519 ' AND sol.source_document_type_id = 10 ' ||
1520 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1521 ' AND mti.trx_source_line_id = sol.line_id ' ||
1522 ' AND md.demand_source_line = mti.source_line_id ';
1523
1524
1525
1526
1527
1528
1529 p_sql(20) := ' select distinct msn.* ' ||
1530 ' from mtl_serial_numbers msn , ' ||
1531 ' mtl_material_transactions mmt , ' ||
1532 ' po_requisition_lines_all prl , ' ||
1533 ' po_requisition_headers_all prh ,' ||
1534 ' rcv_shipment_headers rsh, ' ||
1535 ' rcv_shipment_lines rsl ,' ||
1536 ' rcv_transactions rt' ||
1537 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1538 ' and rsh.ship_to_org_id = ' || l_org_id ||
1539 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1540 ' and prh.org_id = ' || l_ou_id ||
1541 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1542 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1543 ' and prl.source_type_code = ''INVENTORY''' ||
1544 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1545 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1546 ' AND rt.requisition_line_id = prl.requisition_line_id ' ||
1547 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1548 ' and mmt.transaction_id = msn.last_transaction_id' ||
1549 ' UNION ALL' ||
1553 ' po_requisition_lines_all prl , ' ||
1550 ' select distinct msn.* ' ||
1551 ' from mtl_serial_numbers msn , ' ||
1552 ' mtl_material_transactions mmt , ' ||
1554 ' po_requisition_headers_all prh ,' ||
1555 ' oe_order_lines_all sol,' ||
1556 ' rcv_shipment_headers rsh, ' ||
1557 ' rcv_shipment_lines rsl ' ||
1558 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1559 ' and rsh.ship_to_org_id = ' || l_org_id ||
1560 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1561 ' and prh.org_id = ' || l_ou_id ||
1562 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1563 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1564 ' and prl.source_type_code = ''INVENTORY''' ||
1565 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1566 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1567 ' and sol.source_document_type_id = 10 ' ||
1568 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
1569 ' and mmt.trx_source_line_id = sol.line_id ' ||
1570 ' and mmt.rcv_transaction_id is null ' ||
1571 ' and mmt.transaction_id = msn.last_transaction_id ';
1572
1573
1574 p_sql(21) := ' select DISTINCT msnt.*' ||
1575 ' from po_requisition_lines_all prl ,' ||
1576 ' po_requisition_headers_all prh ,' ||
1577 ' mtl_serial_numbers_temp msnt ,' ||
1578 ' mtl_system_items msi,' ||
1579 ' rcv_transactions_interface rti,' ||
1580 ' rcv_shipment_headers rsh, ' ||
1581 ' rcv_shipment_lines rsl ' ||
1582 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1583 ' and rsh.ship_to_org_id = ' || l_org_id ||
1584 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1585 ' and prh.org_id = ' || l_ou_id ||
1586 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1587 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
1588 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1589 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1590 ' and prl.source_type_code = ''INVENTORY''' ||
1591 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
1592 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
1593 ' )' ||
1594 ' and rti.interface_transaction_id = msnt.transaction_temp_id' ||
1595 ' and msi.inventory_item_id = rti.item_id' ||
1596 ' and msi.organization_id = rti.to_organization_id' ||
1597 ' and msi.serial_number_control_code <> 1' ||
1598 ' and msi.lot_control_code = 1' ||
1599 ' UNION ALL' ||
1600 ' select DISTINCT msnt.*' ||
1601 ' from po_requisition_lines_all prl ,' ||
1602 ' po_requisition_headers_all prh ,' ||
1603 ' mtl_serial_numbers_temp msnt ,' ||
1604 ' mtl_transaction_lots_temp mtlt,' ||
1605 ' mtl_system_items msi,' ||
1606 ' rcv_transactions_interface rti,' ||
1607 ' rcv_shipment_headers rsh, ' ||
1608 ' rcv_shipment_lines rsl ' ||
1609 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1610 ' and rsh.ship_to_org_id = ' || l_org_id ||
1611 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1612 ' and prh.org_id = ' || l_ou_id ||
1613 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1614 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1615 ' and prl.source_type_code = ''INVENTORY''' ||
1616 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1617 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1618 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
1619 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
1620 ' )' ||
1621 ' and rti.interface_transaction_id = mtlt.transaction_temp_id' ||
1622 ' and mtlt.SERIAL_TRANSACTION_TEMP_ID = msnt.transaction_temp_id' ||
1623 ' and msi.inventory_item_id = rti.item_id' ||
1624 ' and msi.organization_id = rti.to_organization_id' ||
1625 ' and msi.serial_number_control_code <> 1' ||
1626 ' and msi.lot_control_code <> 1' ||
1627 ' UNION ALL' ||
1628 ' select DISTINCT msnt.*' ||
1629 ' from po_requisition_lines_all prl,' ||
1630 ' po_requisition_headers_all prh,' ||
1631 ' mtl_serial_numbers_temp msnt,' ||
1632 ' mtl_system_items msi,' ||
1633 ' oe_order_lines_all sol,' ||
1634 ' mtl_material_transactions_temp mmtt,' ||
1635 ' rcv_shipment_headers rsh, ' ||
1636 ' rcv_shipment_lines rsl ' ||
1637 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1638 ' and rsh.ship_to_org_id = ' || l_org_id ||
1639 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1640 ' and prh.org_id = ' || l_ou_id ||
1641 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1642 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1643 ' and prl.source_type_code = ''INVENTORY''' ||
1644 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1645 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1646 ' and sol.source_document_line_id = prl.requisition_line_id' ||
1647 ' and sol.source_document_type_id = 10' ||
1648 ' and mmtt.trx_source_line_id = sol.line_id' ||
1649 ' and msnt.transaction_TEMP_id = mmtt.transaction_TEMP_id' ||
1650 ' and msi.inventory_item_id = mmtt.inventory_item_id' ||
1651 ' and msi.organization_id = mmtt.organization_id' ||
1652 ' and msi.serial_number_control_code <> 1' ||
1653 ' and msi.lot_control_code = 1' ||
1654 ' UNION ALL' ||
1655 ' select DISTINCT msnt.*' ||
1656 ' from po_requisition_lines_all prl,' ||
1657 ' po_requisition_headers_all prh,' ||
1661 ' oe_order_lines_all sol,' ||
1658 ' mtl_serial_numbers_temp msnt,' ||
1659 ' mtl_transaction_lots_temp mtlt,' ||
1660 ' mtl_system_items msi,' ||
1662 ' mtl_material_transactions_temp mmtt,' ||
1663 ' rcv_shipment_headers rsh, ' ||
1664 ' rcv_shipment_lines rsl ' ||
1665 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1666 ' and rsh.ship_to_org_id = ' || l_org_id ||
1667 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1668 ' and prh.org_id = ' || l_ou_id ||
1669 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1670 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1671 ' and prl.source_type_code = ''INVENTORY''' ||
1672 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1673 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1674 ' and sol.source_document_line_id = prl.requisition_line_id' ||
1675 ' and sol.source_document_type_id = 10' ||
1676 ' and mmtt.trx_source_line_id = sol.line_id' ||
1677 ' and mmtt.transaction_TEMP_id = mtlt.transaction_TEMP_id' ||
1678 ' and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id' ||
1679 ' and msi.inventory_item_id = mmtt.inventory_item_id' ||
1680 ' and msi.organization_id = mmtt.organization_id' ||
1681 ' and msi.serial_number_control_code <> 1' ||
1682 ' and msi.lot_control_code <> 1 ';
1683
1684
1685
1686 p_sql(22) := ' select distinct msni.* ' ||
1687 ' from rcv_transactions_interface rti ,' ||
1688 ' po_requisition_lines_all prl , ' ||
1689 ' po_requisition_headers_all prh , ' ||
1690 ' mtl_serial_numbers_interface msni ,' ||
1691 ' mtl_system_items msi,' ||
1692 ' rcv_shipment_headers rsh, ' ||
1693 ' rcv_shipment_lines rsl ' ||
1694 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1695 ' and rsh.ship_to_org_id = ' || l_org_id ||
1696 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1697 ' and prh.org_id = ' || l_ou_id ||
1698 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1699 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1700 ' and prl.source_type_code = ''INVENTORY''' ||
1701 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1702 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1703 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
1704 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
1705 ' )' ||
1706 ' and rti.interface_transaction_id = msni.product_transaction_id' ||
1707 ' and msi.inventory_item_id = rti.item_id' ||
1708 ' and msi.organization_id = rti.to_organization_id' ||
1709 ' and msi.serial_number_control_code <> 1' ||
1710 ' and msi.lot_control_code = 1' ||
1711 ' UNION ALL' ||
1712 ' select distinct msni.* ' ||
1713 ' from rcv_transactions_interface rti ,' ||
1714 ' po_requisition_lines_all prl , ' ||
1715 ' po_requisition_headers_all prh , ' ||
1716 ' mtl_serial_numbers_interface msni ,' ||
1717 ' mtl_transaction_lots_interface mtli,' ||
1718 ' mtl_system_items msi,' ||
1719 ' rcv_shipment_headers rsh, ' ||
1720 ' rcv_shipment_lines rsl ' ||
1721 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1722 ' and rsh.ship_to_org_id = ' || l_org_id ||
1723 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1724 ' and prh.org_id = ' || l_ou_id ||
1725 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1726 ' and prh.requisition_header_id = prl.requisition_header_id' ||
1727 ' and prl.source_type_code = ''INVENTORY''' ||
1728 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1729 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1730 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
1731 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
1732 ' )' ||
1733 ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
1734 ' and mtli.serial_transaction_temp_id = msni.transaction_interface_id ' ||
1735 ' and msi.inventory_item_id = rti.item_id' ||
1736 ' and msi.organization_id = rti.to_organization_id' ||
1737 ' and msi.serial_number_control_code <> 1' ||
1738 ' and msi.lot_control_code <> 1 ';
1739
1740
1741 p_sql(23) := ' select distinct mut.* ' ||
1742 ' from mtl_material_transactions mmt , ' ||
1743 ' po_requisition_lines_all prl , ' ||
1744 ' mtl_unit_transactions mut , ' ||
1745 ' mtl_system_items msi, ' ||
1746 ' rcv_transactions rt, ' ||
1747 ' rcv_shipment_headers rsh, ' ||
1748 ' rcv_shipment_lines rsl ' ||
1749 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1750 ' and rsh.ship_to_org_id = ' || l_org_id ||
1751 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1752 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1753 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1754 ' AND rt.requisition_line_id = prl.requisition_line_id ' ||
1755 ' and prl.source_type_code = ''INVENTORY''' ||
1756 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1757 ' and mmt.transaction_id = mut.transaction_id ' ||
1758 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
1759 ' and msi.organization_id = mmt.organization_id ' ||
1760 ' and msi.serial_number_control_code <> 1 ' ||
1761 ' and msi.lot_control_code = 1 ' ||
1762 ' union all ' ||
1763 ' select distinct mut.* ' ||
1764 ' from mtl_material_transactions mmt , ' ||
1765 ' po_requisition_lines_all prl , ' ||
1766 ' mtl_unit_transactions mut , ' ||
1767 ' mtl_system_items msi , ' ||
1771 ' rcv_shipment_lines rsl ' ||
1768 ' rcv_transactions rt , ' ||
1769 ' mtl_transaction_lot_numbers mtln, ' ||
1770 ' rcv_shipment_headers rsh, ' ||
1772 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1773 ' and rsh.ship_to_org_id = ' || l_org_id ||
1774 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1775 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1776 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1777 ' and prl.source_type_code = ''INVENTORY''' ||
1778 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
1779 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1780 ' and mtln.transaction_id = mmt.transaction_id ' ||
1781 ' and mut.transaction_id = mtln.serial_transaction_id ' ||
1782 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
1783 ' and msi.organization_id = mmt.organization_id ' ||
1784 ' and msi.serial_number_control_code <> 1 ' ||
1785 ' and msi.lot_control_code <> 1 ' ||
1786 ' union all ' ||
1787 ' select distinct mut.* ' ||
1788 ' from mtl_material_transactions mmt , ' ||
1789 ' po_requisition_lines_all prl , ' ||
1790 ' mtl_unit_transactions mut , ' ||
1791 ' mtl_system_items msi , ' ||
1792 ' oe_order_lines_all sol, ' ||
1793 ' rcv_shipment_headers rsh, ' ||
1794 ' rcv_shipment_lines rsl ' ||
1795 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1796 ' and rsh.ship_to_org_id = ' || l_org_id ||
1797 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1798 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1799 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1800 ' and prl.source_type_code = ''INVENTORY''' ||
1801 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
1802 ' and sol.source_document_type_id = 10 ' ||
1803 ' and mmt.trx_source_line_id = sol.line_id ' ||
1804 ' and mut.transaction_id = mmt.transaction_id ' ||
1805 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
1806 ' and msi.organization_id = mmt.organization_id ' ||
1807 ' and msi.serial_number_control_code <> 1 ' ||
1808 ' and msi.lot_control_code = 1 ' ||
1809 ' union all ' ||
1810 ' select distinct mut.* ' ||
1811 ' from mtl_material_transactions mmt , ' ||
1812 ' po_requisition_lines_all prl , ' ||
1813 ' mtl_unit_transactions mut , ' ||
1814 ' mtl_system_items msi , ' ||
1815 ' oe_order_lines_all sol , ' ||
1816 ' mtl_transaction_lot_numbers mtln, ' ||
1817 ' rcv_shipment_headers rsh, ' ||
1818 ' rcv_shipment_lines rsl ' ||
1819 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1820 ' and rsh.ship_to_org_id = ' || l_org_id ||
1821 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1822 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1823 ' and prl.source_type_code = ''INVENTORY'' ' ||
1824 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1825 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
1826 ' and sol.source_document_type_id = 10 ' ||
1827 ' and mmt.trx_source_line_id = sol.line_id ' ||
1828 ' and mtln.transaction_id = mmt.transaction_id ' ||
1829 ' and mut.transaction_id = mtln.serial_transaction_id ' ||
1830 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
1831 ' and msi.organization_id = mmt.organization_id ' ||
1832 ' and msi.serial_number_control_code <> 1 ' ||
1833 ' and msi.lot_control_code <> 1 ';
1834
1835
1836
1837
1838
1839
1840 p_sql(24) := ' SELECT rss.* ' ||
1841 ' FROM rcv_serials_supply rss , ' ||
1842 ' rcv_shipment_lines rsl , ' ||
1843 ' po_requisition_headers_all prh , ' ||
1844 ' po_requisition_lines_all prl , ' ||
1845 ' rcv_shipment_headers rsh ' ||
1846 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1847 ' and rsh.ship_to_org_id = ' || l_org_id ||
1848 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1849 ' and prh.org_id = ' || l_ou_id ||
1850 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1851 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1852 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1853 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1854 ' AND rss.shipment_line_id = rsl.shipment_line_id ' ||
1855 ' AND rsl.source_document_code = ''REQ'' ' ||
1856 ' ORDER BY rss.supply_type_code , ' ||
1857 ' rss.serial_num ';
1858
1859
1860 p_sql(25) := ' SELECT rst.* ' ||
1861 ' FROM rcv_serial_transactions rst , ' ||
1862 ' rcv_shipment_lines rsl , ' ||
1863 ' po_requisition_headers_all prh , ' ||
1864 ' po_requisition_lines_all prl , ' ||
1865 ' rcv_shipment_headers rsh ' ||
1866 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1867 ' and rsh.ship_to_org_id = ' || l_org_id ||
1868 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1869 ' and prh.org_id = ' || l_ou_id ||
1870 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1871 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1872 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1873 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1874 ' AND rsl.source_document_code = ''REQ'' ' ||
1875 ' AND rst.shipment_line_id = rsl.shipment_line_id ' ||
1876 ' ORDER BY rst.serial_transaction_type , ' ||
1877 ' rst.serial_num ';
1878
1879
1883 ' po_requisition_headers_all prh , ' ||
1880 p_sql(26) := ' SELECT rsi.* ' ||
1881 ' FROM rcv_serials_interface rsi , ' ||
1882 ' rcv_shipment_lines rsl , ' ||
1884 ' po_requisition_lines_all prl , ' ||
1885 ' rcv_shipment_headers rsh ,' ||
1886 ' rcv_transactions_interface rti' ||
1887 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1888 ' and rsh.ship_to_org_id = ' || l_org_id ||
1889 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1890 ' and prh.org_id = ' || l_ou_id ||
1891 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1892 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1893 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1894 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1895 ' AND prl.line_num = nvl( 1 ,prl.line_num) ' ||
1896 ' AND rsl.source_document_code = ''REQ'' ' ||
1897 ' AND rsi.item_id = rsl.item_id ' ||
1898 ' AND rsi.organization_id = rsl.to_organization_id ' ||
1899 ' AND rsi.interface_transaction_id = rti.interface_transaction_id' ||
1900 ' AND rti.shipment_line_id = rsl.shipment_line_id ';
1901
1902
1903
1904
1905 p_sql(27) := ' select distinct mln.* ' ||
1906 ' from mtl_lot_numbers mln ,' ||
1907 ' mtl_transaction_lot_numbers mtln ,' ||
1908 ' mtl_material_transactions mmt ,' ||
1909 ' po_requisition_lines_all prl ,' ||
1910 ' rcv_transactions rt,' ||
1911 ' rcv_shipment_headers rsh,' ||
1912 ' rcv_shipment_lines rsl ' ||
1913 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1914 ' and rsh.ship_to_org_id = ' || '''' || l_org_id || '''' ||
1915 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1916 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1917 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1918 ' and rt.requisition_line_id = prl.requisition_line_id' ||
1919 ' and prl.source_type_code = ''INVENTORY''' ||
1920 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
1921 ' and mmt.transaction_id = mtln.transaction_id ' ||
1922 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
1923 ' and mln.organization_id = mmt.organization_id ' ||
1924 ' and mln.lot_number = mtln.lot_number ' ||
1925 ' UNION ALL' ||
1926 ' select distinct mln.* ' ||
1927 ' from mtl_lot_numbers mln , ' ||
1928 ' mtl_transaction_lot_numbers mtln ,' ||
1929 ' mtl_material_transactions mmt , ' ||
1930 ' po_requisition_lines_all prl , ' ||
1931 ' oe_order_lines_all sol,' ||
1932 ' rcv_shipment_headers rsh, ' ||
1933 ' rcv_shipment_lines rsl ' ||
1934 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1935 ' and rsh.ship_to_org_id = ' || '''' || l_org_id || '''' ||
1936 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1937 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1938 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1939 ' and prl.source_type_code = ''INVENTORY''' ||
1940 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
1941 ' and sol.source_document_type_id = 10 ' ||
1942 ' and mmt.transaction_id = mtln.transaction_id ' ||
1943 ' and mmt.trx_source_line_id = sol.line_id' ||
1944 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
1945 ' and mln.organization_id = mmt.organization_id ' ||
1946 ' and mln.lot_number = mtln.lot_number ';
1947
1948
1949 p_sql(28) := ' select distinct mtln.*' ||
1950 ' from mtl_transaction_lot_numbers mtln ,' ||
1951 ' mtl_material_transactions mmt , ' ||
1952 ' po_requisition_lines_all prl , ' ||
1953 ' rcv_transactions rt,' ||
1954 ' rcv_shipment_headers rsh, ' ||
1955 ' rcv_shipment_lines rsl ' ||
1956 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1957 ' and rsh.ship_to_org_id = ' || l_org_id ||
1958 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1959 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1960 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1961 ' and prl.source_type_code = ''INVENTORY'' ' ||
1962 ' and rt.requisition_line_id = prl.requisition_line_id' ||
1963 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
1964 ' and mmt.transaction_id = mtln.transaction_id' ||
1965 ' UNION ALL' ||
1966 ' select distinct mtln.* ' ||
1967 ' from mtl_transaction_lot_numbers mtln , ' ||
1968 ' mtl_material_transactions mmt , ' ||
1969 ' po_requisition_lines_all prl , ' ||
1970 ' oe_order_lines_all sol,' ||
1971 ' rcv_shipment_headers rsh, ' ||
1972 ' rcv_shipment_lines rsl ' ||
1973 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1974 ' and rsh.ship_to_org_id = ' || l_org_id ||
1975 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1976 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1977 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1978 ' and prl.source_type_code = ''INVENTORY'' ' ||
1979 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
1980 ' and sol.source_document_type_id = 10 ' ||
1981 ' and mmt.trx_source_line_id = sol.line_id ' ||
1982 ' and mmt.transaction_id = mtln.transaction_id ';
1983
1984
1985
1986
1987 p_sql(29) := ' select distinct mtli.* ' ||
1988 ' from mtl_transaction_lots_interface mtli , ' ||
1989 ' mtl_transactions_interface mti , ' ||
1990 ' po_requisition_lines_all prl , ' ||
1991 ' rcv_shipment_headers rsh, ' ||
1992 ' rcv_shipment_lines rsl ' ||
1993 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1997 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
1994 ' and rsh.ship_to_org_id = ' || l_org_id ||
1995 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1996 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1998 ' and prl.source_type_code = ''INVENTORY'' ' ||
1999 ' and mti.requisition_line_id = prl.requisition_line_id ' ||
2000 ' and mti.transaction_interface_id = mtli.transaction_interface_id' ||
2001 ' UNION ALL' ||
2002 ' select distinct mtli.* ' ||
2003 ' from mtl_transaction_lots_interface mtli , ' ||
2004 ' rcv_transactions_interface rti , ' ||
2005 ' po_requisition_lines_all prl , ' ||
2006 ' rcv_shipment_headers rsh, ' ||
2007 ' rcv_shipment_lines rsl ' ||
2008 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2009 ' and rsh.ship_to_org_id = ' || l_org_id ||
2010 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2011 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
2012 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2013 ' and prl.source_type_code = ''INVENTORY'' ' ||
2014 ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
2015 ' and mtli.product_code =''RCV'' ' ||
2016 ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99) ';
2017
2018
2019
2020
2021
2022
2023 p_sql(30) := ' select distinct mtlt.* ' ||
2024 ' from mtl_transaction_lots_temp mtlt ,' ||
2025 ' rcv_transactions_interface rti, ' ||
2026 ' po_requisition_lines_all prl , ' ||
2027 ' rcv_shipment_headers rsh, ' ||
2028 ' rcv_shipment_lines rsl ' ||
2029 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2030 ' and rsh.ship_to_org_id = ' || l_org_id ||
2031 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2032 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
2033 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2034 ' and prl.source_type_code = ''INVENTORY'' ' ||
2035 ' and rti.interface_transaction_id = mtlt.product_transaction_id' ||
2036 ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99) ' ||
2037 ' UNION ALL' ||
2038 ' select distinct mtlt.* ' ||
2039 ' from mtl_transaction_lots_temp mtlt ,' ||
2040 ' mtl_material_transactions_temp mmtt, ' ||
2041 ' po_requisition_lines_all prl , ' ||
2042 ' oe_order_lines_all sol,' ||
2043 ' rcv_shipment_headers rsh, ' ||
2044 ' rcv_shipment_lines rsl ' ||
2045 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2046 ' and rsh.ship_to_org_id = ' || l_org_id ||
2047 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2048 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
2049 ' and prl.source_type_code = ''INVENTORY'' ' ||
2050 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2051 ' and sol.source_document_line_id = prl.requisition_line_id' ||
2052 ' and sol.source_document_type_id = 10 ' ||
2053 ' and mmtt.trx_source_line_id = sol.line_id ';
2054
2055
2056 p_sql(31) := ' SELECT rls.* ' ||
2057 ' FROM rcv_lots_supply rls , ' ||
2058 ' rcv_shipment_lines rsl , ' ||
2059 ' po_requisition_headers_all prh , ' ||
2060 ' po_requisition_lines_all prl , ' ||
2061 ' oe_order_lines_all sol , ' ||
2062 ' rcv_shipment_headers rsh ' ||
2063 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2064 ' and rsh.ship_to_org_id = ' || l_org_id ||
2065 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2066 ' and prh.org_id = ' || l_ou_id ||
2067 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2068 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2069 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2070 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2071 ' AND rsl.shipment_line_id = rls.shipment_line_id ' ||
2072 ' AND sol.source_document_type_id = 10 ' ||
2073 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
2074 ' AND rsl.source_document_code = ''REQ'' ';
2075
2076
2077
2078
2079
2080 p_sql(32) := ' SELECT rlt.* ' ||
2081 ' FROM rcv_lot_transactions rlt , ' ||
2082 ' rcv_shipment_lines rsl , ' ||
2083 ' po_requisition_headers_all prh , ' ||
2084 ' po_requisition_lines_all prl , ' ||
2085 ' rcv_shipment_headers rsh ' ||
2086 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2087 ' and rsh.ship_to_org_id = ' || l_org_id ||
2088 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2089 ' and prh.org_id = ' || l_ou_id ||
2090 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2091 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2092 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2093 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2094 ' AND rsl.shipment_line_id = rlt.shipment_line_id ' ||
2095 ' AND rsl.source_document_code = ''REQ'' ' ||
2096 ' and prl.source_type_code = ''INVENTORY'' ';
2097
2098
2099
2100 p_sql(33) := ' SELECT rli.* ' ||
2101 ' FROM rcv_lots_interface rli , ' ||
2102 ' rcv_transactions_interface rti , ' ||
2103 ' po_requisition_headers_all prh , ' ||
2104 ' po_requisition_lines_all prl ,' ||
2105 ' rcv_shipment_headers rsh,' ||
2106 ' rcv_shipment_lines rsl' ||
2107 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2108 ' and rsh.ship_to_org_id = ' || l_org_id ||
2109 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2110 ' and prh.org_id = ' || l_ou_id ||
2114 ' and prl.source_type_code = ''INVENTORY'' ' ||
2111 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2112 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2113 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2115 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2116 ' AND (rti.shipment_header_id = rsh.shipment_header_id OR' ||
2117 ' rti.shipment_num = rsh.shipment_num)' ||
2118 ' AND rti.interface_transaction_id = rli.interface_transaction_id ';
2119
2120
2121
2122 p_sql(34) := ' SELECT DISTINCT msi.* ' ||
2123 ' FROM po_requisition_headers_all prh, ' ||
2124 ' po_requisition_lines_all prl, ' ||
2125 ' mtl_system_items msi, ' ||
2126 ' rcv_shipment_headers rsh, ' ||
2127 ' rcv_shipment_lines rsl ' ||
2128 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2129 ' and rsh.ship_to_org_id = ' || l_org_id ||
2130 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2131 ' and prh.org_id = ' || l_ou_id ||
2132 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2133 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2134 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2135 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2136 ' AND prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'') ' ||
2137 ' AND prl.item_id = msi.inventory_item_id ' ||
2138 ' AND prl.destination_organization_id = msi.organization_id ';
2139
2140
2141 p_sql(35) := ' select distinct mtt.transaction_type_id , ' ||
2142 ' mtt.transaction_type_name , ' ||
2143 ' mtt.transaction_source_type_id , ' ||
2144 ' mtt.transaction_action_id , ' ||
2145 ' mtt.user_defined_flag , ' ||
2146 ' mtt.disable_date ' ||
2147 ' from mtl_transaction_types mtt , ' ||
2148 ' mtl_material_transactions mmt , ' ||
2149 ' po_requisition_lines_all prl , ' ||
2150 ' po_requisition_headers_all prh ,' ||
2151 ' rcv_transactions rt,' ||
2152 ' rcv_shipment_headers rsh, ' ||
2153 ' rcv_shipment_lines rsl ' ||
2154 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2155 ' and rsh.ship_to_org_id = ' || l_org_id ||
2156 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2157 ' and prh.org_id = ' || l_ou_id ||
2158 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2159 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2160 ' AND rsh.shipment_header_id = rsl.shipment_header_id' ||
2161 ' AND rt.requisition_line_id = prl.requisition_line_id' ||
2162 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
2163 ' and mmt.transaction_type_id = mtt.transaction_type_id' ||
2164 ' UNION ALL' ||
2165 ' select distinct mtt.transaction_type_id , ' ||
2166 ' mtt.transaction_type_name , ' ||
2167 ' mtt.transaction_source_type_id , ' ||
2168 ' mtt.transaction_action_id , ' ||
2169 ' mtt.user_defined_flag , ' ||
2170 ' mtt.disable_date ' ||
2171 ' from mtl_transaction_types mtt , ' ||
2172 ' mtl_material_transactions mmt , ' ||
2173 ' po_requisition_lines_all prl , ' ||
2174 ' po_requisition_headers_all prh,' ||
2175 ' oe_order_lines_all sol,' ||
2176 ' rcv_shipment_headers rsh, ' ||
2177 ' rcv_shipment_lines rsl ' ||
2178 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2179 ' and rsh.ship_to_org_id = ' || l_org_id ||
2180 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2181 ' and prh.org_id = ' || l_ou_id ||
2182 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2183 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2184 ' AND rsh.shipment_header_id = rsl.shipment_header_id' ||
2185 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
2186 ' and sol.source_document_type_id = 10 ' ||
2187 ' and mmt.trx_source_line_id = sol.line_id ' ||
2188 ' and mmt.transaction_type_id = mtt.transaction_type_id ';
2189
2190
2191
2192
2193 p_sql(36) := ' SELECT ood.* ' ||
2194 ' FROM org_organization_definitions ood ' ||
2195 ' WHERE exists ' ||
2196 ' (SELECT 1 ' ||
2197 ' FROM po_requisition_headers_all prh , ' ||
2198 ' po_requisition_lines_all prl , ' ||
2199 ' financials_system_params_all fsp , ' ||
2200 ' rcv_shipment_headers rsh, ' ||
2201 ' rcv_shipment_lines rsl ' ||
2202 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2203 ' and rsh.ship_to_org_id = ' || l_org_id ||
2204 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2205 ' and prh.org_id = ' || l_ou_id ||
2206 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2207 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2208 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2209 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2210 ' AND prh.type_lookup_code in (''INTERNAL'',''PURCHASE'') ' ||
2211 ' AND (prl.destination_organization_id = ood.organization_id ' ||
2212 ' OR prl.source_organization_id = ood.organization_id ' ||
2213 ' OR (prh.org_id = fsp.org_id ' ||
2214 ' AND ood.organization_id = fsp.inventory_organization_id ) ) ' ||
2215 ' ) ';
2216
2217
2218 p_sql(37) := ' SELECT DISTINCT mp.* ' ||
2219 ' FROM mtl_parameters mp , ' ||
2220 ' po_requisition_headers_all prh , ' ||
2221 ' po_requisition_lines_all prl ,' ||
2222 ' rcv_shipment_headers rsh, ' ||
2223 ' rcv_shipment_lines rsl ' ||
2224 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2225 ' and rsh.ship_to_org_id = ' || l_org_id ||
2226 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2227 ' and prh.org_id = ' || l_ou_id ||
2228 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2229 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2230 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2231 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2232 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
2233 ' AND (prl.destination_organization_id = mp.organization_id ' ||
2234 ' OR prl.source_organization_id = mp.organization_id ) ';
2235
2236
2237 p_sql(38) := ' SELECT miop.* ' ||
2238 ' FROM mtl_interorg_parameters miop ' ||
2239 ' WHERE exists ' ||
2240 ' (SELECT 1 ' ||
2241 ' FROM po_requisition_headers_all prh , ' ||
2242 ' po_requisition_lines_all prl, ' ||
2243 ' rcv_shipment_headers rsh, ' ||
2244 ' rcv_shipment_lines rsl ' ||
2245 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2246 ' and rsh.ship_to_org_id = ' || l_org_id ||
2247 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2248 ' and prh.org_id = ' || l_ou_id ||
2249 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2250 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2251 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2252 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2253 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
2254 ' AND (prl.destination_organization_id = miop.to_organization_id ' ||
2255 ' AND prl.source_organization_id = miop.from_organization_id )' ||
2256 ' ) ';
2257
2258
2259 p_sql(39) := ' SELECT rp.* ' ||
2260 ' FROM rcv_parameters rp ' ||
2261 ' WHERE exists ' ||
2262 ' (SELECT 1 ' ||
2263 ' FROM po_requisition_headers_all prh , ' ||
2264 ' po_requisition_lines_all prl , ' ||
2265 ' financials_system_params_all fsp, ' ||
2266 ' rcv_shipment_headers rsh, ' ||
2267 ' rcv_shipment_lines rsl ' ||
2268 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
2269 ' and rsh.ship_to_org_id = ' || l_org_id ||
2270 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
2271 ' and prh.org_id = ' || l_ou_id ||
2272 ' AND prl.line_num = nvl(' || l_line_num || ',-99) ' ||
2273 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
2274 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
2275 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
2276 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
2277 ' AND (prl.destination_organization_id = rp.organization_id ' ||
2278 ' OR prl.source_organization_id = rp.organization_id ' ||
2279 ' OR (prh.org_id = fsp.org_id ' ||
2280 ' AND rp.organization_id = fsp.inventory_organization_id ) ) ' ||
2281 ' ) ';
2282
2283 p_sql(40) := ' SELECT lookup_code , ' ||
2284 ' meaning , ' ||
2285 ' enabled_flag , ' ||
2286 ' start_date_active , ' ||
2287 ' end_date_active ' ||
2288 ' FROM mfg_lookups ' ||
2289 ' WHERE lookup_type = ''MTL_LOT_CONTROL'' ';
2290
2291 p_sql(41) := ' SELECT lookup_code , ' ||
2292 ' meaning , ' ||
2293 ' enabled_flag , ' ||
2294 ' start_date_active , ' ||
2295 ' end_date_active ' ||
2296 ' FROM mfg_lookups ' ||
2297 ' WHERE lookup_type = ''MTL_LOT_GENERATION'' ';
2298
2299
2300 p_sql(42) := ' SELECT lookup_code , ' ||
2301 ' meaning , ' ||
2302 ' enabled_flag , ' ||
2303 ' start_date_active , ' ||
2304 ' end_date_active ' ||
2305 ' FROM mfg_lookups ' ||
2306 ' WHERE lookup_type = ''MTL_LOT_UNIQUENESS'' ';
2307
2308
2309 p_sql(43) := ' SELECT lookup_type , ' ||
2310 ' lookup_code , ' ||
2311 ' meaning , ' ||
2312 ' enabled_flag , ' ||
2313 ' start_date_active , ' ||
2314 ' end_date_active ' ||
2315 ' FROM mfg_lookups ' ||
2316 ' WHERE lookup_type = ''MTL_SERIAL_NUMBER'' ';
2317
2318
2319 p_sql(44) := ' SELECT lookup_type , ' ||
2320 ' lookup_code , ' ||
2321 ' meaning , ' ||
2322 ' enabled_flag , ' ||
2323 ' start_date_active , ' ||
2324 ' end_date_active ' ||
2325 ' FROM mfg_lookups ' ||
2326 ' WHERE lookup_type = ''MTL_SERIAL_NUMBER_TYPE'' ';
2327
2328
2329 p_sql(45) := ' SELECT lookup_type , ' ||
2330 ' lookup_code , ' ||
2331 ' meaning , ' ||
2332 ' enabled_flag , ' ||
2333 ' start_date_active , ' ||
2334 ' end_date_active ' ||
2335 ' FROM mfg_lookups ' ||
2336 ' WHERE lookup_type = ''MTL_SERIAL_GENERATION'' ';
2337
2338
2339 p_sql(46) := ' SELECT lookup_type , ' ||
2340 ' lookup_code , ' ||
2341 ' meaning , ' ||
2342 ' enabled_flag , ' ||
2343 ' start_date_active , ' ||
2344 ' end_date_active ' ||
2345 ' FROM mfg_lookups ' ||
2346 ' WHERE lookup_type = ''SERIAL_NUM_STATUS'' ';
2347
2348
2349 RETURN;
2350 END;
2351
2352 END IO_DIAGNOSTICS2 ;