[Home] [Help]
PACKAGE BODY: APPS.IO_DIAGNOSTICS3
Source
1 PACKAGE BODY io_diagnostics3 AS
2 /* $Header: INVDIO3B.pls 120.0.12000000.1 2007/08/09 06:48:57 ssadasiv noship $ */
3
4 PROCEDURE req_receipt_shipment_sql(p_ou_id IN NUMBER, p_req_num IN VARCHAR2, p_shipment_num IN VARCHAR2, p_receipt_num
5 IN VARCHAR2, p_org_id IN NUMBER, p_sql IN OUT NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
6 l_ou_id po_requisition_headers_all.org_id%TYPE := p_ou_id;
7 l_req_num po_requisition_headers_all.segment1%TYPE := p_req_num;
8 l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
9 l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
10 l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;
11 l_count NUMBER := 0;
12
13 BEGIN
14
15
16
17 p_sql(1) := ' SELECT prh.* ' ||
18 ' FROM po_requisition_headers_all prh, ' ||
19 ' po_requisition_lines_all prl ' ||
20 ' where prh.segment1 = ' || '''' || l_req_num || '''' ||
21 ' and prh.org_id = ' || l_ou_id ||
22 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
23 ' AND prl.source_type_code = ''INVENTORY'' ';
24
25
26
27
28
29
30 p_sql(2) := ' SELECT prl.* ' ||
31 ' FROM po_requisition_headers_all prh, ' ||
32 ' po_requisition_lines_all prl, ' ||
33 ' rcv_shipment_headers rsh, ' ||
34 ' rcv_shipment_lines rsl ' ||
35 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
36 ' and rsh.ship_to_org_id = ' || l_org_id ||
37 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
38 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
39 ' AND prl.source_type_code = ''INVENTORY'' ' ||
40 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
41 ' and prh.org_id = ' || l_ou_id ||
42
43 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
44 ' ORDER BY prl.requisition_line_id ';
45
46
47
48
49
50
51 p_sql(3) := ' SELECT prd.* ' ||
52 ' FROM po_requisition_headers_all prh, ' ||
53 ' po_requisition_lines_all prl, ' ||
54 ' po_req_distributions_all prd, ' ||
55 ' rcv_shipment_headers rsh, ' ||
56 ' rcv_shipment_lines rsl ' ||
57 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
58 ' and rsh.ship_to_org_id = ' || l_org_id ||
59 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
60 ' and prh.org_id = ' || l_ou_id ||
61
62 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
63 ' AND prl.requisition_line_id = prd.requisition_line_id ' ||
64 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
65 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
66 ' AND prl.source_type_code = ''INVENTORY'' ';
67
68
69
70
71
72 p_sql(4) := ' SELECT oel.* ' ||
73 ' FROM oe_order_lines_all oel, ' ||
74 ' po_requisition_lines_all prl, ' ||
75 ' po_requisition_headers_all prh,' ||
76 ' rcv_shipment_headers rsh, ' ||
77 ' rcv_shipment_lines rsl ' ||
78 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
79 ' and rsh.ship_to_org_id = ' || l_org_id ||
80 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
81 ' and prh.org_id = ' || l_ou_id ||
82
83 ' AND prl.requisition_line_id = oel.source_document_line_id ' ||
84 ' AND oel.source_document_type_id = 10 ' ||
85 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
86 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
87 ' AND prl.source_type_code = ''INVENTORY''' ||
88 ' ORDER BY oel.line_id ';
89
90
91 p_sql(5) := ' SELECT wsh.* ' ||
92 ' FROM wsh_delivery_details wsh , ' ||
93 ' wsh_delivery_assignments wda , ' ||
94 ' wsh_new_deliveries wnd , ' ||
95 ' oe_order_lines_all sol , ' ||
96 ' po_requisition_lines_all prl , ' ||
97 ' po_requisition_headers_all prh , ' ||
98 ' rcv_shipment_headers rsh, ' ||
99 ' rcv_shipment_lines rsl ' ||
100 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
101 ' and rsh.ship_to_org_id = ' || l_org_id ||
102 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
103 ' and prh.org_id = ' || l_ou_id ||
104
105 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
106 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
107 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
108 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
109 ' AND sol.source_document_type_id = 10 ' ||
110 ' AND wsh.source_line_id = sol.line_id ' ||
111 ' AND wsh.delivery_detail_id = wda.delivery_detail_id ' ||
112 ' AND wda.delivery_id = wnd.delivery_id ' ||
113 ' UNION ALL ' ||
114 ' SELECT wsh.* ' ||
115 ' FROM wsh_delivery_details wsh , ' ||
116 ' mtl_transactions_interface mti , ' ||
117 ' po_requisition_lines_all prl , ' ||
118 ' po_requisition_headers_all prh , ' ||
119 ' oe_order_lines_all sol, ' ||
120 ' rcv_shipment_headers rsh, ' ||
121 ' rcv_shipment_lines rsl ' ||
122 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
123 ' and rsh.ship_to_org_id = ' || l_org_id ||
124 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
125 ' and prh.org_id = ' || l_ou_id ||
126
127 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
128 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
129 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
130 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
131 ' AND sol.source_document_type_id = 10 ' ||
132 ' AND mti.trx_source_line_id = sol.line_id ' ||
133 ' AND mti.picking_line_id = wsh.delivery_detail_id ';
134
135
136 p_sql(6) := ' SELECT rhi.* ' ||
137 ' FROM rcv_headers_interface rhi,' ||
138 ' rcv_shipment_headers rsh ' ||
139 ' WHERE rhi.receipt_header_id = rsh.shipment_header_id ' ||
140 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
141 ' and rsh.ship_to_org_id = ' || l_org_id ;
142
143
144 p_sql(7) := ' SELECT DISTINCT rti.*' ||
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 p_sql(8) := ' SELECT DISTINCT pie.* ' ||
151 ' FROM po_interface_errors pie ' ||
152 ' WHERE pie.interface_transaction_id IN ' ||
153 ' ( SELECT DISTINCT rti.interface_transaction_id ' ||
154 ' FROM rcv_transactions_interface rti' ||
155 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
156 ' and rti.to_organization_id = ' || l_org_id ||
157 ' OR pie.interface_line_id IN ' ||
158 ' ( SELECT DISTINCT rti.interface_transaction_id' ||
159 ' FROM rcv_transactions_interface rti ' ||
160 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
161 ' and rti.to_organization_id = ' || l_org_id || '))';
162
163
164
165
166
167
168 p_sql(9) := ' SELECT DISTINCT rsh.* ' ||
169 ' FROM rcv_shipment_headers rsh , ' ||
170 ' rcv_shipment_lines rsl , ' ||
171 ' po_requisition_headers_all prh , ' ||
172 ' po_requisition_lines_all prl ' ||
173 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
174 ' and rsh.ship_to_org_id = ' || l_org_id ||
175 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
176 ' and prh.org_id = ' || l_ou_id ||
177
178 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
179 ' AND prl.source_type_code = ''INVENTORY''' ||
180 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
181 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
182 ' AND rsh.receipt_source_code = ''INTERNAL ORDER'' ';
183
184
185
186 p_sql(10) := ' SELECT rsl.* ' ||
187 ' FROM rcv_shipment_headers rsh, ' ||
188 ' rcv_shipment_lines rsl , ' ||
189 ' po_requisition_headers_all prh , ' ||
190 ' po_requisition_lines_all prl ' ||
191 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
192 ' and rsh.ship_to_org_id = ' || l_org_id ||
193 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
194 ' and prh.org_id = ' || l_ou_id ||
195
196 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
197 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
198 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
199 ' AND prl.source_type_code = ''INVENTORY''' ||
200 ' AND rsl.source_document_code = ''REQ'' ';
201
202 p_sql(11) := ' SELECT rt.* ' ||
203 ' FROM rcv_transactions rt , ' ||
204 ' po_requisition_headers_all prh , ' ||
205 ' po_requisition_lines_all prl ,' ||
206 ' rcv_shipment_headers rsh, ' ||
207 ' rcv_shipment_lines rsl ' ||
208 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
209 ' and rsh.ship_to_org_id = ' || l_org_id ||
210 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
211 ' and prh.org_id = ' || l_ou_id ||
212
213 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
214 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
215 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
216 ' AND prl.source_type_code = ''INVENTORY'' ' ||
217 ' AND rt.requisition_line_id = prl.requisition_line_id ' ||
218 ' AND rt.shipment_line_id = rsl.shipment_line_id ';
219
220 p_sql(12) := ' SELECT ms.* ' ||
221 ' FROM mtl_supply ms , ' ||
222 ' po_requisition_headers_all prh , ' ||
223 ' po_requisition_lines_all prl ,' ||
224 ' rcv_shipment_headers rsh, ' ||
225 ' rcv_shipment_lines rsl ' ||
226 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
227 ' and rsh.ship_to_org_id = ' || l_org_id ||
228 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
229 ' and prh.org_id = ' || l_ou_id ||
230
231 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
232 ' AND prl.source_type_code = ''INVENTORY''' ||
233 ' AND rsh.shipment_header_id = rsl.shipment_header_id' ||
234 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
235 ' AND ms.shipment_header_id = rsh.shipment_header_id ';
236
237
238
239
240
241
242 p_sql(13) := ' SELECT rs.* ' ||
243 ' FROM rcv_supply rs , ' ||
244 ' po_requisition_headers_all prh ,' ||
245 ' po_requisition_lines_all prl,' ||
246 ' rcv_shipment_headers rsh, ' ||
247 ' rcv_shipment_lines rsl ' ||
248 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
249 ' and rsh.ship_to_org_id = ' || l_org_id ||
250 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
251 ' and prh.org_id = ' || l_ou_id ||
252
253 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
254 ' AND prl.source_type_code = ''INVENTORY'' ' ||
255 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
256 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
257 ' AND rs.shipment_header_id = rsh.shipment_header_id ';
258
259 p_sql(14) := ' SELECT mtrl.* ' ||
260 ' FROM mtl_txn_request_lines mtrl, ' ||
261 ' rcv_shipment_headers rsh, ' ||
262 ' rcv_shipment_lines rsl, ' ||
263 ' po_requisition_headers_all prh, ' ||
264 ' po_requisition_lines_all prl ' ||
265 ' WHERE rsh.shipment_num = '|| '''' || l_shipment_num || '''' ||
266 ' and rsh.ship_to_org_id = l_org_id ' ||
267 ' and prh.segment1 = '|| '''' || l_req_num || '''' ||
268 ' and prh.org_id = '|| l_ou_id ||
269 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
270 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
271 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
272 ' AND prl.source_type_code = ''INVENTORY''' ||
273 ' and mtrl.inventory_item_id=rsl.item_id ' ||
274 ' and nvl(mtrl.revision,0)=nvl(prl.item_revision,0) ' ||
275 ' and mtrl.organization_id=rsl.to_organization_id ' ||
276 ' and mtrl.transaction_type_id=52'||
277 ' and mtrl.line_status=7';
278
279 /*' SELECT mtrl.* ' ||
280 ' FROM mtl_txn_request_lines mtrl, ' ||
281 ' rcv_shipment_headers rsh, ' ||
282 ' rcv_shipment_lines rsl, ' ||
283 ' po_requisition_headers_all prh, ' ||
284 ' po_requisition_lines_all prl ' ||
285 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
286 ' and rsh.ship_to_org_id = ' || l_org_id ||
287 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
288 ' and prh.org_id = ' || l_ou_id ||
289 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
290 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
291 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
292 ' AND prl.source_type_code = ''INVENTORY'' ' ||
293 ' AND mtrl.reference_id = rsl.shipment_line_id ' ||
294 ' AND mtrl.reference = ''SHIPMENT_LINE_ID'' ' ||
295 ' AND rsl.source_document_code = ''REQ'' '; */
296
297 p_sql(15) := ' SELECT MTI.* ' ||
298 ' FROM MTL_TRANSACTIONS_INTERFACE MTI, ' ||
299 ' PO_REQUISITION_LINES_ALL PRL, ' ||
300 ' PO_REQUISITION_HEADERS_ALL PRH, ' ||
301 ' OE_ORDER_LINES_ALL SOL, ' ||
302 ' RCV_SHIPMENT_HEADERS RSH, ' ||
303 ' RCV_SHIPMENT_LINES RSL ' ||
304 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
305 ' and rsh.ship_to_org_id = ' || l_org_id ||
306 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
307 ' and prh.org_id = ' || l_ou_id ||
308
309 ' AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID ' ||
310 ' AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID ' ||
311 ' AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID ' ||
312 ' AND prl.source_type_code = ''INVENTORY'' ' ||
313 ' AND SOL.SOURCE_DOCUMENT_TYPE_ID = 10 ' ||
314 ' AND SOL.SOURCE_DOCUMENT_LINE_ID = PRL.REQUISITION_LINE_ID ' ||
315 ' AND MTI.TRX_SOURCE_LINE_ID = SOL.LINE_ID ' ||
316 ' AND MTI.SOURCE_CODE = ''ORDER ENTRY'' ';
317
318 p_sql(16) := ' SELECT mmtt.* ' ||
319 ' FROM mtl_material_transactions_temp mmtt , ' ||
320 ' po_requisition_lines_all prl , ' ||
321 ' po_requisition_headers_all prh, ' ||
322 ' rcv_shipment_headers rsh, ' ||
323 ' rcv_shipment_lines rsl,' ||
324 ' rcv_transactions rt ' ||
325 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
326 ' and rsh.ship_to_org_id = ' || l_org_id ||
327 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
328 ' and prh.org_id = ' || l_ou_id ||
329 ' AND prh.segment1 = ''229'' ' ||
330 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
331
332 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
333 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
334 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
335 ' and prl.source_type_code = ''INVENTORY''' ||
336 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
337 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
338 ' and mmtt.rcv_transaction_id = rt.transaction_id ' ||
339 ' UNION ALL' ||
340 ' SELECT mmtt.* ' ||
341 ' FROM mtl_material_transactions_temp mmtt , ' ||
345 ' rcv_shipment_lines rsl,' ||
342 ' po_requisition_lines_all prl , ' ||
343 ' po_requisition_headers_all prh, ' ||
344 ' rcv_shipment_headers rsh, ' ||
346 ' oe_order_lines_all sol ' ||
347 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
348 ' and rsh.ship_to_org_id = ' || l_org_id ||
349 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
350 ' and prh.org_id = ' || l_ou_id ||
351 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
352 ' and prl.source_type_code = ''INVENTORY'' ' ||
353 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
354
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_type_id = 10 ' ||
358 ' and sol.source_document_line_id = prl.requisition_line_id' ||
359 ' and mmtt.trx_source_line_id = sol.line_id ';
360
361
362
363
364 p_sql(17) := ' SELECT mmt.* ' ||
365 ' FROM mtl_material_transactions mmt , ' ||
366 ' po_requisition_lines_all prl , ' ||
367 ' po_requisition_headers_all prh, ' ||
368 ' rcv_shipment_headers rsh, ' ||
369 ' rcv_shipment_lines rsl,' ||
370 ' rcv_transactions rt ' ||
371 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
372 ' and rsh.ship_to_org_id = ' || l_org_id ||
373 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
374 ' and prh.org_id = ' || l_ou_id ||
375 ' AND prh.segment1 = ''229'' ' ||
376 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
377
378 ' AND prl.requisition_header_id = prh.requisition_header_id ' ||
379 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
380 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
381 ' and prl.source_type_code = ''INVENTORY''' ||
382 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
383 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
384 ' UNION ALL' ||
385 ' SELECT mmt.* ' ||
386 ' FROM mtl_material_transactions mmt , ' ||
387 ' po_requisition_lines_all prl , ' ||
388 ' po_requisition_headers_all prh, ' ||
389 ' rcv_shipment_headers rsh, ' ||
390 ' rcv_shipment_lines rsl,' ||
391 ' oe_order_lines_all sol ' ||
392 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
393 ' and rsh.ship_to_org_id = ' || l_org_id ||
394 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
395 ' and prh.org_id = ' || l_ou_id ||
396 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
397 ' and prl.source_type_code = ''INVENTORY'' ' ||
398
399 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
400 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
401 ' and sol.source_document_type_id = 10 ' ||
402 ' and sol.source_document_line_id = prl.requisition_line_id' ||
403 ' and mmt.trx_source_line_id = sol.line_id ' ||
404 ' and mmt.transaction_action_id = 21';
405
406
407
408 p_sql(18) := ' SELECT mr.* ' ||
409 ' FROM mtl_reservations mr , ' ||
410 ' oe_order_lines_all sol , ' ||
411 ' po_requisition_lines_all prl , ' ||
412 ' po_requisition_headers_all prh, ' ||
413 ' rcv_shipment_headers rsh, ' ||
414 ' rcv_shipment_lines rsl ' ||
415 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
416 ' and rsh.ship_to_org_id = ' || l_org_id ||
417 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
418 ' and prh.org_id = ' || l_ou_id ||
419
420 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
421 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
422 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
423 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
424 ' AND sol.source_document_type_id = 10 ' ||
425 ' AND mr.demand_source_line_id = sol.line_id ' ||
426 ' AND mr.demand_source_type_id = 8 ' ||
427 ' UNION ALL ' ||
428 ' SELECT mr.* ' ||
429 ' FROM mtl_reservations mr , ' ||
430 ' mtl_transactions_interface mti , ' ||
431 ' po_requisition_lines_all prl , ' ||
432 ' po_requisition_headers_all prh , ' ||
433 ' oe_order_lines_all sol ,' ||
434 ' rcv_shipment_headers rsh, ' ||
435 ' rcv_shipment_lines rsl ' ||
436 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
437 ' and rsh.ship_to_org_id = ' || l_org_id ||
438 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
439 ' and prh.org_id = ' || l_ou_id ||
440
441 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
442 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
443 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
444 ' AND sol.source_document_type_id = 10 ' ||
445 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
446 ' AND mti.trx_source_line_id = sol.line_id ' ||
447 ' AND mr.demand_source_line_id = mti.trx_source_line_id ';
448
449 p_sql(19) := ' SELECT md.* ' ||
450 ' FROM mtl_demand md , ' ||
451 ' oe_order_lines_all sol , ' ||
452 ' po_requisition_lines_all prl , ' ||
453 ' po_requisition_headers_all prh , ' ||
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 prh.segment1 = ' || '''' || l_req_num || '''' ||
459 ' and prh.org_id = ' || l_ou_id ||
460
461 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
462 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
466 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
463 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
464 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
465 ' AND sol.source_document_type_id = 10 ' ||
467 ' AND md.demand_source_line = sol.line_id ' ||
468 ' AND md.demand_source_type = 8 ' ||
469 ' UNION ALL ' ||
470 ' SELECT md.* ' ||
471 ' FROM mtl_demand md , ' ||
472 ' mtl_transactions_interface mti , ' ||
473 ' po_requisition_lines_all prl , ' ||
474 ' po_requisition_headers_all prh , ' ||
475 ' oe_order_lines_all sol , ' ||
476 ' rcv_shipment_headers rsh, ' ||
477 ' rcv_shipment_lines rsl ' ||
478 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
479 ' and rsh.ship_to_org_id = ' || l_org_id ||
480 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
481 ' and prh.org_id = ' || l_ou_id ||
482
483 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
484 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
485 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
486 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
487 ' AND sol.source_document_type_id = 10 ' ||
488 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
489 ' AND mti.trx_source_line_id = sol.line_id ' ||
490 ' AND md.demand_source_line = mti.source_line_id ';
491
492
493
494
495
496
497 p_sql(20) := ' select distinct msn.* ' ||
498 ' from mtl_serial_numbers msn , ' ||
499 ' mtl_material_transactions mmt , ' ||
500 ' po_requisition_lines_all prl , ' ||
501 ' po_requisition_headers_all prh ,' ||
502 ' rcv_shipment_headers rsh, ' ||
503 ' rcv_shipment_lines rsl ,' ||
504 ' rcv_transactions rt' ||
505 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
506 ' and rsh.ship_to_org_id = ' || l_org_id ||
507 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
508 ' and prh.org_id = ' || l_ou_id ||
509
510 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
511 ' and prl.source_type_code = ''INVENTORY''' ||
512 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
513 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
514 ' AND rt.requisition_line_id = prl.requisition_line_id ' ||
515 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
516 ' and mmt.transaction_id = msn.last_transaction_id' ||
517 ' UNION ALL' ||
518 ' select distinct msn.* ' ||
519 ' from mtl_serial_numbers msn , ' ||
520 ' mtl_material_transactions mmt , ' ||
521 ' po_requisition_lines_all prl , ' ||
522 ' po_requisition_headers_all prh ,' ||
523 ' oe_order_lines_all sol,' ||
524 ' rcv_shipment_headers rsh, ' ||
525 ' rcv_shipment_lines rsl ' ||
526 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
527 ' and rsh.ship_to_org_id = ' || l_org_id ||
528 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
529 ' and prh.org_id = ' || l_ou_id ||
530
531 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
532 ' and prl.source_type_code = ''INVENTORY''' ||
533 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
534 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
535 ' and sol.source_document_type_id = 10 ' ||
536 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
537 ' and mmt.trx_source_line_id = sol.line_id ' ||
538 ' and mmt.rcv_transaction_id is null ' ||
539 ' and mmt.transaction_id = msn.last_transaction_id ';
540
541
542 p_sql(21) := ' select DISTINCT msnt.*' ||
543 ' from po_requisition_lines_all prl ,' ||
544 ' po_requisition_headers_all prh ,' ||
545 ' mtl_serial_numbers_temp msnt ,' ||
546 ' mtl_system_items msi,' ||
547 ' rcv_transactions_interface rti,' ||
548 ' rcv_shipment_headers rsh, ' ||
549 ' rcv_shipment_lines rsl ' ||
550 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
551 ' and rsh.ship_to_org_id = ' || l_org_id ||
552 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
553 ' and prh.org_id = ' || l_ou_id ||
554
555 ' and prh.requisition_header_id = prl.requisition_header_id ' ||
556 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
557 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
558 ' and prl.source_type_code = ''INVENTORY''' ||
559 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
560 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
561 ' )' ||
562 ' and rti.interface_transaction_id = msnt.transaction_temp_id' ||
563 ' and msi.inventory_item_id = rti.item_id' ||
564 ' and msi.organization_id = rti.to_organization_id' ||
565 ' and msi.serial_number_control_code <> 1' ||
566 ' and msi.lot_control_code = 1' ||
567 ' UNION ALL' ||
568 ' select DISTINCT msnt.*' ||
569 ' from po_requisition_lines_all prl ,' ||
570 ' po_requisition_headers_all prh ,' ||
571 ' mtl_serial_numbers_temp msnt ,' ||
572 ' mtl_transaction_lots_temp mtlt,' ||
573 ' mtl_system_items msi,' ||
574 ' rcv_transactions_interface rti,' ||
575 ' rcv_shipment_headers rsh, ' ||
576 ' rcv_shipment_lines rsl ' ||
577 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
578 ' and rsh.ship_to_org_id = ' || l_org_id ||
579 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
580 ' and prh.org_id = ' || l_ou_id ||
581
585 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
582 ' and prh.requisition_header_id = prl.requisition_header_id' ||
583 ' and prl.source_type_code = ''INVENTORY''' ||
584 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
586 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
587 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
588 ' )' ||
589 ' and rti.interface_transaction_id = mtlt.transaction_temp_id' ||
590 ' and mtlt.SERIAL_TRANSACTION_TEMP_ID = msnt.transaction_temp_id' ||
591 ' and msi.inventory_item_id = rti.item_id' ||
592 ' and msi.organization_id = rti.to_organization_id' ||
593 ' and msi.serial_number_control_code <> 1' ||
594 ' and msi.lot_control_code <> 1' ||
595 ' UNION ALL' ||
596 ' select DISTINCT msnt.*' ||
597 ' from po_requisition_lines_all prl,' ||
598 ' po_requisition_headers_all prh,' ||
599 ' mtl_serial_numbers_temp msnt,' ||
600 ' mtl_system_items msi,' ||
601 ' oe_order_lines_all sol,' ||
602 ' mtl_material_transactions_temp mmtt,' ||
603 ' rcv_shipment_headers rsh, ' ||
604 ' rcv_shipment_lines rsl ' ||
605 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
606 ' and rsh.ship_to_org_id = ' || l_org_id ||
607 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
608 ' and prh.org_id = ' || l_ou_id ||
609
610 ' and prh.requisition_header_id = prl.requisition_header_id' ||
611 ' and prl.source_type_code = ''INVENTORY''' ||
612 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
613 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
614 ' and sol.source_document_line_id = prl.requisition_line_id' ||
615 ' and sol.source_document_type_id = 10' ||
616 ' and mmtt.trx_source_line_id = sol.line_id' ||
617 ' and msnt.transaction_TEMP_id = mmtt.transaction_TEMP_id' ||
618 ' and msi.inventory_item_id = mmtt.inventory_item_id' ||
619 ' and msi.organization_id = mmtt.organization_id' ||
620 ' and msi.serial_number_control_code <> 1' ||
621 ' and msi.lot_control_code = 1' ||
622 ' UNION ALL' ||
623 ' select DISTINCT msnt.*' ||
624 ' from po_requisition_lines_all prl,' ||
625 ' po_requisition_headers_all prh,' ||
626 ' mtl_serial_numbers_temp msnt,' ||
627 ' mtl_transaction_lots_temp mtlt,' ||
628 ' mtl_system_items msi,' ||
629 ' oe_order_lines_all sol,' ||
630 ' mtl_material_transactions_temp mmtt,' ||
631 ' rcv_shipment_headers rsh, ' ||
632 ' rcv_shipment_lines rsl ' ||
633 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
634 ' and rsh.ship_to_org_id = ' || l_org_id ||
635 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
636 ' and prh.org_id = ' || l_ou_id ||
637
638 ' and prh.requisition_header_id = prl.requisition_header_id' ||
639 ' and prl.source_type_code = ''INVENTORY''' ||
640 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
641 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
642 ' and sol.source_document_line_id = prl.requisition_line_id' ||
643 ' and sol.source_document_type_id = 10' ||
644 ' and mmtt.trx_source_line_id = sol.line_id' ||
645 ' and mmtt.transaction_TEMP_id = mtlt.transaction_TEMP_id' ||
646 ' and mtlt.serial_transaction_temp_id = msnt.transaction_temp_id' ||
647 ' and msi.inventory_item_id = mmtt.inventory_item_id' ||
648 ' and msi.organization_id = mmtt.organization_id' ||
649 ' and msi.serial_number_control_code <> 1' ||
650 ' and msi.lot_control_code <> 1 ';
651
652
653
654 p_sql(22) := ' select distinct msni.* ' ||
655 ' from rcv_transactions_interface rti ,' ||
656 ' po_requisition_lines_all prl , ' ||
657 ' po_requisition_headers_all prh , ' ||
658 ' mtl_serial_numbers_interface msni ,' ||
659 ' mtl_system_items msi,' ||
660 ' rcv_shipment_headers rsh, ' ||
661 ' rcv_shipment_lines rsl ' ||
662 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
663 ' and rsh.ship_to_org_id = ' || l_org_id ||
664 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
665 ' and prh.org_id = ' || l_ou_id ||
666
667 ' and prh.requisition_header_id = prl.requisition_header_id' ||
668 ' and prl.source_type_code = ''INVENTORY''' ||
669 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
670 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
671 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
672 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
673 ' )' ||
674 ' and rti.interface_transaction_id = msni.product_transaction_id' ||
675 ' and msi.inventory_item_id = rti.item_id' ||
676 ' and msi.organization_id = rti.to_organization_id' ||
677 ' and msi.serial_number_control_code <> 1' ||
678 ' and msi.lot_control_code = 1' ||
679 ' UNION ALL' ||
680 ' select distinct msni.* ' ||
681 ' from rcv_transactions_interface rti ,' ||
682 ' po_requisition_lines_all prl , ' ||
683 ' po_requisition_headers_all prh , ' ||
684 ' mtl_serial_numbers_interface msni ,' ||
685 ' mtl_transaction_lots_interface mtli,' ||
686 ' mtl_system_items msi,' ||
687 ' rcv_shipment_headers rsh, ' ||
688 ' rcv_shipment_lines rsl ' ||
689 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
690 ' and rsh.ship_to_org_id = ' || l_org_id ||
691 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
692 ' and prh.org_id = ' || l_ou_id ||
693
694 ' and prh.requisition_header_id = prl.requisition_header_id' ||
695 ' and prl.source_type_code = ''INVENTORY''' ||
699 ' or rti.req_num IS NOT NULL and rti.req_num = prh.segment1' ||
696 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
697 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
698 ' and (prl.requisition_line_id = Nvl(rti.requisition_line_id,-99)' ||
700 ' )' ||
701 ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
702 ' and mtli.serial_transaction_temp_id = msni.transaction_interface_id ' ||
703 ' and msi.inventory_item_id = rti.item_id' ||
704 ' and msi.organization_id = rti.to_organization_id' ||
705 ' and msi.serial_number_control_code <> 1' ||
706 ' and msi.lot_control_code <> 1 ';
707
708
709 p_sql(23) := ' select distinct mut.* ' ||
710 ' from mtl_material_transactions mmt , ' ||
711 ' po_requisition_lines_all prl , ' ||
712 ' mtl_unit_transactions mut , ' ||
713 ' mtl_system_items msi, ' ||
714 ' rcv_transactions rt, ' ||
715 ' rcv_shipment_headers rsh, ' ||
716 ' rcv_shipment_lines rsl ' ||
717 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
718 ' and rsh.ship_to_org_id = ' || l_org_id ||
719 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
720 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
721
722 ' AND rt.requisition_line_id = prl.requisition_line_id ' ||
723 ' and prl.source_type_code = ''INVENTORY''' ||
724 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
725 ' and mmt.transaction_id = mut.transaction_id ' ||
726 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
727 ' and msi.organization_id = mmt.organization_id ' ||
728 ' and msi.serial_number_control_code <> 1 ' ||
729 ' and msi.lot_control_code = 1 ' ||
730 ' union all ' ||
731 ' select distinct mut.* ' ||
732 ' from mtl_material_transactions mmt , ' ||
733 ' po_requisition_lines_all prl , ' ||
734 ' mtl_unit_transactions mut , ' ||
735 ' mtl_system_items msi , ' ||
736 ' rcv_transactions rt , ' ||
737 ' mtl_transaction_lot_numbers mtln, ' ||
738 ' rcv_shipment_headers rsh, ' ||
739 ' rcv_shipment_lines rsl ' ||
740 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
741 ' and rsh.ship_to_org_id = ' || l_org_id ||
742 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
743 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
744
745 ' and prl.source_type_code = ''INVENTORY''' ||
746 ' and rt.requisition_line_id = prl.requisition_line_id ' ||
747 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
748 ' and mtln.transaction_id = mmt.transaction_id ' ||
749 ' and mut.transaction_id = mtln.serial_transaction_id ' ||
750 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
751 ' and msi.organization_id = mmt.organization_id ' ||
752 ' and msi.serial_number_control_code <> 1 ' ||
753 ' and msi.lot_control_code <> 1 ' ||
754 ' union all ' ||
755 ' select distinct mut.* ' ||
756 ' from mtl_material_transactions mmt , ' ||
757 ' po_requisition_lines_all prl , ' ||
758 ' mtl_unit_transactions mut , ' ||
759 ' mtl_system_items msi , ' ||
760 ' oe_order_lines_all sol, ' ||
761 ' rcv_shipment_headers rsh, ' ||
762 ' rcv_shipment_lines rsl ' ||
763 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
764 ' and rsh.ship_to_org_id = ' || l_org_id ||
765 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
766 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
767
768 ' and prl.source_type_code = ''INVENTORY''' ||
769 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
770 ' and sol.source_document_type_id = 10 ' ||
771 ' and mmt.trx_source_line_id = sol.line_id ' ||
772 ' and mut.transaction_id = mmt.transaction_id ' ||
773 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
774 ' and msi.organization_id = mmt.organization_id ' ||
775 ' and msi.serial_number_control_code <> 1 ' ||
776 ' and msi.lot_control_code = 1 ' ||
777 ' union all ' ||
778 ' select distinct mut.* ' ||
779 ' from mtl_material_transactions mmt , ' ||
780 ' po_requisition_lines_all prl , ' ||
781 ' mtl_unit_transactions mut , ' ||
782 ' mtl_system_items msi , ' ||
783 ' oe_order_lines_all sol , ' ||
784 ' mtl_transaction_lot_numbers mtln, ' ||
785 ' rcv_shipment_headers rsh, ' ||
786 ' rcv_shipment_lines rsl ' ||
787 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
788 ' and rsh.ship_to_org_id = ' || l_org_id ||
789 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
790 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
791 ' and prl.source_type_code = ''INVENTORY'' ' ||
792
793 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
794 ' and sol.source_document_type_id = 10 ' ||
795 ' and mmt.trx_source_line_id = sol.line_id ' ||
796 ' and mtln.transaction_id = mmt.transaction_id ' ||
797 ' and mut.transaction_id = mtln.serial_transaction_id ' ||
798 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
799 ' and msi.organization_id = mmt.organization_id ' ||
800 ' and msi.serial_number_control_code <> 1 ' ||
801 ' and msi.lot_control_code <> 1 ';
802
803
804
805
806
807
808 p_sql(24) := ' SELECT rss.* ' ||
812 ' po_requisition_lines_all prl , ' ||
809 ' FROM rcv_serials_supply rss , ' ||
810 ' rcv_shipment_lines rsl , ' ||
811 ' po_requisition_headers_all prh , ' ||
813 ' rcv_shipment_headers rsh ' ||
814 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
815 ' and rsh.ship_to_org_id = ' || l_org_id ||
816 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
817 ' and prh.org_id = ' || l_ou_id ||
818
819 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
820 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
821 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
822 ' AND rss.shipment_line_id = rsl.shipment_line_id ' ||
823 ' AND rsl.source_document_code = ''REQ'' ' ||
824 ' ORDER BY rss.supply_type_code , ' ||
825 ' rss.serial_num ';
826
827
828 p_sql(25) := ' SELECT rst.* ' ||
829 ' FROM rcv_serial_transactions rst , ' ||
830 ' rcv_shipment_lines rsl , ' ||
831 ' po_requisition_headers_all prh , ' ||
832 ' po_requisition_lines_all prl , ' ||
833 ' rcv_shipment_headers rsh ' ||
834 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
835 ' and rsh.ship_to_org_id = ' || l_org_id ||
836 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
837 ' and prh.org_id = ' || l_ou_id ||
838
839 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
840 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
841 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
842 ' AND rsl.source_document_code = ''REQ'' ' ||
843 ' AND rst.shipment_line_id = rsl.shipment_line_id ' ||
844 ' ORDER BY rst.serial_transaction_type , ' ||
845 ' rst.serial_num ';
846
847
848 p_sql(26) := ' SELECT rsi.* ' ||
849 ' FROM rcv_serials_interface rsi , ' ||
850 ' rcv_shipment_lines rsl , ' ||
851 ' po_requisition_headers_all prh , ' ||
852 ' po_requisition_lines_all prl , ' ||
853 ' rcv_shipment_headers rsh ,' ||
854 ' rcv_transactions_interface rti' ||
855 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
856 ' and rsh.ship_to_org_id = ' || l_org_id ||
857 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
858 ' and prh.org_id = ' || l_ou_id ||
859
860 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
861 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
862 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
863
864 ' AND rsl.source_document_code = ''REQ'' ' ||
865 ' AND rsi.item_id = rsl.item_id ' ||
866 ' AND rsi.organization_id = rsl.to_organization_id ' ||
867 ' AND rsi.interface_transaction_id = rti.interface_transaction_id' ||
868 ' AND rti.shipment_line_id = rsl.shipment_line_id ';
869
870
871
872
873 p_sql(27) := ' select distinct mln.* ' ||
874 ' from mtl_lot_numbers mln ,' ||
875 ' mtl_transaction_lot_numbers mtln ,' ||
876 ' mtl_material_transactions mmt ,' ||
877 ' po_requisition_lines_all prl ,' ||
878 ' rcv_transactions rt,' ||
879 ' rcv_shipment_headers rsh,' ||
880 ' rcv_shipment_lines rsl ' ||
881 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
882 ' and rsh.ship_to_org_id = ' || '''' || l_org_id || '''' ||
883 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
884 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
885
886 ' and rt.requisition_line_id = prl.requisition_line_id' ||
887 ' and prl.source_type_code = ''INVENTORY''' ||
888 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
889 ' and mmt.transaction_id = mtln.transaction_id ' ||
890 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
891 ' and mln.organization_id = mmt.organization_id ' ||
892 ' and mln.lot_number = mtln.lot_number ' ||
893 ' UNION ALL' ||
894 ' select distinct mln.* ' ||
895 ' from mtl_lot_numbers mln , ' ||
896 ' mtl_transaction_lot_numbers mtln ,' ||
897 ' mtl_material_transactions mmt , ' ||
898 ' po_requisition_lines_all prl , ' ||
899 ' oe_order_lines_all sol,' ||
900 ' rcv_shipment_headers rsh, ' ||
901 ' rcv_shipment_lines rsl ' ||
902 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
903 ' and rsh.ship_to_org_id = ' || '''' || l_org_id || '''' ||
904 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
905 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
906
907 ' and prl.source_type_code = ''INVENTORY''' ||
908 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
909 ' and sol.source_document_type_id = 10 ' ||
910 ' and mmt.transaction_id = mtln.transaction_id ' ||
911 ' and mmt.trx_source_line_id = sol.line_id' ||
912 ' and mln.inventory_item_id = mmt.inventory_item_id ' ||
913 ' and mln.organization_id = mmt.organization_id ' ||
914 ' and mln.lot_number = mtln.lot_number ';
915
916
917 p_sql(28) := ' select distinct mtln.*' ||
918 ' from mtl_transaction_lot_numbers mtln ,' ||
919 ' mtl_material_transactions mmt , ' ||
920 ' po_requisition_lines_all prl , ' ||
921 ' rcv_transactions rt,' ||
922 ' rcv_shipment_headers rsh, ' ||
923 ' rcv_shipment_lines rsl ' ||
924 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
925 ' and rsh.ship_to_org_id = ' || l_org_id ||
926 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
927 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
928
929 ' and prl.source_type_code = ''INVENTORY'' ' ||
930 ' and rt.requisition_line_id = prl.requisition_line_id' ||
934 ' select distinct mtln.* ' ||
931 ' and mmt.rcv_transaction_id = rt.transaction_id' ||
932 ' and mmt.transaction_id = mtln.transaction_id' ||
933 ' UNION ALL' ||
935 ' from mtl_transaction_lot_numbers mtln , ' ||
936 ' mtl_material_transactions mmt , ' ||
937 ' po_requisition_lines_all prl , ' ||
938 ' oe_order_lines_all sol,' ||
939 ' rcv_shipment_headers rsh, ' ||
940 ' rcv_shipment_lines rsl ' ||
941 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
942 ' and rsh.ship_to_org_id = ' || l_org_id ||
943 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
944 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
945
946 ' and prl.source_type_code = ''INVENTORY'' ' ||
947 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
948 ' and sol.source_document_type_id = 10 ' ||
949 ' and mmt.trx_source_line_id = sol.line_id ' ||
950 ' and mmt.transaction_id = mtln.transaction_id ';
951
952
953
954
955 p_sql(29) := ' select distinct mtli.* ' ||
956 ' from mtl_transaction_lots_interface mtli , ' ||
957 ' mtl_transactions_interface mti , ' ||
958 ' po_requisition_lines_all prl , ' ||
959 ' rcv_shipment_headers rsh, ' ||
960 ' rcv_shipment_lines rsl ' ||
961 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
962 ' and rsh.ship_to_org_id = ' || l_org_id ||
963 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
964 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
965
966 ' and prl.source_type_code = ''INVENTORY'' ' ||
967 ' and mti.requisition_line_id = prl.requisition_line_id ' ||
968 ' and mti.transaction_interface_id = mtli.transaction_interface_id' ||
969 ' UNION ALL' ||
970 ' select distinct mtli.* ' ||
971 ' from mtl_transaction_lots_interface mtli , ' ||
972 ' rcv_transactions_interface rti , ' ||
973 ' po_requisition_lines_all prl , ' ||
974 ' rcv_shipment_headers rsh, ' ||
975 ' rcv_shipment_lines rsl ' ||
976 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
977 ' and rsh.ship_to_org_id = ' || l_org_id ||
978 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
979 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
980
981 ' and prl.source_type_code = ''INVENTORY'' ' ||
982 ' and rti.interface_transaction_id = mtli.product_transaction_id' ||
983 ' and mtli.product_code =''RCV'' ' ||
984 ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99) ';
985
986
987
988
989
990
991 p_sql(30) := ' select distinct mtlt.* ' ||
992 ' from mtl_transaction_lots_temp mtlt ,' ||
993 ' rcv_transactions_interface rti, ' ||
994 ' po_requisition_lines_all prl , ' ||
995 ' rcv_shipment_headers rsh, ' ||
996 ' rcv_shipment_lines rsl ' ||
997 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
998 ' and rsh.ship_to_org_id = ' || l_org_id ||
999 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1000 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1001
1002 ' and prl.source_type_code = ''INVENTORY'' ' ||
1003 ' and rti.interface_transaction_id = mtlt.product_transaction_id' ||
1004 ' and prl.requisition_line_id = Nvl(rti.requisition_line_id,-99) ' ||
1005 ' UNION ALL' ||
1006 ' select distinct mtlt.* ' ||
1007 ' from mtl_transaction_lots_temp mtlt ,' ||
1008 ' mtl_material_transactions_temp mmtt, ' ||
1009 ' po_requisition_lines_all prl , ' ||
1010 ' oe_order_lines_all sol,' ||
1011 ' rcv_shipment_headers rsh, ' ||
1012 ' rcv_shipment_lines rsl ' ||
1013 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1014 ' and rsh.ship_to_org_id = ' || l_org_id ||
1015 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1016 ' AND rsl.requisition_line_id = prl.requisition_line_id' ||
1017 ' and prl.source_type_code = ''INVENTORY'' ' ||
1018
1019 ' and sol.source_document_line_id = prl.requisition_line_id' ||
1020 ' and sol.source_document_type_id = 10 ' ||
1021 ' and mmtt.trx_source_line_id = sol.line_id ';
1022
1023
1024 p_sql(31) := ' SELECT rls.* ' ||
1025 ' FROM rcv_lots_supply rls , ' ||
1026 ' rcv_shipment_lines rsl , ' ||
1027 ' po_requisition_headers_all prh , ' ||
1028 ' po_requisition_lines_all prl , ' ||
1029 ' oe_order_lines_all sol , ' ||
1030 ' rcv_shipment_headers rsh ' ||
1031 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1032 ' and rsh.ship_to_org_id = ' || l_org_id ||
1033 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1034 ' and prh.org_id = ' || l_ou_id ||
1035
1036 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1037 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1038 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1039 ' AND rsl.shipment_line_id = rls.shipment_line_id ' ||
1040 ' AND sol.source_document_type_id = 10 ' ||
1041 ' AND sol.source_document_line_id = prl.requisition_line_id ' ||
1042 ' AND rsl.source_document_code = ''REQ'' ';
1043
1044
1045
1046
1047
1048 p_sql(32) := ' SELECT rlt.* ' ||
1049 ' FROM rcv_lot_transactions rlt , ' ||
1050 ' rcv_shipment_lines rsl , ' ||
1051 ' po_requisition_headers_all prh , ' ||
1052 ' po_requisition_lines_all prl , ' ||
1053 ' rcv_shipment_headers rsh ' ||
1054 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1055 ' and rsh.ship_to_org_id = ' || l_org_id ||
1059 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1056 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1057 ' and prh.org_id = ' || l_ou_id ||
1058
1060 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1061 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1062 ' AND rsl.shipment_line_id = rlt.shipment_line_id ' ||
1063 ' AND rsl.source_document_code = ''REQ'' ' ||
1064 ' and prl.source_type_code = ''INVENTORY'' ';
1065
1066
1067
1068 p_sql(33) := ' SELECT rli.* ' ||
1069 ' FROM rcv_lots_interface rli , ' ||
1070 ' rcv_transactions_interface rti , ' ||
1071 ' po_requisition_headers_all prh , ' ||
1072 ' po_requisition_lines_all prl ,' ||
1073 ' rcv_shipment_headers rsh,' ||
1074 ' rcv_shipment_lines rsl' ||
1075 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1076 ' and rsh.ship_to_org_id = ' || l_org_id ||
1077 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1078 ' and prh.org_id = ' || l_ou_id ||
1079
1080 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1081 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1082 ' and prl.source_type_code = ''INVENTORY'' ' ||
1083 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1084 ' AND (rti.shipment_header_id = rsh.shipment_header_id OR' ||
1085 ' rti.shipment_num = rsh.shipment_num)' ||
1086 ' AND rti.interface_transaction_id = rli.interface_transaction_id ';
1087
1088
1089
1090 p_sql(34) := ' SELECT DISTINCT msi.* ' ||
1091 ' FROM po_requisition_headers_all prh, ' ||
1092 ' po_requisition_lines_all prl, ' ||
1093 ' mtl_system_items msi, ' ||
1094 ' rcv_shipment_headers rsh, ' ||
1095 ' rcv_shipment_lines rsl ' ||
1096 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1097 ' and rsh.ship_to_org_id = ' || l_org_id ||
1098 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1099 ' and prh.org_id = ' || l_ou_id ||
1100
1101 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1102 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1103 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1104 ' AND prh.type_lookup_code in (''INTERNAL'', ''PURCHASE'') ' ||
1105 ' AND prl.item_id = msi.inventory_item_id ' ||
1106 ' AND prl.destination_organization_id = msi.organization_id ';
1107
1108
1109 p_sql(35) := ' select distinct mtt.transaction_type_id , ' ||
1110 ' mtt.transaction_type_name , ' ||
1111 ' mtt.transaction_source_type_id , ' ||
1112 ' mtt.transaction_action_id , ' ||
1113 ' mtt.user_defined_flag , ' ||
1114 ' mtt.disable_date ' ||
1115 ' from mtl_transaction_types mtt , ' ||
1116 ' mtl_material_transactions mmt , ' ||
1117 ' po_requisition_lines_all prl , ' ||
1118 ' po_requisition_headers_all prh ,' ||
1119 ' rcv_transactions rt,' ||
1120 ' rcv_shipment_headers rsh, ' ||
1121 ' rcv_shipment_lines rsl ' ||
1122 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1123 ' and rsh.ship_to_org_id = ' || l_org_id ||
1124 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1125 ' and prh.org_id = ' || l_ou_id ||
1126
1127 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1128 ' AND rsh.shipment_header_id = rsl.shipment_header_id' ||
1129 ' AND rt.requisition_line_id = prl.requisition_line_id' ||
1130 ' and mmt.rcv_transaction_id = rt.transaction_id ' ||
1131 ' and mmt.transaction_type_id = mtt.transaction_type_id' ||
1132 ' UNION ALL' ||
1133 ' select distinct mtt.transaction_type_id , ' ||
1134 ' mtt.transaction_type_name , ' ||
1135 ' mtt.transaction_source_type_id , ' ||
1136 ' mtt.transaction_action_id , ' ||
1137 ' mtt.user_defined_flag , ' ||
1138 ' mtt.disable_date ' ||
1139 ' from mtl_transaction_types mtt , ' ||
1140 ' mtl_material_transactions mmt , ' ||
1141 ' po_requisition_lines_all prl , ' ||
1142 ' po_requisition_headers_all prh,' ||
1143 ' oe_order_lines_all sol,' ||
1144 ' rcv_shipment_headers rsh, ' ||
1145 ' rcv_shipment_lines rsl ' ||
1146 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1147 ' and rsh.ship_to_org_id = ' || l_org_id ||
1148 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1149 ' and prh.org_id = ' || l_ou_id ||
1150
1151 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1152 ' AND rsh.shipment_header_id = rsl.shipment_header_id' ||
1153 ' and sol.source_document_line_id = prl.requisition_line_id ' ||
1154 ' and sol.source_document_type_id = 10 ' ||
1155 ' and mmt.trx_source_line_id = sol.line_id ' ||
1156 ' and mmt.transaction_type_id = mtt.transaction_type_id ';
1157
1158
1159
1160
1161 p_sql(36) := ' SELECT ood.* ' ||
1162 ' FROM org_organization_definitions ood ' ||
1163 ' WHERE exists ' ||
1164 ' (SELECT 1 ' ||
1165 ' FROM po_requisition_headers_all prh , ' ||
1166 ' po_requisition_lines_all prl , ' ||
1167 ' financials_system_params_all fsp , ' ||
1168 ' rcv_shipment_headers rsh, ' ||
1169 ' rcv_shipment_lines rsl ' ||
1170 ' WHERE rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1171 ' and rsh.ship_to_org_id = ' || l_org_id ||
1172 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1173 ' and prh.org_id = ' || l_ou_id ||
1174
1175 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1179 ' AND (prl.destination_organization_id = ood.organization_id ' ||
1176 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1177 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1178 ' AND prh.type_lookup_code in (''INTERNAL'',''PURCHASE'') ' ||
1180 ' OR prl.source_organization_id = ood.organization_id ' ||
1181 ' OR (prh.org_id = fsp.org_id ' ||
1182 ' AND ood.organization_id = fsp.inventory_organization_id ) ) ' ||
1183 ' ) ';
1184
1185
1186 p_sql(37) := ' SELECT DISTINCT mp.* ' ||
1187 ' FROM mtl_parameters mp , ' ||
1188 ' po_requisition_headers_all prh , ' ||
1189 ' po_requisition_lines_all prl ,' ||
1190 ' rcv_shipment_headers rsh, ' ||
1191 ' rcv_shipment_lines rsl ' ||
1192 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1193 ' and rsh.ship_to_org_id = ' || l_org_id ||
1194 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1195 ' and prh.org_id = ' || l_ou_id ||
1196
1197 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1198 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1199 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1200 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
1201 ' AND (prl.destination_organization_id = mp.organization_id ' ||
1202 ' OR prl.source_organization_id = mp.organization_id ) ';
1203
1204
1205 p_sql(38) := ' SELECT miop.* ' ||
1206 ' FROM mtl_interorg_parameters miop ' ||
1207 ' WHERE exists ' ||
1208 ' (SELECT 1 ' ||
1209 ' FROM po_requisition_headers_all prh , ' ||
1210 ' po_requisition_lines_all prl, ' ||
1211 ' rcv_shipment_headers rsh, ' ||
1212 ' rcv_shipment_lines rsl ' ||
1213 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1214 ' and rsh.ship_to_org_id = ' || l_org_id ||
1215 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1216 ' and prh.org_id = ' || l_ou_id ||
1217
1218 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1219 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1220 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1221 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
1222 ' AND (prl.destination_organization_id = miop.to_organization_id ' ||
1223 ' AND prl.source_organization_id = miop.from_organization_id )' ||
1224 ' ) ';
1225
1226
1227 p_sql(39) := ' SELECT rp.* ' ||
1228 ' FROM rcv_parameters rp ' ||
1229 ' WHERE exists ' ||
1230 ' (SELECT 1 ' ||
1231 ' FROM po_requisition_headers_all prh , ' ||
1232 ' po_requisition_lines_all prl , ' ||
1233 ' financials_system_params_all fsp, ' ||
1234 ' rcv_shipment_headers rsh, ' ||
1235 ' rcv_shipment_lines rsl ' ||
1236 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
1237 ' and rsh.ship_to_org_id = ' || l_org_id ||
1238 ' and prh.segment1 = ' || '''' || l_req_num || '''' ||
1239 ' and prh.org_id = ' || l_ou_id ||
1240
1241 ' AND prh.requisition_header_id = prl.requisition_header_id ' ||
1242 ' AND rsh.shipment_header_id = rsl.shipment_header_id ' ||
1243 ' AND rsl.requisition_line_id = prl.requisition_line_id ' ||
1244 ' AND prh.type_lookup_code = ''INTERNAL'' ' ||
1245 ' AND (prl.destination_organization_id = rp.organization_id ' ||
1246 ' OR prl.source_organization_id = rp.organization_id ' ||
1247 ' OR (prh.org_id = fsp.org_id ' ||
1248 ' AND rp.organization_id = fsp.inventory_organization_id ) ) ' ||
1249 ' ) ';
1250
1251 p_sql(40) := ' SELECT lookup_code , ' ||
1252 ' meaning , ' ||
1253 ' enabled_flag , ' ||
1254 ' start_date_active , ' ||
1255 ' end_date_active ' ||
1256 ' FROM mfg_lookups ' ||
1257 ' WHERE lookup_type = ''MTL_LOT_CONTROL'' ';
1258
1259 p_sql(41) := ' SELECT lookup_code , ' ||
1260 ' meaning , ' ||
1261 ' enabled_flag , ' ||
1262 ' start_date_active , ' ||
1263 ' end_date_active ' ||
1264 ' FROM mfg_lookups ' ||
1265 ' WHERE lookup_type = ''MTL_LOT_GENERATION'' ';
1266
1267
1268 p_sql(42) := ' SELECT lookup_code , ' ||
1269 ' meaning , ' ||
1270 ' enabled_flag , ' ||
1271 ' start_date_active , ' ||
1272 ' end_date_active ' ||
1273 ' FROM mfg_lookups ' ||
1274 ' WHERE lookup_type = ''MTL_LOT_UNIQUENESS'' ';
1275
1276
1277 p_sql(43) := ' SELECT lookup_type , ' ||
1278 ' lookup_code , ' ||
1279 ' meaning , ' ||
1280 ' enabled_flag , ' ||
1281 ' start_date_active , ' ||
1282 ' end_date_active ' ||
1283 ' FROM mfg_lookups ' ||
1284 ' WHERE lookup_type = ''MTL_SERIAL_NUMBER'' ';
1285
1286
1287 p_sql(44) := ' SELECT lookup_type , ' ||
1288 ' lookup_code , ' ||
1289 ' meaning , ' ||
1290 ' enabled_flag , ' ||
1291 ' start_date_active , ' ||
1292 ' end_date_active ' ||
1293 ' FROM mfg_lookups ' ||
1294 ' WHERE lookup_type = ''MTL_SERIAL_NUMBER_TYPE'' ';
1295
1296
1297 p_sql(45) := ' SELECT lookup_type , ' ||
1298 ' lookup_code , ' ||
1299 ' meaning , ' ||
1300 ' enabled_flag , ' ||
1301 ' start_date_active , ' ||
1302 ' end_date_active ' ||
1303 ' FROM mfg_lookups ' ||
1307 p_sql(46) := ' SELECT lookup_type , ' ||
1304 ' WHERE lookup_type = ''MTL_SERIAL_GENERATION'' ';
1305
1306
1308 ' lookup_code , ' ||
1309 ' meaning , ' ||
1310 ' enabled_flag , ' ||
1311 ' start_date_active , ' ||
1312 ' end_date_active ' ||
1313 ' FROM mfg_lookups ' ||
1314 ' WHERE lookup_type = ''SERIAL_NUM_STATUS'' ';
1315
1316
1317 RETURN;
1318 END;
1319
1320 END IO_DIAGNOSTICS3 ;