PACKAGE BODY: APPS.EGO_P4T_UPGRADE_PVT

Source

1 PACKAGE BODY ego_p4t_upgrade_pvt AS
2 /* $Header: EGOP4TUB.pls 120.3 2010/02/07 20:10:39 chechand noship $ */
3 
4   PROCEDURE upgrade_to_pim4telco (start_effective_date IN DATE)
5   IS
6     cursor non_ver_iccs IS
7       select a.item_catalog_group_id as IccID from  mtl_item_catalog_groups_b a
8       where a.item_catalog_group_id not in
9       (SELECT DISTINCT item_catalog_group_id FROM EGO_MTL_CATALOG_GRP_VERS_B);
10 
11      profile_value varchar2(1) := fnd_profile.value('EGO_ENABLE_P4T');
12      draft_str VARCHAR2(2000);
13      default_ver_str varchar2(2000);
14 
15       v_icc_count NUMBER;
16       v_versioned NUMBER;
17       v_not_versioned NUMBER;
18 
19     BEGIN
20     if profile_value <> 'Y' then
21       return;
22     end if;
23 
24     --No of ICC
25     SELECT Count(*) INTO v_icc_count FROM  mtl_item_catalog_groups_b;
26 
27     --No of ICC VERSIONED
28     SELECT Count(*) INTO v_versioned FROM  mtl_item_catalog_groups_b
29     WHERE item_catalog_group_id IN
30     (SELECT DISTINCT item_catalog_group_id FROM EGO_MTL_CATALOG_GRP_VERS_B);
31 
32     --No of ICC NOT VERSIONED
33     SELECT Count(*) INTO v_not_versioned FROM  mtl_item_catalog_groups_b
34     WHERE item_catalog_group_id NOT IN
35     (SELECT DISTINCT item_catalog_group_id FROM EGO_MTL_CATALOG_GRP_VERS_B);
36 
37     Dbms_Output.put_line('PIM For Telco upgrade script execution:');
38     Dbms_Output.put_line(' Total number of Item Catalog Categories available : '||v_icc_count);
39     Dbms_Output.put_line(' Total number of Versioned ICCs : '||v_versioned);
40     Dbms_Output.put_line(' Total number of Non-versioned ICCs : '||v_not_versioned);
41 
42 
43     SELECT message_text into draft_str
44     FROM fnd_new_messages
45     WHERE
46     application_id = (SELECT application_id
47                       FROM fnd_application
48                       WHERE application_short_name = 'EGO') AND
49     message_name = 'EGO_ICC_DRAFT_VERSION' AND
50     language_code = USERENV('LANG') ;
51 
52     SELECT message_text into default_ver_str
53     FROM fnd_new_messages
54     WHERE
55     application_id = (SELECT application_id
56                       FROM fnd_application
57                       WHERE application_short_name = 'EGO') AND
58     message_name = 'EGO_ICC_DEFAULT_VERS' AND
59     language_code = USERENV('LANG')  ;
60 
61     for rec in non_ver_iccs loop
62         insert into EGO_MTL_CATALOG_GRP_VERS_B
63           (item_catalog_group_id,
64           version_seq_id,
65           version_description,
66           start_active_date,
67           end_active_date,
68           created_by,
69           creation_date,
70           last_updated_by,
71           last_update_date,
72           last_update_login)
73         values
74           (rec.IccID,
75           0,
76           draft_str,
77           null,
78           null,
79           FND_GLOBAL.USER_ID,
80           sysdate,
81           FND_GLOBAL.USER_ID,
82           sysdate,
83           FND_GLOBAL.LOGIN_ID);
84 
85         insert into EGO_MTL_CATALOG_GRP_VERS_B
86           (item_catalog_group_id,
87           version_seq_id,
88           version_description,
89           start_active_date,
90           end_active_date,
91           created_by,
92           creation_date,
93           last_updated_by,
94           last_update_date,
95           last_update_login)
96         values
97           (rec.IccID,
98           1,
99           default_ver_str,
100           nvl(start_effective_date, sysdate),
101           null,
102           FND_GLOBAL.USER_ID,
103           sysdate,
104           FND_GLOBAL.USER_ID,
105           sysdate,
106           FND_GLOBAL.LOGIN_ID);
107 
108     end loop;
109     commit;
110     end upgrade_to_pim4telco;
111 
112 END ego_p4t_upgrade_pvt;