[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
fnd_msg_pub.delete_msg;
select cwrv.wrp_rule_id,
cwrv.excess_value_limit,
cwrv.excess_ts_min,
cwrv.excess_ts_max,
cwrv.excess_lead_time,
cwrv.rep_int_value_limit,
cwrv.rep_int_ts_min,
cwrv.rep_int_ts_max,
cwrv.rep_int_lead_time,
cwrv.rep_ext_value_limit,
cwrv.rep_ext_ts_min,
cwrv.rep_ext_ts_max,
cwrv.rep_ext_lead_time,
cwrv.nb_int_value_limit,
cwrv.nb_int_ts_min,
cwrv.nb_int_ts_max,
cwrv.nb_int_lead_time,
cwrv.nb_ext_value_limit,
cwrv.nb_ext_ts_min,
cwrv.nb_ext_ts_max,
cwrv.nb_ext_lead_time
from csp_wrp_rules_vl cwrv,
csp_planning_parameters cpp
where cpp.organization_id = l_organization_id
and cpp.organization_type = 'W'
and cwrv.wrp_rule_id = cpp.wrp_rule_id;
select cpd.inventory_item_id,
nvl(cpd.related_item_id, cpd.inventory_item_id) supplied_item_id,
cpd.plan_detail_type,
cpd.source_organization_id,
cpd.quantity,
cpd.plan_date,
nvl(cuh.tracking_signal,0) tracking_signal,
nvl(cpl.newbuy_lead_time,0) newbuy_lead_time,
nvl(cpl.repair_lead_time,0) repair_lead_time,
nvl(cpl.excess_lead_time,0) excess_lead_time,
nvl(cic.item_cost,0) item_cost
from csp_plan_details cpd,
csp_plan_leadtimes cpl,
cst_item_costs cic,
mtl_parameters mp,
csp_usage_headers cuh
where cpd.organization_id = l_organization_id
and cpl.organization_id = cpd.organization_id
and cpl.inventory_item_id = cpd.inventory_item_id
and cpd.plan_detail_type in ('4110','4210','4310')
and cic.organization_id = cpd.organization_id
and cic.inventory_item_id = cpd.inventory_item_id
and cic.cost_type_id = mp.primary_cost_method
and mp.organization_id = cpd.organization_id
and cuh.organization_id(+) = cpd.organization_id
and cuh.inventory_item_id(+) = cpd.inventory_item_id
and cuh.header_data_type(+) = '4'
and nvl(cic.item_cost,0) > 0;
select decode(misl.source_type,1,'INTERNAL','EXTERNAL')
from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp
where cpp.organization_id = p_organization_id
and misl.organization_id = cpp.organization_id
and misl.assignment_set_id =cpp.repair_assignment_set_id
and inventory_item_id = p_supplied_item_id
and SOURCE_TYPE in (1,3)
and sourcing_level = (select min(sourcing_level) from
MRP_ITEM_SOURCING_LEVELS_V
where organization_id = p_organization_id
and assignment_set_id = cpp.repair_assignment_set_id
and inventory_item_id = p_supplied_item_id
and sourcing_level not in (2,9))
order by misl.rank;
select decode(nvl(msi.source_type,mp.source_type),1,'INTERNAL','EXTERNAL')
from mtl_system_items msi,
mtl_parameters mp
where mp.organization_id = msi.organization_id
and msi.organization_id = p_organization_id
and msi.inventory_item_id = p_supplied_item_id;
select decode(cpd.parent_type,'8611','NewBuy Internal Inside ',
'8612','NewBuy Internal Outside',
'8613','NewBuy External Inside ',
'8614','NewBuy External Outside',
'8621','Repair Internal Inside ',
'8622','Repair Internal Outside',
'8623','Repair External Inside ',
'8624','Repair External Outside',
'8631','Excess Internal Inside ',
'8632','Excess Internal Outside',
'.......................') ||
lpad(to_char(count(*)),15,' ') ||
lpad(to_char(round(
sum(cpd.quantity * cic.item_cost),2)),15,' ') ||
lpad(to_char(sum(cpd.quantity)),15,' ') ||
lpad(to_char(round(avg(
nvl(cuh.tracking_signal,0)),2)),11,' ') statistics
from csp_plan_details cpd,
cst_item_costs cic,
mtl_parameters mp,
csp_usage_headers cuh
where cic.organization_id = cpd.organization_id
and cic.inventory_item_id = cpd.inventory_item_id
and cpd.plan_detail_type in ('8610','8620','8630')
and mp.organization_id = cpd.organization_id
and cic.cost_type_id = mp.primary_cost_method
and cuh.organization_id(+) = cpd.organization_id
and cuh.inventory_item_id(+) = cpd.inventory_item_id
and cuh.header_data_type(+) = '4'
group by cpd.parent_type;
update csp_plan_details
set plan_detail_type = decode(cr.plan_detail_type,'4110','8630',
'4210','8620',
'4310','8610'),
parent_type = l_parent_type
where inventory_item_id = cr.inventory_item_id
and organization_id = l_organization_id
and plan_detail_type = cr.plan_detail_type
and quantity = cr.quantity
and plan_date = cr.plan_date;
delete from csp_plan_leadtimes
where organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id);
insert into csp_plan_leadtimes(
inventory_item_id,
organization_id,
excess_lead_time,
repair_lead_time,
newbuy_lead_time,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select
cpd.inventory_item_id,
cpd.organization_id,
(select max(nvl(mism1.intransit_time, 0))
from MRP_ITEM_SOURCING_LEVELS_V misl, csp_planning_parameters cpp, mtl_interorg_ship_methods mism1
where mism1.to_organization_id = cpp.organization_id
and mism1.from_organization_id = misl.source_organization_id
and mism1.default_flag = 1
and cpp.organization_id = cpd.organization_id
and misl.organization_id = cpp.organization_id
and misl.assignment_set_id =cpp.usable_assignment_set_id
and misl.inventory_item_id = cpd.inventory_item_id
and misl.SOURCE_TYPE = 1
and sourcing_level = (select min(sourcing_level)
from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = cpd.organization_id
and assignment_set_id = cpp.usable_assignment_set_id
and inventory_item_id = cpd.inventory_item_id
and sourcing_level not in (2,9))) Excess_Lead_Time,
(select max(nvl(mism.intransit_time, 0) +
nvl(msib.repair_leadtime, 0) +
(select nvl(max(nvl(mism2.intransit_time, 0)), 0)
from MRP_ITEM_SOURCING_LEVELS_V misl1,
csp_planning_parameters cpp,
mtl_interorg_ship_methods mism2
where mism2.to_organization_id = decode(misl.source_type, 1, misl.source_organization_id, 3, hoi.organization_id)
and mism2.from_organization_id = misl1.source_organization_id
and mism2.default_flag = 1
and cpp.organization_id = cpd.organization_id
and misl1.organization_id = cpp.organization_id
and misl1.assignment_set_id =cpp.defective_assignment_set_id
and misl1.inventory_item_id = cpd.inventory_item_id
and SOURCE_TYPE = 1
and sourcing_level = (select min(sourcing_level)
from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = cpd.organization_id
and assignment_set_id = cpp.defective_assignment_set_id
and inventory_item_id = cpd.inventory_item_id
and sourcing_level not in (2,9))
))
from MRP_ITEM_SOURCING_LEVELS_V misl,
csp_planning_parameters cpp,
mtl_interorg_ship_methods mism,
mtl_system_items_b msib,
hr_organization_information hoi
where msib.inventory_item_id = cpd.inventory_item_id
and msib.organization_id = decode(misl.source_type, 1, misl.source_organization_id, 3, hoi.organization_id)
and mism.to_organization_id = misl.organization_id
and mism.from_organization_id = decode(misl.source_type, 1, misl.source_organization_id, 3, hoi.organization_id)
and mism.default_flag = 1
and cpp.organization_id = cpd.organization_id
and misl.organization_id = cpp.organization_id
and misl.assignment_set_id = cpp.repair_assignment_set_id
and misl.inventory_item_id = cpd.inventory_item_id
and misl.SOURCE_TYPE in ( 1, 3)
and sourcing_level = (select min(sourcing_level)
from MRP_ITEM_SOURCING_LEVELS_V
where organization_id = cpd.organization_id
and assignment_set_id = cpp.repair_assignment_set_id
and inventory_item_id = cpd.inventory_item_id
and sourcing_level not in (2,9))
and hoi.ORG_INFORMATION_CONTEXT(+) = 'Customer/Supplier Association'
and hoi.org_information3(+) = misl.vendor_id
) Repair_Lead_time,
(select decode(nvl(msib.preprocessing_lead_time, 0) +
nvl(msib.full_lead_time, 0) +
nvl(msib.postprocessing_lead_time,0),0,null,
nvl(msib.preprocessing_lead_time, 0) +
nvl(msib.full_lead_time, 0) +
nvl(msib.postprocessing_lead_time,0))
from mtl_system_items_b msib
where inventory_item_id = cpd.inventory_item_id
and organization_id = cpd.organization_id) NewBuy_Lead_Time,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_Details cpd
where plan_detail_type = '1'
and organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id, inventory_item_id)
group by organization_id, inventory_item_id;
delete from csp_plan_reorders
where organization_id = p_organization_id
and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id);
insert into csp_plan_reorders(
inventory_item_id,
organization_id,
excess_rop,
repair_rop,
newbuy_rop,
excess_edq,
repair_edq,
newbuy_edq,
excess_safety_stock,
repair_safety_stock,
newbuy_safety_stock,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
(select b.inventory_item_id,
b.organization_id,
(nvl(csf.safety_factor,0) * b.standard_deviation + b.excess_total_req) excess_rop,
(nvl(csf1.safety_factor, 0) * b.standard_deviation + b.repair_total_req) repair_rop,
(nvl(csf2.safety_factor, 0) * b.standard_deviation + b.newbuy_total_req) newbuy_rop,
b.excess_edq,
b.repair_edq,
b.newbuy_edq,
(nvl(csf.safety_factor,0) * nvl(b.standard_deviation, 0)) excess_safety_stock,
(nvl(csf1.safety_factor,0) * nvl(b.standard_deviation, 0)) repair_safety_stock,
(nvl(csf2.safety_factor,0) * nvl(b.standard_deviation, 0)) newbuy_safety_stock,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_safety_factors csf,
csp_Safety_factors csf1,
csp_safety_factors csf2,
(select decode(nvl(cpp1.safety_stock_flag, 'N'), 'N', 0, decode(a.excess_awr, 0, 0 , decode(nvl(cuh.item_cost, 0), 0, 0, decode(nvl(cipp.excess_edq_factor, cpp1.excess_edq_factor), 0, 0,
LEAST(52, GREATEST(3, ROUND(a.excess_awr * 52/(ROUND(nvl(cipp.excess_edq_factor, cpp1.excess_edq_factor) * (SQRT(52 * a.excess_awr * cuh.item_Cost)/cuh.item_Cost),4))))))))) excess_exposures
, a.excess_total_req
, decode(cic.item_cost, 0, 0, decode(a.excess_awr, 0, 0, ROUND(nvl(cipp.excess_edq_factor, cpp1.excess_edq_factor) * (SQRT(52 * a.excess_awr * cic.item_Cost)/cic.item_Cost),4))) Excess_EDQ
, decode(nvl(cpp1.safety_stock_flag, 'N'), 'N', 0, decode(a.repair_awr, 0, 0, decode(nvl(cic.item_cost, 0), 0, 0, decode(nvl(cipp.repair_edq_factor, cpp1.repair_edq_factor), 0, 0,
LEAST(52, GREATEST(3, ROUND(a.repair_awr * 52/(ROUND(nvl(cipp.repair_Edq_factor, cpp1.repair_edq_factor) * (SQRT(52 * a.repair_awr * cic.item_cost)/cic.item_cost),4))))))))) repair_exposures
, a.repair_total_req
, decode(cic.item_cost, 0, 0, decode(a.repair_awr, 0, 0, ROUND(nvl(cipp.repair_edq_Factor, cpp1.repair_edq_factor) * (SQRT(52 * a.repair_awr * cic.item_cost)/cic.item_cost),4))) Repair_EDQ
, decode(nvl(cpp1.safety_stock_flag, 'N'), 'N', 0, decode(a.newbuy_awr, 0, 0, decode(nvl(cic.item_cost, 0), 0, 0, decode(nvl(cipp.newbuy_edq_factor, cpp1.newbuy_edq_factor), 0, 0,
LEAST(52, GREATEST(3, ROUND(a.newbuy_awr * 52/(ROUND(nvl(cipp.newbuy_edq_factor, cpp1.newbuy_edq_factor) * (SQRT(52 * a.newbuy_awr * cic.item_cost)/cic.item_cost),4))))))))) newbuy_exposures
, a.newbuy_total_req
, decode(cic.item_cost, 0, 0, decode(a.newbuy_awr, 0, 0, ROUND(nvl(cipp.newbuy_edq_factor, cpp1.newbuy_edq_factor) * (SQRT(52 * a.newbuy_awr * cic.item_cost)/cic.item_cost),4))) NewBuy_EDQ
, a.inventory_item_id
, a.organization_id
, nvl(nvl(cipp.excess_service_level, cpp1.excess_service_level), cpp1.service_level) excess_service_level
,nvl(nvl(cipp.repair_service_level, cpp1.repair_service_level), cpp1.service_level) repair_service_level
,nvl(nvl(cipp.newbuy_service_level, cpp1.newbuy_service_level), cpp1.service_level) newbuy_service_level
,nvl(cuh.standard_deviation, 0) standard_deviation
from csp_usage_headers cuh,
cst_item_costs cic,
mtl_parameters mp,
csp_planning_parameters cpp1,
csp_item_pl_params cipp,
(select decode(nvl(cpl.Excess_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
sum(decode(sign(cpl.Excess_lead_time - cfrb.period_size), -1,
decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
/(cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.excess_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.Excess_lead_time - (cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4)) Excess_Total_Req,
(decode(nvl(cpl.Excess_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
sum(decode(sign(cpl.Excess_lead_time - cfrb.period_size), -1,
decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
/(cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.excess_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.Excess_lead_time - (cfrb.period_size *
floor(cpl.Excess_Lead_Time/cfrb.period_size)))/cfrb.period_size) , 4))/ cpl.Excess_Lead_Time) * 7 excess_awr,
decode(nvl(cpl.Repair_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
floor(cpl.Repair_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
floor(cpl.Repair_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
sum(decode(sign(cpl.repair_lead_time - cfrb.period_size), -1,
decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
/(cfrb.period_size * floor(cpl.Repair_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date -
trunc(sysdate)- cpl.repair_lead_time), -1, cpd.quantity, 0), 0)))*
((cpl.Repair_lead_time - (cfrb.period_size * floor(cpl.Repair_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4)) Repair_Total_Req,
(decode(nvl(cpl.Repair_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
floor(cpl.Repair_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
floor(cpl.Repair_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
sum(decode(sign(cpl.repair_lead_time - cfrb.period_size), -1,
decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
/(cfrb.period_size *
floor(cpl.Repair_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.repair_lead_time), -1, cpd.quantity, 0), 0)))*
((cpl.Repair_lead_time - (cfrb.period_size *
floor(cpl.Repair_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4))/cpl.repair_lead_time) * 7 Repair_AWR,
decode(nvl(cpl.NewBuy_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
floor(cpl.NewBuy_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
sum(decode(sign(cpl.NewBuy_lead_time - cfrb.period_size), -1,
decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
/(cfrb.period_size * floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date
- trunc(sysdate)- cpl.newbuy_lead_time), -1, cpd.quantity, 0), 0)))*
((cpl.NewBuy_lead_time - (cfrb.period_size * floor(cpl.NewBuy_Lead_Time/cfrb.period_size)))/cfrb.period_size), 4)) NewBuy_Total_Req,
(decode(nvl(cpl.NewBuy_Lead_Time, 0), 0, 0, round(sum(decode((cfrb.period_size *
floor(cpl.NewBuy_Lead_Time/cfrb.period_size)), 0, 0, decode(floor((cpd.plan_date - trunc(sysdate))/(cfrb.period_size *
floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 0, cpd.quantity, 0))) +
sum(decode(sign(cpl.NewBuy_lead_time - cfrb.period_size), -1,
decode(sign((cpd.plan_date - trunc(sysdate)) - cfrb.period_size), -1, cpd.quantity, 0), decode(floor((cpd.plan_date - trunc(sysdate))
/(cfrb.period_size *
floor(cpl.NewBuy_Lead_Time/cfrb.period_size))), 1, decode(sign(plan_date - trunc(sysdate)- cpl.newbuy_lead_time), -1, cpd.quantity, 0), 0)))* ((cpl.NewBuy_lead_time - (cfrb.period_size *
floor(cpl.NewBuy_Lead_Time/cfrb.period_size)))/cfrb.period_size),4))/ cpl.newbuy_lead_time) * 7 NewBuy_AWR,
cpd.inventory_item_id,
cpd.organization_id
from csp_plan_details cpd,
csp_plan_leadtimes cpl,
csp_planning_parameters cpp,
csp_forecast_rules_b cfrb
where cpd.organization_id = p_organization_id
and cpd.inventory_item_id = nvl(p_inventory_item_id, cpd.inventory_item_id)
and cpd.plan_detail_type = 1000
and cpd.plan_Date between trunc(sysdate)
and trunc(sysdate) + greatest(nvl(cpl.excess_lead_time, 0), nvl(cpl.repair_lead_time, 0), cpl.newbuy_lead_time)
and cpl.inventory_item_id(+) = cpd.inventory_item_id
and cpl.organization_id(+) = cpd.organization_id
and cpp.organization_id = cpd.organization_id
and cfrb.forecast_rule_id = cpp.forecast_rule_id
group by cpd.organization_id, cpd.inventory_item_id, cfrb.period_size,
cpl.excess_lead_time, cpl.repair_lead_time, cpl.newbuy_lead_time) a
where cuh.organization_id(+) = a.organization_id
and cuh.inventory_item_id(+) = a.inventory_item_id
and cuh.secondary_inventory(+) = '-'
and cuh.header_data_type(+) = 4
and cpp1.organization_type = 'W'
and cpp1.organization_id = a.organization_id
and cipp.organization_id(+) = a.organization_id
and cipp.inventory_item_id(+) = a.inventory_item_id
and cic.inventory_item_id = a.inventory_item_id(+)
AND cic.organization_id = mp.organization_id
AND cic.cost_type_id = mp.primary_cost_method
AND mp.organization_id = a.organization_id) b
where csf.exposures(+) = b.excess_exposures
and csf.service_level(+) = b.excess_service_level
and csf1.exposures(+) = b.repair_exposures
and csf1.service_level(+) = b.repair_Service_level
and csf2.exposures(+) = b.newbuy_exposures
and csf2.service_level(+) = b.newbuy_Service_level );
insert into csp_plan_details(
plan_detail_type,
parent_type,
inventory_item_id,
related_item_id,
organization_id,
source_organization_id,
quantity,
plan_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select decode(cpd.related_item_id,null,min('6100'),min('6200')),
min('6000'),
cpd.inventory_item_id,
cpd.related_item_id,
cpd.organization_id,
cpd.source_organization_id,
sum(mmt.primary_quantity),
trunc(trunc(sysdate) - round((trunc(sysdate) - trunc(mmt.transaction_date))/cfrb.period_size)*cfrb.period_size),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_planning_parameters cpp,
csp_forecast_rules_b cfrb,
mtl_material_transactions mmt,
csp_usg_transaction_types cutt
where cpd.plan_detail_type in ('9002','9003')
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpp.organization_id = cpd.source_organization_id
and cfrb.forecast_rule_id = cpp.forecast_rule_id
and mmt.organization_id = cpd.source_organization_id
and mmt.inventory_item_id = nvl(cpd.related_item_id,cpd.inventory_item_id)
and cutt.forecast_rule_id = cpp.forecast_rule_id
and cutt.transaction_type_id = mmt.transaction_type_id
and mmt.transaction_date between trunc(sysdate) - (cfrb.history_periods*cfrb.period_size) and trunc(sysdate)
group by cpd.inventory_item_id,
cpd.related_item_id,
cpd.organization_id,
cpd.source_organization_id,
trunc(trunc(sysdate) - round((trunc(sysdate) - trunc(mmt.transaction_date))/cfrb.period_size)*cfrb.period_size);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
forecast_periods,
period_size)
select min('6000'),
null,
null,
cpd.source_organization_id,
sum(quantity),
cpd.plan_date,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
max(a.history_periods),
max(a.period_size)
from csp_plan_details cpd,
(select round(max(cfrb.history_periods*cfrb.period_size)/max(cfrb.period_size)+0.499999) history_periods,max(cfrb.period_size) period_size,cpd.organization_id,cpd.inventory_item_id
from csp_forecast_rules_b cfrb,
csp_planning_parameters cpp,
csp_plan_details cpd
where cfrb.forecast_rule_id = cpp.forecast_rule_id
and cpd.plan_detail_type in ('6100','6200')
and cpd.source_organization_id = cpp.organization_id
group by cpd.organization_id, cpd.inventory_item_id) a
where cpd.plan_detail_type in ('6100','6200')
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and a.inventory_item_id = cpd.inventory_item_id
and a.organization_id = cpd.organization_id
group by cpd.plan_date,cpd.inventory_item_id,cpd.organization_id,cpd.source_organization_id;
delete from csp_plan_details cpd
where cpd.plan_detail_type = '6100'
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and not exists
(select 'x'
from csp_plan_details
where plan_detail_type = '6200'
and inventory_item_id = cpd.inventory_item_id
and organization_id = cpd.organization_id);
select cpd.organization_id,
cpd.inventory_item_id,
l_history_periods - round((trunc(sysdate) - trunc(plan_date))/l_forecast_period_size) period,
quantity,
alpha,
beta,
nvl(cpl.repair_lead_time,0) repair_lead_time
from csp_plan_details cpd,
csp_plan_leadtimes cpl,
csp_forecast_rules_b cfrb
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = '6000'
and cfrb.forecast_rule_id = l_forecast_rule_id
and cpl.organization_id = cpd.organization_id
and cpl.inventory_item_id = cpd.inventory_item_id
order by cpd.organization_id,
cpd.inventory_item_id,
plan_date;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '7000',
null,
null,
null,
a.quantity,
trunc(sysdate + cpl.repair_lead_time + (rownum-1) * l_period_size),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_leadtimes cpl,
(select round(sum(cpd2.quantity* l_period_size /cfrb.period_size/cfrb.history_periods)) quantity,
cpd2.organization_id organization_id,
cpd2.inventory_item_id inventory_item_id
from csp_plan_details cpd2,
csp_forecast_rules_b cfrb,
csp_planning_parameters cpp
where cpd2.plan_detail_type = '6000'
and cpp.organization_id = cpd2.source_organization_id
and cfrb.forecast_rule_id = cpp.forecast_rule_id
and cfrb.forecast_method in (1,3,4)
group by cpd2.organization_id,
cpd2.inventory_item_id) a
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and a.organization_id = cpd.organization_id
and a.inventory_item_id = cpd.inventory_item_id
and cpl.organization_id = cpd.organization_id
and cpl.inventory_item_id = cpd.inventory_item_id
and rownum <= l_forecast_periods;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '7000',
null,
null,
null,
a.quantity,
trunc(sysdate + cpl.repair_lead_time + (rownum-1) * l_period_size),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_leadtimes cpl,
(select round(sum(quantity*decode(round((trunc(sysdate)-trunc(plan_date))/l_forecast_period_size),
1,weighted_avg_period1,
2,weighted_avg_period2,
3,weighted_avg_period3,
4,weighted_avg_period4,
5,weighted_avg_period5,
6,weighted_avg_period6,
7,weighted_avg_period7,
8,weighted_avg_period8,
9,weighted_avg_period9,
10,weighted_avg_period10,
11,weighted_avg_period11,
12,weighted_avg_period12))*l_period_size/l_forecast_period_size) quantity,
cpd2.organization_id organization_id,
cpd2.inventory_item_id inventory_item_id
from csp_plan_details cpd2,
csp_forecast_rules_b cfrb,
csp_planning_parameters cpp
where cpd2.plan_detail_type = '6000'
and cpp.organization_id = cpd2.source_organization_id
and cfrb.forecast_rule_id = cpp.forecast_rule_id
and cfrb.forecast_method = 2
group by cpd2.organization_id,
cpd2.inventory_item_id) a
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and a.organization_id = cpd.organization_id
and a.inventory_item_id = cpd.inventory_item_id
and cpl.organization_id = cpd.organization_id
and cpl.inventory_item_id = cpd.inventory_item_id
and rownum <= l_forecast_periods;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '7000',
null,
null,
null,
l_forecast * l_period_size/l_forecast_period_size,
trunc(sysdate + cr.repair_lead_time + (rownum-1) * l_period_size),
l_item,
l_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details
where organization_id = l_organization_id
and rownum <= l_forecast_periods;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '7000',
null,
null,
null,
l_forecast * l_period_size/l_forecast_period_size,
trunc(sysdate + l_repair_lead_time + (rownum -1) * l_period_size),
l_item,
l_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details
where organization_id = l_organization_id
and rownum <= l_forecast_periods;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '7000',
null,
null,
null,
l_forecast + l_trend * rownum * l_period_size/l_forecast_period_size,
trunc(sysdate + cr.repair_lead_time + (rownum-1) * l_period_size),
l_item,
l_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details
where organization_id = l_organization_id
and rownum <= l_forecast_periods;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '7000',
null,
null,
null,
l_forecast + l_trend * rownum * l_period_size/l_forecast_period_size,
trunc(sysdate + l_repair_lead_time + (rownum-1) * l_period_size),
l_item,
l_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details
where organization_id = l_organization_id
and rownum <= l_forecast_periods;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '1100',
min('1000'),
null,
null,
round(sum(quantity*l_period_size/cfrb.period_size)),
trunc(sysdate) + (l_counter - 1) * l_period_size,
inventory_item_id,
organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_usage_histories cuh,
csp_forecast_rules_b cfrb
where history_data_type = 2
and period_start_date between decode(l_forecast_method,4,trunc(sysdate) + (l_counter - 1) * l_period_size,period_start_date)
and decode(l_forecast_method,4,trunc(sysdate) + (l_counter - 1) * l_period_size + (l_period_size - 1),trunc(sysdate) + l_period_size * l_forecast_periods - 1)
and organization_id = l_organization_id
and subinventory_code = '-'
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
and cfrb.forecast_rule_id = l_forecast_rule_id
and cuh.quantity > 0
group by decode(history_data_type,2,'1100',7,'1300',8,'1400'),
trunc(sysdate) + (l_counter - 1) * l_period_size,
inventory_item_id,
organization_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select decode(history_data_type,7,'1300',8,'1400'),
min('1000'),
null,
null,
sum(quantity),
trunc(sysdate) + (l_counter - 1) * l_period_size,
inventory_item_id,
organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_usage_histories cuh
where history_data_type in (7,8)
and period_start_date between trunc(sysdate) + (l_counter - 1) * l_period_size
and trunc(sysdate) + l_counter * l_period_size - 1
and organization_id = l_organization_id
and subinventory_code = '-'
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
group by decode(history_data_type,7,'1300',8,'1400'),
trunc(sysdate) + (l_counter - 1) * l_period_size,
inventory_item_id,
organization_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '1200',
'1000',
null,
null,
round(sum(cpc.population_change*nvl(cfr.manual_failure_rate,cfr.calculated_failure_rate)/7 * l_period_size *
(least(cpc.end_date,(trunc(sysdate)+(l_counter)*l_period_size)) - trunc(sysdate))/(cpc.end_date - cpc.start_date))),
trunc(sysdate) + (l_counter-1) * l_period_size,
cfr.inventory_item_id,
cpc.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_failure_rates cfr,
csp_population_changes cpc,
csp_planning_parameters cpp
where cfr.inventory_item_id = nvl(l_inventory_item_id,cfr.inventory_item_id)
and cfr.product_id = cpc.product_id
and cpc.organization_id = l_organization_id
and cpp.organization_id = cpc.organization_id
and cpp.organization_type = 'W'
and cfr.planning_parameters_id = cpp.product_norm_node_id
and trunc(cpc.end_date) > trunc(sysdate)
and trunc(cpc.start_date) < trunc(sysdate)+(l_counter-1)*l_period_size
group by
cpc.organization_id,
cfr.inventory_item_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select max('1610'),
max('1600'),
null,
cpd.organization_id,
greatest(sum(cpd.quantity),0),
max(trunc(trunc(sysdate) + ((cpd.plan_date - trunc(sysdate))/l_period_size)*l_period_size)),
cpd.inventory_item_id,
cpd.source_organization_id,
max(fnd_global.user_id),
max(sysdate),
max(fnd_global.user_id),
max(sysdate),
max(fnd_global.login_id)
from csp_plan_details cpd
where cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.source_organization_id = l_organization_id
and cpd.plan_detail_type = '4310'
group by cpd.organization_id,cpd.inventory_item_id,cpd.source_organization_id,cpd.plan_date;--heh
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select max('1600'),
max('1000'),
null,
null,
greatest(sum(cpd.quantity),0),
cpd.plan_date,
cpd.inventory_item_id,
cpd.organization_id,
max(fnd_global.user_id),
max(sysdate),
max(fnd_global.user_id),
max(sysdate),
max(fnd_global.login_id)
from csp_plan_details cpd
where cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.organization_id = l_organization_id
and cpd.plan_detail_type = '1610'
group by
cpd.organization_id,
cpd.inventory_item_id,
cpd.plan_date;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('1500'),
min('1000'),
null,
null,
sum(nvl(oola.ordered_quantity,0) - nvl(oola.cancelled_quantity,0) - nvl(oola.shipped_quantity,0)),
decode(sign(trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate)),
-1,trunc(sysdate-l_period_size),
0,trunc(sysdate),
1,trunc(sysdate) + trunc((trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate))/l_period_size)*l_period_size),
oola.inventory_item_id,
oola.ship_from_org_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from oe_order_lines_all oola
where oola.ship_from_org_id = l_organization_id
and oola.open_flag = 'Y'
and nvl(oola.order_source_id,0) <> 10
and nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)) < trunc(sysdate) + l_period_size * l_forecast_periods
and oola.inventory_item_id = nvl(l_inventory_item_id,oola.inventory_item_id)
group by
decode(sign(trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate)),
-1,trunc(sysdate-l_period_size),
0,trunc(sysdate),
1,trunc(sysdate) + trunc((trunc(nvl(oola.schedule_ship_date,nvl(oola.promise_date,oola.request_date)))-trunc(sysdate))/l_period_size)*l_period_size),
oola.inventory_item_id,
oola.ship_from_org_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2310',
ms.from_organization_id*-1,'2110',
crph.requisition_header_id,'2210',
cpt.req_line_id,'2210'),
decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2300',
ms.from_organization_id*-1,'2100',
crph.requisition_header_id,'2200',
cpt.req_line_id,'2200'),
pv.vendor_name||'.'||pha.segment1,
nvl(ms.from_organization_id,-1),
sum(to_org_primary_quantity),
trunc(nvl(expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
ms.item_id,
ms.to_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from mtl_supply ms,
csp_repair_po_headers crph,
po_headers_all pha,
po_vendors pv,
csd_product_transactions cpt
where ms.to_organization_id = l_organization_id
and ms.item_id > 0
and ms.supply_type_code <> 'REQ'
and ms.item_id = nvl(l_inventory_item_id,ms.item_id)
and crph.purchase_order_header_id(+) = ms.po_header_id
and pha.po_header_id = ms.po_header_id
and pha.vendor_id = pv.vendor_id
and cpt.req_line_id(+) = ms.req_line_id
group by decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2310',
ms.from_organization_id*-1,'2110',
crph.requisition_header_id,'2210',
cpt.req_line_id,'2210'),
decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2300',
ms.from_organization_id*-1,'2100',
crph.requisition_header_id,'2200',
cpt.req_line_id,'2200'),
pv.vendor_name||'.'||pha.segment1,
nvl(ms.from_organization_id,-1),
trunc(nvl(expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
ms.item_id,
ms.to_organization_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2310',
ms.from_organization_id*-1,'2110',
crph.requisition_header_id,'2210',
cpt.req_line_id,'2210'),
decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2300',
ms.from_organization_id*-1,'2100',
crph.requisition_header_id,'2200',
cpt.req_line_id,'2200'),
nvl(ooha.order_number,prha.segment1),
nvl(ms.from_organization_id,-1),
sum(ms.to_org_primary_quantity),
trunc(nvl(ms.expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
ms.item_id,
ms.to_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from mtl_supply ms,
csp_repair_po_headers crph,
po_requisition_headers_all prha,
oe_order_headers_all ooha,
csd_product_transactions cpt
where ms.to_organization_id = l_organization_id
and ms.item_id > 0
and ms.supply_type_code = 'REQ'
and ms.item_id = nvl(l_inventory_item_id,ms.item_id)
and crph.requisition_header_id(+) = ms.req_header_id
and prha.requisition_header_id = ms.req_header_id
and ooha.order_source_id(+) = 10
and ooha.orig_sys_document_ref(+) = prha.segment1
and cpt.req_line_id(+) = ms.req_line_id
group by decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2310',
ms.from_organization_id*-1,'2110',
crph.requisition_header_id,'2210',
cpt.req_line_id,'2210'),
decode(nvl(cpt.req_line_id,nvl(crph.requisition_header_id,nvl(ms.from_organization_id*-1,-1))),
-1,'2300',
ms.from_organization_id*-1,'2100',
crph.requisition_header_id,'2200',
cpt.req_line_id,'2200'),
nvl(ooha.order_number,prha.segment1),
nvl(ms.from_organization_id,-1),
trunc(nvl(ms.expected_delivery_date,nvl(crph.need_by_date,nvl(ms.need_by_date,trunc(sysdate))))),
ms.item_id,
ms.to_organization_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select decode(pria.source_type_code,'VENDOR',decode(crph.requisition_line_id,null,'2310','2210'),'2110'),
decode(pria.source_type_code,'VENDOR',decode(crph.requisition_line_id,null,'2300','2200'),'2100'),
pria.req_number_segment1,
nvl(pria.source_organization_id,-1),
pria.quantity,
trunc(nvl(pria.need_by_date,nvl(crph.need_by_date,trunc(sysdate)))),
pria.item_id,
pria.destination_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from po_requisitions_interface_all pria,
csp_repair_po_headers crph
where pria.destination_organization_id = l_organization_id
and pria.item_id = nvl(l_inventory_item_id,pria.item_id)
and crph.requisition_line_id(+) = pria.requisition_line_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '2310',
'2300',
wjsi.job_name,
wjsi.organization_id,
wjsi.start_quantity,
trunc(nvl(wjsi.last_unit_completion_date,sysdate)),
wjsi.primary_item_id,
wjsi.organization_id,
-1001012,--fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from wip_job_schedule_interface wjsi
where wjsi.organization_id = l_organization_id
and wjsi.primary_item_id = nvl(l_inventory_item_id,wjsi.primary_item_id);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select parent_type,
'1',
null,
null,
greatest(sum(quantity),0),
plan_date,
inventory_item_id,
organization_id,
min(created_by),
min(creation_date),
min(last_updated_by),
min(last_update_date),
min(last_update_login)
from csp_plan_details cpd
where parent_type = '1000'
and plan_detail_type in ('1100','1200','1300','1500','1600')
and organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
and not exists (select 'x'
from csp_plan_details
where organization_id = cpd.organization_id
and inventory_item_id = cpd.inventory_item_id
and plan_date = cpd.plan_date
and plan_detail_type = '1400')
group by parent_type,'1',null,null,plan_date,inventory_item_id,organization_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select parent_type,
'1',
null,
null,
greatest(sum(quantity),0),
plan_date,
inventory_item_id,
organization_id,
min(created_by),
min(creation_date),
min(last_updated_by),
min(last_update_date),
min(last_update_login)
from csp_plan_details cpd
where parent_type = '1000'
and plan_detail_type in ('1400','1500','1600')
and organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
and not exists (select 'x'
from csp_plan_details
where organization_id = cpd.organization_id
and inventory_item_id = cpd.inventory_item_id
and plan_date = cpd.plan_date
and plan_detail_type in ('1000'))
group by parent_type,'1',null,null,plan_date,inventory_item_id,organization_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select parent_type,
'2000',
null,
null,
sum(quantity),
plan_date, --hehtrunc(greatest(trunc(sysdate) + floor(((plan_date - trunc(sysdate))/l_period_size))*l_period_size,trunc(sysdate) - l_period_size)),
inventory_item_id,
organization_id,
min(created_by),
min(creation_date),
min(last_updated_by),
min(last_update_date),
min(last_update_login)
from csp_plan_details
where parent_type in ('2100','2200','2300')
and organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
group by parent_type,plan_date,--trunc(greatest(trunc(sysdate) + floor(((plan_date - trunc(sysdate))/l_period_size))*l_period_size,trunc(sysdate) - l_period_size)),
inventory_item_id,organization_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('2000'),
min('1'),
null,
null,
sum(quantity),
plan_date,
inventory_item_id,
organization_id,
min(created_by),
min(creation_date),
min(last_updated_by),
min(last_update_date),
min(last_update_login)
from csp_plan_details
where plan_detail_type in ('2100','2200','2300')
and organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
group by organization_id,inventory_item_id,plan_date;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select
min('9004'),
min('1'),
null,
null,
least(0,sum(decode(cpd.plan_detail_type,'1000',decode(sign(trunc(sysdate+6+i*l_period_size)-cpd.plan_date),-1,0,cpd.quantity*-1),
'4220',decode(sign(trunc(sysdate+6+i*l_period_size)-cpd.plan_date),-1,0,cpd.quantity),
'1' ,cpd.available_quantity,cpd.quantity))
- decode(p_source_type,
'EXCESS',min(nvl(cpr.excess_safety_stock,0)),
'REPAIR',min(nvl(cpr.repair_safety_stock,0)),
'REPAIR_FORECAST',min(nvl(cpr.repair_safety_stock,0)),
'NEWBUY',min(nvl(cpr.newbuy_safety_stock,0)))) * -1,
min(trunc(sysdate+i*l_period_size)),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.plan_detail_type in ('1','1000','2000','4110','4210','4310','4220')
and cpr.organization_id (+) = cpd.organization_id
and cpr.inventory_item_id (+) = cpd.inventory_item_id
and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
group by cpd.organization_id,cpd.inventory_item_id;
delete from csp_plan_details cpd
where (organization_id,inventory_item_id) in
(select csi.organization_id,csi.inventory_item_id
from csp_supersede_items csi
where csi.inventory_item_id = cpd.inventory_item_id
and csi.organization_id = cpd.organization_id
and csi.sub_inventory_code = '-'
and csi.item_supplied <> csi.inventory_item_id)
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = '9004';
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select
min('8110'),
null,
null,
null,
greatest(0,least(sum(decode(cpd.plan_detail_type,'2300',nvl(cpd.quantity,0),0)),
sum(decode(cpd.plan_detail_type,'2300',0,
'1',nvl(cpd.available_quantity,0),
nvl(cpd.quantity,0)))
-greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0)*nvl(min(cipp.newbuy_edq_multiple),nvl(l_edq_multiple,1)),
nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0)*nvl(min(cipp.repair_edq_multiple),nvl(l_edq_multiple,1)),
nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0)*nvl(min(cipp.excess_edq_multiple),nvl(l_edq_multiple,1)),0))) excess_cancel_newbuy,
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr,
csp_item_pl_params cipp
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('1','2000','2300')
and cpd.quantity > 0
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
and cipp.organization_id(+) = cpd.organization_id
and cipp.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd,
mtl_parameters mp,
cst_item_costs cict
where cpd.plan_detail_type = '8110'
and mp.organization_id = cict.organization_id
and cict.inventory_item_id = cpd.inventory_item_id
and cict.organization_id = cpd.organization_id
and cict.cost_type_id = mp.primary_cost_method
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8210'), --Cancel new-buy
null,
null,
null,
greatest(least(sum(decode(cpd.plan_detail_type,'8110',nvl(cpd.quantity*-1,0),'2300',nvl(cpd.quantity,0),0)),
sum(decode(cpd.plan_detail_type,'4110',nvl(cpd.quantity*-1,0),'9001',nvl(cpd.quantity,0),0))),0),
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('2300','8110','4110','9001')
and cpd.quantity > 0
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8220'), --Cancel repair
null,
null,
null,
greatest(least(sum(decode(cpd.plan_detail_type,'8120',nvl(cpd.quantity*-1,0),'2200',nvl(cpd.quantity,0),0)),
sum(decode(cpd.plan_detail_type,'2100',nvl(cpd.quantity*-1,0),'8210',nvl(cpd.quantity*-1,0),'9001',nvl(cpd.quantity,0),0))),0),
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
-10014,--fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('2100','2200','8120','8210','9001')
and cpd.quantity > 0
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
-10016,--fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8220'), --Cancel repair
null,
null,
null,
greatest(least(sum(decode(cpd.plan_detail_type,'8120',nvl(cpd.quantity*-1,0),'2200',nvl(cpd.quantity,0),0)),
sum(decode(cpd.plan_detail_type,'4100',nvl(cpd.quantity*-1,0),'8210',nvl(cpd.quantity*-1,0),'9001',nvl(cpd.quantity,0),0))),0),
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('4100','2200','8120','8210','9001')
and cpd.quantity > 0
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd,
mtl_parameters mp,
cst_item_costs cict
where cpd.plan_detail_type in ('8210','8220')
and mp.organization_id = cict.organization_id
and cict.inventory_item_id = cpd.inventory_item_id
and cict.organization_id = cpd.organization_id
and cict.cost_type_id = mp.primary_cost_method
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8310'), --Cancel new-buy
null,
null,
null,
greatest(least(sum(decode(cpd.plan_detail_type,'4200',nvl(cpd.quantity*-1,0),'9002',nvl(cpd.available_quantity,0),'9003',nvl(cpd.available_quantity,0),0)),
sum(decode(cpd.plan_detail_type,'8110',nvl(cpd.quantity*-1,0),'8210',nvl(cpd.quantity*-1,0),'2300',nvl(cpd.quantity,0),0))),0),
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('4200','2300','8110','8210','9002','9003')
and cpd.quantity > 0
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd,
mtl_parameters mp,
cst_item_costs cict
where cpd.plan_detail_type = '8310'
and mp.organization_id = cict.organization_id
and cict.inventory_item_id = cpd.inventory_item_id
and cict.organization_id = cpd.organization_id
and cict.cost_type_id = mp.primary_cost_method
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8120'),
null,
null,
null,
greatest(least(sum(decode(cpd.plan_detail_type,'2200',nvl(cpd.quantity,0),0)),
sum(decode(cpd.plan_detail_type,
'1',nvl(cpd.available_quantity,0),
'8110',nvl(cpd.quantity*-1,0),
'2200',0,
nvl(cpd.quantity,0))) -
greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0)*nvl(min(cipp.newbuy_edq_multiple),nvl(l_edq_multiple,1)),
nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0)*nvl(min(cipp.repair_edq_multiple),nvl(l_edq_multiple,1)),
nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0)*nvl(min(cipp.excess_edq_multiple),nvl(l_edq_multiple,1)))),0),
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr,
csp_item_pl_params cipp
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('1','2000','2200','8110')
and cpd.quantity > 0
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
and cipp.organization_id(+) = cpd.organization_id
and cipp.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd,
mtl_parameters mp,
cst_item_costs cict
where cpd.plan_detail_type = '8120'
and mp.organization_id = cict.organization_id
and cict.inventory_item_id = cpd.inventory_item_id
and cict.organization_id = cpd.organization_id
and cict.cost_type_id = mp.primary_cost_method
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
insert into csp_plan_details(
plan_detail_type,
parent_type,
inventory_item_id,
organization_id,
plan_date,
quantity,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '8410',
null,
cpd.inventory_item_id,
cpd.organization_id,
cpd.plan_date,
(cpd.quantity - decode(l_onhand_condition_in,
0,0,
1,greatest(cpr.repair_safety_stock,
cpr.excess_safety_stock,
cpr.newbuy_safety_stock),
2,greatest(cpr.repair_rop,
cpr.excess_rop,
cpr.newbuy_rop)))*-1,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = l_onhand_type_in
and cpd.quantity < decode(l_onhand_condition_in,
0,0,
1,greatest(cpr.repair_safety_stock,
cpr.excess_safety_stock,
cpr.newbuy_safety_stock),
2,greatest(cpr.repair_rop,
cpr.excess_rop,
cpr.newbuy_rop))
and cpd.plan_date between trunc(sysdate+decode(nvl(l_start_day_in,0),0,l_period_size * -1,l_start_day_in))
and trunc(sysdate+l_end_day_in)
and cpr.organization_id = cpd.organization_id
and cpr.inventory_item_id = cpd.inventory_item_id
and exists (select 'x'
from csp_plan_details
where organization_id = cpd.organization_id
and inventory_item_id = cpd.inventory_item_id
and plan_detail_type = '2000'
and quantity > 0
and plan_date >= cpd.plan_date)
and plan_date >= trunc(sysdate);
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd
where cpd.plan_detail_type = '8410'
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
having count(*) < l_periods_in);
insert into csp_plan_details(
plan_detail_type,
parent_type,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '8410',
null,
cpd.inventory_item_id,
cpd.organization_id,
-1001872,--fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = l_onhand_type_in
and cpd.quantity < decode(l_onhand_condition_in,
0,0,
1,greatest(cpr.repair_safety_stock,
cpr.excess_safety_stock,
cpr.newbuy_safety_stock),
2,greatest(cpr.repair_rop,
cpr.excess_rop,
cpr.newbuy_rop))
and cpd.plan_date between trunc(sysdate+l_start_day_in) and trunc(sysdate+l_end_day_in)
and cpr.organization_id = cpd.organization_id
and cpr.inventory_item_id = cpd.inventory_item_id
group by cpd.organization_id,cpd.inventory_item_id
having count(*) > l_periods_in;
insert into csp_plan_details(
plan_detail_type,
parent_type,
inventory_item_id,
organization_id,
plan_date,
quantity,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '8420',
null,
cpd.inventory_item_id,
cpd.organization_id,
cpd.plan_date,
cpd.quantity - greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr,
cst_item_costs cic,
mtl_parameters mp
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = l_onhand_type_out
and cpd.quantity > greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out)
and cpd.plan_date between trunc(sysdate+decode(nvl(l_start_day_out,0),0,l_period_size*-1,l_start_day_out)) and trunc(sysdate+l_end_day_out)
and cpr.organization_id = cpd.organization_id
and cpr.inventory_item_id = cpd.inventory_item_id
and cic.inventory_item_id = cpd.inventory_item_id
and cic.organization_id = cpd.organization_id
and cic.cost_type_id = mp.primary_cost_method
and mp.organization_id = cpd.organization_id
and cic.item_cost * (cpd.quantity - greatest(cpr.repair_rop + cpr.repair_edq * l_edq_multiple_out,
cpr.excess_rop + cpr.excess_edq * l_edq_multiple_out,
cpr.newbuy_rop + cpr.newbuy_edq * l_edq_multiple_out)) > l_onhand_value_out;
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd
where cpd.plan_detail_type = '8420'
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
group by cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
having count(*) < l_periods_out);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8130'),
null,
null,
null,
greatest(least(sum(decode(cpd.plan_detail_type,'2100',nvl(cpd.quantity,0),0)),
sum(decode(cpd.plan_detail_type,
'8110',nvl(cpd.quantity*-1,0),
'8120',nvl(cpd.quantity*-1,0),
'2100',0,
'1' ,nvl(cpd.available_quantity,0),
nvl(cpd.quantity,0)))
- greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0)*nvl(l_edq_multiple,1),
nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0)*nvl(l_edq_multiple,1),
nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0)*nvl(l_edq_multiple,1))),0),
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.quantity > 0
and cpd.plan_detail_type in ('1','2000','2100','8110','8120')
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
delete from csp_plan_details
where (plan_detail_type,organization_id,inventory_item_id) in
(select cpd.plan_detail_type,cpd.organization_id,cpd.inventory_item_id
from csp_plan_details cpd,
mtl_parameters mp,
cst_item_costs cict
where cpd.plan_detail_type = '8130'
and mp.organization_id = cict.organization_id
and cict.inventory_item_id = cpd.inventory_item_id
and cict.organization_id = cpd.organization_id
and cict.cost_type_id = mp.primary_cost_method
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and nvl(cpd.quantity * cict.item_cost,1) <= l_minimum_value);
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
available_quantity,
excess_quantity,
onhand_quantity,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('9001'),
null,
null,
mislv.source_organization_id,
nvl(csp_validate_pub.get_avail_qty(mislv.source_organization_id,null,null,mislv.inventory_item_id),0),
greatest(0,nvl(csp_validate_pub.get_available_qty,0)
- greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0),
nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0),
nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0))),
nvl(csp_validate_pub.get_onhand_qty,0),
greatest(0,nvl(csp_validate_pub.get_available_qty,0)
- greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0),
nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0),
nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0))),
min(trunc(sysdate)),
mislv.inventory_item_id,
l_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from MRP_ITEM_SOURCING_LEVELS_V mislv,
csp_plan_reorders cpr,
csp_plan_details cpd
where mislv.organization_id = l_organization_id
and mislv.assignment_set_id = l_usable_assignment_set_id
and mislv.inventory_item_id = cpd.inventory_item_id
and mislv.sourcing_level not in (2,9)
and mislv.source_organization_id <> l_organization_id
and cpr.organization_id(+) = mislv.source_organization_id
and cpr.inventory_item_id(+) = mislv.inventory_item_id
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = '1'
group by cpr.newbuy_rop,
cpr.newbuy_edq,
cpr.repair_rop,
cpr.repair_edq,
cpr.excess_rop,
cpr.excess_edq,
mislv.inventory_item_id,
mislv.source_organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
available_quantity,
onhand_quantity,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '9002',
null,
null,
mislv.source_organization_id,
nvl(csp_validate_pub.get_avail_qty(mislv.source_organization_id,null,null,mislv.inventory_item_id),0),
nvl(csp_validate_pub.get_onhand_qty,0),
nvl(csp_validate_pub.get_available_qty,0),
trunc(sysdate),
mislv.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from MRP_ITEM_SOURCING_LEVELS_V mislv,
csp_plan_details cpd
where mislv.organization_id = cpd.organization_id
and mislv.assignment_set_id = l_defective_assignment_set_id
and mislv.inventory_item_id = cpd.inventory_item_id
and mislv.sourcing_level not in (2,9)
and mislv.source_organization_id <> l_organization_id
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = '1';
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
available_quantity,
onhand_quantity,
quantity,
plan_date,
inventory_item_id,
organization_id,
related_item_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '9003',
null,
null,
mislv.source_organization_id,
nvl(csp_validate_pub.get_avail_qty(mislv.source_organization_id,null,null,mri.inventory_item_id),0),
nvl(csp_validate_pub.get_onhand_qty,0),
nvl(csp_validate_pub.get_available_qty,0),
trunc(sysdate),
mri.related_item_id,
cpd.organization_id,
mri.inventory_item_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from MRP_ITEM_SOURCING_LEVELS_V mislv,
csp_plan_details cpd,
mtl_related_items mri,
mtl_parameters mp
where mislv.organization_id = cpd.organization_id
and mislv.assignment_set_id = l_defective_assignment_set_id
and mislv.inventory_item_id = cpd.inventory_item_id
and mislv.sourcing_level not in (2,9)
and mislv.source_organization_id <> cpd.organization_id
and cpd.organization_id = l_organization_id
and cpd.plan_detail_type = '1'
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and mp.organization_id = cpd.organization_id
and mri.organization_id = mp.master_organization_id
and mri.relationship_type_id = 18
and mri.related_item_id = cpd.inventory_item_id;
delete from csp_plan_details cpd
where cpd.plan_detail_type = '9002'
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.inventory_item_id in
(select cpd2.related_item_id
from csp_plan_details cpd2
where cpd2.plan_detail_type = '9003'
and cpd2.organization_id = l_organization_id
and cpd2.related_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id));
select distinct cpd.inventory_item_id,
cpd.organization_id
from csp_plan_details cpd
where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type = '9004'
and cpd.quantity > 0;
select cpd.quantity,
-- greatest(nvl(cpr.excess_safety_stock,0),
-- nvl(cpr.repair_safety_stock,0),
-- nvl(cpr.newbuy_safety_stock,0)) +
-- nvl(cpr.excess_safety_stock,0) +
-- nvl(decode(p_source_type,'EXCESS',cpr.excess_safety_stock,
-- 'REPAIR',cpr.repair_safety_stock,
-- 'NEWBUY',cpr.newbuy_safety_stock,
-- 'REPAIR_FORECAST',cpr.repair_safety_stock),0) unfilled_quantity,
decode(p_source_type,'EXCESS',cpr.excess_edq,
'REPAIR',cpr.repair_edq,
'NEWBUY',cpr.newbuy_edq,
'REPAIR_FORECAST',cpr.repair_edq) edq_quantity,
trunc(cpd.plan_date) plan_date,
cpr.newbuy_safety_stock - cpr.repair_safety_stock
from csp_plan_details cpd,
csp_plan_reorders cpr
where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = p_inventory_item_id
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
and cpd.plan_detail_type = '9004'
and cpd.quantity > 0
-- and cpd.plan_date > nvl(l_rep_return_date,cpd.plan_date-1)
order by cpd.organization_id,
cpd.inventory_item_id,
cpd.plan_date;
select cpd.source_organization_id,
sum(decode(cpd.plan_detail_type,'4110',nvl(cpd.quantity,0)*-1,
nvl(cpd.quantity,0)))
from csp_plan_details cpd
where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = p_inventory_item_id
and cpd.plan_detail_type in ('4110','9001')
group by cpd.source_organization_id,
cpd.inventory_item_id
order by 2 desc;
select cpd.source_organization_id,
sum(decode(cpd.plan_detail_type,'4210',nvl(cpd.quantity,0)*-1,
nvl(cpd.available_quantity,0))),
cpd.related_item_id
from csp_plan_details cpd
where cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = p_inventory_item_id
and cpd.plan_detail_type in ('4210','9002','9003')
group by cpd.source_organization_id,
cpd.inventory_item_id,
cpd.related_item_id
order by cpd.related_item_id desc, 2 desc;
select null,
sum(nvl(decode(cpd.plan_detail_type,'4220',cpd.quantity*-1,cpd.quantity),0)),
cpr.repair_edq,
null
from csp_plan_reorders cpr,
csp_plan_details cpd
where cpr.organization_id = l_organization_id
and cpr.inventory_item_id = p_inventory_item_id
and cpd.organization_id = cpr.organization_id
and cpd.inventory_item_id = p_inventory_item_id
and cpd.plan_detail_type in ('4220','7000')
and cpd.plan_date <= l_plan_date
group by
cpr.repair_edq
order by 2 desc;
select nvl(msib.source_organization_id,mp.source_organization_id),
cpr.newbuy_edq,
null
from mtl_system_items_b msib,
mtl_parameters mp,
csp_plan_reorders cpr
where msib.organization_id = l_organization_id
and msib.inventory_item_id = p_inventory_item_id
and mp.organization_id = msib.organization_id
and cpr.organization_id = msib.organization_id
and cpr.inventory_item_id = msib.inventory_item_id;
insert into csp_plan_details(
plan_detail_id,
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
related_item_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select csp_plan_details_s1.nextval,
decode(l_source_type,'EXCESS','4110',
'REPAIR','4210',
'NEWBUY','4310',
'REPAIR_FORECAST','4220'),
decode(l_source_type,'EXCESS','4100',
'REPAIR','4200',
'NEWBUY','4300',
'REPAIR_FORECAST','1'),
null,
l_source_organization_id,
l_order_quantity,
--heh greatest(decode(l_source_type,'REPAIR_FORECAST',trunc(l_plan_date),
-- trunc(l_plan_date+(l_period_size-(l_unfilled_quantity/cpd.quantity)*l_period_size))),
-- trunc(sysdate+1)),
greatest(trunc(l_plan_date),trunc(sysdate+1)),
cr.inventory_item_id,
l_organization_id,
l_related_item_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd
where cpd.organization_id = l_organization_id
and cpd.inventory_item_id = cr.inventory_item_id
and cpd.plan_detail_type = '1000'
and cpd.plan_date = l_plan_date;
update csp_plan_details
set quantity = quantity - l_order_quantity
where organization_id = l_organization_id
and inventory_item_id = cr.inventory_item_id
and plan_date >= l_plan_date
and plan_detail_type = '9004';
update csp_plan_details
set quantity = 0
where organization_id = l_organization_id
and inventory_item_id = cr.inventory_item_id
and plan_date = l_plan_date
and plan_detail_type = '9004';
delete from csp_plan_details
where organization_id = l_organization_id
and plan_detail_type = '9004';
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select decode(cpd.plan_detail_type,'4110','4100','4210','4200','4310','4300'),
min('4000'),
null,
null,
sum(cpd.quantity),
trunc(plan_date),--trunc(trunc(sysdate) + (trunc((plan_date - trunc(sysdate))/l_period_size))*l_period_size),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd
where cpd.plan_detail_type in ('4110','4210','4310')
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
group by cpd.organization_id,cpd.inventory_item_id,trunc(plan_date),--hehtrunc(trunc(sysdate) + (trunc((plan_date - trunc(sysdate))/l_period_size))*l_period_size)
cpd.plan_detail_type;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('4000'),
min('1'),
null,
null,
sum(cpd.quantity),
cpd.plan_date,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd
where cpd.plan_detail_type in ('4100','4200','4300')
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
group by cpd.organization_id,cpd.inventory_item_id,cpd.plan_detail_type,cpd.plan_date;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('3000'),
min('1'),
null,
null,
min(cpd2.available_quantity)+sum(decode(cpd.plan_detail_type,'1000',cpd.quantity*-1,cpd.quantity)),
trunc(max(sysdate + (i-1)*l_period_size)),
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,csp_plan_details cpd2
where cpd.plan_detail_type in ('1000','2000')
and cpd2.plan_detail_type = '1'
and cpd.organization_id = cpd2.organization_id
and cpd.inventory_item_id = cpd2.inventory_item_id
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_date < trunc(sysdate+i*l_period_size)
group by cpd.organization_id,cpd.inventory_item_id;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('5000'),
min('1'),
null,
null,
min(cpd.quantity)+
sum(nvl(cpd2.quantity,0)),
cpd.plan_date,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_plan_details cpd2
where cpd.plan_detail_type = '3000'
and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd2.organization_id = cpd.organization_id
and cpd2.inventory_item_id = cpd.inventory_item_id
and cpd2.plan_date <= cpd.plan_date+6
and cpd2.plan_detail_type in ('4000','4220')
group by cpd.organization_id,cpd.inventory_item_id,cpd.plan_date;
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '5000',
'1',
null,
null,
cpd.quantity,
cpd.plan_date,
cpd.inventory_item_id,
cpd.organization_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd
where cpd.plan_detail_type = '3000'
and cpd.organization_id = nvl(l_organization_id,cpd.organization_id)
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_date not in
(select cpd2.plan_date
from csp_plan_details cpd2
where cpd2.organization_id = cpd.organization_id
and cpd2.inventory_item_id = cpd.inventory_item_id
and cpd2.plan_detail_type = '5000');
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
available_quantity,
onhand_quantity,
excess_quantity,
quantity,
plan_date,
inventory_item_id,
organization_id,
period_size,
forecast_periods,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select '1',
'0',
null,
null,
nvl(csp_validate_pub.get_avail_qty(msib.organization_id,null,null,msib.inventory_item_id),0),
nvl(csp_validate_pub.get_onhand_qty,0),
greatest(0,nvl(csp_validate_pub.get_available_qty,0)
- greatest(nvl(cpr.newbuy_rop,0)+nvl(cpr.newbuy_edq,0),
nvl(cpr.repair_rop,0)+nvl(cpr.repair_edq,0),
nvl(cpr.excess_rop,0)+nvl(cpr.excess_edq,0))),
nvl(csp_validate_pub.get_onhand_qty,0),
trunc(sysdate),
msib.inventory_item_id,
l_organization_id,
l_orig_period_size,
l_orig_forecast_periods,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from mtl_system_items_b msib,
csp_plan_reorders cpr
where msib.organization_id = l_organization_id
and msib.inventory_item_id = nvl(l_inventory_item_id,msib.inventory_item_id)
and cpr.organization_id(+) = msib.organization_id
and cpr.inventory_item_id(+) = msib.inventory_item_id
and msib.inventory_item_id in
(select l_inventory_item_id
from dual
union
select distinct cpd2.inventory_item_id
from csp_plan_details cpd2
where cpd2.plan_detail_type in ('1000','2000')
and cpd2.organization_id = l_organization_id
and cpd2.inventory_item_id = nvl(l_inventory_item_id,cpd2.inventory_item_id));
insert into csp_plan_details(
plan_detail_type,
parent_type,
source_number,
source_organization_id,
quantity,
plan_date,
inventory_item_id,
organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select min('8510'),
null,
null,
null,
null,
min(cpd.plan_date),
cpd.inventory_item_id,
cpd.organization_id,
min(cpd.created_by),
min(cpd.creation_date),
min(cpd.last_updated_by),
min(cpd.last_update_date),
min(cpd.last_update_login)
from csp_plan_details cpd,
csp_supersede_items csi
where cpd.plan_detail_type in ('1200','1300','1400','2000')
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and csi.organization_id = cpd.organization_id
and csi.sub_inventory_code = '-'
and csi.inventory_item_id = cpd.inventory_item_id
and csi.inventory_item_id <> csi.item_supplied
group by cpd.organization_id,cpd.inventory_item_id;
delete from csp_plan_details cpd
where (organization_id,inventory_item_id) in
(select csi.organization_id,csi.inventory_item_id
from csp_supersede_items csi
where csi.inventory_item_id = cpd.inventory_item_id
and csi.organization_id = cpd.organization_id
and csi.sub_inventory_code = '-'
and csi.item_supplied <> csi.inventory_item_id)
and cpd.organization_id = l_organization_id
and cpd.inventory_item_id = nvl(l_inventory_item_id,cpd.inventory_item_id)
and cpd.plan_detail_type in ('8110','8120','8130','8210','8220','8310','8410','8420');
insert into csp_plan_histories(
plan_detail_type,
organization_id,
inventory_item_id,
parent_type,
plan_date,
source_number,
source_organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
related_item_id,
available_quantity,
excess_quantity,
onhand_quantity,
quantity,
security_group_id,
plan_detail_id,
period_size,
forecast_periods,
history_type,
history_date)
select plan_detail_type,
organization_id,
inventory_item_id,
parent_type,
plan_date,
source_number,
source_organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
related_item_id,
available_quantity,
excess_quantity,
onhand_quantity,
quantity,
security_group_id,
plan_detail_id,
period_size,
forecast_periods,
p_history_type,
l_history_date
from csp_plan_details
where organization_id = p_organization_id
and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id);
insert into csp_pl_param_histories(
organization_id,
inventory_item_id,
excess_service_level,
repair_service_level,
newbuy_service_level,
excess_edq_factor,
repair_edq_factor,
newbuy_edq_factor,
excess_edq_multiple,
repair_edq_multiple,
newbuy_edq_multiple,
excess_rop,
repair_rop,
newbuy_rop,
excess_safety_stock,
repair_safety_stock,
newbuy_safety_stock,
excess_edq,
repair_edq,
newbuy_edq,
excess_lead_time,
repair_lead_time,
newbuy_lead_time,
history_type,
history_date,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
select cpd.organization_id,
cpd.inventory_item_id,
nvl(cipp.excess_service_level,cpp.excess_service_level),
nvl(cipp.repair_service_level,cpp.repair_service_level),
nvl(cipp.newbuy_service_level,cpp.newbuy_service_level),
nvl(cipp.excess_edq_factor,cpp.excess_edq_factor),
nvl(cipp.repair_edq_factor,cpp.repair_edq_factor),
nvl(cipp.newbuy_edq_factor,cpp.newbuy_edq_factor),
nvl(cipp.excess_edq_multiple,cpp.edq_multiple),
nvl(cipp.repair_edq_multiple,cpp.edq_multiple),
nvl(cipp.newbuy_edq_multiple,cpp.edq_multiple),
cpr.excess_rop,
cpr.repair_rop,
cpr.newbuy_rop,
cpr.excess_safety_stock,
cpr.repair_safety_stock,
cpr.newbuy_safety_stock,
cpr.excess_edq,
cpr.repair_edq,
cpr.newbuy_edq,
cpl.excess_lead_time,
cpl.repair_lead_time,
cpl.newbuy_lead_time,
p_history_type,
l_history_date,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id
from csp_plan_details cpd,
csp_planning_parameters cpp,
csp_plan_reorders cpr,
csp_plan_leadtimes cpl,
csp_item_pl_params cipp
where cpd.plan_detail_type = '1'
and cpd.organization_id = p_organization_id
and cpd.inventory_item_id = nvl(p_inventory_item_id,cpd.inventory_item_id)
and cpp.organization_id = cpd.organization_id
and cpr.organization_id(+) = cpd.organization_id
and cpr.inventory_item_id(+) = cpd.inventory_item_id
and cpl.organization_id(+) = cpd.organization_id
and cpl.inventory_item_id(+) = cpd.inventory_item_id
and cipp.organization_id(+) = cpd.organization_id
and cipp.inventory_item_id(+) = cpd.inventory_item_id;
delete from csp_plan_histories
where history_date < sysdate - p_days
and organization_id = nvl(l_organization_id,organization_id);
delete from csp_plan_details
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id;
insert into csp_plan_details(
plan_detail_type,
organization_id,
inventory_item_id,
parent_type,
plan_date,
source_number,
source_organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
related_item_id,
available_quantity,
excess_quantity,
onhand_quantity,
quantity,
security_group_id,
plan_detail_id,
period_size,
forecast_periods)
select plan_detail_type,
organization_id,
inventory_item_id,
parent_type,
plan_date,
source_number,
source_organization_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
related_item_id,
available_quantity,
excess_quantity,
onhand_quantity,
quantity,
security_group_id,
plan_detail_id,
period_size,
forecast_periods
from csp_plan_histories
where organization_id = p_organization_id
and inventory_item_id = nvl(p_inventory_item_id,inventory_item_id)
and history_date = p_history_date;
select cfrb.forecast_periods,
cfrb.period_size
from csp_forecast_rules_b cfrb,
csp_planning_parameters cpp
where cfrb.forecast_rule_id = cpp.forecast_rule_id
and cpp.organization_id = p_organization_id
and cpp.organization_type = 'W';
delete from csp_plan_details
where organization_id = p_organization_id
and inventory_item_id = p_inventory_item_id
and plan_detail_type in ('1','9001','9002','9003');
select cfrb.forecast_rule_id,
cfrb.forecast_periods,
cfrb.forecast_method,
cfrb.history_periods,
cfrb.period_size,
cpp.organization_id,
cpp.usable_assignment_set_id,
cpp.defective_assignment_set_id,
cpp.repair_assignment_set_id,
cpp.edq_multiple,
cpp.level_id,
cpp.reschedule_rule_id,
cpp.minimum_value
from csp_forecast_rules_b cfrb,
csp_planning_parameters cpp
where cfrb.forecast_rule_id = cpp.forecast_rule_id
and cpp.organization_id = nvl(l_organization_id,cpp.organization_id)
and cpp.organization_type = 'W';
select onhand_type_in,
start_day_in,
end_day_in,
onhand_condition_in,
periods_in,
onhand_type_out,
start_day_out,
end_day_out,
onhand_value_out,
edq_multiple_out,
periods_out
from csp_reschedule_rules_vl
where reschedule_rule_id = l_reschedule_rule_id;
select inventory_item_id
from csp_plan_details
where organization_id = p_organization_id
and plan_detail_type in ('8610','8620','8630');
delete from csp_plan_details
where organization_id = nvl(l_organization_id,organization_id)
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id);
delete from csp_plan_details
where organization_id = nvl(l_organization_id,organization_id)
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
and plan_detail_type not in ('8610','8620','8630');
delete from csp_plan_details
where plan_detail_type in ('8110','8120','8130','8210',
'8220','8310','8410','8420')
and organization_id = l_organization_id
and inventory_item_id = nvl(l_inventory_item_id,inventory_item_id)
and quantity = 0;