[Home] [Help]
38: IS
39: BEGIN
40: if (p_msg_level >= G_MSG_LEVEL) then
41:
42: FND_LOG.STRING(p_msg_level, G_PKG_NAME, p_msg);
43: if FND_GLOBAL.Conc_Request_Id is not null then
44: fnd_file.put_line(FND_FILE.LOG, p_msg);
45: end if;
46:
47: end if;
48:
49: EXCEPTION
50: WHEN OTHERS THEN
51: LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: ' || sqlerrm);
52: END LogMessage;
53:
54:
55: PROCEDURE populate_uwq_sum_concur (
58: FROM_DATE IN VARCHAR2)
59: IS
60: BEGIN
61: --Bug5691098. Start.
62: LogMessage(FND_LOG.LEVEL_STATEMENT,' Populate_uwq_sum_concur Started.');
63:
64: Insert_Summary(x_errbuf,x_retcode,FROM_DATE,'CP');
65:
66: LogMessage(FND_LOG.LEVEL_STATEMENT,' Populate_uwq_sum_concur Finished.');
62: LogMessage(FND_LOG.LEVEL_STATEMENT,' Populate_uwq_sum_concur Started.');
63:
64: Insert_Summary(x_errbuf,x_retcode,FROM_DATE,'CP');
65:
66: LogMessage(FND_LOG.LEVEL_STATEMENT,' Populate_uwq_sum_concur Finished.');
67: --Bug5691098. End.
68: END populate_uwq_sum_concur;
69:
70:
1883:
1884: insert_conc_req;
1885:
1886: l_from_date := to_date(substr(FROM_DATE, 1, 10), 'YYYY/MM/DD');
1887: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Input FROM_DATE = ' || l_from_date);
1888:
1889: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
1890: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Batch size = ' || l_max_fetches);
1891:
1886: l_from_date := to_date(substr(FROM_DATE, 1, 10), 'YYYY/MM/DD');
1887: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Input FROM_DATE = ' || l_from_date);
1888:
1889: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
1890: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Batch size = ' || l_max_fetches);
1891:
1892: OPEN c_get_level;
1893: FETCH c_get_level INTO l_level;
1894: CLOSE c_get_level;
1891:
1892: OPEN c_get_level;
1893: FETCH c_get_level INTO l_level;
1894: CLOSE c_get_level;
1895: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Level = ' || l_level);
1896:
1897: IF (l_level = 'DELINQUENCY') THEN
1898: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency level is not implemented at this time. Exiting.');
1899: return;
1894: CLOSE c_get_level;
1895: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Level = ' || l_level);
1896:
1897: IF (l_level = 'DELINQUENCY') THEN
1898: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency level is not implemented at this time. Exiting.');
1899: return;
1900: END IF;
1901:
1902: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
1899: return;
1900: END IF;
1901:
1902: l_cash := IEX_UTILITIES.get_cache_value('DEFAULT_EXCHANGE_RATE_TYPE', 'SELECT DEFAULT_EXCHANGE_RATE_TYPE FROM AR_CMGT_SETUP_OPTIONS');
1903: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
1904:
1905: /* Begin Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
1906: if (p_mode = 'CP') then
1907: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
1903: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'DEFAULT_EXCHANGE_RATE_TYPE = ' || l_cash);
1904:
1905: /* Begin Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
1906: if (p_mode = 'CP') then
1907: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
1908: --Start bug 6876187 gnramasa 14th mar 08
1909: --update ar_trx_bal_summary set reference_1 = 1;
1910: --Begin bug#7133605 schekuri 09-Jun-2008
1911: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
1907: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updating Reference_1 of AR_TRX_BAL_SUMMARY for Delinquent Customers');
1908: --Start bug 6876187 gnramasa 14th mar 08
1909: --update ar_trx_bal_summary set reference_1 = 1;
1910: --Begin bug#7133605 schekuri 09-Jun-2008
1911: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = 1...');
1912: BEGIN
1913: OPEN c_cust_account_id_1;
1914: LOOP
1915: FETCH c_cust_account_id_1 BULK COLLECT INTO
1937: EXCEPTION
1938:
1939: WHEN deadlock_detected THEN
1940: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to 1' || sqlerrm);
1941: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to 1.');
1942: ROLLBACK;
1943: l_cust_account_id_1.delete;
1944: if c_cust_account_id_1%ISOPEN then
1945: close c_cust_account_id_1;
1967:
1968: CLOSE C_CUST_ACCOUNT_ID_DL1;
1969: EXCEPTION
1970: WHEN LOCKED_BY_ANOTHER_SESSION THEN
1971: LogMessage(FND_LOG.LEVEL_STATEMENT,'Records corresponding to account id '||l_cust_account_id_1(i)|| ' are locked by another session');
1972: if C_CUST_ACCOUNT_ID_DL1%ISOPEN THEN
1973: CLOSE C_CUST_ACCOUNT_ID_DL1;
1974: END IF;
1975: WHEN OTHERS THEN
1972: if C_CUST_ACCOUNT_ID_DL1%ISOPEN THEN
1973: CLOSE C_CUST_ACCOUNT_ID_DL1;
1974: END IF;
1975: WHEN OTHERS THEN
1976: LogMessage(FND_LOG.LEVEL_STATEMENT,'Error while updating reference_1 to 1 in Dead lock handler '||sqlerrm);
1977: IF C_CUST_ACCOUNT_ID_DL1%ISOPEN THEN
1978: CLOSE C_CUST_ACCOUNT_ID_DL1;
1979: END IF;
1980: END;
1997: WHEN OTHERS THEN
1998: IEX_DEBUG_PUB.LOGMESSAGE(SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = 1');
1999: END;
2000:
2001: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Starting to update ar_trx_bal_summary with reference_1 = Null...');
2002: BEGIN
2003: OPEN c_cust_account_id_n;
2004: LOOP
2005: FETCH c_cust_account_id_n BULK COLLECT INTO
2004: LOOP
2005: FETCH c_cust_account_id_n BULK COLLECT INTO
2006: l_cust_account_id_n LIMIT G_BATCH_SIZE;
2007: IF l_cust_account_id_n.count = 0 THEN
2008: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exit after Update ar_trx_bal_summary on complete with reference_1 = Null...');
2009: CLOSE c_cust_account_id_n;
2010: EXIT;
2011: ELSE
2012: FORALL I IN l_cust_account_id_n.first..l_cust_account_id_n.last
2015: WHERE CUST_ACCOUNT_ID = l_cust_account_id_n(I)
2016: and reference_1='1';
2017: l_cust_account_id_n.delete;
2018: commit;
2019: LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQL%ROWCOUNT || 'Rows updated in ar_trx_bal_summary with reference_1 = Null');
2020: END IF;
2021: END LOOP;
2022: EXCEPTION
2023: WHEN deadlock_detected THEN
2021: END LOOP;
2022: EXCEPTION
2023: WHEN deadlock_detected THEN
2024: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deadlock detected when updating ar_trx_bal_summary.reference to null' || sqlerrm);
2025: LogMessage(FND_LOG.LEVEL_STATEMENT,' Deadlock detected when updating ar_trx_bal_summary.reference to null.');
2026: ROLLBACK;
2027: l_cust_account_id_n.delete;
2028: if c_cust_account_id_n%ISOPEN then
2029: close c_cust_account_id_n;
2051:
2052: CLOSE C_CUST_ACCOUNT_ID_DLN;
2053: EXCEPTION
2054: WHEN LOCKED_BY_ANOTHER_SESSION THEN
2055: LogMessage(FND_LOG.LEVEL_STATEMENT,'Records corresponding to account id '||l_cust_account_id_n(i)|| ' are locked by another session');
2056: if C_CUST_ACCOUNT_ID_DLN%ISOPEN THEN
2057: CLOSE C_CUST_ACCOUNT_ID_DLN;
2058: END IF;
2059: WHEN OTHERS THEN
2056: if C_CUST_ACCOUNT_ID_DLN%ISOPEN THEN
2057: CLOSE C_CUST_ACCOUNT_ID_DLN;
2058: END IF;
2059: WHEN OTHERS THEN
2060: LogMessage(FND_LOG.LEVEL_STATEMENT,'Error while updating reference_1 to 1 in Dead lock handler '||sqlerrm);
2061: IF C_CUST_ACCOUNT_ID_DLN%ISOPEN THEN
2062: CLOSE C_CUST_ACCOUNT_ID_DLN;
2063: END IF;
2064: END;
2077: close c_cust_account_id_1;
2078: end if;
2079: ROLLBACK;
2080: WHEN OTHERS THEN
2081: LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQLERRM || ' Error while updating ar_trx_bal_summary with reference_1 = Null');
2082: END;
2083: --End bug 6876187 gnramasa 14th mar 08
2084: --End bug#7133605 schekuri 09-Jun-2008
2085:
2087: where cust_account_id in
2088: ( select distinct cust_account_id
2089: from iex_delinquencies_all
2090: where status in ('DELINQUENT','PREDELINQUENT'));*/
2091: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Done updating Reference_1 of AR_TRX_BAL_SUMMARY ');
2092:
2093: end if;
2094: /* End Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
2095:
2093: end if;
2094: /* End Kasreeni 3/1/2007 Bug 5905023 We will update everytime instead of once */
2095:
2096: if (l_from_date is null and p_mode = 'CP' ) then --Bug5691098
2097: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fully repopulating IEX_DLN_UWQ_SUMMARY table...');
2098:
2099: OPEN c_get_table;
2100: FETCH c_get_table INTO l_truncate_table;
2101: CLOSE c_get_table;
2098:
2099: OPEN c_get_table;
2100: FETCH c_get_table INTO l_truncate_table;
2101: CLOSE c_get_table;
2102: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'truncate table ' || l_truncate_table);
2103:
2104: EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2105: LogMessage(FND_LOG.LEVEL_STATEMENT,'Truncated whole table');
2106:
2101: CLOSE c_get_table;
2102: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'truncate table ' || l_truncate_table);
2103:
2104: EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2105: LogMessage(FND_LOG.LEVEL_STATEMENT,'Truncated whole table');
2106:
2107: elsiF (l_from_date is not null) then --Bug5691098
2108: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
2109: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
2104: EXECUTE IMMEDIATE 'truncate table ' || l_truncate_table;
2105: LogMessage(FND_LOG.LEVEL_STATEMENT,'Truncated whole table');
2106:
2107: elsiF (l_from_date is not null) then --Bug5691098
2108: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Partially repopulating IEX_DLN_UWQ_SUMMARY table...');
2109: /* Begin gnramasa Modified for bug 5677415 27/11/2006 */
2110: IF l_level = 'CUSTOMER' THEN
2111: delete from IEX_DLN_UWQ_SUMMARY where party_id in
2112: (select party_id from ar_trx_bal_summary trb, hz_cust_accounts hza
2119: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date));
2120: END IF;
2121: /* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2122: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */
2123: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No of rows deleted: ' || SQL%ROWCOUNT);
2124: /* End gnramasa Modified for bug 5677415 27/11/2006 */
2125: LogMessage(FND_LOG.LEVEL_STATEMENT,'Deleted rows that will be repopulated');
2126: end if;
2127:
2121: /* delete from IEX_DLN_UWQ_SUMMARY where (cust_account_id, site_use_id, org_id) in
2122: (select cust_account_id, site_use_id, org_id from ar_trx_bal_summary where trunc(LAST_UPDATE_DATE) >= trunc(l_from_date)); */
2123: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No of rows deleted: ' || SQL%ROWCOUNT);
2124: /* End gnramasa Modified for bug 5677415 27/11/2006 */
2125: LogMessage(FND_LOG.LEVEL_STATEMENT,'Deleted rows that will be repopulated');
2126: end if;
2127:
2128: -- 1. Fetching and inserting data into the table
2129: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2125: LogMessage(FND_LOG.LEVEL_STATEMENT,'Deleted rows that will be repopulated');
2126: end if;
2127:
2128: -- 1. Fetching and inserting data into the table
2129: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2130: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'1. Fetching and inserting data into the table...');
2131:
2132: l_total := 0;
2133: l_count := 0;
2126: end if;
2127:
2128: -- 1. Fetching and inserting data into the table
2129: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2130: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'1. Fetching and inserting data into the table...');
2131:
2132: l_total := 0;
2133: l_count := 0;
2134:
2131:
2132: l_total := 0;
2133: l_count := 0;
2134:
2135: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2136:
2137: -- Begin - Andre Araujo - 10/20/06 - Added dynamic sql - Assembling BILL_TO query
2138:
2139:
2159: end if;
2160: END IF;
2161:
2162: -- End - Andre Araujo - 10/20/06 - Added dynamic sql - Assembling query
2163: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2164:
2165: LOOP
2166: l_count := l_count +1;
2167: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
2163: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2164:
2165: LOOP
2166: l_count := l_count +1;
2167: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
2168: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
2169:
2170: L_ORG_ID.delete;
2171: L_COLLECTOR_ID.delete;
2164:
2165: LOOP
2166: l_count := l_count +1;
2167: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
2168: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
2169:
2170: L_ORG_ID.delete;
2171: L_COLLECTOR_ID.delete;
2172: L_COLLECTOR_RESOURCE_ID.delete;
2239: L_ACTIVE_PROMISES.delete;
2240: L_COMPLETE_PROMISES.delete;
2241: L_PENDING_PROMISES.delete;
2242:
2243: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
2244:
2245: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2246: IF l_level = 'CUSTOMER' THEN
2247: if (l_from_date is null) then
2241: L_PENDING_PROMISES.delete;
2242:
2243: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
2244:
2245: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2246: IF l_level = 'CUSTOMER' THEN
2247: if (l_from_date is null) then
2248: FETCH c_iex_cu_uwq_summary bulk collect
2249: INTO
2713: END IF;
2714:
2715: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
2716:
2717: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2718: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
2719: EXIT;
2720:
2721: ELSE
2714:
2715: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
2716:
2717: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2718: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
2719: EXIT;
2720:
2721: ELSE
2722:
2719: EXIT;
2720:
2721: ELSE
2722:
2723: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2724: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
2725: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
2726: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2727:
2720:
2721: ELSE
2722:
2723: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2724: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
2725: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
2726: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2727:
2728: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
2721: ELSE
2722:
2723: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2724: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
2725: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
2726: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2727:
2728: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
2729: INSERT INTO IEX_DLN_UWQ_SUMMARY
2722:
2723: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2724: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
2725: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
2726: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2727:
2728: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
2729: INSERT INTO IEX_DLN_UWQ_SUMMARY
2730: (DLN_UWQ_SUMMARY_ID
2874: L_ACTIVE_PROMISES(i),
2875: L_COMPLETE_PROMISES(i),
2876: L_PENDING_PROMISES(i));
2877:
2878: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2879: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
2880:
2881: COMMIT;
2882: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
2875: L_COMPLETE_PROMISES(i),
2876: L_PENDING_PROMISES(i));
2877:
2878: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2879: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
2880:
2881: COMMIT;
2882: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
2883:
2878: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2879: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
2880:
2881: COMMIT;
2882: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
2883:
2884: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
2885: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
2886:
2881: COMMIT;
2882: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
2883:
2884: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
2885: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
2886:
2887: END IF;
2888:
2889: END LOOP;
2920:
2921: -- End - Andre Araujo - 10/20/06 - Added dynamic sql
2922: END IF;
2923:
2924: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
2925: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total inserted ' || l_total || ' rows');
2926: If (P_mode = 'CP') THEN --Bug5691098
2927: -- 2. Fetching and updating table with stategy info
2928: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2921: -- End - Andre Araujo - 10/20/06 - Added dynamic sql
2922: END IF;
2923:
2924: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
2925: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total inserted ' || l_total || ' rows');
2926: If (P_mode = 'CP') THEN --Bug5691098
2927: -- 2. Fetching and updating table with stategy info
2928: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2929: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'2. Fetching and updating table with stategy info...');
2924: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
2925: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total inserted ' || l_total || ' rows');
2926: If (P_mode = 'CP') THEN --Bug5691098
2927: -- 2. Fetching and updating table with stategy info
2928: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2929: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'2. Fetching and updating table with stategy info...');
2930: l_total := 0;
2931: l_count := 0;
2932: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2925: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total inserted ' || l_total || ' rows');
2926: If (P_mode = 'CP') THEN --Bug5691098
2927: -- 2. Fetching and updating table with stategy info
2928: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2929: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'2. Fetching and updating table with stategy info...');
2930: l_total := 0;
2931: l_count := 0;
2932: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2933: OPEN c_strategy_summary(l_level, l_from_date);
2928: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' ');
2929: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'2. Fetching and updating table with stategy info...');
2930: l_total := 0;
2931: l_count := 0;
2932: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2933: OPEN c_strategy_summary(l_level, l_from_date);
2934: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2935: LOOP
2936: l_count := l_count +1;
2930: l_total := 0;
2931: l_count := 0;
2932: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2933: OPEN c_strategy_summary(l_level, l_from_date);
2934: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2935: LOOP
2936: l_count := l_count +1;
2937: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
2938: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
2933: OPEN c_strategy_summary(l_level, l_from_date);
2934: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2935: LOOP
2936: l_count := l_count +1;
2937: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
2938: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
2939:
2940: L_JTF_OBJECT_ID.delete;
2941: L_WORK_ITEM_ID.delete;
2934: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End open cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2935: LOOP
2936: l_count := l_count +1;
2937: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
2938: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
2939:
2940: L_JTF_OBJECT_ID.delete;
2941: L_WORK_ITEM_ID.delete;
2942: L_SCHEDULE_START.delete;
2953: L_END_TIME.delete;
2954: L_WORK_ITEM_ORDER.delete;
2955: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
2956:
2957: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
2958:
2959: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2960: FETCH c_strategy_summary bulk collect
2961: INTO
2955: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
2956:
2957: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
2958:
2959: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2960: FETCH c_strategy_summary bulk collect
2961: INTO
2962: L_JTF_OBJECT_ID,
2963: L_WORK_ITEM_ID,
2978: limit l_max_fetches;
2979:
2980: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
2981:
2982: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2983: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
2984: CLOSE c_strategy_summary;
2985: EXIT;
2986:
2979:
2980: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
2981:
2982: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2983: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
2984: CLOSE c_strategy_summary;
2985: EXIT;
2986:
2987: ELSE
2985: EXIT;
2986:
2987: ELSE
2988:
2989: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2990: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
2991: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
2992: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2993:
2986:
2987: ELSE
2988:
2989: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2990: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
2991: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
2992: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2993:
2994: IF l_level = 'CUSTOMER' THEN
2987: ELSE
2988:
2989: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2990: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
2991: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
2992: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2993:
2994: IF l_level = 'CUSTOMER' THEN
2995:
2988:
2989: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2990: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
2991: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
2992: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
2993:
2994: IF l_level = 'CUSTOMER' THEN
2995:
2996: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
3055: WHERE SITE_USE_ID = L_JTF_OBJECT_ID(i);
3056:
3057: END IF;
3058:
3059: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
3060: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3061: COMMIT;
3062: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
3063:
3056:
3057: END IF;
3058:
3059: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
3060: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3061: COMMIT;
3062: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
3063:
3064: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
3058:
3059: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
3060: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3061: COMMIT;
3062: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
3063:
3064: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
3065: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
3066:
3061: COMMIT;
3062: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
3063:
3064: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
3065: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
3066:
3067: END IF;
3068:
3069: END LOOP;
3071: IF c_strategy_summary % ISOPEN THEN
3072: CLOSE c_strategy_summary;
3073: END IF;
3074:
3075: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3076: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total updated ' || l_total || ' rows with strategy info');
3077: End If; --Bug5691098
3078:
3079: --Bug5701973. Start.
3072: CLOSE c_strategy_summary;
3073: END IF;
3074:
3075: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3076: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total updated ' || l_total || ' rows with strategy info');
3077: End If; --Bug5691098
3078:
3079: --Bug5701973. Start.
3080: IF (l_from_date IS NOT NULL and p_mode = 'CP') THEN --Bug5691098
3077: End If; --Bug5691098
3078:
3079: --Bug5701973. Start.
3080: IF (l_from_date IS NOT NULL and p_mode = 'CP') THEN --Bug5691098
3081: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3082: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Started at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3083: BEGIN
3084: --Party changes update
3085: OPEN changed_party(l_from_date);
3078:
3079: --Bug5701973. Start.
3080: IF (l_from_date IS NOT NULL and p_mode = 'CP') THEN --Bug5691098
3081: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3082: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Started at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3083: BEGIN
3084: --Party changes update
3085: OPEN changed_party(l_from_date);
3086: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'3. Opened Cursor changed_party at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3082: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Started at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3083: BEGIN
3084: --Party changes update
3085: OPEN changed_party(l_from_date);
3086: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'3. Opened Cursor changed_party at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3087: L_PARTY_ID.delete;
3088: L_ADDRESS1.delete;
3089: L_CITY.delete;
3090: L_STATE.delete;
3107: L_POSTAL_CODE
3108: limit l_max_fetches;
3109: IF L_PARTY_ID.COUNT = 0 THEN
3110:
3111: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Changed_Party Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3112: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3113: EXIT;
3114:
3115: ELSE
3108: limit l_max_fetches;
3109: IF L_PARTY_ID.COUNT = 0 THEN
3110:
3111: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Changed_Party Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3112: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3113: EXIT;
3114:
3115: ELSE
3116:
3126: postal_code = L_POSTAL_CODE(i),
3127: last_update_date = SYSDATE,
3128: last_updated_by = FND_GLOBAL.USER_ID
3129: WHERE PARTY_ID = L_PARTY_ID(i);
3130: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_Party Cursor updated ' ||L_PARTY_ID.count || ' rows ');
3131: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3132: COMMIT;
3133: END IF;
3134: END LOOP;
3127: last_update_date = SYSDATE,
3128: last_updated_by = FND_GLOBAL.USER_ID
3129: WHERE PARTY_ID = L_PARTY_ID(i);
3130: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_Party Cursor updated ' ||L_PARTY_ID.count || ' rows ');
3131: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3132: COMMIT;
3133: END IF;
3134: END LOOP;
3135: CLOSE changed_party;
3135: CLOSE changed_party;
3136:
3137:
3138: EXCEPTION WHEN OTHERS THEN
3139: LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQLERRM);
3140: END;
3141:
3142: --Profile Changes Update
3143: BEGIN
3141:
3142: --Profile Changes Update
3143: BEGIN
3144: OPEN changed_profiles(l_from_date);
3145: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'4.Opened changed_profiles cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3146:
3147: L_PARTY_ID.delete;
3148: L_CUST_ACCOUNT_ID.delete;
3149: L_SITE_USE_ID.delete;
3158: L_SITE_USE_ID
3159: limit l_max_fetches;
3160: IF L_PARTY_ID.COUNT = 0 THEN
3161:
3162: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: Changed_Profiles ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3163: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3164: EXIT;
3165:
3166: ELSE
3159: limit l_max_fetches;
3160: IF L_PARTY_ID.COUNT = 0 THEN
3161:
3162: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: Changed_Profiles ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3163: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3164: EXIT;
3165:
3166: ELSE
3167:
3173: WHERE
3174: PARTY_ID = L_PARTY_ID(i)
3175: AND nvl(CUST_ACCOUNT_ID,1) = nvl(L_CUST_ACCOUNT_ID(i),1)
3176: AND nvl(SITE_USE_ID,1) = nvl(L_SITE_USE_ID(i),1);
3177: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_profiles updated ' || L_PARTY_ID.count || ' rows ' );
3178: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3179:
3180: COMMIT;
3181: END IF;
3174: PARTY_ID = L_PARTY_ID(i)
3175: AND nvl(CUST_ACCOUNT_ID,1) = nvl(L_CUST_ACCOUNT_ID(i),1)
3176: AND nvl(SITE_USE_ID,1) = nvl(L_SITE_USE_ID(i),1);
3177: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_profiles updated ' || L_PARTY_ID.count || ' rows ' );
3178: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3179:
3180: COMMIT;
3181: END IF;
3182: END LOOP;
3183: IF changed_profiles % ISOPEN THEN
3184: CLOSE changed_profiles;
3185: END IF;
3186: EXCEPTION WHEN OTHERS THEN
3187: LogMessage(FND_LOG.LEVEL_UNEXPECTED,SQLERRM);
3188: END;
3189:
3190: --Collector Changes Update
3191: BEGIN
3189:
3190: --Collector Changes Update
3191: BEGIN
3192: OPEN changed_collector(l_from_date);
3193: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'5.Open changed_collector cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3194: L_COLLECTOR_RESOURCE_ID.delete ;
3195: L_COLLECTOR_ID.delete;
3196: L_RESOURCE_TYPE.delete;
3197:
3203: L_COLLECTOR_ID
3204: limit l_max_fetches;
3205: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
3206:
3207: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: Changed_Collector ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3208: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3209: EXIT;
3210:
3211: ELSE
3204: limit l_max_fetches;
3205: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
3206:
3207: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: Changed_Collector ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3208: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3209: EXIT;
3210:
3211: ELSE
3212:
3217: last_update_date = SYSDATE,
3218: last_updated_by = FND_GLOBAL.USER_ID
3219: WHERE
3220: COLLECTOR_ID = L_COLLECTOR_ID(i);
3221: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_collector updated ' || L_COLLECTOR_ID.count || ' rows ');
3222: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3223:
3224: COMMIT;
3225: END IF;
3218: last_updated_by = FND_GLOBAL.USER_ID
3219: WHERE
3220: COLLECTOR_ID = L_COLLECTOR_ID(i);
3221: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_collector updated ' || L_COLLECTOR_ID.count || ' rows ');
3222: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3223:
3224: COMMIT;
3225: END IF;
3226: END LOOP;
3228: CLOSE changed_collector;
3229: END IF;
3230:
3231: EXCEPTION WHEN OTHERS THEN
3232: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Changed Collector update received' || SQLERRM);
3233: END;
3234: --Contact Point Changes Update
3235: BEGIN
3236: OPEN changed_contact(l_from_date);
3233: END;
3234: --Contact Point Changes Update
3235: BEGIN
3236: OPEN changed_contact(l_from_date);
3237: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'6.Opened Cursor changed_contact cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3238: L_PARTY_ID.delete;
3239: L_PHONE_COUNTRY_CODE.delete;
3240: L_PHONE_AREA_CODE.delete;
3241: L_PHONE_NUMBER.delete;
3253:
3254: limit l_max_fetches;
3255: IF L_PARTY_ID.COUNT = 0 THEN
3256:
3257: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Changed_contact Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3258: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3259: EXIT;
3260:
3261: ELSE
3254: limit l_max_fetches;
3255: IF L_PARTY_ID.COUNT = 0 THEN
3256:
3257: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Changed_contact Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3258: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3259: EXIT;
3260:
3261: ELSE
3262:
3269: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
3270: last_update_date = SYSDATE,
3271: last_updated_by = FND_GLOBAL.USER_ID
3272: WHERE PARTY_ID = L_PARTY_ID(i);
3273: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_contact Cursor updated ' ||L_PARTY_ID.count || ' rows ');
3274: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3275:
3276: COMMIT;
3277: END IF;
3270: last_update_date = SYSDATE,
3271: last_updated_by = FND_GLOBAL.USER_ID
3272: WHERE PARTY_ID = L_PARTY_ID(i);
3273: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Changed_contact Cursor updated ' ||L_PARTY_ID.count || ' rows ');
3274: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3275:
3276: COMMIT;
3277: END IF;
3278: END LOOP;
3279: CLOSE changed_contact;
3280:
3281:
3282: EXCEPTION WHEN OTHERS THEN
3283: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point raised error ' || SQLERRM);
3284: END;
3285:
3286: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Finished at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3287: END IF;
3282: EXCEPTION WHEN OTHERS THEN
3283: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point raised error ' || SQLERRM);
3284: END;
3285:
3286: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'TCA Update Finished at : ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3287: END IF;
3288:
3289: --Bug5701973. End.
3290:
3294:
3295:
3296: EXCEPTION
3297: WHEN deadlock_detected THEN
3298: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: deadlock detected');
3299: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3300: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3301: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3302: x_errbuf := sqlerrm;
3295:
3296: EXCEPTION
3297: WHEN deadlock_detected THEN
3298: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: deadlock detected');
3299: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3300: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3301: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3302: x_errbuf := sqlerrm;
3303: x_retcode := SQLCODE;
3296: EXCEPTION
3297: WHEN deadlock_detected THEN
3298: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: deadlock detected');
3299: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3300: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3301: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3302: x_errbuf := sqlerrm;
3303: x_retcode := SQLCODE;
3304:
3297: WHEN deadlock_detected THEN
3298: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: deadlock detected');
3299: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3300: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3301: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3302: x_errbuf := sqlerrm;
3303: x_retcode := SQLCODE;
3304:
3305:
3345: message => 'The process has failed. Please review log file.');
3346: end if;
3347:
3348: WHEN others THEN
3349: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: other');
3350: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3351: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3352: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3353: x_errbuf := sqlerrm;
3346: end if;
3347:
3348: WHEN others THEN
3349: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: other');
3350: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3351: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3352: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3353: x_errbuf := sqlerrm;
3354: x_retcode := SQLCODE;
3347:
3348: WHEN others THEN
3349: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: other');
3350: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3351: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3352: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3353: x_errbuf := sqlerrm;
3354: x_retcode := SQLCODE;
3355:
3348: WHEN others THEN
3349: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Exception in populate_uwq_sum: other');
3350: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'SQLCODE: ' || to_char(SQLCODE) || ' SQLERRM: ' || sqlerrm);
3351: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3352: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Total processed ' || l_total || ' rows');
3353: x_errbuf := sqlerrm;
3354: x_retcode := SQLCODE;
3355:
3356: -- Begin - Andre Araujo - 10/20/06 - Added dynamic sql
3793: BEGIN
3794:
3795: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
3796: if p_mode='DLN' then
3797: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
3798: -- Start Bug #6251657 bibeura 25-OCT-2007
3799: BEGIN
3800: OPEN C_BILLTO_DEL_DLN;
3801: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_del_dln cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3797: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
3798: -- Start Bug #6251657 bibeura 25-OCT-2007
3799: BEGIN
3800: OPEN C_BILLTO_DEL_DLN;
3801: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_del_dln cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3802: L_SITE_USE_ID.delete;
3803: L_NUMBER_OF_DELINQUENCIES.delete;
3804: L_PENDING_DELINQUENCIES.delete;
3805: L_COMPLETE_DELINQUENCIES.delete;
3819: L_ORG_ID
3820: limit l_max_fetches;
3821: FND_FILE.PUT_LINE(FND_FILE.LOG, 'fetched records '||L_SITE_USE_ID.COUNT) ;
3822: IF L_SITE_USE_ID.COUNT = 0 THEN
3823: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_del_dln ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3824: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3825: EXIT;
3826: ELSE
3827:
3820: limit l_max_fetches;
3821: FND_FILE.PUT_LINE(FND_FILE.LOG, 'fetched records '||L_SITE_USE_ID.COUNT) ;
3822: IF L_SITE_USE_ID.COUNT = 0 THEN
3823: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_del_dln ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3824: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
3825: EXIT;
3826: ELSE
3827:
3828: forall i IN L_SITE_USE_ID.FIRST .. L_SITE_USE_ID.LAST
3834: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
3835: WHERE
3836: SITE_USE_ID = L_SITE_USE_ID(i)
3837: AND ORG_ID=L_ORG_ID(i);
3838: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
3839: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3840: END IF;
3841: END LOOP;
3842: IF C_BILLTO_DEL_DLN % ISOPEN THEN
3835: WHERE
3836: SITE_USE_ID = L_SITE_USE_ID(i)
3837: AND ORG_ID=L_ORG_ID(i);
3838: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
3839: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
3840: END IF;
3841: END LOOP;
3842: IF C_BILLTO_DEL_DLN % ISOPEN THEN
3843: CLOSE C_BILLTO_DEL_DLN;
3843: CLOSE C_BILLTO_DEL_DLN;
3844: END IF;
3845:
3846: EXCEPTION WHEN OTHERS THEN
3847: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
3848: END;
3849: -- End Bug #6251657 bibeura 25-OCT-2007
3850: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
3851: select customer_site_use_id,'IEX_BILLTO',org_id from iex_delinquencies del
3862:
3863: open c_iex_billto_uwq_summary;
3864: loop
3865: l_count := l_count +1;
3866: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
3867: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
3868:
3869: L_ORG_ID.delete;
3870: L_COLLECTOR_ID.delete;
3863: open c_iex_billto_uwq_summary;
3864: loop
3865: l_count := l_count +1;
3866: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
3867: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
3868:
3869: L_ORG_ID.delete;
3870: L_COLLECTOR_ID.delete;
3871: L_COLLECTOR_RESOURCE_ID.delete;
3937: L_PROMISE_AMOUNT.delete;
3938: L_ACTIVE_PROMISES.delete;
3939: L_COMPLETE_PROMISES.delete;
3940: L_PENDING_PROMISES.delete;
3941: LogMessage(FND_LOG.LEVEL_STATEMENT,'Start fetching records...');
3942: FETCH c_iex_billto_uwq_summary bulk collect
3943: INTO
3944: L_ORG_ID,
3945: L_IEU_OBJECT_FUNCTION,
3994: limit l_max_fetches;
3995:
3996: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
3997:
3998: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3999: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4000: EXIT;
4001:
4002: ELSE
3995:
3996: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
3997:
3998: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3999: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4000: EXIT;
4001:
4002: ELSE
4003:
4000: EXIT;
4001:
4002: ELSE
4003:
4004: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4005: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
4006: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
4007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4008: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
4001:
4002: ELSE
4003:
4004: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4005: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
4006: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
4007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4008: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
4009: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
4002: ELSE
4003:
4004: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4005: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
4006: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
4007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4008: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
4009: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
4010: INSERT INTO IEX_DLN_UWQ_SUMMARY
4003:
4004: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4005: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
4006: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
4007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4008: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
4009: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
4010: INSERT INTO IEX_DLN_UWQ_SUMMARY
4011: (DLN_UWQ_SUMMARY_ID
4004: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4005: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
4006: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
4007: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4008: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
4009: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
4010: INSERT INTO IEX_DLN_UWQ_SUMMARY
4011: (DLN_UWQ_SUMMARY_ID
4012: ,ORG_ID
4117: 0,
4118: 0,
4119: 0);
4120:
4121: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4122: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
4123:
4124: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
4125: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
4118: 0,
4119: 0);
4120:
4121: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4122: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
4123:
4124: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
4125: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
4126:
4121: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4122: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
4123:
4124: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
4125: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
4126:
4127: END IF;
4128:
4129: END LOOP;
4131:
4132: OPEN c_strategy_summary;
4133: LOOP
4134: l_count := l_count +1;
4135: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
4136: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
4137:
4138: L_JTF_OBJECT_ID.delete;
4139: L_WORK_ITEM_ID.delete;
4132: OPEN c_strategy_summary;
4133: LOOP
4134: l_count := l_count +1;
4135: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
4136: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
4137:
4138: L_JTF_OBJECT_ID.delete;
4139: L_WORK_ITEM_ID.delete;
4140: L_SCHEDULE_START.delete;
4151: L_END_TIME.delete;
4152: L_WORK_ITEM_ORDER.delete;
4153: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
4154:
4155: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
4156:
4157: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4158: FETCH c_strategy_summary bulk collect
4159: INTO
4153: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
4154:
4155: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
4156:
4157: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4158: FETCH c_strategy_summary bulk collect
4159: INTO
4160: L_JTF_OBJECT_ID,
4161: L_WORK_ITEM_ID,
4174: L_WORK_ITEM_ORDER,
4175: L_ESCALATED_YN --Added for bug#6981126 by schekuri on 27-Jun-2008
4176: limit l_max_fetches;
4177:
4178: LogMessage(FND_LOG.LEVEL_STATEMENT,L_JTF_OBJECT_ID.COUNT);
4179:
4180: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
4181:
4182: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4178: LogMessage(FND_LOG.LEVEL_STATEMENT,L_JTF_OBJECT_ID.COUNT);
4179:
4180: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
4181:
4182: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4183: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4184: CLOSE c_strategy_summary;
4185: EXIT;
4186:
4179:
4180: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
4181:
4182: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4183: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4184: CLOSE c_strategy_summary;
4185: EXIT;
4186:
4187: ELSE
4185: EXIT;
4186:
4187: ELSE
4188:
4189: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4190: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
4191: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
4192: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4193:
4186:
4187: ELSE
4188:
4189: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4190: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
4191: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
4192: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4193:
4194: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
4187: ELSE
4188:
4189: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4190: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
4191: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
4192: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4193:
4194: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
4195: UPDATE IEX_DLN_UWQ_SUMMARY
4188:
4189: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4190: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
4191: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
4192: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4193:
4194: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
4195: UPDATE IEX_DLN_UWQ_SUMMARY
4196: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
4210: WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
4211: WHERE SITE_USE_ID = L_JTF_OBJECT_ID(i);
4212:
4213:
4214: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
4215: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4216:
4217: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
4218: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
4211: WHERE SITE_USE_ID = L_JTF_OBJECT_ID(i);
4212:
4213:
4214: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
4215: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4216:
4217: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
4218: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
4219:
4214: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
4215: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4216:
4217: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
4218: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
4219:
4220: END IF;
4221:
4222: END LOOP;
4226: END IF;
4227:
4228: BEGIN
4229: OPEN C_COLLECTOR_PROF;
4230: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open C_COLLECTOR_PROF cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4231: L_COLLECTOR_RESOURCE_ID.delete ;
4232: L_COLLECTOR_ID.delete;
4233: L_RESOURCE_TYPE.delete;
4234: L_PARTY_ID.delete;
4246: L_SITE_USE_ID
4247: limit l_max_fetches;
4248: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
4249:
4250: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: C_COLLECTOR_PROF ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4251: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4252: EXIT;
4253:
4254: ELSE
4247: limit l_max_fetches;
4248: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
4249:
4250: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: C_COLLECTOR_PROF ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4251: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4252: EXIT;
4253:
4254: ELSE
4255:
4261: last_update_date = SYSDATE,
4262: last_updated_by = FND_GLOBAL.USER_ID
4263: WHERE
4264: SITE_USE_ID = L_SITE_USE_ID(i);
4265: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' C_COLLECTOR_PROF updated ' || L_COLLECTOR_ID.count || ' rows ');
4266: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4267:
4268:
4269: END IF;
4262: last_updated_by = FND_GLOBAL.USER_ID
4263: WHERE
4264: SITE_USE_ID = L_SITE_USE_ID(i);
4265: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' C_COLLECTOR_PROF updated ' || L_COLLECTOR_ID.count || ' rows ');
4266: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4267:
4268:
4269: END IF;
4270: END LOOP;
4272: CLOSE C_COLLECTOR_PROF;
4273: END IF;
4274:
4275: EXCEPTION WHEN OTHERS THEN
4276: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'C_COLLECTOR_PROF update received' || SQLERRM);
4277: END;
4278:
4279: BEGIN
4280: OPEN c_contact_point;
4277: END;
4278:
4279: BEGIN
4280: OPEN c_contact_point;
4281: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Opened Cursor c_contact_point cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4282: L_PARTY_ID.delete;
4283: L_PHONE_COUNTRY_CODE.delete;
4284: L_PHONE_AREA_CODE.delete;
4285: L_PHONE_NUMBER.delete;
4297:
4298: limit l_max_fetches;
4299: IF L_PARTY_ID.COUNT = 0 THEN
4300:
4301: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'c_contact_point Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4302: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4303: EXIT;
4304:
4305: ELSE
4298: limit l_max_fetches;
4299: IF L_PARTY_ID.COUNT = 0 THEN
4300:
4301: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'c_contact_point Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4302: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4303: EXIT;
4304:
4305: ELSE
4306:
4313: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
4314: last_update_date = SYSDATE,
4315: last_updated_by = FND_GLOBAL.USER_ID
4316: WHERE PARTY_ID = L_PARTY_ID(i);
4317: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_contact_point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
4318: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4319:
4320:
4321: END IF;
4314: last_update_date = SYSDATE,
4315: last_updated_by = FND_GLOBAL.USER_ID
4316: WHERE PARTY_ID = L_PARTY_ID(i);
4317: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_contact_point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
4318: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4319:
4320:
4321: END IF;
4322: END LOOP;
4323: CLOSE c_contact_point;
4324:
4325:
4326: EXCEPTION WHEN OTHERS THEN
4327: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point raised error ' || SQLERRM);
4328: END;
4329: -- gnramasa
4330: BEGIN
4331: OPEN C_BILLTO_DEL;
4328: END;
4329: -- gnramasa
4330: BEGIN
4331: OPEN C_BILLTO_DEL;
4332: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_del cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4333: L_SITE_USE_ID.delete;
4334: L_NUMBER_OF_DELINQUENCIES.delete;
4335: L_PENDING_DELINQUENCIES.delete;
4336: L_COMPLETE_DELINQUENCIES.delete;
4346: L_ACTIVE_DELINQUENCIES
4347: limit l_max_fetches;
4348: IF L_SITE_USE_ID.COUNT = 0 THEN
4349:
4350: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_del ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4351: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4352: EXIT;
4353:
4354: ELSE
4347: limit l_max_fetches;
4348: IF L_SITE_USE_ID.COUNT = 0 THEN
4349:
4350: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_del ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4351: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4352: EXIT;
4353:
4354: ELSE
4355:
4360: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
4361: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
4362: WHERE
4363: SITE_USE_ID = L_SITE_USE_ID(i);
4364: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_del updated ' || L_COLLECTOR_ID.count || ' rows ');
4365: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4366:
4367:
4368: END IF;
4361: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
4362: WHERE
4363: SITE_USE_ID = L_SITE_USE_ID(i);
4364: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_del updated ' || L_COLLECTOR_ID.count || ' rows ');
4365: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4366:
4367:
4368: END IF;
4369: END LOOP;
4371: CLOSE C_BILLTO_DEL;
4372: END IF;
4373:
4374: EXCEPTION WHEN OTHERS THEN
4375: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
4376: END;
4377:
4378: BEGIN
4379: OPEN C_BILLTO_PRO;
4376: END;
4377:
4378: BEGIN
4379: OPEN C_BILLTO_PRO;
4380: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_pro cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4381: L_SITE_USE_ID.delete;
4382: L_ACTIVE_PROMISES.delete;
4383: L_COMPLETE_PROMISES.delete;
4384: L_PENDING_PROMISES.delete;
4392: L_ACTIVE_PROMISES
4393: limit l_max_fetches;
4394: IF L_SITE_USE_ID.COUNT = 0 THEN
4395:
4396: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_pro ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4397: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4398: EXIT;
4399:
4400: ELSE
4393: limit l_max_fetches;
4394: IF L_SITE_USE_ID.COUNT = 0 THEN
4395:
4396: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_pro ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4397: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4398: EXIT;
4399:
4400: ELSE
4401:
4405: COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
4406: PENDING_PROMISES = L_PENDING_PROMISES(i)
4407: WHERE
4408: SITE_USE_ID = L_SITE_USE_ID(i);
4409: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
4410: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4411:
4412:
4413: END IF;
4406: PENDING_PROMISES = L_PENDING_PROMISES(i)
4407: WHERE
4408: SITE_USE_ID = L_SITE_USE_ID(i);
4409: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
4410: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4411:
4412:
4413: END IF;
4414: END LOOP;
4416: CLOSE C_BILLTO_PRO;
4417: END IF;
4418:
4419: EXCEPTION WHEN OTHERS THEN
4420: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Promise update received' || SQLERRM);
4421: END;
4422:
4423: BEGIN
4424: OPEN C_BILLTO_PRO_SUMM;
4421: END;
4422:
4423: BEGIN
4424: OPEN C_BILLTO_PRO_SUMM;
4425: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_pro_summ cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4426: L_SITE_USE_ID.delete;
4427: L_NUMBER_OF_PROMISES.delete;
4428: L_BROKEN_PROMISE_AMOUNT .delete;
4429: L_PROMISE_AMOUNT.delete;
4437: L_PROMISE_AMOUNT
4438: limit l_max_fetches;
4439: IF L_SITE_USE_ID.COUNT = 0 THEN
4440:
4441: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_pro_summ ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4442: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4443: EXIT;
4444:
4445: ELSE
4438: limit l_max_fetches;
4439: IF L_SITE_USE_ID.COUNT = 0 THEN
4440:
4441: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_pro_summ ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4442: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4443: EXIT;
4444:
4445: ELSE
4446:
4450: BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
4451: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
4452: WHERE
4453: SITE_USE_ID = L_SITE_USE_ID(i);
4454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
4455: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4456:
4457:
4458: END IF;
4451: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
4452: WHERE
4453: SITE_USE_ID = L_SITE_USE_ID(i);
4454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
4455: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4456:
4457:
4458: END IF;
4459: END LOOP;
4461: CLOSE C_BILLTO_PRO_SUMM;
4462: END IF;
4463:
4464: EXCEPTION WHEN OTHERS THEN
4465: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Broken Promise update received' || SQLERRM);
4466: END;
4467:
4468: BEGIN
4469: OPEN C_BILLTO_SCORE;
4466: END;
4467:
4468: BEGIN
4469: OPEN C_BILLTO_SCORE;
4470: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_score cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4471: L_SITE_USE_ID.delete;
4472: L_SCORE.delete;
4473:
4474: LOOP
4478: L_SCORE
4479: limit l_max_fetches;
4480: IF L_SITE_USE_ID.COUNT = 0 THEN
4481:
4482: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_score ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4483: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4484: EXIT;
4485:
4486: ELSE
4479: limit l_max_fetches;
4480: IF L_SITE_USE_ID.COUNT = 0 THEN
4481:
4482: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_score ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4483: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4484: EXIT;
4485:
4486: ELSE
4487:
4489: UPDATE IEX_DLN_UWQ_SUMMARY
4490: SET SCORE = L_SCORE(i)
4491: WHERE
4492: SITE_USE_ID = L_SITE_USE_ID(i);
4493: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_score updated ' || L_COLLECTOR_ID.count || ' rows ');
4494: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4495:
4496:
4497: END IF;
4490: SET SCORE = L_SCORE(i)
4491: WHERE
4492: SITE_USE_ID = L_SITE_USE_ID(i);
4493: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_score updated ' || L_COLLECTOR_ID.count || ' rows ');
4494: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4495:
4496:
4497: END IF;
4498: END LOOP;
4500: CLOSE C_BILLTO_SCORE;
4501: END IF;
4502:
4503: EXCEPTION WHEN OTHERS THEN
4504: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Score update received' || SQLERRM);
4505: END;
4506:
4507: BEGIN
4508: OPEN C_BILLTO_PAST_DUE;
4505: END;
4506:
4507: BEGIN
4508: OPEN C_BILLTO_PAST_DUE;
4509: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_billto_past_due cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4510: L_SITE_USE_ID.delete;
4511: L_PAST_DUE_INV_VALUE.delete;
4512:
4513: LOOP
4517: L_PAST_DUE_INV_VALUE
4518: limit l_max_fetches;
4519: IF L_SITE_USE_ID.COUNT = 0 THEN
4520:
4521: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_past_due ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4522: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4523: EXIT;
4524:
4525: ELSE
4518: limit l_max_fetches;
4519: IF L_SITE_USE_ID.COUNT = 0 THEN
4520:
4521: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_billto_past_due ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4522: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4523: EXIT;
4524:
4525: ELSE
4526:
4528: UPDATE IEX_DLN_UWQ_SUMMARY
4529: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
4530: WHERE
4531: SITE_USE_ID = L_SITE_USE_ID(i);
4532: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
4533: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4534:
4535:
4536: END IF;
4529: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
4530: WHERE
4531: SITE_USE_ID = L_SITE_USE_ID(i);
4532: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_billto_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
4533: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4534:
4535:
4536: END IF;
4537: END LOOP;
4539: CLOSE C_BILLTO_PAST_DUE;
4540: END IF;
4541:
4542: EXCEPTION WHEN OTHERS THEN
4543: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Past due invoice update received' || SQLERRM);
4544: END;
4545:
4546: BEGIN
4547: OPEN C_LAST_PAYMENT_NO_AMOUNT;
4544: END;
4545:
4546: BEGIN
4547: OPEN C_LAST_PAYMENT_NO_AMOUNT;
4548: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_last_payment_no_amount cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4549: L_SITE_USE_ID.delete;
4550: L_LAST_PAYMENT_NUMBER.delete;
4551: L_LAST_PAYMENT_AMOUNT.delete;
4552:
4558: L_LAST_PAYMENT_AMOUNT
4559: limit l_max_fetches;
4560: IF L_SITE_USE_ID.COUNT = 0 THEN
4561:
4562: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_last_payment_no_amount ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4563: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4564: EXIT;
4565:
4566: ELSE
4559: limit l_max_fetches;
4560: IF L_SITE_USE_ID.COUNT = 0 THEN
4561:
4562: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_last_payment_no_amount ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4563: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4564: EXIT;
4565:
4566: ELSE
4567:
4570: SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
4571: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
4572: WHERE
4573: SITE_USE_ID = L_SITE_USE_ID(i);
4574: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
4575: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4576:
4577:
4578: END IF;
4571: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
4572: WHERE
4573: SITE_USE_ID = L_SITE_USE_ID(i);
4574: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
4575: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4576:
4577:
4578: END IF;
4579: END LOOP;
4581: CLOSE C_LAST_PAYMENT_NO_AMOUNT;
4582: END IF;
4583:
4584: EXCEPTION WHEN OTHERS THEN
4585: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
4586: END;
4587:
4588: BEGIN
4589: OPEN C_BANKRUPTCIES;
4586: END;
4587:
4588: BEGIN
4589: OPEN C_BANKRUPTCIES;
4590: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_bankruptcies cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4591: L_SITE_USE_ID.delete;
4592: L_NUMBER_OF_BANKRUPTCIES.delete;
4593:
4594: LOOP
4598: L_NUMBER_OF_BANKRUPTCIES
4599: limit l_max_fetches;
4600: IF L_SITE_USE_ID.COUNT = 0 THEN
4601:
4602: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_bankruptcies ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4603: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4604: EXIT;
4605:
4606: ELSE
4599: limit l_max_fetches;
4600: IF L_SITE_USE_ID.COUNT = 0 THEN
4601:
4602: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_bankruptcies ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
4603: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
4604: EXIT;
4605:
4606: ELSE
4607:
4609: UPDATE IEX_DLN_UWQ_SUMMARY
4610: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
4611: WHERE
4612: SITE_USE_ID = L_SITE_USE_ID(i);
4613: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
4614: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4615:
4616:
4617: END IF;
4610: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
4611: WHERE
4612: SITE_USE_ID = L_SITE_USE_ID(i);
4613: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
4614: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
4615:
4616:
4617: END IF;
4618: END LOOP;
4620: CLOSE C_BANKRUPTCIES;
4621: END IF;
4622:
4623: EXCEPTION WHEN OTHERS THEN
4624: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
4625: END;
4626: COMMIT;
4627: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
4628:
4623: EXCEPTION WHEN OTHERS THEN
4624: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
4625: END;
4626: COMMIT;
4627: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
4628:
4629: EXCEPTION
4630: WHEN OTHERS THEN
4631: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
4627: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
4628:
4629: EXCEPTION
4630: WHEN OTHERS THEN
4631: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
4632: END BILLTO_REFRESH_SUMMARY_INCR;
4633:
4634: PROCEDURE account_refresh_summary_incr(
4635: x_errbuf OUT nocopy VARCHAR2,
5018:
5019: BEGIN
5020: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
5021: if p_mode='DLN' then
5022: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
5023: -- Start Bug #6251657 bibeura 25-OCT-2007
5024: BEGIN
5025: OPEN c_account_del_dln;
5026: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_del_dln cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5022: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
5023: -- Start Bug #6251657 bibeura 25-OCT-2007
5024: BEGIN
5025: OPEN c_account_del_dln;
5026: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_del_dln cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5027: L_CUST_ACCOUNT_ID.delete;
5028: L_NUMBER_OF_DELINQUENCIES.delete;
5029: L_PENDING_DELINQUENCIES.delete;
5030: L_COMPLETE_DELINQUENCIES.delete;
5043: L_ACTIVE_DELINQUENCIES,
5044: L_ORG_ID
5045: limit l_max_fetches;
5046: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5047: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_del_dln ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5048: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5049: EXIT;
5050: ELSE
5051:
5044: L_ORG_ID
5045: limit l_max_fetches;
5046: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5047: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_del_dln ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5048: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5049: EXIT;
5050: ELSE
5051:
5052: forall i IN L_CUST_ACCOUNT_ID.FIRST .. L_CUST_ACCOUNT_ID.LAST
5058: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
5059: WHERE
5060: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i)
5061: AND ORG_ID=L_ORG_ID(i);
5062: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
5063: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5064: END IF;
5065: END LOOP;
5066: IF c_account_del_dln % ISOPEN THEN
5059: WHERE
5060: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i)
5061: AND ORG_ID=L_ORG_ID(i);
5062: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
5063: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5064: END IF;
5065: END LOOP;
5066: IF c_account_del_dln % ISOPEN THEN
5067: CLOSE c_account_del_dln;
5068: END IF;
5069:
5070: EXCEPTION
5071: WHEN OTHERS THEN
5072: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
5073: END;
5074: -- End Bug #6251657 bibeura 25-OCT-2007
5075: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
5076: select cust_account_id,'IEX_ACCOUNT',org_id from iex_delinquencies del
5088:
5089: open c_iex_account_uwq_summary;
5090: loop
5091: l_count := l_count +1;
5092: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
5093: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
5094:
5095: L_ORG_ID.delete;
5096: L_COLLECTOR_ID.delete;
5089: open c_iex_account_uwq_summary;
5090: loop
5091: l_count := l_count +1;
5092: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
5093: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
5094:
5095: L_ORG_ID.delete;
5096: L_COLLECTOR_ID.delete;
5097: L_COLLECTOR_RESOURCE_ID.delete;
5163: L_PROMISE_AMOUNT.delete;
5164: L_ACTIVE_PROMISES.delete;
5165: L_COMPLETE_PROMISES.delete;
5166: L_PENDING_PROMISES.delete;
5167: LogMessage(FND_LOG.LEVEL_STATEMENT,'Start fetching records...');
5168: FETCH c_iex_account_uwq_summary bulk collect
5169: INTO
5170: L_ORG_ID,
5171: L_IEU_OBJECT_FUNCTION,
5220: limit l_max_fetches;
5221:
5222: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
5223:
5224: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5225: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5226: EXIT;
5227:
5228: ELSE
5221:
5222: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
5223:
5224: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5225: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5226: EXIT;
5227:
5228: ELSE
5229:
5226: EXIT;
5227:
5228: ELSE
5229:
5230: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5231: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
5232: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
5233: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5234: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
5227:
5228: ELSE
5229:
5230: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5231: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
5232: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
5233: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5234: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
5235: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
5228: ELSE
5229:
5230: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5231: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
5232: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
5233: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5234: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
5235: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
5236: INSERT INTO IEX_DLN_UWQ_SUMMARY
5229:
5230: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5231: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
5232: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
5233: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5234: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
5235: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
5236: INSERT INTO IEX_DLN_UWQ_SUMMARY
5237: (DLN_UWQ_SUMMARY_ID
5230: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5231: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
5232: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
5233: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5234: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
5235: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
5236: INSERT INTO IEX_DLN_UWQ_SUMMARY
5237: (DLN_UWQ_SUMMARY_ID
5238: ,ORG_ID
5343: 0,
5344: 0,
5345: 0);
5346:
5347: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5348: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
5349:
5350:
5351: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
5344: 0,
5345: 0);
5346:
5347: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5348: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
5349:
5350:
5351: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
5352: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
5348: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
5349:
5350:
5351: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
5352: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
5353:
5354: END IF;
5355:
5356: END LOOP;
5358:
5359: OPEN c_strategy_summary;
5360: LOOP
5361: l_count := l_count +1;
5362: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
5363: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
5364:
5365: L_JTF_OBJECT_ID.delete;
5366: L_WORK_ITEM_ID.delete;
5359: OPEN c_strategy_summary;
5360: LOOP
5361: l_count := l_count +1;
5362: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
5363: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
5364:
5365: L_JTF_OBJECT_ID.delete;
5366: L_WORK_ITEM_ID.delete;
5367: L_SCHEDULE_START.delete;
5378: L_END_TIME.delete;
5379: L_WORK_ITEM_ORDER.delete;
5380: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
5381:
5382: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
5383:
5384: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5385: FETCH c_strategy_summary bulk collect
5386: INTO
5380: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
5381:
5382: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
5383:
5384: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5385: FETCH c_strategy_summary bulk collect
5386: INTO
5387: L_JTF_OBJECT_ID,
5388: L_WORK_ITEM_ID,
5401: L_WORK_ITEM_ORDER,
5402: L_ESCALATED_YN --Added for bug#6981126 by schekuri on 27-Jun-2008
5403: limit l_max_fetches;
5404:
5405: LogMessage(FND_LOG.LEVEL_STATEMENT,L_JTF_OBJECT_ID.COUNT);
5406:
5407: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
5408:
5409: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5405: LogMessage(FND_LOG.LEVEL_STATEMENT,L_JTF_OBJECT_ID.COUNT);
5406:
5407: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
5408:
5409: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5410: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5411: CLOSE c_strategy_summary;
5412: EXIT;
5413:
5406:
5407: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
5408:
5409: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5410: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5411: CLOSE c_strategy_summary;
5412: EXIT;
5413:
5414: ELSE
5412: EXIT;
5413:
5414: ELSE
5415:
5416: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5417: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
5418: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
5419: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5420:
5413:
5414: ELSE
5415:
5416: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5417: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
5418: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
5419: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5420:
5421: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
5414: ELSE
5415:
5416: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5417: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
5418: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
5419: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5420:
5421: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
5422: UPDATE IEX_DLN_UWQ_SUMMARY
5415:
5416: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5417: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
5418: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
5419: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5420:
5421: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
5422: UPDATE IEX_DLN_UWQ_SUMMARY
5423: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
5437: WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
5438: WHERE cust_account_id = L_JTF_OBJECT_ID(i);
5439:
5440:
5441: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
5442: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5443:
5444:
5445:
5438: WHERE cust_account_id = L_JTF_OBJECT_ID(i);
5439:
5440:
5441: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
5442: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5443:
5444:
5445:
5446: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
5443:
5444:
5445:
5446: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
5447: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
5448:
5449: END IF;
5450:
5451: END LOOP;
5455: END IF;
5456:
5457: BEGIN
5458: OPEN C_COLLECTOR_PROF;
5459: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_collector_prof cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5460: L_COLLECTOR_RESOURCE_ID.delete ;
5461: L_COLLECTOR_ID.delete;
5462: L_RESOURCE_TYPE.delete;
5463: L_PARTY_ID.delete;
5473: L_CUST_ACCOUNT_ID
5474: limit l_max_fetches;
5475: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
5476:
5477: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_collector_prof ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5478: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5479: EXIT;
5480:
5481: ELSE
5474: limit l_max_fetches;
5475: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
5476:
5477: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_collector_prof ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5478: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5479: EXIT;
5480:
5481: ELSE
5482:
5488: last_update_date = SYSDATE,
5489: last_updated_by = FND_GLOBAL.USER_ID
5490: WHERE
5491: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5492: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_collector_prof updated ' || L_COLLECTOR_ID.count || ' rows ');
5493: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5494:
5495:
5496: END IF;
5489: last_updated_by = FND_GLOBAL.USER_ID
5490: WHERE
5491: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5492: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_collector_prof updated ' || L_COLLECTOR_ID.count || ' rows ');
5493: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5494:
5495:
5496: END IF;
5497: END LOOP;
5499: CLOSE C_COLLECTOR_PROF;
5500: END IF;
5501:
5502: EXCEPTION WHEN OTHERS THEN
5503: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Collector profile update received' || SQLERRM);
5504: END;
5505:
5506: BEGIN
5507: OPEN c_contact_point;
5504: END;
5505:
5506: BEGIN
5507: OPEN c_contact_point;
5508: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Opened Cursor c_contact_point cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5509: L_PARTY_ID.delete;
5510: L_PHONE_COUNTRY_CODE.delete;
5511: L_PHONE_AREA_CODE.delete;
5512: L_PHONE_NUMBER.delete;
5524:
5525: limit l_max_fetches;
5526: IF L_PARTY_ID.COUNT = 0 THEN
5527:
5528: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'c_contact_point Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5529: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5530: EXIT;
5531:
5532: ELSE
5525: limit l_max_fetches;
5526: IF L_PARTY_ID.COUNT = 0 THEN
5527:
5528: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'c_contact_point Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5529: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5530: EXIT;
5531:
5532: ELSE
5533:
5540: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
5541: last_update_date = SYSDATE,
5542: last_updated_by = FND_GLOBAL.USER_ID
5543: WHERE PARTY_ID = L_PARTY_ID(i);
5544: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
5545: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5546:
5547:
5548: END IF;
5541: last_update_date = SYSDATE,
5542: last_updated_by = FND_GLOBAL.USER_ID
5543: WHERE PARTY_ID = L_PARTY_ID(i);
5544: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
5545: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5546:
5547:
5548: END IF;
5549: END LOOP;
5550: CLOSE c_contact_point;
5551:
5552:
5553: EXCEPTION WHEN OTHERS THEN
5554: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point raised error ' || SQLERRM);
5555: END;
5556: -- gnramasa
5557: BEGIN
5558: OPEN C_ACCOUNT_DEL;
5555: END;
5556: -- gnramasa
5557: BEGIN
5558: OPEN C_ACCOUNT_DEL;
5559: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_del cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5560: L_CUST_ACCOUNT_ID.delete;
5561: L_NUMBER_OF_DELINQUENCIES.delete;
5562: L_PENDING_DELINQUENCIES.delete;
5563: L_COMPLETE_DELINQUENCIES.delete;
5573: L_ACTIVE_DELINQUENCIES
5574: limit l_max_fetches;
5575: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5576:
5577: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_del ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5578: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5579: EXIT;
5580:
5581: ELSE
5574: limit l_max_fetches;
5575: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5576:
5577: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_del ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5578: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5579: EXIT;
5580:
5581: ELSE
5582:
5587: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
5588: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
5589: WHERE
5590: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5591: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_del updated ' || L_COLLECTOR_ID.count || ' rows ');
5592: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5593:
5594:
5595: END IF;
5588: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
5589: WHERE
5590: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5591: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_del updated ' || L_COLLECTOR_ID.count || ' rows ');
5592: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5593:
5594:
5595: END IF;
5596: END LOOP;
5598: CLOSE C_ACCOUNT_DEL;
5599: END IF;
5600:
5601: EXCEPTION WHEN OTHERS THEN
5602: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
5603: END;
5604:
5605: BEGIN
5606: OPEN C_ACCOUNT_PRO;
5603: END;
5604:
5605: BEGIN
5606: OPEN C_ACCOUNT_PRO;
5607: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_pro cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5608: L_CUST_ACCOUNT_ID.delete;
5609: L_ACTIVE_PROMISES.delete;
5610: L_COMPLETE_PROMISES.delete;
5611: L_PENDING_PROMISES.delete;
5619: L_ACTIVE_PROMISES
5620: limit l_max_fetches;
5621: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5622:
5623: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_pro ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5624: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5625: EXIT;
5626:
5627: ELSE
5620: limit l_max_fetches;
5621: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5622:
5623: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_pro ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5624: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5625: EXIT;
5626:
5627: ELSE
5628:
5632: COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
5633: PENDING_PROMISES = L_PENDING_PROMISES(i)
5634: WHERE
5635: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5636: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
5637: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5638:
5639:
5640: END IF;
5633: PENDING_PROMISES = L_PENDING_PROMISES(i)
5634: WHERE
5635: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5636: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
5637: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5638:
5639:
5640: END IF;
5641: END LOOP;
5643: CLOSE C_ACCOUNT_PRO;
5644: END IF;
5645:
5646: EXCEPTION WHEN OTHERS THEN
5647: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Promise update received' || SQLERRM);
5648: END;
5649:
5650: BEGIN
5651: OPEN C_ACCOUNT_PRO_SUMM;
5648: END;
5649:
5650: BEGIN
5651: OPEN C_ACCOUNT_PRO_SUMM;
5652: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_pro_summ cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5653: L_CUST_ACCOUNT_ID.delete;
5654: L_NUMBER_OF_PROMISES.delete;
5655: L_BROKEN_PROMISE_AMOUNT .delete;
5656: L_PROMISE_AMOUNT.delete;
5664: L_PROMISE_AMOUNT
5665: limit l_max_fetches;
5666: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5667:
5668: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_pro_summ ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5669: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5670: EXIT;
5671:
5672: ELSE
5665: limit l_max_fetches;
5666: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5667:
5668: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_pro_summ ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5669: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5670: EXIT;
5671:
5672: ELSE
5673:
5677: BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
5678: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
5679: WHERE
5680: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5681: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
5682: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5683:
5684:
5685: END IF;
5678: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
5679: WHERE
5680: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5681: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
5682: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5683:
5684:
5685: END IF;
5686: END LOOP;
5688: CLOSE C_ACCOUNT_PRO_SUMM;
5689: END IF;
5690:
5691: EXCEPTION WHEN OTHERS THEN
5692: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Broken Promise update received' || SQLERRM);
5693: END;
5694:
5695: BEGIN
5696: OPEN C_ACCOUNT_SCORE;
5693: END;
5694:
5695: BEGIN
5696: OPEN C_ACCOUNT_SCORE;
5697: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_score cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5698: L_CUST_ACCOUNT_ID.delete;
5699: L_SCORE.delete;
5700:
5701: LOOP
5705: L_SCORE
5706: limit l_max_fetches;
5707: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5708:
5709: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_score ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5710: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5711: EXIT;
5712:
5713: ELSE
5706: limit l_max_fetches;
5707: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5708:
5709: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_score ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5710: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5711: EXIT;
5712:
5713: ELSE
5714:
5716: UPDATE IEX_DLN_UWQ_SUMMARY
5717: SET SCORE = L_SCORE(i)
5718: WHERE
5719: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5720: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_score updated ' || L_COLLECTOR_ID.count || ' rows ');
5721: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5722:
5723:
5724: END IF;
5717: SET SCORE = L_SCORE(i)
5718: WHERE
5719: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5720: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_score updated ' || L_COLLECTOR_ID.count || ' rows ');
5721: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5722:
5723:
5724: END IF;
5725: END LOOP;
5727: CLOSE C_ACCOUNT_SCORE;
5728: END IF;
5729:
5730: EXCEPTION WHEN OTHERS THEN
5731: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Score update received' || SQLERRM);
5732: END;
5733:
5734: BEGIN
5735: OPEN C_ACCOUNT_PAST_DUE;
5732: END;
5733:
5734: BEGIN
5735: OPEN C_ACCOUNT_PAST_DUE;
5736: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_account_past_due cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5737: L_CUST_ACCOUNT_ID.delete;
5738: L_PAST_DUE_INV_VALUE.delete;
5739:
5740: LOOP
5744: L_PAST_DUE_INV_VALUE
5745: limit l_max_fetches;
5746: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5747:
5748: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_past_due ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5749: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5750: EXIT;
5751:
5752: ELSE
5745: limit l_max_fetches;
5746: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5747:
5748: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_account_past_due ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5749: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5750: EXIT;
5751:
5752: ELSE
5753:
5755: UPDATE IEX_DLN_UWQ_SUMMARY
5756: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
5757: WHERE
5758: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5759: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
5760: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5761:
5762:
5763: END IF;
5756: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
5757: WHERE
5758: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5759: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_account_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
5760: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5761:
5762:
5763: END IF;
5764: END LOOP;
5766: CLOSE C_ACCOUNT_PAST_DUE;
5767: END IF;
5768:
5769: EXCEPTION WHEN OTHERS THEN
5770: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Past due invoice update received' || SQLERRM);
5771: END;
5772:
5773: BEGIN
5774: OPEN C_LAST_PAYMENT_NO_AMOUNT;
5771: END;
5772:
5773: BEGIN
5774: OPEN C_LAST_PAYMENT_NO_AMOUNT;
5775: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_last_payment_no_amount cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5776: L_CUST_ACCOUNT_ID.delete;
5777: L_LAST_PAYMENT_NUMBER.delete;
5778: L_LAST_PAYMENT_AMOUNT.delete;
5779:
5785: L_LAST_PAYMENT_AMOUNT
5786: limit l_max_fetches;
5787: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5788:
5789: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_last_payment_no_amount ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5790: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5791: EXIT;
5792:
5793: ELSE
5786: limit l_max_fetches;
5787: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5788:
5789: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_last_payment_no_amount ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5790: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5791: EXIT;
5792:
5793: ELSE
5794:
5797: SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
5798: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
5799: WHERE
5800: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5801: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
5802: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5803:
5804:
5805: END IF;
5798: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
5799: WHERE
5800: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5801: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
5802: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5803:
5804:
5805: END IF;
5806: END LOOP;
5808: CLOSE C_LAST_PAYMENT_NO_AMOUNT;
5809: END IF;
5810:
5811: EXCEPTION WHEN OTHERS THEN
5812: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
5813: END;
5814:
5815: BEGIN
5816: OPEN C_BANKRUPTCIES;
5813: END;
5814:
5815: BEGIN
5816: OPEN C_BANKRUPTCIES;
5817: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_bankruptcies cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5818: L_CUST_ACCOUNT_ID.delete;
5819: L_NUMBER_OF_BANKRUPTCIES.delete;
5820:
5821: LOOP
5825: L_NUMBER_OF_BANKRUPTCIES
5826: limit l_max_fetches;
5827: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5828:
5829: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_bankruptcies ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5830: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5831: EXIT;
5832:
5833: ELSE
5826: limit l_max_fetches;
5827: IF L_CUST_ACCOUNT_ID.COUNT = 0 THEN
5828:
5829: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_bankruptcies ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
5830: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
5831: EXIT;
5832:
5833: ELSE
5834:
5836: UPDATE IEX_DLN_UWQ_SUMMARY
5837: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
5838: WHERE
5839: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5840: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
5841: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5842:
5843:
5844: END IF;
5837: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
5838: WHERE
5839: CUST_ACCOUNT_ID = L_CUST_ACCOUNT_ID(i);
5840: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
5841: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
5842:
5843:
5844: END IF;
5845: END LOOP;
5847: CLOSE C_BANKRUPTCIES;
5848: END IF;
5849:
5850: EXCEPTION WHEN OTHERS THEN
5851: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
5852: END;
5853: --gnramasa
5854: COMMIT;
5855: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
5851: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
5852: END;
5853: --gnramasa
5854: COMMIT;
5855: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
5856:
5857: EXCEPTION
5858: WHEN OTHERS THEN
5859: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
5855: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
5856:
5857: EXCEPTION
5858: WHEN OTHERS THEN
5859: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
5860: END ACCOUNT_REFRESH_SUMMARY_INCR;
5861:
5862: PROCEDURE customer_refresh_summary_incr(
5863: x_errbuf OUT nocopy VARCHAR2,
6239:
6240: BEGIN
6241: l_max_fetches := to_number(nvl(fnd_profile.value('IEX_BATCH_SIZE'), '100000'));
6242: if p_mode='DLN' then
6243: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
6244: -- Start Bug #6251657 bibeura 25-OCT-2007
6245: BEGIN
6246: OPEN c_customer_del_dln;
6247: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_del_dln cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6243: LogMessage(FND_LOG.LEVEL_STATEMENT,'Starting..');
6244: -- Start Bug #6251657 bibeura 25-OCT-2007
6245: BEGIN
6246: OPEN c_customer_del_dln;
6247: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_del_dln cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6248: L_PARTY_ID.delete;
6249: L_NUMBER_OF_DELINQUENCIES.delete;
6250: L_PENDING_DELINQUENCIES.delete;
6251: L_COMPLETE_DELINQUENCIES.delete;
6264: L_ACTIVE_DELINQUENCIES,
6265: L_ORG_ID
6266: limit l_max_fetches;
6267: IF L_PARTY_ID.COUNT = 0 THEN
6268: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_del_dln ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6269: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6270: EXIT;
6271: ELSE
6272:
6265: L_ORG_ID
6266: limit l_max_fetches;
6267: IF L_PARTY_ID.COUNT = 0 THEN
6268: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_del_dln ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6269: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6270: EXIT;
6271: ELSE
6272:
6273: forall i IN L_PARTY_ID.FIRST .. L_PARTY_ID.LAST
6279: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
6280: WHERE
6281: PARTY_ID = L_PARTY_ID(i)
6282: AND ORG_ID=L_ORG_ID(i);
6283: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
6284: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6285: END IF;
6286: END LOOP;
6287: IF c_customer_del_dln%ISOPEN THEN
6280: WHERE
6281: PARTY_ID = L_PARTY_ID(i)
6282: AND ORG_ID=L_ORG_ID(i);
6283: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_del_dln updated ' || L_COLLECTOR_ID.count || ' rows ');
6284: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6285: END IF;
6286: END LOOP;
6287: IF c_customer_del_dln%ISOPEN THEN
6288: CLOSE c_customer_del_dln;
6288: CLOSE c_customer_del_dln;
6289: END IF;
6290:
6291: EXCEPTION WHEN OTHERS THEN
6292: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
6293: END;
6294: --End Bug #6251657 bibeura 25-OCT-2007
6295:
6296: insert into iex_pop_uwq_summ_gt(object_id,object_type,org_id)
6309:
6310: open c_iex_customer_uwq_summary;
6311: loop
6312: l_count := l_count +1;
6313: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
6314: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
6315:
6316: L_ORG_ID.delete;
6317: L_COLLECTOR_ID.delete;
6310: open c_iex_customer_uwq_summary;
6311: loop
6312: l_count := l_count +1;
6313: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
6314: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
6315:
6316: L_ORG_ID.delete;
6317: L_COLLECTOR_ID.delete;
6318: L_COLLECTOR_RESOURCE_ID.delete;
6384: L_PROMISE_AMOUNT.delete;
6385: L_ACTIVE_PROMISES.delete;
6386: L_COMPLETE_PROMISES.delete;
6387: L_PENDING_PROMISES.delete;
6388: LogMessage(FND_LOG.LEVEL_STATEMENT,'Start fetching records...');
6389: FETCH c_iex_customer_uwq_summary bulk collect
6390: INTO
6391: L_ORG_ID,
6392: L_IEU_OBJECT_FUNCTION,
6441: limit l_max_fetches;
6442:
6443: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
6444:
6445: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6446: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6447: EXIT;
6448:
6449: ELSE
6442:
6443: IF L_IEU_OBJECT_FUNCTION.COUNT = 0 THEN
6444:
6445: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6446: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6447: EXIT;
6448:
6449: ELSE
6450:
6447: EXIT;
6448:
6449: ELSE
6450:
6451: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6452: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
6453: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
6454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6455: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
6448:
6449: ELSE
6450:
6451: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6452: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
6453: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
6454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6455: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
6456: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
6449: ELSE
6450:
6451: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6452: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
6453: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
6454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6455: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
6456: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
6457: INSERT INTO IEX_DLN_UWQ_SUMMARY
6450:
6451: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6452: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
6453: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
6454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6455: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
6456: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
6457: INSERT INTO IEX_DLN_UWQ_SUMMARY
6458: (DLN_UWQ_SUMMARY_ID
6451: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6452: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows.');
6453: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inserting...');
6454: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6455: LogMessage(FND_LOG.LEVEL_STATEMENT,'inserting records..');
6456: forall i IN L_IEU_OBJECT_FUNCTION.FIRST .. L_IEU_OBJECT_FUNCTION.LAST
6457: INSERT INTO IEX_DLN_UWQ_SUMMARY
6458: (DLN_UWQ_SUMMARY_ID
6459: ,ORG_ID
6564: 0,
6565: 0,
6566: 0);
6567:
6568: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6569: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
6570:
6571:
6572: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
6565: 0,
6566: 0);
6567:
6568: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End inserting time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6569: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
6570:
6571:
6572: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
6573: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
6569: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Inserted ' || L_IEU_OBJECT_FUNCTION.COUNT || ' rows');
6570:
6571:
6572: l_total := l_total + L_IEU_OBJECT_FUNCTION.COUNT;
6573: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
6574:
6575: END IF;
6576:
6577: END LOOP;
6579:
6580: OPEN c_strategy_summary;
6581: LOOP
6582: l_count := l_count +1;
6583: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
6584: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
6585:
6586: L_JTF_OBJECT_ID.delete;
6587: L_WORK_ITEM_ID.delete;
6580: OPEN c_strategy_summary;
6581: LOOP
6582: l_count := l_count +1;
6583: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'----------');
6584: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Bulk ' || l_count);
6585:
6586: L_JTF_OBJECT_ID.delete;
6587: L_WORK_ITEM_ID.delete;
6588: L_SCHEDULE_START.delete;
6599: L_END_TIME.delete;
6600: L_WORK_ITEM_ORDER.delete;
6601: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
6602:
6603: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
6604:
6605: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6606: FETCH c_strategy_summary bulk collect
6607: INTO
6601: L_ESCALATED_YN.delete; --Added for bug#6981126 by schekuri on 27-Jun-2008
6602:
6603: LogMessage(FND_LOG.LEVEL_STATEMENT,'Inited all arrays');
6604:
6605: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6606: FETCH c_strategy_summary bulk collect
6607: INTO
6608: L_JTF_OBJECT_ID,
6609: L_WORK_ITEM_ID,
6622: L_WORK_ITEM_ORDER,
6623: L_ESCALATED_YN --Added for bug#6981126 by schekuri on 27-Jun-2008
6624: limit l_max_fetches;
6625:
6626: LogMessage(FND_LOG.LEVEL_STATEMENT,L_JTF_OBJECT_ID.COUNT);
6627:
6628: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
6629:
6630: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6626: LogMessage(FND_LOG.LEVEL_STATEMENT,L_JTF_OBJECT_ID.COUNT);
6627:
6628: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
6629:
6630: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6631: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6632: CLOSE c_strategy_summary;
6633: EXIT;
6634:
6627:
6628: IF L_JTF_OBJECT_ID.COUNT = 0 THEN
6629:
6630: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6631: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6632: CLOSE c_strategy_summary;
6633: EXIT;
6634:
6635: ELSE
6633: EXIT;
6634:
6635: ELSE
6636:
6637: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6638: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
6639: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
6640: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6641:
6634:
6635: ELSE
6636:
6637: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6638: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
6639: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
6640: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6641:
6642: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
6635: ELSE
6636:
6637: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6638: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
6639: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
6640: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6641:
6642: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
6643: UPDATE IEX_DLN_UWQ_SUMMARY
6636:
6637: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6638: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Fetched ' || L_JTF_OBJECT_ID.COUNT || ' rows.');
6639: LogMessage(FND_LOG.LEVEL_STATEMENT,' Updating table...');
6640: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Start updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6641:
6642: forall i IN L_JTF_OBJECT_ID.FIRST .. L_JTF_OBJECT_ID.LAST
6643: UPDATE IEX_DLN_UWQ_SUMMARY
6644: SET WORK_ITEM_ID = L_WORK_ITEM_ID(i),
6658: WKITEM_ESCALATED_YN = L_ESCALATED_YN(i) --Added for bug#6981126 by schekuri on 27-Jun-2008
6659: WHERE party_id = L_JTF_OBJECT_ID(i);
6660:
6661:
6662: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
6663: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6664:
6665: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
6666: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
6659: WHERE party_id = L_JTF_OBJECT_ID(i);
6660:
6661:
6662: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
6663: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6664:
6665: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
6666: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
6667:
6662: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Updated ' || L_JTF_OBJECT_ID.COUNT || ' rows');
6663: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End updating time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6664:
6665: l_total := l_total + L_JTF_OBJECT_ID.COUNT;
6666: LogMessage(FND_LOG.LEVEL_STATEMENT,'So far processed ' || l_total || ' rows');
6667:
6668: END IF;
6669:
6670: END LOOP;
6674: END IF;
6675:
6676: BEGIN
6677: OPEN C_COLLECTOR_PROF;
6678: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_collector_prof cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6679: L_COLLECTOR_RESOURCE_ID.delete ;
6680: L_COLLECTOR_ID.delete;
6681: L_RESOURCE_TYPE.delete;
6682: L_PARTY_ID.delete;
6690: L_PARTY_ID
6691: limit l_max_fetches;
6692: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
6693:
6694: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_collector_prof ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6695: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6696: EXIT;
6697:
6698: ELSE
6691: limit l_max_fetches;
6692: IF L_COLLECTOR_RESOURCE_ID.COUNT = 0 THEN
6693:
6694: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_collector_prof ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6695: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6696: EXIT;
6697:
6698: ELSE
6699:
6705: last_update_date = SYSDATE,
6706: last_updated_by = FND_GLOBAL.USER_ID
6707: WHERE
6708: party_id = L_PARTY_ID(i);
6709: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_collector_prof updated ' || L_COLLECTOR_ID.count || ' rows ');
6710: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6711:
6712: END IF;
6713: END LOOP;
6706: last_updated_by = FND_GLOBAL.USER_ID
6707: WHERE
6708: party_id = L_PARTY_ID(i);
6709: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_collector_prof updated ' || L_COLLECTOR_ID.count || ' rows ');
6710: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6711:
6712: END IF;
6713: END LOOP;
6714: IF C_COLLECTOR_PROF % ISOPEN THEN
6715: CLOSE C_COLLECTOR_PROF;
6716: END IF;
6717:
6718: EXCEPTION WHEN OTHERS THEN
6719: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Collector profile update received' || SQLERRM);
6720: END;
6721:
6722: BEGIN
6723: OPEN c_contact_point;
6720: END;
6721:
6722: BEGIN
6723: OPEN c_contact_point;
6724: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Opened Cursor c_contact_point cursor at time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6725: L_PARTY_ID.delete;
6726: L_PHONE_COUNTRY_CODE.delete;
6727: L_PHONE_AREA_CODE.delete;
6728: L_PHONE_NUMBER.delete;
6739: L_PHONE_EXTENSION
6740: limit l_max_fetches;
6741: IF L_PARTY_ID.COUNT = 0 THEN
6742:
6743: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'c_contact_point Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6744: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6745: EXIT;
6746:
6747: ELSE
6740: limit l_max_fetches;
6741: IF L_PARTY_ID.COUNT = 0 THEN
6742:
6743: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'c_contact_point Cursor Fetching end time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6744: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6745: EXIT;
6746:
6747: ELSE
6748:
6755: PHONE_EXTENSION = L_PHONE_EXTENSION(i),
6756: last_update_date = SYSDATE,
6757: last_updated_by = FND_GLOBAL.USER_ID
6758: WHERE PARTY_ID = L_PARTY_ID(i);
6759: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_contact_point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
6760: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6761:
6762: END IF;
6763: END LOOP;
6756: last_update_date = SYSDATE,
6757: last_updated_by = FND_GLOBAL.USER_ID
6758: WHERE PARTY_ID = L_PARTY_ID(i);
6759: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_contact_point Cursor updated ' ||L_PARTY_ID.count || ' rows ');
6760: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6761:
6762: END IF;
6763: END LOOP;
6764: CLOSE c_contact_point;
6764: CLOSE c_contact_point;
6765:
6766:
6767: EXCEPTION WHEN OTHERS THEN
6768: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point raised error ' || SQLERRM);
6769: END;
6770: BEGIN
6771: OPEN C_CUSTOMER_DEL;
6772: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_del cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6768: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' Contact point raised error ' || SQLERRM);
6769: END;
6770: BEGIN
6771: OPEN C_CUSTOMER_DEL;
6772: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_del cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6773: L_PARTY_ID.delete;
6774: L_NUMBER_OF_DELINQUENCIES.delete;
6775: L_PENDING_DELINQUENCIES.delete;
6776: L_COMPLETE_DELINQUENCIES.delete;
6786: L_ACTIVE_DELINQUENCIES
6787: limit l_max_fetches;
6788: IF L_PARTY_ID.COUNT = 0 THEN
6789:
6790: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_del ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6791: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6792: EXIT;
6793:
6794: ELSE
6787: limit l_max_fetches;
6788: IF L_PARTY_ID.COUNT = 0 THEN
6789:
6790: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_del ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6791: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6792: EXIT;
6793:
6794: ELSE
6795:
6800: COMPLETE_DELINQUENCIES = L_COMPLETE_DELINQUENCIES(i),
6801: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
6802: WHERE
6803: PARTY_ID = L_PARTY_ID(i);
6804: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_del updated ' || L_COLLECTOR_ID.count || ' rows ');
6805: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6806:
6807: END IF;
6808: END LOOP;
6801: PENDING_DELINQUENCIES = L_PENDING_DELINQUENCIES(i)
6802: WHERE
6803: PARTY_ID = L_PARTY_ID(i);
6804: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_del updated ' || L_COLLECTOR_ID.count || ' rows ');
6805: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6806:
6807: END IF;
6808: END LOOP;
6809: IF C_CUSTOMER_DEL % ISOPEN THEN
6810: CLOSE C_CUSTOMER_DEL;
6811: END IF;
6812:
6813: EXCEPTION WHEN OTHERS THEN
6814: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency update received' || SQLERRM);
6815: END;
6816:
6817: BEGIN
6818: OPEN C_CUSTOMER_PRO;
6815: END;
6816:
6817: BEGIN
6818: OPEN C_CUSTOMER_PRO;
6819: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_pro cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6820: L_PARTY_ID.delete;
6821: L_ACTIVE_PROMISES.delete;
6822: L_COMPLETE_PROMISES.delete;
6823: L_PENDING_PROMISES.delete;
6831: L_ACTIVE_PROMISES
6832: limit l_max_fetches;
6833: IF L_PARTY_ID.COUNT = 0 THEN
6834:
6835: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_pro ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6836: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6837: EXIT;
6838:
6839: ELSE
6832: limit l_max_fetches;
6833: IF L_PARTY_ID.COUNT = 0 THEN
6834:
6835: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_pro ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6836: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6837: EXIT;
6838:
6839: ELSE
6840:
6844: COMPLETE_PROMISES = L_COMPLETE_PROMISES(i),
6845: PENDING_PROMISES = L_PENDING_PROMISES(i)
6846: WHERE
6847: PARTY_ID = L_PARTY_ID(i);
6848: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
6849: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6850:
6851: END IF;
6852: END LOOP;
6845: PENDING_PROMISES = L_PENDING_PROMISES(i)
6846: WHERE
6847: PARTY_ID = L_PARTY_ID(i);
6848: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_pro updated ' || L_COLLECTOR_ID.count || ' rows ');
6849: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6850:
6851: END IF;
6852: END LOOP;
6853: IF C_CUSTOMER_PRO % ISOPEN THEN
6854: CLOSE C_CUSTOMER_PRO;
6855: END IF;
6856:
6857: EXCEPTION WHEN OTHERS THEN
6858: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Promise update received' || SQLERRM);
6859: END;
6860:
6861: BEGIN
6862: OPEN C_CUSTOMER_PRO_SUMM;
6859: END;
6860:
6861: BEGIN
6862: OPEN C_CUSTOMER_PRO_SUMM;
6863: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_pro_summ cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6864: L_PARTY_ID.delete;
6865: L_NUMBER_OF_PROMISES.delete;
6866: L_BROKEN_PROMISE_AMOUNT .delete;
6867: L_PROMISE_AMOUNT.delete;
6875: L_PROMISE_AMOUNT
6876: limit l_max_fetches;
6877: IF L_PARTY_ID.COUNT = 0 THEN
6878:
6879: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_pro_summ ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6880: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6881: EXIT;
6882:
6883: ELSE
6876: limit l_max_fetches;
6877: IF L_PARTY_ID.COUNT = 0 THEN
6878:
6879: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_pro_summ ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6880: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6881: EXIT;
6882:
6883: ELSE
6884:
6888: BROKEN_PROMISE_AMOUNT = L_BROKEN_PROMISE_AMOUNT(i),
6889: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
6890: WHERE
6891: PARTY_ID = L_PARTY_ID(i);
6892: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
6893: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6894:
6895: END IF;
6896: END LOOP;
6889: PROMISE_AMOUNT = L_PROMISE_AMOUNT(i)
6890: WHERE
6891: PARTY_ID = L_PARTY_ID(i);
6892: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_pro_summ updated ' || L_COLLECTOR_ID.count || ' rows ');
6893: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6894:
6895: END IF;
6896: END LOOP;
6897: IF C_CUSTOMER_PRO_SUMM % ISOPEN THEN
6898: CLOSE C_CUSTOMER_PRO_SUMM;
6899: END IF;
6900:
6901: EXCEPTION WHEN OTHERS THEN
6902: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Broken Promise update received' || SQLERRM);
6903: END;
6904:
6905: BEGIN
6906: OPEN C_CUSTOMER_SCORE;
6903: END;
6904:
6905: BEGIN
6906: OPEN C_CUSTOMER_SCORE;
6907: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_score cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6908: L_PARTY_ID.delete;
6909: L_SCORE.delete;
6910:
6911: LOOP
6915: L_SCORE
6916: limit l_max_fetches;
6917: IF L_PARTY_ID.COUNT = 0 THEN
6918:
6919: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_score ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6920: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6921: EXIT;
6922:
6923: ELSE
6916: limit l_max_fetches;
6917: IF L_PARTY_ID.COUNT = 0 THEN
6918:
6919: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_score ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6920: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6921: EXIT;
6922:
6923: ELSE
6924:
6926: UPDATE IEX_DLN_UWQ_SUMMARY
6927: SET SCORE = L_SCORE(i)
6928: WHERE
6929: PARTY_ID = L_PARTY_ID(i);
6930: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_score updated ' || L_COLLECTOR_ID.count || ' rows ');
6931: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6932:
6933: END IF;
6934: END LOOP;
6927: SET SCORE = L_SCORE(i)
6928: WHERE
6929: PARTY_ID = L_PARTY_ID(i);
6930: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_score updated ' || L_COLLECTOR_ID.count || ' rows ');
6931: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6932:
6933: END IF;
6934: END LOOP;
6935: IF C_CUSTOMER_SCORE % ISOPEN THEN
6936: CLOSE C_CUSTOMER_SCORE;
6937: END IF;
6938:
6939: EXCEPTION WHEN OTHERS THEN
6940: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Score update received' || SQLERRM);
6941: END;
6942:
6943: BEGIN
6944: OPEN C_CUSTOMER_PAST_DUE;
6941: END;
6942:
6943: BEGIN
6944: OPEN C_CUSTOMER_PAST_DUE;
6945: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_customer_past_due cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6946: L_PARTY_ID.delete;
6947: L_PAST_DUE_INV_VALUE.delete;
6948:
6949: LOOP
6953: L_PAST_DUE_INV_VALUE
6954: limit l_max_fetches;
6955: IF L_PARTY_ID.COUNT = 0 THEN
6956:
6957: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_past_due ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6958: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6959: EXIT;
6960:
6961: ELSE
6954: limit l_max_fetches;
6955: IF L_PARTY_ID.COUNT = 0 THEN
6956:
6957: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_customer_past_due ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6958: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6959: EXIT;
6960:
6961: ELSE
6962:
6964: UPDATE IEX_DLN_UWQ_SUMMARY
6965: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
6966: WHERE
6967: PARTY_ID = L_PARTY_ID(i);
6968: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
6969: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6970:
6971: END IF;
6972: END LOOP;
6965: SET PAST_DUE_INV_VALUE = L_PAST_DUE_INV_VALUE(i)
6966: WHERE
6967: PARTY_ID = L_PARTY_ID(i);
6968: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_customer_past_due updated ' || L_COLLECTOR_ID.count || ' rows ');
6969: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
6970:
6971: END IF;
6972: END LOOP;
6973: IF C_CUSTOMER_PAST_DUE % ISOPEN THEN
6974: CLOSE C_CUSTOMER_PAST_DUE;
6975: END IF;
6976:
6977: EXCEPTION WHEN OTHERS THEN
6978: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Past due invoice update received' || SQLERRM);
6979: END;
6980:
6981: BEGIN
6982: OPEN C_LAST_PAYMENT_NO_AMOUNT;
6979: END;
6980:
6981: BEGIN
6982: OPEN C_LAST_PAYMENT_NO_AMOUNT;
6983: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_last_payment_no_amount cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6984: L_PARTY_ID.delete;
6985: L_LAST_PAYMENT_NUMBER.delete;
6986: L_LAST_PAYMENT_AMOUNT.delete;
6987:
6993: L_LAST_PAYMENT_AMOUNT
6994: limit l_max_fetches;
6995: IF L_PARTY_ID.COUNT = 0 THEN
6996:
6997: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_last_payment_no_amount ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6998: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6999: EXIT;
7000:
7001: ELSE
6994: limit l_max_fetches;
6995: IF L_PARTY_ID.COUNT = 0 THEN
6996:
6997: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_last_payment_no_amount ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
6998: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
6999: EXIT;
7000:
7001: ELSE
7002:
7005: SET LAST_PAYMENT_NUMBER = L_LAST_PAYMENT_NUMBER(i),
7006: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
7007: WHERE
7008: PARTY_ID = L_PARTY_ID(i);
7009: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
7010: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
7011:
7012: END IF;
7013: END LOOP;
7006: LAST_PAYMENT_AMOUNT = L_LAST_PAYMENT_AMOUNT(i)
7007: WHERE
7008: PARTY_ID = L_PARTY_ID(i);
7009: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_last_payment_no_amount updated ' || L_COLLECTOR_ID.count || ' rows ');
7010: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
7011:
7012: END IF;
7013: END LOOP;
7014: IF C_LAST_PAYMENT_NO_AMOUNT % ISOPEN THEN
7015: CLOSE C_LAST_PAYMENT_NO_AMOUNT;
7016: END IF;
7017:
7018: EXCEPTION WHEN OTHERS THEN
7019: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
7020: END;
7021:
7022: BEGIN
7023: OPEN C_BANKRUPTCIES;
7020: END;
7021:
7022: BEGIN
7023: OPEN C_BANKRUPTCIES;
7024: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Open c_bankruptcies cursor time: ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
7025: L_PARTY_ID.delete;
7026: L_NUMBER_OF_BANKRUPTCIES.delete;
7027:
7028: LOOP
7032: L_NUMBER_OF_BANKRUPTCIES
7033: limit l_max_fetches;
7034: IF L_PARTY_ID.COUNT = 0 THEN
7035:
7036: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_bankruptcies ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
7037: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
7038: EXIT;
7039:
7040: ELSE
7033: limit l_max_fetches;
7034: IF L_PARTY_ID.COUNT = 0 THEN
7035:
7036: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'End fetching time: c_bankruptcies ' || to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
7037: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'No records found - processing complete. Exiting the loop');
7038: EXIT;
7039:
7040: ELSE
7041:
7043: UPDATE IEX_DLN_UWQ_SUMMARY
7044: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
7045: WHERE
7046: PARTY_ID = L_PARTY_ID(i);
7047: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
7048: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
7049:
7050: END IF;
7051: END LOOP;
7044: SET NUMBER_OF_BANKRUPTCIES = L_NUMBER_OF_BANKRUPTCIES(i)
7045: WHERE
7046: PARTY_ID = L_PARTY_ID(i);
7047: LogMessage(FND_LOG.LEVEL_UNEXPECTED,' c_bankruptcies updated ' || L_COLLECTOR_ID.count || ' rows ');
7048: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'------------------------');
7049:
7050: END IF;
7051: END LOOP;
7052: IF C_BANKRUPTCIES % ISOPEN THEN
7053: CLOSE C_BANKRUPTCIES;
7054: END IF;
7055:
7056: EXCEPTION WHEN OTHERS THEN
7057: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
7058: END;
7059: --gnramasa
7060: COMMIT;
7061: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
7057: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Last payment no and amount update received' || SQLERRM);
7058: END;
7059: --gnramasa
7060: COMMIT;
7061: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
7062:
7063: EXCEPTION
7064: WHEN OTHERS THEN
7065: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
7061: LogMessage(FND_LOG.LEVEL_STATEMENT,'Commited');
7062:
7063: EXCEPTION
7064: WHEN OTHERS THEN
7065: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
7066: END CUSTOMER_REFRESH_SUMMARY_INCR;
7067:
7068: PROCEDURE refresh_summary_incr(
7069: x_errbuf OUT nocopy VARCHAR2,
7076: BEGIN
7077: OPEN c_get_level;
7078: FETCH c_get_level INTO l_level;
7079: CLOSE c_get_level;
7080: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Level = ' || l_level);
7081:
7082: IF l_level = 'CUSTOMER' THEN
7083: customer_refresh_summary_incr(x_errbuf,
7084: x_retcode,
7095: from_date,
7096: p_mode);
7097:
7098: ELSE
7099: LogMessage(FND_LOG.LEVEL_UNEXPECTED,'Delinquency level is not implemented at this time. Exiting.');
7100: return;
7101: END IF;
7102: EXCEPTION
7103: WHEN OTHERS THEN
7100: return;
7101: END IF;
7102: EXCEPTION
7103: WHEN OTHERS THEN
7104: LogMessage(FND_LOG.LEVEL_STATEMENT,sqlerrm);
7105: END;
7106:
7107: BEGIN
7108: G_LOG_ENABLED := 'N';
7105: END;
7106:
7107: BEGIN
7108: G_LOG_ENABLED := 'N';
7109: G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
7110:
7111: /* getting msg logging info */
7112: G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
7113: if (G_LOG_ENABLED = 'N') then
7110:
7111: /* getting msg logging info */
7112: G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
7113: if (G_LOG_ENABLED = 'N') then
7114: G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
7115: else
7116: G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
7117: end if;
7118:
7112: G_LOG_ENABLED := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'), 'N');
7113: if (G_LOG_ENABLED = 'N') then
7114: G_MSG_LEVEL := FND_LOG.LEVEL_UNEXPECTED;
7115: else
7116: G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
7117: end if;
7118:
7119: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
7120: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
7115: else
7116: G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
7117: end if;
7118:
7119: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
7120: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
7121: END;
7116: G_MSG_LEVEL := NVL(to_number(FND_PROFILE.VALUE('AFLOG_LEVEL')), FND_LOG.LEVEL_UNEXPECTED);
7117: end if;
7118:
7119: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_LOG_ENABLED: ' || G_LOG_ENABLED);
7120: LogMessage(FND_LOG.LEVEL_STATEMENT, 'G_MSG_LEVEL: ' || G_MSG_LEVEL);
7121: END;