[Home] [Help]
125: prod.product_attribute,
126: prod.product_attr_value,
127: prod.product_id
128: ) cust_prod,
129: ozf_forecast_dimentions dim
130: where dim.obj_type = p_obj_type
131: and dim.obj_id = p_obj_id
132: AND dim.forecast_id = p_forecast_id
133: and dim.product_attribute_context = cust_prod.product_attribute_context
666:
667: l_fcst_dimentions VARCHAR2(30000);
668: l_fcst_exclusions VARCHAR2(30000);
669:
670: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
671: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
672: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
673:
674: l_product_attribute_context_e ozf_forecast_prod_exclusions.product_attribute_context_e%TYPE;
667: l_fcst_dimentions VARCHAR2(30000);
668: l_fcst_exclusions VARCHAR2(30000);
669:
670: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
671: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
672: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
673:
674: l_product_attribute_context_e ozf_forecast_prod_exclusions.product_attribute_context_e%TYPE;
675: l_product_attribute_e ozf_forecast_prod_exclusions.product_attribute_e%TYPE;
668: l_fcst_exclusions VARCHAR2(30000);
669:
670: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
671: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
672: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
673:
674: l_product_attribute_context_e ozf_forecast_prod_exclusions.product_attribute_context_e%TYPE;
675: l_product_attribute_e ozf_forecast_prod_exclusions.product_attribute_e%TYPE;
676: l_product_attr_value_e ozf_forecast_prod_exclusions.product_attr_value_e%TYPE;
674: l_product_attribute_context_e ozf_forecast_prod_exclusions.product_attribute_context_e%TYPE;
675: l_product_attribute_e ozf_forecast_prod_exclusions.product_attribute_e%TYPE;
676: l_product_attr_value_e ozf_forecast_prod_exclusions.product_attr_value_e%TYPE;
677:
678: l_qualifier_grouping_no ozf_forecast_dimentions.qualifier_grouping_no%TYPE;
679: l_qualifier_context ozf_forecast_dimentions.qualifier_context%TYPE;
680: l_qualifier_context_attribute ozf_forecast_dimentions.qualifier_attribute%TYPE;
681: l_qualifier_attr_value ozf_forecast_dimentions.qualifier_attr_value%TYPE;
682: l_qualifier_attr_value_to ozf_forecast_dimentions.qualifier_attr_value_to%TYPE;
675: l_product_attribute_e ozf_forecast_prod_exclusions.product_attribute_e%TYPE;
676: l_product_attr_value_e ozf_forecast_prod_exclusions.product_attr_value_e%TYPE;
677:
678: l_qualifier_grouping_no ozf_forecast_dimentions.qualifier_grouping_no%TYPE;
679: l_qualifier_context ozf_forecast_dimentions.qualifier_context%TYPE;
680: l_qualifier_context_attribute ozf_forecast_dimentions.qualifier_attribute%TYPE;
681: l_qualifier_attr_value ozf_forecast_dimentions.qualifier_attr_value%TYPE;
682: l_qualifier_attr_value_to ozf_forecast_dimentions.qualifier_attr_value_to%TYPE;
683: l_comparison_operator_code ozf_forecast_dimentions.comparison_operator_code%TYPE;
676: l_product_attr_value_e ozf_forecast_prod_exclusions.product_attr_value_e%TYPE;
677:
678: l_qualifier_grouping_no ozf_forecast_dimentions.qualifier_grouping_no%TYPE;
679: l_qualifier_context ozf_forecast_dimentions.qualifier_context%TYPE;
680: l_qualifier_context_attribute ozf_forecast_dimentions.qualifier_attribute%TYPE;
681: l_qualifier_attr_value ozf_forecast_dimentions.qualifier_attr_value%TYPE;
682: l_qualifier_attr_value_to ozf_forecast_dimentions.qualifier_attr_value_to%TYPE;
683: l_comparison_operator_code ozf_forecast_dimentions.comparison_operator_code%TYPE;
684:
677:
678: l_qualifier_grouping_no ozf_forecast_dimentions.qualifier_grouping_no%TYPE;
679: l_qualifier_context ozf_forecast_dimentions.qualifier_context%TYPE;
680: l_qualifier_context_attribute ozf_forecast_dimentions.qualifier_attribute%TYPE;
681: l_qualifier_attr_value ozf_forecast_dimentions.qualifier_attr_value%TYPE;
682: l_qualifier_attr_value_to ozf_forecast_dimentions.qualifier_attr_value_to%TYPE;
683: l_comparison_operator_code ozf_forecast_dimentions.comparison_operator_code%TYPE;
684:
685: l_count NUMBER :=0;
678: l_qualifier_grouping_no ozf_forecast_dimentions.qualifier_grouping_no%TYPE;
679: l_qualifier_context ozf_forecast_dimentions.qualifier_context%TYPE;
680: l_qualifier_context_attribute ozf_forecast_dimentions.qualifier_attribute%TYPE;
681: l_qualifier_attr_value ozf_forecast_dimentions.qualifier_attr_value%TYPE;
682: l_qualifier_attr_value_to ozf_forecast_dimentions.qualifier_attr_value_to%TYPE;
683: l_comparison_operator_code ozf_forecast_dimentions.comparison_operator_code%TYPE;
684:
685: l_count NUMBER :=0;
686: l_obj_type VARCHAR2(30);
679: l_qualifier_context ozf_forecast_dimentions.qualifier_context%TYPE;
680: l_qualifier_context_attribute ozf_forecast_dimentions.qualifier_attribute%TYPE;
681: l_qualifier_attr_value ozf_forecast_dimentions.qualifier_attr_value%TYPE;
682: l_qualifier_attr_value_to ozf_forecast_dimentions.qualifier_attr_value_to%TYPE;
683: l_comparison_operator_code ozf_forecast_dimentions.comparison_operator_code%TYPE;
684:
685: l_count NUMBER :=0;
686: l_obj_type VARCHAR2(30);
687:
741:
742: END IF;
743: ----dbms_output.put_line( ' -- IN create_dimentions, before deleting **** -- ');
744: -- inanaiah: R12 - delete records only of the forecast being refered otherwise the reference needed is lost when creating new version
745: DELETE FROM ozf_forecast_dimentions
746: WHERE obj_type = p_obj_type
747: AND obj_id = p_obj_id
748: AND forecast_id = p_forecast_id;
749:
774: ----dbms_output.put_line( ' -- IN create_dimentions, AFTER fetch dim_cv **** -- ');
775: EXIT WHEN dim_cv%NOTFOUND;
776:
777: l_count := l_count + 1 ;
778: ----dbms_output.put_line( ' -- IN create_dimentions, B4 INSERT INTO ozf_forecast_dimentions **** -- ');
779: INSERT INTO ozf_forecast_dimentions(FORECAST_DIMENTION_ID,
780: OBJ_TYPE,
781: OBJ_ID,
782: PRODUCT_ATTRIBUTE_CONTEXT,
775: EXIT WHEN dim_cv%NOTFOUND;
776:
777: l_count := l_count + 1 ;
778: ----dbms_output.put_line( ' -- IN create_dimentions, B4 INSERT INTO ozf_forecast_dimentions **** -- ');
779: INSERT INTO ozf_forecast_dimentions(FORECAST_DIMENTION_ID,
780: OBJ_TYPE,
781: OBJ_ID,
782: PRODUCT_ATTRIBUTE_CONTEXT,
783: PRODUCT_ATTRIBUTE,
794: LAST_UPDATE_DATE,
795: LAST_UPDATED_BY,
796: LAST_UPDATE_LOGIN,
797: FORECAST_ID)
798: VALUES ( ozf_forecast_dimentions_s.nextval,
799: p_obj_type,
800: p_obj_id,
801: l_product_attribute_context,
802: l_product_attribute,
948: SELECT DISTINCT
949: product_attribute_context,
950: product_attribute,
951: product_attr_value
952: FROM ozf_forecast_dimentions
953: WHERE obj_type = p_obj_type
954: AND obj_id = p_obj_id
955: AND forecast_id = p_forecast_id;
956:
968: AND obj_id = p_obj_id ;
969: /*
970: CURSOR c_no_products IS
971: SELECT COUNT(*)
972: FROM ozf_forecast_dimentions
973: WHERE obj_type = p_obj_type
974: AND obj_id = p_obj_id;
975: */
976: CURSOR c_no_excl_products( p_product_attribute_context IN VARCHAR2,
1238: IS
1239:
1240: CURSOR c_groups IS
1241: SELECT distinct qualifier_grouping_no
1242: FROM ozf_forecast_dimentions
1243: WHERE obj_type = p_obj_type
1244: AND obj_id = p_obj_id
1245: AND forecast_id = p_forecast_id;
1246:
1255: qualifier_attribute,
1256: qualifier_attr_value,
1257: qualifier_attr_value_to,
1258: comparison_operator_code
1259: FROM ozf_forecast_dimentions
1260: WHERE obj_type = p_obj_type
1261: AND obj_id = p_obj_id
1262: AND forecast_id = p_forecast_id
1263: AND qualifier_grouping_no = l_grouping_no ;
1269: qualifier_attribute,
1270: qualifier_attr_value,
1271: qualifier_attr_value_to,
1272: comparison_operator_code
1273: FROM ozf_forecast_dimentions
1274: WHERE obj_type = p_obj_type
1275: AND obj_id = p_obj_id
1276: AND forecast_id = p_forecast_id
1277: AND qualifier_grouping_no = l_grouping_no) ;
1754: bs.order_uom,
1755: p_fcst_uom,
1756: null, null)
1757: )
1758: from ozf_forecast_dimentions dim,
1759: ozf_forecast_products prod,
1760: ozf_forecast_customers mkt,
1761: ozf_forecast_periods time,
1762: ams_base_sales_mv bs
1789: SELECT min(forecast_dimention_id) forecast_dimention_id,
1790: product_attribute_context,
1791: product_attribute,
1792: product_attr_value
1793: FROM ozf_forecast_dimentions
1794: WHERE obj_type = p_obj_type
1795: AND obj_id = p_obj_id
1796: AND forecast_id = p_forecast_id
1797: AND qualifier_grouping_no = NVL(l_qualifier_grouping_no, qualifier_grouping_no)
1807: l_product_attr_value IN VARCHAR2 )
1808: IS
1809: SELECT min(forecast_dimention_id) forecast_dimention_id,
1810: min(qualifier_grouping_no) qualifier_grouping_no
1811: FROM ozf_forecast_dimentions
1812: WHERE obj_type = p_obj_type
1813: AND obj_id = p_obj_id
1814: AND forecast_id = p_forecast_id
1815: AND product_attribute_context = NVL(l_product_attribute_context, product_attribute_context)
1866: WHERE off.offer_code = p_offer_code;
1867:
1868:
1869: l_base_sales NUMBER := 0;
1870: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
1871: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
1872: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
1873: l_qualifier_grouping_no NUMBER;
1874: l_period_number NUMBER;
1867:
1868:
1869: l_base_sales NUMBER := 0;
1870: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
1871: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
1872: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
1873: l_qualifier_grouping_no NUMBER;
1874: l_period_number NUMBER;
1875:
1868:
1869: l_base_sales NUMBER := 0;
1870: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
1871: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
1872: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
1873: l_qualifier_grouping_no NUMBER;
1874: l_period_number NUMBER;
1875:
1876: -- R12
2205: CURSOR get_product_qualfiers(p_forecast_dimention_id IN NUMBER) IS
2206: SELECT product_attribute_context,
2207: product_attribute,
2208: product_attr_value
2209: FROM ozf_forecast_dimentions
2210: WHERE forecast_dimention_id = p_forecast_dimention_id
2211: AND forecast_id = p_forecast_id;
2212:
2213: CURSOR get_market_qualifiers(p_forecast_dimention_id IN NUMBER) IS
2211: AND forecast_id = p_forecast_id;
2212:
2213: CURSOR get_market_qualifiers(p_forecast_dimention_id IN NUMBER) IS
2214: SELECT qualifier_grouping_no
2215: FROM ozf_forecast_dimentions
2216: WHERE forecast_dimention_id = p_forecast_dimention_id
2217: AND forecast_id = p_forecast_id;
2218:
2219: l_base_sales NUMBER := 0;
2217: AND forecast_id = p_forecast_id;
2218:
2219: l_base_sales NUMBER := 0;
2220:
2221: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
2222: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
2223: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
2224: l_qualifier_grouping_no NUMBER;
2225: l_period_number NUMBER;
2218:
2219: l_base_sales NUMBER := 0;
2220:
2221: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
2222: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
2223: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
2224: l_qualifier_grouping_no NUMBER;
2225: l_period_number NUMBER;
2226:
2219: l_base_sales NUMBER := 0;
2220:
2221: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
2222: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
2223: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
2224: l_qualifier_grouping_no NUMBER;
2225: l_period_number NUMBER;
2226:
2227: l_previous_fact_id NUMBER ;
2567:
2568: i NUMBER := 0;
2569: j NUMBER := 0;
2570: l_base_sales NUMBER := 0;
2571: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
2572: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
2573: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
2574: l_qualifier_grouping_no NUMBER;
2575: l_period_number NUMBER;
2568: i NUMBER := 0;
2569: j NUMBER := 0;
2570: l_base_sales NUMBER := 0;
2571: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
2572: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
2573: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
2574: l_qualifier_grouping_no NUMBER;
2575: l_period_number NUMBER;
2576: l_previous_fact_id NUMBER ;
2569: j NUMBER := 0;
2570: l_base_sales NUMBER := 0;
2571: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
2572: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
2573: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
2574: l_qualifier_grouping_no NUMBER;
2575: l_period_number NUMBER;
2576: l_previous_fact_id NUMBER ;
2577: l_root_fact_id NUMBER;
3086: CURSOR get_product_qualfiers(p_forecast_dimention_id IN NUMBER) IS
3087: SELECT product_attribute_context,
3088: product_attribute,
3089: product_attr_value
3090: FROM ozf_forecast_dimentions
3091: WHERE forecast_dimention_id = p_forecast_dimention_id
3092: AND forecast_id = p_forecast_id;
3093:
3094: CURSOR get_market_qualifiers(p_forecast_dimention_id IN NUMBER) IS
3092: AND forecast_id = p_forecast_id;
3093:
3094: CURSOR get_market_qualifiers(p_forecast_dimention_id IN NUMBER) IS
3095: SELECT qualifier_grouping_no
3096: FROM ozf_forecast_dimentions
3097: WHERE forecast_dimention_id = p_forecast_dimention_id
3098: AND forecast_id = p_forecast_id;
3099:
3100: CURSOR get_products(l_qualifier_grouping_no IN NUMBER) IS
3101: SELECT min(forecast_dimention_id) forecast_dimention_id,
3102: product_attribute_context,
3103: product_attribute,
3104: product_attr_value
3105: FROM ozf_forecast_dimentions
3106: WHERE obj_type = p_obj_type
3107: AND obj_id = p_obj_id
3108: AND forecast_id = p_forecast_id
3109: AND qualifier_grouping_no = NVL(l_qualifier_grouping_no, qualifier_grouping_no)
3117: l_product_attr_value IN VARCHAR2 )
3118: IS
3119: SELECT min(forecast_dimention_id) forecast_dimention_id,
3120: min(qualifier_grouping_no) qualifier_grouping_no
3121: FROM ozf_forecast_dimentions
3122: WHERE obj_type = p_obj_type
3123: AND obj_id = p_obj_id
3124: AND forecast_id = p_forecast_id
3125: AND product_attribute_context = NVL(l_product_attribute_context, product_attribute_context)
3151: l_orig_incremental_sales NUMBER := 0;
3152: l_new_incremental_sales NUMBER := 0;
3153: l_activity_metric_id NUMBER := 0;
3154: l_base_sales NUMBER := 0;
3155: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
3156: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
3157: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
3158: l_qualifier_grouping_no NUMBER;
3159: l_period_number NUMBER;
3152: l_new_incremental_sales NUMBER := 0;
3153: l_activity_metric_id NUMBER := 0;
3154: l_base_sales NUMBER := 0;
3155: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
3156: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
3157: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
3158: l_qualifier_grouping_no NUMBER;
3159: l_period_number NUMBER;
3160: l_previous_fact_id NUMBER ;
3153: l_activity_metric_id NUMBER := 0;
3154: l_base_sales NUMBER := 0;
3155: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
3156: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
3157: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
3158: l_qualifier_grouping_no NUMBER;
3159: l_period_number NUMBER;
3160: l_previous_fact_id NUMBER ;
3161: l_root_fact_id NUMBER;
3343: and prod.obj_type = cust.obj_type
3344: and prod.obj_id = cust.obj_id
3345: and cust.site_use_code = 'SHIP_TO'
3346: ) cust_prod,
3347: ozf_forecast_dimentions dim,
3348: ozf_forecast_periods period
3349: where dim.obj_type = p_obj_type
3350: and dim.obj_id = p_obj_id
3351: AND dim.forecast_id = p_forecast_id
3591: CURSOR get_product_qualfiers(p_forecast_dimention_id IN NUMBER) IS
3592: SELECT product_attribute_context,
3593: product_attribute,
3594: product_attr_value
3595: FROM ozf_forecast_dimentions
3596: WHERE forecast_dimention_id = p_forecast_dimention_id
3597: AND forecast_id = p_forecast_id;
3598:
3599: CURSOR get_market_qualifiers(p_forecast_dimention_id IN NUMBER) IS
3597: AND forecast_id = p_forecast_id;
3598:
3599: CURSOR get_market_qualifiers(p_forecast_dimention_id IN NUMBER) IS
3600: SELECT qualifier_grouping_no
3601: FROM ozf_forecast_dimentions
3602: WHERE forecast_dimention_id = p_forecast_dimention_id
3603: AND forecast_id = p_forecast_id;
3604:
3605: CURSOR get_products(l_qualifier_grouping_no IN NUMBER) IS
3606: SELECT min(forecast_dimention_id) forecast_dimention_id,
3607: product_attribute_context,
3608: product_attribute,
3609: product_attr_value
3610: FROM ozf_forecast_dimentions
3611: WHERE obj_type = p_obj_type
3612: AND obj_id = p_obj_id
3613: AND forecast_id = p_forecast_id
3614: AND qualifier_grouping_no = NVL(l_qualifier_grouping_no, qualifier_grouping_no)
3622: l_product_attr_value IN VARCHAR2 )
3623: IS
3624: SELECT min(forecast_dimention_id) forecast_dimention_id,
3625: min(qualifier_grouping_no) qualifier_grouping_no
3626: FROM ozf_forecast_dimentions
3627: WHERE obj_type = p_obj_type
3628: AND obj_id = p_obj_id
3629: AND forecast_id = p_forecast_id
3630: AND product_attribute_context = NVL(l_product_attribute_context, product_attribute_context)
3655:
3656:
3657: l_activity_metric_id NUMBER := p_activity_metric_id;
3658: l_base_sales NUMBER := 0;
3659: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
3660: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
3661: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
3662: l_qualifier_grouping_no NUMBER;
3663: l_period_number NUMBER;
3656:
3657: l_activity_metric_id NUMBER := p_activity_metric_id;
3658: l_base_sales NUMBER := 0;
3659: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
3660: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
3661: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
3662: l_qualifier_grouping_no NUMBER;
3663: l_period_number NUMBER;
3664: l_previous_fact_id NUMBER ;
3657: l_activity_metric_id NUMBER := p_activity_metric_id;
3658: l_base_sales NUMBER := 0;
3659: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
3660: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
3661: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
3662: l_qualifier_grouping_no NUMBER;
3663: l_period_number NUMBER;
3664: l_previous_fact_id NUMBER ;
3665: l_root_fact_id NUMBER;
4016: and prod.obj_type = cust.obj_type
4017: and prod.obj_id = cust.obj_id
4018: and cust.site_use_code = 'SHIP_TO'
4019: ) cust_prod,
4020: ozf_forecast_dimentions dim,
4021: ozf_forecast_periods period
4022: where dim.obj_type = p_obj_type
4023: and dim.obj_id = p_obj_id
4024: AND dim.forecast_id = p_forecast_id
5005: SELECT count(*) INTO l_new_count FROM (
5006: SELECT product_attribute_context, product_attribute, product_attr_value,
5007: qualifier_grouping_no, qualifier_context, qualifier_attribute,
5008: qualifier_attr_value
5009: FROM ozf_forecast_dimentions
5010: WHERE obj_id = p_obj_id
5011: AND obj_type = p_obj_type
5012: AND forecast_id = l_forecast_id);
5013:
5014: SELECT count(*) INTO l_common_count FROM (
5015: SELECT product_attribute_context, product_attribute, product_attr_value,
5016: qualifier_grouping_no, qualifier_context, qualifier_attribute,
5017: qualifier_attr_value
5018: FROM ozf_forecast_dimentions
5019: WHERE obj_id = p_obj_id
5020: AND obj_type = p_obj_type
5021: AND forecast_id = l_forecast_id
5022: INTERSECT
5022: INTERSECT
5023: SELECT product_attribute_context, product_attribute, product_attr_value,
5024: qualifier_grouping_no, qualifier_context, qualifier_attribute,
5025: qualifier_attr_value
5026: FROM ozf_forecast_dimentions
5027: WHERE obj_id = l_offer_id
5028: AND obj_type = l_disp_type
5029: AND forecast_id = l_forecast_id);
5030:
7810: SELECT qualifier_context,
7811: qualifier_attribute,
7812: qualifier_attr_value,
7813: comparison_operator_code
7814: FROM ozf_forecast_dimentions
7815: WHERE obj_type = p_obj_type
7816: AND obj_id = p_obj_id
7817: AND forecast_id = p_forecast_id
7818: AND product_attribute_context = 'ITEM'
8034: l_list_price NUMBER;
8035:
8036: l_obj_type VARCHAR2(30);
8037: l_obj_id NUMBER;
8038: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
8039: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
8040: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
8041: l_fcst_uom OZF_ACT_FORECASTS_ALL.forecast_uom_code%TYPE;
8042: l_currency_code ozf_worksheet_headers_b.currency_code%TYPE;
8035:
8036: l_obj_type VARCHAR2(30);
8037: l_obj_id NUMBER;
8038: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
8039: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
8040: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
8041: l_fcst_uom OZF_ACT_FORECASTS_ALL.forecast_uom_code%TYPE;
8042: l_currency_code ozf_worksheet_headers_b.currency_code%TYPE;
8043: l_price_list_id NUMBER;
8036: l_obj_type VARCHAR2(30);
8037: l_obj_id NUMBER;
8038: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
8039: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
8040: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
8041: l_fcst_uom OZF_ACT_FORECASTS_ALL.forecast_uom_code%TYPE;
8042: l_currency_code ozf_worksheet_headers_b.currency_code%TYPE;
8043: l_price_list_id NUMBER;
8044: l_forecast_id NUMBER;
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
8075: fact.activity_metric_fact_id = p_activity_metric_fact_id
8076: AND fcst.FORECAST_ID = fact.act_metric_used_by_id
8336: l_other_costs NUMBER;
8337:
8338: l_obj_type VARCHAR2(30);
8339: l_obj_id NUMBER;
8340: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
8341: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
8342: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
8343: l_fcst_uom OZF_ACT_FORECASTS_ALL.forecast_uom_code%TYPE;
8344: l_currency_code ozf_worksheet_headers_b.currency_code%TYPE;
8337:
8338: l_obj_type VARCHAR2(30);
8339: l_obj_id NUMBER;
8340: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
8341: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
8342: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
8343: l_fcst_uom OZF_ACT_FORECASTS_ALL.forecast_uom_code%TYPE;
8344: l_currency_code ozf_worksheet_headers_b.currency_code%TYPE;
8345: l_price_list_id NUMBER;
8338: l_obj_type VARCHAR2(30);
8339: l_obj_id NUMBER;
8340: l_product_attribute_context ozf_forecast_dimentions.product_attribute_context%TYPE;
8341: l_product_attribute ozf_forecast_dimentions.product_attribute%TYPE;
8342: l_product_attr_value ozf_forecast_dimentions.product_attr_value%TYPE;
8343: l_fcst_uom OZF_ACT_FORECASTS_ALL.forecast_uom_code%TYPE;
8344: l_currency_code ozf_worksheet_headers_b.currency_code%TYPE;
8345: l_price_list_id NUMBER;
8346:
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
8374: fact.activity_metric_fact_id = p_activity_metric_fact_id
8375: AND fcst.FORECAST_ID = fact.act_metric_used_by_id
9663: dim.product_attribute_context,
9664: dim.product_attr_value,
9665: prod.qty,
9666: prod.forecast_dimention_id
9667: FROM ozf_forecast_dimentions dim,
9668: (select fact.fact_reference forecast_dimention_id,
9669: DECODE(fcst.BASE_QUANTITY_TYPE,
9670: 'BASELINE',
9671: SUM(fact.BASELINE_SALES + fact.INCREMENTAL_SALES),
9696: dim.product_attribute_context,
9697: dim.product_attr_value,
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'
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
9986: and fact.arc_act_metric_used_by = 'FCST'