[Home] [Help]
PACKAGE BODY: APPS.FII_GL_BUDGET_EXTRACTION
Source
1 PACKAGE BODY FII_GL_BUDGET_EXTRACTION AS
2 /*$Header: FIIBUDXB.pls 120.4 2007/06/20 04:05:31 wywong ship $*/
3
4 g_usage_code CONSTANT VARCHAR2(10) := 'DBI';
5 g_debug_flag CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
6 g_phase VARCHAR2(100);
7 g_prim_curr VARCHAR2(30) := NULL;
8 g_sec_curr VARCHAR2(30) := NULL;
9 g_fii_user_id NUMBER(15);
10 g_fii_login_id NUMBER(15);
11 g_unassigned_id NUMBER(15);
12 g_global_start_date CONSTANT DATE := to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'),'MM/DD/YYYY');
13
14
15 -----------------------------------------------------------------------
16 -- PROCEDURE INIT
17 -----------------------------------------------------------------------
18 PROCEDURE Init(retcode IN OUT NOCOPY VARCHAR2) is
19
20 l_vset_id number;
21 l_ret_code varchar2(15);
22 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
23 G_UNASSIGNED_API_ERROR EXCEPTION;
24 BEGIN
25
26 if g_debug_flag = 'Y' then
27 FII_UTIL.write_log('Calling procedure: INIT');
28 FII_UTIL.write_log('');
29 end if;
30
31 g_phase := 'Find currency information.';
32
33 -- Find all currency related information
34 --------------------------------------------------------------
35 g_prim_curr := BIS_COMMON_PARAMETERS.get_currency_code;
36 g_sec_curr := BIS_COMMON_PARAMETERS.get_secondary_currency_code;
37
38 g_phase := 'Find login information.';
39
40 -- Find login information
41 --------------------------------------------------------------
42 g_fii_user_id := FND_GLOBAL.User_Id;
43 g_fii_login_id := FND_GLOBAL.Login_Id;
44
45 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
46 RAISE G_LOGIN_INFO_NOT_AVABLE;
47 END IF;
48
49 IF g_debug_flag = 'Y' THEN
50 FII_UTIL.write_log('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
51 END IF;
52
53 g_phase := 'Find the unassigned value for user_dim1 and user dim2.';
54
55 --Gets the unassigned id value for user_dim1_id and user_dim2_id
56 --------------------------------------------------------------
57 FII_GL_EXTRACTION_UTIL.get_unassigned_id(g_unassigned_id, l_vset_id, l_ret_code);
58 IF(l_ret_code = -1) THEN
59 raise G_UNASSIGNED_API_ERROR;
60 END IF;
61
62 EXCEPTION
63 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
64 retcode := 'E';
65 FII_UTIL.write_log('Init: can not get User ID and Login ID, program exits.');
66 raise;
67 WHEN G_UNASSIGNED_API_ERROR THEN
68 retcode := 'E';
69 FII_UTIL.write_log('Init: UNASSIGNED value API errored, program exits.');
70 raise;
71 WHEN OTHERS THEN
72 retcode := 'E';
73 FII_UTIL.write_log('
74 ---------------------------------
75 Error in Procedure: INIT
76 Phase: '||g_phase||'
77 Message: '||sqlerrm);
78 raise;
79 END Init;
80
81
82
83 -----------------------------------------------------------------------
84 -- PROCEDURE MAIN
85 -----------------------------------------------------------------------
86 --retcode returns 'S' for successful execution and 'E' for Errors
87
88 PROCEDURE Main (retcode IN OUT NOCOPY VARCHAR2)
89 IS
90
91 l_row_count NUMBER(15) := 0;
92 FIIBUDX_fatal_err EXCEPTION;
93 l_status VARCHAR2(1) := null;
94 l_retcode varchar2(15) := 0; --Set to S: Success, E: Error, or W: Warning
95
96 /*
97 --- Warn user of accounts without a finantial category type in fii_fin_cat_type_assgns---
98 -----------------------------------------------------------------------------------------
99 CURSOR miss_fin_type_cur IS
100 SELECT glv.concatenated_segments,
101 sob.name ledger_name,
102 budv.budget_name
103 FROM fii_gl_budget_extract_t t,
104 gl_budget_versions budv,
105 gl_sets_of_books sob,
106 gl_code_combinations_kfv glv,
107 fii_fin_cat_type_assgns fin
108 WHERE t.budget_version_id = budv.budget_version_id
109 AND t.ledger_id = sob.set_of_books_id
110 AND t.code_combination_id = glv.code_combination_id
111 AND t.fin_category_id IS NOT NULL
112 AND fin.fin_category_id (+) = t.fin_category_id
113 AND fin.fin_category_id IS NULL
114 GROUP BY glv.concatenated_segments,
115 sob.name,
116 budv.budget_name;
117 */
118 /*
119 --- Warn user of accounts not in fii_gl_ccid_dimensions (dimensions not mapped correctly)
120 --------------------------------------------------------------------------------------------
121 CURSOR miss_ccid_cur IS
122 SELECT glv.concatenated_segments,
123 sob.name ledger_name,
124 budv.budget_name
125 FROM fii_gl_budget_extract_t t,
126 gl_budget_versions budv,
127 gl_sets_of_books sob,
128 gl_code_combinations_kfv glv,
129 fii_gl_ccid_dimensions ccid
130 WHERE t.budget_version_id = budv.budget_version_id
131 AND t.ledger_id = sob.set_of_books_id
132 AND t.code_combination_id = glv.code_combination_id
133 AND ccid.code_combination_id (+) = t.code_combination_id
134 AND ccid.code_combination_id IS NULL
135 GROUP BY glv.concatenated_segments,
136 sob.name,
137 budv.budget_name;
138 */
139
140 --- Warn user of periods not translated to primary currency ---------------------------
141 -----------------------------------------------------------------------------------------
142 --The missing GL periods are found as the difference between records in the FDS
143 --assignments table (fii_slg_budget_asgns) and the temp table (FII_GL_BUDGET_EXTRACT_T)
144 -----------------------------------------------------------------------------------------
145 CURSOR miss_per_cur_prim IS
146 SELECT /*+ use_nl (setup) use_nl(sob) use_nl(per) use_nl(bud) */
147 sob.name ledger_name, bud.budget_name, per.period_name
148 from fii_slg_budget_asgns setup,
149 gl_periods per,
150 gl_ledgers_public_v sob,
151 gl_budgets_v bud
152 where setup.budget_version_id = bud.budget_version_id
153 and sob.ledger_id = setup.ledger_id
154 and sob.period_set_name = per.period_set_name
155 and sob.accounted_period_type = per.period_type
156 and per.start_date >= setup.from_period_start_date
157 and per.end_date <= setup.to_period_end_date
158 and per.period_num between bud.first_valid_period_num and bud.last_valid_period_num
159 --the last condition serves to avoid pulling in adjusting periods that match the end date of FDS budgets but are not defined in GL as part of the budgets
160 MINUS
161 SELECT /*+ use_hash(budv, t) parallel(t) */
162 sob.name ledger_name, budv.budget_name, t.period_name
163 from fii_gl_budget_extract_t t, gl_budget_versions budv,
164 gl_ledgers_public_v sob
165 where t.budget_version_id = budv.budget_version_id
166 and t.ledger_id = sob.ledger_id
167 and t.currency_code = g_prim_curr;
168
169
170
171 --- Warn user of periods not translated to secondary currency ---------------------------
172 -----------------------------------------------------------------------------------------
173 --The missing GL periods are found as the difference between records in the FDS
174 --assignments table (fii_slg_budget_asgns) and the temp table (FII_GL_BUDGET_EXTRACT_T)
175 -----------------------------------------------------------------------------------------
176 CURSOR miss_per_cur_sec IS
177 SELECT /*+ use_nl (setup) use_nl(sob) use_nl(per) use_nl(bud) */
178 sob.name ledger_name, bud.budget_name, per.period_name
179 from fii_slg_budget_asgns setup,
180 gl_periods per,
181 gl_ledgers_public_v sob,
182 gl_budgets_v bud
183 where setup.budget_version_id = bud.budget_version_id
184 and sob.ledger_id = setup.ledger_id
185 and sob.period_set_name = per.period_set_name
186 and sob.accounted_period_type = per.period_type
187 and per.start_date >= setup.from_period_start_date
188 and per.end_date <= setup.to_period_end_date
189 and per.period_num between bud.first_valid_period_num and bud.last_valid_period_num
190 --the last condition serves to avoid pulling in adjusting periods that match the end date of FDS budgets but are not defined in GL as part of the budgets
191 MINUS
192 SELECT /*+ use_hash(budv, t) parallel(t) */
193 sob.name ledger_name, budv.budget_name, t.period_name
194 from fii_gl_budget_extract_t t, gl_budget_versions budv,
195 gl_ledgers_public_v sob
196 where t.budget_version_id = budv.budget_version_id
197 and t.ledger_id = sob.ledger_id
198 and t.currency_code = g_sec_curr;
199
200 --------------------------------------------------------------------------------
201 -- Warn user of names of any duplicate budgets that have been aggregated -------
202 -- Duplicate records are found by the count for same ccid/period combination----
203 --------------------------------------------------------------------------------
204 CURSOR dup_cur IS
205 SELECT /*+ ordered use_hash(sob,budv,glv) parallel(glv) parallel(t) parallel(sob) pq_distribute(sob hash,hash) pq_distribute(budv hash,hash) pq_distribute(glv hash,hash) parallel(budv) parallel(v) */
206 glv.concatenated_segments,
207 sob.name ledger_name,
208 budv.budget_name
209 FROM FII_GL_BUDGET_EXTRACT_T t,
210 GL_LEDGERS_PUBLIC_V sob,
211 GL_BUDGET_VERSIONS budv,
212 gl_code_combinations_kfv glv,
213 (select /*+ use_hash(fin,t,ccid) parallel(t) parallel(ccid) parallel(fin) pq_distribute(fin hash,hash) pq_distribute(ccid hash,hash)*/
214 t.code_combination_id, period_name
215 from FII_GL_BUDGET_EXTRACT_T t,
216 fii_gl_ccid_dimensions ccid,
217 fii_fin_cat_type_assgns fin
218 where ccid.code_combination_id = t.code_combination_id
219 and fin.fin_category_id = t.fin_category_id
220 and fin.fin_cat_type_code IN ('EXP','R')
221 group by t.plan_type_code_flag,
222 t.period_type_id,
223 t.period_name,
224 t.code_combination_id,
225 t.currency_code
226 having COUNT(t.code_combination_id)>1) v
227 WHERE v.code_combination_id = t.code_combination_id
228 AND v.period_name = t.period_name
229 AND glv.code_combination_id = t.code_combination_id
230 AND t.ledger_id = sob.ledger_id
231 AND t.budget_version_id = budv.budget_version_id
232 GROUP BY glv.concatenated_segments,
233 sob.name,
234 budv.budget_name;
235
236 --miss_fin_type_rec miss_fin_type_cur%ROWTYPE;
237 --miss_ccid_rec miss_ccid_cur%ROWTYPE;
238 miss_per_rec_prim miss_per_cur_prim%ROWTYPE;
239 miss_per_rec_sec miss_per_cur_sec%ROWTYPE;
240 dup_rec dup_cur%ROWTYPE;
241
242 BEGIN
243
244 --Added the following for generating a complete trace file
245 execute immediate ('alter session set max_dump_file_size=''unlimited'' events=''10046 trace name context forever, level 8''');
246
247 g_phase := 'Start of main routine in FII_GL_BUDGET_EXTRACTION.';
248 retcode := 'S';
249
250 if g_debug_flag = 'Y' then
251 FII_UTIL.write_log('');
252 FII_UTIL.write_log(g_phase);
253 end if;
254
255 ------------------------
256 -- Enable DBMS_OUTPUT --
257 ------------------------
258 --DBMS_OUTPUT.enable;
259
260 ------------------------------------------------
261 -- Initialize other setups
262 ------------------------------------------------
263 g_phase := 'Calling INIT';
264 INIT(retcode);
265
266
267 ----- FII Budget Source Profile Option must be set to 'GL'
268 ---------------------------------------------------------
269 g_phase := 'Checking the FII Budget Source Profile Option.';
270 if g_debug_flag = 'Y' then
271 FII_UTIL.write_log(g_phase);
272 end if;
273
274 IF FND_PROFILE.value('FII_BUDGET_SOURCE') <> 'GL' THEN
275 FII_MESSAGE.Write_Log
276 (msg_name => 'FII_BUD_SOURCE_PROFILE_E',
277 token_num => 0);
278 RAISE FIIBUDX_fatal_err;
279 END IF;
280
281
282 -------------------------------------------------------------
283 --- Truncate temp table -------------------------------------
284 -------------------------------------------------------------
285 g_phase := 'Truncating temp table FII_GL_BUDGET_EXTRACT_T.';
286 if g_debug_flag = 'Y' then
287 FII_UTIL.write_log(g_phase);
288 end if;
289
290 fii_util.truncate_table('FII_GL_BUDGET_EXTRACT_T', 'FII', l_retcode);
291 IF l_retcode = -1 then
292 fii_util.write_log('Error in fii_util.truncate_table(''FII_GL_BUDGET_EXTRACT_T'', ''FII'', l_retcode)');
293 raise FIIBUDX_fatal_err;
294 END IF;
295
296 ----------------------------------------------------
297 -- Populate CCC - Mgr mappings temp. table
298 -----------------------------------------------------
299 g_phase := 'Call program that populates CCC - Mgr mappings temp. table.';
300 FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR (l_status);
301
302 IF l_status = -1 then
303 fii_util.write_log('Error in FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR ...');
304 fii_util.write_log('Table FII_CCC_MGR_GT is not populated');
305 raise NO_DATA_FOUND;
306 END IF;
307
308
309 -------------------------------------------------------------
310 --- Extracting budgets from gl_balances into temp table -----
311 -------------------------------------------------------------
312 g_phase := 'Extract budgets from gl_balances into temp table FII_GL_BUDGET_EXTRACT_T';
313
314 if g_debug_flag = 'Y' then
315 FII_UTIL.write_log('');
316 FII_UTIL.write_log(g_phase);
317 FII_UTIL.start_timer;
318 end if;
319
320 INSERT /*+ append parallel(t) */ INTO FII_GL_BUDGET_EXTRACT_T t
321 (plan_type_code_flag,
322 time_id,
323 period_type_id,
324 period_name,
325 ledger_id,
326 budget_version_id,
327 prim_amount_g,
328 sec_amount_g,
329 code_combination_id,
330 company_id,
331 cost_center_id,
332 company_cost_center_org_id,
333 fin_category_id,
334 category_id,
335 user_dim1_id,
336 user_dim2_id,
337 currency_code,
338 last_update_date,
339 last_updated_by,
340 creation_date,
341 created_by,
342 last_update_login)
343 SELECT /*+ use_hash(glper,sob, slga, fincat,ccid)
344 parallel(ccid) parallel(cccorg) parallel(slga) parallel(glper) pq_distribute(glper hash,hash)
345 parallel(sob) parallel(fincat) pq_distribute(cccorg hash,hash) pq_distribute(slga hash,hash) */
346
347 slga.plan_type_code,
348 null, --time_id
349 32,
350 glper.period_name,
351 slga.ledger_id,
352 slga.budget_version_id,
353 DECODE(bal.currency_code,
354 g_prim_curr, DECODE(fincat.fin_cat_type_code,
355 'R', SUM(bal.period_net_cr - bal.period_net_dr),
356 'EXP', -SUM(bal.period_net_cr - bal.period_net_dr),
357 0),
358 g_sec_curr, 0) prim_amount,
359 DECODE(bal.currency_code,
360 g_prim_curr, 0,
361 g_sec_curr, DECODE(fincat.fin_cat_type_code,
362 'R', SUM(bal.period_net_cr - bal.period_net_dr),
363 'EXP', -SUM(bal.period_net_cr - bal.period_net_dr),
364 0)) sec_amount,
365 bal.code_combination_id,
366 ccid.company_id,
367 ccid.cost_center_id,
368 NVL(cccorg.ccc_org_id, -1) ccc_org_id,
369 ccid.natural_account_id,
373 bal.currency_code,
370 NVL(ccid.prod_category_id, -1) prod_category_id,
371 NVL(ccid.user_dim1_id, g_unassigned_id) user_dim1_id,
372 NVL(ccid.user_dim2_id, g_unassigned_id) user_dim2_id,
374 sysdate,
375 g_fii_user_id,
376 sysdate,
377 g_fii_user_id,
378 g_fii_user_id
379 FROM GL_BALANCES bal,
380 FII_GL_CCID_DIMENSIONS ccid,
381 FII_CCC_MGR_GT cccorg,
382 FII_SLG_BUDGET_ASGNS slga,
383 GL_PERIODS glper,
384 GL_LEDGERS_PUBLIC_V sob,
385 FII_FIN_CAT_TYPE_ASSGNS fincat
386 WHERE fincat.fin_category_id (+) = ccid.natural_account_id
387 AND (fincat.fin_cat_type_code IN ('EXP','R') OR fincat.fin_cat_type_code IS NULL) -- if NULL then amounts will be both 0
388 AND bal.code_combination_id = ccid.code_combination_id (+)
389 AND bal.ledger_id = slga.ledger_id
390 AND bal.budget_version_id = slga.budget_version_id
391 AND bal.period_type = glper.period_type
392 AND bal.period_name = glper.period_name
393 AND glper.period_set_name = sob.period_set_name
394 AND sob.ledger_id = slga.ledger_id
395 AND glper.start_date >= slga.from_period_start_date
396 AND glper.end_date <= slga.to_period_end_date
397 AND bal.currency_code IN (g_prim_curr, g_sec_curr)
398 AND bal.actual_flag = 'B'
399 AND cccorg.company_id (+) = ccid.company_id
400 AND cccorg.cost_center_id (+) = ccid.cost_center_id
401 GROUP BY slga.plan_type_code,
402 slga.ledger_id,
403 slga.budget_version_id,
404 fincat.fin_cat_type_code,
405 glper.period_name,
406 bal.code_combination_id,
407 ccid.company_id,
408 ccid.cost_center_id,
409 NVL(cccorg.ccc_org_id, -1),
410 ccid.natural_account_id,
411 NVL(ccid.prod_category_id, -1),
412 NVL(ccid.user_dim1_id, g_unassigned_id),
413 NVL(ccid.user_dim2_id, g_unassigned_id),
414 bal.currency_code,
415 glper.adjustment_period_flag;
416
417 /* l_row_count := SQL%ROWCOUNT; */
418 /* DBMS_OUTPUT.put_line('Inserted ' || TO_CHAR(l_row_count) || ' rows in FII_GL_BUDGET_EXTRACT_T.'); */
419 /* l_row_count := 0; */
420 if g_debug_flag = 'Y' then
421 FII_UTIL.write_log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_BUDGET_EXTRACT_T.');
422 FII_UTIL.stop_timer;
423 FII_UTIL.print_timer('Duration');
424 end if;
425
426 commit;
427
428
429 --------------------------------------------------------
430 --- Warning messages -----------------------------------
431 --------------------------------------------------------
432
433 -------------------------------------
434 --Display Primary Curreny Warning
435 -------------------------------------
436 g_phase := 'Warn user of periods not translated to primary currency.';
437
438 if g_debug_flag = 'Y' then
439 FII_UTIL.write_log('');
440 FII_UTIL.write_log(g_phase);
441 end if;
442
443 OPEN miss_per_cur_prim;
444 FETCH miss_per_cur_prim INTO miss_per_rec_prim;
445
446 If (miss_per_cur_prim%ROWCOUNT > 0) THEN
447
448 FII_UTIL.write_output('---------------------------------------------------------------------');
449 FII_MESSAGE.write_output(msg_name => 'FII_CUR1_TRANS_REC',
450 token_num => 0);
451 FII_UTIL.write_output('('||miss_per_rec_prim.ledger_name||
452 ', '||miss_per_rec_prim.budget_name||
453 ', '||miss_per_rec_prim.period_name||')');
454
455 /* DBMS_OUTPUT.put_line('---------------------------------------------------------------------'); */
456 /* DBMS_OUTPUT.put_line('Following is a list of budgets with periods that are not available in the primary global currency in GL:'); */
457 /* DBMS_OUTPUT.PUT_LINE('Ledger: '||miss_per_rec_prim.ledger_name|| */
458 /* ', Budget: '||miss_per_rec_prim.budget_name|| */
459 /* ', Period: '||miss_per_rec_prim.period_name); */
460
461
462 LOOP
463 FETCH miss_per_cur_prim INTO miss_per_rec_prim;
464 EXIT WHEN miss_per_cur_prim%NOTFOUND;
465 FII_UTIL.write_output('('||miss_per_rec_prim.ledger_name||
466 ', '||miss_per_rec_prim.budget_name||
467 ', '||miss_per_rec_prim.period_name||')');
468
469 /* DBMS_OUTPUT.PUT_LINE('Ledger: '||miss_per_rec_prim.ledger_name|| */
470 /* ', Budget: '||miss_per_rec_prim.budget_name|| */
471 /* ', Period: '||miss_per_rec_prim.period_name); */
472 END LOOP;
473 FII_UTIL.write_output('---------------------------------------------------------------------');
474 /* DBMS_OUTPUT.put_line('---------------------------------------------------------------------'); */
475
476 retcode := 'W';
477 END IF;
478 CLOSE miss_per_cur_prim;
479
480
481 -------------------------------------
482 --Display Secondary Curreny Warning
483 -------------------------------------
484 g_phase := 'Warn user of periods not translated to secondary currency.';
485
486 if g_debug_flag = 'Y' then
487 FII_UTIL.write_log('');
488 FII_UTIL.write_log(g_phase);
489 end if;
490
491 -- Bugfix 6053573
492
493 IF g_sec_curr IS NOT NULL THEN
494
495 OPEN miss_per_cur_sec;
496 FETCH miss_per_cur_sec INTO miss_per_rec_sec;
497
498 If (miss_per_cur_sec%ROWCOUNT > 0) THEN
499
500 FII_UTIL.write_output('---------------------------------------------------------------------');
501 FII_MESSAGE.write_output(msg_name => 'FII_CUR2_TRANS_REC',
502 token_num => 0);
503 FII_UTIL.write_output('('||miss_per_rec_sec.ledger_name||
504 ', '||miss_per_rec_sec.budget_name||
505 ', '||miss_per_rec_sec.period_name||')');
506
507 /* DBMS_OUTPUT.put_line('---------------------------------------------------------------------'); */
508 /* DBMS_OUTPUT.put_line('Following is a list of budgets with periods that are not available in the secondary global currency in GL:'); */
509 /* DBMS_OUTPUT.PUT_LINE('Ledger: '||miss_per_rec_sec.ledger_name|| */
510 /* ', Budget: '||miss_per_rec_sec.budget_name|| */
511 /* ', Period: '||miss_per_rec_sec.period_name); */
512
513
514 LOOP
515 FETCH miss_per_cur_sec INTO miss_per_rec_sec;
516 EXIT WHEN miss_per_cur_sec%NOTFOUND;
517 FII_UTIL.write_output('('||miss_per_rec_sec.ledger_name||
518 ', '||miss_per_rec_sec.budget_name||
519 ', '||miss_per_rec_sec.period_name||')');
520 /* DBMS_OUTPUT.PUT_LINE('Ledger: '||miss_per_rec_sec.ledger_name|| */
521 /* ', Budget: '||miss_per_rec_sec.budget_name|| */
522 /* ', Period: '||miss_per_rec_sec.period_name); */
523 END LOOP;
524 FII_UTIL.write_output('---------------------------------------------------------------------');
525 /* DBMS_OUTPUT.put_line('---------------------------------------------------------------------'); */
526
527 retcode := 'W';
528 END IF;
529 CLOSE miss_per_cur_sec;
530 END IF;
531
532 -------------------------------------
533 -- Display Duplicates Warning
534 -------------------------------------
535 g_phase := 'Warn user of names of any duplicate budgets that have been aggregated.';
536
537 if g_debug_flag = 'Y' then
538 FII_UTIL.write_log('');
539 FII_UTIL.write_log(g_phase);
540 end if;
541 OPEN dup_cur;
542 FETCH dup_cur INTO dup_rec;
543
544 If (dup_cur%ROWCOUNT > 0) THEN
545
546 FII_UTIL.write_output('---------------------------------------------------------------------');
547 FII_MESSAGE.write_output(msg_name => 'FII_DUP_REC_AGG_REC',
548 token_num => 0);
549 FII_UTIL.write_output('('||dup_rec.concatenated_segments||
550 ', '||dup_rec.ledger_name||
551 ', '||dup_rec.budget_name||')');
552
553 /* DBMS_OUTPUT.put_line('Following is a list of budgets with duplicate code combinations and time periods: */
554 /* The budgets for these periods have been summed up.' ); */
555 /* DBMS_OUTPUT.PUT_LINE('Ledger: '||dup_rec.ledger_name|| */
556 /* ', Budget: '||dup_rec.budget_name); */
557
558
559 LOOP
560 FETCH dup_cur INTO dup_rec;
561 EXIT WHEN dup_cur%NOTFOUND;
562 FII_UTIL.write_output('('||dup_rec.concatenated_segments||
563 ', '||dup_rec.ledger_name||
564 ', '||dup_rec.budget_name||')');
565 /* DBMS_OUTPUT.PUT_LINE('Ledger: '||dup_rec.ledger_name|| */
566 /* ', Budget: '||dup_rec.budget_name); */
567 END LOOP;
568 FII_UTIL.write_output('---------------------------------------------------------------------');
569 /* DBMS_OUTPUT.put_line('---------------------------------------------------------------------------------'); */
570
571 retcode := 'W';
572 END IF;
573 CLOSE dup_cur;
574
575
576
577 ----------------------------------------------------------------------------
578 --- Inserting into temp table second time (after the warning cursors) for the following reasons:
579 --- - insert fii time_id
580 -- - add adjusting periods into non-adjusting period based on end date
581 -- - remove budgets with 0 amounts (this also takes care of discarding
582 -- records that don't have a financial category type or ccid)
583 -- - remove code_combination_id from group by
587 if g_debug_flag = 'Y' then
584 ----------------------------------------------------------------------------
585 g_phase := 'Inserting second time into temp table FII_GL_BUDGET_EXTRACT_T.';
586
588 FII_UTIL.write_log('');
589 FII_UTIL.write_log(g_phase);
590 FII_UTIL.start_timer;
591 end if;
592
593 INSERT /*+ append parallel(t) */ INTO FII_GL_BUDGET_EXTRACT_T t
594 (plan_type_code_flag,
595 time_id,
596 period_type_id,
597 ledger_id,
598 prim_amount_g,
599 sec_amount_g,
600 company_id,
601 cost_center_id,
602 company_cost_center_org_id,
603 fin_category_id,
604 category_id,
605 user_dim1_id,
606 user_dim2_id,
607 last_update_date,
608 last_updated_by,
609 creation_date,
610 created_by,
611 last_update_login)
612 SELECT /*+ parallel(t) parallel(period) parallel(GLPER) parallel(SOB) pq_distribute(glper hash,hash) */
613 plan_type_code_flag,
614 period.ent_period_id time_id,
615 32,
616 t.ledger_id,
617 SUM(prim_amount_g),
618 SUM(sec_amount_g),
619 company_id,
620 cost_center_id,
621 company_cost_center_org_id,
622 fin_category_id,
623 category_id,
624 user_dim1_id,
625 user_dim2_id,
626 sysdate,
627 g_fii_user_id,
628 sysdate,
629 g_fii_user_id,
630 g_fii_login_id
631 FROM FII_GL_BUDGET_EXTRACT_T t,
632 FII_TIME_ENT_PERIOD period,
633 GL_PERIODS glper,
634 GL_LEDGERS_PUBLIC_V sob,
635 fii_slg_budget_asgns setup
636 WHERE t.period_name = glper.period_name
637 ANd sob.ledger_id = t.ledger_id
638 AND sob.period_set_name = glper.period_set_name
639 AND sob.accounted_period_type = glper.period_type
640 AND ((glper.adjustment_period_flag = 'N' and glper.start_date = period.start_date)
641 OR
642 (glper.adjustment_period_flag = 'Y'))
643 AND ((glper.adjustment_period_flag = 'N' and glper.end_date = period.end_date)
644 OR
645 (glper.adjustment_period_flag = 'Y' and glper.end_date between period.start_date and period.end_date))
646 AND setup.ledger_id = t.ledger_id
647 AND setup.budget_version_id = t.budget_version_id
648 AND setup.plan_type_code = t.plan_type_code_flag
649 AND period.start_date >= setup.from_period_start_date
650 AND period.end_date <= setup.to_period_end_date
651 AND ((prim_amount_g <> 0 and sec_amount_g = 0) --to remove budget amounts that are 0
652 OR (prim_amount_g = 0 and sec_amount_g <> 0))
653 GROUP BY plan_type_code_flag,
654 t.ledger_id,
655 company_id,
656 cost_center_id,
657 company_cost_center_org_id,
658 fin_category_id,
659 category_id,
660 user_dim1_id,
661 user_dim2_id,
662 period.ent_period_id;
663
664 --If posted_date is to be populated from
665 --the temp table like the rest of the
666 --columns, then it should be added to
667 --the group by condition since it is part of index FII_BUDGET_BASE_U1.
668 --It will also have to be added to the delete statement.
669
670
671 if g_debug_flag = 'Y' then
672 FII_UTIL.write_log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_BUDGET_EXTRACT_T.');
673 FII_UTIL.stop_timer;
674 FII_UTIL.print_timer('Duration');
675 end if;
676
677 commit;
678
679 -------------------------------------------------------------
680 --- Rolling Up on time in temp table ------------------------
681 -------------------------------------------------------------
682 g_phase := 'Rolling Up on time in temp table FII_GL_BUDGET_EXTRACT_T.';
683
684 if g_debug_flag = 'Y' then
685 FII_UTIL.write_log('');
686 FII_UTIL.write_log(g_phase);
687 FII_UTIL.start_timer;
688 end if;
689
690 INSERT /*+ append parallel(a) */ INTO FII_GL_BUDGET_EXTRACT_T a
691 (plan_type_code_flag,
692 time_id,
693 period_type_id,
694 ledger_id,
695 prim_amount_g,
696 sec_amount_g,
697 company_id,
698 cost_center_id,
699 company_cost_center_org_id,
700 fin_category_id,
701 user_dim1_id,
702 user_dim2_id,
703 category_id,
704 last_update_date,
705 last_updated_by,
706 creation_date,
707 created_by,
708 last_update_login)
709 SELECT /*+ parallel(temp) parallel(period) pq_distribute(temp hash,hash) */
710 temp.plan_type_code_flag,
711 NVL(period.ent_qtr_id, period.ent_year_id) time_id,
712 DECODE(period.ent_qtr_id, NULL, 128, 64) period_type_id,
713 temp.ledger_id,
714 SUM(temp.prim_amount_g) prim_amount_g,
715 SUM(temp.sec_amount_g) sec_amount_g,
716 temp.company_id,
717 temp.cost_center_id,
718 company_cost_center_org_id,
719 temp.fin_category_id,
720 temp.user_dim1_id,
721 temp.user_dim2_id,
722 temp.category_id,
723 sysdate,
724 g_fii_user_id,
725 sysdate,
726 g_fii_user_id,
727 g_fii_login_id
728 FROM FII_GL_BUDGET_EXTRACT_T temp,
729 FII_TIME_ENT_PERIOD period
730 WHERE temp.time_id = period.ent_period_id
731 GROUP BY
732 plan_type_code_flag,
733 ledger_id,
734 company_id,
735 cost_center_id,
736 company_cost_center_org_id,
737 fin_category_id,
738 user_dim1_id,
739 user_dim2_id,
740 category_id,
741 ROLLUP (period.ent_year_id,
742 period.ent_qtr_id)
743 HAVING period.ent_year_id is not null;
744
745 if g_debug_flag = 'Y' then
746 FII_UTIL.write_log('Inserted ' || SQL%ROWCOUNT || ' Roll Up rows in FII_GL_BUDGET_EXTRACT_T on time.');
747 FII_UTIL.stop_timer;
748 FII_UTIL.print_timer('Duration');
749 end if;
750 /* l_row_count := SQL%ROWCOUNT; */
751 /* DBMS_OUTPUT.put_line('Rolled Up ' || TO_CHAR(l_row_count) || ' rows in FII_GL_BUDGET_EXTRACT_T on time.'); */
752 /* l_row_count := 0; */
753
754 commit;
755
756
757 --- Deleting from fii_budget_base by diffing with temp table -------------
758 --------------------------------------------------------------------------
759 g_phase := 'Deleting from fii_budget_base by diffing with temp table.';
760
761 if g_debug_flag = 'Y' then
762 FII_UTIL.write_log('');
763 FII_UTIL.put_line(g_phase);
764 FII_UTIL.start_timer;
765 end if;
766
767 DELETE FROM fii_budget_base
768 WHERE (time_id,
769 period_type_id,
770 ledger_id,
771 company_id,
772 cost_center_id,
773 NVL(company_cost_center_org_id,-1),
774 fin_category_id,
775 NVL(category_id, -1),
776 user_dim1_id,
777 user_dim2_id,
778 plan_type_code,
779 prim_amount_g,
780 sec_amount_g,
781 prim_amount_total,
782 sec_amount_total)
783 IN
784 (SELECT time_id,
785 period_type_id,
786 ledger_id,
787 company_id,
788 cost_center_id,
789 NVL(company_cost_center_org_id,-1),
790 fin_category_id,
791 NVL(category_id, -1),
792 user_dim1_id,
793 user_dim2_id,
794 plan_type_code,
795 prim_amount_g,
796 sec_amount_g,
797 prim_amount_total,
798 sec_amount_total
799 FROM fii_budget_base
800 MINUS
801 SELECT time_id,
802 period_type_id,
803 ledger_id,
804 company_id,
805 cost_center_id,
806 NVL(company_cost_center_org_id, -1),
807 fin_category_id,
808 NVL(category_id, -1),
809 user_dim1_id,
810 user_dim2_id,
811 plan_type_code_flag,
812 prim_amount_g,
813 sec_amount_g,
814 prim_amount_g,
815 sec_amount_g
816 FROM FII_GL_BUDGET_EXTRACT_T
817 WHERE time_id is not null);
818
819
820 if g_debug_flag = 'Y' then
821 FII_UTIL.write_log('Deleted ' || SQL%ROWCOUNT || ' rows from fii_budget_base.');
822 FII_UTIL.stop_timer;
823 FII_UTIL.print_timer('Duration');
824 end if;
825 /* l_row_count := SQL%ROWCOUNT; */
826 /* DBMS_OUTPUT.put_line('Deleted ' || TO_CHAR(l_row_count) || ' rows from fii_budget_base.'); */
827 /* l_row_count := 0; */
828
829
830 --- Insert contents of temp table into fii_budget_base --------------------
831 --------------------------------------------------------------------------
832 g_phase := 'Insert contents of temp table into fii_budget_base.';
833
834 if g_debug_flag = 'Y' then
835 FII_UTIL.write_log('');
836 FII_UTIL.write_log(g_phase);
837 FII_UTIL.start_timer;
838 end if;
839
840
841 INSERT /*+ append parallel(f) */ INTO fii_budget_base f
842 (plan_type_code,
843 time_id,
844 period_type_id,
845 ledger_id,
846 prim_amount_g,
847 sec_amount_g,
848 prim_amount_total,
849 sec_amount_total,
850 creation_date,
851 created_by,
852 last_update_date,
853 last_updated_by,
854 last_update_login,
855 category_id,
856 user_dim1_id,
857 user_dim2_id,
858 fin_category_id,
859 company_id,
860 cost_center_id,
861 company_cost_center_org_id,
862 posted_date)
863 (SELECT /*+ parallel(t) */ plan_type_code_flag,
864 time_id,
865 period_type_id,
866 ledger_id,
867 prim_amount_g,
868 sec_amount_g,
869 prim_amount_g,
870 sec_amount_g,
871 sysdate,
872 g_fii_user_id,
873 sysdate,
874 g_fii_user_id,
875 g_fii_login_id,
876 category_id,
877 user_dim1_id,
878 user_dim2_id,
879 fin_category_id,
880 company_id,
881 cost_center_id,
882 company_cost_center_org_id,
883 g_global_start_date
884 FROM FII_GL_BUDGET_EXTRACT_T t
885 WHERE t.time_id is not null
886 AND NOT EXISTS (SELECT /*+ parallel(b) */ time_id,
887 period_type_id,
888 ledger_id,
889 company_id,
890 cost_center_id,
891 NVL(company_cost_center_org_id,-1),
892 fin_category_id,
893 NVL(category_id, -1),
894 user_dim1_id,
895 user_dim2_id,
896 plan_type_code,
897 prim_amount_g,
898 sec_amount_g,
899 prim_amount_total,
900 sec_amount_total
901 FROM fii_budget_base b
902 WHERE time_id = t.time_id
903 and period_type_id = t.period_type_id
904 and ledger_id = t.ledger_id
905 and company_id = t.company_id
906 and cost_center_id = t.cost_center_id
907 and NVL(company_cost_center_org_id,-1) = NVL(t.company_cost_center_org_id,-1)
908 and fin_category_id = t.fin_category_id
909 and NVL(category_id, -1) = NVL(t.category_id, -1)
910 and user_dim1_id = t.user_dim1_id
911 and user_dim2_id = t.user_dim2_id
912 and plan_type_code = t.plan_type_code_flag
913 and prim_amount_g = t.prim_amount_g
914 and sec_amount_g = t.sec_amount_g
915 and prim_amount_total = t.prim_amount_g
916 and sec_amount_total = t.sec_amount_g));
917
918
919 if g_debug_flag = 'Y' then
920 FII_UTIL.write_log('Inserted ' || SQL%ROWCOUNT || ' rows into fii_budget_base.');
921 FII_UTIL.stop_timer;
922 FII_UTIL.print_timer('Duration');
923 end if;
924 /* l_row_count := SQL%ROWCOUNT; */
925 /* DBMS_OUTPUT.put_line('Inserted ' || TO_CHAR(l_row_count) || ' rows into fii_budget_base.'); */
926 /* l_row_count := 0; */
927
928 --
929 -- Commit all changes
930 --
931 COMMIT;
932
933 EXCEPTION
934 WHEN FIIBUDX_fatal_err THEN
935 retcode := 'E';
936 Rollback;
937 FII_UTIL.write_log('Fatal errors occured during the upload process.');
938 /* DBMS_OUTPUT.put_line('Fatal errors occured during the upload process.'); */
939
940 WHEN OTHERS Then
941 retcode := 'E';
942 Rollback;
943
944 FII_UTIL.write_output('
945 Error in Function: Main
946 Phase: '|| g_phase || '
947 Message: ' || sqlerrm);
948
949 /* DBMS_OUTPUT.put_line(' */
950 /* Error in Function: Main */
951 /* Phase: '|| g_phase || ' */
952 /* Message: ' || sqlerrm); */
953 -------------------------------------------------------------------------------
954 END;
955
956 END FII_GL_BUDGET_EXTRACTION;