[Home] [Help]
PACKAGE BODY: APPS.BOM_GLOBALS
Source
1 PACKAGE BODY BOM_Globals AS
2 /* $Header: BOMSGLBB.pls 120.24.12010000.2 2008/11/17 11:05:21 gliang ship $ */
3 /**********************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMSGLBB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Globals
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 16-JUL-1999 Rahul Chitko Initial Creation
21 --
22 -- 09-MAY-2001 Refai Farook EAM related changes
23 --
24 -- 22-AUG-01 Refai Farook One To Many support changes
25 --
26 -- 08-Apr-2003 snelloli Added Functions Get_Alternate Get_Structure_Type
27 **********************************************************************/
28
29 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Globals';
30
31 -- Global variable holding ECO workflow approval process name
32
33 G_PROCESS_NAME VARCHAR2(30) := NULL;
34 G_System_Information System_Information_Rec_Type;
35 G_Control_Rec BOM_BO_PUB.Control_Rec_Type;
36
37 PROCEDURE Init_System_Info_Rec
38 ( x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
39 , x_return_status IN OUT NOCOPY VARCHAR2
40 )
41 IS
42 BEGIN
43 Bom_Globals.Set_user_id( p_user_id => FND_GLOBAL.user_id);
44 Bom_Globals.Set_login_id( p_login_id => FND_GLOBAL.login_id);
45 Bom_Globals.Set_prog_id( p_prog_id => FND_GLOBAL.conc_program_id);
46 Bom_Globals.Set_prog_appid( p_prog_appid => FND_GLOBAL.prog_appl_id);
47 Bom_Globals.Set_request_id( p_request_id => FND_GLOBAL.conc_request_id);
48
49 END Init_System_Info_Rec;
50
51 /****************************************************************************
52 * The following procedures and functions are the get and set routines for the
53 * system_information_record.
54 * Numeric attributes of the record have Get functions with a naming convention
55 * of Get_<Attribute_Name> ex. Get_Bill_Sequence_Id
56 * For attributes of type Boolean the convention is IS_<Boolean_Attribute_Name>
57 * Ex. Is_Eco_Impl will return value of the boolean attribute Eco_Impl.
58 * Similarly the set procedures will have the convention of Set_<Attribute_Name>
59 * with the respective attribute Type variable as an input.
60 * There are also two routines which get and set the entire record as a whole.
61 * Added 06/21/99 by RC.
62 *****************************************************************************/
63
64 /**************************************************************************
65 * Function : Get_System_Information
66 * Returns : System_Information Record
67 * Parameters IN : None
68 * Parameters OUT: None
69 * Purpose : This procedure will return the value of the system information
70 * record.
71 ****************************************************************************/
72 FUNCTION Get_System_Information RETURN Bom_Globals.System_Information_Rec_Type
73 IS
74 BEGIN
75 RETURN G_System_Information;
76
77 END Get_System_Information;
78
79
80 /***************************************************************************
81 * Procedure : Set_System_Information
82 * Returns : None
83 * Parameters IN : System_Information_Record
84 * Parameters OUT: None
85 * Purpose : This procedure will set the value of the system information
86 * record.
87 ****************************************************************************/
88 PROCEDURE Set_System_Information
89 ( p_system_information_rec IN
90 Bom_Globals.System_Information_Rec_Type)
91 IS
92 BEGIN
93 G_System_Information := p_system_information_rec;
94
95 END Set_System_Information;
96
97
98 PROCEDURE Check_Approved_For_Process
99 ( p_change_notice IN VARCHAR2
100 , p_organization_id IN NUMBER
101 , x_processed IN OUT NOCOPY BOOLEAN
102 , x_err_text IN OUT NOCOPY VARCHAR2
103 )
104 IS
105 l_process_name VARCHAR2(30) := NULL;
106 l_approval_status_type NUMBER;
107 BEGIN
108 x_processed := FALSE;
109 -- Get Workflow Process name
110 l_process_name := BOM_Globals.Get_Process_Name;
111 SELECT approval_status_type
112 INTO l_approval_status_type
113 FROM eng_engineering_changes
114 WHERE change_notice = p_change_notice
115 AND organization_id = p_organization_id;
116
117 -- ECO w/ Process is Approved
118
119 IF l_approval_status_type = 5 AND
120 l_process_name is NOT NULL
121 THEN
122 x_processed := TRUE;
123 END IF;
124
125 EXCEPTION
126 WHEN NO_DATA_FOUND THEN
127 x_processed := FALSE;
128 WHEN OTHERS THEN
129 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
130 THEN
131 x_err_text := G_PKG_NAME || '(Check_Approved_For_Process) - '
132 || 'ECO Header' || substrb(SQLERRM,1,60);
133 END IF;
134 RAISE FND_API.G_EXC_ERROR;
135
136 END Check_Approved_For_Process;
137
138 PROCEDURE Set_Request_For_Approval
139 ( p_change_notice IN VARCHAR2
140 , p_organization_id IN NUMBER
141 , x_err_text IN OUT NOCOPY VARCHAR2
142 )
143 IS
144 BEGIN
145 -- Set ECO to 'Not Submitted For Approval'
146
147 UPDATE eng_engineering_changes
148 SET approval_status_type = 1,
149 approval_request_date = null,
150 approval_date = null,
151 last_update_date = SYSDATE,
152 last_updated_by = FND_GLOBAL.USER_ID,
153 last_update_login = FND_GLOBAL.LOGIN_ID
154 WHERE organization_id = p_organization_id
155 AND change_notice = p_change_notice;
156
157 -- Set all "Scheduled" revised items to "Open"
158
159 UPDATE eng_revised_items
160 SET status_type = 1,
161 last_update_date = SYSDATE,
162 last_updated_by = FND_GLOBAL.USER_ID,
163 last_update_login = FND_GLOBAL.LOGIN_ID
164 WHERE organization_id = p_organization_id
165 AND change_notice = p_change_notice
166 AND status_type = 4;
167
168 -- Issue warning
169 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
170 THEN
171 NULL;
172 END IF;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
177 THEN
178 x_err_text := G_PKG_NAME || '(Set_Request_For_Approval) -
179 ECO Header and Revised Items' || substrb(SQLERRM,1,60);
180 END IF;
181
182 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
183
184 END Set_Request_For_Approval;
185
186 PROCEDURE Init_Process_Name
187 ( p_change_order_type_id IN NUMBER
188 , p_priority_code IN VARCHAR2
189 , p_organization_id IN NUMBER
190 )
191 IS
192 l_process_name VARCHAR2(30) := NULL;
193 BEGIN
194
195 IF p_change_order_type_id IS NULL THEN
196 G_PROCESS_NAME := NULL;
197 END IF;
198
199 SELECT process_name
200 INTO l_process_name
201 FROM eng_change_type_processes
202 WHERE change_order_type_id = p_change_order_type_id
203 AND ( p_priority_code is NOT NULL
204 AND eng_change_priority_code = p_priority_code
205 AND organization_id = p_organization_id)
206 OR
207 (p_priority_code is NULL
208 AND eng_change_priority_code is NULL);
209
210 G_PROCESS_NAME := l_process_name;
211
212 EXCEPTION
213 WHEN NO_DATA_FOUND THEN
214
215 G_PROCESS_NAME := NULL;
216
217 END Init_Process_Name;
218
219 FUNCTION Get_Process_Name
220 RETURN VARCHAR2
221 IS
222 BEGIN
223 RETURN G_PROCESS_NAME;
224 END Get_Process_Name;
225
226 /*****************************************************************************
227 * Procedure : Set_Bill_Sequence_id
228 * Returns : None
229 * Parameters IN : Bill_Sequence_Id
230 * Parameters OUT: None
231 * Purpose : This procedure will set the bill_sequence_id value in the
232 * system_information record.
233 *
234 *****************************************************************************/
235 PROCEDURE Set_Bill_Sequence_id
236 ( p_bill_sequence_id IN NUMBER)
237 IS
238 BEGIN
239 G_System_Information.bill_sequence_id := p_bill_sequence_id;
240 END;
241
242 /***************************************************************************
243 * Function : Get_Bill_Sequence_id
244 * Returns : Number
245 * Parameters IN : None
246 * Parameters OUT: None
247 * Purpose : This function will return the bill_sequence_id value in the
248 * system_information record.
249 ******************************************************************************/
250 FUNCTION Get_Bill_Sequence_id RETURN NUMBER
251 IS
252 BEGIN
253 RETURN G_System_Information.bill_sequence_id;
254
255 END Get_Bill_Sequence_id;
256
257 /*****************************************************************************
258 * Procedure : Set_Entity
259 * Returns : None
260 * Parameters IN : Entity Name
261 * Parameter IN OUT NOCOPY : None
262 * Purpose : Will set the entity name in the System Information Record.
263 *
264 ******************************************************************************/
265 PROCEDURE Set_Entity
266 ( p_entity IN VARCHAR2)
267 IS
268 BEGIN
269 G_System_information.entity := p_entity;
270 END Set_Entity;
271
272 /****************************************************************************
273 * Function : Get_Entity
274 * Returns : VARCHAR2
275 * Parameters IN : None
276 * Parameter IN OUT NOCOPY : None
277 * Purpose : Will return the entity name in the System Information Record.
278 *
279 *****************************************************************************/
280 FUNCTION Get_Entity RETURN VARCHAR2
281 IS
282 BEGIN
283 RETURN G_System_Information.entity;
284 END Get_Entity;
285
286 /****************************************************************************
287 * Procedure : Set_Org_id
288 * Returns : None
289 * Parameters IN : Organization_Id
290 * Parameters OUT: None
291 * Purpose : Will set the org_id attribute of the sytem_information_record
292 *
293 *****************************************************************************/
294 PROCEDURE Set_Org_id
295 ( p_org_id IN NUMBER)
296 IS
297 BEGIN
298 G_System_Information.org_id := p_org_id;
299
300 END Set_Org_Id;
301
302 /***************************************************************************
303 * Function : Get_Org_id
304 * Returns : Number
305 * Parameters IN : None
306 * Parameters OUT: None
307 * Purpose : Will return the org_id attribute of the
308 * sytem_information_record
309 *****************************************************************************/
310 FUNCTION Get_Org_id RETURN NUMBER
311 IS
312 BEGIN
313 RETURN G_System_Information.org_id;
314
315 END Get_Org_Id;
316
317 /****************************************************************************
318 * Procedure : Set_Eco_Name
319 * Returns : None
320 * Parameters IN : Eco_Name
321 * Parameters OUT: None
322 * Purpose : Will set the Eco_Name attribute of the
323 * system_information record
324 ******************************************************************************/
325 PROCEDURE Set_Eco_Name
326 ( p_eco_name IN VARCHAR2)
327 IS
328 BEGIN
329 G_System_Information.eco_name := p_eco_name;
330
331 END Set_Eco_Name;
332
333 /****************************************************************************
334 * Function : Get_Eco_Name
335 * Returns : VARCHAR2
336 * Parameters IN : None
337 * Parameters OUT: None
338 * Purpose : Will return the Eco_Name attribute of the
339 * system_information record
340 *****************************************************************************/
341 FUNCTION Get_Eco_Name RETURN VARCHAR2
342 IS
343 BEGIN
344 RETURN G_System_Information.eco_name;
345
346 END Get_Eco_Name;
347
348 /*****************************************************************************
349 * Procedure : Set_User_Id
350 * Returns : None
351 * Parameters IN : User ID
352 * Parameters OUT: None
353 * Purpose : Will set the user ID attribute of the
354 * system_information_record
355 *****************************************************************************/
356 PROCEDURE Set_User_Id
357 ( p_user_id IN NUMBER)
358 IS
359 BEGIN
360 G_System_Information.user_id := p_user_id;
361
362 END Set_User_Id;
363
364 /***************************************************************************
365 * Function : Get_User_Id
366 * Returns : Number
367 * Parameters IN : None
368 * Parameters OUT: None
369 * Purpose : Will return the user_id attribute from the
370 * system_information_record
371 *****************************************************************************/
372 FUNCTION Get_User_ID RETURN NUMBER
373 IS
374 BEGIN
375 RETURN G_System_Information.user_id;
376
377 END Get_User_id;
378
379 /***************************************************************************
380 * Procedure : Set_Routing_Sequence_Id
381 * Returns : None
382 * Parameters IN : p_routing_sequence_id
383 * Parameters OUT: None
384 * Purpose : Procedure will set the Routing Sequence Id attribute
385 * Routing_Sequence_Id of the system information record.
386 *****************************************************************************/
387 PROCEDURE Set_Routing_Sequence_Id
388 ( p_routing_sequence_id IN NUMBER)
389 IS
390 BEGIN
391 G_System_Information.routing_sequence_id :=
392 p_routing_sequence_id;
393
394 END Set_Routing_Sequence_Id;
395
396 /***************************************************************************
397 * Function : Get_Routing_Sequence_Id
398 * Returns : NUMBER
399 * Parameters IN : None
400 * Parameters OUT: None
401 * Purpose : Function will return the value of Routing Sequence Id
402 * from the system
403 * information record.
407 BEGIN
404 *****************************************************************************/
405 FUNCTION Get_Routing_Sequence_Id RETURN NUMBER
406 IS
408 RETURN G_System_Information.routing_sequence_id;
409
410 END Get_Routing_Sequence_Id;
411
412 /***************************************************************************
413 * Procedure : Set_Lot_Number
414 * Returns : None
415 * Parameters IN : p_lot_number
416 * Parameters OUT: None
417 * Purpose : Procedure will set the Lot Number attribute
418 * of the system information record.
419 *****************************************************************************/
420 PROCEDURE Set_Lot_Number
421 ( p_lot_number IN VARCHAR2 )
422 IS
423 BEGIN
424 G_System_Information.lot_number := p_lot_number;
425
426 END Set_Lot_Number;
427
428 /***************************************************************************
429 * Function : Get_Lot_Number
430 * Returns : VARCHAR2
431 * Parameters IN : None
432 * Parameters OUT: None
433 * Purpose : Function will return the value of Lot Number
434 * from the system information record.
435 *****************************************************************************/
436 FUNCTION Get_Lot_Number RETURN VARCHAR2
437 IS
438 BEGIN
439 RETURN G_System_Information.lot_number;
440
441 END Get_Lot_Number;
442
443 /***************************************************************************
444 * Procedure : Set_From_Wip_Entity_Id
445 * Returns : None
446 * Parameters IN : p_from_wip_entity_id
447 * Parameters OUT: None
448 * Purpose : Procedure will set the from wip entity id attribute
449 * of the system information record.
450 *****************************************************************************/
451 PROCEDURE Set_From_Wip_Entity_Id
452 ( p_from_wip_entity_id IN NUMBER)
453 IS
454 BEGIN
455 G_System_Information.from_wip_entity_id := p_from_wip_entity_id;
456
457 END Set_From_Wip_Entity_Id;
458
459 /***************************************************************************
460 * Function : Get_From_Wip_Entity_Id
461 * Returns : NUMBER
462 * Parameters IN : None
463 * Parameters OUT: None
464 * Purpose : Function will return the value of from wip entity id
465 * from the system information record.
466 *****************************************************************************/
467 FUNCTION Get_From_Wip_Entity_Id RETURN NUMBER
468 IS
469 BEGIN
470 RETURN G_System_Information.from_wip_entity_id;
471
472 END Get_From_Wip_Entity_Id;
473
474 /***************************************************************************
475 * Procedure : Set_To_Wip_Entity_Id
476 * Returns : None
477 * Parameters IN : p_to_wip_entity_id
478 * Parameters OUT: None
479 * Purpose : Procedure will set the to wip entity id attribute
480 * of the system information record.
481 *****************************************************************************/
482
483 PROCEDURE Set_To_Wip_Entity_Id
484 ( p_to_wip_entity_id IN NUMBER)
485 IS
486 BEGIN
487 G_System_Information.to_wip_entity_id := p_to_wip_entity_id;
488
489 END Set_To_Wip_Entity_Id;
490
491 /***************************************************************************
492 * Function : Get_To_Wip_Entity_Id
493 * Returns : NUMBER
494 * Parameters IN : None
495 * Parameters OUT: None
496 * Purpose : Function will return the value of to wip entity id
497 * from the system information record.
498 *****************************************************************************/
499 FUNCTION Get_To_Wip_Entity_Id RETURN NUMBER
500 IS
501 BEGIN
502 RETURN G_System_Information.to_wip_entity_id;
503
504 END Get_To_Wip_Entity_Id;
505
506 /***************************************************************************
507 * Procedure : Set_From_Cum_Qty
508 * Returns : None
509 * Parameters IN : p_from_cum_qty
510 * Parameters OUT: None
511 * Purpose : Procedure will set the From Cum Qty attribute
512 * of the system information record.
513 *****************************************************************************/
514 PROCEDURE Set_From_Cum_Qty
515 ( p_from_cum_qty IN NUMBER)
516 IS
517 BEGIN
518 G_System_Information.from_cum_qty := p_from_cum_qty;
519
520 END Set_From_Cum_Qty;
521
522 /***************************************************************************
523 * Function : Get_From_Cum_Qty
524 * Returns : NUMBER
525 * Parameters IN : None
526 * Parameters OUT: None
527 * Purpose : Function will return the value of From Cum Qty
528 * from the system information record.
529 *****************************************************************************/
530 FUNCTION Get_From_Cum_Qty RETURN NUMBER
531 IS
532 BEGIN
533 RETURN G_System_Information.from_cum_qty;
534
535 END Get_From_Cum_Qty;
536
537 /***************************************************************************
538 * Procedure : Set_Eco_For_Production
539 * Returns : None
540 * Parameters IN : p_eco_for_production
544 *****************************************************************************/
541 * Parameters OUT: None
542 * Purpose : Procedure will set the Eco For Production attribute
543 * of the system information record.
545 PROCEDURE Set_Eco_For_Production
546 ( p_eco_for_production IN NUMBER)
547 IS
548 BEGIN
549 G_System_Information.eco_for_production := p_eco_for_production;
550
551 END Set_Eco_For_Production;
552
553 /***************************************************************************
554 * Function : Get_Eco_For_Production
555 * Returns : NUMBER
556 * Parameters IN : None
557 * Parameters OUT: None
558 * Purpose : Function will return the value of Eco For Production
559 * from the system information record.
560 *****************************************************************************/
561 FUNCTION Get_Eco_For_Production RETURN NUMBER
562 IS
563 BEGIN
564 RETURN G_System_Information.eco_for_production;
565
566 END Get_Eco_For_Production;
567
568 /***************************************************************************
569 * Procedure : Set_New_Routing_Revision
570 * Returns : None
571 * Parameters IN : p_new_routing_revision
572 * Parameters OUT: None
573 * Purpose : Procedure will set the routing revision attribute
574 * of the system information record.
575 *****************************************************************************/
576 PROCEDURE Set_New_Routing_Revision
577 ( p_new_routing_revision IN VARCHAR2 )
578 IS
579 BEGIN
580 G_System_Information.new_routing_revision := p_new_routing_revision;
581
582 END Set_New_Routing_Revision;
583
584 /***************************************************************************
585 * Function : Get_New_Routing_Revision
586 * Returns : VARCHAR2
587 * Parameters IN : None
588 * Parameters OUT: None
589 * Purpose : Function will return the value of routing revision
590 * from the system information record.
591 *****************************************************************************/
592 FUNCTION Get_New_Routing_Revision RETURN VARCHAR2
593 IS
594 BEGIN
595 RETURN G_System_Information.new_routing_revision;
596
597 END Get_New_Routing_Revision;
598
599
600 /****************************************************************************
601 * Procedure : Set_Login_Id
602 * Returns : None
603 * Paramaters IN : p_login_id
604 * Parameters OUT: None
605 * Purpose : Will set the login ID attribute of the system information
606 * record.
607 *****************************************************************************/
608 PROCEDURE Set_Login_Id
609 ( p_login_id IN NUMBER )
610 IS
611 BEGIN
612 G_System_Information.login_id := p_login_id;
613
614 END Set_Login_Id;
615
616 /****************************************************************************
617 * Function : Get_Login_Id
618 * Returns : Number
619 * Paramaters IN : None
620 * Parameters OUT: None
621 * Purpose : Will retun the login ID attribute of the system information
622 * record.
623 *****************************************************************************/
624 FUNCTION Get_Login_Id RETURN NUMBER
625 IS
626 BEGIN
627 RETURN G_System_Information.Login_Id;
628 END;
629
630 /***************************************************************************
631 * Procedure : Set_Prog_AppId
632 * Returns : None
633 * Parameters IN : p_prog_appid
634 * Parameters OUT: None
635 * Purpose : Will set the Program Application Id attribute of the
636 * System Information Record.
637 *****************************************************************************/
638 PROCEDURE Set_Prog_AppId
639 ( p_prog_Appid IN NUMBER )
640 IS
641 BEGIN
642 G_System_Information.prog_appid := p_prog_appid;
643
644 END Set_Prog_AppId;
645
646 /***************************************************************************
647 * Function : Get_Prog_AppId
648 * Returns : Number
649 * Parameters IN : None
650 * Parameters OUT: None
651 * Purpose : Will return the Program Application Id (prog_appid)
652 * attribute of the system information record.
653 *****************************************************************************/
654 FUNCTION Get_Prog_AppId RETURN NUMBER
655 IS
656 BEGIN
657 RETURN G_System_Information.prog_AppId;
658
659 END Get_Prog_AppId;
660
661
662 /***************************************************************************
663 * Procedure : Set_Prog_Id
664 * Returns : None
665 * Parameters IN : p_prog_id
666 * Parameters OUT: None
667 * Purpose : Will set the Program Id attribute of the system information
668 * record.
669 *****************************************************************************/
670 PROCEDURE Set_Prog_Id
671 ( p_prog_id IN NUMBER )
672 IS
673 BEGIN
674 G_System_Information.prog_id := p_prog_id;
675
676 END Set_Prog_Id;
677
678 /***************************************************************************
679 * Function : Get_Prog_Id
683 * Purpose : Function will return the Prog_Id attribute of the System
680 * Returns : NUMBER
681 * Parameters IN : None
682 * Parameters OUT: None
684 * information record.
685 *****************************************************************************/
686 FUNCTION Get_Prog_Id RETURN NUMBER
687 IS
688 BEGIN
689 RETURN G_System_Information.prog_id;
690
691 END Get_Prog_Id;
692
693 /***************************************************************************
694 * Procedure : Set_Request_Id
695 * Returns : None
696 * Parameters IN : p_request_id
697 * Parameters OUT: None
698 * Purpose : Procedure will set the request_id attribute of the
699 * system information record.
700 *****************************************************************************/
701 PROCEDURE Set_Request_Id
702 ( p_request_id IN NUMBER )
703 IS
704 BEGIN
705 G_System_Information.request_id := p_request_id;
706 END;
707
708
709 /***************************************************************************
710 * Function : Get_Request_Id
711 * Returns : NUMBER
712 * Parameters IN : None
713 * Parameters OUT: None
714 * Purpose : Function will return the value of the request_id attribute
715 * of the system information record.
716 *****************************************************************************/
717 FUNCTION Get_Request_id RETURN NUMBER
718 IS
719 BEGIN
720 RETURN G_System_Information.request_id;
721
722 END Get_Request_Id;
723
724 /***************************************************************************
725 * Procedure : Set_Eco_Impl
726 * Returns : None
727 * Parameters IN : p_eco_impl
728 * Parameters OUT: None
729 * Purpose : Will set the attribute Eco_Impl of system information record
730 * to true or false based on the implemented status of the ECO
731 *****************************************************************************/
732 PROCEDURE Set_Eco_Impl
733 ( p_eco_impl IN BOOLEAN )
734 IS
735 BEGIN
736 G_System_Information.eco_impl := p_eco_impl;
737
738 END Set_Eco_Impl;
739
740 /***************************************************************************
741 * Function : Is_Eco_Impl
742 * Returns : BOOLEAN
743 * Parameters IN : None
744 * Parameters OUT: None
745 * Purpose : Function will true or false value of the system information
746 * record's attribute Eco_Impl. True if ECO is implemented and
747 * false otherwise.
748 *****************************************************************************/
749 FUNCTION Is_Eco_Impl RETURN BOOLEAN
750 IS
751 BEGIN
752 RETURN G_System_Information.eco_impl;
753
754 END Is_Eco_Impl;
755
756 /***************************************************************************
757 * Procedure : Set_Eco_Cancl
758 * Returns : None
759 * Parameters IN : p_eco_cancl
760 * Parameters OUT: None
761 * Purpose : Procedure will set the value of the system information
762 * record attribute, Eco_Cancl. True if the Eco is canceled
763 * and false otherwise.
764 *****************************************************************************/
765 PROCEDURE Set_Eco_Cancl
766 ( p_eco_cancl IN BOOLEAN )
767 IS
768 BEGIN
769 G_System_Information.eco_cancl := p_eco_cancl;
770
771 END Set_Eco_Cancl;
772
773 /***************************************************************************
774 * Function : Is_Eco_Cancl
775 * Returns : BOOLEAN
776 * Parameters IN : None
777 * Parameters OUT: None
778 * Purpose : Function will return true or false value of the system
779 * information record's attribute Eco_Cancl.
780 *****************************************************************************/
781 FUNCTION Is_Eco_Cancl RETURN BOOLEAN
782 IS
783 BEGIN
784 RETURN G_System_Information.eco_cancl;
785
786 END Is_Eco_Cancl;
787
788
789 /***************************************************************************
790 * Procedure : Set_Wkfl_Process
791 * Returns : None
792 * Parameters IN : p_wkfl_process
793 * Parameters OUT: None
794 * Purpose : Procedure will set a true or false value in the attribute
795 * WKFL_Process of the system information record.
796 *****************************************************************************/
797 PROCEDURE Set_Wkfl_Process
798 ( p_wkfl_process IN BOOLEAN )
799 IS
800 BEGIN
801 G_System_Information.wkfl_process := p_wkfl_process;
802
803 END Set_Wkfl_Process;
804
805 /***************************************************************************
806 * Function : Is_Wkfl_Process
807 * Returns : BOOLEAN
808 * Parameters IN : None
809 * Parameters OUT: None
810 * Purpose : Function will return the value of the system information
811 * record attribute Wkfl_Process. True if a Workflow process
812 * exists the ECO and false otherwise.
813 *****************************************************************************/
814 FUNCTION Is_Wkfl_Process RETURN BOOLEAN
815 IS
816 BEGIN
817 RETURN G_System_Information.wkfl_process;
818
819 END Is_Wkfl_Process;
820
821
822 /***************************************************************************
826 * Parameters OUT: None
823 * Procedure : Set_Eco_Access
824 * Returns : None
825 * Parameters IN : p_eco_access
827 * Purpose : Procedure will set the value of the system information record
828 * attribute Eco_Access. True if the user has access to the ECO
829 * and false otherwise.
830 *****************************************************************************/
831 PROCEDURE Set_Eco_Access
832 ( p_eco_access IN BOOLEAN )
833 IS
834 BEGIN
835 G_System_Information.eco_access := p_eco_access;
836
837 END Set_Eco_Access;
838
839 /***************************************************************************
840 * Function : Is_Eco_Access
841 * Returns : BOOLEAN
842 * Parameters IN : None
843 * Parameters OUT: None
844 * Purpose : Function will return true if the Eco_Access is True and
845 * false otherwise.
846 *****************************************************************************/
847 FUNCTION Is_Eco_Access RETURN BOOLEAN
848 IS
849 BEGIN
850 RETURN G_System_Information.eco_access;
851
852 END Is_Eco_Access;
853
854 /***************************************************************************
855 * Procedure : Set_RItem_Impl
856 * Returns : None
857 * Parameters IN : p_ritem_impl
858 * Parameters OUT: None
859 * Purpose : Procedure will set the value of system iformation record
860 * attribute RItem_Impl.
861 *****************************************************************************/
862 PROCEDURE Set_RItem_Impl
863 ( p_ritem_impl IN BOOLEAN )
864 IS
865 BEGIN
866 G_System_Information.ritem_impl := p_ritem_impl;
867
868 END Set_RItem_Impl;
869
870 /***************************************************************************
871 * Function : Is_RItem_Impl
872 * Returns : BOOLEAN
873 * Parameters IN : None
874 * Parameters OUT: None
875 * Purpose : Function will answer true or false to the question
876 * Is Revised Item Implemented ?
877 *****************************************************************************/
878 FUNCTION Is_RItem_Impl RETURN BOOLEAN
879 IS
880 BEGIN
881 RETURN G_System_Information.RItem_Impl;
882
883 END Is_RItem_Impl;
884
885 /***************************************************************************
886 * Procedure : Set_RItem_Cancl
887 * Returns : None
888 * Parameters IN : p_ritem_cancl
889 * Parameters OUT: None
890 * Purpose : Procedure will set the value of system information record
891 * attribute RItem_cancl.
892 *****************************************************************************/
893 PROCEDURE Set_RItem_Cancl
894 ( p_ritem_cancl IN BOOLEAN )
895 IS
896 BEGIN
897 G_System_Information.ritem_cancl := p_ritem_cancl;
898
899 END Set_RItem_Cancl;
900
901 /***************************************************************************
902 * Function : Is_RItem_Cancl
903 * Returns : BOOLEAN
904 * Parameters IN : None
905 * Parameters OUT: None
906 * Purpose : Function will answer true or false to the question
907 * Is Revised Item Canceled?
908 *****************************************************************************/
909 FUNCTION Is_RItem_Cancl RETURN BOOLEAN
910 IS
911 BEGIN
912 RETURN G_System_Information.ritem_cancl;
913
914 END Is_RItem_Cancl;
915
916 /***************************************************************************
917 * Procedure : Set_RComp_Cancl
918 * Returns : None
919 * Parameters IN : p_Comp_Cancl
920 * Parameters OUT: None
921 * Purpose : Procedure will set the value of system iformation record
922 * attribute RComp_Cancl.
923 *****************************************************************************/
924 PROCEDURE Set_RComp_Cancl
925 ( p_rcomp_cancl IN BOOLEAN )
926 IS
927 BEGIN
928 G_System_Information.rcomp_cancl := p_rcomp_cancl;
929
930 END Set_RComp_Cancl;
931
932 /***************************************************************************
933 * Function : Is_RComp_Cancl
934 * Returns : BOOLEAN
935 * Parameters IN : None
936 * Parameters OUT: None
937 * Purpose : Function will answer true or false to the question
938 * Is Revised Revised Component canceled ?
939 *****************************************************************************/
940 FUNCTION Is_RComp_Cancl RETURN BOOLEAN
941 IS
942 BEGIN
943 RETURN G_System_Information.rcomp_cancl;
944
945 END Is_rcomp_cancl;
946
947 /***************************************************************************
948 * Procedure : Set_Std_Item_Access
949 * Returns : None
950 * Parameters IN : p_std_item_access
951 * Parameters OUT: None
952 * Purpose : Will set the value of the attribute STD_Item_Access in the
953 * system information record.
954 *****************************************************************************/
955 PROCEDURE Set_Std_Item_Access
956 ( p_std_item_access IN NUMBER )
957 IS
958 BEGIN
959 G_System_Information.std_item_access := p_std_item_access;
960
961 END Set_Std_Item_Access;
962
966 * Parameters IN : None
963 /**************************************************************************
964 * Function : Get_Std_Item_Access
965 * Returns : NUMBER
967 * Parameters OUT: None
968 * Purpose : Will return the value of the Standard Item Access attribute
969 * Std_Item_Access from the system information record.
970 ***************************************************************************/
971 FUNCTION Get_Std_Item_Access RETURN NUMBER
972 IS
973 BEGIN
974 RETURN G_System_Information.std_item_access;
975
976 END Get_Std_Item_Access;
977
978 /***************************************************************************
979 * Procedure : Set_Mdl_Item_Access
980 * Returns : None
981 * Parameters IN : p_Mdl_item_access
982 * Parameters OUT: None
983 * Purpose : Will set the value of the attribute Mdl_Item_Access in the
984 * system information record.
985 *****************************************************************************/
986 PROCEDURE Set_Mdl_Item_Access
987 ( p_mdl_item_access IN NUMBER )
988 IS
989 BEGIN
990 G_System_Information.mdl_item_access := p_mdl_item_access;
991
992 END Set_Mdl_Item_Access;
993
994 /**************************************************************************
995 * Function : Get_Mdl_Item_Access
996 * Returns : NUMBER
997 * Parameters IN : None
998 * Parameters OUT: None
999 * Purpose : Will return the value of the Model Item Access attribute
1000 * Mdl_Item_Access from the system information record.
1001 ***************************************************************************/
1002 FUNCTION Get_Mdl_Item_Access RETURN NUMBER
1003 IS
1004 BEGIN
1005 RETURN G_System_Information.mdl_item_access;
1006
1007 END Get_Mdl_Item_Access;
1008
1009
1010 /***************************************************************************
1011 * Procedure : Set_Pln_Item_Access
1012 * Returns : None
1013 * Parameters IN : p_Pln_item_access
1014 * Parameters OUT: None
1015 * Purpose : Will set the value of the attribute Pln_Item_Access in the
1016 * system information record.
1017 *****************************************************************************/
1018 PROCEDURE Set_Pln_Item_Access
1019 ( p_Pln_item_access IN NUMBER )
1020 IS
1021 BEGIN
1022 G_System_Information.Pln_item_access := p_Pln_item_access;
1023
1024 END Set_Pln_Item_Access;
1025
1026 /**************************************************************************
1027 * Function : Get_Pln_Item_Access
1028 * Returns : NUMBER
1029 * Parameters IN : None
1030 * Parameters OUT: None
1031 * Purpose : Will return the value of the Planning Item Access attribute
1032 * Pln_Item_Access from the system information record.
1033 ***************************************************************************/
1034 FUNCTION Get_Pln_Item_Access RETURN NUMBER
1035 IS
1036 BEGIN
1037 RETURN G_System_Information.Pln_item_access;
1038
1039 END Get_Pln_Item_Access;
1040
1041 /***************************************************************************
1042 * Procedure : Set_OC_Item_Access
1043 * Returns : None
1044 * Parameters IN : p_OC_item_access
1045 * Parameters OUT: None
1046 * Purpose : Will set the value of the attribute OC_Item_Access in the
1047 * system information record.
1048 *****************************************************************************/
1049 PROCEDURE Set_OC_Item_Access
1050 ( p_oc_item_access IN NUMBER )
1051 IS
1052 BEGIN
1053 G_System_Information.oc_item_access := p_oc_item_access;
1054
1055 END Set_OC_Item_Access;
1056
1057 /**************************************************************************
1058 * Function : Get_OC_Item_Access
1059 * Returns : NUMBER
1060 * Parameters IN : None
1061 * Parameters OUT: None
1062 * Purpose : Will return value of the Option Class Item Access attribute
1063 * OC_Item_Access from the system information record.
1064 ***************************************************************************/
1065 FUNCTION Get_OC_Item_Access RETURN NUMBER
1066 IS
1067 BEGIN
1068 RETURN G_System_Information.oc_item_access;
1069
1070 END Get_OC_Item_Access;
1071
1072 /***************************************************************************
1073 * Procedure : Set_Unit_Effectivity
1074 * Returns : None
1075 * Parameters IN : p_Unit_Effectivity
1076 * Parameters OUT: None
1077 * Purpose : Will set the value of the attribute Unit_Effectivity in the
1078 * system information record.
1079 *****************************************************************************/
1080 PROCEDURE Set_Unit_Effectivity
1081 ( p_Unit_Effectivity IN BOOLEAN )
1082 IS
1083 BEGIN
1084 G_System_Information.unit_effectivity := p_unit_effectivity;
1085
1086 END Set_Unit_Effectivity;
1087
1088 /**************************************************************************
1089 * Function : Get_Unit_Effectivity
1090 * Returns : NUMBER
1091 * Parameters IN : None
1092 * Parameters OUT: None
1093 * Purpose : Will return value of the unit effective item attribute
1094 * Unit_Effectivity from the system information record.
1095 ***************************************************************************/
1096 FUNCTION Get_Unit_Effectivity RETURN BOOLEAN
1097 IS
1098 BEGIN
1102
1099 RETURN G_System_Information.Unit_Effectivity;
1100
1101 END Get_Unit_Effectivity;
1103 /***************************************************************************
1104 * Procedure : Set_Unit_Controlled_Item
1105 * Returns : None
1106 * Parameters IN : p_Unit_Controlled_Item
1107 * Parameters OUT: None
1108 * Purpose : Will set the value of the attribute Unit_Controlled_Item in
1109 * system information record.
1110 *****************************************************************************/
1111 PROCEDURE Set_Unit_Controlled_Item
1112 ( p_Unit_Controlled_Item IN BOOLEAN)
1113 IS
1114 BEGIN
1115 G_System_Information.unit_controlled_item := p_unit_controlled_item;
1116
1117 END Set_Unit_Controlled_Item;
1118
1119 PROCEDURE Set_Unit_Controlled_Item
1120 ( p_inventory_item_id IN NUMBER
1121 , p_organization_id IN NUMBER
1122 )
1123 IS
1124 Cursor Unit_Controlled_Item IS
1125 SELECT effectivity_control
1126 FROM mtl_system_items
1127 WHERE inventory_item_id = p_inventory_item_id
1128 AND organization_id = p_organization_id;
1129 BEGIN
1130 FOR Unit_Cont_Item IN Unit_Controlled_Item
1131 LOOP
1132 IF Unit_Cont_Item.Effectivity_Control = 2
1133 THEN
1134 G_System_Information.unit_controlled_item := TRUE;
1135 ELSIF Unit_Cont_Item.Effectivity_Control = 1
1136 THEN
1137 G_System_Information.unit_controlled_item := FALSE;
1138 END IF;
1139 END LOOP;
1140 END Set_Unit_Controlled_Item;
1141
1142 /**************************************************************************
1143 * Function : Get_Unit_Controlled_Item
1144 * Returns : NUMBER
1145 * Parameters IN : None
1146 * Parameters OUT: None
1147 * Purpose : Will return value of the unit effective item attribute
1148 * Unit_Controlled_Item from the system information record.
1149 ***************************************************************************/
1150 FUNCTION Get_Unit_Controlled_Item RETURN BOOLEAN
1151 IS
1152 BEGIN
1153 RETURN G_System_Information.Unit_Controlled_Item;
1154
1155 END Get_Unit_Controlled_Item;
1156
1157 /***************************************************************************
1158 * Procedure : Set_Unit_Controlled_Component
1159 * Returns : None
1160 * Parameters IN : p_Unit_Controlled_Component
1161 * Parameters OUT: None
1162 * Purpose : Will set the value of the attribute Unit_Controlled_Component
1163 * in the system information record.
1164 *****************************************************************************/
1165 PROCEDURE Set_Unit_Controlled_Component
1166 ( p_Unit_Controlled_Component IN BOOLEAN)
1167 IS
1168 BEGIN
1169 G_System_Information.unit_controlled_component
1170 := p_unit_controlled_component;
1171
1172 END Set_Unit_Controlled_Component;
1173
1174 PROCEDURE Set_Unit_Controlled_Component
1175 ( p_inventory_item_id IN NUMBER
1176 , p_organization_id IN NUMBER
1177 )
1178 IS
1179 Cursor Unit_Controlled_Item IS
1180 SELECT effectivity_control
1181 FROM mtl_system_items
1182 WHERE inventory_item_id = p_inventory_item_id
1183 AND organization_id = p_organization_id;
1184 BEGIN
1185 FOR Unit_Cont_Item IN Unit_Controlled_Item
1186 LOOP
1187 IF Unit_Cont_Item.Effectivity_Control = 2
1188 THEN
1189 G_System_Information.unit_controlled_component := TRUE;
1190 ELSIF Unit_Cont_Item.Effectivity_Control = 1
1191 THEN
1192 G_System_Information.unit_controlled_component := FALSE;
1193 END IF;
1194 END LOOP;
1195 END Set_Unit_Controlled_Component;
1196
1197 /**************************************************************************
1198 * Function : Get_Unit_Controlled_Component
1199 * Returns : NUMBER
1200 * Parameters IN : None
1201 * Parameters OUT: None
1202 * Purpose : Will return value of the unit effective component attribute
1203 * Unit_Controlled_Component from the system information record.
1204 ***************************************************************************/
1205 FUNCTION Get_Unit_Controlled_Component RETURN BOOLEAN
1206 IS
1207 BEGIN
1208 RETURN G_System_Information.Unit_Controlled_Component;
1209
1210 END Get_Unit_Controlled_Component;
1211
1212 /***************************************************************************
1213 * Procedure : Set_Require_Item_Rev
1214 * Returns : None
1215 * Parameters IN : p_Require_Rev
1216 * Parameters OUT: None
1217 * Purpose : Will set the value of the attribute Require_Item_Rev
1218 * in the system information record.
1219 *****************************************************************************/
1220 PROCEDURE Set_Require_Item_Rev
1221 ( p_Require_Rev IN NUMBER )
1222 IS
1223 BEGIN
1224 G_System_Information.Require_Item_Rev
1225 := p_Require_Rev;
1226 END Set_Require_Item_Rev;
1227
1228 /**************************************************************************
1229 * Function : Is_Item_Rev_Required
1230 * Returns : NUMBER
1231 * Parameters IN : None
1232 * Parameters OUT: None
1236 FUNCTION Is_Item_Rev_Required RETURN NUMBER
1233 * Purpose : Will return value of the Require_Item_Rev attribute
1234 * from the system information record.
1235 ***************************************************************************/
1237 IS
1238 BEGIN
1239 RETURN G_System_Information.Require_Item_Rev;
1240
1241 END Is_Item_Rev_Required;
1242
1243 /***************************************************************************
1244 * Procedure : Set_Current_Revision
1245 * Returns : None
1246 * Parameters IN : p_current_revision
1247 * Parameters OUT: None
1248 * Purpose : Procedure will set the current revision attribute of the
1249 * system information record.
1250 *****************************************************************************/
1251 PROCEDURE Set_Current_Revision
1252 ( p_current_revision IN VARCHAR2 )
1253 IS
1254 BEGIN
1255 G_System_Information.current_revision := p_current_revision;
1256
1257 END Set_Current_Revision;
1258
1259 /***************************************************************************
1260 * Function : Get_Current_Revision
1261 * Returns : VARCHAR2(3)
1262 * Parameters IN : None
1263 * Parameters OUT: None
1264 * Purpose : Function will return the value of current revision attribute
1265 * of the system information record.
1266 *****************************************************************************/
1267 FUNCTION Get_Current_Revision RETURN VARCHAR2
1268 IS
1269 BEGIN
1270 RETURN G_System_Information.current_revision;
1271
1272 END Get_Current_Revision;
1273
1274 /***************************************************************************
1275 * Procedure : Set_BO_Identifier
1276 * Returns : None
1277 * Parameters IN : p_bo_identifier
1278 * Parameters OUT: None
1279 * Purpose : Procedure will set the Business object identifier attribute
1280 * BO_Identifier of the system information record.
1281 *****************************************************************************/
1282 PROCEDURE Set_BO_Identifier
1283 ( p_bo_identifier IN VARCHAR2 )
1284 IS
1285 BEGIN
1286 G_System_Information.bo_identifier := p_bo_identifier;
1287 Error_Handler.Set_Bo_Identifier(p_bo_identifier);
1288
1289 END Set_BO_Identifier;
1290
1291 /***************************************************************************
1292 * Function : Get_BO_Identifier
1293 * Returns : VARCHAR2
1294 * Parameters IN : None
1295 * Parameters OUT: None
1296 * Purpose : Function will return the value of the business object
1297 * identifier attribute BO_Identifier from the system
1298 * information record.
1299 *****************************************************************************/
1300 FUNCTION Get_BO_Identifier RETURN VARCHAR2
1301 IS
1302 BEGIN
1303 RETURN G_System_Information.bo_identifier;
1304
1305 END Get_BO_Identifier;
1306
1307 /**************************************************************************
1308 * Procedure : Transaction_Type_Validity
1309 * Parameters IN : Transaction Type
1310 * Entity Name
1311 * Entity ID, so that it can be used in a meaningful message
1312 * Parameters OUT: Valid flag
1313 * Message Token Table
1314 * Purpose : This procedure will check if the transaction type is valid
1315 * for a particular entity.
1316 **************************************************************************/
1317 PROCEDURE Transaction_Type_Validity
1318 ( p_transaction_type IN VARCHAR2
1319 , p_entity IN VARCHAR2
1320 , p_entity_id IN VARCHAR2
1321 , x_valid IN OUT NOCOPY BOOLEAN
1322 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1323 )
1324 IS
1325 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1326 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1327 BEGIN
1328 l_token_tbl(1).token_name := 'ENTITY_ID';
1329 l_token_tbl(1).token_value := p_entity_id;
1330
1331 x_valid := TRUE;
1332
1333 /* Introducing a new transaction type 'SYNC' to support BOM creation
1334 from WIP (EAM) */
1335
1336 IF p_transaction_type = 'SYNC' THEN
1337 x_mesg_token_tbl := l_mesg_token_tbl;
1338 RETURN;
1339 END IF;
1340
1341 IF (p_entity IN ('Bom_Header','Bom_Comps','Bom_Ref_Desgs','Bom_Sub_Comps','Bom_Comp_Ops')
1342 AND
1343 NVL(p_transaction_type, FND_API.G_MISS_CHAR)
1344 NOT IN ('CREATE', 'UPDATE', 'DELETE')
1345 )
1346 OR
1347 ( p_entity ='Bom_Rev' AND
1348 NVL(p_transaction_type, FND_API.G_MISS_CHAR)
1349 NOT IN ('CREATE', 'UPDATE')
1350 )
1351 THEN
1352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1353 THEN
1354 IF p_entity = 'Bom_Header'
1355 THEN
1356 Error_Handler.Add_Error_Token
1357 ( p_Message_Name => 'BOM_HEADER_TRANS_TYPE_INVALID'
1358 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1359 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1360 );
1361 ELSIF p_entity = 'Bom_Rev'
1362 THEN
1363 Error_Handler.Add_Error_Token
1364 ( p_Message_Name => 'BOM_REV_TRANS_TYPE_INVALID'
1368 ELSIF p_entity = 'Bom_Comps'
1365 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1366 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1367 );
1369 THEN
1370 Error_Handler.Add_Error_Token
1371 ( p_Message_Name => 'BOM_CMP_TRANS_TYPE_INVALID'
1372 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1373 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1374 );
1375 ELSIF p_entity = 'Bom_Ref_Desgs'
1376 THEN
1377 Error_Handler.Add_Error_Token
1378 ( p_Message_Name => 'BOM_RFD_TRANS_TYPE_INVALID'
1379 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1380 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1381 );
1382 ELSIF p_entity = 'Bom_Sub_Comps'
1383 THEN
1384 Error_Handler.Add_Error_Token
1385 ( p_Message_Name => 'BOM_SBC_TRANS_TYPE_INVALID'
1386 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1387 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1388 );
1389 ELSIF p_entity = 'Bom_Comp_Ops'
1390 THEN
1391 Error_Handler.Add_Error_Token
1392 ( p_Message_Name => 'BOM_COPS_TRANS_TYPE_INVALID'
1393 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1394 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1395 );
1396 END IF;
1397 END IF;
1398
1399 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1400 x_valid := FALSE;
1401 END IF;
1402
1403 END Transaction_Type_Validity;
1404
1405 PROCEDURE Set_Debug
1406 ( p_debug_flag IN VARCHAR2 )
1407 IS
1408 BEGIN
1409 G_System_Information.debug_flag := p_debug_flag;
1410 Error_Handler.Set_Debug(p_debug_flag => p_debug_flag);
1411 END Set_Debug;
1412
1413 FUNCTION Get_Debug RETURN VARCHAR2
1414 IS
1415 BEGIN
1416 RETURN G_System_Information.debug_flag;
1417 -- RETURN Error_Handler.Get_Debug;
1418 END;
1419
1420 PROCEDURE Set_Assembly_Item_Id
1421 ( p_assembly_item_id IN NUMBER )
1422 IS
1423 BEGIN
1424 G_System_Information.assembly_item_id := p_assembly_item_id;
1425 END Set_Assembly_Item_Id;
1426
1427 FUNCTION Get_Assembly_Item_Id RETURN NUMBER IS
1428 BEGIN
1429 RETURN G_System_Information.assembly_item_id;
1430 END Get_Assembly_Item_Id;
1431
1432 /***************************************************************************
1433 * Function : Set_Caller_Type
1434 * Returns : None
1435 * Parameters IN : p_caller_type
1436 * Parameters OUT: None
1437 * Purpose : Procedure will set the value of Caller_type in
1438 * G_Control_Rec
1439 *****************************************************************************/
1440 Procedure Set_Caller_Type
1441 ( p_caller_type IN VARCHAR2)
1442 IS
1443 BEGIN
1444 G_Control_Rec.Caller_Type := p_caller_type;
1445
1446 End Set_Caller_Type;
1447
1448 /***************************************************************************
1449 * Function : Get_Caller_Type
1450 * Returns : Caller type(VARCHAR2)
1451 * Parameters IN : None
1452 * Parameters OUT: None
1453 * Purpose : Function will return the value of Caller_type in
1454 * G_Control_Rec
1455 *****************************************************************************/
1456 Function Get_Caller_Type RETURN VARCHAR2
1457 IS
1458 BEGIN
1459 RETURN G_Control_Rec.Caller_Type;
1460 End Get_Caller_Type;
1461
1462 FUNCTION RETRIEVE_MESSAGE(
1463 p_application_id IN VARCHAR2
1464 , p_message_name IN VARCHAR2
1465 ) RETURN VARCHAR2
1466 IS
1467 BEGIN
1468 Fnd_Message.Set_Name ( application => p_application_id,
1469 name => p_message_name
1470 );
1471 return Fnd_Message.Get;
1472 END RETRIEVE_MESSAGE;
1473
1474
1475 /* Bug 5737158
1476 **************************************************************************
1477 * Function : Get_Concat_Segs
1478 * Returns : The concatenated item segments
1479 * Parameters IN : Item_id , Organization_id
1480 * Parameters OUT: None
1481 * Purpose : Can be used in views to get the concatenated item segments.
1482 * Non-displayed segments would not be returned.
1483 ****************************************************************************/
1484 FUNCTION Get_Concat_Segs(p_item_id IN NUMBER,
1485 p_org_id IN NUMBER )
1486 RETURN VARCHAR2
1487 IS
1488 l_get_flex BOOLEAN;
1489 l_no_segments NUMBER ;
1490 l_value varchar2(2000);
1491 l_segments fnd_flex_ext.SegmentArray;
1492 BEGIN
1493 l_get_flex := fnd_flex_ext.get_segments('INV','MSTK',101,p_item_id,l_no_segments,l_segments,p_org_id);
1494 if (l_get_flex) then
1495 l_value := fnd_flex_ext.concatenate_segments(l_no_segments,l_segments,fnd_flex_ext.get_delimiter('INV','MSTK',101));
1496 end if;
1497
1501 return null;
1498 return l_value;
1499 EXCEPTION
1500 WHEN OTHERS THEN
1502 END Get_Concat_Segs;
1503
1504 /***************************************************************************
1505 * Function : Get_Alternate
1506 * Returns : alternate_bom_designator type(VARCHAR2)
1507 * Parameters IN : p_bill_sequence_id type(NUMBER)
1508 * Parameters OUT: None
1509 * Purpose : Function will return the Alternate BOM Designatore
1510 *****************************************************************************/
1511
1512 FUNCTION Get_Alternate
1513 (p_bill_sequence_id NUMBER)
1514 RETURN VARCHAR2
1515 IS
1516 cursor c_alternate IS
1517 SELECT alternate_bom_designator
1518 FROM bom_structures_b
1519 WHERE bill_sequence_id = p_bill_sequence_id;
1520 BEGIN
1521 for alternate in c_alternate
1522 loop
1523 if (alternate.alternate_bom_designator IS NULL)
1524 then
1525 return RETRIEVE_MESSAGE('BOM','BOM_PRIMARY');
1526
1527 else
1528 return alternate.alternate_bom_designator;
1529 end if;
1530 end loop;
1531
1532 return null;
1533 END;
1534
1535
1536 /***************************************************************************
1537 * Function : Get_Structure_Type
1538 * Returns : display_name type(VARCHAR2)
1539 * Parameters IN : p_bill_sequence_id type(NUMBER)
1540 * p_organization_id type(NUMBER)
1541 * Parameters OUT: None
1542 * Purpose : Function will return the display name For the Structure Type
1543 *****************************************************************************/
1544
1545 FUNCTION Get_Structure_Type
1546 ( p_bill_sequence_id IN NUMBER
1547 , p_organization_id IN NUMBER
1548 )
1549 RETURN VARCHAR2
1550 IS
1551 CURSOR c_structure_type(l_bill_sequence_id NUMBER
1552 ) IS
1553 SELECT display_name
1554 FROM bom_structure_types_vl st
1555 , bom_structures_b bsb
1556 WHERE bsb.bill_sequence_id = l_bill_sequence_id
1557 and bsb.structure_type_id = st.structure_type_id;
1558
1559 BEGIN
1560 for structure_type in c_structure_type( l_bill_sequence_id => p_bill_sequence_id)
1561 loop
1562 return structure_type.display_name;
1563 end loop;
1564
1565 RETURN null;
1566
1567 END;
1568
1569 /***************************************************************************
1570 * Function : get_item_type
1571 * Returns : meaning type(VARCHAR2)
1572 * Parameters IN : p_item_type type(VARCHAR2)
1573 * Parameters OUT: None
1574 * Purpose : Function will return the Item Type Meaning
1575 *****************************************************************************/
1576
1577 FUNCTION get_item_type
1578 ( p_item_type IN VARCHAR2)
1579 RETURN VARCHAR2
1580 IS
1581 cursor c_item_type is
1582 select meaning
1583 from fnd_lookup_values
1584 where LOOKUP_CODE = p_ITEM_TYPE
1585 AND LOOKUP_TYPE = 'ITEM_TYPE'
1586 AND LANGUAGE = USERENV('LANG');
1587 BEGIN
1588 if p_item_type is null
1589 then
1590 return null;
1591 end if;
1592
1593 for item_type in c_item_type
1594 loop
1595 return item_type.meaning;
1596 end loop;
1597
1598 return null;
1599 END;
1600
1601 FUNCTION get_reference_designators
1602 ( p_component_sequence_id IN NUMBER
1603 ) return VARCHAR2
1604 IS
1605 cursor c_ref_desg ( p_component_seq IN NUMBER)
1606 IS
1607 SELECT component_reference_designator
1608 FROM bom_reference_designators rd
1609 , bom_components_b comp
1610 WHERE comp.component_sequence_id = p_component_seq
1611 AND rd.component_sequence_id = comp.component_sequence_id
1612 AND ( (comp.implementation_date IS NULL AND
1613 rd.change_notice = comp.change_notice
1614 ) OR
1615 (comp.implementation_date is NOT NULL AND
1616 (rd.acd_type IS NULL OR rd.acd_type <> 3)
1617 )
1618 )
1619 order by 1 DESC;
1620
1621 --l_ref_desg VARCHAR2(32000);
1622 l_ref_desg VARCHAR2(4000);
1623 l_length NUMBER;
1624 BEGIN
1625 l_ref_desg := null;
1626 l_length := 0;
1627 FOR ref_desg IN c_ref_desg(p_component_seq => p_component_sequence_id)
1628 LOOP
1629 l_length := l_length + length(ref_desg.component_reference_designator) + 2;
1630 IF(l_length < 3998) THEN
1631 l_ref_desg := ref_desg.component_reference_designator || ', ' || l_ref_desg;
1632 ELSE
1633 EXIT;
1634 END IF;
1635 END LOOP;
1636 if (l_ref_desg is not null) then
1637 l_ref_desg := substr(l_ref_desg, 0, length(l_ref_desg) - 2);
1638 end if;
1639 IF(l_length >= 3998) THEN
1640 l_ref_desg := l_ref_desg || ', ...';
1641 END IF;
1642 return l_ref_desg;
1643
1644 END get_reference_designators;
1645
1646
1647 FUNCTION Get_Item_Name(p_item_id IN NUMBER,p_org_id IN NUMBER)
1648 RETURN VARCHAR2
1649 IS
1650 l_item_name MTL_SYSTEM_ITEMS_KFV.concatenated_segments%TYPE;
1651 BEGIN
1652 SELECT concatenated_segments
1653 INTO l_item_name
1657 RETURN l_item_name;
1654 FROM mtl_system_items_kfv
1655 WHERE inventory_item_id = p_item_id
1656 AND organization_id = p_org_id;
1658 EXCEPTION
1659 WHEN NO_DATA_FOUND THEN
1660 return null;
1661 END Get_Item_Name;
1662
1663 PROCEDURE Get_Orgs(p_org_hier IN VARCHAR2 , p_organization_id IN NUMBER, x_org_list IN OUT NOCOPY BOM_GLOBALS.OrgID_tbl_type )
1664 IS
1665 t_org_code_list Inv_Orghierarchy_Pvt.OrgID_tbl_type;
1666 starting_org_counter NUMBER ;
1667 I NUMBER := 1 ;
1668 N NUMBER := 0 ;
1669 err_msg VARCHAR2(200);
1670 BEGIN
1671 Inv_Orghierarchy_Pvt.ORG_HIERARCHY_LIST(p_org_hier,p_organization_id,t_org_code_list) ;
1672 starting_org_counter := 1;
1673 FOR I IN starting_org_counter..t_org_code_list.COUNT LOOP
1674 N:=N+1;
1675 x_org_list(N) := t_org_code_list(I);
1676 END LOOP;
1677 END Get_Orgs;
1678
1679 PROCEDURE Set_Validate_For_Plm
1680 ( p_validate_for_plm_flag IN VARCHAR2 )
1681 IS
1682 BEGIN
1683 G_System_Information.validate_for_plm := p_validate_for_plm_flag;
1684 END Set_Validate_For_Plm;
1685
1686 FUNCTION Get_Validate_For_Plm RETURN VARCHAR2
1687 IS
1688 BEGIN
1689 RETURN G_System_Information.validate_for_plm;
1690 END;
1691
1692
1693 --
1694 /* Procedure to default the New Structure Revision Attributes */
1695 PROCEDURE GET_DEF_REV_ATTRS
1696 ( p_bill_sequence_id IN NUMBER
1697 , p_comp_item_id IN NUMBER
1698 , p_effectivity_date IN DATE
1699 , x_object_revision_id OUT NOCOPY VARCHAR2
1700 , x_minor_revision_id OUT NOCOPY VARCHAR2
1701 , x_comp_revision_id OUT NOCOPY VARCHAR2
1702 , x_comp_minor_revision_id OUT NOCOPY VARCHAR2
1703 )
1704 IS
1705
1706 stmt1 LONG;
1707 stmt2 LONG;
1708 x_install_ego boolean;
1709 x_status VARCHAR2(1);
1710 x_industry VARCHAR2(1);
1711 x_schema VARCHAR2(30);
1712 object_type VARCHAR2(20);
1713
1714 CURSOR
1715 get_ass_current_rev
1716 (p_bill_sequence_id in Number,
1717 p_effectivity_date in Date )
1718 IS
1719 SELECT
1720 revision_id
1721 FROM
1722 mtl_item_revisions_B mir,
1723 bom_bill_of_materials bom
1724 WHERE
1725 mir.inventory_item_id = bom.assembly_item_id
1726 AND mir.organization_id = bom.organization_id
1727 AND bom.bill_sequence_id = p_bill_sequence_id
1728 AND effectivity_date =
1729 (SELECT max(mir1.effectivity_date)
1730 FROM mtl_item_revisions_b mir1
1731 WHERE mir1.inventory_item_id = mir.inventory_item_id
1732 AND mir1.organization_id = mir.organization_id
1733 AND mir1.effectivity_date <= p_effectivity_date
1734 AND ROWNUM = 1);
1735
1736 CURSOR
1737 get_comp_current_rev
1738 (p_bill_sequence_id Number,
1739 p_component_item_id Number,
1740 p_effectivity_date Date
1741 )
1742 IS
1743 SELECT
1744 revision_id
1745 FROM
1746 mtl_item_revisions_B mir,
1747 bom_bill_of_materials bom
1748 WHERE
1749 mir.inventory_item_id = p_component_item_id
1750 AND mir.organization_id = bom.organization_id
1751 AND bom.bill_sequence_id = p_bill_sequence_id
1752 AND effectivity_date = (SELECT max(mir1.effectivity_date)
1753 FROM mtl_item_revisions_b mir1
1754 WHERE mir1.inventory_item_id = mir.inventory_item_id
1755 AND mir1.organization_id = mir.organization_id
1756 AND mir1.effectivity_date <= p_effectivity_date
1757 AND ROWNUM = 1);
1758
1759
1760 BEGIN
1761 object_type := 'EGO_ITEM_REVISION';
1762 x_minor_revision_id := 0;
1763 x_comp_minor_revision_id :=0;
1764 x_install_ego := Fnd_Installation.Get_App_Info
1765 (application_short_name => 'EGO',
1766 status => x_status,
1767 industry => x_industry,
1768 oracle_schema => x_schema);
1769
1770
1771
1772 FOR c_obj_rev IN get_ass_current_rev
1773 ( p_bill_sequence_id => p_bill_sequence_id
1774 ,p_effectivity_date => p_effectivity_date)
1775 LOOP
1776 x_object_revision_id := c_obj_rev.revision_id;
1777
1778 IF (x_status = 'I' ) then
1779 stmt2 := ' SELECT '||
1780 ' nvl(max(minor_revision_id),0) minor_revision_id '||
1781 ' FROM '||
1782 ' ego_minor_revisions emr, '||
1783 ' bom_bill_of_materials bom '||
1784 ' WHERE '||
1785 ' emr.pk1_value = to_char(bom.assembly_item_id) '||
1786 ' AND emr.pk2_value = to_char(bom.organization_id)' ||
1787 ' AND bom.bill_sequence_id = :bill_seq_id' ||
1788 ' and emr.pk3_value = :object_rev_id' ||
1789 ' and emr.obj_name = :object_type';
1790
1794 END LOOP;
1791 execute immediate stmt2 using p_bill_sequence_id, x_object_revision_id, object_type;-- into x_minor_revision_id;
1792 END IF;
1793
1795
1796 FOR c_comp_rev IN get_comp_current_rev
1797 ( p_bill_sequence_id => p_bill_sequence_id
1798 ,p_component_item_id => p_comp_item_id
1799 ,p_effectivity_date => p_effectivity_date)
1800 LOOP
1801 x_comp_revision_id := c_comp_rev.revision_id;
1802
1803 IF (x_status = 'I') then
1804 stmt1 := ' SELECT '||
1805 'nvl(max(minor_revision_id),0) minor_revision_id ' ||
1806 ' FROM '||
1807 ' ego_minor_revisions emr, '||
1808 ' bom_bill_of_materials bom '||
1809 ' WHERE '||
1810 ' emr.pk1_value = to_char(:comp_item_id) ' ||
1811 ' AND emr.pk2_value = to_char(bom.organization_id) '||
1812 ' AND bom.bill_sequence_id = :bill_seq_id ' ||
1813 ' and emr.pk3_value = :comp_rev_id' ||
1814 ' and emr.obj_name = :object_type';
1815
1816
1817 execute immediate stmt1 into x_comp_minor_revision_id using p_comp_item_id, p_bill_sequence_id, x_comp_revision_id, object_type;
1818 END IF;
1819
1820 END LOOP;
1821
1822 END;
1823
1824 /***************************************************************************
1825 * Function : Is_GTIN_Structure_Type_Id
1826 * Returns : TRUE / FALSE
1827 * Parameters IN : Structure Type Id
1828 * Parameters OUT: None
1829 * Purpose : Function will return TRUE if the given structure type is
1830 * 'Packaging Hierarchy' otherwise returns false
1831 *****************************************************************************/
1832 FUNCTION Is_GTIN_Structure_Type_Id (p_Structure_Type_Id IN NUMBER) RETURN BOOLEAN
1833 IS
1834 l_GTIN_Id NUMBER;
1835 BEGIN
1836 SELECT Structure_Type_Id
1837 INTO l_GTIN_Id
1838 FROM bom_structure_types_vl
1839 WHERE Structure_Type_Name ='Packaging Hierarchy'
1840 AND Structure_Type_Id = p_Structure_Type_Id;
1841
1842 RETURN TRUE;
1843
1844 EXCEPTION
1845 WHEN NO_DATA_FOUND THEN
1846 Return FALSE;
1847 END Is_GTIN_Structure_Type_Id;
1848
1849
1850 /***************************************************************************
1851 * Function : Get_Change_Policy_Val
1852 * Returns : Change Policy value
1853 * Parameters IN : Bill Sequence Id
1854 * Item's revision code
1855 * Parameters OUT: None
1856 * Purpose : Function will return the change policy value if there is any policy
1857 * otherwise it will return "ALLOWED"
1858 *****************************************************************************/
1859 FUNCTION Get_Change_Policy_Val (p_bill_seq_id IN NUMBER,
1860 p_item_revision_code IN VARCHAR2)
1861 RETURN VARCHAR2 IS
1862
1863 l_change_policy_char_val VARCHAR2(80);
1864 l_item_rev_code VARCHAR2(30);
1865 l_item_id NUMBER;
1866 l_org_id NUMBER;
1867
1868 BEGIN
1869
1870 l_change_policy_char_val := 'ALLOWED';
1871 l_item_rev_code := p_item_revision_code;
1872
1873 --If revision code is not passed then current revision will be queried.
1874 -- Commenting the revision related code, as structure change policies are not dependent on item's revision
1875 /*IF p_item_revision_code IS NULL THEN
1876 SELECT Assembly_Item_id, Organization_Id into l_item_id, l_org_id
1877 FROM Bom_Structures_b
1878 WHERE Bill_Sequence_Id = p_bill_seq_id;
1879 l_item_rev_code := BOM_REVISIONS.GET_ITEM_REVISION_FN('ALL', 'ALL', l_org_id, l_item_id, SYSDATE);
1880 END IF;
1881 */
1882 -- Getting the change policy value.
1883 SELECT
1884 ecp.policy_char_value INTO l_change_policy_char_val
1885 FROM
1886 MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP, Bom_Structures_b bsb
1887 WHERE
1888 ecp.policy_object_pk1_value =
1889 (SELECT TO_CHAR(ic.item_catalog_group_id)
1890 FROM mtl_item_catalog_groups_b ic
1891 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
1892 FROM EGO_OBJ_TYPE_LIFECYCLES olc
1893 WHERE olc.object_id = (SELECT OBJECT_ID
1894 FROM fnd_objects
1895 WHERE obj_name = 'EGO_ITEM')
1896 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
1897 AND olc.object_classification_code = ic.item_catalog_group_id
1898 )
1899 AND ROWNUM = 1
1900 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1901 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
1902 AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
1903 AND ecp.policy_object_pk3_value = ITEM_DTLS.current_phase_id
1904 AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
1905 AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
1906 AND ecp.attribute_code = 'STRUCTURE_TYPE'
1907 AND bsb.Structure_Type_id = ecp.attribute_number_value
1908 AND bsb.Assembly_item_id = ITEM_DTLS.inventory_item_id
1912 RETURN l_change_policy_char_val;
1909 AND bsb.organization_id = ITEM_DTLS.organization_id
1910 AND bsb.Bill_Sequence_id = p_bill_seq_id;
1911
1913
1914 EXCEPTION WHEN OTHERS THEN
1915
1916 RETURN l_change_policy_char_val;
1917
1918 END Get_Change_Policy_Val;
1919
1920
1921 /***************************************************************************
1922 * Function : Get_Change_Policy_Val
1923 * Returns : Change Policy value
1924 * Parameters IN : Inventory Item Id
1925 * Organization Id
1926 * Structure Type id
1927 * Parameters OUT: None
1928 * Purpose : Function will return the change policy value if there is any policy
1929 * otherwise it will return "ALLOWED". This is used to determine whether
1930 * structure header creation is allowed for the current item/str type.
1931 *****************************************************************************/
1932 FUNCTION Get_Change_Policy_Val (p_item_id IN NUMBER,
1933 p_org_id IN NUMBER,
1934 p_structure_type_id in NUMBER)
1935 RETURN VARCHAR2 IS
1936
1937 l_change_policy_char_val VARCHAR2(80);
1938 l_item_rev_code VARCHAR2(30);
1939
1940 BEGIN
1941 --get the current rev of the item, since structure header is independent of item rev.
1942 -- Commenting the revision related code, as structure change policies are not dependent on item's revision
1943 --l_item_rev_code := BOM_REVISIONS.GET_ITEM_REVISION_FN('ALL', 'ALL', p_org_id, p_item_id, SYSDATE);
1944 l_change_policy_char_val := 'ALLOWED';
1945
1946 SELECT
1947 ecp.policy_char_value INTO l_change_policy_char_val
1948 FROM
1949 MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP
1950 WHERE
1951 ecp.policy_object_pk1_value =
1952 (SELECT TO_CHAR(ic.item_catalog_group_id)
1953 FROM mtl_item_catalog_groups_b ic
1954 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
1955 FROM EGO_OBJ_TYPE_LIFECYCLES olc
1956 WHERE olc.object_id = (SELECT OBJECT_ID
1957 FROM fnd_objects
1958 WHERE obj_name = 'EGO_ITEM')
1959 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
1960 AND olc.object_classification_code = ic.item_catalog_group_id
1961 )
1962 AND ROWNUM = 1
1963 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
1964 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
1965 AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
1966 AND ecp.policy_object_pk3_value = ITEM_DTLS.current_phase_id
1967 AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
1968 AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
1969 AND ecp.attribute_code = 'STRUCTURE_TYPE'
1970 AND ecp.attribute_number_value = p_structure_type_id
1971 AND ITEM_DTLS.inventory_item_id = p_item_id
1972 AND ITEM_DTLS.organization_id = p_org_id;
1973
1974 RETURN l_change_policy_char_val;
1975
1976 EXCEPTION WHEN OTHERS THEN
1977
1978 RETURN l_change_policy_char_val;
1979
1980
1981
1982 END Get_Change_Policy_Val;
1983
1984 /****************************************************************************
1985 * Function : GET_PRIMARY_UI
1986 * Returns : VARCHAR2
1987 * Parameters IN : None
1988 * Parameter IN OUT NOCOPY : None
1989 * Purpose : Returns Alternate designator string for Primary
1990 * structure.(bug:4162717)
1991 *****************************************************************************/
1992 FUNCTION GET_PRIMARY_UI RETURN VARCHAR2
1993 IS
1994 BEGIN
1995 RETURN G_PRIMARY_UI;
1996 END GET_PRIMARY_UI;
1997
1998 FUNCTION Is_PLM_Enabled RETURN VARCHAR2
1999 IS
2000 l_plm_profile_value NUMBER;
2001 l_returnValue VARCHAR2(1);
2002 BEGIN
2003 l_plm_profile_value := fnd_profile.value('EGO_ENABLE_PLM');
2004 IF (l_plm_profile_value = 1)
2005 THEN
2006 l_returnValue := 'Y';
2007 ELSE
2008 l_returnValue := 'N';
2009 END IF;
2010 RETURN l_returnValue;
2011 END;
2012
2013 FUNCTION Is_PIM_Enabled RETURN VARCHAR2
2014 IS
2015 l_pim_profile_value NUMBER;
2016 l_returnValue VARCHAR2(1);
2017 BEGIN
2018 l_pim_profile_value := fnd_profile.value('EGO_ENABLE_PIMDL');
2019 IF (l_pim_profile_value = 1)
2020 THEN
2021 l_returnValue := 'Y';
2022 ELSE
2023 l_returnValue := 'N';
2024 END IF;
2025 RETURN l_returnValue;
2026 END;
2027
2028 FUNCTION Is_PDS_Enabled RETURN VARCHAR2
2029 IS
2030 l_gdsn_profile_value NUMBER;
2031 l_returnValue VARCHAR2(1);
2032 BEGIN
2033 l_gdsn_profile_value := fnd_profile.value('EGO_UCCNET_ENABLED');
2034 IF (l_gdsn_profile_value = 1)
2035 THEN
2036 l_returnValue := 'Y';
2037 ELSE
2038 l_returnValue := 'N';
2039 END IF;
2040 RETURN l_returnValue;
2041 END;
2042
2043
2044 FUNCTION Is_PIM_PDS_Enabled RETURN VARCHAR2
2045 IS
2046 l_pim_profile_value NUMBER;
2047 l_gdsn_profile_value NUMBER;
2048 l_returnValue VARCHAR2(1);
2052
2049 BEGIN
2050 l_pim_profile_value := fnd_profile.value('EGO_ENABLE_PIMDL');
2051 l_gdsn_profile_value := fnd_profile.value('EGO_UCCNET_ENABLED') ;
2053 --dbms_output.put_line(''||l_pim_profile_value);
2054 --dbms_output.put_line(''||l_gdsn_profile_value);
2055
2056 IF (l_pim_profile_value = 1 OR l_gdsn_profile_value = 1)
2057 THEN
2058 l_returnValue := 'Y';
2059 ELSE
2060 l_returnValue := 'N';
2061 END IF;
2062 RETURN l_returnValue;
2063 END;
2064
2065 /****************************************************************************
2066 * Function : Check_ItemAttrGroup_Security
2067 * Returns : VARCHAR2
2068 * Parameters IN : viewPrivilegeName
2069 * editPrivilegeName
2070 * partyId, inventoryItemId ,organizationId
2071 * Parameter IN OUT NOCOPY : None
2072 * Purpose : Returns T if view privilege is allowed
2073 * Returns E if view and edit privilege are allowed
2074 * Returns F if view privilege is not allowed.
2075 *****************************************************************************/
2076
2077
2078 FUNCTION Check_ItemAttrGroup_Security(viewPrivilegeName IN VARCHAR2,
2079 editPrivilegeName IN VARCHAR2,
2080 partyId IN VARCHAR2,
2081 inventoryItemId IN NUMBER,
2082 organizationId IN NUMBER) RETURN VARCHAR2
2083 IS
2084 l_view_privilege VARCHAR2(1) := NULL;
2085 l_edit_privilege VARCHAR2(1) := NULL;
2086 l_attrgroup_security VARCHAR2(1) := NULL;
2087 BEGIN
2088
2089 IF (viewPrivilegeName IS NOT NULL)
2090 THEN
2091 SELECT EGO_DATA_SECURITY.CHECK_FUNCTION(1.0,viewPrivilegeName,'EGO_ITEM',inventoryItemId,
2092 organizationId,null, null, null,partyId) INTO l_view_privilege
2093 FROM DUAL;
2094 END IF;
2095
2096 IF (editPrivilegeName IS NOT NULL)
2097 THEN
2098 SELECT EGO_DATA_SECURITY.CHECK_FUNCTION(1.0,editPrivilegeName,'EGO_ITEM',inventoryItemId,
2099 organizationId,null, null, null,partyId) INTO l_edit_privilege
2100 FROM DUAL;
2101 END IF;
2102
2103 IF (l_view_privilege IS NOT NULL)
2104 THEN
2105 l_attrgroup_security := l_view_privilege;
2106 IF (l_view_privilege = 'T')
2107 THEN
2108 IF ((l_edit_privilege IS NOT NULL) AND (l_edit_privilege = 'T'))
2109 THEN
2110 l_attrgroup_security := 'E';
2111 END IF;
2112 END IF;
2113 ELSE
2114 l_attrgroup_security := l_edit_privilege;
2115 IF (l_edit_privilege = 'T')
2116 THEN
2117 l_attrgroup_security := 'E';
2118 ELSIF (l_edit_privilege = 'F')
2119 THEN
2120 l_attrgroup_security := 'T';
2121 END IF;
2122 END IF;
2123
2124 RETURN l_attrgroup_security;
2125 END;
2126
2127 /*#
2128 * This function will return Y if the revised component is editable.
2129 * @return Y or N
2130 * @rep:category BUSINESS_ENTITY BOM_BILL_OF_MATERIAL
2131 * @rep:compatibility S
2132 * @rep:scope private
2133 * @rep:lifecycle active
2134 */
2135 FUNCTION Is_Rev_Comp_Editable(p_comp_seq_id IN NUMBER)
2136 RETURN VARCHAR2
2137 IS
2138 l_eng_api_call VARCHAR2(1000);
2139 l_edit_flag VARCHAR2(1);
2140 BEGIN
2141 --Dynamic call to remove dependency on ENG
2142 l_eng_api_call := 'BEGIN
2143 ENG_REVISED_ITEMS_PKG.Check_Rev_Comp_Editable (
2144 p_component_sequence_id => :1,
2145 x_rev_comp_editable_flag => :2);
2146 END;';
2147
2148 EXECUTE IMMEDIATE l_eng_api_call
2149 USING IN p_comp_seq_id, OUT l_edit_flag;
2150
2151 IF l_edit_flag = FND_API.G_TRUE
2152 THEN
2153 RETURN 'Y';
2154 ELSE
2155 RETURN 'N';
2156 END IF;
2157
2158 EXCEPTION
2159 WHEN OTHERS THEN
2160 RETURN 'Y';
2161
2162 END;
2163
2164
2165
2166 /* Return the structure change policy value associated with the revision/item level */
2167
2168 FUNCTION Get_Change_Policy_Val (p_item_id IN NUMBER,
2169 p_org_id IN NUMBER,
2170 p_rev_id IN NUMBER,
2171 p_rev_date IN DATE,
2172 p_structure_type_id in NUMBER) RETURN VARCHAR2 IS
2173 CURSOR changePolicy IS
2174 SELECT ecp.policy_char_value
2175 FROM
2176 (SELECT NVL(mirb.lifecycle_id, msi.lifecycle_id) AS lifecycle_id,
2177 NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
2178 msi.item_catalog_group_id item_catalog_group_id,
2179 msi.inventory_item_id, msi.organization_id , mirb.revision_id
2180 FROM mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
2181 WHERE msi.INVENTORY_ITEM_ID = p_item_id
2182 AND msi.ORGANIZATION_ID = p_org_id
2183 AND mirb.revision_id = nvl(p_rev_id,BOM_Revisions.Get_Item_Revision_Id_Fn('ALL','ALL',p_org_id, p_item_id, p_rev_date) )
2184 AND (mirb.current_phase_id IS NOT NULL OR msi.current_phase_id IS NOT NULL)) ITEM_DTLS,
2185 ENG_CHANGE_POLICIES_V ECP
2186 WHERE
2190 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
2187 ecp.policy_object_pk1_value =
2188 (SELECT TO_CHAR(ic.item_catalog_group_id)
2189 FROM mtl_item_catalog_groups_b ic
2191 FROM EGO_OBJ_TYPE_LIFECYCLES olc
2192 WHERE olc.object_id = (SELECT OBJECT_ID
2193 FROM fnd_objects
2194 WHERE obj_name = 'EGO_ITEM')
2195 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
2196 AND olc.object_classification_code = ic.item_catalog_group_id
2197 )
2198 AND ROWNUM = 1
2199 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2200 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
2201 AND ecp.policy_object_pk2_value = item_dtls.lifecycle_id
2202 AND ecp.policy_object_pk3_value = item_dtls.phase_id
2203 AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
2204 AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
2205 AND ecp.attribute_code = 'STRUCTURE_TYPE'
2206 AND ecp.attribute_number_value = p_structure_type_id;
2207
2208 BEGIN
2209
2210 FOR r1 IN changePolicy
2211 LOOP
2212 Return r1.policy_char_value;
2213 END LOOP;
2214
2215 Return 'ALLOWED';
2216
2217 END;
2218
2219
2220 FUNCTION getItemRevCode(p_rev_id IN NUMBER)
2221 RETURN VARCHAR2
2222 IS
2223 l_rev_code MTL_ITEM_REVISIONS_B.REVISION%TYPE;
2224 BEGIN
2225 SELECT revision
2226 INTO l_rev_code
2227 FROM mtl_item_revisions_b
2228 WHERE revision_id = p_rev_id;
2229
2230 RETURN l_rev_code;
2231 END;
2232
2233
2234 /* Return value: 'Y' --> Component's effectivity range is allowed
2235 Revision: Change Ploicy value --> Component's effectivity range is NOT allowed.
2236 Check the revision and the change policy value mentioned in the return value
2237 */
2238
2239 FUNCTION Check_Change_Policy_Range (p_item_id IN NUMBER,
2240 p_org_id IN NUMBER,
2241 p_start_revision IN VARCHAR2,
2242 p_end_revision IN VARCHAR2,
2243 p_start_rev_id IN NUMBER,
2244 p_end_rev_id IN NUMBER,
2245 p_effective_date IN DATE,
2246 p_disable_date IN DATE,
2247 p_current_chg_pol IN VARCHAR2,
2248 p_structure_type_id IN NUMBER,
2249 p_use_eco IN VARCHAR2) RETURN VARCHAR2 IS
2250 CURSOR changePolicy(p_start_rev VARCHAR2, p_end_rev VARCHAR2) IS
2251 SELECT item_dtls.revision, ecp.policy_char_value
2252 FROM
2253 (SELECT NVL(mirb.lifecycle_id, msi.lifecycle_id) AS lifecycle_id,
2254 NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
2255 msi.item_catalog_group_id item_catalog_group_id,
2256 msi.inventory_item_id, msi.organization_id , mirb.revision
2257 FROM mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
2258 WHERE msi.INVENTORY_ITEM_ID = p_item_id
2259 AND msi.ORGANIZATION_ID = p_org_id
2260 AND mirb.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
2261 AND mirb.ORGANIZATION_ID = msi.ORGANIZATION_ID
2262 AND mirb.revision >= nvl(p_start_rev,BOM_Revisions.Get_Item_Revision_Fn('ALL','ALL',p_org_id, p_item_id, p_effective_date) )
2263 AND mirb.revision <= nvl(p_end_rev,decode(p_disable_date,null,mirb.revision,BOM_Revisions.Get_Item_Revision_Fn('ALL','ALL',p_org_id, p_item_id, p_disable_date)) )
2264 AND (mirb.current_phase_id IS NOT NULL OR msi.current_phase_id IS NOT NULL)) ITEM_DTLS,
2265 ENG_CHANGE_POLICIES_V ECP
2266 WHERE
2267 ecp.policy_object_pk1_value =
2268 (SELECT TO_CHAR(ic.item_catalog_group_id)
2269 FROM mtl_item_catalog_groups_b ic
2270 WHERE EXISTS (SELECT olc.object_classification_code CatalogId
2271 FROM EGO_OBJ_TYPE_LIFECYCLES olc
2272 WHERE olc.object_id = (SELECT OBJECT_ID
2273 FROM fnd_objects
2274 WHERE obj_name = 'EGO_ITEM')
2275 AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
2276 AND olc.object_classification_code = ic.item_catalog_group_id
2277 )
2278 AND ROWNUM = 1
2279 CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
2280 START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
2281 AND ecp.policy_object_pk2_value = item_dtls.lifecycle_id
2282 AND ecp.policy_object_pk3_value = item_dtls.phase_id
2283 AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
2284 AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
2285 AND ecp.attribute_code = 'STRUCTURE_TYPE'
2286 AND ecp.attribute_number_value = p_structure_type_id
2287 AND ecp.policy_char_value <> p_current_chg_pol
2288 ORDER BY item_dtls.revision;
2289
2290 l_start_rev mtl_item_revisions_b.revision%TYPE;
2291 l_end_rev mtl_item_revisions_b.revision%TYPE;
2292
2293 BEGIN
2294 l_start_rev := p_start_revision;
2298 l_start_rev := getItemRevCode(p_start_rev_id);
2295 l_end_rev := p_end_revision;
2296 IF p_start_revision IS NULL AND p_start_rev_id IS NOT NULL
2297 THEN
2299 END IF;
2300 IF p_end_revision IS NULL AND p_end_rev_id IS NOT NULL
2301 THEN
2302 l_end_rev := getItemRevCode(p_end_rev_id);
2303 END IF;
2304
2305 FOR r1 IN changePolicy(l_start_rev, l_end_rev)
2306 LOOP
2307 IF r1.policy_char_value = 'NOT_ALLOWED'
2308 OR(r1.policy_char_value = 'CHANGE_ORDER_REQUIRED' AND p_use_eco <> 'Y')
2309 THEN
2310 Return r1.revision||': '||r1.policy_char_value;
2311 END IF;
2312 END LOOP;
2313
2314 Return 'Y';
2315 END;
2316
2317
2318 FUNCTION getRevDate(p_rev_id IN NUMBER)
2319 RETURN VARCHAR2
2320 IS
2321 l_rev_date MTL_ITEM_REVISIONS_B.EFFECTIVITY_DATE%TYPE;
2322 BEGIN
2323
2324 SELECT effectivity_date
2325 INTO l_rev_date
2326 FROM mtl_item_revisions_b
2327 where revision_id = p_rev_id;
2328
2329 RETURN l_rev_date;
2330 END;
2331
2332
2333 FUNCTION Check_Change_Policy_Range (p_item_id IN NUMBER,
2334 p_org_id IN NUMBER,
2335 p_component_sequence_id IN NUMBER,
2336 p_current_chg_pol IN VARCHAR2,
2337 p_structure_type_id IN NUMBER,
2338 p_context_rev_id IN NUMBER,
2339 p_use_eco IN VARCHAR2)
2340 RETURN VARCHAR2
2341 IS
2342 l_start_rev_id NUMBER;
2343 l_end_rev_id NUMBER;
2344 l_effective_date DATE;
2345 l_disable_date DATE;
2346 l_start_date DATE;
2347 l_eff_ctrl NUMBER;
2348
2349 BEGIN
2350
2351 SELECT from_end_item_rev_id, to_end_item_rev_id, effectivity_date, disable_date
2352 INTO l_start_rev_id, l_end_rev_id, l_effective_date, l_disable_date
2353 FROM BOM_COMPONENTS_B
2354 WHERE component_sequence_id = p_component_sequence_id;
2355
2356 IF p_context_rev_id IS NOT NULL
2357 THEN
2358 SELECT effectivity_date
2359 INTO l_start_date
2360 FROM mtl_item_revisions_b
2361 WHERE revision_id = p_context_rev_id;
2362
2363 SELECT effectivity_control
2364 INTO l_eff_ctrl
2365 FROM BOM_STRUCTURES_B
2366 WHERE bill_sequence_id = (SELECT bill_sequence_id
2367 FROM bom_components_b
2368 WHERE component_sequence_id = p_component_sequence_id
2369 AND ROWNUM = 1);
2370
2371 IF l_eff_ctrl = 1 AND SYSDATE BETWEEN l_effective_date AND l_start_date
2372 THEN
2373 l_effective_date := l_start_date;
2374 ELSIF l_eff_ctrl = 4 AND getItemRevCode(p_context_rev_id) > getItemRevCode(l_start_rev_id)
2375 AND getRevDate(l_start_rev_id) < SYSDATE
2376 THEN
2377 l_start_rev_id := p_context_rev_id;
2378 END IF;
2379 END IF;
2380
2381 RETURN Check_Change_Policy_Range (p_item_id => p_item_id,
2382 p_org_id => p_org_id,
2383 p_start_revision => null,
2384 p_end_revision => null,
2385 p_start_rev_id => l_start_rev_id,
2386 p_end_rev_id => l_end_rev_id,
2387 p_effective_date => l_effective_date,
2388 p_disable_date => l_disable_date,
2389 p_current_chg_pol => p_current_chg_pol,
2390 p_structure_type_id => p_structure_type_id,
2391 p_use_eco => p_use_eco);
2392
2393 END;
2394
2395 /*FUNCTION Check_Change_Policy_Range (p_item_id IN NUMBER,
2396 p_org_id IN NUMBER,
2397 p_component_sequence_id IN NUMBER,
2398 p_current_chg_pol IN VARCHAR2,
2399 p_structure_type_id IN NUMBER,
2400 p_use_eco IN VARCHAR2)
2401 RETURN VARCHAR2
2402 IS
2403 l_start_rev_id NUMBER;
2404 l_end_rev_id NUMBER;
2405 l_effective_date DATE;
2406 l_disable_date DATE;
2407
2408 BEGIN
2409
2410 SELECT from_end_item_rev_id, to_end_item_rev_id, effectivity_date, disable_date
2411 INTO l_start_rev_id, l_end_rev_id, l_effective_date, l_disable_date
2412 FROM BOM_COMPONENTS_B
2413 WHERE component_sequence_id = p_component_sequence_id;
2414
2415 RETURN Check_Change_Policy_Range (p_item_id => p_item_id,
2416 p_org_id => p_org_id,
2417 p_start_revision => null,
2418 p_end_revision => null,
2419 p_start_rev_id => l_start_rev_id,
2420 p_end_rev_id => l_end_rev_id,
2421 p_effective_date => l_effective_date,
2422 p_disable_date => l_disable_date,
2423 p_current_chg_pol => p_current_chg_pol,
2424 p_structure_type_id => p_structure_type_id,
2425 p_use_eco => p_use_eco);
2426
2427 END;
2428 */
2432 IS
2429 Procedure copy_Comp_User_Attrs(p_src_comp_seq_id IN NUMBER,
2430 p_dest_comp_seq_id IN NUMBER,
2431 x_Return_Status OUT NOCOPY VARCHAR2)
2433 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2434 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
2435 l_new_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
2436 l_bill_seq_id NUMBER;
2437 l_errorcode NUMBER;
2438 l_msg_data VARCHAR2(100);
2439 l_msg_count NUMBER := 0;
2440 --l_return_status VARCHAR2(1);
2441 l_str_type NUMBER;
2442
2443 Cursor get_bill_seq_id(p_comp_sequence_id Number)
2444 IS
2445 SELECT bill_sequence_id
2446 from BOM_COMPONENTS_B
2447 where component_sequence_id = p_comp_sequence_id;
2448
2449 Cursor get_structure_type(p_bill_seq_id NUMBER)
2450 IS
2451 Select structure_type_id
2452 from BOM_STRUCTURES_B
2453 where bill_sequence_id = p_bill_seq_id;
2454
2455 BEGIN
2456
2457 Open get_bill_seq_id(p_comp_sequence_id => p_src_comp_seq_id);
2458 Fetch get_bill_seq_id INTO l_bill_seq_id;
2459 Close get_bill_seq_id;
2460
2461 Open get_structure_type(p_bill_seq_id => l_bill_seq_id);
2462 Fetch get_structure_type INTO l_str_type;
2463 Close get_structure_type;
2464
2465 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' , to_char(p_src_comp_seq_id))
2466 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' , to_char(l_bill_seq_id)) );
2467 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' , to_char(p_dest_comp_seq_id)),
2468 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' , to_char(l_bill_seq_id)) );
2469 l_new_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID', TO_CHAR(l_str_type)));
2470 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data(
2471 p_api_version => 1.0
2472 ,p_application_id => 702
2473 ,p_object_name => 'BOM_COMPONENTS'
2474 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
2475 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
2476 ,p_new_cc_col_value_pairs => l_new_str_type
2477 ,x_return_status => x_Return_Status
2478 ,x_errorcode => l_errorcode
2479 ,x_msg_count => l_msg_count
2480 ,x_msg_data => l_msg_data
2481 );
2482 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
2483 THEN
2484 fnd_message.set_name('BOM', 'BOM_SPLIT_FAILED');
2485 return;
2486 END IF;
2487 END copy_Comp_User_Attrs;
2488
2489 /* Function to split the component and copy the user attributes, RFD and Subcomps */
2490 FUNCTION split_component(p_comp_seq_id IN NUMBER,
2491 p_rev_id IN NUMBER,
2492 p_disable_rev_id IN NUMBER,
2493 p_disable_date IN DATE)
2494 RETURN NUMBER
2495 IS
2496 l_component_seqeunce_id NUMBER;
2497 l_Return_Status VARCHAR2(1);
2498 l_effectivity_control NUMBER;
2499 l_start_effectivity_date DATE;
2500 l_disable_date DATE;
2501 l_start_rev_id NUMBER;
2502 l_end_rev_id NUMBER;
2503 l_temp_num NUMBER;
2504 BEGIN
2505 --Get the new ComponentSequenceId
2506 SELECT Bom_Inventory_Components_S.NEXTVAL INTO l_component_seqeunce_id FROM dual;
2507
2508 --Get the parents effectivity control
2509 SELECT Effectivity_Control INTO l_effectivity_control FROM BOM_STRUCTURES_B
2510 WHERE Bill_Sequence_Id = (SELECT Bill_Sequence_Id FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id);
2511
2512 -- IF the effectivity is not of date or rev return
2513 IF l_effectivity_control = 1 THEN
2514 SELECT Effectivity_Date INTO l_start_effectivity_date FROM MTL_ITEM_REVISIONS_B WHERE REVISION_ID = p_rev_id;
2515 SELECT Disable_date INTO l_disable_date FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id;
2516 l_start_rev_id := null;
2517 l_end_rev_id := null;
2518 ELSIF l_effectivity_control = 4 THEN
2519 SELECT SYSDATE INTO l_start_effectivity_date FROM dual;
2520 l_disable_date := null;
2521 l_start_rev_id := p_rev_id;
2522 SELECT To_End_Item_Rev_Id INTO l_end_rev_id FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id;
2523 ELSE
2524 l_component_seqeunce_id := 0;
2525 RETURN l_component_seqeunce_id;
2526 END IF;
2527
2528 --Before creating new component, disable the existing row
2529 IF l_effectivity_control = 1
2530 THEN
2531 UPDATE bom_components_b
2532 SET disable_date = p_disable_date
2533 WHERE component_sequence_id = p_comp_seq_id;
2534 ELSIF l_effectivity_control = 4
2535 THEN
2539 END IF;
2536 UPDATE bom_components_b
2537 SET to_end_item_rev_id = p_disable_rev_id
2538 WHERE component_sequence_id = p_comp_seq_id;
2540
2541
2542 --Create new Component
2543 INSERT INTO BOM_COMPONENTS_B
2544 ( SUPPLY_SUBINVENTORY
2545 , OPERATION_LEAD_TIME_PERCENT
2546 , REVISED_ITEM_SEQUENCE_ID
2547 , COST_FACTOR
2548 , REQUIRED_FOR_REVENUE
2549 , HIGH_QUANTITY
2550 , COMPONENT_SEQUENCE_ID
2551 , PROGRAM_APPLICATION_ID
2552 , WIP_SUPPLY_TYPE
2553 , SUPPLY_LOCATOR_ID
2554 , BOM_ITEM_TYPE
2555 , OPERATION_SEQ_NUM
2556 , COMPONENT_ITEM_ID
2557 , LAST_UPDATE_DATE
2558 , LAST_UPDATED_BY
2559 , CREATION_DATE
2560 , CREATED_BY
2561 , LAST_UPDATE_LOGIN
2562 , ITEM_NUM
2563 , COMPONENT_QUANTITY
2564 , COMPONENT_YIELD_FACTOR
2565 , COMPONENT_REMARKS
2566 , EFFECTIVITY_DATE
2567 , CHANGE_NOTICE
2568 , IMPLEMENTATION_DATE
2569 , DISABLE_DATE
2570 , ATTRIBUTE_CATEGORY
2571 , ATTRIBUTE1
2572 , ATTRIBUTE2
2573 , ATTRIBUTE3
2574 , ATTRIBUTE4
2575 , ATTRIBUTE5
2576 , ATTRIBUTE6
2577 , ATTRIBUTE7
2578 , ATTRIBUTE8
2579 , ATTRIBUTE9
2580 , ATTRIBUTE10
2581 , ATTRIBUTE11
2582 , ATTRIBUTE12
2583 , ATTRIBUTE13
2584 , ATTRIBUTE14
2585 , ATTRIBUTE15
2586 , PLANNING_FACTOR
2587 , QUANTITY_RELATED
2588 , SO_BASIS
2589 , OPTIONAL
2590 , MUTUALLY_EXCLUSIVE_OPTIONS
2591 , INCLUDE_IN_COST_ROLLUP
2592 , CHECK_ATP
2593 , SHIPPING_ALLOWED
2594 , REQUIRED_TO_SHIP
2595 , INCLUDE_ON_SHIP_DOCS
2596 , INCLUDE_ON_BILL_DOCS
2597 , LOW_QUANTITY
2598 , ACD_TYPE
2599 , OLD_COMPONENT_SEQUENCE_ID
2600 , BILL_SEQUENCE_ID
2601 , REQUEST_ID
2602 , PROGRAM_ID
2603 , PROGRAM_UPDATE_DATE
2604 , PICK_COMPONENTS
2605 , Original_System_Reference
2606 , From_End_Item_Unit_Number
2607 , To_End_Item_Unit_Number
2608 , Eco_For_Production -- Added by MK
2609 , Enforce_Int_Requirements
2610 , Auto_Request_Material -- Added in 11.5.9 by ADEY
2611 , Obj_Name -- Added by hgelli.
2612 , pk1_value
2613 , pk2_value
2614 , Suggested_Vendor_Name --- Deepu
2615 , Vendor_Id --- Deepu
2616 , Unit_Price --- Deepu
2617 , from_object_revision_id
2618 , from_minor_revision_id
2619 , from_end_item_rev_id
2620 , to_end_item_rev_id
2621 , component_item_revision_id
2622 , basis_type
2623 , common_component_sequence_id
2624 )
2625 SELECT comp_rec.SUPPLY_SUBINVENTORY
2626 , comp_rec.OPERATION_LEAD_TIME_PERCENT
2627 , comp_rec.REVISED_ITEM_SEQUENCE_ID
2628 , comp_rec.COST_FACTOR
2629 , comp_rec.REQUIRED_FOR_REVENUE
2630 , comp_rec.HIGH_QUANTITY
2631 , l_component_seqeunce_id
2632 , comp_rec.PROGRAM_APPLICATION_ID
2633 , comp_rec.WIP_SUPPLY_TYPE
2634 , comp_rec.SUPPLY_LOCATOR_ID
2635 , comp_rec.BOM_ITEM_TYPE
2636 , comp_rec.OPERATION_SEQ_NUM
2637 , comp_rec.COMPONENT_ITEM_ID
2638 , sysdate
2639 , comp_rec.LAST_UPDATED_BY
2640 , sysdate
2641 , comp_rec.CREATED_BY
2642 , comp_rec.LAST_UPDATE_LOGIN
2643 , comp_rec.ITEM_NUM
2644 , comp_rec.COMPONENT_QUANTITY
2645 , comp_rec.COMPONENT_YIELD_FACTOR
2646 , comp_rec.COMPONENT_REMARKS
2647 , l_start_effectivity_date
2648 , comp_rec.CHANGE_NOTICE
2649 , comp_rec.IMPLEMENTATION_DATE
2650 , l_disable_date
2651 , comp_rec.ATTRIBUTE_CATEGORY
2652 , comp_rec.ATTRIBUTE1
2653 , comp_rec.ATTRIBUTE2
2654 , comp_rec.ATTRIBUTE3
2655 , comp_rec.ATTRIBUTE4
2656 , comp_rec.ATTRIBUTE5
2657 , comp_rec.ATTRIBUTE6
2658 , comp_rec.ATTRIBUTE7
2659 , comp_rec.ATTRIBUTE8
2660 , comp_rec.ATTRIBUTE9
2661 , comp_rec.ATTRIBUTE10
2662 , comp_rec.ATTRIBUTE11
2663 , comp_rec.ATTRIBUTE12
2664 , comp_rec.ATTRIBUTE13
2665 , comp_rec.ATTRIBUTE14
2666 , comp_rec.ATTRIBUTE15
2667 , comp_rec.PLANNING_FACTOR
2668 , comp_rec.QUANTITY_RELATED
2669 , comp_rec.SO_BASIS
2670 , comp_rec.OPTIONAL
2671 , comp_rec.MUTUALLY_EXCLUSIVE_OPTIONS
2672 , comp_rec.INCLUDE_IN_COST_ROLLUP
2673 , comp_rec.CHECK_ATP
2674 , comp_rec.SHIPPING_ALLOWED
2675 , comp_rec.REQUIRED_TO_SHIP
2676 , comp_rec.INCLUDE_ON_SHIP_DOCS
2677 , comp_rec.INCLUDE_ON_BILL_DOCS
2678 , comp_rec.LOW_QUANTITY
2679 , comp_rec.ACD_TYPE
2680 , comp_rec.OLD_COMPONENT_SEQUENCE_ID
2684 , comp_rec.PROGRAM_UPDATE_DATE
2681 , comp_rec.bill_sequence_id
2682 , comp_rec.REQUEST_ID
2683 , comp_rec.PROGRAM_ID
2685 , comp_rec.PICK_COMPONENTS
2686 , comp_rec.Original_System_Reference
2687 , comp_rec.From_End_Item_Unit_Number
2688 , comp_rec.To_End_Item_Unit_Number
2689 , comp_rec.Eco_For_Production -- Added by MK
2690 , comp_rec.Enforce_Int_Requirements
2691 , comp_rec.Auto_Request_Material -- Added in 11.5.9 by ADEY
2692 , comp_rec.Obj_Name -- Added by hgelli.
2693 , comp_rec.pk1_value
2694 , comp_rec.pk2_value
2695 , comp_rec.Suggested_Vendor_Name --- Deepu
2696 , comp_rec.Vendor_Id --- Deepu
2697 , comp_rec.Unit_Price --- Deepu
2698 , comp_rec.from_object_revision_id
2699 , comp_rec.from_minor_revision_id
2700 , l_start_rev_id
2701 , l_end_rev_id
2702 , comp_rec.component_item_revision_id
2703 , comp_rec.basis_type
2704 , comp_rec.common_component_sequence_id
2705 FROM BOM_COMPONENTS_B comp_rec
2706 WHERE comp_rec.component_sequence_id = p_comp_seq_id;
2707
2708 SELECT Count(1) INTO l_temp_num FROM bom_components_b WHERE component_sequence_id = l_component_seqeunce_id;
2709
2710 --Copy component user attributes to new component
2711 copy_Comp_User_Attrs(p_src_comp_seq_id => p_comp_seq_id,
2712 p_dest_comp_seq_id => l_component_seqeunce_id,
2713 x_Return_Status => l_Return_Status);
2714
2715 -- Copy Reference designators to new component
2716 INSERT INTO BOM_REFERENCE_DESIGNATORS
2717 ( COMPONENT_REFERENCE_DESIGNATOR
2718 , LAST_UPDATE_DATE
2719 , LAST_UPDATED_BY
2720 , CREATION_DATE
2721 , CREATED_BY
2722 , LAST_UPDATE_LOGIN
2723 , REF_DESIGNATOR_COMMENT
2724 , CHANGE_NOTICE
2725 , COMPONENT_SEQUENCE_ID
2726 , ACD_TYPE
2727 , REQUEST_ID
2728 , PROGRAM_APPLICATION_ID
2729 , PROGRAM_ID
2730 , PROGRAM_UPDATE_DATE
2731 , ATTRIBUTE_CATEGORY
2732 , ATTRIBUTE1
2733 , ATTRIBUTE2
2734 , ATTRIBUTE3
2735 , ATTRIBUTE4
2736 , ATTRIBUTE5
2737 , ATTRIBUTE6
2738 , ATTRIBUTE7
2739 , ATTRIBUTE8
2740 , ATTRIBUTE9
2741 , ATTRIBUTE10
2742 , ATTRIBUTE11
2743 , ATTRIBUTE12
2744 , ATTRIBUTE13
2745 , ATTRIBUTE14
2746 , ATTRIBUTE15
2747 , Original_System_Reference
2748 , common_component_sequence_id
2749 )
2750 SELECT
2751 ref_desg.component_reference_designator
2752 , SYSDATE
2753 , ref_desg.LAST_UPDATED_BY
2754 , SYSDATE
2755 , ref_desg.CREATED_BY
2756 , ref_desg.LAST_UPDATE_LOGIN
2757 , DECODE( ref_desg.ref_designator_comment
2758 , FND_API.G_MISS_CHAR
2759 , NULL
2760 , ref_desg.ref_designator_comment )
2761 , ref_desg.change_notice
2762 , l_component_seqeunce_id
2763 , ref_desg.acd_type
2764 , NULL /* Request Id */
2765 , Bom_Globals.Get_Prog_AppId
2766 , Bom_Globals.Get_Prog_Id
2767 , SYSDATE
2768 , ref_desg.attribute_category
2769 , ref_desg.attribute1
2770 , ref_desg.attribute2
2771 , ref_desg.attribute3
2772 , ref_desg.attribute4
2773 , ref_desg.attribute5
2774 , ref_desg.attribute6
2775 , ref_desg.attribute7
2776 , ref_desg.attribute8
2777 , ref_desg.attribute9
2778 , ref_desg.attribute10
2779 , ref_desg.attribute11
2780 , ref_desg.attribute12
2781 , ref_desg.attribute13
2782 , ref_desg.attribute14
2783 , ref_desg.attribute15
2784 , ref_desg.Original_System_Reference
2785 , ref_desg.common_component_sequence_id
2786 FROM BOM_REFERENCE_DESIGNATORS ref_desg
2787 WHERE ref_desg.component_sequence_id = p_comp_seq_id;
2788
2789 -- Copy Substitute components to new component
2790 INSERT INTO BOM_SUBSTITUTE_COMPONENTS
2791 ( SUBSTITUTE_COMPONENT_ID
2792 , LAST_UPDATE_DATE
2793 , LAST_UPDATED_BY
2794 , CREATION_DATE
2795 , CREATED_BY
2796 , LAST_UPDATE_LOGIN
2797 , SUBSTITUTE_ITEM_QUANTITY
2798 , COMPONENT_SEQUENCE_ID
2799 , ACD_TYPE
2800 , CHANGE_NOTICE
2801 , REQUEST_ID
2802 , PROGRAM_APPLICATION_ID
2803 , PROGRAM_UPDATE_DATE
2804 , ATTRIBUTE_CATEGORY
2805 , ATTRIBUTE1
2806 , ATTRIBUTE2
2807 , ATTRIBUTE3
2808 , ATTRIBUTE4
2809 , ATTRIBUTE5
2810 , ATTRIBUTE6
2811 , ATTRIBUTE7
2812 , ATTRIBUTE8
2813 , ATTRIBUTE9
2814 , ATTRIBUTE10
2815 , ATTRIBUTE11
2816 , ATTRIBUTE12
2817 , ATTRIBUTE13
2818 , ATTRIBUTE14
2819 , ATTRIBUTE15
2820 , PROGRAM_ID
2821 , Original_System_Reference
2822 , Enforce_Int_Requirements
2823 , common_component_sequence_id
2824 )
2825 SELECT
2826 sub_comp.substitute_component_id
2830 , sub_comp.CREATED_BY
2827 , SYSDATE
2828 , sub_comp.LAST_UPDATED_BY
2829 , SYSDATE
2831 , sub_comp.LAST_UPDATE_LOGIN
2832 , sub_comp.substitute_item_quantity
2833 , l_component_seqeunce_id
2834 , sub_comp.acd_type
2835 , sub_comp.Change_Notice
2836 , NULL /* Request Id */
2837 , Bom_Globals.Get_Prog_AppId
2838 , SYSDATE
2839 , sub_comp.attribute_category
2840 , sub_comp.attribute1
2841 , sub_comp.attribute2
2842 , sub_comp.attribute3
2843 , sub_comp.attribute4
2844 , sub_comp.attribute5
2845 , sub_comp.attribute6
2846 , sub_comp.attribute7
2847 , sub_comp.attribute8
2848 , sub_comp.attribute9
2849 , sub_comp.attribute10
2850 , sub_comp.attribute11
2851 , sub_comp.attribute12
2852 , sub_comp.attribute13
2853 , sub_comp.attribute14
2854 , sub_comp.attribute15
2855 , Bom_Globals.Get_Prog_Id
2856 , sub_comp.Original_System_Reference
2857 , sub_comp.enforce_int_requirements
2858 , sub_comp.common_component_sequence_id
2859 FROM BOM_SUBSTITUTE_COMPONENTS sub_comp
2860 WHERE sub_comp.component_sequence_id = p_comp_seq_id;
2861
2862
2863 INSERT INTO bom_component_operations
2864 (
2865 COMP_OPERATION_SEQ_ID ,
2866 OPERATION_SEQ_NUM ,
2867 OPERATION_SEQUENCE_ID ,
2868 LAST_UPDATE_DATE ,
2869 LAST_UPDATED_BY ,
2870 CREATION_DATE ,
2871 CREATED_BY ,
2872 LAST_UPDATE_LOGIN ,
2873 COMPONENT_SEQUENCE_ID ,
2874 BILL_SEQUENCE_ID ,
2875 CONSUMING_OPERATION_FLAG ,
2876 CONSUMPTION_QUANTITY ,
2877 SUPPLY_SUBINVENTORY ,
2878 SUPPLY_LOCATOR_ID ,
2879 WIP_SUPPLY_TYPE ,
2880 ATTRIBUTE_CATEGORY ,
2881 ATTRIBUTE1 ,
2882 ATTRIBUTE2 ,
2883 ATTRIBUTE3 ,
2884 ATTRIBUTE4 ,
2885 ATTRIBUTE5 ,
2886 ATTRIBUTE6 ,
2887 ATTRIBUTE7 ,
2888 ATTRIBUTE8 ,
2889 ATTRIBUTE9 ,
2890 ATTRIBUTE10 ,
2891 ATTRIBUTE11 ,
2892 ATTRIBUTE12 ,
2893 ATTRIBUTE13 ,
2894 ATTRIBUTE14 ,
2895 ATTRIBUTE15 ,
2896 COMMON_COMPONENT_SEQUENCE_ID)
2897 SELECT
2898 bom_component_operations_s.NEXTVAL ,
2899 comp_ops.OPERATION_SEQ_NUM ,
2900 comp_ops.OPERATION_SEQUENCE_ID ,
2901 comp_ops.LAST_UPDATE_DATE ,
2902 comp_ops.LAST_UPDATED_BY ,
2903 comp_ops.CREATION_DATE ,
2904 comp_ops.CREATED_BY ,
2905 comp_ops.LAST_UPDATE_LOGIN ,
2906 l_component_seqeunce_id ,
2907 comp_ops.BILL_SEQUENCE_ID ,
2908 comp_ops.CONSUMING_OPERATION_FLAG ,
2909 comp_ops.CONSUMPTION_QUANTITY ,
2910 comp_ops.SUPPLY_SUBINVENTORY ,
2911 comp_ops.SUPPLY_LOCATOR_ID ,
2912 comp_ops.WIP_SUPPLY_TYPE ,
2913 comp_ops.ATTRIBUTE_CATEGORY ,
2914 comp_ops.ATTRIBUTE1 ,
2915 comp_ops.ATTRIBUTE2 ,
2916 comp_ops.ATTRIBUTE3 ,
2917 comp_ops.ATTRIBUTE4 ,
2918 comp_ops.ATTRIBUTE5 ,
2919 comp_ops.ATTRIBUTE6 ,
2920 comp_ops.ATTRIBUTE7 ,
2921 comp_ops.ATTRIBUTE8 ,
2922 comp_ops.ATTRIBUTE9 ,
2923 comp_ops.ATTRIBUTE10 ,
2924 comp_ops.ATTRIBUTE11 ,
2925 comp_ops.ATTRIBUTE12 ,
2926 comp_ops.ATTRIBUTE13 ,
2927 comp_ops.ATTRIBUTE14 ,
2928 comp_ops.ATTRIBUTE15 ,
2929 comp_ops.COMMON_COMPONENT_SEQUENCE_ID
2930 FROM BOM_COMPONENT_OPERATIONS comp_ops
2931 WHERE comp_ops.component_sequence_id = p_comp_seq_id;
2932
2933
2934 RETURN l_component_seqeunce_id;
2935 END split_component;
2936
2937 /* Function to get the effective component with respect to the passed parent item's revision and explosion date*/
2938 FUNCTION get_effetive_component(p_comp_seq_id IN NUMBER,
2939 p_rev_id IN NUMBER,
2940 p_explosion_date DATE)
2941 RETURN NUMBER
2942 IS
2943 l_component_seqeunce_id NUMBER;
2944 l_effectivity_control NUMBER;
2945 l_start_effectivity_date DATE;
2946 l_rev_code VARCHAR2(30);
2947 BEGIN
2948 --Get the parents effectivity control
2949 SELECT Effectivity_Control INTO l_effectivity_control FROM BOM_STRUCTURES_B
2950 WHERE Bill_Sequence_Id = (SELECT Bill_Sequence_Id FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id);
2951
2952 -- IF the effectivity is not of date or rev return
2953 BEGIN
2957 IF(l_start_effectivity_date < SYSDATE) THEN
2954 IF l_effectivity_control = 1 THEN -- DATE
2955 IF p_explosion_date IS NULL THEN
2956 SELECT Effectivity_Date INTO l_start_effectivity_date FROM MTL_ITEM_REVISIONS_B WHERE REVISION_ID = p_rev_id;
2958 l_start_effectivity_date := SYSDATE;
2959 END IF;
2960 ELSE
2961 l_start_effectivity_date := p_explosion_date;
2962 END IF;
2963 SELECT
2964 bcb2.component_sequence_id INTO l_component_seqeunce_id
2965 FROM
2966 bom_components_b bcb1, bom_components_b bcb2
2967 WHERE
2968 bcb1.component_sequence_id = p_comp_seq_id
2969 AND bcb1.bill_sequence_id = bcb2.bill_sequence_id
2970 AND nvl(bcb1.obj_name,'EGO_ITEM') = nvl(bcb2.obj_name,'EGO_ITEM')
2971 AND bcb1.pk1_value = bcb2.pk1_value
2972 AND bcb1.operation_seq_num = bcb2.operation_seq_num
2973 AND bcb2.Implementation_Date IS NOT NULL
2974 AND bcb2.effectivity_date <= l_start_effectivity_date
2975 AND (bcb2.disable_date IS NULL OR bcb2.disable_date > l_start_effectivity_date);
2976
2977 ELSIF l_effectivity_control = 4 THEN -- REVISION
2978 SELECT Revision INTO l_rev_code FROM MTL_ITEM_REVISIONS_B WHERE REVISION_ID = p_rev_id;
2979 SELECT
2980 bcb2.component_sequence_id INTO l_component_seqeunce_id
2981 FROM
2982 bom_components_b bcb1, bom_components_b bcb2, mtl_item_revisions_b mirb1, mtl_item_revisions_b mirb2
2983 WHERE
2984 bcb1.component_sequence_id = p_comp_seq_id
2985 AND bcb1.bill_sequence_id = bcb2.bill_sequence_id
2986 AND nvl(bcb1.obj_name,'EGO_ITEM') = nvl(bcb2.obj_name,'EGO_ITEM')
2987 AND bcb1.pk1_value = bcb2.pk1_value
2988 AND bcb1.operation_seq_num = bcb2.operation_seq_num
2989 AND bcb2.Implementation_Date IS NOT NULL
2990 AND bcb2.disable_date IS NULL
2991 AND mirb1.revision_id = bcb2.from_end_item_rev_id
2992 AND mirb2.revision_id = Nvl(bcb2.to_end_item_rev_id, bcb2.from_end_item_rev_id)
2993 AND mirb1.revision <= l_rev_code
2994 AND (bcb2.to_end_item_rev_id IS NULL OR mirb2.revision >= l_rev_code);
2995
2996 ELSE
2997 l_component_seqeunce_id := p_comp_seq_id;
2998 END IF;
2999 EXCEPTION
3000 WHEN NO_DATA_FOUND THEN
3001 l_component_seqeunce_id := p_comp_seq_id;
3002 END;
3003
3004 RETURN l_component_seqeunce_id;
3005 END get_effetive_component;
3006
3007 /* Function to get the effective component with respect to the passed parent item's revision */
3008 FUNCTION get_effetive_component(p_comp_seq_id IN NUMBER,
3009 p_rev_id IN NUMBER)
3010 RETURN NUMBER
3011 IS
3012 BEGIN
3013 RETURN get_effetive_component(p_comp_seq_id, p_rev_id, NULL);
3014 END get_effetive_component;
3015
3016 /* Function to get the catalog category name for the given item_catalog_group_id */
3017 FUNCTION get_item_catalog_category(p_item_catalog_group_id IN NUMBER)
3018 RETURN VARCHAR2
3019 IS
3020 l_catalog_category_name VARCHAR2(2020);
3021 BEGIN
3022 -- Bug 7570437 mtl_item_catalog_groups_kfv should not be used.
3023 -- SELECT concatenated_segments
3024 SELECT decode(MICG.item_catalog_group_id, NULL, NULL,
3025 FND_FLEX_SERVER.GET_KFV_CONCAT_SEGS_BY_CCID('COMPACT',401,'MICG',101,MICG.item_catalog_group_id,NULL) )
3026 INTO l_catalog_category_name
3027 FROM mtl_item_catalog_groups MICG
3028 -- FROM mtl_item_catalog_groups_kfv
3029 WHERE item_catalog_group_id = p_item_catalog_group_id;
3030
3031 RETURN l_catalog_category_name;
3032
3033 EXCEPTION
3034 WHEN NO_DATA_FOUND THEN
3035 RETURN NULL;
3036 END;
3037
3038
3039 FUNCTION Get_Bill_Header_ECN(p_bill_seq_id IN NUMBER)
3040 RETURN VARCHAR2
3041 IS
3042 l_pend_from_ecn BOM_STRUCTURES_B.pending_from_ecn%TYPE;
3043 BEGIN
3044 SELECT pending_from_ecn
3045 INTO l_pend_from_ecn
3046 FROM bom_structures_b
3047 WHERE bill_sequence_id = p_bill_Seq_id;
3048
3049 IF l_pend_from_ecn IS NOT NULL
3050 THEN
3051 RETURN l_pend_from_ecn;
3052 END IF;
3053
3054 RETURN NULL;
3055
3056 EXCEPTION
3057 WHEN NO_DATA_FOUND THEN
3058 RETURN NULL;
3059
3060 END;
3061
3062 PROCEDURE uda_attribute_defaulting(p_bill_sequence_id IN VARCHAR2
3063 ,p_component_sequence_id IN VARCHAR2 DEFAULT NULL
3064 ,p_object_name IN VARCHAR2
3065 ,p_structure_type_id IN VARCHAR2
3066 ,x_return_status OUT NOCOPY VARCHAR2
3067 ,x_msg_data OUT NOCOPY VARCHAR2)
3068
3069 IS
3070
3071 l_error_code VARCHAR2(2000);
3072 l_msg_count NUMBER;
3073 l_msg_data VARCHAR2(2000);
3074 l_failed_row_id_list VARCHAR2(2000);
3075
3076 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3077 l_class_code_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3078 l_data_level_values EGO_COL_NAME_VALUE_PAIR_ARRAY;
3079
3080 l_object_name VARCHAR2(50);
3081 l_application_id NUMBER;
3082 l_additional_class_Code_list VARCHAR2(32000);
3086 l_return_status VARCHAR2(10);
3083 l_attribute_group_type VARCHAR2(50);
3084
3085
3087
3088 CURSOR parent_st_type_cursor
3089 IS
3090 SELECT STRUCTURE_TYPE_ID,PARENT_STRUCTURE_TYPE_ID
3091 FROM BOM_STRUCTURE_TYPES_B
3092 CONNECT BY PRIOR PARENT_STRUCTURE_TYPE_ID = STRUCTURE_TYPE_ID
3093 START WITH STRUCTURE_TYPE_ID = p_structure_type_id;
3094
3095 BEGIN
3096 x_return_status := l_return_status;
3097 l_additional_class_Code_list := NULL;
3098
3099 IF p_object_name = 'BOM_STRUCTURE' THEN
3100 l_object_name := 'BOM_STRUCTURE';
3101 l_attribute_group_type := 'BOM_STRUCTUREMGMT_GROUP';
3102
3103 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
3104 ( EGO_COL_NAME_VALUE_PAIR_OBJ('BILL_SEQUENCE_ID', p_bill_sequence_id));
3105
3106 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
3107 (EGO_COL_NAME_VALUE_PAIR_OBJ('STRUCTURE_TYPE_ID', p_structure_type_id));
3108
3109 ELSIF p_object_name = 'BOM_COMPONENTS' THEN
3110 l_object_name := 'BOM_COMPONENTS';
3111 l_attribute_group_type := 'BOM_COMPONENTMGMT_GROUP';
3112 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
3113 ( EGO_COL_NAME_VALUE_PAIR_OBJ('COMPONENT_SEQUENCE_ID', p_component_sequence_id)
3114 , EGO_COL_NAME_VALUE_PAIR_OBJ('BILL_SEQUENCE_ID', p_bill_sequence_id));
3115
3116 l_class_code_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY
3117 (EGO_COL_NAME_VALUE_PAIR_OBJ('STRUCTURE_TYPE_ID', p_structure_type_id));
3118
3119 END IF;
3120
3121 l_application_id := 702;
3122
3123 FOR st_type_rec IN parent_st_type_cursor
3124 LOOP
3125 IF (st_type_rec.PARENT_STRUCTURE_TYPE_ID IS NOT NULL) THEN
3126 l_additional_class_Code_list := l_additional_class_Code_list || st_type_rec.PARENT_STRUCTURE_TYPE_ID || ',';
3127 END IF;
3128 END LOOP;
3129
3130 IF l_additional_class_Code_list IS NULL THEN
3131 l_additional_class_Code_list := '1';
3132 ELSE
3133 l_additional_class_Code_list := l_additional_class_Code_list|| p_structure_type_id ;
3134 END IF;
3135 EGO_USER_ATTRS_DATA_PVT.Apply_Default_Vals_For_Entity
3136 ( p_object_name => l_object_name
3137 ,p_application_id => l_application_id
3138 ,p_attr_group_type => l_attribute_group_type
3139 ,p_attr_groups_to_exclude => null
3140 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
3141 ,p_class_code_name_value_pairs => l_class_code_name_value_pairs
3142 ,p_data_level_values => null
3143 ,p_additional_class_Code_list => l_additional_class_Code_list
3144 ,p_init_error_handler => 'T'
3145 ,p_init_fnd_msg_list => 'T'
3146 ,p_log_errors => 'T'
3147 ,p_add_errors_to_fnd_stack => 'T'
3148 ,P_commit => 'F'
3149 ,x_failed_row_id_list => l_failed_row_id_list
3150 ,x_return_status => l_return_status
3151 ,x_errorcode => l_error_code
3152 ,x_msg_count => l_msg_count
3153 ,x_msg_data => l_msg_data
3154 );
3155
3156 x_return_status := l_return_status ;
3157
3158 EXCEPTION
3159 WHEN OTHERS THEN
3160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3161 x_msg_data := SQLERRM;
3162
3163 END uda_attribute_defaulting;
3164
3165 /*
3166 * This API is the Starting point for Logging time statistics
3167 * for Performance Testing.G_TIME_LOGGED stores the start time and
3168 * G_METHOD_LOGGED stores the current API for which time is logged
3169 */
3170 PROCEDURE Init_Logging
3171 IS
3172 BEGIN
3173 G_TIME_LOGGED := new NUM_VARRAY();
3174 G_METHOD_LOGGED := new VARCHAR2_VARRAY();
3175 G_TOP := 0 ;
3176 END Init_Logging;
3177
3178 /*
3179 * API for writing the Performance Statistics in XML format.
3180 * This will create the data in xml format in the fnd_util_file
3181 */
3182 PROCEDURE Start_Logging(flow_name VARCHAR2,flow_id NUMBER)
3183 IS
3184 BEGIN
3185 IF ((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) AND ( G_PROFILE_ENABLED = FND_PROFILE.VALUE('BOM:LOG_EXECUTION_TIME'))) THEN
3186 G_FLOW_ID := flow_id;
3187 FND_FILE.put_line(FND_FILE.LOG,'BOM Performance Log: <PerformanceStatistics flowID= "' || flow_id || '" flowName= "' || flow_name || '" >');
3188 END IF;
3189 END Start_Logging;
3190
3191 /*
3192 * API for Logging the beginning of any Procedure whose execution time
3193 * needs to calculated
3194 */
3195 PROCEDURE Log_Start_Time(operation_name VARCHAR2)
3196 IS
3197 BEGIN
3198 G_TIME_LOGGED.extend;
3199 G_METHOD_LOGGED.extend;
3200 IF G_TOP < G_STACK_SIZE THEN
3201 G_TOP := G_TOP + 1;
3202 SELECT dbms_utility.get_time INTO G_TIME_LOGGED(G_TOP) FROM dual;
3203 G_METHOD_LOGGED(G_TOP) := operation_name;
3204 END IF;
3205 END Log_Start_Time;
3206
3207 /*
3208 * API for calculating the execution time for any API
3209 */
3210 PROCEDURE Log_Exec_Time
3211 IS
3212 temp1 NUMBER;
3213 temp2 NUMBER;
3214 BEGIN
3215 IF ((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) AND ( G_PROFILE_ENABLED = FND_PROFILE.VALUE('BOM:LOG_EXECUTION_TIME'))) THEN
3219 FND_FILE.put_line(FND_FILE.LOG,'BOM Performance Log: <Operation flowID= "' || G_FLOW_ID || '" flowName= "' || G_METHOD_LOGGED(G_TOP) || '" ExecTimeinMillis= "'|| (temp2 - temp1) || '" >');
3216 IF G_TOP > 0 THEN
3217 temp1 := G_TIME_LOGGED(G_TOP);
3218 SELECT dbms_utility.get_time INTO temp2 FROM dual;
3220 G_TOP := G_TOP -1 ;
3221 END IF;
3222 END IF;
3223 END Log_Exec_Time;
3224
3225 /*
3226 * API for creating the correct closing tags for the xml format getting
3227 * created in fnd util file
3228 */
3229
3230
3231 PROCEDURE Stop_Logging
3232 IS
3233 BEGIN
3234 IF ((FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) AND ( G_PROFILE_ENABLED = FND_PROFILE.VALUE('BOM:LOG_EXECUTION_TIME'))) THEN
3235 FND_FILE.put_line(FND_FILE.LOG,'BOM Performance Log: </PerformanceStatistics>');
3236 END IF;
3237 END Stop_Logging;
3238
3239
3240 PROCEDURE set_show_Impl_comps_only(p_option IN VARCHAR2)
3241 IS
3242 BEGIN
3243 G_SHOW_IMPL_COMPS_ONLY := p_option;
3244 END;
3245
3246
3247 FUNCTION get_show_Impl_comps_only RETURN VARCHAR2
3248 IS
3249 BEGIN
3250 RETURN G_SHOW_IMPL_COMPS_ONLY;
3251 END;
3252
3253
3254 FUNCTION check_chg_pol_for_delete(p_bill_seq_id IN NUMBER,
3255 p_comp_seq_id IN NUMBER,
3256 p_start_revision IN VARCHAR2,
3257 p_end_revision IN VARCHAR2,
3258 p_start_rev_id IN NUMBER,
3259 p_end_rev_id IN NUMBER,
3260 p_effective_date IN DATE,
3261 p_disable_date IN DATE,
3262 p_current_chg_pol IN VARCHAR2) RETURN VARCHAR2
3263 IS
3264 l_item_id NUMBER;
3265 l_org_id NUMBER;
3266 l_str_type_id NUMBER;
3267 l_initial_rev mtl_item_revisions_b.revision%TYPE;
3268 BEGIN
3269
3270 SELECT assembly_item_id, organization_id, structure_type_id
3271 INTO l_item_id, l_org_id, l_str_type_id
3272 FROM BOM_STRUCTURES_B
3273 WHERE bill_sequence_id = p_bill_seq_id;
3274
3275 IF p_comp_seq_id IS NOT NULL --Component delete
3276 THEN
3277 RETURN Check_Change_Policy_Range (p_item_id => l_item_id,
3278 p_org_id => l_org_id,
3279 p_component_sequence_id => p_comp_seq_id,
3280 p_current_chg_pol => p_current_chg_pol,
3281 p_structure_type_id => l_str_type_id,
3282 p_context_rev_id => null,
3283 p_use_eco => 'N');
3284 ELSE --structure delete
3285 SELECT revision
3286 INTO l_initial_rev
3287 FROM (SELECT revision
3288 FROM mtl_item_revisions_b
3289 WHERE inventory_item_id = l_item_id
3290 AND organization_id = l_org_id
3291 AND implementation_date IS NOT NULL
3292 ORDER BY effectivity_date)
3293 WHERE ROWNUM = 1;
3294
3295 RETURN Check_Change_Policy_Range (p_item_id => l_item_id,
3296 p_org_id => l_org_id,
3297 p_start_revision => l_initial_rev,
3298 p_end_revision => null,
3299 p_start_rev_id => null,
3300 p_end_rev_id => null,
3301 p_effective_date => null,
3302 p_disable_date => null,
3303 p_current_chg_pol => p_current_chg_pol,
3304 p_structure_type_id => l_str_type_id,
3305 p_use_eco => 'N');
3306 END IF;
3307
3308 END;
3309
3310
3311 FUNCTION get_comp_names(p_comp_seq_ids IN VARCHAR2) RETURN VARCHAR2
3312 IS
3313 l_item_id NUMBER;
3314 l_org_id NUMBER;
3315 l_item_names VARCHAR2(32767) := '';
3316
3317 CURSOR get_item_details
3318 IS
3319 SELECT pk1_value, pk2_value
3320 FROM bom_components_b
3321 WHERE INSTR(','||p_comp_seq_ids||',', ','||component_sequence_id||',') > 0;
3322
3323 BEGIN
3324 FOR item IN get_item_details
3325 LOOP
3326 l_item_id := item.pk1_value;
3327 l_org_id := item.pk2_value;
3328 l_item_names := l_item_names || Get_Item_Name(l_item_id, l_org_id) || ', '; END LOOP;
3329 IF (l_item_names IS NOT NULL) THEN
3330 l_item_names := substr(l_item_names, 0, length(l_item_names) - 2);
3331 END IF;
3332 IF(length(l_item_names) >= 4000) THEN
3333 l_item_names := substr(l_item_names, 0, 3994) || ', ...';
3334 END IF;
3335 RETURN l_item_names;
3336 END;
3337
3338
3339 FUNCTION get_lookup_meaning(p_lookup_type IN VARCHAR2,
3340 p_lookup_code IN VARCHAR2) RETURN VARCHAR2
3341 IS
3342 l_meaning mfg_lookups.meaning%TYPE := NULL;
3343 BEGIN
3344 SELECT meaning
3345 INTO l_meaning
3346 FROM mfg_lookups
3347 WHERE lookup_type = p_lookup_type
3348 AND lookup_code = p_lookup_code;
3349
3350 RETURN l_meaning;
3351 EXCEPTION
3352 WHEN No_Data_Found THEN
3353 RETURN l_meaning;
3354 END;
3355
3356 /****************************************************************************
3357 * Procedure : Set_Profile_Org_id
3358 * Returns : None
3359 * Parameters IN : Organization_Id
3360 * Parameters OUT: None
3361 * Purpose : Will set the 'MFG_ORGANIZATION_ID in the profile
3362 * For Bug id
3363 *****************************************************************************/
3364 PROCEDURE Set_Profile_Org_id
3365 ( p_org_id IN NUMBER)
3366 IS
3367 BEGIN
3368
3369 fnd_profile.put('MFG_ORGANIZATION_ID', p_org_id);
3370
3371 END Set_Profile_Org_id;
3372 END BOM_Globals;