[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct mst.sr_instance_id,
mst.organization_id,
item.base_item_id,
item.inventory_item_id,
mst.period_start_date,
mpb.bkt_start_date,
mpb.bkt_end_date,
mpb.days_in_bkt,
mpb.bucket_type,
mpb.bucket_index,
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
item.item_name,
item.description,
null, --base_item_name
item.uom_code,
item.planner_code, --Bug 4424426
NULL, --mst.planning_group,
NULL, --mst.project_id,
NULL, --mst.task_id,
sum(mst.safety_stock_quantity)
FROM msc_safety_stocks mst,
msc_plan_buckets mpb,
msc_plan_organizations_v ov,
msc_system_items item,
msc_companies c,
msc_company_sites s,
msc_trading_partners t,
msc_trading_partner_maps m,
msc_plans p
WHERE p.plan_id = mst.plan_id
and mst.plan_id = p_plan_id
and mst.organization_id = nvl(p_org_id, mst.organization_id)
and mst.sr_instance_id = nvl(p_sr_instance_id, mst.sr_instance_id)
and item.inventory_item_id = nvl(p_item_id, item.inventory_item_id)
and mst.plan_id = item.plan_id
and mst.sr_instance_id = item.sr_instance_id
and mst.organization_id = item.organization_id
and mst.inventory_item_id = item.inventory_item_id
and t.sr_tp_id = mst.organization_id
and t.sr_instance_id = mst.sr_instance_id
and t.partner_type = 3
and m.tp_key = t.partner_id
and m.map_type = 2
and s.company_site_id = m.company_key
and c.company_id = s.company_id
--and mst.safety_stock_quantity > 0
and NVL(item.planner_code,'-99') = NVL(p_planner_code, NVL(item.planner_code,'-99'))
and NVL(item.abc_class_name,'-99') = NVL(p_abc_class, NVL(item.abc_class_name,'-99'))
and mst.plan_id = ov.plan_id -- Bug# 3913477
and mst.organization_id =ov.planned_organization
and mst.sr_instance_id = ov.sr_instance_id
and mst.plan_id = mpb.plan_id
--and mst.organization_id = mpb.organization_id
and mst.sr_instance_id = mpb.sr_instance_id
and ov.plan_id = mpb.plan_id
and mpb.curr_flag = 1
and trunc(mst.period_start_date) between trunc(mpb.bkt_start_date) and trunc(mpb.bkt_end_date)
and nvl(mst.planning_group, '-99') = nvl(p_planning_gp, nvl(mst.planning_group, '-99'))
and nvl(mst.project_id,-99) = nvl(p_project_id, nvl(mst.project_id,-99))
and nvl(mst.task_id, -99) = nvl(p_task_id, nvl(mst.task_id, -99))
and p.plan_completion_date is not null
and trunc(mst.period_start_date) between nvl(trunc(p.plan_start_date),trunc(mst.period_start_date)) and
nvl(trunc(p_horizon_end),trunc(mst.period_start_date))
GROUP BY
mst.sr_instance_id,
mst.organization_id,
item.base_item_id,
item.inventory_item_id,
mst.period_start_date,
mpb.bkt_start_date,
mpb.bkt_end_date,
mpb.days_in_bkt,
mpb.bucket_type,
mpb.bucket_index,
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
item.item_name,
item.description,
null,
item.uom_code,
item.planner_code,--Bug 4424426
NULL, --mst.planning_group,
NULL, --mst.project_id,
NULL --mst.task_id
ORDER BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
SELECT mpb.bucket_index, mpb.bkt_start_date, mpb.bkt_end_date, mpb.bucket_type
FROM msc_plan_buckets mpb,
msc_plan_organizations_v ov -- Bug# 3913477
WHERE ov.plan_id = p_plan_id
AND ov.sr_instance_id = p_sr_instance_id
AND ov.planned_organization = p_org_id
and mpb.plan_id = ov.plan_id
and mpb.curr_flag = 1
AND bkt_start_date between nvl(p_horizon_start_date,mpb.bkt_start_date)
and nvl(p_horizon_end_date, mpb.bkt_end_date)
ORDER BY bucket_index;
SELECT rec.sr_instance_id,
rec.organization_id,
msi.base_item_id,
msi.inventory_item_id,
trunc(rec.new_schedule_date),
mpb.bkt_start_date,
mpb.bkt_end_date,
mpb.days_in_bkt,
mpb.bucket_type,
mpb.bucket_index,
DECODE(rec.order_type,
PURCHASE_ORDER, PAB_SUPPLY,
PURCH_REQ, PAB_SUPPLY,
WORK_ORDER, PAB_SUPPLY,
FLOW_SCHED, PAB_SUPPLY,
REPETITIVE_SCHEDULE, PAB_SUPPLY,
PLANNED_ORDER, PAB_SUPPLY,
NONSTD_JOB, PAB_SUPPLY,
RECEIPT_PURCH_ORDER, PAB_SUPPLY,
SHIPMENT, PAB_SUPPLY,
RECEIPT_SHIPMENT, PAB_SUPPLY,
DIS_JOB_BY, PAB_DEMAND,
NON_ST_JOB_BY, PAB_DEMAND,
REP_SCHED_BY, PAB_DEMAND,
PLANNED_BY, PAB_DEMAND,
FLOW_SCHED_BY, PAB_DEMAND,
PAYBACK_SUPPLY, PAB_SUPPLY,
ON_HAND_QTY, PAB_ONHAND,
PAB_SUPPLY),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null, ---base item name
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --rec.planning_group,
NULL, --rec.project_id,
NULL, --rec.task_id,
SUM(DECODE(msi.base_item_id,NULL,
DECODE(rec.disposition_status_type,
2, 0,
DECODE(rec.last_unit_completion_date,
NULL, rec.new_order_quantity, rec.daily_rate) *
DECODE(rec.order_type, DIS_JOB_BY, -1,
NON_ST_JOB_BY, -1,
REP_SCHED_BY, -1,
PLANNED_BY, -1,
FLOW_SCHED_BY, -1,
1)),
DECODE(rec.last_unit_completion_date,
NULL, rec.new_order_quantity, rec.daily_rate) *
DECODE(rec.order_type, DIS_JOB_BY, -1, NON_ST_JOB_BY, -1,
REP_SCHED_BY, -1, PLANNED_BY, -1,
FLOW_SCHED_BY, -1, 1))) new_quantity
FROM msc_plans p,
msc_trading_partners param,
msc_system_items msi,
msc_supplies rec,
msc_plan_buckets mpb,
msc_plan_organizations_v ov,
msc_companies c,
msc_company_sites s,
msc_trading_partners t,
msc_trading_partner_maps m
WHERE p.plan_id = p_plan_id
AND p.plan_id = msi.plan_id
AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id)
AND msi.organization_id = nvl(p_org_id, msi.organization_id)
AND msi.sr_instance_id = nvl(p_sr_instance_id, msi.sr_instance_id)
AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
AND param.sr_tp_id = rec.organization_id
AND param.sr_instance_id = rec.sr_instance_id
AND param.partner_type = 3
AND rec.plan_id = msi.plan_id
AND rec.inventory_item_id = msi.inventory_item_id
AND rec.organization_id = msi.organization_id
AND rec.sr_instance_id = msi.sr_instance_id
AND t.sr_tp_id = rec.organization_id
AND t.sr_instance_id = rec.sr_instance_id
AND t.partner_type = 3
AND m.tp_key = t.partner_id
AND m.map_type = 2
AND s.company_site_id = m.company_key
AND c.company_id = s.company_id
AND nvl(rec.planning_group, '-99') = nvl(p_planning_gp, nvl(rec.planning_group, '-99'))
AND nvl(rec.project_id,-99) = nvl(p_project_id, nvl(rec.project_id,-99))
AND nvl(rec.task_id, -99) = nvl(p_task_id, nvl(rec.task_id, -99))
AND rec.plan_id = ov.plan_id --- bug# 4106955
AND rec.organization_id =ov.planned_organization
AND rec.sr_instance_id = ov.sr_instance_id
AND rec.plan_id = mpb.plan_id
AND rec.plan_id = p.plan_id
--AND rec.organization_id = mpb.organization_id --- bug# 4106955
AND rec.sr_instance_id = mpb.sr_instance_id
AND ov.plan_id = mpb.plan_id
AND mpb.curr_flag = 1
AND trunc(rec.new_schedule_date) between trunc(mpb.bkt_start_date) and trunc(mpb.bkt_end_date)
AND p.plan_completion_date is not null
AND trunc(rec.new_schedule_date) BETWEEN nvl(trunc(p.plan_start_date), trunc(rec.new_schedule_date)) and
nvl(trunc(p_horizon_end), trunc(rec.new_schedule_date))
GROUP BY
rec.sr_instance_id,
rec.organization_id ,
msi.base_item_id,
msi.inventory_item_id,
rec.new_schedule_date,
mpb.bkt_start_date,
mpb.bkt_end_date,
mpb.days_in_bkt,
mpb.bucket_type,
mpb.bucket_index,
DECODE(rec.order_type,
PURCHASE_ORDER, PAB_SUPPLY,
PURCH_REQ, PAB_SUPPLY,
WORK_ORDER, PAB_SUPPLY,
FLOW_SCHED, PAB_SUPPLY,
REPETITIVE_SCHEDULE, PAB_SUPPLY,
PLANNED_ORDER, PAB_SUPPLY,
NONSTD_JOB, PAB_SUPPLY,
RECEIPT_PURCH_ORDER, PAB_SUPPLY,
SHIPMENT, PAB_SUPPLY,
RECEIPT_SHIPMENT, PAB_SUPPLY,
DIS_JOB_BY, PAB_DEMAND,
NON_ST_JOB_BY, PAB_DEMAND,
REP_SCHED_BY, PAB_DEMAND,
PLANNED_BY, PAB_DEMAND,
FLOW_SCHED_BY, PAB_DEMAND,
PAYBACK_SUPPLY, PAB_SUPPLY,
ON_HAND_QTY, PAB_ONHAND,
PAB_SUPPLY),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null, --base item name
msi.uom_code,
msi.planner_code, --Bug 4424426
NULL, --rec.planning_group,
NULL, --rec.project_id,
NULL --rec.task_id
UNION ALL
SELECT
mgr.sr_instance_id,
mgr.organization_id,
msi.base_item_id,
msi.inventory_item_id,
trunc(mgr.using_assembly_demand_date),
mpb.bkt_start_date,
mpb.bkt_end_date,
mpb.days_in_bkt,
mpb.bucket_type,
mpb.bucket_index,
decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
23,PAB_SCRAP_DEMAND,24,PAB_DEMAND,25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
PAB_DEMAND),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null, ---base item name
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --mgr.planning_group,
NULL, --mgr.project_id,
NULL, --mgr.task_id,
SUM(DECODE(mgr.assembly_demand_comp_date,
NULL, DECODE(mgr.origination_type,
29,(nvl(mgr.probability,1)*using_requirement_quantity),
31, 0,
using_requirement_quantity),
DECODE(mgr.origination_type,
29,(nvl(mgr.probability,1)*daily_demand_rate),
31, 0,
daily_demand_rate)))/
DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
29,nvl(mgr.probability,0),
null)) ,1) ,1),
0,1,
nvl(LEAST(SUM(DECODE(mgr.origination_type,
29,nvl(mgr.probability,0),
null)) ,1) ,1)) new_quantity
FROM msc_plans p,
msc_trading_partners param,
msc_system_items msi,
msc_demands mgr,
msc_plan_buckets mpb,
msc_plan_organizations_v ov,
msc_companies c,
msc_company_sites s,
msc_trading_partners t,
msc_trading_partner_maps m
WHERE p.plan_id = p_plan_id
AND p.plan_id = mgr.plan_id
AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id )
AND mgr.organization_id = nvl(p_org_id, mgr.organization_id)
AND mgr.sr_instance_id = nvl(p_sr_instance_id, mgr.sr_instance_id)
AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
AND param.sr_tp_id = mgr.organization_id
AND param.sr_instance_id = mgr.sr_instance_id
AND param.partner_type = 3
AND mgr.plan_id = msi.plan_id
AND mgr.inventory_item_id = msi.inventory_item_id
AND mgr.organization_id = msi.organization_id
AND mgr.sr_instance_id = msi.sr_instance_id
AND t.sr_tp_id = mgr.organization_id
AND t.sr_instance_id = mgr.sr_instance_id
AND t.partner_type = 3
AND m.tp_key = t.partner_id
AND m.map_type = 2
AND s.company_site_id = m.company_key
AND c.company_id = s.company_id
AND nvl(mgr.planning_group, '-99') = nvl(p_planning_gp, nvl(mgr.planning_group, '-99'))
AND nvl(mgr.project_id,-99) = nvl(p_project_id, nvl(mgr.project_id,-99))
AND nvl(mgr.task_id, -99) = nvl(p_task_id, nvl(mgr.task_id, -99))
AND mgr.plan_id = ov.plan_id -- Bug# 3913477
AND mgr.organization_id =ov.planned_organization
AND mgr.sr_instance_id = ov.sr_instance_id
AND mgr.plan_id = mpb.plan_id
AND mgr.plan_id = p.plan_id
--AND mgr.organization_id = mpb.organization_id
AND mgr.sr_instance_id = mpb.sr_instance_id
AND ov.plan_id = mpb.plan_id
AND mpb.curr_flag = 1
AND trunc(mgr.using_assembly_demand_date) between trunc(mpb.bkt_start_date) and trunc(mpb.bkt_end_date)
AND p.plan_completion_date is not null
AND trunc(mgr.using_assembly_demand_date) BETWEEN nvl(trunc(p.plan_start_date), trunc(mgr.using_assembly_demand_date))
AND nvl(trunc(p_horizon_end), trunc(mgr.using_assembly_demand_date))
AND not exists (
select 'cancelled IR'
from msc_supplies mr
where mgr.origination_type in (30,6)
and mgr.disposition_id = mr.transaction_id
and mgr.plan_id = mr.plan_id
and mgr.sr_instance_id = mr.sr_instance_id
and mr.disposition_status_type = 2)
GROUP BY
mgr.sr_instance_id,
mgr.organization_id,
msi.base_item_id,
msi.inventory_item_id,
mgr.using_assembly_demand_date,
mpb.bkt_start_date,
mpb.bkt_end_date,
mpb.days_in_bkt,
mpb.bucket_type,
mpb.bucket_index,
decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
23,PAB_SCRAP_DEMAND,24,PAB_DEMAND, 25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
PAB_DEMAND),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null,
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --mgr.planning_group,
NULL, --mgr.project_id,
NULL --mgr.task_id
UNION ALL
/*----------------------------------------------------------------------
Bug# 3893860
The following 2 select statement are added for the fix. The
fix will include the past due pab calculation
That means will include all the data before the plan_start_date.
There will be no join to msc_plan_buckets.
----------------------------------------------------------------------*/
SELECT rec.sr_instance_id,
rec.organization_id,
msi.base_item_id,
msi.inventory_item_id,
trunc(rec.new_schedule_date),
null,--mpb.bkt_start_date,
null,--mpb.bkt_end_date,
null,--mpb.days_in_bkt,
null,--mpb.bucket_type,
null,--mpb.bucket_index,
DECODE(rec.order_type,
PURCHASE_ORDER, PAB_SUPPLY,
PURCH_REQ, PAB_SUPPLY,
WORK_ORDER, PAB_SUPPLY,
FLOW_SCHED, PAB_SUPPLY,
REPETITIVE_SCHEDULE, PAB_SUPPLY,
PLANNED_ORDER, PAB_SUPPLY,
NONSTD_JOB, PAB_SUPPLY,
RECEIPT_PURCH_ORDER, PAB_SUPPLY,
SHIPMENT, PAB_SUPPLY,
RECEIPT_SHIPMENT, PAB_SUPPLY,
DIS_JOB_BY, PAB_DEMAND,
NON_ST_JOB_BY, PAB_DEMAND,
REP_SCHED_BY, PAB_DEMAND,
PLANNED_BY, PAB_DEMAND,
FLOW_SCHED_BY, PAB_DEMAND,
PAYBACK_SUPPLY, PAB_SUPPLY,
ON_HAND_QTY, PAB_ONHAND,
PAB_SUPPLY),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null, ---base item name
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --rec.planning_group,
NULL, --rec.project_id,
NULL, --rec.task_id,
SUM(DECODE(msi.base_item_id,NULL,
DECODE(rec.disposition_status_type,
2, 0,
DECODE(rec.last_unit_completion_date,
NULL, rec.new_order_quantity, rec.daily_rate) *
DECODE(rec.order_type, DIS_JOB_BY, -1,
NON_ST_JOB_BY, -1,
REP_SCHED_BY, -1,
PLANNED_BY, -1,
FLOW_SCHED_BY, -1,
1)),
DECODE(rec.last_unit_completion_date,
NULL, rec.new_order_quantity, rec.daily_rate) *
DECODE(rec.order_type, DIS_JOB_BY, -1, NON_ST_JOB_BY, -1,
REP_SCHED_BY, -1, PLANNED_BY, -1,
FLOW_SCHED_BY, -1, 1))) new_quantity
FROM msc_plans p,
msc_trading_partners param,
msc_system_items msi,
msc_supplies rec,
msc_companies c,
msc_company_sites s,
msc_trading_partners t,
msc_trading_partner_maps m
WHERE p.plan_id = p_plan_id
AND p.plan_id = msi.plan_id
AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id)
AND msi.organization_id = nvl(p_org_id, msi.organization_id)
AND msi.sr_instance_id = nvl(p_sr_instance_id, msi.sr_instance_id)
AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
AND param.sr_tp_id = rec.organization_id
AND param.sr_instance_id = rec.sr_instance_id
AND param.partner_type = 3
AND rec.plan_id = msi.plan_id
AND rec.inventory_item_id = msi.inventory_item_id
AND rec.organization_id = msi.organization_id
AND rec.sr_instance_id = msi.sr_instance_id
AND t.sr_tp_id = rec.organization_id
AND t.sr_instance_id = rec.sr_instance_id
AND t.partner_type = 3
AND m.tp_key = t.partner_id
AND m.map_type = 2
AND s.company_site_id = m.company_key
AND c.company_id = s.company_id
AND nvl(rec.planning_group, '-99') = nvl(p_planning_gp, nvl(rec.planning_group, '-99'))
AND nvl(rec.project_id,-99) = nvl(p_project_id, nvl(rec.project_id,-99))
AND nvl(rec.task_id, -99) = nvl(p_task_id, nvl(rec.task_id, -99))
AND rec.plan_id = p.plan_id
AND p.plan_completion_date is not null
AND trunc(rec.new_schedule_date) <= nvl(trunc(p_horizon_end), rec.new_schedule_date)
and trunc(rec.new_schedule_date ) < trunc( p.plan_start_date)
GROUP BY
rec.sr_instance_id,
rec.organization_id ,
msi.base_item_id,
msi.inventory_item_id,
rec.new_schedule_date,
null,-- mpb.bkt_start_date,
null,-- mpb.bkt_end_date,
null,-- mpb.days_in_bkt,
null,-- mpb.bucket_type,
null,-- mpb.bucket_index,
DECODE(rec.order_type,
PURCHASE_ORDER, PAB_SUPPLY,
PURCH_REQ, PAB_SUPPLY,
WORK_ORDER, PAB_SUPPLY,
FLOW_SCHED, PAB_SUPPLY,
REPETITIVE_SCHEDULE, PAB_SUPPLY,
PLANNED_ORDER, PAB_SUPPLY,
NONSTD_JOB, PAB_SUPPLY,
RECEIPT_PURCH_ORDER, PAB_SUPPLY,
SHIPMENT, PAB_SUPPLY,
RECEIPT_SHIPMENT, PAB_SUPPLY,
DIS_JOB_BY, PAB_DEMAND,
NON_ST_JOB_BY, PAB_DEMAND,
REP_SCHED_BY, PAB_DEMAND,
PLANNED_BY, PAB_DEMAND,
FLOW_SCHED_BY, PAB_DEMAND,
PAYBACK_SUPPLY, PAB_SUPPLY,
ON_HAND_QTY, PAB_ONHAND,
PAB_SUPPLY),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null, --base item name
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --rec.planning_group,
NULL, --rec.project_id,
NULL --rec.task_id
UNION ALL
SELECT
mgr.sr_instance_id,
mgr.organization_id,
msi.base_item_id,
msi.inventory_item_id,
trunc(mgr.using_assembly_demand_date),
NULL, --mpb.bkt_start_date,
NULL, --mpb.bkt_end_date,
NULL, --mpb.days_in_bkt,
NULL, --mpb.bucket_type,
NULL, --mpb.bucket_index,
decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
23,PAB_SCRAP_DEMAND,24,PAB_DEMAND,25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
PAB_DEMAND),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null, ---base item name
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --mgr.planning_group,
NULL, --mgr.project_id,
NULL, --mgr.task_id,
SUM(DECODE(mgr.assembly_demand_comp_date,
NULL, DECODE(mgr.origination_type,
29,(nvl(mgr.probability,1)*using_requirement_quantity),
31, 0,
using_requirement_quantity),
DECODE(mgr.origination_type,
29,(nvl(mgr.probability,1)*daily_demand_rate),
31, 0,
daily_demand_rate)))/
DECODE(nvl(LEAST(SUM(DECODE(mgr.origination_type,
29,nvl(mgr.probability,0),
null)) ,1) ,1),
0,1,
nvl(LEAST(SUM(DECODE(mgr.origination_type,
29,nvl(mgr.probability,0),
null)) ,1) ,1)) new_quantity
FROM msc_plans p,
msc_trading_partners param,
msc_system_items msi,
msc_demands mgr,
msc_companies c,
msc_company_sites s,
msc_trading_partners t,
msc_trading_partner_maps m
WHERE p.plan_id = p_plan_id
AND p.plan_id = mgr.plan_id
AND msi.inventory_item_id = nvl(p_item_id, msi.inventory_item_id )
AND mgr.organization_id = nvl(p_org_id, mgr.organization_id)
AND mgr.sr_instance_id = nvl(p_sr_instance_id, mgr.sr_instance_id)
AND NVL(msi.planner_code,'-99') = NVL(p_planner_code, NVL(msi.planner_code,'-99'))
AND NVL(msi.abc_class_name,'-99') = NVL(p_abc_class, NVL(msi.abc_class_name,'-99'))
AND param.sr_tp_id = mgr.organization_id
AND param.sr_instance_id = mgr.sr_instance_id
AND param.partner_type = 3
AND mgr.plan_id = msi.plan_id
AND mgr.inventory_item_id = msi.inventory_item_id
AND mgr.organization_id = msi.organization_id
AND mgr.sr_instance_id = msi.sr_instance_id
AND t.sr_tp_id = mgr.organization_id
AND t.sr_instance_id = mgr.sr_instance_id
AND t.partner_type = 3
AND m.tp_key = t.partner_id
AND m.map_type = 2
AND s.company_site_id = m.company_key
AND c.company_id = s.company_id
AND nvl(mgr.planning_group, '-99') = nvl(p_planning_gp, nvl(mgr.planning_group, '-99'))
AND nvl(mgr.project_id,-99) = nvl(p_project_id, nvl(mgr.project_id,-99))
AND nvl(mgr.task_id, -99) = nvl(p_task_id, nvl(mgr.task_id, -99))
AND mgr.plan_id = p.plan_id
AND p.plan_completion_date is not null
AND trunc(mgr.using_assembly_demand_date) <= nvl(trunc(p_horizon_end), trunc(mgr.using_assembly_demand_date))
AND trunc(mgr.using_assembly_demand_date) < trunc(p.plan_start_date)
AND not exists (
select 'cancelled IR'
from msc_supplies mr
where mgr.origination_type in (30,6)
and mgr.disposition_id = mr.transaction_id
and mgr.plan_id = mr.plan_id
and mgr.sr_instance_id = mr.sr_instance_id
and mr.disposition_status_type = 2)
GROUP BY
mgr.sr_instance_id,
mgr.organization_id,
msi.base_item_id,
msi.inventory_item_id,
mgr.using_assembly_demand_date,
NULL, --mpb.bkt_start_date,
NULL, --mpb.bkt_end_date,
NULL, --mpb.days_in_bkt,
NULL, --mpb.bucket_type,
NULL, --mpb.bucket_index,
decode (mgr.origination_type, 1, PAB_DEMAND,2,PAB_DEMAND,3,PAB_DEMAND,
4,PAB_DEMAND,5,PAB_EXP_LOT,6,PAB_DEMAND,7,PAB_DEMAND,8,PAB_DEMAND,
9,PAB_DEMAND,10,PAB_DEMAND,11,PAB_DEMAND,12,PAB_DEMAND,
15,PAB_DEMAND,16,PAB_SCRAP_DEMAND,17,PAB_SCRAP_DEMAND,18,PAB_SCRAP_DEMAND,
19,PAB_SCRAP_DEMAND,20,PAB_SCRAP_DEMAND,21,PAB_SCRAP_DEMAND,22,PAB_DEMAND,
23,PAB_SCRAP_DEMAND,24,PAB_DEMAND, 25,PAB_DEMAND,26,PAB_SCRAP_DEMAND,
29,PAB_DEMAND,30,PAB_DEMAND,DEMAND_PAYBACK,PAB_DEMAND,
PAB_DEMAND),
c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name,
msi.item_name,
msi.description,
null,
msi.uom_code,
msi.planner_code,--Bug 4424426
NULL, --mgr.planning_group,
NULL, --mgr.project_id,
NULL --mgr.task_id
ORDER BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14;
select
fnd_global.user_id,
fnd_global.user_name --,
--fnd_global.resp_name,
--fnd_global.application_name
into l_user_id,
l_user_name --,
--l_resp_name,
--l_application_name
from dual;
SELECT language_code
INTO l_language_code
FROM fnd_languages
WHERE nls_language = l_language;
select compile_designator
into p_designator
from msc_plans
where plan_id = p_plan_id;
select instance_id
into p_sr_instance_id
from msc_apps_instances
where instance_code = p_inst_code;
select sr_tp_id
into p_org_id
from msc_trading_partners
where organization_code = p_org_code and
sr_instance_id = p_sr_instance_id and
partner_type = 3 and
company_id is null;
select sysdate
into l_horizon_start
from dual;
select sysdate +365
into l_horizon_end
from dual;
SELECT item_name
INTO l_item_name
FROM msc_items
WHERE inventory_item_id = p_item_id;
SELECT partner_name
INTO l_supp_name
FROM msc_trading_partners
WHERE partner_id = p_supplier_id;
SELECT tp_site_code
INTO l_supp_site
FROM msc_trading_partner_sites
WHERE partner_id = p_supplier_id
AND partner_site_id = p_supplier_site_id;
delete_old_safety_stock(
p_plan_id,
p_org_id,
p_sr_instance_id,
p_planner_code,
p_abc_class,
p_item_id,
p_planning_gp,
p_project_id,
p_task_id,
l_horizon_start,
l_horizon_end,
p_overwrite
);
insert_into_sup_dem(
p_errbuf
,p_retcode
,p_plan_id
,l_horizon_start
,l_horizon_end
,SAFETY_STOCK
,t_sr_instance_id
,t_org_id
,t_pub
,t_pub_id
,t_pub_site
,t_pub_site_id
,t_base_item_id
,t_item_id
,t_bucket_type
,t_bucket_start
,t_bucket_end
,t_bucket_index
,t_qty
,t_qty
,t_qty
,t_item_name
,t_item_desc
,t_base_item_name
,t_proj_number
,t_task_number
,t_planning_gp
,t_uom_code
,t_planner_code
,t_period_start_date
,t_master_item_name
,t_master_item_desc
,l_version
,p_designator
,l_user_id
,l_language_code
);
insert_into_sup_dem(
p_errbuf
,p_retcode
,p_plan_id
,l_horizon_start
,l_horizon_end
,PROJECTED_AVAILABLE_BALANCE
,a_sr_instance_id
,a_org_id
,a_pub
,a_pub_id
,a_pub_site
,a_pub_site_id
,a_base_item_id
,a_item_id
,a_bucket_type
,a_bucket_start
,a_bucket_end
,a_bucket_index
,a_qty
,a_total_qty
,a_temp_qty
,a_item_name
,a_item_desc
,a_base_item_name
,a_proj_number
,a_task_number
,a_planning_gp
,a_uom_code
,a_planner_code
,a_period_start_date
,a_master_item_name
,a_master_item_desc
,l_version
,p_designator
,l_user_id
,l_language_code
);
SELECT 1 INTO l_records_exist
FROM dual
WHERE exists ( SELECT 1
FROM msc_sup_dem_entries
WHERE plan_id = -1
AND publisher_order_type = 2
AND designator = p_designator
AND version = l_version);
select c.company_id,
c.company_name,
s.company_site_id,
s.company_site_name
into t_pub_id(j),
t_pub(j),
t_pub_site_id(j),
t_pub_site(j)
from msc_companies c,
msc_company_sites s,
msc_trading_partner_maps m,
msc_trading_partners t
where t.sr_tp_id = t_org_id(j) and
t.sr_instance_id = t_sr_instance_id(j) and
t.partner_type = 3 and
m.tp_key = t.partner_id and
m.map_type = 2 and
s.company_site_id = m.company_key and
c.company_id = s.company_id;
select item_name, description
into t_item_name(j), t_item_desc(j)
from msc_system_items
where sr_instance_id = t_sr_instance_id(j)
and organization_id = t_org_id(j)
and inventory_item_id = t_item_id(j)
and plan_id = -1;
select item_name
into t_base_item_name(j)
from msc_system_items
where sr_instance_id = t_sr_instance_id(j)
and organization_id = t_org_id(j)
and inventory_item_id = t_base_item_id(j)
and plan_id = -1;
select distinct c.company_id,
c.company_name
into t_supp_id(j),
t_supp(j)
from msc_companies c,
msc_trading_partner_maps m,
msc_company_relationships r,
msc_item_suppliers mis
where m.tp_key = mis.supplier_id and
m.map_type = 1 and
r.relationship_id = m.company_key and
r.subject_id = t_pub_id(j) and
r.relationship_type = 2 and
c.company_id = r.object_id and
mis.plan_id = -1 and
mis.organization_id = t_org_id(j) and
mis.sr_instance_id = t_sr_instance_id(j) and
mis.inventory_item_id = t_item_id(j) and
mis.supplier_id = p_supp_id;
select s.company_site_id,
s.company_site_name
into t_supp_site_id(j),
t_supp_site(j)
from msc_company_sites s,
msc_trading_partner_maps m,
msc_item_suppliers mis
where m.tp_key = mis.supplier_site_id and
m.map_type = 3 and
s.company_site_id = m.company_key and
s.company_id = t_supp_id(j) and
m.tp_key = mis.supplier_site_id and
mis.plan_id = -1 and
mis.organization_id = t_org_id(j) and
mis.sr_instance_id = t_sr_instance_id(j) and
mis.inventory_item_id = t_item_id(j) and
mis.supplier_id = p_supp_id and
mis.supplier_site_id = p_supp_site_id
;
select s.company_site_id,
s.company_site_name
into t_supp_site_id(j),
t_supp_site(j)
from msc_company_sites s,
msc_trading_partner_maps m,
msc_item_suppliers mis
where m.map_type = 3 and
s.company_site_id = m.company_key and
s.company_id = t_supp_id(j) and
m.tp_key = mis.supplier_site_id and
mis.plan_id = -1 and
mis.organization_id = t_org_id(j) and
mis.sr_instance_id = t_sr_instance_id(j) and
mis.inventory_item_id = t_item_id(j) and
mis.supplier_id = p_supp_id
;
select item_name,
description
into t_master_item_name(j),
t_master_item_desc(j)
from msc_items
where inventory_item_id = t_item_id(j);
PROCEDURE insert_into_sup_dem (
p_err OUT nocopy varchar2,
p_ret OUT nocopy number,
p_plan_id IN number,
p_horizon_start IN date,
p_horizon_end IN date,
p_type IN number,
t_sr_instance_id IN numberList,
t_org_id IN numberList,
t_pub IN companyNameList,
t_pub_id IN numberList,
t_pub_site IN companySiteList,
t_pub_site_id IN numberList,
t_base_item_id IN numberList,
t_item_id IN numberList,
t_bucket_type IN numberList,
t_bucket_start IN dateList,
t_bucket_end IN dateList,
t_bucket_index IN numberList,
t_qty IN numberList,
t_total_qty IN numberList,
t_temp_qty IN numberList,
t_item_name IN itemNameList,
t_item_desc IN itemDescList,
t_base_item_name IN itemNameList,
t_proj_number IN numberList,
t_task_number IN numberList,
t_planning_gp IN planningGroupList,
t_uom_code IN itemUomList,
t_planner_code IN plannerCodeList,
t_key_date IN dateList,
t_master_item_name IN itemNameList,
t_master_item_desc IN itemDescList,
p_version IN varchar2,
p_designator IN varchar2,
p_user_id IN number,
p_language_code IN varchar2
) IS
CURSOR get_bucket_date (p_plan_id in number,
p_sr_instance_id in number,
p_org_id in number,
p_start_date in date,
p_end_date in date) IS
SELECT mpb.bucket_index, trunc(mpb.bkt_start_date), trunc(mpb.bkt_end_date), mpb.bucket_type
FROM msc_plan_buckets mpb ,
msc_plan_organizations_v ov -- Bug# 3913477
WHERE ov.plan_id = p_plan_id
AND ov.sr_instance_id = p_sr_instance_id
AND ov.planned_organization = p_org_id
AND mpb.plan_id = ov.plan_id
AND mpb.curr_flag = 1
AND bkt_start_date between nvl(p_start_date, mpb.bkt_start_date)
and nvl(p_end_date, mpb.bkt_end_date)
ORDER BY bucket_index;
l_record_inserted number;
l_record_inserted := 0;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
SAFETY_STOCK,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
t_key_date(j),
t_key_date(j),
t_qty(j),
t_qty(j),
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
--dbms_output.Put_line('insert for the gap ' || t_qty(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
SAFETY_STOCK,
l_order_type_desc,
l_bucket_type_desc,
b_bkt_type(k),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
b_bkt_end_date(k), --l_prev_work_date,
b_bkt_end_date(k), --l_prev_work_date,
l_qty,
l_qty,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
whichever ends first. The loop inserts data for MULTIORG case or MULTI_ITEM
case whenever pub_id or pub_site_id( ORG_ID) or item_id changes.
-------------------------------------------------------------------------------*/
IF (j > 1 AND
(t_pub_id(j) <> t_pub_id(j-1) OR
t_pub_site_id(j) <> t_pub_site_id(j-1) OR
t_item_id(j) <> t_item_id(j-1) )) THEN
Open get_bucket_date (p_plan_id,
t_sr_instance_id(j-1),
t_org_id(j-1),
t_bucket_start(j-1),
p_horizon_end);
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j-1),
t_pub_id(j-1),
t_pub_site(j-1),
t_pub_site_id(j-1),
SAFETY_STOCK,
l_order_type_desc,
l_bucket_type_desc,
b_bkt_type(k),
t_item_id(j-1),
t_master_item_name(j-1),
t_item_name(j-1),
nvl(t_master_item_desc(j-1), t_item_desc(j-1)),
t_item_desc(j-1),
t_base_item_id(j-1),
t_base_item_name(j-1),
t_uom_code(j-1),
t_uom_code(j-1),
null,
b_bkt_end_date(k), --l_prev_work_date,
b_bkt_end_date(k), --l_prev_work_date,
l_qty,
l_qty,
null,
msc_cl_refresh_s.nextval,
t_pub(j-1),
t_pub_id(j-1),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j-1),
t_task_number(j-1),
t_planning_gp(j-1),
t_planner_code(j-1),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
for inserting trailors for last data i.e. for SINGLE ORG case or
in case of multiorg for the last pub_site_id(ORG_ID) or last item_id
data available in table msc_safety_stock.
------------------------------------------------------------------------*/
IF (j = t_pub_id.COUNT) then
Open get_bucket_date (p_plan_id,
t_sr_instance_id(j),
t_org_id(j),
t_bucket_start(j),
p_horizon_end);
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
SAFETY_STOCK,
l_order_type_desc,
l_bucket_type_desc,
b_bkt_type(k),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
b_bkt_end_date(k), --l_prev_work_date,
b_bkt_end_date(k), --l_prev_work_date,
l_qty,
l_qty,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records to be inserted for safety stock : ' || l_record_inserted);
l_record_inserted := 0;
SELECT plan_start_date
INTO l_plan_start_date
FROM msc_plans
WHERE plan_id = p_plan_id;
update msc_sup_dem_entries
set quantity = l_total,
primary_quantity = l_total
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and inventory_item_id = t_item_id(j)
and publisher_order_type = PROJECTED_AVAILABLE_BALANCE
and trunc(key_date) = trunc(l_plan_start_date) -1 ;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
PROJECTED_AVAILABLE_BALANCE,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
l_plan_start_date - 1, --t_key_date(j),
l_plan_start_date - 1, --t_key_date(j),
l_total,
l_total,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
PROJECTED_AVAILABLE_BALANCE,
l_order_type_desc,
l_bucket_type_desc,
b_bkt_type(k),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
b_bkt_end_date(k), --l_prev_work_date,
b_bkt_end_date(k), --l_prev_work_date,
l_total,
l_total,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
PROJECTED_AVAILABLE_BALANCE,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
t_key_date(j),
t_key_date(j),
l_total - l_exp_qty,
l_total - l_exp_qty,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
--dbms_output.put_line('update');
update msc_sup_dem_entries
set quantity = l_total,
primary_quantity = l_total
where publisher_id = t_pub_id(j)
and publisher_site_id = t_pub_site_id(j)
and inventory_item_id = t_item_id(j)
and publisher_order_type = PROJECTED_AVAILABLE_BALANCE
and trunc(key_date) = trunc(t_key_date(j));
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
PROJECTED_AVAILABLE_BALANCE,
l_order_type_desc,
l_bucket_type_desc,
t_bucket_type(j),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
t_key_date(j),
t_key_date(j),
l_total,
l_total,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
whichever ends first. The loop inserts data for MULTIORG , MULTI_ITEM case
whenever pub_id or pub_site_id( ORG_ID) or item_id changes.
-------------------------------------------------------------------------*/
IF (j > 1 AND
(t_pub_id(j) <> t_pub_id(j-1) OR
t_pub_site_id(j) <> t_pub_site_id(j-1) OR
t_item_id(j) <> t_item_id(j-1) )) THEN
Open get_bucket_date (p_plan_id,
t_sr_instance_id(j-1),
t_org_id(j-1),
t_bucket_start(j-1),
p_horizon_end);
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j-1),
t_pub_id(j-1),
t_pub_site(j-1),
t_pub_site_id(j-1),
PROJECTED_AVAILABLE_BALANCE,
l_order_type_desc,
l_bucket_type_desc,
b_bkt_type(k),
t_item_id(j-1),
t_master_item_name(j-1),
t_item_name(j-1),
nvl(t_master_item_desc(j-1), t_item_desc(j-1)),
t_item_desc(j-1),
t_base_item_id(j-1),
t_base_item_name(j-1),
t_uom_code(j-1),
t_uom_code(j-1),
null,
b_bkt_end_date(k), --l_prev_work_date,
b_bkt_end_date(k), --l_prev_work_date,
l_total,
l_total,
null,
msc_cl_refresh_s.nextval,
t_pub(j-1),
t_pub_id(j-1),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j-1),
t_task_number(j-1),
t_planning_gp(j-1),
t_planner_code(j-1),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
for inserting trailors for last data i.e. for SINGLE ORG case or
in case of multiorg for the last pub_site_id(ORG_ID) or last item_id
data available in tables.
-----------------------------------------------------------------------*/
IF (j = t_pub_id.COUNT ) THEN
Open get_bucket_date (p_plan_id,
t_sr_instance_id(j),
t_org_id(j),
t_bucket_start(j),
p_horizon_end);
insert into msc_sup_dem_entries (
transaction_id,
plan_id,
sr_instance_id,
publisher_name,
publisher_id,
publisher_site_name,
publisher_site_id,
publisher_order_type,
publisher_order_type_desc,
bucket_type_desc,
bucket_type,
inventory_item_id,
item_name,
owner_item_name,
item_description,
owner_item_description,
base_item_id,
base_item_name,
primary_uom,
uom_code,
tp_uom_code,
key_date,
new_schedule_date,
quantity,
primary_quantity,
tp_quantity,
last_refresh_number,
posting_party_name,
posting_party_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
project_number,
task_number,
planning_group,
planner_code,
version,
designator
) values (
msc_sup_dem_entries_s.nextval,
-1,
-1,
t_pub(j),
t_pub_id(j),
t_pub_site(j),
t_pub_site_id(j),
PROJECTED_AVAILABLE_BALANCE,
l_order_type_desc,
l_bucket_type_desc,
b_bkt_type(k),
t_item_id(j),
t_master_item_name(j),
t_item_name(j),
nvl(t_master_item_desc(j), t_item_desc(j)),
t_item_desc(j),
t_base_item_id(j),
t_base_item_name(j),
t_uom_code(j),
t_uom_code(j),
null,
b_bkt_end_date(k), --l_prev_work_date,
b_bkt_end_date(k), --l_prev_work_date,
l_total,
l_total,
null,
msc_cl_refresh_s.nextval,
t_pub(j),
t_pub_id(j),
nvl(p_user_id,-1),
sysdate,
nvl(p_user_id,-1),
sysdate,
t_proj_number(j),
t_task_number(j),
t_planning_gp(j),
t_planner_code(j),
p_version,
p_designator);
l_record_inserted := l_record_inserted + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Records to be inserted/updated for pab based on horizon date: ' || l_record_inserted);
--dbms_output.put_line('Error in insert_sup_dem_entries: ' || sqlerrm);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error in insert_sup_dem_entries. PSS program will not publish data. Error: ' || sqlerrm);
END insert_into_sup_dem;
PROCEDURE delete_old_safety_stock(
p_plan_id in number,
p_org_id in number,
p_sr_instance_id in number,
p_planner_code in varchar2,
p_abc_class in varchar2,
p_item_id in number,
p_planning_gp in varchar2,
p_project_id in number,
p_task_id in number,
p_horizon_start in date,
p_horizon_end in date,
p_overwrite in number
) IS
l_row number;
IF ( p_overwrite = 1) THEN --delete all
delete from msc_sup_dem_entries sd
where sd.publisher_order_type in (SAFETY_STOCK, PROJECTED_AVAILABLE_BALANCE) and
sd.plan_id = -1 and
sd.publisher_id = 1 and
exists (select cs.company_site_id
from msc_plan_organizations o,
msc_company_sites cs,
msc_trading_partner_maps m,
msc_trading_partners p
where o.plan_id = p_plan_id and
p.sr_tp_id = nvl(p_org_id, o.organization_id) and
p.sr_instance_id = nvl(p_sr_instance_id,
o.sr_instance_id) and
p.partner_type = 3 and
m.tp_key = p.partner_id and
m.map_type = 2 and
cs.company_site_id = m.company_key and
cs.company_id = 1
and cs.company_site_id=sd.publisher_site_id and rownum=1) and
sd.inventory_item_id = nvl(p_item_id, sd.inventory_item_id);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
ELSIF ( p_overwrite = 2) THEN --delete by overwritten
delete from msc_sup_dem_entries sd
where sd.publisher_order_type in (SAFETY_STOCK, PROJECTED_AVAILABLE_BALANCE) and
sd.plan_id = -1 and
sd.publisher_id = 1 and
exists (select cs.company_site_id
from msc_plan_organizations o,
msc_company_sites cs,
msc_trading_partner_maps m,
msc_trading_partners p
where o.plan_id = p_plan_id and
p.sr_tp_id = nvl(p_org_id, o.organization_id) and
p.sr_instance_id = nvl(p_sr_instance_id,
o.sr_instance_id) and
p.partner_type = 3 and
m.tp_key = p.partner_id and
m.map_type = 2 and
cs.company_site_id = m.company_key and
cs.company_id = 1
and sd.publisher_site_id =cs.company_site_id and rownum=1) and
sd.inventory_item_id= nvl(p_item_id, sd.inventory_item_id) and
--NVL(sd.planner_code,'-99') = nvl(p_planner_code, NVL(sd.planner_code, '-99')) and
--NVL(sd.planning_group,'-99') = nvl(p_planning_gp, NVL(sd.planning_group, '-99')) and
--NVL(sd.project_number,'-99') = nvl(p_project_id, NVL(sd.project_number, '-99')) and
--NVL(sd.task_number, '-99') = nvl(p_task_id, NVL(sd.task_number, '-99')) and
key_date between nvl(p_horizon_start, sysdate - 36500) and
nvl(p_horizon_end, sysdate + 36500);
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Deleted number records: ' || l_row);
END delete_old_safety_stock;
SELECT m.message_text
FROM fnd_new_messages m,
fnd_application a
WHERE m.message_name = msg_name AND
m.language_code = lang AND
a.application_short_name = app_name AND
m.application_id = a.application_id;
select meaning
into l_order_type_desc
from mfg_lookups
where lookup_type = 'MSC_X_ORDER_TYPE'
and lookup_code = p_order_type_code;