[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(cmp_mfp.allocated_quantity)
--,sum(ass_mfp.allocated_quantity)
INTO l_numerator
--,l_denominator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_demands md,
msc_supplies ass_ms,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_supplies cmp_ms,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where cmp_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - assembly and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_supplies - assembly */
and ass_ms.inventory_item_id = ass_msi.inventory_item_id
and ass_ms.plan_id = ass_msi.plan_id
and ass_ms.sr_instance_id = ass_msi.sr_instance_id
and ass_ms.organization_id = ass_msi.organization_id
/* msc_demands - assembly and msc_full_pegging - assembly */
and md.demand_id = ass_mfp.demand_id
and md.plan_id = ass_mfp.plan_id
and md.sr_instance_id = ass_mfp.sr_instance_id
and md.organization_id = ass_mfp.organization_id
and md.origination_type in (6,8,29,30)
/* msc_supplies - assembly and msc_full_pegging - assembly */
and ass_ms.transaction_id = ass_mfp.transaction_id
and ass_ms.plan_id = ass_mfp.plan_id
and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
and ass_ms.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - components and msc_supplies - components */
and cmp_mfp.transaction_id = cmp_ms.transaction_id
and cmp_mfp.organization_id = cmp_ms.organization_id
and cmp_mfp.sr_instance_id = cmp_ms.sr_instance_id
and cmp_mfp.plan_id = cmp_ms.plan_id
/* msc_supplies - components and msc_system_items - components */
and cmp_ms.inventory_item_id = cmp_msi.inventory_item_id
and cmp_ms.plan_id = cmp_msi.plan_id
and cmp_ms.sr_instance_id = cmp_msi.sr_instance_id
and cmp_ms.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY,COMPONENT,EFF and DISABLE DATE */
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk
/* Is this really required, as we know the ASSEMBLY and COMPONENT? */
and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id
and md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
select /*+ ORDERED */ sum(ass_mfp.allocated_quantity)
INTO l_denominator
from msc_plan_organizations mpo,
msc_demands ass_md,
msc_system_items ass_msi,
msc_full_pegging ass_mfp,
msc_supplies ass_ms
where ass_msi.plan_id = mpo.plan_id
and ass_msi.sr_instance_id = mpo.sr_instance_id
and ass_msi.organization_id = mpo.organization_id
/* msc_system_items - assembly and msd_demands - assembly */
and ass_msi.plan_id = ass_md.plan_id
and ass_msi.sr_instance_id = ass_md.sr_instance_id
and ass_msi.organization_id = ass_md.organization_id
and ass_msi.inventory_item_id = ass_md.inventory_item_id
and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
/*msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
and ass_md.origination_type in (6,8,29,30)
/* msc_full_pegging - assembly and msc_supplies - assembly */
and ass_ms.transaction_id = ass_mfp.transaction_id
and ass_ms.plan_id = ass_mfp.plan_id
and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
and ass_ms.organization_id = ass_mfp.organization_id
and ass_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
/* For given PLAN,INSTANCE,ASSEMBLY,EFF and DISABLE DATE */
and mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and ass_md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
select /*+ ORDERED */ sum(cmp_mfp.allocated_quantity*(greatest((ass_ms.new_schedule_date - cmp_ms.new_schedule_date),0)))
-- ,sum(cmp_mfp.allocated_quantity)
INTO l_numerator
-- ,l_denominator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_supplies ass_ms,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_supplies cmp_ms,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where cmp_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - assembly and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_supplies - assembly */
and ass_ms.inventory_item_id = ass_msi.inventory_item_id
and ass_ms.plan_id = ass_msi.plan_id
and ass_ms.sr_instance_id = ass_msi.sr_instance_id
and ass_ms.organization_id = ass_msi.organization_id
/* msc_supplies - assembly and msc_full_pegging - assembly */
and ass_ms.transaction_id = ass_mfp.transaction_id
and ass_ms.plan_id = ass_mfp.plan_id
and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
and ass_ms.organization_id = ass_mfp.organization_id
and ass_ms.order_type not in (18,3 ) -- Exclude On Hand Supplies and Discrete Jobs Bug 4878648
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - components and msc_supplies - components */
and cmp_mfp.transaction_id = cmp_ms.transaction_id
and cmp_mfp.organization_id = cmp_ms.organization_id
and cmp_mfp.sr_instance_id = cmp_ms.sr_instance_id
and cmp_mfp.plan_id = cmp_ms.plan_id
/* msc_supplies - components and msc_system_items - components */
and cmp_ms.inventory_item_id = cmp_msi.inventory_item_id
and cmp_ms.plan_id = cmp_msi.plan_id
and cmp_ms.sr_instance_id = cmp_msi.sr_instance_id
and cmp_ms.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY,COMPONENT,EFF and DISABLE DATE */
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk
--and ass_mfp.demand_date between p_effectivity_date and p_disable_date
/* Is this really required, as we know the ASSEMBLY and COMPONENT? */
and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id;
select /*+ ORDERED */ sum(cmp_mfp.allocated_quantity)
INTO l_denominator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_supplies ass_ms,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_supplies cmp_ms,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where cmp_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - assembly and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_supplies - assembly */
and ass_ms.inventory_item_id = ass_msi.inventory_item_id
and ass_ms.plan_id = ass_msi.plan_id
and ass_ms.sr_instance_id = ass_msi.sr_instance_id
and ass_ms.organization_id = ass_msi.organization_id
/* msc_supplies - assembly and msc_full_pegging - assembly */
and ass_ms.transaction_id = ass_mfp.transaction_id
and ass_ms.plan_id = ass_mfp.plan_id
and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
and ass_ms.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - components and msc_supplies - components */
and cmp_mfp.transaction_id = cmp_ms.transaction_id
and cmp_mfp.organization_id = cmp_ms.organization_id
and cmp_mfp.sr_instance_id = cmp_ms.sr_instance_id
and cmp_mfp.plan_id = cmp_ms.plan_id
/* msc_supplies - components and msc_system_items - components */
and cmp_ms.inventory_item_id = cmp_msi.inventory_item_id
and cmp_ms.plan_id = cmp_msi.plan_id
and cmp_ms.sr_instance_id = cmp_msi.sr_instance_id
and cmp_ms.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY,COMPONENT,EFF and DISABLE DATE */
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk
--and ass_mfp.demand_date between p_effectivity_date and p_disable_date
/* Is this really required, as we know the ASSEMBLY and COMPONENT? */
and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id;
select sum((mfp2.allocated_quantity/ms2.new_order_quantity)*mrr.resource_hours)
--,sum(mfp2.allocated_quantity)
INTO l_numerator
--,l_denominator
from
msc_plan_organizations mpo1,
msc_system_items msi,
msc_demands md,
msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_supplies ms2,
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_plan_organizations mpo2
where
mpo1.plan_id = p_supply_plan_id
and msi.sr_inventory_item_id = p_assembly_pk
and msi.plan_id = mpo1.plan_id
and msi.organization_id = mpo1.organization_id
and msi.sr_instance_id = mpo1.sr_instance_id
/* msc_system_items and msc_demands */
and msi.inventory_item_id = md.inventory_item_id
and msi.plan_id = md.plan_id
and msi.organization_id = md.organization_id
and msi.sr_instance_id = md.sr_instance_id
/*msc_demands and msc_full_pegging1 */
and md.demand_id = mfp1.demand_id
and md.plan_id = mfp1.plan_id
and md.sr_instance_id = mfp1.sr_instance_id
and md.organization_id = mfp1.organization_id
and md.origination_type in (6,8,29,30)
/*msc_full_pegging1 and msc_full_pegging2 */
and mfp1.pegging_id = mfp2.end_pegging_id
and mfp1.plan_id = mfp2.plan_id
and mfp1.sr_instance_id = mfp2.sr_instance_id -- (No organization id join between mfp1 and mfp2 because single demand can span across various orgs.
/* msc_full_pegging2 and msc_resource_requirements */
and mfp2.transaction_id = mrr.supply_id
and mfp2.plan_id = mrr.plan_id
and mfp2.sr_instance_id = mrr.sr_instance_id
and mfp2.organization_id = mrr.organization_id
/* msc_full_pegging2 and msc_supplies */
and ms2.transaction_id = mfp2.transaction_id
and ms2.plan_id = mfp2.plan_id
and ms2.sr_instance_id = mfp2.sr_instance_id
and ms2.organization_id = mfp2.organization_id
/* msc_resource_requirements and msc_department_resources */
and mrr.resource_id = mdr.resource_id
and mrr.plan_id = mdr.plan_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.organization_id = mdr.organization_id
/* msc_department_resources and msc_plan_organizations */
and decode(mdr.resource_id,-1,mdr.department_code,mdr.resource_code)= p_component_pk
and mdr.plan_id = p_supply_plan_id
and mdr.sr_instance_id = p_instance_id
and mdr.organization_id = mpo2.organization_id
and mpo2.plan_id = p_supply_plan_id
and mrr.parent_id = 2 -- Records Inserted by HLS as Net Resource Requirements
and md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
select /*+ ORDERED */ sum(ass_mfp.allocated_quantity)
INTO l_denominator
from msc_plan_organizations mpo,
msc_demands ass_md,
msc_system_items ass_msi,
msc_full_pegging ass_mfp,
msc_supplies ass_ms
where ass_msi.plan_id = mpo.plan_id
and ass_msi.sr_instance_id = mpo.sr_instance_id
and ass_msi.organization_id = mpo.organization_id
/* msc_system_items - assembly and msd_demands - assembly */
and ass_msi.plan_id = ass_md.plan_id
and ass_msi.sr_instance_id = ass_md.sr_instance_id
and ass_msi.organization_id = ass_md.organization_id
and ass_msi.inventory_item_id = ass_md.inventory_item_id
and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
/*msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
and ass_md.origination_type in (6,8,29,30)
/* msc_full_pegging - assembly and msc_supplies - assembly */
and ass_ms.transaction_id = ass_mfp.transaction_id
and ass_ms.plan_id = ass_mfp.plan_id
and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
and ass_ms.organization_id = ass_mfp.organization_id
and ass_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
/* For given PLAN,INSTANCE,ASSEMBLY,EFF and DISABLE DATE */
and mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and ass_md.using_assembly_demand_date between p_effectivity_date and p_disable_date;
select sum(mfp2.allocated_quantity*(greatest((ms.new_schedule_date-mrr.end_date),0)))
--sum(mfp2.allocated_quantity),
INTO l_numerator
--l_denominator,
from
msc_plan_organizations mpo1,
msc_system_items msi,
msc_supplies ms,
msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_resource_requirements mrr,
msc_department_resources mdr,
msc_plan_organizations mpo2
where
mpo1.plan_id = p_supply_plan_id
and msi.sr_inventory_item_id = p_assembly_pk
and msi.plan_id = mpo1.plan_id
and msi.organization_id = mpo1.organization_id
and msi.sr_instance_id = mpo1.sr_instance_id
/* msc_system_items - assembly and msc_supplies - assembly */
and msi.inventory_item_id = ms.inventory_item_id
and msi.plan_id = ms.plan_id
and msi.organization_id = ms.organization_id
and msi.sr_instance_id = ms.sr_instance_id
/*msc_supplies - assembly and msc_full_pegging1 - assembly */
and ms.transaction_id = mfp1.transaction_id
and ms.plan_id = mfp1.plan_id
and ms.sr_instance_id = mfp1.sr_instance_id
and ms.organization_id = mfp1.organization_id
/*msc_full_pegging1 - assembly and msc_full_pegging2 - component */
and mfp1.pegging_id = mfp2.end_pegging_id
and mfp1.plan_id = mfp2.plan_id
and mfp1.sr_instance_id = mfp2.sr_instance_id -- (No organization id join between mfp1 and mfp2 because single demand ca span across various orgs.
/* msc_full_pegging2 - component and msc_resource_requirements */
and mfp2.transaction_id = mrr.supply_id
and mfp2.plan_id = mrr.plan_id
and mfp2.sr_instance_id = mrr.sr_instance_id
and mfp2.organization_id = mrr.organization_id
/* msc_resource_requirements and msc_department_resources */
and mrr.resource_id = mdr.resource_id
and mrr.plan_id = mdr.plan_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.organization_id = mdr.organization_id
/* msc_department_resources and msc_plan_organizations - component */
and decode(mdr.resource_id,-1,mdr.department_code,mdr.resource_code)= p_component_pk
and mdr.plan_id = mpo2.plan_id
and mdr.sr_instance_id = mpo2.sr_instance_id
and mdr.organization_id = mpo2.organization_id
and mpo2.plan_id = p_supply_plan_id
--and mfp1.demand_date between p_effectivity_date and p_disable_date
and mrr.parent_id = 2; -- Records Inserted by HLS as Net Resource Requirements
select /*+ ORDERED */ sum(ass_mfp.allocated_quantity)
INTO l_denominator
from msc_plan_organizations mpo,
msc_demands ass_md,
msc_system_items ass_msi,
msc_full_pegging ass_mfp,
msc_supplies ass_ms
where ass_msi.plan_id = mpo.plan_id
and ass_msi.sr_instance_id = mpo.sr_instance_id
and ass_msi.organization_id = mpo.organization_id
/* msc_system_items and msd_demands */
and ass_msi.plan_id = ass_md.plan_id
and ass_msi.sr_instance_id = ass_md.sr_instance_id
and ass_msi.organization_id = ass_md.organization_id
and ass_msi.inventory_item_id = ass_md.inventory_item_id
and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
/*msc_demands and msc_full_pegging */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
and ass_md.origination_type in (6,8,29,30)
/* msc_full_pegging and msc_supplies */
and ass_ms.transaction_id = ass_mfp.transaction_id
and ass_ms.plan_id = ass_mfp.plan_id
and ass_ms.sr_instance_id = ass_mfp.sr_instance_id
and ass_ms.organization_id = ass_mfp.organization_id
and ass_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
/* For given PLAN,INSTANCE,ASSEMBLY,EFF and DISABLE DATE */
and mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk;
select sum(cmp_mfp.allocated_quantity)
INTO l_numerator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where ass_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - components and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
and ass_md.origination_type in (6,8,29,30)
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
/* and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id Bug 5211017*/
/* msc_full_pegging - components and msc_demands - components */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_mfp.organization_id = cmp_md.organization_id
and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
and cmp_mfp.plan_id = cmp_md.plan_id
/* msc_demands - components and msc_system_items - components */
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk;
select sum(cmp_md.USING_REQUIREMENT_QUANTITY)
INTO l_denominator
from
msc_plan_organizations cmp_mpo,
msc_system_items cmp_msi,
msc_demands cmp_md
where cmp_mpo.plan_id = p_supply_plan_id
/* msc_system_items - comp and msc_plan_organizations - comp */
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
and cmp_msi.organization_id = cmp_mpo.organization_id
/* msc_demands - comp and msc_system_items - comp */
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
/* For given PLAN,INSTANCE and COMPONENT*/
and cmp_mpo.plan_id = p_supply_plan_id
and cmp_msi.sr_instance_id = p_instance_id
and cmp_msi.sr_inventory_item_id = p_component_pk;
select sum(cmp_mfp.allocated_quantity)
INTO l_numerator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
--msc_supplies cmp_ms BUG 5210812,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where ass_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - components and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
and ass_md.origination_type in (6,8,29,30) -- Independent Demands of Assembly
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) -- Independent Demands of Assembly
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - components and msc_demands - components */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_mfp.organization_id = cmp_md.organization_id
and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
and cmp_mfp.plan_id = cmp_md.plan_id
and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
/* msc_full_pegging - components and msc_supplies - componnets
and cmp_ms.transaction_id = cmp_mfp.transaction_id
and cmp_ms.plan_id = cmp_mfp.plan_id
and cmp_ms.sr_instance_id = cmp_mfp.sr_instance_id
and cmp_ms.organization_id = cmp_mfp.organization_id
and cmp_ms.order_type not in ( 18 ) -- Exclude On Hand Supplies
Commented above code for the BUG 5210812*/
/* msc_demands - components and msc_system_items - components */
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk;
select sum(ass_md.USING_REQUIREMENT_QUANTITY)
INTO l_denominator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_demands ass_md
where ass_mpo.plan_id = p_supply_plan_id
/* msc_system_items - asmb and msc_plan_organizations - asmb */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_demands - asmb and msc_system_items - asmb */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
and ass_md.origination_type in (6,8,29,30) -- Independent Demands for Assembly
/* For given PLAN,INSTANCE and COMPONENT */
and ass_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk;
select sum(cmp_mfp.allocated_quantity*(ass_md.USING_ASSEMBLY_DEMAND_DATE - cmp_md.USING_ASSEMBLY_DEMAND_DATE))
INTO l_denominator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where ass_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - components and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
and ass_md.origination_type in (6,8,29,30) -- Independent Demands of Assembly
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) -- Independent Demands of Assembly
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - components and msc_demands - components */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_mfp.organization_id = cmp_md.organization_id
and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
and cmp_mfp.plan_id = cmp_md.plan_id
and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
/* msc_demands - components and msc_system_items - components */
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk;
select sum(cmp_mfp.allocated_quantity)
INTO l_denominator
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where ass_mpo.plan_id = p_supply_plan_id
/* mpo_plan_organizations - components and msc_system_items - assembly */
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
and ass_md.origination_type in (6,8,29,30) -- Independent Demands of Assembly
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - components */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.end_origination_type in (6,8,29,30) -- Independent Demands of Assembly
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - components and msc_demands - components */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_mfp.organization_id = cmp_md.organization_id
and cmp_mfp.sr_instance_id = cmp_md.sr_instance_id
and cmp_mfp.plan_id = cmp_md.plan_id
and cmp_md.origination_type in (29,30,8,6,24,3,1,54) -- Gross Requirements of Components across All Organizations.
/* msc_demands - components and msc_system_items - components */
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - components and mpo_plan_organizations - components */
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
/* For given PLAN,INSTANCE,ASSEMBLY and COMPONENT*/
and cmp_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = p_instance_id
and ass_msi.sr_inventory_item_id = p_assembly_pk
and cmp_msi.sr_inventory_item_id = p_component_pk;
select distinct supply_plan_id
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and nvl(supply_plan_id,-1) > 0; -- For Legacy Supply Plans the Supply_Plan_Name field will be populated with the Designators.
select /*+ ORDERED */ distinct
ass_msi.sr_instance_id SR_INSTANCE_ID,
ass_msi.sr_inventory_item_id SR_ASSEMBLY_PK,
cmp_msi.sr_inventory_item_id SR_COMPONENT_PK,
trunc(ass_mfp.demand_date,'MM') EFFECTIVITY_DATE,
last_day(ass_mfp.demand_date) DISABLE_DATE
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msd_level_values ass_mlv,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
msd_level_values cmp_mlv,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where ass_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msd_leve_values - assembly */
and ass_mlv.instance = ass_msi.sr_instance_id
and ass_mlv.level_id = 1
and ass_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - component */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
and cmp_mfp.pegging_id <> cmp_mfp.end_pegging_id
/* msc_full_pegging - component and msc_demands - component */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - assembly and msd_leve_values - assembly */
and cmp_mlv.instance = ass_msi.sr_instance_id
and cmp_mlv.level_id = 1
and cmp_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
/* msc_system_items - components and msc_demands - components */
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_msi.planning_make_buy_code = 2 -- Buy Items Only
and cmp_msi.critical_component_flag = 1 -- Critical Component Only
and cmp_mpo.plan_id = p_supply_plan_id -- For a given ascp plan
and ass_msi.sr_inventory_item_id <> cmp_msi.sr_inventory_item_id; -- This condition is required as we are getting the assembly as component to same assembly, in case of Inter Org Transfer.
select /*+ ORDERED */
distinct
msi.sr_instance_id SR_INSTANCE_ID,
msi.sr_inventory_item_id SR_ASSEMBLY_PK,
decode(mdr.resource_id,-1,'L'||'.'||mdr.department_code,'R'||'.'||mdr.resource_code) SR_COMPONENT_PK,
trunc(mfp1.demand_date,'MM') EFFECTIVITY_DATE,
last_day(mfp1.demand_date) DISABLE_DATE
from
msc_plan_organizations mpo1,
msc_system_items msi,
msd_level_values mlv1,
msc_demands md,
msc_full_pegging mfp1,
msc_full_pegging mfp2,
msc_resource_requirements mrr,
msc_department_resources mdr,
msd_level_values mlv2,
msc_plan_organizations mpo2
where
mpo1.plan_id = p_supply_plan_id
and msi.plan_id = mpo1.plan_id
and msi.organization_id = mpo1.organization_id
and msi.sr_instance_id = mpo1.sr_instance_id
/*msc_system_items and msd_level_values */
and mlv1.instance = msi.sr_instance_id
and mlv1.level_id = 1
and mlv1.sr_level_pk = to_char(msi.sr_inventory_item_id)
/* msc_system_items and msc_demands */
and msi.inventory_item_id = md.inventory_item_id
and msi.plan_id = md.plan_id
and msi.organization_id = md.organization_id
and msi.sr_instance_id = md.sr_instance_id
/*msc_demands and msc_full_pegging1 */
and md.demand_id = mfp1.demand_id
and md.plan_id = mfp1.plan_id
and md.sr_instance_id = mfp1.sr_instance_id
and md.organization_id = mfp1.organization_id
/*msc_full_pegging1 and msc_full_pegging2 */
and mfp1.pegging_id = mfp2.end_pegging_id
and mfp1.plan_id = mfp2.plan_id
and mfp1.sr_instance_id = mfp2.sr_instance_id -- (No organization id join between mfp1 and mfp2 because single demand ca span across various orgs.
/* msc_full_pegging2 and msc_resource_requirements */
and mfp2.transaction_id = mrr.supply_id
and mfp2.plan_id = mrr.plan_id
and mfp2.sr_instance_id = mrr.sr_instance_id
and mfp2.organization_id = mrr.organization_id
/* msc_resource_requirements and msc_department_resources */
and mrr.resource_id = mdr.resource_id
and mrr.plan_id = mdr.plan_id
and mrr.sr_instance_id = mdr.sr_instance_id
and mrr.organization_id = mdr.organization_id
/* msc_department_resources and msd_level_values */
and mlv2.instance = mdr.sr_instance_id
and mlv2.level_id = 1
and mlv2.sr_level_pk = decode(mdr.resource_id,-1,'L'||'.'||mdr.department_code,'R'||'.'||mdr.resource_code)
/* msc_department_resources and msc_plan_organizations */
and mdr.plan_id = mpo2.plan_id
and mdr.sr_instance_id = mpo2.sr_instance_id
and mdr.organization_id = mpo2.organization_id
and mpo2.plan_id = p_supply_plan_id
and mrr.parent_id = 2;
select distinct
sup_plan_bom.sr_instance_id,
sup_plan_bom.sr_assembly_pk,
sup_plan_bom.sr_component_pk,
sup_plan_bom.effectivity_date,
sup_plan_bom.disable_date,
sup_plan_bom.res_comp
from msd_ascp_bom_comp sup_plan_bom,
msc_plans sup_plan,
msd_dp_scenarios dp_scen
where sup_plan_bom.cap_usg_ratio_obj = sup_plan.compile_designator
and sup_plan.plan_id = dp_scen.supply_plan_id
and dp_scen.demand_plan_id = p_demand_plan_id
and dp_scen.supply_plan_id is not null
--and dp_scen.supply_plan_id > 0 (Required or Redundant)..?
order by sup_plan_bom.sr_instance_id,sup_plan_bom.sr_assembly_pk,sup_plan_bom.sr_component_pk;
select scenario_name,supply_plan_name,old_supply_plan_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and supply_plan_name is not null; -- No need to add condition of supply_plan_id > 0 because we need to
select scenario_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and old_supply_plan_name = p_supply_plan_name
AND rownum < 2;
select scenario_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and supply_plan_name = p_supply_plan_name
AND rownum < 2;
select compile_designator,trunc(plan_completion_date) into v_plan_name,v_plan_comp_date
from msc_plans
where plan_id = Supply_Plans_Rec.supply_plan_id;
select nvl(trunc(last_collected_date),C_NULL_DATE) into v_last_collected_date
from msd_ascp_bom_comp
where cap_usg_ratio_obj = v_plan_name
and plan_type= 'SOP'
and rownum < 2;
delete from msd_ascp_bom_comp
where cap_usg_ratio_obj = v_plan_name
and plan_type = 'SOP';
INSERT INTO msd_ascp_bom_comp
( SR_INSTANCE_ID,
CAP_USG_RATIO_OBJ,
SR_ASSEMBLY_PK,
SR_COMPONENT_PK,
EFFECTIVITY_DATE,
DISABLE_DATE,
RES_COMP,
CAPACITY_USAGE_RATIO,
LEAD_TIME,
LAST_COLLECTED_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_TYPE,
BOM_TYPE )
SELECT
lb_instance_id(j),
v_plan_name,
lb_assembly_pk(j),
lb_component_pk(j),
lb_effectivity_date(j),
lb_disable_date(j),
'C',
calculate_cu_and_lt(C_CU,
lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
lb_component_pk(j),
'C',
lb_effectivity_date(j),
lb_disable_date(j)),
calculate_cu_and_lt(C_LT,
lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
lb_component_pk(j),
'C',
lb_effectivity_date(j),
lb_disable_date(j)),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
'SOP',
'SOP'
FROM DUAL;
INSERT INTO msd_ascp_bom_comp
( SR_INSTANCE_ID,
CAP_USG_RATIO_OBJ,
SR_ASSEMBLY_PK,
SR_COMPONENT_PK,
EFFECTIVITY_DATE,
DISABLE_DATE,
RES_COMP,
CAPACITY_USAGE_RATIO,
LEAD_TIME,
LAST_COLLECTED_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_TYPE,
BOM_TYPE )
SELECT
lb_instance_id(j),
v_plan_name,
lb_assembly_pk(j),
lb_component_pk(j),
lb_effectivity_date(j),
lb_disable_date(j),
substr(lb_component_pk(j),1,1),
calculate_cu_and_lt(C_CU,
lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
substr(lb_component_pk(j),3,length(lb_component_pk(j))),
substr(lb_component_pk(j),1,1),
lb_effectivity_date(j),
lb_disable_date(j)),
calculate_cu_and_lt(C_LT,
lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
substr(lb_component_pk(j),3,length(lb_component_pk(j))),
substr(lb_component_pk(j),1,1),
lb_effectivity_date(j),
lb_disable_date(j)),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
'SOP',
'SOP'
FROM DUAL;
select nvl(min(CURR_START_DATE),to_date(null)),to_date(null) INTO l_effectivity_date,l_disable_date
from msc_plans
where plan_id in ( select distinct supply_plan_id
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and supply_plan_id > 0 );
delete msd_sop_collapsed_bom_comp
where demand_plan_id = p_demand_plan_id; --where sop_plan_id = p_demand_plan_id;
INSERT INTO msd_sop_collapsed_bom_comp
( SR_INSTANCE_ID,
DEMAND_PLAN_ID, --SOP_PLAN_ID,
SR_ASSEMBLY_PK,
SR_COMPONENT_PK,
EFFECTIVITY_DATE,
DISABLE_DATE,
RES_COMP,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_TYPE,
BOM_TYPE )
VALUES
( lb_instance_id(j),
p_demand_plan_id,
lb_assembly_pk(j),
lb_component_pk(j),
lb_effectivity_date(j), --l_effectivity_date,
lb_disable_date(j), --l_disable_date,
lb_res_comp(j),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
'SOP',
'SOP' );
update msd_dp_scenarios
set old_supply_plan_id = supply_plan_id,
old_supply_plan_name = supply_plan_name
where demand_plan_id = p_demand_plan_id
and supply_plan_name is not null;
update msd_dp_scenarios
set old_supply_plan_id = supply_plan_id,
old_supply_plan_name = supply_plan_name
where demand_plan_id = p_demand_plan_id
and supply_plan_name is not null;
select distinct supply_plan_id
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and nvl(supply_plan_id,-1) > 0; -- For Legacy Supply Plans the Supply_Plan_Name field will be populated with the Designators.
select /*+ ORDERED */ distinct
ass_msi.sr_instance_id SR_INSTANCE_ID,
ass_msi.sr_inventory_item_id SR_ASSEMBLY_PK,
cmp_msi.sr_inventory_item_id SR_COMPONENT_PK
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msd_level_values ass_mlv,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
msd_level_values cmp_mlv,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where
/* msc_system_items - assembly and msc_plans - assembly */
ass_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msd_level_values - assembly */
and ass_mlv.instance = ass_msi.sr_instance_id
and ass_mlv.level_id = 1
and ass_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - component */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
/* msc_full_pegging - component and msc_demands - component */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - assembly and msd_leve_values - assembly */
and cmp_mlv.instance = ass_msi.sr_instance_id
and cmp_mlv.level_id = 1
and cmp_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
/* msc_system_items - components and msc_demands - components */
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_mpo.plan_id = p_supply_plan_id; -- For a given ascp plan
select /*+ ORDERED */ distinct
ass_msi.sr_instance_id SR_INSTANCE_ID,
ass_msi.sr_inventory_item_id SR_ASSEMBLY_PK,
cmp_msi.sr_inventory_item_id SR_COMPONENT_PK
from
msc_plan_organizations ass_mpo,
msc_system_items ass_msi,
msd_level_values ass_mlv,
msc_demands ass_md,
msc_full_pegging ass_mfp,
msc_full_pegging cmp_mfp,
msc_demands cmp_md,
msd_level_values cmp_mlv,
msc_system_items cmp_msi,
msc_plan_organizations cmp_mpo
where
/* msc_system_items - assembly and msc_plans - assembly */
ass_mpo.plan_id = p_supply_plan_id
and ass_msi.sr_instance_id = ass_mpo.sr_instance_id
and ass_msi.plan_id = ass_mpo.plan_id
and ass_msi.organization_id = ass_mpo.organization_id
/* msc_system_items - assembly and msd_level_values - assembly */
and ass_mlv.instance = ass_msi.sr_instance_id
and ass_mlv.level_id = 1
and ass_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
/* msc_system_items - assembly and msc_demands - assembly */
and ass_md.inventory_item_id = ass_msi.inventory_item_id
and ass_md.origination_type in (6,8,29,30) --Include all independent Demand Types
and ass_md.plan_id = ass_msi.plan_id
and ass_md.sr_instance_id = ass_msi.sr_instance_id
and ass_md.organization_id = ass_msi.organization_id
/* msc_demands - assembly and msc_full_pegging - assembly */
and ass_md.demand_id = ass_mfp.demand_id
and ass_md.plan_id = ass_mfp.plan_id
and ass_md.sr_instance_id = ass_mfp.sr_instance_id
and ass_md.organization_id = ass_mfp.organization_id
/* msc_full_pegging - assembly and msc_full_pegging - component */ -- No organization_id join between ass_mfp and cmp_mfp because single demand can span across various orgs.
and ass_mfp.pegging_id = cmp_mfp.end_pegging_id
and cmp_mfp.plan_id = ass_mfp.plan_id
and cmp_mfp.sr_instance_id = ass_mfp.sr_instance_id
/* msc_full_pegging - component and msc_demands - component */
and cmp_mfp.demand_id = cmp_md.demand_id
and cmp_md.inventory_item_id = cmp_msi.inventory_item_id
and cmp_md.plan_id = cmp_msi.plan_id
and cmp_md.sr_instance_id = cmp_msi.sr_instance_id
and cmp_md.organization_id = cmp_msi.organization_id
/* msc_system_items - assembly and msd_leve_values - assembly */
and cmp_mlv.instance = ass_msi.sr_instance_id
and cmp_mlv.level_id = 1
and cmp_mlv.sr_level_pk = to_char(ass_msi.sr_inventory_item_id)
/* msc_system_items - components and msc_demands - components */
and cmp_msi.sr_instance_id = cmp_mpo.sr_instance_id
and cmp_msi.plan_id = cmp_mpo.plan_id
and cmp_msi.organization_id = cmp_mpo.organization_id
and cmp_mpo.plan_id = p_supply_plan_id; -- For a given ascp plan
select distinct
sup_plan_bom.sr_instance_id,
sup_plan_bom.sr_assembly_pk,
sup_plan_bom.sr_component_pk,
sup_plan_bom.bom_type
from msd_ascp_bom_comp sup_plan_bom,
msc_plans sup_plan,
msd_dp_scenarios dp_scen
where sup_plan_bom.cap_usg_ratio_obj = sup_plan.compile_designator
and sup_plan.plan_id = dp_scen.supply_plan_id
and dp_scen.demand_plan_id = p_demand_plan_id
and dp_scen.supply_plan_id is not null
order by sup_plan_bom.sr_instance_id,sup_plan_bom.sr_assembly_pk,sup_plan_bom.sr_component_pk;
select scenario_name,supply_plan_name,old_supply_plan_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and supply_plan_name is not null; -- No need to add condition of supply_plan_id > 0 because we need to
select scenario_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and old_supply_plan_name = p_supply_plan_name
AND rownum < 2;
select scenario_name
from msd_dp_scenarios
where demand_plan_id = p_demand_plan_id
and supply_plan_name = p_supply_plan_name
AND rownum < 2;
select compile_designator,trunc(plan_completion_date) into v_plan_name,v_plan_comp_date
from msc_plans
where plan_id = Supply_Plans_Rec.supply_plan_id;
select distinct nvl(trunc(last_collected_date),C_NULL_DATE) into v_last_collected_date
from msd_ascp_bom_comp
where cap_usg_ratio_obj = v_plan_name
and plan_type = 'EOL'
and rownum < 2;
delete from msd_ascp_bom_comp
where cap_usg_ratio_obj = v_plan_name
and plan_type = 'EOL';
INSERT INTO msd_ascp_bom_comp
( SR_INSTANCE_ID,
CAP_USG_RATIO_OBJ,
SR_ASSEMBLY_PK,
SR_COMPONENT_PK,
CAPACITY_USAGE_RATIO,
LAST_COLLECTED_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_TYPE,
BOM_TYPE )
SELECT
lb_instance_id(j),
v_plan_name,
lb_assembly_pk(j),
lb_component_pk(j),
calc_eol_wur(lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
lb_component_pk(j)),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
'EOL',
'WUR'
FROM DUAL;
INSERT INTO msd_ascp_bom_comp
( SR_INSTANCE_ID,
CAP_USG_RATIO_OBJ,
SR_ASSEMBLY_PK,
SR_COMPONENT_PK,
CAPACITY_USAGE_RATIO,
LEAD_TIME,
LAST_COLLECTED_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_TYPE,
BOM_TYPE )
SELECT
lb_instance_id(j),
v_plan_name,
lb_assembly_pk(j),
lb_component_pk(j),
calc_eol_smb(C_CU,
lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
lb_component_pk(j)),
calc_eol_smb(C_LT,
lb_instance_id(j),
Supply_Plans_Rec.supply_plan_id,
lb_assembly_pk(j),
lb_component_pk(j)),
sysdate,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
'EOL',
'SMB'
FROM DUAL;
delete msd_sop_collapsed_bom_comp
where demand_plan_id = p_demand_plan_id;
INSERT INTO msd_sop_collapsed_bom_comp
( SR_INSTANCE_ID,
DEMAND_PLAN_ID,
SR_ASSEMBLY_PK,
SR_COMPONENT_PK,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
PLAN_TYPE,
BOM_TYPE )
VALUES
( lb_instance_id(j),
p_demand_plan_id,
lb_assembly_pk(j),
lb_component_pk(j),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
'EOL',
lb_bom_type(j) );
update msd_dp_scenarios
set old_supply_plan_id = supply_plan_id,
old_supply_plan_name = supply_plan_name
where demand_plan_id = p_demand_plan_id
and supply_plan_name is not null;
select decode(nvl(plan_type,C_DP),'SOP',C_SOP,'EOL',C_EOL,10) into v_plan_type
from msd_demand_plans
where demand_plan_id = p_demand_plan_id;