[Home] [Help]
262: rownum SEQUENCE_ID
263: FROM
264: (SELECT distinct Invoice_ID
265: FROM (SELECT Key_Value1 INVOICE_ID
266: FROM ap_dbi_log log
267: WHERE Table_Name IN ('AP_INVOICES', 'AP_INVOICE_DISTRIBUTIONS')
268: AND Creation_Date >= g_timestamp2
269: AND Creation_Date < g_timestamp1
270: UNION
268: AND Creation_Date >= g_timestamp2
269: AND Creation_Date < g_timestamp1
270: UNION
271: SELECT Key_Value1_ID Invoice_ID
272: FROM FII_AP_DBI_Log_Exp_T
273: WHERE Table_Name IN ('AP_INVOICES', 'AP_INVOICE_DISTRIBUTIONS')));
274:
275: l_count := SQL%ROWCOUNT;
276:
810: FII_UTIL.put_line('Deleted ' || SQL%ROWCOUNT || ' records from FII_AP_INV_DIST_F');
811: FII_UTIL.put_line('');
812: end if;
813:
814: FOR i IN 0..31 LOOP --i represents the partition of ap_dbi_log.
815:
816: IF g_timestamp3 + 30 >= g_timestamp1 AND g_oper_imp_prof_flag = 'Y' THEN --Copy records into Operations log table.
817:
818: g_state := 'Copying records from partition ' || i || ' into FII_AP_DBI_LOG_PS_T.';
814: FOR i IN 0..31 LOOP --i represents the partition of ap_dbi_log.
815:
816: IF g_timestamp3 + 30 >= g_timestamp1 AND g_oper_imp_prof_flag = 'Y' THEN --Copy records into Operations log table.
817:
818: g_state := 'Copying records from partition ' || i || ' into FII_AP_DBI_LOG_PS_T.';
819:
820: INSERT INTO FII_AP_DBI_LOG_PS_T(
821: Table_Name,
822: Operation_Flag,
816: IF g_timestamp3 + 30 >= g_timestamp1 AND g_oper_imp_prof_flag = 'Y' THEN --Copy records into Operations log table.
817:
818: g_state := 'Copying records from partition ' || i || ' into FII_AP_DBI_LOG_PS_T.';
819:
820: INSERT INTO FII_AP_DBI_LOG_PS_T(
821: Table_Name,
822: Operation_Flag,
823: Key_Value1_ID,
824: Key_Value2_ID,
835: Last_Updated_By,
836: Last_Update_Login,
837: Creation_Date,
838: Last_Update_Date
839: FROM AP_DBI_LOG
840: WHERE Partition_ID = i
841: AND Creation_Date >= g_timestamp2
842: AND Creation_Date < g_timestamp1;
843: END IF;
842: AND Creation_Date < g_timestamp1;
843: END IF;
844:
845: IF NOT (i = g_act_part1 OR i = g_act_part2) THEN --This is a non-active partition.
846: EXECUTE IMMEDIATE 'ALTER TABLE ' || g_ap_schema || '.AP_DBI_LOG TRUNCATE PARTITION P' || to_char(i);
847: END IF;
848:
849: END LOOP;
850:
847: END IF;
848:
849: END LOOP;
850:
851: TRUNCATE_TABLE('FII_AP_DBI_LOG_EXP_T');
852:
853: COMMIT;
854:
855: EXCEPTION
1380:
1381: commit;
1382:
1383: IF g_oper_imp_prof_flag = 'Y' THEN
1384: FOR i IN 0..31 LOOP --i represents the partition of ap_dbi_log.
1385:
1386: IF g_timestamp3 + 30 >= g_timestamp1 THEN --Copy records into Operations log table.
1387: INSERT INTO FII_AP_DBI_LOG_PS_T(
1388: Table_Name,
1383: IF g_oper_imp_prof_flag = 'Y' THEN
1384: FOR i IN 0..31 LOOP --i represents the partition of ap_dbi_log.
1385:
1386: IF g_timestamp3 + 30 >= g_timestamp1 THEN --Copy records into Operations log table.
1387: INSERT INTO FII_AP_DBI_LOG_PS_T(
1388: Table_Name,
1389: Operation_Flag,
1390: Key_Value1_ID,
1391: Key_Value2_ID,
1402: Last_Updated_By,
1403: Last_Update_Login,
1404: Creation_Date,
1405: Last_Update_Date
1406: FROM AP_DBI_LOG
1407: WHERE Partition_ID = i
1408: AND Creation_Date >= g_timestamp2
1409: AND Creation_Date < g_timestamp1;
1410: END IF;
1409: AND Creation_Date < g_timestamp1;
1410: END IF;
1411:
1412: IF NOT (i = g_act_part1 OR i = g_act_part2) THEN --This is a non-active partition.
1413: EXECUTE IMMEDIATE 'ALTER TABLE ' || g_ap_schema || '.AP_DBI_LOG TRUNCATE PARTITION P' || to_char(i);
1414: END IF;
1415:
1416: END LOOP;
1417:
1416: END LOOP;
1417:
1418: END IF;
1419:
1420: TRUNCATE_TABLE('FII_AP_DBI_LOG_EXP_T');
1421:
1422: COMMIT;
1423:
1424: END INSERT_INTO_SUMMARY;
1706: ---------------------------------------------------------
1707: g_truncate_id := 'N';
1708:
1709: ------------------------------------------------------------
1710: --Get timestamps used to maintain ap_dbi_log.
1711: --g_timestamp1 - current timestamp.
1712: --g_timestamp2 - last Payables Operation/Expenses load.
1713: --g_timestamp3 - last Payables Operations load, if implemented.
1714: --g_timestamp4 - last Payables Expenses load.
1712: --g_timestamp2 - last Payables Operation/Expenses load.
1713: --g_timestamp3 - last Payables Operations load, if implemented.
1714: --g_timestamp4 - last Payables Expenses load.
1715: -------------------------------------------------------------
1716: g_state := 'Defining timestamps to maintain ap_dbi_log.';
1717: IF g_debug_flag = 'Y' then
1718: FII_UTIL.put_line('');
1719: FII_UTIL.put_line(g_state);
1720: fii_util.put_line('');
1836:
1837: IF p_program_type = 'L' THEN
1838:
1839: IF g_oper_imp_prof_flag = 'N' THEN
1840: g_state := 'Truncating AP_DBI_LOG.';
1841: EXECUTE IMMEDIATE('TRUNCATE TABLE ' || g_ap_schema || '.AP_DBI_LOG');
1842: END IF;
1843:
1844:
1837: IF p_program_type = 'L' THEN
1838:
1839: IF g_oper_imp_prof_flag = 'N' THEN
1840: g_state := 'Truncating AP_DBI_LOG.';
1841: EXECUTE IMMEDIATE('TRUNCATE TABLE ' || g_ap_schema || '.AP_DBI_LOG');
1842: END IF;
1843:
1844:
1845: INSERT_INTO_STG (NVL(p_parallel_query, 1),
2077: end if;
2078:
2079: IF p_program_type = 'L' THEN
2080: ------------------------------------------------------------
2081: -- Initial Mode. This program will also update AP_DBI_LOG
2082: ------------------------------------------------------------
2083: INSERT_INTO_SUMMARY(p_parallel_query);
2084: ELSE
2085: ------------------------------------------------------------
2082: ------------------------------------------------------------
2083: INSERT_INTO_SUMMARY(p_parallel_query);
2084: ELSE
2085: ------------------------------------------------------------
2086: -- Incremental Mode. This program will also clean up AP_DBI_LOG
2087: ------------------------------------------------------------
2088: POPULATE_AP_BASE_SUM;
2089: END IF;
2090: