[Home] [Help]
PACKAGE BODY: APPS.IOT_DIAGNOSTICS
Source
1 PACKAGE BODY iot_diagnostics AS
2 /* $Header: INVDIOT1B.pls 120.0.12000000.1 2007/08/09 06:49:17 ssadasiv noship $ */
3
4 PROCEDURE shipment_num_sql(p_org_id IN NUMBER, p_shipment_num IN VARCHAR2, p_receipt_num IN VARCHAR2, p_sql IN OUT
5 NOCOPY INV_DIAG_RCV_PO_COMMON.sqls_list) IS
6 l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
7 l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
8 l_org_id rcv_shipment_headers.organization_id%TYPE := p_org_id;
9 l_line_num rcv_shipment_lines.line_num%TYPE := NULL;
10
11 BEGIN
12
13 p_sql(1):= ' select distinct rhi.* ' ||
14 ' from rcv_headers_interface rhi ' ||
15 ' where rhi.shipment_num = ' || '''' || l_shipment_num || '''' ||
16 ' or exists (' ||
17 ' select 1 ' ||
18 ' from rcv_shipment_headers rsh ' ||
19 ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
20 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
21 ' and rhi.receipt_header_id = rsh.shipment_header_id) ';
22
23
24 p_sql(2):= ' select distinct rti.* ' ||
25 ' from rcv_transactions_interface rti ' ||
26 ' where (exists (' ||
27 ' select 1 ' ||
28 ' from rcv_headers_interface rhi ' ||
29 ' where rti.header_interface_id = rhi.header_interface_id ' ||
30 ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
31 ' or exists (' ||
32 ' select 1 ' ||
33 ' from rcv_shipment_headers rsh ' ||
34 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
35 ' and rsh.ship_to_org_id = ' || l_org_id ||
36 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
37 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
38 ' or exists (' ||
39 ' select 2 ' ||
40 ' from rcv_shipment_headers rsh ' ||
41 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
42 ' and rsh.ship_to_org_id = ' || l_org_id ||
43 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
44 ' and (rti.shipment_header_id = rsh.shipment_header_id ' ||
45 ' OR rti.shipment_num = nvl(' || '''' || l_shipment_num || '''' || ',rsh.shipment_num) ) ) ) ';
46
47
48
49 p_sql(3):= ' select distinct pie.* ' ||
50 ' from po_interface_errors pie ' ||
51 ' where exists (' ||
52 ' select 1 ' ||
53 ' from rcv_headers_interface rhi ' ||
54 ' where (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
55 ' or exists (' ||
56 ' select 1 ' ||
57 ' from rcv_shipment_headers rsh ' ||
58 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
59 ' and rsh.ship_to_org_id = ' || l_org_id ||
60 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ' ||
61 ' and ((pie.interface_header_id = rhi.header_interface_id ' ||
62 ' and pie.table_name = ''RCV_HEADERS_INTERFACE'') ' ||
63 ' or exists (' ||
64 ' select 2 ' ||
65 ' from rcv_transactions_interface rti ' ||
66 ' where rti.header_interface_id = rhi.header_interface_id ' ||
67 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
68 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'' ) ) ) ' ||
69 ' or exists (' ||
70 ' select 2 ' ||
71 ' from rcv_transactions_interface rti , rcv_shipment_headers rsh ' ||
72 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
73 ' and rsh.ship_to_org_id = ' || l_org_id ||
74 ' and rti.shipment_header_id = rsh.shipment_header_id ' ||
75 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
76 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ';
77
78
79
80 p_sql(4):= ' select distinct rsh.* ' ||
81 ' from rcv_shipment_headers rsh' ||
82 ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
83 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
84 ' and rsh.ship_to_org_id = ' || l_org_id ||
85 ' order by rsh.shipment_header_id ';
86
87
88
89 p_sql(5):= ' select distinct rsl.* ' ||
90 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl ' ||
91 ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
92 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
93 ' and rsh.ship_to_org_id = ' || l_org_id ||
94 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
95 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
96 ' order by rsl.shipment_header_id , rsl.shipment_line_id ';
97
98
99
100 p_sql(6):= ' select distinct rt.* ' ||
101 ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
102 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
103 ' and rsh.ship_to_org_id = ' || l_org_id ||
104 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
105 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
106 ' order by rt.shipment_header_id , rt.shipment_line_id , rt.transaction_id ';
107
108
109
110 p_sql(7):= ' select distinct ms.* ' ||
111 ' from mtl_supply ms , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
112 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
113 ' and rsh.ship_to_org_id = ' || l_org_id ||
114 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
115 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
116 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
117 ' and rsl.shipment_line_id = ms.shipment_line_id ';
118
119
120
121 p_sql(8):= ' select distinct rs.* ' ||
122 ' from rcv_supply rs , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
123 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
124 ' and rsh.ship_to_org_id = ' || l_org_id ||
125 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
126 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
127 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
128 ' and rs.shipment_line_id = rsl.shipment_line_id ';
129
130
131 p_sql(9):= ' select distinct mmtt.* ' ||
132 ' from mtl_material_transactions_temp mmtt ' ||
133 ' where mmtt.shipment_number = ' || '''' || l_shipment_num || '''' ||
134 ' order by mmtt.organization_id , mmtt.transaction_date ';
135
136
137 p_sql(10):= ' select distinct mmt.*' ||
138 ' from mtl_material_transactions mmt' ||
139 ' where exists (select 1' ||
140 ' from rcv_shipment_headers rsh' ||
141 ' where mmt.shipment_number = rsh.shipment_num' ||
142 ' and rsh.shipment_num= ' || '''' || l_shipment_num || '''' ||
143 ' and rsh.ship_to_org_id = ' || l_org_id ||
144 ' )' ||
145 ' or exists (select 2 ' ||
146 ' from rcv_transactions rt, ' ||
147 ' rcv_shipment_headers rsh' ||
148 ' where rt.transaction_id = mmt.rcv_transaction_id' ||
149 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
150 ' and rsh.shipment_num= ' || '''' || l_shipment_num || '''' ||
151 ' and rsh.ship_to_org_id = ' || l_org_id ||') ';
152
153
154 p_sql(11):= ' select distinct mln.* ' ||
155 ' from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt ' ||
156 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
157 ' and mmt.transaction_id = mtln.transaction_id ' ||
158 ' and mln.lot_number = mtln.lot_number order by mln.organization_id , mln.inventory_item_id , mln.lot_number ';
159
160
161
162 p_sql(12):= ' select distinct mtln.* ' ||
163 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt ' ||
164 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
165 ' and mmt.transaction_id = mtln.transaction_id order by mtln.organization_id , mtln.inventory_item_id , mtln.lot_number
166 ';
167
168
169
170 p_sql(13):= ' select distinct mtli.* ' ||
171 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti ' ||
172 ' where mti.shipment_number = ' || '''' || l_shipment_num || '''' ||
173 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
174 ' union all ' ||
175 ' select mtli.* ' ||
176 ' from mtl_transaction_lots_interface mtli , rcv_transactions_interface rti ' ||
177 ' where (exists (' ||
178 ' select 1 ' ||
179 ' from rcv_headers_interface rhi ' ||
180 ' where rti.header_interface_id = rhi.header_interface_id ' ||
181 ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
182 ' or exists (' ||
183 ' select 1 ' ||
184 ' from rcv_shipment_headers rsh ' ||
185 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
186 ' and rsh.ship_to_org_id = ' || l_org_id ||
187 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
188 ' or exists (' ||
189 ' select 2 ' ||
190 ' from rcv_shipment_headers rsh ' ||
191 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
192 ' and rsh.ship_to_org_id = ' || l_org_id ||
193 ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
194 ' and mtli.product_transaction_id = rti.interface_transaction_id ';
195
196
197
198 p_sql(14):= ' select distinct mtlt.* ' ||
199 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt ' ||
200 ' where mmtt.shipment_number = ' || '''' || l_shipment_num || '''' ||
201 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
202
203
204
205 p_sql(15):= ' select distinct rls.* ' ||
206 ' from rcv_lots_supply rls , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
207 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
208 ' and rsh.ship_to_org_id = ' || l_org_id ||
209 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
210 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
211 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
212 ' and rsl.shipment_line_id = rls.shipment_line_id ' ||
213 ' order by rls.shipment_line_id, rls.lot_num';
214
215
216
217 p_sql(16):= ' select distinct rlt.* ' ||
218 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
219 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
220 ' and rsh.ship_to_org_id = ' || l_org_id ||
221 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
222 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
223 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
224 ' and rsl.shipment_line_id = rlt.shipment_line_id order by rlt.shipment_line_id , rlt.item_id , rlt.lot_num ';
225
226
227
228 p_sql(17):= ' select distinct rli.* ' ||
229 ' from rcv_lots_interface rli , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
230 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
231 ' and rsh.ship_to_org_id = ' || l_org_id ||
232 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
233 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
234 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
235 ' and rsl.item_id = Nvl(rli.shipment_line_id,rsl.item_id )' ||
236 ' and rsl.item_id = rli.item_id' ||
237 ' order by rli.item_id , rli.lot_num ';
238
239
240 p_sql(18):=' select distinct rss.* ' ||
241 ' from rcv_serials_supply rss , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rt ' ||
242 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
243 ' and rsh.ship_to_org_id = ' || l_org_id ||
244 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
245 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
246 ' and rsl.shipment_line_id = rt.shipment_line_id ' ||
247 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
248 ' and rss.shipment_line_id = rsl.shipment_line_id' ||
249 ' order by rss.serial_num ';
250
251
252
253 p_sql(19):= ' select distinct rst.* ' ||
254 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
255 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
256 ' and rsh.ship_to_org_id = ' || l_org_id ||
257 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
258 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
259 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
260 ' and rst.shipment_line_id = rsl.shipment_line_id ' ||
261 ' order by rst.shipment_line_id , rst.serial_num ';
262
263
264
265 p_sql(20):= ' select distinct rsi.* ' ||
266 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
267 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
268 ' and rsh.ship_to_org_id = ' || l_org_id ||
269 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
270 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
271 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
272 ' and rsi.item_id = rsl.item_id ' ||
273 ' and rsi.organization_id = rsl.to_organization_id ' ||
274 ' order by rsi.organization_id , rsi.item_id , rsi.fm_serial_num ';
275
276
277
278 p_sql(21):= ' select distinct msn.* ' ||
279 ' from mtl_serial_numbers msn , mtl_material_transactions mmt , rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
280 ' where msn.last_transaction_id = mmt.transaction_id ' ||
281 ' and mmt.shipment_number = rsh.shipment_num' ||
282 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
283 ' and rsh.ship_to_org_id = ' || l_org_id ||
284 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
285 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
286 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num) order by msn.inventory_item_id ,
287 msn.serial_number ';
288
289
290
291 p_sql(22):= ' select distinct msnt.* ' ||
292 ' from mtl_serial_numbers_temp msnt' ||
293 ' where (exists (SELECT 1 ' ||
294 ' from mtl_material_transactions_temp mmtt ' ||
295 ' where mmtt.shipment_number = ' || '''' || l_shipment_num || '''' ||
296 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
297 ' or exists (' ||
298 ' select 1 ' ||
299 ' from mtl_transaction_lots_temp mtlt ' ||
300 ' where mtlt.serial_transaction_temp_id = msnt.transaction_temp_id ' ||
301 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id) ) ' ||
302 ' )' ||
303 ' OR exists (SELECT 2 ' ||
304 ' from rcv_transactions_interface rti ' ||
305 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
306 ' and (msnt.transaction_temp_id = rti.interface_transaction_id ' ||
307 ' or exists (' ||
308 ' select 1 ' ||
309 ' from mtl_transaction_lots_temp mtlt ' ||
310 ' where mtlt.serial_transaction_temp_id = msnt.transaction_temp_id ' ||
311 ' and rti.interface_transaction_id = mtlt.transaction_temp_id) )' ||
312 ' )' ||
313 ' ) ';
314
315
316 p_sql(23):= ' select distinct msni.* ' ||
317 ' from mtl_serial_numbers_interface msni , mtl_transactions_interface mti , mtl_system_items msi ' ||
318 ' where mti.shipment_number = ' || '''' || l_shipment_num || '''' ||
319 ' and msi.inventory_item_id = mti.inventory_item_id ' ||
320 ' and msi.organization_id = mti.organization_id ' ||
321 ' and msi.serial_number_control_code <> 1 ' ||
322 ' and ((msni.transaction_Interface_id = mti.transaction_Interface_id ' ||
323 ' and msi.lot_control_code = 1 ) ' ||
324 ' or (exists (' ||
325 ' select 3 ' ||
326 ' from mtl_transaction_lots_interface mtli ' ||
327 ' where mtli.transaction_Interface_id = mti.transaction_Interface_id ' ||
328 ' and msni.transaction_Interface_id = mtli.serial_transaction_temp_id ) ' ||
329 ' and (msi.lot_control_code <> 1 ) ) ) ' ||
330 ' union all ' ||
331 ' select msni.* ' ||
332 ' from mtl_serial_numbers_interface msni , rcv_transactions_interface rti ' ||
333 ' where (exists (' ||
334 ' select 1 ' ||
335 ' from rcv_headers_interface rhi ' ||
336 ' where rti.header_interface_id = rhi.header_interface_id ' ||
337 ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
338 ' or exists (' ||
342 ' and rsh.ship_to_org_id = ' || l_org_id ||
339 ' select 1 ' ||
340 ' from rcv_shipment_headers rsh ' ||
341 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
343 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
344 ' or exists (' ||
345 ' select 2 ' ||
346 ' from rcv_shipment_headers rsh ' ||
347 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
348 ' and rsh.ship_to_org_id = ' || l_org_id ||
349 ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
350 ' and msni.product_transaction_id = rti.interface_transaction_id ';
351
352
353
354 p_sql(24):= ' select distinct mut.* ' ||
355 ' from mtl_unit_transactions mut , mtl_material_transactions mmt , rcv_transactions rt, rcv_shipment_headers rsh,
356 rcv_shipment_lines rsl' ||
357 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
358 ' and rsh.ship_to_org_id = ' || l_org_id ||
359 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
360 ' and rsl.shipment_line_id = rt.shipment_line_id ' ||
361 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
362 ' and rt.transaction_id = mmt.rcv_transaction_id ' ||
363 ' and rt.shipment_header_id = rsh.shipment_header_id ' ||
364 ' and (mut.transaction_id = mmt.transaction_id ' ||
365 ' or (exists (' ||
366 ' select 2 ' ||
367 ' from mtl_transaction_lot_numbers mtln ' ||
368 ' where mut.transaction_id = mtln.serial_transaction_id ' ||
369 ' and mmt.transaction_id = mtln.transaction_id) ) ) order by mut.inventory_item_id , mut.serial_number ';
370
371
372
373
374 p_sql(25):= ' select distinct msi.* ' ||
375 ' from mtl_material_transactions mmt , mtl_system_items msi ' ||
376 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
377 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
378 ' and msi.organization_id in (mmt.organization_id , mmt.transfer_organization_id)' ||
379 ' order by msi.organization_id , msi.inventory_item_id ';
380
381
382
383 p_sql(26):= ' select distinct mtt.transaction_type_id , mtt.transaction_type_name ,
384 mtt.transaction_source_type_id , mtt.transaction_action_id , ' ||
385 ' mtt.user_defined_flag , mtt.disable_date ' ||
386 ' from mtl_transaction_types mtt , mtl_material_transactions mmt ' ||
387 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
388 ' and mtt.transaction_type_id = mmt.transaction_type_id order by mtt.transaction_type_id ';
389
390
391 p_sql(27):= ' select distinct ood.* ' ||
392 ' from org_organization_definitions ood ' ||
393 ' where exists (SELECT 1 from mtl_material_transactions mmt' ||
394 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
395 ' and (ood.organization_id = mmt.organization_id or ood.organization_id = mmt.transfer_organization_id )
396 )' ||
397 ' OR exists (SELECT 1 ' ||
398 ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
399 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
400 ' and rsh.ship_to_org_id = ' || l_org_id ||
401 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
402 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
403 ' and (rsh.organization_id = ood.organization_id or rsh.ship_to_org_id = ood.organization_id)' ||
404 ' )' ||
405 ' order by ood.organization_id ';
406
407
408
409 p_sql(28):= ' select distinct miop.*' ||
410 ' from mtl_interorg_parameters miop , mtl_material_transactions mmt' ||
411 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
412 ' and mmt.transfer_organization_id = ' || l_org_id ||
413 ' and miop.from_organization_id = mmt.organization_id' ||
414 ' and miop.to_organization_id = mmt.transfer_organization_id' ||
415 ' and mmt.rcv_transaction_id is null ';
416
417
418
419 p_sql(29):= ' select distinct mp.* ' ||
420 ' from mtl_parameters mp , mtl_material_transactions mmt ' ||
421 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
422 ' and (mp.organization_id = mmt.organization_id ' ||
423 ' or mp.organization_id = mmt.transfer_organization_id ) order by mp.organization_id ';
424
425
426
427 p_sql(30):= ' select distinct rp.* ' ||
428 ' from rcv_parameters rp , mtl_material_transactions mmt ' ||
429 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
430 ' and (rp.organization_id = mmt.organization_id ' ||
431 ' or rp.organization_id = mmt.transfer_organization_id ) order by rp.organization_id ';
432
433
434
435 p_sql(31):= ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
436 ' from mfg_lookups ' ||
437 ' where lookup_type = ''MTL_LOT_CONTROL'' ';
438
439
440
441 p_sql(32):= ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
442 ' from mfg_lookups ' ||
443 ' where lookup_type = ''MTL_LOT_GENERATION'' ';
444
445
446
447 p_sql(33):= ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
448 ' from mfg_lookups ' ||
449 ' where lookup_type = ''MTL_LOT_UNIQUENESS'' ';
450
451
452 p_sql(34):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
453 ' from mfg_lookups ' ||
454 ' where lookup_type = ''MTL_SERIAL_NUMBER'' ';
455
456
457
458 p_sql(35):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
459 ' from mfg_lookups ' ||
463
460 ' where lookup_type = ''MTL_SERIAL_NUMBER_TYPE'' ';
461
462
464 p_sql(36):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
465 ' from mfg_lookups ' ||
466 ' where lookup_type = ''MTL_SERIAL_GENERATION'' ';
467
468
469
470 p_sql(37):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
471 ' from mfg_lookups ' ||
472 ' where lookup_type = ''SERIAL_NUM_STATUS'' ';
473
474
475 RETURN;
476 END;
477
478
479
480 PROCEDURE shipment_line_num_sql(p_org_id IN NUMBER,p_shipment_num IN VARCHAR2,
481 p_shipment_line_num IN NUMBER, p_receipt_num IN VARCHAR2, p_sql IN OUT NOCOPY
482 INV_DIAG_RCV_PO_COMMON.sqls_list) IS
483
484 l_line_num rcv_shipment_lines.line_num%TYPE := p_shipment_line_num;
485 l_shipment_num rcv_shipment_headers.shipment_num%TYPE := p_shipment_num;
486 l_receipt_num rcv_shipment_headers.receipt_num%TYPE := p_receipt_num;
487 l_org_id rcv_shipment_headers.ship_to_org_id%TYPE := p_org_id;
488
489
490 BEGIN
491
492
493 p_sql(1):= ' select distinct rhi.* ' ||
494 ' from rcv_headers_interface rhi ' ||
495 ' where rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
496 ' or exists (' ||
497 ' select 1 ' ||
498 ' from rcv_shipment_headers rsh ' ||
499 ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
500 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
501 ' and rhi.receipt_header_id = rsh.shipment_header_id) ';
502
503
504 p_sql(2):= ' select distinct rti.* ' ||
505 ' from rcv_transactions_interface rti ' ||
506 ' where (exists (' ||
507 ' select 1 ' ||
508 ' from rcv_headers_interface rhi ' ||
509 ' where rti.header_interface_id = rhi.header_interface_id ' ||
510 ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
511 ' or exists (' ||
512 ' select 1 ' ||
513 ' from rcv_shipment_headers rsh ' ||
514 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
515 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
516 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
517 ' or exists (' ||
518 ' select 2 ' ||
519 ' from rcv_shipment_headers rsh ' ||
520 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
521 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
522 ' and (rti.shipment_header_id = rsh.shipment_header_id ' ||
523 ' OR rti.shipment_num = nvl(' || '''' || l_shipment_num || '''' || ',rsh.shipment_num) ) ) ) ';
524
525
526
527 p_sql(3):= ' select distinct pie.* ' ||
528 ' from po_interface_errors pie ' ||
529 ' where exists (' ||
530 ' select 1 ' ||
531 ' from rcv_headers_interface rhi ' ||
532 ' where (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
533 ' or exists (' ||
534 ' select 1 ' ||
535 ' from rcv_shipment_headers rsh ' ||
536 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
537 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ' ||
538 ' and ((pie.interface_header_id = rhi.header_interface_id ' ||
539 ' and pie.table_name = ''RCV_HEADERS_INTERFACE'') ' ||
540 ' or exists (' ||
541 ' select 2 ' ||
542 ' from rcv_transactions_interface rti ' ||
543 ' where rti.header_interface_id = rhi.header_interface_id ' ||
544 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
545 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'' ) ) ) ' ||
546 ' or exists (' ||
547 ' select 2 ' ||
548 ' from rcv_transactions_interface rti , rcv_shipment_headers rsh ' ||
549 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
550 ' and rti.shipment_header_id = rsh.shipment_header_id ' ||
551 ' and pie.interface_line_id = rti.interface_transaction_id ' ||
552 ' and pie.table_name = ''RCV_TRANSACTIONS_INTERFACE'') ';
553
554
555
556 p_sql(4):= ' select distinct rsh.* ' ||
557 ' from rcv_shipment_headers rsh' ||
558 ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
559 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
560 ' order by rsh.shipment_header_id ';
561
562
563
564 p_sql(5):= ' select distinct rsl.* ' ||
565 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl ' ||
566 ' where rsh.receipt_source_code = ''INVENTORY'' ' ||
567 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
568 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
569 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
570 ' order by rsl.shipment_header_id , rsl.shipment_line_id ';
571
572
573
574 p_sql(6):= ' select distinct rt.* ' ||
575 ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
576 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
577 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
578 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
579 ' order by rt.shipment_header_id , rt.shipment_line_id , rt.transaction_id ';
580
581
582
583 p_sql(7):= ' select distinct ms.* ' ||
584 ' from mtl_supply ms , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
585 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
586 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
587 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
591
588 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
589 ' and rsl.shipment_line_id = ms.shipment_line_id ';
590
592
593 p_sql(8):= ' select distinct rs.* ' ||
594 ' from rcv_supply rs , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
595 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
596 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
597 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
598 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
599 ' and rs.shipment_line_id = rsl.shipment_line_id ';
600
601
602
603 p_sql(9):= ' select distinct mmtt.* ' ||
604 ' from mtl_material_transactions_temp mmtt ' ||
605 ' where mmtt.shipment_number = ' || '''' || l_shipment_num || '''' ||
606 ' order by mmtt.organization_id , mmtt.transaction_date ';
607
608 p_sql(10):= ' select distinct mmt.*' ||
609 ' from mtl_material_transactions mmt' ||
610 ' where exists (select 1' ||
611 ' from rcv_shipment_headers rsh, ' ||
612 ' rcv_shipment_lines rsl' ||
613 ' where mmt.shipment_number = rsh.shipment_num' ||
614 ' and rsh.shipment_num= ' || '''' || l_shipment_num || '''' ||
615 ' and rsh.ship_to_org_id = '|| l_org_id ||
616 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
617 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
618 ' )' ||
619 ' or exists (select 2 ' ||
620 ' from rcv_transactions rt , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
621 ' where rt.transaction_id = mmt.rcv_transaction_id' ||
622 ' and rt.shipment_header_id = rsh.shipment_header_id' ||
623 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
624 ' and rsh.ship_to_org_id = ' || l_org_id ||
625 ' and rsl.shipment_header_id = rsh.shipment_header_id ' ||
626 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num))';
627
628
629 p_sql(11) := ' select distinct mln.* ' ||
630 ' from mtl_lot_numbers mln , mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt ' ||
631 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
632 ' and mmt.transaction_id = mtln.transaction_id ' ||
633 ' and mln.lot_number = mtln.lot_number order by mln.organization_id , mln.inventory_item_id , mln.lot_number ';
634
635
636
637 p_sql(12):= ' select distinct mtln.* ' ||
638 ' from mtl_transaction_lot_numbers mtln , mtl_material_transactions mmt ' ||
639 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
640 ' and mmt.transaction_id = mtln.transaction_id order by mtln.organization_id , mtln.inventory_item_id , mtln.lot_number
641 ';
642
643
644
645 p_sql(13):= ' select distinct mtli.* ' ||
646 ' from mtl_transaction_lots_interface mtli , mtl_transactions_interface mti ' ||
647 ' where mti.shipment_number = ' || '''' || l_shipment_num || '''' ||
648 ' and mti.transaction_interface_id = mtli.transaction_interface_id ' ||
649 ' union all ' ||
650 ' select mtli.* ' ||
651 ' from mtl_transaction_lots_interface mtli , rcv_transactions_interface rti ' ||
652 ' where (exists (' ||
653 ' select 1 ' ||
654 ' from rcv_headers_interface rhi ' ||
655 ' where rti.header_interface_id = rhi.header_interface_id ' ||
656 ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
657 ' or exists (' ||
658 ' select 1 ' ||
659 ' from rcv_shipment_headers rsh ' ||
660 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
661 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
662 ' or exists (' ||
663 ' select 2 ' ||
664 ' from rcv_shipment_headers rsh ' ||
665 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
666 ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
667 ' and mtli.product_transaction_id = rti.interface_transaction_id ';
668
669
670
671 p_sql(14):= ' select distinct mtlt.* ' ||
672 ' from mtl_transaction_lots_temp mtlt , mtl_material_transactions_temp mmtt ' ||
673 ' where mmtt.shipment_number = ' || '''' || l_shipment_num || '''' ||
674 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id order by mtlt.transaction_temp_id , mtlt.lot_number ';
675
676
677 p_sql(15):= ' select distinct rls.* ' ||
678 ' from rcv_lots_supply rls , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
679 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
680 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
681 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
682 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
683 ' and rsl.shipment_line_id = rls.shipment_line_id ' ||
684 ' order by rls.shipment_line_id, rls.lot_num ';
685
686
687
688 p_sql(16):= ' select distinct rlt.* ' ||
689 ' from rcv_lot_transactions rlt , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
690 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
691 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
692 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
693 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
694 ' and rsl.shipment_line_id = rlt.shipment_line_id order by rlt.shipment_line_id , rlt.item_id , rlt.lot_num ';
695
696
697
698 p_sql(17):= ' select distinct rli.* ' ||
699 ' from rcv_lots_interface rli , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
700 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
701 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
705 ' and rsl.item_id = rli.item_id' ||
702 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
703 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
704 ' and rsl.item_id = Nvl(rli.shipment_line_id,rsl.item_id )' ||
706 ' order by rli.item_id , rli.lot_num ';
707
708
709 p_sql(18):= ' select distinct rss.* ' ||
710 ' from rcv_serials_supply rss , rcv_shipment_lines rsl , rcv_shipment_headers rsh , rcv_transactions rt ' ||
711 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
712 ' and rsh.ship_to_org_id = ' || l_org_id ||
713 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
714 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
715 ' and rsl.shipment_line_id = rt.shipment_line_id ' ||
716 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
717 ' and rss.shipment_line_id = rsl.shipment_line_id' ||
718 ' order by rss.serial_num ';
719
720
721
722 p_sql(19):= ' select distinct rst.* ' ||
723 ' from rcv_serial_transactions rst , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
724 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
725 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
726 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
727 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
728 ' and rst.shipment_line_id = rsl.shipment_line_id ' ||
729 ' order by rst.shipment_line_id , rst.serial_num ';
730
731
732
733 p_sql(20):= ' select distinct rsi.* ' ||
734 ' from rcv_serials_interface rsi , rcv_shipment_lines rsl , rcv_shipment_headers rsh ' ||
735 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
736 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
737 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
738 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
739 ' and rsi.item_id = rsl.item_id ' ||
740 ' and rsi.organization_id = rsl.to_organization_id ' ||
741 ' order by rsi.organization_id , rsi.item_id , rsi.fm_serial_num ';
742
743
744 p_sql(21):= ' select distinct msn.* ' ||
745 ' from mtl_serial_numbers msn , mtl_material_transactions mmt , rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
746 ' where msn.last_transaction_id = mmt.transaction_id ' ||
747 ' and mmt.shipment_number = rsh.shipment_num' ||
748 ' and rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
749 ' and rsh.receipt_source_code = ''INVENTORY'' ' ||
750 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
751 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num) order by msn.inventory_item_id ,
752 msn.serial_number ';
753
754
755 p_sql(22):= ' select distinct msnt.* ' ||
756 ' from mtl_serial_numbers_temp msnt' ||
757 ' where (exists (SELECT 1 ' ||
758 ' from mtl_material_transactions_temp mmtt ' ||
759 ' where mmtt.shipment_number = ' || '''' || l_shipment_num || '''' ||
760 ' and (msnt.transaction_temp_id = mmtt.transaction_temp_id ' ||
761 ' or exists (' ||
762 ' select 1 ' ||
763 ' from mtl_transaction_lots_temp mtlt ' ||
764 ' where mtlt.serial_transaction_temp_id = msnt.transaction_temp_id ' ||
765 ' and mmtt.transaction_temp_id = mtlt.transaction_temp_id) ) ' ||
766 ' )' ||
767 ' OR exists (SELECT 2 ' ||
768 ' from rcv_transactions_interface rti ' ||
769 ' where rti.shipment_num = ' || '''' || l_shipment_num || '''' ||
770 ' and (msnt.transaction_temp_id = rti.interface_transaction_id ' ||
771 ' or exists (' ||
772 ' select 1 ' ||
773 ' from mtl_transaction_lots_temp mtlt ' ||
774 ' where mtlt.serial_transaction_temp_id = msnt.transaction_temp_id ' ||
775 ' and rti.interface_transaction_id = mtlt.transaction_temp_id) )' ||
776 ' )' ||
777 ' ) ';
778
779
780 p_sql(23):= ' select distinct msni.* ' ||
781 ' from mtl_serial_numbers_interface msni , mtl_transactions_interface mti , mtl_system_items msi ' ||
782 ' where mti.shipment_number = ' || '''' || l_shipment_num || '''' ||
783 ' and msi.inventory_item_id = mti.inventory_item_id ' ||
784 ' and msi.organization_id = mti.organization_id ' ||
785 ' and msi.serial_number_control_code <> 1 ' ||
786 ' and ((msni.transaction_Interface_id = mti.transaction_Interface_id ' ||
787 ' and msi.lot_control_code = 1 ) ' ||
788 ' or (exists (' ||
789 ' select 3 ' ||
790 ' from mtl_transaction_lots_interface mtli ' ||
791 ' where mtli.transaction_Interface_id = mti.transaction_Interface_id ' ||
792 ' and msni.transaction_Interface_id = mtli.serial_transaction_temp_id ) ' ||
793 ' and (msi.lot_control_code <> 1 ) ) ) ' ||
794 ' union all ' ||
795 ' select msni.* ' ||
796 ' from mtl_serial_numbers_interface msni , rcv_transactions_interface rti ' ||
797 ' where (exists (' ||
798 ' select 1 ' ||
799 ' from rcv_headers_interface rhi ' ||
800 ' where rti.header_interface_id = rhi.header_interface_id ' ||
801 ' and (rhi.shipment_num = ' || '''' || l_shipment_num || ''''||
802 ' or exists (' ||
803 ' select 1 ' ||
804 ' from rcv_shipment_headers rsh ' ||
805 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
806 ' and rhi.receipt_header_id = rsh.shipment_header_id) ) ) ' ||
807 ' or exists (' ||
808 ' select 2 ' ||
809 ' from rcv_shipment_headers rsh ' ||
810 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
814
811 ' and rti.shipment_header_id = rsh.shipment_header_id ) ) ' ||
812 ' and msni.product_transaction_id = rti.interface_transaction_id ';
813
815
816 p_sql(24):= ' select distinct mut.* ' ||
817 ' from mtl_unit_transactions mut , mtl_material_transactions mmt , rcv_transactions rt, rcv_shipment_headers rsh,
818 rcv_shipment_lines rsl' ||
819 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
820 ' and rsh.ship_to_org_id = ' || l_org_id ||
821 ' and rsh.shipment_header_id = rsl.shipment_header_id ' ||
822 ' and rsl.shipment_line_id = rt.shipment_line_id ' ||
823 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
824 ' and rt.transaction_id = mmt.rcv_transaction_id ' ||
825 ' and rt.shipment_header_id = rsh.shipment_header_id ' ||
826 ' and (mut.transaction_id = mmt.transaction_id ' ||
827 ' or (exists (' ||
828 ' select 2 ' ||
829 ' from mtl_transaction_lot_numbers mtln ' ||
830 ' where mut.transaction_id = mtln.serial_transaction_id ' ||
831 ' and mmt.transaction_id = mtln.transaction_id) ) ) order by mut.inventory_item_id , mut.serial_number ';
832
833
834
835 p_sql(25):= ' select distinct msi.* ' ||
836 ' from mtl_material_transactions mmt , mtl_system_items msi , rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
837 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
838 ' and mmt.shipment_number = rsh.shipment_num' ||
839 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
840 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
841 ' and rsl.item_id = msi.inventory_item_id ' ||
842 ' and msi.inventory_item_id = mmt.inventory_item_id ' ||
843 ' and msi.organization_id in (mmt.organization_id , mmt.transfer_organization_id) ' ||
844 ' order by msi.organization_id , msi.inventory_item_id ';
845
846
847
848
849 p_sql(26):= ' select distinct mtt.transaction_type_id , mtt.transaction_type_name ,
850 mtt.transaction_source_type_id , mtt.transaction_action_id , ' ||
851 ' mtt.user_defined_flag , mtt.disable_date ' ||
852 ' from mtl_transaction_types mtt , mtl_material_transactions mmt ' ||
853 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
854 ' and mtt.transaction_type_id = mmt.transaction_type_id' ||
855 ' and exists ( SELECT 1' ||
856 ' from rcv_shipment_headers rsh , rcv_shipment_lines rsl' ||
857 ' where mmt.shipment_number = rsh.shipment_num' ||
858 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
859 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num))' ||
860 ' order by mtt.transaction_type_id ';
861
862
863
864 p_sql(27):= ' select distinct ood.* ' ||
865 ' from org_organization_definitions ood ' ||
866 ' where exists (SELECT 1 from mtl_material_transactions mmt' ||
867 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
868 ' and (ood.organization_id = mmt.organization_id or ood.organization_id = mmt.transfer_organization_id )
869 )' ||
870 ' OR exists (SELECT 1 ' ||
871 ' from rcv_shipment_headers rsh, rcv_shipment_lines rsl' ||
872 ' where rsh.shipment_num = ' || '''' || l_shipment_num || '''' ||
873 ' and rsh.shipment_header_id = rsl.shipment_header_id' ||
874 ' and rsl.line_num = nvl(' || '''' || l_line_num || '''' || ',rsl.line_num)' ||
875 ' and (rsh.organization_id = ood.organization_id or rsh.ship_to_org_id = ood.organization_id)' ||
876 ' )' ||
877 ' order by ood.organization_id ';
878
879
880
881 p_sql(28):= ' select distinct miop.*' ||
882 ' from mtl_interorg_parameters miop , mtl_material_transactions mmt' ||
883 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
884 ' and mmt.transfer_organization_id = ' || l_org_id ||
885 ' and miop.from_organization_id = mmt.organization_id' ||
886 ' and miop.to_organization_id = mmt.transfer_organization_id' ||
887 ' and mmt.rcv_transaction_id is null ';
888
889
890
891 p_sql(29):= ' select distinct mp.* ' ||
892 ' from mtl_parameters mp , mtl_material_transactions mmt ' ||
893 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
894 ' and (mp.organization_id = mmt.organization_id ' ||
895 ' or mp.organization_id = mmt.transfer_organization_id ) order by mp.organization_id ';
896
897
898
899 p_sql(30):= ' select distinct rp.* ' ||
900 ' from rcv_parameters rp , mtl_material_transactions mmt ' ||
901 ' where mmt.shipment_number = ' || '''' || l_shipment_num || '''' ||
902 ' and (rp.organization_id = mmt.organization_id ' ||
903 ' or rp.organization_id = mmt.transfer_organization_id ) order by rp.organization_id ';
904
905
906 p_sql(31):= ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
907 ' from mfg_lookups ' ||
908 ' where lookup_type = ''MTL_LOT_CONTROL'' ';
909
910
911
912 p_sql(32):= ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
913 ' from mfg_lookups ' ||
914 ' where lookup_type = ''MTL_LOT_GENERATION'' ';
915
916
917
918 p_sql(33):= ' select lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
919 ' from mfg_lookups ' ||
920 ' where lookup_type = ''MTL_LOT_UNIQUENESS'' ';
921
922 p_sql(34):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
923 ' from mfg_lookups ' ||
924 ' where lookup_type = ''MTL_SERIAL_NUMBER'' ';
925
926
927
928 p_sql(35):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
932
929 ' from mfg_lookups ' ||
930 ' where lookup_type = ''MTL_SERIAL_NUMBER_TYPE'' ';
931
933
934 p_sql(36):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
935 ' from mfg_lookups ' ||
936 ' where lookup_type = ''MTL_SERIAL_GENERATION'' ';
937
938
939
940 p_sql(37):= ' select lookup_type , lookup_code , meaning , enabled_flag , start_date_active , end_date_active ' ||
941 ' from mfg_lookups ' ||
942 ' where lookup_type = ''SERIAL_NUM_STATUS'' ';
943
944 RETURN;
945 END;
946
947 END IOT_DIAGNOSTICS ;
948
949