[Home] [Help]
1676: p_root_fact_id IN NUMBER,
1677: p_node_id IN NUMBER ) IS
1678: BEGIN
1679: ----dbms_output.put_line( ' -- $$$$$$ create_fact-- ');
1680: INSERT INTO ozf_act_metric_facts_all (
1681: ACTIVITY_METRIC_FACT_ID , LAST_UPDATE_DATE ,
1682: LAST_UPDATED_BY , CREATION_DATE ,
1683: CREATED_BY , OBJECT_VERSION_NUMBER ,
1684: ACT_METRIC_USED_BY_ID , ARC_ACT_METRIC_USED_BY ,
1691: BASE_QUANTITY , ROOT_FACT_ID ,
1692: PREVIOUS_FACT_ID , FACT_TYPE ,
1693: FACT_REFERENCE , LAST_UPDATE_LOGIN,
1694: FORECAST_REMAINING_QUANTITY , NODE_ID)
1695: VALUES ( ozf_act_metric_facts_all_s.nextval , sysdate ,
1696: fnd_global.user_id , sysdate ,
1697: fnd_global.user_id , 1 ,
1698: p_forecast_id , 'FCST' ,
1699: 'NUMERIC' , p_activity_metric_id,
2185: CURSOR level_one_facts IS
2186: SELECT activity_metric_fact_id,
2187: fact_reference,
2188: fact_type
2189: FROM ozf_act_metric_facts_all
2190: WHERE arc_act_metric_used_by = 'FCST'
2191: AND act_metric_used_by_id = p_forecast_id
2192: AND previous_fact_id IS NULL
2193: AND root_fact_id IS NULL;
2195: CURSOR level_two_facts(p_previous_fact_id IN NUMBER) IS
2196: SELECT activity_metric_fact_id,
2197: fact_reference,
2198: fact_type
2199: FROM ozf_act_metric_facts_all
2200: WHERE arc_act_metric_used_by = 'FCST'
2201: AND act_metric_used_by_id = p_forecast_id
2202: AND previous_fact_id = p_previous_fact_id
2203: AND root_fact_id IS NULL;
2253:
2254: IF ( p_level = 'ONE' )
2255: THEN
2256:
2257: DELETE FROM ozf_act_metric_facts_all
2258: WHERE activity_metric_id = p_activity_metric_id ;
2259: --
2260:
2261: --R12 Baseline
2297:
2298: --
2299: -- Delete level two and three facts.
2300:
2301: DELETE FROM ozf_act_metric_facts_all
2302: WHERE activity_metric_id = p_activity_metric_id
2303: AND previous_fact_id IS NOT NULL;
2304:
2305: FOR i IN level_one_facts
3059: SELECT activity_metric_fact_id,
3060: fact_reference,
3061: fact_type,
3062: incremental_sales
3063: FROM ozf_act_metric_facts_all
3064: WHERE activity_metric_fact_id = p_activity_metric_fact_id;
3065:
3066: CURSOR level_two_facts IS
3067: SELECT activity_metric_fact_id,
3066: CURSOR level_two_facts IS
3067: SELECT activity_metric_fact_id,
3068: fact_reference,
3069: fact_type
3070: FROM ozf_act_metric_facts_all
3071: WHERE arc_act_metric_used_by = 'FCST'
3072: AND act_metric_used_by_id = p_forecast_id
3073: AND previous_fact_id = p_activity_metric_fact_id
3074: AND root_fact_id IS NULL;
3076: CURSOR level_three_facts(p_previous_fact_id IN NUMBER) IS
3077: SELECT activity_metric_fact_id,
3078: fact_reference,
3079: fact_type
3080: FROM ozf_act_metric_facts_all
3081: WHERE arc_act_metric_used_by = 'FCST'
3082: AND act_metric_used_by_id = p_forecast_id
3083: AND previous_fact_id = p_previous_fact_id
3084: AND root_fact_id = p_activity_metric_fact_id;
3304:
3305: -- Now, all three dimensions are available: P, T, M
3306: BEGIN
3307:
3308: UPDATE ozf_act_metric_facts_all outer
3309: SET
3310: outer.incremental_sales =
3311: (
3312: select
3388:
3389: --- At this point, all numbers will be there at the most granular i.e. at level3
3390:
3391: --- NOW, ROLLUP all numbers FOR LEVEL 2
3392: UPDATE ozf_act_metric_facts_all outer
3393: SET (outer.baseline_sales, outer.incremental_sales) =
3394: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
3395: FROM ozf_act_metric_facts_all inner
3396: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
3391: --- NOW, ROLLUP all numbers FOR LEVEL 2
3392: UPDATE ozf_act_metric_facts_all outer
3393: SET (outer.baseline_sales, outer.incremental_sales) =
3394: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
3395: FROM ozf_act_metric_facts_all inner
3396: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
3397: AND inner.arc_act_metric_used_by = 'FCST'
3398: AND inner.act_metric_used_by_id = p_forecast_id
3399: AND inner.fact_type = l_dimention3),
3407:
3408: IF p_obj_type = 'OFFR'
3409: THEN
3410: --- NOW, ROLLUP all numbers FOR LEVEL 1 (this is always 'PRODUCT') -- only for OFFR, not for WKST
3411: UPDATE ozf_act_metric_facts_all outer
3412: SET (outer.baseline_sales, outer.incremental_sales) =
3413: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
3414: FROM ozf_act_metric_facts_all inner
3415: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
3410: --- NOW, ROLLUP all numbers FOR LEVEL 1 (this is always 'PRODUCT') -- only for OFFR, not for WKST
3411: UPDATE ozf_act_metric_facts_all outer
3412: SET (outer.baseline_sales, outer.incremental_sales) =
3413: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
3414: FROM ozf_act_metric_facts_all inner
3415: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
3416: AND inner.arc_act_metric_used_by = 'FCST'
3417: AND inner.act_metric_used_by_id = p_forecast_id
3418: AND inner.fact_type = l_dimention2),
3425: AND outer.activity_metric_fact_id = p_activity_metric_fact_id;
3426:
3427: ---- NOW, ROLLUP all numbers FOR FORECAST Header LEVEL
3428: SELECT NVL(inner.incremental_sales,0) INTO l_new_incremental_sales
3429: FROM ozf_act_metric_facts_all inner
3430: WHERE inner.arc_act_metric_used_by = 'FCST'
3431: AND inner.act_metric_used_by_id = p_forecast_id
3432: AND inner.activity_metric_fact_id = p_activity_metric_fact_id;
3433:
3438: ELSIF p_obj_type = 'WKST'
3439: THEN
3440:
3441: SELECT NVL(SUM(inner.incremental_sales), 0) INTO l_new_incremental_sales
3442: FROM ozf_act_metric_facts_all inner
3443: WHERE inner.previous_fact_id = p_activity_metric_fact_id
3444: AND inner.arc_act_metric_used_by = 'FCST'
3445: AND inner.act_metric_used_by_id = p_forecast_id
3446: AND inner.fact_type = l_dimention2;
3561: CURSOR level_one_facts IS
3562: SELECT activity_metric_fact_id,
3563: fact_reference,
3564: fact_type
3565: FROM ozf_act_metric_facts_all
3566: WHERE arc_act_metric_used_by = 'FCST'
3567: AND act_metric_used_by_id = p_forecast_id
3568: AND previous_fact_id IS NULL
3569: AND root_fact_id IS NULL;
3571: CURSOR level_two_facts(p_previous_fact_id IN NUMBER) IS
3572: SELECT activity_metric_fact_id,
3573: fact_reference,
3574: fact_type
3575: FROM ozf_act_metric_facts_all
3576: WHERE arc_act_metric_used_by = 'FCST'
3577: AND act_metric_used_by_id = p_forecast_id
3578: AND previous_fact_id = p_previous_fact_id
3579: AND root_fact_id IS NULL;
3581: CURSOR level_three_facts(p_root_fact_id IN NUMBER, p_previous_fact_id IN NUMBER) IS
3582: SELECT activity_metric_fact_id,
3583: fact_reference,
3584: fact_type
3585: FROM ozf_act_metric_facts_all
3586: WHERE arc_act_metric_used_by = 'FCST'
3587: AND act_metric_used_by_id = p_forecast_id
3588: AND previous_fact_id = p_previous_fact_id
3589: AND root_fact_id = p_root_fact_id;
3912: ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3913: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3914: END IF;
3915:
3916: UPDATE ozf_act_metric_facts_all prod_fact
3917: SET tpr_percent = NVL(l_tpr_percent,0)
3918: WHERE prod_fact.activity_metric_fact_id = i.activity_metric_fact_id
3919: AND prod_fact.arc_act_metric_used_by = 'FCST'
3920: AND prod_fact.act_metric_used_by_id = p_forecast_id;
3977:
3978: -- Now, all three dimensions are available: P, T, M
3979: BEGIN
3980:
3981: UPDATE ozf_act_metric_facts_all outer
3982: SET (outer.baseline_sales, outer.incremental_sales) =
3983: (
3984: select
3985: ROUND(NVL (SUM(sales.baseline_sales) ,0) ) baseline_sales,
4053: --dbms_output.put_line( ' ~~~~ ERROR in Update Baseline Sales for ABOVE record -- ');
4054: END;
4055:
4056:
4057: UPDATE ozf_act_metric_facts_all outer2
4058: SET outer2.baseline_sales=0
4059: WHERE outer2.activity_metric_fact_id = k.activity_metric_fact_id
4060: AND outer2.arc_act_metric_used_by = 'FCST'
4061: AND outer2.act_metric_used_by_id = p_forecast_id
4060: AND outer2.arc_act_metric_used_by = 'FCST'
4061: AND outer2.act_metric_used_by_id = p_forecast_id
4062: AND outer2.baseline_sales IS NULL;
4063:
4064: UPDATE ozf_act_metric_facts_all outer3
4065: SET outer3.incremental_sales=0
4066: WHERE outer3.activity_metric_fact_id = k.activity_metric_fact_id
4067: AND outer3.arc_act_metric_used_by = 'FCST'
4068: AND outer3.act_metric_used_by_id = p_forecast_id
4077:
4078:
4079: --- At this point, all numbers will be there at the most granular i.e. at level3
4080: --- NOW, ROLLUP all numbers FOR LEVEL 2
4081: UPDATE ozf_act_metric_facts_all outer
4082: SET (outer.baseline_sales, outer.incremental_sales) =
4083: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
4084: FROM ozf_act_metric_facts_all inner
4085: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
4080: --- NOW, ROLLUP all numbers FOR LEVEL 2
4081: UPDATE ozf_act_metric_facts_all outer
4082: SET (outer.baseline_sales, outer.incremental_sales) =
4083: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
4084: FROM ozf_act_metric_facts_all inner
4085: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
4086: AND inner.arc_act_metric_used_by = 'FCST'
4087: AND inner.act_metric_used_by_id = p_forecast_id
4088: AND inner.fact_type = l_dimention3),
4093: AND outer.fact_type = l_dimention2;
4094:
4095:
4096: --- NOW, ROLLUP all numbers FOR LEVEL 1 (this is always 'PRODUCT')
4097: UPDATE ozf_act_metric_facts_all outer
4098: SET (outer.baseline_sales, outer.incremental_sales) =
4099: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
4100: FROM ozf_act_metric_facts_all inner
4101: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
4096: --- NOW, ROLLUP all numbers FOR LEVEL 1 (this is always 'PRODUCT')
4097: UPDATE ozf_act_metric_facts_all outer
4098: SET (outer.baseline_sales, outer.incremental_sales) =
4099: ( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
4100: FROM ozf_act_metric_facts_all inner
4101: WHERE inner.previous_fact_id = outer.activity_metric_fact_id
4102: AND inner.arc_act_metric_used_by = 'FCST'
4103: AND inner.act_metric_used_by_id = p_forecast_id
4104: AND inner.fact_type = l_dimention2),
4115: UPDATE ozf_act_forecasts_all outer
4116: SET (outer.forecast_quantity, outer.base_quantity) =
4117: ( SELECT (NVL(SUM(inner.baseline_sales),0) + NVL(SUM(inner.incremental_sales),0)) total_forecast,
4118: NVL(SUM(inner.baseline_sales),0) baseline_sales
4119: FROM ozf_act_metric_facts_all inner
4120: WHERE inner.arc_act_metric_used_by = 'FCST'
4121: AND inner.act_metric_used_by_id = p_forecast_id
4122: AND inner.fact_type = l_dimention1),
4123: outer.dimention1 = l_dimention1,
4505:
4506:
4507: UPDATE ozf_act_forecasts_all
4508: SET base_quantity = ( SELECT NVL(SUM(base_quantity),0)
4509: FROM ozf_act_metric_facts_all
4510: WHERE arc_act_metric_used_by = 'FCST'
4511: AND act_metric_used_by_id = l_forecast_id
4512: AND fact_type = 'PRODUCT') ,
4513: dimention1 = 'PRODUCT',
5374: l_three_f_quan NUMBER := 0;
5375:
5376: CURSOR C1(p_fcast_id IN NUMBER) IS
5377: SELECT activity_metric_fact_id, fact_value
5378: FROM ozf_act_metric_facts_all
5379: WHERE arc_act_metric_used_by = 'FCST'
5380: AND act_metric_used_by_id = p_fcast_id
5381: AND previous_fact_id IS NULL
5382: AND root_fact_id IS NULL
5383: and nvl(node_id,1) <> 3 ;
5384:
5385: CURSOR C2(prev_fact_id IN NUMBER, p_used_by_id IN NUMBER) IS
5386: SELECT activity_metric_fact_id, fact_value
5387: FROM ozf_act_metric_facts_all
5388: WHERE arc_act_metric_used_by = 'FCST'
5389: AND act_metric_used_by_id = p_used_by_id
5390: AND root_fact_id IS NULL
5391: AND previous_fact_id = prev_fact_id
5393:
5394:
5395: CURSOR C3(prev_fact_id IN NUMBER, p_used_by_id IN NUMBER) IS
5396: SELECT activity_metric_fact_id, fact_value
5397: FROM ozf_act_metric_facts_all
5398: WHERE arc_act_metric_used_by = 'FCST'
5399: AND act_metric_used_by_id = p_used_by_id
5400: AND root_fact_id IS NOT NULL
5401: AND previous_fact_id = prev_fact_id
5442:
5443: END LOOP;
5444:
5445: IF(l_three_f_quan <> 0) THEN
5446: UPDATE ozf_act_metric_facts_all
5447: SET FORECAST_REMAINING_QUANTITY = record_l_two.fact_value - l_three_f_quan
5448: WHERE activity_metric_fact_id = record_l_two.activity_metric_fact_id;
5449:
5450: END IF;
5453:
5454: END LOOP;
5455:
5456: IF(l_two_f_quan <> 0) THEN
5457: UPDATE ozf_act_metric_facts_all
5458: SET FORECAST_REMAINING_QUANTITY = record_l_one.fact_value - l_two_f_quan
5459: WHERE activity_metric_fact_id = record_l_one.activity_metric_fact_id;
5460:
5461: END IF;
5511: l_three_f_quan NUMBER := 0;
5512:
5513: CURSOR C1(p_fcast_id IN NUMBER) IS
5514: SELECT activity_metric_fact_id, incremental_sales
5515: FROM ozf_act_metric_facts_all
5516: WHERE arc_act_metric_used_by = 'FCST'
5517: AND act_metric_used_by_id = p_fcast_id
5518: AND previous_fact_id IS NULL
5519: AND root_fact_id IS NULL
5520: and nvl(node_id,1) <> 3 ;
5521:
5522: CURSOR C2(prev_fact_id IN NUMBER, p_used_by_id IN NUMBER) IS
5523: SELECT activity_metric_fact_id, incremental_sales
5524: FROM ozf_act_metric_facts_all
5525: WHERE arc_act_metric_used_by = 'FCST'
5526: AND act_metric_used_by_id = p_used_by_id
5527: AND root_fact_id IS NULL
5528: AND previous_fact_id = prev_fact_id
5530:
5531:
5532: CURSOR C3(prev_fact_id IN NUMBER, p_used_by_id IN NUMBER) IS
5533: SELECT activity_metric_fact_id, incremental_sales
5534: FROM ozf_act_metric_facts_all
5535: WHERE arc_act_metric_used_by = 'FCST'
5536: AND act_metric_used_by_id = p_used_by_id
5537: AND root_fact_id IS NOT NULL
5538: AND previous_fact_id = prev_fact_id
5578:
5579: END LOOP;
5580:
5581: IF(l_three_f_quan <> 0) THEN
5582: UPDATE ozf_act_metric_facts_all
5583: SET FORECAST_REMAINING_QUANTITY = record_l_two.incremental_sales - l_three_f_quan
5584: WHERE activity_metric_fact_id = record_l_two.activity_metric_fact_id;
5585:
5586: END IF;
5589:
5590: END LOOP;
5591:
5592: IF(l_two_f_quan <> 0) THEN
5593: UPDATE ozf_act_metric_facts_all
5594: SET FORECAST_REMAINING_QUANTITY = record_l_one.incremental_sales - l_two_f_quan
5595: WHERE activity_metric_fact_id = record_l_one.activity_metric_fact_id;
5596:
5597: END IF;
5648: WHERE forecast_id = p_fcast_id;
5649:
5650: CURSOR C_LevelOneRecords(p_fcast_id IN NUMBER) IS
5651: SELECT activity_metric_fact_id, fact_type, forecast_remaining_quantity
5652: FROM ozf_act_metric_facts_all
5653: WHERE arc_act_metric_used_by = 'FCST'
5654: AND act_metric_used_by_id = p_fcast_id
5655: AND previous_fact_id IS NULL
5656: AND root_fact_id IS NULL;
5656: AND root_fact_id IS NULL;
5657:
5658: CURSOR C_LevelTwoRecords(p_fcast_id IN NUMBER, p_prev_id IN NUMBER) IS
5659: SELECT activity_metric_fact_id, fact_type, forecast_remaining_quantity
5660: FROM ozf_act_metric_facts_all
5661: WHERE arc_act_metric_used_by = 'FCST'
5662: AND act_metric_used_by_id = p_fcast_id
5663: AND previous_fact_id IS NOT NULL
5664: AND root_fact_id IS NULL
5835: root_fact_id,
5836: forecast_remaining_quantity,
5837: forward_buy_quantity,
5838: node_id
5839: FROM ozf_act_metric_facts_all
5840: WHERE arc_act_metric_used_by = 'FCST'
5841: AND act_metric_used_by_id = p_fcast_id
5842: AND activity_metric_id = p_activity_metric_id
5843: AND root_fact_id IS NULL
5856: root_fact_id,
5857: forecast_remaining_quantity,
5858: forward_buy_quantity,
5859: node_id
5860: FROM ozf_act_metric_facts_all
5861: WHERE arc_act_metric_used_by = 'FCST'
5862: AND act_metric_used_by_id = p_fcast_id
5863: AND activity_metric_id = p_activity_metric_id
5864: AND root_fact_id IS NULL
5879: root_fact_id,
5880: forecast_remaining_quantity,
5881: forward_buy_quantity,
5882: node_id
5883: FROM ozf_act_metric_facts_all
5884: WHERE arc_act_metric_used_by = 'FCST'
5885: AND act_metric_used_by_id = p_fcast_id
5886: AND activity_metric_id = p_activity_metric_id
5887: AND previous_fact_id = p_previous_fact_id
6172: FROM ozf_act_metrics_all b
6173: WHERE act_metric_used_by_id = p_forecast_id
6174: AND arc_act_metric_used_by = 'FCST';
6175:
6176: -- Insert new row in the ozf_act_metric_facts_all table for
6177: -- each existing row in ozf_act_metric_facts_all (based on a given act_metric_used_by_id and activity_metric_id)
6178:
6179: OPEN c_get_activity_metric_id(p_forecast_id);
6180: FETCH c_get_activity_metric_id INTO l_previous_activity_metric_id;
6173: WHERE act_metric_used_by_id = p_forecast_id
6174: AND arc_act_metric_used_by = 'FCST';
6175:
6176: -- Insert new row in the ozf_act_metric_facts_all table for
6177: -- each existing row in ozf_act_metric_facts_all (based on a given act_metric_used_by_id and activity_metric_id)
6178:
6179: OPEN c_get_activity_metric_id(p_forecast_id);
6180: FETCH c_get_activity_metric_id INTO l_previous_activity_metric_id;
6181: CLOSE c_get_activity_metric_id;
6186: l_previous_activity_metric_id
6187: )
6188: LOOP
6189: -- generating activity_metric_fact_id for each record in Level One with the sequence
6190: --l_act_metric_fact_id_level_1 := ozf_act_metric_facts_all_s.nextval;
6191: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_1 FROM dual;
6192:
6193: INSERT INTO ozf_act_metric_facts_all (
6194: ACTIVITY_METRIC_FACT_ID ,
6187: )
6188: LOOP
6189: -- generating activity_metric_fact_id for each record in Level One with the sequence
6190: --l_act_metric_fact_id_level_1 := ozf_act_metric_facts_all_s.nextval;
6191: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_1 FROM dual;
6192:
6193: INSERT INTO ozf_act_metric_facts_all (
6194: ACTIVITY_METRIC_FACT_ID ,
6195: LAST_UPDATE_DATE ,
6189: -- generating activity_metric_fact_id for each record in Level One with the sequence
6190: --l_act_metric_fact_id_level_1 := ozf_act_metric_facts_all_s.nextval;
6191: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_1 FROM dual;
6192:
6193: INSERT INTO ozf_act_metric_facts_all (
6194: ACTIVITY_METRIC_FACT_ID ,
6195: LAST_UPDATE_DATE ,
6196: LAST_UPDATED_BY,
6197: CREATION_DATE,
6261: )
6262: LOOP
6263:
6264: -- generating activity_metric_fact_id for each record in Level Two with the sequence
6265: --l_act_metric_fact_id_level_2 := ozf_act_metric_facts_all_s.nextval;
6266: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_2 FROM dual;
6267:
6268: INSERT INTO ozf_act_metric_facts_all (
6269: ACTIVITY_METRIC_FACT_ID ,
6262: LOOP
6263:
6264: -- generating activity_metric_fact_id for each record in Level Two with the sequence
6265: --l_act_metric_fact_id_level_2 := ozf_act_metric_facts_all_s.nextval;
6266: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_2 FROM dual;
6267:
6268: INSERT INTO ozf_act_metric_facts_all (
6269: ACTIVITY_METRIC_FACT_ID ,
6270: LAST_UPDATE_DATE ,
6264: -- generating activity_metric_fact_id for each record in Level Two with the sequence
6265: --l_act_metric_fact_id_level_2 := ozf_act_metric_facts_all_s.nextval;
6266: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_2 FROM dual;
6267:
6268: INSERT INTO ozf_act_metric_facts_all (
6269: ACTIVITY_METRIC_FACT_ID ,
6270: LAST_UPDATE_DATE ,
6271: LAST_UPDATED_BY,
6272: CREATION_DATE,
6338: )
6339: LOOP
6340:
6341: -- generating activity_metric_fact_id for each new record in Level Three with the sequence
6342: --l_act_metric_fact_id_level_3 := ozf_act_metric_facts_all_s.nextval;
6343: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_3 FROM dual;
6344:
6345: INSERT INTO ozf_act_metric_facts_all (
6346: ACTIVITY_METRIC_FACT_ID ,
6339: LOOP
6340:
6341: -- generating activity_metric_fact_id for each new record in Level Three with the sequence
6342: --l_act_metric_fact_id_level_3 := ozf_act_metric_facts_all_s.nextval;
6343: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_3 FROM dual;
6344:
6345: INSERT INTO ozf_act_metric_facts_all (
6346: ACTIVITY_METRIC_FACT_ID ,
6347: LAST_UPDATE_DATE ,
6341: -- generating activity_metric_fact_id for each new record in Level Three with the sequence
6342: --l_act_metric_fact_id_level_3 := ozf_act_metric_facts_all_s.nextval;
6343: SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_3 FROM dual;
6344:
6345: INSERT INTO ozf_act_metric_facts_all (
6346: ACTIVITY_METRIC_FACT_ID ,
6347: LAST_UPDATE_DATE ,
6348: LAST_UPDATED_BY,
6349: CREATION_DATE,
6486: fact_value,
6487: fact_percent,
6488: root_fact_id,
6489: forward_buy_quantity
6490: FROM ozf_act_metric_facts_all
6491: WHERE arc_act_metric_used_by = 'FCST'
6492: AND act_metric_used_by_id = p_fcast_id
6493: AND previous_fact_id = p_prev_id
6494: order by 6; -- added this on 04/09/02 for Forward Buy calculations for TIME
6505: fact_value,
6506: fact_percent,
6507: root_fact_id,
6508: forward_buy_quantity
6509: FROM ozf_act_metric_facts_all
6510: WHERE arc_act_metric_used_by = 'FCST'
6511: AND act_metric_used_by_id = p_fcast_id
6512: AND previous_fact_id = p_prev_id
6513: AND root_fact_id IS NOT NULL
6554:
6555: -- Get the fact value and forward buy value for the parent
6556: SELECT fact_value, forward_buy_quantity
6557: INTO l_parent_fact_value, l_parent_fwd_buy_qty
6558: FROM ozf_act_metric_facts_all
6559: WHERE arc_act_metric_used_by = 'FCST'
6560: AND act_metric_used_by_id = p_fcast_id
6561: AND activity_metric_fact_id = p_id;
6562:
6587:
6588: IF (l_temp_previous_fact_id <> facts_record.previous_fact_id) THEN
6589: SELECT count(*), sum(forward_buy_quantity)
6590: INTO l_temp_count, l_fwd_buy_sum_all_recs
6591: FROM ozf_act_metric_facts_all
6592: WHERE arc_act_metric_used_by = 'FCST'
6593: AND act_metric_used_by_id = p_fcast_id
6594: AND previous_fact_id = facts_record.previous_fact_id;
6595:
6593: AND act_metric_used_by_id = p_fcast_id
6594: AND previous_fact_id = facts_record.previous_fact_id;
6595:
6596: SELECT forecast_remaining_quantity INTO l_fcst_remaining_quantity
6597: FROM ozf_act_metric_facts_all
6598: WHERE arc_act_metric_used_by = 'FCST'
6599: AND act_metric_used_by_id = p_fcast_id
6600: AND activity_metric_fact_id = facts_record.previous_fact_id;
6601:
6675: -- this will also work for the last record if the RemainingFcstQty of its parent <> 0
6676:
6677: ----dbms_output.put_line('Update 1st : factVal = ' || l_fact_value || ' Perc ' || round(l_fact_percent,4) ||' fwd ' || round(l_forward_buy_quantity));
6678:
6679: UPDATE ozf_act_metric_facts_all
6680: SET fact_value = l_fact_value,
6681: fact_percent = round(l_fact_percent,4),
6682: forward_buy_quantity = round(l_forward_buy_quantity)
6683: WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
6696:
6697: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
6698: SELECT NVL(sum(fact_value),0), NVL(sum(forward_buy_quantity),0)
6699: INTO l_fval_sum_minus_last_rec, l_fwdbuy_sum_minus_last_rec
6700: FROM ozf_act_metric_facts_all
6701: WHERE arc_act_metric_used_by = 'FCST'
6702: AND act_metric_used_by_id = p_fcast_id
6703: AND previous_fact_id = facts_record.previous_fact_id
6704: AND activity_metric_fact_id <> facts_record.activity_metric_fact_id ;
6731: END IF;
6732:
6733: ----dbms_output.put_line('Update: factVal = ' || l_fact_value || ' Perc ' || round(l_fact_percent,4) || ' fwd ' || round(l_forward_buy_quantity));
6734:
6735: UPDATE ozf_act_metric_facts_all
6736: SET fact_value = l_fact_value,
6737: fact_percent = round(l_fact_percent,4),
6738: forward_buy_quantity = l_forward_buy_quantity
6739: WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
6748:
6749: IF (l_temp_sub_previous_fact_id <> facts_subrecord.previous_fact_id) THEN
6750: SELECT count(*), sum(forward_buy_quantity)
6751: INTO l_temp_sub_count, l_fwd_buy_sum_all_sub_recs
6752: FROM ozf_act_metric_facts_all
6753: WHERE arc_act_metric_used_by = 'FCST'
6754: AND act_metric_used_by_id = p_fcast_id
6755: AND previous_fact_id = facts_subrecord.previous_fact_id
6756: AND root_fact_id IS NOT NULL;
6833: -- this will also work for the last record if the RemainingFcstQty of its parent <> 0
6834:
6835: ----dbms_output.put_line('Update: factVal = ' || l_fact_value || ' Perc ' || round(l_fact_percent,4)|| ' fwd ' || round(l_forward_buy_quantity));
6836:
6837: UPDATE ozf_act_metric_facts_all
6838: SET fact_value = l_fact_value,
6839: fact_percent = round(l_fact_percent,4),
6840: forward_buy_quantity = round(l_forward_buy_quantity)
6841: WHERE activity_metric_fact_id = facts_subrecord.activity_metric_fact_id;
6849:
6850: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
6851: SELECT NVL(sum(fact_value),0), NVL(sum(forward_buy_quantity),0)
6852: INTO l_fval_sum_minus_last_rec, l_fwdbuy_sum_minus_last_rec
6853: FROM ozf_act_metric_facts_all
6854: WHERE arc_act_metric_used_by = 'FCST'
6855: AND act_metric_used_by_id = p_fcast_id
6856: AND previous_fact_id = facts_subrecord.previous_fact_id
6857: AND root_fact_id IS NOT NULL
6885: END IF;
6886:
6887: ----dbms_output.put_line('Update: factVal = ' || l_fact_value || ' Perc ' || round(l_fact_percent,4) || ' fwd ' || round(l_forward_buy_quantity));
6888:
6889: UPDATE ozf_act_metric_facts_all
6890: SET fact_value = l_fact_value,
6891: fact_percent = round(l_fact_percent,4),
6892: forward_buy_quantity = l_forward_buy_quantity
6893: WHERE activity_metric_fact_id = facts_subrecord.activity_metric_fact_id;
6967: fact_value,
6968: fact_percent,
6969: root_fact_id,
6970: forward_buy_quantity
6971: FROM ozf_act_metric_facts_all
6972: WHERE arc_act_metric_used_by = 'FCST'
6973: AND act_metric_used_by_id = p_forecast_id
6974: AND previous_fact_id IS NULL
6975: AND root_fact_id IS NULL
7029: --------------------------------
7030:
7031: SELECT count(*), sum(forward_buy_quantity)
7032: INTO l_temp_count, l_fwd_buy_sum_all_recs
7033: FROM ozf_act_metric_facts_all
7034: WHERE arc_act_metric_used_by = 'FCST'
7035: AND act_metric_used_by_id = p_fcast_id
7036: AND previous_fact_id IS NULL
7037: AND root_fact_id IS NULL;
7097: END IF;
7098:
7099: END IF;
7100:
7101: UPDATE ozf_act_metric_facts_all
7102: SET fact_value = l_fact_value,
7103: fact_percent = round(l_fact_percent,4),
7104: forward_buy_quantity = round(l_forward_buy_quantity)
7105: WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
7125:
7126: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
7127: SELECT NVL(sum(fact_value),0), NVL(sum(forward_buy_quantity),0)
7128: INTO l_fval_sum_minus_last_rec, l_fwdbuy_sum_minus_last_rec
7129: FROM ozf_act_metric_facts_all
7130: WHERE arc_act_metric_used_by = 'FCST'
7131: AND act_metric_used_by_id = p_fcast_id
7132: AND previous_fact_id IS NULL
7133: AND root_fact_id IS NULL
7160: l_forward_buy_quantity := round(p_fwd_buy_value - l_fwdbuy_sum_minus_last_rec);
7161: END IF;
7162:
7163:
7164: UPDATE ozf_act_metric_facts_all
7165: SET fact_value = l_fact_value,
7166: fact_percent = round(l_fact_percent,4),
7167: forward_buy_quantity = l_forward_buy_quantity
7168: WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
7284: a.root_fact_id,
7285: b.base_quantity overall_base_quantity,
7286: b.forecast_quantity overall_forecast_quantity,
7287: b.forward_buy_quantity overall_fwd_buy_qty
7288: FROM ozf_act_metric_facts_all a, ozf_act_forecasts_all b
7289: WHERE a.arc_act_metric_used_by = 'FCST'
7290: AND a.act_metric_used_by_id = p_used_by_id
7291: AND a.previous_fact_id IS NULL
7292: AND b.forecast_id = a.act_metric_used_by_id
7306: f.fact_value,
7307: f.fact_percent,
7308: f.root_fact_id,
7309: f1.forward_buy_quantity level_one_fwd_buy_qty
7310: FROM ozf_act_metric_facts_all f, ozf_act_metric_facts_all f1
7311: WHERE f.arc_act_metric_used_by = 'FCST'
7312: AND f.act_metric_used_by_id = p_used_by_id
7313: AND f.previous_fact_id IS NOT NULL
7314: AND f.root_fact_id IS NULL
7329: f.fact_value,
7330: f.fact_percent,
7331: f.root_fact_id,
7332: f1.forward_buy_quantity level_two_fwd_buy_qty
7333: FROM ozf_act_metric_facts_all f, ozf_act_metric_facts_all f1
7334: WHERE f.arc_act_metric_used_by = 'FCST'
7335: AND f.act_metric_used_by_id = p_used_by_id
7336: AND f.root_fact_id IS NOT NULL
7337: AND f.previous_fact_id = f1.activity_metric_fact_id
7369: IF (l_level_num = 1) THEN
7370:
7371: SELECT count(*)
7372: INTO l_temp_count
7373: FROM ozf_act_metric_facts_all
7374: WHERE arc_act_metric_used_by = 'FCST'
7375: AND act_metric_used_by_id = p_used_by_id
7376: AND previous_fact_id IS NULL
7377: AND root_fact_id IS NULL
7424: l_fact_value := round(l_temp_fact_value);
7425: ELSIF (l_temp_counter = l_temp_count) THEN
7426: SELECT NVL(sum(fact_value),0)
7427: INTO l_fval_sum_minus_last_rec
7428: FROM ozf_act_metric_facts_all
7429: WHERE arc_act_metric_used_by = 'FCST'
7430: AND act_metric_used_by_id = p_used_by_id
7431: AND previous_fact_id IS NULL
7432: AND root_fact_id IS NULL
7454: ELSIF ( (rec.level_one_fact_type <> 'TIME') AND (l_temp_counter = l_temp_count) ) THEN
7455: -- So, calculate the last record's fact value such that the total sum of fact values = Total Fcast Qty.
7456: SELECT NVL(sum(forward_buy_quantity),0)
7457: INTO l_fwdbuy_sum_minus_last_rec
7458: FROM ozf_act_metric_facts_all
7459: WHERE arc_act_metric_used_by = 'FCST'
7460: AND act_metric_used_by_id = p_used_by_id
7461: AND previous_fact_id IS NULL
7462: AND root_fact_id IS NULL
7465:
7466: l_forward_buy_quantity := round(rec.overall_fwd_buy_qty - l_fwdbuy_sum_minus_last_rec);
7467: END IF;
7468:
7469: UPDATE ozf_act_metric_facts_all
7470: SET fact_value = l_fact_value,
7471: fact_percent = round(l_fact_percent,4),
7472: forward_buy_quantity = l_forward_buy_quantity
7473: WHERE activity_metric_fact_id = rec.activity_metric_fact_id;
7492: l_counter := 1;
7493:
7494:
7495: SELECT count(*) INTO l_temp_count
7496: FROM ozf_act_metric_facts_all
7497: WHERE arc_act_metric_used_by = 'FCST'
7498: AND act_metric_used_by_id = p_used_by_id
7499: AND previous_fact_id = rec.previous_fact_id
7500: AND root_fact_id IS NULL
7549: ELSIF (l_counter = l_temp_count) THEN
7550: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
7551: SELECT NVL(sum(fact_value),0)
7552: INTO l_fval_sum_minus_last_rec
7553: FROM ozf_act_metric_facts_all
7554: WHERE arc_act_metric_used_by = 'FCST'
7555: AND act_metric_used_by_id = p_used_by_id
7556: AND previous_fact_id = rec.previous_fact_id
7557: AND root_fact_id IS NULL
7578: ELSIF ( (rec.level_two_fact_type <> 'TIME') AND (l_counter = l_temp_count) ) THEN
7579: -- So, calculate the last record's fact value such that the total sum of fact values = Total Fcast Qty.
7580: SELECT NVL(sum(forward_buy_quantity),0)
7581: INTO l_fwdbuy_sum_minus_last_rec
7582: FROM ozf_act_metric_facts_all
7583: WHERE arc_act_metric_used_by = 'FCST'
7584: AND act_metric_used_by_id = p_used_by_id
7585: AND previous_fact_id = rec.previous_fact_id
7586: AND root_fact_id IS NULL
7589:
7590: l_forward_buy_quantity := round(rec.level_one_fwd_buy_qty - l_fwdbuy_sum_minus_last_rec);
7591: END IF;
7592:
7593: UPDATE ozf_act_metric_facts_all
7594: SET fact_value = l_fact_value,
7595: fact_percent = round(l_fact_percent,4),
7596: forward_buy_quantity = l_forward_buy_quantity
7597: WHERE activity_metric_fact_id = rec.level_two_fact_id
7615: l_counter := 1;
7616:
7617:
7618: SELECT count(*) INTO l_temp_count
7619: FROM ozf_act_metric_facts_all
7620: WHERE arc_act_metric_used_by = 'FCST'
7621: AND act_metric_used_by_id = p_used_by_id
7622: AND previous_fact_id = rec.previous_fact_id
7623: AND root_fact_id IS NOT NULL
7672: ELSIF (l_counter = l_temp_count) THEN
7673: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
7674: SELECT NVL(sum(fact_value),0)
7675: INTO l_fval_sum_minus_last_rec
7676: FROM ozf_act_metric_facts_all
7677: WHERE arc_act_metric_used_by = 'FCST'
7678: AND act_metric_used_by_id = p_used_by_id
7679: AND previous_fact_id = rec.previous_fact_id
7680: AND root_fact_id IS NOT NULL
7701: ELSIF ( (rec.level_three_fact_type <> 'TIME') AND (l_counter = l_temp_count) ) THEN
7702: -- So, calculate the last record's fact value such that the total sum of fact values = Total Fcast Qty.
7703: SELECT NVL(sum(forward_buy_quantity),0)
7704: INTO l_fwdbuy_sum_minus_last_rec
7705: FROM ozf_act_metric_facts_all
7706: WHERE arc_act_metric_used_by = 'FCST'
7707: AND act_metric_used_by_id = p_used_by_id
7708: AND previous_fact_id = rec.previous_fact_id
7709: AND root_fact_id IS NOT NULL
7712:
7713: l_forward_buy_quantity := round(rec.level_two_fwd_buy_qty - l_fwdbuy_sum_minus_last_rec);
7714: END IF;
7715:
7716: UPDATE ozf_act_metric_facts_all
7717: SET fact_value = l_fact_value,
7718: fact_percent = round(l_fact_percent,4),
7719: forward_buy_quantity = l_forward_buy_quantity
7720: WHERE activity_metric_fact_id = rec.level_three_fact_id;
8066: l_currency_code ,
8067: l_price_list_id ,
8068: l_forecast_id
8069: FROM
8070: ozf_act_metric_facts_all fact,
8071: OZF_ACT_FORECASTS_ALL fcst,
8072: ozf_forecast_dimentions dim,
8073: ozf_worksheet_headers_b wkst
8074: WHERE
8365: l_fcst_uom ,
8366: l_currency_code ,
8367: l_price_list_id
8368: FROM
8369: ozf_act_metric_facts_all fact,
8370: OZF_ACT_FORECASTS_ALL fcst,
8371: ozf_forecast_dimentions dim,
8372: ozf_worksheet_headers_b wkst
8373: WHERE
9435: = a.qp_list_header_id(+);
9436:
9437: CURSOR c2 IS
9438: SELECT distinct count(act_metric_used_by_id)
9439: FROM ozf_act_metric_facts_all
9440: WHERE arc_act_metric_used_by = 'FCST'
9441: AND act_metric_used_by_id = p_used_by_id
9442: AND fact_type = p_dimention
9443: GROUP by previous_fact_id;
9496: FETCH c2 INTO l_dimention_count;
9497: CLOSE c2;
9498: END IF;
9499:
9500: UPDATE ozf_act_metric_facts_all f
9501: SET (fact_value, fact_percent, forward_buy_quantity ) =
9502: ( SELECT ROUND( a.fact_value ) ,
9503: ROUND( a.fact_value * 100 / a.total_forecast_quantity ) fact_percent,
9504: ROUND( (a.fact_value * 100 / a.total_forecast_quantity)
9513: )
9514:
9515: , ( NVL(previous_fact.fact_value, fcst.forecast_quantity)/l_dimention_count
9516: /*( SELECT COUNT(f1.act_metric_used_by_id)
9517: FROM ozf_act_metric_facts_all f1
9518: WHERE f1.act_metric_used_by_id = fact.act_metric_used_by_id
9519: AND f1.arc_act_metric_used_by = 'FCST'
9520: AND f1.fact_type= p_dimention
9521: AND NVL(f1.previous_fact_id,-99) =
9530: ) total_forecast_quantity,
9531: NVL(previous_fact.forward_buy_quantity, fcst.forward_buy_quantity) total_forward_buy_quantity
9532: FROM
9533: ozf_act_forecasts_all fcst,
9534: ozf_act_metric_facts_all fact,
9535: ozf_act_metric_facts_all previous_fact
9536: WHERE
9537: fact.act_metric_used_by_id = fcst.forecast_id
9538: AND fact.previous_fact_id = previous_fact.activity_metric_fact_id(+)
9531: NVL(previous_fact.forward_buy_quantity, fcst.forward_buy_quantity) total_forward_buy_quantity
9532: FROM
9533: ozf_act_forecasts_all fcst,
9534: ozf_act_metric_facts_all fact,
9535: ozf_act_metric_facts_all previous_fact
9536: WHERE
9537: fact.act_metric_used_by_id = fcst.forecast_id
9538: AND fact.previous_fact_id = previous_fact.activity_metric_fact_id(+)
9539: AND fact.act_metric_used_by_id = p_used_by_id
9548:
9549: END IF;
9550: -- Adjust the last record
9551:
9552: UPDATE ozf_act_metric_facts_all f
9553: SET ( fact_value, forward_buy_quantity) =
9554: ( SELECT f.fact_value + a.adj_fact_value,
9555: f.forward_buy_quantity + a.adj_fwd_buy_quantity
9556: FROM
9559: NVL(MIN(previous_fact.forward_buy_quantity), MIN(fcst.forward_buy_quantity)) -
9560: SUM(fact.forward_buy_quantity) adj_fwd_buy_quantity,
9561: MAX(fact.activity_metric_fact_id) activity_metric_fact_id
9562: FROM ozf_act_forecasts_all fcst,
9563: ozf_act_metric_facts_all fact,
9564: ozf_act_metric_facts_all previous_fact
9565: WHERE fact.act_metric_used_by_id = p_used_by_id
9566: AND fact.arc_act_metric_used_by = 'FCST'
9567: AND fact.fact_type = p_dimention
9560: SUM(fact.forward_buy_quantity) adj_fwd_buy_quantity,
9561: MAX(fact.activity_metric_fact_id) activity_metric_fact_id
9562: FROM ozf_act_forecasts_all fcst,
9563: ozf_act_metric_facts_all fact,
9564: ozf_act_metric_facts_all previous_fact
9565: WHERE fact.act_metric_used_by_id = p_used_by_id
9566: AND fact.arc_act_metric_used_by = 'FCST'
9567: AND fact.fact_type = p_dimention
9568: AND fact.act_metric_used_by_id = fcst.forecast_id
9572: ) a
9573: WHERE a.activity_metric_fact_id = f.activity_metric_fact_id
9574: )
9575: WHERE activity_metric_fact_id in (SELECT MAX(activity_metric_fact_id)
9576: FROM ozf_act_metric_facts_all
9577: WHERE act_metric_used_by_id = p_used_by_id
9578: AND arc_act_metric_used_by = 'FCST'
9579: AND fact_type = p_dimention
9580: AND NVL(node_id,1) <> 3
9669: DECODE(fcst.BASE_QUANTITY_TYPE,
9670: 'BASELINE',
9671: SUM(fact.BASELINE_SALES + fact.INCREMENTAL_SALES),
9672: sum(fact.fact_value)) qty
9673: from ozf_act_metric_facts_all fact,
9674: OZF_ACT_FORECASTS_ALL fcst
9675: where fact.fact_type = 'PRODUCT'
9676: and fact.arc_act_metric_used_by = 'FCST'
9677: and fact.act_metric_used_by_id = p_forecast_id
9698: prod.qty,
9699: prod.forecast_dimention_id
9700: FROM ozf_forecast_dimentions dim,
9701: (select fact_reference forecast_dimention_id, sum(fact_value) qty
9702: from ozf_act_metric_facts_all
9703: where fact_type = 'PRODUCT'
9704: and arc_act_metric_used_by = 'FCST'
9705: and act_metric_used_by_id = p_forecast_id
9706: group by fact_reference ) prod
9977: fact.node_id,
9978: NVL(previous_fact.fact_value,fcst.forecast_quantity) total_forecast,
9979: NVL(previous_fact.forward_buy_quantity, fcst.forward_buy_quantity) total_forward_buy,
9980: fcst.forecast_uom_code
9981: from ozf_act_metric_facts_all fact,
9982: ozf_forecast_dimentions dim ,
9983: ozf_act_metric_facts_all previous_fact,
9984: ozf_act_forecasts_all fcst
9985: where fcst.forecast_id = p_used_by_id
9979: NVL(previous_fact.forward_buy_quantity, fcst.forward_buy_quantity) total_forward_buy,
9980: fcst.forecast_uom_code
9981: from ozf_act_metric_facts_all fact,
9982: ozf_forecast_dimentions dim ,
9983: ozf_act_metric_facts_all previous_fact,
9984: ozf_act_forecasts_all fcst
9985: where fcst.forecast_id = p_used_by_id
9986: and fact.arc_act_metric_used_by = 'FCST'
9987: and fact.act_metric_used_by_id = fcst.forecast_id
10204: l_fact_forward_buy := round( (i.total_forward_buy*l_ratio)/l_total_ratio );
10205:
10206: END IF;
10207:
10208: UPDATE ozf_act_metric_facts_all
10209: SET fact_value = l_fact_value,
10210: fact_percent = l_fact_percent,
10211: forward_buy_quantity = l_fact_forward_buy
10212: WHERE activity_metric_fact_id = i.activity_metric_fact_id;
10299: from_date,
10300: to_date,
10301: incremental_sales,
10302: root_fact_id
10303: FROM ozf_act_metric_facts_all
10304: WHERE arc_act_metric_used_by = 'FCST'
10305: AND act_metric_used_by_id = p_fcast_id
10306: AND previous_fact_id = p_prev_id
10307: order by 6;
10315: from_date,
10316: to_date,
10317: incremental_sales,
10318: root_fact_id
10319: FROM ozf_act_metric_facts_all
10320: WHERE arc_act_metric_used_by = 'FCST'
10321: AND act_metric_used_by_id = p_fcast_id
10322: AND previous_fact_id = p_prev_id
10323: AND root_fact_id IS NOT NULL
10352: -- Cascade_Baseline_Update called directly (not from Cascade_baseline_levels)
10353: -- Get the fact value for the parent
10354: SELECT incremental_sales, tpr_percent, fact_type
10355: INTO l_parent_fact_value, l_tpr_percent, l_fact_type
10356: FROM ozf_act_metric_facts_all
10357: WHERE arc_act_metric_used_by = 'FCST'
10358: AND act_metric_used_by_id = p_fcast_id
10359: AND activity_metric_fact_id = p_id;
10360:
10398: IF ( l_cascade_flag <> 0 ) THEN
10399:
10400: SELECT count(*), sum(incremental_sales)
10401: INTO l_temp_count, l_fact_value_sum_all_recs
10402: FROM ozf_act_metric_facts_all
10403: WHERE arc_act_metric_used_by = 'FCST'
10404: AND act_metric_used_by_id = p_fcast_id
10405: AND previous_fact_id = p_id;
10406:
10405: AND previous_fact_id = p_id;
10406:
10407: SELECT incremental_sales, forecast_remaining_quantity
10408: INTO l_current_fact_value, l_total_rem_value
10409: FROM ozf_act_metric_facts_all
10410: WHERE arc_act_metric_used_by = 'FCST'
10411: AND act_metric_used_by_id = p_fcast_id
10412: AND activity_metric_fact_id = p_id;
10413:
10435:
10436: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
10437: SELECT NVL(sum(incremental_sales),0)
10438: INTO l_fval_sum_minus_last_rec
10439: FROM ozf_act_metric_facts_all
10440: WHERE arc_act_metric_used_by = 'FCST'
10441: AND act_metric_used_by_id = p_fcast_id
10442: AND previous_fact_id = facts_record.previous_fact_id
10443: AND activity_metric_fact_id <> facts_record.activity_metric_fact_id ;
10448:
10449: l_rem_value := l_fact_value - facts_record.incremental_sales + facts_record.forecast_remaining_quantity;
10450:
10451: -- update the Remaining to Forecast only it is no the last level. Else, set it to 0.
10452: UPDATE ozf_act_metric_facts_all
10453: SET incremental_sales = l_fact_value
10454: --forecast_remaining_quantity = decode(sign(l_rem_value), -1, decode(root_fact_id,NULL,l_rem_value, 0),0)
10455: WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
10456:
10463: FOR facts_subrecord IN C_FindSubRecords(facts_record.activity_metric_fact_id, p_fcast_id) LOOP
10464: IF (l_temp_sub_previous_fact_id <> facts_subrecord.previous_fact_id) THEN
10465: SELECT count(*), sum(incremental_sales)
10466: INTO l_temp_sub_count, l_fact_value_sum_all_sub_recs
10467: FROM ozf_act_metric_facts_all
10468: WHERE arc_act_metric_used_by = 'FCST'
10469: AND act_metric_used_by_id = p_fcast_id
10470: AND previous_fact_id = facts_subrecord.previous_fact_id
10471: AND root_fact_id IS NOT NULL;
10493:
10494: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
10495: SELECT NVL(sum(incremental_sales),0)
10496: INTO l_fval_sum_minus_last_rec
10497: FROM ozf_act_metric_facts_all
10498: WHERE arc_act_metric_used_by = 'FCST'
10499: AND act_metric_used_by_id = p_fcast_id
10500: AND previous_fact_id = facts_subrecord.previous_fact_id
10501: AND root_fact_id IS NOT NULL
10504: l_fact_value := round(l_temp_parent_fact_value - l_fval_sum_minus_last_rec);
10505:
10506: END IF;
10507:
10508: UPDATE ozf_act_metric_facts_all
10509: SET incremental_sales = l_fact_value
10510: WHERE activity_metric_fact_id = facts_subrecord.activity_metric_fact_id;
10511:
10512: END LOOP; -- subrecord
10583: from_date,
10584: to_date,
10585: incremental_sales,
10586: root_fact_id
10587: FROM ozf_act_metric_facts_all
10588: WHERE arc_act_metric_used_by = 'FCST'
10589: AND act_metric_used_by_id = p_forecast_id
10590: AND previous_fact_id IS NULL
10591: AND root_fact_id IS NULL
10623: WHERE forecast_id = p_fcast_id;
10624:
10625: SELECT count(*), sum(incremental_sales)
10626: INTO l_temp_count, l_fact_value_sum_all_recs
10627: FROM ozf_act_metric_facts_all
10628: WHERE arc_act_metric_used_by = 'FCST'
10629: AND act_metric_used_by_id = p_fcast_id
10630: AND previous_fact_id IS NULL
10631: AND root_fact_id IS NULL;
10653:
10654: --Calculating the l_fval_sum_minus_last_rec since this is the last record in this set
10655: SELECT NVL(sum(incremental_sales),0)
10656: INTO l_fval_sum_minus_last_rec
10657: FROM ozf_act_metric_facts_all
10658: WHERE arc_act_metric_used_by = 'FCST'
10659: AND act_metric_used_by_id = p_fcast_id
10660: AND previous_fact_id IS NULL
10661: AND root_fact_id IS NULL
10686: x_msg_count,
10687: x_msg_data
10688: );
10689:
10690: UPDATE ozf_act_metric_facts_all
10691: SET incremental_sales = l_fact_value
10692: --forecast_remaining_quantity = decode(sign(l_rem_value), -1, l_rem_value, 0)
10693: WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
10694: