[Home] [Help]
688: BEGIN
689:
690: IF (OZF_DEBUG_HIGH_ON) THEN
691:
692: OZF_Utility_PVT.debug_message(l_full_name || ': Start Creating Dimentions');
693:
694: END IF;
695:
696: SAVEPOINT Create_Dimentions;
892:
893: END IF;
894:
895: IF (OZF_DEBUG_HIGH_ON) THEN
896: OZF_Utility_PVT.debug_message(l_full_name || ': End Creating Dimentions');
897: END IF;
898:
899: EXCEPTION
900:
1009: BEGIN
1010:
1011: IF (OZF_DEBUG_HIGH_ON) THEN
1012:
1013: OZF_Utility_PVT.debug_message(l_full_name || ': Start populate products');
1014:
1015: END IF;
1016:
1017: SAVEPOINT Populate_Products;
1163: END LOOP;
1164:
1165: IF (OZF_DEBUG_HIGH_ON) THEN
1166:
1167: OZF_Utility_PVT.debug_message(l_full_name || ': End populate products');
1168:
1169: END IF;
1170:
1171: EXCEPTION
1298: BEGIN
1299:
1300: IF (OZF_DEBUG_HIGH_ON) THEN
1301:
1302: OZF_Utility_PVT.debug_message(l_full_name || ': Start populate customers');
1303:
1304: END IF;
1305: SAVEPOINT Populate_Customers;
1306:
1528:
1529: BEGIN
1530: IF (OZF_DEBUG_HIGH_ON)
1531: THEN
1532: OZF_Utility_PVT.debug_message(l_full_name || ': Start Populate Periods');
1533: END IF;
1534: SAVEPOINT Populate_Periods;
1535:
1536: IF FND_API.to_boolean(p_init_msg_list) THEN
1643: END LOOP;
1644:
1645: IF (OZF_DEBUG_HIGH_ON)
1646: THEN
1647: OZF_Utility_PVT.debug_message(l_full_name || ': End Populate Periods');
1648: END IF;
1649:
1650: EXCEPTION
1651: WHEN OTHERS THEN
1919:
1920: BEGIN
1921:
1922: IF (OZF_DEBUG_HIGH_ON) THEN
1923: OZF_Utility_PVT.debug_message(l_full_name || ': Start Create Dimention Facts');
1924: END IF;
1925:
1926: SAVEPOINT Create_Dimention_Facts;
1927:
2127:
2128: END IF;
2129:
2130: IF (OZF_DEBUG_HIGH_ON) THEN
2131: OZF_Utility_PVT.debug_message(l_full_name || ': End Create Dimention Facts');
2132: END IF;
2133:
2134: EXCEPTION
2135:
2229:
2230: BEGIN
2231:
2232: IF (OZF_DEBUG_HIGH_ON) THEN
2233: OZF_Utility_PVT.debug_message(l_full_name || ': Start Create Fcst Facts');
2234: END IF;
2235:
2236: SAVEPOINT Create_Fcst_Facts;
2237:
2439: END IF; -- End Level Three
2440:
2441: IF (OZF_DEBUG_HIGH_ON) THEN
2442:
2443: OZF_Utility_PVT.debug_message(l_full_name || ': End Create Fcst Facts');
2444:
2445: END IF;
2446:
2447: EXCEPTION
2654:
2655: BEGIN
2656:
2657: --IF (OZF_DEBUG_HIGH_ON) THEN
2658: -- OZF_Utility_PVT.debug_message(l_full_name || ': Start get_best_fit_lift ');
2659: --END IF;
2660:
2661: -- First: Get Offer or Wkst Forecast Header Level Details from the forecast id
2662: IF p_obj_type = 'OFFR'
2844: -- See how to get discount percent in process_baseline ....
2845:
2846: IF l_where_str IS NOT NULL THEN
2847: l_final_lift_sql := l_lift_factor_sql || l_where_str;
2848: --OZF_Utility_PVT.debug_message( ' ~~l_where_str~~=> '|| l_where_str );
2849: ELSE
2850: l_final_lift_sql := l_lift_factor_sql;
2851: END IF;
2852: --OZF_Utility_PVT.debug_message( ' ~~~l_final_lift_sql~~~~ => '||l_final_lift_sql );
2848: --OZF_Utility_PVT.debug_message( ' ~~l_where_str~~=> '|| l_where_str );
2849: ELSE
2850: l_final_lift_sql := l_lift_factor_sql;
2851: END IF;
2852: --OZF_Utility_PVT.debug_message( ' ~~~l_final_lift_sql~~~~ => '||l_final_lift_sql );
2853:
2854: OPEN get_lift_factor_csr FOR l_final_lift_sql USING
2855: p_base_quantity_ref,
2856: p_market_type,
2860: p_report_date
2861: ;
2862: FETCH get_lift_factor_csr INTO l_lift_factor;
2863: CLOSE get_lift_factor_csr ;
2864: --OZF_Utility_PVT.debug_message( ' ~~~ l_lift_factor ~~~~ =====> '|| to_char(l_lift_factor) );
2865:
2866:
2867: IF l_lift_factor IS NULL THEN
2868:
2865:
2866:
2867: IF l_lift_factor IS NULL THEN
2868:
2869: --OZF_Utility_PVT.debug_message( ' ~~~ l_lift_factor IS NULL ~~~~ ');
2870:
2871: l_where_str := NULL;
2872: l_final_lift_sql := NULL;
2873:
2922: END IF;
2923:
2924: IF l_where_str IS NOT NULL THEN
2925: l_final_lift_sql := l_lift_factor_sql || l_where_str;
2926: --OZF_Utility_PVT.debug_message( ' ~222~l_where_str~~=> '|| l_where_str );
2927: ELSE
2928: l_final_lift_sql := l_lift_factor_sql;
2929: END IF;
2930:
2938: ;
2939: FETCH get_lift_factor_csr INTO l_lift_factor;
2940: CLOSE get_lift_factor_csr ;
2941:
2942: --OZF_Utility_PVT.debug_message( ' 2222~~~ l_lift_factor ~~~~ =====> '||l_lift_factor );
2943:
2944: END IF; -- IF l_lift_factor IS NULL THEN
2945:
2946: /*
2953: END IF;
2954: */
2955:
2956: -- IF (OZF_DEBUG_HIGH_ON) THEN
2957: -- OZF_Utility_PVT.debug_message(l_full_name || ': End get_best_fit_lift ');
2958: -- END IF;
2959:
2960: RETURN NVL(l_lift_factor, 0);
2961:
2964: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
2965: THEN
2966: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2967: END IF;
2968: --OZF_Utility_PVT.debug_message(' get_best_fit_lift : OTHER ERROR ' || sqlerrm );
2969: FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
2970: p_count => l_msg_count,
2971: p_data => l_msg_data);
2972: Return 0; -- i.e. lift is ZERO
3180: BEGIN
3181:
3182:
3183: IF (OZF_DEBUG_HIGH_ON) THEN
3184: OZF_Utility_PVT.debug_message(l_full_name || ': Start adjusting baseline product, market and time spreads');
3185: END IF;
3186:
3187: --
3188: -- Initialize savepoint.
3450: -- return new incr sales
3451: p_new_incremental_sales := l_new_incremental_sales;
3452:
3453: IF (OZF_DEBUG_HIGH_ON) THEN
3454: OZF_Utility_PVT.debug_message(l_full_name || ': End adjusting baseline product, market and time spreads');
3455: END IF;
3456:
3457: EXCEPTION
3458:
3715: BEGIN
3716:
3717:
3718: IF (OZF_DEBUG_HIGH_ON) THEN
3719: OZF_Utility_PVT.debug_message(l_full_name || ': Start Process Baseline Forecast ');
3720: END IF;
3721:
3722: --
3723: -- Initialize savepoint.
4128: --last_scenario_id = NVL(last_scenario_id,0)+1
4129: WHERE outer.forecast_id = p_forecast_id;
4130:
4131: IF (OZF_DEBUG_HIGH_ON) THEN
4132: OZF_Utility_PVT.debug_message(l_full_name || ': End Process Baseline Forecast ');
4133: END IF;
4134:
4135: EXCEPTION
4136:
4210: BEGIN
4211:
4212: IF (OZF_DEBUG_HIGH_ON)
4213: THEN
4214: OZF_Utility_PVT.debug_message(l_full_name || ': Start Create Wkst Forecasts');
4215: END IF;
4216:
4217: --
4218: -- Initialize savepoint.
4527: END LOOP;
4528:
4529: IF (OZF_DEBUG_HIGH_ON)
4530: THEN
4531: OZF_Utility_PVT.debug_message(l_full_name || ': End Create Wkst Forecasts');
4532: END IF;
4533:
4534: EXCEPTION
4535: WHEN FND_API.G_EXC_ERROR THEN
4672: BEGIN
4673:
4674: IF (OZF_DEBUG_HIGH_ON) THEN
4675:
4676: OZF_Utility_PVT.debug_message(l_full_name || ': Start Create Base Sales');
4677:
4678: END IF;
4679:
4680: --
5309: <
5310:
5311: IF (OZF_DEBUG_HIGH_ON) THEN
5312:
5313: OZF_Utility_PVT.debug_message(l_full_name || ': End Create Base Sales');
5314:
5315: END IF;
5316: --dbms_output.put_line( ' -- 15 -- ');
5317:
5409: BEGIN
5410:
5411: IF (OZF_DEBUG_HIGH_ON) THEN
5412:
5413: OZF_Utility_PVT.debug_message(l_full_name || ': Start Fcst Remaining Qty');
5414:
5415: END IF;
5416: SAVEPOINT Fcst_Remaining_Qty;
5417:
5470:
5471:
5472: IF (OZF_DEBUG_HIGH_ON) THEN
5473:
5474: OZF_Utility_PVT.debug_message(l_full_name || ': End Fcst Remaining Qty');
5475:
5476: END IF;
5477:
5478: EXCEPTION
5546: BEGIN
5547:
5548: IF (OZF_DEBUG_HIGH_ON) THEN
5549:
5550: OZF_Utility_PVT.debug_message(l_full_name || ': Start Fcst Baseline Remaining Qty');
5551:
5552: END IF;
5553: SAVEPOINT Fcst_BL_Remaining_Qty;
5554:
5606:
5607:
5608: IF (OZF_DEBUG_HIGH_ON) THEN
5609:
5610: OZF_Utility_PVT.debug_message(l_full_name || ': End Fcst Remaining Qty');
5611:
5612: END IF;
5613:
5614: EXCEPTION
5682: BEGIN
5683:
5684: IF (OZF_DEBUG_HIGH_ON) THEN
5685:
5686: OZF_Utility_PVT.debug_message(l_full_name || ': Start Freeze Check');
5687:
5688: END IF;
5689: SAVEPOINT freeze_check;
5690:
5759: END IF;
5760:
5761: IF (OZF_DEBUG_HIGH_ON) THEN
5762:
5763: OZF_Utility_PVT.debug_message(l_full_name || ': End Freeze Check');
5764:
5765: END IF;
5766:
5767: EXCEPTION
5906: SAVEPOINT copy_forecast;
5907:
5908: IF (OZF_DEBUG_HIGH_ON) THEN
5909:
5910: OZF_Utility_PVT.debug_message(l_full_name || ': start refresh parties');
5911:
5912: END IF;
5913:
5914: IF FND_API.to_boolean(p_init_msg_list) THEN
6519: l_return_status VARCHAR2(1);
6520:
6521: BEGIN
6522:
6523: -- IF (OZF_DEBUG_HIGH_ON) THEN OZF_Utility_PVT.debug_message(l_full_name || ': start refresh parties'); END IF;
6524:
6525: IF FND_API.to_boolean(p_init_msg_list) THEN
6526: FND_MSG_PUB.initialize;
6527: END IF;
6982:
6983:
6984: BEGIN
6985:
6986: -- IF (OZF_DEBUG_HIGH_ON) THEN OZF_Utility_PVT.debug_message(l_full_name || ': start refresh parties'); END IF;
6987:
6988: IF FND_API.to_boolean(p_init_msg_list) THEN
6989: FND_MSG_PUB.initialize;
6990: END IF;
7345:
7346:
7347: BEGIN
7348:
7349: -- IF (OZF_DEBUG_HIGH_ON) THEN OZF_Utility_PVT.debug_message(l_full_name || ': start refresh parties'); END IF;
7350:
7351: IF FND_API.to_boolean(p_init_msg_list) THEN
7352: FND_MSG_PUB.initialize;
7353: END IF;
7834:
7835:
7836: BEGIN
7837:
7838: -- IF (OZF_DEBUG_HIGH_ON) THEN OZF_Utility_PVT.debug_message(l_full_name || ': start get_list_price '); END IF;
7839:
7840: IF FND_API.to_boolean(p_init_msg_list) THEN
7841: FND_MSG_PUB.initialize;
7842: END IF;
7986:
7987: WHEN FND_API.G_EXC_ERROR THEN
7988: IF (OZF_DEBUG_HIGH_ON) THEN
7989:
7990: OZF_Utility_PVT.debug_message('Validate_get_list_price_of_goods: ' || substr(sqlerrm, 1, 100));
7991: END IF;
7992: x_return_status := FND_API.G_RET_STS_ERROR;
7993:
7994: FND_MSG_PUB.Count_And_Get (
8104: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
8105: THEN
8106: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
8107: END IF;
8108: --OZF_Utility_PVT.debug_message(' get_product_list_price : OTHER ERROR ' || sqlerrm );
8109: FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
8110: p_count => l_msg_count,
8111: p_data => l_msg_data);
8112: Return 33; -- i.e. list price is ZERO
8169: BEGIN
8170:
8171: SAVEPOINT calc_cost_of_goods;
8172:
8173: -- IF (OZF_DEBUG_HIGH_ON) THEN OZF_Utility_PVT.debug_message(l_full_name || ': start refresh parties'); END IF;
8174:
8175: IF FND_API.to_boolean(p_init_msg_list) THEN
8176: FND_MSG_PUB.initialize;
8177: END IF;
8244:
8245: EXCEPTION
8246:
8247: WHEN FND_API.G_EXC_ERROR THEN
8248: -- IF (OZF_DEBUG_HIGH_ON) THEN OZF_Utility_PVT.debug_message('Validate_Standard_Cost_Of_Goods: ' || substr(sqlerrm, 1, 100)); END IF;
8249: ROLLBACK TO calc_cost_of_goods;
8250: x_return_status := FND_API.G_RET_STS_ERROR;
8251: FND_MSG_PUB.Count_And_Get (
8252: p_encoded => FND_API.g_false,
8416: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
8417: THEN
8418: FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
8419: END IF;
8420: --OZF_Utility_PVT.debug_message(' get_product_cost : OTHER ERROR ' || sqlerrm );
8421: FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
8422: p_count => l_msg_count,
8423: p_data => l_msg_data);
8424: Return 100; -- i.e. cost is ZERO
8801:
8802: /*
8803: ,'VOLUME_OFFER'
8804: ,''
8805: ,OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE', a.offer_type)
8806: )
8807: */
8808:
8809: CURSOR c_offer_discounts IS
8810: SELECT TRIM(
8811: DECODE(a.offer_type
8812: ,'DEAL'
8813: ,DECODE(qpl.accrual_flag
8814: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8815: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8816: )
8817: ,'VOLUME_OFFER'
8818: ,DECODE(qpl.accrual_flag
8811: DECODE(a.offer_type
8812: ,'DEAL'
8813: ,DECODE(qpl.accrual_flag
8814: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8815: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8816: )
8817: ,'VOLUME_OFFER'
8818: ,DECODE(qpl.accrual_flag
8819: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8815: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8816: )
8817: ,'VOLUME_OFFER'
8818: ,DECODE(qpl.accrual_flag
8819: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8820: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8821: ) ||':'
8822: , '') ||
8823: ' ' || qpl.operand || ' ' ||
8816: )
8817: ,'VOLUME_OFFER'
8818: ,DECODE(qpl.accrual_flag
8819: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8820: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8821: ) ||':'
8822: , '') ||
8823: ' ' || qpl.operand || ' ' ||
8824: DECODE(qpl.arithmetic_operator,'%','%',' ')
8840: CURSOR c_obj_discounts IS
8841: SELECT DECODE(a.offer_type
8842: ,'DEAL'
8843: ,DECODE(qpl.accrual_flag
8844: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8845: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8846: )
8847: ,OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE', a.offer_type)
8848: ) ||
8841: SELECT DECODE(a.offer_type
8842: ,'DEAL'
8843: ,DECODE(qpl.accrual_flag
8844: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8845: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8846: )
8847: ,OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE', a.offer_type)
8848: ) ||
8849: ' ' ||
8843: ,DECODE(qpl.accrual_flag
8844: ,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8845: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8846: )
8847: ,OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE', a.offer_type)
8848: ) ||
8849: ' ' ||
8850: qpl.operand ||
8851: ' ' ||
8867: --R12 -- Volume Offer Discounts
8868: CURSOR c_volume_offer_discounts IS
8869: SELECT TRIM(
8870: DECODE(OFFR.VOLUME_OFFER_TYPE
8871: ,'ACCRUAL', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8872: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8873: )
8874: || ':'
8875: || ' '
8868: CURSOR c_volume_offer_discounts IS
8869: SELECT TRIM(
8870: DECODE(OFFR.VOLUME_OFFER_TYPE
8871: ,'ACCRUAL', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
8872: , OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
8873: )
8874: || ':'
8875: || ' '
8876: || MAX(DIS.DISCOUNT)
10520: <
10521:
10522: IF (OZF_DEBUG_HIGH_ON) THEN
10523:
10524: OZF_Utility_PVT.debug_message(l_full_name || ': End cascade baseline update');
10525:
10526: END IF;
10527:
10528: EXCEPTION
10597: l_return_status VARCHAR2(1);
10598:
10599: BEGIN
10600: IF (OZF_DEBUG_HIGH_ON) THEN
10601: OZF_Utility_PVT.debug_message(l_full_name || ': Start Cascade Baseline Levels');
10602: END IF;
10603:
10604: IF FND_API.to_boolean(p_init_msg_list) THEN
10605: FND_MSG_PUB.initialize;
10698: SET forecast_quantity = p_fcast_value
10699: WHERE forecast_id = p_fcast_id;
10700:
10701: IF (OZF_DEBUG_HIGH_ON) THEN
10702: OZF_Utility_PVT.debug_message(l_full_name || ': End Cascade Baseline Levels');
10703: END IF;
10704:
10705: EXCEPTION
10706: WHEN OTHERS THEN