[Home] [Help]
52: l_time_alloc_id number := -1;
53:
54: BEGIN
55:
56: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
57:
58: OPEN time_seq_csr;
59: FETCH time_seq_csr INTO l_time_alloc_id;
60: CLOSE time_seq_csr;
75: return l_time_alloc_id;
76:
77: EXCEPTION
78: WHEN OTHERS THEN
79: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
80: END get_time_allocation_id;
81:
82:
83:
110: l_product_alloc_id number := -1;
111:
112: BEGIN
113:
114: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
115:
116: OPEN product_seq_csr;
117: FETCH product_seq_csr INTO l_product_alloc_id;
118: CLOSE product_seq_csr;
133: return l_product_alloc_id;
134:
135: EXCEPTION
136: WHEN OTHERS THEN
137: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
138: END get_product_allocation_id;
139:
140:
141:
169: l_account_alloc_id number := -1;
170:
171: BEGIN
172:
173: --OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
174:
175: OPEN account_seq_csr;
176: FETCH account_seq_csr INTO l_account_alloc_id;
177: CLOSE account_seq_csr;
192: return l_account_alloc_id;
193:
194: EXCEPTION
195: WHEN OTHERS THEN
196: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
197: END get_account_allocation_id;
198:
199:
200: -- ------------------------
453: l_item_type := p_item_type;
454: l_time_id := p_time_id;
455:
456: /*
457: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
458: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
459: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
460: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
461: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
454: l_time_id := p_time_id;
455:
456: /*
457: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
458: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
459: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
460: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
461: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
462: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
455:
456: /*
457: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
458: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
459: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
460: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
461: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
462: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
463: */
456: /*
457: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
458: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
459: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
460: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
461: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
462: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
463: */
464:
457: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
458: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
459: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
460: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
461: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
462: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
463: */
464:
465:
458: OZF_UTILITY_PVT.debug_message('1. l_object_type------->: ' || l_object_type);
459: OZF_UTILITY_PVT.debug_message('2. l_object_id--------->: ' || l_object_id);
460: OZF_UTILITY_PVT.debug_message('3. l_item_id----------->: ' || l_item_id);
461: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
462: OZF_UTILITY_PVT.debug_message('5. l_time_id----------->: ' || l_time_id);
463: */
464:
465:
466: IF l_object_type IN ('ROOT', 'CUST')
598: l_item_key := p_object_id ||'_'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
599: l_parameter_list := WF_PARAMETER_LIST_T();
600: l_event_name := 'oracle.apps.ozf.quota.TargetApproval';
601:
602: ozf_utility_pvt.debug_message('-> Raising Bussiness EVENT for Account Allocation Id == '||p_object_id||' ; ' );
603: ozf_utility_pvt.debug_message('-> ITEM KEY == '||l_item_key||' ; ' );
604:
605: wf_event.AddParameterToList(p_name => 'P_ACCOUNT_ALLOCATION_ID',
606: p_value => p_object_id,
599: l_parameter_list := WF_PARAMETER_LIST_T();
600: l_event_name := 'oracle.apps.ozf.quota.TargetApproval';
601:
602: ozf_utility_pvt.debug_message('-> Raising Bussiness EVENT for Account Allocation Id == '||p_object_id||' ; ' );
603: ozf_utility_pvt.debug_message('-> ITEM KEY == '||l_item_key||' ; ' );
604:
605: wf_event.AddParameterToList(p_name => 'P_ACCOUNT_ALLOCATION_ID',
606: p_value => p_object_id,
607: p_parameterlist => l_parameter_list);
613:
614: EXCEPTION
615: WHEN OTHERS THEN
616: RAISE Fnd_Api.g_exc_error;
617: ozf_utility_pvt.debug_message('Exception in raising business event for Target Approval');
618: END;
619:
620:
621:
1060:
1061:
1062: BEGIN
1063:
1064: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1065:
1066: -- Standard Start of API savepoint
1067: SAVEPOINT create_old_fact_product_spread;
1068:
1108:
1109: l_start_date := to_char(l_allocation_rec.from_date, 'YYYY/MM/DD');
1110: l_end_date := to_char(l_allocation_rec.to_date, 'YYYY/MM/DD');
1111:
1112: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
1113: ||l_start_date||' AND '||l_end_date||' ; '
1114: ||' Period_Type_id = '||l_period_type_id||' ; ');
1115:
1116: IF l_start_date IS NULL OR
1129: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
1130: RAISE OZF_TP_BLANK_PERIOD_TBL;
1131: END IF;
1132:
1133: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
1134: ||' AND '||l_end_date||' ; ');
1135: IF l_period_tbl IS NOT NULL THEN
1136: IF l_period_tbl.COUNT > 0 THEN
1137: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
1139: IF l_period_tbl.exists(l_idx) THEN
1140:
1141: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1142:
1143: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1144: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1145: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1146: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1147: END IF;
1141: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1142:
1143: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1144: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1145: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1146: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1147: END IF;
1148: END LOOP;
1149: END IF;
1148: END LOOP;
1149: END IF;
1150: END IF;
1151:
1152: --OZF_UTILITY_PVT.debug_message(' out of lysp period table');
1153:
1154: --l_in_clause := '(';
1155: l_in_clause := NULL;
1156: IF l_period_tbl IS NOT NULL THEN
1164: END LOOP;
1165: END IF;
1166: --l_in_clause := l_in_clause||')';
1167:
1168: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_in_clause == '||l_in_clause);
1169:
1170: --l_lysp_in_clause := '(';
1171: l_lysp_in_clause := NULL;
1172: IF l_lysp_period_tbl IS NOT NULL THEN
1180: END LOOP;
1181: END IF;
1182: --l_lysp_in_clause := l_lysp_in_clause||')';
1183:
1184: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_lysp_in_clause == '||l_lysp_in_clause);
1185:
1186: l_get_total_target_sql := l_get_total_target_sql||l_in_clause ||')';
1187:
1188: OPEN get_total_target_csr FOR l_get_total_target_sql USING l_fund_id;
1198: l_multiplying_factor := 0;
1199: END IF;
1200:
1201:
1202: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
1203:
1204:
1205: ------- Insert rows for PRODUCT and TIME Allocation Records for given FACT ------------------------------
1206:
1204:
1205: ------- Insert rows for PRODUCT and TIME Allocation Records for given FACT ------------------------------
1206:
1207:
1208: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating Product and Time Allocations Records'
1209: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1210:
1211: FOR root_product_rec IN root_product_spread_csr(l_fund_id)
1212: LOOP
1442: --------END: ORIGINAL FIX for difference due to ROUNDING --------------------------------------------------|
1443:
1444:
1445:
1446: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
1447: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1448:
1449: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
1450:
1445:
1446: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
1447: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1448:
1449: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
1450:
1451: EXCEPTION
1452: WHEN OZF_TP_DIFF_TIME_SPREAD THEN
1453: ROLLBACK TO create_old_fact_product_spread;
1455: FND_MSG_PUB.add;
1456: x_return_status := FND_API.g_ret_sts_error ;
1457: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1458: p_data => x_error_message);
1459: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
1460: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
1461:
1462: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
1463: ROLLBACK TO create_old_fact_product_spread;
1456: x_return_status := FND_API.g_ret_sts_error ;
1457: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1458: p_data => x_error_message);
1459: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
1460: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
1461:
1462: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
1463: ROLLBACK TO create_old_fact_product_spread;
1464: FND_MESSAGE.set_name('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT');
1465: FND_MSG_PUB.add;
1466: x_return_status := FND_API.g_ret_sts_error ;
1467: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1468: p_data => x_error_message);
1469: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
1470: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
1471:
1472: WHEN FND_API.G_EXC_ERROR THEN
1473: ROLLBACK TO create_old_fact_product_spread;
1466: x_return_status := FND_API.g_ret_sts_error ;
1467: FND_MSG_PUB.count_and_get (p_count => x_error_number,
1468: p_data => x_error_message);
1469: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
1470: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
1471:
1472: WHEN FND_API.G_EXC_ERROR THEN
1473: ROLLBACK TO create_old_fact_product_spread;
1474: x_return_status := FND_API.g_ret_sts_error ;
1488: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
1489: END IF;
1490: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
1491: p_data => x_error_message);
1492: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
1493:
1494: END create_old_fact_product_spread;
1495:
1496:
1769:
1770:
1771: BEGIN
1772:
1773: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
1774:
1775: -- Standard Start of API savepoint
1776: SAVEPOINT create_fact_product_spread;
1777:
1799: CLOSE fact_csr ;
1800:
1801: l_territory_id := l_fact_rec.node_id;
1802:
1803: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Territory Id => '
1804: || l_territory_id ||' ; ');
1805:
1806: OPEN allocation_csr(l_fact_rec.activity_metric_id);
1807: FETCH allocation_csr INTO l_allocation_rec;
1820:
1821: l_start_date := to_char(l_allocation_rec.from_date, 'YYYY/MM/DD');
1822: l_end_date := to_char(l_allocation_rec.to_date, 'YYYY/MM/DD');
1823:
1824: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
1825: ||l_start_date||' AND '||l_end_date||' ; '
1826: ||' Period_Type_id = '||l_period_type_id||' ; ');
1827:
1828: IF l_start_date IS NULL OR
1841: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
1842: RAISE OZF_TP_BLANK_PERIOD_TBL;
1843: END IF;
1844:
1845: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
1846: ||' AND '||l_end_date||' ; ');
1847: IF l_period_tbl IS NOT NULL THEN
1848: IF l_period_tbl.COUNT > 0 THEN
1849: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
1851: IF l_period_tbl.exists(l_idx) THEN
1852:
1853: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1854:
1855: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1856: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1857: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1858: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1859: END IF;
1853: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
1854:
1855: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
1856: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
1857: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
1858: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
1859: END IF;
1860: END LOOP;
1861: END IF;
1860: END LOOP;
1861: END IF;
1862: END IF;
1863:
1864: --OZF_UTILITY_PVT.debug_message(' out of lysp period table');
1865:
1866: --l_in_clause := '(';
1867: l_in_clause := NULL;
1868: IF l_period_tbl IS NOT NULL THEN
1876: END LOOP;
1877: END IF;
1878: --l_in_clause := l_in_clause||')';
1879:
1880: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_in_clause == '||l_in_clause);
1881:
1882: --l_lysp_in_clause := '(';
1883: l_lysp_in_clause := NULL;
1884: IF l_lysp_period_tbl IS NOT NULL THEN
1892: END LOOP;
1893: END IF;
1894: --l_lysp_in_clause := l_lysp_in_clause||')';
1895:
1896: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_lysp_in_clause == '||l_lysp_in_clause);
1897:
1898: --//00000 May17th,2004 - as per new requirements in Bug 3594874 000000000000000000000000000000000000
1899: /*
1900: l_get_total_target_sql := l_get_total_target_sql||l_in_clause ||')';
1911: ELSE
1912: l_multiplying_factor := 0;
1913: END IF;
1914:
1915: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
1916: */
1917: --//0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
1918:
1919:
1921:
1922:
1923: l_grand_total_lysp_sales := 0;
1924:
1925: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating Product and Time Allocations Records'
1926: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
1927:
1928: FOR root_product_rec IN root_product_spread_csr(l_fund_id)
1929: LOOP
2099: ELSE
2100: l_multiplying_factor := 0;
2101: END IF;
2102:
2103: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor is '|| l_multiplying_factor);
2104:
2105: UPDATE OZF_TIME_ALLOCATIONS t
2106: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_multiplying_factor), 0),
2107: t.object_version_number = t.object_version_number + 1,
2301: ---END: ORIGINAL FIX for difference due to ROUNDING --------------------------------------------------|
2302:
2303:
2304:
2305: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
2306: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
2307:
2308: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
2309:
2304:
2305: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
2306: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
2307:
2308: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
2309:
2310: EXCEPTION
2311: WHEN OZF_TP_DIFF_TIME_SPREAD THEN
2312: ROLLBACK TO create_fact_product_spread;
2314: FND_MSG_PUB.add;
2315: x_return_status := FND_API.g_ret_sts_error ;
2316: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2317: p_data => x_error_message);
2318: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
2319: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
2320:
2321: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
2322: ROLLBACK TO create_fact_product_spread;
2315: x_return_status := FND_API.g_ret_sts_error ;
2316: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2317: p_data => x_error_message);
2318: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : TIME SPREAD MISMATCH EXCEPTION = '||sqlerrm(sqlcode));
2319: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DIFF_TIME_SPREAD_TXT'));
2320:
2321: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
2322: ROLLBACK TO create_fact_product_spread;
2323: FND_MESSAGE.set_name('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT');
2324: FND_MSG_PUB.add;
2325: x_return_status := FND_API.g_ret_sts_error ;
2326: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2327: p_data => x_error_message);
2328: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
2329: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
2330:
2331: WHEN FND_API.G_EXC_ERROR THEN
2332: ROLLBACK TO create_fact_product_spread;
2325: x_return_status := FND_API.g_ret_sts_error ;
2326: FND_MSG_PUB.count_and_get (p_count => x_error_number,
2327: p_data => x_error_message);
2328: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
2329: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
2330:
2331: WHEN FND_API.G_EXC_ERROR THEN
2332: ROLLBACK TO create_fact_product_spread;
2333: x_return_status := FND_API.g_ret_sts_error ;
2347: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
2348: END IF;
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||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
2352:
2353: END create_fact_product_spread;
2354:
2355:
2588:
2589:
2590: BEGIN
2591:
2592: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
2593:
2594: -- Standard Start of API savepoint
2595: SAVEPOINT create_root_product_spread;
2596:
2629: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
2630: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
2631: l_period_type_id := l_fund_rec.period_type_id;
2632:
2633: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
2634: ||l_start_date||' AND '||l_end_date||' ; '
2635: ||' Period_Type_id = '||l_period_type_id||' ; ');
2636:
2637: IF l_start_date IS NULL OR
2649: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
2650: RAISE OZF_TP_BLANK_PERIOD_TBL;
2651: END IF;
2652:
2653: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
2654: ||' AND '||l_end_date||' ; ');
2655: IF l_period_tbl IS NOT NULL THEN
2656: IF l_period_tbl.COUNT > 0 THEN
2657: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
2659: IF l_period_tbl.exists(l_idx) THEN
2660:
2661: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
2662:
2663: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
2664: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
2665: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
2666: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
2667: END IF;
2661: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
2662:
2663: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
2664: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
2665: OZF_UTILITY_PVT.debug_message(SubStr('l_lysp_period_tbl('||TO_CHAR(l_idx)||') = '
2666: ||TO_CHAR(l_lysp_period_tbl(l_idx)), 1,255));
2667: END IF;
2668: END LOOP;
2669: END IF;
2684: END LOOP;
2685: END IF;
2686: l_in_clause := l_in_clause||')';
2687:
2688: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': l_in_clause == '||l_in_clause);
2689:
2690: ------- Insert rows for PRODUCTS ------------------------------------------------
2691:
2692: l_grand_total_lysp_sales := 0;
2690: ------- Insert rows for PRODUCTS ------------------------------------------------
2691:
2692: l_grand_total_lysp_sales := 0;
2693:
2694: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating Product and Time Allocations Records'
2695: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
2696:
2697: FOR product_rec IN product_elig_csr(l_fund_id)
2698: LOOP
3093: l_grand_total_lysp_sales := l_grand_total_lysp_sales + l_total_lysp_sales;
3094:
3095:
3096:
3097: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating Product and Time Allocations Records'
3098: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3099:
3100: ------ Updating Target ----------------------------------------------------------------
3101:
3103: l_total_root_quota := NVL(l_fund_rec.original_budget, 0) + NVL(l_fund_rec.transfered_in_amt, 0);
3104:
3105: l_total_root_quota := NVL(l_total_root_quota, 0);
3106:
3107: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Total Root Quota'
3108: || 'FOR Fund_id = '|| l_fund_id || 'is == ' ||l_total_root_quota||' ;');
3109:
3110: l_multiplying_factor := 0;
3111: IF l_grand_total_lysp_sales > 0 THEN
3113: ELSE
3114: l_multiplying_factor := 0;
3115: END IF;
3116:
3117: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor == '
3118: ||l_multiplying_factor||' ;');
3119: /*
3120: UPDATE OZF_PRODUCT_ALLOCATIONS p
3121: SET p.TARGET = ROUND( (NVL(p.LYSP_SALES, 0) * l_multiplying_factor), 0),
3418: */
3419:
3420:
3421:
3422: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3423:
3424: EXCEPTION
3425: WHEN OZF_TP_BLANK_PERIOD_TBL THEN
3426: ROLLBACK TO create_root_product_spread;
3428: FND_MSG_PUB.add;
3429: x_return_status := FND_API.g_ret_sts_error ;
3430: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3431: p_data => x_error_message);
3432: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
3433: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
3434:
3435: WHEN FND_API.G_EXC_ERROR THEN
3436: ROLLBACK TO create_root_product_spread;
3429: x_return_status := FND_API.g_ret_sts_error ;
3430: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3431: p_data => x_error_message);
3432: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : BLANK PERIOD TABLE EXCEPTION = '||sqlerrm(sqlcode));
3433: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_BLANK_PERIOD_TBL_TXT'));
3434:
3435: WHEN FND_API.G_EXC_ERROR THEN
3436: ROLLBACK TO create_root_product_spread;
3437: x_return_status := FND_API.g_ret_sts_error ;
3451: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3452: END IF;
3453: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3454: p_data => x_error_message);
3455: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3456:
3457: END create_root_product_spread;
3458:
3459:
3515: l_fact_rec fact_csr%ROWTYPE;
3516:
3517: BEGIN
3518:
3519: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
3520:
3521: -- Standard Start of API savepoint
3522: SAVEPOINT publish_fact_product_spread;
3523:
3552: --=========>>> Can do some more validation here to check if the caller JTT Quota page is not calling this by mistake
3553: --=========>>> It should call this only after Updating the FACT record with the Newly created FUND id
3554:
3555: ELSE
3556: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Fact Number : ' ||l_fact_id|| ' is : '||
3557: l_status_code);
3558: RAISE OZF_TP_OPER_NOT_ALLOWED;
3559: END IF;
3560:
3562: ------- Start Publishing Product Allocation Records for allocations of l_fact_id -----------------------
3563:
3564: l_fund_id := l_fact_rec.act_metric_used_by_id;
3565:
3566: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Publishing Product Allocations Records'
3567: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3568:
3569: UPDATE ozf_product_allocations p
3570: SET p.fund_id = l_fund_id,
3582:
3583: */
3584:
3585:
3586: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Publishing Product Allocations Records'
3587: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3588:
3589: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3590:
3585:
3586: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Publishing Product Allocations Records'
3587: || 'FOR Fact_id = '|| l_fact_id || ' with NEW Fund_id = '||l_fund_id||' ; ');
3588:
3589: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3590:
3591: EXCEPTION
3592: WHEN OZF_TP_OPER_NOT_ALLOWED THEN
3593: ROLLBACK TO publish_fact_product_spread;
3595: FND_MSG_PUB.add;
3596: x_return_status := FND_API.g_ret_sts_error ;
3597: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3598: p_data => x_error_message);
3599: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_OPER_NOT_ALLOWED_TXT'));
3600:
3601: WHEN FND_API.G_EXC_ERROR THEN
3602: ROLLBACK TO publish_fact_product_spread;
3603: x_return_status := FND_API.g_ret_sts_error ;
3617: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3618: END IF;
3619: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3620: p_data => x_error_message);
3621: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3622:
3623: END publish_fact_product_spread;
3624:
3625:
3660:
3661:
3662: BEGIN
3663:
3664: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
3665:
3666: -- Standard Start of API savepoint
3667: SAVEPOINT delete_fact_product_spread;
3668:
3686: l_fact_id := p_fact_id;
3687:
3688: ------- Start Deleting Product and Time Allocation Records -----------------------
3689:
3690: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
3691: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3692:
3693: FOR fact_product_spread_rec IN fact_product_spread_csr(l_fact_id)
3694: LOOP
3701: WHERE p.allocation_for = 'FACT'
3702: AND p.allocation_for_id = l_fact_id;
3703:
3704:
3705: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3706: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3707: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3708:
3709: EXCEPTION
3703:
3704:
3705: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3706: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
3707: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3708:
3709: EXCEPTION
3710: WHEN FND_API.G_EXC_ERROR THEN
3711: ROLLBACK TO delete_fact_product_spread;
3726: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3727: END IF;
3728: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3729: p_data => x_error_message);
3730: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3731:
3732: END delete_fact_product_spread;
3733:
3734:
3834:
3835:
3836: BEGIN
3837:
3838: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
3839:
3840: -- Standard Start of API savepoint
3841: SAVEPOINT delete_cascade_product_spread;
3842:
3868:
3869: IF l_status_code IN ('DRAFT', 'PLANNING') THEN
3870: null;
3871: ELSE
3872: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Root Fund Number : ' ||l_fund_id|| ' is : '||
3873: l_status_code);
3874: RAISE OZF_TP_CHG_PS_NOT_ALLOWED;
3875: END IF;
3876:
3876:
3877:
3878: ------- Start Deleting Product and Time Allocation Records for allocations of l_fund_id -----------------------
3879:
3880: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
3881: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
3882:
3883: FOR allocation_rec IN allocation_csr(l_fund_id)
3884: LOOP
3893: );
3894: END LOOP;
3895: END LOOP;
3896:
3897: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3898: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
3899:
3900: ------- Start Deleting Product and Time Allocation Records for ROOT i.e. l_fund_id ----------------------
3901:
3899:
3900: ------- Start Deleting Product and Time Allocation Records for ROOT i.e. l_fund_id ----------------------
3901:
3902:
3903: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
3904: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3905:
3906: FOR fund_product_spread_rec IN fund_product_spread_csr(l_fund_id)
3907: LOOP
3915: AND p.allocation_for_id = l_fund_id
3916: AND p.fund_id = l_fund_id;
3917:
3918:
3919: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3920: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3921:
3922:
3923: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3919: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
3920: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
3921:
3922:
3923: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
3924:
3925: EXCEPTION
3926: WHEN OZF_TP_CHG_PS_NOT_ALLOWED THEN
3927: ROLLBACK TO delete_cascade_product_spread;
3929: FND_MSG_PUB.add;
3930: x_return_status := FND_API.g_ret_sts_error ;
3931: FND_MSG_PUB.count_and_get (p_count => x_error_number,
3932: p_data => x_error_message);
3933: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_CHG_PS_NOT_ALLOWED_TXT'));
3934:
3935: WHEN FND_API.G_EXC_ERROR THEN
3936: ROLLBACK TO delete_cascade_product_spread;
3937: x_return_status := FND_API.g_ret_sts_error ;
3951: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
3952: END IF;
3953: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
3954: p_data => x_error_message);
3955: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
3956:
3957: END delete_cascade_product_spread;
3958:
3959:
4144:
4145:
4146: BEGIN
4147:
4148: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
4149:
4150: -- Standard Start of API savepoint
4151: SAVEPOINT add_cascade_product_spread;
4152:
4178:
4179: IF l_status_code IN ('DRAFT', 'PLANNING') THEN
4180: null;
4181: ELSE
4182: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Root Fund Number : ' ||l_fund_id|| ' is : '||
4183: l_status_code);
4184: RAISE OZF_TP_ADDITEM_NOT_ALLOWED;
4185: END IF;
4186:
4186:
4187:
4188: ------- Start Adding Product and Time Allocation Records for allocations of l_fund_id -----------------------
4189:
4190: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Adding Product and Time Allocations Records'
4191: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4192:
4193: FOR allocation_rec IN allocation_csr(l_fund_id)
4194: LOOP
4201: OPEN prod_alloc_csr(l_fact_id);
4202: FETCH prod_alloc_csr INTO l_prod_alloc_rec;
4203: IF prod_alloc_csr%NOTFOUND THEN
4204: CLOSE prod_alloc_csr;
4205: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': NO Product and Time Allocations Records exist'
4206: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
4207: GOTO next_fact_iteration;
4208: END IF;
4209: CLOSE prod_alloc_csr;
4207: GOTO next_fact_iteration;
4208: END IF;
4209: CLOSE prod_alloc_csr;
4210:
4211: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Adding Product and Time Allocations Records'
4212: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4213:
4214: p_prod_alloc_rec := NULL;
4215:
4342: p.last_updated_by = FND_GLOBAL.USER_ID,
4343: p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
4344: WHERE p.product_allocation_id = l_product_allocation_id;
4345:
4346: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Adding Product and Time Allocations Records'
4347: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4348:
4349: <
4350: NULL;
4350: NULL;
4351: END LOOP;
4352: END LOOP;
4353:
4354: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Adding Product and Time Allocations Records'
4355: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4356:
4357: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4358:
4353:
4354: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Adding Product and Time Allocations Records'
4355: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4356:
4357: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4358:
4359: EXCEPTION
4360: WHEN OZF_TP_ADDITEM_NOT_ALLOWED THEN
4361: ROLLBACK TO add_cascade_product_spread;
4363: FND_MSG_PUB.add;
4364: x_return_status := FND_API.g_ret_sts_error ;
4365: FND_MSG_PUB.count_and_get (p_count => x_error_number,
4366: p_data => x_error_message);
4367: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_ADDITEM_NOT_ALLOWED_TXT'));
4368:
4369: WHEN FND_API.G_EXC_ERROR THEN
4370: ROLLBACK TO add_cascade_product_spread;
4371: x_return_status := FND_API.g_ret_sts_error ;
4385: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
4386: END IF;
4387: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
4388: p_data => x_error_message);
4389: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
4390:
4391: END add_cascade_product_spread;
4392:
4393:
4584:
4585:
4586: BEGIN
4587:
4588: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
4589:
4590: -- Standard Start of API savepoint
4591: SAVEPOINT delete_single_product;
4592:
4618:
4619: IF l_status_code IN ('DRAFT', 'PLANNING') THEN
4620: null;
4621: ELSE
4622: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': STATUS of Root Fund Number : '
4623: ||l_fund_id|| ' is : '||l_status_code);
4624: RAISE OZF_TP_DELITEM_NOT_ALLOWED;
4625: END IF;
4626:
4627:
4628:
4629: ------- Start Deleting Product and Time Allocation Records for allocations of l_fact_id -----------------------
4630:
4631: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Deleting Product and Time Allocations Records'
4632: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4633:
4634: FOR allocation_rec IN allocation_csr(l_fund_id)
4635: LOOP
4642: OPEN prod_alloc_csr(l_fact_id, l_item_id, l_item_type);
4643: FETCH prod_alloc_csr INTO l_prod_alloc_rec;
4644: IF prod_alloc_csr%NOTFOUND THEN
4645: CLOSE prod_alloc_csr;
4646: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': NO Product and Time Allocations Records exist'
4647: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
4648: GOTO next_fact_iteration;
4649: END IF;
4650: CLOSE prod_alloc_csr;
4652: OPEN prod_alloc_csr(l_fact_id, -9999, 'OTHERS');
4653: FETCH prod_alloc_csr INTO l_others_prod_alloc_rec;
4654: IF prod_alloc_csr%NOTFOUND THEN
4655: CLOSE prod_alloc_csr;
4656: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': NO OTHERS Product and Time Allocations Records exist'
4657: || 'FOR Fact_id = '|| l_fact_id || ' ; ');
4658: GOTO next_fact_iteration;
4659: END IF;
4660: CLOSE prod_alloc_csr;
4659: END IF;
4660: CLOSE prod_alloc_csr;
4661:
4662:
4663: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4664: ': Begin - Updating OTHERS Product and Time Allocations Records'
4665: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4666:
4667: FOR time_alloc_rec IN time_alloc_csr(l_prod_alloc_rec.product_allocation_id)
4688: p.last_update_login = FND_GLOBAL.CONC_LOGIN_ID
4689: WHERE p.product_allocation_id = l_others_prod_alloc_rec.product_allocation_id;
4690:
4691:
4692: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Updating OTHERS Product and Time Allocations Records'
4693: || 'FOR Fact_ID = '|| l_fact_id || ' ; ');
4694:
4695:
4696: DELETE ozf_time_allocations t
4705: NULL;
4706: END LOOP;
4707: END LOOP;
4708:
4709: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
4710: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4711:
4712: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4713:
4708:
4709: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Deleting Product and Time Allocations Records'
4710: || 'FOR Allocations of Fund_id = '|| l_fund_id || ' ; ');
4711:
4712: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4713:
4714: EXCEPTION
4715: WHEN OZF_TP_DELITEM_NOT_ALLOWED THEN
4716: ROLLBACK TO delete_single_product;
4718: FND_MSG_PUB.add;
4719: x_return_status := FND_API.g_ret_sts_error ;
4720: FND_MSG_PUB.count_and_get (p_count => x_error_number,
4721: p_data => x_error_message);
4722: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_DELITEM_NOT_ALLOWED_TXT'));
4723:
4724: WHEN FND_API.G_EXC_ERROR THEN
4725: ROLLBACK TO delete_single_product;
4726: x_return_status := FND_API.g_ret_sts_error ;
4740: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
4741: END IF;
4742: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
4743: p_data => x_error_message);
4744: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
4745:
4746: END delete_single_product;
4747:
4748: -- ------------------------
4794:
4795:
4796: BEGIN
4797:
4798: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
4799:
4800: -- Standard Start of API savepoint
4801: SAVEPOINT delete_target_allocation;
4802:
4820: l_fund_id := p_fund_id;
4821:
4822: ------- Start Deleting Product and Time Allocation Records -----------------------
4823:
4824: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4825: ': Begin - Deleting Account, Product and Time Allocations Records'
4826: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4827:
4828: FOR account_rec IN fund_account_spread_csr(l_fund_id)
4848: WHERE p.allocation_for = 'FUND'
4849: AND p.allocation_for_id = l_fund_id;
4850:
4851:
4852: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4853: ': End - Deleting Account, Product and Time Allocations Records'
4854: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4855:
4856: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4852: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
4853: ': End - Deleting Account, Product and Time Allocations Records'
4854: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
4855:
4856: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
4857:
4858: EXCEPTION
4859: WHEN FND_API.G_EXC_ERROR THEN
4860: ROLLBACK TO delete_target_allocation;
4875: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
4876: END IF;
4877: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
4878: p_data => x_error_message);
4879: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
4880:
4881: END delete_target_allocation;
4882:
4883:
5279:
5280: -- Initialize API return status to SUCCESS
5281: x_return_status := FND_API.g_ret_sts_success;
5282:
5283: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
5284:
5285: l_fund_id := p_fund_id;
5286:
5287: OPEN fund_csr(l_fund_id);
5300: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
5301: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
5302: l_period_type_id := l_fund_rec.period_type_id;
5303:
5304: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
5305: ||l_start_date||' AND '||l_end_date||' ; '
5306: ||' Period_Type_id = '||l_period_type_id||' ; ');
5307:
5308: IF l_start_date IS NULL OR
5320: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
5321: RAISE OZF_TP_BLANK_PERIOD_TBL;
5322: END IF;
5323:
5324: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
5325: ||' AND '||l_end_date||' ; ');
5326: IF l_period_tbl IS NOT NULL THEN
5327: IF l_period_tbl.COUNT > 0 THEN
5328: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
5330: IF l_period_tbl.exists(l_idx) THEN
5331:
5332: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
5333:
5334: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
5335: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
5336: END IF;
5337: END LOOP;
5338: END IF;
5365: END IF;
5366: --l_lysp_in_clause := l_lysp_in_clause||')';
5367:
5368:
5369: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
5370: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
5371: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
5372: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
5373:
5367:
5368:
5369: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
5370: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
5371: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
5372: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
5373:
5374:
5375: l_total_lysp_sales_sql := l_total_lysp_sales_sql||l_lysp_in_clause||')';
5395: l_total_target_unalloc := l_total_root_quota;
5396: l_time_target_unalloc := l_total_root_quota / l_denominator;
5397: END IF;
5398:
5399: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor = '
5400: || l_multiplying_factor || ' ; ');
5401:
5402: ------ for product allocations: based upon Funds product spread ------------------
5403:
5410: l_total_fund_quota := NVL(l_total_fund_quota, 0);
5411:
5412: ------- Insert rows for ACCOUNTS ------------------------------------------------
5413:
5414: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name
5415: || ': Begin - Populating Account and Time Allocations Records'
5416: || ' FOR Fund_id = '|| l_fund_id
5417: || ' AND Territory_id = '|| l_territory_id
5418: || ' ; ');
5554:
5555: ------- Insert rows for PRODUCT and TIME Allocation Records for given ACCOUNT ---------------------------
5556:
5557:
5558: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
5559: ': Begin - Populating Product and Time Allocations Records'
5560: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
5561:
5562:
5749:
5750:
5751:
5752:
5753: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
5754: ': End - Populating Product and Time Allocations Records'
5755: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
5756:
5757:
5757:
5758: END LOOP account_loop;
5759:
5760:
5761: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating UNALLOCATED Allocations Records'
5762: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
5763:
5764: p_acct_alloc_rec := NULL;
5765:
5932: END IF;
5933:
5934: -------END: FIX for difference due to ROUNDING --------------------------------------------------|
5935:
5936: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating UNALLOCATED Allocations Records'
5937: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
5938:
5939:
5940:
5939:
5940:
5941:
5942:
5943: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
5944:
5945: EXCEPTION
5946:
5947: WHEN OZF_TP_INVALID_PARAM THEN
5950: FND_MSG_PUB.add;
5951: x_return_status := FND_API.g_ret_sts_error ;
5952: FND_MSG_PUB.count_and_get (p_count => x_error_number,
5953: p_data => x_error_message);
5954: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
5955: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
5956:
5957: WHEN FND_API.G_EXC_ERROR THEN
5958: ROLLBACK TO old_allocate_target_first_time;
5951: x_return_status := FND_API.g_ret_sts_error ;
5952: FND_MSG_PUB.count_and_get (p_count => x_error_number,
5953: p_data => x_error_message);
5954: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
5955: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
5956:
5957: WHEN FND_API.G_EXC_ERROR THEN
5958: ROLLBACK TO old_allocate_target_first_time;
5959: x_return_status := FND_API.g_ret_sts_error ;
5979: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
5980: END IF;
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||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
5984:
5985: END old_allocate_target_first_time;
5986:
5987:
6389:
6390: -- Initialize API return status to SUCCESS
6391: x_return_status := FND_API.g_ret_sts_success;
6392:
6393: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
6394:
6395: l_fund_id := p_fund_id;
6396:
6397: OPEN fund_csr(l_fund_id);
6410: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
6411: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
6412: l_period_type_id := l_fund_rec.period_type_id;
6413:
6414: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
6415: ||l_start_date||' AND '||l_end_date||' ; '
6416: ||' Period_Type_id = '||l_period_type_id||' ; ');
6417:
6418: IF l_start_date IS NULL OR
6430: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
6431: RAISE OZF_TP_BLANK_PERIOD_TBL;
6432: END IF;
6433:
6434: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
6435: ||' AND '||l_end_date||' ; ');
6436: IF l_period_tbl IS NOT NULL THEN
6437: IF l_period_tbl.COUNT > 0 THEN
6438: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
6440: IF l_period_tbl.exists(l_idx) THEN
6441:
6442: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
6443:
6444: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
6445: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
6446: END IF;
6447: END LOOP;
6448: END IF;
6475: END IF;
6476: --l_lysp_in_clause := l_lysp_in_clause||')';
6477:
6478:
6479: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
6480: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
6481: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
6482: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
6483:
6477:
6478:
6479: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
6480: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
6481: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
6482: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
6483:
6484:
6485: l_total_lysp_sales_sql := l_total_lysp_sales_sql||l_lysp_in_clause||')';
6505: l_total_target_unalloc := l_total_root_quota;
6506: l_time_target_unalloc := l_total_root_quota / l_denominator;
6507: END IF;
6508:
6509: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Multiplying Factor = '
6510: || l_multiplying_factor || ' ; ');
6511:
6512: ------ for product allocations: based upon Funds product spread ------------------
6513:
6520: l_total_fund_quota := NVL(l_total_fund_quota, 0);
6521:
6522: ------- Insert rows for ACCOUNTS ------------------------------------------------
6523:
6524: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name
6525: || ': Begin - Populating Account and Time Allocations Records'
6526: || ' FOR Fund_id = '|| l_fund_id
6527: || ' AND Territory_id = '|| l_territory_id
6528: || ' ; ');
6699:
6700: ------- Insert rows for PRODUCT and TIME Allocation Records for given ACCOUNT ---------------------------
6701:
6702:
6703: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
6704: ': Begin - Populating Product and Time Allocations Records'
6705: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
6706:
6707:
6895: ELSE
6896: l_prod_mltply_factor := 0;
6897: END IF;
6898:
6899: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6900: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6901: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6902:
6903: UPDATE OZF_TIME_ALLOCATIONS t
6896: l_prod_mltply_factor := 0;
6897: END IF;
6898:
6899: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6900: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6901: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6902:
6903: UPDATE OZF_TIME_ALLOCATIONS t
6904: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
6897: END IF;
6898:
6899: -- OZF_UTILITY_PVT.debug_message('1. mkothari --'||' -- l_prod_mltply_factor==> '||l_prod_mltply_factor || ' ; ');
6900: -- OZF_UTILITY_PVT.debug_message('2. mkothari --'||' l_total_account_target===> '||l_total_account_target);
6901: -- OZF_UTILITY_PVT.debug_message('3. mkothari --'||' l_p_grand_total_lysp_sales='||l_p_grand_total_lysp_sales);
6902:
6903: UPDATE OZF_TIME_ALLOCATIONS t
6904: SET t.TARGET = ROUND((NVL(t.LYSP_SALES, 0) * l_prod_mltply_factor), 0),
6905: t.object_version_number = t.object_version_number + 1,
7035: AND p.allocation_for_id = l_account_allocation_id;
7036:
7037: l_diff_target := (NVL(l_total_account_target, 0) - NVL(l_diff_target_1, 0));
7038:
7039: -- OZF_UTILITY_PVT.debug_message('mkothari -- l_diff_target => '||l_diff_target || ' ; ');
7040:
7041: EXCEPTION
7042: WHEN OTHERS THEN
7043: l_diff_target := 0;
7174:
7175: --------END: FIX for difference due to ROUNDING FOR each ShipTos Product Spread ------------------|
7176:
7177:
7178: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
7179: ': End - Populating Product and Time Allocations Records'
7180: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
7181:
7182:
7182:
7183: END LOOP account_loop;
7184:
7185:
7186: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Begin - Populating UNALLOCATED Allocations Records'
7187: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
7188:
7189: p_acct_alloc_rec := NULL;
7190:
7319: AND a.parent_party_id = -9999 );
7320:
7321: l_diff_target := NVL(l_diff_target_1, 0) - NVL(l_diff_target_2, 0);
7322:
7323: -- OZF_UTILITY_PVT.debug_message('mkothari --- UNALLOCATED l_diff_target => '||l_diff_target || ' ; ');
7324:
7325: EXCEPTION
7326: WHEN OTHERS THEN
7327: l_diff_target := 0;
7365: END IF;
7366:
7367: -------END: FIX for difference due to ROUNDING --------------------------------------------------|
7368:
7369: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': End - Populating UNALLOCATED Allocations Records'
7370: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
7371:
7372:
7373:
7387: AND a.allocation_for_id = l_fund_id;
7388:
7389: l_diff_target := (NVL(l_total_root_quota, 0) - NVL(l_diff_target_1, 0));
7390:
7391: -- OZF_UTILITY_PVT.debug_message('mkothari-- ALL TARGETS l_diff_target => '||l_diff_target || ' ; ');
7392:
7393: EXCEPTION
7394: WHEN OTHERS THEN
7395: l_diff_target := 0;
7451: ELSE
7452: l_diff_target := FLOOR(l_diff_target); -- (So, +1.5 will become +1)
7453: END IF;
7454:
7455: -- OZF_UTILITY_PVT.debug_message('222. mkothari-- ALL TARGETS l_diff_target => '||l_diff_target || ' ; ');
7456:
7457: l_temp_account_allocation_id := 0;
7458:
7459: UPDATE OZF_TIME_ALLOCATIONS t
7502: RAISE fnd_api.g_exc_unexpected_error;
7503: END IF;
7504:
7505:
7506: -- OZF_UTILITY_PVT.debug_message('4.mkothari--'||'ALL TARGETS AccAllocID=> '||l_temp_account_allocation_id|| ';');
7507:
7508: UPDATE OZF_ACCOUNT_ALLOCATIONS a
7509: SET a.TARGET = a.TARGET + l_diff_target,
7510: a.object_version_number = a.object_version_number + 1,
7606:
7607:
7608: -------END: FIX for difference due to ROUNDING for ALL Targets taken together--------------------|
7609:
7610: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
7611:
7612: EXCEPTION
7613:
7614: WHEN OZF_TP_INVALID_PARAM THEN
7617: FND_MSG_PUB.add;
7618: x_return_status := FND_API.g_ret_sts_error ;
7619: FND_MSG_PUB.count_and_get (p_count => x_error_number,
7620: p_data => x_error_message);
7621: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
7622: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
7623:
7624: WHEN FND_API.G_EXC_ERROR THEN
7625: ROLLBACK TO allocate_target_first_time;
7618: x_return_status := FND_API.g_ret_sts_error ;
7619: FND_MSG_PUB.count_and_get (p_count => x_error_number,
7620: p_data => x_error_message);
7621: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
7622: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
7623:
7624: WHEN FND_API.G_EXC_ERROR THEN
7625: ROLLBACK TO allocate_target_first_time;
7626: x_return_status := FND_API.g_ret_sts_error ;
7646: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
7647: END IF;
7648: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
7649: p_data => x_error_message);
7650: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
7651:
7652: END allocate_target_first_time;
7653:
7654:
8054:
8055: -- Initialize API return status to SUCCESS
8056: x_return_status := FND_API.g_ret_sts_success;
8057:
8058: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- start');
8059:
8060: l_fund_id := p_fund_id;
8061: l_old_start_date := p_old_start_date;
8062: l_new_end_date := p_new_end_date;
8079: l_start_date := to_char(l_old_start_date, 'YYYY/MM/DD');
8080: l_end_date := to_char(l_new_end_date, 'YYYY/MM/DD');
8081: l_period_type_id := 32; -- l_fund_rec.period_type_id;
8082:
8083: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8084: ': Begin - Getting ADDON Time_ids Between '
8085: ||l_start_date||' AND '||l_end_date||' ; ');
8086:
8087: l_new_period_tbl := OZF_TIME_API_PVT.get_period_tbl
8117: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8118: END IF;
8119: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8120: p_data => x_error_message);
8121: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : COLLECTION UNNESTING: OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8122:
8123: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
8124: RAISE OZF_TP_BLANK_PERIOD_TBL;
8125: END IF;
8129: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
8130: RAISE OZF_TP_BLANK_PERIOD_TBL;
8131: END IF;
8132:
8133: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8134: ': End - Getting ADDON Time_ids Between '
8135: ||l_start_date||' AND '||l_end_date||' ; ');
8136:
8137:
8142: IF l_period_tbl.exists(l_idx) THEN
8143:
8144: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
8145:
8146: OZF_UTILITY_PVT.debug_message(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
8147: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
8148: END IF;
8149: END LOOP;
8150: END IF;
8177: END IF;
8178: --l_lysp_in_clause := l_lysp_in_clause||')';
8179:
8180:
8181: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
8182: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
8183: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
8184: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
8185:
8179:
8180:
8181: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
8182: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
8183: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
8184: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
8185:
8186: OPEN total_lysp_sales_csr(l_fund_id,l_lysp_in_clause);
8187: FETCH total_lysp_sales_csr INTO l_total_lysp_sales;
8212:
8213:
8214: ------- Insert rows for ACCOUNT-TIME Allocations and Update rows for Account Allocations ---------
8215:
8216: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8217: ': Begin - Updating Account and Populating Time Allocations Records'
8218: || 'FOR Fund_id = '|| l_fund_id || ' ; ');
8219: <
8220: FOR account_rec IN account_csr(l_fund_id)
8301:
8302: ------- Insert rows for Product-Time Allocations and Update rows for Account Allocations ---------
8303:
8304:
8305: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8306: ': Begin - Updating Product and Populating Time Allocations Records'
8307: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
8308:
8309:
8438: END LOOP account_product_loop;
8439:
8440:
8441:
8442: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8443: ': End - Updating Product and Populating Time Allocations Records'
8444: || 'FOR site_use_id = '|| account_rec.site_use_id || ' ; ');
8445:
8446:
8449:
8450: ------- Insert rows for Unalloc-Time Allocations and Update rows for Unalloc Allocations ---------
8451:
8452:
8453: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8454: ': Begin - Updating UNALLOCATED Account and Populating Time Allocations Records'
8455: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
8456:
8457: UPDATE ozf_account_allocations a
8518: END IF;
8519: END LOOP unalloc_time_loop;
8520:
8521:
8522: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name ||
8523: ': End - Updating UNALLOCATED Account and Populating Time Allocations Records'
8524: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
8525:
8526:
8524: || 'FOR FUND_Id = '|| l_fund_id || ' ; ');
8525:
8526:
8527:
8528: OZF_UTILITY_PVT.debug_message('Private API: ' || l_full_api_name || ' -- end');
8529:
8530: EXCEPTION
8531:
8532: WHEN OZF_TP_INVALID_PARAM THEN
8535: FND_MSG_PUB.add;
8536: x_return_status := FND_API.g_ret_sts_error ;
8537: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8538: p_data => x_error_message);
8539: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8540: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8541:
8542: WHEN FND_API.G_EXC_ERROR THEN
8543: ROLLBACK TO allocate_target_addon;
8536: x_return_status := FND_API.g_ret_sts_error ;
8537: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8538: p_data => x_error_message);
8539: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8540: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8541:
8542: WHEN FND_API.G_EXC_ERROR THEN
8543: ROLLBACK TO allocate_target_addon;
8544: x_return_status := FND_API.g_ret_sts_error ;
8564: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8565: END IF;
8566: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8567: p_data => x_error_message);
8568: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8569:
8570: END allocate_target_addon;
8571:
8572:
8670:
8671: -- Initialize API return status to SUCCESS
8672: x_return_status := FND_API.g_ret_sts_success;
8673:
8674: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- start');
8675:
8676: l_mode := p_mode;
8677: l_obj_id := p_obj_id;
8678: l_context:= p_context;
8676: l_mode := p_mode;
8677: l_obj_id := p_obj_id;
8678: l_context:= p_context;
8679:
8680: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8681: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8682: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8683: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8684:
8677: l_obj_id := p_obj_id;
8678: l_context:= p_context;
8679:
8680: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8681: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8682: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8683: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8684:
8685:
8678: l_context:= p_context;
8679:
8680: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8681: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8682: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8683: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8684:
8685:
8686: IF l_mode IN ('CREATE', 'DELETE', 'PUBLISH', 'ADD')
8679:
8680: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8681: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8682: OZF_UTILITY_PVT.debug_message('2. l_Object_id--------->: ' || l_obj_id);
8683: OZF_UTILITY_PVT.debug_message('3. l_context----------->: ' || l_context);
8684:
8685:
8686: IF l_mode IN ('CREATE', 'DELETE', 'PUBLISH', 'ADD')
8687: AND l_obj_id > 0
8753: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
8754: RAISE FND_API.g_exc_unexpected_error;
8755: END IF;
8756:
8757: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- end');
8758:
8759: EXCEPTION
8760:
8761: WHEN OZF_TP_INVALID_PARAM THEN
8764: FND_MSG_PUB.add;
8765: x_return_status := FND_API.g_ret_sts_error ;
8766: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8767: p_data => x_error_message);
8768: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8769: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8770:
8771: WHEN FND_API.G_EXC_ERROR THEN
8772: ROLLBACK TO setup_product_spread;
8765: x_return_status := FND_API.g_ret_sts_error ;
8766: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8767: p_data => x_error_message);
8768: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8769: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8770:
8771: WHEN FND_API.G_EXC_ERROR THEN
8772: ROLLBACK TO setup_product_spread;
8773: x_return_status := FND_API.g_ret_sts_error ;
8793: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8794: END IF;
8795: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8796: p_data => x_error_message);
8797: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8798:
8799: END setup_product_spread;
8800:
8801:
8860:
8861: -- Initialize API return status to SUCCESS
8862: x_return_status := FND_API.g_ret_sts_success;
8863:
8864: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- start');
8865:
8866: l_mode := p_mode;
8867: l_fund_id := p_fund_id;
8868: l_item_id := p_item_id;
8867: l_fund_id := p_fund_id;
8868: l_item_id := p_item_id;
8869: l_item_type := p_item_type;
8870:
8871: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8872: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8873: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8874: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8875: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8868: l_item_id := p_item_id;
8869: l_item_type := p_item_type;
8870:
8871: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8872: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8873: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8874: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8875: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8876:
8869: l_item_type := p_item_type;
8870:
8871: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8872: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8873: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8874: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8875: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8876:
8877: IF l_mode IN ('ADD', 'DELETE')
8870:
8871: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8872: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8873: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8874: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8875: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8876:
8877: IF l_mode IN ('ADD', 'DELETE')
8878: AND l_fund_id > 0
8871: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
8872: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
8873: OZF_UTILITY_PVT.debug_message('2. l_fund_id ---------->: ' || l_fund_id);
8874: OZF_UTILITY_PVT.debug_message('3. l_item_id ---------->: ' || l_item_id);
8875: OZF_UTILITY_PVT.debug_message('4. l_item_type--------->: ' || l_item_type);
8876:
8877: IF l_mode IN ('ADD', 'DELETE')
8878: AND l_fund_id > 0
8879: AND l_item_id > 0
8918: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
8919: RAISE FND_API.g_exc_unexpected_error;
8920: END IF;
8921:
8922: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- end');
8923:
8924: EXCEPTION
8925:
8926: WHEN OZF_TP_INVALID_PARAM THEN
8929: FND_MSG_PUB.add;
8930: x_return_status := FND_API.g_ret_sts_error ;
8931: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8932: p_data => x_error_message);
8933: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8934: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8935:
8936: WHEN FND_API.G_EXC_ERROR THEN
8937: ROLLBACK TO cascade_product_spread;
8930: x_return_status := FND_API.g_ret_sts_error ;
8931: FND_MSG_PUB.count_and_get (p_count => x_error_number,
8932: p_data => x_error_message);
8933: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
8934: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
8935:
8936: WHEN FND_API.G_EXC_ERROR THEN
8937: ROLLBACK TO cascade_product_spread;
8938: x_return_status := FND_API.g_ret_sts_error ;
8958: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
8959: END IF;
8960: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
8961: p_data => x_error_message);
8962: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
8963:
8964: END cascade_product_spread;
8965:
8966:
9032:
9033: -- Initialize API return status to SUCCESS
9034: x_return_status := FND_API.g_ret_sts_success;
9035:
9036: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- start');
9037:
9038: l_mode := p_mode;
9039: l_fund_id := p_fund_id;
9040: l_old_start_date := p_old_start_date;
9041: l_new_end_date := p_new_end_date;
9042: l_addon_fact_id:= p_addon_fact_id;
9043: l_addon_amount:= p_addon_amount;
9044:
9045: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9046: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9042: l_addon_fact_id:= p_addon_fact_id;
9043: l_addon_amount:= p_addon_amount;
9044:
9045: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9046: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9050: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9043: l_addon_amount:= p_addon_amount;
9044:
9045: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9046: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9050: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9051: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9044:
9045: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9046: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9050: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9051: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9052:
9045: OZF_UTILITY_PVT.debug_message('API Parameters For:---->: ' || l_full_api_name);
9046: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9050: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9051: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9052:
9053: IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
9046: OZF_UTILITY_PVT.debug_message('1. l_mode ------------->: ' || l_mode);
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9050: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9051: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9052:
9053: IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
9054: NULL;
9047: OZF_UTILITY_PVT.debug_message('2. l_fund_id----------->: ' || l_fund_id);
9048: OZF_UTILITY_PVT.debug_message('3. l_old_start_date---->: ' || to_char(l_old_start_date, 'YYYY/MM/DD'));
9049: OZF_UTILITY_PVT.debug_message('4. l_new_end_date------>: ' || to_char(l_new_end_date, 'YYYY/MM/DD'));
9050: OZF_UTILITY_PVT.debug_message('5. l_addon_fact_id----->: ' || l_addon_fact_id);
9051: OZF_UTILITY_PVT.debug_message('6. l_addon_amount------>: ' || l_addon_amount);
9052:
9053: IF l_mode IN ('FIRSTTIME', 'ADDON', 'DELETE') AND l_fund_id > 0 THEN
9054: NULL;
9055: ELSE
9115: ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
9116: RAISE FND_API.g_exc_unexpected_error;
9117: END IF;
9118:
9119: OZF_UTILITY_PVT.debug_message('Public API: ' || l_full_api_name || ' -- end');
9120:
9121: EXCEPTION
9122:
9123: WHEN OZF_TP_INVALID_PARAM THEN
9126: FND_MSG_PUB.add;
9127: x_return_status := FND_API.g_ret_sts_error ;
9128: FND_MSG_PUB.count_and_get (p_count => x_error_number,
9129: p_data => x_error_message);
9130: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
9131: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
9132:
9133: WHEN FND_API.G_EXC_ERROR THEN
9134: ROLLBACK TO allocate_target;
9127: x_return_status := FND_API.g_ret_sts_error ;
9128: FND_MSG_PUB.count_and_get (p_count => x_error_number,
9129: p_data => x_error_message);
9130: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
9131: OZF_UTILITY_PVT.debug_message(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
9132:
9133: WHEN FND_API.G_EXC_ERROR THEN
9134: ROLLBACK TO allocate_target;
9135: x_return_status := FND_API.g_ret_sts_error ;
9155: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
9156: END IF;
9157: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
9158: p_data => x_error_message);
9159: OZF_UTILITY_PVT.debug_message(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
9160:
9161: END allocate_target;
9162:
9163:
9528:
9529: -- Initialize API return status to SUCCESS
9530: x_return_status := FND_API.g_ret_sts_success;
9531:
9532: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9533:
9534: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9535: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9536: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9530: x_return_status := FND_API.g_ret_sts_success;
9531:
9532: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9533:
9534: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9535: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9536: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9537: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9538:
9531:
9532: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9533:
9534: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9535: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9536: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9537: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9538:
9539:
9532: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
9533:
9534: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9535: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9536: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9537: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9538:
9539:
9540: OPEN fund_csr;
9533:
9534: Ozf_Utility_pvt.write_conc_log('- Parameter - p_fund_id ==> ' || p_fund_id);
9535: Ozf_Utility_pvt.write_conc_log('- Parameter - p_corr_fund_id ==> ' || p_corr_fund_id);
9536: Ozf_Utility_pvt.write_conc_log('- Parameter - p_terr_id ==> ' || p_terr_id);
9537: Ozf_Utility_pvt.write_conc_log('- Parameter - p_ship_to_site_use_id ==> ' || p_ship_to_site_use_id);
9538:
9539:
9540: OPEN fund_csr;
9541: FETCH fund_csr INTO l_fund_rec;
9544: l_start_date := to_char(l_fund_rec.start_date_active, 'YYYY/MM/DD');
9545: l_end_date := to_char(l_fund_rec.end_date_active, 'YYYY/MM/DD');
9546: l_period_type_id := l_fund_rec.period_type_id;
9547:
9548: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': Begin - Getting Time_ids Between '
9549: ||l_start_date||' AND '||l_end_date||' ; '
9550: ||' Period_Type_id = '||l_period_type_id||' ; ');
9551:
9552: IF l_start_date IS NULL OR
9564: IF l_period_tbl IS NULL OR l_period_tbl.COUNT <= 0 THEN
9565: RAISE OZF_TP_BLANK_PERIOD_TBL;
9566: END IF;
9567:
9568: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': End - Getting Time_ids Between '||l_start_date
9569: ||' AND '||l_end_date||' ; ');
9570: IF l_period_tbl IS NOT NULL THEN
9571: IF l_period_tbl.COUNT > 0 THEN
9572: FOR l_idx IN l_period_tbl.first..l_period_tbl.last
9574: IF l_period_tbl.exists(l_idx) THEN
9575:
9576: l_lysp_period_tbl(l_idx) := OZF_TIME_API_PVT.get_lysp_id (l_period_tbl(l_idx), l_period_type_id);
9577:
9578: Ozf_Utility_pvt.write_conc_log(SubStr('l_period_tbl('||TO_CHAR(l_idx)||') = '
9579: ||TO_CHAR(l_period_tbl(l_idx)), 1,255));
9580: END IF;
9581: END LOOP;
9582: END IF;
9609: END IF;
9610: --l_lysp_in_clause := l_lysp_in_clause||')';
9611:
9612:
9613: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
9614: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
9615: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
9616: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
9617:
9611:
9612:
9613: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': Time_ids between '||l_start_date
9614: ||' AND '||l_end_date||' ARE : '||l_in_clause||' ; ');
9615: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ': LYSP-Time_ids between '||l_start_date
9616: ||' AND '||l_end_date||' ARE : '||l_lysp_in_clause||' ; ');
9617:
9618:
9619: -- 1. CREATING NEW SHIPTO ACCOUNT HEADER RECORD ....
10183: END LOOP account_product_loop;
10184:
10185: END IF;
10186:
10187: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- end');
10188:
10189: EXCEPTION
10190:
10191: WHEN OZF_TP_INVALID_PARAM THEN
10194: FND_MSG_PUB.add;
10195: x_return_status := FND_API.g_ret_sts_error ;
10196: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10197: p_data => x_error_message);
10198: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10199: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10200:
10201: WHEN FND_API.G_EXC_ERROR THEN
10202: ROLLBACK TO adjust_target_for_acct_added;
10195: x_return_status := FND_API.g_ret_sts_error ;
10196: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10197: p_data => x_error_message);
10198: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10199: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10200:
10201: WHEN FND_API.G_EXC_ERROR THEN
10202: ROLLBACK TO adjust_target_for_acct_added;
10203: x_return_status := FND_API.g_ret_sts_error ;
10223: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
10224: END IF;
10225: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
10226: p_data => x_error_message);
10227: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
10228:
10229: END adjust_target_for_acct_added;
10230:
10231: -- ------------------------
10281:
10282: -- Initialize API return status to SUCCESS
10283: x_return_status := FND_API.g_ret_sts_success;
10284:
10285: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
10286:
10287: IF p_fund_id IS NULL OR p_ship_to_site_use_id IS NULL
10288: THEN
10289: RAISE OZF_TP_INVALID_PARAM;
10319:
10320: -- 1. ADJUSTING UNALLOCATED Time records....
10321:
10322: BEGIN
10323: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 1. ADJUSTING UNALLOCATED Time records....');
10324:
10325: -- Increase the Unallocated for Current and Future Periods
10326: update ozf_time_allocations tta
10327: set tta.target = tta.target + (
10350: END;
10351:
10352: -- 1(b). ADJUSTING UNALLOCATED Account record ....
10353: BEGIN
10354: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 1(b). ADJUSTING UNALLOCATED Account record....');
10355:
10356: -- Rollup the total targets to the unallocated records
10357: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10358: SET (aa.TARGET, aa.LYSP_SALES) = (
10370:
10371: -- 2. ADJUSTING SHIP TO Time records. - Curr and Future Periods ...
10372:
10373: BEGIN
10374: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 2. ADJUSTING SHIP TO Time records....');
10375:
10376: -- Past Targets stay with the shipTo
10377: -- Make ShipTos Current and Future Periods Targets ZERO
10378: update ozf_time_allocations ttb
10396: END;
10397:
10398: -- 2(b). ADJUSTING SHIP TO Account record ....
10399: BEGIN
10400: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 2(b). ADJUSTING SHIP TO Account record....');
10401:
10402: -- Rollup the total targets to the shipTo record
10403: UPDATE OZF_ACCOUNT_ALLOCATIONS aa
10404: SET (aa.TARGET, aa.LYSP_SALES) = (
10418:
10419: -- 3. ADJUSTING ShipTos PRODUCT SPREAD Time records ....
10420:
10421: BEGIN
10422: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 3. ADJUSTING ShipTos Product Spread Time Records....');
10423:
10424: -- CURRENT AND FUTURE TARGETS of this Shiptos products are made ZERO
10425: update ozf_time_allocations tt
10426: set tt.target = 0,
10449: -- Unallocated is automatically shown in the header of the product spread
10450:
10451: -- 5. ADJUSTING ShipTos PRODUCT SPREAD Header records ....
10452: BEGIN
10453: Ozf_Utility_pvt.write_conc_log(' - '||l_full_api_name|| ' - 5. ADJUSTING ShipTos Product Spread Header Records....');
10454:
10455: -- Rollup the target numbers to the product record
10456: UPDATE OZF_PRODUCT_ALLOCATIONS p
10457: SET p.TARGET = (SELECT SUM(ti.TARGET)
10474:
10475:
10476: <
10477:
10478: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- end');
10479:
10480: EXCEPTION
10481:
10482: WHEN OZF_TP_INVALID_PARAM THEN
10485: FND_MSG_PUB.add;
10486: x_return_status := FND_API.g_ret_sts_error ;
10487: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10488: p_data => x_error_message);
10489: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10490: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10491:
10492: WHEN FND_API.G_EXC_ERROR THEN
10493: ROLLBACK TO adjust_target_for_acct_deleted;
10486: x_return_status := FND_API.g_ret_sts_error ;
10487: FND_MSG_PUB.count_and_get (p_count => x_error_number,
10488: p_data => x_error_message);
10489: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : INVALID PARAMETER EXCEPTION = '||sqlerrm(sqlcode));
10490: Ozf_Utility_pvt.write_conc_log(fnd_message.get_string('OZF', 'OZF_TP_INVALID_PARAM_TXT'));
10491:
10492: WHEN FND_API.G_EXC_ERROR THEN
10493: ROLLBACK TO adjust_target_for_acct_deleted;
10494: x_return_status := FND_API.g_ret_sts_error ;
10514: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
10515: END IF;
10516: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
10517: p_data => x_error_message);
10518: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
10519:
10520: END adjust_target_for_acct_deleted;
10521:
10522:
10839:
10840: -- Initialize API return status to SUCCESS
10841: x_return_status := FND_API.g_ret_sts_success;
10842:
10843: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- start');
10844:
10845: FOR terr IN terr_list_csr
10846: LOOP
10847: ---1. PROCESS ADDITION OF ACCOUNTS
10854: FOR quota_allocation in allocation_list_csr (quota.fund_id)
10855: LOOP
10856:
10857: IF G_DEBUG_LEVEL THEN
10858: Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- Process Added Accounts for Quota Id =>'||quota.fund_id);
10859: END IF;
10860: -- FIND the corr_quota, such that there exists a corr_terr which lost that ship_to
10861: OPEN corr_quota_csr (quota_allocation.activity_metric_id, new_accounts.site_use_id);
10862: FETCH corr_quota_csr INTO l_corr_quota_id, l_node_id, l_previous_fact_id, l_activity_metric_fact_id;
10895:
10896: FOR quota IN del_quota_list_csr(terr.node_id, del_accounts.site_use_id)
10897: LOOP
10898: IF G_DEBUG_LEVEL THEN
10899: Ozf_Utility_pvt.write_conc_log('- '||l_full_api_name||'- Process Deleted Accounts for Site Use Id =>'||del_accounts.site_use_id);
10900: END IF;
10901: adjust_target_for_acct_deleted (
10902: p_api_version => p_api_version,
10903: x_return_status => x_return_status,
10919: END LOOP; -- deleted_accounts_csr (terr.node_id)
10920:
10921: -- 3. UPDATE ACCOUNT ALLOCATION account details based upon latest information from Terr (i.e from TCA )
10922: IF G_DEBUG_LEVEL THEN
10923: 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).');
10924: END IF;
10925:
10926:
10927: --- Jan 5th, 2005
10982:
10983:
10984: END LOOP; -- terr_list_csr
10985:
10986: Ozf_Utility_pvt.write_conc_log('Private API: ' || l_full_api_name || ' -- end');
10987:
10988: EXCEPTION
10989:
10990: WHEN FND_API.G_EXC_ERROR THEN
11012: FND_MSG_PUB.Add_Exc_Msg (g_pkg_name, l_api_name);
11013: END IF;
11014: FND_MSG_PUB.Count_And_Get (p_count => x_error_number,
11015: p_data => x_error_message);
11016: Ozf_Utility_pvt.write_conc_log(l_full_api_name||' : OTHERS EXCEPTION = '||sqlerrm(sqlcode));
11017:
11018: END adjust_account_targets;
11019:
11020: