[Home] [Help]
103:
104: BEGIN
105:
106: if g_debug_flag = 'Y' then
107: FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
108: FII_UTIL.put_line('');
109: end if;
110:
111: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
104: BEGIN
105:
106: if g_debug_flag = 'Y' then
107: FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
108: FII_UTIL.put_line('');
109: end if;
110:
111: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
112: if g_debug_flag = 'Y' then
109: end if;
110:
111: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
112: if g_debug_flag = 'Y' then
113: FII_UTIL.put_line(g_phase);
114: FII_UTIL.put_line('');
115: end if;
116:
117: BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
110:
111: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
112: if g_debug_flag = 'Y' then
113: FII_UTIL.put_line(g_phase);
114: FII_UTIL.put_line('');
115: end if;
116:
117: BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
118:
117: BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
118:
119: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
120: if g_debug_flag = 'Y' then
121: FII_UTIL.put_line(g_phase);
122: FII_UTIL.put_line('');
123: end if;
124:
125: /*
118:
119: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
120: if g_debug_flag = 'Y' then
121: FII_UTIL.put_line(g_phase);
122: FII_UTIL.put_line('');
123: end if;
124:
125: /*
126: FOR rate_record in PrimMissingRate LOOP
163:
164: EXCEPTION
165: WHEN NO_DATA_FOUND THEN
166: g_retcode:=-1;
167: FII_UTIL.put_line('
168: ---------------------------------------------------
169: Error in Procedure: REPORT_MISSING_RATES
170: Phase: '||g_phase||'
171: Message: Should have missing rates but found none');
171: Message: Should have missing rates but found none');
172: raise;
173: WHEN OTHERS THEN
174: g_retcode := -1;
175: FII_UTIL.put_line('
176: ---------------------------------
177: Error in Procedure: REPORT_MISSING_RATES
178: Phase: '||g_phase||'
179: Message: '||sqlerrm);
212:
213: BEGIN
214:
215: if g_debug_flag = 'Y' then
216: FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
217: FII_UTIL.put_line('');
218: end if;
219:
220: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
213: BEGIN
214:
215: if g_debug_flag = 'Y' then
216: FII_UTIL.put_line('Calling procedure: REPORT_MISSING_RATES');
217: FII_UTIL.put_line('');
218: end if;
219:
220: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
221: if g_debug_flag = 'Y' then
218: end if;
219:
220: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
221: if g_debug_flag = 'Y' then
222: FII_UTIL.put_line(g_phase);
223: FII_UTIL.put_line('');
224: end if;
225:
226: BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
219:
220: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRateHeader to write out report header';
221: if g_debug_flag = 'Y' then
222: FII_UTIL.put_line(g_phase);
223: FII_UTIL.put_line('');
224: end if;
225:
226: BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
227:
226: BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
227:
228: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
229: if g_debug_flag = 'Y' then
230: FII_UTIL.put_line(g_phase);
231: FII_UTIL.put_line('');
232: end if;
233:
234: FOR rate_record in PrimMissingRate LOOP
227:
228: g_phase := 'Calling BIS_COLLECTION_UTILITIES.WriteMissingRate to write out report contents';
229: if g_debug_flag = 'Y' then
230: FII_UTIL.put_line(g_phase);
231: FII_UTIL.put_line('');
232: end if;
233:
234: FOR rate_record in PrimMissingRate LOOP
235: BIS_COLLECTION_UTILITIES.writemissingrate(
252: EXCEPTION
253: WHEN NO_DATA_FOUND THEN
254: g_retcode:=-1;
255: if g_debug_flag = 'Y' then
256: FII_UTIL.put_line('
257: ---------------------------------------------------
258: Error in Procedure: REPORT_MISSING_RATES_L
259: Phase: '||g_phase||'
260: Message: Should have missing rates but found none');
262: raise;
263: WHEN OTHERS THEN
264: g_retcode := -1;
265: if g_debug_flag = 'Y' then
266: FII_UTIL.put_line('
267: ---------------------------------
268: Error in Procedure: REPORT_MISSING_RATES_L
269: Phase: '||g_phase||'
270: Message: '||sqlerrm);
282:
283: l_stmt:='drop table '||g_fii_schema||'.'|| p_table_name;
284:
285: if g_debug_flag = 'Y' then
286: FII_UTIL.put_line('');
287: FII_UTIL.put_line(l_stmt);
288: end if;
289:
290: execute immediate l_stmt;
283: l_stmt:='drop table '||g_fii_schema||'.'|| p_table_name;
284:
285: if g_debug_flag = 'Y' then
286: FII_UTIL.put_line('');
287: FII_UTIL.put_line(l_stmt);
288: end if;
289:
290: execute immediate l_stmt;
291:
294: NULL; -- Oracle 942, table does not exist, no actions
295: WHEN OTHERS THEN
296: g_retcode := -1;
297: if g_debug_flag = 'Y' then
298: FII_UTIL.put_line('
299: ---------------------------------
300: Error in Procedure: DROP_TABLE
301: Message: '||sqlerrm);
302: end if;
313:
314: l_stmt:='truncate table '||g_fii_schema||'.'|| p_table_name;
315:
316: if g_debug_flag = 'Y' then
317: FII_UTIL.put_line('');
318: FII_UTIL.put_line(l_stmt);
319: end if;
320:
321: execute immediate l_stmt;
314: l_stmt:='truncate table '||g_fii_schema||'.'|| p_table_name;
315:
316: if g_debug_flag = 'Y' then
317: FII_UTIL.put_line('');
318: FII_UTIL.put_line(l_stmt);
319: end if;
320:
321: execute immediate l_stmt;
322:
323: Exception
324: WHEN OTHERS THEN
325: g_retcode := -1;
326: if g_debug_flag = 'Y' then
327: FII_UTIL.put_line('
328: ---------------------------------
329: Error in Procedure: TRUNCATE_TABLE
330: Message: '||sqlerrm);
331: end if;
340:
341: BEGIN
342:
343: if g_debug_flag = 'Y' then
344: FII_UTIL.put_line('Calling procedure: INIT');
345: FII_UTIL.put_line('');
346: end if;
347:
348: -- -------------------------------------------
341: BEGIN
342:
343: if g_debug_flag = 'Y' then
344: FII_UTIL.put_line('Calling procedure: INIT');
345: FII_UTIL.put_line('');
346: end if;
347:
348: -- -------------------------------------------
349: -- Turn on parallel insert/dml for the session
359: ----------------------------------------------------------
360: -- Find the schema owner of FII
361: ----------------------------------------------------------
362: g_phase := 'Find FII schema';
363: g_fii_schema := FII_UTIL.get_schema_name ('FII');
364:
365: --------------------------------------------------------------
366: -- Find all currency related information
367: --------------------------------------------------------------
389: g_sec_rate_type_name := null;
390: end if;
391: exception
392: when others then
393: fii_util.write_log('Failed to convert rate_type to rate_type_name' );
394: raise;
395: end;
396:
397: g_phase := 'Find User ID and User Login';
403: RAISE G_LOGIN_INFO_NOT_AVABLE;
404: END IF;
405:
406: if g_debug_flag = 'Y' then
407: FII_UTIL.put_line('User ID: ' || g_fii_user_id || ' Login ID: ' || g_fii_login_id);
408: end if;
409:
410: EXCEPTION
411: WHEN G_LOGIN_INFO_NOT_AVABLE THEN
409:
410: EXCEPTION
411: WHEN G_LOGIN_INFO_NOT_AVABLE THEN
412: g_retcode := -1;
413: FII_UTIL.put_line('Init: can not get User ID and Login ID, program exits');
414: raise;
415: WHEN OTHERS THEN
416: g_retcode := -1;
417: FII_UTIL.put_line('
413: FII_UTIL.put_line('Init: can not get User ID and Login ID, program exits');
414: raise;
415: WHEN OTHERS THEN
416: g_retcode := -1;
417: FII_UTIL.put_line('
418: ---------------------------------
419: Error in Procedure: INIT
420: Phase: '||g_phase||'
421: Message: '||sqlerrm);
433: BEGIN
434:
435: g_phase := 'Check if Source Legder Assignments setup has changed';
436: if g_debug_flag = 'Y' then
437: FII_UTIL.put_line(g_phase);
438: end if;
439:
440: SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
441: INTO l_slg_chg
489: WHEN NO_DATA_FOUND THEN
490: RETURN 'FALSE';
491: WHEN OTHERS THEN
492: g_retcode := -1;
493: FII_UTIL.put_line('
494: -----------------------------
495: Error occured in Funcation: CHECK_IF_SLG_SET_UP_CHANGE
496: Phase: '||g_phase||'
497: Message: ' || sqlerrm);
508:
509: BEGIN
510: g_phase := 'Check if Product Assignments set up has changed';
511: if g_debug_flag = 'Y' then
512: FII_UTIL.put_line(g_phase);
513: end if;
514:
515: SELECT DECODE(item_value, 'Y', 'TRUE', 'FALSE')
516: INTO l_prd_chg
564: WHEN NO_DATA_FOUND THEN
565: RETURN 'FALSE';
566: WHEN OTHERS THEN
567: g_retcode := -1;
568: FII_UTIL.put_line('
569: -----------------------------
570: Error occured in Funcation: CHECK_IF_PRD_SET_UP_CHANGE
571: Phase: '||g_phase||'
572: Message: ' || sqlerrm);
585:
586: BEGIN
587:
588: if g_debug_flag = 'Y' then
589: FII_UTIL.put_line('Calling procedure: REGISTER_JOBS');
590: FII_UTIL.put_line('');
591: end if;
592:
593: g_phase := 'Register jobs for workers';
586: BEGIN
587:
588: if g_debug_flag = 'Y' then
589: FII_UTIL.put_line('Calling procedure: REGISTER_JOBS');
590: FII_UTIL.put_line('');
591: end if;
592:
593: g_phase := 'Register jobs for workers';
594: if g_debug_flag = 'Y' then
591: end if;
592:
593: g_phase := 'Register jobs for workers';
594: if g_debug_flag = 'Y' then
595: FII_UTIL.put_line('Register jobs for workers');
596: end if;
597:
598: ------------------------------------------------------------
599: -- select min and max sequence IDs from your ID Temp table
613: l_start_number := least(l_end_number, l_max_number) + 1;
614: END LOOP;
615:
616: if g_debug_flag = 'Y' then
617: FII_UTIL.put_line('Inserted ' || l_count || ' jobs into FII_GL_WORKER_JOBS table');
618: end if;
619:
620: COMMIT;
621:
621:
622: EXCEPTION
623: WHEN OTHERS THEN
624: g_retcode := -1;
625: FII_UTIL.put_line('
626: ---------------------------------
627: Error in Procedure: REGISTER_JOBS
628: Phase: '||g_phase||'
629: Message: '||sqlerrm);
646: p_worker_no);
647: IF (l_request_id = 0) THEN
648: rollback;
649: g_retcode := -1;
650: FII_UTIL.put_line('
651: ---------------------------------
652: Error in Procedure: LAUNCH_WORKER
653: Message: '||fnd_message.get);
654: raise G_NO_CHILD_PROCESS;
657:
658: EXCEPTION
659: WHEN G_NO_CHILD_PROCESS THEN
660: g_retcode := -1;
661: FII_UTIL.put_line('No child process launched');
662: raise;
663: WHEN OTHERS THEN
664: rollback;
665: g_retcode := -1;
662: raise;
663: WHEN OTHERS THEN
664: rollback;
665: g_retcode := -1;
666: FII_UTIL.put_line('
667: ---------------------------------
668: Error in Procedure: LAUNCH_WORKER
669: Message: '||sqlerrm);
670: raise;
686: ------------------------------------------------------
687: -- Set default directory in case if the profile option
688: -- BIS_DEBUG_LOG_DIRECTORY is not set up
689: ------------------------------------------------------
690: l_dir:=FII_UTIL.get_utl_file_dir;
691:
692: ----------------------------------------------------------------
693: -- fii_util.initialize will get profile options FII_DEBUG_MODE
694: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
689: ------------------------------------------------------
690: l_dir:=FII_UTIL.get_utl_file_dir;
691:
692: ----------------------------------------------------------------
693: -- fii_util.initialize will get profile options FII_DEBUG_MODE
694: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
695: -- the log files and output files are written to
696: ----------------------------------------------------------------
697: g_phase := 'Calling FII_UTIL.initialize ';
693: -- fii_util.initialize will get profile options FII_DEBUG_MODE
694: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
695: -- the log files and output files are written to
696: ----------------------------------------------------------------
697: g_phase := 'Calling FII_UTIL.initialize ';
698: FII_UTIL.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_GL_JE_B_C_Worker');
699:
700: g_fii_user_id := FND_GLOBAL.User_Id;
701: g_fii_login_id := FND_GLOBAL.Login_Id;
694: -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
695: -- the log files and output files are written to
696: ----------------------------------------------------------------
697: g_phase := 'Calling FII_UTIL.initialize ';
698: FII_UTIL.initialize(p_object_name||'.log',p_object_name||'.out',l_dir, 'FII_GL_JE_B_C_Worker');
699:
700: g_fii_user_id := FND_GLOBAL.User_Id;
701: g_fii_login_id := FND_GLOBAL.Login_Id;
702:
703: EXCEPTION
704: WHEN OTHERS THEN
705: rollback;
706: g_retcode := -1;
707: FII_UTIL.put_line('
708: ---------------------------------
709: Error in Procedure: CHILD_SETUP
710: Phase: '||g_phase||'
711: Message: '||sqlerrm);
725: BEGIN
726:
727: g_phase := 'Checking for missing rates';
728: if g_debug_flag = 'Y' then
729: FII_UTIL.put_line(g_phase);
730: end if;
731:
732: ------------------------------------------------------
733: -- If there are missing exchange rates indicated in
752: END IF;
753:
754: IF l_row_cnt = 0 THEN
755: IF g_debug_flag = 'Y' THEN
756: FII_UTIL.put_line('Summary Error Check completed successfully, no data found!');
757: END IF;
758: RETURN;
759: END IF;
760:
766: FII_MESSAGE.write_output (msg_name => 'FII_MISS_EXCH_RATE_FOUND', token_num => 0);
767: FII_MESSAGE.write_log (msg_name => 'FII_MISS_EXCH_RATE_FOUND', token_num => 0);
768: FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT', token_num => 0);
769:
770: ----FII_UTIL.put_line('Missing currency conversion rates found, program will exit with error status. Please fix the missing conversion rates');
771:
772: g_retcode := -1;
773: g_missing_rates := 1;
774: IF p_program_type = 'L' THEN
780: END IF;
781:
782: g_phase := 'Checking for Time dimension';
783: if g_debug_flag = 'Y' then
784: FII_UTIL.put_line(g_phase);
785: end if;
786:
787: -----------------------------------------------------------
788: -- If we find record in the staging table which references
800: FII_MESSAGE.write_output (msg_name => 'FII_TIME_DIM_STALE', token_num => 0);
801: FII_MESSAGE.write_log (msg_name => 'FII_TIME_DIM_STALE', token_num => 0);
802: FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT', token_num => 0);
803:
804: ----FII_UTIL.put_line('Time Dimension is not fully populated. Please populate Time dimension to cover the date range you are collecting');
805:
806: g_retcode := -1; --we set it error out for missing time
807: g_missing_time := 1;
808: RETURN;
808: RETURN;
809: END IF;
810:
811: if g_debug_flag = 'Y' then
812: FII_UTIL.put_line('Summary Error Check completed successfully, no error found!');
813: end if;
814: RETURN;
815:
816: EXCEPTION
815:
816: EXCEPTION
817: WHEN OTHERS THEN
818: g_retcode := -1;
819: FII_UTIL.put_line('
820: ---------------------------------
821: Error occured in Summary_err_check function
822: Phase: '||g_phase||'
823: Message: '||sqlerrm);
830: PROCEDURE Clean_Up IS
831: BEGIN
832:
833: if g_debug_flag = 'Y' then
834: FII_UTIL.put_line('Calling procedure: CLEAN_UP');
835: end if;
836:
837: TRUNCATE_TABLE('FII_GL_WORKER_JOBS');
838:
848:
849: EXCEPTION
850: WHEN OTHERS Then
851: g_retcode:=-1;
852: FII_UTIL.put_line('
853: ---------------------------------
854: Error in Procedure: Clean_Up
855: Message: ' || sqlerrm);
856: RAISE;
872: ---------------------------------------------------------------------
873: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
874:
875: if g_debug_flag = 'Y' then
876: FII_UTIL.put_line('');
877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
878: FII_UTIL.start_timer;
879: end if;
880:
873: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table for week level';
874:
875: if g_debug_flag = 'Y' then
876: FII_UTIL.put_line('');
877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
878: FII_UTIL.start_timer;
879: end if;
880:
881: INSERT INTO fii_gl_je_summary_stg
874:
875: if g_debug_flag = 'Y' then
876: FII_UTIL.put_line('');
877: FII_UTIL.put_line('Inserting weekly aggregated data into FII_GL_JE_SUMMARY_STG table');
878: FII_UTIL.start_timer;
879: end if;
880:
881: INSERT INTO fii_gl_je_summary_stg
882: (
961: stg.posted_date;
962:
963:
964: if g_debug_flag = 'Y' then
965: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
966: FII_UTIL.stop_timer;
967: FII_UTIL.print_timer('Duration');
968: end if;
969:
962:
963:
964: if g_debug_flag = 'Y' then
965: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
966: FII_UTIL.stop_timer;
967: FII_UTIL.print_timer('Duration');
968: end if;
969:
970: Exception
963:
964: if g_debug_flag = 'Y' then
965: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
966: FII_UTIL.stop_timer;
967: FII_UTIL.print_timer('Duration');
968: end if;
969:
970: Exception
971: WHEN OTHERS Then
969:
970: Exception
971: WHEN OTHERS Then
972: g_retcode := -1;
973: FII_UTIL.put_line('
974: Error in phase ' || g_phase || ' of Sum_Aggregate_Week procedure' || '
975: Message: ' || sqlerrm);
976: ROLLBACK;
977: raise;
991: --------------------------------------------------------------------
992: g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
993:
994: if g_debug_flag = 'Y' then
995: FII_UTIL.start_timer;
996: FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');
997: end if;
998:
999: Update FII_GL_JE_SUMMARY_STG stg
992: g_phase := 'Update global amount in FII_GL_JE_SUMMARY_STG table';
993:
994: if g_debug_flag = 'Y' then
995: FII_UTIL.start_timer;
996: FII_UTIL.put_line('Updating global amount in FII_GL_JE_SUMMARY_STG');
997: end if;
998:
999: Update FII_GL_JE_SUMMARY_STG stg
1000: SET stg.prim_amount_g = round((stg.amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1003: stg.obligated_amount_prim = round((stg.obligated_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau,
1004: stg.other_amount_prim = round((stg.other_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau;
1005:
1006: if g_debug_flag = 'Y' then
1007: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');
1008: FII_UTIL.stop_timer;
1009: FII_UTIL.print_timer('Duration');
1010: end if;
1011:
1004: stg.other_amount_prim = round((stg.other_amount_b * prim_conversion_rate)/g_primary_mau)*g_primary_mau;
1005:
1006: if g_debug_flag = 'Y' then
1007: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');
1008: FII_UTIL.stop_timer;
1009: FII_UTIL.print_timer('Duration');
1010: end if;
1011:
1012: ---------------------------------------------------------------------
1005:
1006: if g_debug_flag = 'Y' then
1007: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records in FII_GL_JE_SUMMARY_STG');
1008: FII_UTIL.stop_timer;
1009: FII_UTIL.print_timer('Duration');
1010: end if;
1011:
1012: ---------------------------------------------------------------------
1013: --Insert aggregate data into FII_GL_JE_SUMMARY_STG table for higher
1015: ---------------------------------------------------------------------
1016: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
1017:
1018: if g_debug_flag = 'Y' then
1019: FII_UTIL.put_line('');
1020: FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');
1021: FII_UTIL.start_timer;
1022: end if;
1023:
1016: g_phase := 'Insert aggregate data into FII_GL_JE_SUMMARY_STG table';
1017:
1018: if g_debug_flag = 'Y' then
1019: FII_UTIL.put_line('');
1020: FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');
1021: FII_UTIL.start_timer;
1022: end if;
1023:
1024: --
1017:
1018: if g_debug_flag = 'Y' then
1019: FII_UTIL.put_line('');
1020: FII_UTIL.put_line('Inserting aggregated data into FII_GL_JE_SUMMARY_STG table');
1021: FII_UTIL.start_timer;
1022: end if;
1023:
1024: --
1025: --bug 3356106: remove rollup by week_id (it's now handled in Sum_aggregate_week)
1114: fday.ent_period_id);
1115:
1116:
1117: if g_debug_flag = 'Y' then
1118: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
1119: ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
1120: FII_UTIL.stop_timer;
1121: FII_UTIL.print_timer('Duration');
1122: end if;
1116:
1117: if g_debug_flag = 'Y' then
1118: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
1119: ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
1120: FII_UTIL.stop_timer;
1121: FII_UTIL.print_timer('Duration');
1122: end if;
1123:
1124: Exception
1117: if g_debug_flag = 'Y' then
1118: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
1119: ' rows of aggregated data into FII_GL_JE_SUMMARY_STG table');
1120: FII_UTIL.stop_timer;
1121: FII_UTIL.print_timer('Duration');
1122: end if;
1123:
1124: Exception
1125: WHEN OTHERS Then
1123:
1124: Exception
1125: WHEN OTHERS Then
1126: g_retcode := -1;
1127: FII_UTIL.put_line('
1128: Error in phase ' || g_phase || ' of Summarize_aggregate procedure' || '
1129: Message: ' || sqlerrm);
1130: ROLLBACK;
1131: raise;
1154:
1155: g_phase := 'Merging records into FII_GL_JE_SUMMARY_B';
1156:
1157: if g_debug_flag = 'Y' then
1158: FII_UTIL.put_line('Merging records into FII_GL_JE_SUMMARY_B');
1159: FII_UTIL.start_timer;
1160: end if;
1161:
1162: MERGE INTO fii_gl_je_summary_b bsum
1155: g_phase := 'Merging records into FII_GL_JE_SUMMARY_B';
1156:
1157: if g_debug_flag = 'Y' then
1158: FII_UTIL.put_line('Merging records into FII_GL_JE_SUMMARY_B');
1159: FII_UTIL.start_timer;
1160: end if;
1161:
1162: MERGE INTO fii_gl_je_summary_b bsum
1163: USING
1290: g_fii_user_id);
1291:
1292:
1293: if g_debug_flag = 'Y' then
1294: FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' rows of records into FII_GL_JE_SUMMARY_B');
1295: FII_UTIL.stop_timer;
1296: FII_UTIL.print_timer('Duration');
1297: end if;
1298:
1291:
1292:
1293: if g_debug_flag = 'Y' then
1294: FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' rows of records into FII_GL_JE_SUMMARY_B');
1295: FII_UTIL.stop_timer;
1296: FII_UTIL.print_timer('Duration');
1297: end if;
1298:
1299: Exception
1292:
1293: if g_debug_flag = 'Y' then
1294: FII_UTIL.put_line('Merged ' || SQL%ROWCOUNT || ' rows of records into FII_GL_JE_SUMMARY_B');
1295: FII_UTIL.stop_timer;
1296: FII_UTIL.print_timer('Duration');
1297: end if;
1298:
1299: Exception
1300: WHEN OTHERS Then
1298:
1299: Exception
1300: WHEN OTHERS Then
1301: g_retcode := -1;
1302: FII_UTIL.put_line('
1303: ----------------------------
1304: Error in Function: Merge
1305: Message: '||sqlerrm);
1306: ROLLBACK;
1314:
1315: BEGIN
1316:
1317: if g_debug_flag = 'Y' then
1318: FII_UTIL.put_line ('Calling Journals_Processed Procedure');
1319: FII_UTIL.start_timer;
1320: end if;
1321:
1322:
1315: BEGIN
1316:
1317: if g_debug_flag = 'Y' then
1318: FII_UTIL.put_line ('Calling Journals_Processed Procedure');
1319: FII_UTIL.start_timer;
1320: end if;
1321:
1322:
1323: ---------------------------------------------------------------------
1344: g_fii_user_id
1345: FROM fii_gl_new_jrl_header_ids;
1346:
1347: if g_debug_flag = 'Y' then
1348: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_PROCESSED_HEADER_IDS');
1349: FII_UTIL.stop_timer;
1350: FII_UTIL.print_timer('Duration');
1351: end if;
1352:
1345: FROM fii_gl_new_jrl_header_ids;
1346:
1347: if g_debug_flag = 'Y' then
1348: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_PROCESSED_HEADER_IDS');
1349: FII_UTIL.stop_timer;
1350: FII_UTIL.print_timer('Duration');
1351: end if;
1352:
1353: Exception
1346:
1347: if g_debug_flag = 'Y' then
1348: FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT || ' rows into FII_GL_PROCESSED_HEADER_IDS');
1349: FII_UTIL.stop_timer;
1350: FII_UTIL.print_timer('Duration');
1351: end if;
1352:
1353: Exception
1354: WHEN OTHERS Then
1352:
1353: Exception
1354: WHEN OTHERS Then
1355: g_retcode := -1;
1356: FII_UTIL.put_line('
1357: ----------------------------
1358: Error in Function: Journal_processed
1359: Message: '||sqlerrm);
1360: ROLLBACK;
1378: -- date range.
1379: -- In future the header ids will be filtered on given set of books id.
1380: -----------------------------------------------------------------------
1381: if g_debug_flag = 'Y' then
1382: FII_UTIL.put_line(' ');
1383: FII_UTIL.put_line('Inserting New Journal header ids');
1384: FII_UTIL.start_timer;
1385: end if;
1386:
1379: -- In future the header ids will be filtered on given set of books id.
1380: -----------------------------------------------------------------------
1381: if g_debug_flag = 'Y' then
1382: FII_UTIL.put_line(' ');
1383: FII_UTIL.put_line('Inserting New Journal header ids');
1384: FII_UTIL.start_timer;
1385: end if;
1386:
1387: --Bug 3121847: changed the hint per performance team suggestion
1380: -----------------------------------------------------------------------
1381: if g_debug_flag = 'Y' then
1382: FII_UTIL.put_line(' ');
1383: FII_UTIL.put_line('Inserting New Journal header ids');
1384: FII_UTIL.start_timer;
1385: end if;
1386:
1387: --Bug 3121847: changed the hint per performance team suggestion
1388:
1456:
1457: l_number_of_rows := SQL%ROWCOUNT;
1458:
1459: if g_debug_flag = 'Y' then
1460: FII_UTIL.put_line('Inserted '||l_number_of_rows||
1461: ' JE header IDs into FII_GL_NEW_JRL_HEADER_IDS');
1462: FII_UTIL.stop_timer;
1463: FII_UTIL.print_timer('Duration');
1464: FII_UTIL.put_line('');
1458:
1459: if g_debug_flag = 'Y' then
1460: FII_UTIL.put_line('Inserted '||l_number_of_rows||
1461: ' JE header IDs into FII_GL_NEW_JRL_HEADER_IDS');
1462: FII_UTIL.stop_timer;
1463: FII_UTIL.print_timer('Duration');
1464: FII_UTIL.put_line('');
1465: end if;
1466:
1459: if g_debug_flag = 'Y' then
1460: FII_UTIL.put_line('Inserted '||l_number_of_rows||
1461: ' JE header IDs into FII_GL_NEW_JRL_HEADER_IDS');
1462: FII_UTIL.stop_timer;
1463: FII_UTIL.print_timer('Duration');
1464: FII_UTIL.put_line('');
1465: end if;
1466:
1467: COMMIT;
1460: FII_UTIL.put_line('Inserted '||l_number_of_rows||
1461: ' JE header IDs into FII_GL_NEW_JRL_HEADER_IDS');
1462: FII_UTIL.stop_timer;
1463: FII_UTIL.print_timer('Duration');
1464: FII_UTIL.put_line('');
1465: end if;
1466:
1467: COMMIT;
1468: return(l_number_of_rows);
1469:
1470: Exception
1471: WHEN OTHERS Then
1472: g_retcode := -1;
1473: FII_UTIL.put_line('
1474: ----------------------------
1475: Error in New_Journals Procedure
1476: Message: '||sqlerrm);
1477: RAISE;
1492: -- Header IDs are stored in FII_GL_NEW_JRL_HEADER_IDS table into
1493: -- FII_GL_JE_SUMMARY_STG.
1494: ------------------------------------------------------------------
1495: if g_debug_flag = 'Y' then
1496: FII_UTIL.put_line('Processing ID range: ' || p_start_range ||
1497: ' to ' || p_end_range);
1498: end if;
1499:
1500: l_stmt:= 'INSERT INTO FII_GL_JE_SUMMARY_STG
1611: njhi.posted_date), -- for encumbrances
1612: null)'; -- for Commercial
1613:
1614: if g_debug_flag = 'Y' then
1615: FII_UTIL.start_timer;
1616: FII_UTIL.put_line('');
1617: FII_UTIL.put_line(l_stmt);
1618: end if;
1619:
1612: null)'; -- for Commercial
1613:
1614: if g_debug_flag = 'Y' then
1615: FII_UTIL.start_timer;
1616: FII_UTIL.put_line('');
1617: FII_UTIL.put_line(l_stmt);
1618: end if;
1619:
1620:
1613:
1614: if g_debug_flag = 'Y' then
1615: FII_UTIL.start_timer;
1616: FII_UTIL.put_line('');
1617: FII_UTIL.put_line(l_stmt);
1618: end if;
1619:
1620:
1621: EXECUTE IMMEDIATE l_stmt;
1624:
1625: commit;
1626:
1627: if g_debug_flag = 'Y' then
1628: FII_UTIL.put_line('');
1629: FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
1630: FII_UTIL.stop_timer;
1631: FII_UTIL.print_timer('Duration');
1632: end if;
1625: commit;
1626:
1627: if g_debug_flag = 'Y' then
1628: FII_UTIL.put_line('');
1629: FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
1630: FII_UTIL.stop_timer;
1631: FII_UTIL.print_timer('Duration');
1632: end if;
1633:
1626:
1627: if g_debug_flag = 'Y' then
1628: FII_UTIL.put_line('');
1629: FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
1630: FII_UTIL.stop_timer;
1631: FII_UTIL.print_timer('Duration');
1632: end if;
1633:
1634: EXCEPTION
1627: if g_debug_flag = 'Y' then
1628: FII_UTIL.put_line('');
1629: FII_UTIL.put_line('Inserted '||l_number_of_rows||' into table FII_GL_JE_SUMMARY_STG with day level data');
1630: FII_UTIL.stop_timer;
1631: FII_UTIL.print_timer('Duration');
1632: end if;
1633:
1634: EXCEPTION
1635: WHEN OTHERS Then
1633:
1634: EXCEPTION
1635: WHEN OTHERS Then
1636: g_retcode := -1;
1637: FII_UTIL.put_line('
1638: ----------------------------
1639: Error in Function: Summarize_day
1640: Message: '||sqlerrm);
1641: raise;
1660:
1661: BEGIN
1662:
1663: if g_debug_flag = 'Y' then
1664: FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
1665: FII_UTIL.put_line('');
1666: end if;
1667:
1668: IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
1661: BEGIN
1662:
1663: if g_debug_flag = 'Y' then
1664: FII_UTIL.put_line('Calling Procedure: VERIFY_CCID_UP_TO_DATE');
1665: FII_UTIL.put_line('');
1666: end if;
1667:
1668: IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
1669: if g_debug_flag = 'Y' then
1666: end if;
1667:
1668: IF(FII_GL_CCID_C.NEW_CCID_IN_GL) THEN
1669: if g_debug_flag = 'Y' then
1670: FII_UTIL.put_line('CCID Dimension is not up to date, calling CCID Dimension update program');
1671: end if;
1672:
1673: g_phase := 'Calling CCID Dimension update program';
1674: l_dummy := FND_REQUEST.SET_MODE(TRUE);
1678:
1679: IF (l_request_id = 0) THEN
1680: rollback;
1681: g_retcode := -1;
1682: FII_UTIL.put_line('
1683: ---------------------------------
1684: Error in Procedure: VERIFY_CCID_UP_TO_DATE
1685: Message: '||fnd_message.get);
1686: raise G_NO_CHILD_PROCESS;
1698:
1699: g_phase := 'Finished calling FND_CONCURRENT.wait_for_request -> ' || l_devphase || ', ' || l_devstatus;
1700: IF (NVL(l_devphase='COMPLETE' AND l_devstatus='NORMAL', FALSE)) THEN
1701: if g_debug_flag = 'Y' then
1702: FII_UTIL.put_line('CCID Dimension populated successfully');
1703: end if;
1704: ELSE
1705: if g_debug_flag = 'Y' then
1706: FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
1702: FII_UTIL.put_line('CCID Dimension populated successfully');
1703: end if;
1704: ELSE
1705: if g_debug_flag = 'Y' then
1706: FII_UTIL.put_line('CCID Dimension populated unsuccessfully');
1707: end if;
1708: raise G_CCID_FAILED;
1709: END IF;
1710:
1710:
1711: ELSE
1712:
1713: if g_debug_flag = 'Y' then
1714: FII_UTIL.put_line('CCID Dimension is up to date');
1715: FII_UTIL.put_line('');
1716: end if;
1717:
1718: END IF;
1711: ELSE
1712:
1713: if g_debug_flag = 'Y' then
1714: FII_UTIL.put_line('CCID Dimension is up to date');
1715: FII_UTIL.put_line('');
1716: end if;
1717:
1718: END IF;
1719:
1719:
1720: Exception
1721: WHEN G_NO_CHILD_PROCESS THEN
1722: g_retcode := -1;
1723: FII_UTIL.put_line('
1724: ----------------------------
1725: Error in Procedure : VERIFY_CCID_UP_TO_DATE
1726: Phase: Submitting Child process to run CCID program');
1727: raise;
1726: Phase: Submitting Child process to run CCID program');
1727: raise;
1728: WHEN G_CCID_FAILED THEN
1729: g_retcode := -1;
1730: FII_UTIL.put_line('
1731: ----------------------------
1732: Error in Procedure : VERIFY_CCID_UP_TO_DATE when running CCID program
1733: Phase: ' || g_phase);
1734: raise;
1733: Phase: ' || g_phase);
1734: raise;
1735: WHEN OTHERS Then
1736: g_retcode := -1;
1737: FII_UTIL.put_line('
1738: ----------------------------
1739: Error in Procedure : VERIFY_CCID_UP_TO_DATE
1740: Phase: ' || g_phase || '
1741: Message: '||sqlerrm);
1762: from gl_encumbrance_types);
1763: BEGIN
1764:
1765: IF g_debug_flag = 'Y' THEN
1766: FII_UTIL.put_line('In procedure POPULATE_ENCUM_MAPPING():');
1767: FII_UTIL.put_line('');
1768: END IF;
1769:
1770: ---------------------------------------------------------------------------
1763: BEGIN
1764:
1765: IF g_debug_flag = 'Y' THEN
1766: FII_UTIL.put_line('In procedure POPULATE_ENCUM_MAPPING():');
1767: FII_UTIL.put_line('');
1768: END IF;
1769:
1770: ---------------------------------------------------------------------------
1771: -- Truncate fii_encum_type_mappings
1770: ---------------------------------------------------------------------------
1771: -- Truncate fii_encum_type_mappings
1772: ---------------------------------------------------------------------------
1773: IF g_debug_flag = 'Y' THEN
1774: fii_util.put_line(' ');
1775: fii_util.put_line('Truncate fii_encum_type_mappings...');
1776: END IF;
1777: TRUNCATE_TABLE('FII_ENCUM_TYPE_MAPPINGS');
1778:
1771: -- Truncate fii_encum_type_mappings
1772: ---------------------------------------------------------------------------
1773: IF g_debug_flag = 'Y' THEN
1774: fii_util.put_line(' ');
1775: fii_util.put_line('Truncate fii_encum_type_mappings...');
1776: END IF;
1777: TRUNCATE_TABLE('FII_ENCUM_TYPE_MAPPINGS');
1778:
1779: INSERT INTO fii_encum_type_mappings
1800: AND a.language = userenv('LANG')
1801: AND upper(a.lookup_code) = upper(b.encumbrance_type);
1802:
1803: IF g_debug_flag = 'Y' THEN
1804: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_encum_type_mappings');
1805: fii_util.stop_timer;
1806: fii_util.print_timer('Duration');
1807: END IF;
1808:
1801: AND upper(a.lookup_code) = upper(b.encumbrance_type);
1802:
1803: IF g_debug_flag = 'Y' THEN
1804: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_encum_type_mappings');
1805: fii_util.stop_timer;
1806: fii_util.print_timer('Duration');
1807: END IF;
1808:
1809: -- Print a warning message if there is any lookup_code that does not match the
1802:
1803: IF g_debug_flag = 'Y' THEN
1804: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_encum_type_mappings');
1805: fii_util.stop_timer;
1806: fii_util.print_timer('Duration');
1807: END IF;
1808:
1809: -- Print a warning message if there is any lookup_code that does not match the
1810: -- encumbrance_type defined in gl_encumbrance_types
1810: -- encumbrance_type defined in gl_encumbrance_types
1811: l_count := 0;
1812: FOR invalid_lookup_codes in invalid_lookup_cur LOOP
1813: IF (l_count = 0) THEN
1814: fii_util.put_line(' ');
1815: fii_util.put_line(
1816: 'WARNING: Invalid lookup codes found in the encumbrance type mappings. ');
1817: fii_util.put_line('Please make sure these lookup codes are valid GL Encumbrance Types.');
1818: fii_util.put_line('Lookup Type Lookup Code ');
1811: l_count := 0;
1812: FOR invalid_lookup_codes in invalid_lookup_cur LOOP
1813: IF (l_count = 0) THEN
1814: fii_util.put_line(' ');
1815: fii_util.put_line(
1816: 'WARNING: Invalid lookup codes found in the encumbrance type mappings. ');
1817: fii_util.put_line('Please make sure these lookup codes are valid GL Encumbrance Types.');
1818: fii_util.put_line('Lookup Type Lookup Code ');
1819: fii_util.put_line('----------- -----------');
1813: IF (l_count = 0) THEN
1814: fii_util.put_line(' ');
1815: fii_util.put_line(
1816: 'WARNING: Invalid lookup codes found in the encumbrance type mappings. ');
1817: fii_util.put_line('Please make sure these lookup codes are valid GL Encumbrance Types.');
1818: fii_util.put_line('Lookup Type Lookup Code ');
1819: fii_util.put_line('----------- -----------');
1820: l_count := l_count + 1;
1821: END IF;
1814: fii_util.put_line(' ');
1815: fii_util.put_line(
1816: 'WARNING: Invalid lookup codes found in the encumbrance type mappings. ');
1817: fii_util.put_line('Please make sure these lookup codes are valid GL Encumbrance Types.');
1818: fii_util.put_line('Lookup Type Lookup Code ');
1819: fii_util.put_line('----------- -----------');
1820: l_count := l_count + 1;
1821: END IF;
1822:
1815: fii_util.put_line(
1816: 'WARNING: Invalid lookup codes found in the encumbrance type mappings. ');
1817: fii_util.put_line('Please make sure these lookup codes are valid GL Encumbrance Types.');
1818: fii_util.put_line('Lookup Type Lookup Code ');
1819: fii_util.put_line('----------- -----------');
1820: l_count := l_count + 1;
1821: END IF;
1822:
1823: fii_util.put_line(invalid_lookup_codes.lookup_type ||' '||
1819: fii_util.put_line('----------- -----------');
1820: l_count := l_count + 1;
1821: END IF;
1822:
1823: fii_util.put_line(invalid_lookup_codes.lookup_type ||' '||
1824: invalid_lookup_codes.lookup_code);
1825:
1826: END LOOP;
1827:
1826: END LOOP;
1827:
1828: -- Raise an error if the mapping table is empty
1829: IF (SQL%ROWCOUNT = 0) THEN
1830: fii_util.put_line('The mapping table between GL Encumrbance Type and FII Encumbrance bucket (fii_encum_type_mappings) is empty. Please enter the encumbrance type mappings.');
1831: raise G_MISSING_ENCUM_MAPPING;
1832: END IF;
1833:
1834: commit;
1835:
1836: Exception
1837: WHEN G_MISSING_ENCUM_MAPPING Then
1838: g_retcode := -1;
1839: FII_UTIL.put_line('POPULATE_ENCUM_MAPPING:Encumbrance mapping is missing.');
1840: raise;
1841:
1842: WHEN OTHERS Then
1843: g_retcode := -1;
1840: raise;
1841:
1842: WHEN OTHERS Then
1843: g_retcode := -1;
1844: FII_UTIL.put_line('
1845: ----------------------------
1846: Error in Procedure : POPULATE_ENCUM_MAPPING
1847: Phase: ' || g_phase || '
1848: Message: '||sqlerrm);
1871: l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
1872: execute immediate l_stmt;
1873:
1874: if g_debug_flag = 'Y' then
1875: fii_util.put_line(' ');
1876: fii_util.put_line('Loading data into staging table');
1877: fii_util.start_timer;
1878: fii_util.put_line('');
1879: end if;
1872: execute immediate l_stmt;
1873:
1874: if g_debug_flag = 'Y' then
1875: fii_util.put_line(' ');
1876: fii_util.put_line('Loading data into staging table');
1877: fii_util.start_timer;
1878: fii_util.put_line('');
1879: end if;
1880:
1873:
1874: if g_debug_flag = 'Y' then
1875: fii_util.put_line(' ');
1876: fii_util.put_line('Loading data into staging table');
1877: fii_util.start_timer;
1878: fii_util.put_line('');
1879: end if;
1880:
1881: g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';
1874: if g_debug_flag = 'Y' then
1875: fii_util.put_line(' ');
1876: fii_util.put_line('Loading data into staging table');
1877: fii_util.start_timer;
1878: fii_util.put_line('');
1879: end if;
1880:
1881: g_phase := 'Inserting into FII_GL_JE_SUMMARY_STG';
1882: INSERT /*+ append parallel(fii_gl_je_summary_stg) */ INTO FII_GL_JE_SUMMARY_STG
2128: g_phase := 'Enabling parallel dml';
2129: execute immediate 'alter session enable parallel dml';
2130:
2131: if g_debug_flag = 'Y' then
2132: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');
2133: fii_util.stop_timer;
2134: fii_util.print_timer('Duration');
2135: end if;
2136:
2129: execute immediate 'alter session enable parallel dml';
2130:
2131: if g_debug_flag = 'Y' then
2132: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');
2133: fii_util.stop_timer;
2134: fii_util.print_timer('Duration');
2135: end if;
2136:
2137: EXCEPTION
2130:
2131: if g_debug_flag = 'Y' then
2132: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_je_summary_stg');
2133: fii_util.stop_timer;
2134: fii_util.print_timer('Duration');
2135: end if;
2136:
2137: EXCEPTION
2138: WHEN OTHERS Then
2136:
2137: EXCEPTION
2138: WHEN OTHERS Then
2139: g_retcode := -1;
2140: FII_UTIL.put_line('
2141: ----------------------------
2142: Error in Function: INSERT_INTO_STG
2143: Phase: ' || g_phase || '
2144: Message: '||sqlerrm);
2165: l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
2166: execute immediate l_stmt;
2167:
2168: if g_debug_flag = 'Y' then
2169: fii_util.put_line(' ');
2170: fii_util.put_line('Rolling up data in staging table');
2171: fii_util.start_timer;
2172: fii_util.put_line('');
2173: end if;
2166: execute immediate l_stmt;
2167:
2168: if g_debug_flag = 'Y' then
2169: fii_util.put_line(' ');
2170: fii_util.put_line('Rolling up data in staging table');
2171: fii_util.start_timer;
2172: fii_util.put_line('');
2173: end if;
2174:
2167:
2168: if g_debug_flag = 'Y' then
2169: fii_util.put_line(' ');
2170: fii_util.put_line('Rolling up data in staging table');
2171: fii_util.start_timer;
2172: fii_util.put_line('');
2173: end if;
2174:
2175: g_phase := 'Inserting into fii_gl_je_summary_b';
2168: if g_debug_flag = 'Y' then
2169: fii_util.put_line(' ');
2170: fii_util.put_line('Rolling up data in staging table');
2171: fii_util.start_timer;
2172: fii_util.put_line('');
2173: end if;
2174:
2175: g_phase := 'Inserting into fii_gl_je_summary_b';
2176: INSERT /*+ append parallel(fii_gl_je_summary_b) */ INTO fii_gl_je_summary_b
2257: fday.week_id,
2258: bsum.posted_date ;
2259:
2260: if g_debug_flag = 'Y' then
2261: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2262: fii_util.stop_timer;
2263: fii_util.print_timer('Duration');
2264: end if;
2265:
2258: bsum.posted_date ;
2259:
2260: if g_debug_flag = 'Y' then
2261: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2262: fii_util.stop_timer;
2263: fii_util.print_timer('Duration');
2264: end if;
2265:
2266: commit;
2259:
2260: if g_debug_flag = 'Y' then
2261: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2262: fii_util.stop_timer;
2263: fii_util.print_timer('Duration');
2264: end if;
2265:
2266: commit;
2267:
2267:
2268: EXCEPTION
2269: WHEN OTHERS Then
2270: g_retcode := -1;
2271: FII_UTIL.put_line('
2272: ----------------------------
2273: Error in Function: Roll_up
2274: Phase: ' || g_phase || '
2275: Message: '||sqlerrm);
2297: l_stmt := 'alter session set hash_area_size= ' ||p_hash_area_size;
2298: execute immediate l_stmt;
2299:
2300: if g_debug_flag = 'Y' then
2301: fii_util.put_line(' ');
2302: fii_util.put_line('Rolling up data in staging table');
2303: fii_util.start_timer;
2304: fii_util.put_line('');
2305: end if;
2298: execute immediate l_stmt;
2299:
2300: if g_debug_flag = 'Y' then
2301: fii_util.put_line(' ');
2302: fii_util.put_line('Rolling up data in staging table');
2303: fii_util.start_timer;
2304: fii_util.put_line('');
2305: end if;
2306:
2299:
2300: if g_debug_flag = 'Y' then
2301: fii_util.put_line(' ');
2302: fii_util.put_line('Rolling up data in staging table');
2303: fii_util.start_timer;
2304: fii_util.put_line('');
2305: end if;
2306:
2307: --Bug 3121847: removed delete in ROLL_UP2 by filtering it out during insert
2300: if g_debug_flag = 'Y' then
2301: fii_util.put_line(' ');
2302: fii_util.put_line('Rolling up data in staging table');
2303: fii_util.start_timer;
2304: fii_util.put_line('');
2305: end if;
2306:
2307: --Bug 3121847: removed delete in ROLL_UP2 by filtering it out during insert
2308:
2393: fday.ent_period_id))
2394: where time_id is not null;
2395:
2396: if g_debug_flag = 'Y' then
2397: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2398: fii_util.stop_timer;
2399: fii_util.print_timer('Duration');
2400: end if;
2401:
2394: where time_id is not null;
2395:
2396: if g_debug_flag = 'Y' then
2397: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2398: fii_util.stop_timer;
2399: fii_util.print_timer('Duration');
2400: end if;
2401:
2402: commit;
2395:
2396: if g_debug_flag = 'Y' then
2397: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2398: fii_util.stop_timer;
2399: fii_util.print_timer('Duration');
2400: end if;
2401:
2402: commit;
2403:
2407:
2408: EXCEPTION
2409: WHEN OTHERS Then
2410: g_retcode := -1;
2411: FII_UTIL.put_line('
2412: ----------------------------
2413: Error in Function: Roll_up2
2414: Phase: ' || g_phase || '
2415: Message: '||sqlerrm);
2433: l_global_prim_curr_code := bis_common_parameters.get_currency_code;
2434: l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
2435:
2436: if g_debug_flag = 'Y' then
2437: fii_util.put_line(' ');
2438: fii_util.put_line('Loading data into rates table');
2439: fii_util.start_timer;
2440: fii_util.put_line('');
2441: end if;
2434: l_global_sec_curr_code := bis_common_parameters.get_secondary_currency_code;
2435:
2436: if g_debug_flag = 'Y' then
2437: fii_util.put_line(' ');
2438: fii_util.put_line('Loading data into rates table');
2439: fii_util.start_timer;
2440: fii_util.put_line('');
2441: end if;
2442:
2435:
2436: if g_debug_flag = 'Y' then
2437: fii_util.put_line(' ');
2438: fii_util.put_line('Loading data into rates table');
2439: fii_util.start_timer;
2440: fii_util.put_line('');
2441: end if;
2442:
2443: g_phase := 'Inserting into fii_gl_revenue_rates_temp';
2436: if g_debug_flag = 'Y' then
2437: fii_util.put_line(' ');
2438: fii_util.put_line('Loading data into rates table');
2439: fii_util.start_timer;
2440: fii_util.put_line('');
2441: end if;
2442:
2443: g_phase := 'Inserting into fii_gl_revenue_rates_temp';
2444: insert into fii_gl_revenue_rates_temp
2467: g_phase := 'Enabling parallel dml';
2468: execute immediate 'alter session enable parallel dml';
2469:
2470: if g_debug_flag = 'Y' then
2471: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_revenue_rates_temp');
2472: fii_util.stop_timer;
2473: fii_util.print_timer('Duration');
2474: end if;
2475:
2468: execute immediate 'alter session enable parallel dml';
2469:
2470: if g_debug_flag = 'Y' then
2471: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_revenue_rates_temp');
2472: fii_util.stop_timer;
2473: fii_util.print_timer('Duration');
2474: end if;
2475:
2476: EXCEPTION
2469:
2470: if g_debug_flag = 'Y' then
2471: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into fii_gl_revenue_rates_temp');
2472: fii_util.stop_timer;
2473: fii_util.print_timer('Duration');
2474: end if;
2475:
2476: EXCEPTION
2477: WHEN OTHERS Then
2475:
2476: EXCEPTION
2477: WHEN OTHERS Then
2478: g_retcode := -1;
2479: FII_UTIL.put_line('
2480: ----------------------------
2481: Error in Function: Insert_Into_Rates
2482: Phase: ' || g_phase || '
2483: Message: '||sqlerrm);
2495:
2496: BEGIN
2497:
2498: if g_debug_flag = 'Y' then
2499: fii_util.put_line(' ');
2500: fii_util.put_line('Loading data into base summary table');
2501: fii_util.start_timer;
2502: fii_util.put_line('');
2503: end if;
2496: BEGIN
2497:
2498: if g_debug_flag = 'Y' then
2499: fii_util.put_line(' ');
2500: fii_util.put_line('Loading data into base summary table');
2501: fii_util.start_timer;
2502: fii_util.put_line('');
2503: end if;
2504:
2497:
2498: if g_debug_flag = 'Y' then
2499: fii_util.put_line(' ');
2500: fii_util.put_line('Loading data into base summary table');
2501: fii_util.start_timer;
2502: fii_util.put_line('');
2503: end if;
2504:
2505: --Bug 3121847: changed the second hint per performance team suggestion
2498: if g_debug_flag = 'Y' then
2499: fii_util.put_line(' ');
2500: fii_util.put_line('Loading data into base summary table');
2501: fii_util.start_timer;
2502: fii_util.put_line('');
2503: end if;
2504:
2505: --Bug 3121847: changed the second hint per performance team suggestion
2506:
2601: g_phase := 'Enabling parallel dml';
2602: execute immediate 'alter session enable parallel dml';
2603:
2604: if g_debug_flag = 'Y' then
2605: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2606: fii_util.stop_timer;
2607: fii_util.print_timer('Duration');
2608: end if;
2609:
2602: execute immediate 'alter session enable parallel dml';
2603:
2604: if g_debug_flag = 'Y' then
2605: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2606: fii_util.stop_timer;
2607: fii_util.print_timer('Duration');
2608: end if;
2609:
2610: commit;
2603:
2604: if g_debug_flag = 'Y' then
2605: fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows into FII_GL_JE_SUMMARY_B');
2606: fii_util.stop_timer;
2607: fii_util.print_timer('Duration');
2608: end if;
2609:
2610: commit;
2611:
2611:
2612: EXCEPTION
2613: WHEN OTHERS Then
2614: g_retcode := -1;
2615: FII_UTIL.put_line('
2616: ----------------------------
2617: Error in Function: Insert_Into_Summary
2618: Phase: ' || g_phase || '
2619: Message: '||sqlerrm);
2629: l_sqlstmt VARCHAR2(5000);
2630: BEGIN
2631:
2632: IF g_debug_flag = 'Y' THEN
2633: fii_util.put_line(' ');
2634: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_f...');
2635: fii_util.start_timer;
2636: fii_util.put_line(' ');
2637: END IF;
2630: BEGIN
2631:
2632: IF g_debug_flag = 'Y' THEN
2633: fii_util.put_line(' ');
2634: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_f...');
2635: fii_util.start_timer;
2636: fii_util.put_line(' ');
2637: END IF;
2638:
2631:
2632: IF g_debug_flag = 'Y' THEN
2633: fii_util.put_line(' ');
2634: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_f...');
2635: fii_util.start_timer;
2636: fii_util.put_line(' ');
2637: END IF;
2638:
2639: g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_f';
2632: IF g_debug_flag = 'Y' THEN
2633: fii_util.put_line(' ');
2634: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_f...');
2635: fii_util.start_timer;
2636: fii_util.put_line(' ');
2637: END IF;
2638:
2639: g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_f';
2640:
2672: ' AND stg.year IS NOT NULL ';
2673:
2674: -- Print out the dynamic SQL statements if running in debug mode
2675: IF g_debug_flag = 'Y' THEN
2676: fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
2677:
2678: FII_MESSAGE.Write_Log
2679: (msg_name => 'FII_ROUTINE_VAL',
2680: token_num => 3 ,
2688:
2689: EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
2690:
2691: IF g_debug_flag = 'Y' THEN
2692: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2693: ' rows into FII_GL_ENC_CARRYFWD_F');
2694: fii_util.put_line('');
2695: fii_util.stop_timer;
2696: fii_util.print_timer('Duration');
2690:
2691: IF g_debug_flag = 'Y' THEN
2692: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2693: ' rows into FII_GL_ENC_CARRYFWD_F');
2694: fii_util.put_line('');
2695: fii_util.stop_timer;
2696: fii_util.print_timer('Duration');
2697: END IF;
2698:
2691: IF g_debug_flag = 'Y' THEN
2692: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2693: ' rows into FII_GL_ENC_CARRYFWD_F');
2694: fii_util.put_line('');
2695: fii_util.stop_timer;
2696: fii_util.print_timer('Duration');
2697: END IF;
2698:
2699: COMMIT;
2692: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2693: ' rows into FII_GL_ENC_CARRYFWD_F');
2694: fii_util.put_line('');
2695: fii_util.stop_timer;
2696: fii_util.print_timer('Duration');
2697: END IF;
2698:
2699: COMMIT;
2700:
2700:
2701: EXCEPTION
2702: WHEN OTHERS Then
2703: g_retcode := -1;
2704: FII_UTIL.put_line('
2705: ----------------------------
2706: Error in Function: INSERT_CARRYFWD_BASE
2707: Phase: ' || g_phase || '
2708: Message: '||sqlerrm);
2723: -- Delete data from fii_gl_enc_carryfwd_f if time/dimension exists in base
2724: -- table is no longer included in the current run.
2725: ---------------------------------------------------------------------------
2726: IF g_debug_flag = 'Y' THEN
2727: fii_util.put_line(' ');
2728: fii_util.put_line('Delete data from fact table if time/dimension no longer exists in the currency run...');
2729: fii_util.start_timer;
2730: fii_util.put_line('');
2731: END IF;
2724: -- table is no longer included in the current run.
2725: ---------------------------------------------------------------------------
2726: IF g_debug_flag = 'Y' THEN
2727: fii_util.put_line(' ');
2728: fii_util.put_line('Delete data from fact table if time/dimension no longer exists in the currency run...');
2729: fii_util.start_timer;
2730: fii_util.put_line('');
2731: END IF;
2732:
2725: ---------------------------------------------------------------------------
2726: IF g_debug_flag = 'Y' THEN
2727: fii_util.put_line(' ');
2728: fii_util.put_line('Delete data from fact table if time/dimension no longer exists in the currency run...');
2729: fii_util.start_timer;
2730: fii_util.put_line('');
2731: END IF;
2732:
2733: g_phase := 'Delete carryforward data from fact not included in currency run';
2726: IF g_debug_flag = 'Y' THEN
2727: fii_util.put_line(' ');
2728: fii_util.put_line('Delete data from fact table if time/dimension no longer exists in the currency run...');
2729: fii_util.start_timer;
2730: fii_util.put_line('');
2731: END IF;
2732:
2733: g_phase := 'Delete carryforward data from fact not included in currency run';
2734:
2748: ' WHERE functional_currency = :global_primary) ';
2749:
2750: -- Print out the dynamic SQL statements if running in debug mode
2751: IF g_debug_flag = 'Y' THEN
2752: fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
2753:
2754: FII_MESSAGE.Write_Log
2755: (msg_name => 'FII_ROUTINE_VAL',
2756: token_num => 3 ,
2764:
2765: EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
2766:
2767: IF g_debug_flag = 'Y' THEN
2768: fii_util.put_line('Deleted '||SQL%ROWCOUNT||
2769: ' rows from FII_GL_ENC_CARRYFWD_F');
2770: fii_util.stop_timer;
2771: fii_util.print_timer('Duration');
2772: END IF;
2766:
2767: IF g_debug_flag = 'Y' THEN
2768: fii_util.put_line('Deleted '||SQL%ROWCOUNT||
2769: ' rows from FII_GL_ENC_CARRYFWD_F');
2770: fii_util.stop_timer;
2771: fii_util.print_timer('Duration');
2772: END IF;
2773:
2774: ---------------------------------------------------------------------------
2767: IF g_debug_flag = 'Y' THEN
2768: fii_util.put_line('Deleted '||SQL%ROWCOUNT||
2769: ' rows from FII_GL_ENC_CARRYFWD_F');
2770: fii_util.stop_timer;
2771: fii_util.print_timer('Duration');
2772: END IF;
2773:
2774: ---------------------------------------------------------------------------
2775: -- Insert new data from fii_gl_enc_carryfwd_t into fii_gl_enc_carryfwd_f
2774: ---------------------------------------------------------------------------
2775: -- Insert new data from fii_gl_enc_carryfwd_t into fii_gl_enc_carryfwd_f
2776: ---------------------------------------------------------------------------
2777: IF g_debug_flag = 'Y' THEN
2778: fii_util.put_line(' ');
2779: fii_util.put_line('Insert new data into fii_gl_enc_carryfwd_f...');
2780: fii_util.start_timer;
2781: fii_util.put_line('');
2782: END IF;
2775: -- Insert new data from fii_gl_enc_carryfwd_t into fii_gl_enc_carryfwd_f
2776: ---------------------------------------------------------------------------
2777: IF g_debug_flag = 'Y' THEN
2778: fii_util.put_line(' ');
2779: fii_util.put_line('Insert new data into fii_gl_enc_carryfwd_f...');
2780: fii_util.start_timer;
2781: fii_util.put_line('');
2782: END IF;
2783:
2776: ---------------------------------------------------------------------------
2777: IF g_debug_flag = 'Y' THEN
2778: fii_util.put_line(' ');
2779: fii_util.put_line('Insert new data into fii_gl_enc_carryfwd_f...');
2780: fii_util.start_timer;
2781: fii_util.put_line('');
2782: END IF;
2783:
2784: g_phase := 'Insert new data into fii_gl_enc_carryfwd_f';
2777: IF g_debug_flag = 'Y' THEN
2778: fii_util.put_line(' ');
2779: fii_util.put_line('Insert new data into fii_gl_enc_carryfwd_f...');
2780: fii_util.start_timer;
2781: fii_util.put_line('');
2782: END IF;
2783:
2784: g_phase := 'Insert new data into fii_gl_enc_carryfwd_f';
2785:
2825: ' FROM fii_gl_enc_carryfwd_f) ';
2826:
2827: -- Print out the dynamic SQL statements if running in debug mode
2828: IF g_debug_flag = 'Y' THEN
2829: fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
2830:
2831: FII_MESSAGE.Write_Log
2832: (msg_name => 'FII_ROUTINE_VAL',
2833: token_num => 3 ,
2841:
2842: EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
2843:
2844: IF g_debug_flag = 'Y' THEN
2845: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2846: ' rows into FII_GL_ENC_CARRYFWD_F');
2847: fii_util.stop_timer;
2848: fii_util.print_timer('Duration');
2849: END IF;
2843:
2844: IF g_debug_flag = 'Y' THEN
2845: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2846: ' rows into FII_GL_ENC_CARRYFWD_F');
2847: fii_util.stop_timer;
2848: fii_util.print_timer('Duration');
2849: END IF;
2850:
2851: EXCEPTION
2844: IF g_debug_flag = 'Y' THEN
2845: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
2846: ' rows into FII_GL_ENC_CARRYFWD_F');
2847: fii_util.stop_timer;
2848: fii_util.print_timer('Duration');
2849: END IF;
2850:
2851: EXCEPTION
2852: WHEN OTHERS Then
2850:
2851: EXCEPTION
2852: WHEN OTHERS Then
2853: g_retcode := -1;
2854: FII_UTIL.put_line('
2855: ----------------------------
2856: Error in Function: MERGE_CARRYFWD_BASE
2857: Phase: ' || g_phase || '
2858: Message: '||sqlerrm);
2890: ---------------------------------------------------------------------------
2891: -- Truncate fii_gl_enc_carrfywd_t
2892: ---------------------------------------------------------------------------
2893: IF g_debug_flag = 'Y' THEN
2894: fii_util.put_line(' ');
2895: fii_util.put_line('Truncate fii_gl_enc_carryfwd_t...');
2896: END IF;
2897: TRUNCATE_TABLE('FII_GL_ENC_CARRYFWD_T');
2898:
2891: -- Truncate fii_gl_enc_carrfywd_t
2892: ---------------------------------------------------------------------------
2893: IF g_debug_flag = 'Y' THEN
2894: fii_util.put_line(' ');
2895: fii_util.put_line('Truncate fii_gl_enc_carryfwd_t...');
2896: END IF;
2897: TRUNCATE_TABLE('FII_GL_ENC_CARRYFWD_T');
2898:
2899: ---------------------------------------------------------------------------
2900: -- If initial load, truncate fii_gl_enc_carrfywd_f as well
2901: ---------------------------------------------------------------------------
2902: IF (l_program_type = 'L') THEN
2903: IF g_debug_flag = 'Y' THEN
2904: fii_util.put_line(' ');
2905: fii_util.put_line('Truncate fii_gl_enc_carryfwd_f...');
2906: END IF;
2907: TRUNCATE_TABLE('FII_GL_ENC_CARRYFWD_F');
2908: END IF;
2901: ---------------------------------------------------------------------------
2902: IF (l_program_type = 'L') THEN
2903: IF g_debug_flag = 'Y' THEN
2904: fii_util.put_line(' ');
2905: fii_util.put_line('Truncate fii_gl_enc_carryfwd_f...');
2906: END IF;
2907: TRUNCATE_TABLE('FII_GL_ENC_CARRYFWD_F');
2908: END IF;
2909:
2911: -- Insert encumbrance carry forward amounts into staging table
2912: ---------------------------------------------------------------------------
2913: g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_t';
2914: IF g_debug_flag = 'Y' THEN
2915: fii_util.put_line(' ');
2916: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_t...');
2917: fii_util.start_timer;
2918: fii_util.put_line('');
2919: END IF;
2912: ---------------------------------------------------------------------------
2913: g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_t';
2914: IF g_debug_flag = 'Y' THEN
2915: fii_util.put_line(' ');
2916: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_t...');
2917: fii_util.start_timer;
2918: fii_util.put_line('');
2919: END IF;
2920:
2913: g_phase := 'Inserting encumbrance carry forward into fii_gl_enc_carryfwd_t';
2914: IF g_debug_flag = 'Y' THEN
2915: fii_util.put_line(' ');
2916: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_t...');
2917: fii_util.start_timer;
2918: fii_util.put_line('');
2919: END IF;
2920:
2921: -- Find out the encumbrance type ID for the seeded encumbrance types
2914: IF g_debug_flag = 'Y' THEN
2915: fii_util.put_line(' ');
2916: fii_util.put_line('Insert carryforward data into fii_gl_enc_carryfwd_t...');
2917: fii_util.start_timer;
2918: fii_util.put_line('');
2919: END IF;
2920:
2921: -- Find out the encumbrance type ID for the seeded encumbrance types
2922: SELECT encumbrance_type_id
3024: ' chart_of_accounts_id, currency_code, year_start_date ';
3025:
3026: -- Print out the dynamic SQL statements if running in debug mode
3027: IF g_debug_flag = 'Y' THEN
3028: fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
3029:
3030: FII_MESSAGE.Write_Log
3031: (msg_name => 'FII_ROUTINE_VAL',
3032: token_num => 3 ,
3050: l_start_date;
3051: END IF;
3052:
3053: IF g_debug_flag = 'Y' THEN
3054: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
3055: ' rows into FII_GL_ENC_CARRYFWD_T');
3056: fii_util.stop_timer;
3057: fii_util.print_timer('Duration');
3058: END IF;
3052:
3053: IF g_debug_flag = 'Y' THEN
3054: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
3055: ' rows into FII_GL_ENC_CARRYFWD_T');
3056: fii_util.stop_timer;
3057: fii_util.print_timer('Duration');
3058: END IF;
3059:
3060: ---------------------------------------------------------------------------
3053: IF g_debug_flag = 'Y' THEN
3054: fii_util.put_line('Inserted '||SQL%ROWCOUNT||
3055: ' rows into FII_GL_ENC_CARRYFWD_T');
3056: fii_util.stop_timer;
3057: fii_util.print_timer('Duration');
3058: END IF;
3059:
3060: ---------------------------------------------------------------------------
3061: -- Needs to commit before reading from table after inserting in parallel
3067: ---------------------------------------------------------------------------
3068: g_phase := 'Validate currencies used in encumbrance carry forward amounts';
3069:
3070: IF g_debug_flag = 'Y' THEN
3071: fii_util.put_line(' ');
3072: fii_util.put_line('Validate currencies used in encumbrance carry forward');
3073: fii_util.put_line('');
3074: END IF;
3075:
3068: g_phase := 'Validate currencies used in encumbrance carry forward amounts';
3069:
3070: IF g_debug_flag = 'Y' THEN
3071: fii_util.put_line(' ');
3072: fii_util.put_line('Validate currencies used in encumbrance carry forward');
3073: fii_util.put_line('');
3074: END IF;
3075:
3076: l_print_hdr1 := FALSE;
3069:
3070: IF g_debug_flag = 'Y' THEN
3071: fii_util.put_line(' ');
3072: fii_util.put_line('Validate currencies used in encumbrance carry forward');
3073: fii_util.put_line('');
3074: END IF;
3075:
3076: l_print_hdr1 := FALSE;
3077:
3082: IF (NOT l_print_hdr1) THEN
3083: -- Set the return code so the program will ends with warning.
3084: l_ret_code := 'W';
3085:
3086: FII_UTIL.Write_Output (' ');
3087: FII_MESSAGE.Write_Log (msg_name => 'FII_INV_ENC_CURR_CODE',
3088: token_num => 0);
3089: FII_MESSAGE.Write_Log (msg_name => 'FII_REFER_TO_OUTPUT',
3090: token_num => 0);
3087: FII_MESSAGE.Write_Log (msg_name => 'FII_INV_ENC_CURR_CODE',
3088: token_num => 0);
3089: FII_MESSAGE.Write_Log (msg_name => 'FII_REFER_TO_OUTPUT',
3090: token_num => 0);
3091: FII_UTIL.put_line('');
3092: FII_MESSAGE.Write_Output (msg_name => 'FII_INV_ENC_CURR_CODE',
3093: token_num => 0);
3094: l_print_hdr1 := TRUE;
3095: END IF;
3093: token_num => 0);
3094: l_print_hdr1 := TRUE;
3095: END IF;
3096:
3097: FII_UTIL.Write_Output (l_sob_name || ' (' || l_currency_code || ')');
3098: END LOOP;
3099:
3100:
3101: ---------------------------------------------------------------------------
3108: ---------------------------------------------------------------------------
3109: g_phase := 'Roll up encumbrance carry forward data into month/qtr/yr slices';
3110:
3111: IF g_debug_flag = 'Y' THEN
3112: fii_util.put_line(' ');
3113: fii_util.put_line(
3114: 'Rollup encumbrance carry forward data into month/quarter/year slices...');
3115: fii_util.put_line('');
3116: fii_util.start_timer;
3109: g_phase := 'Roll up encumbrance carry forward data into month/qtr/yr slices';
3110:
3111: IF g_debug_flag = 'Y' THEN
3112: fii_util.put_line(' ');
3113: fii_util.put_line(
3114: 'Rollup encumbrance carry forward data into month/quarter/year slices...');
3115: fii_util.put_line('');
3116: fii_util.start_timer;
3117: fii_util.put_line('');
3111: IF g_debug_flag = 'Y' THEN
3112: fii_util.put_line(' ');
3113: fii_util.put_line(
3114: 'Rollup encumbrance carry forward data into month/quarter/year slices...');
3115: fii_util.put_line('');
3116: fii_util.start_timer;
3117: fii_util.put_line('');
3118: END IF;
3119:
3112: fii_util.put_line(' ');
3113: fii_util.put_line(
3114: 'Rollup encumbrance carry forward data into month/quarter/year slices...');
3115: fii_util.put_line('');
3116: fii_util.start_timer;
3117: fii_util.put_line('');
3118: END IF;
3119:
3120: l_sqlstmt :=
3113: fii_util.put_line(
3114: 'Rollup encumbrance carry forward data into month/quarter/year slices...');
3115: fii_util.put_line('');
3116: fii_util.start_timer;
3117: fii_util.put_line('');
3118: END IF;
3119:
3120: l_sqlstmt :=
3121: ' INSERT /*+ append parallel(fii_gl_enc_carryfwd_t) */ '||
3157: ' WHERE ent_year_id IS NOT NULL ';
3158:
3159: -- Print out the dynamic SQL statements if running in debug mode
3160: IF g_debug_flag = 'Y' THEN
3161: fii_util.put_line('l_sqlstmt = '|| l_sqlstmt);
3162:
3163: FII_MESSAGE.Write_Log
3164: (msg_name => 'FII_ROUTINE_VAL',
3165: token_num => 3 ,
3173:
3174: EXECUTE IMMEDIATE l_sqlstmt USING g_prim_currency;
3175:
3176: IF g_debug_flag = 'Y' THEN
3177: fii_util.put_line('Inserted '|| SQL%ROWCOUNT||
3178: ' rows into FII_GL_ENC_CARRYFWD_T');
3179: fii_util.stop_timer;
3180: fii_util.print_timer('Duration');
3181: END IF;
3175:
3176: IF g_debug_flag = 'Y' THEN
3177: fii_util.put_line('Inserted '|| SQL%ROWCOUNT||
3178: ' rows into FII_GL_ENC_CARRYFWD_T');
3179: fii_util.stop_timer;
3180: fii_util.print_timer('Duration');
3181: END IF;
3182:
3183: ---------------------------------------------------------------------------
3176: IF g_debug_flag = 'Y' THEN
3177: fii_util.put_line('Inserted '|| SQL%ROWCOUNT||
3178: ' rows into FII_GL_ENC_CARRYFWD_T');
3179: fii_util.stop_timer;
3180: fii_util.print_timer('Duration');
3181: END IF;
3182:
3183: ---------------------------------------------------------------------------
3184: -- Needs to commit before reading from table after inserting in parallel
3199:
3200: EXCEPTION
3201: WHEN OTHERS Then
3202: g_retcode := -1;
3203: FII_UTIL.put_line('
3204: ----------------------------
3205: Error in Function: INSERT_ENC_CARRYFWD
3206: Phase: ' || g_phase || '
3207: Message: '||sqlerrm);
3269: -----------------------------------------------
3270: -- Do the necessary setups for logging and
3271: -- output
3272: -----------------------------------------------
3273: l_dir := FII_UTIL.get_utl_file_dir;
3274:
3275: ------------------------------------------------
3276: -- Initialize API will fetch the FII_DEBUG_MODE
3277: -- profile option and intialize g_debug variable
3278: -- accordingly. It will also read in profile
3279: -- option BIS_DEBUG_LOG_DIRECTORY to find out
3280: -- the log directory
3281: ------------------------------------------------
3282: g_phase := 'Calling FII_UTIL.initialize';
3283: IF g_program_type = 'I' THEN
3284: FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_C');
3285: ELSIF g_program_type = 'L' THEN
3286: FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_L');
3280: -- the log directory
3281: ------------------------------------------------
3282: g_phase := 'Calling FII_UTIL.initialize';
3283: IF g_program_type = 'I' THEN
3284: FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_C');
3285: ELSIF g_program_type = 'L' THEN
3286: FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_L');
3287: END IF;
3288:
3282: g_phase := 'Calling FII_UTIL.initialize';
3283: IF g_program_type = 'I' THEN
3284: FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_C');
3285: ELSIF g_program_type = 'L' THEN
3286: FII_UTIL.initialize('FII_GL_SUM.log','FII_GL_SUM.out',l_dir, 'FII_GL_JE_B_L');
3287: END IF;
3288:
3289: -----------------------------------------------------
3290: -- Calling BIS API to do common set ups
3329: -- everything before starts.
3330: ------------------------------------------------
3331: IF p_program_type = 'L' THEN
3332: IF g_debug_flag = 'Y' then
3333: FII_UTIL.put_line('Running in Initial Load mode, truncate STG, summary and other processing tables.');
3334: END IF;
3335: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_STG');
3336: TRUNCATE_TABLE('FII_GL_JE_SUMMARY_B');
3337: TRUNCATE_TABLE('FII_GL_PROCESSED_HEADER_IDS');
3356:
3357: IF (l_slg_chg = 'TRUE') THEN
3358: FII_MESSAGE.write_output (msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
3359: FII_MESSAGE.write_log (msg_name => 'FII_TRUNC_SUMMARY', token_num => 0);
3360: ----FII_UTIL.put_line('Source Ledger Group setup has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
3361: END IF;
3362:
3363: IF (l_prd_chg = 'TRUE') THEN
3364: FII_MESSAGE.write_output (msg_name => 'FII_TRUNC_SUMMARY_PRD', token_num => 0);
3362:
3363: IF (l_prd_chg = 'TRUE') THEN
3364: FII_MESSAGE.write_output (msg_name => 'FII_TRUNC_SUMMARY_PRD', token_num => 0);
3365: FII_MESSAGE.write_log (msg_name => 'FII_TRUNC_SUMMARY_PRD', token_num => 0);
3366: ----FII_UTIL.put_line('Product Assignment has changed. Please run the Request Set in the Initial mode to repopulate the summaries.');
3367: END IF;
3368:
3369: -- should fail the program if either slg or prd changed
3370: IF l_slg_chg = 'TRUE' OR l_prd_chg = 'TRUE' THEN
3456:
3457: END IF;
3458:
3459: if g_debug_flag = 'Y' then
3460: FII_UTIL.put_line('User submitted start date range: ' || l_start_date);
3461: FII_UTIL.put_line('User submitted end date range: ' || l_end_date);
3462: end if;
3463:
3464: l_period_start_date := l_start_date;
3457: END IF;
3458:
3459: if g_debug_flag = 'Y' then
3460: FII_UTIL.put_line('User submitted start date range: ' || l_start_date);
3461: FII_UTIL.put_line('User submitted end date range: ' || l_end_date);
3462: end if;
3463:
3464: l_period_start_date := l_start_date;
3465: l_period_end_date := l_end_date;
3464: l_period_start_date := l_start_date;
3465: l_period_end_date := l_end_date;
3466:
3467: if g_debug_flag = 'Y' then
3468: FII_UTIL.put_line('Collection Period start date: ' || l_period_start_date);
3469: FII_UTIL.put_line('Collection Period end date: ' || l_period_end_date);
3470: end if;
3471:
3472: ----------------------------------------------------------
3465: l_period_end_date := l_end_date;
3466:
3467: if g_debug_flag = 'Y' then
3468: FII_UTIL.put_line('Collection Period start date: ' || l_period_start_date);
3469: FII_UTIL.put_line('Collection Period end date: ' || l_period_end_date);
3470: end if;
3471:
3472: ----------------------------------------------------------
3473: -- Determine if we need to resume. If there are records
3478: -- in the staging table
3479: ----------------------------------------------------------
3480: g_phase := 'Determine if we need to resume';
3481: if g_debug_flag = 'Y' then
3482: FII_UTIL.put_line(g_phase);
3483: end if;
3484:
3485: SELECT COUNT(*)
3486: INTO stg_count
3537: -- Calling CLEAN_UP procedure to clean up all processing
3538: -- tables
3539: --------------------------------------------------------------
3540: if g_debug_flag = 'Y' then
3541: FII_UTIL.put_line('');
3542: FII_UTIL.put_line('Cleaning up processing tables before actual processing start');
3543: FII_UTIL.put_line('------------------------------------------------------------');
3544: end if;
3545: CLEAN_UP;
3538: -- tables
3539: --------------------------------------------------------------
3540: if g_debug_flag = 'Y' then
3541: FII_UTIL.put_line('');
3542: FII_UTIL.put_line('Cleaning up processing tables before actual processing start');
3543: FII_UTIL.put_line('------------------------------------------------------------');
3544: end if;
3545: CLEAN_UP;
3546:
3539: --------------------------------------------------------------
3540: if g_debug_flag = 'Y' then
3541: FII_UTIL.put_line('');
3542: FII_UTIL.put_line('Cleaning up processing tables before actual processing start');
3543: FII_UTIL.put_line('------------------------------------------------------------');
3544: end if;
3545: CLEAN_UP;
3546:
3547: if g_debug_flag = 'Y' then
3544: end if;
3545: CLEAN_UP;
3546:
3547: if g_debug_flag = 'Y' then
3548: FII_UTIL.put_line('------------------------------------------------------------');
3549: FII_UTIL.put_line('');
3550: end if;
3551:
3552: ---------------------------------------------------------
3545: CLEAN_UP;
3546:
3547: if g_debug_flag = 'Y' then
3548: FII_UTIL.put_line('------------------------------------------------------------');
3549: FII_UTIL.put_line('');
3550: end if;
3551:
3552: ---------------------------------------------------------
3553: -- After we do initial clean up, we will set this flag to
3561: -- FII_NEW_GL_HEADER_ID_TEMP
3562: ----------------------------------------------------------------
3563: g_phase := 'Identify New Journal Headers to process';
3564: if g_debug_flag = 'Y' then
3565: FII_UTIL.put_line(g_phase);
3566: end if;
3567:
3568: --------------------------------------------------------
3569: -- NEW_JOURNALS will identify the new journals which
3575: l_ids_count := NEW_JOURNALS(l_period_start_date, l_period_end_date);
3576:
3577: IF (l_ids_count = 0) THEN
3578: if g_debug_flag = 'Y' then
3579: FII_UTIL.put_line('No Journal Entries to Process, exit.');
3580: end if;
3581: RETURN;
3582: END IF;
3583:
3597: -- CCID dimension.
3598: ----------------------------------------------------------------
3599: g_phase := 'Verifying if CCID Dimension is up to date';
3600: if g_debug_flag = 'Y' then
3601: FII_UTIL.put_line(g_phase);
3602: end if;
3603:
3604: VERIFY_CCID_UP_TO_DATE;
3605:
3605:
3606: IF (g_industry = 'G') THEN
3607: g_phase := 'Populate encumbrance type mapping table';
3608: if g_debug_flag = 'Y' then
3609: FII_UTIL.put_line(g_phase);
3610: end if;
3611:
3612: POPULATE_ENCUM_MAPPING;
3613: END IF;
3625: ----------------------------------------------------------------
3626: g_phase := 'Calling Routine Register_Jobs';
3627:
3628: if g_debug_flag = 'Y' then
3629: FII_UTIL.put_line(g_phase);
3630: end if;
3631:
3632: Register_Jobs();
3633:
3648: l_worker(i) := LAUNCH_WORKER(i);
3649: COMMIT;
3650:
3651: if g_debug_flag = 'Y' then
3652: FII_util.put_line(' Worker '||i||' request id: '||l_worker(i));
3653: end if;
3654: END LOOP;
3655:
3656: -- Monitor Child process after launching them
3681: l_tot_cnt
3682: FROM FII_GL_WORKER_JOBS;
3683:
3684: if g_debug_flag = 'Y' then
3685: FII_UTIL.put_line('Job status - Unassigned:'||l_unassigned_cnt||
3686: ' In Process:'||l_wip_cnt||
3687: ' Completed:'||l_completed_cnt||
3688: ' Failed:'||l_failed_cnt);
3689: end if;
3689: end if;
3690:
3691: IF (l_failed_cnt > 0) THEN
3692: g_retcode := -1;
3693: FII_UTIL.put_line('
3694: ---------------------------------
3695: Error in Main Procedure:
3696: Message: At least one of the workers have errored out');
3697: RAISE G_CHILD_PROCESS_ISSUE;
3703: -- have completed. Then we can exit the loop
3704: -- --------------------------------------------
3705: IF (l_tot_cnt = l_completed_cnt) THEN
3706: if g_debug_flag = 'Y' then
3707: FII_UTIL.put_line ('All jobs have completed');
3708: end if;
3709: EXIT;
3710: END IF;
3711:
3729: -- for now
3730: -- --------------------------------------
3731: IF (l_cycle > MAX_LOOP) THEN
3732: g_retcode := -1;
3733: FII_UTIL.put_line('
3734: ---------------------------------
3735: Error in Main Procedure:
3736: Message: No progress have been made for '||MAX_LOOP||' minutes.
3737: Terminating');
3765: g_truncate_stg := FALSE;
3766:
3767: g_phase := 'Fixing missing rates in temporary staging table';
3768: if g_debug_flag = 'Y' then
3769: FII_UTIL.put_line(g_phase);
3770:
3771: FII_UTIL.start_timer;
3772: end if;
3773:
3767: g_phase := 'Fixing missing rates in temporary staging table';
3768: if g_debug_flag = 'Y' then
3769: FII_UTIL.put_line(g_phase);
3770:
3771: FII_UTIL.start_timer;
3772: end if;
3773:
3774: Update FII_GL_JE_SUMMARY_STG stg
3775: SET prim_conversion_rate =
3776: fii_currency.get_global_rate_primary(stg.functional_currency,least(sysdate, stg.effective_date))
3777: WHERE stg.prim_conversion_rate < 0;
3778:
3779: if g_debug_flag = 'Y' then
3780: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
3781: FII_UTIL.stop_timer;
3782: FII_UTIL.print_timer('Duration');
3783:
3784: FII_UTIL.start_timer;
3777: WHERE stg.prim_conversion_rate < 0;
3778:
3779: if g_debug_flag = 'Y' then
3780: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
3781: FII_UTIL.stop_timer;
3782: FII_UTIL.print_timer('Duration');
3783:
3784: FII_UTIL.start_timer;
3785: end if;
3778:
3779: if g_debug_flag = 'Y' then
3780: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
3781: FII_UTIL.stop_timer;
3782: FII_UTIL.print_timer('Duration');
3783:
3784: FII_UTIL.start_timer;
3785: end if;
3786:
3780: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for primary currency rates in staging table');
3781: FII_UTIL.stop_timer;
3782: FII_UTIL.print_timer('Duration');
3783:
3784: FII_UTIL.start_timer;
3785: end if;
3786:
3787: commit; --use commit after print out correct SQL%ROWCOUNT
3788:
3791: fii_currency.get_global_rate_secondary(stg.functional_currency,least(sysdate, stg.effective_date))
3792: WHERE stg.sec_conversion_rate < 0;
3793:
3794: if g_debug_flag = 'Y' then
3795: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
3796: FII_UTIL.stop_timer;
3797: FII_UTIL.print_timer('Duration');
3798: end if;
3799:
3792: WHERE stg.sec_conversion_rate < 0;
3793:
3794: if g_debug_flag = 'Y' then
3795: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
3796: FII_UTIL.stop_timer;
3797: FII_UTIL.print_timer('Duration');
3798: end if;
3799:
3800: commit; --use commit after print out correct SQL%ROWCOUNT
3793:
3794: if g_debug_flag = 'Y' then
3795: FII_UTIL.put_line('Updated ' || SQL%ROWCOUNT || ' records for secondary currency rates in staging table');
3796: FII_UTIL.stop_timer;
3797: FII_UTIL.print_timer('Duration');
3798: end if;
3799:
3800: commit; --use commit after print out correct SQL%ROWCOUNT
3801:
3809: -- table.
3810: -----------------------------------------------------------------
3811: g_phase:= 'Summarization Error Check';
3812: if g_debug_flag = 'Y' then
3813: FII_UTIL.put_line(g_phase);
3814: end if;
3815:
3816: Summary_err_check (p_program_type);
3817:
3828: -- YTD into the FII_GL_JE_SUMMARY_STG table.
3829: -------------------------------------------------------------
3830: g_phase := 'Aggregating summarized data';
3831: if g_debug_flag = 'Y' then
3832: FII_UTIL.put_line('');
3833: FII_UTIL.put_line(g_phase);
3834: end if;
3835:
3836: IF p_program_type = 'I' THEN
3829: -------------------------------------------------------------
3830: g_phase := 'Aggregating summarized data';
3831: if g_debug_flag = 'Y' then
3832: FII_UTIL.put_line('');
3833: FII_UTIL.put_line(g_phase);
3834: end if;
3835:
3836: IF p_program_type = 'I' THEN
3837:
3851: -- FII_GL_JE_SUMMARY_B table.
3852: --------------------------------------------------------
3853: g_phase := 'Merging records into base summary table';
3854: if g_debug_flag = 'Y' then
3855: FII_UTIL.put_line('');
3856: FII_UTIL.put_line(g_phase);
3857: end if;
3858:
3859: IF p_program_type = 'I' THEN
3852: --------------------------------------------------------
3853: g_phase := 'Merging records into base summary table';
3854: if g_debug_flag = 'Y' then
3855: FII_UTIL.put_line('');
3856: FII_UTIL.put_line(g_phase);
3857: end if;
3858:
3859: IF p_program_type = 'I' THEN
3860: Merge;
3869: -- Jornals_processed.
3870: -----------------------------------------------------------------
3871: g_phase := 'Inserting processed JE Header IDs';
3872: if g_debug_flag = 'Y' then
3873: FII_UTIL.put_line('');
3874: FII_UTIL.put_line(g_phase);
3875: end if;
3876:
3877: Journals_processed;
3870: -----------------------------------------------------------------
3871: g_phase := 'Inserting processed JE Header IDs';
3872: if g_debug_flag = 'Y' then
3873: FII_UTIL.put_line('');
3874: FII_UTIL.put_line(g_phase);
3875: end if;
3876:
3877: Journals_processed;
3878:
3923: Exception
3924: WHEN OTHERS Then
3925: g_retcode := -1;
3926: clean_up;
3927: FII_UTIL.put_line('
3928: Error in Function: Main
3929: Phase: '|| g_phase || '
3930: Message: ' || sqlerrm);
3931: retcode := g_retcode;
3962: g_phase := 'Calling child_setup';
3963: CHILD_SETUP('FII_GL_SUM_SUBWORKER'||p_worker_no);
3964:
3965: if g_debug_flag = 'Y' then
3966: FII_UTIL.put_line(' ');
3967: FII_UTIL.put_timestamp;
3968: FII_UTIL.put_line('Worker '||p_worker_no||' Starting');
3969: end if;
3970:
3963: CHILD_SETUP('FII_GL_SUM_SUBWORKER'||p_worker_no);
3964:
3965: if g_debug_flag = 'Y' then
3966: FII_UTIL.put_line(' ');
3967: FII_UTIL.put_timestamp;
3968: FII_UTIL.put_line('Worker '||p_worker_no||' Starting');
3969: end if;
3970:
3971: -- ------------------------------------------
3964:
3965: if g_debug_flag = 'Y' then
3966: FII_UTIL.put_line(' ');
3967: FII_UTIL.put_timestamp;
3968: FII_UTIL.put_line('Worker '||p_worker_no||' Starting');
3969: end if;
3970:
3971: -- ------------------------------------------
3972: -- Loop thru job list
3985: l_total_cnt
3986: FROM FII_GL_WORKER_JOBS;
3987:
3988: if g_debug_flag = 'Y' then
3989: FII_UTIL.put_line('Job status - Unassigned: '||l_unassigned_cnt||
3990: ' In Process: '||l_wip_cnt||
3991: ' Completed: '||l_completed_cnt||
3992: ' Failed: '||l_failed_cnt||
3993: ' Total: '|| l_total_cnt);
3994: end if;
3995:
3996: IF (l_failed_cnt > 0) THEN
3997: if g_debug_flag = 'Y' then
3998: FII_UTIL.put_line('');
3999: FII_UTIL.put_line('Another worker have errored out. Stop processing.');
4000: end if;
4001: EXIT;
4002: ELSIF (l_unassigned_cnt = 0) THEN
3995:
3996: IF (l_failed_cnt > 0) THEN
3997: if g_debug_flag = 'Y' then
3998: FII_UTIL.put_line('');
3999: FII_UTIL.put_line('Another worker have errored out. Stop processing.');
4000: end if;
4001: EXIT;
4002: ELSIF (l_unassigned_cnt = 0) THEN
4003: if g_debug_flag = 'Y' then
4000: end if;
4001: EXIT;
4002: ELSIF (l_unassigned_cnt = 0) THEN
4003: if g_debug_flag = 'Y' then
4004: FII_UTIL.put_line('');
4005: FII_UTIL.put_line('No more jobs left. Terminating.');
4006: end if;
4007: EXIT;
4008: ELSIF (l_completed_cnt = l_total_cnt) THEN
4001: EXIT;
4002: ELSIF (l_unassigned_cnt = 0) THEN
4003: if g_debug_flag = 'Y' then
4004: FII_UTIL.put_line('');
4005: FII_UTIL.put_line('No more jobs left. Terminating.');
4006: end if;
4007: EXIT;
4008: ELSIF (l_completed_cnt = l_total_cnt) THEN
4009: if g_debug_flag = 'Y' then
4006: end if;
4007: EXIT;
4008: ELSIF (l_completed_cnt = l_total_cnt) THEN
4009: if g_debug_flag = 'Y' then
4010: FII_UTIL.put_line('');
4011: FII_UTIL.put_line('All jobs completed, no more job. Terminating');
4012: end if;
4013: EXIT;
4014: ELSIF (l_unassigned_cnt > 0) THEN
4007: EXIT;
4008: ELSIF (l_completed_cnt = l_total_cnt) THEN
4009: if g_debug_flag = 'Y' then
4010: FII_UTIL.put_line('');
4011: FII_UTIL.put_line('All jobs completed, no more job. Terminating');
4012: end if;
4013: EXIT;
4014: ELSIF (l_unassigned_cnt > 0) THEN
4015: UPDATE FII_GL_WORKER_JOBS
4017: worker_number = p_worker_no
4018: WHERE status = 'UNASSIGNED'
4019: AND rownum < 2;
4020: if g_debug_flag = 'Y' then
4021: FII_UTIL.put_line('Taking job from job queue');
4022: end if;
4023: l_count := sql%rowcount;
4024: COMMIT;
4025: END IF;
4035: DECLARE
4036: BEGIN
4037: g_phase := 'Getting ID range from FII_GL_WORKER_JOBS table';
4038: if g_debug_flag = 'Y' then
4039: FII_UTIL.put_line(g_phase);
4040: end if;
4041:
4042: SELECT start_range,
4043: end_range
4053: -- Passing start range and end range parameters
4054: --------------------------------------------------
4055: g_phase := 'Inserting day level summarized records';
4056: if g_debug_flag = 'Y' then
4057: FII_UTIL.put_line(g_phase);
4058: end if;
4059:
4060: Summarize_Day(l_start_range,
4061: l_end_range);
4067: -- to complete
4068: -----------------------------------------------------
4069: g_phase:='Updating job status in FII_GL_WORKER_JOBS table';
4070: if g_debug_flag = 'Y' then
4071: FII_UTIL.put_line(g_phase);
4072: end if;
4073:
4074: UPDATE FII_GL_WORKER_JOBS
4075: SET status = 'COMPLETED'
4099:
4100: EXCEPTION
4101: WHEN OTHERS THEN
4102: retcode:= g_retcode;
4103: FII_UTIL.put_line('
4104: ---------------------------------
4105: Error in Procedure: WORKER
4106: Phase: '|| g_phase || '
4107: Message: '||sqlerrm);