[Home] [Help]
PACKAGE BODY: APPS.BIL_OBI_GROUPS_DENORM_PKG
Source
1 PACKAGE BODY BIL_OBI_GROUPS_DENORM_PKG AS
2 /*$Header: bilobieesgb.pls 120.0.12000000.1 2007/04/12 06:04:26 kreardon noship $*/
3
4 g_pkg VARCHAR2(240);
5 g_row_num NUMBER;
6
7 PROCEDURE load(errbuf IN OUT NOCOPY VARCHAR2,
8 retcode IN OUT NOCOPY VARCHAR2) IS
9
10 l_proc VARCHAR2(100);
11 l_stmt VARCHAR2(400);
12 p_table_name VARCHAR2(400);
13 l_schema_name VARCHAR2(400);
14 BEGIN
15
16 g_pkg := 'bil.patch.115.sql.bil_obi_groups_denorm_pkg.';
17 l_proc := 'load';
18 p_table_name := 'BIL_OBI_RS_GROUPS_DENORM';
19
20 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
21 bil_bi_util_collection_pkg.writeLog(
22 p_log_level => fnd_log.LEVEL_PROCEDURE,
23 p_module => g_pkg || l_proc || ' begin',
24 p_msg => 'Start of Procedure '|| l_proc);
25 END IF;
26
27 l_schema_name := bil_bi_util_collection_pkg.get_schema_name('BIL');
28 l_stmt:='TRUNCATE TABLE '|| l_schema_name || '.' || p_table_name;
29
30 EXECUTE IMMEDIATE l_stmt;
31
32 INSERT INTO BIL_OBI_RS_GROUPS_DENORM
33 (DENORM_GRP_ID,
34 PARENT_GROUP_ID,
35 GROUP_ID,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATE_LOGIN,
41 IMMEDIATE_PARENT_FLAG,
42 START_DATE_ACTIVE,
43 END_DATE_ACTIVE,
44 ACTIVE_FLAG,
45 DENORM_LEVEL,
46 GROUP_LEVEL1,
47 GROUP_LEVEL2,
48 GROUP_LEVEL3,
49 GROUP_LEVEL4)
50 select denorm_grp_id, parent_group_id, group_id, created_by, creation_date,last_updated_by, last_update_date,
51 last_update_login, immediate_parent_flag, start_date_active, end_date_active, active_flag, denorm_level,
52 decode(denorm_level,0,group_id, group_level1) group_level1,
53 decode(denorm_level,0,group_id,1,group_id,group_level2) group_level2,
54 decode(denorm_level,0,group_id,1,group_id,2,group_id,group_level3) group_level3,
55 decode(denorm_level,0,group_id,1,group_id,2,group_id,3,group_id,group_level4) group_level4
56 from
57 (select denorm_grp_id, parent_group_id, group_id, created_by, creation_date,last_updated_by, last_update_date,
58 last_update_login, immediate_parent_flag, start_date_active, end_date_active, active_flag, denorm_level, group_level1,
59 group_level2, group_level3, group_level4, lag(group_level4) over (partition by group_id order by group_id, denorm_level) group_level5
60 from
61 (select denorm_grp_id, group_id, parent_group_id, created_by, creation_date,last_updated_by, last_update_date,
62 last_update_login, immediate_parent_flag, start_date_active, end_date_active, active_flag, denorm_level, group_level1,
63 group_level2, group_level3, lag(group_level3) over (partition by group_id order by group_id, denorm_level) group_level4
64 from
65 (select denorm_grp_id, group_id, parent_group_id, created_by, creation_date,last_updated_by, last_update_date,
66 last_update_login, immediate_parent_flag, start_date_active, end_date_active, active_flag, denorm_level, group_level1,
67 group_level2, lag(group_level2) over (partition by group_id order by group_id, denorm_level) group_level3
68 from
69 (select denorm_grp_id, group_id, parent_group_id, created_by, creation_date,last_updated_by, last_update_date,
70 last_update_login, immediate_parent_flag, start_date_active, end_date_active, active_flag, denorm_level, group_level1,
71 lag(group_level1) over (partition by group_id order by group_id, denorm_level) group_level2
72 from
73 (select denorm_grp_id, group_id, parent_group_id, created_by, creation_date,last_updated_by, last_update_date,
74 last_update_login, immediate_parent_flag, start_date_active, end_date_active, active_flag, denorm_level,
75 lag(parent_group_id) over (partition by group_id order by group_id, denorm_level) group_level1
76 from
77 (select j.denorm_grp_id, j.group_id, j.parent_group_id, j.created_by, j.creation_date,j.last_updated_by,
78 j.last_update_date,j.last_update_login, j.immediate_parent_flag, j.start_date_active, j.end_date_active,
79 j.active_flag, j.denorm_level
80 from jtf_rs_groups_denorm j, jtf_rs_group_usages u
81 where j.group_id = u.group_id
82 and u.usage = 'SALES'
83 and latest_relationship_flag = 'Y'
84 order by group_id, denorm_level))))));
85
86
87 g_row_num := sql%rowcount;
88
89 COMMIT;
90
91 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
92 bil_bi_util_collection_pkg.writeLog(
93 p_log_level => fnd_log.LEVEL_EVENT,
94 p_module => g_pkg || l_proc ,
95 p_msg => 'Inserted '||g_row_num||' into BIL_OBI_RS_GROUPS_DENORM table from JTF_RS_GROUPS_DENORM');
96 END IF;
97
98 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
99 bil_bi_util_collection_pkg.writeLog(
100 p_log_level => fnd_log.LEVEL_PROCEDURE,
101 p_module => g_pkg || l_proc || ' end ',
102 p_msg => 'End of Procedure '|| l_proc);
103 END IF;
104
105 Exception
106 When Others Then
107 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
108 fnd_message.set_token('ERRNO' ,SQLCODE);
109 fnd_message.set_token('REASON' ,SQLERRM);
110 fnd_message.set_token('ROUTINE' , l_proc);
111 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
112 p_module => g_pkg || l_proc || ' proc_error',
113 p_msg => fnd_message.get,
114 p_force_log => TRUE);
115
116 RAISE;
117
118 END load;
119
120
121 END BIL_OBI_GROUPS_DENORM_PKG;