[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select LOOKUP_CODE
From FND_LOOKUPS
Where LOOKUP_TYPE = p_Lookup_type
And MEANING = p_Meaning;
Select MESSAGE_TEXT
From FND_NEW_MESSAGES
Where APPLICATION_ID = 523
And MESSAGE_NAME = 'CSP_NO_LEAD_TIME';
Select HISTORY_PERIODS,
PERIOD_SIZE,
cfrb.FORECAST_RULE_ID
From CSP_PLANNING_PARAMETERS cpp,
CSP_FORECAST_RULES_B cfrb
Where cpp.ORGANIZATION_ID IS NULL
And cpp.SECONDARY_INVENTORY IS NULL
And cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
fnd_msg_pub.delete_msg;
Select MESSAGE_TEXT
From FND_NEW_MESSAGES
Where APPLICATION_ID = 523
And MESSAGE_NAME = 'CSP_NO_LEAD_TIME';
Select sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,1)),
sum(decode(cpp.recommend_method,'PNORM',1,'USAGE_PNORM',1,0)),
sum(decode(cpp.recommend_method,'TNORM',1,'USAGE_TNORM',1,0))
from csp_planning_parameters cpp
where level_id like p_level_id || '%'
and node_type in ('ORGANIZATION_WH','SUBINVENTORY');
Delete from csp_usage_headers cuh
Where (cuh.organization_id,cuh.secondary_inventory) in
(select cpp.organization_id ,nvl(cpp.secondary_inventory,'-')
from csp_planning_parameters cpp
Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
DELETE FROM csp_usage_histories
WHERE history_data_type IN (2,5);
Delete from csp_usage_histories cuh
Where history_data_type in (2,5)
And (cuh.organization_id,cuh.subinventory_code) in
(select cpp.organization_id ,nvl(cpp.secondary_inventory,'-')
from csp_planning_parameters cpp
Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
ITEM_COST,
LEAD_TIME,
STANDARD_DEVIATION,
PROCESS_STATUS,
EXTERNAL_DATA,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
SELECT NULL,
mis.inventory_item_id,
mis.organization_id,
mis.secondary_inventory,
10,
NULL,
NULL,
cic.item_cost,
NULL,
NULL,
'O',
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
From CSP_PLANNING_PARAMETERS cpp,
MTL_ITEM_SUB_INVENTORIES mis,
CST_ITEM_COSTS cic,
MTL_PARAMETERS mp
where cpp.node_type = 'SUBINVENTORY'
and mis.organization_id = cpp.organization_id
and mis.secondary_inventory = cpp.secondary_inventory
and mp.organization_id = cpp.organization_id
and cic.inventory_item_id = mis.inventory_item_id
And cic.organization_id = mis.organization_id
And cic.cost_type_id = mp.primary_cost_method
and (mis.min_minmax_quantity > 0 OR mis.max_minmax_quantity > 0) ;
fnd_message.set_token('VALUE','Stock List Update');
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
ITEM_COST,
LEAD_TIME,
STANDARD_DEVIATION,
PROCESS_STATUS,
EXTERNAL_DATA,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
SELECT NULL,
msi.inventory_item_id,
msi.organization_id,
'-',
11,
NULL,
NULL,
cic.item_cost,
NULL,
NULL,
'O',
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
From CSP_PLANNING_PARAMETERS cpp,
MTL_SYSTEM_ITEMS msi,
CST_ITEM_COSTS cic,
MTL_PARAMETERS mp
where cpp.node_type = 'ORGANIZATION_WH'
and mp.organization_id = cpp.organization_id
and msi.organization_id = cpp.organization_id
and msi.inventory_planning_code = 2
and cic.inventory_item_id = msi.inventory_item_id
And cic.organization_id = msi.organization_id
And cic.cost_type_id = mp.primary_cost_method
and (msi.min_minmax_quantity > 0 OR msi.max_minmax_quantity > 0) ;
fnd_message.set_token('VALUE','Insert Usage Headers Update');
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
ITEM_COST,
LEAD_TIME,
STANDARD_DEVIATION,
PROCESS_STATUS,
EXTERNAL_DATA,
PLANNING_PARAMETERS_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
Select NULL,
cuh.inventory_item_id,
cuh.organization_id,
cuh.secondary_inventory,
decode(nvl(cuh.secondary_inventory,'-'),'-',4,1),
NULL,
decode(sign(
sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu,6,cuh.awu,0))) -
sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,7,cuh.awu,9,cuh.awu,0)))),
1,
decode(sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,7,nvl(cuh.awu,0),9,nvl(cuh.awu,0),0))),0,
sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight4,6,cuh.awu * cpp.usage_weight4,8,cuh.awu,0))),
sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight1,6,cuh.awu * cpp.usage_weight1,8,cuh.awu,cuh.awu * (1 - cpp.usage_weight1))))) ,
-1,
decode(nvl(sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu,6,cuh.awu,0))),0),0,
sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,0,6,0,8,cuh.awu,cuh.awu * (1- usage_weight3)))),
sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight2,6,cuh.awu * cpp.usage_weight2,8,cuh.awu,cuh.awu * (1 - cpp.usage_weight2))))),
sum(decode(recommend_method,
'PNORM',decode(cuh.header_data_type,7,cuh.awu,8,cuh.awu,0),
'TNORM',decode(cuh.header_data_type,9,cuh.awu,8,cuh.awu,0),
'USAGE',decode(cuh.header_data_type,5,cuh.awu,6,cuh.awu,8,cuh.awu,0),
decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight1,6,cuh.awu * cpp.usage_weight1,8,cuh.awu,cuh.awu * (1- cpp.usage_weight1))))),
nvl(cic.item_cost,0),
nvl(nvl(mism1.intransit_time,nvl(mism2.intransit_time,nvl(mism3.intransit_time,mism4.intransit_time))),
decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))),
sum(nvl(cuh.standard_deviation,0)),
'O',
'N',
cpp.planning_parameters_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
from csp_planning_parameters cpp,
csp_usage_headers cuh,
cst_item_costs cic,
mtl_parameters mp,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
mtl_secondary_inventories msi,
mtl_interorg_ship_methods mism1,
mtl_interorg_ship_methods mism2,
mtl_interorg_ship_methods mism3,
mtl_interorg_ship_methods mism4
where cpp.level_id like p_level_id || '%'
and cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
and cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
and cuh.organization_id = cpp.organization_id
and cuh.secondary_inventory = nvl(cpp.secondary_inventory,'-')
and cuh.header_data_type in (5,6,7,8,9,10,11)
and cic.inventory_item_id = cuh.inventory_item_id
And cic.organization_id = cuh.organization_id
And cic.cost_type_id = mp.primary_cost_method
and mp.organization_id = cuh.organization_id
and misi.organization_id (+) = cuh.organization_id
and misi.inventory_item_id (+) = cuh.inventory_item_id
and misi.secondary_inventory (+) = cuh.secondary_inventory
and mism4.to_organization_id (+) = mp.organization_id
and mism4.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
and mism4.default_flag (+) = 1
and mism3.to_organization_id (+) = msi.organization_id
and mism3.from_organization_id (+) = decode(msi.source_type,1,msi.source_organization_id,3,msi.source_organization_id,-1)
and mism3.default_flag (+) = 1
and mism2.to_organization_id (+) = msib.organization_id
and mism2.from_organization_id (+) = decode(msib.source_type,1,msib.source_organization_id,3,msib.source_organization_id,-1)
and mism2.default_flag (+) = 1
and mism1.to_organization_id (+) = misi.organization_id
and mism1.from_organization_id (+) = decode(misi.source_type,1,misi.source_organization_id,3,misi.source_organization_id,-1)
and mism1.default_flag (+) = 1
and msib.organization_id = cuh.organization_id
and msib.inventory_item_id = cuh.inventory_item_id
and msi.organization_id(+) = cuh.organization_id
and msi.secondary_inventory_name(+) = cuh.secondary_inventory
Group by cuh.inventory_item_id,
cuh.organization_id,
cuh.secondary_inventory,
cpp.planning_parameters_id,
nvl(cic.item_cost,0),
nvl(nvl(mism1.intransit_time,nvl(mism2.intransit_time,nvl(mism3.intransit_time,mism4.intransit_time))),
decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ;
fnd_message.set_token('VALUE','Insert Usage Headers Update2');
update csp_usage_headers cuh
set process_status = 'E'
Where (cuh.inventory_item_id,cuh.organization_id,cuh.secondary_inventory) in
(select mic.inventory_item_id,mic.organization_id,nvl(cpp.secondary_inventory,'-')
from csp_planning_parameters cpp,
mtl_item_categories mic
where cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
and mic.organization_id = cpp.organization_id
and mic.category_set_id = cpp.category_set_id
and mic.category_id = nvl(cpp.category_id,mic.category_id));
UPDATE CSP_USAGE_HEADERS usg_headers
SET (recommended_min_quantity,recommended_max_quantity) =
(SELECT decode(sq.MAX_QUANTITY,0,0,greatest(1,sq.MIN_QUANTITY)),
sq.MAX_QUANTITY
FROM (Select cuh.Inventory_Item_Id,
cuh.Organization_Id,
cuh.Secondary_Inventory,
ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time + DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) Min_quantity,
ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
from CSP_USAGE_HEADERS cuh,
CSP_PLANNING_PARAMETERS cpp,
CSP_SAFETY_FACTORS csf
Where cuh.header_data_type = 1
And cuh.process_status = 'O'
And cpp.organization_id = cuh.organization_id
And cpp.secondary_inventory = cuh.secondary_inventory
And cpp.node_type = 'SUBINVENTORY'
And csf.service_level = cpp.service_level
And csf.exposures = GREATEST(3,DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,DECODE(cpp.edq_factor,0,0,
LEAST(ROUND( cuh.AWU *52/ROUND(cpp.Edq_Factor *
(SQRT(52 * cuh.AWU * cuh.Item_Cost)/cuh.Item_Cost),4)),52)))))) sq
WHERE usg_headers.INVENTORY_ITEM_ID = sq.INVENTORY_ITEM_ID
AND usg_headers.ORGANIZATION_ID = sq.ORGANIZATION_ID
AND usg_headers.SECONDARY_INVENTORY = sq.SECONDARY_INVENTORY)
WHERE usg_headers.header_data_type = 1
and usg_headers.process_status = 'O';
UPDATE csp_usage_headers usg_headers
Set (recommended_min_quantity,recommended_max_quantity) =
(SELECT decode(sq.MAX_QUANTITY,0,0,greatest(1,sq.MIN_QUANTITY)),
sq.MAX_QUANTITY
FROM (Select cuh.inventory_item_id,
cuh.organization_id,
ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) min_quantity,
ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
from CSP_USAGE_HEADERS cuh,
CSP_PLANNING_PARAMETERS cpp,
CSP_SAFETY_FACTORS csf
Where cuh.header_data_type = 4
And cuh.process_status = 'O'
And cpp.organization_id = cuh.organization_id
And cpp.node_type = 'ORGANIZATION_WH'
And csf.service_level = cpp.service_level
And csf.exposures = GREATEST(3,DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,DECODE(cpp.edq_factor,0,0,
LEAST(ROUND( cuh.AWU *52/ROUND(cpp.Edq_Factor *
(SQRT(52 * cuh.AWU * cuh.Item_Cost)/cuh.Item_Cost),4)),52)))))) sq
WHERE sq.inventory_item_id = usg_headers.inventory_item_id
AND sq.organization_id = usg_headers.organization_id)
WHERE usg_headers.header_data_type = 4
and usg_headers.process_status = 'O';
update csp_usage_headers cuh
set tracking_signal =
(select round(decode(a.forecast_periods - 1,0,0, sum(a.diff)/
sqrt((sum(a.diff * a.diff) - (sum(a.diff) * sum(a.diff)/a.forecast_periods)) / (a.forecast_periods - 1))),4)
from (
select cuh_fcst.inventory_item_id,
cuh_fcst.organization_id,
cuh_fcst.subinventory_code,
cuh_fcst.quantity - sum(cuh_usg.quantity) diff,
cfrb.forecast_periods
from csp_usage_histories cuh_fcst,
csp_planning_parameters cpp,
csp_forecast_rules_b cfrb,
csp_usage_histories cuh_usg
where cuh_fcst.history_data_type = 5
and cuh_fcst.period_start_date between (trunc(sysdate) - cfrb.forecast_periods * cfrb.period_size * cfrb.tracking_signal_cycle) and trunc(sysdate)
and cuh_fcst.organization_id = cpp.organization_id
and cuh_fcst.subinventory_code = cpp.secondary_inventory
and cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM')
and cpp.node_type = 'SUBINVENTORY'
and cfrb.forecast_rule_id = cpp.forecast_rule_id
and cuh_usg.period_start_date between cuh_fcst.period_start_date and
cuh_fcst.period_start_date + cfrb.period_size
and cuh_usg.organization_id = cuh_fcst.organization_id
and cuh_usg.subinventory_code = cuh_fcst.subinventory_code
and cuh_usg.inventory_item_id = cuh_fcst.inventory_item_id
and cuh_usg.history_data_type = 1
group by cuh_fcst.inventory_item_id,cuh_fcst.organization_id,
cuh_fcst.subinventory_code,cuh_fcst.quantity,
cfrb.forecast_periods) a
where a.inventory_item_id = cuh.inventory_item_id
and a.organization_id = cuh.organization_id
and a.subinventory_code = cuh.secondary_inventory
group by a.inventory_item_id,a.organization_id,a.subinventory_code,
a.forecast_periods)
where cuh.header_data_type = 1
and process_status = 'O';
update csp_usage_headers cuh
set tracking_signal =
(select round(decode(a.forecast_periods - 1,0,0, sum(a.diff)/
sqrt((sum(a.diff * a.diff) - (sum(a.diff) * sum(a.diff)/a.forecast_periods)) / (a.forecast_periods - 1))),4)
from (
select cuh_fcst.inventory_item_id,
cuh_fcst.organization_id,
cuh_fcst.quantity - sum(cuh_usg.quantity) diff,
cfrb.forecast_periods
from csp_usage_histories cuh_fcst,
csp_planning_parameters cpp,
csp_forecast_rules_b cfrb,
csp_usage_org_mv cuh_usg
where cuh_fcst.history_data_type = 5
and cuh_fcst.period_start_date between (trunc(sysdate) - cfrb.forecast_periods * cfrb.period_size * cfrb.tracking_signal_cycle) and trunc(sysdate)
and cpp.organization_id = cuh_fcst.organization_id
and cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM')
and cpp.node_type = 'ORGANIZATION_WH'
and cfrb.forecast_rule_id = cpp.forecast_rule_id
and cuh_usg.period_start_date between cuh_fcst.period_start_date and
cuh_fcst.period_start_date + cfrb.period_size
and cuh_usg.organization_id = cuh_fcst.organization_id
and cuh_usg.inventory_item_id = cuh_fcst.inventory_item_id
group by cuh_fcst.inventory_item_id,cuh_fcst.organization_id,
cuh_fcst.quantity, cfrb.forecast_periods) a
where a.inventory_item_id = cuh.inventory_item_id
and a.organization_id = cuh.organization_id
group by a.inventory_item_id,a.organization_id, a.forecast_periods)
where header_data_type = 4
and process_status = 'O';
-- Delete from Csp_Usage_Headers
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HEADERS';
-- Delete from Csp_Usage_Histories
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
-- Delete from Csp_Supply_Chain
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
select PROFILE_OPTION_VALUE
into G_LAST_RUN_DATE
from fnd_profile_option_values
where APPLICATION_ID = 523
and PROFILE_OPTION_ID in
(select profile_option_id
from fnd_profile_options
where profile_option_name = ('CSP_USAGE_RUN_DATE'));
Select nvl(csi.item_supplied,cuh.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
cuh.ORGANIZATION_ID,
cuh.SUBINVENTORY_CODE,
(trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
- ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE,
sum(cuh.QUANTITY) QUANTITY,
5 HISTORY_DATA_TYPE,
cfrb.HISTORY_PERIODS,
cfrb.forecast_rule_id,
DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) FORECAST_PERIODS ,
cfrb.FORECAST_METHOD,
cfrb.PERIOD_SIZE,
cfrb.ALPHA,
cfrb.BETA,
cfrb.WEIGHTED_AVG_PERIOD1,
cfrb.WEIGHTED_AVG_PERIOD2,
cfrb.WEIGHTED_AVG_PERIOD3,
cfrb.WEIGHTED_AVG_PERIOD4,
cfrb.WEIGHTED_AVG_PERIOD5,
cfrb.WEIGHTED_AVG_PERIOD6,
cfrb.WEIGHTED_AVG_PERIOD7,
cfrb.WEIGHTED_AVG_PERIOD8,
cfrb.WEIGHTED_AVG_PERIOD9,
cfrb.WEIGHTED_AVG_PERIOD10,
cfrb.WEIGHTED_AVG_PERIOD11,
cfrb.WEIGHTED_AVG_PERIOD12,
cpp.RECOMMEND_METHOD
From CSP_PLANNING_PARAMETERS cpp,
CSP_USAGE_HISTORIES cuh,
CSP_SUPERSEDE_ITEMS csi,
CSP_FORECAST_RULES_B cfrb
Where cpp.level_id like P_Level_Id || '%'
And cpp.node_type = 'SUBINVENTORY'
And cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
And cuh.organization_id = cpp.organization_id
And cuh.subinventory_code = cpp.secondary_inventory
And cuh.HISTORY_DATA_TYPE = 1
And (cuh.PERIOD_START_DATE BETWEEN (trunc(sysdate) - cfrb.forecast_periods
* cfrb.period_size * l_ts_cycle) - cfrb.period_size * cfrb.history_periods - (cfrb.period_size - 1)
AND (trunc(sysdate) - cfrb.forecast_periods * period_size * l_ts_cycle))
And cuh.transaction_type_id in (select transaction_type_id
from csp_usg_transaction_types cutt
where cutt.forecast_rule_id = cpp.forecast_rule_id)
And csi.inventory_item_id(+) = cuh.inventory_item_id
And csi.organization_id (+) = cuh.organization_id
And csi.sub_inventory_code(+) = cuh.subinventory_code
AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID
AND cfrb.tracking_signal_cycle >= l_ts_cycle
Group By nvl(csi.item_supplied,cuh.INVENTORY_ITEM_ID) ,
cuh.ORGANIZATION_ID,
cuh.SUBINVENTORY_CODE,
(trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
- ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size,
cfrb.HISTORY_PERIODS,
cfrb.forecast_rule_id,
DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) ,
cfrb.FORECAST_METHOD,
cfrb.PERIOD_SIZE,
cfrb.ALPHA,
cfrb.BETA,
cfrb.WEIGHTED_AVG_PERIOD1,
cfrb.WEIGHTED_AVG_PERIOD2,
cfrb.WEIGHTED_AVG_PERIOD3,
cfrb.WEIGHTED_AVG_PERIOD4,
cfrb.WEIGHTED_AVG_PERIOD5,
cfrb.WEIGHTED_AVG_PERIOD6,
cfrb.WEIGHTED_AVG_PERIOD7,
cfrb.WEIGHTED_AVG_PERIOD8,
cfrb.WEIGHTED_AVG_PERIOD9,
cfrb.WEIGHTED_AVG_PERIOD10,
cfrb.WEIGHTED_AVG_PERIOD11,
cfrb.WEIGHTED_AVG_PERIOD12,
cpp.RECOMMEND_METHOD
UNION ALL
Select nvl(csi.item_supplied,cuom.INVENTORY_ITEM_ID),
cuom.ORGANIZATION_ID,
'-' SUBINVENTORY_CODE ,
(trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
- ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
cuom.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE,
sum(cuom.QUANTITY) QUANTITY,
6 HISTORY_DATA_TYPE,
cfrb.HISTORY_PERIODS,
cfrb.forecast_rule_id,
DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) FORECAST_PERIODS ,
cfrb.FORECAST_METHOD,
cfrb.PERIOD_SIZE,
cfrb.ALPHA,
cfrb.BETA,
cfrb.WEIGHTED_AVG_PERIOD1,
cfrb.WEIGHTED_AVG_PERIOD2,
cfrb.WEIGHTED_AVG_PERIOD3,
cfrb.WEIGHTED_AVG_PERIOD4,
cfrb.WEIGHTED_AVG_PERIOD5,
cfrb.WEIGHTED_AVG_PERIOD6,
cfrb.WEIGHTED_AVG_PERIOD7,
cfrb.WEIGHTED_AVG_PERIOD8,
cfrb.WEIGHTED_AVG_PERIOD9,
cfrb.WEIGHTED_AVG_PERIOD10,
cfrb.WEIGHTED_AVG_PERIOD11,
cfrb.WEIGHTED_AVG_PERIOD12,
cpp.RECOMMEND_METHOD
From CSP_PLANNING_PARAMETERS cpp,
CSP_USAGE_ORG_MV cuom,
CSP_SUPERSEDE_ITEMS csi,
CSP_FORECAST_RULES_B cfrb
Where cpp.level_id like P_Level_Id || '%'
And cpp.node_type = 'ORGANIZATION_WH'
And cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
And (cuom.PERIOD_START_DATE BETWEEN (trunc(sysdate) - cfrb.forecast_periods
* cfrb.period_size * l_ts_cycle) - cfrb.period_size * cfrb.history_periods - (cfrb.period_size - 1)
AND (trunc(sysdate) - cfrb.forecast_periods * period_size * l_ts_cycle))
And cuom.ORGANIZATION_ID = cpp.ORGANIZATION_ID
And csi.inventory_item_id(+) = cuom.inventory_item_id
And csi.organization_id (+) = cuom.organization_id
And csi.sub_inventory_code(+) = '-'
AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID
Group By nvl(csi.item_supplied,cuom.INVENTORY_ITEM_ID),
cuom.ORGANIZATION_ID,
(trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
- ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
cuom.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size,
cfrb.HISTORY_PERIODS,
cfrb.forecast_rule_id,
DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) ,
cfrb.FORECAST_METHOD,
cfrb.PERIOD_SIZE,
cfrb.ALPHA,
cfrb.BETA,
cfrb.WEIGHTED_AVG_PERIOD1,
cfrb.WEIGHTED_AVG_PERIOD2,
cfrb.WEIGHTED_AVG_PERIOD3,
cfrb.WEIGHTED_AVG_PERIOD4,
cfrb.WEIGHTED_AVG_PERIOD5,
cfrb.WEIGHTED_AVG_PERIOD6,
cfrb.WEIGHTED_AVG_PERIOD7,
cfrb.WEIGHTED_AVG_PERIOD8,
cfrb.WEIGHTED_AVG_PERIOD9,
cfrb.WEIGHTED_AVG_PERIOD10,
cfrb.WEIGHTED_AVG_PERIOD11,
cfrb.WEIGHTED_AVG_PERIOD12,
cpp.RECOMMEND_METHOD
Order By 1,2,3,4;
SELECT nvl(max(tracking_signal_cycle),0)
INTO l_max_ts_cycle
FROM csp_forecast_rules_b;
INSERT INTO CSP_USAGE_HISTORIES (Usage_Id,
created_by,
creation_date,
last_updated_by,
last_update_date,
inventory_item_id,
organization_id,
subinventory_code,
period_type,
period_start_date,
quantity,
history_data_type)
VALUES ( csp_usage_histories_s1.nextval,
fnd_global.user_id, sysdate,
fnd_global.user_id,sysdate,
l_Prev_Rec.Inventory_Item_id,
l_Prev_Rec.Organization_id,
l_Prev_Rec.Subinventory_code,
3,
(trunc(sysdate) - l_prev_rec.forecast_periods * l_prev_rec.period_size * l_ts_cycle) +
(l_prev_rec.period_size * (l_Index -1)),
ROUND(decode(sign(l_Forecast_Qty_Tbl(l_Index)),-1,0,l_Forecast_Qty_Tbl(l_Index)),4),
decode(p_reason_code,'RECM',2,'TS',5));
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
STANDARD_DEVIATION,
LEAD_TIME,
PROCESS_STATUS,
EXTERNAL_DATA,
COMMENTS,
ITEM_COST,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (NULL,
l_prev_Rec.inventory_Item_Id,
l_prev_rec.Organization_Id,
l_prev_rec.Subinventory_code,
l_prev_rec.History_Data_Type,
NULL,
l_Awu,
l_Standard_Deviation,
NULL,
'O',
'N',
NULL,
NULL,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.conc_login_id);
l_Usage_Qty_Tbl.Delete;
l_Weighted_Avg_Tbl.Delete;
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
PROCESS_STATUS,
EXTERNAL_DATA,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
select NULL,
nvl(csi.item_supplied,cppf.inventory_item_id),
cppf.organization_id,
nvl(cppf.secondary_inventory,'-'),
7, -- Product Norm Usage
NULL,
cppf.current_population *
nvl(manual_failure_rate,calculated_failure_rate),
'O',
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
from csp_product_populations_fr_v cppf,
csp_supersede_items csi
where cppf.level_id like p_level_id || '%'
and cppf.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
and csi.inventory_item_id(+) = cppf.inventory_item_id
and csi.organization_id (+) = cppf.organization_id
and csi.sub_inventory_code (+) = cppf.secondary_inventory;
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
PROCESS_STATUS,
EXTERNAL_DATA,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
select NULL,
nvl(csi.item_supplied,cnpp.inventory_item_id),
cnpp.organization_id,
cnpp.secondary_inventory,
8, -- New Product planning
NULL,
cnpp.population_change *
nvl(cnpp.manual_failure_rate,
cnpp.calculated_failure_rate),
'O',
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
from csp_new_product_planning_v cnpp,
csp_supersede_items csi
where cnpp.level_id like p_level_id || '%'
and csi.inventory_item_id(+) = cnpp.inventory_item_id
and csi.organization_id (+) = cnpp.organization_id
and csi.sub_inventory_code (+) = cnpp.secondary_inventory;
INSERT INTO CSP_USAGE_HEADERS
(USAGE_HEADER_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
HEADER_DATA_TYPE,
RAW_AWU,
AWU,
PROCESS_STATUS,
EXTERNAL_DATA,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
select NULL,
nvl(csi.item_supplied,curos.inventory_item_id),
curos.organization_id,
curos.secondary_inventory,
9, -- Territory Norm
NULL,
curos.awu,
'O',
'N',
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_login_id
from csp_usage_reg_org_subinv_v curos,
csp_supersede_items csi
where curos.level_id like p_level_id || '%'
and csi.inventory_item_id (+) = curos.inventory_item_id
and csi.organization_id (+) = curos.organization_id
and csi.sub_inventory_code(+) = curos.secondary_inventory
group by nvl(csi.item_supplied,curos.inventory_item_id),
curos.organization_id,curos.secondary_inventory,curos.awu;
select node_type
from csp_planning_parameters
where level_id = p_level_id;
INSERT INTO CSP_SUPPLY_CHAIN (
source_type,
source_organization_id,
source_subinventory,
organization_id,
secondary_inventory,
inventory_item_id,
lead_time,
supply_level,
creation_date,
created_by,
last_updated_by,
last_update_date)
select /*+ parallel(MSIB,8) parallel(MISI,8) parallel(MSI,8)
parallel(CSC,8) */
nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
cri.organization_id,
cri.secondary_inventory,
cri.inventory_item_id,
NULL,
1, -- supply_level
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate
from csp_region_items_v cri,
mtl_related_items mri,
mtl_parameters mp,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
mtl_secondary_inventories msi
where cri.level_id like l_level_id || '%'
and mp.organization_id = cri.organization_id
and mri.organization_id = mp.master_organization_id
and mri.inventory_item_id = cri.inventory_item_id
and misi.organization_id (+) = cri.organization_id
and misi.inventory_item_id (+) = cri.inventory_item_id
and misi.secondary_inventory (+) = cri.secondary_inventory
and msib.organization_id = cri.organization_id
and msib.inventory_item_id = cri.inventory_item_id
and msi.organization_id = cri.organization_id
and msi.secondary_inventory_name = cri.secondary_inventory
Group By nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
cri.organization_id,
cri.secondary_inventory,
cri.inventory_item_id;
insert into csp_supply_chain(
source_type,
source_organization_id,
source_subinventory,
organization_id,
secondary_inventory,
inventory_item_id,
lead_time,
supply_level,
creation_date,
created_by,
last_updated_by,
last_update_date)
select
nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
csc.source_organization_id organization_id,
nvl(csc.source_subinventory,'-') subinventory_code,
csc.inventory_item_id,
NULL,
l_supply_level,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate
from mtl_parameters mp,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
mtl_secondary_inventories msi,
csp_supply_chain csc
where mp.organization_id = csc.source_organization_id
and misi.organization_id (+) = csc.source_organization_id
and misi.inventory_item_id (+) = csc.inventory_item_id
and misi.secondary_inventory (+) = csc.source_subinventory
and msib.organization_id = csc.source_organization_id
and msib.inventory_item_id = csc.inventory_item_id
and msi.organization_id (+) = csc.source_organization_id
and msi.secondary_inventory_name (+) = csc.source_subinventory
and csc.supply_level = l_supply_level - 1
and csc.source_type IN (1,3)
Group By
nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
csc.source_organization_id ,
nvl(csc.source_subinventory,'-') ,
csc.inventory_item_id;
Select MAX(HISTORY_PERIODS * PERIOD_SIZE) PERIOD_SIZE
From CSP_PLANNING_PARAMETERS cpp,
CSP_FORECAST_RULES_B cfrb
Where cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
INSERT INTO CSP_SUPPLY_CHAIN (
source_type,
source_organization_id,
source_subinventory,
organization_id,
secondary_inventory,
inventory_item_id,
lead_time,
supply_level,
creation_date,
created_by,
last_updated_by,
last_update_date)
select /*+ parallel(MSIB,8) parallel(MISI,8) parallel(MSI,8)
parallel(CSC,8) */
nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
mmt.organization_id,
mmt.subinventory_code,
mmt.inventory_item_id,
nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) lead_time,
1, -- supply_level
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate
from mtl_parameters mp,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
mtl_secondary_inventories msi,
mtl_material_transactions mmt,
mtl_interorg_ship_methods mism
where mp.organization_id = mmt.organization_id
and misi.organization_id (+) = mmt.organization_id
and misi.inventory_item_id (+) = mmt.inventory_item_id
and misi.secondary_inventory (+) = mmt.subinventory_code
and mism.to_organization_id (+) = mp.organization_id
and mism.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
and mism.default_flag (+) = 1
and msib.organization_id = mmt.organization_id
and msib.inventory_item_id = mmt.inventory_item_id
and msi.organization_id = mmt.organization_id
and msi.secondary_inventory_name = mmt.subinventory_code
and mmt.transaction_action_id = g_txn_action_id
and (mmt.transaction_date > (trunc(sysdate) - l_period_size - 1) and
mmt.transaction_date < trunc(sysdate))
Group By nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
mmt.organization_id,
mmt.subinventory_code,
mmt.inventory_item_id,
nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ,
1,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate;
insert into csp_supply_chain(
source_type,
source_organization_id,
source_subinventory,
organization_id,
secondary_inventory,
inventory_item_id,
lead_time,
supply_level,
creation_date,
created_by,
last_updated_by,
last_update_date)
select
nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
csc.source_organization_id organization_id,
nvl(csc.source_subinventory,'-') subinventory_code,
csc.inventory_item_id,
nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0, nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0),
nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)), nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) lead_time,
l_supply_level,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate
from mtl_parameters mp,
mtl_system_items_b msib,
mtl_item_sub_inventories misi,
mtl_secondary_inventories msi,
csp_supply_chain csc,
mtl_interorg_ship_methods mism
where mp.organization_id = csc.source_organization_id
and misi.organization_id (+) = csc.source_organization_id
and misi.inventory_item_id (+) = csc.inventory_item_id
and misi.secondary_inventory (+) = csc.source_subinventory
and mism.to_organization_id (+) = mp.organization_id
and mism.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
and mism.default_flag (+) = g_default_flag
and msib.organization_id = csc.source_organization_id
and msib.inventory_item_id = csc.inventory_item_id
and msi.organization_id (+) = csc.source_organization_id
and msi.secondary_inventory_name (+) = csc.source_subinventory
and csc.supply_level = l_supply_level - 1
and csc.source_type IN (1,3)
Group By
nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
csc.source_organization_id ,
nvl(csc.source_subinventory,'-') ,
csc.inventory_item_id,
nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0, nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0),
nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)), nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ,
l_supply_level,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate;
insert into csp_usage_histories(
organization_id,
subinventory_code,
inventory_item_id,
period_start_date,
transaction_type_id,
quantity,
history_data_type,
period_type,
created_by,
creation_date,
last_updated_by,
last_update_date)
select mmt.organization_id,
mmt.subinventory_code,
mmt.inventory_item_id,
trunc(transaction_date),
transaction_type_id,
sum(mmt.primary_quantity) * -1 primary_quantity,
1, -- History data type
3, -- Period type
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
from mtl_material_transactions mmt
where mmt.creation_date >
decode(G_LAST_RUN_DATE,fnd_api.g_miss_date,G_LAST_RUN_DATE,
to_date(to_char(G_LAST_RUN_DATE,'dd/mm/yy') || ' 23:59:59','dd/mm/yy hh24:mi:ss')) And mmt.creation_date < trunc(sysdate)
and mmt.transaction_action_id = g_txn_action_id
group by
mmt.organization_id,
mmt.subinventory_code,
mmt.inventory_item_id,
trunc(transaction_date),
transaction_type_id,
1, -- History data type
3, -- Period type
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate;
insert into csp_usage_histories(
organization_id,
subinventory_code,
inventory_item_id,
period_start_date,
transaction_type_id,
quantity,
history_data_type,
period_type,
created_by,
creation_date,
last_updated_by,
last_update_date)
select /*+ ORDERED */
nvl(csc.source_organization_id,-1),
nvl(csc.source_subinventory,'-'),
csc.inventory_item_id,
trunc(cuh.period_start_date),
transaction_type_id,
sum(cuh.quantity),
1,--heh decode(csc.source_type,2,3,1), -- History data type
3, -- Period type
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate
from csp_supply_chain csc,
csp_usage_histories cuh
where cuh.history_data_type = 1
and cuh.period_start_date > G_LAST_RUN_DATE
and cuh.organization_id = csc.organization_id
and cuh.subinventory_code = csc.secondary_inventory
and cuh.inventory_item_id = csc.inventory_item_id
and csc.supply_level = l_supply_level
group by
nvl(csc.source_organization_id,-1),
nvl(csc.source_subinventory,'-'),
csc.inventory_item_id,
trunc(cuh.period_start_date),
transaction_type_id,
1,--heh decode(csc.source_type,2,3,1), -- History data type
3, -- Period type
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate;
Select decode(cuh.RECOMMENDED_MAX_QUANTITY,0,0,greatest(1,cuh.RECOMMENDED_MIN_QUANTITY)) RECOMMENDED_MIN_QUANTITY,
cuh.RECOMMENDED_MAX_QUANTITY,
cuh.INVENTORY_ITEM_ID,
cuh.ORGANIZATION_ID,
cuh.SECONDARY_INVENTORY
From csp_usage_headers cuh,
csp_planning_parameters cpp,
mtl_item_sub_inventories misi,
csp_business_rules_b cbrb
Where header_data_type = 1
and process_status = 'O'
and cuh.planning_parameters_id = cpp.planning_parameters_id
and cpp.node_type = 'SUBINVENTORY'
and misi.organization_id(+) = cuh.organization_id
and misi.inventory_item_id(+) = cuh.inventory_item_id
and misi.secondary_inventory(+) = cuh.secondary_inventory
And cbrb.business_rule_id = cpp.recommendation_rule_id
And nvl(cbrb.business_rule_value3,1) >
abs(decode(cbrb.business_rule_value3,null,0,
nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
nvl(misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0)))
And nvl(cbrb.business_rule_value4,1) >
abs(decode(cbrb.business_rule_value4,null,0,
ROUND((nvl((cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY) *
cuh.item_cost,0)/
DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0) *
nvl(cuh.item_cost ,0),0,1,misi.MAX_MINMAX_QUANTITY*cuh.item_cost)) * 100,2)))
And nvl(cbrb.business_rule_value5,1) >
abs(decode(cbrb.business_rule_value5,null,0,
nvl(cuh.RECOMMENDED_MAX_QUANTITY - misi.MAX_MINMAX_QUANTITY,0)))
And nvl(cbrb.business_rule_value6,1) >
abs(decode(cbrb.business_rule_value6,null,0,
ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY,0)/
DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0),0,1,
misi.MAX_MINMAX_QUANTITY)) * 100,2)))
And (nvl(cuh.tracking_signal,0) >= decode(cpp.recommend_method,'PNORM',0,
'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
And nvl(cuh.tracking_signal,0) <= decode(cpp.recommend_method,'PNORM',0,
'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))));
Select cuh.inventory_item_id,
cuh.organization_id,
cuh.recommended_min_quantity,
cuh.recommended_max_quantity
from CSP_USAGE_HEADERS cuh,
CSP_PLANNING_PARAMETERS cpp,
MTL_SYSTEM_ITEMS_B msib,
CSP_BUSINESS_RULES_B cbrb
Where msib.INVENTORY_ITEM_ID = cuh.INVENTORY_ITEM_ID
And msib.ORGANIZATION_ID = cuh.ORGANIZATION_ID
And cuh.header_data_type = 4
And cuh.process_status = 'O'
And cpp.node_type = 'ORGANIZATION_WH'
And cpp.organization_id = cuh.organization_id
And cbrb.business_rule_id = cpp.recommendation_rule_id
And nvl(cbrb.business_rule_value3,1) >
abs(decode(cbrb.business_rule_value3,null,0,
nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
nvl(msib.MAX_MINMAX_QUANTITY * cuh.item_cost,0)))
And nvl(cbrb.business_rule_value4,1) >
abs(decode(cbrb.business_rule_value4,null,0,
ROUND((nvl((cuh.recommended_max_quantity - msib.MAX_MINMAX_QUANTITY) *
cuh.item_cost,0)/
DECODE(nvl(msib.MAX_MINMAX_QUANTITY,0) *
nvl(cuh.item_cost ,0),0,1,msib.MAX_MINMAX_QUANTITY*cuh.item_cost)) * 100,2)))
And nvl(cbrb.business_rule_value5,1) >
abs(decode(cbrb.business_rule_value5,null,0,
nvl(cuh.RECOMMENDED_MAX_QUANTITY - msib.MAX_MINMAX_QUANTITY,0)))
And nvl(cbrb.business_rule_value6,1) >
abs(decode(cbrb.business_rule_value6,null,0,
ROUND((nvl(cuh.recommended_max_quantity - msib.MAX_MINMAX_QUANTITY,0)/
DECODE(nvl(msib.MAX_MINMAX_QUANTITY,0),0,1,
msib.MAX_MINMAX_QUANTITY)) * 100,2)))
And (nvl(cuh.tracking_signal,0) >= decode(cpp.recommend_method,'PNORM',0,
'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
And nvl(cuh.tracking_signal,0) <= decode(cpp.recommend_method,'PNORM',0,
'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))));
(Select cuh.Inventory_Item_Id,
cuh.Organization_Id,
cuh.secondary_inventory,
cuh.recommended_min_quantity,
cuh.recommended_max_quantity
From csp_usage_headers cuh,
csp_planning_parameters cpp,
mtl_item_sub_inventories misi,
CSP.csp_business_rules_b cbrb
Where cuh.planning_parameters_id = cpp.planning_parameters_id
and misi.organization_id(+) = cuh.organization_id
and misi.inventory_item_id(+) = cuh.inventory_item_id
and misi.secondary_inventory(+) = cuh.secondary_inventory
And cbrb.business_rule_id = cpp.recommendation_rule_id
And nvl(cbrb.business_rule_value3,1) >
decode(cbrb.business_rule_value3,null,0,nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
nvl(misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0))
And nvl(cbrb.business_rule_value4,1) >
decode(cbrb.business_rule_value4,null,0,
ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0)/
DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0) * nvl(cuh.item_cost ,0),0,1,misi.MAX_MINMAX_QUANTITY)) * 100,2))
And nvl(cbrb.business_rule_value5,1) >
decode(cbrb.business_rule_value5,null,0,nvl(cuh.RECOMMENDED_MAX_QUANTITY - misi.MAX_MINMAX_QUANTITY,0))
And nvl(cbrb.business_rule_value6,1) >
decode(cbrb.business_rule_value6,null,0,
ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY,0)/DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0),0,1,
misi.MAX_MINMAX_QUANTITY)) * 100,2))
And (nvl(cuh.tracking_signal,0) >=
decode(cpp.recommend_method,'PNORM',0,'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
And nvl(cuh.tracking_signal,0) <=
decode(cpp.recommend_method,'PNORM',0,'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))))) sq
ON (item_subinv.INVENTORY_ITEM_ID = sq.INVENTORY_ITEM_ID
AND item_subinv.ORGANIZATION_ID = sq.ORGANIZATION_ID
AND item_subinv.SECONDARY_INVENTORY = sq.SECONDARY_INVENTORY)
WHEN MATCHED THEN UPDATE SET item_subinv.MIN_MINMAX_QUANTITY = decode(sq.RECOMMENDED_MAX_QUANTITY,0,0,greatest(1,sq.RECOMMENDED_MIN_QUANTITY)),
item_subinv.MAX_MINMAX_QUANTITY = sq.RECOMMENDED_MAX_QUANTITY,
item_subinv.INVENTORY_PLANNING_CODE = 2
WHEN NOT MATCHED THEN INSERT(item_subinv.INVENTORY_ITEM_ID,
item_subinv.ORGANIZATION_ID,
item_subinv.SECONDARY_INVENTORY,
item_subinv.LAST_UPDATE_DATE,
item_subinv.LAST_UPDATED_BY,
item_subinv.CREATION_DATE,
item_subinv.CREATED_BY,
item_subinv.LAST_UPDATE_LOGIN,
item_subinv.MIN_MINMAX_QUANTITY,
item_subinv.MAX_MINMAX_QUANTITY,
item_subinv.INVENTORY_PLANNING_CODE)
VALUES (sq.INVENTORY_ITEM_ID,
sq.ORGANIZATION_ID,
sq.SECONDARY_INVENTORY,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
sq.RECOMMENDED_MIN_QUANTITY,
sq.RECOMMENDED_MAX_QUANTITY,2);
UPDATE MTL_ITEM_SUB_INVENTORIES item_subinv
SET MIN_MINMAX_QUANTITY = usg_hdr_rec.RECOMMENDED_MIN_QUANTITY,
MAX_MINMAX_QUANTITY = usg_hdr_rec.RECOMMENDED_MAX_QUANTITY,
INVENTORY_PLANNING_CODE = 2
WHERE item_subinv.INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
and item_subinv.ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
and item_subinv.SECONDARY_INVENTORY = usg_hdr_rec.SECONDARY_INVENTORY;
INSERT INTO MTL_ITEM_SUB_INVENTORIES
(INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SECONDARY_INVENTORY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
MIN_MINMAX_QUANTITY,
MAX_MINMAX_QUANTITY,
INVENTORY_PLANNING_CODE)
VALUES (usg_hdr_rec.INVENTORY_ITEM_ID,
usg_hdr_rec.ORGANIZATION_ID,
usg_hdr_rec.SECONDARY_INVENTORY,
sysdate,
FND_GLOBAL.user_id,
sysdate,
FND_GLOBAL.user_id,
FND_GLOBAL.conc_login_id,
usg_hdr_rec.RECOMMENDED_MIN_QUANTITY,
usg_hdr_rec.RECOMMENDED_MAX_QUANTITY,2);
UPDATE CSP_USAGE_HEADERS
SET PROCESS_STATUS = 'C'
WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
AND SECONDARY_INVENTORY = usg_hdr_rec.SECONDARY_INVENTORY
AND HEADER_DATA_TYPE = 1
AND PROCESS_STATUS = 'O';
UPDATE mtl_system_items_b mtl_items
Set min_minmax_quantity = usg_hdr_rec.recommended_min_quantity,
max_minmax_quantity = usg_hdr_rec.recommended_max_quantity,
mtl_items.inventory_planning_code = 2
WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID;
UPDATE CSP_USAGE_HEADERS
SET PROCESS_STATUS = 'C'
WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
AND HEADER_DATA_TYPE = 4
AND PROCESS_STATUS = 'O';
select nvl(cuh.standard_deviation,0) standard_deviation,
cuh.awu ,
cuh.item_cost,
cuh.lead_time,
cpp.edq_factor,
cpp.service_level
from csp_usage_headers cuh,
csp_planning_parameters cpp
where cuh.inventory_item_id = p_item_id
and cuh.organization_id = p_org_id
and cuh.secondary_inventory = '-'
and cuh.header_data_type = 4
and cpp.organization_id = p_org_id
and cpp.secondary_inventory is null;