[Home] [Help]
826: 'FII_RECONV_CACHE_RATE_STEP', NULL
827: , 'PROD_NAME', p_gl_name));
828:
829: -- Insert rates to FII_RECONV_RATES based on the transactions of
830: -- FII_GL_JE_SUMMARY_B within the passed date range
831: INSERT INTO FII_RECONV_RATES
832: ( REQUEST_ID, RATE_DATE_ID, RATE_DATE, FROM_CURRENCY
833: , PRIMARY_RATE, SECONDARY_RATE)
834: (SELECT DISTINCT
841: , t.report_date, p_secondary_rate_type))
842: FROM FII_TIME_DAY t, GL_LEDGERS_PUBLIC_V sob
843: WHERE t.report_date_julian BETWEEN p_from_date_id and p_to_date_id
844: AND EXISTS (SELECT 'This date has GL transaction'
845: FROM FII_GL_JE_SUMMARY_B gl
846: WHERE gl.time_id = t.report_date_julian
847: -- Bug fix 4637659: Added to check PERIOD_TYPE_ID since it is the first column
848: -- of the changed FII_GL_JE_SUMMARY_B_U1
849: AND gl.period_type_id = 1
844: AND EXISTS (SELECT 'This date has GL transaction'
845: FROM FII_GL_JE_SUMMARY_B gl
846: WHERE gl.time_id = t.report_date_julian
847: -- Bug fix 4637659: Added to check PERIOD_TYPE_ID since it is the first column
848: -- of the changed FII_GL_JE_SUMMARY_B_U1
849: AND gl.period_type_id = 1
850: AND gl.ledger_id = sob.ledger_id));
851:
852: -- LOG: Print the number of cached rates for GL
1008: -- PROCEDURE
1009: -- `()
1010: --
1011: -- DESCRIPTION:
1012: -- It will reconvert global amounts for the GL base table FII_GL_JE_SUMMARY_B.
1013: PROCEDURE reconvert_gl( errbuf IN OUT NOCOPY VARCHAR2
1014: , retcode IN OUT NOCOPY VARCHAR2
1015: , p_request_id IN NUMBER
1016: , p_user_id IN NUMBER
1053: l_process_step := 'CHECK_USE_ID_COLS';
1054: BEGIN
1055: -- If it can pass through this SQL, it means we should use ID columns
1056:
1057: -- Build the SELECT SQL to check FII_GL_JE_SUMMARY_B
1058: l_processSQL(1) := 'SELECT company_id, cost_center_id, fin_category_id';
1059: l_processSQL(2) := 'FROM FII_GL_JE_SUMMARY_B WHERE rownum = 1';
1060:
1061: -- Open cursor
1055: -- If it can pass through this SQL, it means we should use ID columns
1056:
1057: -- Build the SELECT SQL to check FII_GL_JE_SUMMARY_B
1058: l_processSQL(1) := 'SELECT company_id, cost_center_id, fin_category_id';
1059: l_processSQL(2) := 'FROM FII_GL_JE_SUMMARY_B WHERE rownum = 1';
1060:
1061: -- Open cursor
1062: l_cursorID := DBMS_SQL.OPEN_CURSOR;
1063: -- Parse the SQL
1078: l_processed_rows := DBMS_SQL.EXECUTE(l_cursorID);
1079: -- Close cursor
1080: DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1081:
1082: -- It can select the ID columns from FII_GL_JE_SUMMARY_B and
1083: -- FII_RECONV_GL_ROLLUP_GT, so set l_use_id_columns to TRUE
1084: l_use_id_columns := TRUE;
1085: FII_UTIL.debug_line('ID columns exists');
1086: EXCEPTION
1189:
1190: -- FROM clause
1191: l_process_step := 'BUILD_DAILY_SQL_FROM';
1192: i := i + 1;
1193: l_processSQL(i) := ' FROM FII_GL_JE_SUMMARY_B g, FII_RECONV_RATES r';
1194:
1195: -- WHERE clauses
1196: l_process_step := 'BUILD_DAILY_SQL_WHERE';
1197: i := i + 1;
1593: l_process_step := 'CLOSE_OTHER_SQL';
1594: DBMS_SQL.CLOSE_CURSOR(l_cursorID);
1595:
1596: --
1597: -- 3.5 Updated FII_GL_JE_SUMMARY_B with the global amount differences,
1598: --
1599:
1600: -- Build the UPDATE SQL to update the differences to FII_GL_JE_SUMMARY_B
1601: l_process_step := 'DELETE_SQL_BUFFER';
1596: --
1597: -- 3.5 Updated FII_GL_JE_SUMMARY_B with the global amount differences,
1598: --
1599:
1600: -- Build the UPDATE SQL to update the differences to FII_GL_JE_SUMMARY_B
1601: l_process_step := 'DELETE_SQL_BUFFER';
1602: -- Reset line count to 1 and delete the SQL buffer since the UPDATE SQL is
1603: -- totally different with previous INSERT SQLs
1604: l_processSQL.delete;
1605: i := 1;
1606:
1607: -- UPDATE clause
1608: l_process_step := 'BUILD_UPDATE_SQL';
1609: l_processSQL(i) := 'UPDATE /*+ parallel(g) full(g) */ FII_GL_JE_SUMMARY_B g';
1610: -- Bug fix 4637659: START
1611: i := i + 1;
1612: l_processSQL(i) :=
1613: 'SET ( PRIM_AMOUNT_G, COMMITTED_AMOUNT_PRIM, OBLIGATED_AMOUNT_PRIM';