[Home] [Help]
PACKAGE BODY: APPS.FII_USER_SEC_OPTIMIZER
Source
1 PACKAGE BODY FII_USER_SEC_OPTIMIZER AS
2 /*$Header: FIIUSECB.pls 120.2 2006/01/12 22:50:08 mmanasse noship $*/
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5 g_phase VARCHAR2(100);
6 g_fii_user_id NUMBER(15);
7 g_fii_login_id NUMBER(15);
8 g_schema_name VARCHAR2(150) := 'FII';
9
10
11 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2,
12 retcode IN OUT NOCOPY VARCHAR2)
13 IS
14
15 FIIUSECB_fatal_err EXCEPTION;
16 l_dir VARCHAR2(400);
17 l_retcode varchar2(15) := 0;
18 ret_val BOOLEAN := FALSE;
19 l_company_top_node_id NUMBER(15);
20 l_cost_ctr_top_node_id NUMBER(15);
21
22 BEGIN
23 --errbuf := NULL;
24 --retcode := 0;
25
26 g_phase := 'Do set up for log file';
27 ----------------------------------------------
28 -- Do set up for log file
29 ----------------------------------------------
30
31 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
32 ------------------------------------------------------
33 -- Set default directory in case if the profile option
34 -- BIS_DEBUG_LOG_DIRECTORY is not set up
35 ------------------------------------------------------
36 if l_dir is NULL then
37 l_dir := FII_UTIL.get_utl_file_dir;
38 end if;
39
40 FII_UTIL.initialize('FII_USER_SEC_OPTIMIZER.log',
41 'FII_USER_SEC_OPTIMIZER.out',l_dir,
42 'FII_USER_SEC_OPTIMIZER');
43
44
45 g_fii_user_id := FND_GLOBAL.User_Id;
46 g_fii_login_id := FND_GLOBAL.Login_Id;
47
48 IF g_debug_flag = 'Y' THEN
49 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_center_grants.');
50 END IF;
51
52
53 -------------------------------------------------------------
54 --- Truncate grants tables ---------------------------------
55 ------------------------------------------------------------
56 g_phase := 'Truncating grants tables FII_COMPANY_GRANTS and FII_COST_CENTER_GRANTS...';
57 if g_debug_flag = 'Y' then
58 FII_UTIL.write_log(g_phase);
59 end if;
60
61 fii_util.truncate_table('FII_COMPANY_GRANTS', 'FII', l_retcode);
62 IF l_retcode = -1 then
63 fii_util.write_log('Error in fii_util.truncate_table(''FII_COMPANY_GRANTS'', ''FII'', l_retcode)');
64 raise FIIUSECB_fatal_err;
65 END IF;
66 fii_util.truncate_table('FII_COST_CENTER_GRANTS', 'FII', l_retcode);
67 IF l_retcode = -1 then
68 fii_util.write_log('Error in fii_util.truncate_table(''FII_COST_CENTER_GRANTS'', ''FII'', l_retcode)');
69 raise FIIUSECB_fatal_err;
70 END IF;
71
72 g_phase := 'Selecting top nodes for company and cost center dimensions from fii_financial_dimensions.';
73
74 select dbi_hier_top_node_id into l_company_top_node_id from fii_financial_dimensions where dimension_short_name='FII_COMPANIES';
75 select dbi_hier_top_node_id into l_cost_ctr_top_node_id from fii_financial_dimensions where dimension_short_name='HRI_CL_ORGCC';
76
77 IF (l_company_top_node_id is null) or (l_cost_ctr_top_node_id is null) THEN
78 fii_util.write_log('Error: Top node for the company or cost center dimension is not assigned.');
79 raise FIIUSECB_fatal_err;
80 END IF;
81
82 g_phase := 'Inserting into fii_company_grants from bis_grants_v.';
83
84
85 INSERT INTO fii_company_grants
86 (user_id,
87 report_region_code,
88 company_id,
89 aggregated_flag,
90 last_update_date, last_updated_by,
91 creation_date, created_by, last_update_login)
92 (SELECT DISTINCT u.user_id,
93 s.report_region_code,
94 decode(s.granted_for,
95 -999, l_company_top_node_id,
96 s.granted_for),
97 h.aggregated_flag,
98 sysdate, g_fii_user_id,
99 sysdate, g_fii_user_id, g_fii_login_id
100 FROM bis_grants_v s, --user_security_initial2 s,
101 fii_com_pmv_agrt_nodes h,
102 fnd_user u
103 WHERE decode(s.granted_for,
104 -999, l_company_top_node_id,
105 s.granted_for) = h.company_id
106 AND s.delegation_parameter='FII_COMPANIES'
107 AND (sysdate BETWEEN TRUNC(s.start_date) AND nvl(TRUNC(s.end_date),to_date('12-31-9999','MM-DD-YYYY')))
108 AND s.granted_to = u.employee_id);
109
110 IF g_debug_flag = 'Y' THEN
111 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_grants.');
112 END IF;
113 --DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows into fii_company_grants.');
114
115 g_phase := 'Inserting into fii_cost_center_grants from bis_grants_v.';
116
117 INSERT INTO fii_cost_center_grants
118 (user_id,
119 report_region_code,
120 cost_center_id,
121 aggregated_flag,
122 last_update_date, last_updated_by,
123 creation_date, created_by, last_update_login)
124 (SELECT DISTINCT u.user_id,
125 s.report_region_code,
126 decode(s.granted_for,
127 -999, l_cost_ctr_top_node_id,
128 s.granted_for),
129 h.aggregated_flag,
130 sysdate, g_fii_user_id,
131 sysdate, g_fii_user_id, g_fii_login_id
132 FROM bis_grants_v s, --user_security_initial2 s,
133 fii_cc_pmv_agrt_nodes h,
134 fnd_user u
135 WHERE decode(s.granted_for,
136 -999, l_cost_ctr_top_node_id,
137 s.granted_for) = h.cost_center_id
138 AND s.delegation_parameter='HRI_CL_ORGCC'
139 AND (sysdate BETWEEN TRUNC(s.start_date) AND nvl(TRUNC(s.end_date),to_date('12-31-9999','MM-DD-YYYY')))
140 AND s.granted_to = u.employee_id);
141
142 IF g_debug_flag = 'Y' THEN
143 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_center_grants.');
144 END IF;
145 --DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows into fii_cost_center_grants.');
146
147 g_phase := 'Filtering out child company assignments from fii_company_grants.';
148
149 DELETE FROM fii_company_grants
150 WHERE (user_id, report_region_code, company_id) IN
151 (SELECT s.user_id, s.report_region_code, s.company_id FROM fii_company_grants s, fii_company_hierarchies h
152 WHERE s.company_id = h.child_company_id
153 AND h.parent_company_id IN (SELECT company_id from fii_company_grants where user_id=s.user_id and report_region_code=s.report_region_code)
154 AND h.parent_company_id <> h.child_company_id);
155
156 IF g_debug_flag = 'Y' THEN
157 FII_UTIL.Write_Log ('Deleted ' || SQL%ROWCOUNT || ' rows from fii_company_grants.');
158 END IF;
159 --DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows from fii_company_grants.');
160
161 g_phase := 'Filtering out child cost center assignments from fii_cost_center_grants.';
162
163 DELETE FROM fii_cost_center_grants
164 WHERE (user_id, report_region_code, cost_center_id) IN
165 (SELECT s.user_id, s.report_region_code, s.cost_center_id FROM fii_cost_center_grants s, fii_cost_ctr_hierarchies h
166 WHERE s.cost_center_id = h.child_cc_id
167 AND h.parent_cc_id IN (SELECT cost_center_id from fii_cost_center_grants where user_id=s.user_id and report_region_code=s.report_region_code)
168 AND h.parent_cc_id <> h.child_cc_id);
169
170 IF g_debug_flag = 'Y' THEN
171 FII_UTIL.Write_Log ('Deleted ' || SQL%ROWCOUNT || ' rows from fii_cost_center_grants.');
172 END IF;
173 --DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows from fii_cost_center_grants.');
174
175 --Call FND_STATS to collect statistics after populating the table
176 g_phase := 'gather_table_stats for fii_cost_center_grants';
177 FND_STATS.gather_table_stats
178 (ownname => g_schema_name,
179 tabname => 'fii_cost_center_grants');
180
181 EXCEPTION
182 WHEN FIIUSECB_fatal_err THEN
183 FII_UTIL.write_log('Fatal errors occured:');
184 FII_UTIL.Write_Log ( 'G_PHASE: ' || g_phase);
185
186 FND_CONCURRENT.Af_Rollback;
187 retcode := sqlcode;
188 errbuf := sqlerrm;
189 ret_val := FND_CONCURRENT.Set_Completion_Status
190 (status => 'ERROR', message => substr(errbuf,1,180));
191 /* DBMS_OUTPUT.put_line('Fatal errors occured during the upload process.'); */
192
193 WHEN OTHERS THEN
194 FII_UTIL.Write_Log ('Other error in Main ');
195 FII_UTIL.Write_Log ( 'G_PHASE: ' || g_phase);
196 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
197
198 FND_CONCURRENT.Af_Rollback;
199 retcode := sqlcode;
200 errbuf := sqlerrm;
201 ret_val := FND_CONCURRENT.Set_Completion_Status
202 (status => 'ERROR', message => substr(errbuf,1,180));
203
204 --DBMS_OUTPUT.PUT_LINE('Error: ' || sqlerrm);
205
206 END Main;
207
208 END FII_USER_SEC_OPTIMIZER;