[Home] [Help]
PACKAGE BODY: APPS.FII_SNAP_TBL_REFRESH
Source
1 PACKAGE BODY FII_SNAP_TBL_REFRESH AS
2 /*$Header: FIISNPRB.pls 120.5 2006/06/02 00:17:15 hlchen noship $*/
3
4 g_phase VARCHAR2(80);
5 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
6 g_retcode VARCHAR2(20) := NULL;
7 g_fii_user_id NUMBER;
8 g_fii_login_id NUMBER;
9 g_fii_sysdate DATE;
10 g_schema_name VARCHAR2(120) := 'FII';
11 l_profile VARCHAR2(1);
12 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
13
14 --We use a un-shipped profile option FII_TEST_SYSDATE ("FII: Test Sysdate")
15 --to reset different sysdate so that we can test for snapshot tables.
16 g_test_sysdate DATE := to_date(FND_PROFILE.value('FII_TEST_SYSDATE'), 'DD/MM/YYYY');
17
18 ----------------------------------------------------
19 -- PROCEDURE Initialize (private)
20 --
21 ----------------------------------------------------
22
23 PROCEDURE Initialize IS
24
25 l_count NUMBER(15) := 0;
26 l_dir VARCHAR2(160);
27 l_check NUMBER;
28
29 BEGIN
30
31 g_phase := 'Do set up for log file';
32 ----------------------------------------------
33 -- Do set up for log file
34 ----------------------------------------------
35
36 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
37 ------------------------------------------------------
38 -- Set default directory in CASE if the profile option
39 -- BIS_DEBUG_LOG_DIRECTORY is not set up
40 ------------------------------------------------------
41 if l_dir is NULL THEN
42 l_dir := FII_UTIL.get_utl_file_dir;
43 end if;
44
45 ----------------------------------------------------------------
46 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
47 -- AND BIS_DEBUG_LOG_DIRECTORY AND set up the directory WHERE
48 -- the log files AND output files are written to
49 ----------------------------------------------------------------
50 FII_UTIL.initialize('FII_SNAP_TBL_REFRESH.log',
51 'FII_SNAP_TBL_REFRESH.out',l_dir,
52 'FII_SNAP_TBL_REFRESH');
53
54 g_phase := 'Obtain FII schema name AND other info';
55
56 -- Obtain FII schema name
57 g_schema_name := FII_UTIL.get_schema_name ('FII');
58
59 -- Obtain user ID, login ID AND sysdate
60 g_fii_user_id := FND_GLOBAL.USER_ID;
61 g_fii_login_id := FND_GLOBAL.LOGIN_ID;
62
63 SELECT sysdate INTO g_fii_sysdate FROM dual;
64
65 g_phase := 'Check FII schema name AND other info';
66 -- If any of the above values is not set, error out
67 IF (g_fii_user_id is NULL OR g_fii_login_id is NULL) THEN
68 FII_UTIL.Write_Log ('>>> Failed Intialization (login info not available)');
69 RAISE G_LOGIN_INFO_NOT_AVABLE;
70 END IF;
71
72 -- Determine if process will be run in debug mode
73 IF g_debug_flag = 'Y' THEN
74 FII_UTIL.Write_Log ('Debug On');
75 ELSE
76 FII_UTIL.Write_Log ('Debug Off');
77 END IF;
78
79 IF g_debug_flag = 'Y' THEN
80 FII_UTIL.Write_Log ('Initialize: Now start processing... ');
81 End If;
82
83 Exception
84
85 When others THEN
86 FII_UTIL.Write_Log ('Unexpected error WHEN calling Initialize...');
87 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
88 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
89 RAISE;
90
91 END Initialize;
92
93
94 ----------------------------------------------------
95 -- PROCEDURE REFRESH_GL_SNAP_F (private)
96 --
97 -- This procedure will (fully) refresh table FII_GL_SNAP_F
98 -- FROM FII_GL_BASE_MAP_MV
99 ----------------------------------------------------
100 PROCEDURE REFRESH_GL_SNAP_F IS
101
102 l_this_date DATE;
103 l_pp_this_date DATE;
104 l_pq_this_date DATE;
105 l_ly_this_date DATE;
106 l_industry_pf VARCHAR2(1);
107 l_this_date_gov DATE;
108 l_min_start_date DATE;
109
110 BEGIN
111
112 g_phase := 'Entering REFRESH_GL_SNAP_F';
113 IF g_debug_flag = 'Y' THEN
114 FII_UTIL.Write_Log ('> Entering REFRESH_GL_SNAP_F');
115 FII_UTIL.start_timer();
116 END IF;
117
118 -- Find out if this is commercial or government install
119 g_phase := 'Find out if this is commercial or government install';
120 l_industry_pf := FND_PROFILE.value('INDUSTRY');
121
122 g_phase := 'Populate l_this_date FROM BIS_SYSTEM_DATE';
123
124 --We use a un-shipped profile option FII_TEST_SYSDATE ("FII: Test Sysdate")
125 --to reset different sysdate so that we can test for snapshot tables.
126 if g_test_sysdate is NULL THEN
127 SELECT trunc(CURRENT_DATE_ID) INTO l_this_date
128 FROM BIS_SYSTEM_DATE;
129 ELSE
130 l_this_date := g_test_sysdate;
131 end if;
132
133 SELECT MIN(start_date) INTO l_min_start_date
134 FROM fii_time_ent_period;
135 --------------------------------------------------
136
137 g_phase := 'Populate l_pp_this_date, l_pq_this_date, l_ly_this_date, l_this_date_gov';
138
139 /* Commented out for bug 4899518 and replaced with select
140 /*
141 l_pp_this_date := FII_TIME_API.ent_sd_pper_end(l_this_date);
142 l_pq_this_date := FII_TIME_API.ent_sd_pqtr_end(l_this_date);
143 l_ly_this_date := FII_TIME_API.ent_sd_lyr_end(l_this_date);
144 l_this_date_gov := FII_TIME_API.ent_cper_end(l_this_date);
145 */
146
147 SELECT NVL(fii_time_api.ent_sd_pper_end(l_this_date),l_min_start_date),
148 NVL(fii_time_api.ent_sd_pqtr_end(l_this_date),l_min_start_date),
149 NVL(fii_time_api.ent_sd_lyr_end(l_this_date),l_min_start_date),
150 NVL( fii_time_api.ent_cper_end(l_this_date),l_min_start_date)
151
152 INTO l_pp_this_date,
153 l_pq_this_date,
154 l_ly_this_date,
155 l_this_date_gov
156
157 FROM DUAL;
158
159 IF g_debug_flag = 'Y' THEN
160 FII_UTIL.Write_Log ('>> l_this_date = ' || l_this_date);
161 FII_UTIL.Write_Log ('>> l_pp_this_date = ' || l_pp_this_date);
162 FII_UTIL.Write_Log ('>> l_pq_this_date = ' || l_pq_this_date);
163 FII_UTIL.Write_Log ('>> l_ly_this_date = ' || l_ly_this_date);
164 FII_UTIL.Write_Log ('>> l_this_date_gov = ' || l_this_date_gov);
165 FII_UTIL.Write_Log ('>> l_industry_pf = ' || l_industry_pf);
166 FII_UTIL.Write_Log (' ');
167 END IF;
168
169 --Always do a full refresh for snapshot tables
170 g_phase := 'Truncate table FII_GL_SNAP_F';
171 FII_UTIL.truncate_table ('FII_GL_SNAP_F', 'FII', g_retcode);
172
173 g_phase := 'Starting to populate table FII_GL_SNAP_F';
174 IF g_debug_flag = 'Y' THEN
175 FII_UTIL.Write_Log ('>> Starting to populate table FII_GL_SNAP_F');
176 END IF;
177
178
179 --------------------------------------------------------------------------
180 --Insert data FROM fii_gl_base_map_mv by joining to fii_time_structures.
181 --Here we calculate XTD amounts for all four days at same time; the sql
182 --query is similar to what we have for PMV reports (using bitand function)
183 --------------------------------------------------------------------------
184
185 insert /*+ append */ INTO FII_GL_SNAP_F
186 ( COST_CENTER_DIM_ID,
187 COMPANY_DIM_ID,
188 FIN_CATEGORY_ID,
189 USER_DIM1_ID,
190 USER_DIM2_ID,
191 LEDGER_ID,
192
193 ACTUAL_B_CUR_MTD,
194 ACTUAL_B_CUR_QTD,
195 ACTUAL_B_CUR_YTD,
196 ACTUAL_B_PRIOR_MTD,
197 ACTUAL_B_PRIOR_QTD,
198 ACTUAL_B_PRIOR_YTD,
199 ACTUAL_B_LAST_YEAR_MTD,
200 ACTUAL_B_LAST_YEAR_QTD,
201
202 ACTUAL_PG_CUR_MTD,
203 ACTUAL_PG_CUR_QTD,
204 ACTUAL_PG_CUR_YTD,
205 ACTUAL_PG_PRIOR_MTD,
206 ACTUAL_PG_PRIOR_QTD,
207 ACTUAL_PG_PRIOR_YTD,
208 ACTUAL_PG_LAST_YEAR_MTD,
209 ACTUAL_PG_LAST_YEAR_QTD,
210
211 ACTUAL_SG_CUR_MTD,
212 ACTUAL_SG_CUR_QTD,
213 ACTUAL_SG_CUR_YTD,
214 ACTUAL_SG_PRIOR_MTD,
215 ACTUAL_SG_PRIOR_QTD,
216 ACTUAL_SG_PRIOR_YTD,
217 ACTUAL_SG_LAST_YEAR_MTD,
218 ACTUAL_SG_LAST_YEAR_QTD,
219
220
221 BUDGET_PG_CUR_MTD,
222 BUDGET_PG_CUR_QTD,
223 BUDGET_PG_CUR_YTD,
224 BUDGET_PG_PRIOR_MTD,
225 BUDGET_PG_PRIOR_QTD,
226 BUDGET_PG_PRIOR_YTD,
227 BUDGET_PG_LAST_YEAR_MTD,
228 BUDGET_PG_LAST_YEAR_QTD,
229
230 BUDGET_SG_CUR_MTD,
231 BUDGET_SG_CUR_QTD,
232 BUDGET_SG_CUR_YTD,
233 BUDGET_SG_PRIOR_MTD,
234 BUDGET_SG_PRIOR_QTD,
235 BUDGET_SG_PRIOR_YTD,
236 BUDGET_SG_LAST_YEAR_MTD,
237 BUDGET_SG_LAST_YEAR_QTD,
238
239
240 FORECAST_PG_CUR_MTD,
241 FORECAST_PG_CUR_QTD,
242 FORECAST_PG_CUR_YTD,
243 FORECAST_PG_PRIOR_MTD,
244 FORECAST_PG_PRIOR_QTD,
245 FORECAST_PG_PRIOR_YTD,
246 FORECAST_PG_LAST_YEAR_MTD,
247 FORECAST_PG_LAST_YEAR_QTD,
248
249 FORECAST_SG_CUR_MTD,
250 FORECAST_SG_CUR_QTD,
251 FORECAST_SG_CUR_YTD,
252 FORECAST_SG_PRIOR_MTD,
253 FORECAST_SG_PRIOR_QTD,
254 FORECAST_SG_PRIOR_YTD,
255 FORECAST_SG_LAST_YEAR_MTD,
256 FORECAST_SG_LAST_YEAR_QTD,
257
258 COMMITTED_AMT_PG_CUR_MTD,
259 COMMITTED_AMT_PG_CUR_QTD,
260 COMMITTED_AMT_PG_CUR_YTD,
261 COMMITTED_AMT_PG_PRIOR_MTD,
262 COMMITTED_AMT_PG_PRIOR_QTD,
263 COMMITTED_AMT_PG_PRIOR_YTD,
264 COMMITTED_AMT_PG_LAST_YEAR_MTD,
265 COMMITTED_AMT_PG_LAST_YEAR_QTD,
266
267 OBLIGATED_AMT_PG_CUR_MTD,
268 OBLIGATED_AMT_PG_CUR_QTD,
269 OBLIGATED_AMT_PG_CUR_YTD,
270 OBLIGATED_AMT_PG_PRIOR_MTD,
271 OBLIGATED_AMT_PG_PRIOR_QTD,
272 OBLIGATED_AMT_PG_PRIOR_YTD,
273 OBLIGATED_AMT_PG_LAST_YEAR_MTD,
274 OBLIGATED_AMT_PG_LAST_YEAR_QTD,
275
276 OTHER_AMT_PG_CUR_MTD,
277 OTHER_AMT_PG_CUR_QTD,
278 OTHER_AMT_PG_CUR_YTD,
279 OTHER_AMT_PG_PRIOR_MTD,
280 OTHER_AMT_PG_PRIOR_QTD,
281 OTHER_AMT_PG_PRIOR_YTD,
282 OTHER_AMT_PG_LAST_YEAR_MTD,
283 OTHER_AMT_PG_LAST_YEAR_QTD,
284
285 BASELINE_AMT_PG_CUR_MTD ,
286 BASELINE_AMT_PG_CUR_QTD,
287 BASELINE_AMT_PG_CUR_YTD,
288 BASELINE_AMT_PG_PRIOR_MTD,
289 BASELINE_AMT_PG_PRIOR_QTD,
290 BASELINE_AMT_PG_PRIOR_YTD,
291 BASELINE_AMT_PG_LAST_YEAR_MTD,
292 BASELINE_AMT_PG_LAST_YEAR_QTD,
293
294 POSTED_DATE,
295
296 LAST_UPDATE_DATE,
297 LAST_UPDATED_BY,
298 CREATION_DATE,
299 CREATED_BY,
300 LAST_UPDATE_LOGIN)
301 SELECT
302 cost_center_dim_id,
303 company_dim_id,
304 fin_category_id,
305 user_dim1_id,
306 user_dim2_id,
307 ledger_id,
308
309 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
310 AND cal.report_date = l_this_date
311 THEN b.actual_b
312 ELSE NULL end) actual_b_cur_mtd,
313 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
314 AND cal.report_date = l_this_date
315 THEN b.actual_b
316 ELSE NULL end) actual_b_cur_qtd,
317 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
318 AND cal.report_date = l_this_date
319 THEN b.actual_b
320 ELSE NULL end) actual_b_cur_ytd,
321 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
322 AND cal.report_date = l_pp_this_date
323 THEN b.actual_b
324 ELSE NULL end) actual_b_prior_mtd,
325 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
326 AND cal.report_date = l_pq_this_date
327 THEN b.actual_b
328 ELSE NULL end) actual_b_prior_qtd,
329 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
330 AND cal.report_date = l_ly_this_date
331 THEN b.actual_b
332 ELSE NULL end) actual_b_prior_ytd,
336 ELSE NULL end) actual_b_last_year_mtd,
333 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
334 AND cal.report_date = l_ly_this_date
335 THEN b.actual_b
337 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
338 AND cal.report_date = l_ly_this_date
339 THEN b.actual_b
340 ELSE NULL end) actual_b_last_year_qtd,
341
342 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
343 AND cal.report_date = l_this_date
344 THEN b.prim_actual_g
345 ELSE NULL end) actual_pg_cur_mtd,
346 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
347 AND cal.report_date = l_this_date
348 THEN b.prim_actual_g
349 ELSE NULL end) actual_pg_cur_qtd,
350 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
351 AND cal.report_date = l_this_date
352 THEN b.prim_actual_g
353 ELSE NULL end) actual_pg_cur_ytd,
354 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
355 AND cal.report_date = l_pp_this_date
356 THEN b.prim_actual_g
357 ELSE NULL end) actual_pg_prior_mtd,
358 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
359 AND cal.report_date = l_pq_this_date
360 THEN b.prim_actual_g
361 ELSE NULL end) actual_pg_prior_qtd,
362 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
363 AND cal.report_date = l_ly_this_date
364 THEN b.prim_actual_g
365 ELSE NULL end) actual_pg_prior_ytd,
366 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
367 AND cal.report_date = l_ly_this_date
368 THEN b.prim_actual_g
369 ELSE NULL end) actual_pg_last_year_mtd,
370 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
371 AND cal.report_date = l_ly_this_date
372 THEN b.prim_actual_g
373 ELSE NULL end) actual_pg_last_year_qtd,
374
375 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
376 AND cal.report_date = l_this_date
377 THEN b.sec_actual_g
378 ELSE NULL end) actual_sg_cur_mtd,
379 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
380 AND cal.report_date = l_this_date
381 THEN b.sec_actual_g
382 ELSE NULL end) actual_sg_cur_qtd,
383 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
384 AND cal.report_date = l_this_date
385 THEN b.sec_actual_g
386 ELSE NULL end) actual_sg_cur_ytd,
387 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
388 AND cal.report_date = l_pp_this_date
389 THEN b.sec_actual_g
390 ELSE NULL end) actual_sg_prior_mtd,
391 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
392 AND cal.report_date = l_pq_this_date
393 THEN b.sec_actual_g
394 ELSE NULL end) actual_sg_prior_qtd,
395 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
396 AND cal.report_date = l_ly_this_date
397 THEN b.sec_actual_g
398 ELSE NULL end) actual_sg_prior_ytd,
399 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
400 AND cal.report_date = l_ly_this_date
401 THEN b.sec_actual_g
402 ELSE NULL end) actual_sg_last_year_mtd,
403 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
404 AND cal.report_date = l_ly_this_date
405 THEN b.sec_actual_g
406 ELSE NULL end) actual_sg_last_year_qtd,
407
408
409 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 64, 4))
410 = decode(l_industry_pf, 'G', 64, 4)
411 AND cal.report_date = decode(l_industry_pf, 'G', l_this_date_gov, l_this_date)
412 THEN b.prim_budget_g
413 ELSE NULL end) budget_pg_cur_mtd,
414 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 128, 8))
415 = decode(l_industry_pf, 'G', 128, 8)
416 AND cal.report_date = decode(l_industry_pf, 'G', l_this_date_gov, l_this_date)
417 THEN b.prim_budget_g
418 ELSE NULL end) budget_pg_cur_qtd,
419 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 256, 16))
420 = decode(l_industry_pf, 'G', 256, 16)
421 AND cal.report_date = decode(l_industry_pf, 'G', l_this_date_gov, l_this_date)
422 THEN b.prim_budget_g
423 ELSE NULL end) budget_pg_cur_ytd,
424 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 64, 4))
425 = decode(l_industry_pf, 'G', 64, 4)
426 AND cal.report_date = l_pp_this_date
427 THEN b.prim_budget_g
428 ELSE NULL end) budget_pg_prior_mtd,
429 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 128, 8))
430 = decode(l_industry_pf, 'G', 128, 8)
431 AND cal.report_date = l_pq_this_date
432 THEN b.prim_budget_g
433 ELSE NULL end) budget_pg_prior_qtd,
434 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 256, 16))
435 = decode(l_industry_pf, 'G', 256, 16)
436 AND cal.report_date = l_ly_this_date
437 THEN b.prim_budget_g
438 ELSE NULL end) budget_pg_prior_ytd,
439 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 64, 4))
443 ELSE NULL end) budget_pg_last_year_mtd,
440 = decode(l_industry_pf, 'G', 64, 4)
441 AND cal.report_date = l_ly_this_date
442 THEN b.prim_budget_g
444 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 128, 8))
445 = decode(l_industry_pf, 'G', 128, 8)
446 AND cal.report_date = l_ly_this_date
447 THEN b.prim_budget_g
448 ELSE NULL end) budget_pg_last_year_qtd,
449
450 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 64, 4))
451 = decode(l_industry_pf, 'G', 64, 4)
452 AND cal.report_date = decode(l_industry_pf, 'G', l_this_date_gov, l_this_date)
453 THEN b.sec_budget_g
454 ELSE NULL end) budget_sg_cur_mtd,
455 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 128, 8))
456 = decode(l_industry_pf, 'G', 128, 8)
457 AND cal.report_date = decode(l_industry_pf, 'G', l_this_date_gov, l_this_date)
458 THEN b.sec_budget_g
459 ELSE NULL end) budget_sg_cur_qtd,
460 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 256, 16))
461 = decode(l_industry_pf, 'G', 256, 16)
462 AND cal.report_date = decode(l_industry_pf, 'G', l_this_date_gov, l_this_date)
463 THEN b.sec_budget_g
464 ELSE NULL end) budget_sg_cur_ytd,
465 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 64, 4))
466 = decode(l_industry_pf, 'G', 64, 4)
467 AND cal.report_date = l_pp_this_date
468 THEN b.sec_budget_g
469 ELSE NULL end) budget_sg_prior_mtd,
470 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 128, 8))
471 = decode(l_industry_pf, 'G', 128, 8)
472 AND cal.report_date = l_pq_this_date
473 THEN b.sec_budget_g
474 ELSE NULL end) budget_sg_prior_qtd,
475 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 256, 16))
476 = decode(l_industry_pf, 'G', 256, 16)
477 AND cal.report_date = l_ly_this_date
478 THEN b.sec_budget_g
479 ELSE NULL end) budget_sg_prior_ytd,
480 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 64, 4))
481 = decode(l_industry_pf, 'G', 64, 4)
482 AND cal.report_date = l_ly_this_date
483 THEN b.sec_budget_g
484 ELSE NULL end) budget_sg_last_year_mtd,
485 SUM(CASE WHEN bitand(cal.record_type_id, decode(l_industry_pf, 'G', 128, 8))
486 = decode(l_industry_pf, 'G', 128, 8)
487 AND cal.report_date = l_ly_this_date
488 THEN b.sec_budget_g
489 ELSE NULL end) budget_sg_last_year_qtd,
490
491
492 SUM(CASE WHEN bitand(cal.record_type_id, 4) = 4
493 AND cal.report_date = l_this_date
494 THEN b.prim_forecast_g
495 ELSE NULL end) forecast_pg_cur_mtd,
496 SUM(CASE WHEN bitand(cal.record_type_id, 8) = 8
497 AND cal.report_date = l_this_date
498 THEN b.prim_forecast_g
499 ELSE NULL end) forecast_pg_cur_qtd,
500 SUM(CASE WHEN bitand(cal.record_type_id, 16) = 16
501 AND cal.report_date = l_this_date
502 THEN b.prim_forecast_g
503 ELSE NULL end) forecast_pg_cur_ytd,
504 SUM(CASE WHEN bitand(cal.record_type_id, 4) = 4
505 AND cal.report_date = l_pp_this_date
506 THEN b.prim_forecast_g
507 ELSE NULL end) forecast_pg_prior_mtd,
508 SUM(CASE WHEN bitand(cal.record_type_id, 8) = 8
509 AND cal.report_date = l_pq_this_date
510 THEN b.prim_forecast_g
511 ELSE NULL end) forecast_pg_prior_qtd,
512 SUM(CASE WHEN bitand(cal.record_type_id, 16) = 16
513 AND cal.report_date = l_ly_this_date
514 THEN b.prim_forecast_g
515 ELSE NULL end) forecast_pg_prior_ytd,
516 SUM(CASE WHEN bitand(cal.record_type_id, 4) = 4
517 AND cal.report_date = l_ly_this_date
518 THEN b.prim_forecast_g
519 ELSE NULL end) forecast_pg_last_year_mtd,
520 SUM(CASE WHEN bitand(cal.record_type_id, 8) = 8
521 AND cal.report_date = l_ly_this_date
522 THEN b.prim_forecast_g
523 ELSE NULL end) forecast_pg_last_year_qtd,
524
525 SUM(CASE WHEN bitand(cal.record_type_id, 4) = 4
526 AND cal.report_date = l_this_date
527 THEN b.sec_forecast_g
528 ELSE NULL end) forecast_sg_cur_mtd,
529 SUM(CASE WHEN bitand(cal.record_type_id, 8) = 8
530 AND cal.report_date = l_this_date
531 THEN b.sec_forecast_g
532 ELSE NULL end) forecast_sg_cur_qtd,
533 SUM(CASE WHEN bitand(cal.record_type_id, 16) = 16
534 AND cal.report_date = l_this_date
535 THEN b.sec_forecast_g
536 ELSE NULL end) forecast_sg_cur_ytd,
537 SUM(CASE WHEN bitand(cal.record_type_id, 4) = 4
538 AND cal.report_date = l_pp_this_date
539 THEN b.sec_forecast_g
540 ELSE NULL end) forecast_sg_prior_mtd,
541 SUM(CASE WHEN bitand(cal.record_type_id, 8) = 8
542 AND cal.report_date = l_pq_this_date
543 THEN b.sec_forecast_g
544 ELSE NULL end) forecast_sg_prior_qtd,
545 SUM(CASE WHEN bitand(cal.record_type_id, 16) = 16
549 SUM(CASE WHEN bitand(cal.record_type_id, 4) = 4
546 AND cal.report_date = l_ly_this_date
547 THEN b.sec_forecast_g
548 ELSE NULL end) forecast_sg_prior_ytd,
550 AND cal.report_date = l_ly_this_date
551 THEN b.sec_forecast_g
552 ELSE NULL end) forecast_sg_last_year_mtd,
553 SUM(CASE WHEN bitand(cal.record_type_id, 8) = 8
554 AND cal.report_date = l_ly_this_date
555 THEN b.sec_forecast_g
556 ELSE NULL end) forecast_sg_last_year_qtd,
557
558
559
560 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
561 AND cal.report_date = l_this_date_gov -- l_this_date
562 THEN b.committed_amount_prim
563 ELSE NULL end) committed_amt_pg_cur_mtd,
564 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
565 AND cal.report_date = l_this_date_gov -- l_this_date
566 THEN b.committed_amount_prim
567 ELSE NULL end) committed_amt_pg_cur_qtd,
568 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
569 AND cal.report_date = l_this_date_gov -- l_this_date
570 THEN b.committed_amount_prim
571 ELSE NULL end) committed_amt_pg_cur_ytd,
572 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
573 AND cal.report_date = l_pp_this_date
574 THEN b.committed_amount_prim
575 ELSE NULL end) committed_amt_pg_prior_mtd,
576 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
577 AND cal.report_date = l_pq_this_date
578 THEN b.committed_amount_prim
579 ELSE NULL end) committed_amt_pg_prior_qtd,
580 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
581 AND cal.report_date = l_ly_this_date
582 THEN b.committed_amount_prim
583 ELSE NULL end) committed_amt_pg_prior_ytd,
584 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
585 AND cal.report_date = l_ly_this_date
586 THEN b.committed_amount_prim
587 ELSE NULL end) committed_amt_pg_last_year_mtd,
588 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
589 AND cal.report_date = l_ly_this_date
590 THEN b.committed_amount_prim
591 ELSE NULL end) committed_amt_pg_last_year_qtd,
592
593 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
594 AND cal.report_date = l_this_date_gov -- l_this_date
595 THEN b.obligated_amount_prim
596 ELSE NULL end) obligated_amt_pg_cur_mtd,
597 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
598 AND cal.report_date = l_this_date_gov -- l_this_date
599 THEN b.obligated_amount_prim
600 ELSE NULL end) obligated_amt_pg_cur_qtd,
601 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
602 AND cal.report_date = l_this_date_gov -- l_this_date
603 THEN b.obligated_amount_prim
604 ELSE NULL end) obligated_amt_pg_cur_ytd,
605 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
606 AND cal.report_date = l_pp_this_date
607 THEN b.obligated_amount_prim
608 ELSE NULL end) obligated_amt_pg_prior_mtd,
609 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
610 AND cal.report_date = l_pq_this_date
611 THEN b.obligated_amount_prim
612 ELSE NULL end) obligated_amt_pg_prior_qtd,
613 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
614 AND cal.report_date = l_ly_this_date
615 THEN b.obligated_amount_prim
616 ELSE NULL end) obligated_amt_pg_prior_ytd,
617 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
618 AND cal.report_date = l_ly_this_date
619 THEN b.obligated_amount_prim
620 ELSE NULL end) obligated_amt_pg_last_year_mtd,
621 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
622 AND cal.report_date = l_ly_this_date
623 THEN b.obligated_amount_prim
624 ELSE NULL end) obligated_amt_pg_last_year_qtd,
625
626 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
627 AND cal.report_date = l_this_date_gov -- l_this_date
628 THEN b.other_amount_prim
629 ELSE NULL end) other_amt_pg_cur_mtd,
630 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
631 AND cal.report_date = l_this_date_gov -- l_this_date
632 THEN b.other_amount_prim
633 ELSE NULL end) other_amt_pg_cur_qtd,
634 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
635 AND cal.report_date = l_this_date_gov -- l_this_date
636 THEN b.other_amount_prim
637 ELSE NULL end) other_amt_pg_cur_ytd,
638 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
639 AND cal.report_date = l_pp_this_date
640 THEN b.other_amount_prim
641 ELSE NULL end) other_amt_pg_prior_mtd,
642 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
643 AND cal.report_date = l_pq_this_date
644 THEN b.other_amount_prim
645 ELSE NULL end) other_amt_pg_prior_qtd,
646 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
647 AND cal.report_date = l_ly_this_date
648 THEN b.other_amount_prim
649 ELSE NULL end) other_amt_pg_prior_ytd,
650 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
651 AND cal.report_date = l_ly_this_date
652 THEN b.other_amount_prim
656 THEN b.other_amount_prim
653 ELSE NULL end) other_amt_pg_last_year_mtd,
654 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
655 AND cal.report_date = l_ly_this_date
657 ELSE NULL end) other_amt_pg_last_year_qtd,
658
659 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
660 AND cal.report_date = l_this_date
661 THEN b.baseline_amount_prim
662 ELSE NULL end) baseline_amt_pg_cur_mtd,
663 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
664 AND cal.report_date = l_this_date
665 THEN b.baseline_amount_prim
666 ELSE NULL end) baseline_amt_pg_cur_qtd,
667 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
668 AND cal.report_date = l_this_date
669 THEN b.baseline_amount_prim
670 ELSE NULL end) baseline_amt_pg_cur_ytd,
671 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
672 AND cal.report_date = l_pp_this_date
673 THEN b.baseline_amount_prim
674 ELSE NULL end) baseline_amt_pg_prior_mtd,
675 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
676 AND cal.report_date = l_pq_this_date
677 THEN b.baseline_amount_prim
678 ELSE NULL end) baseline_amt_pg_prior_qtd,
679 SUM(CASE WHEN bitand(cal.record_type_id, 256) = 256
680 AND cal.report_date = l_ly_this_date
681 THEN b.baseline_amount_prim
682 ELSE NULL end) baseline_amt_pg_prior_ytd,
683 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
684 AND cal.report_date = l_ly_this_date
685 THEN b.baseline_amount_prim
686 ELSE NULL end) baseline_amt_pg_last_year_mtd,
687 SUM(CASE WHEN bitand(cal.record_type_id, 128) = 128
688 AND cal.report_date = l_ly_this_date
689 THEN b.baseline_amount_prim
690 ELSE NULL end) baseline_amt_pg_last_year_qtd,
691
692 b.POSTED_DATE,
693
694 g_fii_sysdate,
695 g_fii_user_id,
696 g_fii_sysdate,
697 g_fii_user_id,
698 g_fii_login_id
699
700 FROM fii_time_structures cal,
701 fii_gl_base_map_mv b
702 WHERE cal.report_date in (l_this_date, l_pp_this_date,
703 l_pq_this_date, l_ly_this_date, l_this_date_gov)
704 AND cal.time_id = b.time_id
705 AND cal.period_type_id = b.period_type_id
706 AND (bitand(cal.record_type_id, 64) = 64 OR
707 bitand(cal.record_type_id, 128) = 128 OR
708 bitand(cal.record_type_id, 256) = 256 OR
709 bitand(cal.record_type_id, 4) = 4 OR
710 bitand(cal.record_type_id, 8) = 8 OR
711 bitand(cal.record_type_id, 16) = 16)
712 GROUP BY b.company_dim_id, b.cost_center_dim_id, b.fin_category_id,
713 b.user_dim1_id, b.user_dim2_id, ledger_id, b.posted_date;
714
715 IF g_debug_flag = 'Y' THEN
716 FII_UTIL.stop_timer();
717 FII_UTIL.Write_Log ('FII_GL_SNAP_F has been populated successfully');
718 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
719 FII_UTIL.print_timer();
720 END IF;
721
722 g_phase := 'Gather table stats for FII_GL_SNAP_F';
723 fnd_stats.gather_table_stats (ownname=>g_schema_name,
724 tabname=>'FII_GL_SNAP_F');
725
726 g_phase := 'Commit the change';
727 commit;
728
729 IF g_debug_flag = 'Y' THEN
730 FII_UTIL.Write_Log ('< Leaving REFRESH_GL_SNAP_F');
731 FII_UTIL.Write_Log (' ');
732 END IF;
733
734 EXCEPTION
735 -- Bug 4174859. Handled no data found exception.
736 WHEN no_data_found THEN
737 FII_MESSAGE.write_log(
738 msg_name => 'FII_PERIOD_NOT_OPEN',
739 token_num => 0);
740 raise;
741
742 WHEN OTHERS THEN
743 FII_UTIL.Write_Log ('Other error in REFRESH_GL_SNAP_F ');
744 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
745 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
746 rollback;
747 raise;
748
749 END REFRESH_GL_SNAP_F;
750
751
752 ----------------------------------------------------
753 -- PROCEDURE REFRESH_GL_SNAP_SUM_F (private)
754 --
755 -- This procedure will (fully) refresh table FII_GL_SNAP_SUM_F
756 -- FROM FII_GL_SNAP_F.
757 -- It aggregates along Financial Category, Company, Cost Center.
758 -- AND User Defined 1 dimensions to the upper portions.
759 ----------------------------------------------------
760 PROCEDURE REFRESH_GL_SNAP_SUM_F IS
761
762 BEGIN
763
764 g_phase := 'Entering REFRESH_GL_SNAP_SUM_F';
765 IF g_debug_flag = 'Y' THEN
766 FII_UTIL.Write_Log ('> Entering REFRESH_GL_SNAP_SUM_F');
767 FII_UTIL.start_timer();
768 END IF;
769
770 g_phase := 'Truncate table FII_GL_SNAP_SUM_F';
771 FII_UTIL.truncate_table ('FII_GL_SNAP_SUM_F', 'FII', g_retcode);
772
773 g_phase := 'Starting to populate table FII_GL_SNAP_SUM_F';
774 IF g_debug_flag = 'Y' THEN
775 FII_UTIL.Write_Log ('>> Starting to populate table FII_GL_SNAP_SUM_F');
776 END IF;
777
778 insert /*+ append */ INTO FII_GL_SNAP_SUM_F
779 (
780 COST_CENTER_DIM_ID,
781 PARENT_COST_CENTER_DIM_ID,
782 COMPANY_DIM_ID,
783 PARENT_COMPANY_DIM_ID,
784 FIN_CATEGORY_ID,
785 PARENT_FIN_CATEGORY_ID,
786 USER_DIM1_ID,
787 PARENT_USER_DIM1_ID,
788 USER_DIM2_ID,
789 LEDGER_ID,
790
791 ACTUAL_B_CUR_MTD,
792 ACTUAL_B_CUR_QTD,
793 ACTUAL_B_CUR_YTD,
794 ACTUAL_B_PRIOR_MTD,
798 ACTUAL_B_LAST_YEAR_QTD,
795 ACTUAL_B_PRIOR_QTD,
796 ACTUAL_B_PRIOR_YTD,
797 ACTUAL_B_LAST_YEAR_MTD,
799
800 ACTUAL_PG_CUR_MTD,
801 ACTUAL_PG_CUR_QTD,
802 ACTUAL_PG_CUR_YTD,
803 ACTUAL_PG_PRIOR_MTD,
804 ACTUAL_PG_PRIOR_QTD,
805 ACTUAL_PG_PRIOR_YTD,
806 ACTUAL_PG_LAST_YEAR_MTD,
807 ACTUAL_PG_LAST_YEAR_QTD,
808
809 ACTUAL_SG_CUR_MTD,
810 ACTUAL_SG_CUR_QTD,
811 ACTUAL_SG_CUR_YTD,
812 ACTUAL_SG_PRIOR_MTD,
813 ACTUAL_SG_PRIOR_QTD,
814 ACTUAL_SG_PRIOR_YTD,
815 ACTUAL_SG_LAST_YEAR_MTD,
816 ACTUAL_SG_LAST_YEAR_QTD,
817
818
819 BUDGET_PG_CUR_MTD,
820 BUDGET_PG_CUR_QTD,
821 BUDGET_PG_CUR_YTD,
822 BUDGET_PG_PRIOR_MTD,
823 BUDGET_PG_PRIOR_QTD,
824 BUDGET_PG_PRIOR_YTD,
825 BUDGET_PG_LAST_YEAR_MTD,
826 BUDGET_PG_LAST_YEAR_QTD,
827
828 BUDGET_SG_CUR_MTD,
829 BUDGET_SG_CUR_QTD,
830 BUDGET_SG_CUR_YTD,
831 BUDGET_SG_PRIOR_MTD,
832 BUDGET_SG_PRIOR_QTD,
833 BUDGET_SG_PRIOR_YTD,
834 BUDGET_SG_LAST_YEAR_MTD,
835 BUDGET_SG_LAST_YEAR_QTD,
836
837
838 FORECAST_PG_CUR_MTD,
839 FORECAST_PG_CUR_QTD,
840 FORECAST_PG_CUR_YTD,
841 FORECAST_PG_PRIOR_MTD,
842 FORECAST_PG_PRIOR_QTD,
843 FORECAST_PG_PRIOR_YTD,
844 FORECAST_PG_LAST_YEAR_MTD,
845 FORECAST_PG_LAST_YEAR_QTD,
846
847 FORECAST_SG_CUR_MTD,
848 FORECAST_SG_CUR_QTD,
849 FORECAST_SG_CUR_YTD,
850 FORECAST_SG_PRIOR_MTD,
851 FORECAST_SG_PRIOR_QTD,
852 FORECAST_SG_PRIOR_YTD,
853 FORECAST_SG_LAST_YEAR_MTD,
854 FORECAST_SG_LAST_YEAR_QTD,
855
856
857 COMMITTED_AMT_PG_CUR_MTD ,
858 COMMITTED_AMT_PG_CUR_QTD ,
859 COMMITTED_AMT_PG_CUR_YTD ,
860 COMMITTED_AMT_PG_PRIOR_MTD ,
861 COMMITTED_AMT_PG_PRIOR_QTD ,
862 COMMITTED_AMT_PG_PRIOR_YTD ,
863 COMMITTED_AMT_PG_LAST_YEAR_MTD ,
864 COMMITTED_AMT_PG_LAST_YEAR_QTD ,
865 OBLIGATED_AMT_PG_CUR_MTD ,
866 OBLIGATED_AMT_PG_CUR_QTD ,
867 OBLIGATED_AMT_PG_CUR_YTD ,
868 OBLIGATED_AMT_PG_PRIOR_MTD ,
869 OBLIGATED_AMT_PG_PRIOR_QTD ,
870 OBLIGATED_AMT_PG_PRIOR_YTD ,
871 OBLIGATED_AMT_PG_LAST_YEAR_MTD ,
872 OBLIGATED_AMT_PG_LAST_YEAR_QTD ,
873 OTHER_AMT_PG_CUR_MTD ,
874 OTHER_AMT_PG_CUR_QTD ,
875 OTHER_AMT_PG_CUR_YTD ,
876 OTHER_AMT_PG_PRIOR_MTD ,
877 OTHER_AMT_PG_PRIOR_QTD ,
878 OTHER_AMT_PG_PRIOR_YTD ,
879 OTHER_AMT_PG_LAST_YEAR_MTD ,
880 OTHER_AMT_PG_LAST_YEAR_QTD ,
881 BASELINE_AMT_PG_CUR_MTD ,
882 BASELINE_AMT_PG_CUR_QTD ,
883 BASELINE_AMT_PG_CUR_YTD ,
884 BASELINE_AMT_PG_PRIOR_MTD ,
885 BASELINE_AMT_PG_PRIOR_QTD ,
886 BASELINE_AMT_PG_PRIOR_YTD ,
887 BASELINE_AMT_PG_LAST_YEAR_MTD ,
888 BASELINE_AMT_PG_LAST_YEAR_QTD ,
889
890 POSTED_DATE,
891
892 LAST_UPDATE_DATE,
893 LAST_UPDATED_BY,
894 CREATION_DATE,
895 CREATED_BY,
896 LAST_UPDATE_LOGIN)
897
898 SELECT
899 cc.NEXT_LEVEL_CC_ID COST_CENTER_DIM_ID,
900 cc.PARENT_CC_ID PARENT_COST_CENTER_DIM_ID,
901 com.NEXT_LEVEL_COMPANY_ID COMPANY_DIM_ID,
902 com.PARENT_COMPANY_ID PARENT_COMPANY_DIM_ID,
903 fin.NEXT_LEVEL_FIN_CAT_ID FIN_CATEGORY_ID,
904 fin.PARENT_FIN_CAT_ID PARENT_FIN_CATEGORY_ID,
905 ud1.NEXT_LEVEL_VALUE_ID USER_DIM1_ID,
906 ud1.PARENT_VALUE_ID PARENT_USER_DIM1_ID,
907
908 b.user_dim2_id,
909 b.ledger_id,
910
911 SUM(actual_b_cur_mtd),
912 SUM(actual_b_cur_qtd),
913 SUM(actual_b_cur_ytd),
914 SUM(actual_b_prior_mtd),
915 SUM(actual_b_prior_qtd),
916 SUM(actual_b_prior_ytd),
917 SUM(actual_b_last_year_mtd),
918 SUM(actual_b_last_year_qtd),
919
920 SUM(actual_pg_cur_mtd),
921 SUM(actual_pg_cur_qtd),
922 SUM(actual_pg_cur_ytd),
923 SUM(actual_pg_prior_mtd),
924 SUM(actual_pg_prior_qtd),
925 SUM(actual_pg_prior_ytd),
926 SUM(actual_pg_last_year_mtd),
927 SUM(actual_pg_last_year_qtd),
928
929 SUM(actual_sg_cur_mtd),
930 SUM(actual_sg_cur_qtd),
931 SUM(actual_sg_cur_ytd),
932 SUM(actual_sg_prior_mtd),
933 SUM(actual_sg_prior_qtd),
934 SUM(actual_sg_prior_ytd),
935 SUM(actual_sg_last_year_mtd),
936 SUM(actual_sg_last_year_qtd),
937
938
939 SUM(budget_pg_cur_mtd),
940 SUM(budget_pg_cur_qtd),
941 SUM(budget_pg_cur_ytd),
942 SUM(budget_pg_prior_mtd),
943 SUM(budget_pg_prior_qtd),
944 SUM(budget_pg_prior_ytd),
945 SUM(budget_pg_last_year_mtd),
946 SUM(budget_pg_last_year_qtd),
947
948 SUM(budget_sg_cur_mtd),
949 SUM(budget_sg_cur_qtd),
950 SUM(budget_sg_cur_ytd),
951 SUM(budget_sg_prior_mtd),
952 SUM(budget_sg_prior_qtd),
953 SUM(budget_sg_prior_ytd),
954 SUM(budget_sg_last_year_mtd),
955 SUM(budget_sg_last_year_qtd),
956
957
958 SUM(forecast_pg_cur_mtd),
959 SUM(forecast_pg_cur_qtd),
960 SUM(forecast_pg_cur_ytd),
964 SUM(forecast_pg_last_year_mtd),
961 SUM(forecast_pg_prior_mtd),
962 SUM(forecast_pg_prior_qtd),
963 SUM(forecast_pg_prior_ytd),
965 SUM(forecast_pg_last_year_qtd),
966
967 SUM(forecast_sg_cur_mtd),
968 SUM(forecast_sg_cur_qtd),
969 SUM(forecast_sg_cur_ytd),
970 SUM(forecast_sg_prior_mtd),
971 SUM(forecast_sg_prior_qtd),
972 SUM(forecast_sg_prior_ytd),
973 SUM(forecast_sg_last_year_mtd),
974 SUM(forecast_sg_last_year_qtd),
975
976
977 SUM(committed_amt_pg_cur_mtd) ,
978 SUM(committed_amt_pg_cur_qtd) ,
979 SUM(committed_amt_pg_cur_ytd) ,
980 SUM(committed_amt_pg_prior_mtd) ,
981 SUM(committed_amt_pg_prior_qtd) ,
982 SUM(committed_amt_pg_prior_ytd) ,
983 SUM(committed_amt_pg_last_year_mtd),
984 SUM(committed_amt_pg_last_year_qtd),
985 SUM(obligated_amt_pg_cur_mtd) ,
986 SUM(obligated_amt_pg_cur_qtd) ,
987 SUM(obligated_amt_pg_cur_ytd) ,
988 SUM(obligated_amt_pg_prior_mtd) ,
989 SUM(obligated_amt_pg_prior_qtd) ,
990 SUM(obligated_amt_pg_prior_ytd) ,
991 SUM(obligated_amt_pg_last_year_mtd),
992 SUM(obligated_amt_pg_last_year_qtd),
993 SUM(other_amt_pg_cur_mtd) ,
994 SUM(other_amt_pg_cur_qtd) ,
998 SUM(other_amt_pg_prior_ytd) ,
995 SUM(other_amt_pg_cur_ytd) ,
996 SUM(other_amt_pg_prior_mtd) ,
997 SUM(other_amt_pg_prior_qtd) ,
999 SUM(other_amt_pg_last_year_mtd) ,
1000 SUM(other_amt_pg_last_year_qtd) ,
1001 SUM(baseline_amt_pg_cur_mtd) ,
1002 SUM(baseline_amt_pg_cur_qtd) ,
1003 SUM(baseline_amt_pg_cur_ytd) ,
1004 SUM(baseline_amt_pg_prior_mtd) ,
1005 SUM(baseline_amt_pg_prior_qtd) ,
1006 SUM(baseline_amt_pg_prior_ytd) ,
1007 SUM(baseline_amt_pg_last_year_mtd) ,
1008 SUM(baseline_amt_pg_last_year_qtd) ,
1009
1010 b.POSTED_DATE,
1011
1012 g_fii_sysdate,
1013 g_fii_user_id,
1014 g_fii_sysdate,
1015 g_fii_user_id,
1016 g_fii_login_id
1017
1018 FROM fii_gl_snap_f b,
1019 fii_fin_item_leaf_hiers fin,
1020 fii_company_hierarchies com,
1021 fii_cost_ctr_hierarchies cc,
1022 fii_udd1_hierarchies ud1
1023 WHERE b.fin_category_id = fin.child_fin_cat_id
1024 AND fin.is_leaf_flag = 'N'
1025 AND fin.aggregate_next_level_flag = 'Y'
1026 AND b.company_dim_id = com.child_company_id
1027 AND com.is_leaf_flag = 'N'
1028 AND com.aggregate_next_level_flag = 'Y'
1029 AND b.cost_center_dim_id = cc.child_cc_id
1030 AND cc.is_leaf_flag = 'N'
1031 AND cc.aggregate_next_level_flag = 'Y'
1032 AND b.user_dim1_id = ud1.child_value_id
1033 AND ud1.is_leaf_flag = 'N'
1034 AND ud1.aggregate_next_level_flag = 'Y'
1035 GROUP BY
1036 b.LEDGER_ID,
1037 fin.PARENT_FIN_CAT_ID,
1038 fin.NEXT_LEVEL_FIN_CAT_ID,
1039 com.PARENT_COMPANY_ID,
1040 com.NEXT_LEVEL_COMPANY_ID,
1041 cc.PARENT_CC_ID,
1042 cc.NEXT_LEVEL_CC_ID,
1043 ud1.PARENT_VALUE_ID,
1044 ud1.NEXT_LEVEL_VALUE_ID,
1045 b.USER_DIM2_ID,
1046 b.posted_date;
1047
1048 IF g_debug_flag = 'Y' THEN
1049 FII_UTIL.stop_timer();
1050 FII_UTIL.Write_Log ( 'FII_GL_SNAP_SUM_F has been populated successfully' );
1051 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
1052 FII_UTIL.print_timer();
1053 END IF;
1054
1055 g_phase := 'Gather table stats for FII_GL_SNAP_SUM_F';
1056 fnd_stats.gather_table_stats (ownname=>g_schema_name,
1057 tabname=>'FII_GL_SNAP_SUM_F');
1058
1059 g_phase := 'Commit the change';
1060 commit;
1061
1062 IF g_debug_flag = 'Y' THEN
1063 FII_UTIL.Write_Log ('< Leaving REFRESH_GL_SNAP_SUM_F');
1064 FII_UTIL.Write_Log (' ');
1065 END IF;
1066
1067 EXCEPTION
1068
1069 WHEN OTHERS THEN
1070 FII_UTIL.Write_Log ('Other error in REFRESH_GL_SNAP_SUM_F ');
1071 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1072 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
1073 rollback;
1074 raise;
1075
1076 END REFRESH_GL_SNAP_SUM_F;
1077
1078
1079 ----------------------------------------------------
1080 -- PROCEDURE REFRESH_GL_LOCAL_SNAP_F (private)
1081 --
1082 -- This procedure will (fully) refresh table FII_GL_LOCAL_SNAP_F
1083 -- FROM FII_GL_JE_SUMMARY_B.
1084 -- It populates data FROM GL base summary by changing rows INTO columns
1085 ----------------------------------------------------
1086 PROCEDURE REFRESH_GL_LOCAL_SNAP_F IS
1087
1088 l_this_date DATE;
1089 l_year_id NUMBER(15);
1090
1091 BEGIN
1092
1093 g_phase := 'Entering REFRESH_GL_LOCAL_SNAP_F';
1094 IF g_debug_flag = 'Y' THEN
1095 FII_UTIL.Write_Log ('> Entering REFRESH_GL_LOCAL_SNAP_F');
1096 FII_UTIL.start_timer();
1097 END IF;
1098
1099 g_phase := 'Populate l_this_date FROM BIS_SYSTEM_DATE';
1100
1101 --We use a un-shipped profile option FII_TEST_SYSDATE ("FII: Test Sysdate")
1102 --to reset different sysdate so that we can test for snapshot tables.
1103 if g_test_sysdate is NULL THEN
1104 SELECT trunc(CURRENT_DATE_ID) INTO l_this_date
1105 FROM BIS_SYSTEM_DATE;
1106 ELSE
1107 l_this_date := g_test_sysdate;
1108 end if;
1109 ---------------------------------------------------------
1110
1111 --l_year_id := to_char(l_this_date, 'yyyy');
1112 --Bug 4283723: Get the ent year for l_this_date
1113 SELECT ent_year_id INTO l_year_id
1114 FROM fii_time_ent_year
1115 WHERE l_this_date between start_date AND end_date;
1116
1117 IF g_debug_flag = 'Y' THEN
1118 FII_UTIL.Write_Log ('>> l_this_date = '|| l_this_date);
1119 FII_UTIL.Write_Log ('>> l_year_id = ' || l_year_id);
1120 FII_UTIL.Write_Log (' ');
1121 END IF;
1122
1123 g_phase := 'Truncate table FII_GL_LOCAL_SNAP_F';
1124 FII_UTIL.truncate_table ('FII_GL_LOCAL_SNAP_F', 'FII', g_retcode);
1125
1126 g_phase := 'Starting to populate table FII_GL_LOCAL_SNAP_F';
1127 IF g_debug_flag = 'Y' THEN
1128 FII_UTIL.Write_Log ('>> Starting to populate table FII_GL_LOCAL_SNAP_F');
1129 END IF;
1130
1131 l_profile := NVL(FND_PROFILE.VALUE('INDUSTRY'),'C');
1132
1133 /* The code to populate different types of budgets should ONLY run when industry profile is set to Government. Consequently,
1134 if profile set to Government, we execute IF part otherwise, we execute ELSE part */
1135
1136 /* Different amount_type_codes used in the code -
1137 A - Actuals
1138 E - Encumbrances (Enc data is stored as sum of normal encumbrances and carryfwd encumbrances)
1139 B - Actual budgets
1140 BB - Baseline budgets*/
1141
1142
1143 IF l_profile = 'G' THEN
1144
1145 insert /*+ append */ INTO FII_GL_LOCAL_SNAP_F
1146 ( YEAR_ID,
1147 COST_CENTER_ID,
1151 USER_DIM2_ID,
1148 COMPANY_ID,
1149 FIN_CATEGORY_ID,
1150 USER_DIM1_ID,
1152 LEDGER_ID,
1153 FIN_CAT_TYPE_CODE,
1154 PRIM_G_MONTH1,
1155 PRIM_G_MONTH2,
1156 PRIM_G_MONTH3,
1157 PRIM_G_MONTH4,
1158 PRIM_G_MONTH5,
1159 PRIM_G_MONTH6,
1160 PRIM_G_MONTH7,
1161 PRIM_G_MONTH8,
1162 PRIM_G_MONTH9,
1163 PRIM_G_MONTH10,
1164 PRIM_G_MONTH11,
1165 PRIM_G_MONTH12,
1166 PRIM_G_MONTH13,
1167 PRIM_G_QTR1,
1168 PRIM_G_QTR2,
1169 PRIM_G_QTR3,
1170 PRIM_G_QTR4,
1171 PRIM_G_YEAR,
1172 PRIM_G_MTD,
1173 PRIM_G_QTD,
1174 PRIM_G_YTD,
1175 SEC_G_MONTH1,
1176 SEC_G_MONTH2,
1177 SEC_G_MONTH3,
1178 SEC_G_MONTH4,
1179 SEC_G_MONTH5,
1180 SEC_G_MONTH6,
1181 SEC_G_MONTH7,
1182 SEC_G_MONTH8,
1183 SEC_G_MONTH9,
1184 SEC_G_MONTH10,
1185 SEC_G_MONTH11,
1186 SEC_G_MONTH12,
1187 SEC_G_MONTH13,
1188 SEC_G_QTR1,
1189 SEC_G_QTR2,
1190 SEC_G_QTR3,
1191 SEC_G_QTR4,
1192 SEC_G_YEAR,
1193 SEC_G_MTD,
1194 SEC_G_QTD,
1195 SEC_G_YTD,
1196
1197 LAST_UPDATE_DATE,
1198 LAST_UPDATED_BY,
1199 CREATION_DATE,
1200 CREATED_BY,
1201 LAST_UPDATE_LOGIN,
1202 AMOUNT_TYPE_CODE)
1203
1204 WITH summary_full AS (SELECT company_id, cost_center_id, fin_category_id,
1205 user_dim1_id, user_dim2_id, ledger_id,
1206 prim_amount_g, sec_amount_g, obligated_amount_prim,
1207 other_amount_prim, committed_amount_prim,
1208 b.time_id, b.period_type_id,
1209 p.ent_year_id, p.sequence period_seq, q.sequence qtr_seq
1210
1211 FROM fii_gl_je_summary_b b,
1212 fii_time_ent_qtr q,
1213 fii_time_ent_period p
1214
1215 WHERE b.period_type_id = 32
1216 AND b.time_id = p.ent_period_id
1217 AND p.ent_qtr_id = q.ent_qtr_id),
1218
1219 summary_xtd AS (SELECT company_id, cost_center_id, fin_category_id,
1220 user_dim1_id, user_dim2_id, ledger_id,
1221 prim_amount_g, sec_amount_g, obligated_amount_prim,
1222 other_amount_prim, committed_amount_prim,
1223 b.time_id, b.period_type_id, cal.record_type_id
1224 FROM fii_gl_je_summary_b b,
1225 fii_time_structures cal
1226
1227 WHERE cal.report_date = l_this_date
1228 AND cal.time_id = b.time_id
1229 AND cal.period_type_id = b.period_type_id
1230 AND (bitand(cal.record_type_id, 64) = 64 OR
1231 bitand(cal.record_type_id, 128) = 128 OR
1232 bitand(cal.record_type_id, 256) = 256)),
1233
1234 budget_full AS (SELECT company_id, cost_center_id, fin_category_id,
1235 user_dim1_id, user_dim2_id, ledger_id,
1236 prim_amount_g, sec_amount_g, baseline_amount_prim,
1237 b.time_id, b.period_type_id,
1238 p.ent_year_id, p.sequence period_seq, q.sequence qtr_seq
1239
1240 FROM fii_budget_base b,
1241 fii_time_ent_qtr q,
1242 fii_time_ent_period p
1243
1244 WHERE b.plan_type_code = 'B'
1245 AND b.period_type_id = 32
1246 AND b.time_id = p.ent_period_id
1247 AND p.ent_qtr_id = q.ent_qtr_id),
1248
1249 budget_xtd AS (SELECT company_id, cost_center_id, fin_category_id,
1250 user_dim1_id, user_dim2_id, ledger_id,
1251 prim_amount_g, sec_amount_g, baseline_amount_prim,
1252 b.time_id, b.period_type_id, cal.record_type_id
1253
1254 FROM fii_budget_base b,
1255 fii_time_structures cal
1256
1257 WHERE cal.report_date = l_this_date
1258 AND b.plan_type_code = 'B'
1259 AND b.period_type_id = cal.period_type_id
1260 AND b.time_id = cal.time_id
1261 AND (bitand(cal.record_type_id, 64) = 64 OR
1262 bitand(cal.record_type_id, 128) = 128 OR
1263 bitand(cal.record_type_id, 256) = 256)),
1264
1265 carryfwd_full AS (SELECT company_id, cost_center_id, fin_category_id,
1266 user_dim1_id, user_dim2_id, ledger_id,
1267 obligated_amount_prim,
1268 other_amount_prim, committed_amount_prim,
1269 b.time_id, b.period_type_id,
1270 p.ent_year_id, p.sequence period_seq, q.sequence qtr_seq
1271 FROM fii_gl_enc_carryfwd_f b,
1272 fii_time_ent_qtr q,
1273 fii_time_ent_period p
1274 WHERE b.period_type_id = 32
1275 AND b.time_id = p.ent_period_id
1276 AND p.ent_qtr_id = q.ent_qtr_id),
1277
1278 carryfwd_xtd AS (SELECT company_id, cost_center_id, fin_category_id,
1279 user_dim1_id, user_dim2_id, ledger_id,
1280 obligated_amount_prim,
1281 other_amount_prim, committed_amount_prim,
1282 b.time_id, b.period_type_id, cal.record_type_id
1283 FROM fii_gl_enc_carryfwd_f b,
1284 fii_time_structures cal
1285
1286 WHERE cal.report_date = l_this_date
1287 AND cal.time_id = b.time_id
1288 AND cal.period_type_id = b.period_type_id
1289 AND (bitand(cal.record_type_id, 64) = 64 OR
1290 bitand(cal.record_type_id, 128) = 128 OR
1291 bitand(cal.record_type_id, 256) = 256))
1292
1293
1294 SELECT /*+ index(a fii_fin_cat_type_assgns_u1) */
1295 f.ent_year_id year_id,
1296 f.cost_center_id,
1297 f.company_id,
1298 f.fin_category_id,
1299 f.user_dim1_id,
1300 f.user_dim2_id,
1301 f.ledger_id,
1302 a.fin_cat_type_code,
1303 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 1, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1304 'E',SUM(DECODE(period_seq, 1, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1308 SUM(DECODE(period_seq, 2, prim_amt_g))) prim_g_month2,
1305 SUM(DECODE(period_seq, 1, prim_amt_g))) prim_g_month1,
1306 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 2, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1307 'E',SUM(DECODE(period_seq, 2, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1309 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 3, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1310 'E',SUM(DECODE(period_seq, 3, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1311 SUM(DECODE(period_seq, 3, prim_amt_g))) prim_g_month3,
1312 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 4, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1313 'E',SUM(DECODE(period_seq, 4, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1314 SUM(DECODE(period_seq, 4, prim_amt_g))) prim_g_month4,
1315 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 5, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1316 'E',SUM(DECODE(period_seq, 5, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1317 SUM(DECODE(period_seq, 5, prim_amt_g))) prim_g_month5,
1318 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 6, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1319 'E',SUM(DECODE(period_seq, 6, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1320 SUM(DECODE(period_seq, 6, prim_amt_g))) prim_g_month6,
1321 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 7, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1322 'E',SUM(DECODE(period_seq, 7, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1323 SUM(DECODE(period_seq, 7, prim_amt_g))) prim_g_month7,
1324 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 8, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1325 'E',SUM(DECODE(period_seq, 8, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1326 SUM(DECODE(period_seq, 8, prim_amt_g))) prim_g_month8,
1327 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 9, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1328 'E',SUM(DECODE(period_seq, 9, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1329 SUM(DECODE(period_seq, 9, prim_amt_g))) prim_g_month9,
1330 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 10, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1331 'E',SUM(DECODE(period_seq, 10, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1332 SUM(DECODE(period_seq, 10, prim_amt_g))) prim_g_month10,
1333 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 11, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1334 'E',SUM(DECODE(period_seq, 11, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1335 SUM(DECODE(period_seq, 11, prim_amt_g))) prim_g_month11,
1336 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 12, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1337 'E',SUM(DECODE(period_seq, 12, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1338 SUM(DECODE(period_seq, 12, prim_amt_g))) prim_g_month12,
1339 DECODE(amount_type_code, 'A',SUM(DECODE(period_seq, 13, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1340 'E',SUM(DECODE(period_seq, 13, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1341 SUM(DECODE(period_seq, 13, prim_amt_g))) prim_g_month13,
1342 DECODE(amount_type_code, 'A',SUM(DECODE(qtr_seq, 1, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1343 'E',SUM(DECODE(qtr_seq, 1, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1344 SUM(DECODE(qtr_seq, 1, prim_amt_g))) prim_g_qtr1,
1345 DECODE(amount_type_code, 'A',SUM(DECODE(qtr_seq, 2, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1346 'E',SUM(DECODE(qtr_seq, 2, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1347 SUM(DECODE(qtr_seq, 2, prim_amt_g))) prim_g_qtr2,
1348 DECODE(amount_type_code, 'A',SUM(DECODE(qtr_seq, 3, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1349 'E',SUM(DECODE(qtr_seq, 3, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1350 SUM(DECODE(qtr_seq, 3, prim_amt_g))) prim_g_qtr3,
1351 DECODE(amount_type_code, 'A',SUM(DECODE(qtr_seq, 4, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1352 'E',SUM(DECODE(qtr_seq, 4, prim_amt_g)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1353 SUM(DECODE(qtr_seq, 4, prim_amt_g))) prim_g_qtr4,
1354 DECODE(amount_type_code, 'A',SUM(NVL(DECODE(qtr_seq, 1, prim_amt_g),0) +
1355 NVL(DECODE(qtr_seq, 2, prim_amt_g),0) +
1356 NVL(DECODE(qtr_seq, 3, prim_amt_g),0) +
1357 NVL(DECODE(qtr_seq, 4, prim_amt_g),0)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1358 'E',SUM(NVL(DECODE(qtr_seq, 1, prim_amt_g),0) +
1359 NVL(DECODE(qtr_seq, 2, prim_amt_g),0) +
1360 NVL(DECODE(qtr_seq, 3, prim_amt_g),0) +
1361 NVL(DECODE(qtr_seq, 4, prim_amt_g),0)) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1362 SUM(NVL(DECODE(qtr_seq, 1, prim_amt_g),0) +
1363 NVL(DECODE(qtr_seq, 2, prim_amt_g),0) +
1364 NVL(DECODE(qtr_seq, 3, prim_amt_g),0) +
1365 NVL(DECODE(qtr_seq, 4, prim_amt_g),0))) prim_g_year,
1366
1367 DECODE(amount_type_code, 'A',SUM(mtd_prim_amt_g) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1368 'E',SUM(mtd_prim_amt_g) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1369 SUM(mtd_prim_amt_g)) prim_g_mtd,
1370 DECODE(amount_type_code, 'A',SUM(qtd_prim_amt_g) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1371 'E',SUM(qtd_prim_amt_g) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1372 SUM(qtd_prim_amt_g)) prim_g_qtd,
1373 DECODE(amount_type_code, 'A',SUM(ytd_prim_amt_g) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1374 'E',SUM(ytd_prim_amt_g) * DECODE(a.fin_cat_type_code, 'R', 1, -1),
1375 SUM(ytd_prim_amt_g)) prim_g_ytd,
1376 SUM(DECODE(period_seq, 1, sec_amt_g))
1377 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month1,
1378 SUM(DECODE(period_seq, 2, sec_amt_g))
1379 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month2,
1380 SUM(DECODE(period_seq, 3, sec_amt_g))
1381 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month3,
1382 SUM(DECODE(period_seq, 4, sec_amt_g))
1386 SUM(DECODE(period_seq, 6, sec_amt_g))
1383 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month4,
1384 SUM(DECODE(period_seq, 5, sec_amt_g))
1385 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month5,
1387 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month6,
1388 SUM(DECODE(period_seq, 7, sec_amt_g))
1389 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month7,
1390 SUM(DECODE(period_seq, 8, sec_amt_g))
1391 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month8,
1392 SUM(DECODE(period_seq, 9, sec_amt_g))
1393 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month9,
1394 SUM(DECODE(period_seq, 10, sec_amt_g))
1395 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month10,
1396 SUM(DECODE(period_seq, 11, sec_amt_g))
1397 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month11,
1398 SUM(DECODE(period_seq, 12, sec_amt_g))
1399 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month12,
1400 SUM(DECODE(period_seq, 13, sec_amt_g))
1401 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month13,
1402 SUM(DECODE(qtr_seq, 1, sec_amt_g))
1403 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr1,
1404 SUM(DECODE(qtr_seq, 2, sec_amt_g))
1405 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr2,
1406 SUM(DECODE(qtr_seq, 3, sec_amt_g))
1407 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr3,
1408 SUM(DECODE(qtr_seq, 4, sec_amt_g))
1409 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr4,
1410 SUM(NVL(DECODE(qtr_seq, 1, sec_amt_g),0) +
1411 NVL(DECODE(qtr_seq, 2, sec_amt_g),0) +
1412 NVL(DECODE(qtr_seq, 3, sec_amt_g),0) +
1413 NVL(DECODE(qtr_seq, 4, sec_amt_g),0))
1414 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_year,
1415 SUM(mtd_sec_amt_g)
1416 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_mtd,
1417 SUM(qtd_sec_amt_g)
1418 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtd,
1419 SUM(ytd_sec_amt_g)
1420 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_ytd,
1421
1422 g_fii_sysdate,
1423 g_fii_user_id,
1424 g_fii_sysdate,
1425 g_fii_user_id,
1426 g_fii_login_id,
1427 amount_type_code
1428
1429 FROM
1430 (SELECT b.ent_year_id, b.qtr_seq, b.period_seq,
1431 b.company_id, b.cost_center_id, b.fin_category_id,
1432 b.user_dim1_id, b.user_dim2_id, ledger_id,
1433 SUM(b.prim_amount_g) prim_amt_g,
1434 SUM(b.sec_amount_g) sec_amt_g,
1435 NULL mtd_prim_amt_g, NULL qtd_prim_amt_g, NULL ytd_prim_amt_g,
1436 NULL mtd_sec_amt_g, NULL qtd_sec_amt_g, NULL ytd_sec_amt_g,
1437 'A' amount_type_code
1438
1439 FROM summary_full b
1440
1441 GROUP BY b.ent_year_id, b.qtr_seq, b.period_seq,
1442 b.company_id, b.cost_center_id, b.fin_category_id,
1443 b.user_dim1_id, b.user_dim2_id, ledger_id, 'A'
1444
1445 UNION ALL
1446
1447 SELECT l_year_id ent_year_id, 0 qtr_seq, 0 period_seq,
1448 b1.company_id, b1.cost_center_id, b1.fin_category_id,
1449 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id,
1450 NULL prim_amt_g, NULL sec_amt_g,
1451 SUM(CASE WHEN bitand(b1.record_type_id, 64) = 64
1452 THEN b1.prim_amount_g
1453 ELSE NULL end) mtd_prim_amt_g,
1454 SUM(CASE WHEN bitand(b1.record_type_id, 128) = 128
1455 THEN b1.prim_amount_g
1456 ELSE NULL end) qtd_prim_amt_g,
1457 SUM(CASE WHEN bitand(b1.record_type_id, 256) = 256
1458 THEN b1.prim_amount_g
1459 ELSE NULL end) ytd_prim_amt_g,
1460 SUM(CASE WHEN bitand(b1.record_type_id, 64) = 64
1461 THEN b1.sec_amount_g
1462 ELSE NULL end) mtd_sec_amt_g,
1463 SUM(CASE WHEN bitand(b1.record_type_id, 128) = 128
1464 THEN b1.sec_amount_g
1465 ELSE NULL end) qtd_sec_amt_g,
1466 SUM(CASE WHEN bitand(b1.record_type_id, 256) = 256
1467 THEN b1.sec_amount_g
1468 ELSE NULL end) ytd_sec_amt_g,
1469 'A' amount_type_code
1470
1471 FROM summary_xtd b1
1472
1473 GROUP BY b1.company_id, b1.cost_center_id, b1.fin_category_id,
1474 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id, 'A'
1475
1476 UNION ALL
1477
1478 SELECT b.ent_year_id, b.qtr_seq, b.period_seq,
1479 b.company_id, b.cost_center_id, b.fin_category_id,
1480 b.user_dim1_id, b.user_dim2_id, ledger_id,
1481 NVL(SUM(b.obligated_amount_prim),0) +
1482 NVL(SUM(b.committed_amount_prim),0) +
1483 NVL(SUM(b.other_amount_prim),0) prim_amt_g,
1484 NULL sec_amt_g,
1485 NULL mtd_prim_amt_g, NULL qtd_prim_amt_g, NULL ytd_prim_amt_g,
1486 NULL mtd_sec_amt_g, NULL qtd_sec_amt_g, NULL ytd_sec_amt_g,
1487 'E' amount_type_code
1488
1489 FROM summary_full b
1490
1491 GROUP BY b.ent_year_id, b.qtr_seq, b.period_seq,
1492 b.company_id, b.cost_center_id, b.fin_category_id,
1493 b.user_dim1_id, b.user_dim2_id, ledger_id, 'E'
1494
1495 UNION ALL
1496
1497 SELECT l_year_id ent_year_id, 0 qtr_seq, 0 period_seq,
1498 b1.company_id, b1.cost_center_id, b1.fin_category_id,
1499 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id,
1500 NULL prim_amt_g, NULL sec_amt_g,
1501 SUM(CASE WHEN bitand(b1.record_type_id, 64) = 64
1502 THEN NVL(b1.obligated_amount_prim,0) +
1503 NVL(b1.committed_amount_prim,0) +
1504 NVL(b1.other_amount_prim,0)
1508 NVL(b1.committed_amount_prim,0) +
1505 ELSE NULL end) mtd_prim_amt_g,
1506 SUM(CASE WHEN bitand(b1.record_type_id, 128) = 128
1507 THEN NVL(b1.obligated_amount_prim,0) +
1509 NVL(b1.other_amount_prim,0)
1510 ELSE NULL end) qtd_prim_amt_g,
1511 SUM(CASE WHEN bitand(b1.record_type_id, 256) = 256
1512 THEN NVL(b1.obligated_amount_prim,0) +
1513 NVL(b1.committed_amount_prim,0) +
1514 NVL(b1.other_amount_prim,0)
1515 ELSE NULL end) ytd_prim_amt_g,
1516 NULL mtd_sec_amt_g,
1517 NULL qtd_sec_amt_g,
1518 NULL ytd_sec_amt_g,
1519 'E' amount_type_code
1520
1521 FROM summary_xtd b1
1522
1523 GROUP BY b1.company_id, b1.cost_center_id, b1.fin_category_id,
1524 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id, 'E'
1525
1526
1527 UNION ALL
1528
1529 SELECT b.ent_year_id, b.qtr_seq, b.period_seq,
1530 b.company_id, b.cost_center_id, b.fin_category_id,
1531 b.user_dim1_id, b.user_dim2_id, ledger_id,
1532 NVL(SUM(b.obligated_amount_prim),0) +
1533 NVL(SUM(b.committed_amount_prim),0) +
1534 NVL(SUM(b.other_amount_prim),0) prim_amt_g,
1535 NULL sec_amt_g,
1536 NULL mtd_prim_amt_g, NULL qtd_prim_amt_g, NULL ytd_prim_amt_g,
1537 NULL mtd_sec_amt_g, NULL qtd_sec_amt_g, NULL ytd_sec_amt_g,
1538 'E' amount_type_code
1539
1540 FROM carryfwd_full b
1541
1542 GROUP BY b.ent_year_id, b.qtr_seq, b.period_seq,
1543 b.company_id, b.cost_center_id, b.fin_category_id,
1544 b.user_dim1_id, b.user_dim2_id, ledger_id, 'E'
1545
1546 UNION ALL
1547
1548 SELECT l_year_id ent_year_id, 0 qtr_seq, 0 period_seq,
1549 b1.company_id, b1.cost_center_id, b1.fin_category_id,
1550 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id,
1551 NULL prim_amt_g, NULL sec_amt_g,
1552 SUM(CASE WHEN bitand(b1.record_type_id, 64) = 64
1553 THEN NVL(b1.obligated_amount_prim,0) +
1554 NVL(b1.committed_amount_prim,0) +
1555 NVL(b1.other_amount_prim,0)
1556 ELSE NULL end) mtd_prim_amt_g,
1557 SUM(CASE WHEN bitand(b1.record_type_id, 128) = 128
1558 THEN NVL(b1.obligated_amount_prim,0) +
1559 NVL(b1.committed_amount_prim,0) +
1560 NVL(b1.other_amount_prim,0)
1561 ELSE NULL end) qtd_prim_amt_g,
1562 SUM(CASE WHEN bitand(b1.record_type_id, 256) = 256
1563 THEN NVL(b1.obligated_amount_prim,0) +
1564 NVL(b1.committed_amount_prim,0) +
1565 NVL(b1.other_amount_prim,0)
1566 ELSE NULL end) ytd_prim_amt_g,
1567 NULL mtd_sec_amt_g,
1568 NULL qtd_sec_amt_g,
1569 NULL ytd_sec_amt_g,
1570 'E' amount_type_code
1571
1572 FROM carryfwd_xtd b1
1573
1574 GROUP BY b1.company_id, b1.cost_center_id, b1.fin_category_id,
1575 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id, 'E'
1576
1577 UNION ALL
1578
1579 SELECT c.ent_year_id, c.qtr_seq, c.period_seq,
1580 c.company_id, c.cost_center_id, c.fin_category_id,
1581 c.user_dim1_id, c.user_dim2_id, c.ledger_id ledger_id,
1582 SUM(c.prim_amount_g) prim_amt_g,
1583 NULL sec_amt_g,
1584 NULL mtd_prim_amt_g, NULL qtd_prim_amt_g, NULL ytd_prim_amt_g,
1585 NULL mtd_sec_amt_g, NULL qtd_sec_amt_g, NULL ytd_sec_amt_g,
1586 'B' amount_type_code
1587
1588
1589 FROM budget_full c
1590
1591 GROUP BY c.ent_year_id, c.qtr_seq, c.period_seq,
1592 c.company_id, c.cost_center_id, c.fin_category_id,
1593 c.user_dim1_id, c.user_dim2_id, c.ledger_id,'B'
1594
1595 UNION ALL
1596
1597 SELECT l_year_id ent_year_id, 0 qtr_seq, 0 period_seq,
1598 c1.company_id, c1.cost_center_id, c1.fin_category_id,
1599 c1.user_dim1_id, c1.user_dim2_id, c1.ledger_id ledger_id,
1600 NULL prim_amt_g, NULL sec_amt_g,
1601 SUM(CASE WHEN bitand(c1.record_type_id, 64) = 64
1602 THEN c1.prim_amount_g
1603 ELSE NULL end) mtd_prim_amt_g,
1604 SUM(CASE WHEN bitand(c1.record_type_id, 128) = 128
1605 THEN c1.prim_amount_g
1606 ELSE NULL end) qtd_prim_amt_g,
1607 SUM(CASE WHEN bitand(c1.record_type_id, 256) = 256
1608 THEN c1.prim_amount_g
1609 ELSE NULL end) ytd_prim_amt_g,
1610 NULL mtd_sec_amt_g,
1611 NULL qtd_sec_amt_g,
1612 NULL ytd_sec_amt_g,
1613 'B' amount_type_code
1614
1615 FROM budget_xtd c1
1616
1617 GROUP BY c1.company_id, c1.cost_center_id, c1.fin_category_id,
1618 c1.user_dim1_id, c1.user_dim2_id, c1.ledger_id, 'B'
1619
1620 UNION ALL
1621
1622 SELECT c.ent_year_id, c.qtr_seq, c.period_seq,
1623 c.company_id, c.cost_center_id, c.fin_category_id,
1624 c.user_dim1_id, c.user_dim2_id, c.ledger_id ledger_id,
1625 SUM(c.baseline_amount_prim) prim_amt_g,
1626 NULL sec_amt_g,
1627 NULL mtd_prim_amt_g, NULL qtd_prim_amt_g, NULL ytd_prim_amt_g,
1628 NULL mtd_sec_amt_g, NULL qtd_sec_amt_g, NULL ytd_sec_amt_g,
1629 'BB' amount_type_code
1630
1631
1632 FROM budget_full c
1633
1634 GROUP BY c.ent_year_id, c.qtr_seq, c.period_seq,
1635 c.company_id, c.cost_center_id, c.fin_category_id,
1636 c.user_dim1_id, c.user_dim2_id, c.ledger_id, 'BB'
1637
1638 UNION ALL
1639
1640 SELECT l_year_id ent_year_id, 0 qtr_seq, 0 period_seq,
1641 c1.company_id, c1.cost_center_id, c1.fin_category_id,
1642 c1.user_dim1_id, c1.user_dim2_id, c1.ledger_id ledger_id,
1646 ELSE NULL end) mtd_prim_amt_g,
1643 NULL prim_amt_g, NULL sec_amt_g,
1644 SUM(CASE WHEN bitand(c1.record_type_id, 64) = 64
1645 THEN c1.baseline_amount_prim
1647 SUM(CASE WHEN bitand(c1.record_type_id, 128) = 128
1648 THEN c1.baseline_amount_prim
1649 ELSE NULL end) qtd_prim_amt_g,
1650 SUM(CASE WHEN bitand(c1.record_type_id, 256) = 256
1651 THEN c1.baseline_amount_prim
1652 ELSE NULL end) ytd_prim_amt_g,
1653 NULL mtd_sec_amt_g,
1654 NULL qtd_sec_amt_g,
1655 NULL ytd_sec_amt_g,
1656 'BB' amount_type_code
1657
1658 FROM budget_xtd c1
1659
1660 GROUP BY c1.company_id, c1.cost_center_id, c1.fin_category_id,
1661 c1.user_dim1_id, c1.user_dim2_id, c1.ledger_id, 'BB') f,
1662
1663 fii_fin_cat_type_assgns a,
1664 fii_com_cc_dim_maps m,
1665 fii_fin_cat_leaf_maps c,
1666 fii_udd1_mappings ud1,
1667 fii_udd2_mappings ud2
1668
1669 WHERE f.fin_category_id = a.fin_category_id
1670 AND a.fin_cat_type_code in ('R', 'OE', 'TE', 'PE', 'CGS')
1671 AND f.user_dim1_id = ud1.child_user_dim1_id
1672 AND f.user_dim2_id = ud2.child_user_dim2_id
1673 AND f.company_id = m.child_company_id
1674 AND f.cost_center_id = m.child_cost_center_id
1675 AND f.fin_category_id = c.child_fin_cat_id
1676
1677 GROUP BY f.ent_year_id, f.company_id,
1678 f.cost_center_id, f.fin_category_id,
1679 f.user_dim1_id, f.user_dim2_id,
1680 f.ledger_id, a.fin_cat_type_code,amount_type_code;
1681
1682 ELSE
1683
1684 insert /*+ append */ INTO FII_GL_LOCAL_SNAP_F
1685 ( YEAR_ID,
1686 COST_CENTER_ID,
1687 COMPANY_ID,
1688 FIN_CATEGORY_ID,
1689 USER_DIM1_ID,
1690 USER_DIM2_ID,
1691 LEDGER_ID,
1692 FIN_CAT_TYPE_CODE,
1693 PRIM_G_MONTH1,
1694 PRIM_G_MONTH2,
1695 PRIM_G_MONTH3,
1696 PRIM_G_MONTH4,
1697 PRIM_G_MONTH5,
1698 PRIM_G_MONTH6,
1699 PRIM_G_MONTH7,
1700 PRIM_G_MONTH8,
1701 PRIM_G_MONTH9,
1702 PRIM_G_MONTH10,
1703 PRIM_G_MONTH11,
1704 PRIM_G_MONTH12,
1705 PRIM_G_MONTH13,
1706 PRIM_G_QTR1,
1707 PRIM_G_QTR2,
1708 PRIM_G_QTR3,
1709 PRIM_G_QTR4,
1710 PRIM_G_YEAR,
1711 PRIM_G_MTD,
1712 PRIM_G_QTD,
1713 PRIM_G_YTD,
1714 SEC_G_MONTH1,
1715 SEC_G_MONTH2,
1716 SEC_G_MONTH3,
1717 SEC_G_MONTH4,
1718 SEC_G_MONTH5,
1719 SEC_G_MONTH6,
1720 SEC_G_MONTH7,
1721 SEC_G_MONTH8,
1722 SEC_G_MONTH9,
1723 SEC_G_MONTH10,
1724 SEC_G_MONTH11,
1725 SEC_G_MONTH12,
1726 SEC_G_MONTH13,
1727 SEC_G_QTR1,
1728 SEC_G_QTR2,
1729 SEC_G_QTR3,
1730 SEC_G_QTR4,
1731 SEC_G_YEAR,
1732 SEC_G_MTD,
1733 SEC_G_QTD,
1734 SEC_G_YTD,
1735
1736 LAST_UPDATE_DATE,
1737 LAST_UPDATED_BY,
1738 CREATION_DATE,
1739 CREATED_BY,
1740 LAST_UPDATE_LOGIN,
1741 AMOUNT_TYPE_CODE)
1742
1743 WITH summary_full AS (SELECT company_id, cost_center_id, fin_category_id,
1744 user_dim1_id, user_dim2_id, ledger_id,
1745 prim_amount_g, sec_amount_g,
1746 b.time_id, b.period_type_id,
1747 p.ent_year_id, p.sequence period_seq, q.sequence qtr_seq
1748
1749 FROM fii_gl_je_summary_b b,
1750 fii_time_ent_qtr q,
1751 fii_time_ent_period p
1752
1753 WHERE b.period_type_id = 32
1754 AND b.time_id = p.ent_period_id
1755 AND p.ent_qtr_id = q.ent_qtr_id),
1756
1757 summary_xtd AS (SELECT company_id, cost_center_id, fin_category_id,
1758 user_dim1_id, user_dim2_id, ledger_id,
1759 prim_amount_g, sec_amount_g,
1760 b.time_id, b.period_type_id, cal.record_type_id
1761
1762 FROM fii_gl_je_summary_b b,
1763 fii_time_structures cal
1764
1765 WHERE cal.report_date = l_this_date
1766 AND cal.time_id = b.time_id
1767 AND cal.period_type_id = b.period_type_id
1768 AND (bitand(cal.record_type_id, 64) = 64 OR
1769 bitand(cal.record_type_id, 128) = 128 OR
1770 bitand(cal.record_type_id, 256) = 256))
1771 SELECT
1772 f.ent_year_id year_id,
1773 f.cost_center_id,
1774 f.company_id,
1775 f.fin_category_id,
1776 f.user_dim1_id,
1777 f.user_dim2_id,
1778 f.ledger_id,
1779 a.fin_cat_type_code,
1780 SUM(DECODE(period_seq, 1, prim_amt_g))
1781 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month1,
1782 SUM(DECODE(period_seq, 2, prim_amt_g))
1783 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month2,
1784 SUM(DECODE(period_seq, 3, prim_amt_g))
1785 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month3,
1786 SUM(DECODE(period_seq, 4, prim_amt_g))
1787 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month4,
1788 SUM(DECODE(period_seq, 5, prim_amt_g))
1789 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month5,
1790 SUM(DECODE(period_seq, 6, prim_amt_g))
1791 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month6,
1792 SUM(DECODE(period_seq, 7, prim_amt_g))
1793 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month7,
1794 SUM(DECODE(period_seq, 8, prim_amt_g))
1795 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month8,
1796 SUM(DECODE(period_seq, 9, prim_amt_g))
1797 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month9,
1798 SUM(DECODE(period_seq, 10, prim_amt_g))
1802 SUM(DECODE(period_seq, 12, prim_amt_g))
1799 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month10,
1800 SUM(DECODE(period_seq, 11, prim_amt_g))
1801 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month11,
1803 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month12,
1804 SUM(DECODE(period_seq, 13, prim_amt_g))
1805 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_month13,
1806 SUM(DECODE(qtr_seq, 1, prim_amt_g))
1807 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_qtr1,
1808 SUM(DECODE(qtr_seq, 2, prim_amt_g))
1809 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_qtr2,
1810 SUM(DECODE(qtr_seq, 3, prim_amt_g))
1811 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_qtr3,
1812 SUM(DECODE(qtr_seq, 4, prim_amt_g))
1813 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_qtr4,
1814 SUM(NVL(DECODE(qtr_seq, 1, prim_amt_g),0) +
1815 NVL(DECODE(qtr_seq, 2, prim_amt_g),0) +
1816 NVL(DECODE(qtr_seq, 3, prim_amt_g),0) +
1817 NVL(DECODE(qtr_seq, 4, prim_amt_g),0))
1818 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_year,
1819 SUM(mtd_prim_amt_g)
1820 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_mtd,
1821 SUM(qtd_prim_amt_g)
1822 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_qtd,
1823 SUM(ytd_prim_amt_g)
1824 * DECODE(a.fin_cat_type_code, 'R', 1, -1) prim_g_ytd,
1825
1826 SUM(DECODE(period_seq, 1, sec_amt_g))
1827 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month1,
1828 SUM(DECODE(period_seq, 2, sec_amt_g))
1829 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month2,
1830 SUM(DECODE(period_seq, 3, sec_amt_g))
1831 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month3,
1832 SUM(DECODE(period_seq, 4, sec_amt_g))
1833 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month4,
1834 SUM(DECODE(period_seq, 5, sec_amt_g))
1835 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month5,
1836 SUM(DECODE(period_seq, 6, sec_amt_g))
1837 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month6,
1838 SUM(DECODE(period_seq, 7, sec_amt_g))
1839 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month7,
1840 SUM(DECODE(period_seq, 8, sec_amt_g))
1841 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month8,
1842 SUM(DECODE(period_seq, 9, sec_amt_g))
1843 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month9,
1844 SUM(DECODE(period_seq, 10, sec_amt_g))
1845 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month10,
1846 SUM(DECODE(period_seq, 11, sec_amt_g))
1847 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month11,
1848 SUM(DECODE(period_seq, 12, sec_amt_g))
1849 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month12,
1850 SUM(DECODE(period_seq, 13, sec_amt_g))
1851 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_month13,
1852 SUM(DECODE(qtr_seq, 1, sec_amt_g))
1853 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr1,
1854 SUM(DECODE(qtr_seq, 2, sec_amt_g))
1855 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr2,
1856 SUM(DECODE(qtr_seq, 3, sec_amt_g))
1857 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr3,
1858 SUM(DECODE(qtr_seq, 4, sec_amt_g))
1859 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtr4,
1860 SUM(NVL(DECODE(qtr_seq, 1, sec_amt_g),0) +
1861 NVL(DECODE(qtr_seq, 2, sec_amt_g),0) +
1862 NVL(DECODE(qtr_seq, 3, sec_amt_g),0) +
1863 NVL(DECODE(qtr_seq, 4, sec_amt_g),0))
1864 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_year,
1865 SUM(mtd_sec_amt_g)
1866 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_mtd,
1867 SUM(qtd_sec_amt_g)
1868 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_qtd,
1869 SUM(ytd_sec_amt_g)
1870 * DECODE(a.fin_cat_type_code, 'R', 1, -1) sec_g_ytd,
1871
1872 g_fii_sysdate,
1873 g_fii_user_id,
1874 g_fii_sysdate,
1875 g_fii_user_id,
1876 g_fii_login_id,
1877 amount_type_code
1878
1879 FROM
1880 (SELECT b.ent_year_id, b.qtr_seq, b.period_seq,
1881 b.company_id, b.cost_center_id, b.fin_category_id,
1882 b.user_dim1_id, b.user_dim2_id, ledger_id,
1883 SUM(b.prim_amount_g) prim_amt_g,
1884 SUM(b.sec_amount_g) sec_amt_g,
1885 NULL mtd_prim_amt_g, NULL qtd_prim_amt_g, NULL ytd_prim_amt_g,
1886 NULL mtd_sec_amt_g, NULL qtd_sec_amt_g, NULL ytd_sec_amt_g,
1887 'A' amount_type_code
1888
1889 FROM summary_full b
1890
1891 GROUP BY b.ent_year_id, b.qtr_seq, b.period_seq,
1892 b.company_id, b.cost_center_id, b.fin_category_id,
1893 b.user_dim1_id, b.user_dim2_id, ledger_id, 'A'
1894
1895 UNION ALL
1896
1897 SELECT l_year_id ent_year_id, 0 qtr_seq, 0 period_seq,
1898 b1.company_id, b1.cost_center_id, b1.fin_category_id,
1899 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id,
1900 NULL prim_amt_g, NULL sec_amt_g,
1901 SUM(CASE WHEN bitand(b1.record_type_id, 64) = 64
1902 THEN b1.prim_amount_g
1903 ELSE NULL end) mtd_prim_amt_g,
1904 SUM(CASE WHEN bitand(b1.record_type_id, 128) = 128
1905 THEN b1.prim_amount_g
1906 ELSE NULL end) qtd_prim_amt_g,
1907 SUM(CASE WHEN bitand(b1.record_type_id, 256) = 256
1908 THEN b1.prim_amount_g
1909 ELSE NULL end) ytd_prim_amt_g,
1910 SUM(CASE WHEN bitand(b1.record_type_id, 64) = 64
1911 THEN b1.sec_amount_g
1912 ELSE NULL end) mtd_sec_amt_g,
1913 SUM(CASE WHEN bitand(b1.record_type_id, 128) = 128
1914 THEN b1.sec_amount_g
1915 ELSE NULL end) qtd_sec_amt_g,
1916 SUM(CASE WHEN bitand(b1.record_type_id, 256) = 256
1917 THEN b1.sec_amount_g
1918 ELSE NULL end) ytd_sec_amt_g,
1919 'A' amount_type_code
1920
1921 FROM summary_xtd b1
1922
1923 GROUP BY b1.company_id, b1.cost_center_id, b1.fin_category_id,
1924 b1.user_dim1_id, b1.user_dim2_id, b1.ledger_id, 'A') f,
1925
1926 fii_fin_cat_type_assgns a,
1927 fii_com_cc_dim_maps m,
1928 fii_fin_cat_leaf_maps c,
1929 fii_udd1_mappings ud1,
1930 fii_udd2_mappings ud2
1931
1932 WHERE f.fin_category_id = a.fin_category_id
1933 AND a.fin_cat_type_code in ('R', 'OE', 'TE', 'PE', 'CGS')
1934 AND f.user_dim1_id = ud1.child_user_dim1_id
1935 AND f.user_dim2_id = ud2.child_user_dim2_id
1936 AND f.company_id = m.child_company_id
1937 AND f.cost_center_id = m.child_cost_center_id
1938 AND f.fin_category_id = c.child_fin_cat_id
1939
1940 GROUP BY f.ent_year_id, f.company_id,
1941 f.cost_center_id, f.fin_category_id,
1942 f.user_dim1_id, f.user_dim2_id,
1943 f.ledger_id, a.fin_cat_type_code,amount_type_code;
1944
1945 END IF;
1946
1947
1948 IF g_debug_flag = 'Y' THEN
1949 FII_UTIL.stop_timer();
1950 FII_UTIL.Write_Log ( 'FII_GL_LOCAL_SNAP_F has been populated successfully' );
1951 FII_UTIL.Write_Log ('Inserted ' || SQL%ROWCOUNT || ' rows');
1952 FII_UTIL.print_timer();
1953 END IF;
1954
1955 g_phase := 'Gather table stats for FII_GL_LOCAL_SNAP_F';
1956 fnd_stats.gather_table_stats (ownname=>g_schema_name,
1957 tabname=>'FII_GL_LOCAL_SNAP_F');
1958
1959 g_phase := 'Commit the change';
1960 commit;
1961
1962 IF g_debug_flag = 'Y' THEN
1963 FII_UTIL.Write_Log ('< Leaving REFRESH_GL_LOCAL_SNAP_F');
1964 FII_UTIL.Write_Log (' ');
1965 END IF;
1966
1967 EXCEPTION
1968
1969 WHEN OTHERS THEN
1970 FII_UTIL.Write_Log ('Other error in REFRESH_GL_LOCAL_SNAP_F ');
1971 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1972 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
1973 rollback;
1974 raise;
1975
1976 END REFRESH_GL_LOCAL_SNAP_F;
1977
1978
1979 ----------------------------------------------------------
1980 -- PROCEDURE MAIN (public)
1981 --
1982 -- This procedure will (fully) refresh all snapshot tables
1983 ----------------------------------------------------------
1984 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2,
1985 retcode IN OUT NOCOPY VARCHAR2) IS
1986
1987 ret_val BOOLEAN := FALSE;
1988
1989 Begin
1990
1991 g_phase := 'Entering Main';
1992 IF g_debug_flag = 'Y' THEN
1993 FII_UTIL.Write_Log ('Entering Main');
1994 END IF;
1995
1996 g_phase := 'Calling Initialize';
1997 IF g_debug_flag = 'Y' THEN
1998 FII_UTIL.Write_Log ('Calling Initialize');
1999 END IF;
2000
2001 Initialize;
2002
2003 g_phase := 'Populating FII_GL_LOCAL_SNAP_F';
2004 IF g_debug_flag = 'Y' THEN
2005 FII_UTIL.Write_Log ('Populating FII_GL_LOCAL_SNAP_F');
2006 END IF;
2007
2008 REFRESH_GL_LOCAL_SNAP_F;
2009
2010 g_phase := 'Populating FII_GL_SNAP_F';
2011 IF g_debug_flag = 'Y' THEN
2012 FII_UTIL.Write_Log ('Populating FII_GL_SNAP_F');
2013 END IF;
2014
2015 REFRESH_GL_SNAP_F;
2016
2017 g_phase := 'Populating FII_GL_SNAP_SUM_F';
2018 IF g_debug_flag = 'Y' THEN
2019 FII_UTIL.Write_Log ('Populating FII_GL_SNAP_SUM_F');
2020 END IF;
2021
2022 REFRESH_GL_SNAP_SUM_F;
2023
2024 g_phase := 'Exiting after successful completion';
2025 IF g_debug_flag = 'Y' THEN
2026 FII_UTIL.Write_Log ('Exiting after successful completion');
2027 END IF;
2028
2029
2030 EXCEPTION
2031
2032 WHEN OTHERS THEN
2033 FII_UTIL.Write_Log ('Other error in Main ');
2034 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
2035 FII_UTIL.Write_Log ('-->'|| sqlcode ||':'|| substr(sqlerrm,1,180));
2036
2037 FND_CONCURRENT.Af_Rollback;
2038 retcode := sqlcode;
2039 errbuf := sqlerrm;
2040 ret_val := FND_CONCURRENT.Set_Completion_Status
2041 (status => 'ERROR', message => substr(errbuf,1,180));
2042
2043 END Main;
2044
2045
2046 END FII_SNAP_TBL_REFRESH;