[Home] [Help]
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
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
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
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;
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
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;
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)*/
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
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:
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;
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: ----------------------------------------------------
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);
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,
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;
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:
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
584: ----------------------------------------------------------------------------
585: g_phase := 'Inserting second time into temp table FII_GL_BUDGET_EXTRACT_T.';
586:
587: if g_debug_flag = 'Y' then
588: FII_UTIL.write_log('');
589: FII_UTIL.write_log(g_phase);
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,
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
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:
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);
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,
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,
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; */
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:
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
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,