1 PACKAGE BODY CSI_UTILITY_GRP AS
2 /* $Header: csigutlb.pls 120.13 2007/02/13 22:22:09 jpwilson ship $ */
3
4 PROCEDURE debug_con_log(p_message IN varchar2) IS
5 BEGIN
6 FND_FILE.PUT_LINE (FND_FILE.LOG, p_message );
7 END debug_con_log;
8
9 -- g_pkg_name VARCHAR2(30) := 'CSI_UTILITY_GRP';
10
11
12 PROCEDURE debug(p_message IN varchar2) IS
13
14 BEGIN
15 csi_t_gen_utility_pvt.add(p_message);
16 EXCEPTION
17 WHEN others THEN
18 null;
19 END debug;
20
21 -- This Function can be used to check if Oracle Installed Base
22 -- Product is Installed and Active at an Implementation. This
23 -- would check for a freeze_flag in Install Parameters.
24 FUNCTION IB_ACTIVE RETURN BOOLEAN IS
25 l_freeze_flag VARCHAR2(1) := 'N';
26 BEGIN
27 BEGIN
28 SELECT nvl(freeze_flag, 'N')
29 INTO l_freeze_flag
30 FROM csi_install_parameters
31 WHERE rownum = 1;
32 IF l_freeze_flag = 'Y' THEN
33 return TRUE;
34 ELSE
35 return FALSE;
36 END IF;
37 EXCEPTION
38 WHEN others THEN
39 return FALSE;
40 END;
41 END IB_ACTIVE;
42
43 --
44 -- This Function can be used to check if Oracle Installed Base
45 -- Product is Installed and Active at an Implementation. This
46 -- would check for a freeze_flag in Install Parameters.
47 -- This function returns a VARCHAR2 in the form 'Y' or 'N'
48 -- and can be used in a SQL statement in the predicate.
49 --
50 FUNCTION IB_ACTIVE_FLAG RETURN VARCHAR2 IS
51 l_freeze_flag VARCHAR2(1) := 'N';
52 BEGIN
53 BEGIN
54 SELECT nvl(freeze_flag, 'N')
55 INTO l_freeze_flag
56 FROM csi_install_parameters
57 WHERE rownum = 1;
58 IF l_freeze_flag = 'Y' THEN
59 return 'Y';
60 ELSE
61 return 'N';
62 END IF;
63 EXCEPTION
64 WHEN others THEN
65 return 'N';
66 END;
67 END IB_ACTIVE_FLAG;
68
69 --
70 -- This function returns the version of the Installed Base
71 -- This would be 1150 when it is on pre 1156
72 --
73 FUNCTION IB_VERSION RETURN NUMBER IS
74 BEGIN
75 If IB_ACTIVE Then
76 RETURN 1156;
77 Else
78 RETURN 1150;
79 End If;
80 Exception
81 When Others Then
82 Return 1150;
83 END IB_VERSION;
84
85 --
86 -- This procedure check if the installed base is active, If not active
87 -- populates the error message in the message queue and raises the
88 -- fnd_api.g_exc_error exception
89 --
90
91 PROCEDURE check_ib_active
92 IS
93 BEGIN
94 -- srramakr modified to look at csi_gen_utility_pvt since ib_active in current package
95 -- has Pragma restriction. (Cursor optimization)
96 IF NOT csi_gen_utility_pvt.IB_ACTIVE THEN
97 FND_MESSAGE.Set_Name('CSI', 'CSI_IB_NOT_ACTIVE');
98 FND_MSG_PUB.Add;
99 RAISE FND_API.G_Exc_Error;
100 END IF;
101 EXCEPTION
102 WHEN fnd_api.g_exc_error THEN
103
104 RAISE fnd_api.g_exc_error;
105
106 WHEN others THEN
107 FND_MESSAGE.Set_Name('CSI', 'CSI_UNEXP_SQL_ERROR');
108 FND_MESSAGE.Set_Token('API_NAME', 'Check_IB_Active');
109 FND_MESSAGE.Set_Token('SQL_ERROR', sqlerrm);
110 FND_MSG_PUB.Add;
111
112 RAISE fnd_api.g_exc_error;
113 END check_ib_active;
114
115
116 --
117 --
118 --
119 PROCEDURE get_config_key_for_om_line(
120 p_line_id IN number,
121 x_config_session_key OUT NOCOPY config_session_key,
122 x_return_status OUT NOCOPY varchar2,
123 x_return_message OUT NOCOPY varchar2)
124 IS
125 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
126 l_session_key config_session_key;
127
128 BEGIN
129 x_return_status := fnd_api.g_ret_sts_success;
130
131 BEGIN
132 SELECT config_header_id,
133 config_rev_nbr,
134 configuration_id
135 INTO l_session_key.session_hdr_id,
136 l_session_key.session_rev_num,
137 l_session_key.session_item_id
138 FROM oe_order_lines_all
139 WHERE line_id = p_line_id;
140
141 IF csi_interface_pkg.check_macd_processing (
142 p_config_session_key => l_session_key,
143 x_return_status => l_return_status)
144 THEN
145 x_config_session_key := l_session_key;
146 END IF;
147
148 EXCEPTION
149 WHEN no_data_found THEN
150 -- stack error message
151 RAISE fnd_api.g_exc_error;
152 END;
153
154 EXCEPTION
155 WHEN fnd_api.g_exc_error THEN
156 x_return_status := fnd_api.g_ret_sts_error;
157 END get_config_key_for_om_line;
158
159 --
160 --
161 --
162 PROCEDURE get_config_inst_valid_status(
163 p_instance_key IN config_instance_key,
164 x_config_valid_status OUT NOCOPY varchar2,
165 x_return_status OUT NOCOPY varchar2,
166 x_return_message OUT NOCOPY varchar2)
167 IS
168 BEGIN
169
170 x_return_status := fnd_api.g_ret_sts_success;
171
172 BEGIN
173
174 SELECT config_valid_status
175 INTO x_config_valid_status
176 FROM csi_t_transaction_lines ctl,
177 csi_t_txn_line_details ctd
178 WHERE ctd.config_inst_hdr_id = p_instance_key.inst_hdr_id
179 AND ctd.config_inst_rev_num = p_instance_key.inst_rev_num
180 AND ctd.config_inst_item_id = p_instance_key.inst_item_id
181 AND ctl.transaction_line_id = ctd.transaction_line_id;
182 EXCEPTION
183 WHEN no_data_found THEN
184 RAISE fnd_api.g_exc_error;
185 END;
186
187 --x_config_valid_status := 'VALID';
188
189 EXCEPTION
190 WHEN fnd_api.g_exc_error THEN
191 x_return_status := fnd_api.g_ret_sts_error;
192 END get_config_inst_valid_status;
193
194 FUNCTION is_network_component(
195 p_order_line_id IN number,
196 x_return_status OUT NOCOPY varchar2)
197 RETURN boolean
198 IS
199 l_session_key config_session_key;
200 l_return_status varchar2(1);
201 l_macd_flag boolean := FALSE;
202 BEGIN
203
204 x_return_status := fnd_api.g_ret_sts_success;
205
206 SELECT config_header_id,
207 config_rev_nbr,
208 configuration_id
209 INTO l_session_key.session_hdr_id,
210 l_session_key.session_rev_num,
211 l_session_key.session_item_id
212 FROM oe_order_lines_all
213 WHERE line_id = p_order_line_id;
214
215 IF csi_interface_pkg.check_macd_processing(
216 p_config_session_key => l_session_key,
217 x_return_status => l_return_status)
218 THEN
219 l_macd_flag := TRUE;
220 END IF;
221
222 return l_macd_flag;
223 EXCEPTION
224 WHEN fnd_api.g_exc_error THEN
225 x_return_status := fnd_api.g_ret_sts_error;
226 END is_network_component;
227
228 PROCEDURE vld_item_ctrl_changes (
229 p_api_version IN NUMBER
230 ,p_commit IN VARCHAR2 := fnd_api.g_false
231 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
232 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
233 ,p_inventory_item_id IN NUMBER
234 ,p_organization_id IN NUMBER
235 ,p_item_attr_name IN VARCHAR2
236 ,p_new_item_attr_value IN VARCHAR2
237 ,p_old_item_attr_value IN VARCHAR2
238 ,x_return_status OUT NOCOPY VARCHAR2
239 ,x_msg_count OUT NOCOPY NUMBER
240 ,x_msg_data OUT NOCOPY VARCHAR2) IS
241
242 l_master_org_id NUMBER;
243 sfm_event_error EXCEPTION;
244 csi_exist_txn_error EXCEPTION;
245 mtl_iface_error EXCEPTION;
246 mtl_temp_error EXCEPTION;
247 active_ib_inst_error EXCEPTION;
248 csi_exist_sfm_error EXCEPTION;
249
250 BEGIN
251
252 BEGIN
253 SELECT master_organization_id
254 INTO l_master_org_id
255 FROM mtl_parameters
256 WHERE organization_id = p_organization_id;
257
258 EXCEPTION
259 WHEN OTHERS then
260 l_master_org_id := NULL;
261 END;
262
263 IF csi_item_instance_vld_pvt.is_trackable(p_inv_item_id => p_inventory_item_id,
264 p_org_id => l_master_org_id) THEN
265 -- Check for READY or ERROR SFM Messages
266 IF csi_utility_grp.vld_exist_sfm_events(p_inventory_item_id) THEN
267 raise sfm_event_error;
268 END IF;
269
270 -- Chcck for ERROR or PENDING CSI Errors
271 IF csi_utility_grp.vld_exist_txn_errors(p_inventory_item_id) THEN
272 raise csi_exist_txn_error;
273 END IF;
274
275 -- Check MTL Transaction Interface Table
276 IF csi_utility_grp.vld_exist_mtl_iface_recs(p_inventory_item_id,
277 p_organization_id) THEN
278 raise mtl_iface_error;
279 END IF;
280
281 -- Check MTL Transaction Temp Table
282 IF csi_utility_grp.vld_exist_mtl_temp_recs(p_inventory_item_id,
283 p_organization_id) THEN
284 raise mtl_temp_error;
285 END IF;
286
287 -- Check for Active IB Instances
288 IF csi_utility_grp.vld_active_ib_inst(p_inventory_item_id) THEN
289 raise active_ib_inst_error;
290 END IF;
291 END IF;
292
293 x_msg_data := NULL;
294 x_return_status := fnd_api.g_ret_sts_success;
295 x_msg_count := NULL;
296
297 EXCEPTION
298 WHEN sfm_event_error THEN
299 fnd_message.set_name('CSI','CSI_IM_EXIST_SFM_ERROR');
300 x_return_status := fnd_api.g_ret_sts_error;
301 x_msg_count := 1;
302 x_msg_data := fnd_message.get;
303
304 WHEN csi_exist_txn_error THEN
305 fnd_message.set_name('CSI','CSI_IM_EXIST_ERROR');
306 x_return_status := fnd_api.g_ret_sts_error;
307 x_msg_count := 1;
308 x_msg_data := fnd_message.get;
309
310 WHEN mtl_iface_error THEN
311 fnd_message.set_name('CSI','CSI_IM_MTL_IFACE_ERROR');
312 x_return_status := fnd_api.g_ret_sts_error;
313 x_msg_count := 1;
314 x_msg_data := fnd_message.get;
315
316 WHEN mtl_temp_error THEN
317 fnd_message.set_name('CSI','CSI_IM_MTL_IFACE_TEMP');
318 x_return_status := fnd_api.g_ret_sts_error;
319 x_msg_count := 1;
320 x_msg_data := fnd_message.get;
321
322 WHEN active_ib_inst_error THEN
323 fnd_message.set_name('CSI','CSI_IM_ACTIVE_IB_INST');
324 x_return_status := fnd_api.g_ret_sts_error;
325 x_msg_count := 1;
326 x_msg_data := fnd_message.get;
327
328 END vld_item_ctrl_changes;
329
330
331 FUNCTION vld_exist_txn_errors (p_item_id IN NUMBER) RETURN BOOLEAN IS
332
333 l_record_found NUMBER := NULL;
334
335 BEGIN
336 select 1
337 into l_record_found
338 from csi_txn_errors cte,
339 mtl_material_transactions mmt
340 where mmt.transaction_id = cte.inv_material_transaction_id
341 and mmt.inventory_item_id = p_item_id
342 and cte.processed_flag in ('R','E','W')
343 and rownum < 2;
344
345 RETURN(TRUE);
346
347 EXCEPTION
348 WHEN no_data_found THEN
349 RETURN(FALSE);
350
351 END vld_exist_txn_errors;
352
353 FUNCTION vld_exist_mtl_iface_recs (p_item_id IN NUMBER,
354 p_org_id IN NUMBER) RETURN BOOLEAN IS
355
356 l_record_found NUMBER := NULL;
357
358 BEGIN
359 select 1
360 into l_record_found
361 from mtl_transactions_interface mti
362 where mti.inventory_item_id = p_item_id
363 and mti.organization_id = p_org_id
364 and rownum < 2;
365
366 RETURN(TRUE);
367
368 EXCEPTION
369 WHEN no_data_found THEN
370 RETURN(FALSE);
371
372 END vld_exist_mtl_iface_recs;
373
374 FUNCTION vld_exist_mtl_temp_recs (p_item_id IN NUMBER,
375 p_org_id IN NUMBER) RETURN BOOLEAN IS
376
377 l_record_found NUMBER := NULL;
378
379 BEGIN
380 select 1
381 into l_record_found
382 from mtl_material_transactions_temp mmtt
383 where mmtt.inventory_item_id = p_item_id
384 and mmtt.organization_id = p_org_id
385 and rownum < 2;
386
387 RETURN(TRUE);
388
389 EXCEPTION
390 WHEN no_data_found THEN
391 RETURN(FALSE);
392
393 END vld_exist_mtl_temp_recs;
394
395 FUNCTION vld_exist_sfm_events (p_item_id IN NUMBER) RETURN BOOLEAN IS
396
397 l_freeze_date DATE;
398
399 CURSOR msg_cur(pc_freeze_date IN DATE) is
400 SELECT msg_id,
401 msg_code,
402 msg_status,
403 body_text,
404 creation_date,
405 description
406 FROM xnp_msgs
407 WHERE (msg_code like 'CSI%' OR msg_code like 'CSE%')
408 AND msg_status in ('READY','FAILED')
409 AND msg_creation_date > pc_freeze_date
410 -- AND nvl(msg_status, 'READY') <> 'PROCESSED' -- commented for Bug 3987286
411 AND recipient_name is null;
412
413 l_amount integer;
414 l_msg_text varchar2(32767);
415 l_source_id NUMBER;
416 l_source_id1 NUMBER;
417 l_item_id number;
418
419 BEGIN
420
421 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
422 csi_gen_utility_pvt.populate_install_param_rec;
423 END IF;
424
425 IF csi_datastructures_pub.g_install_param_rec.freeze_date is NULL then
426 SELECT freeze_date
427 INTO l_freeze_date
428 FROM csi_install_parameters
429 WHERE rownum = 1;
430 ELSE
431 l_freeze_date := csi_datastructures_pub.g_install_param_rec.freeze_date;
432 END IF;
433
434 FOR msg_rec in msg_cur (l_freeze_date)
435 LOOP
436 l_amount := null;
437 l_amount := dbms_lob.getlength(msg_rec.body_text);
438 l_msg_text := null;
439 l_item_id := null;
440
441 dbms_lob.read(
442 lob_loc => msg_rec.body_text,
443 amount => l_amount,
444 offset => 1,
445 buffer => l_msg_text );
446
447 l_source_id := null;
448
449 IF msg_rec.msg_code in ('CSISOFUL', 'CSIRMAFL') THEN
450
451 xnp_xml_utils.decode(l_msg_text, 'ORDER_LINE_ID', l_source_id);
452
453 BEGIN
454 select inventory_item_id
455 into l_item_id
456 from oe_order_lines_all
457 where line_id = l_source_id;
458
459 IF p_item_id = l_item_id THEN
460 RETURN(TRUE);
461 END IF;
462
463 EXCEPTION
464 WHEN no_data_found THEN
465 -- Record could have been purged so do nothing
466 null;
467 END;
468
469 ELSIF msg_rec.msg_code in ('CSICYCNT',
470 'CSIINTDS',
471 'CSIINTSR',
472 'CSIINTSS',
473 'CSIISUHZ',
474 'CSIISUPT',
475 'CSIMSIHZ',
476 'CSIMSIPT',
477 'CSIMSISU',
478 'CSIMSRCV',
479 'CSIMSRHZ',
480 'CSIMSRPT',
481 'CSIOKSHP',
482 'CSIORGDS',
483 'CSIORGTR',
484 'CSIORGTS',
485 'CSIPHYIN',
486 'CSIPOINV',
487 'CSIRMARC',
488 'CSISOSHP',
489 'CSISUBTR',
490 'CSIWIPAC',
491 'CSIWIPAR',
492 'CSIWIPCI',
493 'CSIWIPCR',
494 'CSIWIPNI',
495 'CSIWIPNR',
496 'CSILOSHP',
497 'CSIEAMRR',
498 'CSIEAMWC',
499 'CSIWIPBR',
500 'CSIWIPBC') THEN
501
502 xnp_xml_utils.decode(l_msg_text, 'MTL_TRANSACTION_ID', l_source_id);
503 xnp_xml_utils.decode(l_msg_text, 'INVENTORY_ITEM_ID', l_source_id1);
504
505 IF l_source_id1 IS NULL THEN
506 BEGIN
507 select inventory_item_id
508 into l_item_id
509 from mtl_material_transactions
510 where transaction_id = l_source_id;
511
512 IF p_item_id = l_item_id THEN
513 RETURN(TRUE);
514 END IF;
515
516 EXCEPTION
517 WHEN no_data_found THEN
518 -- Record could have been purged so do nothing
519 null;
520 END;
521
522 ELSE -- Inventory Item ID is in XML Message
523
524 IF p_item_id = l_source_id1 THEN
525 RETURN(TRUE);
526 END IF;
527
528 END IF;
529
530 ELSIF msg_rec.msg_code = 'CSEPORCV' THEN
531 xnp_xml_utils.decode(l_msg_text, 'RCV_TRANSACTION_ID', l_source_id);
532
533 BEGIN
534 select pla.Item_Id
535 into l_item_id
536 from rcv_transactions rt,
537 po_lines_all pla
538 where rt.transaction_id = l_source_id
539 and rt.po_Line_Id = pla.po_Line_Id;
540
541 IF p_item_id = l_item_id THEN
542 RETURN(TRUE);
543 END IF;
544
545 EXCEPTION
546 WHEN others THEN
547 -- Record could have been purged so do nothing
548 null;
549 END;
550
551 ELSIF msg_rec.msg_code in ('CSEOUTSV',
552 'CSEITUNI',
553 'CSEITSVS',
554 'CSEITMVS',
555 'CSEITINS',
556 'CSEINSVS') THEN
557 -- WFM Transactions for CSE
558 xnp_xml_utils.decode(l_msg_text, 'ITEM_ID', l_source_id);
559
560 IF p_item_id = l_source_id THEN
561 RETURN(TRUE);
562 END IF;
563
564 END IF;
565
566 END LOOP;
567
568 RETURN(FALSE);
569
570 END vld_exist_sfm_events;
571
572 FUNCTION vld_active_ib_inst (p_item_id IN NUMBER) RETURN BOOLEAN IS
573
574 l_record_found NUMBER := NULL;
575
576 BEGIN
577 select 1
578 into l_record_found
579 from csi_item_instances cii
580 where cii.inventory_item_id = p_item_id
581 and cii.active_end_date IS NULL
582 and rownum < 2;
583
584 RETURN(TRUE);
585
586 EXCEPTION
587 WHEN no_data_found THEN
588 RETURN(FALSE);
589
590 END vld_active_ib_inst;
591
592
593 /********** Start New Functions for Inventory MACD validations **********/
594
595 -- check_inv_serial_cz_keys will call the other 3 functions internally
596 -- and will return either Y or N
597 --
598 -- N = Serial Number is NOT in a MACD Configuration
599 --
600 -- Y = Serial number IS in a MACD Configuration
601 --
602 --
603
604 FUNCTION check_inv_serial_cz_keys (p_inventory_item_id IN NUMBER,
605 p_organization_id IN NUMBER,
606 p_serial_number IN VARCHAR2) RETURN VARCHAR2 IS
607
608 inv_inst_cz_keys EXCEPTION;
609
610 BEGIN
611 -- Check to see if an instance exists and has the Config Keys on it.
612
613 IF csi_utility_grp.check_inv_inst_cz_keys(p_inventory_item_id,
614 p_organization_id,
615 p_serial_number) THEN
616 raise inv_inst_cz_keys;
617 END IF;
618
619 -- Check to see if an instance exists that has an error and has the Config Keys on it.
620
621 IF csi_utility_grp.check_inv_error_cz_keys(p_inventory_item_id,
622 p_organization_id,
623 p_serial_number) THEN
624 raise inv_inst_cz_keys;
625 END IF;
626
627 -- Check to see if an instance exists in a Pending or Failed in the
628 -- Status in the SFM Queue and if it has Config Keys on it.
629
630 IF csi_utility_grp.check_inv_sfm_cz_keys(p_inventory_item_id,
631 p_organization_id,
632 p_serial_number) THEN
633 raise inv_inst_cz_keys;
634 END IF;
635
636 -- All functions did not return anything so there are no items that qualify to be
637 -- In a MACD Configuration. So return 'N'
638
639 Return 'N';
640
641 EXCEPTION
642
643 WHEN inv_inst_cz_keys THEN
644 RETURN 'Y';
645
646 WHEN others THEN
647 RETURN 'Y';
648
649 END; -- End of check_inv_serial_cz_keys
650
651 FUNCTION check_inv_inst_cz_keys (p_inventory_item_id IN NUMBER,
652 p_organization_id IN NUMBER,
653 p_serial_number IN VARCHAR2) RETURN BOOLEAN IS
654
655 l_record_found NUMBER := NULL;
656
657 BEGIN
658 SELECT 1
659 INTO l_record_found
660 FROM csi_item_instances
661 WHERE inventory_item_id = p_inventory_item_id
662 AND serial_number = p_serial_number
663 AND config_inst_hdr_id is NOT NULL
664 AND config_inst_rev_num is NOT NULL
665 AND config_inst_item_id is NOT NULL;
666
667 RETURN (TRUE);
668
669 EXCEPTION
670 WHEN no_data_found THEN
671 RETURN(FALSE);
672
673 WHEN others THEN
674 RETURN(TRUE);
675 END; -- check_inv_inst_cz_keys
676
677
678 FUNCTION check_inv_error_cz_keys (p_inventory_item_id IN NUMBER,
679 p_organization_id IN NUMBER,
680 p_serial_number IN VARCHAR2) RETURN BOOLEAN IS
681
682
683 l_config_keys csi_utility_grp.config_session_key;
684 l_return_status VARCHAR2(1);
685
686 BEGIN
687
688 SELECT ool.config_header_id config_session_hdr_id,
689 ool.config_rev_nbr config_session_rev_num,
690 ool.configuration_id config_session_item_id
691 INTO l_config_keys.session_hdr_id,
692 l_config_keys.session_rev_num,
693 l_config_keys.session_item_id
694 FROM csi_txn_errors cte,
695 mtl_material_transactions mmt,
696 mtl_unit_transactions mut,
697 oe_order_lines_all ool
698 WHERE mmt.transaction_id = mut.transaction_id
699 AND mmt.transaction_action_id = 1
700 AND mmt.transaction_source_type_id = 2
701 AND mut.transaction_id = cte.inv_material_transaction_id
702 AND mut.inventory_item_id = p_inventory_item_id
703 AND mut.serial_number = p_serial_number
704 AND cte.processed_flag in ('R','E','W')
705 AND cte.transaction_type_id = 51
706 AND mmt.trx_source_line_id = ool.line_id;
707
708 IF csi_interface_pkg.check_MACD_processing(l_config_keys,
709 l_return_status) THEN
710
711 -- There are records that are errored but not processed to Install Base Yet
712 RETURN (TRUE);
713 ELSE
714 -- There are no records that are errored just exit.
715 RETURN (FALSE);
716 END IF;
717
718 EXCEPTION
719 WHEN no_data_found THEN
720 RETURN(FALSE);
721
722 WHEN others THEN
723 RETURN(TRUE);
724
725 END; -- check_inv_error_cz_keys
726
727
728 FUNCTION check_inv_sfm_cz_keys (p_inventory_item_id IN NUMBER,
729 p_organization_id IN NUMBER,
730 p_serial_number IN VARCHAR2) RETURN BOOLEAN IS
731
732
733 l_freeze_date DATE;
734 l_config_keys csi_utility_grp.config_session_key;
735 l_return_status VARCHAR2(1);
736
737 CURSOR msg_cur(pc_freeze_date IN DATE) is
738 SELECT msg_id,
739 msg_code,
740 msg_status,
741 body_text,
742 creation_date,
743 description
744 FROM xnp_msgs
745 WHERE msg_code = 'CSISOSHP'
746 AND msg_status in ('READY','FAILED')
747 AND msg_creation_date > pc_freeze_date
748 AND recipient_name is null;
749
750 CURSOR c_config_keys (pc_item_id in NUMBER,
751 pc_serial_number in VARCHAR2,
752 pc_transaction_id in NUMBER) IS
753 SELECT ool.config_header_id config_session_hdr_id,
754 ool.config_rev_nbr config_session_rev_num,
755 ool.configuration_id config_session_item_id
756 FROM mtl_material_transactions mmt,
757 mtl_unit_transactions mut,
758 oe_order_lines_all ool
759 WHERE mmt.transaction_id = mut.transaction_id
760 AND mmt.transaction_id = pc_transaction_id
761 AND mmt.transaction_action_id = 1
762 AND mmt.transaction_source_type_id = 2
763 AND mut.inventory_item_id = pc_item_id
764 AND mut.serial_number = pc_serial_number
765 AND mmt.trx_source_line_id = ool.line_id;
766
767 r_config_keys c_config_keys%rowtype;
768
769 l_amount integer;
770 l_msg_text varchar2(32767);
771 l_source_id varchar2(200);
772 l_item_id number;
773
774 BEGIN
775
776 SELECT freeze_date
777 INTO l_freeze_date
778 FROM csi_install_parameters
779 WHERE rownum = 1;
780
781 FOR msg_rec in msg_cur(l_freeze_date)
782 LOOP
783 l_amount := null;
784 l_amount := dbms_lob.getlength(msg_rec.body_text);
785 l_msg_text := null;
786 l_item_id := null;
787
788 dbms_lob.read(
789 lob_loc => msg_rec.body_text,
790 amount => l_amount,
791 offset => 1,
792 buffer => l_msg_text );
793
794 l_source_id := null;
795
796 xnp_xml_utils.decode(l_msg_text, 'MTL_TRANSACTION_ID', l_source_id);
797
798 FOR r_config_keys in c_config_keys (p_inventory_item_id,
799 p_serial_number,
800 l_source_id) LOOP
801
802 l_config_keys.session_hdr_id := r_config_keys.config_session_hdr_id;
803 l_config_keys.session_rev_num := r_config_keys.config_session_rev_num;
804 l_config_keys.session_item_id := r_config_keys.config_session_item_id;
805
806 -- Call function again to see if this is in a MACD config or not
807
808 IF csi_interface_pkg.check_MACD_processing(l_config_keys,
809 l_return_status) THEN
810
811 -- There are records in the SFM Queue but not processed to Install Base Yet
812 RETURN (TRUE);
813 END IF;
814
815 END LOOP; -- c_config_keys loop
816
817 END LOOP; -- msg_rec loop
818
819 RETURN (FALSE);
820
821 EXCEPTION
822
823 WHEN others THEN
824 RETURN(TRUE);
825
826 END; -- check_inv_sfm_cz_keys
827
828 /********** End New Functions for Inventory MACD validations **********/
829
830 PROCEDURE get_impacted_item_instances( p_api_version IN NUMBER,
831 p_commit IN VARCHAR2 := fnd_api.g_false,
832 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
833 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
834 x_txn_inst_tbl OUT NOCOPY TXN_INST_TBL,
835 p_txn_oks_rec IN TXN_OKS_REC,
836 x_return_status OUT NOCOPY VARCHAR2,
837 x_msg_count OUT NOCOPY NUMBER,
838 x_msg_data OUT NOCOPY VARCHAR2) IS
839
840 l_api_name CONSTANT VARCHAR2(30) := 'GET_IMPACTED_ITEM_INSTANCES';
841 l_party_found NUMBER;
842 j NUMBER := 1;
843 l_txn_inst_tbl csi_utility_grp.TXN_INST_TBL;
844 l_txn_oks_rec csi_utility_grp.txn_oks_rec;
845 l_return_status VARCHAR2(1);
846 l_error_message VARCHAR2(2000);
847 l_sql_error VARCHAR2(2000);
848 l_msg_data VARCHAR2(2000);
849 l_msg_count NUMBER;
850
851 -- FTS on this cursor
852 CURSOR dummy_csr(pc_batch_id IN NUMBER) IS
853 SELECT cil.active_end_date active_end_date
854 ,cil.installation_date installation_date
855 ,cil.txn_line_detail_id txn_line_detail_id
856 FROM csi_t_txn_line_details cil,
857 csi_mass_edit_entries_b cmee
858 WHERE cmee.entry_id = pc_batch_id
859 AND cmee.txn_line_id = cil.transaction_line_id
860 AND cil.instance_id IS NULL;
861
862 dummy_rec dummy_csr%rowtype;
863
864 BEGIN
865
866 debug('Start of get_impacted_instances...');
867
868 x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870 IF p_txn_oks_rec.batch_id IS NULL THEN -- Single Instance Usability
871
872 l_txn_oks_rec := p_txn_oks_rec;
873
874 -- Now build the txn_inst_tbl to be passed out with the impacted instances
875 debug(' Single Instance Usability .. Calling get_instances');
876
877 csi_utility_grp.get_instances (p_txn_oks_rec,
878 l_txn_inst_tbl,
879 l_return_status,
880 l_msg_count,
881 l_msg_data);
882 x_txn_inst_tbl := l_txn_inst_tbl;
883
884 ELSE -- Mass Update
885
886 l_txn_oks_rec := p_txn_oks_rec;
887 /****
888 FOR dummy_rec IN dummy_csr (p_txn_oks_rec.batch_id) LOOP
889
890 IF dummy_rec.active_end_date IS NOT NULL THEN
891 l_txn_oks_rec.transaction_type(j) := 'TRM';
892 j := j + 1;
893 END IF;
894
895 IF dummy_rec.installation_date IS NOT NULL THEN
896 l_txn_oks_rec.transaction_type(j) := 'IDC';
897 j := j + 1;
898 END IF;
899
900 BEGIN
901 -- SELECT 1
902 -- INTO l_party_found
903 -- FROM csi_t_party_details
904 -- WHERE txn_line_detail_id = dummy_rec.txn_line_detail_id
905 -- AND relationship_type_code = 'OWNER'
906 -- AND party_source_table = 'HZ_PARTIES';
907 l_party_found := 1;
908 IF l_party_found IS NOT NULL THEN
909 l_txn_oks_rec.transaction_type(j) := 'TRF';
910 j := j + 1;
911 END IF;
912
913 EXCEPTION
914 WHEN no_data_found THEN
915 NULL; -- No Party Change Do Nothing
916 END;
917
918 END LOOP; -- dummy_csr
919 ****/
920 -- Now build the txn_inst_tbl to be passed out with the impacted instances
921
922 debug(' Mass Update Batch ('||l_txn_oks_rec.batch_id||') .. Calling get_instances');
923
924 csi_utility_grp.get_instances (l_txn_oks_rec,
925 l_txn_inst_tbl,
926 l_return_status,
927 l_msg_count,
928 l_msg_data);
929
930 x_txn_inst_tbl := l_txn_inst_tbl;
931
932 END IF;
933
934 EXCEPTION
935 WHEN fnd_api.g_exc_error THEN
936 x_return_status := FND_API.G_RET_STS_ERROR;
937 x_msg_count := l_msg_count;
938 x_msg_data := l_msg_data;
939
940 WHEN others THEN
941 l_sql_error := SQLERRM;
942 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
943 fnd_message.set_token('API_NAME',l_api_name);
944 fnd_message.set_token('SQL_ERROR',l_sql_error);
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 x_msg_count := 1;
947 x_msg_data := fnd_message.get;
948
949 END get_impacted_item_instances;
950
951
952 PROCEDURE get_instances (p_txn_oks_rec IN TXN_OKS_REC,
953 x_txn_inst_tbl OUT NOCOPY TXN_INST_TBL,
954 x_return_status OUT NOCOPY VARCHAR2,
955 x_msg_count OUT NOCOPY NUMBER,
956 x_msg_data OUT NOCOPY VARCHAR2) IS
957
958
959 l_api_name CONSTANT VARCHAR2(30) := 'GET_INSTANCES';
960 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
961 l_rel_tbl csi_datastructures_pub.ii_relationship_tbl;
962 l_depth NUMBER;
963 l_active_relationship_only VARCHAR2(1);
964 l_active_instances_only VARCHAR2(1);
965 l_config_only VARCHAR2(1); -- if true will retrieve instances with config keys
966 l_time_stamp DATE;
967 l_get_dfs VARCHAR2(1) := FND_API.G_TRUE;
968 l_ii_relationship_level_tbl csi_ii_relationships_pvt.ii_relationship_level_tbl;
969 l_return_status VARCHAR2(1);
970 l_error_message VARCHAR2(2000);
971 l_sql_error VARCHAR2(2000);
972 l_msg_data VARCHAR2(2000);
973 l_msg_count NUMBER;
974 l_msg_index NUMBER;
975 l_txn_instances_tbl T_NUM;
976 l_txn_trf_instances_tbl T_NUM;
977 l_txn_idc_instances_tbl T_NUM;
978 l_dummy_owner_party NUMBER;
979 l_owner_party_id NUMBER;
980 l_child_owner_party_id NUMBER;
981 l_parent_owner_party_id NUMBER;
982 l_parent_install_date DATE;
983 l_child_install_date DATE;
984
985 trx1 NUMBER := 1;
986 trx2 NUMBER := 1;
987 trx10 NUMBER := 1;
988 trx20 NUMBER := 1;
989 dup NUMBER := 1;
990 rel NUMBER := 0;
991 rel1 NUMBER := 0;
992 pty1 NUMBER := 1;
993 pty2 NUMBER := 1;
994 id NUMBER := 1;
995 id1 NUMBER := 1;
996 dup_inst_found VARCHAR2(1) := 'N';
997 inst NUMBER := 0;
998 j NUMBER := 0;
999
1000 -- FTS on this cursor
1001 CURSOR inst_csr(pc_batch_id IN NUMBER) IS
1002 SELECT cil.instance_id instance_id
1003 FROM csi_t_txn_line_details cil,
1004 csi_mass_edit_entries_b cmee
1005 WHERE cmee.entry_id = pc_batch_id
1006 AND cmee.txn_line_id = cil.transaction_line_id
1007 AND cil.instance_id IS NOT NULL;
1008
1009 inst_rec inst_csr%rowtype;
1010
1011 CURSOR dummy_csr(pc_batch_id IN NUMBER) IS
1012 SELECT cil.active_end_date active_end_date
1013 ,cil.installation_date installation_date
1014 ,cil.txn_line_detail_id txn_line_detail_id
1015 FROM csi_t_txn_line_details cil,
1016 csi_mass_edit_entries_b cmee
1017 WHERE cmee.entry_id = pc_batch_id
1018 AND cmee.txn_line_id = cil.transaction_line_id
1019 AND cil.instance_id IS NULL;
1020
1021 dummy_rec dummy_csr%rowtype;
1022
1023 CURSOR install_date_csr (pc_instance_id IN NUMBER) IS
1024 SELECT install_date
1025 FROM csi_item_instances
1026 WHERE instance_id = pc_instance_id;
1027
1028 install_date_rec install_date_csr%rowtype;
1029
1030 CURSOR parent_child_party_csr (pc_instance_id IN NUMBER) IS
1031 SELECT owner_party_id
1032 FROM csi_item_instances
1033 WHERE instance_id = pc_instance_id;
1034
1035 parent_child_party_rec parent_child_party_csr%rowtype;
1036
1037 BEGIN
1038
1039 debug(' Start of get_instances ...');
1040
1041 -- If this is a Mass Update Transaction then get all the instances in the Batch
1042
1043 IF p_txn_oks_rec.batch_id IS NOT NULL THEN
1044
1045 debug(' Start of get_instances ... Mass Update Batch Processing');
1046
1047 OPEN dummy_csr(p_txn_oks_rec.batch_id);
1048 FETCH dummy_csr INTO dummy_rec;
1049 CLOSE dummy_csr;
1050
1051 FOR inst_rec IN inst_csr(p_txn_oks_rec.batch_id) LOOP
1052
1053 dup_inst_found := 'N';
1054
1055 IF l_txn_instances_tbl.count > 0 THEN
1056 FOR dup IN l_txn_instances_tbl.FIRST .. l_txn_instances_tbl.LAST LOOP
1057 IF l_txn_instances_tbl(dup) = inst_rec.instance_id THEN
1058 dup_inst_found := 'Y';
1059 exit;
1060 END IF;
1061 END LOOP; -- Check if Instance is already in out table
1062 END IF; -- l_txn_instances_tbl.count
1063
1064 IF dup_inst_found = 'N' THEN
1065 l_relationship_query_rec.object_id := inst_rec.instance_id;
1066 l_relationship_query_rec.relationship_type_code := 'COMPONENT-OF';
1067 csi_ii_relationships_pvt.Get_Children (l_relationship_query_rec ,
1068 l_rel_tbl ,
1069 NULL, --l_depth
1070 fnd_api.g_true, --l_active_relationship_only
1071 fnd_api.g_true, --l_active_instances_only
1072 fnd_api.g_false, --
1073 NULL,
1074 l_get_dfs ,
1075 l_ii_relationship_level_tbl ,
1076 x_return_status ,
1077 x_msg_count ,
1078 x_msg_data );
1079
1080 debug(' Does this instance have any children? ('||l_rel_tbl.count||')');
1081
1082 IF NOT l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1083 l_msg_index := 1;
1084 WHILE l_msg_count > 0 loop
1085 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1086 l_msg_index := l_msg_index + 1;
1087 l_msg_count := l_msg_count - 1;
1088 END LOOP;
1089 RAISE fnd_api.g_exc_error;
1090 END IF;
1091
1092
1093 IF l_rel_tbl.count = 0 THEN
1094 debug(' No children so add Instance to the table');
1095 -- its a parent just add to the table
1096 inst := l_txn_instances_tbl.count + 1;
1097 l_txn_instances_tbl(inst) := inst_rec.instance_id;
1098
1099 ELSE
1100 debug(' Children Exist so loop through the table and get the Owner Party of the Parent and Installation Date');
1101
1102 BEGIN
1103 SELECT owner_party_id,install_date
1104 INTO l_parent_owner_party_id,l_parent_install_date
1105 FROM csi_item_instances
1106 WHERE instance_id = inst_rec.instance_id;
1107
1108 debug(' Parent Owner Party ('||l_parent_owner_party_id||')');
1109 debug(' Parent Installation Date ('||l_parent_install_date||')');
1110
1111 EXCEPTION
1112 WHEN no_data_found THEN
1113 l_parent_owner_party_id := NULL;
1114 l_parent_install_date := NULL;
1115 END;
1116
1117 FOR rel IN l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
1118 -- Continue to build l_txn_instances_tbl
1119 inst := l_txn_instances_tbl.count + 1;
1120 l_txn_instances_tbl(inst) := l_rel_tbl(rel).subject_id;
1121 END LOOP; -- l_rel_tbl
1122
1123 -- Insert Parent ID
1124 inst := l_txn_instances_tbl.count + 1;
1125 l_txn_instances_tbl(inst) := inst_rec.instance_id;
1126
1127 -- Check to see if this is TRF if so compare the owner parties
1128 FOR trx_trf IN p_txn_oks_rec.transaction_type.FIRST .. p_txn_oks_rec.transaction_type.LAST LOOP
1129 IF p_txn_oks_rec.transaction_type(trx_trf) = 'TRF' THEN
1130 debug(' TRF Transaction Type');
1131
1132 l_txn_trf_instances_tbl := l_txn_instances_tbl;
1133
1134 FOR trf IN l_txn_trf_instances_tbl.FIRST .. l_txn_trf_instances_tbl.LAST LOOP
1135 SELECT owner_party_id
1136 INTO l_owner_party_id
1137 FROM csi_item_instances
1138 WHERE instance_id = l_txn_trf_instances_tbl(trf);
1139
1140 debug(' Owner Party: '||l_owner_party_id||' of Instance: '||l_txn_trf_instances_tbl(trf));
1141
1142 IF l_parent_owner_party_id <> l_owner_party_id THEN
1143 debug(' Owner Parties do not match so remove this instance: '||l_txn_trf_instances_tbl(trf));
1144 l_txn_trf_instances_tbl.delete(trf);
1145 END IF;
1146
1147 END LOOP; -- trf index
1148
1149 END IF; -- 'TRF' IF
1150 END LOOP; -- trx_trf index
1151 END IF; -- rel tbl
1152
1153 END IF; -- dup_inst_found
1154 END LOOP; -- inst_csr
1155
1156 -- Assign the table of instances to all of the Transaction Type Rows
1157 FOR trx10 IN p_txn_oks_rec.transaction_type.FIRST .. p_txn_oks_rec.transaction_type.LAST LOOP
1158
1159 IF p_txn_oks_rec.transaction_type(trx10) = 'TRF' THEN
1160
1161 IF l_txn_trf_instances_tbl.count = 0 THEN
1162 x_txn_inst_tbl(trx10).instance_tbl := l_txn_instances_tbl;
1163 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1164
1165 ELSE
1166 j := l_txn_trf_instances_tbl.count;
1167
1168 FOR i IN l_txn_instances_tbl.first .. l_txn_instances_tbl.last LOOP
1169 l_txn_trf_instances_tbl(j) := l_txn_instances_tbl(i);
1170 j := l_txn_trf_instances_tbl.count + 1;
1171 END LOOP;
1172
1173 x_txn_inst_tbl(trx10).instance_tbl := l_txn_trf_instances_tbl;
1174 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1175
1176 END IF;
1177
1178 ELSIF p_txn_oks_rec.transaction_type(trx10) = 'IDC' THEN
1179 debug(' IDC Transaction Type');
1180 l_txn_idc_instances_tbl := l_txn_instances_tbl;
1181
1182 FOR idc IN l_txn_idc_instances_tbl.FIRST .. l_txn_idc_instances_tbl.LAST LOOP
1183
1184 OPEN install_date_csr(l_txn_idc_instances_tbl(idc));
1185 FETCH install_date_csr INTO install_date_rec;
1186 CLOSE install_date_csr;
1187
1188 IF l_parent_install_date <> install_date_rec.install_date THEN
1189 debug(' Installation Dates do not match so remove this instance: '||l_txn_idc_instances_tbl(id));
1190 l_txn_idc_instances_tbl.delete(id);
1191 END IF;
1192
1193 x_txn_inst_tbl(trx10).instance_tbl := l_txn_idc_instances_tbl;
1194 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1195 END LOOP; -- idc idx
1196
1197 ELSIF p_txn_oks_rec.transaction_type(trx10) = 'TRM' THEN
1198 -- Pass out Parent and any Children
1199 debug(' TRM Transaction Type');
1200 x_txn_inst_tbl(trx10).instance_tbl := l_txn_instances_tbl;
1201 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1202
1203 END IF;
1204
1205 END LOOP;
1206
1207 ELSE -- Single Instance
1208
1209 debug(' Start of get_instances ... Single Instance Processing');
1210 l_relationship_query_rec.object_id := p_txn_oks_rec.instance_id;
1211 l_relationship_query_rec.relationship_type_code := 'COMPONENT-OF';
1212
1213 csi_ii_relationships_pvt.Get_Children (l_relationship_query_rec ,
1214 l_rel_tbl ,
1215 NULL, --l_depth
1216 fnd_api.g_true, --l_active_relationship_only
1217 fnd_api.g_true, --l_active_instances_only
1218 fnd_api.g_false, --
1219 NULL,
1220 l_get_dfs ,
1221 l_ii_relationship_level_tbl ,
1222 x_return_status ,
1223 x_msg_count ,
1224 x_msg_data );
1225
1226 IF NOT l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1227 l_msg_index := 1;
1228 WHILE l_msg_count > 0 loop
1229 l_error_message := l_error_message || fnd_msg_pub.get(l_msg_index,FND_API.G_FALSE);
1230 l_msg_index := l_msg_index + 1;
1231 l_msg_count := l_msg_count - 1;
1232 END LOOP;
1233 RAISE fnd_api.g_exc_error;
1234 END IF;
1235
1236 IF l_rel_tbl.count = 0 THEN
1237 debug(' No Children so just add this instance to the out table');
1238 -- its a parent just add to the table
1239 inst := l_txn_instances_tbl.count + 1;
1240 l_txn_instances_tbl(inst) := inst_rec.instance_id;
1241 ELSE
1242 FOR rel IN l_rel_tbl.FIRST .. l_rel_tbl.LAST LOOP
1243 inst := l_txn_instances_tbl.count + 1;
1244 debug(' Children Found so add this instance to the out table: '||l_rel_tbl(rel).subject_id);
1245 l_txn_instances_tbl(inst) := l_rel_tbl(rel).subject_id;
1246 END LOOP; -- l_rel_tbl
1247 END IF;
1248
1249 -- Assign the table of instances to all of the Transaction Type Rows
1250 FOR trx10 IN p_txn_oks_rec.transaction_type.FIRST .. p_txn_oks_rec.transaction_type.LAST LOOP
1251 IF p_txn_oks_rec.transaction_type(trx10) = 'TRF' THEN
1252 debug(' TRF Transaction Type');
1253
1254 l_txn_trf_instances_tbl := l_txn_instances_tbl;
1255
1256 FOR trf IN l_txn_trf_instances_tbl.FIRST .. l_txn_trf_instances_tbl.LAST LOOP
1257
1258 -- Get the Parent Owner Party ID
1259 OPEN parent_child_party_csr(p_txn_oks_rec.instance_id);
1260 FETCH parent_child_party_csr INTO l_owner_party_id;
1261 CLOSE parent_child_party_csr;
1262
1263 -- Get the Child Owner Party ID
1264 OPEN parent_child_party_csr(l_txn_trf_instances_tbl(trf));
1265 FETCH parent_child_party_csr INTO l_child_owner_party_id;
1266 CLOSE parent_child_party_csr;
1267
1268 debug(' Parent Owner Party: '||l_owner_party_id);
1269 debug(' Child Owner Party: '||l_child_owner_party_id);
1270 IF l_owner_party_id <> l_child_owner_party_id THEN
1271 l_txn_trf_instances_tbl.delete(trf);
1272 END IF;
1273 END LOOP; -- trf index
1274
1275 x_txn_inst_tbl(trx10).instance_tbl := l_txn_trf_instances_tbl;
1276 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1277
1278 ELSIF p_txn_oks_rec.transaction_type(trx10) = 'IDC' THEN
1279 debug(' IDC Transaction Type');
1280
1281 l_txn_idc_instances_tbl := l_txn_instances_tbl;
1282
1283 FOR idc IN l_txn_idc_instances_tbl.FIRST .. l_txn_idc_instances_tbl.LAST LOOP
1284
1285 -- Get the Parent Install Date
1286 OPEN install_date_csr(p_txn_oks_rec.instance_id);
1287 FETCH install_date_csr INTO l_parent_install_date;
1288 CLOSE install_date_csr;
1289
1290 -- Get the Child Install Date
1291 OPEN install_date_csr(l_txn_idc_instances_tbl(idc));
1292 FETCH install_date_csr INTO l_child_install_date;
1293 CLOSE install_date_csr;
1294
1295 debug(' Parent Installation Date: '||l_parent_install_date);
1296 debug(' Child Installation Date: '||l_child_install_date);
1297 IF l_parent_install_date <> l_child_install_date THEN
1298 l_txn_idc_instances_tbl.delete(id);
1299 END IF;
1300 END LOOP; -- idc idx
1301
1302 x_txn_inst_tbl(trx10).instance_tbl := l_txn_idc_instances_tbl;
1303 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1304
1305 ELSIF p_txn_oks_rec.transaction_type(trx10) in ('TRM','RIN') THEN
1306 debug(' '||p_txn_oks_rec.transaction_type(trx10)|| 'Transaction Type');
1307 -- Pass out Parent and any Children
1308 x_txn_inst_tbl(trx10).instance_tbl := l_txn_instances_tbl;
1309 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1310
1311 ELSIF p_txn_oks_rec.transaction_type(trx10) in ('UPD','SPL') THEN
1312 -- Pass out Just the Parent
1313 debug(' '||p_txn_oks_rec.transaction_type(trx10)|| 'Transaction Type');
1314 x_txn_inst_tbl(trx10).instance_tbl(trx10) := p_txn_oks_rec.instance_id;
1315 x_txn_inst_tbl(trx10).transaction_type := p_txn_oks_rec.transaction_type(trx10);
1316 END IF;
1317
1318 END LOOP;
1319
1320 END IF; -- Batch ID
1321
1322 EXCEPTION
1323 WHEN fnd_api.g_exc_error THEN
1324 x_return_status := FND_API.G_RET_STS_ERROR;
1325 x_msg_count := l_msg_count;
1326 x_msg_data := l_msg_data;
1327
1328 WHEN others THEN
1329 l_sql_error := SQLERRM;
1330 fnd_message.set_name('CSI','CSI_UNEXP_SQL_ERROR');
1331 fnd_message.set_token('API_NAME',l_api_name);
1332 fnd_message.set_token('SQL_ERROR',l_sql_error);
1333 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1334 x_msg_count := 1;
1335 x_msg_data := fnd_message.get;
1336
1337 END get_instances;
1338
1339 -- Procedures for INV/OM Transaction Data Purge
1340 PROCEDURE inv_txn_data_purge(
1341 p_inv_period_from_date IN DATE,
1342 p_inv_period_to_date IN DATE,
1343 p_organization_id IN NUMBER,
1344 x_return_status OUT NOCOPY varchar2,
1345 x_return_message OUT NOCOPY varchar2) IS
1346
1347 l_freeze_date DATE;
1348 inv_purge_not_allowed EXCEPTION;
1349 inv_purge_allowed EXCEPTION;
1350
1351 TYPE NumTabType is varray(10000) of number;
1352 TYPE DateTabType is varray(10000) of date;
1353
1354 l_transaction_id_tab NumTabType;
1355 l_transaction_date_tab DateTabType;
1356
1357 MAX_BUFFER_SIZE NUMBER := 1000;
1358
1359 CURSOR c_mtl_data (pc_from_date in DATE,
1360 pc_to_date in DATE) IS
1361 SELECT transaction_id,
1362 transaction_date
1363 FROM mtl_material_transactions
1364 WHERE transaction_date between pc_from_date and pc_to_date;
1365
1366 CURSOR c_csi_txns (pc_transaction_id in NUMBER) IS
1367 SELECT 1
1368 FROM csi_transactions
1369 WHERE inv_material_transaction_id = pc_transaction_id;
1370
1371 l_csi_txn_found NUMBER := NULL;
1372
1373 BEGIN
1374 x_return_status := FND_API.G_RET_STS_SUCCESS;
1375
1376 -- Bulk Collect the Transaction Data and validate each transaction
1377 OPEN c_mtl_data (p_inv_period_from_date,p_inv_period_to_date);
1378 LOOP
1379
1380 FETCH c_mtl_data BULK COLLECT
1381 INTO l_transaction_id_tab,
1382 l_transaction_date_tab
1383 LIMIT MAX_BUFFER_SIZE;
1384
1385 FOR ind IN 1 .. l_transaction_id_tab.COUNT LOOP
1386
1387 -- Check for the Freeze Date Existance and If there is a data is that
1388 -- before the freeze date.
1389
1390 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
1391 csi_gen_utility_pvt.populate_install_param_rec;
1392 END IF;
1393
1394 IF csi_datastructures_pub.g_install_param_rec.freeze_date is NULL then
1395 raise inv_purge_allowed;
1396 ELSE
1397 l_freeze_date := csi_datastructures_pub.g_install_param_rec.freeze_date;
1398 END IF;
1399
1400 IF l_freeze_date > l_transaction_date_tab(ind) THEN
1401 RAISE inv_purge_allowed;
1402 END IF;
1403
1404 --Check to see if this transaction is supported in Installed Base.
1405
1406 IF NOT csi_inv_trxs_pkg.valid_ib_txn(l_transaction_id_tab(ind)) THEN
1407 raise inv_purge_allowed;
1408 END IF;
1409
1410 --Check to see if a transaction for this material transaction exists in IB
1411 OPEN c_csi_txns (l_transaction_id_tab(ind));
1412 FETCH c_csi_txns into l_csi_txn_found;
1413 CLOSE c_csi_txns;
1414
1415 IF l_csi_txn_found IS NULL THEN
1416 raise inv_purge_not_allowed;
1417 ELSE
1418 Raise inv_purge_allowed;
1419 END IF;
1420 END LOOP; -- Txn ID Loop
1421
1422 END LOOP; -- Main Loop
1423 CLOSE c_mtl_data;
1424
1425 EXCEPTION
1426 WHEN inv_purge_not_allowed THEN
1427 fnd_message.set_name('CSI','CSI_INV_NO_DATA_PURGE');
1428 x_return_status := fnd_api.g_ret_sts_error;
1429 x_return_message := fnd_message.get;
1430
1431 WHEN inv_purge_allowed THEN
1432 x_return_status := fnd_api.g_ret_sts_success;
1433 x_return_message := NULL;
1434
1435 WHEN others THEN
1436 fnd_message.set_name('CSI','CSI_INV_NO_DATA_PURGE');
1437 x_return_status := fnd_api.g_ret_sts_error;
1438 x_return_message := fnd_message.get;
1439
1440 END inv_txn_data_purge;
1441
1442 PROCEDURE om_txn_data_purge(
1443 p_om_txn_info IN csi_utility_grp.om_txn_info_tbl,
1444 x_return_status OUT NOCOPY varchar2,
1445 x_return_message OUT NOCOPY varchar2) IS
1446
1447 l_freeze_date DATE;
1448 l_freeze_date_error VARCHAR2(1) := 'N';
1449 j NUMBER;
1450 l_purge_allowed VARCHAR2(1) := 'Y';
1451 om_purge_not_allowed EXCEPTION;
1452 om_purge_allowed EXCEPTION;
1453
1454 CURSOR c_csi_txns (pc_line_id IN NUMBER,
1455 pc_txn_id IN NUMBER) IS
1456 SELECT transaction_id,source_header_ref
1457 FROM csi_transactions
1458 WHERE source_line_ref_id = pc_line_id
1459 AND transaction_type_id = pc_txn_id;
1460
1461 r_csi_txns c_csi_txns%rowtype;
1462
1463 CURSOR c_so_info (pc_line_id in NUMBER) is
1464 SELECT oeh.header_id,
1465 oel.line_id,
1466 oeh.order_number,
1467 oel.line_number
1468 FROM oe_order_headers_all oeh,
1469 oe_order_lines_all oel
1470 WHERE oeh.header_id = oel.header_id
1471 AND oel.line_id = pc_line_id;
1472
1473 r_so_info c_so_info%rowtype;
1474
1475 BEGIN
1476
1477 x_return_status := FND_API.G_RET_STS_SUCCESS;
1478
1479 j := 1;
1480
1481 -- OM will pass a table of order lines that belong to 1 order. We need to loop
1482 -- and validate each line and if any line cannot be purged the entire order data
1483 -- will be retained.
1484
1485
1486 FOR j in p_om_txn_info.FIRST .. p_om_txn_info.LAST LOOP
1487
1488 IF j = 1 THEN
1489 debug_con_log('***** Start of Install Base Purge Program for Order Header '||p_om_txn_info(1).header_id||' *****');
1490 END IF;
1491
1492 -- Check for the Freeze Date Existance and If there is a data is that
1493 -- before the freeze date.
1494
1495 IF csi_datastructures_pub.g_install_param_rec.fetch_flag IS NULL THEN
1496 csi_gen_utility_pvt.populate_install_param_rec;
1497 END IF;
1498
1499 IF csi_datastructures_pub.g_install_param_rec.freeze_date is NOT NULL then
1500 l_freeze_date := csi_datastructures_pub.g_install_param_rec.freeze_date;
1501 ELSE
1502 l_freeze_date := p_om_txn_info(j).request_date + 1;
1503 END IF;
1504
1505 IF l_freeze_date < trunc(p_om_txn_info(j).request_date) THEN
1506
1507 OPEN c_so_info (p_om_txn_info(j).line_id);
1508 FETCH c_so_info into r_so_info;
1509 CLOSE c_so_info;
1510
1511 debug_con_log('Order Number......'||r_so_info.order_number);
1512 debug_con_log(' Line Number.....'||r_so_info.line_number);
1513 debug_con_log(' Vld Org.........'||p_om_txn_info(j).inv_vld_organization_id);
1514 debug_con_log(' Item Id.........'||p_om_txn_info(j).inventory_item_id);
1515 debug_con_log(' Freeze Date.....'||l_freeze_date);
1516 debug_con_log(' Request Date....'||p_om_txn_info(j).request_date);
1517 debug_con_log(' Line ID.........'||p_om_txn_info(j).line_id);
1518
1519 --Check if item is CSI trackable
1520 IF csi_item_instance_vld_pvt.is_trackable(
1521 p_inv_item_id => p_om_txn_info(j).inventory_item_id,
1522 p_stack_err_msg => FALSE,
1523 p_org_id => p_om_txn_info(j).inv_vld_organization_id) THEN
1524
1525
1526 --Check to see if a transaction for this material transaction exists in IB
1527 OPEN c_csi_txns (p_om_txn_info(j).line_id,51);
1528 FETCH c_csi_txns into r_csi_txns;
1529 CLOSE c_csi_txns;
1530
1531 IF r_csi_txns.transaction_id IS NULL THEN
1532 fnd_message.set_name('CSI','CSI_OM_PURGE_LINE_ERROR');
1533 fnd_message.set_token('LINE_NUMBER',r_so_info.line_number);
1534 debug_con_log(fnd_message.get);
1535 l_purge_allowed := 'N';
1536 END IF;
1537
1538 END IF; -- Trackable Item
1539
1540 ELSE
1541 l_freeze_date_error := 'Y';
1542 END IF; -- Freeze/Request Date
1543
1544 END LOOP; -- Main For Loop
1545
1546 IF l_freeze_date_error = 'Y' THEN
1547 debug_con_log('This order does not need to be validated by Install Base because either the ordered date was before Install Base was being used or the item is not trackable.');
1548 END IF;
1549
1550 IF l_purge_allowed = 'N' THEN
1551 RAISE om_purge_not_allowed;
1552 ELSE
1553 RAISE om_purge_allowed;
1554 END IF;
1555
1556 EXCEPTION
1557 WHEN om_purge_not_allowed THEN
1558 fnd_message.set_name('CSI','CSI_OM_PURGE_ERROR');
1559 fnd_message.set_token('ORDER_NUMBER',r_so_info.order_number);
1560 x_return_status := fnd_api.g_ret_sts_error;
1561 x_return_message := fnd_message.get;
1562 debug_con_log('***** End of Install Base Purge Program for Order '||r_so_info.order_number||' *****');
1563 debug_con_log('');
1564
1565 WHEN om_purge_allowed THEN
1566 x_return_status := fnd_api.g_ret_sts_success;
1567 x_return_message := NULL;
1568 debug_con_log('***** End of Install Base Purge Program for Order '||r_so_info.order_number||' *****');
1569 debug_con_log('');
1570
1571 WHEN others THEN
1572 fnd_message.set_name('CSI','CSI_OM_PURGE_ERROR');
1573 fnd_message.set_token('ORDER_NUMBER',r_so_info.order_number);
1574 x_return_status := fnd_api.g_ret_sts_error;
1575 x_return_message := fnd_message.get;
1576 debug_con_log('***** End of Install Base Purge Program for Order '||r_so_info.order_number||' *****');
1577 debug_con_log('');
1578
1579 END om_txn_data_purge;
1580
1581 PROCEDURE purge_txn_detail_tables (
1582 errbuf OUT NOCOPY VARCHAR2
1583 ,retcode OUT NOCOPY NUMBER) IS
1584
1585
1586 l_order_status VARCHAR2(25) := NULL;
1587 l_processed_recs NUMBER;
1588 l_mass_update_recs NUMBER;
1589 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1590 l_msg_count NUMBER;
1591 l_msg_data VARCHAR2(2000);
1592
1593 purge_error EXCEPTION;
1594
1595 CURSOR c_mu (p_status IN VARCHAR2) IS
1596 SELECT txn_line_id
1597 FROM csi_mass_edit_entries_b
1598 WHERE status_code = p_status;
1599
1600 CURSOR c_processed_recs (p_status IN VARCHAR2) IS
1601 SELECT transaction_line_id
1602 FROM csi_t_transaction_lines
1603 WHERE processing_status = p_status
1604 AND migrated_flag is NULL;
1605
1606 CURSOR c_migrated_recs IS
1607 SELECT transaction_line_id,
1608 source_transaction_id
1609 FROM csi_t_transaction_lines
1610 WHERE migrated_flag = 'Y';
1611
1612 BEGIN
1613
1614
1615 debug_con_log(' ********** Start of CSI_T Table Purge ********** ');
1616 debug_con_log(' ');
1617 debug_con_log(' Processing all Migrated Records ... ');
1618
1619 savepoint csi_tdtl_purge;
1620
1621 -- Process Migrated Records
1622 FOR r_migrated_recs in c_migrated_recs LOOP
1623
1624 l_order_status := NULL;
1625
1626 BEGIN
1627 debug_con_log(' Check to see if the Order is open for Source Transaction ID: '||r_migrated_recs.source_transaction_id);
1628
1629 -- Check to see if the Order Status is closed for this line.
1630 SELECT oh.flow_status_code
1631 INTO l_order_status
1632 FROM oe_order_headers_all oh, oe_order_lines_all ol
1633 WHERE ol.line_id = r_migrated_recs.source_transaction_id
1634 AND ol.header_id = oh.header_id;
1635
1636 -- Check OE to see if the Order that this Line is on is Closed. If so then remove the
1637 -- the Txn Details
1638
1639 debug_con_log(' Order found and the Flow Status is: '||l_order_status);
1640
1641 IF l_order_status in ('CLOSED','CANCELLED') THEN
1642
1643 debug_con_log(' Before csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Detail: '||r_migrated_recs.transaction_line_id);
1644
1645 csi_t_txn_details_grp.delete_transaction_dtls (
1646 p_api_version => 1.0
1647 ,p_commit => fnd_api.g_false
1648 ,p_init_msg_list => fnd_api.g_false
1649 ,p_validation_level => fnd_api.g_valid_level_full
1650 ,p_transaction_line_id => r_migrated_recs.transaction_line_id
1651 ,p_api_caller_identity => 'PURGE'
1652 ,x_return_status => l_return_status
1653 ,x_msg_count => l_msg_count
1654 ,x_msg_data => l_msg_data);
1655
1656 debug_con_log(' After csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Details');
1657 debug_con_log(' Return Status is :'||l_return_status||' for Txn Line '||r_migrated_recs.transaction_line_id);
1658
1659 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1660 debug_con_log(' l_msg_data: '||l_msg_data);
1661 Raise purge_error;
1662 END IF;
1663
1664 END IF;
1665
1666 EXCEPTION
1667 WHEN no_data_found THEN
1668 -- No Order Data is found it must have been purged..Txn Details can be removed.
1669 debug_con_log(' No Order found so OM data must have been purged. Remove Txn Detail data');
1670 debug_con_log(' Before csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Detail: '||r_migrated_recs.transaction_line_id);
1671
1672 csi_t_txn_details_grp.delete_transaction_dtls (
1673 p_api_version => 1.0
1674 ,p_commit => fnd_api.g_false
1675 ,p_init_msg_list => fnd_api.g_false
1676 ,p_validation_level => fnd_api.g_valid_level_full
1677 ,p_transaction_line_id => r_migrated_recs.transaction_line_id
1678 ,p_api_caller_identity => 'PURGE'
1679 ,x_return_status => l_return_status
1680 ,x_msg_count => l_msg_count
1681 ,x_msg_data => l_msg_data);
1682
1683 debug_con_log(' After csi_t_txn_details_grp.delete_transaction_dtls to remove Txn Line Details');
1684 debug_con_log(' Return Status is :'||l_return_status||' for Txn Line '||r_migrated_recs.transaction_line_id);
1685
1686 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1687 debug_con_log(' l_msg_data: '||l_msg_data);
1688 Raise purge_error;
1689 END IF;
1690
1691 END;
1692
1693 END LOOP; -- c_migrated_records
1694
1695 commit;
1696
1697 debug_con_log(' Finished Processing all Migrated Records ... ');
1698 debug_con_log(' ');
1699 debug_con_log(' Processing all Processed Records ... ');
1700
1701 savepoint csi_tdtl_purge;
1702
1703 -- Process all Processed Records
1704 FOR r_processed_recs in c_processed_recs ('PROCESSED') LOOP
1705
1706 l_processed_recs := 0;
1707
1708 BEGIN
1709 debug_con_log(' Check to see if any of the Txn Lines are a Non Source Type for Txn Line ID: '||r_processed_recs.transaction_line_id);
1710 SELECT count(*)
1711 INTO l_processed_recs
1712 FROM csi_t_txn_line_details
1713 WHERE transaction_line_id = r_processed_recs.transaction_line_id
1714 AND source_transaction_flag = 'N';
1715
1716 IF l_processed_recs = 0 THEN
1717
1718 -- There are only source records so we can delete the Transaction Details for this
1719 -- Transaction Line
1720
1721 debug_con_log(' There are no Non Source Lines so remove the Txn Detail: '||r_processed_recs.transaction_line_id);
1722 debug_con_log(' Before csi_t_txn_details_grp.delete_transaction_dtls');
1723
1724 csi_t_txn_details_grp.delete_transaction_dtls (
1725 p_api_version => 1.0
1726 ,p_commit => fnd_api.g_false
1727 ,p_init_msg_list => fnd_api.g_false
1728 ,p_validation_level => fnd_api.g_valid_level_full
1729 ,p_transaction_line_id => r_processed_recs.transaction_line_id
1730 ,p_api_caller_identity => 'PURGE'
1731 ,x_return_status => l_return_status
1732 ,x_msg_count => l_msg_count
1733 ,x_msg_data => l_msg_data);
1734
1735 debug_con_log(' After csi_t_txn_details_grp.delete_transaction_dtls');
1736 debug_con_log(' Return Status is :'||l_return_status||' for Txn Line '||r_processed_recs.transaction_line_id);
1737
1738 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1739 debug_con_log(' l_msg_data: '||l_msg_data);
1740 Raise purge_error;
1741 END IF;
1742
1743 ELSE
1744 -- There are non source records so do not do anything.
1745 debug_con_log(' There are Source Lines so do not remove the Txn Detail: '||r_processed_recs.transaction_line_id);
1746 END IF;
1747
1748
1749 EXCEPTION
1750 WHEN others THEN
1751 -- Some unexpected error
1752 debug_con_log(' Unexpected Error for Processed Recs: '||SQLERRM);
1753
1754 END;
1755
1756 END LOOP; --c_processed_recs
1757
1758 commit;
1759
1760 debug_con_log(' Finished Processing all Processed Records ... ');
1761 debug_con_log(' ');
1762 debug_con_log(' Remove Mass Update Recs with no Txn Details ... ');
1763
1764 -- Delete all Mass Update Batch Records because all the batch lines have been deleted.
1765
1766 savepoint csi_tdtl_purge;
1767
1768 FOR r_mu in c_mu ('SUCCESSFUL') LOOP
1769
1770 l_mass_update_recs := 0;
1771
1772 BEGIN
1773 debug_con_log(' Check to see if the Txn Line in Mass Update has any records in csi_t_transaction_lines: '||r_mu.txn_line_id);
1774 SELECT 1
1775 INTO l_mass_update_recs
1776 FROM csi_t_transaction_lines
1777 WHERE transaction_line_id = r_mu.txn_line_id;
1778
1779 EXCEPTION
1780 WHEN no_data_found THEN
1781 debug_con_log(' No Records exist so remove the Mass Update data from csi_mass_edit_entries_b: '||r_mu.txn_line_id);
1782
1783 DELETE from csi_mass_edit_entries_b
1784 WHERE txn_line_id = r_mu.txn_line_id;
1785
1786 END;
1787 END LOOP; -- c_mu
1788
1789 commit;
1790
1791 debug_con_log(' Finished Processing Mass Update Recs with no Txn Details ... ');
1792 debug_con_log(' ');
1793 debug_con_log(' ********** End of CSI_T Table Purge ********** ');
1794
1795 EXCEPTION
1796
1797 WHEN purge_error THEN
1798 debug_con_log(' EXCEPTION: Purge Error');
1799 rollback to csi_tdtl_purge;
1800 fnd_message.set_name('CSI','CSI_TXN_DTL_PURGE_ERROR');
1801 debug_con_log('Error: '||fnd_message.get);
1802
1803 WHEN others THEN
1804 debug_con_log(' When OTHERS Exception: '||SQLERRM);
1805 rollback to csi_tdtl_purge;
1806 fnd_message.set_name('CSI','CSI_OTHERS_EXCEPTION');
1807 debug_con_log('Error: '||fnd_message.get);
1808
1809 END; -- End of purge_txn_detail_tables
1810
1811 END CSI_UTILITY_GRP;
[Home] [Help]