[Home] [Help]
PACKAGE BODY: APPS.CSI_DEBUG_PKG
Source
1 PACKAGE BODY csi_debug_pkg as
2 /* $Header: csidbugb.pls 120.3 2005/07/08 17:10:11 brmanesh noship $ */
3
4 l_mmt_tbl_cache mmt_tbl;
5 l_item_tbl_cache item_tbl;
6 l_inst_tbl_cache instance_tbl;
7
8 FUNCTION fill(
9 p_column in varchar2,
10 p_width in number,
11 p_side in varchar2 default 'R')
12 RETURN varchar2 is
13 l_column varchar2(2000);
14 BEGIN
15 l_column := nvl(p_column, ' ');
16 IF p_side = 'L' THEN
17 return(lpad(l_column, p_width, ' '));
18 ELSIF p_side = 'R' THEN
19 return(rpad(l_column, p_width, ' '));
20 END IF;
21 END fill;
22
23 PROCEDURE check_and_init_debug
24 IS
25 l_debug_level number;
26 l_debug_path varchar2(240);
27 l_utl_file_dir varchar2(2000);
28 BEGIN
29 g_utl_check_done := 'Y';
30
31 SELECT fnd_profile.value('csi_debug_level')
32 INTO l_debug_level
33 FROM sys.dual;
34
35 IF nvl(l_debug_level, 0) = 0 THEN
36 csi_t_gen_utility_pvt.g_debug_level := 10;
37 END IF;
38
39 SELECT fnd_profile.value('csi_logfile_path')
40 INTO l_debug_path
41 FROM sys.dual;
42
43 SELECT value
44 INTO l_utl_file_dir
45 FROM v$parameter
46 WHERE name = 'utl_file_dir';
47
48 IF l_debug_path not like '%'||l_utl_file_dir||'%' THEN
49 SELECT substr(l_utl_file_dir, 1,
50 decode(instr(l_utl_file_dir, ',', 1), 0,
51 length(l_utl_file_dir), instr(l_utl_file_dir, ',', 1)-1 ))
52 INTO l_debug_path
53 FROM sys.dual;
54 END IF;
55
56 csi_t_gen_utility_pvt.g_dir := l_debug_path;
57
58 --dbms_output.put_line('Output File : '||
59 --csi_t_gen_utility_pvt.g_dir||'/'||csi_t_gen_utility_pvt.g_file);
60
61 EXCEPTION
62 WHEN others THEN
63 null;
64 END check_and_init_debug;
65
66 PROCEDURE add(
67 p_message IN varchar2)
68 IS
69 BEGIN
70 IF g_utl_check_done = 'N' THEN
71 check_and_init_debug;
72 END IF;
73 csi_t_gen_utility_pvt.add(p_message);
74 fnd_file.put_line(fnd_file.output, p_message);
75 END add;
76
77 PROCEDURE blank_line IS
78 BEGIN
79 add(' ');
80 END blank_line;
81
82 PROCEDURE cache_instance_id(
83 p_inst_id IN number )
84 IS
85 l_new_ind binary_integer := 0;
86 l_cached boolean := FALSE;
87 BEGIN
88
89 IF nvl(p_inst_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
90
91 IF l_inst_tbl_cache.COUNT > 0 THEN
92 FOR l_ind IN l_inst_tbl_cache.FIRST..l_inst_tbl_cache.LAST
93 LOOP
94 IF p_inst_id = l_inst_tbl_cache(l_ind).instance_id THEN
95 l_cached := TRUE;
96 exit;
97 END IF;
98 END LOOP;
99 END IF;
100
101 IF not(l_cached) THEN
102 l_new_ind := l_inst_tbl_cache.COUNT + 1;
103 l_inst_tbl_cache(l_new_ind).instance_id := p_inst_id;
104 END IF;
105
106 END IF;
107
108 END cache_instance_id;
109
110 PROCEDURE cache_item_rec(
111 p_item_rec IN item_rec)
112 IS
113 l_ind binary_integer := 0;
114 BEGIN
115 l_ind := l_item_tbl_cache.COUNT + 1;
116 l_item_tbl_cache(l_ind) := p_item_rec;
117 END cache_item_rec;
118
119 PROCEDURE get_item_rec_from_cache(
120 p_item_id IN number,
121 p_organization_id IN number,
122 x_item_rec OUT nocopy item_rec,
123 x_cached OUT nocopy boolean)
124 IS
125 BEGIN
126 x_cached := FALSE;
127 IF l_item_tbl_cache.COUNT > 0 THEN
128 FOR l_ind IN l_item_tbl_cache.FIRST .. l_item_tbl_cache.LAST
129 LOOP
130 IF l_item_tbl_cache(l_ind).item_id = p_item_id AND
131 l_item_tbl_cache(l_ind).organization_id = p_organization_id
132 THEN
133 x_item_rec := l_item_tbl_cache(l_ind);
134 x_cached := TRUE;
135 exit;
136 END IF;
137 END LOOP;
138 END IF;
139 END get_item_rec_from_cache;
140
141 PROCEDURE get_item_rec(
142 p_item_id IN number,
143 p_organization_id IN number,
144 x_item_rec OUT nocopy item_rec)
145 IS
146 l_item_rec item_rec;
147 l_cached boolean := FALSE;
148 BEGIN
149
150 get_item_rec_from_cache(
151 p_item_id => p_item_id,
152 p_organization_id => p_organization_id,
153 x_item_rec => l_item_rec,
154 x_cached => l_cached);
155
156 IF NOT (l_cached) THEN
157 SELECT inventory_item_id,
158 organization_id,
159 reservable_type,
160 nvl(comms_nl_trackable_flag,'N'),
161 serial_number_control_code,
162 lot_control_code,
163 shippable_item_flag,
164 bom_item_type,
165 concatenated_segments,
166 primary_uom_code,
167 location_control_code,
168 revision_qty_control_code,
169 base_item_id,
170 pick_components_flag,
171 returnable_flag,
172 wip_supply_type,
173 planning_make_buy_code,
174 inventory_item_flag,
175 mtl_transactions_enabled_flag
176 INTO l_item_rec.item_id,
177 l_item_rec.organization_id,
178 l_item_rec.reservable_type,
179 l_item_rec.ib_trackable_flag,
180 l_item_rec.serial_code,
181 l_item_rec.lot_code,
182 l_item_rec.shippable_flag,
183 l_item_rec.bom_item_type,
184 l_item_rec.item,
185 l_item_rec.primary_uom_code,
186 l_item_rec.locator_code,
187 l_item_rec.revision_code,
188 l_item_rec.base_item_id,
189 l_item_rec.pick_flag,
190 l_item_rec.returnable_flag,
191 l_item_rec.wip_supply_type,
192 l_item_rec.make_buy_code,
193 l_item_rec.inventory_flag,
194 l_item_rec.inv_transactable_flag
195 FROM mtl_system_items_vl
196 WHERE inventory_item_id = p_item_id
197 AND organization_id = p_organization_id;
198
199 cache_item_rec(p_item_rec => l_item_rec);
200
201 END IF;
202
203 x_item_rec := l_item_rec;
204
205 END get_item_rec;
206
207 PROCEDURE get_next_level(
208 p_parent_line_id IN number,
209 p_order_lines IN order_lines,
210 x_next_level_lines OUT nocopy order_lines)
211 IS
212 x_ind binary_integer := 0;
213 l_ind binary_integer := 0;
214 BEGIN
215 IF p_order_lines.COUNT > 0 THEN
216 l_ind := 0;
217 LOOP
218
219 l_ind := p_order_lines.NEXT(l_ind);
220 EXIT when l_ind is null;
221
222 IF p_order_lines(l_ind).link_to_line_id = p_parent_line_id THEN
223 x_ind := x_ind + 1;
224 x_next_level_lines(x_ind) := p_order_lines(l_ind);
225 END IF;
226 END LOOP;
227 END IF;
228 END get_next_level;
229
230 FUNCTION already_pushed(
231 p_line_id IN number,
232 p_sorted_lines IN order_lines)
233 RETURN boolean
234 IS
235 l_return boolean := FALSE;
236 BEGIN
237 IF p_sorted_lines.COUNT > 0 THEN
238 FOR l_ind IN p_sorted_lines.FIRST .. p_sorted_lines.LAST
239 LOOP
240 IF p_sorted_lines(l_ind).line_id = p_line_id THEN
241 l_return := TRUE;
242 exit;
243 END IF;
244 END LOOP;
245 END IF;
246 RETURN l_return;
247 END already_pushed;
248
249 PROCEDURE get_model_lines(
250 p_model_line_id IN number,
251 p_order_lines IN order_lines,
252 x_model_lines OUT nocopy order_lines)
253 IS
254 l_ind binary_integer := 0;
255 x_ind binary_integer := 0;
256 BEGIN
257 IF p_order_lines.COUNT > 0 THEN
258 l_ind := 0;
259 LOOP
260 l_ind := p_order_lines.NEXT(l_ind);
261 EXIT when l_ind is null;
262
263 IF p_order_lines(l_ind).top_model_line_id = p_model_line_id THEN
264 x_ind := x_ind + 1;
265 x_model_lines(x_ind) := p_order_lines(l_ind);
266 END IF;
267
268 END LOOP;
269 END IF;
270 END get_model_lines;
271
272 PROCEDURE sort_the_order(
273 px_order_lines IN OUT nocopy order_lines)
274 IS
275
276 l_order_lines order_lines;
277 l_top_lines order_lines;
278 l_model_lines order_lines;
279 l_sorted_lines order_lines;
280 l_next_level_lines order_lines;
281
282 l_model_count number := 0;
283
284 l_ind binary_integer := 0;
285 l_s_ind binary_integer := 0;
286 l_t_ind binary_integer := 0;
287
288 BEGIN
289
290 l_order_lines := px_order_lines;
291
292 IF l_order_lines.COUNT > 0 THEN
293 -- get the top_model lines first
294 FOR l_ind IN l_order_lines.FIRST .. l_order_lines.LAST
295 LOOP
296
297 IF l_order_lines(l_ind).link_to_line_id is null THEN
298
299 l_t_ind := l_t_ind + 1;
300 l_top_lines(l_t_ind) := l_order_lines(l_ind);
301
302 END IF;
303
304 END LOOP;
305
306 IF l_top_lines.COUNT > 0 THEN
307
308 FOR l_t_ind IN l_top_lines.FIRST .. l_top_lines.LAST
309 LOOP
310
311 get_model_lines(
312 p_model_line_id => l_top_lines(l_t_ind).line_id,
313 p_order_lines => l_order_lines,
314 x_model_lines => l_model_lines);
315
316 l_model_count := l_model_lines.count;
317
318 l_s_ind := l_s_ind + 1;
319 l_sorted_lines(l_s_ind) := l_top_lines(l_t_ind);
320
321 l_model_count := l_model_count - 1;
322
323 --get the next level
324 get_next_level(
325 p_parent_line_id => l_top_lines(l_t_ind).line_id,
326 p_order_lines => l_model_lines,
327 x_next_level_lines => l_next_level_lines);
328
329 LOOP
330
331 IF l_model_count <= 0 THEN
332 exit;
333 END IF;
334
335 IF l_next_level_lines.count > 0 THEN
336
337 l_ind := 0;
338 LOOP
339
340 <<skip>>
341
342 l_ind := l_next_level_lines.NEXT(l_ind);
343 EXIT when l_ind is null;
344
345 IF already_pushed(l_next_level_lines(l_ind).line_id, l_sorted_lines) THEN
346 GOTO SKIP;
347 END IF;
348
349 l_s_ind := l_s_ind + 1;
350 l_sorted_lines(l_s_ind) := l_next_level_lines(l_ind);
351 l_model_count := l_model_count - 1;
352
353 get_next_level(
354 p_parent_line_id => l_sorted_lines(l_s_ind).line_id,
355 p_order_lines => l_model_lines,
356 x_next_level_lines => l_next_level_lines);
357
358 IF l_next_level_lines.count = 0 THEN
359 l_next_level_lines := l_model_lines;
360 END IF;
361
362 exit;
363
364 END LOOP;
365 END IF;
366
367 END LOOP;
368
369 END LOOP;
370
371 END IF;
372
373 END IF;
374
375 px_order_lines := l_sorted_lines;
376
377 END sort_the_order;
378
379 PROCEDURE dump_order_line(
380 p_order_line IN order_line)
381 IS
382 l_line varchar2(2000);
383 l_ib_ind varchar2(1);
384 BEGIN
385
386 SELECT decode(p_order_line.ib_trackable_flag, 'Y', '*', ' ')
387 INTO l_ib_ind
388 FROM sys.dual;
389
390 l_line := l_ib_ind||fill(p_order_line.line_id, 9)||
391 fill(p_order_line.line_number, 8)||
392 fill(lpad(' ', p_order_line.level*2, ' ')||p_order_line.item_number, 25)||
393 fill(p_order_line.identified_item_type, 14)||
394 fill(p_order_line.item_id, 9)||
395 fill(p_order_line.ship_from_org_id, 4)||
396 fill(p_order_line.order_qty, 5)||
397 fill(p_order_line.order_uom, 3)||
398 fill(p_order_line.link_to_line_id, 9)||
399 fill(p_order_line.line_status, 12);
400 add(l_line);
401 END dump_order_line;
402
403 PROCEDURE dump_order_summary(
404 p_order_number IN number,
405 px_order_lines IN OUT nocopy order_lines)
406 IS
407 l_order_lines order_lines;
408 l_line_header varchar2(2000);
409 BEGIN
410
411 l_order_lines := px_order_lines;
412
413 IF l_order_lines.count > 0 THEN
414
415 blank_line;
416 add('Order Number : '||p_order_number);
417 blank_line;
418
419 sort_the_order(l_order_lines);
420
421 l_line_header := fill(' LineID', 10)||
422 fill('Line',8)||
423 fill('Item', 25)||
424 fill('Type ',14)||
425 fill('ItemId ',9)||
426 fill('Org', 4)||
427 fill('Qty ', 5)||
428 fill('UM',3)||
429 fill('Link ',9)||
430 fill('Status',12);
431 add(l_line_header);
432 l_line_header := fill('--------', 10)||
433 fill('--------',8)||
434 fill('--------------------', 25)||
435 fill('-------',14)||
436 fill('-------',9)||
437 fill('---', 4)||
438 fill('----', 5)||
439 fill('--',3)||
440 fill('-------',9)||
441 fill('--------',12);
442 add(l_line_header);
443
444 FOR l_ind in l_order_lines.FIRST .. l_order_lines.LAST
445 LOOP
446 dump_order_line(l_order_lines(l_ind));
447 END LOOP;
448
449 blank_line;
450
451 END IF;
452
453 px_order_lines := l_order_lines;
454
455 END dump_order_summary;
456
457 PROCEDURE dump_order_detail(
458 p_order_line IN order_line)
459 IS
460 l_out varchar2(2000);
461 BEGIN
462 blank_line;
463 l_out := fill(p_order_line.line_number, 9)||
464 fill(p_order_line.line_id, 9)||
465 fill(p_order_line.identified_item_type, 14);
466 add(l_out);
467 blank_line;
468 l_out := ' '||fill('org_id',22)||': '||fill(p_order_line.org_id,20)||
469 fill('order_qty', 22)||': '||fill(p_order_line.order_qty, 20);
470 add(l_out);
471 l_out := ' '||fill('header_id',22)||': '||fill(p_order_line.header_id, 20)||
472 fill('order_uom', 22)||': '||fill(p_order_line.order_uom, 20);
473 add(l_out);
474 l_out := ' '||fill('creation_date',22)||': '||
475 fill(to_char(p_order_line.creation_date,'MM/DD/YY HH24:MI:SS'), 20)||
476 fill('item_revision', 22)||': '||fill(p_order_line.item_revision, 20);
477 add(l_out);
478
479 l_out := ' '||fill('shippable_flag',22)||': '||fill(p_order_line.shippable_flag, 20);
480 add(l_out);
481
482 l_out := ' '||fill('shipped_flag',22)||': '||fill(p_order_line.shipped_flag, 20)||
483 fill('fulfilled_flag', 22)||': '||fill(p_order_line.fulfilled_flag, 20);
484 add(l_out);
485
486 l_out := ' '||fill('shipped_quantity',22)||': '||fill(p_order_line.shipped_quantity, 20)||
487 fill('fulfilled_quantity', 22)||': '||fill(p_order_line.fulfilled_quantity, 20);
488 add(l_out);
489
490 l_out := ' '||fill('shipment_date',22)||': '||
491 fill(to_char(p_order_line.shipment_date, 'MM/DD/YY HH24:MI:SS'), 20)||
492 fill('fulfillment_date', 22)||': '||
493 fill(to_char(p_order_line.fulfillment_date,'MM/DD/YY HH24:MI:SS'), 20);
494 add(l_out);
495
496 l_out := ' '||fill('order_type_id',22)||': '||fill(p_order_line.order_type_id, 20)||
497 fill('link_to_line_id', 22)||': '||fill(p_order_line.link_to_line_id, 20);
498 add(l_out);
499
500 l_out := ' '||fill('line_type_id',22)||': '||fill(p_order_line.line_type_id, 20)||
501 fill('ato_line_id',22)||': '||fill(p_order_line.ato_line_id, 20);
502 add(l_out);
503
504 l_out := ' '||fill('item_type',22)||': '||fill(p_order_line.item_type, 20)||
505 fill('top_model_line_id',22)||': '||fill(p_order_line.top_model_line_id, 20);
506 add(l_out);
507
508 l_out := ' '||fill('line_category_code',22)||': '||fill(p_order_line.line_category_code, 20)||
509 fill('split_from_line_id',22)||': '||fill(p_order_line.split_from_line_id, 20);
510 add(l_out);
511
512 l_out := ' '||fill('ship_from_org_id',22)||': '||fill(p_order_line.ship_from_org_id, 20);
513
514 add(l_out);
515 l_out := ' '||fill('ship_to_org_id',22)||': '||fill(p_order_line.ship_to_org_id, 20)||
516 fill('ship_to_contact_id',22)||': '||fill(p_order_line.ship_to_contact_id, 20);
517 add(l_out);
518
519 l_out := ' '||fill('invoice_to_org_id', 22)||': '||fill(p_order_line.invoice_to_org_id, 20)||
520 fill('invoice_to_contact_id', 22)||': '||fill(p_order_line.invoice_to_contact_id, 20);
521 add(l_out);
522
523 l_out := ' '||fill('deliver_to_org_id',22)||': '||fill(p_order_line.deliver_to_org_id, 20)||
524 fill('deliver_to_contact_id',22)||': '||fill(p_order_line.deliver_to_contact_id, 20);
525 add(l_out);
526
527 l_out := ' '||fill('price_list_id',22)||': '||fill(p_order_line.price_list_id, 20)||
528 fill('drop_ship_flag',22)||': '||fill(p_order_line.drop_ship_flag, 20);
529 add(l_out);
530 l_out := ' '||fill('unit_selling_price',22)||': '||fill(p_order_line.unit_selling_price, 20)||
531 fill('cancelled_flag',22)||': '||fill(p_order_line.cancelled_flag, 20);
532 add(l_out);
533 l_out := ' '||fill('configuration_id',22)||': '||fill(p_order_line.configuration_id, 20);
534 add(l_out);
535 l_out := ' '||fill('config_header_id',22)||': '||fill(p_order_line.config_header_id, 20);
536 add(l_out);
537 l_out := ' '||fill('config_rev_nbr',22)||': '||fill(p_order_line.config_rev_nbr, 20);
538 add(l_out);
539
540 END dump_order_detail;
541
542 PROCEDURE get_ib_trackable_lines(
543 px_order_lines IN OUT nocopy order_lines)
544 IS
545 l_order_lines order_lines;
546 l_ind binary_integer := 0;
547 BEGIN
548 IF px_order_lines.count > 0 THEN
549 FOR px_ind IN px_order_lines.FIRST .. px_order_lines.LAST
550 LOOP
551 IF px_order_lines(px_ind).ib_trackable_flag = 'Y' THEN
552 l_ind := l_ind + 1;
553 l_order_lines(l_ind) := px_order_lines(px_ind);
554 END IF;
555 END LOOP;
556 END IF;
557 px_order_lines := l_order_lines;
558 END get_ib_trackable_lines;
559
560 PROCEDURE dump_tld(
561 p_transaction_line_id IN number,
562 px_tld_tbl IN OUT nocopy tld_tbl)
563 IS
564
565 l_out varchar2(2000);
566 l_tld_ind binary_integer := 0;
567 l_instance_key varchar2(30);
568
569 CURSOR tld_cur(p_transaction_line_id IN number) IS
570 SELECT transaction_line_id,
571 txn_line_detail_id,
572 inventory_item_id,
573 quantity,
574 serial_number,
575 lot_number,
576 instance_id,
577 changed_instance_id,
578 location_type_code,
579 location_id,
580 processing_status,
581 source_transaction_flag,
582 config_inst_baseline_rev_num,
583 config_inst_hdr_id,
584 config_inst_item_id,
585 config_inst_rev_num
586 FROM csi_t_txn_line_details
587 WHERE transaction_line_id = p_transaction_line_id
588 ORDER BY source_transaction_flag desc, txn_line_detail_id asc;
589
590 BEGIN
591
592 blank_line;
593
594 l_tld_ind := px_tld_tbl.COUNT;
595
596 FOR tld_rec IN tld_cur(p_transaction_line_id)
597 LOOP
598
599 IF tld_cur%rowcount = 1 THEN
600 l_out := ' '||fill('S', 2)||
601 fill('TLDID ', 9)||
602 fill('ItemID', 9)||
603 fill('Qty',9)||
604 fill('InstID', 9)||
605 fill('InstKey',20)||
606 fill('Status', 10)||
607 fill('Serial', 15)||
608 fill('Lot', 12);
609 add(l_out);
610 l_out := ' '||fill('-', 2)||
611 fill('----- ', 9)||
612 fill('------', 9)||
613 fill('---',9)||
614 fill('------', 9)||
615 fill('--------', 20)||
616 fill('-------', 10)||
617 fill('------', 15)||
618 fill('---', 12);
619 add(l_out);
620 END IF;
621
622 l_tld_ind := l_tld_ind + 1;
623
624 px_tld_tbl(l_tld_ind).transaction_line_id := tld_rec.transaction_line_id;
625 px_tld_tbl(l_tld_ind).txn_line_detail_id := tld_rec.txn_line_detail_id;
626 px_tld_tbl(l_tld_ind).inventory_item_id := tld_rec.inventory_item_id;
627 px_tld_tbl(l_tld_ind).quantity := tld_rec.quantity;
628 px_tld_tbl(l_tld_ind).serial_number := tld_rec.serial_number;
629 px_tld_tbl(l_tld_ind).lot_number := tld_rec.lot_number;
630 px_tld_tbl(l_tld_ind).instance_id := tld_rec.changed_instance_id;
631 px_tld_tbl(l_tld_ind).location_type_code := tld_rec.location_type_code;
632 px_tld_tbl(l_tld_ind).location_id := tld_rec.location_id;
633 px_tld_tbl(l_tld_ind).processing_status := tld_rec.processing_status;
634 px_tld_tbl(l_tld_ind).source_transaction_flag := tld_rec.source_transaction_flag;
635 px_tld_tbl(l_tld_ind).config_inst_baseline_rev_num := tld_rec.config_inst_baseline_rev_num;
636 px_tld_tbl(l_tld_ind).config_inst_hdr_id := tld_rec.config_inst_hdr_id;
637 px_tld_tbl(l_tld_ind).config_inst_item_id := tld_rec.config_inst_item_id;
638 px_tld_tbl(l_tld_ind).config_inst_rev_num := tld_rec.config_inst_rev_num;
639
640 IF tld_rec.processing_status = 'ERROR' THEN
641 cache_instance_id(tld_rec.changed_instance_id);
642 END IF;
643
644 l_instance_key := tld_rec.config_inst_hdr_id||'.'||
645 tld_rec.config_inst_item_id||'.'||
646 tld_rec.config_inst_rev_num||'.'||
647 tld_rec.config_inst_baseline_rev_num;
648
649 l_out := ' '||fill(tld_rec.source_transaction_flag, 2)||
650 fill(tld_rec.txn_line_detail_id, 9)||
651 fill(tld_rec.inventory_item_id, 9)||
652 fill(tld_rec.quantity, 9)||
653 fill(tld_rec.instance_id, 9)||
654 fill(l_instance_key, 20)||
655 fill(tld_rec.processing_status, 10)||
656 fill(tld_rec.serial_number, 15)||
657 fill(tld_rec.lot_number, 12);
658 add(l_out);
659
660 END LOOP;
661
662 END dump_tld;
663
664 PROCEDURE dump_tld_details(
665 p_tld_tbl IN tld_tbl)
666 IS
667
668 l_out varchar2(2000);
669
670 CURSOR tld_pty_cur(p_txn_line_detail_id IN number) IS
671 SELECT txn_party_detail_id,
672 party_source_table,
673 party_source_id,
674 relationship_type_code,
675 contact_flag,
676 contact_party_id
677 FROM csi_t_party_details
678 WHERE txn_line_detail_id = p_txn_line_detail_id;
679
680 CURSOR tld_acct_cur(p_txn_party_detail_id IN number) IS
681 SELECT txn_account_detail_id,
682 ip_account_id,
683 relationship_type_code,
684 account_id,
685 active_start_date,
686 ship_to_address_id,
687 bill_to_address_id
688 FROM csi_t_party_accounts
689 WHERE txn_party_detail_id = p_txn_party_detail_id;
690
691 BEGIN
692
693 IF p_tld_tbl.COUNT > 0 THEN
694 FOR tld_ind IN p_tld_tbl.FIRST .. p_tld_tbl.LAST
695 LOOP
696
697 FOR tld_pty_rec IN tld_pty_cur(p_tld_tbl(tld_ind).txn_line_detail_id)
698 LOOP
699 l_out := ' '||fill('TPDID', 9)||
700 fill('RelType', 12)||
701 fill('PartySource', 20)||
702 fill('PartyID', 9)||
703 fill('C',2)||
704 fill('ConPtyID', 9);
705 add(l_out);
706 l_out := ' '||fill('-----', 9)||
707 fill('-------', 12)||
708 fill('-----------', 20)||
709 fill('-------', 9)||
710 fill('-',2)||
711 fill('--------', 9);
712 add(l_out);
713
714 l_out := ' '||fill(tld_pty_rec.txn_party_detail_id, 9)||
715 fill(tld_pty_rec.relationship_type_code, 12)||
716 fill(tld_pty_rec.party_source_table, 20)||
717 fill(tld_pty_rec.party_source_id, 9)||
718 fill(tld_pty_rec.contact_flag, 2)||
719 fill(tld_pty_rec.contact_party_id, 9);
720 add(l_out);
721
722 l_out := ' '||fill('TADID',9)||
723 fill('RelType',12)||
724 fill('AcctID',9)||
725 fill('IPAcctID',9)||
726 fill('ShipToID',9)||
727 fill('BillToID',9);
728 add(l_out);
729 l_out := ' '||fill('-----',9)||
730 fill('-------',12)||
731 fill('------',9)||
732 fill('--------',9)||
733 fill('--------',9)||
734 fill('--------',9);
735 add(l_out);
736
737 FOR tld_acct_rec IN tld_acct_cur(tld_pty_rec.txn_party_detail_id)
738 LOOP
739 l_out := ' '||fill(tld_acct_rec.txn_account_detail_id,9)||
740 fill(tld_acct_rec.relationship_type_code,12)||
741 fill(tld_acct_rec.account_id,9)||
742 fill(tld_acct_rec.ip_account_id,9)||
743 fill(tld_acct_rec.ship_to_address_id,9)||
744 fill(tld_acct_rec.bill_to_address_id,9);
745 add(l_out);
746 END LOOP;
747 END LOOP;
748 END LOOP;
749 END IF;
750 END dump_tld_details;
751
752 PROCEDURE dump_tiir(
753 p_tl_rec IN csi_t_transaction_lines%rowtype,
754 p_tld_tbl IN tld_tbl)
755 IS
756 l_out varchar2(2000);
757 CURSOR t_iir_cur(p_txn_line_id IN NUMBER) IS
758 SELECT txn_relationship_id,
759 subject_type,
760 subject_id,
761 relationship_type_code,
762 object_type,
763 object_id,
764 position_reference,
765 display_order,
766 mandatory_flag,
767 active_end_date,
768 csi_inst_relationship_id,
769 migrated_flag,
770 sub_config_inst_hdr_id,
771 sub_config_inst_rev_num,
772 sub_config_inst_item_id,
773 obj_config_inst_hdr_id,
774 obj_config_inst_rev_num,
775 obj_config_inst_item_id
776 FROM csi_t_ii_relationships
777 WHERE transaction_line_id = p_txn_line_id;
778
779 CURSOR macd_iir_cur(p_inst_hdr_id IN NUMBER, p_inst_item_id IN number, p_inst_rev_num in number) IS
780 SELECT txn_relationship_id,
781 subject_type,
782 subject_id,
783 relationship_type_code,
784 object_type,
785 object_id,
786 position_reference,
787 display_order,
788 mandatory_flag,
789 active_end_date,
790 csi_inst_relationship_id,
791 migrated_flag,
792 sub_config_inst_hdr_id,
793 sub_config_inst_rev_num,
794 sub_config_inst_item_id,
795 obj_config_inst_hdr_id,
796 obj_config_inst_rev_num,
797 obj_config_inst_item_id
798 FROM csi_t_ii_relationships
799 WHERE (sub_config_inst_hdr_id = p_inst_hdr_id
800 AND
801 sub_config_inst_item_id = p_inst_item_id
802 AND
803 sub_config_inst_rev_num = p_inst_rev_num)
804 OR (obj_config_inst_hdr_id = p_inst_hdr_id
805 AND
806 obj_config_inst_item_id = p_inst_item_id
807 AND
808 obj_config_inst_rev_num = p_inst_rev_num);
809
810 BEGIN
811 blank_line;
812
813 IF p_tl_rec.source_transaction_table = 'CONFIGURATOR' THEN
814 IF p_tld_tbl.COUNT > 0 THEN
815 l_out := ' '||fill('TRID', 9)||
816 fill('SType', 6)||
817 fill('SubID', 9)||
818 fill('RelTypeCode', 18)||
819 fill('OType', 6)||
820 fill('ObjID',9);
821 add(l_out);
822 l_out := ' '||fill('----', 9)||
823 fill('-----', 6)||
824 fill('-----', 9)||
825 fill('-----------', 18)||
826 fill('-----', 6)||
827 fill('-----',9);
828 add(l_out);
829
830 FOR l_ind IN p_tld_tbl.FIRST .. p_tld_tbl.LAST
831 LOOP
832 FOR iir_rec IN macd_iir_cur(
833 p_inst_hdr_id => p_tld_tbl(l_ind).config_inst_hdr_id,
834 p_inst_item_id => p_tld_tbl(l_ind).config_inst_item_id,
835 p_inst_rev_num => p_tld_tbl(l_ind).config_inst_rev_num)
836 LOOP
837 l_out := ' '||fill(iir_rec.txn_relationship_id, 9)||
838 fill(iir_rec.subject_type, 6)||
839 fill(iir_rec.subject_id, 9)||
840 fill(iir_rec.relationship_type_code, 18)||
841 fill(iir_rec.object_type, 6)||
842 fill(iir_rec.object_id,9);
843 add(l_out);
844 END LOOP;
845 END LOOP;
846 END IF;
847 ELSE
848 FOR iir_rec IN t_iir_cur(p_tl_rec.transaction_line_id)
849 LOOP
850
851 IF t_iir_cur%rowcount = 1 THEN
852 l_out := ' '||fill('TRID', 9)||
853 fill('SType', 6)||
854 fill('SubID', 9)||
855 fill('RelTypeCode', 18)||
856 fill('OType', 6)||
857 fill('ObjID',9);
858 add(l_out);
859 l_out := ' '||fill('----', 9)||
860 fill('-----', 6)||
861 fill('-----', 9)||
862 fill('-----------', 18)||
863 fill('-----', 6)||
864 fill('-----',9);
865 add(l_out);
866 END IF;
867
868 l_out := ' '||fill(iir_rec.txn_relationship_id, 9)||
869 fill(iir_rec.subject_type, 6)||
870 fill(iir_rec.subject_id, 9)||
871 fill(iir_rec.relationship_type_code, 18)||
872 fill(iir_rec.object_type, 6)||
873 fill(iir_rec.object_id,9);
874 add(l_out);
875 END LOOP;
876 END IF;
877 END dump_tiir;
878
879 PROCEDURE dump_installation_details(
880 p_order_lines IN order_lines,
881 p_source_table IN varchar2)
882 IS
883
884 l_order_lines order_lines;
885 l_rowcount number;
886 l_prepend varchar2(30);
887 l_out varchar2(2000);
888 l_tl_rec csi_t_transaction_lines%rowtype;
889 l_tld_tbl tld_tbl;
890
891 l_print_header boolean := TRUE;
892 l_session_key varchar2(30);
893
894 BEGIN
895
896 l_order_lines := p_order_lines;
897
898 IF l_order_lines.COUNT > 0 THEN
899
900 FOR l_ind IN l_order_lines.FIRST .. l_order_lines.LAST
901 LOOP
902 IF l_order_lines(l_ind).macd_flag = 'Y' THEN
903 SELECT *
904 INTO l_tl_rec
905 FROM csi_t_transaction_lines
906 WHERE source_transaction_table = 'CONFIGURATOR'
907 AND config_session_hdr_id = l_order_lines(l_ind).config_header_id
908 AND config_session_rev_num = l_order_lines(l_ind).config_rev_nbr
909 AND config_session_item_id = l_order_lines(l_ind).configuration_id;
910 ELSE
911 BEGIN
912 SELECT *
913 INTO l_tl_rec
914 FROM csi_t_transaction_lines
915 WHERE source_transaction_table = p_source_table
916 AND source_transaction_id = l_order_lines(l_ind).line_id;
917 EXCEPTION
918 WHEN no_data_found THEN
919 l_tl_rec.transaction_line_id := null;
920 END;
921 END IF;
922
923 IF l_tl_rec.transaction_line_id is not null THEN
924 IF l_print_header THEN
925 blank_line;
926 blank_line;
927 add('Installation Details ('||p_source_table||') :- ');
928 add('--------------------');
929 l_print_header := FALSE;
930 END IF;
931
932 blank_line;
933
934 l_session_key := l_tl_rec.config_session_hdr_id||'.'||
935 l_tl_rec.config_session_item_id||'.'||
936 l_tl_rec.config_session_rev_num;
937
938 l_out := fill(l_order_lines(l_ind).line_number, 9)||
939 fill(l_order_lines(l_ind).line_id, 9)||
940 fill(l_order_lines(l_ind).identified_item_type, 14)||
941 fill(l_tl_rec.transaction_line_id, 9)||
942 fill(l_tl_rec.source_txn_header_id, 9)||
943 fill(l_session_key, 20)||
944 fill(l_tl_rec.config_valid_status, 5)||
945 fill(l_tl_rec.processing_status, 15);
946 add(l_out);
947
948 dump_tld(
949 p_transaction_line_id => l_tl_rec.transaction_line_id,
950 px_tld_tbl => l_tld_tbl);
951
952 dump_tiir(
953 p_tl_rec => l_tl_rec,
954 p_tld_tbl => l_tld_tbl);
955
956 END IF;
957 END LOOP;
958 END IF;
959 END dump_installation_details;
960
961 PROCEDURE cache_mmt_rec(
962 p_mmt_rec IN mmt_rec)
963 IS
964 l_ind binary_integer := 0;
965 BEGIN
966 l_ind := l_mmt_tbl_cache.COUNT + 1;
967 l_mmt_tbl_cache(l_ind) := p_mmt_rec;
968 END cache_mmt_rec;
969
970 PROCEDURE get_mmt_rec_from_cache(
971 p_mtl_txn_id IN number,
972 x_mmt_rec OUT nocopy mmt_rec,
973 x_cached OUT nocopy boolean)
974 IS
975 BEGIN
976 x_cached := FALSE;
977 IF l_mmt_tbl_cache.COUNT > 0 THEN
978 FOR l_ind IN l_mmt_tbl_cache.FIRST .. l_mmt_tbl_cache.LAST
979 LOOP
980 IF l_mmt_tbl_cache(l_ind).mtl_txn_id = p_mtl_txn_id THEN
981 x_mmt_rec := l_mmt_tbl_cache(l_ind);
982 x_cached := TRUE;
983 exit;
984 END IF;
985 END LOOP;
986 END IF;
987 END get_mmt_rec_from_cache;
988
989 PROCEDURE get_mmt_status(
990 px_mmt_rec IN OUT nocopy mmt_rec)
991 IS
992 BEGIN
993
994 IF csi_inv_trxs_pkg.valid_ib_txn(px_mmt_rec.mtl_txn_id) THEN
995 BEGIN
996 SELECT transaction_id,
997 transaction_date
998 INTO px_mmt_rec.csi_txn_id,
999 px_mmt_rec.csi_txn_date
1000 FROM csi_transactions
1001 WHERE inv_material_transaction_id = px_mmt_rec.mtl_txn_id
1002 AND rownum = 1;
1003 px_mmt_rec.status := 'PROCESSED';
1004 EXCEPTION
1005 WHEN no_data_found THEN
1006 px_mmt_rec.csi_txn_id := null;
1007 px_mmt_rec.csi_txn_date := null;
1008 BEGIN
1009 SELECT transaction_error_id,
1010 error_text
1011 INTO px_mmt_rec.error_id,
1012 px_mmt_rec.error_text
1013 FROM csi_txn_errors
1014 WHERE inv_material_transaction_id = px_mmt_rec.mtl_txn_id
1015 AND processed_flag in ('E', 'R');
1016 px_mmt_rec.status := 'ERROR';
1017 EXCEPTION
1018 WHEN no_data_found THEN
1019 px_mmt_rec.error_id := null;
1020 px_mmt_rec.error_text := null;
1021 BEGIN
1022 SELECT msg_id,
1023 msg_code,
1024 msg_status
1025 INTO px_mmt_rec.message_id,
1026 px_mmt_rec.message_code,
1027 px_mmt_rec.message_status
1028 FROM xnp_msgs
1029 WHERE dbms_lob.instr(body_text, 'MTL_TRANSACTION_ID') > 0
1030 AND dbms_lob.instr(body_text, px_mmt_rec.mtl_txn_id) > 0
1031 AND rownum = 1;
1032 px_mmt_rec.status := 'IN_QUEUE';
1033 EXCEPTION
1034 WHEN no_data_found THEN
1035 px_mmt_rec.status := 'UNKNOWN';
1036 px_mmt_rec.message_id := null;
1037 px_mmt_rec.message_code := null;
1038 px_mmt_rec.message_status := null;
1039 END;
1040 END;
1041 END;
1042 ELSE
1043 px_mmt_rec.status := 'UNKNOWN';
1044 px_mmt_rec.message_id := null;
1045 px_mmt_rec.message_code := null;
1046 px_mmt_rec.message_status := null;
1047 END IF;
1048
1049 END get_mmt_status;
1050
1051 PROCEDURE get_mmt_rec(
1052 p_mtl_txn_id IN number,
1053 x_mmt_rec OUT nocopy mmt_rec)
1054 IS
1055
1056 l_cached boolean := FALSE;
1057 l_mmt_rec mmt_rec;
1058 m_ind binary_integer := 0;
1059 l_out varchar2(2000);
1060
1061 CURSOR mtl_txn_cur(p_txn_id IN number) IS
1062 SELECT mmt.transaction_id,
1063 mmt.transaction_date,
1064 mmt.transaction_quantity,
1065 mmt.transaction_uom,
1066 mmt.primary_quantity,
1067 mmt.transaction_action_id,
1068 mmt.transaction_source_type_id,
1069 mmt.transaction_type_id,
1070 mmt.inventory_item_id,
1071 mmt.organization_id ,
1072 mmt.transaction_source_id,
1073 mmt.trx_source_line_id,
1074 mmt.transfer_transaction_id,
1075 mtt.transaction_type_name,
1076 mtt.type_class,
1077 mtt.user_defined_flag
1078 FROM mtl_material_transactions mmt,
1079 mtl_transaction_types mtt
1080 WHERE mmt.transaction_id = p_txn_id
1081 AND mtt.transaction_type_id = mmt.transaction_type_id;
1082
1083 BEGIN
1084
1085 get_mmt_rec_from_cache(
1086 p_mtl_txn_id => p_mtl_txn_id,
1087 x_mmt_rec => l_mmt_rec,
1088 x_cached => l_cached);
1089
1090 IF NOT (l_cached) THEN
1091 FOR mtl_txn_rec IN mtl_txn_cur(p_mtl_txn_id)
1092 LOOP
1093
1094 l_mmt_rec.mtl_txn_id := mtl_txn_rec.transaction_id;
1095 l_mmt_rec.mtl_txn_date := mtl_txn_rec.transaction_date;
1096 l_mmt_rec.item_id := mtl_txn_rec.inventory_item_id;
1097 l_mmt_rec.organization_id := mtl_txn_rec.organization_id;
1098 l_mmt_rec.mtl_type_id := mtl_txn_rec.transaction_type_id;
1099 l_mmt_rec.mtl_txn_name := mtl_txn_rec.transaction_type_name;
1100 l_mmt_rec.mtl_action_id := mtl_txn_rec.transaction_action_id;
1101 l_mmt_rec.mtl_source_type_id := mtl_txn_rec.transaction_source_type_id;
1102 l_mmt_rec.mtl_source_id := mtl_txn_rec.transaction_source_id;
1103 l_mmt_rec.mtl_source_line_id := mtl_txn_rec.trx_source_line_id;
1104 l_mmt_rec.mtl_txn_qty := mtl_txn_rec.transaction_quantity;
1105 l_mmt_rec.mtl_txn_uom := mtl_txn_rec.transaction_uom;
1106 l_mmt_rec.mtl_pri_qty := mtl_txn_rec.primary_quantity;
1107 l_mmt_rec.mtl_type_class := mtl_txn_rec.type_class;
1108 l_mmt_rec.mtl_xfer_txn_id := mtl_txn_rec.transfer_transaction_id;
1109 l_mmt_rec.user_defined := mtl_txn_rec.user_defined_flag;
1110
1111 get_mmt_status(l_mmt_rec);
1112
1113 END LOOP;
1114
1115 cache_mmt_rec(p_mmt_rec => l_mmt_rec);
1116
1117 END IF;
1118
1119 x_mmt_rec := l_mmt_rec;
1120
1121 END get_mmt_rec;
1122
1123 PROCEDURE dump_mmt_rec(
1124 p_mmt_rec IN mmt_rec,
1125 p_index IN number default 1)
1126 IS
1127 l_out varchar2(2000);
1128 l_reference varchar2(2000);
1129 BEGIN
1130
1131 IF p_index = 1 THEN
1132 blank_line;
1133 l_out := ' '||
1134 fill('OrgID', 6)||
1135 fill('TxnName', 22)||
1136 fill('TxnID', 10)||
1137 fill('TxnDate', 18)||
1138 fill('TQty', 8)||
1139 fill('TUOM', 5)||
1140 fill('SrcID', 8)||
1141 fill('SrcLnID', 8);
1142 add(l_out);
1143
1144 l_out := ' '||
1145 fill('-----', 6)||
1146 fill('-------', 22)||
1147 fill('-----', 10)||
1148 fill('-------', 18)||
1149 fill('------', 8)||
1150 fill('----', 5)||
1151 fill('-----', 8)||
1152 fill('-------', 8);
1153 add(l_out);
1154 END IF;
1155
1156 IF p_mmt_rec.status = 'PROCESSED' THEN
1157 l_reference := ' '||fill(p_mmt_rec.status, 10)||
1158 'CsiTxnID : '||p_mmt_rec.csi_txn_id;
1159 ELSIF p_mmt_rec.status = 'ERROR' THEN
1160 l_reference := ' ERROR : '||
1161 p_mmt_rec.error_text;
1162 ELSIF p_mmt_rec.status = 'IN_QUEUE' THEN
1163 l_reference := ' '||fill(p_mmt_rec.status, 10)||
1164 'MsgID : '||p_mmt_rec.message_id||'.'||p_mmt_rec.message_status;
1165 ELSIF p_mmt_rec.status = 'UNKNOWN' THEN
1166 l_reference := ' '||fill(p_mmt_rec.status, 10);
1167 END IF;
1168
1169 l_out := ' '||
1170 fill(p_mmt_rec.organization_id, 6)||
1171 fill(p_mmt_rec.mtl_txn_name, 22)||
1172 fill(p_mmt_rec.mtl_txn_id, 10)||
1173 fill(to_char(p_mmt_rec.mtl_txn_date, 'MM/DD/YY HH24:MI:SS'), 18)||
1174 fill(p_mmt_rec.mtl_txn_qty, 8)||
1175 fill(p_mmt_rec.mtl_txn_uom, 5)||
1176 fill(p_mmt_rec.mtl_source_id, 8)||
1177 fill(p_mmt_rec.mtl_source_line_id, 8);
1178 add(l_out);
1179
1180 l_out := l_reference;
1181 add(l_out);
1182
1183 END dump_mmt_rec;
1184
1185 PROCEDURE get_mut_tbl(
1186 p_mtl_txn_id IN number,
1187 p_error_flag IN varchar2 default 'N',
1188 x_mut_tbl OUT nocopy mut_tbl)
1189 IS
1190
1191 l_mut_tbl mut_tbl;
1192 l_mut_ind binary_integer := 0;
1193
1194 CURSOR srl_cur(
1195 p_txn_id in number)
1196 IS
1197 SELECT mut.serial_number serial_number,
1198 mut.inventory_item_id item_id,
1199 mut.organization_id organization_id,
1200 to_char(null) lot_number
1201 FROM mtl_unit_transactions mut
1202 WHERE mut.transaction_id = p_txn_id
1203 UNION
1204 SELECT mut.serial_number serial_number,
1205 mut.inventory_item_id item_id,
1206 mut.organization_id organization_id,
1207 mtln.lot_number lot_number
1208 FROM mtl_transaction_lot_numbers mtln,
1209 mtl_unit_transactions mut
1210 WHERE mtln.transaction_id = p_txn_id
1211 AND mut.transaction_id = mtln.serial_transaction_id
1212 ORDER BY 1;
1213
1214 BEGIN
1215
1216 FOR srl_rec IN srl_cur(p_mtl_txn_id)
1217 LOOP
1218 l_mut_ind := l_mut_ind + 1;
1219 l_mut_tbl(l_mut_ind).serial_number := srl_rec.serial_number;
1220 l_mut_tbl(l_mut_ind).item_id := srl_rec.item_id;
1221 l_mut_tbl(l_mut_ind).lot_number := srl_rec.lot_number;
1222
1223 BEGIN
1224 SELECT instance_id,
1225 location_type_code,
1226 instance_usage_code
1227 INTO l_mut_tbl(l_mut_ind).instance_id ,
1228 l_mut_tbl(l_mut_ind).location_type_code,
1229 l_mut_tbl(l_mut_ind).instance_usage_code
1230 FROM csi_item_instances
1231 WHERE inventory_item_id = srl_rec.item_id
1232 AND serial_number = srl_rec.serial_number;
1233
1234 IF p_error_flag = 'Y' THEN
1235 cache_instance_id(l_mut_tbl(l_mut_ind).instance_id);
1236 END IF;
1237
1238 EXCEPTION
1239 WHEN no_data_found THEN
1240 null;
1241 END;
1242
1243 END LOOP;
1244
1245 x_mut_tbl := l_mut_tbl;
1246
1247 END get_mut_tbl;
1248
1249 PROCEDURE dump_mut_tbl(
1250 p_mut_tbl IN mut_tbl)
1251 IS
1252 l_out varchar2(2000);
1253 BEGIN
1254 IF p_mut_tbl.COUNT > 0 THEN
1255 l_out := ' '||fill('Serial#', 15)||
1256 fill('Lot#', 15)||
1257 fill('InstID', 10)||
1258 fill('LocationType', 18)||
1259 fill('UsageCode', 18);
1260 add(l_out);
1261
1262 l_out := ' '||fill('-------', 15)||
1263 fill('----', 15)||
1264 fill('------', 10)||
1265 fill('------------', 18)||
1266 fill('---------', 18);
1267 add(l_out);
1268
1269 FOR p_ind IN p_mut_tbl.FIRST .. p_mut_tbl.LAST
1270 LOOP
1271 l_out := ' '||fill(p_mut_tbl(p_ind).serial_number, 15)||
1272 fill(p_mut_tbl(p_ind).lot_number, 15)||
1273 fill(p_mut_tbl(p_ind).instance_id, 10)||
1274 fill(p_mut_tbl(p_ind).location_type_code, 18)||
1275 fill(p_mut_tbl(p_ind).instance_usage_code, 18);
1276 add(l_out);
1277 END LOOP;
1278 END IF;
1279 END dump_mut_tbl;
1280
1281 PROCEDURE get_srl_mmt_tbl(
1282 p_serial_number IN varchar2,
1283 p_item_id IN number,
1284 x_mmt_tbl OUT nocopy mmt_tbl)
1285 IS
1286
1287 l_mmt_rec mmt_rec;
1288 l_mmt_tbl mmt_tbl;
1289 l_mmt_ind binary_integer := 0;
1290
1291 CURSOR all_txn_cur(
1292 p_serial_number in varchar2,
1293 p_item_id in number)
1294 IS
1295 SELECT mmt.creation_date mtl_creation_date,
1296 mmt.transaction_id mtl_txn_id
1297 FROM mtl_unit_transactions mut,
1298 mtl_material_transactions mmt
1299 WHERE mut.serial_number = p_serial_number
1300 AND mut.inventory_item_id = p_item_id
1301 AND mmt.transaction_id = mut.transaction_id
1302 UNION ALL
1303 SELECT mmt.creation_date mtl_creation_date,
1304 mmt.transaction_id mtl_txn_id
1305 FROM mtl_unit_transactions mut,
1306 mtl_transaction_lot_numbers mtln,
1307 mtl_material_transactions mmt
1308 WHERE mut.serial_number = p_serial_number
1309 AND mut.inventory_item_id = p_item_id
1310 AND mtln.organization_id = mut.organization_id
1311 AND mtln.transaction_date = mut.transaction_date
1312 AND mtln.serial_transaction_id = mut.transaction_id
1313 AND mmt.transaction_id = mtln.transaction_id
1314 ORDER BY 1 desc, 2 desc;
1315
1316 BEGIN
1317 FOR all_txn_rec IN all_txn_cur(
1318 p_serial_number => p_serial_number,
1319 p_item_id => p_item_id)
1320 LOOP
1321 get_mmt_rec(
1322 p_mtl_txn_id => all_txn_rec.mtl_txn_id,
1323 x_mmt_rec => l_mmt_rec);
1324 l_mmt_ind := l_mmt_ind + 1;
1325 l_mmt_tbl(l_mmt_ind) := l_mmt_rec;
1326 END LOOP;
1327 x_mmt_tbl := l_mmt_tbl;
1328 END get_srl_mmt_tbl;
1329
1330 PROCEDURE txn_status(
1331 p_mtl_txn_id IN number)
1332 IS
1333 l_error_flag varchar2(1);
1334 l_mmt_rec mmt_rec;
1335 l_mut_tbl mut_tbl;
1336 BEGIN
1337
1338 IF csi_t_gen_utility_pvt.g_file is null THEN
1339 csi_t_gen_utility_pvt.build_file_name(
1340 p_file_segment1 => 'csimtltxn',
1341 p_file_segment2 => p_mtl_txn_id);
1342 END IF;
1343
1344 get_mmt_rec(
1345 p_mtl_txn_id => p_mtl_txn_id,
1346 x_mmt_rec => l_mmt_rec);
1347
1348 dump_mmt_rec(
1349 p_mmt_rec => l_mmt_rec);
1350
1351 IF l_mmt_rec.status = 'ERROR' THEN
1352 l_error_flag := 'Y';
1353 ELSE
1354 l_error_flag := 'N';
1355 END IF;
1356
1357 get_mut_tbl(
1358 p_mtl_txn_id => p_mtl_txn_id,
1359 p_error_flag => l_error_flag,
1360 x_mut_tbl => l_mut_tbl);
1361
1362 dump_mut_tbl(l_mut_tbl);
1363
1364 IF l_mut_tbl.count > 0 THEN
1365
1366 blank_line;
1367 add('Serial Transactions :-');
1368 add('-------------------');
1369
1370 FOR l_ind IN l_mut_tbl.FIRST .. l_mut_tbl.LAST
1371 LOOP
1372
1373 serial_status(
1374 p_serial_number => l_mut_tbl(l_ind).serial_number,
1375 p_item_id => l_mut_tbl(l_ind).item_id,
1376 p_standalone_mode => 'N');
1377
1378 END LOOP;
1379 END IF;
1380
1381 END txn_status;
1382
1383 PROCEDURE get_job_rec(
1384 p_wip_entity_name IN varchar2,
1385 p_organization_id IN number,
1386 x_job_rec OUT nocopy job_rec)
1387 IS
1388 l_job_rec job_rec;
1389 BEGIN
1390
1391 l_job_rec.wip_entity_name := p_wip_entity_name;
1392
1393 SELECT wip_entity_id,
1394 entity_type,
1395 organization_id
1396 INTO l_job_rec.wip_entity_id,
1397 l_job_rec.wip_entity_type,
1398 l_job_rec.organization_id
1399 FROM wip_entities
1400 WHERE wip_entity_name = p_wip_entity_name
1401 AND organization_id = p_organization_id;
1402
1403 BEGIN
1404 IF l_job_rec.wip_entity_type = 4 THEN -- flow schedules
1405 SELECT primary_item_id,
1406 quantity_completed,
1407 quantity_completed,
1408 status
1409 INTO l_job_rec.primary_item_id,
1410 l_job_rec.start_qty, -- wo less case compl qty is job qty
1411 l_job_rec.qty_completed,
1412 l_job_rec.wip_job_status
1413 FROM wip_flow_schedules
1414 WHERE wip_entity_id = l_job_rec.wip_entity_id
1415 AND organization_id = l_job_rec.organization_id;
1416 ELSE -- discrete jobs
1417 SELECT primary_item_id,
1418 start_quantity,
1419 quantity_completed,
1420 job_type,
1421 status_type,
1422 nvl(maintenance_object_source, 0),
1423 source_code,
1424 source_line_id
1425 INTO l_job_rec.primary_item_id,
1426 l_job_rec.start_qty,
1427 l_job_rec.qty_completed,
1428 l_job_rec.wip_entity_type,
1429 l_job_rec.wip_job_status,
1430 l_job_rec.maint_obj_source,
1431 l_job_rec.source_code,
1432 l_job_rec.source_line_id
1433 FROM wip_discrete_jobs
1434 WHERE wip_entity_id = l_job_rec.wip_entity_id
1435 AND organization_id = l_job_rec.organization_id;
1436 END IF;
1437 EXCEPTION
1438 WHEN no_data_found THEN
1439 null;
1440 END;
1441 x_job_rec := l_job_rec;
1442 END get_job_rec;
1443
1444 PROCEDURE dump_job_rec(
1445 p_job_rec IN job_rec)
1446 IS
1447 l_out varchar2(2000);
1448 BEGIN
1449
1450 blank_line;
1451 add('Job Name : '||p_job_rec.wip_entity_name);
1452 blank_line;
1453
1454 l_out := fill('WipEntityID',14)||
1455 fill('Type', 6)||
1456 fill('Status',8)||
1457 fill('ItemID', 14)||
1458 fill('Org', 4)||
1459 fill('JobQty', 10)||
1460 fill('ComplQty',10);
1461 add(l_out);
1462
1463 l_out := fill('-----------',14)||
1464 fill('----', 6)||
1465 fill('------',8)||
1466 fill('------', 14)||
1467 fill('---', 4)||
1468 fill('------', 10)||
1469 fill('--------',10);
1470 add(l_out);
1471
1472 l_out := fill(p_job_rec.wip_entity_id,14)||
1473 fill(p_job_rec.wip_entity_type, 6)||
1474 fill(p_job_rec.wip_job_status,8)||
1475 fill(p_job_rec.primary_item_id, 14)||
1476 fill(p_job_rec.organization_id, 4)||
1477 fill(p_job_rec.start_qty, 10)||
1478 fill(p_job_rec.qty_completed,10);
1479 add(l_out);
1480
1481 END dump_job_rec;
1482
1483 PROCEDURE get_wip_req_tbl(
1484 p_wip_entity_id IN number,
1485 p_organization_id IN number,
1486 x_wip_req_tbl OUT nocopy wip_req_tbl)
1487 IS
1488 l_wip_req_tbl wip_req_tbl;
1489 l_ind binary_integer := 0;
1490 l_item_rec item_rec;
1491 l_ib_ind char;
1492 CURSOR req_cur IS
1493 SELECT segment1,
1494 inventory_item_id,
1495 organization_id,
1496 operation_seq_num,
1497 component_sequence_id,
1498 quantity_per_assembly,
1499 required_quantity,
1500 quantity_issued,
1501 wip_supply_type,
1502 supply_subinventory
1503 FROM wip_requirement_operations
1504 WHERE wip_entity_id = p_wip_entity_id
1505 AND organization_id = p_organization_id
1506 ORDER BY operation_seq_num, component_sequence_id;
1507 BEGIN
1508 FOR req_rec IN req_cur
1509 LOOP
1510
1511 l_ind := req_cur%rowcount;
1512
1513 get_item_rec(
1514 p_item_id => req_rec.inventory_item_id,
1515 p_organization_id => req_rec.organization_id,
1516 x_item_rec => l_item_rec);
1517
1518 SELECT decode(l_item_rec.ib_trackable_flag, 'Y', '*', ' ')
1519 INTO l_ib_ind
1520 FROM sys.dual;
1521
1522 l_wip_req_tbl(l_ind).comp_item := l_ib_ind||req_rec.segment1;
1523 l_wip_req_tbl(l_ind).comp_item_id := req_rec.inventory_item_id;
1524 l_wip_req_tbl(l_ind).oper_seq_num := req_rec.operation_seq_num;
1525 l_wip_req_tbl(l_ind).comp_seq_id := req_rec.component_sequence_id;
1526 l_wip_req_tbl(l_ind).qty_per_assy := req_rec.quantity_per_assembly;
1527 l_wip_req_tbl(l_ind).required_qty := req_rec.required_quantity;
1528 l_wip_req_tbl(l_ind).qty_issued := req_rec.quantity_issued;
1529 l_wip_req_tbl(l_ind).wip_supply_type := req_rec.wip_supply_type;
1530 l_wip_req_tbl(l_ind).supply_subinv := req_rec.supply_subinventory;
1531 l_wip_req_tbl(l_ind).primary_uom_code := l_item_rec.primary_uom_code;
1532 l_wip_req_tbl(l_ind).serial_code := l_item_rec.serial_code;
1533 l_wip_req_tbl(l_ind).lot_code := l_item_rec.lot_code;
1534
1535 END LOOP;
1536
1537 x_wip_req_tbl := l_wip_req_tbl;
1538
1539 END get_wip_req_tbl;
1540
1541 PROCEDURE dump_wip_req_tbl(
1542 p_wip_req_tbl IN wip_req_tbl)
1543 IS
1544 l_out varchar2(2000);
1545 BEGIN
1546
1547 blank_line;
1548
1549 IF p_wip_req_tbl.count > 0 THEN
1550
1551 add('WIP Material Requirements :-');
1552 blank_line;
1553
1554 l_out := ' '||fill('CompItem',19)||
1555 fill('ItemID', 12)||
1556 fill('Oper', 5)||
1557 fill('QPA',10)||
1558 fill('Required', 10)||
1559 fill('Issued', 10)||
1560 fill('SType', 8)||
1561 fill('SrlCtrl', 8)||
1562 fill('LotCtrl', 8);
1563 add(l_out);
1564
1565 l_out := ' '||fill('---------',19)||
1566 fill('-------', 12)||
1567 fill('----', 5)||
1568 fill('---',10)||
1569 fill('--------', 10)||
1570 fill('------', 10)||
1571 fill('-----', 8)||
1572 fill('-------', 8)||
1573 fill('-------', 8);
1574 add(l_out);
1575
1576 FOR l_ind IN p_wip_req_tbl.FIRST .. p_wip_req_tbl.LAST
1577 LOOP
1578 l_out := ' '||fill(p_wip_req_tbl(l_ind).comp_item,20)||
1579 fill(p_wip_req_tbl(l_ind).comp_item_id, 12)||
1580 fill(p_wip_req_tbl(l_ind).oper_seq_num, 5)||
1581 fill(p_wip_req_tbl(l_ind).qty_per_assy,10)||
1582 fill(p_wip_req_tbl(l_ind).required_qty, 10)||
1583 fill(p_wip_req_tbl(l_ind).qty_issued, 10)||
1584 fill(p_wip_req_tbl(l_ind).wip_supply_type, 8)||
1585 fill(p_wip_req_tbl(l_ind).serial_code, 8)||
1586 fill(p_wip_req_tbl(l_ind).lot_code, 8);
1587 add(l_out);
1588
1589 END LOOP;
1590
1591 END IF;
1592
1593 END dump_wip_req_tbl;
1594
1595 PROCEDURE get_wip_mmt_tbl(
1596 p_wip_entity_id IN number,
1597 p_organization_id IN number,
1598 x_wip_mmt_tbl OUT nocopy mmt_tbl)
1599 IS
1600 l_mmt_tbl mmt_tbl;
1601 l_mmt_rec mmt_rec;
1602 mmt_ind binary_integer := 0;
1603
1604 CURSOR wip_txn_cur(p_wip_entity_id IN number, p_organization_id IN number) IS
1605 SELECT mmt.transaction_id
1606 FROM mtl_material_transactions mmt,
1607 mtl_system_items msi
1608 WHERE mmt.transaction_source_type_id = 5
1609 AND mmt.organization_id = p_organization_id
1610 AND mmt.transaction_source_id = p_wip_entity_id
1611 AND msi.inventory_item_id = mmt.inventory_item_id
1612 AND msi.organization_id = mmt.organization_id
1613 AND nvl(msi.comms_nl_trackable_flag, 'N') = 'Y'
1614 ORDER BY transaction_date desc;
1615
1616 BEGIN
1617
1618 FOR wip_txn_rec IN wip_txn_cur(p_wip_entity_id, p_organization_id)
1619 LOOP
1620
1621 get_mmt_rec(
1622 p_mtl_txn_id => wip_txn_rec.transaction_id,
1623 x_mmt_rec => l_mmt_rec);
1624
1625 mmt_ind := mmt_ind + 1;
1626 l_mmt_tbl(mmt_ind) := l_mmt_rec;
1627
1628 END LOOP;
1629
1630 x_wip_mmt_tbl := l_mmt_tbl;
1631
1632 END get_wip_mmt_tbl;
1633
1634 PROCEDURE dump_wip_mmt_tbl(
1635 p_wip_mmt_tbl IN mmt_tbl)
1636 IS
1637 l_mut_tbl mut_tbl;
1638 BEGIN
1639
1640 IF p_wip_mmt_tbl.COUNT > 0 THEN
1641
1642 blank_line;
1643 add('WIP Material Transactions :-');
1644
1645 FOR p_ind IN p_wip_mmt_tbl.FIRST .. p_wip_mmt_tbl.LAST
1646 LOOP
1647
1648 dump_mmt_rec(p_wip_mmt_tbl(p_ind));
1649
1650 get_mut_tbl(
1651 p_mtl_txn_id => p_wip_mmt_tbl(p_ind).mtl_txn_id,
1652 x_mut_tbl => l_mut_tbl);
1653
1654 dump_mut_tbl(l_mut_tbl);
1655 END LOOP;
1656 END IF;
1657
1658 END dump_wip_mmt_tbl;
1659
1660 PROCEDURE serial_status(
1661 p_serial_number IN varchar2,
1662 p_item_id IN number,
1663 p_standalone_mode IN varchar2)
1664 IS
1665 l_out varchar2(2000);
1666 l_srl_mmt_tbl mmt_tbl;
1667 l_current_status number;
1668 l_current_org_id number;
1669 l_gen_object_id number;
1670 l_parent_serial_number varchar2(80);
1671 l_parent_item_id number;
1672
1673 l_instance_id number;
1674 l_instance_number varchar2(30);
1675
1676 CURSOR parent_srl_cur(p_gen_object_id in number) IS
1677 SELECT msn.serial_number,
1678 msn.inventory_item_id
1679 FROM mtl_object_genealogy mog,
1680 mtl_serial_numbers msn
1681 WHERE mog.object_type = 2 -- serial genealogy
1682 AND mog.object_id = p_gen_object_id
1683 AND mog.parent_object_type = 2 -- serial genealogy
1684 AND msn.gen_object_id = mog.parent_object_id
1685 AND sysdate BETWEEN nvl(mog.start_date_active, sysdate-1)
1686 AND nvl(mog.end_date_active, sysdate+1);
1687 BEGIN
1688
1689 IF csi_t_gen_utility_pvt.g_file is null THEN
1690 csi_t_gen_utility_pvt.build_file_name(
1691 p_file_segment1 => 'csisrl',
1692 p_file_segment2 => p_serial_number);
1693 END IF;
1694
1695 blank_line;
1696 l_out := fill('Serial#', 15)||
1697 fill('ItemID', 9)||
1698 fill('Stat', 5)||
1699 fill('CurrOrg', 8)||
1700 fill('GObjID', 7)||
1701 fill('PSerial#',15)||
1702 fill('InstNum',10);
1703 add(l_out);
1704
1705 l_out := fill('-------', 15)||
1706 fill('------', 9)||
1707 fill('----', 5)||
1708 fill('-------', 8)||
1709 fill('------', 7)||
1710 fill('--------',15)||
1711 fill('-------',10);
1712 add(l_out);
1713
1714 SELECT current_status,
1715 current_organization_id,
1716 gen_object_id
1717 INTO l_current_status,
1718 l_current_org_id,
1719 l_gen_object_id
1720 FROM mtl_serial_numbers
1721 WHERE serial_number = p_serial_number
1722 AND inventory_item_id = p_item_id;
1723
1724 FOR parent_srl_rec IN parent_srl_cur(l_gen_object_id)
1725 LOOP
1726 IF parent_srl_cur%rowcount = 1 THEN
1727 l_parent_serial_number := parent_srl_rec.serial_number;
1728 l_parent_item_id := parent_srl_rec.inventory_item_id;
1729 ELSE
1730 l_parent_serial_number := '*Multiple*';
1731 END IF;
1732 END LOOP;
1733
1734 BEGIN
1735 SELECT instance_id,
1736 instance_number
1737 INTO l_instance_id,
1738 l_instance_number
1739 FROM csi_item_instances
1740 WHERE inventory_item_id = p_item_id
1741 AND serial_number = p_serial_number;
1742 IF p_standalone_mode = 'Y' THEN
1743 cache_instance_id(l_instance_id);
1744 END IF;
1745 EXCEPTION
1746 WHEN no_data_found THEN
1747 l_instance_id := null;
1748 l_instance_number := '*None*';
1749 WHEN too_many_rows THEN
1750 l_instance_id := null;
1751 l_instance_number := '*Multiple*';
1752 END;
1753
1754 l_out := fill(p_serial_number, 15)||
1755 fill(p_item_id, 9)||
1756 fill(l_current_status, 5)||
1757 fill(l_current_org_id, 8)||
1758 fill(l_gen_object_id, 7)||
1759 fill(l_parent_serial_number,15)||
1760 fill(l_instance_number, 10);
1761 add(l_out);
1762
1763 get_srl_mmt_tbl(
1764 p_serial_number => p_serial_number,
1765 p_item_id => p_item_id,
1766 x_mmt_tbl => l_srl_mmt_tbl);
1767
1768 IF l_srl_mmt_tbl.COUNT > 0 THEN
1769 FOR srl_mmt_ind IN l_srl_mmt_tbl.FIRST .. l_srl_mmt_tbl.LAST
1770 LOOP
1771 dump_mmt_rec(
1772 p_mmt_rec => l_srl_mmt_tbl(srl_mmt_ind),
1773 p_index => srl_mmt_ind);
1774 END LOOP;
1775 END IF;
1776
1777 IF p_standalone_mode = 'Y' THEN
1778 IF l_instance_id is not null THEN
1779 blank_line;
1780 add('Instance Details :-');
1781 add('---------------- :-');
1782 blank_line;
1783 instance_status(
1784 p_instance_id => l_instance_id,
1785 p_standalone_mode => 'N');
1786 END IF;
1787 END IF;
1788
1789 EXCEPTION
1790 WHEN others THEN
1791 add(' other error : '||substr(sqlerrm, 1, 255));
1792 END serial_status;
1793
1794 PROCEDURE dump_fulfill_transactions(
1795 p_order_lines IN order_lines)
1796 IS
1797
1798 l_out varchar2(2000);
1799 l_processed boolean := FALSE;
1800 l_print_header boolean := TRUE;
1801
1802 CURSOR csi_txn_cur(p_order_line_id IN number) IS
1803 SELECT transaction_id,
1804 transaction_type_id,
1805 transaction_date,
1806 source_header_ref_id,
1807 source_header_ref,
1808 source_line_ref
1809 FROM csi_transactions
1810 WHERE source_line_ref_id = p_order_line_id;
1811
1812 CURSOR err_cur(p_order_line_id IN number) IS
1813 SELECT transaction_error_id,
1814 source_type,
1815 source_header_ref,
1816 error_text
1817 FROM csi_txn_errors
1818 WHERE source_id = p_order_line_id
1819 AND processed_flag in ('E', 'R');
1820
1821 BEGIN
1822 IF p_order_lines.COUNT > 0 THEN
1823 FOR l_ind IN p_order_lines.FIRST .. p_order_lines.LAST
1824 LOOP
1825 IF p_order_lines(l_ind).fulfilled_flag = 'Y' AND
1826 p_order_lines(l_ind).shipped_flag = 'N'
1827 THEN
1828
1829 IF l_print_header THEN
1830 blank_line;
1831 blank_line;
1832 add('Fulfillment Transactions :-');
1833 add('------------------------');
1834 l_print_header := FALSE;
1835 END IF;
1836
1837 blank_line;
1838 l_out := fill(p_order_lines(l_ind).line_number, 9)||
1839 fill(p_order_lines(l_ind).line_id, 9)||
1840 fill(p_order_lines(l_ind).identified_item_type, 14);
1841 add(l_out);
1842 l_processed := FALSE;
1843 FOR csi_txn_rec IN csi_txn_cur(p_order_lines(l_ind).line_id)
1844 LOOP
1845 l_processed := TRUE;
1846 IF csi_txn_cur%rowcount = 1 THEN
1847 l_out := ' '||fill('csi_txn_id', 11)||
1848 fill('csi_txn_type_id', 16)||
1849 fill('csi_txn_date', 19)||
1850 fill('src_hdr_ref', 12);
1851 add(l_out);
1852 l_out := ' '||fill('----------', 11)||
1853 fill('---------------', 16)||
1854 fill('------------', 19)||
1855 fill('-----------', 12);
1856 add(l_out);
1857 END IF;
1858 l_out := ' '||fill(csi_txn_rec.transaction_id, 11)||
1859 fill(csi_txn_rec.transaction_type_id, 16)||
1860 fill(to_char(csi_txn_rec.transaction_date, 'MM/DD/YY HH24:MI:SS'), 19)||
1861 fill(csi_txn_rec.source_header_ref, 12);
1862 add(l_out);
1863 END LOOP;
1864
1865 --IF NOT (l_processed) THEN
1866 FOR err_rec IN err_cur(p_order_lines(l_ind).line_id)
1867 LOOP
1868 IF err_cur%rowcount = 1 THEN
1869 l_out := ' '||fill('txn_error_id', 13)||
1870 fill('source_type', 12)||
1871 fill('error_text', 75);
1872 add(l_out);
1873 l_out := ' '||fill('------------', 13)||
1874 fill('-----------', 12)||
1875 fill('----------', 75);
1876 add(l_out);
1877 END IF;
1878 l_out := ' '||fill(err_rec.transaction_error_id, 13)||
1879 fill(err_rec.source_type, 12)||
1880 fill(err_rec.error_text, 75);
1881 add(l_out);
1882 END LOOP;
1883 --END IF;
1884
1885 END IF;
1886 END LOOP;
1887 END IF;
1888 END dump_fulfill_transactions;
1889
1890 PROCEDURE dump_shipping_transactions(
1891 p_order_lines IN order_lines)
1892 IS
1893
1894 l_mmt_rec mmt_rec;
1895 l_mmt_tbl mmt_tbl;
1896 l_mut_tbl mut_tbl;
1897 l_all_mut_tbl mut_tbl;
1898 l_srl_mmt_tbl mmt_tbl;
1899
1900 all_mut_ind binary_integer := 0;
1901 mmt_ind binary_integer := 0;
1902 l_out varchar2(2000);
1903 l_print_header boolean := TRUE;
1904 l_error_flag varchar2(1);
1905
1906 CURSOR ship_txn_cur(p_line_id IN number, p_item_id IN number) IS
1907 SELECT transaction_id
1908 FROM mtl_material_transactions
1909 WHERE transaction_source_type_id = 2
1910 AND transaction_action_id = 1
1911 AND inventory_item_id = p_item_id
1912 AND trx_source_line_id = p_line_id
1913 ORDER BY transaction_id desc;
1914
1915 BEGIN
1916
1917 IF p_order_lines.COUNT > 0 THEN
1918 FOR l_ind IN p_order_lines.FIRST .. p_order_lines.LAST
1919 LOOP
1920
1921 IF p_order_lines(l_ind).shipped_flag = 'Y' THEN
1922
1923 FOR ship_txn_rec IN ship_txn_cur(p_order_lines(l_ind).line_id, p_order_lines(l_ind).item_id)
1924 LOOP
1925
1926 IF l_print_header THEN
1927 blank_line;
1928 blank_line;
1929 add('Shipping Transactions :-');
1930 add('---------------------');
1931 l_print_header := FALSE;
1932 END IF;
1933
1934 IF ship_txn_cur%rowcount = 1 THEN
1935 blank_line;
1936 l_out := fill(p_order_lines(l_ind).line_number, 9)||
1937 fill(p_order_lines(l_ind).line_id, 9)||
1938 fill(p_order_lines(l_ind).identified_item_type, 14);
1939 add(l_out);
1940 END IF;
1941
1942 get_mmt_rec(
1943 p_mtl_txn_id => ship_txn_rec.transaction_id,
1944 x_mmt_rec => l_mmt_rec);
1945
1946 dump_mmt_rec(l_mmt_rec);
1947
1948 IF l_mmt_rec.status = 'ERROR' THEN
1949 l_error_flag := 'Y';
1950 ELSE
1951 l_error_flag := 'N';
1952 END IF;
1953
1954 get_mut_tbl(
1955 p_mtl_txn_id => ship_txn_rec.transaction_id,
1956 p_error_flag => l_error_flag,
1957 x_mut_tbl => l_mut_tbl);
1958
1959 dump_mut_tbl(l_mut_tbl);
1960
1961 mmt_ind := mmt_ind + 1;
1962 l_mmt_tbl(mmt_ind) := l_mmt_rec;
1963
1964 IF l_mmt_rec.status = 'ERROR' THEN
1965 IF l_mut_tbl.COUNT > 0 THEN
1966 FOR mut_ind IN l_mut_tbl.FIRST..l_mut_tbl.LAST
1967 LOOP
1968 all_mut_ind := all_mut_ind + 1;
1969 l_all_mut_tbl(all_mut_ind) := l_mut_tbl(mut_ind);
1970 END LOOP;
1971 END IF;
1972 END IF;
1973
1974 END LOOP;
1975
1976 END IF;
1977
1978 END LOOP;
1979
1980 l_print_header := TRUE;
1981 IF l_all_mut_tbl.COUNT > 0 THEN
1982 FOR l_ind IN l_all_mut_tbl.FIRST .. l_all_mut_tbl.LAST
1983 LOOP
1984
1985 IF l_print_header THEN
1986 blank_line;
1987 blank_line;
1988 add('Serial Transactions :-');
1989 add('-------------------');
1990 l_print_header := FALSE;
1991 END IF;
1992
1993 serial_status(
1994 p_serial_number => l_all_mut_tbl(l_ind).serial_number,
1995 p_item_id => l_all_mut_tbl(l_ind).item_id,
1996 p_standalone_mode => 'N');
1997 END LOOP;
1998 END IF;
1999
2000 END IF;
2001
2002 END dump_shipping_transactions;
2003
2004 PROCEDURE get_item_type(
2005 p_item_type_code IN varchar2,
2006 p_line_id IN number,
2007 p_ato_line_id IN number,
2008 p_top_model_line_id IN number,
2009 x_item_type OUT nocopy varchar2)
2010 IS
2011 l_sub_model_flag varchar2(1);
2012 BEGIN
2013
2014 IF p_item_type_code = 'MODEL' THEN
2015 IF p_ato_line_id = p_line_id THEN
2016 x_item_type := 'ATO_MODEL';
2017 ELSIF p_ato_line_id is null THEN
2018 x_item_type := 'PTO_MODEL';
2019 END IF;
2020 ELSIF p_item_type_code = 'KIT' THEN
2021 x_item_type := 'KIT';
2022 ELSIF p_item_type_code = 'OPTION' THEN
2023 IF p_ato_line_id is not null THEN
2024 x_item_type := 'ATO_OPTION';
2025 ELSIF p_ato_line_id is null THEN
2026 x_item_type := 'PTO_OPTION';
2027 END IF;
2028 ELSIF p_item_type_code = 'CLASS' THEN
2029 IF p_ato_line_id is not null THEN
2030 BEGIN
2031 SELECT 'Y'
2032 INTO l_sub_model_flag
2033 FROM sys.dual
2034 WHERE exists (
2035 SELECT 'X'
2036 FROM bom_cto_order_lines
2037 WHERE ato_line_id = p_ato_line_id
2038 AND parent_ato_line_id = p_line_id);
2039 x_item_type := 'ATO_SUB_MODEL';
2040 EXCEPTION
2041 WHEN no_data_found THEN
2042 x_item_type := 'ATO_CLASS';
2043 END;
2044 ELSIF p_ato_line_id is null THEN
2045 x_item_type := 'PTO_CLASS';
2046 END IF;
2047 ELSIF p_item_type_code = 'INCLUDED' THEN
2048 x_item_type := 'INCLUDED_ITEM';
2049 ELSIF p_item_type_code = 'CONFIG' THEN
2050 x_item_type := 'CONFIG_ITEM';
2051 ELSIF p_item_type_code = 'STANDARD' THEN
2052 x_item_type := 'STANDARD';
2053 END IF;
2054 END get_item_type;
2055
2056 PROCEDURE dump_item_rec(
2057 p_item_rec IN item_rec)
2058 IS
2059 l_out varchar2(2000);
2060 BEGIN
2061 blank_line;
2062
2063 l_out := ' '||fill('item', 22)||': '||p_item_rec.item;
2064 add(l_out);
2065
2066 l_out := ' '||fill('item_id',22)||': '||fill(p_item_rec.item_id, 20)||
2067 fill('organization_id', 22)||': '||fill(p_item_rec.organization_id, 25);
2068
2069 add(l_out);
2070
2071 l_out := ' '||fill('ib_trackable_flag', 22)||': '||fill(p_item_rec.ib_trackable_flag, 20)||
2072 fill('srl_control_code', 22)||': '||fill(p_item_rec.serial_code, 20);
2073 add(l_out);
2074
2075 l_out := ' '||fill('shippable_flag', 22)||': '||fill(p_item_rec.shippable_flag, 20)||
2076 fill('lot_control_code', 22)||': '||fill(p_item_rec.lot_code, 20);
2077 add(l_out);
2078
2079 l_out := ' '||fill('returnable_flag', 22)||': '||fill(p_item_rec.returnable_flag, 20)||
2080 fill('rev_control_code',22)||': '||fill(p_item_rec.revision_code, 20);
2081 add(l_out);
2082
2083 l_out := ' '||fill('pick_component_flag', 22)||': '||fill(p_item_rec.pick_flag, 20)||
2084 fill('loc_control_code', 22)||': '||fill(p_item_rec.locator_code, 20);
2085 add(l_out);
2086
2087 l_out := ' '||fill('inv_transactable_flag', 22)||': '||fill(p_item_rec.inv_transactable_flag, 20)||
2088 fill('primary_uom_code', 22)||': '||fill(p_item_rec.primary_uom_code, 20);
2089 add(l_out);
2090
2091 l_out := ' '||fill('bom_item_type', 22)||': '||fill(p_item_rec.bom_item_type, 20)||
2092 fill('wip_supply_type', 22)||': '||fill(p_item_rec.wip_supply_type, 20);
2093
2094 add(l_out);
2095 l_out := ' '||fill('plan_make_buy_code', 22)||': '||fill(p_item_rec.make_buy_code, 20)||
2096 fill('reservable_type', 22)||': '||fill(p_item_rec.reservable_type, 20);
2097 add(l_out);
2098
2099 END dump_item_rec;
2100
2101 PROCEDURE dump_item_info_for_order(
2102 p_order_lines IN order_lines)
2103 IS
2104 l_item_rec item_rec;
2105 l_out varchar2(2000);
2106 BEGIN
2107 IF p_order_lines.COUNT > 0 THEN
2108 blank_line;
2109 add('Item details for Order :-');
2110 add('----------------------');
2111 FOR l_ind IN p_order_lines.FIRST .. p_order_lines.LAST
2112 LOOP
2113
2114 blank_line;
2115 l_out := fill(p_order_lines(l_ind).line_number, 9)||
2116 fill(p_order_lines(l_ind).line_id, 9)||
2117 fill(p_order_lines(l_ind).identified_item_type, 14);
2118 add(l_out);
2119
2120 get_item_rec(
2121 p_item_id => p_order_lines(l_ind).item_id,
2122 p_organization_id => p_order_lines(l_ind).ship_from_org_id,
2123 x_item_rec => l_item_rec);
2124
2125 dump_item_rec(
2126 p_item_rec => l_item_rec);
2127
2128 END LOOP;
2129 END IF;
2130 END dump_item_info_for_order;
2131
2132 PROCEDURE dump_order_details(
2133 p_order_lines IN order_lines)
2134 IS
2135 l_item_rec item_rec;
2136 BEGIN
2137
2138 IF p_order_lines.COUNT > 0 THEN
2139 blank_line;
2140 add('Order Details :-');
2141 add('----------------');
2142 FOR l_ind IN p_order_lines.FIRST .. p_order_lines.LAST
2143 LOOP
2144
2145 dump_order_detail(p_order_lines(l_ind));
2146
2147 get_item_rec(
2148 p_item_id => p_order_lines(l_ind).item_id,
2149 p_organization_id => p_order_lines(l_ind).ship_from_org_id,
2150 x_item_rec => l_item_rec);
2151
2152 dump_item_rec(
2153 p_item_rec => l_item_rec);
2154
2155 END LOOP;
2156 END IF;
2157 END dump_order_details;
2158
2159 PROCEDURE get_ato_models(
2160 p_order_lines IN order_lines,
2161 x_ato_model_tbl OUT nocopy ato_model_tbl)
2162 IS
2163 l_ind binary_integer := 0;
2164 l_ato_model_tbl ato_model_tbl;
2165 l_check_in_cto_table boolean := FALSE;
2166 l_config_item_id number;
2167 l_request_id number;
2168
2169 CURSOR wdj_cur(
2170 p_line_id IN number,
2171 p_item_id IN number,
2172 p_organization_id IN number)
2173 IS
2174 SELECT wip_entity_id,
2175 organization_id,
2176 request_id
2177 FROM wip_discrete_jobs
2178 WHERE primary_item_id = p_item_id
2179 AND organization_id = p_organization_id
2180 AND source_line_id = p_line_id
2181 AND status_type <> 7 -- excluding the cancelled wip jobs
2182 ORDER by wip_entity_id desc;
2183
2184 CURSOR wdj_sub_cur(
2185 p_item_id IN number,
2186 p_organization_id IN number,
2187 p_request_id IN number)
2188 IS
2189 SELECT wip_entity_id,
2190 organization_id,
2191 request_id
2192 FROM wip_discrete_jobs
2193 WHERE primary_item_id = p_item_id
2194 AND organization_id = p_organization_id
2195 AND request_id = p_request_id
2196 AND status_type <> 7 -- excluding the cancelled wip jobs
2197 ORDER by wip_entity_id desc;
2198
2199 BEGIN
2200 IF p_order_lines.COUNT > 0 THEN
2201 FOR p_ind IN p_order_lines.FIRST .. p_order_lines.LAST
2202 LOOP
2203 IF p_order_lines(p_ind).identified_item_type in ('ATO_MODEL', 'ATO_SUB_MODEL') THEN
2204
2205 l_ind := l_ind + 1;
2206
2207 l_ato_model_tbl(l_ind).model_line_id := p_order_lines(p_ind).line_id;
2208 l_ato_model_tbl(l_ind).model_item_id := p_order_lines(p_ind).item_id;
2209
2210 l_check_in_cto_table := FALSE;
2211
2212 IF p_order_lines(p_ind).identified_item_type = 'ATO_MODEL' THEN
2213 l_ato_model_tbl(l_ind).sub_model_flag := 'N';
2214 BEGIN
2215 SELECT line_id,
2216 inventory_item_id
2217 INTO l_ato_model_tbl(l_ind).config_line_id,
2218 l_ato_model_tbl(l_ind).config_item_id
2219 FROM oe_order_lines_all
2220 WHERE header_id = p_order_lines(p_ind).header_id
2221 AND link_to_line_id = p_order_lines(p_ind).ato_line_id
2222 AND item_type_code = 'CONFIG';
2223 EXCEPTION
2224 WHEN no_data_found THEN
2225 l_check_in_cto_table := TRUE;
2226 WHEN too_many_rows THEN
2227 BEGIN
2228 SELECT line_id,
2229 inventory_item_id
2230 INTO l_ato_model_tbl(l_ind).config_line_id,
2231 l_ato_model_tbl(l_ind).config_item_id
2232 FROM oe_order_lines_all
2233 WHERE header_id = p_order_lines(p_ind).header_id
2234 AND link_to_line_id = p_order_lines(p_ind).ato_line_id
2235 AND item_type_code = 'CONFIG'
2236 AND split_from_line_id is null;
2237 EXCEPTION
2238 WHEN others THEN
2239 l_check_in_cto_table := TRUE;
2240 END;
2241 END;
2242 ELSE
2243 l_check_in_cto_table := TRUE;
2244 l_ato_model_tbl(l_ind).sub_model_flag := 'Y';
2245 l_ato_model_tbl(l_ind).config_line_id := null;
2246 END IF;
2247
2248 SELECT wip_supply_type,
2249 parent_ato_line_id,
2250 config_item_id
2251 INTO l_ato_model_tbl(l_ind).wip_supply_type,
2252 l_ato_model_tbl(l_ind).parent_ato_line_id,
2253 l_config_item_id
2254 FROM bom_cto_order_lines
2255 WHERE line_id = p_order_lines(p_ind).line_id;
2256
2257 l_ato_model_tbl(l_ind).config_item_id := l_config_item_id;
2258
2259 IF l_ato_model_tbl(l_ind).wip_supply_type = 6 OR
2260 l_ato_model_tbl(l_ind).config_line_id is null
2261 THEN
2262 l_ato_model_tbl(l_ind).phantom_flag := 'Y';
2263 ELSE
2264 l_ato_model_tbl(l_ind).phantom_flag := 'N';
2265 END IF;
2266
2267 IF l_ato_model_tbl(l_ind).phantom_flag = 'N' THEN
2268 FOR wdj_rec IN wdj_cur(
2269 p_line_id => l_ato_model_tbl(l_ind).config_line_id,
2270 p_item_id => l_ato_model_tbl(l_ind).config_item_id,
2271 p_organization_id => p_order_lines(p_ind).ship_from_org_id)
2272 LOOP
2273 l_ato_model_tbl(l_ind).wip_entity_id := wdj_rec.wip_entity_id;
2274 l_ato_model_tbl(l_ind).organization_id := wdj_rec.organization_id;
2275 l_request_id := wdj_rec.request_id;
2276 exit;
2277 END LOOP;
2278
2279 ELSE
2280 IF l_request_id IS not null THEN
2281 FOR wdj_sub_rec IN wdj_sub_cur(
2282 p_item_id => l_ato_model_tbl(l_ind).config_item_id,
2283 p_organization_id => p_order_lines(p_ind).ship_from_org_id,
2284 p_request_id => l_request_id)
2285 LOOP
2286 l_ato_model_tbl(l_ind).wip_entity_id := wdj_sub_rec.wip_entity_id;
2287 l_ato_model_tbl(l_ind).organization_id := wdj_sub_rec.organization_id;
2288 exit;
2289 END LOOP;
2290 END IF;
2291 END IF;
2292
2293 IF l_ato_model_tbl(l_ind).wip_entity_id is not null THEN
2294 SELECT wip_entity_name
2295 INTO l_ato_model_tbl(l_ind).wip_entity_name
2296 FROM wip_entities
2297 WHERE wip_entity_id = l_ato_model_tbl(l_ind).wip_entity_id;
2298 END IF;
2299
2300 END IF;
2301 END LOOP;
2302 END IF;
2303 x_ato_model_tbl := l_ato_model_tbl;
2304 END get_ato_models;
2305
2306 PROCEDURE dump_ato_models(
2307 p_order_lines IN order_lines)
2308 IS
2309 l_ato_model_tbl ato_model_tbl;
2310 l_out varchar2(2000);
2311 BEGIN
2312
2313 IF p_order_lines.COUNT > 0 THEN
2314 get_ato_models(
2315 p_order_lines => p_order_lines,
2316 x_ato_model_tbl => l_ato_model_tbl);
2317
2318 IF l_ato_model_tbl.COUNT > 0 THEN
2319 blank_line;
2320 add('ATO Model Information :-');
2321 blank_line;
2322 l_out := fill('ato_line_id', 12)||
2323 fill('ato_item_id', 12)||
2324 fill('con_line_id', 12)||
2325 fill('con_item_id', 12)||
2326 fill('wip_job_id', 12)||
2327 fill('wip_job_name', 20);
2328 add(l_out);
2329 l_out := fill('-----------', 12)||
2330 fill('-----------', 12)||
2331 fill('-----------', 12)||
2332 fill('-----------', 12)||
2333 fill('----------', 12)||
2334 fill('------------', 20);
2335 add(l_out);
2336 FOR l_ind IN l_ato_model_tbl.FIRST .. l_ato_model_tbl.LAST
2337 LOOP
2338
2339 l_out := fill(l_ato_model_tbl(l_ind).model_line_id, 12)||
2340 fill(l_ato_model_tbl(l_ind).model_item_id, 12)||
2341 fill(l_ato_model_tbl(l_ind).config_line_id, 12)||
2342 fill(l_ato_model_tbl(l_ind).config_item_id, 12)||
2343 fill(l_ato_model_tbl(l_ind).wip_entity_id, 12)||
2344 fill(l_ato_model_tbl(l_ind).wip_entity_name, 20);
2345 add(l_out);
2346
2347 END LOOP;
2348
2349 FOR l_ind IN l_ato_model_tbl.FIRST .. l_ato_model_tbl.LAST
2350 LOOP
2351 IF l_ato_model_tbl(l_ind).wip_entity_name is not null THEN
2352 job_status(
2353 p_job_name => l_ato_model_tbl(l_ind).wip_entity_name,
2354 p_organization_id => l_ato_model_tbl(l_ind).organization_id);
2355 END IF;
2356 END LOOP;
2357
2358 END IF;
2359
2360 END IF;
2361
2362 END dump_ato_models;
2363
2364 PROCEDURE dump_install_parameters IS
2365 l_out varchar2(2000);
2366 CURSOR param_cur IS
2367 SELECT internal_party_id,
2368 project_location_id,
2369 wip_location_id,
2370 in_transit_location_id,
2371 po_location_id,
2372 category_set_id,
2373 freeze_flag,
2374 freeze_date,
2375 show_all_party_location,
2376 ownership_override_at_txn,
2377 sfm_queue_bypass_flag,
2378 auto_allocate_comp_at_wip,
2379 to_date(null) txn_seq_start_date,
2380 null ownership_cascade_at_txn
2381 FROM csi_install_parameters;
2382 BEGIN
2383
2384 blank_line;
2385 blank_line;
2386 add('Install Parameters :-');
2387 add('------------------');
2388 blank_line;
2389
2390 FOR param_rec IN param_cur
2391 LOOP
2392 l_out := ' '||fill('internal_party_id', 25)||': '||param_rec.internal_party_id;
2393 add(l_out);
2394 l_out := ' '||fill('project_location_id', 25)||': '||param_rec.project_location_id;
2395 add(l_out);
2396 l_out := ' '||fill('wip_location_id', 25)||': '||param_rec.wip_location_id;
2397 add(l_out);
2398 l_out := ' '||fill('in_transit_location_id', 25)||': '||param_rec.in_transit_location_id;
2399 add(l_out);
2400 l_out := ' '||fill('po_location_id', 25)||': '||param_rec.po_location_id;
2401 add(l_out);
2402 l_out := ' '||fill('category_set_id', 25)||': '||param_rec.category_set_id;
2403 add(l_out);
2404 l_out := ' '||fill('freeze_flag', 25)||': '||param_rec.freeze_flag;
2405 add(l_out);
2406 l_out := ' '||fill('freeze_date', 25)||': '||
2407 to_char(param_rec.freeze_date, 'DD-MON-YYYY HH24:MI:SS');
2408 add(l_out);
2409 l_out := ' '||fill('show_all_party_location', 25)||': '||param_rec.show_all_party_location;
2410 add(l_out);
2411 l_out := ' '||fill('ownership_override_at_txn', 25)||': '||param_rec.ownership_override_at_txn;
2412 add(l_out);
2413 l_out := ' '||fill('sfm_queue_bypass_flag', 25)||': '||param_rec.sfm_queue_bypass_flag;
2414 add(l_out);
2415 l_out := ' '||fill('auto_allocate_comp_at_wip', 25)||': '||param_rec.auto_allocate_comp_at_wip;
2416 add(l_out);
2417 l_out := ' '||fill('txn_seq_start_date', 25)||': '||
2418 to_char(param_rec.txn_seq_start_date, 'DD-MON-YYYY HH24:MI:SS');
2419 add(l_out);
2420 l_out := ' '||fill('ownership_cascade_at_txn', 25)||': '||param_rec.ownership_cascade_at_txn;
2421 add(l_out);
2422 END LOOP;
2423 END dump_install_parameters;
2424
2425 PROCEDURE get_order_lines(
2426 p_order_number IN number,
2427 x_order_lines OUT nocopy order_lines)
2428 IS
2429
2430 l_order_lines order_lines;
2431 l_ind binary_integer;
2432 l_message varchar2(2000);
2433 l_line_header varchar2(2000);
2434 l_item_rec item_rec;
2435 l_macd_processing boolean := FALSE;
2436 l_om_session_key csi_utility_grp.config_session_key;
2437 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2438
2439 CURSOR order_cur(p_order_number IN number) IS
2440 SELECT oeh.header_id,
2441 oel.line_id,
2442 oel.inventory_item_id,
2443 oel.ordered_quantity,
2444 oel.order_quantity_uom,
2445 oel.ordered_item,
2446 oel.item_revision,
2447 oel.line_number||'.'||nvl(oel.option_number,0)||'.'||oel.shipment_number line_number,
2448 nvl(oel.ship_from_org_id,oeh.ship_from_org_id) ship_from_org_id,
2449 nvl(oel.sold_to_org_id, oeh.sold_to_org_id) sold_to_org_id,
2450 nvl(oel.deliver_to_org_id, oeh.deliver_to_org_id) deliver_to_org_id,
2451 nvl(oel.invoice_to_org_id, oeh.invoice_to_org_id) invoice_to_org_id,
2452 nvl(oel.ship_to_org_id, oeh.ship_to_org_id) ship_to_org_id,
2453 oel.fulfilled_quantity,
2454 oel.flow_status_code,
2455 oel.item_type_code,
2456 oel.link_to_line_id,
2457 oel.ato_line_id,
2458 oel.top_model_line_id,
2459 oel.sort_order,
2460 oel.org_id,
2461 oeh.order_type_id,
2462 oel.line_type_id,
2463 oel.ship_to_contact_id,
2464 oel.invoice_to_contact_id,
2465 oel.deliver_to_contact_id,
2466 nvl(oel.price_list_id, oeh.price_list_id) price_list_id,
2467 oel.unit_selling_price,
2468 oel.creation_date,
2469 oel.component_sequence_id,
2470 oel.line_category_code,
2471 oel.cancelled_flag,
2472 oel.source_type_code,
2473 oel.drop_ship_flag,
2474 nvl(oel.fulfilled_flag, 'N') fulfilled_flag,
2475 oel.configuration_id,
2476 oel.config_header_id,
2477 oel.config_rev_nbr,
2478 oel.shippable_flag,
2479 oel.fulfillment_date,
2480 oel.shipping_interfaced_flag,
2481 oel.split_from_line_id,
2482 oel.actual_shipment_date,
2483 oel.shipped_quantity
2484 FROM oe_order_lines_all oel,
2485 oe_order_headers_all oeh
2486 WHERE oeh.order_number = p_order_number
2487 AND oel.header_id = oeh.header_id
2488 ORDER BY oel.line_number, oel.sort_order;
2489
2490
2491 BEGIN
2492
2493 FOR order_rec in order_cur(p_order_number)
2494 LOOP
2495
2496 l_ind := order_cur%rowcount;
2497 l_order_lines(l_ind).header_id := order_rec.header_id;
2498 l_order_lines(l_ind).line_id := order_rec.line_id;
2499 l_order_lines(l_ind).line_number := order_rec.line_number;
2500 l_order_lines(l_ind).item_id := order_rec.inventory_item_id;
2501 l_order_lines(l_ind).ship_from_org_id := order_rec.ship_from_org_id;
2502 l_order_lines(l_ind).item_number := order_rec.ordered_item;
2503 l_order_lines(l_ind).order_qty := order_rec.ordered_quantity;
2504 l_order_lines(l_ind).order_uom := order_rec.order_quantity_uom;
2505 l_order_lines(l_ind).line_status := order_rec.flow_status_code;
2506 l_order_lines(l_ind).item_type := order_rec.item_type_code;
2507 l_order_lines(l_ind).link_to_line_id := order_rec.link_to_line_id;
2508 l_order_lines(l_ind).ato_line_id := order_rec.ato_line_id;
2509 l_order_lines(l_ind).top_model_line_id := order_rec.top_model_line_id;
2510 l_order_lines(l_ind).sort_order := order_rec.sort_order;
2511
2512 l_order_lines(l_ind).level := (length(order_rec.sort_order)/4) - 1;
2513
2514 get_item_type(
2515 p_item_type_code => order_rec.item_type_code,
2516 p_line_id => order_rec.line_id,
2517 p_ato_line_id => order_rec.ato_line_id,
2518 p_top_model_line_id => order_rec.top_model_line_id,
2519 x_item_type => l_order_lines(l_ind).identified_item_type);
2520
2521 get_item_rec(
2522 p_item_id => order_rec.inventory_item_id,
2523 p_organization_id => order_rec.ship_from_org_id,
2524 x_item_rec => l_item_rec);
2525
2526 l_order_lines(l_ind).ib_trackable_flag := l_item_rec.ib_trackable_flag;
2527 l_order_lines(l_ind).shippable_flag := l_item_rec.shippable_flag;
2528
2529
2530 l_order_lines(l_ind).org_id := order_rec.org_id;
2531 l_order_lines(l_ind).order_type_id := order_rec.order_type_id;
2532 l_order_lines(l_ind).line_type_id := order_rec.line_type_id;
2533 l_order_lines(l_ind).ship_to_contact_id := order_rec.ship_to_contact_id;
2534 l_order_lines(l_ind).invoice_to_contact_id := order_rec.invoice_to_contact_id;
2535 l_order_lines(l_ind).deliver_to_contact_id := order_rec.deliver_to_contact_id;
2536 l_order_lines(l_ind).price_list_id := order_rec.price_list_id;
2537 l_order_lines(l_ind).unit_selling_price := order_rec.unit_selling_price;
2538 l_order_lines(l_ind).creation_date := order_rec.creation_date;
2539 l_order_lines(l_ind).comp_seq_id := order_rec.component_sequence_id;
2540 l_order_lines(l_ind).line_category_code := order_rec.line_category_code;
2541 l_order_lines(l_ind).cancelled_flag := order_rec.cancelled_flag;
2542 l_order_lines(l_ind).source_type_code := order_rec.source_type_code;
2543 l_order_lines(l_ind).drop_ship_flag := order_rec.drop_ship_flag;
2544 l_order_lines(l_ind).fulfilled_flag := order_rec.fulfilled_flag;
2545 l_order_lines(l_ind).configuration_id := order_rec.configuration_id;
2546 l_order_lines(l_ind).config_header_id := order_rec.config_header_id;
2547 l_order_lines(l_ind).config_rev_nbr := order_rec.config_rev_nbr;
2548 l_order_lines(l_ind).fulfillment_date := order_rec.fulfillment_date;
2549 l_order_lines(l_ind).shipment_date := order_rec.actual_shipment_date;
2550 l_order_lines(l_ind).shipped_flag := order_rec.shipping_interfaced_flag;
2551 l_order_lines(l_ind).split_from_line_id := order_rec.split_from_line_id;
2552
2553 l_order_lines(l_ind).ship_to_org_id := order_rec.ship_to_org_id;
2554 l_order_lines(l_ind).invoice_to_org_id := order_rec.invoice_to_org_id;
2555 l_order_lines(l_ind).deliver_to_org_id := order_rec.deliver_to_org_id;
2556 l_order_lines(l_ind).item_revision := order_rec.item_revision;
2557 l_order_lines(l_ind).fulfilled_quantity := order_rec.fulfilled_quantity;
2558 l_order_lines(l_ind).shipped_quantity := order_rec.shipped_quantity;
2559
2560
2561 l_om_session_key.session_hdr_id := order_rec.config_header_id;
2562 l_om_session_key.session_rev_num := order_rec.config_rev_nbr;
2563 l_om_session_key.session_item_id := order_rec.configuration_id;
2564
2565 l_macd_processing := csi_interface_pkg.check_macd_processing(
2566 p_config_session_key => l_om_session_key,
2567 x_return_status => l_return_status);
2568
2569 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2570 RAISE fnd_api.g_exc_error;
2571 END IF;
2572
2573 IF l_macd_processing THEN
2574 l_order_lines(l_ind).macd_flag := 'Y';
2575 END IF;
2576
2577 END LOOP;
2578
2579 x_order_lines := l_order_lines;
2580
2581 END get_order_lines;
2582
2583 PROCEDURE dump_shipping_file_versions IS
2584 l_source_files csi_debug_pkg.source_files;
2585 BEGIN
2586
2587 l_source_files(1).file_name := 'csipioss.pls';
2588 l_source_files(2).file_name := 'csipiosb.pls';
2589 l_source_files(3).file_name := 'csipiofs.pls';
2590 l_source_files(4).file_name := 'csipiofb.pls';
2591 l_source_files(5).file_name := 'csiutls.pls';
2592 l_source_files(6).file_name := 'csiutlb.pls';
2593
2594 csi_debug_pkg.dump_file_versions(l_source_files);
2595
2596 END dump_shipping_file_versions;
2597
2598 PROCEDURE order_status(
2599 p_order_number IN number)
2600 IS
2601 l_order_lines order_lines;
2602 l_all_order_lines order_lines;
2603 BEGIN
2604
2605 csi_t_gen_utility_pvt.build_file_name(
2606 p_file_segment1 => 'csiorder',
2607 p_file_segment2 => p_order_number);
2608
2609 get_order_lines(
2610 p_order_number => p_order_number,
2611 x_order_lines => l_order_lines);
2612
2613 IF l_order_lines.COUNT > 0 THEN
2614
2615 dump_order_summary(
2616 p_order_number => p_order_number,
2617 px_order_lines => l_order_lines);
2618
2619 dump_order_details(
2620 p_order_lines => l_order_lines);
2621
2622 dump_ato_models(
2623 p_order_lines => l_order_lines);
2624
2625 l_all_order_lines := l_order_lines;
2626
2627 get_ib_trackable_lines(
2628 px_order_lines => l_order_lines);
2629
2630 dump_installation_details(
2631 p_order_lines => l_order_lines,
2632 p_source_table => 'OE_ORDER_LINES_ALL');
2633
2634 dump_installation_details(
2635 p_order_lines => l_order_lines,
2636 p_source_table => 'WSH_DELIVERY_DETAILS');
2637
2638 dump_shipping_transactions(
2639 p_order_lines => l_order_lines);
2640
2641 dump_fulfill_transactions(
2642 p_order_lines => l_order_lines);
2643
2644 /*
2645 IF l_inst_tbl_cache.count > 0 THEN
2646 blank_line;
2647 add('Instance Details :-');
2648 add('---------------- :-');
2649 FOR l_ind IN l_inst_tbl_cache.FIRST .. l_inst_tbl_cache.LAST
2650 LOOP
2651 blank_line;
2652 instance_status(
2653 p_instance_id => l_inst_tbl_cache(l_ind).instance_id,
2654 p_standalone_mode => 'N');
2655 END LOOP;
2656 END IF;
2657 */
2658
2659 dump_install_parameters;
2660
2661 dump_shipping_file_versions;
2662
2663 ELSE
2664 add('Order Number '||p_order_number||' is invalid.');
2665 END IF;
2666
2667 END order_status;
2668
2669 PROCEDURE rma_status(
2670 p_rma_number IN number)
2671 IS
2672 BEGIN
2673 null;
2674 END rma_status;
2675
2676 PROCEDURE instance_status(
2677 p_instance_id IN number,
2678 p_standalone_mode IN varchar2)
2679 IS
2680
2681 CURSOR inst_cur(p_inst_id in number) IS
2682 SELECT instance_id,
2683 instance_number,
2684 inventory_item_id,
2685 serial_number,
2686 lot_number,
2687 location_type_code,
2688 location_id,
2689 instance_usage_code,
2690 last_oe_order_line_id,
2691 last_vld_organization_id,
2692 active_end_date
2693 FROM csi_item_instances
2694 WHERE instance_id = p_inst_id;
2695
2696 CURSOR comp_cur(p_inst_id in number) IS
2697 SELECT iir.relationship_id,
2698 iir.subject_id,
2699 iir.relationship_type_code,
2700 iir.active_end_date rel_end_date,
2701 ii.serial_number,
2702 ii.location_type_code,
2703 ii.instance_usage_code,
2704 ii.active_end_date instance_end_date
2705 FROM csi_ii_relationships iir,
2706 csi_item_instances ii
2707 WHERE iir.object_id = p_inst_id
2708 AND ii.instance_id = iir.subject_id;
2709
2710 CURSOR txn_cur(p_inst_id IN number) IS
2711 SELECT ct.transaction_id,
2712 ct.transaction_type_id,
2713 to_char(ct.transaction_date, 'mm/dd/yy hh:mi:ss') transaction_date,
2714 to_char(ct.source_transaction_date, 'mm/dd/yy hh:mi:ss') source_transaction_date,
2715 ctt.source_transaction_type,
2716 ct.inv_material_transaction_id
2717 FROM csi_item_instances_h ciih,
2718 csi_transactions ct,
2719 csi_txn_types ctt
2720 WHERE ciih.instance_id = p_inst_id
2721 AND ct.transaction_id = ciih.transaction_id
2722 AND ctt.transaction_type_id = ct.transaction_type_id
2723 ORDER BY ct.source_transaction_date desc;
2724
2725 l_out varchar2(2000);
2726
2727 BEGIN
2728
2729 IF csi_t_gen_utility_pvt.g_file is null THEN
2730 csi_t_gen_utility_pvt.build_file_name(
2731 p_file_segment1 => 'csiinst',
2732 p_file_segment2 => p_instance_id);
2733 END IF;
2734
2735 FOR inst_rec IN inst_cur(p_instance_id)
2736 LOOP
2737
2738 add('Instance ID : '||inst_rec.instance_id);
2739 blank_line;
2740
2741 l_out := ' '||fill('Inst#', 10)||
2742 fill('Serial#', 15)||
2743 fill('Lot#', 15)||
2744 fill('LocationType', 18)||
2745 fill('UsageCode', 18)||
2746 fill('EndDate', 10);
2747 add(l_out);
2748
2749 l_out := ' '||fill('------', 10)||
2750 fill('-------', 15)||
2751 fill('----', 15)||
2752 fill('------------', 18)||
2753 fill('---------', 18)||
2754 fill('------', 10);
2755 add(l_out);
2756
2757 l_out := ' '||fill(inst_rec.instance_number, 10)||
2758 fill(inst_rec.serial_number, 15)||
2759 fill(inst_rec.lot_number, 15)||
2760 fill(inst_rec.location_type_code, 18)||
2761 fill(inst_rec.instance_usage_code, 18)||
2762 fill(inst_rec.active_end_date, 10);
2763 add(l_out);
2764
2765
2766 FOR comp_rec IN comp_cur(p_instance_id)
2767 LOOP
2768 IF comp_cur%rowcount = 1 THEN
2769 blank_line;
2770 add(' Instance Relationships :-');
2771 blank_line;
2772 l_out := ' '||
2773 fill('RelID', 10)||
2774 fill('CompInstID', 12)||
2775 fill('Relationship', 14)||
2776 fill('RelEndDate', 12)||
2777 fill('Serial#', 15)||
2778 fill('UsageCode', 16)||
2779 fill('InstEndDate', 12);
2780 add(l_out);
2781 l_out := ' '||
2782 fill('-----', 10)||
2783 fill('----------', 12)||
2784 fill('------------', 14)||
2785 fill('----------', 12)||
2786 fill('-------', 15)||
2787 fill('---------', 16)||
2788 fill('-----------', 12);
2789 add(l_out);
2790 END IF;
2791
2792 l_out := ' '||
2793 fill(comp_rec.relationship_id, 10)||
2794 fill(comp_rec.subject_id, 12)||
2795 fill(comp_rec.relationship_type_code, 14)||
2796 fill(comp_rec.rel_end_date, 12)||
2797 fill(comp_rec.serial_number, 15)||
2798 fill(comp_rec.instance_usage_code, 18)||
2799 fill(comp_rec.instance_end_date, 12);
2800 add(l_out);
2801 END LOOP;
2802
2803 FOR txn_rec IN txn_cur(p_instance_id)
2804 LOOP
2805 IF txn_cur%rowcount = 1 THEN
2806 blank_line;
2807 add(' Instance Transactions :-');
2808 blank_line;
2809 l_out := ' '||
2810 fill('TTID', 5)||
2811 fill('TxnType', 25)||
2812 fill('TxnID', 10)||
2813 fill('TxnDate', 19)||
2814 fill('SrcTxnDate', 19)||
2815 fill('MtlTxnID', 12);
2816 add(l_out);
2817 l_out := ' '||
2818 fill('----', 5)||
2819 fill('-------', 25)||
2820 fill('-----', 10)||
2821 fill('-------', 19)||
2822 fill('----------', 19)||
2823 fill('--------', 12);
2824 add(l_out);
2825 END IF;
2826
2827 l_out := ' '||
2828 fill(txn_rec.transaction_type_id, 5)||
2829 fill(txn_rec.source_transaction_type, 25)||
2830 fill(txn_rec.transaction_id, 10)||
2831 fill(txn_rec.transaction_date, 19)||
2832 fill(txn_rec.source_transaction_date, 19)||
2833 fill(txn_rec.inv_material_transaction_id, 12);
2834 add(l_out);
2835
2836 END LOOP;
2837
2838 IF p_standalone_mode = 'Y' THEN
2839 IF inst_rec.serial_number is not null THEN
2840 serial_status(
2841 p_serial_number => inst_rec.serial_number,
2842 p_item_id => inst_rec.inventory_item_id,
2843 p_standalone_mode => 'N');
2844 END IF;
2845 END IF;
2846
2847 END LOOP;
2848
2849 END instance_status;
2850
2851 PROCEDURE job_status(
2852 p_job_name IN varchar2,
2853 p_organization_id IN number)
2854 IS
2855 l_job_rec job_rec;
2856 l_item_rec item_rec;
2857 l_wip_req_tbl wip_req_tbl;
2858 l_wip_mmt_tbl mmt_tbl;
2859 BEGIN
2860
2861 IF csi_t_gen_utility_pvt.g_file is null THEN
2862 csi_t_gen_utility_pvt.build_file_name(
2863 p_file_segment1 => 'csijob',
2864 p_file_segment2 => p_job_name);
2865 END IF;
2866
2867 get_job_rec(
2868 p_wip_entity_name => p_job_name,
2869 p_organization_id => p_organization_id,
2870 x_job_rec => l_job_rec);
2871
2872 dump_job_rec(l_job_rec);
2873
2874 get_item_rec(
2875 p_item_id => l_job_rec.primary_item_id,
2876 p_organization_id => l_job_rec.organization_id,
2877 x_item_rec => l_item_rec);
2878
2879 dump_item_rec(
2880 p_item_rec => l_item_rec);
2881
2882 get_wip_req_tbl(
2883 p_wip_entity_id => l_job_rec.wip_entity_id,
2884 p_organization_id => l_job_rec.organization_id,
2885 x_wip_req_tbl => l_wip_req_tbl);
2886
2887 dump_wip_req_tbl(
2888 p_wip_req_tbl => l_wip_req_tbl);
2889
2890 get_wip_mmt_tbl(
2891 p_wip_entity_id => l_job_rec.wip_entity_id,
2892 p_organization_id => l_job_rec.organization_id,
2893 x_wip_mmt_tbl => l_wip_mmt_tbl);
2894
2895 dump_wip_mmt_tbl(
2896 p_wip_mmt_tbl => l_wip_mmt_tbl);
2897
2898 END job_status;
2899
2900 PROCEDURE dump_patch_history(
2901 p_file_id IN number)
2902 IS
2903 CURSOR patch_hist_cur(p_file_id IN number) IS
2904 SELECT distinct afv.version, afv.creation_date, ab.bug_number
2905 FROM ad_file_versions afv,
2906 ad_patch_run_bug_actions aprba,
2907 ad_patch_run_bugs aprb,
2908 ad_bugs ab
2909 WHERE afv.file_id = p_file_id
2910 AND aprba.file_id(+) = afv.file_id
2911 AND aprba.patch_file_version_id(+) = afv.file_version_id
2912 AND aprb.patch_run_bug_id(+) = aprba.patch_run_bug_id
2913 AND aprb.success_flag(+) = 'Y'
2914 AND ab.bug_id(+) = aprb.bug_id
2915 ORDER BY afv.creation_date desc;
2916 l_out varchar2(2000);
2917 BEGIN
2918 blank_line;
2919 FOR patch_hist_rec IN patch_hist_cur(p_file_id)
2920 LOOP
2921 IF patch_hist_cur%rowcount = 1 THEN
2922 l_out := ' '||
2923 fill('Patch Date', 12)||
2924 fill('Version', 20)||
2925 fill('Bug Number', 15);
2926 add(l_out);
2927 l_out := ' '||
2928 fill('-----------', 12)||
2929 fill('------------------', 20)||
2930 fill('--------------', 15);
2931 add(l_out);
2932 END IF;
2933 l_out := ' '||
2934 fill(patch_hist_rec.creation_date, 12)||
2935 fill(patch_hist_rec.version, 20)||
2936 fill(patch_hist_rec.bug_number, 15);
2937 add(l_out);
2938
2939 EXIT WHEN patch_hist_cur%rowcount = 5;
2940
2941 END LOOP;
2942 END dump_patch_history;
2943
2944 PROCEDURE dump_file_version(
2945 p_file_name IN varchar2,
2946 p_subdir IN varchar2 default 'patch/115/sql',
2947 p_prod_code IN varchar2 default 'CSI')
2948 IS
2949 l_file_id number;
2950 l_latest_version varchar2(80);
2951 l_out varchar2(2000);
2952
2953 CURSOR latest_version_cur(p_file_id IN number) IS
2954 SELECT version
2955 FROM ad_file_versions
2956 WHERE file_id = p_file_id
2957 ORDER BY file_version_id desc;
2958
2959 BEGIN
2960
2961 SELECT file_id
2962 INTO l_file_id
2963 FROM ad_files
2964 WHERE filename = p_file_name
2965 AND subdir = p_subdir
2966 AND app_short_name = p_prod_code;
2967
2968 OPEN latest_version_cur(l_file_id);
2969 FETCH latest_version_cur INTO l_latest_version;
2970 CLOSE latest_version_cur;
2971
2972 blank_line;
2973 l_out := 'File Name : '||p_file_name||' Version : '||l_latest_version;
2974 add(l_out);
2975
2976 IF l_latest_version is not null THEN
2977 dump_patch_history(
2978 p_file_id => l_file_id);
2979 END IF;
2980
2981 EXCEPTION
2982 WHEN no_data_found THEN
2983 add('Invalid File : '||p_file_name);
2984 END dump_file_version;
2985
2986 PROCEDURE dump_file_versions(
2987 p_source_files IN source_files)
2988 IS
2989 BEGIN
2990 IF p_source_files.count > 0 THEN
2991 FOR l_ind IN p_source_files.FIRST .. p_source_files.LAST
2992 LOOP
2993 dump_file_version(p_source_files(l_ind).file_name);
2994 END LOOP;
2995 END IF;
2996 END dump_file_versions;
2997
2998 PROCEDURE diagnose(
2999 errbuf OUT nocopy varchar2,
3000 retcode OUT nocopy number,
3001 p_entity IN varchar2,
3002 p_parameter1 IN varchar2,
3003 p_parameter2 IN varchar2)
3004 IS
3005 l_order_number number;
3006
3007 l_job_name varchar2(30);
3008 l_organization_id number;
3009
3010 l_mtl_txn_id number;
3011 l_serial_number varchar2(80);
3012 l_inventory_item_id number;
3013
3014 BEGIN
3015
3016 IF p_entity = 'ORDER' THEN
3017 l_order_number := to_number(p_parameter1);
3018 order_status(
3019 p_order_number => l_order_number);
3020 END IF;
3021
3022 IF p_entity = 'SERIAL' THEN
3023 l_serial_number := p_parameter1;
3024 l_inventory_item_id := to_number(p_parameter2);
3025 serial_status(
3026 p_serial_number => l_serial_number,
3027 p_item_id => l_inventory_item_id);
3028 END IF;
3029
3030 IF p_entity = 'JOB' THEN
3031 l_job_name := p_parameter1;
3032 l_organization_id := to_number(p_parameter2);
3033 job_status(
3034 p_job_name => l_job_name,
3035 p_organization_id => l_organization_id);
3036 END IF;
3037
3038 IF p_entity = 'TRANSACTION' THEN
3039 l_mtl_txn_id := p_parameter1;
3040 txn_status(
3041 p_mtl_txn_id => l_mtl_txn_id);
3042 END IF;
3043
3044 EXCEPTION
3045 WHEN others THEN
3046 add(sqlerrm);
3047 END diagnose;
3048
3049 END csi_debug_pkg;