[Home] [Help]
71: l_time_alloc_id number := -1;
72:
73: BEGIN
74:
75: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
76:
77: OPEN time_seq_csr;
78: FETCH time_seq_csr INTO l_time_alloc_id;
79: CLOSE time_seq_csr;
94: return l_time_alloc_id;
95:
96: EXCEPTION
97: WHEN OTHERS THEN
98: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
99: END get_time_allocation_id;
100:
101:
102:
129: l_product_alloc_id number := -1;
130:
131: BEGIN
132:
133: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
134:
135: OPEN product_seq_csr;
136: FETCH product_seq_csr INTO l_product_alloc_id;
137: CLOSE product_seq_csr;
152: return l_product_alloc_id;
153:
154: EXCEPTION
155: WHEN OTHERS THEN
156: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
157: END get_product_allocation_id;
158:
159:
160:
188: l_account_alloc_id number := -1;
189:
190: BEGIN
191:
192: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
193:
194: OPEN account_seq_csr;
195: FETCH account_seq_csr INTO l_account_alloc_id;
196: CLOSE account_seq_csr;
211: return l_account_alloc_id;
212:
213: EXCEPTION
214: WHEN OTHERS THEN
215: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
216: END get_account_allocation_id;
217:
218:
219: -- ------------------------
472: l_item_type := p_item_type;
473: l_time_id := p_time_id;
474:
475: /*
476: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
477: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
478: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
479: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
480: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
473: l_time_id := p_time_id;
474:
475: /*
476: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
477: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
478: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
479: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
480: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
481: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
474:
475: /*
476: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
477: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
478: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
479: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
480: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
481: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
482: */
475: /*
476: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
477: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
478: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
479: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
480: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
481: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
482: */
483:
476: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
477: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
478: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
479: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
480: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
481: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
482: */
483:
484:
477: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
478: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
479: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
480: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
481: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
482: */
483:
484:
485: IF l_object_type IN ('ROOT', 'CUST')
617: l_item_key := p_object_id ||'_'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
618: l_parameter_list := WF_PARAMETER_LIST_T();
619: l_event_name := 'oracle.apps.ozf.quota.TargetApproval';
620:
621: ozf_utility_pvt.debug_message('-> Raising Bussiness EVENT for Account Allocation Id == '||p_object_id||' ; ' );
622: ozf_utility_pvt.debug_message('-> ITEM KEY == '||l_item_key||' ; ' );
623:
624: wf_event.AddParameterToList(p_name => 'P_ACCOUNT_ALLOCATION_ID',
625: p_value => p_object_id,
618: l_parameter_list := WF_PARAMETER_LIST_T();
619: l_event_name := 'oracle.apps.ozf.quota.TargetApproval';
620:
621: ozf_utility_pvt.debug_message('-> Raising Bussiness EVENT for Account Allocation Id == '||p_object_id||' ; ' );
622: ozf_utility_pvt.debug_message('-> ITEM KEY == '||l_item_key||' ; ' );
623:
624: wf_event.AddParameterToList(p_name => 'P_ACCOUNT_ALLOCATION_ID',
625: p_value => p_object_id,
626: p_parameterlist => l_parameter_list);
632:
633: EXCEPTION
634: WHEN OTHERS THEN
635: RAISE Fnd_Api.g_exc_error;
636: ozf_utility_pvt.debug_message('Exception in raising business event for Target Approval');
637: END;
638:
639:
640:
1079:
1080:
1081: BEGIN
1082:
1083: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1084:
1085: -- Standard Start of API savepoint
1086: SAVEPOINT create_old_fact_product_spread;
1087:
1129:
1130: l_start_date := to_char(l_allocation_rec.from_date, 'YYYY/MM/DD');
1131: l_end_date := to_char(l_allocation_rec.to_date, 'YYYY/MM/DD');
1132:
1133: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
1134: ||l_start_date||' AND '||l_end_date||' ; '
1135: ||' Period_Type_id = '||l_period_type_id||' ; ');
1136:
1137: IF l_start_date IS NULL OR
1150: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
1151: RAISE OZF_TP_BLANK_PERIOD_TBL;
1152: END IF;
1153:
1154: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
1155: ||' AND '||l_end_date||' ; ');
1156: IF l_period_tbl IS NOT NULL THEN
1157: IF l_period_tbl.COUNT > 0 THEN
1158: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
1160: IF l_period_tbl.exists(l_idx) THEN
1161:
1162: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1163:
1164: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1165: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1166: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1167: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1168: END IF;
1162: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1163:
1164: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1165: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1166: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1167: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1168: END IF;
1169: END LOOP;
1170: END IF;
1169: END LOOP;
1170: END IF;
1171: END IF;
1172:
1173: --OZF_UTILITY_PVT.debug_message(' out of lysp period table');
1174:
1175: --l_in_clause := '(';
1176: l_in_clause := NULL;
1177: IF l_period_tbl IS NOT NULL THEN
1185: END LOOP;
1186: END IF;
1187: --l_in_clause := l_in_clause||')';
1188:
1189: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_in_clause == '||l_in_clause);
1190:
1191: --l_lysp_in_clause := '(';
1192: l_lysp_in_clause := NULL;
1193: IF l_lysp_period_tbl IS NOT NULL THEN
1201: END LOOP;
1202: END IF;
1203: --l_lysp_in_clause := l_lysp_in_clause||')';
1204:
1205: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_lysp_in_clause == '||l_lysp_in_clause);
1206:
1207: l_get_total_target_sql := l_get_total_target_sql||l_in_clause ||')';
1208:
1209: OPEN get_total_target_csr FOR l_get_total_target_sql USING l_fund_id;
1219: l_multiplying_factor := 0;
1220: END IF;
1221:
1222:
1223: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
1224:
1225:
1226: ------- Insert rows for PRODUCT and TIME Allocation Records for given FACT ------------------------------
1227:
1225:
1226: ------- Insert rows for PRODUCT and TIME Allocation Records for given FACT ------------------------------
1227:
1228:
1229: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating Product and Time Allocations Records'
1230: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1231:
1232: FOR root_product_rec IN root_product_spread_csr(l_fund_id)
1233: LOOP
1463: --------END: ORIGINAL FIX for difference due to ROUNDING --------------------------------------------------|
1464:
1465:
1466:
1467: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
1468: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1469:
1470: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
1471:
1466:
1467: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
1468: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1469:
1470: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
1471:
1472: EXCEPTION
1473: WHEN OZF_TP_DIFF_TIME_SPREAD THEN
1474: ROLLBACK TO create_old_fact_product_spread;
1476: FND_MSG_PUB.add;
1477: x_return_status := FND_API.g_ret_sts_error ;
1478: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1479: p_data => x_error_message);
1480: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
1481: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
1482:
1483: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
1484: ROLLBACK TO create_old_fact_product_spread;
1477: x_return_status := FND_API.g_ret_sts_error ;
1478: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1479: p_data => x_error_message);
1480: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
1481: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
1482:
1483: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
1484: ROLLBACK TO create_old_fact_product_spread;
1485: FND_MESSAGE.set_name('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT');
1486: FND_MSG_PUB.add;
1487: x_return_status := FND_API.g_ret_sts_error ;
1488: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1489: p_data => x_error_message);
1490: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
1491: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
1492:
1493: WHEN FND_API.G_EXC_ERROR THEN
1494: ROLLBACK TO create_old_fact_product_spread;
1487: x_return_status := FND_API.g_ret_sts_error ;
1488: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1489: p_data => x_error_message);
1490: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
1491: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
1492:
1493: WHEN FND_API.G_EXC_ERROR THEN
1494: ROLLBACK TO create_old_fact_product_spread;
1495: x_return_status := FND_API.g_ret_sts_error ;
1509: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
1510: END IF;
1511: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
1512: p_data => x_error_message);
1513: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
1514:
1515: END create_old_fact_product_spread;
1516:
1517:
1790:
1791:
1792: BEGIN
1793:
1794: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1795:
1796: -- Standard Start of API savepoint
1797: SAVEPOINT create_fact_product_spread;
1798:
1822: l_territory_id := l_fact_rec.node_id;
1823:
1824: l_org_id := get_org_id(l_territory_id); --Bugfix 7540057
1825:
1826: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Territory Id => '
1827: || l_territory_id ||' ; ');
1828:
1829: OPEN allocation_csr(l_fact_rec.activity_metric_id);
1830: FETCH allocation_csr INTO l_allocation_rec;
1843:
1844: l_start_date := to_char(l_allocation_rec.from_date, 'YYYY/MM/DD');
1845: l_end_date := to_char(l_allocation_rec.to_date, 'YYYY/MM/DD');
1846:
1847: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
1848: ||l_start_date||' AND '||l_end_date||' ; '
1849: ||' Period_Type_id = '||l_period_type_id||' ; ');
1850:
1851: IF l_start_date IS NULL OR
1864: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
1865: RAISE OZF_TP_BLANK_PERIOD_TBL;
1866: END IF;
1867:
1868: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
1869: ||' AND '||l_end_date||' ; ');
1870: IF l_period_tbl IS NOT NULL THEN
1871: IF l_period_tbl.COUNT > 0 THEN
1872: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
1874: IF l_period_tbl.exists(l_idx) THEN
1875:
1876: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1877:
1878: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1879: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1880: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1881: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1882: END IF;
1876: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1877:
1878: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1879: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1880: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1881: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1882: END IF;
1883: END LOOP;
1884: END IF;
1883: END LOOP;
1884: END IF;
1885: END IF;
1886:
1887: --OZF_UTILITY_PVT.debug_message(' out of lysp period table');
1888:
1889: --l_in_clause := '(';
1890: l_in_clause := NULL;
1891: IF l_period_tbl IS NOT NULL THEN
1899: END LOOP;
1900: END IF;
1901: --l_in_clause := l_in_clause||')';
1902:
1903: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_in_clause == '||l_in_clause);
1904:
1905: --l_lysp_in_clause := '(';
1906: l_lysp_in_clause := NULL;
1907: IF l_lysp_period_tbl IS NOT NULL THEN
1915: END LOOP;
1916: END IF;
1917: --l_lysp_in_clause := l_lysp_in_clause||')';
1918:
1919: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_lysp_in_clause == '||l_lysp_in_clause);
1920:
1921: --//00000 May17th,2004 - as per new requirements in Bug 3594874 000000000000000000000000000000000000
1922: /*
1923: l_get_total_target_sql := l_get_total_target_sql||l_in_clause ||')';
1934: ELSE
1935: l_multiplying_factor := 0;
1936: END IF;
1937:
1938: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
1939: */
1940: --//0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
1941:
1942:
1944:
1945:
1946: l_grand_total_lysp_sales := 0;
1947:
1948: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating Product and Time Allocations Records'
1949: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1950:
1951: FOR root_product_rec IN root_product_spread_csr(l_fund_id)
1952: LOOP
2122: ELSE
2123: l_multiplying_factor := 0;
2124: END IF;
2125:
2126: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
2127:
2128: UPDATE OZF_TIME_ALLOCATIONS t
2129: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
2130: t.object_version_number = t.object_version_number + 1,
2324: ---END: ORIGINAL FIX for difference due to ROUNDING --------------------------------------------------|
2325:
2326:
2327:
2328: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
2329: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
2330:
2331: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
2332:
2327:
2328: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
2329: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
2330:
2331: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
2332:
2333: EXCEPTION
2334: WHEN OZF_TP_DIFF_TIME_SPREAD THEN
2335: ROLLBACK TO create_fact_product_spread;
2337: FND_MSG_PUB.add;
2338: x_return_status := FND_API.g_ret_sts_error ;
2339: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2340: p_data => x_error_message);
2341: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
2342: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
2343:
2344: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
2345: ROLLBACK TO create_fact_product_spread;
2338: x_return_status := FND_API.g_ret_sts_error ;
2339: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2340: p_data => x_error_message);
2341: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
2342: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
2343:
2344: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
2345: ROLLBACK TO create_fact_product_spread;
2346: FND_MESSAGE.set_name('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT');
2347: FND_MSG_PUB.add;
2348: x_return_status := FND_API.g_ret_sts_error ;
2349: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2350: p_data => x_error_message);
2351: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
2352: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
2353:
2354: WHEN FND_API.G_EXC_ERROR THEN
2355: ROLLBACK TO create_fact_product_spread;
2348: x_return_status := FND_API.g_ret_sts_error ;
2349: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2350: p_data => x_error_message);
2351: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
2352: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
2353:
2354: WHEN FND_API.G_EXC_ERROR THEN
2355: ROLLBACK TO create_fact_product_spread;
2356: x_return_status := FND_API.g_ret_sts_error ;
2370: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
2371: END IF;
2372: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
2373: p_data => x_error_message);
2374: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
2375:
2376: END create_fact_product_spread;
2377:
2378:
2611:
2612:
2613: BEGIN
2614:
2615: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
2616:
2617: -- Standard Start of API savepoint
2618: SAVEPOINT create_root_product_spread;
2619:
2654: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
2655: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
2656: l_period_type_id := l_fund_rec.period_type_id;
2657:
2658: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
2659: ||l_start_date||' AND '||l_end_date||' ; '
2660: ||' Period_Type_id = '||l_period_type_id||' ; ');
2661:
2662: IF l_start_date IS NULL OR
2674: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
2675: RAISE OZF_TP_BLANK_PERIOD_TBL;
2676: END IF;
2677:
2678: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
2679: ||' AND '||l_end_date||' ; ');
2680: IF l_period_tbl IS NOT NULL THEN
2681: IF l_period_tbl.COUNT > 0 THEN
2682: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
2684: IF l_period_tbl.exists(l_idx) THEN
2685:
2686: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
2687:
2688: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
2689: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
2690: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
2691: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
2692: END IF;
2686: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
2687:
2688: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
2689: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
2690: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
2691: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
2692: END IF;
2693: END LOOP;
2694: END IF;
2709: END LOOP;
2710: END IF;
2711: l_in_clause := l_in_clause||')';
2712:
2713: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_in_clause == '||l_in_clause);
2714:
2715: ------- Insert rows for PRODUCTS ------------------------------------------------
2716:
2717: l_grand_total_lysp_sales := 0;
2715: ------- Insert rows for PRODUCTS ------------------------------------------------
2716:
2717: l_grand_total_lysp_sales := 0;
2718:
2719: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating Product and Time Allocations Records'
2720: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
2721:
2722: FOR product_rec IN product_elig_csr(l_fund_id)
2723: LOOP
3118: l_grand_total_lysp_sales := l_grand_total_lysp_sales + l_total_lysp_sales;
3119:
3120:
3121:
3122: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
3123: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3124:
3125: ------ Updating Target ----------------------------------------------------------------
3126:
3128: l_total_root_quota := NVL(l_fund_rec.original_budget, 0) + NVL(l_fund_rec.transfered_in_amt, 0);
3129:
3130: l_total_root_quota := NVL(l_total_root_quota, 0);
3131:
3132: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Total Root Quota'
3133: || 'FOR Fund_id = '|| l_fund_id || 'is == ' ||l_total_root_quota||' ;');
3134:
3135: l_multiplying_factor := 0;
3136: IF l_grand_total_lysp_sales > 0 THEN
3138: ELSE
3139: l_multiplying_factor := 0;
3140: END IF;
3141:
3142: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor == '
3143: ||l_multiplying_factor||' ;');
3144: /*
3145: UPDATE OZF_PRODUCT_ALLOCATIONS p
3146: SET p.TARGET = ROUND( (NVL(p.LYSP_SALES, 0) * l_multiplying_factor), 0),
3443: */
3444:
3445:
3446:
3447: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3448:
3449: EXCEPTION
3450: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
3451: ROLLBACK TO create_root_product_spread;
3453: FND_MSG_PUB.add;
3454: x_return_status := FND_API.g_ret_sts_error ;
3455: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3456: p_data => x_error_message);
3457: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
3458: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
3459:
3460: WHEN FND_API.G_EXC_ERROR THEN
3461: ROLLBACK TO create_root_product_spread;
3454: x_return_status := FND_API.g_ret_sts_error ;
3455: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3456: p_data => x_error_message);
3457: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
3458: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
3459:
3460: WHEN FND_API.G_EXC_ERROR THEN
3461: ROLLBACK TO create_root_product_spread;
3462: x_return_status := FND_API.g_ret_sts_error ;
3476: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3477: END IF;
3478: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3479: p_data => x_error_message);
3480: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3481:
3482: END create_root_product_spread;
3483:
3484:
3540: l_fact_rec fact_csr%ROWTYPE;
3541:
3542: BEGIN
3543:
3544: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
3545:
3546: -- Standard Start of API savepoint
3547: SAVEPOINT publish_fact_product_spread;
3548:
3577: --=========>>> Can do some more validation here to check if the caller JTT Quota page is not calling this by mistake
3578: --=========>>> It should call this only after Updating the FACT record with the Newly created FUND id
3579:
3580: ELSE
3581: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Fact Number : ' ||l_fact_id|| ' is : '||
3582: l_status_code);
3583: RAISE OZF_TP_OPER_NOT_ALLOWED;
3584: END IF;
3585:
3587: ------- Start Publishing Product Allocation Records for allocations of l_fact_id -----------------------
3588:
3589: l_fund_id := l_fact_rec.act_metric_used_by_id;
3590:
3591: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Publishing Product Allocations Records'
3592: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3593:
3594: UPDATE ozf_product_allocations p
3595: SET p.fund_id = l_fund_id,
3607:
3608: */
3609:
3610:
3611: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Publishing Product Allocations Records'
3612: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3613:
3614: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3615:
3610:
3611: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Publishing Product Allocations Records'
3612: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3613:
3614: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3615:
3616: EXCEPTION
3617: WHEN OZF_TP_OPER_NOT_ALLOWED THEN
3618: ROLLBACK TO publish_fact_product_spread;
3620: FND_MSG_PUB.add;
3621: x_return_status := FND_API.g_ret_sts_error ;
3622: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3623: p_data => x_error_message);
3624: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_OPER_NOT_ALLOWED_TXT'));
3625:
3626: WHEN FND_API.G_EXC_ERROR THEN
3627: ROLLBACK TO publish_fact_product_spread;
3628: x_return_status := FND_API.g_ret_sts_error ;
3642: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3643: END IF;
3644: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3645: p_data => x_error_message);
3646: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3647:
3648: END publish_fact_product_spread;
3649:
3650:
3685:
3686:
3687: BEGIN
3688:
3689: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
3690:
3691: -- Standard Start of API savepoint
3692: SAVEPOINT delete_fact_product_spread;
3693:
3711: l_fact_id := p_fact_id;
3712:
3713: ------- Start Deleting Product and Time Allocation Records -----------------------
3714:
3715: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
3716: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3717:
3718: FOR fact_product_spread_rec IN fact_product_spread_csr(l_fact_id)
3719: LOOP
3726: WHERE p.allocation_for = 'FACT'
3727: AND p.allocation_for_id = l_fact_id;
3728:
3729:
3730: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3731: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3732: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3733:
3734: EXCEPTION
3728:
3729:
3730: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3731: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3732: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3733:
3734: EXCEPTION
3735: WHEN FND_API.G_EXC_ERROR THEN
3736: ROLLBACK TO delete_fact_product_spread;
3751: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3752: END IF;
3753: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3754: p_data => x_error_message);
3755: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3756:
3757: END delete_fact_product_spread;
3758:
3759:
3859:
3860:
3861: BEGIN
3862:
3863: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
3864:
3865: -- Standard Start of API savepoint
3866: SAVEPOINT delete_cascade_product_spread;
3867:
3893:
3894: IF l_status_code IN ('DRAFT', 'PLANNING') THEN
3895: null;
3896: ELSE
3897: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Root Fund Number : ' ||l_fund_id|| ' is : '||
3898: l_status_code);
3899: RAISE OZF_TP_CHG_PS_NOT_ALLOWED;
3900: END IF;
3901:
3901:
3902:
3903: ------- Start Deleting Product and Time Allocation Records for allocations of l_fund_id -----------------------
3904:
3905: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
3906: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
3907:
3908: FOR allocation_rec IN allocation_csr(l_fund_id)
3909: LOOP
3918: );
3919: END LOOP;
3920: END LOOP;
3921:
3922: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3923: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
3924:
3925: ------- Start Deleting Product and Time Allocation Records for ROOT i.e. l_fund_id ----------------------
3926:
3924:
3925: ------- Start Deleting Product and Time Allocation Records for ROOT i.e. l_fund_id ----------------------
3926:
3927:
3928: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
3929: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3930:
3931: FOR fund_product_spread_rec IN fund_product_spread_csr(l_fund_id)
3932: LOOP
3940: AND p.allocation_for_id = l_fund_id
3941: AND p.fund_id = l_fund_id;
3942:
3943:
3944: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3945: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3946:
3947:
3948: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3944: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3945: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3946:
3947:
3948: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3949:
3950: EXCEPTION
3951: WHEN OZF_TP_CHG_PS_NOT_ALLOWED THEN
3952: ROLLBACK TO delete_cascade_product_spread;
3954: FND_MSG_PUB.add;
3955: x_return_status := FND_API.g_ret_sts_error ;
3956: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3957: p_data => x_error_message);
3958: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_CHG_PS_NOT_ALLOWED_TXT'));
3959:
3960: WHEN FND_API.G_EXC_ERROR THEN
3961: ROLLBACK TO delete_cascade_product_spread;
3962: x_return_status := FND_API.g_ret_sts_error ;
3976: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3977: END IF;
3978: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3979: p_data => x_error_message);
3980: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3981:
3982: END delete_cascade_product_spread;
3983:
3984:
4169:
4170:
4171: BEGIN
4172:
4173: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
4174:
4175: -- Standard Start of API savepoint
4176: SAVEPOINT add_cascade_product_spread;
4177:
4203:
4204: IF l_status_code IN ('DRAFT', 'PLANNING') THEN
4205: null;
4206: ELSE
4207: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Root Fund Number : ' ||l_fund_id|| ' is : '||
4208: l_status_code);
4209: RAISE OZF_TP_ADDITEM_NOT_ALLOWED;
4210: END IF;
4211:
4211:
4212:
4213: ------- Start Adding Product and Time Allocation Records for allocations of l_fund_id -----------------------
4214:
4215: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Adding Product and Time Allocations Records'
4216: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4217:
4218: FOR allocation_rec IN allocation_csr(l_fund_id)
4219: LOOP
4228: OPEN prod_alloc_csr(l_fact_id);
4229: FETCH prod_alloc_csr INTO l_prod_alloc_rec;
4230: IF prod_alloc_csr%NOTFOUND THEN
4231: CLOSE prod_alloc_csr;
4232: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': NO Product and Time Allocations Records exist'
4233: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
4234: GOTO next_fact_iteration;
4235: END IF;
4236: CLOSE prod_alloc_csr;
4234: GOTO next_fact_iteration;
4235: END IF;
4236: CLOSE prod_alloc_csr;
4237:
4238: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Adding Product and Time Allocations Records'
4239: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4240:
4241: p_prod_alloc_rec := NULL;
4242:
4369: p.last_updated_by = FND_GLOBAL.USER_ID,
4370: p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
4371: WHERE p.product_allocation_id = l_product_allocation_id;
4372:
4373: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Adding Product and Time Allocations Records'
4374: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4375:
4376: <
4377: NULL;
4377: NULL;
4378: END LOOP;
4379: END LOOP;
4380:
4381: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Adding Product and Time Allocations Records'
4382: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4383:
4384: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4385:
4380:
4381: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Adding Product and Time Allocations Records'
4382: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4383:
4384: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4385:
4386: EXCEPTION
4387: WHEN OZF_TP_ADDITEM_NOT_ALLOWED THEN
4388: ROLLBACK TO add_cascade_product_spread;
4390: FND_MSG_PUB.add;
4391: x_return_status := FND_API.g_ret_sts_error ;
4392: FND_MSG_PUB.count_and_get (p_count => x_error_number,
4393: p_data => x_error_message);
4394: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_ADDITEM_NOT_ALLOWED_TXT'));
4395:
4396: WHEN FND_API.G_EXC_ERROR THEN
4397: ROLLBACK TO add_cascade_product_spread;
4398: x_return_status := FND_API.g_ret_sts_error ;
4412: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
4413: END IF;
4414: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
4415: p_data => x_error_message);
4416: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
4417:
4418: END add_cascade_product_spread;
4419:
4420:
4611:
4612:
4613: BEGIN
4614:
4615: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
4616:
4617: -- Standard Start of API savepoint
4618: SAVEPOINT delete_single_product;
4619:
4645:
4646: IF l_status_code IN ('DRAFT', 'PLANNING') THEN
4647: null;
4648: ELSE
4649: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Root Fund Number : '
4650: ||l_fund_id|| ' is : '||l_status_code);
4651: RAISE OZF_TP_DELITEM_NOT_ALLOWED;
4652: END IF;
4653:
4654:
4655:
4656: ------- Start Deleting Product and Time Allocation Records for allocations of l_fact_id -----------------------
4657:
4658: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
4659: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4660:
4661: FOR allocation_rec IN allocation_csr(l_fund_id)
4662: LOOP
4669: OPEN prod_alloc_csr(l_fact_id, l_item_id, l_item_type);
4670: FETCH prod_alloc_csr INTO l_prod_alloc_rec;
4671: IF prod_alloc_csr%NOTFOUND THEN
4672: CLOSE prod_alloc_csr;
4673: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': NO Product and Time Allocations Records exist'
4674: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
4675: GOTO next_fact_iteration;
4676: END IF;
4677: CLOSE prod_alloc_csr;
4679: OPEN prod_alloc_csr(l_fact_id, -9999, 'OTHERS');
4680: FETCH prod_alloc_csr INTO l_others_prod_alloc_rec;
4681: IF prod_alloc_csr%NOTFOUND THEN
4682: CLOSE prod_alloc_csr;
4683: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': NO OTHERS Product and Time Allocations Records exist'
4684: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
4685: GOTO next_fact_iteration;
4686: END IF;
4687: CLOSE prod_alloc_csr;
4686: END IF;
4687: CLOSE prod_alloc_csr;
4688:
4689:
4690: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4691: ': Begin - Updating OTHERS Product and Time Allocations Records'
4692: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4693:
4694: FOR time_alloc_rec IN time_alloc_csr(l_prod_alloc_rec.product_allocation_id)
4715: p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
4716: WHERE p.product_allocation_id = l_others_prod_alloc_rec.product_allocation_id;
4717:
4718:
4719: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Updating OTHERS Product and Time Allocations Records'
4720: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4721:
4722:
4723: DELETE ozf_time_allocations t
4732: NULL;
4733: END LOOP;
4734: END LOOP;
4735:
4736: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
4737: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4738:
4739: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4740:
4735:
4736: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
4737: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4738:
4739: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4740:
4741: EXCEPTION
4742: WHEN OZF_TP_DELITEM_NOT_ALLOWED THEN
4743: ROLLBACK TO delete_single_product;
4745: FND_MSG_PUB.add;
4746: x_return_status := FND_API.g_ret_sts_error ;
4747: FND_MSG_PUB.count_and_get (p_count => x_error_number,
4748: p_data => x_error_message);
4749: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DELITEM_NOT_ALLOWED_TXT'));
4750:
4751: WHEN FND_API.G_EXC_ERROR THEN
4752: ROLLBACK TO delete_single_product;
4753: x_return_status := FND_API.g_ret_sts_error ;
4767: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
4768: END IF;
4769: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
4770: p_data => x_error_message);
4771: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
4772:
4773: END delete_single_product;
4774:
4775: -- ------------------------
4821:
4822:
4823: BEGIN
4824:
4825: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
4826:
4827: -- Standard Start of API savepoint
4828: SAVEPOINT delete_target_allocation;
4829:
4847: l_fund_id := p_fund_id;
4848:
4849: ------- Start Deleting Product and Time Allocation Records -----------------------
4850:
4851: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4852: ': Begin - Deleting Account, Product and Time Allocations Records'
4853: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4854:
4855: FOR account_rec IN fund_account_spread_csr(l_fund_id)
4875: WHERE p.allocation_for = 'FUND'
4876: AND p.allocation_for_id = l_fund_id;
4877:
4878:
4879: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4880: ': End - Deleting Account, Product and Time Allocations Records'
4881: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4882:
4883: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4879: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4880: ': End - Deleting Account, Product and Time Allocations Records'
4881: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4882:
4883: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4884:
4885: EXCEPTION
4886: WHEN FND_API.G_EXC_ERROR THEN
4887: ROLLBACK TO delete_target_allocation;
4902: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
4903: END IF;
4904: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
4905: p_data => x_error_message);
4906: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
4907:
4908: END delete_target_allocation;
4909:
4910:
5306:
5307: -- Initialize API return status to SUCCESS
5308: x_return_status := FND_API.g_ret_sts_success;
5309:
5310: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
5311:
5312: l_fund_id := p_fund_id;
5313:
5314: OPEN fund_csr(l_fund_id);
5329: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
5330: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
5331: l_period_type_id := l_fund_rec.period_type_id;
5332:
5333: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
5334: ||l_start_date||' AND '||l_end_date||' ; '
5335: ||' Period_Type_id = '||l_period_type_id||' ; ');
5336:
5337: IF l_start_date IS NULL OR
5349: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
5350: RAISE OZF_TP_BLANK_PERIOD_TBL;
5351: END IF;
5352:
5353: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
5354: ||' AND '||l_end_date||' ; ');
5355: IF l_period_tbl IS NOT NULL THEN
5356: IF l_period_tbl.COUNT > 0 THEN
5357: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
5359: IF l_period_tbl.exists(l_idx) THEN
5360:
5361: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
5362:
5363: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
5364: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
5365: END IF;
5366: END LOOP;
5367: END IF;
5394: END IF;
5395: --l_lysp_in_clause := l_lysp_in_clause||')';
5396:
5397:
5398: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
5399: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
5400: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
5401: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
5402:
5396:
5397:
5398: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
5399: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
5400: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
5401: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
5402:
5403:
5404: l_total_lysp_sales_sql := l_total_lysp_sales_sql||l_lysp_in_clause||')';
5424: l_total_target_unalloc := l_total_root_quota;
5425: l_time_target_unalloc := l_total_root_quota / l_denominator;
5426: END IF;
5427:
5428: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor = '
5429: || l_multiplying_factor || ' ; ');
5430:
5431: ------ for product allocations: based upon Funds product spread ------------------
5432:
5439: l_total_fund_quota := NVL(l_total_fund_quota, 0);
5440:
5441: ------- Insert rows for ACCOUNTS ------------------------------------------------
5442:
5443: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name
5444: || ': Begin - Populating Account and Time Allocations Records'
5445: || ' FOR Fund_id = '|| l_fund_id
5446: || ' AND Territory_id = '|| l_territory_id
5447: || ' ; ');
5583:
5584: ------- Insert rows for PRODUCT and TIME Allocation Records for given ACCOUNT ---------------------------
5585:
5586:
5587: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
5588: ': Begin - Populating Product and Time Allocations Records'
5589: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
5590:
5591:
5778:
5779:
5780:
5781:
5782: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
5783: ': End - Populating Product and Time Allocations Records'
5784: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
5785:
5786:
5786:
5787: END LOOP account_loop;
5788:
5789:
5790: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating UNALLOCATED Allocations Records'
5791: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
5792:
5793: p_acct_alloc_rec := NULL;
5794:
5961: END IF;
5962:
5963: -------END: FIX for difference due to ROUNDING --------------------------------------------------|
5964:
5965: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating UNALLOCATED Allocations Records'
5966: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
5967:
5968:
5969:
5968:
5969:
5970:
5971:
5972: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
5973:
5974: EXCEPTION
5975:
5976: WHEN OZF_TP_INVALID_PARAM THEN
5979: FND_MSG_PUB.add;
5980: x_return_status := FND_API.g_ret_sts_error ;
5981: FND_MSG_PUB.count_and_get (p_count => x_error_number,
5982: p_data => x_error_message);
5983: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
5984: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
5985:
5986: WHEN FND_API.G_EXC_ERROR THEN
5987: ROLLBACK TO old_allocate_target_first_time;
5980: x_return_status := FND_API.g_ret_sts_error ;
5981: FND_MSG_PUB.count_and_get (p_count => x_error_number,
5982: p_data => x_error_message);
5983: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
5984: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
5985:
5986: WHEN FND_API.G_EXC_ERROR THEN
5987: ROLLBACK TO old_allocate_target_first_time;
5988: x_return_status := FND_API.g_ret_sts_error ;
6008: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
6009: END IF;
6010: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
6011: p_data => x_error_message);
6012: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
6013:
6014: END old_allocate_target_first_time;
6015:
6016:
6418:
6419: -- Initialize API return status to SUCCESS
6420: x_return_status := FND_API.g_ret_sts_success;
6421:
6422: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
6423:
6424: l_fund_id := p_fund_id;
6425:
6426: OPEN fund_csr(l_fund_id);
6441: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
6442: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
6443: l_period_type_id := l_fund_rec.period_type_id;
6444:
6445: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
6446: ||l_start_date||' AND '||l_end_date||' ; '
6447: ||' Period_Type_id = '||l_period_type_id||' ; ');
6448:
6449: IF l_start_date IS NULL OR
6461: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
6462: RAISE OZF_TP_BLANK_PERIOD_TBL;
6463: END IF;
6464:
6465: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
6466: ||' AND '||l_end_date||' ; ');
6467: IF l_period_tbl IS NOT NULL THEN
6468: IF l_period_tbl.COUNT > 0 THEN
6469: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
6471: IF l_period_tbl.exists(l_idx) THEN
6472:
6473: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
6474:
6475: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
6476: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
6477: END IF;
6478: END LOOP;
6479: END IF;
6506: END IF;
6507: --l_lysp_in_clause := l_lysp_in_clause||')';
6508:
6509:
6510: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
6511: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
6512: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
6513: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
6514:
6508:
6509:
6510: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
6511: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
6512: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
6513: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
6514:
6515:
6516: l_total_lysp_sales_sql := l_total_lysp_sales_sql||l_lysp_in_clause||')';
6536: l_total_target_unalloc := l_total_root_quota;
6537: l_time_target_unalloc := l_total_root_quota / l_denominator;
6538: END IF;
6539:
6540: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor = '
6541: || l_multiplying_factor || ' ; ');
6542:
6543: ------ for product allocations: based upon Funds product spread ------------------
6544:
6551: l_total_fund_quota := NVL(l_total_fund_quota, 0);
6552:
6553: ------- Insert rows for ACCOUNTS ------------------------------------------------
6554:
6555: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name
6556: || ': Begin - Populating Account and Time Allocations Records'
6557: || ' FOR Fund_id = '|| l_fund_id
6558: || ' AND Territory_id = '|| l_territory_id
6559: || ' ; ');
6730:
6731: ------- Insert rows for PRODUCT and TIME Allocation Records for given ACCOUNT ---------------------------
6732:
6733:
6734: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
6735: ': Begin - Populating Product and Time Allocations Records'
6736: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
6737:
6738:
6926: ELSE
6927: l_prod_mltply_factor := 0;
6928: END IF;
6929:
6930: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6931: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6932: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6933:
6934: UPDATE OZF_TIME_ALLOCATIONS t
6927: l_prod_mltply_factor := 0;
6928: END IF;
6929:
6930: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6931: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6932: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6933:
6934: UPDATE OZF_TIME_ALLOCATIONS t
6935: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
6928: END IF;
6929:
6930: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6931: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6932: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6933:
6934: UPDATE OZF_TIME_ALLOCATIONS t
6935: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
6936: t.object_version_number = t.object_version_number + 1,
7066: AND p.allocation_for_id = l_account_allocation_id;
7067:
7068: l_diff_target := (NVL(l_total_account_target, 0) - NVL(l_diff_target_1, 0));
7069:
7070: -- OZF_UTILITY_PVT.debug_message('mkothari -- l_diff_target => '||l_diff_target || ' ; ');
7071:
7072: EXCEPTION
7073: WHEN OTHERS THEN
7074: l_diff_target := 0;
7205:
7206: --------END: FIX for difference due to ROUNDING FOR each ShipTos Product Spread ------------------|
7207:
7208:
7209: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
7210: ': End - Populating Product and Time Allocations Records'
7211: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
7212:
7213:
7213:
7214: END LOOP account_loop;
7215:
7216:
7217: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating UNALLOCATED Allocations Records'
7218: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
7219:
7220: p_acct_alloc_rec := NULL;
7221:
7350: AND a.parent_party_id = -9999 );
7351:
7352: l_diff_target := NVL(l_diff_target_1, 0) - NVL(l_diff_target_2, 0);
7353:
7354: -- OZF_UTILITY_PVT.debug_message('mkothari --- UNALLOCATED l_diff_target => '||l_diff_target || ' ; ');
7355:
7356: EXCEPTION
7357: WHEN OTHERS THEN
7358: l_diff_target := 0;
7396: END IF;
7397:
7398: -------END: FIX for difference due to ROUNDING --------------------------------------------------|
7399:
7400: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating UNALLOCATED Allocations Records'
7401: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
7402:
7403:
7404:
7418: AND a.allocation_for_id = l_fund_id;
7419:
7420: l_diff_target := (NVL(l_total_root_quota, 0) - NVL(l_diff_target_1, 0));
7421:
7422: -- OZF_UTILITY_PVT.debug_message('mkothari-- ALL TARGETS l_diff_target => '||l_diff_target || ' ; ');
7423:
7424: EXCEPTION
7425: WHEN OTHERS THEN
7426: l_diff_target := 0;
7482: ELSE
7483: l_diff_target := FLOOR(l_diff_target); -- (So, +1.5 will become +1)
7484: END IF;
7485:
7486: -- OZF_UTILITY_PVT.debug_message('222. mkothari-- ALL TARGETS l_diff_target => '||l_diff_target || ' ; ');
7487:
7488: l_temp_account_allocation_id := 0;
7489:
7490: UPDATE OZF_TIME_ALLOCATIONS t
7533: RAISE fnd_api.g_exc_unexpected_error;
7534: END IF;
7535:
7536:
7537: -- OZF_UTILITY_PVT.debug_message('4.mkothari--'||'ALL TARGETS AccAllocID=> '||l_temp_account_allocation_id|| ';');
7538:
7539: UPDATE OZF_ACCOUNT_ALLOCATIONS a
7540: SET a.TARGET = a.TARGET + l_diff_target,
7541: a.object_version_number = a.object_version_number + 1,
7637:
7638:
7639: -------END: FIX for difference due to ROUNDING for ALL Targets taken together--------------------|
7640:
7641: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
7642:
7643: EXCEPTION
7644:
7645: WHEN OZF_TP_INVALID_PARAM THEN
7648: FND_MSG_PUB.add;
7649: x_return_status := FND_API.g_ret_sts_error ;
7650: FND_MSG_PUB.count_and_get (p_count => x_error_number,
7651: p_data => x_error_message);
7652: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
7653: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
7654:
7655: WHEN FND_API.G_EXC_ERROR THEN
7656: ROLLBACK TO allocate_target_first_time;
7649: x_return_status := FND_API.g_ret_sts_error ;
7650: FND_MSG_PUB.count_and_get (p_count => x_error_number,
7651: p_data => x_error_message);
7652: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
7653: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
7654:
7655: WHEN FND_API.G_EXC_ERROR THEN
7656: ROLLBACK TO allocate_target_first_time;
7657: x_return_status := FND_API.g_ret_sts_error ;
7677: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
7678: END IF;
7679: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
7680: p_data => x_error_message);
7681: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
7682:
7683: END allocate_target_first_time;
7684:
7685:
8085:
8086: -- Initialize API return status to SUCCESS
8087: x_return_status := FND_API.g_ret_sts_success;
8088:
8089: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
8090:
8091: l_fund_id := p_fund_id;
8092: l_old_start_date := p_old_start_date;
8093: l_new_end_date := p_new_end_date;
8112: l_start_date := to_char(l_old_start_date, 'YYYY/MM/DD');
8113: l_end_date := to_char(l_new_end_date, 'YYYY/MM/DD');
8114: l_period_type_id := 32; -- l_fund_rec.period_type_id;
8115:
8116: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8117: ': Begin - Getting ADDON Time_ids Between '
8118: ||l_start_date||' AND '||l_end_date||' ; ');
8119:
8120: l_new_period_tbl := OZF_TIME_API_PVT.get_period_tbl
8150: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8151: END IF;
8152: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8153: p_data => x_error_message);
8154: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : COLLECTION UNNESTING: OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8155:
8156: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
8157: RAISE OZF_TP_BLANK_PERIOD_TBL;
8158: END IF;
8162: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
8163: RAISE OZF_TP_BLANK_PERIOD_TBL;
8164: END IF;
8165:
8166: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8167: ': End - Getting ADDON Time_ids Between '
8168: ||l_start_date||' AND '||l_end_date||' ; ');
8169:
8170:
8175: IF l_period_tbl.exists(l_idx) THEN
8176:
8177: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
8178:
8179: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
8180: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
8181: END IF;
8182: END LOOP;
8183: END IF;
8210: END IF;
8211: --l_lysp_in_clause := l_lysp_in_clause||')';
8212:
8213:
8214: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
8215: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
8216: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
8217: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
8218:
8212:
8213:
8214: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
8215: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
8216: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
8217: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
8218:
8219: OPEN total_lysp_sales_csr(l_fund_id,l_lysp_in_clause);
8220: FETCH total_lysp_sales_csr INTO l_total_lysp_sales;
8245:
8246:
8247: ------- Insert rows for ACCOUNT-TIME Allocations and Update rows for Account Allocations ---------
8248:
8249: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8250: ': Begin - Updating Account and Populating Time Allocations Records'
8251: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
8252: <
8253: FOR account_rec IN account_csr(l_fund_id)
8334:
8335: ------- Insert rows for Product-Time Allocations and Update rows for Account Allocations ---------
8336:
8337:
8338: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8339: ': Begin - Updating Product and Populating Time Allocations Records'
8340: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
8341:
8342:
8471: END LOOP account_product_loop;
8472:
8473:
8474:
8475: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8476: ': End - Updating Product and Populating Time Allocations Records'
8477: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
8478:
8479:
8482:
8483: ------- Insert rows for Unalloc-Time Allocations and Update rows for Unalloc Allocations ---------
8484:
8485:
8486: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8487: ': Begin - Updating UNALLOCATED Account and Populating Time Allocations Records'
8488: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
8489:
8490: UPDATE ozf_account_allocations a
8551: END IF;
8552: END LOOP unalloc_time_loop;
8553:
8554:
8555: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8556: ': End - Updating UNALLOCATED Account and Populating Time Allocations Records'
8557: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
8558:
8559:
8557: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
8558:
8559:
8560:
8561: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
8562:
8563: EXCEPTION
8564:
8565: WHEN OZF_TP_INVALID_PARAM THEN
8568: FND_MSG_PUB.add;
8569: x_return_status := FND_API.g_ret_sts_error ;
8570: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8571: p_data => x_error_message);
8572: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8573: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8574:
8575: WHEN FND_API.G_EXC_ERROR THEN
8576: ROLLBACK TO allocate_target_addon;
8569: x_return_status := FND_API.g_ret_sts_error ;
8570: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8571: p_data => x_error_message);
8572: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8573: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8574:
8575: WHEN FND_API.G_EXC_ERROR THEN
8576: ROLLBACK TO allocate_target_addon;
8577: x_return_status := FND_API.g_ret_sts_error ;
8597: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8598: END IF;
8599: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8600: p_data => x_error_message);
8601: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8602:
8603: END allocate_target_addon;
8604:
8605:
8703:
8704: -- Initialize API return status to SUCCESS
8705: x_return_status := FND_API.g_ret_sts_success;
8706:
8707: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- start');
8708:
8709: l_mode := p_mode;
8710: l_obj_id := p_obj_id;
8711: l_context:= p_context;
8709: l_mode := p_mode;
8710: l_obj_id := p_obj_id;
8711: l_context:= p_context;
8712:
8713: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8714: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8715: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8716: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8717:
8710: l_obj_id := p_obj_id;
8711: l_context:= p_context;
8712:
8713: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8714: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8715: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8716: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8717:
8718:
8711: l_context:= p_context;
8712:
8713: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8714: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8715: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8716: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8717:
8718:
8719: IF l_mode IN ('CREATE', 'DELETE', 'PUBLISH', 'ADD')
8712:
8713: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8714: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8715: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8716: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8717:
8718:
8719: IF l_mode IN ('CREATE', 'DELETE', 'PUBLISH', 'ADD')
8720: AND l_obj_id > 0
8786: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
8787: RAISE FND_API.g_exc_unexpected_error;
8788: END IF;
8789:
8790: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- end');
8791:
8792: EXCEPTION
8793:
8794: WHEN OZF_TP_INVALID_PARAM THEN
8797: FND_MSG_PUB.add;
8798: x_return_status := FND_API.g_ret_sts_error ;
8799: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8800: p_data => x_error_message);
8801: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8802: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8803:
8804: WHEN FND_API.G_EXC_ERROR THEN
8805: ROLLBACK TO setup_product_spread;
8798: x_return_status := FND_API.g_ret_sts_error ;
8799: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8800: p_data => x_error_message);
8801: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8802: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8803:
8804: WHEN FND_API.G_EXC_ERROR THEN
8805: ROLLBACK TO setup_product_spread;
8806: x_return_status := FND_API.g_ret_sts_error ;
8826: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8827: END IF;
8828: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8829: p_data => x_error_message);
8830: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8831:
8832: END setup_product_spread;
8833:
8834:
8893:
8894: -- Initialize API return status to SUCCESS
8895: x_return_status := FND_API.g_ret_sts_success;
8896:
8897: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- start');
8898:
8899: l_mode := p_mode;
8900: l_fund_id := p_fund_id;
8901: l_item_id := p_item_id;
8900: l_fund_id := p_fund_id;
8901: l_item_id := p_item_id;
8902: l_item_type := p_item_type;
8903:
8904: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8905: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8906: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8907: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8908: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8901: l_item_id := p_item_id;
8902: l_item_type := p_item_type;
8903:
8904: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8905: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8906: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8907: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8908: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8909:
8902: l_item_type := p_item_type;
8903:
8904: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8905: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8906: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8907: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8908: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8909:
8910: IF l_mode IN ('ADD', 'DELETE')
8903:
8904: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8905: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8906: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8907: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8908: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8909:
8910: IF l_mode IN ('ADD', 'DELETE')
8911: AND l_fund_id > 0
8904: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8905: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8906: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8907: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8908: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8909:
8910: IF l_mode IN ('ADD', 'DELETE')
8911: AND l_fund_id > 0
8912: AND l_item_id > 0
8951: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
8952: RAISE FND_API.g_exc_unexpected_error;
8953: END IF;
8954:
8955: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- end');
8956:
8957: EXCEPTION
8958:
8959: WHEN OZF_TP_INVALID_PARAM THEN
8962: FND_MSG_PUB.add;
8963: x_return_status := FND_API.g_ret_sts_error ;
8964: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8965: p_data => x_error_message);
8966: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8967: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8968:
8969: WHEN FND_API.G_EXC_ERROR THEN
8970: ROLLBACK TO cascade_product_spread;
8963: x_return_status := FND_API.g_ret_sts_error ;
8964: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8965: p_data => x_error_message);
8966: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8967: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8968:
8969: WHEN FND_API.G_EXC_ERROR THEN
8970: ROLLBACK TO cascade_product_spread;
8971: x_return_status := FND_API.g_ret_sts_error ;
8991: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8992: END IF;
8993: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8994: p_data => x_error_message);
8995: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8996:
8997: END cascade_product_spread;
8998:
8999:
9065:
9066: -- Initialize API return status to SUCCESS
9067: x_return_status := FND_API.g_ret_sts_success;
9068:
9069: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- start');
9070:
9071: l_mode := p_mode;
9072: l_fund_id := p_fund_id;
9073: l_old_start_date := p_old_start_date;
9074: l_new_end_date := p_new_end_date;
9075: l_addon_fact_id:= p_addon_fact_id;
9076: l_addon_amount:= p_addon_amount;
9077:
9078: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9079: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9075: l_addon_fact_id:= p_addon_fact_id;
9076: l_addon_amount:= p_addon_amount;
9077:
9078: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9079: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9083: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9076: l_addon_amount:= p_addon_amount;
9077:
9078: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9079: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9083: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9084: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9077:
9078: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9079: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9083: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9084: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9085:
9078: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9079: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9083: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9084: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9085:
9086: IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
9079: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9083: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9084: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9085:
9086: IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
9087: NULL;
9080: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9081: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9082: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9083: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9084: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9085:
9086: IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
9087: NULL;
9088: ELSE
9148: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9149: RAISE FND_API.g_exc_unexpected_error;
9150: END IF;
9151:
9152: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- end');
9153:
9154: EXCEPTION
9155:
9156: WHEN OZF_TP_INVALID_PARAM THEN
9159: FND_MSG_PUB.add;
9160: x_return_status := FND_API.g_ret_sts_error ;
9161: FND_MSG_PUB.count_and_get (p_count => x_error_number,
9162: p_data => x_error_message);
9163: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
9164: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
9165:
9166: WHEN FND_API.G_EXC_ERROR THEN
9167: ROLLBACK TO allocate_target;
9160: x_return_status := FND_API.g_ret_sts_error ;
9161: FND_MSG_PUB.count_and_get (p_count => x_error_number,
9162: p_data => x_error_message);
9163: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
9164: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
9165:
9166: WHEN FND_API.G_EXC_ERROR THEN
9167: ROLLBACK TO allocate_target;
9168: x_return_status := FND_API.g_ret_sts_error ;
9188: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
9189: END IF;
9190: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
9191: p_data => x_error_message);
9192: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
9193:
9194: END allocate_target;
9195:
9196:
9561:
9562: -- Initialize API return status to SUCCESS
9563: x_return_status := FND_API.g_ret_sts_success;
9564:
9565: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9566:
9567: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9568: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9569: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9563: x_return_status := FND_API.g_ret_sts_success;
9564:
9565: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9566:
9567: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9568: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9569: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9570: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9571:
9564:
9565: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9566:
9567: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9568: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9569: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9570: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9571:
9572:
9565: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9566:
9567: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9568: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9569: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9570: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9571:
9572:
9573: OPEN fund_csr;
9566:
9567: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9568: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9569: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9570: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9571:
9572:
9573: OPEN fund_csr;
9574: FETCH fund_csr INTO l_fund_rec;
9579: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
9580: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
9581: l_period_type_id := l_fund_rec.period_type_id;
9582:
9583: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
9584: ||l_start_date||' AND '||l_end_date||' ; '
9585: ||' Period_Type_id = '||l_period_type_id||' ; ');
9586:
9587: IF l_start_date IS NULL OR
9599: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
9600: RAISE OZF_TP_BLANK_PERIOD_TBL;
9601: END IF;
9602:
9603: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
9604: ||' AND '||l_end_date||' ; ');
9605: IF l_period_tbl IS NOT NULL THEN
9606: IF l_period_tbl.COUNT > 0 THEN
9607: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
9609: IF l_period_tbl.exists(l_idx) THEN
9610:
9611: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
9612:
9613: Ozf_Utility_pvt.write_conc_log(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
9614: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
9615: END IF;
9616: END LOOP;
9617: END IF;
9644: END IF;
9645: --l_lysp_in_clause := l_lysp_in_clause||')';
9646:
9647:
9648: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
9649: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
9650: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
9651: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
9652:
9646:
9647:
9648: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
9649: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
9650: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
9651: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
9652:
9653:
9654: -- 1. CREATING NEW SHIPTO ACCOUNT HEADER RECORD ....
10218: END LOOP account_product_loop;
10219:
10220: END IF;
10221:
10222: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- end');
10223:
10224: EXCEPTION
10225:
10226: WHEN OZF_TP_INVALID_PARAM THEN
10229: FND_MSG_PUB.add;
10230: x_return_status := FND_API.g_ret_sts_error ;
10231: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10232: p_data => x_error_message);
10233: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10234: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10235:
10236: WHEN FND_API.G_EXC_ERROR THEN
10237: ROLLBACK TO adjust_target_for_acct_added;
10230: x_return_status := FND_API.g_ret_sts_error ;
10231: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10232: p_data => x_error_message);
10233: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10234: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10235:
10236: WHEN FND_API.G_EXC_ERROR THEN
10237: ROLLBACK TO adjust_target_for_acct_added;
10238: x_return_status := FND_API.g_ret_sts_error ;
10258: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
10259: END IF;
10260: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
10261: p_data => x_error_message);
10262: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
10263:
10264: END adjust_target_for_acct_added;
10265:
10266: -- ------------------------
10316:
10317: -- Initialize API return status to SUCCESS
10318: x_return_status := FND_API.g_ret_sts_success;
10319:
10320: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
10321:
10322: IF p_fund_id IS NULL OR p_ship_to_site_use_id IS NULL
10323: THEN
10324: RAISE OZF_TP_INVALID_PARAM;
10354:
10355: -- 1. ADJUSTING UNALLOCATED Time records....
10356:
10357: BEGIN
10358: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 1. ADJUSTING UNALLOCATED Time records....');
10359:
10360: -- Increase the Unallocated for Current and Future Periods
10361: update ozf_time_allocations tta
10362: set tta.target = tta.target + (
10385: END;
10386:
10387: -- 1(b). ADJUSTING UNALLOCATED Account record ....
10388: BEGIN
10389: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 1(b). ADJUSTING UNALLOCATED Account record....');
10390:
10391: -- Rollup the total targets to the unallocated records
10392: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10393: SET (aa.TARGET, aa.LYSP_SALES) = (
10405:
10406: -- 2. ADJUSTING SHIP TO Time records. - Curr and Future Periods ...
10407:
10408: BEGIN
10409: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 2. ADJUSTING SHIP TO Time records....');
10410:
10411: -- Past Targets stay with the shipTo
10412: -- Make ShipTos Current and Future Periods Targets ZERO
10413: update ozf_time_allocations ttb
10431: END;
10432:
10433: -- 2(b). ADJUSTING SHIP TO Account record ....
10434: BEGIN
10435: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 2(b). ADJUSTING SHIP TO Account record....');
10436:
10437: -- Rollup the total targets to the shipTo record
10438: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10439: SET (aa.TARGET, aa.LYSP_SALES) = (
10453:
10454: -- 3. ADJUSTING ShipTos PRODUCT SPREAD Time records ....
10455:
10456: BEGIN
10457: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 3. ADJUSTING ShipTos Product Spread Time Records....');
10458:
10459: -- CURRENT AND FUTURE TARGETS of this Shiptos products are made ZERO
10460: update ozf_time_allocations tt
10461: set tt.target = 0,
10484: -- Unallocated is automatically shown in the header of the product spread
10485:
10486: -- 5. ADJUSTING ShipTos PRODUCT SPREAD Header records ....
10487: BEGIN
10488: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 5. ADJUSTING ShipTos Product Spread Header Records....');
10489:
10490: -- Rollup the target numbers to the product record
10491: UPDATE OZF_PRODUCT_ALLOCATIONS p
10492: SET p.TARGET = (SELECT SUM(ti.TARGET)
10509:
10510:
10511: <
10512:
10513: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- end');
10514:
10515: EXCEPTION
10516:
10517: WHEN OZF_TP_INVALID_PARAM THEN
10520: FND_MSG_PUB.add;
10521: x_return_status := FND_API.g_ret_sts_error ;
10522: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10523: p_data => x_error_message);
10524: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10525: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10526:
10527: WHEN FND_API.G_EXC_ERROR THEN
10528: ROLLBACK TO adjust_target_for_acct_deleted;
10521: x_return_status := FND_API.g_ret_sts_error ;
10522: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10523: p_data => x_error_message);
10524: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10525: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10526:
10527: WHEN FND_API.G_EXC_ERROR THEN
10528: ROLLBACK TO adjust_target_for_acct_deleted;
10529: x_return_status := FND_API.g_ret_sts_error ;
10549: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
10550: END IF;
10551: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
10552: p_data => x_error_message);
10553: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
10554:
10555: END adjust_target_for_acct_deleted;
10556:
10557:
10874:
10875: -- Initialize API return status to SUCCESS
10876: x_return_status := FND_API.g_ret_sts_success;
10877:
10878: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
10879:
10880: FOR terr IN terr_list_csr
10881: LOOP
10882: ---1. PROCESS ADDITION OF ACCOUNTS
10889: FOR quota_allocation in allocation_list_csr (quota.fund_id)
10890: LOOP
10891:
10892: IF G_DEBUG_LEVEL THEN
10893: Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- Process Added Accounts for Quota Id =>'||quota.fund_id);
10894: END IF;
10895: -- FIND the corr_quota, such that there exists a corr_terr which lost that ship_to
10896: OPEN corr_quota_csr (quota_allocation.activity_metric_id, new_accounts.site_use_id);
10897: FETCH corr_quota_csr INTO l_corr_quota_id, l_node_id, l_previous_fact_id, l_activity_metric_fact_id;
10930:
10931: FOR quota IN del_quota_list_csr(terr.node_id, del_accounts.site_use_id)
10932: LOOP
10933: IF G_DEBUG_LEVEL THEN
10934: Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- Process Deleted Accounts for Site Use Id =>'||del_accounts.site_use_id);
10935: END IF;
10936: adjust_target_for_acct_deleted (
10937: p_api_version => p_api_version,
10938: x_return_status => x_return_status,
10954: END LOOP; -- deleted_accounts_csr (terr.node_id)
10955:
10956: -- 3. UPDATE ACCOUNT ALLOCATION account details based upon latest information from Terr (i.e from TCA )
10957: IF G_DEBUG_LEVEL THEN
10958: Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- UPDATE ACCOUNT ALLOCATION Table''s account details based upon latest information from Terr (i.e from TCA).');
10959: END IF;
10960:
10961:
10962: --- Jan 5th, 2005
11017:
11018:
11019: END LOOP; -- terr_list_csr
11020:
11021: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- end');
11022:
11023: EXCEPTION
11024:
11025: WHEN FND_API.G_EXC_ERROR THEN
11047: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
11048: END IF;
11049: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
11050: p_data => x_error_message);
11051: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
11052:
11053: END adjust_account_targets;
11054:
11055: