[Home] [Help]
PACKAGE BODY: APPS.FII_GL_TOP_SPENDERS_PKG
Source
1 PACKAGE BODY fii_gl_top_spenders_pkg AS
2 /* $Header: FIIGLTSB.pls 120.3 2006/05/05 10:08:56 hpoddar noship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5 g_retcode VARCHAR2(20) := NULL;
6 g_phase VARCHAR2(100);
7 g_fii_schema VARCHAR2(30);
8 g_fii_user_id NUMBER(15);
9 g_fii_login_id NUMBER(15);
10
11 PROCEDURE INIT IS
12
13 l_status VARCHAR2(30);
14 l_industry VARCHAR2(30);
15 l_ap_row_cnt NUMBER;
16
17 BEGIN
18
19 ----------------------------------------------------------
20 -- Determine whether ap base summary (FII_AP_INV_B) is populated
21 ----------------------------------------------------------
22 g_phase := 'Determine whether ap base summary is populated ';
23 SELECT 1 INTO l_ap_row_cnt
24 FROM FII_AP_INV_B
25 WHERE rownum = 1;
26
27 ----------------------------------------------------------
28 -- Find the schema owner of FII
29 ----------------------------------------------------------
30
31 g_phase := 'Find FII schema';
32 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry,
33 g_fii_schema))THEN
34 NULL;
35 END IF;
36
37 ----------------------------------------------------------
38 -- Find user id and user login
39 ----------------------------------------------------------
40
41 g_phase := 'Find User ID and User Login';
42
43 g_fii_user_id := FND_GLOBAL.User_Id;
44 g_fii_login_id := FND_GLOBAL.Login_Id;
45
46 ----------------------------------------------------------
47 -- Truncate staging and base tables
48 ----------------------------------------------------------
49 g_phase := 'Truncate table FII_TOP_SPENDERS_STG';
50 FII_UTIL.truncate_table ('FII_TOP_SPENDERS_STG', 'FII', g_retcode);
51
52 g_phase := 'Truncate table FII_TOP_SPNDR_SUM_B';
53 FII_UTIL.truncate_table ('FII_TOP_SPNDR_SUM_B', 'FII', g_retcode);
54
55 Exception
56
57 WHEN NO_DATA_FOUND THEN
58 FII_UTIL.Write_Log ( 'Phase: ' || g_phase);
59 FII_MESSAGE.write_log(msg_name => 'FII_GL_AP_BASE_EMPTY',
60 token_num => 0);
61 g_retcode := 1;
62 RAISE;
63
64 WHEN OTHERS THEN
65 g_retcode := -1;
66
67 FII_UTIL.write_log('
68 ---------------------------------
69 Error in Procedure: INIT
70 Phase: '||g_phase||'
71 Message: '||sqlerrm);
72
73 RAISE;
74
75 END INIT;
76
77
78 PROCEDURE POPULATE_STG_MONTHS IS
79
80 BEGIN
81
82 if g_debug_flag = 'Y' then
83 fii_util.write_log('Populating month slices in fii_top_spenders_stg table');
84 end if;
85
86 g_phase := 'Insert month slices into fii_top_spenders_stg table';
87
88 INSERT /*+ append parallel(stg) */ INTO FII_TOP_SPENDERS_STG (
89 period_id,
90 slice_type_flag,
91 qtr_id,
92 year_id,
93 person_id,
94 ccc_org_id,
95 prim_amount_g,
96 sec_amount_g,
97 no_of_exp_rpts,
98 last_update_date,
99 last_updated_by,
100 creation_date,
101 created_by,
102 last_update_login)
103 SELECT /*+ ordered use_hash(b,m) parallel(b) parallel(m) */
104 t.ENT_PERIOD_ID,
105 'M',
106 t.ENT_QTR_ID,
107 t.ENT_YEAR_ID,
108 b.employee_id,
109 m.COMPANY_COST_CENTER_ORG_ID,
110 sum(b.PRIM_AMOUNT_G),
111 sum(b.SEC_AMOUNT_G) ,
112 count(distinct b.INVOICE_ID),
113 sysdate,
114 g_fii_user_id,
115 sysdate,
116 g_fii_user_id,
117 g_fii_login_id
118 FROM fii_time_day t, FII_AP_INV_B b, FII_COM_CC_MAPPINGS m
119 WHERE b.discretionary_expense_flag = 'Y'
120 and b.account_Date = t.report_date
121 and b.COMPANY_ID = m.COMPANY_ID
122 and b.COST_CENTER_ID = m.COST_CENTER_ID
123 GROUP BY m.COMPANY_COST_CENTER_ORG_ID, b.employee_id, t.ENT_YEAR_ID, t.ENT_QTR_ID, t.ENT_PERIOD_ID;
124
125 if g_debug_flag = 'Y' then
126 fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPENDERS_STG');
127 end if;
128
129 commit;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 g_retcode := -1;
134
135 FII_UTIL.write_log('
136 ----------------------------
137 Error in Function: POPULATE_STG_MONTHS
138 Phase: '||g_phase||'
139 Message: '||sqlerrm);
140
141 RAISE;
142
143 END POPULATE_STG_MONTHS;
144
145 PROCEDURE POPULATE_STG_QUARTERS IS
146
147 BEGIN
148
149 if g_debug_flag = 'Y' then
150 fii_util.write_log('Populating quarter slices in fii_top_spenders_stg table');
151 end if;
152
153 g_phase := 'Insert quarter slices into fii_top_spenders_stg table';
154
155 INSERT /*+ append parallel(stg) */ INTO FII_TOP_SPENDERS_STG (
156 period_id,
157 slice_type_flag,
158 qtr_id,
159 year_id,
160 person_id,
161 ccc_org_id,
162 prim_amount_g,
163 sec_amount_g,
164 no_of_exp_rpts,
165 last_update_date,
166 last_updated_by,
167 creation_date,
168 created_by,
169 last_update_login)
170
171 SELECT /*+ parallel(t) */
172 t.PERIOD_ID,
173 'Q',
174 t.QTR_ID,
175 t.YEAR_ID,
176 t.person_id,
177 t.CCC_ORG_ID,
178 SUM(t.PRIM_AMOUNT_G) OVER (PARTITION BY t.QTR_ID, t.YEAR_ID, t.CCC_ORG_ID, t.person_id ORDER BY t.period_id ROWS UNBOUNDED PRECEDING) AS PRIM_AMOUNT_G,
179 SUM(t.SEC_AMOUNT_G) OVER (PARTITION BY t.QTR_ID, t.YEAR_ID, t.CCC_ORG_ID, t.person_id ORDER BY t.period_id ROWS UNBOUNDED PRECEDING) AS SEC_AMOUNT_G,
180 SUM(t.no_of_exp_rpts) OVER (PARTITION BY t.QTR_ID, t.YEAR_ID, t.CCC_ORG_ID, t.person_id ORDER BY t.period_id ROWS UNBOUNDED PRECEDING) AS no_of_exp_rpts,
181 sysdate,
182 g_fii_user_id,
183 sysdate,
184 g_fii_user_id,
185 g_fii_login_id
186 FROM fii_top_spenders_stg t;
187
188 if g_debug_flag = 'Y' then
189 fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPENDERS_STG');
190 end if;
191
192 commit;
193
194 EXCEPTION
195 WHEN OTHERS THEN
196 g_retcode := -1;
197
198 FII_UTIL.write_log('
199 ----------------------------
200 Error in Function: POPULATE_STG_QUARTERS
201 Phase: '||g_phase||'
202 Message: '||sqlerrm);
203
204 RAISE;
205
206 END POPULATE_STG_QUARTERS;
207
208
209 PROCEDURE POPULATE_STG_YEARS IS
210
211 BEGIN
212
213 if g_debug_flag = 'Y' then
214 fii_util.write_log('Populating year slices in fii_top_spenders_stg table');
215 end if;
216
217 g_phase := 'Insert year slices into fii_top_spenders_stg table';
218
219 INSERT /*+ append parallel(stg) */ INTO FII_TOP_SPENDERS_STG (
220 period_id,
221 slice_type_flag,
222 qtr_id,
223 year_id,
224 person_id,
225 ccc_org_id,
226 prim_amount_g,
227 sec_amount_g,
228 no_of_exp_rpts,
229 last_update_date,
230 last_updated_by,
231 creation_date,
232 created_by,
233 last_update_login)
234 SELECT /*+ append parallel(b) */
235 b.PERIOD_ID,
236 'Y',
237 b.QTR_ID,
238 b.YEAR_ID,
239 b.person_id,
240 b.CCC_ORG_ID,
241 SUM(B.PRIM_AMOUNT_G) OVER (PARTITION BY B.YEAR_ID, b.CCC_ORG_ID, b.person_id ORDER BY b.period_id ROWS UNBOUNDED PRECEDING) AS PRIM_AMOUNT_G,
242 SUM(B.SEC_AMOUNT_G) OVER (PARTITION BY B.YEAR_ID, b.CCC_ORG_ID, b.person_id ORDER BY b.period_id ROWS UNBOUNDED PRECEDING) AS SEC_AMOUNT_G,
243 SUM(B.no_of_exp_rpts) OVER (PARTITION BY B.YEAR_ID, b.CCC_ORG_ID, b.person_id ORDER BY b.period_id ROWS UNBOUNDED PRECEDING) AS no_of_exp_rpts,
244 sysdate,
245 g_fii_user_id,
246 sysdate,
247 g_fii_user_id,
248 g_fii_login_id
249 FROM fii_top_spenders_stg b
250 WHERE b.slice_type_flag = 'M';
251
252 if g_debug_flag = 'Y' then
253 fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPENDERS_STG');
254 end if;
255
256 commit;
257
258 EXCEPTION
259 WHEN OTHERS THEN
260 g_retcode := -1;
261
262 FII_UTIL.write_log('
263 ----------------------------
264 Error in Function: POPULATE_STG_YEARS
265 Phase: '||g_phase||'
266 Message: '||sqlerrm);
267
268 RAISE;
269
270 END POPULATE_STG_YEARS;
271
272 PROCEDURE POPULATE_SUMMARY IS
273
274 BEGIN
275
276 if g_debug_flag = 'Y' then
277 fii_util.write_log('Populating fii_top_spndr_sum_b table');
278 end if;
279
280 g_phase := 'Insert rows into fii_top_spndr_sum_b table';
281
282 INSERT /*+ append parallel(b) */ INTO fii_top_spndr_sum_b (
283 person_id,
284 period_id,
285 slice_type_flag,
286 manager_id,
287 rank_within_manager_ptd,
288 prim_ptd_g,
289 sec_ptd_g,
290 no_exp_reports_ptd,
291 last_update_date,
292 last_updated_by,
293 creation_date,
294 created_by,
295 last_update_login)
296 SELECT
297 person_id,
298 period_id,
299 slice_type_flag,
300 manager_id,
301 RANK_WITHIN_MANAGER_ptd,
302 prim_ptd_g,
303 sec_ptd_g,
304 no_of_exp_rpts_ptd,
305 sysdate,
306 g_fii_user_id,
307 sysdate,
308 g_fii_user_id,
309 g_fii_login_id
310 FROM (SELECT /*+ ordered use_hash(stg) parallel(stg) */
311 stg.person_id person_id,
312 stg.period_id period_id,
313 stg.slice_type_flag,
314 help.manager_id manager_id,
315 RANK() OVER (PARTITION BY stg.period_id, stg.slice_type_flag, help.manager_id
316 ORDER BY sum(stg.prim_amount_g) DESC) AS RANK_WITHIN_MANAGER_ptd ,
317 sum(stg.prim_amount_g) prim_ptd_g,
318 sum(stg.sec_amount_g) sec_ptd_g,
319 sum(stg.no_of_exp_rpts) no_of_exp_rpts_ptd
320 FROM fii_org_mgr_mappings help, fii_top_spenders_stg stg
321 WHERE help.ccc_org_id = stg.ccc_org_id
322 GROUP BY stg.person_id ,
323 stg.period_id ,
324 stg.slice_type_flag,
325 help.manager_id ) x
326 WHERE x.RANK_WITHIN_MANAGER_ptd <= 10;
327
328 if g_debug_flag = 'Y' then
329 fii_util.write_log('Inserted '||SQL%ROWCOUNT||' rows into FII_TOP_SPNDR_SUM_B');
330 end if;
331
332 FND_STATS.gather_table_stats
333 (ownname => g_fii_schema,
334 tabname => 'FII_TOP_SPNDR_SUM_B');
335 commit;
336
337 EXCEPTION
338 WHEN OTHERS THEN
339 g_retcode := -1;
340
341 FII_UTIL.write_log('
342 ----------------------------
343 Error in Function: POPULATE_SUMMARY
344 Phase: '||g_phase||'
345 Message: '||sqlerrm);
346
347 RAISE;
348
349 END POPULATE_SUMMARY;
350
351 ----------------------------------
352 -- Public Functions and Procedures
353 ----------------------------------
354
355 PROCEDURE Main ( ERRBUF IN OUT NOCOPY VARCHAR2,
356 RETCODE IN OUT NOCOPY VARCHAR2) IS
357 l_dir VARCHAR2(400);
358 BEGIN
359 l_dir:=FII_UTIL.get_utl_file_dir;
360 FII_UTIL.initialize('fii_gl_top_spenders_pkg.log','fii_gl_top_spenders_pkg.out',l_dir, 'fii_gl_top_spenders_pkg');
361
362 INIT;
363 POPULATE_STG_MONTHS;
364 POPULATE_STG_QUARTERS;
365 POPULATE_STG_YEARS;
366 POPULATE_SUMMARY;
367
368 -- Exception handling
369 EXCEPTION
370 WHEN NO_DATA_FOUND THEN
371 RETCODE := g_retcode;
372 WHEN OTHERS THEN
373 RETCODE := g_retcode;
374
375 END MAIN;
376
377 END fii_gl_top_spenders_pkg;