[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_ent_year
WHERE ent_year_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_ent_qtr
WHERE ent_qtr_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_ent_period
WHERE ent_period_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_week
WHERE week_id = p_time_id;
SELECT start_date, end_date INTO x_start_date, x_end_date
FROM ozf_time_day
WHERE report_date_julian = p_time_id;
SELECT period_number,
start_date,
end_date,
period_type_id
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type
AND obj_id = p_object_id
AND period_number = NVL(p_period_number, period_number)
AND (forecast_id IS NULL -- inanaiah: For compatibility with older release
OR forecast_id = p_forecast_id); -- inanaiah: making periods bound to forecast_id as is the case when creating new version ;
select SUM(sales.sales_qty)
from ozf_order_sales_sumry_mv sales,
ozf_time_rpt_struct rpt,
( select cust.qualifier_grouping_no,
cust.cust_account_id,
decode ( count(cust.qualifier_grouping_no),
1, max(cust.site_use_code), null
) site_use_code,
decode ( count(cust.qualifier_grouping_no),
1, max(cust.site_use_id), null
) site_use_id,
prod.product_attribute_context,
prod.product_attribute,
prod.product_attr_value,
prod.product_id inventory_item_id
from ozf_forecast_customers cust,
ozf_forecast_products prod
where prod.obj_type = p_obj_type
and prod.obj_id = p_obj_id
and prod.obj_type = cust.obj_type
and prod.obj_id = cust.obj_id
group by cust.qualifier_grouping_no,
cust.cust_account_id,
prod.product_attribute_context,
prod.product_attribute,
prod.product_attr_value,
prod.product_id
) cust_prod,
ozf_forecast_dimentions dim
where dim.obj_type = p_obj_type
and dim.obj_id = p_obj_id
AND dim.forecast_id = p_forecast_id
and dim.product_attribute_context = cust_prod.product_attribute_context
and dim.product_attribute = cust_prod.product_attribute
and dim.product_attr_value = cust_prod.product_attr_value
and dim.qualifier_grouping_no = cust_prod.qualifier_grouping_no
and sales.sold_to_cust_account_id = cust_prod.cust_account_id
and decode(cust_prod.site_use_code,
NULL,-99,
'BILL_TO',sales.bill_to_site_use_id,
sales.ship_to_site_use_id) = NVL(cust_prod.site_use_id, -99)
and sales.inventory_item_id = cust_prod.inventory_item_id
and rpt.report_date = p_as_of_date
and BITAND(rpt.record_type_id, p_record_type_id ) = rpt.record_type_id
and rpt.time_id = sales.time_id
and dim.product_attribute_context = NVL(p_product_attribute_context, dim.product_attribute_context)
and dim.product_attribute = NVL(p_product_attribute, dim.product_attribute)
and dim.product_attr_value = NVL(p_product_attr_value, dim.product_attr_value)
and dim.qualifier_grouping_no = NVL(p_qualifier_grouping_no, dim.qualifier_grouping_no) ;
SELECT base_quantity_type, offer_code
FROM ozf_act_forecasts_all
WHERE forecast_id = p_forecast_id;
SELECT qp_list_header_id --offer_id
FROM ozf_offers off
WHERE off.offer_code = p_offer_code;
SELECT ozf_act_metrics_all_s.NEXTVAL
FROM dual;
INSERT INTO ozf_act_metrics_all (
ACTIVITY_METRIC_ID , LAST_UPDATE_DATE,
LAST_UPDATED_BY , CREATION_DATE,
CREATED_BY , LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER , ACT_METRIC_USED_BY_ID,
ARC_ACT_METRIC_USED_BY , APPLICATION_ID,
SENSITIVE_DATA_FLAG , METRIC_ID,
ORG_ID,
DIRTY_FLAG )
VALUES ( l_activity_metric_id , sysdate,
fnd_global.user_id , sysdate,
fnd_global.user_id , fnd_global.login_id,
1 , l_forecast_id,
'FCST' , 530 ,
'N' , 1,
MO_GLOBAL.GET_CURRENT_ORG_ID() ,
'Y');
'select distinct '||
' prd.PRODUCT_ATTRIBUTE_CONTEXT, '||
' prd.PRODUCT_ATTRIBUTE, '||
' prd.PRODUCT_ATTR_VALUE, '||
' (off.qp_list_header_id + mkt.QUALIFIER_GROUPING_NO) qualifier_grouping_no, '||
' mkt.QUALIFIER_CONTEXT, '||
' mkt.QUALIFIER_ATTRIBUTE, '||
' mkt.QUALIFIER_ATTR_VALUE, '||
' mkt.QUALIFIER_ATTR_VALUE_TO, '||
' mkt.COMPARISON_OPERATOR_CODE '||
'from qp_pricing_attributes prd, '||
' qp_qualifiers mkt, '||
' ozf_act_offers off, '||
' qp_list_lines ln '||
'where off.arc_act_offer_used_by = :l_object_type '||
'and off.act_offer_used_by_id = :l_object_id '||
'and off.qp_list_header_id = prd.list_header_id '||
'and prd.excluder_flag = ''N'' '||
'and prd.list_line_id = ln.list_line_id '||
'and (ln.start_date_active < SYSDATE '||
' OR ln.start_date_active IS NULL) '||
'and (ln.end_date_active > SYSDATE '||
' OR ln.end_date_active IS NULL) '||
'and prd.list_header_id = mkt.list_header_id ' ||
'and (mkt.start_date_active < SYSDATE '||
' OR mkt.start_date_active IS NULL) '||
'and (mkt.end_date_active > SYSDATE '||
' OR mkt.end_date_active IS NULL) '||
'and mkt.list_line_id = -1';
'select exp.PRODUCT_ATTRIBUTE_CONTEXT, '||
' exp.PRODUCT_ATTRIBUTE, '||
' exp.PRODUCT_ATTR_VALUE, '||
' prd.PRODUCT_ATTRIBUTE_CONTEXT, '||
' prd.PRODUCT_ATTRIBUTE, '||
' prd.PRODUCT_ATTR_VALUE ' ||
'from qp_pricing_attributes prd, '||
' qp_pricing_attributes exp,'||
' ozf_act_offers off '||
'where off.arc_act_offer_used_by = :l_object_type '||
'and off.act_offer_used_by_id = :l_object_id '||
'and off.qp_list_header_id = prd.list_header_id '||
'and prd.excluder_flag = ''Y'' '||
'and prd.list_line_id = exp.list_line_id '||
'and exp.excluder_flag = ''N'' ' ;
'select distinct '||
' prd.PRODUCT_ATTRIBUTE_CONTEXT, '||
' prd.PRODUCT_ATTRIBUTE, '||
' prd.PRODUCT_ATTR_VALUE, '||
' mkt.QUALIFIER_GROUPING_NO, '||
' mkt.QUALIFIER_CONTEXT, '||
' mkt.QUALIFIER_ATTRIBUTE, '||
' mkt.QUALIFIER_ATTR_VALUE, '||
' mkt.QUALIFIER_ATTR_VALUE_TO, '||
' mkt.COMPARISON_OPERATOR_CODE '||
'from qp_pricing_attributes prd, '||
' qp_qualifiers mkt, '||
' qp_list_lines ln '||
'where ''OFFR'' = :l_object_type '||
'and prd.list_header_id = :l_object_id '||
'and prd.excluder_flag = ''N'' '||
'and prd.list_line_id = ln.list_line_id '||
'and (ln.start_date_active < SYSDATE '||
' OR ln.start_date_active IS NULL) '||
'and (ln.end_date_active > SYSDATE '||
' OR ln.end_date_active IS NULL) '||
'and prd.list_header_id = mkt.list_header_id ' ||
'and (mkt.start_date_active < SYSDATE '||
' OR mkt.start_date_active IS NULL) '||
'and (mkt.end_date_active > SYSDATE '||
' OR mkt.end_date_active IS NULL) '||
'and mkt.list_line_id = -1';
'select exp.PRODUCT_ATTRIBUTE_CONTEXT, '||
' exp.PRODUCT_ATTRIBUTE, '||
' exp.PRODUCT_ATTR_VALUE, '||
' prd.PRODUCT_ATTRIBUTE_CONTEXT, '||
' prd.PRODUCT_ATTRIBUTE, '||
' prd.PRODUCT_ATTR_VALUE ' ||
'from qp_pricing_attributes prd, '||
' qp_pricing_attributes exp '||
'where ''OFFR'' = :l_object_type '||
'and prd.list_header_id = :l_object_id '||
'and prd.excluder_flag = ''Y'' '||
'and prd.list_line_id = exp.list_line_id '||
'and exp.excluder_flag = ''N'' ' ;
'select ''ITEM'' PRODUCT_ATTRIBUTE_CONTEXT ,'||
' DECODE(a.level_type_code,''PRODUCT'', '||
' ''PRICING_ATTRIBUTE1'', '||
' ''PRICING_ATTRIBUTE2'') PRODUCT_ATTRIBUTE , '||
' DECODE(a.level_type_code,''PRODUCT'', '||
' a.inventory_item_id, '||
' a.category_id) PRODUCT_ATTR_VALUE, '||
' 10 QUALIFIER_GROUPING_NO , '||
' ''CUSTOMER'' QUALIFIER_CONTEXT, '||
' ''QUALIFIER_ATTRIBUTE2'' QUALIFIER_ATTRIBUTE, '||
' b.qualifier_id QUALIFIER_ATTR_VALUE, '||
' null QUALIFIER_ATTR_VALUE_TO, '||
' ''='' COMPARISON_OPERATOR_CODE '||
'from ams_act_products a, '||
'ozf_offers b '||
'where a.arc_act_product_used_by = :l_object_type '||
'and a.act_product_used_by_id = :l_object_id '||
'and a.act_product_used_by_id = b.qp_list_header_id '||
'and a.excluded_flag = ''N'' ' ;
'select ''ITEM'' product_attribute_context, '||
' DECODE(a.level_type_code,''PRODUCT'', '||
' ''PRICING_ATTRIBUTE1'', '||
' ''PRICING_ATTRIBUTE2'') product_attribute , '||
' DECODE(a.level_type_code,''PRODUCT'', '||
' a.inventory_item_id, '||
' a.category_id) product_attr_value, '||
' ''ITEM'' product_attribute_context_e , '||
' DECODE(b.level_type_code,''PRODUCT'', '||
' ''PRICING_ATTRIBUTE1'', '||
' ''PRICING_ATTRIBUTE2'') product_attribute_e , '||
' DECODE(b.level_type_code,''PRODUCT'', '||
' b.inventory_item_id, '||
' b.category_id) product_attr_value_e '||
'from ams_act_products a, '||
' ams_act_products b '||
'where a.arc_act_product_used_by = :l_object_type '||
'and a.act_product_used_by_id = :l_object_id '||
'and a.excluded_flag = ''N'' '||
'and b.arc_act_product_used_by = ''PROD'' '||
'and b.act_product_used_by_id = a.activity_product_id '||
'and b.excluded_flag = ''Y'' ';
'select prd.product_attribute_context, '||
' prd.product_attribute, '||
' prd.product_attr_value, '||
' -1 qualifier_grouping_no, '||
' mkt.qualifier_context, '||
' mkt.qualifier_attribute, '||
' mkt.qualifier_attr_value, '||
' NULL qualifier_attr_value_to, '||
' mkt.comparison_operator_code '||
'from ozf_worksheet_lines prd, '||
' ozf_worksheet_qualifiers mkt '||
'where ''WKST'' = :l_object_type '||
'and mkt.worksheet_header_id = :l_object_id '||
'and mkt.worksheet_header_id = prd.worksheet_header_id '||
'and prd.exclude_flag = ''N'' ';
'SELECT '||
' ODP.PRODUCT_CONTEXT, '||
' ODP.PRODUCT_ATTRIBUTE, '||
' ODP.PRODUCT_ATTR_VALUE, '||
' MKT.QUALIFIER_GROUPING_NO, '||
' MKT.QUALIFIER_CONTEXT, '||
' MKT.QUALIFIER_ATTRIBUTE, '||
' MKT.QUALIFIER_ATTR_VALUE, '||
' MKT.QUALIFIER_ATTR_VALUE_TO, '||
' MKT.COMPARISON_OPERATOR_CODE '||
'FROM '||
' OZF_OFFERS OFFR, '||
' OZF_OFFER_DISCOUNT_LINES ODL, '||
' OZF_OFFER_DISCOUNT_PRODUCTS ODP, '||
' QP_QUALIFIERS MKT '||
'WHERE ''OFFR'' = :l_object_type '||
' AND OFFR.QP_LIST_HEADER_ID = :l_object_id '||
' AND OFFR.OFFER_ID = ODL.OFFER_ID '||
' AND ODL.TIER_TYPE = ''PBH'' '||
' AND ODP.OFFER_ID = OFFR.OFFER_ID '||
' AND ODP.OFFER_DISCOUNT_LINE_ID = ODL.OFFER_DISCOUNT_LINE_ID '||
' AND ODP.APPLY_DISCOUNT_FLAG = ''Y'' '||
' AND OFFR.QP_LIST_HEADER_ID = MKT.LIST_HEADER_ID '||
' AND (MKT.START_DATE_ACTIVE < SYSDATE OR MKT.START_DATE_ACTIVE IS NULL) '||
' AND (MKT.END_DATE_ACTIVE > SYSDATE OR MKT.END_DATE_ACTIVE IS NULL) '||
' AND MKT.LIST_LINE_ID = -1';
' SELECT '||
' EXCODP.PRODUCT_CONTEXT, '||
' EXCODP.PRODUCT_ATTRIBUTE, '||
' EXCODP.PRODUCT_ATTR_VALUE, '||
' PRDODP.PRODUCT_CONTEXT, '||
' PRDODP.PRODUCT_ATTRIBUTE, '||
' PRDODP.PRODUCT_ATTR_VALUE '||
'FROM '||
' OZF_OFFERS OFFR, '||
' OZF_OFFER_DISCOUNT_LINES ODL, '||
' OZF_OFFER_DISCOUNT_PRODUCTS PRDODP, '||
' OZF_OFFER_DISCOUNT_PRODUCTS EXCODP '||
'WHERE ''OFFR'' = :l_object_type '||
' AND OFFR.QP_LIST_HEADER_ID = :l_object_id '||
' AND OFFR.OFFER_ID = ODL.OFFER_ID '||
' AND ODL.TIER_TYPE = ''PBH'' '||
' AND EXCODP.OFFER_ID = OFFR.OFFER_ID '||
' AND ODL.OFFER_DISCOUNT_LINE_ID = EXCODP.OFFER_DISCOUNT_LINE_ID '||
' AND EXCODP.APPLY_DISCOUNT_FLAG = ''N'' '||
' AND PRDODP.OFFER_ID = OFFR.OFFER_ID '||
' AND ODL.OFFER_DISCOUNT_LINE_ID = PRDODP.OFFER_DISCOUNT_LINE_ID '||
' AND PRDODP.APPLY_DISCOUNT_FLAG = ''Y'' ';
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = l_qp_list_header_id;
DELETE FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id;
INSERT INTO ozf_forecast_dimentions(FORECAST_DIMENTION_ID,
OBJ_TYPE,
OBJ_ID,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTRIBUTE,
PRODUCT_ATTR_VALUE,
QUALIFIER_GROUPING_NO,
QUALIFIER_CONTEXT,
QUALIFIER_ATTRIBUTE,
QUALIFIER_ATTR_VALUE,
QUALIFIER_ATTR_VALUE_TO,
COMPARISON_OPERATOR_CODE,
SECURITY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
FORECAST_ID)
VALUES ( ozf_forecast_dimentions_s.nextval,
p_obj_type,
p_obj_id,
l_product_attribute_context,
l_product_attribute,
l_product_attr_value,
l_qualifier_grouping_no,
l_qualifier_context,
l_qualifier_context_attribute,
l_qualifier_attr_value,
l_qualifier_attr_value_to,
l_comparison_operator_code,
NULL,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
p_forecast_id );
/* inanaiah: R12 - not deleted as the reference is needed when creating new version
DELETE FROM ozf_forecast_prod_exclusions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id ;
INSERT INTO ozf_forecast_prod_exclusions(
FORECAST_PROD_EXCLUSION_ID,
OBJ_TYPE,
OBJ_ID,
PRODUCT_ATTRIBUTE_CONTEXT,
PRODUCT_ATTRIBUTE,
PRODUCT_ATTR_VALUE,
PRODUCT_ATTRIBUTE_CONTEXT_E,
PRODUCT_ATTRIBUTE_E,
PRODUCT_ATTR_VALUE_E,
SECURITY_GROUP_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES ( ozf_forecast_prod_exclusions_s.nextval,
p_obj_type,
p_obj_id,
l_product_attribute_context,
l_product_attribute,
l_product_attr_value,
l_product_attribute_context_e,
l_product_attribute_e,
l_product_attr_value_e,
NULL,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id );
SELECT DISTINCT
product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id;
SELECT product_attribute_context_e,
product_attribute_e,
product_attr_value_e
FROM ozf_forecast_prod_exclusions
WHERE product_attribute_context = p_product_attribute_context
AND product_attribute = p_product_attribute
AND product_attr_value = p_product_attr_value
AND obj_type = p_obj_type
AND obj_id = p_obj_id ;
SELECT COUNT(*)
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id;
SELECT COUNT(*)
FROM ozf_forecast_prod_exclusions
WHERE product_attribute_context = p_product_attribute_context
AND product_attribute = p_product_attribute
AND product_attr_value = p_product_attr_value
AND obj_type = p_obj_type
AND obj_id = p_obj_id ;
delete from ozf_forecast_products
where obj_type = p_obj_type
and obj_id = p_obj_id ;
FND_DSQL.add_text('INSERT INTO ozf_forecast_products( ');
FND_DSQL.add_text('last_update_date, ');
FND_DSQL.add_text('last_updated_by , ');
FND_DSQL.add_text('last_update_login )');
FND_DSQL.add_text(' SELECT ');
SELECT DECODE(instr(upper(condition_id_column),'SITE_USE_ID'),
0,DECODE(instr(upper(condition_id_column),'PARTY_SITE_ID'),0,'N','P'),
'C')
FROM ozf_denorm_queries
WHERE query_for='ELIG'
AND condition_id_column IS NOT NULL
AND context = l_context
AND attribute = l_attribute;
SELECT distinct qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id;
SELECT site_use_code
FROM hz_cust_site_uses
WHERE site_use_id = p_site_use_id;
SELECT DISTINCT
qualifier_context,
qualifier_attribute,
qualifier_attr_value,
qualifier_attr_value_to,
comparison_operator_code
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND qualifier_grouping_no = l_grouping_no ;
SELECT COUNT(*)
FROM (SELECT DISTINCT
qualifier_context,
qualifier_attribute,
qualifier_attr_value,
qualifier_attr_value_to,
comparison_operator_code
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND qualifier_grouping_no = l_grouping_no) ;
DELETE FROM ozf_forecast_customers
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id ;
FND_DSQL.add_text('INSERT INTO ozf_forecast_customers( ');
FND_DSQL.add_text('last_update_date, ');
FND_DSQL.add_text('last_updated_by, ');
FND_DSQL.add_text('last_update_login )');
FND_DSQL.add_text(' SELECT ');
SELECT DISTINCT
decode(p_period_type_id ,
1, report_date_julian,
16, week_id,
32, month_id,
64, ent_qtr_id
, ent_year_id ) time_id
FROM ozf_time_day
WHERE report_date BETWEEN p_start_date AND p_end_date;
SELECT to_char(report_date) name, start_date, end_date
FROM ozf_time_day
WHERE report_date_julian = l_time_id;
SELECT name, start_date, end_date
FROM ozf_time_week
WHERE week_id = l_time_id;
SELECT name, start_date, end_date
FROM ozf_time_ent_period
WHERE ent_period_id = l_time_id;
SELECT name, start_date, end_date
FROM ozf_time_ent_qtr
WHERE ent_qtr_id = l_time_id;
SELECT name, start_date, end_date
FROM ozf_time_ent_year
WHERE ent_year_id = l_time_id;
DELETE FROM ozf_forecast_periods
WHERE obj_id = p_obj_id
AND obj_type = p_obj_type
AND forecast_id = p_forecast_id;
INSERT INTO ozf_forecast_periods (
forecast_period_id,
obj_type,
obj_id,
period_number,
start_date,
end_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
period_type_id,
period_name,
forecast_id)
VALUES ( ozf_forecast_periods_s.nextval,
p_obj_type,
p_obj_id,
l_period_number,
l_temp_start_date,
l_temp_end_date,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_period_type_id,
l_name,
p_forecast_id );
INSERT INTO ozf_act_metric_facts_all (
ACTIVITY_METRIC_FACT_ID , LAST_UPDATE_DATE ,
LAST_UPDATED_BY , CREATION_DATE ,
CREATED_BY , OBJECT_VERSION_NUMBER ,
ACT_METRIC_USED_BY_ID , ARC_ACT_METRIC_USED_BY ,
VALUE_TYPE , ACTIVITY_METRIC_ID ,
TRANS_FORECASTED_VALUE , FUNCTIONAL_CURRENCY_CODE ,
FUNC_FORECASTED_VALUE , ORG_ID ,
DE_METRIC_ID , TIME_ID1 ,
FROM_DATE , TO_DATE ,
FACT_VALUE , FACT_PERCENT ,
BASE_QUANTITY , ROOT_FACT_ID ,
PREVIOUS_FACT_ID , FACT_TYPE ,
FACT_REFERENCE , LAST_UPDATE_LOGIN,
FORECAST_REMAINING_QUANTITY , NODE_ID)
VALUES ( ozf_act_metric_facts_all_s.nextval , sysdate ,
fnd_global.user_id , sysdate ,
fnd_global.user_id , 1 ,
p_forecast_id , 'FCST' ,
'NUMERIC' , p_activity_metric_id,
0 , 'NONE',
0, MO_GLOBAL.GET_CURRENT_ORG_ID(),
0 , 0 ,
p_start_date , p_end_date ,
NULL , NULL ,
ROUND(NVL(p_base_quantity,0)) , p_root_fact_id ,
p_previous_fact_id , p_fact_type ,
p_fact_reference , fnd_global.login_id,
0 , p_node_id);
select SUM(inv_convert.inv_um_convert( bs.product_id,
null,
bs.qty,
bs.order_uom,
p_fcst_uom,
null, null)
)
from ozf_forecast_dimentions dim,
ozf_forecast_products prod,
ozf_forecast_customers mkt,
ozf_forecast_periods time,
ams_base_sales_mv bs
where dim.obj_type = p_obj_type
and dim.obj_id = p_obj_id
and dim.obj_type = prod.obj_type
and dim.obj_id = prod.obj_id
and dim.product_attribute_context = prod.product_attribute_context
and dim.product_attribute = prod.product_attribute
and dim.product_attr_value = prod.product_attr_value
and dim.obj_type = mkt.obj_type
and dim.obj_id = mkt.obj_id
and dim.qualifier_grouping_no = mkt.qualifier_grouping_no
and dim.obj_type = time.obj_type
and dim.obj_id = time.obj_id
and bs.product_id = prod.product_id
and bs.cust_account_id = mkt.cust_account_id
and bs.ordered_date between time.start_date and time.end_date
and dim.product_attribute_context = NVL(l_product_attribute_context, dim.product_attribute_context)
and dim.product_attribute = NVL(l_product_attribute, dim.product_attribute)
and dim.product_attr_value = NVL(l_product_attr_value, dim.product_attr_value)
and dim.qualifier_grouping_no = NVL(l_qualifier_grouping_no, dim.qualifier_grouping_no)
and time.period_number = NVL(l_period_number,time.period_number)
and DECODE(mkt.site_use_code,
'BILL_TO', bs.bill_to_site_id,
'SHIP_TO', bs.ship_to_site_id, 99) = NVL(mkt.SITE_USE_ID,99) ;
SELECT min(forecast_dimention_id) forecast_dimention_id,
product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND qualifier_grouping_no = NVL(l_qualifier_grouping_no, qualifier_grouping_no)
GROUP BY
product_attribute_context,
product_attribute,
product_attr_value ;
SELECT min(forecast_dimention_id) forecast_dimention_id,
min(qualifier_grouping_no) qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND product_attribute_context = NVL(l_product_attribute_context, product_attribute_context)
AND product_attribute = NVL(l_product_attribute, product_attribute)
AND product_attr_value = NVL(l_product_attr_value, product_attr_value)
GROUP BY
qualifier_context,
qualifier_attribute,
qualifier_attr_value,
qualifier_grouping_no ;
SELECT forecast_period_id,
period_number,
--ADD_MONTHS(start_date,12) start_date,
--ADD_MONTHS(end_date,12) end_date
start_date,
end_date
FROM ozf_forecast_periods
--WHERE obj_type = p_obj_type
WHERE obj_type = 'DISP'
AND obj_id = p_obj_id
AND (forecast_id IS NULL -- inanaiah: For compatibility with older release
OR forecast_id = p_forecast_id); -- inanaiah: making periods bound to forecast_id as is the case when creating new version
SELECT count(forecast_period_id)
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type
AND obj_id = p_id
AND (forecast_id IS NULL -- inanaiah: For compatibility with older release
OR forecast_id = p_forecast_id); -- inanaiah: making periods bound to forecast_id as is the case when creating new version
SELECT period_number
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type -- 'OFFR'
AND obj_id = p_id
AND (forecast_id IS NULL -- inanaiah: For compatibility with older release
OR forecast_id = p_forecast_id); -- inanaiah: making periods bound to forecast_id as is the case when creating new version
SELECT base_quantity_ref, offer_code
FROM ozf_act_forecasts_all
WHERE forecast_id = p_forecast_id;
SELECT qp_list_header_id --offer_id
FROM ozf_offers off
WHERE off.offer_code = p_offer_code;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_obj_id;
SELECT DECODE(qpl.list_line_type_code
,'DIS', DECODE(qpl.operand
,100 , DECODE(qpl.arithmetic_operator
,'%', 3
, 2 )
, 2)
, 1) promotion_type
FROM qp_list_lines qpl,
qp_pricing_attributes qp
WHERE qpl.list_header_id = p_obj_id
AND qpl.list_line_id = qp.list_line_id
AND qp.excluder_flag = 'N'
AND qp.product_attribute_context = l_product_attribute_context
AND qp.product_attribute = l_product_attribute
AND qp.product_attr_value = l_product_attr_value
ORDER BY promotion_type;
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL;
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id = p_previous_fact_id
AND root_fact_id IS NULL;
SELECT product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE forecast_dimention_id = p_forecast_dimention_id
AND forecast_id = p_forecast_id;
SELECT qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE forecast_dimention_id = p_forecast_dimention_id
AND forecast_id = p_forecast_id;
DELETE FROM ozf_act_metric_facts_all
WHERE activity_metric_id = p_activity_metric_id ;
UPDATE ozf_act_forecasts_all
SET dimention2 = NULL,
dimention3 = NULL
WHERE forecast_id = p_forecast_id ;
DELETE FROM ozf_act_metric_facts_all
WHERE activity_metric_id = p_activity_metric_id
AND previous_fact_id IS NOT NULL;
SELECT
PERIOD_LEVEL,
BASE_QUANTITY_TYPE,
BASE_QUANTITY_REF,
FORECAST_SPREAD_TYPE,
BASE_QUANTITY_START_DATE,
BASE_QUANTITY_END_DATE,
INCREMENT_QUOTA,
FORECAST_UOM_CODE,
LAST_SCENARIO_ID
FROM OZF_ACT_FORECASTS_ALL
WHERE FORECAST_ID = l_fcst_id;
SELECT
a.PERIOD_LEVEL,
a.BASE_QUANTITY_TYPE,
a.BASE_QUANTITY_REF,
a.FORECAST_SPREAD_TYPE,
trunc(b.start_date_active) wkst_start_date_active,
trunc(b.end_date_active) wkst_end_date_active,
a.INCREMENT_QUOTA,
a.forecast_uom_code,
a.LAST_SCENARIO_ID
FROM ozf_act_forecasts_all a,
ozf_worksheet_headers_b b
WHERE a.FORECAST_ID = p_forecast_id
AND b.worksheet_header_id = NVL(p_obj_id, b.worksheet_header_id)
AND a.arc_act_fcast_used_by = NVL(p_obj_type, 'WKST')
AND a.act_fcast_used_by_id = b.worksheet_header_id
AND b.forecast_generated = DECODE(p_obj_id, NULL, 'N',b.forecast_generated);
SELECT
offperf.channel_id
FROM
ozf_offer_performances offperf
WHERE
offperf.list_header_id = p_obj_id
AND offperf.product_attribute_context = p_product_attribute_context
AND DECODE(offperf.product_attr_value,'ALL','MATCH', offperf.product_attr_value) =
DECODE(offperf.product_attr_value,'ALL','MATCH', p_product_attr_value)
AND DECODE(offperf.product_attribute,'PRICING_ATTRIBUTE3','MATCH', offperf.product_attribute) =
DECODE(offperf.product_attribute,'PRICING_ATTRIBUTE3','MATCH', p_product_attribute)
AND p_report_date between NVL(offperf.start_date, p_report_date) and NVL (offperf.end_date, p_report_date);
SELECT
offperf.channel_id
FROM
ozf_offer_performances offperf
WHERE
offperf.used_by = p_obj_type
AND offperf.used_by = p_obj_id
AND offperf.product_attribute_context = p_product_attribute_context
AND DECODE(offperf.product_attr_value,'ALL','MATCH', offperf.product_attr_value) =
DECODE(offperf.product_attr_value,'ALL','MATCH', p_product_attr_value)
AND DECODE(offperf.product_attribute,'PRICING_ATTRIBUTE3','MATCH', offperf.product_attribute) =
DECODE(offperf.product_attribute,'PRICING_ATTRIBUTE3','MATCH', p_product_attribute)
AND p_report_date between NVL(offperf.start_date, p_report_date) and NVL (offperf.end_date, p_report_date);
SELECT
TRADE_TACTIC_COLUMN_NAME
FROM
OZF_TRADE_TACTICS_MAPPING
WHERE DATA_SOURCE = p_base_quantity_ref
AND TRADE_TACTIC_ID = l_trade_tactic_id
AND rownum = 1
ORDER BY CREATION_DATE DESC;
' SELECT MIN(LIFT.LIFT_FACTOR) ' ||
' FROM OZF_LIFT_FACTORS_FACTS LIFT, AMS_PARTY_MARKET_SEGMENTS DENORM ' ||
' WHERE LIFT.DATA_SOURCE = :L_BASE_QUANTITY_REF '||
' AND LIFT.MARKET_TYPE = DENORM.MARKET_QUALIFIER_TYPE '||
' AND LIFT.MARKET_ID = DENORM.MARKET_QUALIFIER_REFERENCE '||
' AND DENORM.SITE_USE_CODE = :L_MARKET_TYPE '||
' AND DENORM.SITE_USE_ID = :L_MARKET_ID '||
' AND LIFT.ITEM_LEVEL = ''PRICING_ATTRIBUTE1'' ' ||
' AND LIFT.ITEM_ID = :L_PRODUCT_ID '||
' AND LIFT.TPR_PERCENT <= :L_TPR_PERCENT '||
' AND :L_REPORT_DATE BETWEEN LIFT.TRANSACTION_FROM_DATE AND LIFT.TRANSACTION_TO_DATE ';
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_obj_id;
SELECT DECODE(qpl.list_line_type_code
,'DIS', DECODE(qpl.operand
,100 , DECODE(qpl.arithmetic_operator
,'%', 3
, 2 )
, 2)
, 1) promotion_type
FROM qp_list_lines qpl,
qp_pricing_attributes qp
WHERE qpl.list_header_id = p_obj_id
AND qpl.list_line_id = qp.list_line_id
AND qp.excluder_flag = 'N'
AND qp.product_attribute_context = l_product_attribute_context
AND qp.product_attribute = l_product_attribute
AND qp.product_attr_value = l_product_attr_value
ORDER BY promotion_type;
SELECT
PERIOD_LEVEL,
BASE_QUANTITY_TYPE,
BASE_QUANTITY_REF,
FORECAST_SPREAD_TYPE,
DIMENTION1,
DIMENTION2,
DIMENTION3,
BASE_QUANTITY_START_DATE,
BASE_QUANTITY_END_DATE,
INCREMENT_QUOTA,
FORECAST_UOM_CODE,
LAST_SCENARIO_ID
FROM OZF_ACT_FORECASTS_ALL
WHERE FORECAST_ID = l_fcst_id;
SELECT
a.PERIOD_LEVEL,
a.BASE_QUANTITY_TYPE,
a.BASE_QUANTITY_REF,
a.FORECAST_SPREAD_TYPE,
a.DIMENTION1,
a.DIMENTION2,
a.DIMENTION3,
trunc(b.start_date_active) wkst_start_date_active,
trunc(b.end_date_active) wkst_end_date_active,
a.INCREMENT_QUOTA,
a.forecast_uom_code,
a.LAST_SCENARIO_ID
FROM ozf_act_forecasts_all a,
ozf_worksheet_headers_b b
WHERE a.FORECAST_ID = p_forecast_id
AND b.worksheet_header_id = NVL(p_obj_id, b.worksheet_header_id)
AND a.arc_act_fcast_used_by = NVL(p_obj_type, 'WKST')
AND a.act_fcast_used_by_id = b.worksheet_header_id
AND b.forecast_generated = DECODE(p_obj_id, NULL, 'N',b.forecast_generated);
SELECT activity_metric_fact_id,
fact_reference,
fact_type,
incremental_sales
FROM ozf_act_metric_facts_all
WHERE activity_metric_fact_id = p_activity_metric_fact_id;
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id = p_activity_metric_fact_id
AND root_fact_id IS NULL;
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id = p_previous_fact_id
AND root_fact_id = p_activity_metric_fact_id;
SELECT product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE forecast_dimention_id = p_forecast_dimention_id
AND forecast_id = p_forecast_id;
SELECT qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE forecast_dimention_id = p_forecast_dimention_id
AND forecast_id = p_forecast_id;
SELECT min(forecast_dimention_id) forecast_dimention_id,
product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND qualifier_grouping_no = NVL(l_qualifier_grouping_no, qualifier_grouping_no)
GROUP BY
product_attribute_context,
product_attribute,
product_attr_value ;
SELECT min(forecast_dimention_id) forecast_dimention_id,
min(qualifier_grouping_no) qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND product_attribute_context = NVL(l_product_attribute_context, product_attribute_context)
AND product_attribute = NVL(l_product_attribute, product_attribute)
AND product_attr_value = NVL(l_product_attr_value, product_attr_value)
GROUP BY
qualifier_context,
qualifier_attribute,
qualifier_attr_value,
qualifier_grouping_no ;
SELECT period_number,
start_date,
end_date,
period_type_id
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id;
SELECT period_number
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type -- 'OFFR'
AND obj_id = p_obj_id
AND (forecast_id IS NULL -- inanaiah: For compatibility with older release
OR forecast_id = p_forecast_id); -- inanaiah: making periods bound to forecast_id as is the case when creating new version ;
UPDATE ozf_act_metric_facts_all outer
SET
outer.incremental_sales =
(
select
ROUND(NVL (SUM(sales.baseline_sales * NVL(OZF_FORECAST_UTIL_PVT.get_best_fit_lift
(
p_obj_type,
p_obj_id,
p_forecast_id,
l_base_quantity_ref,
sales.market_type,
sales.market_id,
dim.product_attribute_context,
dim.product_attribute,
dim.product_attr_value,
sales.item_id,
l_tpr_percent,
rpt.report_date
), 0)
) ,0) ) incremental_sales
from OZF_BASEline_sales_v sales,
ozf_time_day rpt,
(select cust.qualifier_grouping_no,
cust.cust_account_id,
cust.site_use_code site_use_code,
cust.site_use_id site_use_id,
prod.product_attribute_context,
prod.product_attribute,
prod.product_attr_value,
prod.product_id inventory_item_id
from ozf_forecast_customers cust,
ozf_forecast_products prod
where prod.obj_type = p_obj_type
and prod.obj_id = p_obj_id
and prod.obj_type = cust.obj_type
and prod.obj_id = cust.obj_id
and cust.site_use_code = 'SHIP_TO'
) cust_prod,
ozf_forecast_dimentions dim,
ozf_forecast_periods period
where dim.obj_type = p_obj_type
and dim.obj_id = p_obj_id
AND dim.forecast_id = p_forecast_id
and dim.product_attribute_context = cust_prod.product_attribute_context
and dim.product_attribute = cust_prod.product_attribute
and dim.product_attr_value = cust_prod.product_attr_value
and dim.qualifier_grouping_no = cust_prod.qualifier_grouping_no
and cust_prod.site_use_code = sales.market_type
and cust_prod.site_use_id = sales.market_id
and sales.item_level = 'PRICING_ATTRIBUTE1'
and sales.item_id = cust_prod.inventory_item_id
and period.obj_type = 'DISP'
and period.obj_id = p_obj_id
and period.forecast_id = p_forecast_id
and rpt.report_date between period.start_date and period.end_date
and rpt.report_date_julian = sales.time_id
and sales.period_type_id = 1
and dim.product_attribute_context = l_product_attribute_context
and dim.product_attribute = l_product_attribute
and dim.product_attr_value = l_product_attr_value
and dim.qualifier_grouping_no = l_qualifier_grouping_no
and period.forecast_period_id = l_period_number
),
outer.forecast_remaining_quantity = 0
WHERE outer.activity_metric_fact_id = k.activity_metric_fact_id
AND outer.arc_act_metric_used_by = 'FCST'
AND outer.act_metric_used_by_id = p_forecast_id;
UPDATE ozf_act_metric_facts_all outer
SET (outer.baseline_sales, outer.incremental_sales) =
( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
FROM ozf_act_metric_facts_all inner
WHERE inner.previous_fact_id = outer.activity_metric_fact_id
AND inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.fact_type = l_dimention3),
outer.forecast_remaining_quantity = 0
WHERE
outer.arc_act_metric_used_by = 'FCST'
AND outer.act_metric_used_by_id = p_forecast_id
AND outer.fact_type = l_dimention2
AND outer.previous_fact_id = p_activity_metric_fact_id;
UPDATE ozf_act_metric_facts_all outer
SET (outer.baseline_sales, outer.incremental_sales) =
( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
FROM ozf_act_metric_facts_all inner
WHERE inner.previous_fact_id = outer.activity_metric_fact_id
AND inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.fact_type = l_dimention2),
outer.tpr_percent = l_tpr_percent,
outer.forecast_remaining_quantity = 0
WHERE
outer.arc_act_metric_used_by = 'FCST'
AND outer.act_metric_used_by_id = p_forecast_id
AND outer.fact_type = l_dimention1
AND outer.activity_metric_fact_id = p_activity_metric_fact_id;
SELECT NVL(inner.incremental_sales,0) INTO l_new_incremental_sales
FROM ozf_act_metric_facts_all inner
WHERE inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.activity_metric_fact_id = p_activity_metric_fact_id;
UPDATE ozf_act_forecasts_all outer
SET outer.forecast_quantity = outer.forecast_quantity + (l_new_incremental_sales - l_orig_incremental_sales)
WHERE outer.forecast_id = p_forecast_id;
SELECT NVL(SUM(inner.incremental_sales), 0) INTO l_new_incremental_sales
FROM ozf_act_metric_facts_all inner
WHERE inner.previous_fact_id = p_activity_metric_fact_id
AND inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.fact_type = l_dimention2;
SELECT
a.PERIOD_LEVEL,
a.BASE_QUANTITY_TYPE,
a.BASE_QUANTITY_REF,
a.FORECAST_SPREAD_TYPE,
a.DIMENTION1,
a.DIMENTION2,
a.DIMENTION3,
a.BASE_QUANTITY_START_DATE,
a.BASE_QUANTITY_END_DATE,
a.INCREMENT_QUOTA,
a.FORECAST_UOM_CODE,
a.LAST_SCENARIO_ID,
b.transaction_currency_code
FROM OZF_ACT_FORECASTS_ALL a, ozf_offers b
WHERE a.FORECAST_ID = p_forecast_id
AND a.arc_act_fcast_used_by = p_obj_type
AND a.act_fcast_used_by_id = p_obj_id
AND b.qp_list_header_id = a.act_fcast_used_by_id;
SELECT
a.PERIOD_LEVEL,
a.BASE_QUANTITY_TYPE,
a.BASE_QUANTITY_REF,
a.FORECAST_SPREAD_TYPE,
a.DIMENTION1,
a.DIMENTION2,
a.DIMENTION3,
trunc(b.start_date_active) wkst_start_date_active,
trunc(b.end_date_active) wkst_end_date_active,
a.INCREMENT_QUOTA,
a.forecast_uom_code,
a.LAST_SCENARIO_ID,
b.currency_code
FROM ozf_act_forecasts_all a,
ozf_worksheet_headers_b b
WHERE a.FORECAST_ID = p_forecast_id
AND b.worksheet_header_id = NVL(p_obj_id, b.worksheet_header_id)
AND a.arc_act_fcast_used_by = NVL(p_obj_type, 'WKST')
AND a.act_fcast_used_by_id = b.worksheet_header_id
AND b.forecast_generated = DECODE(p_obj_id, NULL, 'N',b.forecast_generated);
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL;
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id = p_previous_fact_id
AND root_fact_id IS NULL;
SELECT activity_metric_fact_id,
fact_reference,
fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id = p_previous_fact_id
AND root_fact_id = p_root_fact_id;
SELECT product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE forecast_dimention_id = p_forecast_dimention_id
AND forecast_id = p_forecast_id;
SELECT qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE forecast_dimention_id = p_forecast_dimention_id
AND forecast_id = p_forecast_id;
SELECT min(forecast_dimention_id) forecast_dimention_id,
product_attribute_context,
product_attribute,
product_attr_value
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND qualifier_grouping_no = NVL(l_qualifier_grouping_no, qualifier_grouping_no)
GROUP BY
product_attribute_context,
product_attribute,
product_attr_value ;
SELECT min(forecast_dimention_id) forecast_dimention_id,
min(qualifier_grouping_no) qualifier_grouping_no
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND product_attribute_context = NVL(l_product_attribute_context, product_attribute_context)
AND product_attribute = NVL(l_product_attribute, product_attribute)
AND product_attr_value = NVL(l_product_attr_value, product_attr_value)
GROUP BY
qualifier_context,
qualifier_attribute,
qualifier_attr_value,
qualifier_grouping_no ;
SELECT period_number,
start_date,
end_date,
period_type_id
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id;
SELECT period_number
FROM ozf_forecast_periods
WHERE obj_type = p_obj_type -- 'OFFR'
AND obj_id = p_obj_id
AND (forecast_id IS NULL -- inanaiah: For compatibility with older release
OR forecast_id = p_forecast_id); -- inanaiah: making periods bound to forecast_id as is the case when creating new version ;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_obj_id;
SELECT DECODE(qpl.list_line_type_code
,'DIS', DECODE(qpl.operand
,100 , DECODE(qpl.arithmetic_operator
,'%', 3
, 2 )
, 2)
, 1) promotion_type
FROM qp_list_lines qpl,
qp_pricing_attributes qp
WHERE qpl.list_header_id = p_obj_id
AND qpl.list_line_id = qp.list_line_id
AND qp.excluder_flag = 'N'
AND qp.product_attribute_context = l_product_attribute_context
AND qp.product_attribute = l_product_attribute
AND qp.product_attr_value = l_product_attr_value
ORDER BY promotion_type;
UPDATE ozf_act_metric_facts_all prod_fact
SET tpr_percent = NVL(l_tpr_percent,0)
WHERE prod_fact.activity_metric_fact_id = i.activity_metric_fact_id
AND prod_fact.arc_act_metric_used_by = 'FCST'
AND prod_fact.act_metric_used_by_id = p_forecast_id;
UPDATE ozf_act_metric_facts_all outer
SET (outer.baseline_sales, outer.incremental_sales) =
(
select
ROUND(NVL (SUM(sales.baseline_sales) ,0) ) baseline_sales,
DECODE(l_tpr_percent, NULL, 0, ROUND(NVL (SUM(sales.baseline_sales * NVL(OZF_FORECAST_UTIL_PVT.get_best_fit_lift
(
p_obj_type,
p_obj_id,
p_forecast_id,
l_base_quantity_ref,
sales.market_type,
sales.market_id,
dim.product_attribute_context,
dim.product_attribute,
dim.product_attr_value,
sales.item_id,
l_tpr_percent,
rpt.report_date
), 0)
) ,0) ) )incremental_sales
from OZF_BASEline_sales_v sales,
ozf_time_day rpt,
(select cust.qualifier_grouping_no,
cust.cust_account_id,
cust.site_use_code site_use_code,
cust.site_use_id site_use_id,
prod.product_attribute_context,
prod.product_attribute,
prod.product_attr_value,
prod.product_id inventory_item_id
from ozf_forecast_customers cust,
ozf_forecast_products prod
where prod.obj_type = p_obj_type
and prod.obj_id = p_obj_id
and prod.obj_type = cust.obj_type
and prod.obj_id = cust.obj_id
and cust.site_use_code = 'SHIP_TO'
) cust_prod,
ozf_forecast_dimentions dim,
ozf_forecast_periods period
where dim.obj_type = p_obj_type
and dim.obj_id = p_obj_id
AND dim.forecast_id = p_forecast_id
and dim.product_attribute_context = cust_prod.product_attribute_context
and dim.product_attribute = cust_prod.product_attribute
and dim.product_attr_value = cust_prod.product_attr_value
and dim.qualifier_grouping_no = cust_prod.qualifier_grouping_no
and cust_prod.site_use_code = sales.market_type
and cust_prod.site_use_id = sales.market_id
and sales.item_level = 'PRICING_ATTRIBUTE1'
and sales.item_id = cust_prod.inventory_item_id
and period.obj_type = 'DISP'
and period.obj_id = p_obj_id
and period.forecast_id = p_forecast_id
and rpt.report_date between period.start_date and period.end_date
and rpt.report_date_julian = sales.time_id
and sales.period_type_id = 1
and dim.product_attribute_context = l_product_attribute_context
and dim.product_attribute = l_product_attribute
and dim.product_attr_value = l_product_attr_value
and dim.qualifier_grouping_no = l_qualifier_grouping_no
and period.forecast_period_id = l_period_number
),
outer.forecast_remaining_quantity = 0
WHERE outer.activity_metric_fact_id = k.activity_metric_fact_id
AND outer.arc_act_metric_used_by = 'FCST'
AND outer.act_metric_used_by_id = p_forecast_id;
UPDATE ozf_act_metric_facts_all outer2
SET outer2.baseline_sales=0
WHERE outer2.activity_metric_fact_id = k.activity_metric_fact_id
AND outer2.arc_act_metric_used_by = 'FCST'
AND outer2.act_metric_used_by_id = p_forecast_id
AND outer2.baseline_sales IS NULL;
UPDATE ozf_act_metric_facts_all outer3
SET outer3.incremental_sales=0
WHERE outer3.activity_metric_fact_id = k.activity_metric_fact_id
AND outer3.arc_act_metric_used_by = 'FCST'
AND outer3.act_metric_used_by_id = p_forecast_id
AND outer3.incremental_sales IS NULL;
UPDATE ozf_act_metric_facts_all outer
SET (outer.baseline_sales, outer.incremental_sales) =
( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
FROM ozf_act_metric_facts_all inner
WHERE inner.previous_fact_id = outer.activity_metric_fact_id
AND inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.fact_type = l_dimention3),
outer.forecast_remaining_quantity = 0
WHERE
outer.arc_act_metric_used_by = 'FCST'
AND outer.act_metric_used_by_id = p_forecast_id
AND outer.fact_type = l_dimention2;
UPDATE ozf_act_metric_facts_all outer
SET (outer.baseline_sales, outer.incremental_sales) =
( SELECT NVL(SUM(inner.baseline_sales),0),NVL(SUM(inner.incremental_sales),0)
FROM ozf_act_metric_facts_all inner
WHERE inner.previous_fact_id = outer.activity_metric_fact_id
AND inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.fact_type = l_dimention2),
outer.forecast_remaining_quantity = 0
WHERE
outer.arc_act_metric_used_by = 'FCST'
AND outer.act_metric_used_by_id = p_forecast_id
AND outer.fact_type = l_dimention1;
UPDATE ozf_act_forecasts_all outer
SET (outer.forecast_quantity, outer.base_quantity) =
( SELECT (NVL(SUM(inner.baseline_sales),0) + NVL(SUM(inner.incremental_sales),0)) total_forecast,
NVL(SUM(inner.baseline_sales),0) baseline_sales
FROM ozf_act_metric_facts_all inner
WHERE inner.arc_act_metric_used_by = 'FCST'
AND inner.act_metric_used_by_id = p_forecast_id
AND inner.fact_type = l_dimention1),
outer.dimention1 = l_dimention1,
outer.dimention2 = l_dimention2,
outer.dimention3 = l_dimention3,
outer.period_level = l_period_level,
outer.forecast_remaining_quantity = 0
--last_scenario_id = NVL(last_scenario_id,0)+1
WHERE outer.forecast_id = p_forecast_id;
SELECT b.worksheet_header_id,
trunc(b.start_date_active) wkst_start_date_active,
trunc(b.end_date_active) wkst_end_date_active,
a.forecast_id ,
a.period_level, /* DAY, WEEK, MONTH, QTR */
a.forecast_uom_code,
a.base_quantity_type, /* LAST_YEAR_SAME_PERIOD, OFFER_CODE, CUSTOM_DATE_RANGE */
a.base_quantity_start_date,
a.base_quantity_end_date,
a.base_quantity_ref /* OFFER_CODE or BASELINE SOURCE */
FROM ozf_act_forecasts_all a,
ozf_worksheet_headers_b b
WHERE b.worksheet_header_id = NVL(p_worksheet_header_id, b.worksheet_header_id)
AND a.arc_act_fcast_used_by = 'WKST'
AND a.act_fcast_used_by_id = b.worksheet_header_id
AND b.forecast_generated = DECODE(p_worksheet_header_id, NULL, 'N',b.forecast_generated);
SELECT NVL(qp.start_date_active,trunc(SYSDATE)) start_date_active,
NVL(qp.end_date_active, trunc(SYSDATE)) end_date_active
FROM qp_list_headers_b qp,
ozf_offers off
WHERE off.offer_code = p_offer_code
AND off.qp_list_header_id = qp.list_header_id ;
UPDATE ozf_act_forecasts_all
SET base_quantity = ( SELECT NVL(SUM(base_quantity),0)
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = l_forecast_id
AND fact_type = 'PRODUCT') ,
dimention1 = 'PRODUCT',
dimention2 = 'TIME'
WHERE forecast_id = l_forecast_id;
UPDATE ozf_worksheet_headers_b
SET forecast_generated = 'Y'
WHERE worksheet_header_id = l_worksheet_header_id;
SELECT forecast_spread_type,
period_level,
forecast_uom_code,
base_quantity_type,
base_quantity_start_date,
base_quantity_end_date,
base_quantity_ref, /* Third party*/
last_scenario_id,
offer_code
FROM ozf_act_forecasts_all
WHERE forecast_id = l_fcst_id;
SELECT NVL(qp.start_date_active,trunc(SYSDATE)) start_date_active,
NVL(qp.end_date_active, trunc(SYSDATE)) end_date_active
FROM qp_list_headers_b qp,
ozf_offers off
WHERE off.offer_code = p_offer_code
AND off.qp_list_header_id = qp.list_header_id ;
SELECT qp_list_header_id --offer_id
FROM ozf_offers off
WHERE off.offer_code = p_offer_code;
SELECT activity_metric_id
FROM ozf_act_metrics_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id;
SELECT count(*) INTO l_new_count FROM (
SELECT product_attribute_context, product_attribute, product_attr_value,
qualifier_grouping_no, qualifier_context, qualifier_attribute,
qualifier_attr_value
FROM ozf_forecast_dimentions
WHERE obj_id = p_obj_id
AND obj_type = p_obj_type
AND forecast_id = l_forecast_id);
SELECT count(*) INTO l_common_count FROM (
SELECT product_attribute_context, product_attribute, product_attr_value,
qualifier_grouping_no, qualifier_context, qualifier_attribute,
qualifier_attr_value
FROM ozf_forecast_dimentions
WHERE obj_id = p_obj_id
AND obj_type = p_obj_type
AND forecast_id = l_forecast_id
INTERSECT
SELECT product_attribute_context, product_attribute, product_attr_value,
qualifier_grouping_no, qualifier_context, qualifier_attribute,
qualifier_attr_value
FROM ozf_forecast_dimentions
WHERE obj_id = l_offer_id
AND obj_type = l_disp_type
AND forecast_id = l_forecast_id);
UPDATE ozf_act_forecasts_all
SET base_quantity = ROUND(NVL(l_base_sales,0)),
dimention1 = p_dimention,
base_quantity_ref = x_fcst_return_rec.spread_count -- base_quantity_ref used in OFFER_CODE basis
WHERE forecast_id = l_forecast_id;
UPDATE ozf_act_forecasts_all
SET base_quantity = ROUND(NVL(l_base_sales,0))
WHERE forecast_id = p_forecast_id;
SELECT activity_metric_fact_id, fact_value
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
and nvl(node_id,1) <> 3 ;
SELECT activity_metric_fact_id, fact_value
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND root_fact_id IS NULL
AND previous_fact_id = prev_fact_id
and nvl(node_id,1) <> 3 ;
SELECT activity_metric_fact_id, fact_value
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND root_fact_id IS NOT NULL
AND previous_fact_id = prev_fact_id
and nvl(node_id,1) <> 3;
UPDATE ozf_act_metric_facts_all
SET FORECAST_REMAINING_QUANTITY = record_l_two.fact_value - l_three_f_quan
WHERE activity_metric_fact_id = record_l_two.activity_metric_fact_id;
UPDATE ozf_act_metric_facts_all
SET FORECAST_REMAINING_QUANTITY = record_l_one.fact_value - l_two_f_quan
WHERE activity_metric_fact_id = record_l_one.activity_metric_fact_id;
UPDATE ozf_act_forecasts_all
SET FORECAST_REMAINING_QUANTITY = forecast_quantity - l_one_f_quan
WHERE forecast_id = p_forecast_id;
SELECT activity_metric_fact_id, incremental_sales
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
and nvl(node_id,1) <> 3 ;
SELECT activity_metric_fact_id, incremental_sales
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND root_fact_id IS NULL
AND previous_fact_id = prev_fact_id
and nvl(node_id,1) <> 3 ;
SELECT activity_metric_fact_id, incremental_sales
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND root_fact_id IS NOT NULL
AND previous_fact_id = prev_fact_id
and nvl(node_id,1) <> 3;
UPDATE ozf_act_metric_facts_all
SET FORECAST_REMAINING_QUANTITY = record_l_two.incremental_sales - l_three_f_quan
WHERE activity_metric_fact_id = record_l_two.activity_metric_fact_id;
UPDATE ozf_act_metric_facts_all
SET FORECAST_REMAINING_QUANTITY = record_l_one.incremental_sales - l_two_f_quan
WHERE activity_metric_fact_id = record_l_one.activity_metric_fact_id;
UPDATE ozf_act_forecasts_all
SET FORECAST_REMAINING_QUANTITY = forecast_quantity - base_quantity - l_one_f_quan
WHERE forecast_id = p_forecast_id;
SELECT forecast_remaining_quantity
FROM ozf_act_forecasts_all
WHERE forecast_id = p_fcast_id;
SELECT activity_metric_fact_id, fact_type, forecast_remaining_quantity
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL;
SELECT activity_metric_fact_id, fact_type, forecast_remaining_quantity
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NOT NULL
AND root_fact_id IS NULL
AND previous_fact_id = p_prev_id;
SELECT ozf_act_forecasts_all_s.NEXTVAL
FROM dual;
SELECT ozf_act_metrics_all_s.NEXTVAL
FROM dual;
SELECT activity_metric_id
FROM ozf_act_metrics_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id;
SELECT activity_metric_fact_id,
fact_type,
base_quantity,
fact_reference,
from_date,
to_date,
fact_value,
fact_percent,
previous_fact_id,
root_fact_id,
forecast_remaining_quantity,
forward_buy_quantity,
node_id
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_id = p_activity_metric_id
AND root_fact_id IS NULL
AND previous_fact_id IS NULL;
SELECT activity_metric_fact_id,
fact_type,
base_quantity,
fact_reference,
from_date,
to_date,
fact_value,
fact_percent,
previous_fact_id,
root_fact_id,
forecast_remaining_quantity,
forward_buy_quantity,
node_id
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_id = p_activity_metric_id
AND root_fact_id IS NULL
AND previous_fact_id IS NOT NULL
AND previous_fact_id = p_previous_fact_id;
SELECT activity_metric_fact_id,
fact_type,
base_quantity,
fact_reference,
from_date,
to_date,
fact_value,
fact_percent,
previous_fact_id,
root_fact_id,
forecast_remaining_quantity,
forward_buy_quantity,
node_id
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_id = p_activity_metric_id
AND previous_fact_id = p_previous_fact_id
AND root_fact_id = p_root_fact_id;
INSERT INTO ozf_act_forecasts_all
(forecast_id
,forecast_type
,arc_act_fcast_used_by
,act_fcast_used_by_id
,creation_date
,created_from
,created_by
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,object_version_number
,hierarchy
,hierarchy_level
,level_value
,forecast_calendar
,period_level
,forecast_period_id
,forecast_date
,forecast_uom_code
,forecast_quantity
,forward_buy_quantity
,forward_buy_period
,base_quantity
,context
,attribute_category
,org_id
,forecast_remaining_quantity
,forecast_remaining_percent
,base_quantity_type
,forecast_spread_type
,dimention1
,dimention2
,dimention3
,last_scenario_id
,freeze_flag
,price_list_id
,base_quantity_start_date
,base_quantity_end_date
,base_quantity_ref
,offer_code
)
SELECT l_forecast_id
,a.forecast_type
,a.arc_act_fcast_used_by
,a.act_fcast_used_by_id
,SYSDATE
,a.created_from
,FND_GLOBAL.User_ID
,SYSDATE
,FND_GLOBAL.User_ID
,FND_GLOBAL.Conc_Login_ID
,a.program_application_id
,a.program_id
,a.program_update_date
,a.request_id
,1 --object_version_number
,a.hierarchy
,a.hierarchy_level
,a.level_value
,a.forecast_calendar
,a.period_level
,a.forecast_period_id
,a.forecast_date
,a.forecast_uom_code
,a.forecast_quantity
,a.forward_buy_quantity
,a.forward_buy_period
,a.base_quantity
,a.context
,a.attribute_category
,MO_GLOBAL.GET_CURRENT_ORG_ID()-- org_id
,a.forecast_remaining_quantity
,a.forecast_remaining_percent
,a.base_quantity_type
,a.forecast_spread_type
,a.dimention1
,a.dimention2
,a.dimention3
,a.last_scenario_id + 1
,'N'
,a.price_list_id
,a.base_quantity_start_date
,a.base_quantity_end_date
,a.base_quantity_ref
,a.offer_code
FROM ozf_act_forecasts_all a
WHERE forecast_id = p_forecast_id;
INSERT INTO ozf_act_metrics_all (
activity_metric_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
act_metric_used_by_id,
arc_act_metric_used_by,
purchase_req_raised_flag,
application_id,
sensitive_data_flag,
budget_id,
metric_id,
transaction_currency_code,
trans_forecasted_value,
trans_committed_value,
trans_actual_value,
functional_currency_code,
func_forecasted_value,
dirty_flag,
func_committed_value,
func_actual_value,
last_calculated_date,
variable_value,
computed_using_function_value,
metric_uom_code,
org_id,
attribute_category,
difference_since_last_calc,
activity_metric_origin_id,
arc_activity_metric_origin,
days_since_last_refresh,
scenario_id,
SUMMARIZE_TO_METRIC,
hierarchy_id,
start_node,
from_level,
to_level,
from_date,
TO_DATE,
amount1,
amount2,
amount3,
percent1,
percent2,
percent3,
published_flag,
pre_function_name,
post_function_name,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
description,
act_metric_date,
depend_act_metric
)
SELECT l_activity_metric_id,
SYSDATE,
Fnd_Global.User_ID,
SYSDATE,
Fnd_Global.User_ID,
Fnd_Global.Conc_Login_ID,
1, --Object Version Number
l_forecast_id,
b.arc_act_metric_used_by,
NVL(b.purchase_req_raised_flag,'N'),
b.application_id,
b.sensitive_data_flag,
b.budget_id,
b.metric_id,
b.transaction_currency_code,
b.trans_forecasted_value,
b.trans_committed_value,
b.trans_actual_value,
b.functional_currency_code,
b.func_forecasted_value,
NVL(b.dirty_flag,'Y'),
b.func_committed_value,
b.func_actual_value,
b.last_calculated_date,
b.variable_value,
b.computed_using_function_value,
b.metric_uom_code,
MO_GLOBAL.GET_CURRENT_ORG_ID() , -- org_id
b.attribute_category,
b.difference_since_last_calc,
b.activity_metric_origin_id,
b.arc_activity_metric_origin,
b.days_since_last_refresh,
b.scenario_id,
b.SUMMARIZE_TO_METRIC,
b.hierarchy_id,
b.start_node,
b.from_level,
b.to_level,
b.from_date,
b.TO_DATE,
b.amount1,
b.amount2,
b.amount3,
b.percent1,
b.percent2,
b.percent3,
b.published_flag,
b.pre_function_name,
b.post_function_name,
b.attribute1,
b.attribute2,
b.attribute3,
b.attribute4,
b.attribute5,
b.attribute6,
b.attribute7,
b.attribute8,
b.attribute9,
b.attribute10,
b.attribute11,
b.attribute12,
b.attribute13,
b.attribute14,
b.attribute15,
b.description,
b.act_metric_date,
b.depend_act_metric
FROM ozf_act_metrics_all b
WHERE act_metric_used_by_id = p_forecast_id
AND arc_act_metric_used_by = 'FCST';
SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_1 FROM dual;
INSERT INTO ozf_act_metric_facts_all (
ACTIVITY_METRIC_FACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER,
ACT_METRIC_USED_BY_ID,
ARC_ACT_METRIC_USED_BY,
VALUE_TYPE,
ACTIVITY_METRIC_ID,
TRANS_FORECASTED_VALUE,
FUNCTIONAL_CURRENCY_CODE,
FUNC_FORECASTED_VALUE,
ORG_ID,
DE_METRIC_ID,
TIME_ID1,
FROM_DATE,
TO_DATE,
FACT_VALUE,
FACT_PERCENT,
BASE_QUANTITY,
ROOT_FACT_ID,
PREVIOUS_FACT_ID,
FACT_TYPE,
FACT_REFERENCE,
last_update_login,
FORECAST_REMAINING_QUANTITY,
FORWARD_BUY_QUANTITY,
NODE_ID
)
VALUES ( l_act_metric_fact_id_level_1,
SYSDATE,
Fnd_Global.User_ID,
SYSDATE,
Fnd_Global.User_ID,
1,
l_forecast_id,
'FCST',
'NUMERIC',
l_activity_metric_id,
0,
'NONE',
0,
MO_GLOBAL.GET_CURRENT_ORG_ID(),
0,
0,
level_one_fact_record.from_date,
level_one_fact_record.to_date,
level_one_fact_record.fact_value,
level_one_fact_record.fact_percent,
NVL(level_one_fact_record.base_quantity,0),
level_one_fact_record.root_fact_id, -- will be NULL
level_one_fact_record.previous_fact_id, -- will be NULL
level_one_fact_record.fact_type,
level_one_fact_record.fact_reference,
fnd_global.login_id,
level_one_fact_record.forecast_remaining_quantity,
level_one_fact_record.forward_buy_quantity,
level_one_fact_record.node_id
);
SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_2 FROM dual;
INSERT INTO ozf_act_metric_facts_all (
ACTIVITY_METRIC_FACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER,
ACT_METRIC_USED_BY_ID,
ARC_ACT_METRIC_USED_BY,
VALUE_TYPE,
ACTIVITY_METRIC_ID,
TRANS_FORECASTED_VALUE,
FUNCTIONAL_CURRENCY_CODE,
FUNC_FORECASTED_VALUE,
ORG_ID,
DE_METRIC_ID,
TIME_ID1,
FROM_DATE,
TO_DATE,
FACT_VALUE,
FACT_PERCENT,
BASE_QUANTITY,
ROOT_FACT_ID,
PREVIOUS_FACT_ID,
FACT_TYPE,
FACT_REFERENCE,
last_update_login,
FORECAST_REMAINING_QUANTITY,
FORWARD_BUY_QUANTITY,
NODE_ID
)
VALUES ( l_act_metric_fact_id_level_2,
SYSDATE,
Fnd_Global.User_ID,
SYSDATE,
Fnd_Global.User_ID,
1,
l_forecast_id,
'FCST',
'NUMERIC',
l_activity_metric_id,
0,
'NONE',
0,
MO_GLOBAL.GET_CURRENT_ORG_ID(),
0,
0,
level_two_fact_record.from_date,
level_two_fact_record.to_date,
level_two_fact_record.fact_value,
level_two_fact_record.fact_percent,
NVL(level_two_fact_record.base_quantity,0),
level_two_fact_record.root_fact_id, -- will be NULL
l_act_metric_fact_id_level_1, -- newly generated Level One activity_metric_fact_id
level_two_fact_record.fact_type,
level_two_fact_record.fact_reference,
fnd_global.login_id,
level_two_fact_record.forecast_remaining_quantity,
level_two_fact_record.forward_buy_quantity,
level_two_fact_record.node_id
);
SELECT ozf_act_metric_facts_all_s.nextval INTO l_act_metric_fact_id_level_3 FROM dual;
INSERT INTO ozf_act_metric_facts_all (
ACTIVITY_METRIC_FACT_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
OBJECT_VERSION_NUMBER,
ACT_METRIC_USED_BY_ID,
ARC_ACT_METRIC_USED_BY,
VALUE_TYPE,
ACTIVITY_METRIC_ID,
TRANS_FORECASTED_VALUE,
FUNCTIONAL_CURRENCY_CODE,
FUNC_FORECASTED_VALUE,
ORG_ID,
DE_METRIC_ID,
TIME_ID1,
FROM_DATE,
TO_DATE,
FACT_VALUE,
FACT_PERCENT,
BASE_QUANTITY,
ROOT_FACT_ID,
PREVIOUS_FACT_ID,
FACT_TYPE,
FACT_REFERENCE,
last_update_login,
FORECAST_REMAINING_QUANTITY,
FORWARD_BUY_QUANTITY,
NODE_ID
)
VALUES ( l_act_metric_fact_id_level_3,
SYSDATE,
Fnd_Global.User_ID,
SYSDATE,
Fnd_Global.User_ID,
1,
l_forecast_id,
'FCST',
'NUMERIC',
l_activity_metric_id,
0,
'NONE',
0,
MO_GLOBAL.GET_CURRENT_ORG_ID(),
0,
0,
level_three_fact_record.from_date,
level_three_fact_record.to_date,
level_three_fact_record.fact_value,
level_three_fact_record.fact_percent,
NVL(level_three_fact_record.base_quantity,0),
l_act_metric_fact_id_level_1, -- newly generated Level One activity_metric_fact_id
l_act_metric_fact_id_level_2, -- newly generated Level Two activity_metric_fact_id
level_three_fact_record.fact_type,
level_three_fact_record.fact_reference,
fnd_global.login_id,
level_three_fact_record.forecast_remaining_quantity,
level_three_fact_record.forward_buy_quantity,
level_three_fact_record.node_id
);
PROCEDURE cascade_update(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_id IN NUMBER,
p_value IN NUMBER,
p_fwd_buy_value IN NUMBER,
p_fcast_id IN NUMBER,
p_cascade_flag IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_fact_percent NUMBER := 0;
SELECT activity_metric_fact_id,
previous_fact_id,
forecast_remaining_quantity,
fact_type,
fact_reference,
from_date,
to_date,
fact_value,
fact_percent,
root_fact_id,
forward_buy_quantity
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = p_prev_id
order by 6; -- added this on 04/09/02 for Forward Buy calculations for TIME
SELECT activity_metric_fact_id,
previous_fact_id,
fact_type,
fact_reference,
from_date,
to_date,
fact_value,
fact_percent,
root_fact_id,
forward_buy_quantity
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = p_prev_id
AND root_fact_id IS NOT NULL
order by 6; -- added this on 04/09/02 for Forward Buy calculations for TIME
l_api_name CONSTANT VARCHAR2(30) := 'Cascade_Update';
SELECT fact_value, forward_buy_quantity
INTO l_parent_fact_value, l_parent_fwd_buy_qty
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_fact_id = p_id;
SELECT count(*), sum(forward_buy_quantity)
INTO l_temp_count, l_fwd_buy_sum_all_recs
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_record.previous_fact_id;
SELECT forecast_remaining_quantity INTO l_fcst_remaining_quantity
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_fact_id = facts_record.previous_fact_id;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = round(l_forward_buy_quantity)
WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
SELECT NVL(sum(fact_value),0), NVL(sum(forward_buy_quantity),0)
INTO l_fval_sum_minus_last_rec, l_fwdbuy_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_record.previous_fact_id
AND activity_metric_fact_id <> facts_record.activity_metric_fact_id ;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = l_forward_buy_quantity
WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
SELECT count(*), sum(forward_buy_quantity)
INTO l_temp_sub_count, l_fwd_buy_sum_all_sub_recs
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_subrecord.previous_fact_id
AND root_fact_id IS NOT NULL;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = round(l_forward_buy_quantity)
WHERE activity_metric_fact_id = facts_subrecord.activity_metric_fact_id;
SELECT NVL(sum(fact_value),0), NVL(sum(forward_buy_quantity),0)
INTO l_fval_sum_minus_last_rec, l_fwdbuy_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_subrecord.previous_fact_id
AND root_fact_id IS NOT NULL
AND activity_metric_fact_id <> facts_subrecord.activity_metric_fact_id ;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = l_forward_buy_quantity
WHERE activity_metric_fact_id = facts_subrecord.activity_metric_fact_id;
END cascade_update;
SELECT activity_metric_fact_id,
previous_fact_id,
forecast_remaining_quantity,
fact_type,
fact_reference,
from_date,
to_date,
fact_value,
fact_percent,
root_fact_id,
forward_buy_quantity
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
order by 6; --changed on 04/09/02
SELECT forecast_quantity, forward_buy_quantity, forecast_remaining_quantity
INTO l_parent_fact_value, l_parent_fwd_buy_qty, l_fcst_remaining_quantity
FROM ozf_act_forecasts_all
WHERE forecast_id = p_fcast_id;
SELECT forecast_remaining_quantity
INTO l_fcst_remaining_quantity
FROM ozf_act_forecasts_all
WHERE forecast_id = p_fcast_id;
SELECT count(*), sum(forward_buy_quantity)
INTO l_temp_count, l_fwd_buy_sum_all_recs
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = round(l_forward_buy_quantity)
WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
cascade_update(p_api_version,
p_init_msg_list,
p_commit,
facts_record.activity_metric_fact_id,
l_fact_value,
round(l_forward_buy_quantity),
p_fcast_id,
l_cascade_flag,
x_return_status,
x_msg_count,
x_msg_data
);
SELECT NVL(sum(fact_value),0), NVL(sum(forward_buy_quantity),0)
INTO l_fval_sum_minus_last_rec, l_fwdbuy_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
AND activity_metric_fact_id <> facts_record.activity_metric_fact_id ;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = l_forward_buy_quantity
WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
cascade_update(p_api_version,
p_init_msg_list,
p_commit,
facts_record.activity_metric_fact_id,
l_fact_value,
round(l_forward_buy_quantity),
p_fcast_id,
l_cascade_flag,
x_return_status,
x_msg_count,
x_msg_data
);
SELECT a.activity_metric_fact_id,
a.previous_fact_id,
a.base_quantity level_one_bq,
a.fact_type level_one_fact_type,
a.fact_reference,
a.from_date,
a.to_date,
a.fact_value,
a.fact_percent,
a.root_fact_id,
b.base_quantity overall_base_quantity,
b.forecast_quantity overall_forecast_quantity,
b.forward_buy_quantity overall_fwd_buy_qty
FROM ozf_act_metric_facts_all a, ozf_act_forecasts_all b
WHERE a.arc_act_metric_used_by = 'FCST'
AND a.act_metric_used_by_id = p_used_by_id
AND a.previous_fact_id IS NULL
AND b.forecast_id = a.act_metric_used_by_id
and nvl(a.node_id,1) <> 3
order by 6;
SELECT f1.base_quantity level_one_bq,
f1.fact_value level_one_fact_val,
f.activity_metric_fact_id level_two_fact_id,
f.previous_fact_id,
f.base_quantity level_two_bq,
f.fact_type level_two_fact_type,
f.fact_reference,
f.from_date,
f.to_date,
f.fact_value,
f.fact_percent,
f.root_fact_id,
f1.forward_buy_quantity level_one_fwd_buy_qty
FROM ozf_act_metric_facts_all f, ozf_act_metric_facts_all f1
WHERE f.arc_act_metric_used_by = 'FCST'
AND f.act_metric_used_by_id = p_used_by_id
AND f.previous_fact_id IS NOT NULL
AND f.root_fact_id IS NULL
AND f.previous_fact_id = f1.activity_metric_fact_id
and nvl(f.node_id,1) <> 3
order by 4,8;
SELECT f1.base_quantity level_two_bq,
f1.fact_value level_two_fact_val,
f.activity_metric_fact_id level_three_fact_id,
f.previous_fact_id,
f.base_quantity level_three_bq,
f.fact_type level_three_fact_type,
f.fact_reference,
f.from_date,
f.to_date,
f.fact_value,
f.fact_percent,
f.root_fact_id,
f1.forward_buy_quantity level_two_fwd_buy_qty
FROM ozf_act_metric_facts_all f, ozf_act_metric_facts_all f1
WHERE f.arc_act_metric_used_by = 'FCST'
AND f.act_metric_used_by_id = p_used_by_id
AND f.root_fact_id IS NOT NULL
AND f.previous_fact_id = f1.activity_metric_fact_id
and nvl(f.node_id,1) <> 3
order by 4,8;
SELECT count(*)
INTO l_temp_count
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
and nvl(node_id,1) <> 3;
SELECT NVL(sum(fact_value),0)
INTO l_fval_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
AND activity_metric_fact_id <> rec.activity_metric_fact_id
and nvl(node_id,1) <> 3;
SELECT NVL(sum(forward_buy_quantity),0)
INTO l_fwdbuy_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
AND activity_metric_fact_id <> rec.activity_metric_fact_id
and nvl(node_id,1) <> 3 ;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = l_forward_buy_quantity
WHERE activity_metric_fact_id = rec.activity_metric_fact_id;
SELECT count(*) INTO l_temp_count
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id = rec.previous_fact_id
AND root_fact_id IS NULL
and nvl(node_id,1) <> 3;
SELECT NVL(sum(fact_value),0)
INTO l_fval_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id = rec.previous_fact_id
AND root_fact_id IS NULL
AND activity_metric_fact_id <> rec.level_two_fact_id
and nvl(node_id,1) <> 3 ;
SELECT NVL(sum(forward_buy_quantity),0)
INTO l_fwdbuy_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id = rec.previous_fact_id
AND root_fact_id IS NULL
AND activity_metric_fact_id <> rec.level_two_fact_id
and nvl(node_id,1) <> 3 ;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = l_forward_buy_quantity
WHERE activity_metric_fact_id = rec.level_two_fact_id
and nvl( node_id,1) <> 3;
SELECT count(*) INTO l_temp_count
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id = rec.previous_fact_id
AND root_fact_id IS NOT NULL
and nvl(node_id,1) <> 3;
SELECT NVL(sum(fact_value),0)
INTO l_fval_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id = rec.previous_fact_id
AND root_fact_id IS NOT NULL
AND activity_metric_fact_id <> rec.level_three_fact_id
and nvl(node_id,1) <> 3;
SELECT NVL(sum(forward_buy_quantity),0)
INTO l_fwdbuy_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND previous_fact_id = rec.previous_fact_id
AND root_fact_id IS NOT NULL
AND activity_metric_fact_id <> rec.level_three_fact_id
and nvl(node_id,1) <> 3;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = round(l_fact_percent,4),
forward_buy_quantity = l_forward_buy_quantity
WHERE activity_metric_fact_id = rec.level_three_fact_id;
SELECT qualifier_context,
qualifier_attribute,
qualifier_attr_value,
comparison_operator_code
FROM ozf_forecast_dimentions
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND forecast_id = p_forecast_id
AND product_attribute_context = 'ITEM'
AND product_attribute = p_product_attribute
AND product_attr_value = p_product_attr_value;
SELECT qualifier_context,
qualifier_attribute,
qualifier_attr_value,
comparison_operator_code
FROM ozf_worksheet_qualifiers
WHERE worksheet_header_id = p_obj_id;
IF l_status_code <> 'UPDATED'
THEN
x_return_status := FND_API.G_RET_STS_ERROR;
SELECT
fcst.ARC_ACT_FCAST_USED_BY,
wkst.WORKSHEET_HEADER_ID,
dim.product_attribute_context,
dim.product_attribute,
dim.product_attr_value,
fcst.FORECAST_UOM_CODE,
wkst.currency_code,
wkst.price_list_id,
fcst.forecast_id
INTO
l_obj_type ,
l_obj_id ,
l_product_attribute_context,
l_product_attribute ,
l_product_attr_value ,
l_fcst_uom ,
l_currency_code ,
l_price_list_id ,
l_forecast_id
FROM
ozf_act_metric_facts_all fact,
OZF_ACT_FORECASTS_ALL fcst,
ozf_forecast_dimentions dim,
ozf_worksheet_headers_b wkst
WHERE
fact.activity_metric_fact_id = p_activity_metric_fact_id
AND fcst.FORECAST_ID = fact.act_metric_used_by_id
AND fact.arc_act_metric_used_by = 'FCST'
AND dim.forecast_dimention_id = fact.fact_reference
AND dim.forecast_id = fact.act_metric_used_by_id
AND wkst.WORKSHEET_HEADER_ID = fcst.ACT_FCAST_USED_BY_ID
AND fcst.ARC_ACT_FCAST_USED_BY = 'WKST';
SELECT product_id
FROM ozf_forecast_products
WHERE obj_type = p_obj_type
AND obj_id = p_obj_id
AND product_attribute_context = 'ITEM'
AND product_attribute = p_product_attribute
AND product_attr_value = p_product_attr_value ;
SELECT prod.product_id,
inv_prod.primary_uom_code
FROM ozf_forecast_products prod,
mtl_system_items_b inv_prod
WHERE prod.obj_type = p_obj_type
AND prod.obj_id = p_obj_id
AND product_attribute_context = 'ITEM'
AND product_attribute = p_product_attribute
AND product_attr_value = p_product_attr_value
AND prod.product_id = inv_prod.inventory_item_id
AND inv_prod.organization_id = p_org_id;
SELECT
fcst.ARC_ACT_FCAST_USED_BY,
wkst.WORKSHEET_HEADER_ID,
dim.product_attribute_context,
dim.product_attribute,
dim.product_attr_value,
fcst.FORECAST_UOM_CODE,
wkst.currency_code,
wkst.price_list_id
INTO
l_obj_type ,
l_obj_id ,
l_product_attribute_context,
l_product_attribute ,
l_product_attr_value ,
l_fcst_uom ,
l_currency_code ,
l_price_list_id
FROM
ozf_act_metric_facts_all fact,
OZF_ACT_FORECASTS_ALL fcst,
ozf_forecast_dimentions dim,
ozf_worksheet_headers_b wkst
WHERE
fact.activity_metric_fact_id = p_activity_metric_fact_id
AND fcst.FORECAST_ID = fact.act_metric_used_by_id
AND fact.arc_act_metric_used_by = 'FCST'
AND dim.forecast_dimention_id = fact.fact_reference
AND dim.forecast_id = fact.act_metric_used_by_id
AND wkst.WORKSHEET_HEADER_ID = fcst.ACT_FCAST_USED_BY_ID
AND fcst.ARC_ACT_FCAST_USED_BY = 'WKST';
SELECT a.forecast_uom_code,
a.price_list_id
FROM ozf_act_forecasts_all a
WHERE a.forecast_id = p_forecast_id ;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_obj_id;
SELECT min(b.offer_type) offer_type,
sum(a.line_lumpsum_qty) total_qty
FROM ams_act_products a ,
ozf_offers b
WHERE a.arc_act_product_used_by = p_obj_type
AND a.act_product_used_by_id = p_obj_id
AND b.qp_list_header_id = a.act_product_used_by_id ;
SELECT DECODE(b.distribution_type,
'AMT', 100 * a.line_lumpsum_qty/b.lumpsum_amount,
'%' , a.line_lumpsum_qty,
'QTY', 100 * a.line_lumpsum_qty/p_lumpsum_qty ) lumpsum_disc
from ams_act_products a,
ozf_offers b
where a.arc_act_product_used_by = p_obj_type
and a.act_product_used_by_id = p_obj_id
and a.act_product_used_by_id = b.qp_list_header_id
and a.excluded_flag = 'N'
and NVL(a.inventory_item_id,a.category_id) = p_product_attr_value;
SELECT qpl.operand,
qpl.arithmetic_operator
FROM qp_pricing_attributes pa,
qp_list_lines qpl,
ozf_offers a
WHERE a.qp_list_header_id = p_obj_id
AND qpl.list_header_id = a.qp_list_header_id
AND qpl.list_line_id = pa.list_line_id
AND pa.excluder_flag = 'N'
AND pa.product_attribute_context = 'ITEM'
AND pa.product_attribute = p_product_attribute
AND pa.product_attr_value = p_product_attr_value ;
SELECT
ODL.DISCOUNT_TYPE arithmetic_operator,
MAX(DIS.DISCOUNT) operand
FROM
OZF_OFFERS OFFR,
OZF_OFFER_DISCOUNT_LINES ODL,
OZF_OFFER_DISCOUNT_PRODUCTS ODP,
OZF_OFFER_DISCOUNT_LINES DIS
WHERE
OFFR.QP_LIST_HEADER_ID = p_obj_id
AND OFFR.OFFER_ID = ODL.OFFER_ID
AND ODL.TIER_TYPE = 'PBH'
AND ODP.OFFER_ID = OFFR.OFFER_ID
AND ODP.OFFER_DISCOUNT_LINE_ID = ODL.OFFER_DISCOUNT_LINE_ID
AND ODP.APPLY_DISCOUNT_FLAG = 'Y'
AND DIS.parent_discount_line_id = ODL.OFFER_DISCOUNT_LINE_ID
AND DIS.TIER_TYPE = 'DIS'
AND DIS.OFFER_ID = ODL.OFFER_ID
AND ODP.PRODUCT_CONTEXT = 'ITEM'
AND ODP.PRODUCT_ATTRIBUTE = p_product_attribute
AND ODP.PRODUCT_ATTR_VALUE = p_product_attr_value
GROUP BY
ODL.DISCOUNT_TYPE;
SELECT prd.operand,
prd.arithmetic_operator
from ozf_worksheet_lines prd,
ozf_worksheet_headers_b hdr
where 'WKST' = p_obj_type
and hdr.worksheet_header_id = p_obj_id
and hdr.worksheet_header_id = prd.worksheet_header_id
AND prd.exclude_flag = 'N'
AND prd.product_attribute_context = 'ITEM'
AND prd.product_attribute = p_product_attribute
AND prd.product_attr_value = p_product_attr_value;
SELECT a.forecast_uom_code,
a.price_list_id
FROM ozf_act_forecasts_all a
WHERE a.forecast_id = p_forecast_id ;
SELECT min(b.offer_type) offer_type,
sum(a.line_lumpsum_qty) total_qty
FROM ams_act_products a ,
ozf_offers b
WHERE a.arc_act_product_used_by = p_obj_type
AND a.act_product_used_by_id = p_obj_id
AND b.qp_list_header_id = a.act_product_used_by_id ;
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_obj_id;
SELECT DECODE(b.distribution_type,
'AMT', a.line_lumpsum_qty,
'%' , (b.lumpsum_amount*a.line_lumpsum_qty)/100 ,
'QTY', (a.line_lumpsum_qty * b.lumpsum_amount)/p_lumpsum_qty ) lumpsum_disc
from ams_act_products a,
ozf_offers b
where a.arc_act_product_used_by = p_obj_type
and a.act_product_used_by_id = p_obj_id
and a.act_product_used_by_id = b.qp_list_header_id
and a.excluded_flag = 'N'
and NVL(a.inventory_item_id,a.category_id) = p_product_attr_value;
SELECT TRIM(
DECODE(a.offer_type
,'DEAL'
,DECODE(qpl.accrual_flag
,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
, OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
)
,'VOLUME_OFFER'
,DECODE(qpl.accrual_flag
,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
, OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
) ||':'
, '') ||
' ' || qpl.operand || ' ' ||
DECODE(qpl.arithmetic_operator,'%','%',' ')
) discount,
qpl.operand,
qpl.arithmetic_operator
FROM qp_pricing_attributes pa,
qp_list_lines qpl,
ozf_offers a
WHERE a.qp_list_header_id = p_obj_id
AND qpl.list_header_id = a.qp_list_header_id
AND qpl.list_line_id = pa.list_line_id
AND pa.excluder_flag = 'N'
AND pa.product_attribute_context = 'ITEM'
AND pa.product_attribute = p_product_attribute
AND pa.product_attr_value = p_product_attr_value ;
SELECT DECODE(a.offer_type
,'DEAL'
,DECODE(qpl.accrual_flag
,'Y', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
, OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
)
,OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE', a.offer_type)
) ||
' ' ||
qpl.operand ||
' ' ||
DECODE(qpl.arithmetic_operator,'%','%',' ') discount,
qpl.operand,
qpl.arithmetic_operator
FROM qp_pricing_attributes pa,
qp_list_lines qpl,
ozf_act_offers a
WHERE a.arc_act_offer_used_by = p_obj_type
AND a.act_offer_used_by_id = p_obj_id
AND qpl.list_header_id = a.qp_list_header_id
AND qpl.list_line_id = pa.list_line_id
AND pa.excluder_flag = 'N'
AND pa.product_attribute_context = 'ITEM'
AND pa.product_attribute = p_product_attribute
AND pa.product_attr_value = p_product_attr_value ;
SELECT TRIM(
DECODE(OFFR.VOLUME_OFFER_TYPE
,'ACCRUAL', OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','ACCRUAL')
, OZF_Utility_PVT.get_lookup_meaning('OZF_OFFER_TYPE','OFF_INVOICE')
)
|| ':'
|| ' '
|| MAX(DIS.DISCOUNT)
|| ' '
|| DECODE(ODL.DISCOUNT_TYPE,'%','%',' ')
) discount,
ODL.DISCOUNT_TYPE arithmetic_operator,
MAX(DIS.DISCOUNT) operand
FROM
OZF_OFFERS OFFR,
OZF_OFFER_DISCOUNT_LINES ODL,
OZF_OFFER_DISCOUNT_PRODUCTS ODP,
OZF_OFFER_DISCOUNT_LINES DIS
WHERE
OFFR.QP_LIST_HEADER_ID = p_obj_id
AND OFFR.OFFER_ID = ODL.OFFER_ID
AND ODL.TIER_TYPE = 'PBH'
AND ODP.OFFER_ID = OFFR.OFFER_ID
AND ODP.OFFER_DISCOUNT_LINE_ID = ODL.OFFER_DISCOUNT_LINE_ID
AND ODP.APPLY_DISCOUNT_FLAG = 'Y'
AND DIS.parent_discount_line_id = ODL.OFFER_DISCOUNT_LINE_ID
AND DIS.TIER_TYPE = 'DIS'
AND DIS.OFFER_ID = ODL.OFFER_ID
AND ODP.PRODUCT_CONTEXT = 'ITEM'
AND ODP.PRODUCT_ATTRIBUTE = p_product_attribute
AND ODP.PRODUCT_ATTR_VALUE = p_product_attr_value
GROUP BY
ODL.DISCOUNT_TYPE,
OFFR.VOLUME_OFFER_TYPE;
SELECT o.offer_type,
p.product_id,
line.order_quantity_uom,
NVL(line.shipped_quantity, line.ordered_quantity) ordered_quantity,
inv_convert.inv_um_convert( p.product_id,
null,
DECODE(line.line_category_code
,'ORDER', line.shipped_quantity
,-NVL(line.shipped_quantity,line.ordered_quantity)
),
line.order_quantity_uom,
p_fcst_uom,
null, null) conv_ordered_quantity,
DECODE(line.line_category_code,
'ORDER', line.unit_list_price,
-line.unit_list_price) unit_list_price,
DECODE(line.line_category_code,
'ORDER', -adj.adjusted_amount
, adj.adjusted_amount) adjusted_amount,
adj.accrual_flag
FROM ozf_forecast_products p,
ozf_offers o,
oe_price_adjustments adj,
oe_order_lines_all line
WHERE p.obj_type = p_obj_type
AND p.obj_id = p_obj_id
AND p.product_attribute_context = 'ITEM'
AND p.product_attribute = p_product_attribute
AND p.product_attr_value = p_product_attr_value
AND o.qp_list_header_id = p.obj_id
AND adj.list_header_id = p.obj_id
AND adj.line_id = line.line_id
AND line.inventory_item_id = p.product_id
AND line.open_flag = 'N'
AND line.cancelled_flag = 'N';
SELECT a.offer_type,
nvl(a.transaction_currency_code,a.fund_request_curr_code),
b.forecast_spread_type
FROM ozf_offers a,
ozf_act_forecasts_all b
WHERE b.forecast_id = p_used_by_id
AND DECODE(b.arc_act_fcast_used_by,'OFFR',b.act_fcast_used_by_id,-99)
= a.qp_list_header_id(+);
SELECT distinct count(act_metric_used_by_id)
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_used_by_id
AND fact_type = p_dimention
GROUP by previous_fact_id;
UPDATE ozf_act_metric_facts_all f
SET (fact_value, fact_percent, forward_buy_quantity ) =
( SELECT ROUND( a.fact_value ) ,
ROUND( a.fact_value * 100 / a.total_forecast_quantity ) fact_percent,
ROUND( (a.fact_value * 100 / a.total_forecast_quantity)
* DECODE(f.fact_type,'TIME',0,a.total_forward_buy_quantity)
* 0.01 ) forward_buy_quantity
FROM
( SELECT DECODE(fcst.forecast_spread_type , 'BASELINE_RATIO'
, ( NVL(previous_fact.fact_value,fcst.forecast_quantity)
* fact.base_quantity
)/DECODE( NVL(previous_fact.base_quantity, fcst.base_quantity),0,1,
NVL(previous_fact.base_quantity, fcst.base_quantity)
)
, ( NVL(previous_fact.fact_value, fcst.forecast_quantity)/l_dimention_count
/*( SELECT COUNT(f1.act_metric_used_by_id)
FROM ozf_act_metric_facts_all f1
WHERE f1.act_metric_used_by_id = fact.act_metric_used_by_id
AND f1.arc_act_metric_used_by = 'FCST'
AND f1.fact_type= p_dimention
AND NVL(f1.previous_fact_id,-99) =
NVL(previous_fact.activity_metric_fact_id,-99)
) */
)
) fact_value,
fact.activity_metric_fact_id,
DECODE( NVL(previous_fact.fact_value, fcst.forecast_quantity),
0,1,
NVL(previous_fact.fact_value, fcst.forecast_quantity)
) total_forecast_quantity,
NVL(previous_fact.forward_buy_quantity, fcst.forward_buy_quantity) total_forward_buy_quantity
FROM
ozf_act_forecasts_all fcst,
ozf_act_metric_facts_all fact,
ozf_act_metric_facts_all previous_fact
WHERE
fact.act_metric_used_by_id = fcst.forecast_id
AND fact.previous_fact_id = previous_fact.activity_metric_fact_id(+)
AND fact.act_metric_used_by_id = p_used_by_id
AND fact.arc_act_metric_used_by = 'FCST'
AND fact.fact_type = p_dimention
) a
WHERE a.activity_metric_fact_id = f.activity_metric_fact_id
)
WHERE f.act_metric_used_by_id = p_used_by_id
AND f.arc_act_metric_used_by = 'FCST'
AND f.fact_type = p_dimention ;
UPDATE ozf_act_metric_facts_all f
SET ( fact_value, forward_buy_quantity) =
( SELECT f.fact_value + a.adj_fact_value,
f.forward_buy_quantity + a.adj_fwd_buy_quantity
FROM
( SELECT NVL(MIN(previous_fact.fact_value), MIN(fcst.forecast_quantity)) -
SUM(fact.fact_value) adj_fact_value,
NVL(MIN(previous_fact.forward_buy_quantity), MIN(fcst.forward_buy_quantity)) -
SUM(fact.forward_buy_quantity) adj_fwd_buy_quantity,
MAX(fact.activity_metric_fact_id) activity_metric_fact_id
FROM ozf_act_forecasts_all fcst,
ozf_act_metric_facts_all fact,
ozf_act_metric_facts_all previous_fact
WHERE fact.act_metric_used_by_id = p_used_by_id
AND fact.arc_act_metric_used_by = 'FCST'
AND fact.fact_type = p_dimention
AND fact.act_metric_used_by_id = fcst.forecast_id
AND NVL(fact.node_id,1) <> 3
AND fact.previous_fact_id = previous_fact.activity_metric_fact_id(+)
GROUP BY fact.previous_fact_id
) a
WHERE a.activity_metric_fact_id = f.activity_metric_fact_id
)
WHERE activity_metric_fact_id in (SELECT MAX(activity_metric_fact_id)
FROM ozf_act_metric_facts_all
WHERE act_metric_used_by_id = p_used_by_id
AND arc_act_metric_used_by = 'FCST'
AND fact_type = p_dimention
AND NVL(node_id,1) <> 3
GROUP BY previous_fact_id ) ;
SELECT a.forecast_uom_code,
a.price_list_id
FROM ozf_act_forecasts_all a
WHERE a.forecast_id = p_forecast_id ;
SELECT dim.product_attribute,
dim.product_attribute_context,
dim.product_attr_value,
prod.qty,
prod.forecast_dimention_id
FROM ozf_forecast_dimentions dim,
(select fact.fact_reference forecast_dimention_id,
DECODE(fcst.BASE_QUANTITY_TYPE,
'BASELINE',
SUM(fact.BASELINE_SALES + fact.INCREMENTAL_SALES),
sum(fact.fact_value)) qty
from ozf_act_metric_facts_all fact,
OZF_ACT_FORECASTS_ALL fcst
where fact.fact_type = 'PRODUCT'
and fact.arc_act_metric_used_by = 'FCST'
and fact.act_metric_used_by_id = p_forecast_id
AND FACT.ACT_METRIC_USED_BY_ID = FCST.FORECAST_ID
group by fact.fact_reference,fcst.BASE_QUANTITY_TYPE) prod,
OZF_OFFER_DISCOUNT_LINES ODL,
OZF_OFFER_DISCOUNT_PRODUCTS ODP
WHERE prod.forecast_dimention_id = dim.forecast_dimention_id
AND dim.forecast_id = p_forecast_id
AND ODL.OFFER_DISCOUNT_LINE_ID = p_offer_discount_line_id
AND ODL.TIER_TYPE = 'PBH'
AND ODP.OFFER_DISCOUNT_LINE_ID = ODL.OFFER_DISCOUNT_LINE_ID
-- AND ODP.APPLY_DISCOUNT_FLAG = 'Y'
AND ODP.INCLUDE_VOLUME_FLAG = 'Y' -- get those products whoose sales is to be counted
AND ODP.PRODUCT_CONTEXT = dim.product_attribute_context
AND ODP.PRODUCT_ATTRIBUTE = dim.product_attribute
AND ODP.PRODUCT_ATTR_VALUE = dim.product_attr_value;
SELECT dim.product_attribute,
dim.product_attribute_context,
dim.product_attr_value,
prod.qty,
prod.forecast_dimention_id
FROM ozf_forecast_dimentions dim,
(select fact_reference forecast_dimention_id, sum(fact_value) qty
from ozf_act_metric_facts_all
where fact_type = 'PRODUCT'
and arc_act_metric_used_by = 'FCST'
and act_metric_used_by_id = p_forecast_id
group by fact_reference ) prod
WHERE prod.forecast_dimention_id = dim.forecast_dimention_id
AND dim.forecast_id = p_forecast_id;
SELECT
ODP.OFFER_DISCOUNT_LINE_ID ,
DIS.volume_from tier_value_from,
DIS.volume_to tier_value_to,
DIS.discount discount,
ODL.discount_type discount_type_code,
ODL.volume_type volume_type,
ODL.uom_code uom_code
FROM
OZF_OFFERS OFFR,
OZF_OFFER_DISCOUNT_LINES ODL,
OZF_OFFER_DISCOUNT_PRODUCTS ODP,
OZF_OFFER_DISCOUNT_LINES DIS
WHERE
OFFR.QP_LIST_HEADER_ID = p_obj_id
AND OFFR.OFFER_ID = ODL.OFFER_ID
AND ODL.TIER_TYPE = 'PBH'
AND ODP.OFFER_ID = OFFR.OFFER_ID
AND ODP.OFFER_DISCOUNT_LINE_ID = ODL.OFFER_DISCOUNT_LINE_ID
AND ODP.APPLY_DISCOUNT_FLAG = 'Y'
AND DIS.parent_discount_line_id = ODL.OFFER_DISCOUNT_LINE_ID
AND DIS.TIER_TYPE = 'DIS'
AND DIS.OFFER_ID = ODL.OFFER_ID
AND ODP.PRODUCT_CONTEXT = 'ITEM'
AND ODP.PRODUCT_ATTRIBUTE = p_product_attribute
AND ODP.PRODUCT_ATTR_VALUE = p_product_attr_value;
SELECT tier_value_from,
tier_value_to,
discount,
discount_type_code,
volume_type,
uom_code
FROM ozf_volume_offer_tiers
WHERE qp_list_header_id = p_obj_id;
SELECT inv_convert.inv_um_convert( null,
null,
l_fcst_qty,
l_fcst_uom,
l_voloffr_uom, null, null) converted_qty
from dual;
select dim.product_attribute_context,
dim.product_attribute,
dim.product_attr_value,
fact.activity_metric_fact_id,
fact.node_id,
NVL(previous_fact.fact_value,fcst.forecast_quantity) total_forecast,
NVL(previous_fact.forward_buy_quantity, fcst.forward_buy_quantity) total_forward_buy,
fcst.forecast_uom_code
from ozf_act_metric_facts_all fact,
ozf_forecast_dimentions dim ,
ozf_act_metric_facts_all previous_fact,
ozf_act_forecasts_all fcst
where fcst.forecast_id = p_used_by_id
and fact.arc_act_metric_used_by = 'FCST'
and fact.act_metric_used_by_id = fcst.forecast_id
and fact.fact_type = p_dimention
and fact.fact_reference = dim.forecast_dimention_id
AND dim.forecast_id = p_used_by_id
and fact.previous_fact_id = previous_fact.activity_metric_fact_id(+)
order by fact.activity_metric_fact_id;
select NVL(prod.pricing_attribute,'PRICING_ATTRIBUTE10') pricing_attribute,
DECODE(disc.list_line_type_code,
'DIS',disc.benefit_uom_code
,prod.product_uom_code) offer_product_uom_code,
DECODE(disc.list_line_type_code,
'DIS',disc.benefit_qty
,prod.pricing_attr_value_from) offer_product_qty,
fcst.forecast_uom_code,
inv_convert.inv_um_convert(
null,
null,
DECODE(disc.list_line_type_code,
'DIS',disc.benefit_qty
,prod.pricing_attr_value_from) ,
DECODE(disc.list_line_type_code,
'DIS',disc.benefit_uom_code
,prod.product_uom_code) ,
fcst.forecast_uom_code,
null,
null) converted_ratio,
disc.operand,
disc.arithmetic_operator,
prod.product_attribute_context,
prod.product_attribute,
prod.product_attr_value,
disc.list_line_type_code,
fcst.price_list_id,
disc.list_header_id
from qp_pricing_attributes prod,
qp_list_lines disc,
ozf_act_forecasts_all fcst
where fcst.forecast_id = p_used_by_id
and fcst.arc_act_fcast_used_by = 'OFFR'
and disc.list_header_id = fcst.act_fcast_used_by_id
and disc.list_line_id = prod.list_line_id;
UPDATE ozf_act_metric_facts_all
SET fact_value = l_fact_value,
fact_percent = l_fact_percent,
forward_buy_quantity = l_fact_forward_buy
WHERE activity_metric_fact_id = i.activity_metric_fact_id;
PROCEDURE cascade_baseline_update(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_id IN NUMBER,
p_value IN NUMBER,
p_fcast_id IN NUMBER,
p_rem_value IN NUMBER,
p_cascade_flag IN NUMBER,
p_tpr_percent IN NUMBER,
p_obj_type IN VARCHAR2,
p_obj_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_fact_percent NUMBER := 0;
SELECT activity_metric_fact_id,
previous_fact_id,
forecast_remaining_quantity,
fact_type,
fact_reference,
from_date,
to_date,
incremental_sales,
root_fact_id
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = p_prev_id
order by 6;
SELECT activity_metric_fact_id,
previous_fact_id,
fact_type,
fact_reference,
from_date,
to_date,
incremental_sales,
root_fact_id
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = p_prev_id
AND root_fact_id IS NOT NULL
order by 6;
l_api_name CONSTANT VARCHAR2(30) := 'Cascade_Baseline_Update';
SELECT incremental_sales, tpr_percent, fact_type
INTO l_parent_fact_value, l_tpr_percent, l_fact_type
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_fact_id = p_id;
GOTO end_of_cascade_baseline_update;
SELECT count(*), sum(incremental_sales)
INTO l_temp_count, l_fact_value_sum_all_recs
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = p_id;
SELECT incremental_sales, forecast_remaining_quantity
INTO l_current_fact_value, l_total_rem_value
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND activity_metric_fact_id = p_id;
SELECT NVL(sum(incremental_sales),0)
INTO l_fval_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_record.previous_fact_id
AND activity_metric_fact_id <> facts_record.activity_metric_fact_id ;
UPDATE ozf_act_metric_facts_all
SET incremental_sales = l_fact_value
--forecast_remaining_quantity = decode(sign(l_rem_value), -1, decode(root_fact_id,NULL,l_rem_value, 0),0)
WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
SELECT count(*), sum(incremental_sales)
INTO l_temp_sub_count, l_fact_value_sum_all_sub_recs
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_subrecord.previous_fact_id
AND root_fact_id IS NOT NULL;
SELECT NVL(sum(incremental_sales),0)
INTO l_fval_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id = facts_subrecord.previous_fact_id
AND root_fact_id IS NOT NULL
AND activity_metric_fact_id <> facts_subrecord.activity_metric_fact_id ;
UPDATE ozf_act_metric_facts_all
SET incremental_sales = l_fact_value
WHERE activity_metric_fact_id = facts_subrecord.activity_metric_fact_id;
<>
IF (OZF_DEBUG_HIGH_ON) THEN
OZF_Utility_PVT.debug_message(l_full_name || ': End cascade baseline update');
END cascade_baseline_update;
SELECT activity_metric_fact_id,
previous_fact_id,
forecast_remaining_quantity,
fact_type,
fact_reference,
from_date,
to_date,
incremental_sales,
root_fact_id
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_forecast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
order by 6;
SELECT forecast_quantity, base_quantity, forecast_remaining_quantity
INTO l_total_fcst_value, l_total_base_qty, l_total_rem_value
FROM ozf_act_forecasts_all
WHERE forecast_id = p_fcast_id;
SELECT count(*), sum(incremental_sales)
INTO l_temp_count, l_fact_value_sum_all_recs
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL;
SELECT NVL(sum(incremental_sales),0)
INTO l_fval_sum_minus_last_rec
FROM ozf_act_metric_facts_all
WHERE arc_act_metric_used_by = 'FCST'
AND act_metric_used_by_id = p_fcast_id
AND previous_fact_id IS NULL
AND root_fact_id IS NULL
AND activity_metric_fact_id <> facts_record.activity_metric_fact_id ;
cascade_baseline_update(p_api_version,
p_init_msg_list,
p_commit,
facts_record.activity_metric_fact_id,
l_fact_value,
p_fcast_id,
l_rem_value,
p_cascade_flag,
l_tpr_percent,
p_obj_type,
p_obj_id,
x_return_status,
x_msg_count,
x_msg_data
);
UPDATE ozf_act_metric_facts_all
SET incremental_sales = l_fact_value
--forecast_remaining_quantity = decode(sign(l_rem_value), -1, l_rem_value, 0)
WHERE activity_metric_fact_id = facts_record.activity_metric_fact_id;
UPDATE ozf_act_forecasts_all
SET forecast_quantity = p_fcast_value
WHERE forecast_id = p_fcast_id;