[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(summary_flag, 1), instance_code
into l_summary_flag, l_instance_code
FROM msc_apps_instances
where instance_id = p_instance_id;
SELECT a.oracle_username
INTO l_msc_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
SELECT count(*)
INTO l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM DBA_TAB_PARTITIONS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = l_table_name
AND PARTITION_NAME = l_partition_name
AND table_owner = l_msc_schema;
UPDATE msc_apps_instances
set so_tbl_status = 2,
summary_flag = 2
where instance_id = p_instance_id;
INSERT INTO MSC_TEMP_SUMM_SO (
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
plan_id,
sr_instance_id,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT so.organization_id,
so.inventory_item_id,
so.demand_class,
so.SD_DATE,
sum(so.sd_qty),
-1,
p_instance_id,
l_sys_date,
l_user_id,
l_sys_date,
l_user_id
FROM
(SELECT
I.organization_id,
I.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1, NVL(D.DEMAND_CLASS,
NVL(TP.default_demand_class,'@@@')), '@@@') demand_class,
DECODE(D.RESERVATION_TYPE,2,C2.next_date, trunc(D.REQUIREMENT_DATE)) SD_DATE,
(D.PRIMARY_UOM_QUANTITY-
GREATEST(NVL(D.RESERVATION_QUANTITY,0),
D.COMPLETED_QUANTITY)) sd_qty
FROM
MSC_SYSTEM_ITEMS I,
MSC_ATP_RULES R,
MSC_SALES_ORDERS D,
MSC_CALENDAR_DATES C,
MSC_CALENDAR_DATES C2,
MSC_TRADING_PARTNERS TP
WHERE I.ATP_FLAG = 'Y'
AND I.ORGANIZATION_ID = TP.SR_TP_ID
AND I.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND I.PLAN_ID = -1
AND I.BOM_ITEM_TYPE <> 5
AND R.RULE_ID = NVL(I.ATP_RULE_ID, TP.default_atp_rule_id)
AND R.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
AND D.DEMAND_SOURCE_TYPE <>
DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
AND D.PRIMARY_UOM_QUANTITY >
GREATEST(NVL(D.RESERVATION_QUANTITY,0),
D.COMPLETED_QUANTITY)
AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
(SELECT S.SUB_INVENTORY_CODE
FROM MSC_SUB_INVENTORIES S
WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2, 1, S.NETTING_TYPE)))
AND (D.RESERVATION_TYPE = 2
OR D.PARENT_DEMAND_ID IS NULL
OR (D.RESERVATION_TYPE = 3 AND
((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
(R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
C2.next_seq_num - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.CALENDAR_CODE = TP.CALENDAR_CODE
AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND C.EXCEPTION_SET_ID = -1
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
AND C2.CALENDAR_CODE = TP.calendar_code
AND C2.EXCEPTION_SET_ID = TP.calendar_exception_set_id
AND C2.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND C2.CALENDAR_DATE = TRUNC(l_sys_date)
AND TP.SR_INSTANCE_ID = p_instance_id
AND TP.PARTNER_TYPE = 3
) SO
GROUP BY so.inventory_item_id, so.organization_id, so.demand_class,
so.sd_date, -1, p_instance_id, l_sys_date, l_user_id);
INSERT INTO MSC_TEMP_SUMM_SD (
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
plan_id,
sr_instance_id,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT sd.organization_id,
sd.inventory_item_id,
sd.demand_class,
sd.SD_DATE,
sum(sd.sd_qty),
-1,
p_instance_id,
l_sys_date,
l_user_id,
l_sys_date,
l_user_id
FROM
(SELECT I.organization_id,
DECODE(I2.ATP_FLAG, 'Y', I2.INVENTORY_ITEM_ID,
I.INVENTORY_ITEM_ID) inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 , NVL(D.DEMAND_CLASS,
NVL(TP.default_demand_class,'@@@')), '@@@') demand_class,
C.PRIOR_DATE SD_DATE, -- 2859130
-1* D.USING_REQUIREMENT_QUANTITY SD_QTY
FROM
MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2,
MSC_ATP_RULES R,
MSC_DEMANDS D,
MSC_CALENDAR_DATES C,
MSC_CALENDAR_DATES C2,
MSC_TRADING_PARTNERS TP
WHERE I.ATP_FLAG = 'Y'
AND I.ORGANIZATION_ID = TP.SR_TP_ID
AND I.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND I.PLAN_ID = -1
AND I.BOM_ITEM_TYPE <> 5
AND I.PLAN_ID = I2.PLAN_ID
AND I.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
AND I2.ORGANIZATION_ID = I.ORGANIZATION_ID
AND I2.INVENTORY_ITEM_ID = NVL(I.PRODUCT_FAMILY_ID,
I.INVENTORY_ITEM_ID)
AND R.RULE_ID = NVL(I.ATP_RULE_ID ,TP.default_atp_rule_id)
AND R.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND USING_REQUIREMENT_QUANTITY <> 0
AND D.ORIGINATION_TYPE in (
DECODE(R.INCLUDE_DISCRETE_WIP_DEMAND, 1, 3, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND, 1, 25, -1),
DECODE(R.INCLUDE_USER_DEFINED_DEMAND, 1, 42, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 2, -1),
DECODE(R.INCLUDE_REP_WIP_DEMAND, 1, 4, -1))
AND C.CALENDAR_CODE = TP.calendar_code
AND C.EXCEPTION_SET_ID = -1
AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND C.CALENDAR_DATE BETWEEN TRUNC(D.USING_ASSEMBLY_DEMAND_DATE)
AND TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
D.USING_ASSEMBLY_DEMAND_DATE))
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
C2.next_seq_num - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C2.CALENDAR_CODE = TP.calendar_code
AND C2.EXCEPTION_SET_ID = TP.calendar_exception_set_id
AND C2.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND C2.CALENDAR_DATE = TRUNC(l_sys_date)
AND TP.SR_INSTANCE_ID = p_instance_id
AND TP.PARTNER_TYPE = 3
UNION ALL
SELECT
I.organization_id,
I.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(DECODE(S.ORDER_TYPE, 5,
MSC_ATP_FUNC.Get_MPS_Demand_Class(S.SCHEDULE_DESIGNATOR_ID),
S.DEMAND_CLASS), NVL(TP.default_demand_class, '@@@')), '@@@')
demand_class,
C.NEXT_DATE SD_DATE, -- 2859130 remove trunc
Decode(order_type, -- 2859130 remove trunc
30, Decode(Sign(S.Daily_rate * (C.Calendar_date -
TRUNC(S.FIRST_UNIT_START_DATE))- S.qty_completed),
-1,S.Daily_rate*(C.Calendar_date - TRUNC(S.First_Unit_Start_date)+1)
- S.qty_completed, S.Daily_rate),
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) ) SD_QTY
FROM
MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2,
MSC_ATP_RULES R,
MSC_SUPPLIES S,
MSC_SUB_INVENTORIES MSI,
MSC_CALENDAR_DATES C,
MSC_CALENDAR_DATES C2,
MSC_TRADING_PARTNERS TP
WHERE I.ATP_FLAG = 'Y'
AND I.ORGANIZATION_ID = TP.SR_TP_ID
AND I.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND I.PLAN_ID = -1
AND I.PLAN_ID = I2.PLAN_ID
AND I.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
AND NVL(I.PRODUCT_FAMILY_ID, I.INVENTORY_ITEM_ID) =
I2.INVENTORY_ITEM_ID
AND DECODE(I.PRODUCT_FAMILY_ID, NULL, 'N', I2.ATP_FLAG ) = 'N'
AND R.RULE_ID = NVL(I.ATP_RULE_ID, TP.default_atp_rule_id)
AND R.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
-- 2859130 remove trunc
AND Decode(S.order_type, 30, S.Daily_rate*
(C.Calendar_date - TRUNC(S.First_Unit_Start_date) + 1) ,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) >
Decode(S.order_type, 30, S.qty_completed,0)
AND (S.ORDER_TYPE IN (
DECODE(R.INCLUDE_PURCHASE_ORDERS, 1, 1, -1),
DECODE(R.INCLUDE_PURCHASE_ORDERS, 1, 8, -1), --1882898
DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 3, -1),
DECODE(R.INCLUDE_REP_WIP_RECEIPTS, 1, 30, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 7, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 15, -1),
DECODE(R.INCLUDE_INTERORG_TRANSFERS, 1, 11, -1),
DECODE(R.INCLUDE_INTERORG_TRANSFERS, 1, 12, -1),
DECODE(R.INCLUDE_ONHAND_AVAILABLE, 1, 18, -1),
DECODE(R.INCLUDE_INTERNAL_REQS, 1, 2, -1),
DECODE(R.INCLUDE_SUPPLIER_REQS, 1, 2, -1),
DECODE(R.INCLUDE_USER_DEFINED_SUPPLY, 1, 41, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, 27, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, 28, -1))
OR
((R.INCLUDE_REP_MPS = 1 OR R.INCLUDE_DISCRETE_MPS = 1) AND
S.ORDER_TYPE = 5
AND exists (SELECT '1'
FROM MSC_DESIGNATORS
WHERE INVENTORY_ATP_FLAG = 1
AND DESIGNATOR_TYPE = 2
AND DESIGNATOR_ID = S.SCHEDULE_DESIGNATOR_ID)))
AND C.CALENDAR_CODE = TP.calendar_code
AND C.EXCEPTION_SET_ID = TP.calendar_exception_set_id
AND C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,
S.NEW_SCHEDULE_DATE))
AND TRUNC(NVL(S.LAST_UNIT_COMPLETION_DATE,
NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
AND DECODE(S.LAST_UNIT_COMPLETION_DATE,
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
AND C.NEXT_SEQ_NUM >= DECODE(S.ORDER_TYPE, 18, C.NEXT_SEQ_NUM,
DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM,
C2.next_seq_num - NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)))
AND C.NEXT_DATE >= DECODE(S.ORDER_TYPE, 27, TRUNC(SYSDATE),
28, TRUNC(SYSDATE),
C.NEXT_DATE) -- to_date removed to avoid GSCC error
AND MSI.plan_id (+) = -1
AND MSI.organization_id (+) = S.ORGANIZATION_ID
AND MSI.sr_instance_id (+) = S.sr_instance_id
AND MSI.sub_inventory_code (+) = S.subinventory_code
AND NVL(MSI.inventory_atp_code,1) <> 2
AND C2.CALENDAR_CODE = TP.calendar_code
AND C2.EXCEPTION_SET_ID = TP.calendar_exception_set_id
AND C2.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
AND C2.CALENDAR_DATE = TRUNC(l_sys_date)
AND TP.SR_INSTANCE_ID = p_instance_id
AND TP.PARTNER_TYPE = 3
) SD
GROUP BY sd.inventory_item_id, sd.organization_id, sd.demand_class,
sd.sd_date, -1, p_instance_id, l_sys_date, l_user_id);
--update the so_tbl_status to 1 so that user can do ATP
BEGIN
UPDATE msc_apps_instances
set so_tbl_status = 1,
summary_flag = 3
where instance_id = p_instance_id;
UPDATE msc_apps_instances
set so_tbl_status = 1,
summary_flag = 1
where instance_id = p_instance_id;
select newp.plan_id, NVL(newp.copy_plan_id, -1),
DECODE(newp.plan_type, 4, 2,
DECODE(daily_material_constraints, 1, 1,
DECODE(daily_resource_constraints, 1, 1,
DECODE(weekly_material_constraints, 1, 1,
DECODE(weekly_resource_constraints, 1, 1,
DECODE(period_material_constraints, 1, 1,
DECODE(period_resource_constraints, 1, 1, 2)
)
)
)
)
)
),
-- IO Perf:3693983: Don't Launch ATP Post Plan Processes for IO Plans
newp.plan_type
into l_new_plan_id, l_new_cp_plan_id, l_optimized_plan, l_plan_type
from msc_plans newp
where newp.plan_id = p_plan_id;
select organization_id, sr_instance_id
BULK COLLECT INTO l_organization_id, l_sr_instance_id
from msc_plan_organizations
WHERE plan_id=p_plan_id;
UPDATE msc_system_items mst1
SET (REPLENISH_TO_ORDER_FLAG,PICK_COMPONENTS_FLAG,ATP_RULE_ID,DEMAND_TIME_FENCE_DAYS) =(SELECT REPLENISH_TO_ORDER_FLAG,PICK_COMPONENTS_FLAG,ATP_RULE_ID,DEMAND_TIME_FENCE_DAYS
FROM msc_system_items mst2
WHERE mst2.sr_instance_id=mst1.sr_instance_id
AND mst2.organization_id=mst1.organization_id
AND mst2.INVENTORY_ITEM_ID=mst1.INVENTORY_ITEM_ID
AND mst2.plan_id=-1
)
WHERE plan_id=p_plan_id
AND mst1.ORGANIZATION_ID = l_organization_id(j)
AND mst1.SR_INSTANCE_ID = l_sr_instance_id(j)
--populate replenish to order flag for option items as well.
AND mst1.bom_item_type in (1,2,4,5)
--bug 3713374: Missing brackets was making OR condition to be stand alone filtering criteria
AND (mst1.atp_flag <> 'N' OR mst1.atp_components_flag <> 'N');
Delete MSC_DEMANDS
where origination_type = -100
and plan_id = p_plan_id
and ORGANIZATION_ID = l_organization_id(j)
and sr_instance_id = l_sr_instance_id(j);
msc_util.msc_log('LOAD_PLAN_SD: no of records deleted: '|| SQL%ROWCOUNT);
INSERT INTO MSC_DEMANDS(
DEMAND_ID,
USING_REQUIREMENT_QUANTITY,
RESERVED_QUANTITY,
USING_ASSEMBLY_DEMAND_DATE,
DEMAND_TYPE,
DEMAND_SOURCE_TYPE,
ORIGINATION_TYPE,
USING_ASSEMBLY_ITEM_ID,
PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SALES_ORDER_LINE_ID,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DEMAND_CLASS,
REFRESH_NUMBER,
ORDER_NUMBER,
APPLIED,
STATUS,
CUSTOMER_ID,
SHIP_TO_SITE_ID,
RECORD_SOURCE,
ATP_SYNCHRONIZATION_FLAG,
DMD_SATISFIED_DATE,
DISPOSITION_ID,
LINK_TO_LINE_ID,
wip_supply_type,
ORIGINAL_ITEM_ID )
(select
msc_demands_s.nextval,
RESERVED_QUANTITY,
0, --putting 0 in reserved qty
sysdate, --USING_ASSEMBLY_DEMAND_DATE,
DEMAND_TYPE,
DEMAND_SOURCE_TYPE,
-100, -- putting orgination_type as -100 so that planning UI will not pick it up.
USING_ASSEMBLY_ITEM_ID,
PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SALES_ORDER_LINE_ID,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DEMAND_CLASS,
REFRESH_NUMBER,
ORDER_NUMBER,
APPLIED,
STATUS,
CUSTOMER_ID,
SHIP_TO_SITE_ID,
RECORD_SOURCE,
ATP_SYNCHRONIZATION_FLAG,
DMD_SATISFIED_DATE,
DISPOSITION_ID,
LINK_TO_LINE_ID,
wip_supply_type,
ORIGINAL_ITEM_ID
from msc_demands
where plan_id = p_plan_id
and reserved_quantity <> 0
and organization_id = l_organization_id(j)
and sr_instance_id = l_sr_instance_id(j)
and origination_type in (30,6)
);
msc_util.msc_log('LOAD_PLAN_SD: no of records updated: '|| SQL%ROWCOUNT);
select plan_id,
DECODE(plan_type, 4, 2,
DECODE(daily_material_constraints, 1, 1,
DECODE(daily_resource_constraints, 1, 1,
DECODE(weekly_material_constraints, 1, 1,
DECODE(weekly_resource_constraints, 1, 1,
DECODE(period_material_constraints, 1, 1,
DECODE(period_resource_constraints, 1, 1, 2)
)
)
)
)
)
)
into l_old_plan_id, l_old_optimized_plan
from msc_plans
where copy_plan_id = p_plan_id;
select count(*)
into l_is_cmro
from msc_sales_orders so
where demand_source_type = 100
and organization_id=l_organization_id(j)
and sr_instance_id = l_sr_instance_id(j);
update msc_demands dem
set demand_source_type = (select distinct demand_source_type
from msc_sales_orders so
where so.sales_order_number = dem.order_number
and so.organization_id = dem.organization_id
and so.sr_instance_id = dem.sr_instance_id
and so.inventory_item_id = dem.inventory_item_id
)
where organization_id = l_organization_id(j)
and sr_instance_id = l_sr_instance_id(j)
and origination_type in (6,30)
and plan_id = l_plan_to_use;
SELECT NVL(SUMMARY_FLAG,1)
into l_summary_flag
from msc_apps_instances
where rownum = 1;
SELECT NVL(SUMMARY_FLAG,1), COMPILE_DESIGNATOR, trunc(plan_start_date)
into l_summary_flag, l_plan_name, l_plan_start_date
from msc_plans
where plan_id = l_plan_to_use;
SELECT a.oracle_username
INTO l_msc_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
SELECT count(*)
INTO l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM DBA_TAB_PARTITIONS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = l_table_name
AND PARTITION_NAME = l_partition_name
AND table_owner = l_msc_schema;
update msc_plans
set summary_flag = G_SF_FULL_SUMMARY_RUNNING -- for summary enhancement: ATP is up hereafter
where plan_id = l_plan_to_use;
MSC_POST_PRO.INSERT_SUPPLIER_DATA(l_plan_to_use,
share_partition,
l_applsys_schema,
1, -- Full summation
l_sysdate);
update msc_plans
set summary_flag = G_SF_SUMMARY_COMPLETED, -- For summary enhancement
latest_refresh_number = (SELECT apps_lrn
FROM MSC_PLAN_REFRESHES
WHERE plan_id = l_plan_to_use)
where plan_id = l_plan_to_use;
update msc_plans
set summary_flag = G_SF_PREALLOC_COMPLETED
where plan_id = l_plan_to_use;
update msc_plans
set summary_flag = G_SF_SUMMARY_NOT_RUN
where plan_id = l_plan_to_use;
msc_util.msc_debug('Update atp_synchronization_flag for 24x7 plan to support re-run after sync failure');
update msc_demands
set atp_synchronization_flag = 0 -- null
where (plan_id, sr_instance_id, organization_id) IN
(select mpo.plan_id, mpo.sr_instance_id, mpo.organization_id
from msc_plan_organizations mpo
where mpo.plan_id = l_old_plan)
and origination_type in (6,30);
update msc_plans
set summary_flag = 1
where plan_id = l_plan_to_use;
select summary_flag,
plan_completion_date,
latest_refresh_number,
trunc(plan_start_date),
-- ATP4drp obtain plan_type info.
plan_type
into l_summary_flag, l_plan_completion_date, l_last_refresh_number, l_plan_start_date, l_plan_type
-- End ATP4drp
from msc_plans
where plan_id = p_plan_id;
SELECT 1
INTO l_time_phased
FROM msc_system_items i ,msc_plan_organizations po
WHERE po.plan_id = p_plan_id
AND i.aggregate_time_fence_date IS NOT NULL
AND i.plan_id = po.plan_id
AND i.organization_id = po.organization_id
AND i.sr_instance_id = po.sr_instance_id
AND rownum = 1;
update msc_plans
set summary_flag = G_SF_NET_SUMMARY_RUNNING
where plan_id = p_plan_id;
SELECT max(refresh_number)
INTO l_new_refresh_number
FROM (SELECT refresh_number
FROM msc_demands
WHERE plan_id = p_plan_id
UNION ALL
SELECT refresh_number
FROM msc_supplies
WHERE plan_id = p_plan_id
UNION ALL
SELECT refresh_number
FROM msc_resource_requirements
WHERE plan_id = p_plan_id
);
update msc_plans
set summary_flag = G_SF_SUMMARY_COMPLETED,
latest_refresh_number = l_new_refresh_number
where plan_id = p_plan_id;
MSC_POST_PRO.INSERT_SUPPLIER_DATA(p_plan_id,
null, -- p_share_partition -> Not required for
null, -- p_applsys_schema -> incremental summation
2, -- Incremental summation
l_sysdate,
l_last_refresh_number,
l_new_refresh_number);
update msc_plans
set summary_flag = G_SF_SUMMARY_COMPLETED, -- For summary enhancement
latest_refresh_number = l_new_refresh_number
where plan_id = p_plan_id;
update msc_plans
set summary_flag = G_SF_SUMMARY_COMPLETED
where plan_id = p_plan_id;
PROCEDURE INSERT_SUPPLIER_DATA(p_plan_id IN NUMBER,
p_share_partition IN varchar2,
p_applsys_schema IN varchar2,
p_full_refresh IN NUMBER, -- 1:Yes, 2:No
p_sys_date IN DATE, -- For summary enhancement
p_last_refresh_number IN NUMBER, -- For summary enhancement
p_new_refresh_number IN NUMBER) -- For summary enhancement
AS
l_partition_name varchar2(30);
msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Share partition ');
msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Delete Data from msc_atp_summary_sup');
delete MSC_ATP_SUMMARY_SUP where plan_id = p_plan_id;
msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading complete summary in MSC_ATP_SUMMARY_SUP');
msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'After loading complete summary in MSC_ATP_SUMMARY_SUP');
msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'Loading net summary in MSC_ATP_SUMMARY_SUP');
msc_util.msc_log('INSERT_SUPPLIER_DATA: ' || 'After loading net summary in MSC_ATP_SUMMARY_SUP');
END INSERT_SUPPLIER_DATA;
SELECT count(*)
INTO l_count
FROM msc_supplier_flex_fences
WHERE plan_id = p_plan_id
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND supplier_id = p_supplier_id
AND supplier_site_id = p_supplier_site_id;
SELECT decode(designator_type, 2, 1, 0),
DECODE(plans.plan_type, 4, 2,
DECODE(daily_material_constraints, 1, 1,
DECODE(daily_resource_constraints, 1, 1,
DECODE(weekly_material_constraints, 1, 1,
DECODE(weekly_resource_constraints, 1, 1,
DECODE(period_material_constraints, 1, 1,
DECODE(period_resource_constraints, 1, 1, 2)
)
)
)
)
)
),
DECODE(l_MSO_Batch_Flag, 'Y', DECODE(plans.plan_type, 4, 0,2,0, -- filter out MPS plans
DECODE(daily_material_constraints, 1, 1,
DECODE(daily_resource_constraints, 1, 1,
DECODE(weekly_material_constraints, 1, 1,
DECODE(weekly_resource_constraints, 1, 1,
DECODE(period_material_constraints, 1, 1,
DECODE(period_resource_constraints, 1, 1, 0)
)
)
)
)
)
), 0)
INTO l_use_bor, l_optimized_plan, l_constraint_plan
FROM msc_designators desig,
msc_plans plans
WHERE plans.plan_id = p_plan_id
AND desig.designator = plans.compile_designator
AND desig.sr_instance_id = plans.sr_instance_id
AND desig.organization_id = plans.organization_id;
msc_util.msc_log('LOAD_RESOURCES: ' || 'Delete Data from MSC_ATP_SUMMARY_RES');
DELETE MSC_ATP_SUMMARY_RES where plan_id = p_plan_id;
msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
msc_util.msc_log('LOAD_RESOURCES: ' || 'Insert data into res table');
msc_util.msc_log('LOAD_RESOURCES: ' || 'After inserting into MSC_ATP_SUMMARY_RES');
SELECT D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') demand_class ,
---bug 2287148: move reservations to sysdate
DECODE(D.RESERVATION_TYPE,2,l_sys_next_date,trunc(D.REQUIREMENT_DATE)) SD_DATE,
SUM ( (D.PRIMARY_UOM_QUANTITY -
GREATEST(NVL(D.RESERVATION_QUANTITY,0),
D.COMPLETED_QUANTITY)) ) sd_qty
FROM
MSC_SALES_ORDERS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE D.SR_INSTANCE_ID = l_instance_id
AND I.REFRESH_NUMBER > l_refresh_number -- get all new flag items
AND I.ORGANIZATION_ID = l_organization_id
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_DEFAULT_ATP_RULE_ID)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
D.COMPLETED_QUANTITY)
AND I.NEW_ATP_FLAG = 'Y' -- New flag to indicate new ATPable item.
AND I.plan_id = -1
AND ((D.PARENT_DEMAND_ID IS NOT NULL) OR -- new sales order and
-- equivalently D.reservation_type = 1
-- the demand for which the sales_order has been pegged
(D.RESERVATION_TYPE = 2 AND D.DEMAND_SOURCE_LINE IS NOT NULL))
AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
(SELECT S.SUB_INVENTORY_CODE
FROM MSC_SUB_INVENTORIES S
WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2, 1, S.NETTING_TYPE)))
AND (D.RESERVATION_TYPE = 2
OR D.PARENT_DEMAND_ID IS NULL
OR (D.RESERVATION_TYPE = 3 AND
((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
(R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
AND EXISTS
(SELECT 1
FROM msc_calendar_dates c
WHERE C.PRIOR_SEQ_NUM >=
DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM
(D.ORGANIZATION_ID, P_INSTANCE_ID, l_sysdate)
- NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.CALENDAR_CODE = l_CALENDAR_CODE
AND C.SR_INSTANCE_ID = p_instance_id
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
)
GROUP BY D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') ,
-- rajjain 02/06/2003 Bug 2782882
DECODE(D.RESERVATION_TYPE,2,l_sys_next_date,trunc(D.REQUIREMENT_DATE));
SELECT
D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') demand_class ,
trunc(D.REQUIREMENT_DATE) SD_DATE,
sum(d.old_RESERVATION_QUANTITY) sd_qty
FROM
MSC_SALES_ORDERS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE D.SR_INSTANCE_ID = l_instance_id
AND D.REFRESH_NUMBER > l_refresh_number
AND I.ORGANIZATION_ID = l_organization_id
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND I.plan_id = -1
AND I.ATP_FLAG = 'Y' -- Get ATP'able items which have
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_DEFAULT_ATP_RULE_ID)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
AND (D.RESERVATION_TYPE=1 and reservation_quantity=0 and old_reservation_quantity <>0)
AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
NVL(D.COMPLETED_QUANTITY,0))
AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
(SELECT S.SUB_INVENTORY_CODE
FROM MSC_SUB_INVENTORIES S
WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2, 1, S.NETTING_TYPE)))
AND EXISTS
(SELECT 1
FROM msc_calendar_dates c
WHERE C.PRIOR_SEQ_NUM >=
DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM(D.ORGANIZATION_ID,
P_INSTANCE_ID,
l_sysdate) - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.CALENDAR_CODE = l_CALENDAR_CODE
AND C.SR_INSTANCE_ID = p_instance_id
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
)
GROUP BY D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') ,
trunc(D.REQUIREMENT_DATE);
SELECT
D.organization_id,
D.inventory_item_id,
NVL(D.DEMAND_CLASS,'@@@') demand_class ,
trunc(d.requirement_date) SD_DATE,
sum(nvl(d.old_primary_uom_quantity, 0) - d.primary_uom_quantity) SD_QTY
-- QUESTION ? Does the above SUM actually result in a DELTA??
FROM
msc_sales_orders d
WHERE
d.reservation_type =2
and d.refresh_number > l_refresh_number
and d.organization_id = l_organization_id
and d.sr_instance_id =l_instance_id
GROUP BY
D.organization_id,
D.inventory_item_id,
NVL(D.DEMAND_CLASS,'@@@'),
trunc(d.requirement_date);
SELECT D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') demand_class ,
---bug 2287148: move reservations to sysdate
DECODE(D.RESERVATION_TYPE,2,l_sys_next_date, trunc(D.REQUIREMENT_DATE)) SD_DATE,
sum(d.primary_uom_quantity -nvl(d.old_primary_uom_quantity, 0)) sd_qty
--SUM ( (D.PRIMARY_UOM_QUANTITY -
--GREATEST(NVL(D.RESERVATION_QUANTITY,0),
--NVL(D.COMPLETED_QUANTITY,0))) ) sd_qty
-- QUESTION ? Does the above SUM actually result in a DELTA??
FROM
MSC_SALES_ORDERS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE D.SR_INSTANCE_ID = l_instance_id
AND D.REFRESH_NUMBER > l_refresh_number
AND I.ORGANIZATION_ID = l_organization_id
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND I.plan_id = -1
AND I.ATP_FLAG = 'Y' -- Get ATP'able items which have
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_DEFAULT_ATP_RULE_ID)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
AND D.PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.RESERVATION_QUANTITY,0),
NVL(D.COMPLETED_QUANTITY,0))
--AND D.DEMAND_SOURCE_LINE is NULL -- new inventory reservations.
AND D.RESERVATION_TYPE <> 1 -- Not a Sales Order item.
AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
(SELECT S.SUB_INVENTORY_CODE
FROM MSC_SUB_INVENTORIES S
WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2, 1, S.NETTING_TYPE)))
AND (D.RESERVATION_TYPE = 2
OR D.PARENT_DEMAND_ID IS NULL
OR (D.RESERVATION_TYPE = 3 AND
((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
(R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
AND EXISTS
(SELECT 1
FROM msc_calendar_dates c
WHERE C.PRIOR_SEQ_NUM >=
DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM(D.ORGANIZATION_ID,
P_INSTANCE_ID,
l_sysdate) - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.CALENDAR_CODE = l_CALENDAR_CODE
AND C.SR_INSTANCE_ID = p_instance_id
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
)
GROUP BY D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') ,
-- rajjain 02/06/2003 Bug 2782882
DECODE(D.RESERVATION_TYPE,2,l_sys_next_date, trunc(D.REQUIREMENT_DATE))
UNION ALL
SELECT D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') demand_class ,
---bug 2287148: move reservations to sysdate
-- bug 5357370: since reservation_type 1 is only selected when it is shipped, we need to either use sysdate if we had reservation or use oldrequirement date if we did not have reservation
decode(NVL(D.old_reservation_quantity, 0), 0, trunc(nvl(D.old_requirement_date, D.requirement_date)), l_sys_next_date) SD_DATE,
-- DECODE(D.RESERVATION_TYPE,2,l_sys_next_date,trunc(D.old_REQUIREMENT_DATE)) SD_DATE,
--5125969 In cases of reservation type 1 and some complete qty
--we want to substract that from total qty
SUM(DECODE(D.RESERVATION_TYPE,1, -1*D.COMPLETED_QUANTITY,
-1*(NVL(D.old_PRIMARY_UOM_QUANTITY,0) -
GREATEST(NVL(D.old_RESERVATION_QUANTITY,0),
NVL(D.old_COMPLETED_QUANTITY,0))))) sd_qty
-- QUESTION ? Does the above SUM actually result in a DELTA??
-- ANSWER : We are subtratcting the sum of the old quantities.
FROM
MSC_SALES_ORDERS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE D.SR_INSTANCE_ID = l_instance_id
AND D.REFRESH_NUMBER > l_refresh_number
AND I.ORGANIZATION_ID = l_organization_id
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND I.plan_id = -1
AND I.ATP_FLAG = 'Y' -- Get ATP'able items which have
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_DEFAULT_ATP_RULE_ID)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_SALES_ORDERS,2,2,-1)
AND D.DEMAND_SOURCE_TYPE <> DECODE(R.INCLUDE_INTERNAL_ORDERS,2,8,-1)
--5125969 Also consider rows where completed_qty is not 0
AND (D.OLD_PRIMARY_UOM_QUANTITY > GREATEST(NVL(D.OLD_RESERVATION_QUANTITY,0),
NVL(D.OLD_COMPLETED_QUANTITY,0))
OR ((NVL(D.COMPLETED_QUANTITY,0) <> 0)AND D.OLD_PRIMARY_UOM_QUANTITY-NVL(D.OLD_COMPLETED_QUANTITY,0)>0))
--5125969 Include reservation type 1 when complete qty is not 0
AND ((D.DEMAND_SOURCE_LINE is NULL -- new inventory reservations.
AND
D.RESERVATION_TYPE <> 1) -- Not a Sales Order item.
OR (D.RESERVATION_TYPE = 1 AND NVL(D.COMPLETED_QUANTITY,0) <> 0))
AND (D.SUBINVENTORY IS NULL OR D.SUBINVENTORY IN
(SELECT S.SUB_INVENTORY_CODE
FROM MSC_SUB_INVENTORIES S
WHERE S.ORGANIZATION_ID=D.ORGANIZATION_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND S.INVENTORY_ATP_CODE =DECODE(R.DEFAULT_ATP_SOURCES,
1, 1, NULL, 1, S.INVENTORY_ATP_CODE)
AND S.NETTING_TYPE =DECODE(R.DEFAULT_ATP_SOURCES,
2, 1, S.NETTING_TYPE)))
AND (D.RESERVATION_TYPE = 2
OR D.PARENT_DEMAND_ID IS NULL
OR (D.RESERVATION_TYPE = 3 AND
((R.INCLUDE_DISCRETE_WIP_RECEIPTS = 1) or
(R.INCLUDE_NONSTD_WIP_RECEIPTS = 1))))
AND EXISTS
(SELECT 1
FROM msc_calendar_dates c
WHERE C.PRIOR_SEQ_NUM >=
DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
NULL, C.PRIOR_SEQ_NUM,
MSC_ATP_FUNC.NEXT_WORK_DAY_SEQNUM(D.ORGANIZATION_ID,
P_INSTANCE_ID,
l_sysdate) - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.CALENDAR_CODE = l_CALENDAR_CODE
AND C.SR_INSTANCE_ID = p_instance_id
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.CALENDAR_DATE = TRUNC(D.old_REQUIREMENT_DATE)
)
GROUP BY D.organization_id,
D.inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(D.DEMAND_CLASS,'@@@'), '@@@') ,
-- rajjain 02/06/2003 Bug 2782882
decode(NVL(D.old_reservation_quantity, 0), 0, trunc(nvl(D.old_requirement_date, D.requirement_date)), l_sys_next_date);
SELECT NVL(summary_flag, 1), NVL(summary_refresh_number,0),apps_lrn ---LCID
INTO l_summary_flag,l_refresh_number,l_apps_lrn ---bug3049003
from msc_apps_instances
where instance_id = p_instance_id;
/* SELECT sr_tp_id
BULK COLLECT INTO l_org_ids
FROM msc_trading_partners
WHERE sr_instance_id = p_instance_id and partner_type = 3;*/
SELECT ORGANIZATION_ID ---bug3049003
BULK COLLECT INTO l_org_ids
FROM msc_instance_orgs
WHERE sr_instance_id = p_instance_id
and org_lrn=l_apps_lrn
and enabled_flag=1;
SELECT cal.next_date
INTO l_sys_next_date
FROM msc_calendar_dates cal
WHERE cal.exception_set_id = l_calendar_exception_set_id
AND cal.calendar_code = l_calendar_code
AND cal.calendar_date = TRUNC(l_sysdate)
AND cal.sr_instance_id = p_instance_id ;
INSERT INTO MSC_ATP_SUMMARY_SO
(plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (-1, p_instance_id, l_organization_id,
l_inventory_item_id, l_demand_class, trunc(l_sd_date),
l_sd_qty, l_sysdate, l_user_id ,
l_sysdate, l_user_id
);
UPDATE MSC_ATP_SUMMARY_SO
SET sd_qty = sd_qty + l_sd_qty, -- The value is now a DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
UPDATE MSC_ATP_SUMMARY_SO
SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
INSERT INTO MSC_ATP_SUMMARY_SO
(plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES ( -1,
p_instance_id,
l_organization_id,
l_inventory_item_id,
l_demand_class,
trunc(l_sd_date),
l_sd_qty,
l_sysdate,
l_user_id ,
l_sysdate,
l_user_id
);
update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
set sd_qty = (sd_qty + l_sd_qty),
last_update_date = l_sysdate,
last_updated_by = l_user_id
where inventory_item_id = l_inventory_item_id
and sr_instance_id = p_instance_id
and organization_id = l_organization_id
and sd_date = trunc(l_sd_date)
and demand_class = l_demand_class ;
UPDATE MSC_ATP_SUMMARY_SO
SET sd_qty = sd_qty - l_sd_qty, -- APPLY THE DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sys_next_date);
INSERT INTO MSC_ATP_SUMMARY_SO
(plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES ( -1,
p_instance_id,
l_organization_id,
l_inventory_item_id,
l_demand_class,
trunc(l_sys_next_date),
- l_sd_qty,
l_sysdate,
l_user_id ,
l_sysdate,
l_user_id
);
update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
set sd_qty = (sd_qty - l_sd_qty),
last_update_date = l_sysdate,
last_updated_by = l_user_id
where inventory_item_id = l_inventory_item_id
and sr_instance_id = p_instance_id
and organization_id = l_organization_id
and sd_date = trunc(l_sys_next_date)
and demand_class = l_demand_class ;
UPDATE MSC_ATP_SUMMARY_SO
SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
INSERT INTO MSC_ATP_SUMMARY_SO
(plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES ( -1,
p_instance_id,
l_organization_id,
l_inventory_item_id,
l_demand_class,
trunc(l_sd_date),
l_sd_qty,
l_sysdate,
l_user_id ,
l_sysdate,
l_user_id
);
update /*+ INDEX(msc_atp_summary_so MSC_ATP_SUMMARY_SO_U1) */ msc_atp_summary_so
set sd_qty = (sd_qty + l_sd_qty),
last_update_date = l_sysdate,
last_updated_by = l_user_id
where inventory_item_id = l_inventory_item_id
and sr_instance_id = p_instance_id
and organization_id = l_organization_id
and sd_date = trunc(l_sd_date)
and demand_class = l_demand_class ;
UPDATE MSC_ATP_SUMMARY_SO
SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
INSERT INTO MSC_ATP_SUMMARY_SO
(plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (-1, p_instance_id, l_organization_id,
l_inventory_item_id, l_demand_class, trunc(l_sd_date),
l_sd_qty, l_sysdate, l_user_id ,
l_sysdate, l_user_id
);
UPDATE MSC_ATP_SUMMARY_SO
SET sd_qty = sd_qty + l_sd_qty, -- The value is a DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
update msc_apps_instances
set summary_flag = 1
where instance_id = p_instance_id;
SELECT inventory_item_id, demand_class, SD_DATE, sum(sd_qty) SD_QTY
FROM
((SELECT
--- bug 2162571: Use Pf's id if doing PF based ATP
DECODE(I2.ATP_FLAG, 'Y', I2.INVENTORY_ITEM_ID,
I.INVENTORY_ITEM_ID) inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 , NVL(D.DEMAND_CLASS,
NVL(l_default_demand_class,'@@@')), '@@@') demand_class,
C.PRIOR_DATE SD_DATE,
-1* D.USING_REQUIREMENT_QUANTITY SD_QTY
--2 SD_TYPE
FROM MSC_CALENDAR_DATES C,
MSC_DEMANDS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2
WHERE I.ATP_FLAG = 'Y' --- I.SR_INVENTORY_ITEM_ID = p_inventory_item_id
AND I.ORGANIZATION_ID = l_organization_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = -1
--- bug 2162571
AND I.PLAN_ID = I2.PLAN_ID
AND I.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
AND I2.INVENTORY_ITEM_ID = NVL(I.PRODUCT_FAMILY_ID, I.INVENTORY_ITEM_ID)
AND D.REFRESH_NUMBER > l_refresh_number
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_default_atp_rule_id)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
-- 1243985
AND USING_REQUIREMENT_QUANTITY <> 0
AND D.ORIGINATION_TYPE in (
DECODE(R.INCLUDE_DISCRETE_WIP_DEMAND, 1, 3, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND, 1, 25, -1),
DECODE(R.INCLUDE_USER_DEFINED_DEMAND, 1, 42, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 2, -1),
DECODE(R.INCLUDE_REP_WIP_DEMAND, 1, 4, -1))
-- Bug 1530311, forecast to be excluded
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = p_instance_id
-- since we store repetitive schedule demand in different ways for
-- ods (total quantity on start date) and pds (daily quantity from
-- start date to end date), we need to make sure we only
-- select work day for pds's repetitive schedule demand.
AND C.CALENDAR_DATE BETWEEN TRUNC(D.USING_ASSEMBLY_DEMAND_DATE) AND
TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
D.USING_ASSEMBLY_DEMAND_DATE))
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE ,
NULL, C.PRIOR_SEQ_NUM,
l_sysdate_seq_num - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
)
UNION ALL -- with old demand information
(SELECT
--- bug 2162571
--I.inventory_item_id,
DECODE(I2.ATP_FLAG, 'Y', I2.INVENTORY_ITEM_ID,
I.INVENTORY_ITEM_ID) inventory_item_id,
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 , NVL(D.DEMAND_CLASS,
NVL(l_default_demand_class,'@@@')), '@@@') demand_class,
C.PRIOR_DATE SD_DATE,
NVL(D.OLD_USING_REQUIREMENT_QUANTITY,0) SD_QTY
--2 SD_TYPE
FROM MSC_CALENDAR_DATES C,
MSC_DEMANDS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2
WHERE I.ATP_FLAG = 'Y' --- I.SR_INVENTORY_ITEM_ID = p_inventory_item_id
AND I.ORGANIZATION_ID = l_organization_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = -1
--- bug 2162571
AND I.PLAN_ID = I2.PLAN_ID
AND I.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
AND I2.INVENTORY_ITEM_ID = NVL(I.PRODUCT_FAMILY_ID, I.INVENTORY_ITEM_ID)
AND D.REFRESH_NUMBER > l_refresh_number
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_default_atp_rule_id)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
-- 1243985
AND NVL(D.OLD_USING_REQUIREMENT_QUANTITY,0) <> 0
AND D.ORIGINATION_TYPE in (
DECODE(R.INCLUDE_DISCRETE_WIP_DEMAND, 1, 3, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_DEMAND, 1, 25, -1),
DECODE(R.INCLUDE_USER_DEFINED_DEMAND, 1, 42, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 2, -1),
DECODE(R.INCLUDE_REP_WIP_DEMAND, 1, 4, -1))
-- Bug 1530311, forecast to be excluded
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = p_instance_id
-- since we store repetitive schedule demand in different ways for
-- ods (total quantity on start date) and pds (daily quantity from
-- start date to end date), we need to make sure we only
-- select work day for pds's repetitive schedule demand.
AND C.CALENDAR_DATE BETWEEN TRUNC(D.OLD_USING_ASSEMBLY_DEMAND_DATE) AND
TRUNC(NVL(D.OLD_ASSEMBLY_DEMAND_COMP_DATE,
D.OLD_USING_ASSEMBLY_DEMAND_DATE))
AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE ,
NULL, C.PRIOR_SEQ_NUM,
l_sysdate_seq_num - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
)
UNION ALL -- new supplies information
(SELECT /*+ ordered index(C,MSC_CALENDAR_DATES_U1) */I.inventory_item_id, -- 5098576/5199686
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(DECODE(S.ORDER_TYPE,
5, MSC_ATP_FUNC.Get_MPS_Demand_Class(S.SCHEDULE_DESIGNATOR_ID),
S.DEMAND_CLASS), NVL(l_default_demand_class, '@@@')), '@@@'),
C.NEXT_DATE SD_DATE,
--- bug 1843471, 2619493
Decode(order_type, -- 2859130 remove trunc
30, Decode(Sign(S.Daily_rate * (C.Calendar_date -
TRUNC(S.FIRST_UNIT_START_DATE))- S.qty_completed),
-1,S.Daily_rate* (C.Calendar_date - TRUNC(S.First_Unit_Start_date) +1) -
S.qty_completed, S.Daily_rate),
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) ) SD_QTY
-- Changed the order of the tables for 5098576/5199686
FROM MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2,
MSC_SUPPLIES S,
MSC_ATP_RULES R,
MSC_SUB_INVENTORIES MSI,
MSC_CALENDAR_DATES C
/* FROM MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
--- bug 2162571 add to another table to get info about product family
MSC_SYSTEM_ITEMS I2,
MSC_SUB_INVENTORIES MSI*/ -- commented for 5098576/5199686
WHERE I.ATP_FLAG = 'Y' ---I.SR_INVENTORY_ITEM_ID = p_inventory_item_id
AND I.ORGANIZATION_ID = l_organization_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = -1
AND I.PLAN_ID = I2.PLAN_ID
--- bug 2162571: add system items tables to itself to filter out supplies of
-- product family members if we are doing PF based atp on the member
-- the logic is:1. If it is a regular member then we consider supplies ond demand of that itme
-- 2. If we do product family with config PF --> A then
-- a. If atp flag on PF is yes then we consider supplies of PF and demand of A
-- b. If atp flag on PF in 'N' then we consider supplies and demands of A
AND I.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
AND NVL(I.PRODUCT_FAMILY_ID, I.INVENTORY_ITEM_ID) = I2.INVENTORY_ITEM_ID
--- in case of PF, if atp_flag on PF is yes then we want to filter out supplies of A
-- For A, the following condition will be true only if ATP_FLAG on PF is 'N'
--- and therefore we will consider supplies of A. If atp_flag on PF is 'Y"
-- then following condition will be false and we will omit supplies of A
AND DECODE(I.PRODUCT_FAMILY_ID, NULL, 'N', I2.ATP_FLAG ) = 'N'
AND S.REFRESH_NUMBER > l_refresh_number
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_default_atp_rule_id)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
---bug 1843471, 2619493
AND Decode(S.order_type, 30, S.Daily_rate* (C.Calendar_date -- 2859130 remove trunc
- TRUNC(S.First_Unit_Start_date) + 1),
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) >
Decode(S.order_type, 30, S.qty_completed,0)
AND (S.ORDER_TYPE IN (
DECODE(R.INCLUDE_PURCHASE_ORDERS, 1, 1, -1),
DECODE(R.INCLUDE_PURCHASE_ORDERS, 1, 8, -1), --1882898
DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 3, -1),
DECODE(R.INCLUDE_REP_WIP_RECEIPTS, 1, 30, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 7, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 15, -1) ,
DECODE(R.INCLUDE_INTERORG_TRANSFERS, 1, 11, -1),
DECODE(R.INCLUDE_INTERORG_TRANSFERS, 1, 12, -1),
DECODE(R.INCLUDE_ONHAND_AVAILABLE, 1, 18, -1),
DECODE(R.INCLUDE_INTERNAL_REQS, 1, 2, -1),
DECODE(R.INCLUDE_SUPPLIER_REQS, 1, 2, -1),
DECODE(R.INCLUDE_USER_DEFINED_SUPPLY, 1, 41, -1) ,
DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, 27, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, 28, -1))
OR ((R.INCLUDE_REP_MPS = 1 OR R.INCLUDE_DISCRETE_MPS = 1) AND
S.ORDER_TYPE = 5
AND exists (SELECT '1'
FROM MSC_DESIGNATORS
WHERE INVENTORY_ATP_FLAG = 1
AND DESIGNATOR_TYPE = 2
AND DESIGNATOR_ID = S.SCHEDULE_DESIGNATOR_ID)))
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = p_instance_id
AND C.CALENDAR_DATE BETWEEN
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
AND TRUNC(NVL(S.LAST_UNIT_COMPLETION_DATE,
NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
AND DECODE(S.LAST_UNIT_COMPLETION_DATE,
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
AND C.NEXT_SEQ_NUM >= DECODE(S.ORDER_TYPE, 18, C.NEXT_SEQ_NUM,
DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM, l_sysdate_seq_num -
NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)))
AND C.NEXT_DATE >= DECODE(S.ORDER_TYPE, 27, TRUNC(l_sysdate),
28, TRUNC(l_sysdate),
C.NEXT_DATE)
--- filter out non-atpable sub-inventories
AND MSI.plan_id (+) = -1
AND MSI.organization_id (+) = l_organization_id
AND MSI.sr_instance_id (+) = p_instance_id
AND MSI.sub_inventory_code (+) = S.subinventory_code
AND NVL(MSI.inventory_atp_code,1) <> 2
-- filter out non-atpable subinventories
)
UNION ALL -- with old supplies information
(SELECT /*+ ordered index(C,MSC_CALENDAR_DATES_U1) */I.inventory_item_id, -- 5098576/5199686
Decode(NVL(R.DEMAND_CLASS_ATP_FLAG,0),1 ,
NVL(DECODE(S.ORDER_TYPE,
5, MSC_ATP_FUNC.Get_MPS_Demand_Class(S.SCHEDULE_DESIGNATOR_ID),
S.DEMAND_CLASS), NVL(l_default_demand_class, '@@@')), '@@@'),
C.NEXT_DATE SD_DATE,
--- bug 1843471, 2619493
-- 2859130 remove trunc on calendar_date
-1 * Decode(order_type,
30, Decode(Sign(NVL(S.OLD_Daily_rate,0) * (C.Calendar_date -
TRUNC(S.OLD_FIRST_UNIT_START_DATE))- NVL(S.OLD_qty_completed,0)),
-1,NVL(S.OLD_Daily_rate,0)* (C.Calendar_date -
TRUNC(S.OLD_First_Unit_Start_date) +1) -
NVL(S.OLD_qty_completed,0), NVL(S.OLD_Daily_rate,0)),
NVL( NVL(S.OLD_FIRM_QUANTITY,S.OLD_NEW_ORDER_QUANTITY),0) ) SD_QTY
/*FROM MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2,
MSC_SUB_INVENTORIES MSI*/
-- Commented 5098576./5199686
-- changed order of tables for 5098576/5199686
FROM MSC_SYSTEM_ITEMS I,
MSC_SYSTEM_ITEMS I2,
MSC_SUPPLIES S,
MSC_ATP_RULES R,
MSC_SUB_INVENTORIES MSI,
MSC_CALENDAR_DATES C
WHERE I.ATP_FLAG = 'Y'
AND I.ORGANIZATION_ID = l_organization_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = -1
AND I.PLAN_ID = I2.PLAN_ID
--- bug 2162571: add system items tables to itself to filter out supplies of
-- product family members if we are doing PF based atp on the member
-- the logic is:1. If it is a regular member then we consider supplies ond demand of that itme
-- 2. If we do product family with config PF --> A then
-- a. If atp flag on PF is yes then we consider supplies of PF and demand of A
-- b. If atp flag on PF in 'N' then we consider supplies and demands of A
AND I.ORGANIZATION_ID = I2.ORGANIZATION_ID
AND I.SR_INSTANCE_ID = I2.SR_INSTANCE_ID
AND NVL(I.PRODUCT_FAMILY_ID, I.INVENTORY_ITEM_ID) = I2.INVENTORY_ITEM_ID
--- in case of PF, if atp_flag on PF is yes then we want to filter out supplies of A
-- For A, the following condition will be true only if ATP_FLAG on PF is 'N'
--- and therefore we will consider supplies of A. If atp_flag on PF is 'Y"
-- then following condition will be false and we will omit supplies of A
AND DECODE(I.PRODUCT_FAMILY_ID, NULL, 'N', I2.ATP_FLAG ) = 'N'
AND S.REFRESH_NUMBER > l_refresh_number
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, l_default_atp_rule_id)
AND R.SR_INSTANCE_ID (+)= I.SR_INSTANCE_ID
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
---bug 1843471, 2619493
-- 2859130 remove trunc
AND Decode(S.order_type, 30, NVL(S.OLD_Daily_rate,0)* (C.Calendar_date
- TRUNC(S.OLD_First_Unit_Start_date) + 1),
NVL(NVL(S.OLD_FIRM_QUANTITY,S.OLD_NEW_ORDER_QUANTITY),0) ) >
Decode(S.order_type, 30, NVL(S.OLD_qty_completed,0),0)
AND (S.ORDER_TYPE IN (
DECODE(R.INCLUDE_PURCHASE_ORDERS, 1, 1, -1),
DECODE(R.INCLUDE_PURCHASE_ORDERS, 1, 8, -1), --1882898
DECODE(R.INCLUDE_DISCRETE_WIP_RECEIPTS, 1, 3, -1),
DECODE(R.INCLUDE_REP_WIP_RECEIPTS, 1, 30, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 7, -1),
DECODE(R.INCLUDE_NONSTD_WIP_RECEIPTS, 1, 15, -1) ,
DECODE(R.INCLUDE_INTERORG_TRANSFERS, 1, 11, -1),
DECODE(R.INCLUDE_INTERORG_TRANSFERS, 1, 12, -1),
DECODE(R.INCLUDE_ONHAND_AVAILABLE, 1, 18, -1),
DECODE(R.INCLUDE_INTERNAL_REQS, 1, 2, -1),
DECODE(R.INCLUDE_SUPPLIER_REQS, 1, 2, -1),
DECODE(R.INCLUDE_USER_DEFINED_SUPPLY, 1, 41, -1) ,
DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, 27, -1),
DECODE(R.INCLUDE_FLOW_SCHEDULE_RECEIPTS, 1, 28, -1))
OR ((R.INCLUDE_REP_MPS = 1 OR R.INCLUDE_DISCRETE_MPS = 1) AND
S.ORDER_TYPE = 5
AND exists (SELECT '1'
FROM MSC_DESIGNATORS
WHERE INVENTORY_ATP_FLAG = 1
AND DESIGNATOR_TYPE = 2
AND DESIGNATOR_ID = S.SCHEDULE_DESIGNATOR_ID)))
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = p_instance_id
AND C.CALENDAR_DATE BETWEEN
TRUNC(NVL(S.OLD_FIRM_DATE,S.OLD_NEW_SCHEDULE_DATE))
AND TRUNC(NVL(S.OLD_LAST_UNIT_COMPLETION_DATE,
NVL(S.OLD_FIRM_DATE,S.OLD_NEW_SCHEDULE_DATE)))
AND DECODE(S.OLD_LAST_UNIT_COMPLETION_DATE,
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
AND C.NEXT_SEQ_NUM >= DECODE(S.ORDER_TYPE, 18, C.NEXT_SEQ_NUM,
DECODE(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,
NULL, C.NEXT_SEQ_NUM, l_sysdate_seq_num -
NVL(R.PAST_DUE_SUPPLY_CUTOFF_FENCE,0)))
AND C.NEXT_DATE >= DECODE(S.ORDER_TYPE, 27, TRUNC(l_sysdate),
28, TRUNC(l_sysdate),
C.NEXT_DATE)
--- filter out non-atpable sub-inventories
AND MSI.plan_id (+) = -1
AND MSI.organization_id (+) = l_organization_id
AND MSI.sr_instance_id (+) = p_instance_id
AND MSI.sub_inventory_code (+) = S.subinventory_code
AND NVL(MSI.inventory_atp_code,1) <> 2
-- filter out non-atpable subinventories
)
)
GROUP BY inventory_item_id, demand_class, sd_date ;
/* SELECT NVL(summary_flag, 1), NVL(summary_refresh_number,0) ---LCID
INTO l_summary_flag, l_refresh_number
from msc_apps_instances
where instance_id = p_instance_id;*/
SELECT NVL(summary_flag, 1), NVL(summary_refresh_number,0),apps_lrn ---LCID
INTO l_summary_flag,l_refresh_number,l_apps_lrn ---bug3049003
from msc_apps_instances
where instance_id = p_instance_id;
/*SELECT sr_tp_id
BULK COLLECT INTO l_org_ids
FROM msc_trading_partners
WHERE sr_instance_id = p_instance_id and partner_type = 3;*/
SELECT ORGANIZATION_ID ---bug3049003
BULK COLLECT INTO l_org_ids
FROM msc_instance_orgs
WHERE sr_instance_id = p_instance_id
and org_lrn=l_apps_lrn
and enabled_flag=1;
SELECT cal.next_seq_num
INTO l_sysdate_seq_num
FROM msc_calendar_dates cal
WHERE cal.exception_set_id = l_calendar_exception_set_id
AND cal.calendar_code = l_calendar_code
AND cal.calendar_date = TRUNC(l_sysdate)
AND cal.sr_instance_id = p_instance_id ;
UPDATE MSC_ATP_SUMMARY_SD
SET sd_qty = sd_qty + l_sd_qty, -- APPLY THE DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
INSERT INTO MSC_ATP_SUMMARY_SD
(plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (-1, p_instance_id, l_organization_id,
l_inventory_item_id, l_demand_class, trunc(l_sd_date),
l_sd_qty, l_sysdate, l_user_id ,
l_sysdate, l_user_id
);
UPDATE MSC_ATP_SUMMARY_SD
SET sd_qty = sd_qty + l_sd_qty, -- The value is a DELTA
last_update_date = l_sysdate,
last_updated_by = l_user_id
WHERE plan_id = -1
AND sr_instance_id = p_instance_id
AND organization_id = l_organization_id
AND inventory_item_id = l_inventory_item_id
AND demand_class = l_demand_class
AND trunc(sd_date) = trunc(l_sd_date);
update msc_apps_instances
set summary_flag = 1
where instance_id = p_instance_id;
SELECT plan_id
FROM msc_plans
WHERE plan_id > p_plan_id
AND NVL(SUMMARY_FLAG, 0) <> 0
ORDER BY plan_id;
SELECT partition_name
INTO l_name
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
FROM all_tab_partitions
WHERE table_name = l_table_name
AND table_owner = p_owner
AND partition_name = l_partition_name;
SELECT NVL(summary_flag, 0)
INTO l_summary_flag
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT instance_id
FROM msc_apps_instances
WHERE instance_id > p_instance_id
AND NVL(summary_flag, 0) <> 0
ORDER BY instance_id;
SELECT NVL(summary_flag, 0)
INTO l_summary_flag
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
SELECT a.oracle_username
INTO l_msc_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
SELECT INSTANCE_ID
BULK COLLECT INTO INSTANCE_IDS
---bug 2389523: use msc_ins_partitions instead of
-- msc_apps_instances to look for existing instance partitions
--FROM MSC_APPS_INSTANCES
FROM MSC_INST_PARTITIONS
ORDER BY INSTANCE_ID;
SELECT count(*)
INTO l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM DBA_TAB_PARTITIONS
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME = l_table_name
AND PARTITION_NAME = l_partition_name
AND table_owner = l_msc_schema;
select count(*)
into l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--from dba_tab_partitions
from ALL_tab_partitions
where table_name = l_table_name
and table_owner = l_msc_schema
and partition_name = l_partition_name;
select count(*)
into l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--from dba_tab_partitions
from all_tab_partitions
where table_name = l_table_name
and table_owner = l_msc_schema
and partition_name = l_partition_name;
select plan_id
bulk collect into plan_ids
--bug 2389523: use msc_plan_partitions instead of msc_plans
--from msc_plans
from msc_plan_partitions
order by plan_id;
select count(*)
into l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--from dba_tab_partitions
from all_tab_partitions
where table_name = l_table_name
and table_owner = l_msc_schema
and partition_name = l_partition_name;
UPDATE msc_apps_instances
SET summary_refresh_number = LCID
WHERE instance_id = p_instance_id;
l_insert_stmt VARCHAR2(8000); -- ssurendr: increased the string length
SELECT NVL(summary_flag,1), compile_designator
INTO l_summary_flag, l_plan_name
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT a.oracle_username,
sysdate,
FND_GLOBAL.USER_ID
INTO l_msc_schema,
l_sysdate,
l_user_id
FROM fnd_oracle_userid a,
fnd_product_installations b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
SELECT count(*)
INTO l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM dba_tab_partitions
FROM all_tab_partitions
WHERE table_name = l_table_name
AND partition_name = l_partition_name
AND table_owner = l_msc_schema;
update msc_plans
set summary_flag = 2
where plan_id = p_plan_id;
SELECT NVL(pre_alloc_hash_size, -1),
NVL(pre_alloc_sort_size, -1),
NVL(pre_alloc_parallel_degree, 1)
INTO l_hash_size,
l_sort_size,
l_parallel_degree
FROM msc_atp_parameters
WHERE rownum = 1;
msc_util.msc_log('before deleteing data from the table');
DELETE MSC_ALLOC_DEMANDS where plan_id = p_plan_id;
DELETE MSC_ALLOC_SUPPLIES where plan_id = p_plan_id;
l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_DEMANDS(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
demand_source_type, --cmro
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity, -- ssurendr 25-NOV-2002: added for alloc w/b
request_date,--bug3263368
--bug3684383 added as in Insert_SD_Into_Details_Temp we need these columns populated
-- to show partner name and location.
customer_id,
ship_to_site_id)
( -- Bug 3370201
SELECT -- Bug 3416241 changes begin Removed all hints to avoid full table scan
-- causing performance issues which in turn causes ORA-01555: snapshot too old
--/*+ use_hash(mv) parallel(mv,' || to_char(l_parallel_degree) || ')
-- full(peg1.d1) full(peg1.d2) full(peg1.peg1) full(peg1.peg2) full(mv) */
-- -- 2859130 full(peg1.cal)
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num),
peg1.demand_date,
SUM(peg1.allocated_quantity) - MIN(peg1.reserved_quantity), --5027568
peg1.demand_id,
peg1.origination_type,
peg1.order_number,
peg1.sales_order_line_id,
peg1.demand_source_type, --cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(peg1.demand_quantity), -- ssurendr 25-NOV-2002: added for alloc w/b
peg1.request_date, --bug3263368
--bug3684383
peg1.customer_id,
peg1.ship_to_site_id
-- min is used to select distinct values as demand_quantity would be
-- repeating for the same demand_id
FROM
-- use inline view so that view parallel hint could be used.
-- msc_demand_pegging_v peg1,
-- 2859130 (SELECT /*+ ordered use_hash(d2 peg2 peg1 d1 tp cal)
(SELECT -- Bug 3416241 changes begin Removed all hints to avoid full table scan
-- causing performance issues which in turn causes ORA-01555: snapshot too old
--/*+ ordered use_hash(d2 peg2 peg1 tp)
-- parallel(d2,' || to_char(l_parallel_degree) || ')
-- parallel(d1,' || to_char(l_parallel_degree) || ')
-- parallel(peg2,' || to_char(l_parallel_degree) || ')
-- parallel(peg1,' || to_char(l_parallel_degree) || ')*/
-- time_phased_atp
-- parallel(tp,' || to_char(l_parallel_degree) || ')
-- 2859130
-- parallel(cal,' || to_char(l_parallel_degree) || ')
peg2.plan_id,
peg2.inventory_item_id,
peg2.organization_id,
peg2.sr_instance_id,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
NVL(d1.demand_class, :def_num) demand_class,
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
NVL(d2.SCHEDULE_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE))) demand_date,----plan by request date, promise date or schedule date
-- cal.prior_date demand_date, -- 2859130
-- cal.calendar_date demand_date,
peg2.allocated_quantity,
DECODE( d2.origination_type, 30, NVL(d2.reserved_quantity, 0), 0) reserved_quantity, --5027568
d2.demand_id,
d2.origination_type,
--d2.order_number,
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id
-- in order_number column
decode(d2.origination_type, 1, to_char(d2.disposition_id), d2.order_number) order_number,
d2.sales_order_line_id,
d2.demand_source_type, --cmro
decode(d2.origination_type, 4, d2.daily_demand_rate,
d2.using_requirement_quantity) demand_quantity , -- rajjain 02/06/2003 Bug 2782882
decode(d2.order_date_type_code,2,d2.request_date,
d2.request_ship_date)request_date, --bug3263368
--peg2.demand_quantity -- ssurendr 25-NOV-2002: added for alloc w/b
--bug3684383
d2.customer_id,
d2.ship_to_site_id
FROM msc_demands d2,
msc_full_pegging peg2,
msc_full_pegging peg1 ,
msc_demands d1
-- time_phased_atp
-- msc_trading_partners tp
-- 2859130
-- msc_calendar_dates cal
WHERE peg2.plan_id = peg1.plan_id
AND peg2.end_pegging_id = peg1.pegging_id
AND peg2.sr_instance_id = peg1.sr_instance_id
AND d1.plan_id = peg1.plan_id
AND d1.demand_id = peg1.demand_id
AND d1.sr_instance_id = peg1.sr_instance_id
AND d2.plan_id = peg2.plan_id
AND d2.demand_id = peg2.demand_id
AND d2.sr_instance_id = peg2.sr_instance_id
AND d2.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)
-- time_phased_atp
-- AND tp.sr_tp_id = peg2.organization_id
-- AND tp.partner_type = 3 -- bug2646304
-- AND tp.sr_instance_id = peg2.sr_instance_id
-- 2859130
-- AND tp.sr_instance_id = cal.sr_instance_id
-- AND tp.calendar_code = cal.calendar_code
-- AND tp.calendar_exception_set_id = cal.exception_set_id
-- AND TRUNC(d2.using_assembly_demand_date) = cal.calendar_date
) peg1,
msc_item_hierarchy_mv mv
WHERE peg1.plan_id = :p_plan_id
AND peg1.inventory_item_id = mv.inventory_item_id(+)
AND peg1.organization_id = mv.organization_id (+)
AND peg1.sr_instance_id = mv.sr_instance_id (+)
AND peg1.demand_date >= mv.effective_date (+)
AND peg1.demand_date <= mv.disable_date (+)
AND peg1.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num),
peg1.demand_date,
peg1.demand_id,
peg1.origination_type,
peg1.order_number,
peg1.sales_order_line_id,
peg1.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
peg1.request_date,
--bug3684383
peg1.customer_id,
peg1.ship_to_site_id)';
msc_util.msc_log('After inserting in msc_alloc_demands part 1');
l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_DEMANDS(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
demand_source_type, --cmro
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity,
request_date)--bug3263368
(
SELECT --5053818
pegging_v.plan_id plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
pegging_v.demand_date,
SUM(pegging_v.allocated_quantity),
pegging_v.demand_id,
pegging_v.origination_type,
pegging_v.order_number,
pegging_v.sales_order_line_id,
pegging_v.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(pegging_v.demand_quantity),
pegging_v.request_date --bug3263368
FROM
(SELECT -- Bug 3416241 changes begin Removed all hints to avoid full table scan
-- causing performance issues which in turn causes ORA-01555: snapshot too old
--/*+ ordered use_hash(peg2 peg1 d s)
-- parallel(peg2,' || to_char(l_parallel_degree) || ')
-- parallel(peg1,' || to_char(l_parallel_degree) || ')
-- parallel(d,' || to_char(l_parallel_degree) || ')
-- parallel(s,' || to_char(l_parallel_degree) || ')
-- full(peg2) full(peg1) full(d) full(s) */
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(s.demand_class, :def_num) demand_class,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(d.SCHEDULE_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))) demand_date,--plan by request date, promise date or schedule date
peg1.allocated_quantity,
d.demand_id,
d.origination_type,
decode(d.origination_type, 1, to_char(d.disposition_id), d.order_number) order_number,
d.sales_order_line_id,
d.demand_source_type, --cmro
decode(d.origination_type, 4, d.daily_demand_rate,
d.using_requirement_quantity) demand_quantity ,
decode(d.order_date_type_code,2,d.request_date,
d.request_ship_date)request_date --bug3263368
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d,
msc_supplies s
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND d.demand_id = peg1.demand_id
AND peg1.plan_id = d.plan_id
AND d.sr_instance_id = peg1.sr_instance_id
AND peg1.sr_instance_id=s.sr_instance_id
AND peg1.plan_id = s.plan_id
AND peg1.transaction_id = s.transaction_id
AND d.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)) pegging_v,
msc_item_hierarchy_mv mv
WHERE pegging_v.inventory_item_id = mv.inventory_item_id(+)
AND pegging_v.organization_id = mv.organization_id (+)
AND pegging_v.sr_instance_id = mv.sr_instance_id (+)
AND pegging_v.demand_date >= mv.effective_date (+)
AND pegging_v.demand_date <= mv.disable_date (+)
AND pegging_v.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
pegging_v.plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num),
pegging_v.demand_date,
pegging_v.demand_id,
pegging_v.origination_type,
pegging_v.order_number,
pegging_v.sales_order_line_id,
pegging_v.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
pegging_v.request_date)';
l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_DEMANDS(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
demand_source_type, --cmro
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity, -- ssurendr 25-NOV-2002: added for alloc w/b
request_date)--bug3263368
(SELECT -- Bug 3416241 changes begin Removed all hints to avoid full table scan
-- causing performance issues which in turn causes ORA-01555: snapshot too old
--/*+ ordered use_hash(peg2 peg1 d)
-- parallel(peg2,' || to_char(l_parallel_degree) || ')
-- parallel(peg1,' || to_char(l_parallel_degree) || ')
-- parallel(d,' || to_char(l_parallel_degree) || ')
-- full(peg2) full(peg1) full(d) */
-- time_phased_atp
-- parallel(tp,' || to_char(l_parallel_degree) || ')
-- 2859130 parallel(cal,' || to_char(l_parallel_degree) || ')
-- full(cal)
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num demand_class,
-- cal.prior_date, -- 2859130
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(d.SCHEDULE_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),--plan by request date, promise date or schedule date
SUM(peg1.allocated_quantity),
d.demand_id,
d.origination_type,
--d.order_number,
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id
-- in order_number column
decode(d.origination_type, 1, to_char(d.disposition_id), d.order_number),
d.sales_order_line_id,
d.demand_source_type, --cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(decode(d.origination_type, 4, d.daily_demand_rate,
d.using_requirement_quantity)), -- rajjain 02/06/2003 Bug 2782882
--MIN(peg1.demand_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as demand_quantity would be
-- repeating for the same demand_id
--decode(d.order_date_type_code,2,d2.request_date,
decode(d.order_date_type_code,2,d.request_date, -- Bug 3370201
d.request_ship_date)request_date --bug3263368
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d
-- time_phased_atp msc_trading_partners tp
-- 2859130 msc_calendar_dates cal
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND d.demand_id = peg1.demand_id
AND peg1.plan_id = d.plan_id
AND d.sr_instance_id = peg1.sr_instance_id
AND d.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)
--AND tp.sr_tp_id = peg1.organization_id
--AND tp.partner_type = 3 -- bug2646304
--AND tp.sr_instance_id = peg1.sr_instance_id
--AND tp.sr_instance_id = cal.sr_instance_id
--AND tp.calendar_code = cal.calendar_code
--AND tp.calendar_exception_set_id = cal.exception_set_id
--AND TRUNC(d.using_assembly_demand_date) = cal.calendar_date
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(d.SCHEDULE_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),--plan by request date, promise date or schedule date
-- 2859130 cal.prior_date,
d.demand_id,
d.origination_type,
decode(d.origination_type, 1, to_char(d.disposition_id), d.order_number),
d.sales_order_line_id,
d.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
--decode(d.order_date_type_code,2,d2.request_date,
decode(d.order_date_type_code,2,d.request_date, -- Bug 3370201
d.request_ship_date) --bug3263368
)';
msc_util.msc_log('after inserting item data into MSC_ALLOC_DEMANDS tables');
l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_SUPPLIES(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
supply_date,
allocated_quantity,
parent_transaction_id,
order_type,
order_number,
schedule_designator_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
(
SELECT /*+ use_hash(peg1 mv) parallel(mv,' || to_char(l_parallel_degree) || ') */
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
peg1.supply_date,
SUM(peg1.allocated_quantity),
peg1.transaction_id,
peg1.order_type,
peg1.order_number,
peg1.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(peg1.supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as supply_quantity would be
-- repeating for the same transaction_id
FROM -- msc_supply_pegging_v peg1,
(SELECT --5053818
-- time_phased_atp
-- parallel(tp,' || to_char(l_parallel_degree) || ')
-- 2859130 parallel(cal,' || to_char(l_parallel_degree) || ')
peg2.plan_id,
peg2.inventory_item_id,
peg2.organization_id,
peg2.sr_instance_id,
NVL (d.demand_class, :def_num) demand_class,
trunc(s.new_schedule_date) supply_date,
-- cal.next_date supply_date, --2859130
peg2.allocated_quantity,
peg2.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id ,
nvl(s.firm_quantity,s.new_order_quantity) supply_quantity -- rajjain 02/06/2003 Bug 2782882
--peg2.supply_quantity -- ssurendr 25-NOV-2002: added for alloc w/b
FROM msc_supplies s,
msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d
-- time_phased_atp msc_trading_partners tp
-- 2859130 msc_calendar_dates cal
WHERE peg2.plan_id = peg1.plan_id
AND peg2.end_pegging_id = peg1.pegging_id
AND d.plan_id = peg1.plan_id
AND d.demand_id = peg1.demand_id
AND d.sr_instance_id = peg1.sr_instance_id
AND d.inventory_item_id = peg1.inventory_item_id
AND s.plan_id = peg2.plan_id
AND s.transaction_id = peg2.transaction_id
AND s.sr_instance_id = peg2.sr_instance_id
-- time_phased_atp
-- AND tp.sr_tp_id = peg2.organization_id
-- AND tp.partner_type = 3 -- bug2646304
-- AND tp.sr_instance_id = peg2.sr_instance_id
-- 2859130 AND tp.sr_instance_id = cal.sr_instance_id
--AND tp.calendar_code = cal.calendar_code
--AND tp.calendar_exception_set_id = cal.exception_set_id
--AND TRUNC(s.new_schedule_date) = cal.calendar_date
) peg1,
msc_item_hierarchy_mv mv
WHERE peg1.plan_id = :p_plan_id
AND peg1.inventory_item_id = mv.inventory_item_id(+)
AND peg1.organization_id = mv.organization_id (+)
AND peg1.sr_instance_id = mv.sr_instance_id (+)
AND peg1.supply_date >= mv.effective_date (+)
AND peg1.supply_date <= mv.disable_date (+)
AND peg1.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num),
peg1.supply_date,
peg1.transaction_id,
peg1.order_type,
peg1.order_number,
peg1.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate)';
msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_SUPPLIES(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
supply_date,
allocated_quantity,
parent_transaction_id,
order_type,
order_number,
schedule_designator_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
supply_quantity)
(
SELECT --5053818
pegging_v.plan_id plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
pegging_v.supply_date,
SUM(pegging_v.allocated_quantity),
pegging_v.transaction_id,
pegging_v.order_type,
pegging_v.order_number,
pegging_v.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(pegging_v.supply_quantity)
FROM
(SELECT --5053818
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(s.demand_class, :def_num) demand_class,
TRUNC(s.new_schedule_date) supply_date,
peg1.allocated_quantity,
peg1.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
nvl(s.firm_quantity,s.new_order_quantity) supply_quantity
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_supplies s
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND s.plan_id = peg1.plan_id
AND s.transaction_id = peg1.transaction_id
AND s.sr_instance_id = peg1.sr_instance_id) pegging_v,
msc_item_hierarchy_mv mv
WHERE pegging_v.inventory_item_id = mv.inventory_item_id(+)
AND pegging_v.organization_id = mv.organization_id (+)
AND pegging_v.sr_instance_id = mv.sr_instance_id (+)
AND pegging_v.supply_date >= mv.effective_date (+)
AND pegging_v.supply_date <= mv.disable_date (+)
AND pegging_v.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
pegging_v.plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num),
pegging_v.supply_date,
pegging_v.transaction_id,
pegging_v.order_type,
pegging_v.order_number,
pegging_v.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate)';
l_sql_stmt_1 := 'INSERT INTO MSC_ALLOC_SUPPLIES(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
supply_date,
allocated_quantity,
parent_transaction_id,
order_type,
order_number,
schedule_designator_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
(
SELECT --5053818
-- time_phased_atp parallel(tp,' || to_char(l_parallel_degree) || ')
-- 2859130 parallel(cal,' || to_char(l_parallel_degree) || ')
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num demand_class,
trunc(s.new_schedule_date),
-- cal.next_date, --2859130
-- cal.calendar_date,
SUM(peg1.allocated_quantity),
peg1.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(nvl(s.firm_quantity,s.new_order_quantity)) -- rajjain 02/06/2003 Bug 2782882
--MIN(peg1.supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as supply_quantity would be
-- repeating for the same transaction_id
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_supplies s
-- time_phased_atp msc_trading_partners tp
-- 2859130 msc_calendar_dates cal
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND s.plan_id = peg1.plan_id
AND s.transaction_id = peg1.transaction_id
AND s.sr_instance_id = peg1.sr_instance_id
-- time_phased_atp
-- AND tp.sr_tp_id = peg1.organization_id
-- AND tp.partner_type = 3 -- bug2646304
-- AND tp.sr_instance_id = peg1.sr_instance_id
-- 2859130 AND tp.sr_instance_id = cal.sr_instance_id
-- AND tp.calendar_code = cal.calendar_code
-- AND tp.calendar_exception_set_id = cal.exception_set_id
-- AND TRUNC(s.new_schedule_date) = cal.calendar_date
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num,
trunc(s.new_schedule_date),
-- 2859130 cal.next_date,
peg1.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate)';
msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
msc_util.msc_log('after inserting item data into MSC_ALLOC_SUPPLIES tables');
msc_util.msc_log('not a shared plan partition, insert data into temp tables');
SELECT t.tablespace_name, NVL(i.def_tablespace_name, t.tablespace_name)
INTO l_tbspace, l_ind_tbspace
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM dba_tab_partitions t,
-- dba_part_indexes i
FROM all_tab_partitions t,
all_part_indexes i
WHERE t.table_owner = l_msc_schema
AND t.table_name = 'MSC_ALLOC_DEMANDS'
AND t.partition_name = 'ALLOC_DEMANDS_' || to_char(l_plan_id)
AND i.owner (+) = t.table_owner
AND i.table_name (+) = t.table_name
AND rownum = 1;
l_insert_stmt := 'CREATE TABLE ' || l_temp_table
|| ' TABLESPACE ' || l_tbspace
|| ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
|| ' as select * from MSC_ALLOC_DEMANDS where 1=2 ';
l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
PLAN_ID NUMBER NOT NULL,
INVENTORY_ITEM_ID NUMBER NOT NULL,
ORGANIZATION_ID NUMBER NOT NULL,
SR_INSTANCE_ID NUMBER NOT NULL,
DEMAND_CLASS VARCHAR2(30) , --bug3272444
DEMAND_DATE DATE NOT NULL,
PARENT_DEMAND_ID NUMBER NOT NULL,
ALLOCATED_QUANTITY NUMBER NOT NULL,
ORIGINATION_TYPE NUMBER NOT NULL,
ORDER_NUMBER VARCHAR2(62),
SALES_ORDER_LINE_ID NUMBER,
OLD_DEMAND_DATE DATE,
OLD_ALLOCATED_QUANTITY NUMBER,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
DEMAND_QUANTITY NUMBER, -- ssurendr 25-NOV-2002: added for alloc w/b
PF_DISPLAY_FLAG NUMBER, -- For time_phased_atp
ORIGINAL_ITEM_ID NUMBER, -- For time_phased_atp
ORIGINAL_ORIGINATION_TYPE NUMBER, -- For time_phased_atp
ORIGINAL_DEMAND_DATE DATE, -- For time_phased_atp
SOURCE_ORGANIZATION_ID NUMBER, -- For time_phased_atp --bug3272444
USING_ASSEMBLY_ITEM_ID NUMBER, -- For time_phased_atp --bug3272444
CUSTOMER_ID NUMBER, -- For time_phased_atp
SHIP_TO_SITE_ID NUMBER, -- For time_phased_atp
REFRESH_NUMBER NUMBER, --bug3272444
OLD_REFRESH_NUMBER NUMBER, --bug3272444
DEMAND_SOURCE_TYPE NUMBER, --cmro
REQUEST_DATE DATE) --bug3263368
TABLESPACE ' || l_tbspace || '
-- NOLOGGING
PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)';
STATEMENT => l_insert_stmt,
OBJECT_NAME => l_temp_table);
STATEMENT => l_insert_stmt,
OBJECT_NAME => l_temp_table);
l_insert_stmt := 'INSERT INTO ' || l_temp_table || '(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
demand_source_type,--cmro
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity, -- ssurendr 25-NOV-2002: added for alloc w/b
request_date,-- Bug 3370201
--bug3684383
customer_id,
ship_to_site_id)
(
SELECT --5053818
-- full(peg1.tp)
-- full(peg1.cal)
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
peg1.demand_date,
(SUM(peg1.allocated_quantity)- MIN(peg1.reserved_quantity)) allocated_quantity, --5027568
peg1.demand_id,
peg1.origination_type,
peg1.order_number,
peg1.sales_order_line_id,
peg1.demand_source_type,--cmro
:l_user_id created_by,
:l_sysdate creation_date,
:l_user_id last_updated_by,
:l_sysdate last_update_date,
MIN(peg1.demand_quantity) demand_quantity, -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as demand_quantity would be
-- repeating for the same demand_id
peg1.request_date, -- Bug 3370201
--bug3684383
peg1.customer_id,
peg1.ship_to_site_id
FROM
-- use inline view so that view parallel hint could be used.
-- msc_demand_pegging_v peg1,
(SELECT --5053818
-- parallel(tp,' || to_char(l_parallel_degree) || ')
-- parallel(cal,' || to_char(l_parallel_degree) || ')
peg2.plan_id,
peg2.inventory_item_id,
peg2.organization_id,
peg2.sr_instance_id,
NVL(d1.demand_class, :def_num) demand_class,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d2.PLANNED_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE),
NVL(d2.SCHEDULE_SHIP_DATE,d2.USING_ASSEMBLY_DEMAND_DATE))) demand_date, --plan by request date, promise date or schedule date
-- cal.prior_date demand_date,
-- cal.calendar_date demand_date, -- 2859130
peg2.allocated_quantity,
DECODE( d2.origination_type, 30, NVL(d2.reserved_quantity, 0), 0) reserved_quantity, --5027568
d2.demand_id,
d2.origination_type,
--d2.order_number,
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id
-- in order_number column
decode(d2.origination_type, 1, to_char(d2.disposition_id), d2.order_number) order_number,
d2.sales_order_line_id,
d2.demand_source_type,--cmro
decode(d2.origination_type, 4, d2.daily_demand_rate,
d2.using_requirement_quantity) demand_quantity, -- rajjain 02/06/2003 Bug 2782882
--peg2.demand_quantity -- ssurendr 25-NOV-2002: added for alloc w/b
decode(d2.order_date_type_code,2,d2.request_date,
d2.request_ship_date) request_date, -- Bug 3370201
--bug3684383
d2.customer_id,
d2.ship_to_site_id
FROM msc_demands d2,
msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d1
-- time_phased_atp msc_trading_partners tp
-- 2859130 msc_calendar_dates cal
WHERE peg2.plan_id = peg1.plan_id
AND peg2.end_pegging_id = peg1.pegging_id
AND peg2.sr_instance_id = peg1.sr_instance_id
AND d1.plan_id = peg1.plan_id
AND d1.demand_id = peg1.demand_id
AND d1.sr_instance_id = peg1.sr_instance_id
AND d2.plan_id = peg2.plan_id
AND d2.demand_id = peg2.demand_id
AND d2.sr_instance_id = peg2.sr_instance_id
AND d2.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)
-- time_phased_atp
-- AND tp.sr_tp_id = peg2.organization_id
-- AND tp.partner_type = 3 -- bug2646304
-- AND tp.sr_instance_id = peg2.sr_instance_id
-- 2859130
-- AND tp.sr_instance_id = cal.sr_instance_id
--AND tp.calendar_code = cal.calendar_code
--AND tp.calendar_exception_set_id = cal.exception_set_id
--AND TRUNC(d2.using_assembly_demand_date) = cal.calendar_date
) peg1,
msc_item_hierarchy_mv mv
WHERE peg1.plan_id = :p_plan_id
AND peg1.inventory_item_id = mv.inventory_item_id(+)
AND peg1.organization_id = mv.organization_id (+)
AND peg1.sr_instance_id = mv.sr_instance_id (+)
AND peg1.demand_date >= mv.effective_date (+)
AND peg1.demand_date <= mv.disable_date (+)
AND peg1.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num),
peg1.demand_date,
peg1.demand_id,
peg1.origination_type,
peg1.order_number,
peg1.sales_order_line_id,
peg1.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
peg1.request_date, -- Bug 3370201
--bug3684383
peg1.customer_id,
peg1.ship_to_site_id)';
DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
l_insert_stmt := 'INSERT INTO ' || l_temp_table || '(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
demand_source_type,--cmro
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity,
request_date) -- Bug 3370201
(
SELECT --5053818
pegging_v.plan_id plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
pegging_v.demand_date,
SUM(pegging_v.allocated_quantity),
pegging_v.demand_id,
pegging_v.origination_type,
pegging_v.order_number,
pegging_v.sales_order_line_id,
pegging_v.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(pegging_v.demand_quantity),
pegging_v.request_date -- Bug 3370201
FROM
(SELECT --5053818
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(s.demand_class, :def_num) demand_class,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(d.SCHEDULE_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))) demand_date,--plan by request date, promise date or schedule date
peg1.allocated_quantity,
d.demand_id,
d.origination_type,
decode(d.origination_type, 1, to_char(d.disposition_id), d.order_number) order_number,
d.sales_order_line_id,
d.demand_source_type,--cmro
decode(d.origination_type, 4, d.daily_demand_rate,
d.using_requirement_quantity) demand_quantity,
decode(d.order_date_type_code,2,d.request_date,
d.request_ship_date) request_date -- Bug 3370201
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d,
msc_supplies s
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND d.demand_id = peg1.demand_id
AND peg1.plan_id = d.plan_id
AND d.sr_instance_id = peg1.sr_instance_id
AND peg1.sr_instance_id=s.sr_instance_id
AND peg1.plan_id = s.plan_id
AND peg1.transaction_id = s.transaction_id
AND d.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)) pegging_v,
msc_item_hierarchy_mv mv
WHERE pegging_v.inventory_item_id = mv.inventory_item_id(+)
AND pegging_v.organization_id = mv.organization_id (+)
AND pegging_v.sr_instance_id = mv.sr_instance_id (+)
AND pegging_v.demand_date >= mv.effective_date (+)
AND pegging_v.demand_date <= mv.disable_date (+)
AND pegging_v.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
pegging_v.plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num),
pegging_v.demand_date,
pegging_v.demand_id,
pegging_v.origination_type,
pegging_v.order_number,
pegging_v.sales_order_line_id,
pegging_v.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
pegging_v.request_date)'; -- Bug 3370201
l_insert_stmt := 'INSERT INTO ' || l_temp_table || '(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
demand_source_type,--cmro
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity, -- ssurendr 25-NOV-2002: added for alloc w/b
request_date) -- Bug 3370201
(SELECT --5053818
-- time_phased_atp
-- parallel(tp,' || to_char(l_parallel_degree) || ')
-- 2859130 full(cal)
--parallel(cal,' || to_char(l_parallel_degree) || ')
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num demand_class,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(d.SCHEDULE_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),--plan by request date, promise date or schedule date -- 2859130
-- cal.prior_date,
SUM(peg1.allocated_quantity),
d.demand_id,
d.origination_type,
--d.order_number,
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id
-- in order_number column
decode(d.origination_type, 1, to_char(d.disposition_id), d.order_number),
d.sales_order_line_id,
d.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(decode(d.origination_type, 4, d.daily_demand_rate,
d.using_requirement_quantity)), -- rajjain 02/06/2003 Bug 2782882
--MIN(peg1.demand_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as demand_quantity would be
-- repeating for the same demand_id
decode(d.order_date_type_code,2,d.request_date,
d.request_ship_date) request_date -- Bug 3370201
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d
-- time_phased_atp msc_trading_partners tp
-- 2859130 msc_calendar_dates cal
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND d.demand_id = peg1.demand_id
AND peg1.plan_id = d.plan_id
AND d.sr_instance_id = peg1.sr_instance_id
AND d.origination_type NOT IN (5,7,8,9,11,15,22,28,29,31,70)
-- time_phased_atp
--AND tp.sr_tp_id = peg1.organization_id
--AND tp.partner_type = 3 -- bug2646304
--AND tp.sr_instance_id = peg1.sr_instance_id
-- 2859130 AND tp.sr_instance_id = cal.sr_instance_id
--AND tp.calendar_code = cal.calendar_code
--AND tp.calendar_exception_set_id = cal.exception_set_id
--AND TRUNC(d.using_assembly_demand_date) = cal.calendar_date
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
trunc(DECODE('||MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF||',
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(d.SCHEDULE_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),--plan by request date, promise date or schedule date
--cal.prior_date,
d.demand_id,
d.origination_type,
decode(d.origination_type, 1, to_char(d.disposition_id), d.order_number),
d.sales_order_line_id,
d.demand_source_type,--cmro
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
decode(d.order_date_type_code,2,d.request_date,
d.request_ship_date) -- Bug 3370201
)';
DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
msc_util.msc_log('after inserting item data into MSC_TEMP_ALLOC_DEMANDS table');
SELECT t.tablespace_name, NVL(i.def_tablespace_name, t.tablespace_name)
INTO l_tbspace, l_ind_tbspace
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM dba_tab_partitions t,
-- dba_part_indexes i
FROM all_tab_partitions t,
all_part_indexes i
WHERE t.table_owner = l_msc_schema
AND t.table_name = 'MSC_ALLOC_SUPPLIES'
AND t.partition_name = 'ALLOC_SUPPLIES_' || to_char(l_plan_id)
AND i.owner (+) = t.table_owner
AND i.table_name (+) = t.table_name
AND rownum = 1;
l_insert_stmt := 'CREATE TABLE ' || l_temp_table
|| ' TABLESPACE ' || l_tbspace
|| ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
|| ' as select * from msc_alloc_supplies where 1=2 ';
l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
PLAN_ID NUMBER NOT NULL,
INVENTORY_ITEM_ID NUMBER NOT NULL,
ORGANIZATION_ID NUMBER NOT NULL,
SR_INSTANCE_ID NUMBER NOT NULL,
DEMAND_CLASS VARCHAR2(30) , --bug3272444
SUPPLY_DATE DATE NOT NULL,
PARENT_TRANSACTION_ID NUMBER NOT NULL,
ALLOCATED_QUANTITY NUMBER NOT NULL,
ORDER_TYPE NUMBER NOT NULL,
ORDER_NUMBER VARCHAR2(240),
SCHEDULE_DESIGNATOR_ID NUMBER,
SALES_ORDER_LINE_ID NUMBER,
OLD_SUPPLY_DATE DATE,
OLD_ALLOCATED_QUANTITY NUMBER,
STEALING_FLAG NUMBER,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
FROM_DEMAND_CLASS VARCHAR2(80), -- ssurendr 25-NOV-2002: added for alloc w/b
SUPPLY_QUANTITY NUMBER, -- ssurendr 25-NOV-2002: added for alloc w/b
ORIGINAL_ORDER_TYPE NUMBER, -- For time_phased_atp --bug3272444
ORIGINAL_ITEM_ID NUMBER, -- For time_phased_atp --bug3272444
CUSTOMER_ID NUMBER, -- For time_phased_atp
SHIP_TO_SITE_ID NUMBER,
REFRESH_NUMBER NUMBER,
OLD_REFRESH_NUMBER NUMBER, --bug3272444
ATO_MODEL_LINE_ID NUMBER,
--ATO_MODEL_LINE_ID NUMBER) -- For time_phased_atp commented as part of cmro
DEMAND_SOURCE_TYPE NUMBER) --cmro
TABLESPACE ' || l_tbspace || '
-- NOLOGGING
PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)';
STATEMENT => l_insert_stmt,
OBJECT_NAME => l_temp_table);
STATEMENT => l_insert_stmt,
OBJECT_NAME => l_temp_table);
l_insert_stmt := 'INSERT INTO ' || l_temp_table || '(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
supply_date,
allocated_quantity,
parent_transaction_id,
order_type,
order_number,
schedule_designator_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
(
SELECT --5053818
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
peg1.supply_date,
SUM(peg1.allocated_quantity) allocated_quantity,
peg1.transaction_id,
peg1.order_type,
peg1.order_number,
peg1.schedule_designator_id,
:l_user_id created_by,
:l_sysdate creation_date,
:l_user_id last_updated_by,
:l_sysdate last_update_date,
MIN(peg1.supply_quantity) supply_quantity -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as supply_quantity would be
-- repeating for the same transaction_id
FROM -- msc_supply_pegging_v peg1,
(SELECT --5053818
-- time_phased_atp parallel(tp,' || to_char(l_parallel_degree) || ')
-- parallel(cal,' || to_char(l_parallel_degree) || ')
peg2.plan_id,
peg2.inventory_item_id,
peg2.organization_id,
peg2.sr_instance_id,
NVL (d.demand_class, :def_num) demand_class,
trunc(s.new_schedule_date) supply_date,
-- cal.next_date supply_date,
-- cal.calendar_date supply_date, -- 2859130
peg2.allocated_quantity,
peg2.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
nvl(s.firm_quantity,s.new_order_quantity) supply_quantity -- rajjain 02/06/2003 Bug 2782882
--peg2.supply_quantity -- ssurendr 25-NOV-2002: added for alloc w/b
FROM msc_supplies s,
msc_full_pegging peg2,
msc_full_pegging peg1,
msc_demands d
-- time_phased_atp msc_trading_partners tp
-- 2859130 msc_calendar_dates cal
WHERE peg2.plan_id = peg1.plan_id
AND peg2.end_pegging_id = peg1.pegging_id
AND d.plan_id = peg1.plan_id
AND d.demand_id = peg1.demand_id
AND d.sr_instance_id = peg1.sr_instance_id
AND d.inventory_item_id = peg1.inventory_item_id
AND s.plan_id = peg2.plan_id
AND s.transaction_id = peg2.transaction_id
AND s.sr_instance_id = peg2.sr_instance_id
-- time_phased_atp
--AND tp.sr_tp_id = peg2.organization_id
--AND tp.partner_type = 3 -- bug2646304
--AND tp.sr_instance_id = peg2.sr_instance_id
-- 2859130 AND tp.sr_instance_id = cal.sr_instance_id
--AND tp.calendar_code = cal.calendar_code
--AND tp.calendar_exception_set_id = cal.exception_set_id
--AND TRUNC(s.new_schedule_date) = cal.calendar_date
) peg1,
msc_item_hierarchy_mv mv
WHERE peg1.plan_id = :p_plan_id
AND peg1.inventory_item_id = mv.inventory_item_id(+)
AND peg1.organization_id = mv.organization_id (+)
AND peg1.sr_instance_id = mv.sr_instance_id (+)
AND peg1.supply_date >= mv.effective_date (+)
AND peg1.supply_date <= mv.disable_date (+)
AND peg1.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(mv.demand_class, :def_num),
peg1.supply_date,
peg1.transaction_id,
peg1.order_type,
peg1.order_number,
peg1.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate)';
DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
msc_util.msc_log('After inserting in msc_alloc_supplies part 1');
l_insert_stmt := 'INSERT INTO ' || l_temp_table || '(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
supply_date,
allocated_quantity,
parent_transaction_id,
order_type,
order_number,
schedule_designator_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
supply_quantity)
(
SELECT --5053818
pegging_v.plan_id plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num) demand_class,
pegging_v.supply_date,
SUM(pegging_v.allocated_quantity),
pegging_v.transaction_id,
pegging_v.order_type,
pegging_v.order_number,
pegging_v.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(pegging_v.supply_quantity)
FROM
(SELECT --5053818
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
NVL(s.demand_class, :def_num) demand_class,
trunc(s.new_schedule_date) supply_date,
peg1.allocated_quantity,
peg1.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
nvl(s.firm_quantity,s.new_order_quantity) supply_quantity
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_supplies s
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND s.plan_id = peg1.plan_id
AND s.transaction_id = peg1.transaction_id
AND s.sr_instance_id = peg1.sr_instance_id) pegging_v,
msc_item_hierarchy_mv mv
WHERE pegging_v.inventory_item_id = mv.inventory_item_id(+)
AND pegging_v.organization_id = mv.organization_id (+)
AND pegging_v.sr_instance_id = mv.sr_instance_id (+)
AND pegging_v.supply_date >= mv.effective_date (+)
AND pegging_v.supply_date <= mv.disable_date (+)
AND pegging_v.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1
GROUP BY
pegging_v.plan_id,
pegging_v.inventory_item_id,
pegging_v.organization_id,
pegging_v.sr_instance_id,
NVL(mv.demand_class, :def_num),
pegging_v.supply_date,
pegging_v.transaction_id,
pegging_v.order_type,
pegging_v.order_number,
pegging_v.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate)';
l_insert_stmt := 'INSERT INTO ' || l_temp_table || '(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
supply_date,
allocated_quantity,
parent_transaction_id,
order_type,
order_number,
schedule_designator_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
(
SELECT --5053818
-- time_phased_atp parallel(tp,' || to_char(l_parallel_degree) || ')
--2859130 parallel(cal,' || to_char(l_parallel_degree) || ')
peg1.plan_id plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num demand_class,
trunc(s.new_schedule_date),
-- cal.next_date,
--cal.calendar_date, -- 2859130
SUM(peg1.allocated_quantity),
peg1.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate,
MIN(nvl(s.firm_quantity,s.new_order_quantity)) -- rajjain 02/06/2003 Bug 2782882
--MIN(peg1.supply_quantity) -- ssurendr 25-NOV-2002: added for alloc w/b
-- min is used to select distinct values as supply_quantity would be
-- repeating for the same transaction_id
FROM msc_full_pegging peg2,
msc_full_pegging peg1,
msc_supplies s
-- msc_trading_partners tp
-- msc_calendar_dates cal
WHERE peg1.plan_id = :p_plan_id
AND peg2.plan_id = peg1.plan_id
AND peg2.pegging_id = peg1.end_pegging_id
AND peg2.demand_id IN (-1, -2)
AND s.plan_id = peg1.plan_id
AND s.transaction_id = peg1.transaction_id
AND s.sr_instance_id = peg1.sr_instance_id
-- time_phased_atp
--AND tp.sr_tp_id = peg1.organization_id
--AND tp.partner_type = 3 -- bug2646304
--AND tp.sr_instance_id = peg1.sr_instance_id
-- 2859130 AND tp.sr_instance_id = cal.sr_instance_id
--AND tp.calendar_code = cal.calendar_code
--AND tp.calendar_exception_set_id = cal.exception_set_id
--AND TRUNC(s.new_schedule_date) = cal.calendar_date
GROUP BY
peg1.plan_id,
peg1.inventory_item_id,
peg1.organization_id,
peg1.sr_instance_id,
:def_num,
trunc(s.new_schedule_date),
-- cal.next_date, -- 2859130
peg1.transaction_id,
s.order_type,
s.order_number,
s.schedule_designator_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate)';
DBMS_SQL.PARSE(cur_handler, l_insert_stmt, DBMS_SQL.NATIVE);
msc_util.msc_log('After inserting in msc_alloc_supplies part 2');
--insert reservation_records to msc_alloc_demands.
msc_util.msc_log('inserting reservation rows in msc_alloc_demands');
INSERT INTO MSC_ALLOC_DEMANDS(
plan_id,
inventory_item_id,
organization_id,
sr_instance_id,
demand_class,
demand_date,
allocated_quantity,
parent_demand_id,
origination_type,
order_number,
sales_order_line_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
demand_quantity
)
(SELECT
plan_id,
USING_ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DECODE(DEMAND_CLASS, NULL, '-1', MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
NULL,
NULL,
USING_ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
TRUNC(SYSDATE),
NULL,
DEMAND_CLASS)), --bug 13064033
TRUNC(SYSDATE),
using_requirement_quantity,
demand_id,
origination_type,
ORDER_NUMBER,
SALES_ORDER_LINE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
using_requirement_quantity
from msc_demands
where plan_id = p_plan_id
and origination_type = -100
and using_requirement_quantity <> 0
);
msc_util.msc_log('inserted reservation rows in msc_alloc_demands :' || SQL%ROWCOUNT);
update msc_plans
set summary_flag = 3
where plan_id = p_plan_id;
update msc_plans
set summary_flag = 1
where plan_id = p_plan_id;
SELECT count(*)
INTO l_count
FROM msc_plans plans,
msc_designators desig
WHERE desig.inventory_atp_flag = 1
AND plans.plan_id = p_plan_id
AND plans.compile_designator = desig.designator
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.organization_id = desig.organization_id
AND plans.plan_completion_date is not null
AND plans.data_completion_date is not null
-- IO Perf:3693983: Don't Launch ATP Post Plan Processes for IO Plans
AND plans.plan_type <> 4;
SELECT count(*)
INTO l_count
FROM msc_plans plans,
msc_designators desig
WHERE desig.inventory_atp_flag = 1
AND plans.plan_id = p_plan_id
AND plans.compile_designator = desig.designator
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.organization_id = desig.organization_id
AND plans.plan_completion_date is not null
AND plans.data_completion_date is not null;
select nvl (copy_plan_id, -1)
into l_copy_plan_id
from msc_plans
where plan_id = p_plan_id
-- IO Perf:3693983: Don't Launch ATP Post Plan Processes for IO Plans
AND plan_type <> 4;
SELECT count(*)
INTO l_count1
FROM msc_plans plans,
msc_designators desig
WHERE desig.inventory_atp_flag = 1
AND plans.plan_id = l_copy_plan_id
AND plans.compile_designator = desig.designator
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.organization_id = desig.organization_id
AND plans.plan_completion_date is not null
AND plans.data_completion_date is not null
AND plans.plan_type <> 4;
UPDATE msc_plans
SET summary_flag = 1
WHERE plan_id = p_plan_id;
UPDATE msc_plans
SET summary_flag = 1
WHERE plan_id = p_plan_id;
UPDATE msc_plans /* for 24x7 ATP */
SET request_id = l_request_id
WHERE plan_id = p_plan_id;
* Deletes entries from mrp_atp_schedule_temp and mrp_atp_details_temp
* older than p_hours old
*
*/
PROCEDURE ATP_Purge_MRP_Temp(
ERRBUF OUT NoCopy VARCHAR2,
RETCODE OUT NoCopy NUMBER,
p_hours IN NUMBER
)
IS
l_retain_date DATE;
msc_util.msc_log('Delete records older than l_retain_date ' ||
to_char(l_retain_date, 'DD:MM:YYYY hh24:mi:ss'));
DELETE FROM mrp_atp_schedule_temp
WHERE last_update_date < l_retain_date;
msc_util.msc_log('Records Deleted from mrp_atp_schedule_temp : ' ||
SQL%ROWCOUNT);
DELETE FROM mrp_atp_details_temp
WHERE last_update_date < l_retain_date;
msc_util.msc_log('Records Deleted from mrp_atp_details_temp : ' ||
SQL%ROWCOUNT);
DELETE FROM msc_atp_src_profile_temp
WHERE last_update_date < l_retain_date;
msc_util.msc_log('Records Deleted from msc_atp_src_profile_temp : ' ||
SQL%ROWCOUNT);
SELECT a.oracle_username
INTO l_mrp_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 704;
SELECT a.oracle_username
INTO l_msc_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'before deleteing data from the table');
DELETE MSC_ATP_SUMMARY_SD where plan_id = p_plan_id;
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'Insert data into sd table');
msc_util.msc_log('LOAD_PLAN_SUMMARY_SD: ' || 'After inserting into MSC_ATP_SUMMARY_SD');
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
SD_DATE,
sum(sd_qty),
last_update_date,
last_updated_by,
creation_date,
created_by
--Bug 6046524 added index hint for performance improvement.
from (SELECT /*+ ORDERED index(C,MSC_CALENDAR_DATES_U1)*/
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
-- TRUNC(C.PRIOR_DATE) SD_DATE,
C.CALENDAR_DATE SD_DATE, -- 2859130
-1* DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
D.USING_REQUIREMENT_QUANTITY) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
MSC_TRADING_PARTNERS P,
MSC_DEMANDS D,
MSC_CALENDAR_DATES C
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
-- 1243985
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,70)
-- Bug 1530311, forecast to be excluded
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_ID
-- since we store repetitive schedule demand in different ways for
-- ods (total quantity on start date) and pds (daily quantity from
-- start date to end date), we need to make sure we only select work day
-- for pds's repetitive schedule demand.
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
AND C.CALENDAR_DATE
BETWEEN
TRUNC(DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))
AND
TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))))--plan by request date, promise date or schedule date
AND ((D.ORIGINATION_TYPE = 4 AND C.SEQ_NUM IS NOT NULL) OR
(D.ORIGINATION_TYPE <> 4))
AND I.ORGANIZATION_ID = P.SR_TP_ID
AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND P.PARTNER_TYPE = 3
AND D.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
--Bug 6046524 added index hint for performance improvement.
SELECT /*+ ORDERED index(C,MSC_CALENDAR_DATES_U1)*/
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
-- TRUNC(C.NEXT_DATE) SD_DATE, -- 2859130
C.CALENDAR_DATE SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
MSC_TRADING_PARTNERS P,
MSC_SUPPLIES S,
MSC_CALENDAR_DATES C
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
-- Exclude Cancelled Supplies 2460645
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
AND TRUNC(NVL(S.LAST_UNIT_COMPLETION_DATE, NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
AND DECODE(S.LAST_UNIT_COMPLETION_DATE,
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
AND I.ORGANIZATION_ID = P.SR_TP_ID
AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND P.PARTNER_TYPE = 3
AND S.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
)
GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
last_update_date, last_updated_by, creation_date, created_by );
msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
SD_DATE,
sum(sd_qty),
last_update_date,
last_updated_by,
creation_date,
created_by
from (SELECT /*+ ORDERED */
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
-- TRUNC(C.PRIOR_DATE) SD_DATE,
C.CALENDAR_DATE SD_DATE, -- 2859130
-1* DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
D.USING_REQUIREMENT_QUANTITY) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
MSC_TRADING_PARTNERS P,
MSC_DEMANDS D,
MSC_CALENDAR_DATES C
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
-- 1243985
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,70)
-- Bug 1530311, forecast to be excluded
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_ID
-- since we store repetitive schedule demand in different ways for
-- ods (total quantity on start date) and pds (daily quantity from
-- start date to end date), we need to make sure we only select work day
-- for pds's repetitive schedule demand.
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
AND C.CALENDAR_DATE
BETWEEN TRUNC(DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))
AND TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))))--plan by request date, promise date or schedule date
AND ((D.ORIGINATION_TYPE = 4 AND C.SEQ_NUM IS NOT NULL) OR
(D.ORIGINATION_TYPE <> 4))
AND I.ORGANIZATION_ID = P.SR_TP_ID
AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND P.PARTNER_TYPE = 3
AND D.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
AND I.AGGREGATE_TIME_FENCE_DATE IS NULL -- PF and members to be picked from alloc tables
UNION ALL
SELECT /*+ ORDERED */
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
-- TRUNC(C.NEXT_DATE) SD_DATE, -- 2859130
C.CALENDAR_DATE SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
MSC_TRADING_PARTNERS P,
MSC_SUPPLIES S,
MSC_CALENDAR_DATES C
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
-- Exclude Cancelled Supplies 2460645
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.SR_INSTANCE_ID = S.SR_INSTANCE_ID
AND C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
AND TRUNC(NVL(S.LAST_UNIT_COMPLETION_DATE, NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
AND DECODE(S.LAST_UNIT_COMPLETION_DATE,
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
AND I.ORGANIZATION_ID = P.SR_TP_ID
AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID
AND P.PARTNER_TYPE = 3
AND S.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
AND I.AGGREGATE_TIME_FENCE_DATE IS NULL -- PF and members to be picked from alloc tables
UNION ALL
SELECT /*+ ORDERED */
AD.plan_id,
AD.sr_instance_id,
AD.organization_id,
AD.inventory_item_id,
'@@@' demand_class,
TRUNC(AD.demand_date) SD_DATE,
-1 * AD.allocated_quantity SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.allocated_quantity <> 0
AND AD.refresh_number IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
SELECT /*+ ORDERED */
SA.plan_id,
SA.sr_instance_id,
SA.organization_id,
SA.inventory_item_id,
'@@@' demand_class,
TRUNC(SA.supply_date) SD_DATE,
SA.allocated_quantity SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.allocated_quantity <> 0
AND SA.refresh_number IS NULL -- consider only planning records in full summation - summary enhancement
)
GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id,demand_class, sd_date,
last_update_date, last_updated_by, creation_date, created_by );
msc_util.msc_log('LOAD_SD_FULL_UNALLOC_UNOPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
SD_DATE,
sum(sd_qty),
last_update_date,
last_updated_by,
creation_date,
created_by
from (SELECT /*+ ORDERED */
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
TRUNC(DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))) SD_DATE,
--plan by request date, promise date or schedule date -- 2859130
-1* D.USING_REQUIREMENT_QUANTITY SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
-- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
-- Not required as calendar has been removed
MSC_DEMANDS D
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,70)
--AND I.ORGANIZATION_ID = P.SR_TP_ID -- Removed with summary enhancement
--AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID -- changes. Not required as calendar
--AND P.PARTNER_TYPE = 3 -- has been removed
AND D.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
SELECT /*+ ORDERED */
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
-- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
-- Not required as calendar has been removed
MSC_SUPPLIES S
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
--AND I.ORGANIZATION_ID = P.SR_TP_ID -- Removed with summary enhancement
--AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID -- changes. Not required as calendar
--AND P.PARTNER_TYPE = 3 -- has been removed
AND S.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
)
GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
last_update_date, last_updated_by, creation_date, created_by );
msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
SD_DATE,
sum(sd_qty),
last_update_date,
last_updated_by,
creation_date,
created_by
from (SELECT /*+ ORDERED */
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
-- Bug 3574164 DMD_SATISFIED_DATE IS CHANGED TO PLANNED_SHIP_DATE.
TRUNC(DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))) SD_DATE,
--plan by request date, promise date or schedule date -- 2859130
-1* D.USING_REQUIREMENT_QUANTITY SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
-- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
-- Not required as calendar has been removed
MSC_DEMANDS D
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,70)
--AND I.ORGANIZATION_ID = P.SR_TP_ID -- Removed with summary enhancement
--AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID -- changes. Not required as calendar
--AND P.PARTNER_TYPE = 3 -- has been removed
AND D.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
AND I.AGGREGATE_TIME_FENCE_DATE IS NULL -- PF and members to be picked from alloc tables
UNION ALL
SELECT /*+ ORDERED */
I.plan_id plan_id,
I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
'@@@' demand_class,
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_SYSTEM_ITEMS I,
-- MSC_TRADING_PARTNERS P, -- Removed with summary enhancement changes
-- Not required as calendar has been removed
MSC_SUPPLIES S
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
--AND I.ORGANIZATION_ID = P.SR_TP_ID -- Removed with summary enhancement
--AND I.SR_INSTANCE_ID = P.SR_INSTANCE_ID -- changes. Not required as calendar
--AND P.PARTNER_TYPE = 3 -- has been removed
AND S.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
AND I.AGGREGATE_TIME_FENCE_DATE IS NULL -- PF and members to be picked from alloc tables
UNION ALL
SELECT /*+ ORDERED */
AD.plan_id,
AD.sr_instance_id,
AD.organization_id,
AD.inventory_item_id,
'@@@' demand_class,
TRUNC(AD.demand_date) SD_DATE,
-1 * AD.allocated_quantity SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.allocated_quantity <> 0
AND AD.refresh_number IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
SELECT /*+ ORDERED */
SA.plan_id,
SA.sr_instance_id,
SA.organization_id,
SA.inventory_item_id,
'@@@' demand_class,
TRUNC(SA.supply_date) SD_DATE,
SA.allocated_quantity SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.allocated_quantity <> 0
AND SA.refresh_number IS NULL -- consider only planning records in full summation - summary enhancement
)
GROUP BY plan_id, inventory_item_id,organization_id, sr_instance_id, demand_class, sd_date,
last_update_date, last_updated_by, creation_date, created_by );
msc_util.msc_log('LOAD_SD_FULL_UNALLOC_OPT_PF: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
SD_DATE,
sum(sd_qty),
last_update_date,
last_updated_by,
creation_date,
created_by
from (SELECT /*+ ORDERED */
AD.plan_id,
AD.sr_instance_id,
AD.organization_id,
AD.inventory_item_id,
AD.demand_class,
TRUNC(AD.demand_date) SD_DATE,
-1 * AD.allocated_quantity SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.allocated_quantity <> 0
AND AD.refresh_number IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
SELECT /*+ ORDERED */
SA.plan_id,
SA.sr_instance_id,
SA.organization_id,
SA.inventory_item_id,
SA.demand_class,
TRUNC(SA.supply_date) SD_DATE,
SA.allocated_quantity SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.allocated_quantity <> 0
AND SA.refresh_number IS NULL -- consider only planning records in full summation - summary enhancement
)
GROUP BY plan_id, inventory_item_id, organization_id, sr_instance_id,demand_class, sd_date,
last_update_date, last_updated_by, creation_date, created_by
HAVING sum(SD_QTY) <> 0);
msc_util.msc_log('LOAD_SD_FULL_ALLOC: ' || 'Records inserted : ' || SQL%ROWCOUNT);
SELECT sr_instance_id,
organization_id,
inventory_item_id,
SD_DATE,
sum(sd_qty)
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
from (SELECT I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
TRUNC(DECODE(D.RECORD_SOURCE,
2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))) SD_DATE,
--plan by request date, promise date or schedule date
decode(D.USING_REQUIREMENT_QUANTITY, -- Consider unscheduled orders as dummy supplies
0, D.OLD_DEMAND_QUANTITY, -- For summary enhancement
-1 * D.USING_REQUIREMENT_QUANTITY) SD_QTY
FROM MSC_SYSTEM_ITEMS I,
MSC_DEMANDS D
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,70)
AND D.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
UNION ALL
SELECT I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
FROM MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- These two conditions
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- may not be required
AND S.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
)
GROUP BY inventory_item_id, organization_id, sr_instance_id, sd_date;
SELECT sr_instance_id,
organization_id,
inventory_item_id,
SD_DATE,
sum(sd_qty)
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
from (SELECT I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
TRUNC(DECODE(D.RECORD_SOURCE,
2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(d.PLANNED_SHIP_DATE,d.USING_ASSEMBLY_DEMAND_DATE))),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))) SD_DATE,
--plan by request date, promise date or schedule date
decode(D.USING_REQUIREMENT_QUANTITY, -- Consider unscheduled orders as dummy supplies
0, D.OLD_DEMAND_QUANTITY, -- For summary enhancement
-1 * D.USING_REQUIREMENT_QUANTITY) SD_QTY
FROM MSC_SYSTEM_ITEMS I,
MSC_DEMANDS D
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND D.PLAN_ID = I.PLAN_ID
AND D.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,70)
AND D.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
AND I.AGGREGATE_TIME_FENCE_DATE IS NULL -- PF and members to be picked from alloc tables
UNION ALL
SELECT I.sr_instance_id,
I.organization_id,
I.inventory_item_id,
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
FROM MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S
WHERE I.ATP_FLAG = 'Y'
AND I.PLAN_ID = p_plan_id
AND S.PLAN_ID = I.PLAN_ID
AND S.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND S.ORGANIZATION_ID = I.ORGANIZATION_ID
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- These two conditions
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- may not be required
AND S.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
AND I.AGGREGATE_TIME_FENCE_DATE IS NULL -- PF and members to be picked from alloc tables
UNION ALL
SELECT AD.sr_instance_id,
AD.organization_id,
AD.inventory_item_id,
TRUNC(AD.demand_date) SD_DATE,
decode(AD.allocated_quantity,
0, AD.old_allocated_quantity,
-1 * AD.allocated_quantity) SD_QTY
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
UNION ALL
SELECT SA.sr_instance_id,
SA.organization_id,
SA.inventory_item_id,
TRUNC(SA.supply_date) SD_DATE,
SA.allocated_quantity SD_QTY
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.allocated_quantity <> 0
AND SA.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
)
GROUP BY inventory_item_id, organization_id, sr_instance_id, sd_date;
UPDATE MSC_ATP_SUMMARY_SD
SET sd_qty = sd_qty + l_sd_quantity_tab(j),
last_update_date = p_sys_date,
last_updated_by = l_user_id
WHERE plan_id = p_plan_id
AND sr_instance_id = l_sr_instance_id_tab(j)
AND inventory_item_id = l_inventory_item_id_tab(j)
AND organization_id = l_organization_id_tab(j)
AND sd_date = l_sd_date_tab(j);
msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL UPDATE');
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (p_plan_id,
l_ins_sr_instance_id_tab(j),
l_ins_organization_id_tab(j),
l_ins_inventory_item_id_tab(j),
'@@@',
l_ins_sd_date_tab(j),
l_ins_sd_quantity_tab(j),
p_sys_date,
l_user_id,
p_sys_date,
l_user_id);
msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'After FORALL INSERT');
msc_util.msc_log('LOAD_SD_NET_UNALLOC: ' || 'No records to be inserted');
SELECT sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
SD_DATE,
sum(sd_qty)
from (SELECT AD.sr_instance_id,
AD.organization_id,
AD.inventory_item_id,
AD.demand_class,
TRUNC(AD.demand_date) SD_DATE,
decode(AD.allocated_quantity,
0, AD.old_allocated_quantity,
-1 * AD.allocated_quantity) SD_QTY
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
UNION ALL
SELECT SA.sr_instance_id,
SA.organization_id,
SA.inventory_item_id,
SA.demand_class,
TRUNC(SA.supply_date) SD_DATE,
decode(SA.ALLOCATED_QUANTITY, -- Consider deleted stealing records as dummy demands
0, -1 * OLD_ALLOCATED_QUANTITY, -- For summary enhancement
SA.ALLOCATED_QUANTITY) SD_QTY
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.REFRESH_NUMBER BETWEEN (p_last_refresh_number + 1) and p_new_refresh_number
)
GROUP BY inventory_item_id, organization_id, sr_instance_id, demand_class, sd_date;
UPDATE MSC_ATP_SUMMARY_SD
SET sd_qty = sd_qty + l_sd_quantity_tab(j),
last_update_date = p_sys_date,
last_updated_by = l_user_id
WHERE plan_id = p_plan_id
AND sr_instance_id = l_sr_instance_id_tab(j)
AND inventory_item_id = l_inventory_item_id_tab(j)
AND organization_id = l_organization_id_tab(j)
AND sd_date = l_sd_date_tab(j)
AND demand_class = l_demand_class_tab(j);
msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL UPDATE');
INSERT INTO MSC_ATP_SUMMARY_SD (
plan_id,
sr_instance_id,
organization_id,
inventory_item_id,
demand_class,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (p_plan_id,
l_ins_sr_instance_id_tab(j),
l_ins_organization_id_tab(j),
l_ins_inventory_item_id_tab(j),
l_ins_demand_class_tab(j),
l_ins_sd_date_tab(j),
l_ins_sd_quantity_tab(j),
p_sys_date,
l_user_id,
p_sys_date,
l_user_id);
msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'After FORALL INSERT');
msc_util.msc_log('LOAD_SD_NET_ALLOC: ' || 'No records to be inserted');
SELECT trunc(p.plan_start_date),
p.sr_instance_id,
p.organization_id,
trunc(p.cutoff_date),
tp.calendar_code
INTO l_plan_start_date,
l_instance_id,
l_org_id,
l_cutoff_date,
l_calendar_code
FROM msc_plans p,
msc_trading_partners tp
WHERE p.plan_id = p_plan_id
AND p.organization_id = tp.sr_tp_id
AND p.sr_instance_id = tp.sr_instance_id
AND tp.partner_type = 3;
INSERT INTO MSC_ATP_SUMMARY_SUP(
plan_id,
sr_instance_id,
inventory_item_id,
supplier_id,
supplier_site_id,
sd_date,
sd_qty,
demand_class,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id, sr_instance_id, inventory_item_id, supplier_id, supplier_site_id, sd_date, sum(sd_qty),
demand_class, last_update_date, last_updated_by, creation_date, created_by
FROM (
SELECT SV.plan_id plan_id,
SV.sr_instance_id,
SV.inventory_item_id inventory_item_id,
SV.supplier_id supplier_id,
SV.supplier_site_id supplier_site_id,
c.calendar_date sd_date, -- 2859130 remove trunc
SV.capacity sd_qty,
null demand_class,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM msc_calendar_dates c,
(SELECT /*+ LEADING (I) */
I.plan_id plan_id,
I.sr_instance_id,
I.inventory_item_id inventory_item_id,
S.supplier_id supplier_id,
S.supplier_site_id supplier_site_id,
S.capacity,
trunc(S.from_date) from_date,
trunc(S.to_date) to_date,
mis.delivery_calendar_code,
mis.supplier_lead_time_date
FROM msc_system_items I,
msc_supplier_capacities s,
msc_item_suppliers mis -- Bug 3912422 - Move to the inner query
WHERE I.plan_id = p_plan_id
AND I.atp_components_flag in ('Y', 'C')
AND s.inventory_item_id = I.inventory_item_id
AND s.sr_instance_id = I.sr_instance_id
AND s.plan_id = I.plan_id
AND s.organization_id = i.organization_id --\
AND s.inventory_item_id = mis.inventory_item_id --|
AND s.sr_instance_id = mis.sr_instance_id --> Bug 3912422
AND s.plan_id = mis.plan_id --|
AND s.organization_id = mis.organization_id --/
AND NOT EXISTS --Bug 3912422, Replaced 'NOT IN' by 'NOT EXISTS'
--AND (I.inventory_item_id, S.supplier_id, nvl(S.supplier_site_id,-1)) NOT IN
-- Bug 3912422
(SELECT 'x' -- summary is not supported with flex flences : summary enhancement
FROM msc_supplier_flex_fences msff
WHERE plan_id = p_plan_id
AND msff.inventory_item_id = s.inventory_item_id --\
AND msff.supplier_id = s.supplier_id -- } Bug 3912422
AND msff.supplier_site_id = s.supplier_site_id --/
AND rownum = 1)
group by I.plan_id,
I.inventory_item_id,
I.sr_instance_id,
s.supplier_id,
s.supplier_site_id,
s.capacity,
trunc(s.from_date),
trunc(s.to_date),
mis.delivery_calendar_code,
mis.supplier_lead_time_date) SV
-- msc_item_suppliers mis -- Bug 3912422 - Move to the inner query
WHERE /* SV.inventory_item_id = mis.inventory_item_id
AND SV.supplier_id = mis.supplier_id
AND nvl(SV.supplier_site_id,-1) = nvl(mis.supplier_site_id, -1)
AND SV.sr_instance_id = mis.sr_instance_id
AND c.calendar_code = nvl(mis.delivery_calendar_code, l_calendar_code)
AND*/ c.calendar_code = nvl(SV.delivery_calendar_code, l_calendar_code)
AND c.calendar_date BETWEEN trunc(SV.from_date)
AND NVL(SV.to_date,l_cutoff_date)
-- AND (c.seq_num IS NOT NULL OR mis.delivery_calendar_code IS NULL) -- Bug 3912422
AND (c.seq_num IS NOT NULL OR SV.delivery_calendar_code IS NULL) -- NULL means FOC
AND c.exception_set_id = l_calendar_exception_set_id
AND c.sr_instance_id = l_instance_id
-- AND c.calendar_date >= mis.supplier_lead_time_date -- Bug 3912422
AND c.calendar_date > SV.supplier_lead_time_date
-- Bug 3912422 - We should start looking from the day after supplier_lead_time_date
-- to accomodate for planning's additional "-1". If SMC is found in ASL then this
-- would mean one day offset as per ASL. If it is FOC then it would mean starting
-- from the next day.
UNION ALL
-- Net out planned orders, purchase orders and purchase requisitions /
-- bug 1303196
SELECT /*+ LEADING (I) */
I.plan_id,
I.sr_instance_id,
I.inventory_item_id,
P.supplier_id,
P.supplier_site_id,
DECODE(tps.shipping_control,'BUYER',p.new_ship_date,p.new_dock_date),
(NVL(p.implement_quantity,0) - p.new_order_quantity) sd_qty,
null demand_class,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM msc_supplies p,
-- msc_trading_partners tp,
-- msc_calendar_dates c,
-- msc_calendar_dates c1,
msc_trading_partner_sites tps,
msc_system_items I
WHERE I.plan_id = p_plan_id
AND I.atp_components_flag in ( 'Y', 'C')
AND (p.order_type IN (5, 2)
OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
AND p.order_type = 1 AND p.promised_date IS NULL))
AND p.plan_id = I.plan_id
AND p.sr_instance_id = I.sr_instance_id
AND p.inventory_item_id = I.inventory_item_id
AND p.organization_id = I.organization_id
AND p.sr_instance_id = I.sr_instance_id
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
AND p.supplier_id is not null
AND p.supplier_id = tps.partner_id (+)
AND p.supplier_site_id = tps.partner_site_id (+)
AND NOT EXISTS --Bug 3912422, Replaced 'NOT IN' by 'NOT EXISTS'
--AND (i.inventory_item_id, p.supplier_id, nvl(p.supplier_site_id,-1)) NOT IN
-- Bug 3912422
(SELECT 'x' -- summary is not supported with flex flences : summary enhancement
FROM msc_supplier_flex_fences msff
WHERE plan_id = p_plan_id
AND msff.inventory_item_id = p.inventory_item_id --\
AND msff.supplier_id = p.supplier_id -- } Bug 3912422
AND msff.supplier_site_id = p.supplier_site_id --/
AND rownum = 1)
/* AND tp.sr_tp_id = p.organization_id
AND tp.sr_instance_id = p.sr_instance_id
AND tp.partner_type = 3
AND c.calendar_date = trunc(p.new_schedule_date) -- 1529756
AND c.calendar_code = tp.calendar_code
AND c.exception_set_id = tp.calendar_exception_set_id
AND c.sr_instance_id = tp.sr_instance_id
AND c1.seq_num = c.prior_seq_num-
nvl(I.postprocessing_lead_time, 0)
AND c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.sr_instance_id = c.sr_instance_id*/
AND p.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
)
group by plan_id,inventory_item_id, supplier_id, supplier_site_id, sr_instance_id,
sd_date, demand_class, last_update_date, last_updated_by, creation_date, created_by
);
msc_util.msc_log('LOAD_SUP_DATA_FULL: ' || 'Records inserted : ' || SQL%ROWCOUNT);
SELECT I.sr_instance_id,
I.inventory_item_id,
P.supplier_id,
P.supplier_site_id,
DECODE(tps.shipping_control,'BUYER',p.new_ship_date,p.new_dock_date),
sum(NVL(p.implement_quantity,0) - p.new_order_quantity) sd_qty
FROM msc_supplies p,
-- msc_trading_partners tp,
-- msc_calendar_dates c,
-- msc_calendar_dates c1,
msc_trading_partner_sites tps,
msc_system_items I
WHERE I.plan_id = p_plan_id
AND I.atp_components_flag in ( 'Y', 'C')
AND (p.order_type IN (5, 2)
OR (MSC_ATP_REQ.G_PURCHASE_ORDER_PREFERENCE = MSC_ATP_REQ.G_PROMISE_DATE
AND p.order_type = 1 AND p.promised_date IS NULL))
AND p.plan_id = I.plan_id
AND p.sr_instance_id = I.sr_instance_id
AND p.inventory_item_id = I.inventory_item_id
AND p.organization_id = I.organization_id
AND p.sr_instance_id = I.sr_instance_id
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
AND p.supplier_id is not null
AND p.supplier_id = tps.partner_id (+)
AND p.supplier_site_id = tps.partner_site_id (+)
AND NOT EXISTS -- Bug 3912422, Replaced 'NOT IN' by 'NOT EXISTS'
--AND (i.inventory_item_id, p.supplier_id, nvl(p.supplier_site_id,-1)) NOT IN
-- Bug 3912422
(SELECT 'x' -- summary is not supported with flex flences : summary enhancement
FROM msc_supplier_flex_fences msff
WHERE plan_id = p_plan_id
AND msff.inventory_item_id = p.inventory_item_id --\
AND msff.supplier_id = p.supplier_id -- } Bug 3912422
AND msff.supplier_site_id = p.supplier_site_id --/
AND rownum = 1)
/* AND tp.sr_tp_id = p.organization_id
AND tp.sr_instance_id = p.sr_instance_id
AND tp.partner_type = 3
AND c.calendar_date = trunc(p.new_schedule_date)
AND c.calendar_code = tp.calendar_code
AND c.exception_set_id = tp.calendar_exception_set_id
AND c.sr_instance_id = tp.sr_instance_id
AND c1.seq_num = c.prior_seq_num-
nvl(I.postprocessing_lead_time, 0)
AND c1.calendar_code = c.calendar_code
AND c1.exception_set_id = c.exception_set_id
AND c1.sr_instance_id = c.sr_instance_id */
AND p.refresh_number between (p_last_refresh_number + 1) and p_new_refresh_number
GROUP BY I.inventory_item_id, P.supplier_id, P.supplier_site_id, I.sr_instance_id,
DECODE(tps.shipping_control,'BUYER',p.new_ship_date,p.new_dock_date);
UPDATE MSC_ATP_SUMMARY_SUP
SET sd_qty = sd_qty + l_sd_quantity_tab(j),
last_update_date = p_sys_date,
last_updated_by = l_user_id
WHERE plan_id = p_plan_id
AND sr_instance_id = l_sr_instance_id_tab(j)
AND inventory_item_id = l_inventory_item_id_tab(j)
AND supplier_id = l_supplier_id_tab(j)
AND supplier_site_id = l_supplier_site_id_tab(j)
AND sd_date = l_sd_date_tab(j);
msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL UPDATE');
INSERT INTO MSC_ATP_SUMMARY_SUP (
plan_id,
sr_instance_id,
inventory_item_id,
supplier_id,
supplier_site_id,
sd_date,
sd_qty,
demand_class,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (p_plan_id,
l_ins_sr_instance_id_tab(j),
l_ins_inventory_item_id_tab(j),
l_ins_supplier_id_tab(j),
l_ins_supplier_site_id_tab(j),
l_ins_sd_date_tab(j),
l_ins_sd_quantity_tab(j),
NULL,
p_sys_date,
l_user_id,
p_sys_date,
l_user_id);
msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'After FORALL INSERT');
msc_util.msc_log('LOAD_SUP_DATA_NET: ' || 'No records to be inserted');
INSERT INTO MSC_ATP_SUMMARY_RES(
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
SD_DATE,
SUM(SD_QTY),
last_update_date,
last_updated_by,
creation_date,
created_by
FROM
(SELECT RES_VIEW.plan_id plan_id,
RES_VIEW.department_id department_id,
RES_VIEW.resource_id resource_id,
RES_VIEW.organization_id organization_id,
RES_VIEW.sr_instance_id sr_instance_id,
trunc(RES_VIEW.SD_DATE) SD_DATE,
RES_VIEW.SD_QTY
* DECODE(RES_VIEW.BATCHABLE_FLAG, 0, 1, NVL(MUC.CONVERSION_RATE,1)) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_UOM_CONVERSIONS MUC,
(SELECT -- hint for better performance.
/*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) */
DR.PLAN_ID plan_id,
NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
DR.RESOURCE_ID resource_id,
DR.organization_id organization_id,
DR.SR_INSTANCE_ID sr_instance_id,
C.CALENDAR_DATE SD_DATE,
-- Bug 3321897, 2943979 For Line Based Resources,
-- Resource_ID is not NULL but -1
-1 * DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS)) *
DECODE(NVL(DR.BATCHABLE_FLAG,2), 1,
(DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, I.UNIT_VOLUME) *
NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY)), 1) SD_QTY,
NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_TRADING_PARTNERS P,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S,
---- re-ordered tables for performance
MSC_CALENDAR_DATES C
WHERE DR.PLAN_ID = REQ.PLAN_ID
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID
AND DR.RESOURCE_ID = REQ.RESOURCE_ID
AND DR.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND DR.organization_id = REQ.ORGANIZATION_ID
AND REQ.PLAN_ID = p_plan_id
AND NVL(REQ.PARENT_ID, 2) = 2
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_Id
AND I.PLAN_ID = S.PLAN_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND I.inventory_item_id = REQ.assembly_item_id ----\
AND ((I.bom_item_type <> 1 --|
and I.bom_item_type <> 2 --|- summary enhancement change for CTO ODR
AND I.atp_flag <> 'N') --|
OR (REQ.record_source = 2)) ----/
AND S.TRANSACTION_ID = REQ.SUPPLY_ID
AND S.PLAN_ID = REQ.PLAN_ID
AND S.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND S.ORGANIZATION_ID = REQ.ORGANIZATION_ID
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND P.SR_TP_ID = DR.ORGANIZATION_ID
AND P.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
AND P.PARTNER_TYPE = 3
AND C.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.CALENDAR_DATE BETWEEN TRUNC(REQ.START_DATE) AND
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE))
AND C.SEQ_NUM IS NOT NULL
AND C.CALENDAR_DATE >= p_plan_start_date -- summary enhancement - made consistent
AND REQ.REFRESH_NUMBER IS NULL)RES_VIEW -- consider only planning records in full summation - summary enhancement
WHERE RES_VIEW.UOM_CODE = MUC.UOM_CODE (+)
AND RES_VIEW.SR_INSTANCE_ID = MUC.SR_INSTANCE_ID (+)
AND MUC.INVENTORY_ITEM_ID (+)= 0
UNION ALL
SELECT MNRA.plan_id plan_id,
MNRA.department_id,
MNRA.resource_id,
MNRA.organization_id,
MNRA.sr_instance_id,
trunc(MNRA.SHIFT_DATE) SD_DATE,
MNRA.CAPACITY_UNITS * ((DECODE(LEAST(MNRA.from_time, MNRA.to_time),
MNRA.to_time,to_time + 24*3600,
MNRA.to_time) - MNRA.from_time)/3600)
* DECODE(NVL(DR.BATCHABLE_FLAG, 2), 1,
DR.MAX_CAPACITY * NVL(MUC.CONVERSION_RATE, 1), 1) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_NET_RESOURCE_AVAIL MNRA,
MSC_DEPARTMENT_RESOURCES DR,
MSC_UOM_CONVERSIONS MUC -- noted in summary enhancement : inconsistent with MSCRATPB
WHERE MNRA.PLAN_ID = p_plan_id
AND NVL(MNRA.PARENT_ID, -2) <> -1
AND DR.PLAN_ID = MNRA.PLAN_ID
AND DR.SR_INSTANCE_ID = MNRA.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = MNRA.ORGANIZATION_ID
AND DR.RESOURCE_ID = MNRA.RESOURCE_ID
AND DR.DEPARTMENT_ID = MNRA.DEPARTMENT_ID
AND DR.UNIT_OF_MEASURE = MUC.UOM_CODE (+)
AND DR.SR_INSTANCE_ID = MUC.SR_INSTANCE_ID (+)
AND MUC.INVENTORY_ITEM_ID (+) = 0
--- un commented the following row. This is done so that less number of rows are selected
AND SHIFT_DATE >= p_plan_start_date -- summary enhancement - made consistent
) group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
last_update_date,last_updated_by, creation_date, created_by
);
msc_util.msc_log('LOAD_RES_FULL_UNOPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_RES(
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
SD_DATE,
SUM(SD_QTY),
last_update_date,
last_updated_by,
creation_date,
created_by
FROM
(SELECT RES_VIEW.plan_id plan_id,
RES_VIEW.department_id department_id,
RES_VIEW.resource_id resource_id,
RES_VIEW.organization_id organization_id,
RES_VIEW.sr_instance_id sr_instance_id,
trunc(RES_VIEW.SD_DATE) SD_DATE,
RES_VIEW.SD_QTY
* DECODE(RES_VIEW.BATCHABLE_FLAG, 0, 1, NVL(MUC.CONVERSION_RATE,1)) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_UOM_CONVERSIONS MUC,
(SELECT -- hint for better performance.
/*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) */
DR.PLAN_ID plan_id,
NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
DR.RESOURCE_ID resource_id,
DR.organization_id organization_id,
DR.SR_INSTANCE_ID sr_instance_id,
TRUNC(REQ.START_DATE) SD_DATE,
-- Bug 3321897, 2943979 For Line Based Resources,
-- Resource_ID is not NULL but -1
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
REQ.RESOURCE_HOURS) * -- 2859130 -- noted in summary enhancement : inconsistent with MSCRATPB
-- DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- REQ.DAILY_RESOURCE_HOURS)) *
DECODE(NVL(DR.BATCHABLE_FLAG,2), 1,
(DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, I.UNIT_VOLUME) *
NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY)), 1) SD_QTY,
NVL(DR.BATCHABLE_FLAG,2) BATCHABLE_FLAG,
DECODE(DR.UOM_CLASS_TYPE,1 , I.WEIGHT_UOM, 2, I.VOLUME_UOM) UOM_CODE
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S
---- re-ordered tables for performance
WHERE DR.PLAN_ID = REQ.PLAN_ID
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID
AND DR.RESOURCE_ID = REQ.RESOURCE_ID
AND DR.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND DR.organization_id = REQ.ORGANIZATION_ID
AND REQ.PLAN_ID = p_plan_id
AND NVL(REQ.PARENT_ID, 1) = 1
AND I.SR_INSTANCE_ID = S.SR_INSTANCE_Id
AND I.PLAN_ID = S.PLAN_ID
AND I.ORGANIZATION_ID = S.ORGANIZATION_ID
AND I.INVENTORY_ITEM_ID = S.INVENTORY_ITEM_ID
AND S.TRANSACTION_ID = REQ.SUPPLY_ID
AND S.PLAN_ID = REQ.PLAN_ID
AND S.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND S.ORGANIZATION_ID = REQ.ORGANIZATION_ID
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND I.inventory_item_id = REQ.assembly_item_id ----\
AND ((I.bom_item_type <> 1 --|
and I.bom_item_type <> 2 --|- summary enhancement change for CTO ODR
AND I.atp_flag <> 'N') --|
OR (REQ.record_source = 2)) ----/
AND REQ.START_DATE >= p_plan_start_date -- summary enhancement - made consistent
AND REQ.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
)RES_VIEW
WHERE RES_VIEW.UOM_CODE = MUC.UOM_CODE (+)
AND RES_VIEW.SR_INSTANCE_ID = MUC.SR_INSTANCE_ID (+)
AND MUC.INVENTORY_ITEM_ID (+)= 0
UNION ALL
SELECT MNRA.plan_id plan_id,
MNRA.department_id,
MNRA.resource_id,
MNRA.organization_id,
MNRA.sr_instance_id,
trunc(MNRA.SHIFT_DATE) SD_DATE,
MNRA.CAPACITY_UNITS * ((DECODE(LEAST(MNRA.from_time, MNRA.to_time),
MNRA.to_time,to_time + 24*3600,
MNRA.to_time) - MNRA.from_time)/3600)
* DECODE(NVL(DR.BATCHABLE_FLAG, 2), 1,
DR.MAX_CAPACITY * NVL(MUC.CONVERSION_RATE, 1), 1) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_NET_RESOURCE_AVAIL MNRA,
MSC_DEPARTMENT_RESOURCES DR,
MSC_UOM_CONVERSIONS MUC -- noted in summary enhancement : inconsistent with MSCRATPB
WHERE MNRA.PLAN_ID = p_plan_id
AND NVL(MNRA.PARENT_ID, -2) <> -1
AND DR.PLAN_ID = MNRA.PLAN_ID
AND DR.SR_INSTANCE_ID = MNRA.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = MNRA.ORGANIZATION_ID
AND DR.RESOURCE_ID = MNRA.RESOURCE_ID
AND DR.DEPARTMENT_ID = MNRA.DEPARTMENT_ID
AND DR.UNIT_OF_MEASURE = MUC.UOM_CODE (+)
AND DR.SR_INSTANCE_ID = MUC.SR_INSTANCE_ID (+)
AND MUC.INVENTORY_ITEM_ID (+) = 0
--- un commented the following row. This is done so that less number of rows are selected
AND SHIFT_DATE >= p_plan_start_date -- summary enhancement - made consistent
)
group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
last_update_date,last_updated_by, creation_date, created_by
);
msc_util.msc_log('LOAD_RES_FULL_OPT_BATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_RES(
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
SD_DATE,
SUM(SD_QTY),
last_update_date,
last_updated_by,
creation_date,
created_by
FROM
(
SELECT /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) index(C MSC_CALENDAR_DATES_U1) */
DR.PLAN_ID plan_id,
NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
DR.RESOURCE_ID resource_id,
DR.organization_id organization_id,
DR.SR_INSTANCE_ID sr_instance_id,
C.CALENDAR_DATE SD_DATE, -- 2859130 remove trunc
-- Bug 3321897, 2943979 For Line Based Resources,
-- Resource_ID is not NULL but -1
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS)) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_TRADING_PARTNERS P,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_SYSTEM_ITEMS I, -- summary enhancement change for CTO ODR
MSC_CALENDAR_DATES C
---- re-ordered tables for performance
WHERE DR.PLAN_ID = REQ.PLAN_ID
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID -- summary enhancement - made consistent
AND DR.RESOURCE_ID = REQ.RESOURCE_ID
AND DR.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND DR.organization_id = REQ.ORGANIZATION_ID
AND REQ.PLAN_ID = p_plan_id
AND NVL(REQ.PARENT_ID, 2) = 2
AND P.SR_TP_ID = DR.ORGANIZATION_ID
AND P.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
AND P.PARTNER_TYPE = 3
AND C.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
AND C.CALENDAR_CODE = P.CALENDAR_CODE
AND C.EXCEPTION_SET_ID = P.CALENDAR_EXCEPTION_SET_ID
AND C.CALENDAR_DATE BETWEEN TRUNC(REQ.START_DATE) AND
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE))
AND C.SEQ_NUM IS NOT NULL
AND C.CALENDAR_DATE >= p_plan_start_date
AND I.SR_INSTANCE_ID = REQ.SR_INSTANCE_Id ----\
AND I.PLAN_ID = REQ.PLAN_ID --|
AND I.ORGANIZATION_ID = REQ.ORGANIZATION_ID --|
AND I.inventory_item_id = REQ.assembly_item_id --|\ summary enhancement
AND ((I.bom_item_type <> 1 --|/ change for CTO ODR
and I.bom_item_type <> 2 --|
AND I.atp_flag <> 'N') --|
OR (REQ.record_source = 2)) ----/
AND REQ.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
SELECT plan_id plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
trunc(SHIFT_DATE) SD_DATE,
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
-- uncommented following line so that less number of rows are selected
AND SHIFT_DATE >= p_plan_start_date -- summary enhancement - made consistent
)
group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
last_update_date,last_updated_by, creation_date, created_by
);
msc_util.msc_log('LOAD_RES_FULL_UNOPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
INSERT INTO MSC_ATP_SUMMARY_RES(
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
(SELECT plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
SD_DATE,
SUM(SD_QTY),
last_update_date,
last_updated_by,
creation_date,
created_by
FROM
(
SELECT /*+ ORDERED index(REQ MSC_RESOURCE_REQUIREMENTS_N2) index(C MSC_CALENDAR_DATES_U1) */
DR.PLAN_ID plan_id,
NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
DR.RESOURCE_ID resource_id,
DR.organization_id organization_id,
DR.SR_INSTANCE_ID sr_instance_id,
TRUNC(REQ.START_DATE) SD_DATE,
-- Bug 3321897, 2943979 For Line Based Resources,
-- Resource_ID is not NULL but -1
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
REQ.RESOURCE_HOURS) SD_QTY, --2859130
-- DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS, -- noted in summary enhancement : inconsistent with MSCRATPB
-- REQ.DAILY_RESOURCE_HOURS)) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_SYSTEM_ITEMS I -- summary enhancement change for CTO ODR
WHERE DR.PLAN_ID = REQ.PLAN_ID
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=REQ.DEPARTMENT_ID -- summary enhancement: made consistent
AND DR.RESOURCE_ID = REQ.RESOURCE_ID
AND DR.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID
AND DR.organization_id = REQ.ORGANIZATION_ID
AND REQ.PLAN_ID = p_plan_id
AND NVL(REQ.PARENT_ID, 1) = 1
AND I.SR_INSTANCE_ID = REQ.SR_INSTANCE_Id ----\
AND I.PLAN_ID = REQ.PLAN_ID --|
AND I.ORGANIZATION_ID = REQ.ORGANIZATION_ID --|
AND I.inventory_item_id = REQ.assembly_item_id --|\ summary enhancement
AND ((I.bom_item_type <> 1 --|/ change for CTO ODR
and I.bom_item_type <> 2 --|
AND I.atp_flag <> 'N') --|
OR (REQ.record_source = 2)) ----/
AND REQ.START_DATE >= p_plan_start_date -- summary enhancement: made consistent
AND REQ.REFRESH_NUMBER IS NULL -- consider only planning records in full summation - summary enhancement
UNION ALL
SELECT plan_id plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
trunc(SHIFT_DATE) SD_DATE,
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) SD_QTY,
p_sys_date last_update_date,
l_user_id last_updated_by,
p_sys_date creation_date,
l_user_id created_by
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SHIFT_DATE >= p_plan_start_date -- summary enhancement: made consistent
)
group by plan_id, department_id, resource_id, organization_id, sr_instance_id, sd_date,
last_update_date,last_updated_by, creation_date, created_by
);
msc_util.msc_log('LOAD_RES_FULL_OPT_NOBATCH: ' || 'Records inserted : ' || SQL%ROWCOUNT);
SELECT NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) department_id,
DR.RESOURCE_ID resource_id,
DR.organization_id organization_id,
DR.SR_INSTANCE_ID sr_instance_id,
-- Bug 3348095
-- Only ATP created records, so use end_date
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)) SD_DATE,
-- TRUNC(REQ.START_DATE) SD_DATE,
-- End Bug 3348095
SUM((-1) * REQ.RESOURCE_HOURS) SD_QTY -- ATP always populates resource_hours
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ
WHERE DR.PLAN_ID = p_plan_id
AND REQ.PLAN_ID = DR.PLAN_ID
AND REQ.SR_INSTANCE_ID = DR.sr_instance_id
AND REQ.RESOURCE_ID = DR.resource_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID) = DR.DEPARTMENT_ID
AND REQ.refresh_number between (p_last_refresh_number + 1) and p_new_refresh_number
GROUP BY DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID, DR.RESOURCE_ID, DR.organization_id, DR.SR_INSTANCE_ID, TRUNC(NVL(REQ.END_DATE, REQ.START_DATE));
UPDATE MSC_ATP_SUMMARY_RES
SET sd_qty = sd_qty + l_sd_quantity_tab(j),
last_update_date = p_sys_date,
last_updated_by = l_user_id
WHERE plan_id = p_plan_id
AND sr_instance_id = l_sr_instance_id_tab(j)
AND organization_id = l_organization_id_tab(j)
AND resource_id = l_resource_id_tab(j)
AND department_id = l_department_id_tab(j)
AND sd_date = l_sd_date_tab(j);
msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL UPDATE');
INSERT INTO MSC_ATP_SUMMARY_RES (
plan_id,
department_id,
resource_id,
organization_id,
sr_instance_id,
sd_date,
sd_qty,
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (p_plan_id,
l_ins_department_id_tab(j),
l_ins_resource_id_tab(j),
l_ins_organization_id_tab(j),
l_ins_sr_instance_id_tab(j),
l_ins_sd_date_tab(j),
l_ins_sd_quantity_tab(j),
p_sys_date,
l_user_id,
p_sys_date,
l_user_id);
msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'After FORALL INSERT');
msc_util.msc_log('LOAD_RES_DATA_NET: ' || 'No records to be inserted');
SELECT a.oracle_username
INTO l_msc_schema
FROM FND_ORACLE_USERID a,
FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
SELECT count(*)
INTO l_count
FROM msc_plans plans,
msc_designators desig
WHERE plans.plan_id = p_plan_id
AND plans.plan_type <> 4
AND plans.compile_designator = desig.designator
AND plans.sr_instance_id = desig.sr_instance_id
AND plans.organization_id = desig.organization_id
AND (desig.inventory_atp_flag = 1
OR plans.copy_plan_id IS NOT NULL);
UPDATE msc_plan_organizations mpo
SET so_lrn =(SELECT so_lrn
FROM msc_instance_orgs mio
WHERE mio.sr_instance_id=mpo.sr_instance_id
AND mio.organization_id=mpo.organization_id
)
WHERE plan_id=p_plan_id;
msc_util.msc_log('atp_snapshot_hook: No. of Rows updated: '|| SQL%ROWCOUNT );
UPDATE msc_system_items mst1
SET (REPLENISH_TO_ORDER_FLAG,PICK_COMPONENTS_FLAG) =(SELECT REPLENISH_TO_ORDER_FLAG,PICK_COMPONENTS_FLAG
FROM msc_system_items mst2
WHERE mst2.sr_instance_id=mst1.sr_instance_id
AND mst2.organization_id=mst1.organization_id
AND mst2.INVENTORY_ITEM_ID=mst1.INVENTORY_ITEM_ID
AND mst2.plan_id=-1
)
WHERE plan_id=p_plan_id;
UPDATE msc_system_items mst1
SET (REPLENISH_TO_ORDER_FLAG,PICK_COMPONENTS_FLAG,ATP_RULE_ID,DEMAND_TIME_FENCE_DAYS) =(SELECT REPLENISH_TO_ORDER_FLAG,PICK_COMPONENTS_FLAG,ATP_RULE_ID,DEMAND_TIME_FENCE_DAYS
FROM msc_system_items mst2
WHERE mst2.sr_instance_id=mst1.sr_instance_id
AND mst2.organization_id=mst1.organization_id
AND mst2.INVENTORY_ITEM_ID=mst1.INVENTORY_ITEM_ID
AND mst2.plan_id=-1
)
WHERE plan_id=p_plan_id
AND mst1.ORGANIZATION_ID = l_organization_id(j)
AND mst1.SR_INSTANCE_ID = l_sr_instance_id(j)
AND mst1.bom_item_type in (1,4,5)
AND mst1.atp_flag <> 'N'
OR mst1.atp_components_flag <> 'N';
Procedure Delete_CTO_BOM_OSS(
p_plan_id IN NUMBER)
IS
BEGIN
msc_util.msc_log('Begin Delete_CTO_BOM_OSS for plan_id: ' || p_plan_id);
msc_util.msc_log('Before Delete data for CTO BOM');
DELETE msc_cto_bom
WHERE nvl(plan_id, p_plan_id) = p_plan_id;
msc_util.msc_log('After Delete data for CTO BOM: ' || SQL%ROWCOUNT);
msc_util.msc_log('Before Delete data for CTO OSS');
DELETE msc_cto_sources
WHERE nvl(plan_id, p_plan_id) = p_plan_id;
msc_util.msc_log('After Delete data for CTO OSS: ' || SQL%ROWCOUNT);
msc_util.msc_log('End Delete_CTO_BOM_OSS');
msc_util.msc_log('Exception in Delete_CTO_BOM_OSS :' || SQLCODE || '-' || SQLERRM);
END Delete_CTO_BOM_OSS;