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