[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM
(
SELECT -- 2859130
--C.CALENDAR_DATE SD_DATE,
-- Bug 3348095
-- For ATP created records use end_date otherwise start_date
DECODE(REQ.record_source, 2, TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)) ,
TRUNC(REQ.START_DATE)) SD_DATE,
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS))) SD_QTY
-- For ATP created records use resource_hours
-- End Bug 3348095
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
-- Add Link to Items
MSC_SYSTEM_ITEMS I
-- 2859130
-- MSC_CALENDAR_DATES C
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, 1) = 1 -- parent_id is 1 for constrained plans. Bug 2809639
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
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
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
-- 2859130
--AND C.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
--AND C.CALENDAR_CODE = p_cal_code
--AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
--AND C.CALENDAR_DATE = TRUNC(REQ.START_DATE) -- Bug 2809639
-- AND C.SEQ_NUM IS NOT NULL -- 2859130
--bug 2341075: chnage sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
--AND C.CALENDAR_DATE >= p_plan_start_date
--bug3693892 added trunc
AND TRUNC(REQ.START_DATE) >= p_plan_start_date
-- 2859130
AND TRUNC(REQ.START_DATE) < trunc(nvl(p_itf,REQ.START_DATE+1))--4135752
UNION ALL
SELECT trunc(SHIFT_DATE) SD_DATE,--4135752
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) SD_QTY
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
AND DEPARTMENT_ID = p_dept_id
--bug 2341075: chnage sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130--4135752
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM
(
SELECT C.CALENDAR_DATE SD_DATE,
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- Bug 3348095
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS))) SD_QTY
-- For ATP created records use resource_hours
-- End Bug 3348095
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
-- Add Link to Items
MSC_SYSTEM_ITEMS I,
MSC_CALENDAR_DATES C
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
-- End Bug 2675504, 2665805
AND NVL(REQ.PARENT_ID, MSC_ATP_PVT.G_OPTIMIZED_PLAN) = MSC_ATP_PVT.G_OPTIMIZED_PLAN
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
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
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
AND C.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID --bug3394866
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
-- Bug 3348095
-- Ensure that the ATP created resource Reqs
-- do not get double counted.
AND C.CALENDAR_DATE BETWEEN DECODE(REQ.record_source, 2,
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)), TRUNC(REQ.START_DATE))
AND TRUNC(NVL(REQ.END_DATE, REQ.START_DATE))
-- End Bug 3348095
AND C.SEQ_NUM IS NOT NULL
--bug 2341075: chnage sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
AND C.CALENDAR_DATE >= p_plan_start_date
-- 2859130
AND C.CALENDAR_DATE < NVL(p_itf, C.CALENDAR_DATE+1)
UNION ALL
SELECT trunc(SHIFT_DATE) SD_DATE,--4135752
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) SD_QTY
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
AND DEPARTMENT_ID = p_dept_id
--bug 2341075: chnage sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date
AND trunc(SHIFT_DATE) < nvl(p_itf, SHIFT_DATE+1) -- 2859130
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM
(
SELECT -- 2859130 C.CALENDAR_DATE SD_DATE,
-- Bug 3348095
-- For ATP created records use end_date otherwise start_date
DECODE(REQ.record_source, 2, TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)),
TRUNC(REQ.START_DATE)) SD_DATE,
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- Bug 3348095
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS)))
-- For ATP created records use resource_hours
-- End Bug 3348095
*
DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, UNIT_VOLUME) *
NVL(MUC.CONVERSION_RATE,1) * NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY) SD_QTY
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
-- 2859130 MSC_CALENDAR_DATES C,
--- add table for resource batching
--- these tables are added to determine how much apacity has already been consumed by the
--- existing supplies
MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S,
MSC_UOM_CONVERSIONS MUC
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, 1) = 1 -- parent_id is 1 for constrained plans. Bug 2809639
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 DECODE(p_uom_type, 1, I.WEIGHT_UOM, 2 , I.VOLUME_UOM) = MUC.UOM_CODE (+)
AND MUC.SR_INSTANCE_ID (+) = I.SR_INSTANCE_ID
AND MUC.INVENTORY_ITEM_ID (+)= 0
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
-- Exclude Cancelled Supplies 2460645
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
AND I.inventory_item_id = REQ.assembly_item_id
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
-- 2859130
--AND C.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
--AND C.CALENDAR_CODE = p_cal_code
--AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
--AND C.CALENDAR_DATE = TRUNC(REQ.START_DATE) -- Bug 2809639
-- AND C.SEQ_NUM IS NOT NULL -- 2859130
---bug 2341075: change sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
--AND C.CALENDAR_DATE >= p_plan_start_date
--bug3693892 added trunc
AND TRUNC(REQ.START_DATE) >= p_plan_start_date
-- 2859130
AND TRUNC(REQ.START_DATE) < trunc(nvl(p_itf, REQ.START_DATE+1)) --4135752
UNION ALL
SELECT trunc(SHIFT_DATE) SD_DATE,--4135752
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600)* p_max_capacity * p_res_conv_rate SD_QTY
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
AND DEPARTMENT_ID = p_dept_id
-- krajan : 2408696 -- agilent
AND organization_id = p_org_id
---bug 2341075: change sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130 --4135752
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM
(
SELECT C.CALENDAR_DATE SD_DATE,
-1*DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- Bug 3348095
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS)))
-- For ATP created records use resource_hours
-- End Bug 3348095
*
DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, UNIT_VOLUME) *
NVL(MUC.CONVERSION_RATE,1) * NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY) SD_QTY
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_CALENDAR_DATES C,
--- add table for resource batching
--- these tables are added to determine how much apacity has already been consumed by the
--- existing supplies
MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S,
MSC_UOM_CONVERSIONS MUC
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, MSC_ATP_PVT.G_OPTIMIZED_PLAN) = MSC_ATP_PVT.G_OPTIMIZED_PLAN
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 DECODE(p_uom_type, 1, I.WEIGHT_UOM, 2 , I.VOLUME_UOM) = MUC.UOM_CODE (+)
AND MUC.SR_INSTANCE_ID (+) = I.SR_INSTANCE_ID
AND MUC.INVENTORY_ITEM_ID (+)= 0
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
-- Exclude Cancelled Supplies 2460645
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
AND I.inventory_item_id = REQ.assembly_item_id
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
AND C.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID --bug3394866
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
-- Bug 3348095
-- Ensure that the ATP created resource Reqs
-- do not get double counted.
AND C.CALENDAR_DATE BETWEEN DECODE(REQ.record_source, 2,
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)), TRUNC(REQ.START_DATE))
AND TRUNC(NVL(REQ.END_DATE, REQ.START_DATE))
-- End Bug 3348095
AND C.SEQ_NUM IS NOT NULL
---bug 2341075: change sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
AND C.CALENDAR_DATE >= p_plan_start_date
-- 2859130
AND C.CALENDAR_DATE < NVL(p_itf, C.CALENDAR_DATE+1)
UNION ALL
SELECT trunc(SHIFT_DATE) SD_DATE, --4135752
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600)* p_max_capacity * p_res_conv_rate SD_QTY
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
AND DEPARTMENT_ID = p_dept_id
-- krajan : 2408696 -- agilent
AND organization_id = p_org_id
---bug 2341075: change sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130 --4135752
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
(SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
1 col17, -- demand
MSC_ATP_FUNC.Get_Order_Type(REQ.SUPPLY_ID, p_plan_id) col18,
l_null_char col19,
-- L.MEANING col19 ,
REQ.SR_INSTANCE_ID col20,
l_null_num col21,
REQ.TRANSACTION_ID col22,
l_null_num col23,
-- Bug 3348095
-1* DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- For ATP created records use resource_hours
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS))) col24,
-- C.CALENDAR_DATE col25, -- 2859130
-- For ATP created records use end_date otherwise start_date
DECODE(REQ.record_source, 2, TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)),
TRUNC(REQ.START_DATE)) col25,
-- End Bug 3348095
l_null_num col26,
MSC_ATP_FUNC.Get_Order_Number(REQ.SUPPLY_ID, p_plan_id) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
-- CTO Option Dependent Resources
-- Option Dependent Resources Capacity Check
-- Add Link to Items
MSC_SYSTEM_ITEMS I
-- 2859130 MSC_CALENDAR_DATES C
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, 1) = 1 -- parent_id is 1 for constrained plans. Bug 2809639
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
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
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
-- 2859130
--AND C.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
--AND C.CALENDAR_CODE = p_cal_code
--AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
--AND C.CALENDAR_DATE = TRUNC(REQ.START_DATE) -- Bug 2809639
-- AND C.SEQ_NUM IS NOT NULL -- 2859130
---bug 2341075: change sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
--AND C.CALENDAR_DATE >= p_plan_start_date
--bug3693892 added trunc
AND TRUNC(REQ.START_DATE) >= p_plan_start_date
-- 2859130
AND TRUNC(REQ.START_DATE) < nvl(p_itf, REQ.START_DATE+1)
UNION ALL
SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
2 col17, -- supply
l_null_num col18,
l_null_char col19,
-- L.MEANING col19 ,
SR_INSTANCE_ID col20,
l_null_num col21,
TRANSACTION_ID col22,
l_null_num col23,
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) col24,
trunc(SHIFT_DATE) col25, --4135752
l_null_num col26,
l_null_char col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
-- 2408696 : krajan
AND organization_id = p_org_id
AND DEPARTMENT_ID = p_dept_id
---bug 2341075: change sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130 --4135752
);
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
(SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
1 col17, -- demand
MSC_ATP_FUNC.Get_Order_Type(REQ.SUPPLY_ID, p_plan_id) col18,
l_null_char col19,
-- L.MEANING col19 ,
REQ.SR_INSTANCE_ID col20,
l_null_num col21,
REQ.TRANSACTION_ID col22,
l_null_num col23,
-- Bug 3348095
-1* DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- For ATP created records use resource_hours
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS))) col24,
-- End Bug 3348095
C.CALENDAR_DATE col25,
l_null_num col26,
MSC_ATP_FUNC.Get_Order_Number(REQ.SUPPLY_ID, p_plan_id) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
-- CTO Option Dependent Resources
-- Option Dependent Resources Capacity Check
-- Add Link to Items
MSC_SYSTEM_ITEMS I,
MSC_CALENDAR_DATES C
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, MSC_ATP_PVT.G_OPTIMIZED_PLAN) = MSC_ATP_PVT.G_OPTIMIZED_PLAN
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
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
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
AND C.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID --bug3394866
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
-- Bug 3348095
-- Ensure that the ATP created resource Reqs
-- do not get double counted.
AND C.CALENDAR_DATE BETWEEN DECODE(REQ.record_source, 2,
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)), TRUNC(REQ.START_DATE))
AND TRUNC(NVL(REQ.END_DATE, REQ.START_DATE))
-- End Bug 3348095
AND C.SEQ_NUM IS NOT NULL
---bug 2341075: change sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
AND C.CALENDAR_DATE >= p_plan_start_date
-- 2859130
AND C.CALENDAR_DATE < NVL(p_itf, C.CALENDAR_DATE+1)
UNION ALL
SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
2 col17, -- supply
l_null_num col18,
l_null_char col19,
-- L.MEANING col19 ,
SR_INSTANCE_ID col20,
l_null_num col21,
TRANSACTION_ID col22,
l_null_num col23,
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) col24,
trunc(SHIFT_DATE) col25, --4135752
l_null_num col26,
l_null_char col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
-- 2408696 : krajan
AND organization_id = p_org_id
AND DEPARTMENT_ID = p_dept_id
---bug 2341075: change sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130 --4135752
);
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
(SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
1 col17, -- demand
MSC_ATP_FUNC.Get_Order_Type(REQ.SUPPLY_ID, p_plan_id) col18,
l_null_char col19,
-- L.MEANING col19 ,
REQ.SR_INSTANCE_ID col20,
l_null_num col21,
REQ.TRANSACTION_ID col22,
l_null_num col23,
-1* DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE, -- 2859130 remove daily_resource_hours
REQ.RESOURCE_HOURS) *
DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, UNIT_VOLUME)
* NVL(MUC.CONVERSION_RATE, 1) * NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY) col24,
-- 2859130 C.CALENDAR_DATE col25,
-- Bug 3348095
-- For ATP created records use end_date otherwise start_date
DECODE(REQ.record_source, 2, TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)),
TRUNC(REQ.START_DATE)) col25,
-- End Bug 3348095
l_null_num col26,
MSC_ATP_FUNC.Get_Order_Number(REQ.SUPPLY_ID, p_plan_id) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
-- 2859130 MSC_CALENDAR_DATES C,
---tables added for resource batching
MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S,
MSC_UOM_CONVERSIONS MUC
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, 1) = 1 -- parent_id is 1 for constrained plans. Bug 2809639
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 DECODE(p_uom_type, 1, I.WEIGHT_UOM, 2 , I.VOLUME_UOM) = MUC.UOM_CODE (+)
AND MUC.SR_INSTANCE_ID (+) = I.SR_INSTANCE_ID
AND MUC.INVENTORY_ITEM_ID (+) = 0
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
-- Exclude Cancelled Supplies 2460645
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
AND I.inventory_item_id = REQ.assembly_item_id
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
-- 2859130
--AND C.SR_INSTANCE_ID = DR.SR_INSTANCE_ID
--AND C.CALENDAR_CODE = p_cal_code
--AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
--AND C.CALENDAR_DATE = TRUNC(REQ.START_DATE) -- Bug 2809639
-- AND C.SEQ_NUM IS NOT NULL -- 2859130
---bug 2341075: change sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
-- AND C.CALENDAR_DATE >= p_plan_start_date
--bug3693892 added trunc
AND TRUNC(REQ.START_DATE) >= p_plan_start_date
-- 2859130
AND TRUNC(REQ.START_DATE) < trunc(nvl(p_itf, REQ.START_DATE+1)) --4135752
UNION ALL
SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
2 col17, -- supply
l_null_num col18,
l_null_char col19,
-- L.MEANING col19 ,
SR_INSTANCE_ID col20,
l_null_num col21,
TRANSACTION_ID col22,
l_null_num col23,
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) * p_max_capacity * p_res_conv_rate col24,
trunc(SHIFT_DATE) col25, --4135752
l_null_num col26,
l_null_char col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
-- 2408696 : krajan agilent
AND organization_id = p_org_id
AND DEPARTMENT_ID = p_dept_id
---bug 2341075: chnage sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130 --4135752
);
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
(SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
1 col17, -- demand
MSC_ATP_FUNC.Get_Order_Type(REQ.SUPPLY_ID, p_plan_id) col18,
l_null_char col19,
-- L.MEANING col19 ,
REQ.SR_INSTANCE_ID col20,
l_null_num col21,
REQ.TRANSACTION_ID col22,
l_null_num col23,
-- Bug 3348095
-1* DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- For ATP created records use resource_hours
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS)))
-- End Bug 3348095
*
DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, UNIT_VOLUME)
* NVL(MUC.CONVERSION_RATE, 1) * NVL(S.NEW_ORDER_QUANTITY, S.FIRM_QUANTITY) col24,
C.CALENDAR_DATE col25,
l_null_num col26,
MSC_ATP_FUNC.Get_Order_Number(REQ.SUPPLY_ID, p_plan_id) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_CALENDAR_DATES C,
---tables added for resource batching
MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S,
MSC_UOM_CONVERSIONS MUC
-- Bug 2675504, 2665805,
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
-- krajan: 2408696 --
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
-- End Bug 2675504, 2665805,
AND NVL(REQ.PARENT_ID, MSC_ATP_PVT.G_OPTIMIZED_PLAN) = MSC_ATP_PVT.G_OPTIMIZED_PLAN
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 DECODE(p_uom_type, 1, I.WEIGHT_UOM, 2 , I.VOLUME_UOM) = MUC.UOM_CODE (+)
AND MUC.SR_INSTANCE_ID (+) = I.SR_INSTANCE_ID
AND MUC.INVENTORY_ITEM_ID (+) = 0
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
-- Exclude Cancelled Supplies 2460645
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
-- CTO Option Dependent Resources ODR
-- Option Dependent Resources Capacity Check
AND I.inventory_item_id = REQ.assembly_item_id
AND ((I.bom_item_type <> 1 and I.bom_item_type <> 2)
-- bom_item_type not model and option_class always committed.
AND (I.atp_flag <> 'N')
-- atp_flag is 'Y' then committed.
OR (REQ.record_source = 2) ) -- this OR may be changed during performance analysis.
-- if record created by ATP then committed.
-- End CTO Option Dependent Resources ODR
AND C.SR_INSTANCE_ID = REQ.SR_INSTANCE_ID --bug3394866
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
-- Bug 3348095
-- Ensure that the ATP created resource Reqs
-- do not get double counted.
AND C.CALENDAR_DATE BETWEEN DECODE(REQ.record_source, 2,
TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)), TRUNC(REQ.START_DATE))
AND TRUNC(NVL(REQ.END_DATE, REQ.START_DATE))
-- End Bug 3348095
AND C.SEQ_NUM IS NOT NULL
---bug 2341075: change sysdate to plan_start_date
--AND C.CALENDAR_DATE >= trunc(sysdate)
AND C.CALENDAR_DATE >= trunc(p_plan_start_date) --4135752
-- 2859130
AND C.CALENDAR_DATE < NVL(p_itf, C.CALENDAR_DATE+1)
UNION ALL
SELECT
p_level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_org_id col6,
p_dept_id col7,
p_res_id col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
p_uom_code col16,
2 col17, -- supply
l_null_num col18,
l_null_char col19,
-- L.MEANING col19 ,
SR_INSTANCE_ID col20,
l_null_num col21,
TRANSACTION_ID col22,
l_null_num col23,
CAPACITY_UNITS * ((DECODE(LEAST(from_time, to_time),
to_time,to_time + 24*3600,
to_time) - from_time)/3600) * p_max_capacity * p_res_conv_rate col24,
trunc(SHIFT_DATE) col25, --4135752
l_null_num col26,
l_null_char col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM MSC_NET_RESOURCE_AVAIL
WHERE PLAN_ID = p_plan_id
AND NVL(PARENT_ID, -2) <> -1
AND SR_INSTANCE_ID = p_instance_id
AND RESOURCE_ID = p_res_id
-- 2408696 : krajan agilent
AND organization_id = p_org_id
AND DEPARTMENT_ID = p_dept_id
---bug 2341075: chnage sysdate to plan_start_date
--AND SHIFT_DATE >= trunc(sysdate)
AND trunc(SHIFT_DATE) >= p_plan_start_date --4135752
AND trunc(SHIFT_DATE) < trunc(nvl(p_itf, SHIFT_DATE+1)) -- 2859130 --4135752
);
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM
(
select /*+ INDEX(r MSC_ATP_SUMMARY_RES_U1) */
trunc(r.sd_date) SD_DATE, --4135752
r.sd_qty
from msc_atp_summary_res r
where r.plan_id = p_plan_id and
r.sr_instance_id = p_instance_id and
r.organization_id = p_org_id and
r.department_id = p_dept_id and
r.resource_id = p_res_id and
---bug 2341075: change sysdate to plan start date
--sd_date >= trunc(sysdate) and
sd_date >= p_plan_start_date and --4135752
sd_date < trunc(nvl(p_itf, sd_date + 1)) and -- 2859130 --4135752
sd_qty <> 0
UNION ALL
-- Summary enhancement : differences from non summary SQL:
-- 1. No union with MSC_NET_RES_AVAIL
-- 2. Get the hours always from RESOURCE_HOURS - never from LOAD_RATE or DAILY_RESOURCE_HOURS
-- 3. PARENT_ID removed from where clause. No difference between constrained and unconstrained plans
-- 4. MSC_SYSTEM_ITEMS not included in the join because the filters on items is not applied for ATP records
-- 5. MSC_PLANS included in the join to get latest refresh number
-- 6. Filter records based on refresh_number
-- Bug 3348095
-- For ATP created records use end_date otherwise start_date
SELECT TRUNC(NVL(REQ.END_DATE, REQ.START_DATE)) SD_DATE,
-- End Bug 3348095
-1 * REQ.RESOURCE_HOURS SD_QTY -- Summary enhancement: Need to bother only about ATP generated records
FROM MSC_DEPARTMENT_RESOURCES DR,
MSC_RESOURCE_REQUIREMENTS REQ,
MSC_PLANS P -- For summary enhancement
--bug3394866
WHERE DR.PLAN_ID = p_plan_id
AND NVL(DR.OWNING_DEPARTMENT_ID, DR.DEPARTMENT_ID)=p_dept_id
AND DR.RESOURCE_ID = p_res_id
AND DR.SR_INSTANCE_ID = p_instance_id
AND DR.organization_id = p_org_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 REQ.DEPARTMENT_ID = DR.DEPARTMENT_ID
AND REQ.ORGANIZATION_ID = DR.ORGANIZATION_ID
--bug3394866
--bug3693892 added trunc
AND TRUNC(REQ.START_DATE) >= p_plan_start_date --4135752
AND TRUNC(REQ.START_DATE) < trunc(nvl(p_itf, REQ.START_DATE+1))
AND P.PLAN_ID = REQ.PLAN_ID
AND (REQ.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR REQ.REFRESH_NUMBER = p_refresh_number)
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
p_insert_flag IN NUMBER,
p_batching_flag IN NUMBER,
p_optimized_flag IN NUMBER,
p_instance_id IN NUMBER,
p_org_id IN NUMBER,
p_plan_id IN NUMBER,
p_plan_start_date IN DATE,
p_dept_id IN NUMBER,
p_res_id IN NUMBER,
p_itf IN DATE,
p_uom_type IN NUMBER,
p_uom_code IN VARCHAR2,
p_max_capacity IN NUMBER,
p_res_conv_rate IN NUMBER,
p_level IN NUMBER,
p_scenario_id IN NUMBER,
p_item_id IN NUMBER,
p_cal_code IN VARCHAR2,
p_cal_exc_set_id IN NUMBER,
p_summary_flag IN VARCHAR2, -- For summary enhancement
p_refresh_number IN NUMBER, -- For summary enhancement
x_atp_dates OUT NoCopy MRP_ATP_PUB.date_arr,
x_atp_qtys OUT NoCopy MRP_ATP_PUB.number_arr,
x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ
) IS
BEGIN
IF nvl(p_insert_flag,0) = 1 THEN
get_res_avail_dtls(
p_batching_flag,
p_optimized_flag,
p_instance_id,
p_org_id,
p_plan_id,
p_plan_start_date,
p_dept_id,
p_res_id,
p_itf,
p_uom_type,
p_uom_code,
p_max_capacity,
p_res_conv_rate,
p_level,
p_scenario_id,
p_item_id,
p_cal_code,
p_cal_exc_set_id,
x_atp_period
);
SELECT SD_DATE, sum(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM
(SELECT /*+ INDEX(D MSC_ATP_SUMMARY_SO_U1) */
trunc(D.SD_DATE) SD_DATE, --4135752
-1* D.SD_QTY SD_QTY
FROM MSC_ATP_SUMMARY_SO D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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 trunc(D.SD_DATE) < trunc(NVL(p_itf, --4135752
D.SD_DATE + 1))
AND NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
DECODE(R.DEMAND_CLASS_ATP_FLAG,
1, NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')),
NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')))
AND D.SD_QTY <> 0
UNION ALL
SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
trunc(S.SD_DATE) SD_DATE, --4135752
S.SD_QTY SD_QTY
FROM MSC_ATP_SUMMARY_SD S,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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
AND trunc(S.SD_DATE) < trunc(NVL(p_itf, S.SD_DATE + 1)) --4135752
AND NVL(S.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
DECODE(R.DEMAND_CLASS_ATP_FLAG,
1, NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')),
NVL(S.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')))
AND S.SD_QTY <> 0
)
group by SD_DATE
order by SD_DATE;--4698199
SELECT SD_DATE, SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM (
SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
SD_DATE, SD_QTY
FROM MSC_ATP_SUMMARY_SD S,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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 trunc(S.SD_DATE) < trunc(NVL(p_itf, S.SD_DATE + 1)) --4135752
UNION ALL
SELECT TRUNC(NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)) SD_DATE,--plan by request,promise,schedule date
decode(D.USING_REQUIREMENT_QUANTITY, -- Consider unscheduled orders as dummy supplies
0, nvl(D.OLD_DEMAND_QUANTITY,0), --4658238 -- For summary enhancement
-1 * D.USING_REQUIREMENT_QUANTITY) SD_QTY
FROM MSC_DEMANDS D,
MSC_SYSTEM_ITEMS I,
MSC_PLANS P -- For summary enhancement
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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 (4,5,7,8,9,11,15,22,28,29,31)
AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
--bug3693892 added trunc
AND trunc(NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)) <
trunc(NVL(p_itf, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE) + 1))
--plan by requestdate,promisedate,scheduledate
AND P.PLAN_ID = I.PLAN_ID
AND (D.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR D.REFRESH_NUMBER = p_refresh_number)
UNION ALL
SELECT TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
FROM MSC_SUPPLIES S,
MSC_SYSTEM_ITEMS I,
MSC_PLANS P -- For summary enhancement
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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 TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1)
AND P.PLAN_ID = I.PLAN_ID
AND (S.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR S.REFRESH_NUMBER = p_refresh_number)
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
SELECT SD_DATE, SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM (
SELECT C.PRIOR_DATE SD_DATE,
-1* D.USING_REQUIREMENT_QUANTITY SD_QTY
FROM MSC_CALENDAR_DATES C,
MSC_DEMANDS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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 = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
AND C.SR_INSTANCE_ID = I.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.
AND C.CALENDAR_DATE BETWEEN TRUNC(D.USING_ASSEMBLY_DEMAND_DATE) AND
TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
D.USING_ASSEMBLY_DEMAND_DATE))
-- new clause 2640489, DECODE is also OR, Explicit OR gives CBO choices
AND (R.PAST_DUE_DEMAND_CUTOFF_FENCE is NULL OR
C.PRIOR_SEQ_NUM >= p_sysdate_seq_num - R.PAST_DUE_DEMAND_CUTOFF_FENCE)
-- AND C.PRIOR_SEQ_NUM >= DECODE(R.PAST_DUE_DEMAND_CUTOFF_FENCE,
-- NULL, C.PRIOR_SEQ_NUM,
-- p_sysdate_seq_num - NVL(R.PAST_DUE_DEMAND_CUTOFF_FENCE,0))
AND C.PRIOR_DATE < NVL(p_itf,
C.PRIOR_DATE + 1)
-- new clause 2640489, DECODE is also OR, Explicit OR gives CBO choices
AND (R.DEMAND_CLASS_ATP_FLAG <> 1 OR
NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) )
-- AND NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
-- DECODE(R.DEMAND_CLASS_ATP_FLAG,
-- 1, NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')),
-- NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')))
UNION ALL
-- bug 2461071 to_date and trunc
SELECT DECODE(D.RESERVATION_TYPE, 2, p_sys_next_date, -- to_date removed to avoid GSCC error
TRUNC(D.REQUIREMENT_DATE)) SD_DATE, --2287148
-1*(D.PRIMARY_UOM_QUANTITY-GREATEST(NVL(D.RESERVATION_QUANTITY,0),
D.COMPLETED_QUANTITY)) SD_QTY
FROM
-- Bug 1756263, performance fix, use EXISTS subquery instead.
--MSC_CALENDAR_DATES C,
MSC_SALES_ORDERS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_CALENDAR_DATES C
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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 DECODE(MSC_ATP_PVT.G_APPS_VER,3,D.COMPLETED_QUANTITY,0) = 0 -- 2300767
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))))
-- new clause, remove existing Exists Query 2640489
AND (R.PAST_DUE_DEMAND_CUTOFF_FENCE is NULL OR
C.PRIOR_SEQ_NUM >= p_sysdate_seq_num - R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.CALENDAR_CODE = p_cal_code
AND C.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C.EXCEPTION_SET_ID = -1
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
AND C.PRIOR_DATE < NVL(p_itf, C.PRIOR_DATE + 1)
-- new clause 2640489, DECODE is also OR, Explicit OR gives CBO choices
AND (R.DEMAND_CLASS_ATP_FLAG <> 1 OR
NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) )
UNION ALL
SELECT C.NEXT_DATE SD_DATE,
Decode(order_type,
30, Decode(Sign(S.Daily_rate * (TRUNC(C.Calendar_date) - TRUNC(S.FIRST_UNIT_START_DATE))- S.qty_completed),
-1,S.Daily_rate* (TRUNC(C.Calendar_date) - TRUNC(S.First_Unit_Start_date) +1)- S.qty_completed,
S.Daily_rate),
5, NVL(S.DAILY_RATE, NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)),
(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) - NVL(S.NON_NETTABLE_QTY, 0)) )SD_QTY
FROM MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_SUB_INVENTORIES MSI
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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
--AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0 -- 1243985
---bug 1843471, 2563139
-- Bug 2132288, 2442009, 2453938
-- Do not include supplies equal to 0 as per 1243985
-- However at the same time, support negative supplies as per Bug 2362079 use ABS.
-- Support Repetitive schedules as per 1843471
-- Support Repetitive MPS as per 2132288, 2442009
AND Decode(S.order_type, 30, S.Daily_rate* (TRUNC(C.Calendar_date) - TRUNC(S.First_Unit_Start_date) + 1),
5, NVL(S.Daily_rate, ABS(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) ),
ABS(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) ) >
Decode(S.order_type, 30, S.qty_completed,0)
-- End Bug 2132288, 2442009, 2453938
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_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
(INCLUDE_INTERNAL_REQS = 1 AND S.ORDER_TYPE = 2 AND
S.SOURCE_ORGANIZATION_ID IS NOT NULL)
OR
(INCLUDE_SUPPLIER_REQS = 1 AND S.ORDER_TYPE = 2 AND
S.SOURCE_ORGANIZATION_ID IS NULL)
OR
((R.INCLUDE_REP_MPS = 1 OR R.INCLUDE_DISCRETE_MPS = 1) AND
S.ORDER_TYPE = 5
-- bug 2461071
AND exists (SELECT '1'
FROM MSC_DESIGNATORS
WHERE INVENTORY_ATP_FLAG = 1
AND DESIGNATOR_TYPE = 2
AND DESIGNATOR_ID = S.SCHEDULE_DESIGNATOR_ID
AND DECODE(R.demand_class_atp_flag,1,
nvl(demand_class,
nvl(p_default_dmd_class,'@@@')),'@@@') =
DECODE(R.demand_class_atp_flag,1,
nvl(p_demand_class,
nvl(p_default_dmd_class,'@@@')),'@@@')
)))
--AND MSC_ATP_FUNC.MPS_ATP(S.SCHEDULE_DESIGNATOR_ID) = 1))
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
AND C.SR_INSTANCE_ID = I.SR_INSTANCE_ID
-- Bug 2132288, 2442009
AND C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
AND TRUNC(NVL(DECODE(S.ORDER_TYPE, 5, S.LAST_UNIT_START_DATE,
S.LAST_UNIT_COMPLETION_DATE), NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
AND DECODE(DECODE(S.ORDER_TYPE, 5, S.LAST_UNIT_START_DATE,
S.LAST_UNIT_COMPLETION_DATE),
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
-- End Bug 2132288, 2442009
-- new clause 2640489, SIMPLIFY FOR CBO
AND (S.ORDER_TYPE = 18
OR R.PAST_DUE_SUPPLY_CUTOFF_FENCE is NULL
OR C.NEXT_SEQ_NUM >= p_sysdate_seq_num - R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_DATE >= DECODE(S.ORDER_TYPE, 27, TRUNC(SYSDATE),
28, TRUNC(SYSDATE),
C.NEXT_DATE)
AND C.NEXT_DATE < NVL(p_itf, C.NEXT_DATE + 1)
AND (R.DEMAND_CLASS_ATP_FLAG <> 1
OR S.ORDER_TYPE = 5
OR NVL(S.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) )
---bug 1735580
--- filter out non-atpable sub-inventories
AND MSI.plan_id (+) = p_plan_id
AND MSI.organization_id (+) = p_org_id
AND MSI.sr_instance_id (+) = p_instance_id
--aND S.subinventory_code = (+) MSI.sub_inventory_code
AND MSI.sub_inventory_code (+) = S.subinventory_code
AND NVL(MSI.inventory_atp_code,1) <> 2 -- filter out non-atpable subinventories
-- SQL Query changes End 2640489
)
GROUP BY SD_DATE
order by SD_DATE;--4698199
SELECT SD_DATE, SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM (
SELECT -- C.PRIOR_DATE SD_DATE, -- 2859130
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
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 requestdate,promisedate,scheduledate
-- -1*D.USING_REQUIREMENT_QUANTITY SD_QTY
-1*(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) SD_QTY --5027568
FROM MSC_DEMANDS D,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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 (4,5,7,8,9,11,15,22,28,29,31,52) -- ignore copy SO for summary enhancement
-- Bug1990155, 1995835 exclude the expired lots demand datreya 9/18/2001
-- Bug 1530311, forecast to be excluded
-- new clause 2640489 SIMPLIFY
-- AND (C.SEQ_NUM IS NOT NULL OR D.ORIGINATION_TYPE <> 4)
-- AND ((D.ORIGINATION_TYPE = 4 AND C.SEQ_NUM IS NOT NULL) OR
-- (D.ORIGINATION_TYPE <> 4))
-- AND C.PRIOR_DATE < NVL(p_itf, C.PRIOR_DATE + 1)
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
--bug3693892 added trunc
AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
AND 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))))
< TRUNC(NVL(p_itf, 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))) + 1))
--plan by request date,promise date ,ship date
UNION ALL
SELECT -- C.NEXT_DATE SD_DATE, -- 2859130
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
FROM MSC_SUPPLIES S,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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 DECODE(S.LAST_UNIT_COMPLETION_DATE,
-- NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
-- AND C.NEXT_DATE < NVL(p_itf, C.NEXT_DATE + 1)
AND TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1) -- 2859130
---bug 1735580
)
GROUP BY SD_DATE
ORDER BY SD_DATE; --4698199
SELECT SD_DATE, SUM(SD_QTY)
BULK COLLECT INTO x_atp_dates, x_atp_qtys
FROM (
SELECT -- C.PRIOR_DATE SD_DATE, -- 2859130
C.CALENDAR_DATE SD_DATE,
-1* DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
--D.USING_REQUIREMENT_QUANTITY) SD_QTY
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) SD_QTY --5027568
FROM MSC_CALENDAR_DATES C,
MSC_DEMANDS D,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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,52) -- ignore copy SO for summary enhancement
-- Bug1990155, 1995835 exclude the expired lots demand datreya 9/18/2001
-- Bug 1530311, forecast to be excluded
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
AND C.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
-- 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 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
AND C.CALENDAR_DATE
BETWEEN
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))))
AND
TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
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)))))
--plan by request date, promise date, schedule date
-- new clause 2640489 SIMPLIFY
AND (C.SEQ_NUM IS NOT NULL OR D.ORIGINATION_TYPE <> 4)
-- AND ((D.ORIGINATION_TYPE = 4 AND C.SEQ_NUM IS NOT NULL) OR
-- (D.ORIGINATION_TYPE <> 4))
-- AND C.PRIOR_DATE < NVL(p_itf, C.PRIOR_DATE + 1)
AND C.CALENDAR_DATE < NVL(p_itf, C.CALENDAR_DATE + 1)
UNION ALL
SELECT -- C.NEXT_DATE SD_DATE, -- 2859130
C.CALENDAR_DATE SD_DATE,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) SD_QTY
FROM MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_SYSTEM_ITEMS I
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
AND C.SR_INSTANCE_ID = I.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)))
-- 2859130 change next_seq_num to 1
AND DECODE(S.LAST_UNIT_COMPLETION_DATE,
NULL, 1, C.SEQ_NUM) IS NOT NULL
-- AND C.NEXT_DATE < NVL(p_itf, C.NEXT_DATE + 1)
AND C.CALENDAR_DATE < NVL(p_itf, C.CALENDAR_DATE + 1) -- 2859130
---bug 1735580
)
GROUP BY SD_DATE
ORDER BY SD_DATE; --5353882
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
ORIG_CUSTOMER_SITE_NAME,--bug3263368
ORIG_CUSTOMER_NAME, --bug3263368
ORIG_DEMAND_CLASS, --bug3263368
ORIG_REQUEST_DATE --bug3263368
)
( -- SQL Query changes Begin 2640489
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4 ,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
1 col17, -- demand
D.ORIGINATION_TYPE col18,
l_null_char col19,
D.SR_INSTANCE_ID col20,
l_null_num col21,
D.DEMAND_ID col22,
l_null_num col23,
-1* D.USING_REQUIREMENT_QUANTITY col24,
C.PRIOR_DATE col25,
l_null_num col26,
DECODE(D.ORIGINATION_TYPE, 1, to_char(D.DISPOSITION_ID), D.ORDER_NUMBER) col27,
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id
-- in disposition_name column
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
MTPS.LOCATION, --bug3263368
MTP.PARTNER_NAME, --bug3263368
D.DEMAND_CLASS, --bug3263368
trunc(DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE, --4135752
D.REQUEST_SHIP_DATE)) --bug3263368
FROM MSC_CALENDAR_DATES C,
MSC_DEMANDS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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 D.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+) --bug3263368
AND C.CALENDAR_CODE=p_cal_code
AND C.EXCEPTION_SET_ID=p_cal_exc_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 (R.PAST_DUE_DEMAND_CUTOFF_FENCE is NULL OR
C.PRIOR_SEQ_NUM >= p_sysdate_seq_num - R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.PRIOR_DATE < NVL(p_itf, C.PRIOR_DATE + 1)
AND (R.DEMAND_CLASS_ATP_FLAG <> 1 OR
NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) )
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
1 col17, -- demand
DECODE(D.RESERVATION_TYPE, 1, 30, 10) col18,
l_null_char col19,
D.SR_INSTANCE_ID col20,
l_null_num col21,
to_number(D.DEMAND_SOURCE_LINE) col22,
l_null_num col23,
-1*(D.PRIMARY_UOM_QUANTITY-
GREATEST(NVL(D.RESERVATION_QUANTITY,0), D.COMPLETED_QUANTITY))
col24,
DECODE(D.RESERVATION_TYPE,2,p_sys_next_date, TRUNC(D.REQUIREMENT_DATE)) col25 , -- to_date removed to avoid GSCC error
l_null_num col26,
D.SALES_ORDER_NUMBER col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
MTPS.LOCATION, --bug3263368
MTP.PARTNER_NAME, --bug3263368
D.DEMAND_CLASS, --bug3263368
trunc(DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE, --4135752
D.REQUEST_SHIP_DATE)) --bug3263368
FROM
MSC_SALES_ORDERS D,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_CALENDAR_DATES C,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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 DECODE(MSC_ATP_PVT.G_APPS_VER,3,D.COMPLETED_QUANTITY,0) = 0 -- 2300767
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))))
-- new clause, remove existing Exists Query 2640489
AND D.SHIP_TO_SITE_USE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+) --bug3263368
AND (R.PAST_DUE_DEMAND_CUTOFF_FENCE is NULL OR
C.PRIOR_SEQ_NUM >= p_sysdate_seq_num - R.PAST_DUE_DEMAND_CUTOFF_FENCE)
AND C.CALENDAR_CODE = p_cal_code
AND C.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C.EXCEPTION_SET_ID = -1
AND C.CALENDAR_DATE = TRUNC(D.REQUIREMENT_DATE)
AND C.PRIOR_DATE < NVL(p_itf, C.PRIOR_DATE + 1)
-- new clause 2640489, DECODE is also OR, Explicit OR gives CBO choices
AND (R.DEMAND_CLASS_ATP_FLAG <> 1 OR
NVL(D.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) )
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4 ,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
2 col17, -- supply
S.ORDER_TYPE col18,
l_null_char col19,
S.SR_INSTANCE_ID col20,
l_null_num col21,
S.TRANSACTION_ID col22,
l_null_num col23,
Decode(order_type,
30, Decode(Sign(S.Daily_rate * (TRUNC(C.Calendar_date) - TRUNC(S.FIRST_UNIT_START_DATE) )- S.qty_completed),
-1,S.Daily_rate* (TRUNC(C.Calendar_date) - TRUNC(S.First_Unit_Start_date) +1)- S.qty_completed,
S.Daily_rate),
5, NVL(S.DAILY_RATE, NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)),
(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) - NVL(S.NON_NETTABLE_QTY, 0)) ) col24,
C.NEXT_DATE col25,
l_null_num col26,
DECODE(S.ORDER_TYPE,
1, S.ORDER_NUMBER,
2, S.ORDER_NUMBER,
3, S.ORDER_NUMBER,
7, S.ORDER_NUMBER,
8, S.ORDER_NUMBER,
5, MSC_ATP_FUNC.Get_Designator(S.SCHEDULE_DESIGNATOR_ID),
11, S.ORDER_NUMBER,
12, S.ORDER_NUMBER,
14, S.ORDER_NUMBER,
15, S.ORDER_NUMBER,
27, S.ORDER_NUMBER,
28, S.ORDER_NUMBER,
41, S.ORDER_NUMBER, -- bug 3745082 'User Defined Supply'
-- NULL) col27,
l_null_char) col27, --bug3814584
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
--null, --bug3263368 ORIG_CUSTOMER_SITE_NAME
--null, --bug3263368 ORIG_CUSTOMER_NAME
--null, --bug3263368 ORIG_DEMAND_CLASS
--null --bug3263368 ORIG_REQUEST_DATE
l_null_char, --bug3814584
l_null_char, --bug3814584
l_null_char, --bug3814584
l_null_date --bug3814584
FROM MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ATP_RULES R,
MSC_SYSTEM_ITEMS I,
MSC_SUB_INVENTORIES MSI
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
AND I.PLAN_ID = p_plan_id
AND R.RULE_ID (+) = NVL(I.ATP_RULE_ID, p_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
AND Decode(S.order_type, 30, S.Daily_rate* (TRUNC(C.Calendar_date)
- TRUNC(S.First_Unit_Start_date) + 1),
5, NVL(S.Daily_rate, ABS(NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY)) ),
ABS(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_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
(INCLUDE_INTERNAL_REQS = 1 AND S.ORDER_TYPE = 2 AND
S.SOURCE_ORGANIZATION_ID IS NOT NULL)
OR
(INCLUDE_SUPPLIER_REQS = 1 AND S.ORDER_TYPE = 2 AND
S.SOURCE_ORGANIZATION_ID IS NULL)
OR
((R.INCLUDE_REP_MPS = 1 OR R.INCLUDE_DISCRETE_MPS = 1) AND
S.ORDER_TYPE = 5
-- bug 2461071
AND exists (SELECT '1'
FROM MSC_DESIGNATORS
WHERE INVENTORY_ATP_FLAG = 1
AND DESIGNATOR_TYPE = 2
AND DESIGNATOR_ID = S.SCHEDULE_DESIGNATOR_ID
AND DECODE(R.demand_class_atp_flag,1,
nvl(demand_class,
nvl(p_default_dmd_class,'@@@')),'@@@') =
DECODE(R.demand_class_atp_flag,1,
nvl(p_demand_class,
nvl(p_default_dmd_class,'@@@')),'@@@')
)))
--AND MSC_ATP_FUNC.MPS_ATP(S.SCHEDULE_DESIGNATOR_ID) = 1
AND C.CALENDAR_CODE = p_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_set_id
AND C.SR_INSTANCE_ID = p_instance_id
-- Bug 2132288, 2442009
AND C.CALENDAR_DATE BETWEEN TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE))
AND TRUNC(NVL(DECODE(S.ORDER_TYPE, 5, S.LAST_UNIT_START_DATE,
S.LAST_UNIT_COMPLETION_DATE), NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)))
AND DECODE(DECODE(S.ORDER_TYPE, 5, S.LAST_UNIT_START_DATE,
S.LAST_UNIT_COMPLETION_DATE),
NULL, C.NEXT_SEQ_NUM, C.SEQ_NUM) IS NOT NULL
-- End Bug 2132288, 2442009
-- new clause 2640489, SIMPLIFY FOR CBO
AND (S.ORDER_TYPE = 18
OR R.PAST_DUE_SUPPLY_CUTOFF_FENCE is NULL
OR C.NEXT_SEQ_NUM >= p_sysdate_seq_num - R.PAST_DUE_SUPPLY_CUTOFF_FENCE)
AND C.NEXT_DATE >= DECODE(S.ORDER_TYPE, 27, TRUNC(SYSDATE),
28, TRUNC(SYSDATE),
C.NEXT_DATE)
AND C.NEXT_DATE < NVL(p_itf, C.NEXT_DATE + 1)
AND (R.DEMAND_CLASS_ATP_FLAG <> 1
OR S.ORDER_TYPE = 5
OR NVL(S.DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) =
NVL(P_DEMAND_CLASS, NVL(p_default_dmd_class,'@@@')) )
--- filter out non-atpable sub-inventories
AND MSI.plan_id (+) = p_plan_id
AND MSI.organization_id (+) = p_org_id
AND MSI.sr_instance_id (+) = p_instance_id
-- AND S.subinventory_code = MSI.sub_inventory_code
AND MSI.sub_inventory_code (+) = S.subinventory_code
AND NVL(MSI.inventory_atp_code,1) <> 2 -- filter out non-atpable subinventories
-- SQL Query changes End 2640489
)
;
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
ORIG_CUSTOMER_SITE_NAME,--bug3263368
ORIG_CUSTOMER_NAME, --bug3263368
ORIG_DEMAND_CLASS, --bug3263368
ORIG_REQUEST_DATE --bug3263368
)
(
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4 ,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
1 col17, -- demand
--D.ORIGINATION_TYPE col18,
DECODE( D.ORIGINATION_TYPE, -100, 30, D.ORIGINATION_TYPE) col18, --5027568
l_null_char col19,
D.SR_INSTANCE_ID col20,
l_null_num col21,
D.DEMAND_ID col22,
l_null_num col23,
-- -1* D.USING_REQUIREMENT_QUANTITY col24,
-1*(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) col24, --5027568
-- C.PRIOR_DATE col25, -- 2859130
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
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)))) col25,
--plan by request date,promise date, schedule date
l_null_num col26,
D.ORDER_NUMBER col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
MTPS.LOCATION, --bug3263368
MTP.PARTNER_NAME, --bug3263368
D.DEMAND_CLASS, --bug3263368
DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
D.REQUEST_SHIP_DATE) --bug3263368
FROM MSC_SYSTEM_ITEMS I,
MSC_DEMANDS D,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+) --bug3263368
AND D.USING_REQUIREMENT_QUANTITY <> 0 --4501434
AND D.ORGANIZATION_ID = I.ORGANIZATION_ID
-- 1243985
-- 2859130 repetitive schedule (4) not supported for constrained plan
AND D.ORIGINATION_TYPE NOT IN (4,5,7,8,9,11,15,22,28,29,31,52) -- ignore copy SO for summary enhancement
-- Bug1990155, 1995835 exclude the expired lots demand datreya 9/18/2001
-- Bug 1530311, need to exclude forecast
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
--bug3693892 added trunc
AND 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))))
< TRUNC(NVL(p_itf,
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))) + 1))
--plan by request date, promise date, schedule date
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4 ,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
2 col17, -- supply
S.ORDER_TYPE col18,
l_null_char col19,
S.SR_INSTANCE_ID col20,
l_null_num col21,
S.TRANSACTION_ID col22,
l_null_num col23,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) col24,
-- C.NEXT_DATE col25, -- 2859130
TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) col25,
l_null_num col26,
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
-- S.ORDER_NUMBER col27,
DECODE(S.ORDER_TYPE, 5, to_char(S.TRANSACTION_ID), S.ORDER_NUMBER) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
--null, --bug3263368 ORIG_CUSTOMER_SITE_NAME
--null, --bug3263368 ORIG_CUSTOMER_NAME
--null, --bug3263368 ORIG_DEMAND_CLASS
--null --bug3263368 ORIG_REQUEST_DATE
l_null_char, --bug3814584
l_null_char, --bug3814584
l_null_char, --bug3814584
l_null_date --bug3814584
FROM MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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 TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) < NVL(p_itf, TRUNC(NVL(S.FIRM_DATE,S.NEW_SCHEDULE_DATE)) + 1)
)
;
INSERT INTO msc_atp_sd_details_temp (
ATP_Level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
ORIG_CUSTOMER_SITE_NAME,--bug3263368
ORIG_CUSTOMER_NAME, --bug3263368
ORIG_DEMAND_CLASS, --bug3263368
ORIG_REQUEST_DATE --bug3263368
)
(
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4 ,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
1 col17, -- demand
--D.ORIGINATION_TYPE col18,
DECODE( D.ORIGINATION_TYPE, -100, 30, D.ORIGINATION_TYPE) col18, --5027568
l_null_char col19,
D.SR_INSTANCE_ID col20,
l_null_num col21,
D.DEMAND_ID col22,
l_null_num col23,
-1* DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
--D.USING_REQUIREMENT_QUANTITY) col24,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) col24, --5027568
-- C.PRIOR_DATE col25, -- 2859130
C.CALENDAR_DATE col25,
l_null_num col26,
D.ORDER_NUMBER col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
MTPS.LOCATION, --bug3263368
MTP.PARTNER_NAME, --bug3263368
D.DEMAND_CLASS, --bug3263368
DECODE(D.ORDER_DATE_TYPE_CODE,2,D.REQUEST_DATE,
D.REQUEST_SHIP_DATE) --bug3263368
FROM MSC_SYSTEM_ITEMS I,
MSC_DEMANDS D,
MSC_CALENDAR_DATES C,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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.USING_REQUIREMENT_QUANTITY <> 0 --4501434
AND D.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+) --bug3263368
-- 1243985
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,52) -- ignore copy SO for summary enhancement
-- Bug1990155, 1995835 exclude the expired lots demand datreya 9/18/2001
-- Bug 1530311, need to exclude forecast
AND C.CALENDAR_CODE=p_cal_code
AND C.EXCEPTION_SET_ID=p_cal_exc_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 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
AND C.CALENDAR_DATE
BETWEEN
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))))
AND
TRUNC(NVL(D.ASSEMBLY_DEMAND_COMP_DATE,
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)))))--plan by request date,promisedate, schedule date
-- new clause 2640489 SIMPLIFY
AND (C.SEQ_NUM IS NOT NULL OR D.ORIGINATION_TYPE <> 4)
-- AND ((D.ORIGINATION_TYPE = 4 AND C.SEQ_NUM IS NOT NULL) OR
-- (D.ORIGINATION_TYPE <> 4))
AND C.PRIOR_DATE < NVL(p_itf, C.PRIOR_DATE + 1)
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_item_id col4 ,
p_request_item_id col5,
p_org_id col6,
l_null_num col7,
l_null_num col8,
l_null_num col9,
l_null_num col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
I.UOM_CODE col16,
2 col17, -- supply
S.ORDER_TYPE col18,
l_null_char col19,
S.SR_INSTANCE_ID col20,
l_null_num col21,
S.TRANSACTION_ID col22,
l_null_num col23,
NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) col24,
-- C.NEXT_DATE col25, -- 2859130
C.CALENDAR_DATE col25,
l_null_num col26,
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
-- S.ORDER_NUMBER col27,
DECODE(S.ORDER_TYPE, 5, to_char(S.TRANSACTION_ID), S.ORDER_NUMBER) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
--null, --bug3263368 ORIG_CUSTOMER_SITE_NAME
--null, --bug3263368 ORIG_CUSTOMER_NAME
--null, --bug3263368 ORIG_DEMAND_CLASS
--null --bug3263368 ORIG_REQUEST_DATE
l_null_char, --bug3814584
l_null_char, --bug3814584
l_null_char, --bug3814584
l_null_date --bug3814584
FROM MSC_SYSTEM_ITEMS I,
MSC_SUPPLIES S,
MSC_CALENDAR_DATES C
WHERE I.SR_INVENTORY_ITEM_ID = p_item_id
AND I.ORGANIZATION_ID = p_org_id
AND I.SR_INSTANCE_ID = p_instance_id
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_cal_code
AND C.EXCEPTION_SET_ID = p_cal_exc_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 C.NEXT_DATE < NVL(p_itf, C.NEXT_DATE + 1)
)
;
SELECT msi.inventory_item_id, msi.sr_inventory_item_id,
(sub.usage_quantity/comp.usage_quantity),
msi.atp_flag,msi.atp_components_flag,comp.usage_quantity
, msi.item_name -- Modularize remove unecessary calls.
--diag_atp
--bug3609031 adding ceil
,ceil(msi.postprocessing_lead_time), ceil(msi.preprocessing_lead_time),
msi.variable_lead_time, msi.fixed_lead_time,
msi.unit_weight, msi.unit_volume,
msi.weight_uom, msi.volume_uom, msi.rounding_control_type
--time_phased_atp
-- ,nvl(msi.product_family_id, msi.inventory_item_id)
--bug 4891470
,DECODE(msi.atp_flag, 'N', msi.inventory_item_id,
DECODE(msi.product_family_id,
NULL, msi.inventory_item_id,
-23453, msi.inventory_item_id,
msi.product_family_id))
,msi.aggregate_time_fence_date
--4570421
,comp.scaling_type scaling_type
,comp.scale_multiple scale_multiple
,comp.scale_rounding_variance scale_rounding_variance
,comp.rounding_direction rounding_direction
,comp.component_yield_factor component_yield_factor
FROM msc_system_items msi, msc_component_substitutes sub,
msc_bom_components comp, msc_boms bom, msc_system_items ch,
msc_system_items pt, mrp_atp_details_temp peg
WHERE peg.session_id = MSC_ATP_PVT.G_SESSION_ID
AND peg.pegging_id = p_parent_pegging_id
AND pt.sr_instance_id = p_instance_id
AND pt.organization_id = peg.organization_id
AND pt.sr_inventory_item_id = peg.inventory_item_id
AND pt.plan_id = p_plan_id
AND ch.plan_id = pt.plan_id
AND ch.organization_id = pt.organization_id
AND ch.sr_instance_id = pt.sr_instance_id
AND ch.sr_inventory_item_id = p_inventory_item_id
AND bom.plan_id = pt.plan_id
AND bom.assembly_item_id = pt.inventory_item_id
AND bom.organization_id = peg.organization_id
-- performance dsting change p_instance_id to pt.sr_instance_id
AND bom.sr_instance_id = pt.sr_instance_id
and bom.alternate_bom_designator is null
AND comp.bill_sequence_id = bom.bill_sequence_id
AND comp.inventory_item_id = ch.inventory_item_id
AND TRUNC(NVL(comp.DISABLE_DATE, l_requested_ship_date+1)) > -- 1221363
trunc(l_requested_ship_date)
AND TRUNC(comp.EFFECTIVITY_DATE) <= TRUNC(l_requested_ship_date)
AND comp.plan_id = bom.plan_id
AND sub.bill_sequence_id = comp.bill_sequence_id
AND sub.plan_id = comp.plan_id
AND sub.component_sequence_id = comp.component_sequence_id
AND msi.inventory_item_id = sub.substitute_item_id
AND msi.organization_id = comp.organization_id
AND msi.sr_instance_id = comp.sr_instance_id
AND msi.plan_id = sub.plan_id
-- BUG 2752227 only get ATPeable substitutes.
AND msi.atp_flag in ('Y', 'C')
ORDER BY priority;
SELECT msi.inventory_item_id, msi.sr_inventory_item_id,
decode (NVL(MSC_ATP_PVT.G_ORG_INFO_REC.org_type, MSC_ATP_PVT.DISCRETE_ORG),
MSC_ATP_PVT.DISCRETE_ORG, decode (nvl (comp.scaling_type, 1),
1, (sub.usage_quantity/comp.usage_quantity),
2, sub.usage_quantity
),
MSC_ATP_PVT.OPM_ORG , decode (nvl (comp.scaling_type, 1),
0, sub.usage_quantity,
1, (sub.usage_quantity/comp.usage_quantity),
2, sub.usage_quantity,
3, (sub.usage_quantity/comp.usage_quantity),
4, (sub.usage_quantity/comp.usage_quantity),
5, (sub.usage_quantity/comp.usage_quantity)
)
), --5008983
--(sub.usage_quantity/comp.usage_quantity),
msi.atp_flag,msi.atp_components_flag,comp.usage_quantity
, msi.item_name -- Modularize remove unecessary calls.
--diag_atp
--bug3609031 adding ceil
,ceil(msi.postprocessing_lead_time), ceil(msi.preprocessing_lead_time),
msi.variable_lead_time, msi.fixed_lead_time,
msi.unit_weight, msi.unit_volume,
msi.weight_uom, msi.volume_uom, msi.rounding_control_type
--time_phased_atp
--,nvl(msi.product_family_id, msi.inventory_item_id) --5006799
,DECODE(msi.product_family_id,
NULL, msi.inventory_item_id,
-23453, msi.inventory_item_id,
msi.product_family_id)
,msi.aggregate_time_fence_date
--4570421
,comp.scaling_type scaling_type
,comp.scale_multiple scale_multiple
,comp.scale_rounding_variance scale_rounding_variance
,comp.rounding_direction rounding_direction
,comp.component_yield_factor component_yield_factor
,sub.usage_quantity/(comp.usage_quantity*comp.component_yield_factor) usage_qty --4775920
FROM msc_system_items msi, msc_component_substitutes sub,
msc_bom_components comp, msc_system_items ch
WHERE ch.plan_id = p_plan_id
AND ch.organization_id = p_organization_id
AND ch.sr_instance_id = p_instance_id
AND ch.sr_inventory_item_id = p_inventory_item_id
AND comp.bill_sequence_id = p_atp_record.bill_seq_id
AND comp.inventory_item_id = ch.inventory_item_id
AND TRUNC(NVL(comp.DISABLE_DATE, l_requested_ship_date+1)) >
trunc(l_requested_ship_date)
AND TRUNC(comp.EFFECTIVITY_DATE) <= TRUNC(l_requested_ship_date)
AND comp.plan_id = ch.plan_id
AND sub.bill_sequence_id = comp.bill_sequence_id
AND sub.plan_id = comp.plan_id
AND sub.component_sequence_id = comp.component_sequence_id
AND msi.inventory_item_id = sub.substitute_item_id
AND msi.organization_id = comp.organization_id
AND msi.sr_instance_id = comp.sr_instance_id
AND msi.plan_id = sub.plan_id
AND msi.atp_flag in ('Y', 'C')
ORDER BY priority;
l_atp_insert_rec MRP_ATP_PVT.AtpRec;
/* rajjain 3008611 select component substitutes for which:
* effective date is greater than or equal to greatest of PTF date, sysdate and component due date
* disable date is less than or equal to greatest of PTF date, sysdate and component due date*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Check_Substitutes: ' || 'p_atp_record.requested_ship_date: ' || p_atp_record.requested_ship_date);
g_atp_record.insert_flag := p_atp_record.insert_flag;
l_mat_atp_info_rec.insert_flag := g_atp_record.insert_flag;
l_atp_insert_rec.instance_id := g_atp_record.instance_id;
l_atp_insert_rec.inventory_item_id := l_pf_item_id;
l_atp_insert_rec.request_item_id := l_inv_item_id;
l_atp_insert_rec.atf_date_quantity := l_atf_date_qty;
l_atp_insert_rec.organization_id := g_atp_record.organization_id;
l_atp_insert_rec.identifier := g_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=
nvl(g_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(g_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_delivery :=
g_atp_record.demand_source_delivery;
l_atp_insert_rec.quantity_ordered:= LEAST(
p_atp_record.requested_date_quantity,
g_atp_record.quantity_ordered);
l_atp_insert_rec.requested_ship_date := l_requested_ship_date;
l_atp_insert_rec.demand_class := g_atp_record.demand_class;
l_atp_insert_rec.refresh_number := p_refresh_number; -- summary enhancement g_atp_record.refresh_number;
l_atp_insert_rec.order_number := g_atp_record.order_number;
l_atp_insert_rec.origination_type := 47;
msc_sch_wb.atp_debug('Check_Substitutes: ' || 'DRP origination_type ='|| l_atp_insert_rec.origination_type);
l_atp_insert_rec.origination_type := 1;
MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
l_plan_id,
0,
l_demand_id);
IF p_atp_record.insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_atp_pegging_id,
MSC_ATP_PVT.G_DEMAND_PEGGING_ID);
msc_sch_wb.atp_debug('Get_Material_Atp_Info: ' || 'p_mat_atp_info_rec.insert_flag: '|| to_char(p_mat_atp_info_rec.insert_flag));
SELECT 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)
)
)
)
)
)
),
summary_flag
INTO MSC_ATP_PVT.G_OPTIMIZED_PLAN, l_summary_flag -- For summary enhancement
FROM msc_plans plans
WHERE plans.plan_id = p_mat_atp_info_rec.plan_id;
SELECT cal.next_seq_num, cal.next_date
INTO l_sysdate_seq_num, 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(sysdate)
AND cal.sr_instance_id = p_mat_atp_info_rec.instance_id ;
IF nvl(p_mat_atp_info_rec.insert_flag, 0) = 0 THEN
IF p_mat_atp_info_rec.inventory_item_id = l_request_item_id THEN
get_mat_avail(
MSC_ATP_PVT.G_SUMMARY_SQL, -- MSC_ATP_PVT.G_SUMMARY_FLAG, -- changed for summary enhancement
MSC_ATP_PVT.G_OPTIMIZED_PLAN,
p_mat_atp_info_rec.inventory_item_id,
p_mat_atp_info_rec.organization_id,
p_mat_atp_info_rec.instance_id,
p_mat_atp_info_rec.plan_id,
l_calendar_code,
l_calendar_exception_set_id,
l_sysdate_seq_num,
l_sys_next_date,
-- l_default_atp_rule_id,
-- p_mat_atp_info_rec.demand_class,
p_mat_atp_info_rec.demand_class, --Sequence of parameters changed as a part of cmro changes
l_default_atp_rule_id,--to correct them
l_default_demand_class,
l_infinite_time_fence_date,
p_mat_atp_info_rec.refresh_number, -- For summary enhancement
l_atp_period_tab,
l_atp_qty_tab
);
ELSE -- IF nvl(p_mat_atp_info_rec.insert_flag, 0) = 0 THEN
l_get_mat_in_rec.infinite_time_fence_date := l_infinite_time_fence_date;
MSC_ATP_REQ.Insert_Details(p_mat_atp_info_rec.instance_id,
p_mat_atp_info_rec.plan_id,
p_mat_atp_info_rec.level,
p_mat_atp_info_rec.identifier,
p_mat_atp_info_rec.scenario_id,
p_mat_atp_info_rec.request_item_id,
p_mat_atp_info_rec.inventory_item_id,
p_mat_atp_info_rec.organization_id,
p_mat_atp_info_rec.demand_class,
p_mat_atp_info_rec.insert_flag,
x_atp_period,
x_atp_supply_demand,
l_return_status,
--diag_atp
l_get_mat_in_rec,
p_mat_atp_info_rec.atf_date); -- For time_phased_atp
msc_sch_wb.atp_debug('Get_Material_Atp_Info: ' || 'after Insert_Details');
END IF; -- IF nvl(p_mat_atp_info_rec.insert_flag, 0) = 0 THEN
p_mat_atp_info_rec.insert_flag,
l_atp_info,
x_atp_period,
x_atp_supply_demand,
--diag_atp
l_get_mat_in_rec,
p_mat_atp_info_rec.refresh_number, -- For summary enhancement
p_mat_atp_info_rec.request_item_id, -- For time_phased_atp
p_mat_atp_info_rec.atf_date); -- For time_phased_atp
p_mat_atp_info_rec.insert_flag,
l_atp_info,
x_atp_period,
x_atp_supply_demand,
l_get_mat_in_rec,
p_mat_atp_info_rec.request_item_id, -- For time_phased_atp
p_mat_atp_info_rec.atf_date); -- For time_phased_atp
IF NVL(p_mat_atp_info_rec.insert_flag,0) = 0 and
p_mat_atp_info_rec.level = 1 AND
nvl(MSC_ATP_PVT.G_ITEM_INFO_REC.atp_comp_flag,'N') = 'N' THEN
x_atp_period.cumulative_quantity := l_atp_info.atp_qty;
PROCEDURE Insert_Details (
p_instance_id IN NUMBER,
p_plan_id IN NUMBER,
p_level IN NUMBER,
p_identifier IN NUMBER,
p_scenario_id IN NUMBER,
p_request_item_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_demand_class IN VARCHAR2,
p_insert_flag IN NUMBER,
x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
x_return_status OUT NoCopy VARCHAR2,
p_get_mat_in_rec In MSC_ATP_REQ.get_mat_in_rec,
p_atf_date IN DATE -- For time_phased_atp
)
IS
i PLS_INTEGER;
msc_sch_wb.atp_debug('***** Begin Insert_Details Procedure *****');
IF p_insert_flag >0 THEN
/* time_phased_atp changes begin*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Details: ' || 'p_atf_date := ' || p_atf_date);
msc_sch_wb.atp_debug('Insert_Details: ' || 'p_request_item_id := ' || p_request_item_id);
msc_sch_wb.atp_debug('Insert_Details: ' || 'p_inventory_item_id := ' || p_inventory_item_id);
msc_sch_wb.atp_debug('Insert_Details: ' || 'l_request_item_id := ' || l_request_item_id);
msc_sch_wb.atp_debug('Insert_Details: ' || 'Time Phased ATP = ' || l_time_phased_atp);
msc_sch_wb.atp_debug('Insert_Details: ' || 'l_default_atp_rule_id='|| l_default_atp_rule_id);
msc_sch_wb.atp_debug('Insert_Details: ' || 'l_calendar_code='||l_calendar_code);
msc_sch_wb.atp_debug('Insert_Details: ' || 'l_calendar_exception_set_id'|| l_calendar_exception_set_id);
msc_sch_wb.atp_debug('Insert_Details: ' || 'l_default_demand_class'|| l_default_demand_class);
SELECT cal.next_seq_num,cal.next_date
INTO l_sysdate_seq_num,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(sysdate)
AND cal.sr_instance_id = p_instance_id ;
msc_sch_wb.atp_debug('Insert_Details: ' || 'System Next Date is : '|| l_sys_next_date);
msc_sch_wb.atp_debug('Insert_Details: ' || 'Sequence Number Is :'|| l_sysdate_seq_num);
msc_sch_wb.atp_debug('Insert_Details: ' || 'before select');
msc_sch_wb.atp_debug('Insert_Details: ' || 'Error occured in procedure Get_Mat_Avail_Pf_Dtls');
msc_sch_wb.atp_debug('Insert_Details: ' || 'after inserting into msc_atp_sd_details_temp');
msc_sch_wb.atp_debug('Insert_Details: ' || 'Total Supply/Demand Recs : '|| SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Insert_Details: ' || 'Error occured in procedure Get_Period_Data_From_Sd_Temp');
msc_sch_wb.atp_debug('Insert_Details: ' || 'before atp_consume');
msc_sch_wb.atp_debug('Insert_Details: ' || 'Error occured in procedure pf_atp_consume');
msc_sch_wb.atp_debug('Insert_Details: ' || 'after atp_consume');
msc_sch_wb.atp_debug('***** End Insert_Details Procedure *****');
END Insert_Details;
p_insert_flag IN NUMBER,
p_search IN NUMBER,
p_demand_class IN VARCHAR2,
--(ssurendr) Bug 2865389 Added routing Sequence id and Bill sequence id for OPM issue.
p_routing_seq_id IN NUMBER,
p_bill_seq_id IN NUMBER,
p_parent_ship_date IN DATE, -- Bug 2814872 Cut-off Date for Resource Check
p_line_identifier IN NUMBER, -- CTO ODR Identifies the line being processed.
x_avail_assembly_qty OUT NoCopy NUMBER,
x_atp_date OUT NoCopy DATE,
x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
x_return_status OUT NoCopy VARCHAR2
)
IS
l_res_requirements MRP_ATP_PVT.Atp_Res_Typ;
SELECT decode(daily_rtg_aggregation_level, CONST_USE_BOR, 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),
plans.summary_flag -- for summary enhancement
INTO l_use_bor, MSC_ATP_PVT.G_OPTIMIZED_PLAN, l_constraint_plan, l_summary_flag
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_sch_wb.atp_debug('Get_Res_Requirements: ' || 'p_insert_flag = '||p_insert_flag);
SELECT DECODE(MSC_ATP_PVT.G_ITEM_INFO_REC.bom_item_type, 1,
p_line_identifier, ATO_PARENT_MODEL_LINE_ID)
-- End Bug 3358981
INTO l_parent_line_id
FROM msc_cto_bom
WHERE line_id = p_line_identifier
AND session_id = MSC_ATP_PVT.G_SESSION_ID
AND sr_inventory_item_id = p_inventory_item_id;
SELECT routing_sequence_id
INTO l_routing_seq_id
FROM msc_routings
WHERE sr_instance_id = p_instance_id
AND plan_id = p_plan_id
AND organization_id = p_organization_id
AND assembly_item_id = l_inventory_item_id;
SELECT cfm_routing_flag, routing_sequence_id
INTO l_routing_type,l_routing_number
FROM msc_routings
WHERE plan_id = p_plan_id and
organization_id = p_organization_id and
sr_instance_id = p_instance_id and
assembly_item_id = l_inventory_item_id and
routing_sequence_id = l_routing_seq_id; -- CTO ODR
SELECT DISTINCT -- collapse common into one in case union all is used.
-- Uncomment: Bug 3432530 For CTO we obtain the Op. Seq. related data only once.
department_id,
owning_department_id,
resource_id,
basis_type,
resource_usage,
requested_date,
lead_time,
efficiency,
utilization,
batch_flag,
max_capacity,
required_unit_capacity,
required_capacity_uom,
res_uom,
res_uom_type,
std_op_code,
--diag_atp
resource_offset_percent,
operation_sequence,
actual_resource_usage,
reverse_cumulative_yield,
department_code,
resource_code
BULK COLLECT INTO l_res_requirements.department_id,
l_res_requirements.owning_department_id,
l_res_requirements.resource_id,
l_res_requirements.basis_type,
l_res_requirements.resource_usage,
l_res_requirements.requested_date,
l_res_requirements.lead_time,
l_res_requirements.efficiency,
l_res_requirements.utilization,
--- these columns have been added for resource batching
l_res_requirements.batch_flag,
l_res_requirements.max_capacity,
l_res_requirements.required_unit_capacity,
l_res_requirements.required_capacity_uom,
l_res_requirements.res_uom,
l_res_requirements.res_uom_type,
l_res_requirements.std_op_code,
---diag_atp
l_res_requirements.resource_offset_percent,
l_res_requirements.operation_sequence,
l_res_requirements.actual_resource_usage,
l_res_requirements.reverse_cumulative_yield,
l_res_requirements.department_code,
l_res_requirements.resource_code
FROM (
-- First select mandatory operations
-- for common routing cases the mandatory for option classes
-- will be clubbed together with the model.
SELECT /*+ ordered */ DISTINCT DR.DEPARTMENT_ID department_id,
-- Distinct: Bug 3432530 For CTO we obtain the Op. Seq. related data only once.
DR.OWNING_DEPARTMENT_ID owning_department_id,
DR.RESOURCE_ID resource_id,
RES.BASIS_TYPE basis_type,
--bug 3766224: Do not chnage usage for lot based resource
ROUND(DECODE(RES.BASIS_TYPE, 2, NVL(RES.RESOURCE_USAGE,0),
(NVL(RES.RESOURCE_USAGE,0)*
-- krajan : 2408696
-- MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0*
--bug3601223 Only if network_scheduling_method is planning percent then use % else 100%
Decode(l_network_scheduling_method,2,
(NVL(OP.NET_PLANNING_PERCENT,100)/100),1)
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD,1)))
/(Decode (nvl (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,MSC_ATP_PVT.DISCRETE_ORG), MSC_ATP_PVT.OPM_ORG, --Bug-4694958
decode (RES.BASIS_TYPE, 3,
NVL(DR.MAX_CAPACITY,1), nvl(rtg.routing_quantity,1)
),
nvl(rtg.routing_quantity,1)
)
)),6) resource_usage, --4694958
-- Bug 2865389 (ssurendr) routing quantity added for OPM fix.
C2.CALENDAR_DATE requested_date,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0))) lead_time,
-- End Bug 3432530 Use local variables.
NVL((DR.EFFICIENCY/100), 1) efficiency,
NVL((DR.UTILIZATION/100), 1) utilization,
NVL(DR.BATCHABLE_FLAG, 2) batch_flag,
NVL(DR.MAX_CAPACITY,0) max_capacity,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's item data.
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_unit_wt, 2, l_item_unit_vol) required_unit_capacity,
---bug 1905284
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_wt_uom, 2, l_item_vol_uom) required_capacity_uom ,
-- End Bug 3432530 Use local variables.
DR.UNIT_OF_MEASURE res_uom,
DR.UOM_CLASS_TYPE res_uom_type,
OP.STANDARD_OPERATION_CODE std_op_code,
--diag_atp
SEQ.RESOURCE_OFFSET_PERCENT resource_offset_percent,
OP.OPERATION_SEQ_NUM operation_sequence,
RES.RESOURCE_USAGE actual_resource_usage,
--NVL(OP.REVERSE_CUMULATIVE_YIELD, 1) reverse_cumulative_yield ,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield ,--4694958
DR.Department_code department_code,
DR.resource_code resource_code
FROM
MSC_CTO_BOM mcbom1,
-- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
MSC_ROUTINGS RTG,
MSC_ROUTING_OPERATIONS OP,
MSC_OPERATION_RESOURCE_SEQS SEQ,
MSC_OPERATION_RESOURCES RES,
MSC_DEPARTMENT_RESOURCES DR, -- this is the sharing dept
MSC_CALENDAR_DATES C1,
MSC_CALENDAR_DATES C2
WHERE mcbom1.session_id = MSC_ATP_PVT.G_SESSION_ID
AND mcbom1.sr_instance_id = p_instance_id
-- Bug 3358981 line is a model then include,
AND (mcbom1.ATO_PARENT_MODEL_LINE_ID = l_parent_line_id OR mcbom1.line_id = l_parent_line_id)
-- get all lines having the same parent model End Bug 3358981
AND ( --mcbom1.parent_line_id = p_line_identifier or
-- Handle situation when parent_line_id is null.
-- Basic thing is that this section should handle all cases.
mcbom1.inventory_item_id = l_inventory_item_id )
AND mcbom1.quantity <> 0
-- Get the routing
AND RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = mcbom1.sr_instance_id
AND RTG.ORGANIZATION_ID = p_organization_id
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id -- Local var for common and others
-- Bug 3432530
-- Comment out join conditions for msc_system_items
-- 3358981 Eliminate semi cartesian product, streamline query.
AND RTG.assembly_item_id = mcbom1.inventory_item_id
-- Join to system items
-- AND I.PLAN_ID = RTG.PLAN_ID
-- AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
-- AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
-- AND I.INVENTORY_ITEM_ID = RTG.assembly_item_id
-- 3358981 Eliminate semi cartesian product, streamline query.
-- AND I.INVENTORY_ITEM_ID = l_inventory_item_id
-- End Bug 3432530
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
-- Get all operations for the routing
AND OP.PLAN_ID = RTG.PLAN_ID
AND OP.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND OP.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
/* Operation is of type Event (Do not select process) */
and NVL(OP.operation_type,1 ) = 1
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(OP.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(OP.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
-- SMCs/Mandatory Operations
AND OP.option_dependent_flag = 2
-- for the configuration
-- Obtain the Resource Seq numbers.
AND SEQ.PLAN_ID = OP.PLAN_ID
AND SEQ.ROUTING_SEQUENCE_ID = OP.ROUTING_SEQUENCE_ID
AND SEQ.SR_INSTANCE_ID = OP.SR_INSTANCE_ID
AND SEQ.OPERATION_SEQUENCE_ID = OP.OPERATION_SEQUENCE_ID
AND RES.BASIS_TYPE in (1,2,3) --4694958
AND RES.PLAN_ID = SEQ.PLAN_ID
AND RES.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND RES.SR_INSTANCE_ID = SEQ.SR_INSTANCE_ID
AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
AND C1.CALENDAR_DATE = p_requested_date
-- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0)))
-- End Bug 3432530
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID -- krajan : 2408696 -- cchen
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
-- krajan: 2408696 - agilent
-- AND MUC1.UOM_CODE = l_uom_code
-- AND MUC1.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
-- AND MUC2.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CODE = RES.UOM_CODE
AND RES.UOM_CODE = l_uom_code
AND DR.PLAN_ID = RTG.PLAN_ID
AND DR.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND DR.RESOURCE_ID = RES.RESOURCE_ID
AND DR.DEPARTMENT_ID = OP.DEPARTMENT_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DR.CTP_FLAG = 1
--bug3601223 get the operations that lie on primary path
AND (l_network_scheduling_method = 2
OR
OP.OPERATION_SEQUENCE_ID IN
( SELECT FROM_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
UNION ALL
SELECT TO_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
)
)
UNION -- ALL
-- Obtain Option Dependent Routing
SELECT /*+ ordered */ DISTINCT DR.DEPARTMENT_ID department_id,
-- Distinct: Bug 3432530 For CTO we obtain the Op. Seq. related data only once.
DR.OWNING_DEPARTMENT_ID owning_department_id,
DR.RESOURCE_ID resource_id,
RES.BASIS_TYPE basis_type,
--bug 3766224: Do not chnage usage for lot based resource
ROUND(DECODE(RES.BASIS_TYPE, 2, NVL(RES.RESOURCE_USAGE,0),
(NVL(RES.RESOURCE_USAGE,0)*
-- krajan : 2408696
-- MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0*
--bug3601223 Only if network_scheduling_method is planning percent then use % else 100%
Decode(l_network_scheduling_method,2,
(NVL(OP.NET_PLANNING_PERCENT,100)/100),1)
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD,1)))
/(Decode (nvl (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,MSC_ATP_PVT.DISCRETE_ORG), MSC_ATP_PVT.OPM_ORG, --Bug-4694958
decode (RES.BASIS_TYPE, 3,
NVL(DR.MAX_CAPACITY,1), nvl(rtg.routing_quantity,1)
),
nvl(rtg.routing_quantity,1)
)
)),6) resource_usage, --4694958
-- Bug 2865389 (ssurendr) routing quantity added for OPM fix.
C2.CALENDAR_DATE requested_date,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0))) lead_time,
-- End Bug 3432530 Use local variables.
NVL((DR.EFFICIENCY/100), 1) efficiency,
NVL((DR.UTILIZATION/100), 1) utilization,
NVL(DR.BATCHABLE_FLAG, 2) batch_flag,
NVL(DR.MAX_CAPACITY,0) max_capacity,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's item data.
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_unit_wt, 2, l_item_unit_vol) required_unit_capacity,
---bug 1905284
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_wt_uom, 2, l_item_vol_uom) required_capacity_uom ,
-- End Bug 3432530 Use local variables.
DR.UNIT_OF_MEASURE res_uom,
DR.UOM_CLASS_TYPE res_uom_type,
OP.STANDARD_OPERATION_CODE std_op_code,
--diag_atp
SEQ.RESOURCE_OFFSET_PERCENT resource_offset_percent,
OP.OPERATION_SEQ_NUM operation_sequence,
RES.RESOURCE_USAGE actual_resource_usage,
--NVL(OP.REVERSE_CUMULATIVE_YIELD, 1) reverse_cumulative_yield ,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield ,--4694958
DR.Department_code department_code,
DR.resource_code resource_code
FROM
MSC_CTO_BOM mcbom1,
MSC_PROCESS_EFFECTIVITY proc,
MSC_CTO_BOM mcbom2,
-- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
MSC_ROUTINGS RTG,
MSC_ROUTING_OPERATIONS OP,
MSC_BOM_COMPONENTS mbc,
MSC_OPERATION_COMPONENTS moc,
MSC_OPERATION_RESOURCE_SEQS SEQ,
MSC_OPERATION_RESOURCES RES,
MSC_DEPARTMENT_RESOURCES DR, -- this is the sharing dept
MSC_CALENDAR_DATES C1,
MSC_CALENDAR_DATES C2
WHERE mcbom1.session_id = MSC_ATP_PVT.G_SESSION_ID
AND mcbom1.sr_instance_id = p_instance_id
-- Bug 3358981 line is a model then include,
AND (mcbom1.ATO_PARENT_MODEL_LINE_ID = l_parent_line_id OR mcbom1.line_id = l_parent_line_id)
-- get all lines having the same parent model End Bug 3358981
AND mcbom1.bom_item_type in (1, 2)
AND mcbom1.inventory_item_id =
decode(MSC_ATP_PVT.G_ITEM_INFO_REC.bom_item_type,
1, mcbom1.inventory_item_id,
2, l_inventory_item_id)
--AND (mcbom1.parent_line_id = p_line_identifier or
-- Handle situation when parent_line_id is null.
-- Basic thing is that this section should handle all cases.
-- mcbom1.inventory_item_id = l_inventory_item_id )
-- Join to msc_process_effectivity
AND proc.plan_id = p_plan_id
AND proc.sr_instance_id = mcbom1.sr_instance_id
AND proc.organization_id = p_organization_id
AND proc.item_id = mcbom1.inventory_item_id
-- Ensure that only items that have a common routing are processed with
-- the model. OC Items having a separate routing will be processed separately.
-- This check below with decode on the left side will be removed while
-- ones below that achieve the same thing retained if performance is an issue.
AND decode(MSC_ATP_PVT.G_ITEM_INFO_REC.bom_item_type, 1, -- bom_item_type for model
NVL(proc.routing_sequence_id, p_routing_seq_id),
proc.routing_sequence_id -- all other cases including option_classes
) = p_routing_seq_id
-- Quantity filter
AND mcbom1.quantity BETWEEN NVL(proc.minimum_quantity,0) AND
DECODE(NVL(proc.maximum_quantity,0),0,99999999,proc.maximum_quantity)
-- Date Filter
-- effective date should be greater than or equal to greatest of PTF date,
-- sysdate and start date, disable date
-- should be less than or equal to greatest of PTF date, sysdate and start date
-- Note p_requested_date is used instead of C2.calendar_date currently,
-- since p_requested_date is used in MSC_ATP_PROC.get_process_effectivity
-- and also from performance considerations.
AND TRUNC(proc.effectivity_date) <=
TRUNC(GREATEST(p_requested_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(NVL(proc.disable_date,GREATEST(p_requested_date, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1))
> TRUNC(GREATEST(p_requested_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
-- Join again to msc_cto_bom to obtain the components as well.
AND mcbom2.sr_instance_id = mcbom1.sr_instance_id
AND mcbom2.session_id = mcbom1.session_id
AND mcbom2.ato_parent_model_line_id = mcbom1.ATO_PARENT_MODEL_LINE_ID
AND NVL(mcbom2.parent_line_id, l_parent_line_id) = mcbom1.line_id
-- to obtain all option classes that have a common routing.
-- Get the routing
AND RTG.PLAN_ID = proc.plan_id
AND RTG.SR_INSTANCE_ID = proc.sr_instance_id -- Qry streamline 3358981
AND RTG.ORGANIZATION_ID = proc.organization_id
AND RTG.ROUTING_SEQUENCE_ID = NVL(proc.routing_sequence_id,
RTG.ROUTING_SEQUENCE_ID)
-- Bug 3432530
-- Comment out join conditions for msc_system_items
-- 3358981 Eliminate semi cartesian product, streamline query.
AND RTG.assembly_item_id = DECODE (proc.routing_sequence_id, NULL,
proc.item_id, l_inventory_item_id )
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
-- Join to system items
-- AND I.PLAN_ID = RTG.PLAN_ID
-- AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
-- AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
-- Bug 3358981
-- AND I.INVENTORY_ITEM_ID = RTG.assembly_item_id
-- 3358981 Eliminate semi cartesian product, streamline query.
-- Ensure that only items that have a common routing are processed with
-- the model. OC Items having a separate routing will be processed separately.
-- AND I.INVENTORY_ITEM_ID = DECODE (proc.routing_sequence_id, NULL,
-- RTG.assembly_item_id, l_inventory_item_id ) -- model's item_id
-- End Bug 3358981
-- End Bug 3432530
-- Get all operations for the routing
AND OP.PLAN_ID = RTG.PLAN_ID
AND OP.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND OP.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
-- filter only on those components that are in the pseudo bom
-- AND OP.option_dependent_flag = 1 --
/* Operation is of type Event (Do not select process) */
and NVL(OP.operation_type,1 ) = 1
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(OP.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(OP.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
-- Validate Model's BOM in sales order with model's bom in manufacturing org.
AND mbc.sr_instance_id = RTG.sr_instance_id -- Qry streamline 3358981
AND mbc.plan_id = RTG.plan_id
AND mbc.organization_id = RTG.organization_id
-- Bug 3358981
-- Ensure that only items that have a common routing are processed with
-- the model. OC Items having a separate routing will be processed separately.
AND mbc.bill_sequence_id = DECODE(proc.routing_sequence_id, NULL,
proc.bill_sequence_id, p_bill_seq_id)
-- End Bug 3358981
AND mbc.using_assembly_id = RTG.assembly_item_id -- Qry streamline 3358981
AND TRUNC(NVL(MBC.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE,
sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(MBC.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND mbc.inventory_item_id = mcbom2.inventory_item_id
-- Optional Items selected in the Sales Order
-- Select the option dependent operations which are needed
-- for the configuration
-- Join to determine all the operations
and moc.plan_id = mbc.plan_id
and moc.sr_instance_id = mbc.sr_instance_id
and moc.organization_id = mbc.organization_id
and moc.bill_sequence_id = mbc.bill_sequence_id
and moc.component_sequence_id = mbc.component_sequence_id
and moc.routing_sequence_id = rtg.routing_sequence_id
and moc.operation_sequence_id = OP.operation_sequence_id
-- Obtain the Resource Seq numbers.
AND SEQ.PLAN_ID = OP.PLAN_ID
AND SEQ.ROUTING_SEQUENCE_ID = OP.ROUTING_SEQUENCE_ID
AND SEQ.SR_INSTANCE_ID = OP.SR_INSTANCE_ID
AND SEQ.OPERATION_SEQUENCE_ID = OP.OPERATION_SEQUENCE_ID
AND RES.BASIS_TYPE in (1,2,3) --4694958
AND RES.PLAN_ID = SEQ.PLAN_ID
AND RES.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND RES.SR_INSTANCE_ID = SEQ.SR_INSTANCE_ID
AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
AND C1.CALENDAR_DATE = p_requested_date
-- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0)))
-- End Bug 3432530
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID -- krajan : 2408696 -- cchen
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
-- krajan: 2408696 - agilent
-- AND MUC1.UOM_CODE = l_uom_code
-- AND MUC1.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
-- AND MUC2.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CODE = RES.UOM_CODE
AND RES.UOM_CODE = l_uom_code
AND DR.PLAN_ID = RTG.PLAN_ID
AND DR.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND DR.RESOURCE_ID = RES.RESOURCE_ID
AND DR.DEPARTMENT_ID = OP.DEPARTMENT_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DR.CTP_FLAG = 1
--bug3601223 get the operations that lie on primary path
AND (l_network_scheduling_method = 2
OR
OP.OPERATION_SEQUENCE_ID IN
( SELECT FROM_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
UNION ALL
SELECT TO_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
)
)
)
ORDER BY requested_date, -- Bug 2313497 Ensure proper order in fetch
operation_sequence, resource_code;
SELECT department_id,
owning_department_id,
resource_id,
basis_type,
resource_usage,
requested_date,
lead_time,
efficiency,
utilization,
batch_flag,
max_capacity,
required_unit_capacity,
required_capacity_uom,
res_uom,
res_uom_type,
std_op_code,
--diag_atp
resource_offset_percent,
operation_sequence,
actual_resource_usage,
reverse_cumulative_yield,
department_code,
resource_code
BULK COLLECT INTO l_res_requirements.department_id,
l_res_requirements.owning_department_id,
l_res_requirements.resource_id,
l_res_requirements.basis_type,
l_res_requirements.resource_usage,
l_res_requirements.requested_date,
l_res_requirements.lead_time,
l_res_requirements.efficiency,
l_res_requirements.utilization,
--- these columns have been added for resource batching
l_res_requirements.batch_flag,
l_res_requirements.max_capacity,
l_res_requirements.required_unit_capacity,
l_res_requirements.required_capacity_uom,
l_res_requirements.res_uom,
l_res_requirements.res_uom_type,
l_res_requirements.std_op_code,
---diag_atp
l_res_requirements.resource_offset_percent,
l_res_requirements.operation_sequence,
l_res_requirements.actual_resource_usage,
l_res_requirements.reverse_cumulative_yield,
l_res_requirements.department_code,
l_res_requirements.resource_code
FROM (
SELECT /*+ ordered */ DR.DEPARTMENT_ID department_id,
DR.OWNING_DEPARTMENT_ID owning_department_id,
DR.RESOURCE_ID resource_id,
RES.BASIS_TYPE basis_type,
--bug 3766224: Do not chnage usage for lot based resource
ROUND(DECODE(RES.BASIS_TYPE, 2, NVL(RES.RESOURCE_USAGE,0),
(NVL(RES.RESOURCE_USAGE,0)*
-- krajan : 2408696
-- MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0*
--bug3601223 Only if network_scheduling_method is planning percent then use % else 100%
Decode(l_network_scheduling_method,2,
(NVL(OP.NET_PLANNING_PERCENT,100)/100),1)
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD,1)))
/(Decode (nvl (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,MSC_ATP_PVT.DISCRETE_ORG), MSC_ATP_PVT.OPM_ORG, --Bug-4694958
decode (RES.BASIS_TYPE, 3,
NVL(DR.MAX_CAPACITY,1), nvl(rtg.routing_quantity,1)
),
nvl(rtg.routing_quantity,1)
)
)),6) resource_usage, --4694958
-- Bug 2865389 (ssurendr) routing quantity added for OPM fix.
C2.CALENDAR_DATE requested_date,
CEIL(((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0))) lead_time,
NVL((DR.EFFICIENCY/100), 1) efficiency,
NVL((DR.UTILIZATION/100), 1) utilization,
NVL(DR.BATCHABLE_FLAG, 2) batch_flag,
NVL(DR.MAX_CAPACITY,0) max_capacity,
DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, I.UNIT_VOLUME) required_unit_capacity,
---bug 1905284
DECODE(DR.UOM_CLASS_TYPE, 1, I.WEIGHT_UOM, 2, I.VOLUME_UOM) required_capacity_uom ,
DR.UNIT_OF_MEASURE res_uom,
DR.UOM_CLASS_TYPE res_uom_type,
OP.STANDARD_OPERATION_CODE std_op_code,
--diag_atp
SEQ.RESOURCE_OFFSET_PERCENT resource_offset_percent,
OP.OPERATION_SEQ_NUM operation_sequence,
RES.RESOURCE_USAGE actual_resource_usage,
--NVL(OP.REVERSE_CUMULATIVE_YIELD, 1) reverse_cumulative_yield ,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield ,--4694958
DR.Department_code department_code,
DR.resource_code resource_code
FROM
-- krajan : 2408696
--agilent chnages: since plan already store the data in right uom, we dont need to convert it
-- MSC_UOM_CONVERSIONS MUC2,
-- MSC_UOM_CONVERSIONS MUC1,
MSC_SYSTEM_ITEMS I,
MSC_ROUTINGS RTG,
MSC_ROUTING_OPERATIONS OP,
MSC_OPERATION_RESOURCE_SEQS SEQ,
MSC_OPERATION_RESOURCES RES,
MSC_DEPARTMENT_RESOURCES DR, -- this is the sharing dept
MSC_CALENDAR_DATES C1,
MSC_CALENDAR_DATES C2
WHERE I.PLAN_ID = RTG.PLAN_ID
AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = p_instance_id
AND RTG.ORGANIZATION_ID = p_organization_id
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND OP.PLAN_ID = RTG.PLAN_ID
AND OP.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND OP.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(OP.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(OP.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
AND SEQ.PLAN_ID = OP.PLAN_ID
AND SEQ.ROUTING_SEQUENCE_ID = OP.ROUTING_SEQUENCE_ID
AND SEQ.SR_INSTANCE_ID = OP.SR_INSTANCE_ID
AND SEQ.OPERATION_SEQUENCE_ID = OP.OPERATION_SEQUENCE_ID
AND RES.BASIS_TYPE in (1,2,3) --4694958
AND RES.PLAN_ID = SEQ.PLAN_ID
AND RES.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND RES.SR_INSTANCE_ID = SEQ.SR_INSTANCE_ID
AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
AND C1.CALENDAR_DATE = p_requested_date
AND C1.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL(((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0)))
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID -- krajan : 2408696 -- cchen
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
-- krajan: 2408696 - agilent
-- AND MUC1.UOM_CODE = l_uom_code
-- AND MUC1.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
-- AND MUC2.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CODE = RES.UOM_CODE
AND RES.UOM_CODE = l_uom_code
AND DR.PLAN_ID = I.PLAN_ID
AND DR.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = I.ORGANIZATION_ID
AND DR.RESOURCE_ID = RES.RESOURCE_ID
AND DR.DEPARTMENT_ID = OP.DEPARTMENT_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DR.CTP_FLAG = 1
--bug3601223 get the operations that lie on primary path
AND (l_network_scheduling_method = 2
OR
OP.OPERATION_SEQUENCE_ID IN
( SELECT FROM_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
UNION ALL
SELECT TO_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
)
)
)
ORDER BY requested_date, -- Bug 2313497 Ensure proper order in fetch
operation_sequence, resource_code;
SELECT DISTINCT -- collapse common into one in case union all is used.
-- Uncomment: Bug 3432530 For CTO we obtain the Op. Seq. related data only once.
department_id,
owning_department_id,
resource_id,
basis_type,
resource_usage,
requested_date,
lead_time,
efficiency,
utilization,
batch_flag,
max_capacity,
required_unit_capacity,
required_capacity_uom,
res_uom,
res_uom_type,
std_op_code,
--diag_atp
resource_offset_percent,
operation_sequence,
actual_resource_usage,
reverse_cumulative_yield,
department_code,
resource_code
BULK COLLECT INTO l_res_requirements.department_id,
l_res_requirements.owning_department_id,
l_res_requirements.resource_id,
l_res_requirements.basis_type,
l_res_requirements.resource_usage,
l_res_requirements.requested_date,
l_res_requirements.lead_time,
l_res_requirements.efficiency,
l_res_requirements.utilization,
--- the following columns are added for resource batching
l_res_requirements.batch_flag,
l_res_requirements.max_capacity,
l_res_requirements.required_unit_capacity,
l_res_requirements.required_capacity_uom,
l_res_requirements.res_uom,
l_res_requirements.res_uom_type,
l_res_requirements.std_op_code,
---diag_atp
l_res_requirements.resource_offset_percent,
l_res_requirements.operation_sequence,
l_res_requirements.actual_resource_usage,
l_res_requirements.reverse_cumulative_yield,
l_res_requirements.department_code,
l_res_requirements.resource_code
FROM (
-- First select mandatory operations
-- for common routing cases the mandatory for option classes
-- will be clubbed together with the model.
SELECT /*+ ordered */ DISTINCT DR.DEPARTMENT_ID department_id,
-- Distinct: Bug 3432530 For CTO we obtain the Op. Seq. related data only once.
DR.OWNING_DEPARTMENT_ID owning_department_id,
DR.RESOURCE_ID resource_id,
RES.BASIS_TYPE basis_type,
--bug 3766224: Do not chnage usage for lot based resource
ROUND(DECODE(RES.BASIS_TYPE, 2, NVL(RES.RESOURCE_USAGE,0),
(NVL(RES.RESOURCE_USAGE,0)
-- krajan : 2408696
-- MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD,1)))
/(Decode (nvl (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,MSC_ATP_PVT.DISCRETE_ORG), MSC_ATP_PVT.OPM_ORG, --Bug-4694958
decode (RES.BASIS_TYPE, 3,
NVL(DR.MAX_CAPACITY,1), nvl(rtg.routing_quantity,1)
),
nvl(rtg.routing_quantity,1)
)
)),6) resource_usage, --4694958
-- Bug 2865389 (ssurendr) routing quantity added for OPM fix.
--(NVL(OP.NET_PLANNING_PERCENT,100)/100)/NVL(OP.REVERSE_CUMULATIVE_YIELD,1) resource_usage,
C2.CALENDAR_DATE requested_date,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0))) lead_time,
-- End Bug 3432530 Use local variables.
NVL((DR.EFFICIENCY/100), 1) efficiency,
NVL((DR.UTILIZATION/100), 1) utilization,
NVL(DR.BATCHABLE_FLAG, 2) batch_flag,
NVL(DR.MAX_CAPACITY,0) max_capacity,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's item data.
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_unit_wt, 2, l_item_unit_vol) required_unit_capacity,
---bug 1905284
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_wt_uom, 2, l_item_vol_uom) required_capacity_uom ,
-- End Bug 3432530 Use local variables.
DR.UNIT_OF_MEASURE res_uom,
DR.UOM_CLASS_TYPE res_uom_type,
OP.STANDARD_OPERATION_CODE std_op_code,
--diag_atp
SEQ.RESOURCE_OFFSET_PERCENT resource_offset_percent,
OP.OPERATION_SEQ_NUM operation_sequence,
RES.RESOURCE_USAGE actual_resource_usage,
--NVL(OP.REVERSE_CUMULATIVE_YIELD, 1) reverse_cumulative_yield,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield,--4694958
DR.Department_code Department_code,
DR.Resource_code Resource_code
FROM
MSC_CTO_BOM mcbom1,
-- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
MSC_ROUTINGS RTG,
MSC_ROUTING_OPERATIONS OP,
MSC_OPERATION_RESOURCE_SEQS SEQ,
MSC_OPERATION_RESOURCES RES,
MSC_DEPARTMENT_RESOURCES DR, -- this is the sharing dept
MSC_CALENDAR_DATES C1,
MSC_CALENDAR_DATES C2
WHERE mcbom1.session_id = MSC_ATP_PVT.G_SESSION_ID
AND mcbom1.sr_instance_id = p_instance_id
-- Bug 3358981 line is a model then include,
AND (mcbom1.ATO_PARENT_MODEL_LINE_ID = l_parent_line_id OR mcbom1.line_id = l_parent_line_id)
-- get all lines having the same parent model End Bug 3358981
AND ( --mcbom1.parent_line_id = p_line_identifier or
-- Handle situation when parent_line_id is null.
-- Basic thing is that this section should handle all cases.
mcbom1.inventory_item_id = l_inventory_item_id )
AND mcbom1.quantity <> 0
-- Get the routing
AND RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = mcbom1.sr_instance_id
AND RTG.ORGANIZATION_ID = p_organization_id
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id -- For common routing this will be null.
-- Bug 3432530
-- Comment out join conditions for msc_system_items
-- 3358981 Eliminate semi cartesian product, streamline query.
AND RTG.assembly_item_id = mcbom1.inventory_item_id
-- Join to system items
-- AND I.PLAN_ID = RTG.PLAN_ID
-- AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
-- AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
-- AND I.INVENTORY_ITEM_ID = RTG.assembly_item_id
-- 3358981 Eliminate semi cartesian product, streamline query.
-- AND I.INVENTORY_ITEM_ID = l_inventory_item_id
-- End Bug 3432530
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
-- Get all operations for the routing
AND OP.PLAN_ID = RTG.PLAN_ID
AND OP.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND OP.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
/* Operation is of type Event (Do not select process) */
and NVL(OP.operation_type,1 ) = 1
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(OP.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(OP.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
-- SMCs/Mandatory Operations
and OP.option_dependent_flag = 2
-- for the configuration
-- Obtain the Resource Seq numbers.
AND SEQ.PLAN_ID = OP.PLAN_ID
AND SEQ.ROUTING_SEQUENCE_ID = OP.ROUTING_SEQUENCE_ID
AND SEQ.SR_INSTANCE_ID = OP.SR_INSTANCE_ID
AND SEQ.OPERATION_SEQUENCE_ID = OP.OPERATION_SEQUENCE_ID
AND RES.BASIS_TYPE in (1,2,3) --4694958
AND RES.PLAN_ID = SEQ.PLAN_ID
AND RES.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND RES.SR_INSTANCE_ID = SEQ.SR_INSTANCE_ID
AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
AND C1.CALENDAR_DATE = p_requested_date
-- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0)))
-- End Bug 3432530
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID -- krajan : 2408696
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
-- krajan : 2408696
-- AND MUC1.UOM_CODE = l_uom_code
-- AND MUC1.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
-- AND MUC2.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CODE = RES.UOM_CODE
AND RES.UOM_CODE = l_uom_code
AND DR.PLAN_ID = RTG.PLAN_ID
AND DR.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND DR.RESOURCE_ID = RES.RESOURCE_ID
AND DR.DEPARTMENT_ID = OP.DEPARTMENT_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DR.CTP_FLAG = 1
UNION ALL
-- Obtain Option Dependent Routing
SELECT /*+ ordered */ DISTINCT DR.DEPARTMENT_ID department_id,
-- Distinct: Bug 3432530 For CTO we obtain the Op. Seq. related data only once.
DR.OWNING_DEPARTMENT_ID owning_department_id,
DR.RESOURCE_ID resource_id,
RES.BASIS_TYPE basis_type,
--bug 3766224: Do not chnage usage for lot based resource
ROUND(DECODE(RES.BASIS_TYPE, 2, NVL(RES.RESOURCE_USAGE,0),
(NVL(RES.RESOURCE_USAGE,0)
-- krajan : 2408696
-- MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD,1)))
/(Decode (nvl (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,MSC_ATP_PVT.DISCRETE_ORG), MSC_ATP_PVT.OPM_ORG, --Bug-4694958
decode (RES.BASIS_TYPE, 3,
NVL(DR.MAX_CAPACITY,1), nvl(rtg.routing_quantity,1)
),
nvl(rtg.routing_quantity,1)
)
)),6) resource_usage, --4694958
-- Bug 2865389 (ssurendr) routing quantity added for OPM fix.
--(NVL(OP.NET_PLANNING_PERCENT,100)/100)/NVL(OP.REVERSE_CUMULATIVE_YIELD,1) resource_usage,
C2.CALENDAR_DATE requested_date,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0))) lead_time,
-- End Bug 3432530 Use local variables.
NVL((DR.EFFICIENCY/100), 1) efficiency,
NVL((DR.UTILIZATION/100), 1) utilization,
NVL(DR.BATCHABLE_FLAG, 2) batch_flag,
NVL(DR.MAX_CAPACITY,0) max_capacity,
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's item data.
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_unit_wt, 2, l_item_unit_vol) required_unit_capacity,
---bug 1905284
DECODE(DR.UOM_CLASS_TYPE, 1, l_item_wt_uom, 2, l_item_vol_uom) required_capacity_uom ,
-- End Bug 3432530 Use local variables.
DR.UNIT_OF_MEASURE res_uom,
DR.UOM_CLASS_TYPE res_uom_type,
OP.STANDARD_OPERATION_CODE std_op_code,
--diag_atp
SEQ.RESOURCE_OFFSET_PERCENT resource_offset_percent,
OP.OPERATION_SEQ_NUM operation_sequence,
RES.RESOURCE_USAGE actual_resource_usage,
--NVL(OP.REVERSE_CUMULATIVE_YIELD, 1) reverse_cumulative_yield,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield,--4694958
DR.Department_code Department_code,
DR.Resource_code Resource_code
FROM
MSC_CTO_BOM mcbom1,
MSC_PROCESS_EFFECTIVITY proc,
MSC_CTO_BOM mcbom2,
-- MSC_SYSTEM_ITEMS I, Bug 3432530 Comment out Join table
MSC_ROUTINGS RTG,
MSC_ROUTING_OPERATIONS OP,
MSC_BOM_COMPONENTS mbc,
MSC_OPERATION_COMPONENTS moc,
MSC_OPERATION_RESOURCE_SEQS SEQ,
MSC_OPERATION_RESOURCES RES,
MSC_DEPARTMENT_RESOURCES DR, -- this is the sharing dept
MSC_CALENDAR_DATES C1,
MSC_CALENDAR_DATES C2
WHERE mcbom1.session_id = MSC_ATP_PVT.G_SESSION_ID
AND mcbom1.sr_instance_id = p_instance_id
-- Bug 3358981 line is a model then include,
AND (mcbom1.ATO_PARENT_MODEL_LINE_ID = l_parent_line_id OR mcbom1.line_id = l_parent_line_id)
-- get all lines having the same parent model End Bug 3358981
AND mcbom1.bom_item_type in (1, 2)
AND mcbom1.inventory_item_id =
decode(MSC_ATP_PVT.G_ITEM_INFO_REC.bom_item_type,
1, mcbom1.inventory_item_id,
2, l_inventory_item_id)
--AND (mcbom1.parent_line_id = p_line_identifier or
-- Handle situation when parent_line_id is null.
-- Basic thing is that this section should handle all cases.
-- mcbom1.inventory_item_id = l_inventory_item_id )
-- Join to msc_process_effectivity
AND proc.plan_id = p_plan_id
AND proc.sr_instance_id = mcbom1.sr_instance_id
AND proc.organization_id = p_organization_id
AND proc.item_id = mcbom1.inventory_item_id
-- Ensure that only items that have a common routing are processed with
-- the model. OC Items having a separate routing will be processed separately.
-- This check below with decode on the left side will be removed while
-- ones below that achieve the same thing retained if performance is an issue.
AND decode(MSC_ATP_PVT.G_ITEM_INFO_REC.bom_item_type, 1, -- bom_item_type for model
NVL(proc.routing_sequence_id, p_routing_seq_id),
proc.routing_sequence_id -- all other cases including option_classes
) = p_routing_seq_id
-- Quantity filter
AND mcbom1.quantity BETWEEN NVL(proc.minimum_quantity,0) AND
DECODE(NVL(proc.maximum_quantity,0),0,99999999,proc.maximum_quantity)
-- Date Filter
-- effective date should be greater than or equal to greatest of PTF date,
-- sysdate and start date, disable date
-- should be less than or equal to greatest of PTF date, sysdate and start date
-- Note p_requested_date is used instead of C2.calendar_date currently,
-- since p_requested_date is used in MSC_ATP_PROC.get_process_effectivity
-- and also from performance considerations.
AND TRUNC(proc.effectivity_date) <=
TRUNC(GREATEST(p_requested_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(NVL(proc.disable_date,GREATEST(p_requested_date, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1))
> TRUNC(GREATEST(p_requested_date, sysdate, MSC_ATP_PVT.G_PTF_DATE))
-- Join again to msc_cto_bom to obtain the components as well.
AND mcbom2.sr_instance_id = mcbom1.sr_instance_id
AND mcbom2.session_id = mcbom1.session_id
AND mcbom2.ato_parent_model_line_id = mcbom1.ATO_PARENT_MODEL_LINE_ID
AND NVL(mcbom2.parent_line_id, l_parent_line_id) = mcbom1.line_id
-- to obtain all option classes that have a common routing.
-- Get the routing
AND RTG.PLAN_ID = proc.plan_id
AND RTG.SR_INSTANCE_ID = proc.sr_instance_id -- Qry Streamline 3358981
AND RTG.ORGANIZATION_ID = proc.organization_id
AND RTG.ROUTING_SEQUENCE_ID = NVL(proc.routing_sequence_id,
RTG.ROUTING_SEQUENCE_ID)
-- Bug 3432530
-- Comment out join conditions for msc_system_items
-- 3358981 Eliminate semi cartesian product, streamline query.
AND RTG.assembly_item_id = DECODE (proc.routing_sequence_id, NULL,
proc.item_id, l_inventory_item_id )
-- Join to system items
-- AND I.PLAN_ID = RTG.PLAN_ID
-- AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
-- AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
-- Bug 3358981
-- AND I.INVENTORY_ITEM_ID = RTG.assembly_item_id
-- 3358981 Eliminate semi cartesian product, streamline query.
-- Ensure that only items that have a common routing are processed with
-- the model. OC Items having a separate routing will be processed separately.
-- AND I.INVENTORY_ITEM_ID = DECODE (proc.routing_sequence_id, NULL,
-- RTG.assembly_item_id, l_inventory_item_id ) -- model's item_id
-- End Bug 3358981
-- End Bug 3432530
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
-- Get all operations for the routing
AND OP.PLAN_ID = RTG.PLAN_ID
AND OP.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND OP.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
-- filter only on those components that are in the pseudo bom
-- AND OP.option_dependent_flag = 1 --
/* Operation is of type Event (Do not select process) */
and NVL(OP.operation_type,1 ) = 1
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(OP.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(OP.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
-- Validate Model's BOM in sales order with model's bom in manufacturing org.
AND mbc.sr_instance_id = RTG.sr_instance_id -- Qry Streamline 3358981
AND mbc.plan_id = RTG.plan_id
AND mbc.organization_id = RTG.organization_id
-- Bug 3358981
-- Ensure that only items that have a common routing are processed with
-- the model. OC Items having a separate routing will be processed separately.
AND mbc.bill_sequence_id = DECODE(proc.routing_sequence_id, NULL,
proc.bill_sequence_id, p_bill_seq_id)
-- End Bug 3358981
AND mbc.using_assembly_id = RTG.assembly_item_id -- Qry Streamline 3358981
AND TRUNC(NVL(MBC.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE,
sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(MBC.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND mbc.inventory_item_id = mcbom2.inventory_item_id
-- Optional Items selected in the Sales Order
-- Select the option dependent operations which are needed.
-- for the configuration
-- Join to determine all the operations
and moc.plan_id = mbc.plan_id
and moc.sr_instance_id = mbc.sr_instance_id
and moc.organization_id = mbc.organization_id
and moc.bill_sequence_id = mbc.bill_sequence_id
and moc.component_sequence_id = mbc.component_sequence_id
and moc.routing_sequence_id = rtg.routing_sequence_id
and moc.operation_sequence_id = OP.operation_sequence_id
-- Obtain the Resource Seq numbers.
AND SEQ.PLAN_ID = OP.PLAN_ID
AND SEQ.ROUTING_SEQUENCE_ID = OP.ROUTING_SEQUENCE_ID
AND SEQ.SR_INSTANCE_ID = OP.SR_INSTANCE_ID
AND SEQ.OPERATION_SEQUENCE_ID = OP.OPERATION_SEQUENCE_ID
AND RES.BASIS_TYPE in (1,2,3) --4694958
AND RES.PLAN_ID = SEQ.PLAN_ID
AND RES.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND RES.SR_INSTANCE_ID = SEQ.SR_INSTANCE_ID
AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
AND C1.CALENDAR_DATE = p_requested_date
-- Bug 3432530 Use RTG instead of MSC_SYSTEM_ITEMS I
AND C1.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
-- Bug 3432530 Use local variables.
-- In case of common routing, use model's lead times.
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL(((NVL(l_item_fixed_lt,0)+
NVL(l_item_var_lt,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0)))
-- End Bug 3432530
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID -- krajan : 2408696
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
-- krajan : 2408696
-- AND MUC1.UOM_CODE = l_uom_code
-- AND MUC1.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
-- AND MUC2.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CODE = RES.UOM_CODE
AND RES.UOM_CODE = l_uom_code
AND DR.PLAN_ID = RTG.PLAN_ID
AND DR.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND DR.RESOURCE_ID = RES.RESOURCE_ID
AND DR.DEPARTMENT_ID = OP.DEPARTMENT_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DR.CTP_FLAG = 1
UNION ALL
SELECT RTG.LINE_ID department_id,
RTG.LINE_ID owning_department_id ,
-1 resource_id,
1 basis_type,
1 resource_usage,
C2.CALENDAR_DATE requested_date,
CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor)) lead_time,
1 efficiency,
1 utilization,
2 batch_flag,
0 max_capacity,
0 required_unit_capacity,
--bug 2845383: Change all direct reference to null to local variables
l_null_char required_capacity_uom,
l_null_char res_uom,
1 res_uom_type,
l_null_char std_op_code,
--diag_atp
l_null_num resource_offset_percent,
l_null_num operation_sequence,
1 actual_resource_usage,
l_null_num reverse_cumulative_yield,
l_null_char department_code,
l_null_char resource_Code
FROM MSC_CALENDAR_DATES C2,
MSC_CALENDAR_DATES C1,
MSC_ROUTINGS RTG,
MSC_SYSTEM_ITEMS I
WHERE I.PLAN_ID = RTG.PLAN_ID
AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = p_instance_id
AND RTG.ORGANIZATION_ID = p_organization_id
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND RTG.CTP_FLAG = 1
AND RTG.LINE_ID IS NOT NULL
AND C1.CALENDAR_DATE = p_requested_date
AND C1.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
)
ORDER by requested_date, -- Bug 2313497 Ensure proper order in fetch
operation_sequence, resource_code;
SELECT department_id,
owning_department_id,
resource_id,
basis_type,
resource_usage,
requested_date,
lead_time,
efficiency,
utilization,
batch_flag,
max_capacity,
required_unit_capacity,
required_capacity_uom,
res_uom,
res_uom_type,
std_op_code,
--diag_atp
resource_offset_percent,
operation_sequence,
actual_resource_usage,
reverse_cumulative_yield,
department_code,
resource_code
BULK COLLECT INTO l_res_requirements.department_id,
l_res_requirements.owning_department_id,
l_res_requirements.resource_id,
l_res_requirements.basis_type,
l_res_requirements.resource_usage,
l_res_requirements.requested_date,
l_res_requirements.lead_time,
l_res_requirements.efficiency,
l_res_requirements.utilization,
--- the following columns are added for resource batching
l_res_requirements.batch_flag,
l_res_requirements.max_capacity,
l_res_requirements.required_unit_capacity,
l_res_requirements.required_capacity_uom,
l_res_requirements.res_uom,
l_res_requirements.res_uom_type,
l_res_requirements.std_op_code,
---diag_atp
l_res_requirements.resource_offset_percent,
l_res_requirements.operation_sequence,
l_res_requirements.actual_resource_usage,
l_res_requirements.reverse_cumulative_yield,
l_res_requirements.department_code,
l_res_requirements.resource_code
FROM (
SELECT /*+ ordered */ DR.DEPARTMENT_ID department_id,
DR.OWNING_DEPARTMENT_ID owning_department_id,
DR.RESOURCE_ID resource_id,
RES.BASIS_TYPE basis_type,
--bug 3766224: Do not chnage usage for lot based resource
ROUND(DECODE(RES.BASIS_TYPE, 2, NVL(RES.RESOURCE_USAGE,0),
(NVL(RES.RESOURCE_USAGE,0)
-- krajan : 2408696
-- MUC2.CONVERSION_RATE/MUC1.CONVERSION_RATE, 0
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD,1)))
/(Decode (nvl (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,MSC_ATP_PVT.DISCRETE_ORG), MSC_ATP_PVT.OPM_ORG, --Bug-4694958
decode (RES.BASIS_TYPE, 3,
NVL(DR.MAX_CAPACITY,1), nvl(rtg.routing_quantity,1)
),
nvl(rtg.routing_quantity,1)
)
)),6) resource_usage, --4694958
-- Bug 2865389 (ssurendr) routing quantity added for OPM fix.
--(NVL(OP.NET_PLANNING_PERCENT,100)/100)/NVL(OP.REVERSE_CUMULATIVE_YIELD,1) resource_usage,
C2.CALENDAR_DATE requested_date,
CEIL(((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0))) lead_time,
NVL((DR.EFFICIENCY/100), 1) efficiency,
NVL((DR.UTILIZATION/100), 1) utilization,
NVL(DR.BATCHABLE_FLAG, 2) batch_flag,
NVL(DR.MAX_CAPACITY,0) max_capacity,
DECODE(DR.UOM_CLASS_TYPE, 1, I.UNIT_WEIGHT, 2, I.UNIT_VOLUME) required_unit_capacity,
--1905284
DECODE(DR.UOM_CLASS_TYPE, 1, I.WEIGHT_UOM, 2, I.VOLUME_UOM) required_capacity_uom,
DR.UNIT_OF_MEASURE res_uom,
DR.UOM_CLASS_TYPE res_uom_type,
OP.STANDARD_OPERATION_CODE std_op_code,
--diag_atp
SEQ.RESOURCE_OFFSET_PERCENT resource_offset_percent,
OP.OPERATION_SEQ_NUM operation_sequence,
RES.RESOURCE_USAGE actual_resource_usage,
--NVL(OP.REVERSE_CUMULATIVE_YIELD, 1) reverse_cumulative_yield,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,1,NVL(OP.REVERSE_CUMULATIVE_YIELD, 1)) reverse_cumulative_yield,--4694958
DR.Department_code Department_code,
DR.Resource_code Resource_code
FROM
MSC_SYSTEM_ITEMS I,
MSC_ROUTINGS RTG,
MSC_ROUTING_OPERATIONS OP,
MSC_OPERATION_RESOURCE_SEQS SEQ,
MSC_OPERATION_RESOURCES RES,
MSC_DEPARTMENT_RESOURCES DR,
MSC_CALENDAR_DATES C1,
MSC_CALENDAR_DATES C2
WHERE I.PLAN_ID = RTG.PLAN_ID
AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = p_instance_id
AND RTG.ORGANIZATION_ID = p_organization_id
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND OP.PLAN_ID = RTG.PLAN_ID
AND OP.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND OP.ROUTING_SEQUENCE_ID = RTG.ROUTING_SEQUENCE_ID
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(OP.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(OP.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
AND SEQ.PLAN_ID = OP.PLAN_ID
AND SEQ.ROUTING_SEQUENCE_ID = OP.ROUTING_SEQUENCE_ID
AND SEQ.SR_INSTANCE_ID = OP.SR_INSTANCE_ID
AND SEQ.OPERATION_SEQUENCE_ID = OP.OPERATION_SEQUENCE_ID
AND RES.BASIS_TYPE in (1,2,3) --4694958
AND RES.PLAN_ID = SEQ.PLAN_ID
AND RES.ROUTING_SEQUENCE_ID = SEQ.ROUTING_SEQUENCE_ID
AND RES.SR_INSTANCE_ID = SEQ.SR_INSTANCE_ID
AND RES.OPERATION_SEQUENCE_ID = SEQ.OPERATION_SEQUENCE_ID
AND RES.RESOURCE_SEQ_NUM = SEQ.RESOURCE_SEQ_NUM
AND NVL(RES.ALTERNATE_NUMBER, 0) = 0 -- bug 1170698
AND C1.CALENDAR_DATE = p_requested_date
AND C1.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL(((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))*
(1-NVL(SEQ.RESOURCE_OFFSET_PERCENT, 0)))
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID -- krajan : 2408696
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
-- krajan : 2408696
-- AND MUC1.UOM_CODE = l_uom_code
-- AND MUC1.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CLASS = MUC1.UOM_CLASS
-- AND MUC2.INVENTORY_ITEM_ID = 0
-- AND MUC2.UOM_CODE = RES.UOM_CODE
AND RES.UOM_CODE = l_uom_code
AND DR.PLAN_ID = I.PLAN_ID
AND DR.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND DR.ORGANIZATION_ID = I.ORGANIZATION_ID
AND DR.RESOURCE_ID = RES.RESOURCE_ID
AND DR.DEPARTMENT_ID = OP.DEPARTMENT_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DR.CTP_FLAG = 1
UNION ALL
SELECT RTG.LINE_ID department_id,
RTG.LINE_ID owning_department_id ,
-1 resource_id,
1 basis_type,
1 resource_usage,
C2.CALENDAR_DATE requested_date,
CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor)) lead_time,
1 efficiency,
1 utilization,
2 batch_flag,
0 max_capacity,
0 required_unit_capacity,
--bug 2845383: Change all direct reference to null to local variables
l_null_char required_capacity_uom,
l_null_char res_uom,
1 res_uom_type,
l_null_char std_op_code,
--diag_atp
l_null_num resource_offset_percent,
l_null_num operation_sequence,
1 actual_resource_usage,
l_null_num reverse_cumulative_yield,
l_null_char department_code,
l_null_char resource_Code
FROM MSC_CALENDAR_DATES C2,
MSC_CALENDAR_DATES C1,
MSC_ROUTINGS RTG,
MSC_SYSTEM_ITEMS I
WHERE I.PLAN_ID = RTG.PLAN_ID
AND I.SR_INSTANCE_ID = RTG.SR_INSTANCE_ID
AND I.INVENTORY_ITEM_ID = RTG.ASSEMBLY_ITEM_ID
AND I.ORGANIZATION_ID = RTG.ORGANIZATION_ID
AND RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = p_instance_id
AND RTG.ORGANIZATION_ID = p_organization_id
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--We are Driving by routing table for performance gains.
--AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND RTG.CTP_FLAG = 1
AND RTG.LINE_ID IS NOT NULL
AND C1.CALENDAR_DATE = p_requested_date
AND C1.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)* p_requested_quantity) * (1+ l_mso_lead_time_factor))
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
)
ORDER by requested_date, -- Bug 2313497 Ensure proper order in fetch
operation_sequence, resource_code;
SELECT b.department_id,
dr.owning_department_id,
b.resource_id,
b.basis,
DECODE(b.resource_id, -1, 1, b.resource_department_hours),
c2.calendar_date,
b.setback_days,
nvl((dr.efficiency/100), 1),
nvl((dr.utilization/100), 1),
--- even though resource batching is not used in case of BOR
--- the following columns are added because the process
-- for all cases is done by the same code. Adding following
--- columns will ensure proper extension of tables
2 batch_flag,
0 max_capacity,
0 required_unit_capacity,
--2845383: change direct refrence to null to local varibale
l_null_char required_capacity_uom,
l_null_char res_uom,
l_null_char res_uom_type,
l_null_char std_op_code,
--diag_atp
l_null_num resource_offset_percent,
l_null_num operation_sequence,
DECODE(b.resource_id, -1, 1, b.resource_department_hours) actual_resource_usage,
1 reverse_cumulative_yield,
dr.department_code department_code,
dr.resource_code resource_Code
BULK COLLECT INTO l_res_requirements.department_id,
l_res_requirements.owning_department_id,
l_res_requirements.resource_id,
l_res_requirements.basis_type,
l_res_requirements.resource_usage,
l_res_requirements.requested_date,
l_res_requirements.lead_time,
l_res_requirements.efficiency,
l_res_requirements.utilization,
--- the following columns are added for resource batching
l_res_requirements.batch_flag,
l_res_requirements.max_capacity,
l_res_requirements.required_unit_capacity,
l_res_requirements.required_capacity_uom ,
l_res_requirements.res_uom,
l_res_requirements.res_uom_type,
l_res_requirements.std_op_code,
---diag_atp
l_res_requirements.resource_offset_percent,
l_res_requirements.operation_sequence,
l_res_requirements.actual_resource_usage,
l_res_requirements.reverse_cumulative_yield,
l_res_requirements.department_code,
l_res_requirements.resource_code
FROM msc_department_resources dr,
msc_bor_requirements b,
msc_calendar_dates c1,
msc_calendar_dates c2
WHERE B.PLAN_ID = p_plan_id
AND B.SR_INSTANCE_ID = p_instance_id
AND B.ORGANIZATION_ID = p_organization_id
AND B.ASSEMBLY_ITEM_ID = l_inv_item_id
-- Chnaged on 01/09/2001 by ngoel for performance improvement
-- MSC_ATP_FUNC.get_inv_item_id(p_instance_id, p_inventory_item_id, p_organization_id)
AND C1.CALENDAR_DATE = p_requested_date
AND C1.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = l_calendar_code
AND C1.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - B.SETBACK_DAYS
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND C2.EXCEPTION_SET_ID = C1.EXCEPTION_SET_ID
AND DR.PLAN_ID = B.PLAN_ID
AND DR.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND DR.RESOURCE_ID = B.RESOURCE_ID
AND DR.DEPARTMENT_ID = B.DEPARTMENT_ID
AND DR.ORGANIZATION_ID = B.ORGANIZATION_ID
-- performance dsting remove nvl from dr.ctp_flag
AND DECODE(DR.LINE_FLAG, 1, 1, DR.CTP_FLAG) = 1
AND (DR.LINE_FLAG <> 1
OR
(DR.LINE_FLAG = 1 AND
EXISTS ( SELECT 'CTP'
FROM MSC_ROUTINGS RTG
WHERE RTG.PLAN_ID = p_plan_id
AND RTG.SR_INSTANCE_ID = B.SR_INSTANCE_ID
AND RTG.ASSEMBLY_ITEM_ID = B.ASSEMBLY_ITEM_ID
AND RTG.ORGANIZATION_ID = B.ORGANIZATION_ID
AND RTG.ROUTING_SEQUENCE_ID = p_routing_seq_id
--(ssurendr) Bug 2865389 OPM fix
-- AND RTG.ALTERNATE_ROUTING_DESIGNATOR IS NULL
AND RTG.LINE_ID = B.DEPARTMENT_ID
AND NVL(RTG.CTP_FLAG, 2) = 1)))
ORDER BY C2.CALENDAR_DATE; -- Bug 2313497 Ensure proper order in fetch
SELECT BOMS.ASSEMBLY_QUANTITY
INTO l_assembly_quantity
FROM MSC_BOMS BOMS
WHERE BOMS.PLAN_ID = p_plan_id
AND BOMS.SR_INSTANCE_ID = p_instance_id
AND BOMS.ORGANIZATION_ID = p_organization_id
AND BOMS.BILL_SEQUENCE_ID = p_bill_seq_id;
SELECT conversion_rate
INTO l_item_conversion_rate
FROM msc_uom_conversions
WHERE inventory_item_id = 0
AND sr_instance_id = p_instance_id
AND UOM_CODE = l_req_capacity_uom;
SELECT conversion_rate
INTO l_res_conversion_rate
FROM msc_uom_conversions
WHERE inventory_item_id = 0
AND sr_instance_id = p_instance_id
AND UOM_CODE = l_res_uom;
p_insert_flag,
l_batching_flag,
MSC_ATP_PVT.G_Optimized_Plan,
p_instance_id,
p_organization_id,
p_plan_id,
l_plan_start_date,
l_department_id,
l_resource_id,
l_infinite_time_fence_date,
l_uom_type,
l_uom_code,
l_max_capacity,
l_res_conversion_rate,
p_level,
p_scenario_id,
p_inventory_item_id,
l_calendar_code,
l_calendar_exception_set_id,
l_summary_sql, -- For summary enhancement
p_refresh_number, -- For summary enhancement
l_atp_period_tab,
l_atp_qty_tab,
l_atp_period
);
p_insert_flag,
l_max_capacity,
l_batchable_flag,
l_res_conversion_rate,
l_uom_type,
l_atp_info,
l_atp_period,
l_atp_supply_demand);
SELECT IDENTIFIER3
INTO l_supply_id
FROM MRP_ATP_DETAILS_TEMP
WHERE PEGGING_ID = p_parent_pegging_id
AND RECORD_TYPE = 3
AND SESSION_ID = MSC_ATP_PVT.G_SESSION_ID;
SELECT IDENTIFIER3
INTO l_supply_id
FROM MRP_ATP_DETAILS_TEMP
WHERE PEGGING_ID = p_parent_pegging_id
AND RECORD_TYPE = 3
AND SESSION_ID = MSC_ATP_PVT.G_SESSION_ID;
IF p_insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_pegging_id,
MSC_ATP_PVT.G_DEMAND_PEGGING_ID);
p_insert_flag IN NUMBER,
p_search IN NUMBER,
p_assign_set_id IN NUMBER,
--(ssurendr) Bug 2865389 Added routing Sequence id and Bill sequence id for OPM issue.
p_routing_seq_id IN NUMBER,
p_bill_seq_id IN NUMBER,
p_family_id IN NUMBER, -- For time_phased_atp
p_atf_date IN DATE, -- For time_phased_atp
p_manufacturing_cal_code IN VARCHAR2, -- For ship_rec_cal
x_avail_assembly_qty OUT NoCopy NUMBER,
x_atp_date OUT NoCopy DATE,
x_atp_period OUT NOCOPY MRP_ATP_PUB.ATP_Period_Typ,
x_atp_supply_demand OUT NOCOPY MRP_ATP_PUB.ATP_Supply_Demand_Typ,
x_return_status OUT NoCopy VARCHAR2,
p_comp_info_rec IN OUT NOCOPY MSC_ATP_REQ.get_comp_info_rec,
p_order_number IN NUMBER := NULL,
p_op_seq_id IN NUMBER --4570421
-- Add new parameter with default value to support creation of
-- Sales Orders for CTO components in a MATO case.
)
IS
l_comp_requirements MRP_ATP_PVT.Atp_Comp_Typ;
select v1.SR_INVENTORY_ITEM_ID,
v1.INVENTORY_ITEM_ID,
v1.qty,
v1.CALENDAR_DATE,
v1.PROCESSING_LEAD_TIME,
v1.WIP_SUPPLY_TYPE,
v1.PREPROCESSING_LEAD_TIME,
v1.REVERSE_CUMULATIVE_YIELD,
v1.AGGREGATE_TIME_FENCE_DATE,
v1.UOM_CODE, --bug3110023
v1.scaling_type,
v1.SCALE_MULTIPLE,
v1.SCALE_ROUNDING_VARIANCE,
v1.ROUNDING_DIRECTION,
v1.component_yield_factor,
v1.usage_qty --4775920
from
(SELECT I2.SR_INVENTORY_ITEM_ID,
I2.INVENTORY_ITEM_ID, --(3004862) circular BOM issue: also select destination id
--4570421
--4862863, dividing by assembly_qty only in non-lot and non-fix cases.
ROUND ((decode (NVL ( MSC_ATP_PVT.G_ORG_INFO_REC.org_type, MSC_ATP_PVT.DISCRETE_ORG), 1, decode (nvl (mbc.scaling_type, 1), 1, (MBC.USAGE_QUANTITY*l_requested_quantity)
/Decode (MSC_ATP_PVT.G_PLAN_COPRODUCTS, 'Y', NVL (BOMS.ASSEMBLY_QUANTITY,1), 1),
2, MBC.USAGE_QUANTITY),
MSC_ATP_PVT.OPM_ORG, decode (nvl (mbc.scaling_type, 1), 0, MBC.USAGE_QUANTITY,
1, (MBC.USAGE_QUANTITY*l_requested_quantity)
/Decode (MSC_ATP_PVT.G_PLAN_COPRODUCTS, 'Y', NVL (BOMS.ASSEMBLY_QUANTITY,1), 1),
2, MBC.USAGE_QUANTITY,
3, (MBC.USAGE_QUANTITY*l_requested_quantity)
/Decode (MSC_ATP_PVT.G_PLAN_COPRODUCTS, 'Y', NVL (BOMS.ASSEMBLY_QUANTITY,1), 1),
4, (MBC.USAGE_QUANTITY*l_requested_quantity)
/Decode (MSC_ATP_PVT.G_PLAN_COPRODUCTS, 'Y', NVL (BOMS.ASSEMBLY_QUANTITY,1), 1),
5, (MBC.USAGE_QUANTITY*l_requested_quantity)
/Decode (MSC_ATP_PVT.G_PLAN_COPRODUCTS, 'Y', NVL (BOMS.ASSEMBLY_QUANTITY,1), 1))
))
--/Decode (MSC_ATP_PVT.G_PLAN_COPRODUCTS, 'Y', NVL (BOMS.ASSEMBLY_QUANTITY,1), 1) --4862863
* DECODE (l_routing_type, 3,Decode (l_network_scheduling_method,
2,(NVL(OP.NET_PLANNING_PERCENT,100)/100),1), 1)
--/NVL (OP.REVERSE_CUMULATIVE_YIELD, DECODE (l_routing_type, 3, NVL (l_first_op_RCY, 1), 1))
/DECODE(OP.REVERSE_CUMULATIVE_YIELD,
0,
DECODE(l_routing_type,
3, NVL(l_first_op_RCY, 1)
,1
),
NVL(OP.REVERSE_CUMULATIVE_YIELD, DECODE(l_routing_type,
3,
NVL(l_first_op_RCY, 1),1
)
)
)
--/NVL (mbc.component_yield_factor, 1) --4767982
,6) qty,
C2.CALENDAR_DATE,
--bug 4106269 changes start here
/*----------------------------------------------------------------------
We will include the Lead time for phantom items based on following parameter
1)MSC: ATP explode phantom components
2)Bom Parameter -Use Phantom Routing
3)Bom Parameter -Inherit Phantom Op-Seq
Various combinations are
Case1 :
MSC: ATP explode phantom components =Yes
Use Phantom Routing =N/a
Inherit Phantom Op-Seq =N/a
Creates supply for ATPable phantom and uses its LT and Routing like a standard item.
Case 2:
MSC: ATP explode phantom components =No
Use Phantom Routing =No
Inherit Phantom Op-Seq =No
Ignore ATPable phantom's Lead Time for calculating components requirement dates.
Phantom is exploded to its components and no supply/resource requirements are created for phantom.
Case 3:
MSC: ATP explode phantom components =No
Use Phantom Routing =Yes
Inherit Phantom Op-Seq =No
Adds ATPable phantom's Lead Time for calculating components requirement dates.
Phantom is exploded to its components and no supply/resource requirements are created for phantom.
Case 4:
MSC: ATP explode phantom components =No
Use Phantom Routing =No
Inherit Phantom Op-Seq =Yes
Ignore ATPable phantom's Lead Time for calculating components requirement dates.
Phantom is exploded to its components and no supply/resource requirements are created for phantom.
Case 5:
MSC: ATP explode phantom components =No
Use Phantom Routing =Yes
Inherit Phantom Op-Seq =Yes
Adds ATPable phantom's Lead Time for calculating components requirement dates.
Phantom is exploded to its components and no supply/resource requirements are created for phantom.
--------------------------------------------------------------------------*/
DECODE(l_wip_supply_type,
6,
DECODE(MSC_ATP_PVT.G_EXPLODE_PHANTOM,
'N',
decode(nvl(MSC_ATP_PVT.G_ORG_INFO_REC.use_phantom_routings,2),
2,
0,
CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)*
l_requested_quantity
)
*(1 + l_mso_lead_time_factor)
)
),
CEIL( ( NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)*
l_requested_quantity
)*
(1 + l_mso_lead_time_factor)
)
),
CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)*
l_requested_quantity)*
(1 + l_mso_lead_time_factor)
)
) PROCESSING_LEAD_TIME,
--4106269
MBC.WIP_SUPPLY_TYPE,
--bug3609031 adding ceil
NVL(ceil(I.PREPROCESSING_LEAD_TIME),0) PREPROCESSING_LEAD_TIME,
--diag_atp
--NVL(OP.REVERSE_CUMULATIVE_YIELD, DECODE(l_routing_type, 3, NVL(l_first_op_RCY, 1),1)) REVERSE_CUMULATIVE_YIELD,
DECODE(OP.REVERSE_CUMULATIVE_YIELD,0,DECODE(l_routing_type, 3, NVL(l_first_op_RCY, 1),1),NVL(OP.REVERSE_CUMULATIVE_YIELD, DECODE(l_routing_type, 3, NVL(l_first_op_RCY, 1),1))) REVERSE_CUMULATIVE_YIELD, --4694958
-- time_phased_atp
I2.AGGREGATE_TIME_FENCE_DATE,
OP.OPERATION_SEQUENCE_ID,
I2.UOM_CODE, --bug3110023
--4570421
mbc.scaling_type scaling_type,
mbc.scale_multiple scale_multiple,
mbc.scale_rounding_variance scale_rounding_variance,
mbc.rounding_direction rounding_direction,
mbc.component_yield_factor component_yield_factor,
MBC.USAGE_QUANTITY*mbc.component_yield_factor usage_qty --4775920
FROM MSC_SYSTEM_ITEMS I2,
MSC_CALENDAR_DATES C2,
MSC_CALENDAR_DATES C1,
MSC_BOM_COMPONENTS MBC,
MSC_BOMS BOMS,
MSC_SYSTEM_ITEMS I,
MSC_OPERATION_COMPONENTS OPC,
MSC_ROUTING_OPERATIONS OP
WHERE I.PLAN_ID = BOMS.PLAN_ID
AND I.SR_INSTANCE_ID = BOMS.SR_INSTANCE_ID
AND I.INVENTORY_ITEM_ID = BOMS.ASSEMBLY_ITEM_ID
AND I.ORGANIZATION_ID = BOMS.ORGANIZATION_ID
AND BOMS.PLAN_ID = p_plan_id
AND BOMS.SR_INSTANCE_ID = p_instance_id
AND BOMS.ORGANIZATION_ID = p_organization_id
AND BOMS.BILL_SEQUENCE_ID = l_bill_seq_id --(3004862) circular BOM issue: use cursor parameter
--(ssurendr) Bug 2865389 Removed condition for Alternate bom designator as
--we are accessing bom by bill sequance id.
--We can now drive by msc_boms for performance gains
--AND BOMS.ALTERNATE_BOM_DESIGNATOR IS NULL
AND MBC.USAGE_QUANTITY > 0
AND MBC.BILL_SEQUENCE_ID = BOMS.BILL_SEQUENCE_ID
AND MBC.PLAN_ID = I.PLAN_ID
AND MBC.SR_INSTANCE_ID = I.SR_INSTANCE_ID
--s_cto_rearch: we do not look at atp flags any more. Slection of components is contolled by
--atp flags setting of the components itself
--AND MBC.ATP_FLAG = 1
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(MBC.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(MBC.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
AND C1.CALENDAR_DATE = l_requested_date
AND C1.SR_INSTANCE_ID = I.SR_INSTANCE_ID
--4106269
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - DECODE(l_wip_supply_type,
6,
DECODE(MSC_ATP_PVT.G_EXPLODE_PHANTOM,
'N',
decode(nvl(MSC_ATP_PVT.G_ORG_INFO_REC.use_phantom_routings,2),
2,
0,
CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)*
l_requested_quantity
)
*(1 + l_mso_lead_time_factor)
)
),
CEIL( ( NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)*
l_requested_quantity
)*
(1 + l_mso_lead_time_factor)
)
),
CEIL((NVL(I.FIXED_LEAD_TIME,0)+
NVL(I.VARIABLE_LEAD_TIME,0)*
l_requested_quantity)*
(1 + l_mso_lead_time_factor)
)
)--4106269
AND C2.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND I2.INVENTORY_ITEM_ID = MBC.INVENTORY_ITEM_ID
AND I2.ORGANIZATION_ID =MBC.ORGANIZATION_ID
AND I2.PLAN_ID = I.PLAN_ID
--4570421
AND C1.CALENDAR_CODE = MSC_ATP_PVT.G_ORG_INFO_REC.CAL_CODE
AND C1.EXCEPTION_SET_ID = MSC_ATP_PVT.G_ORG_INFO_REC.CAL_EXCEPTION_SET_ID
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.EXCEPTION_SET_ID = MSC_ATP_PVT.G_ORG_INFO_REC.CAL_EXCEPTION_SET_ID
--s_cto_rearch
-- select only atpable components. For model and option class, we do not look at atp components
--flag as they do not carry any significance once config is created
AND (I2.atp_flag <> 'N' or I2.atp_components_flag <> DECODE(I2.BOM_ITEM_TYPE,
1, I2.atp_components_flag,
2, I2.atp_components_flag,
'N'))
AND I2.SR_INSTANCE_ID = I.SR_INSTANCE_ID
AND OPC.PLAN_ID (+) = MBC.PLAN_ID
AND OPC.ORGANIZATION_ID (+) = MBC.ORGANIZATION_ID
AND OPC.SR_INSTANCE_ID (+) = MBC.SR_INSTANCE_ID
AND OPC.COMPONENT_SEQUENCE_ID (+) = MBC.COMPONENT_SEQUENCE_ID
AND OPC.BILL_SEQUENCE_ID (+) = MBC.BILL_SEQUENCE_ID
AND OP.PLAN_ID (+) = OPC.PLAN_ID
AND OP.SR_INSTANCE_ID (+) = OPC.SR_INSTANCE_ID
AND OP.OPERATION_SEQUENCE_ID (+) = OPC.OPERATION_SEQUENCE_ID
AND OP.ROUTING_SEQUENCE_ID (+) = OPC.ROUTING_SEQUENCE_ID ) v1
--bug3601223 get the components that are used on primary path
where l_routing_type <> 3
OR l_network_scheduling_method = 2
OR v1.OPERATION_SEQUENCE_ID IS NULL
OR v1.OPERATION_SEQUENCE_ID IN
( SELECT FROM_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
UNION ALL
SELECT TO_OP_SEQ_ID
FROM MSC_OPERATION_NETWORKS
WHERE PLAN_ID = p_plan_id
AND SR_INSTANCE_ID = p_instance_id
AND ROUTING_SEQUENCE_ID = p_routing_seq_id
AND TRANSITION_TYPE = 1
);
SELECT I2.SR_INVENTORY_ITEM_ID,
I2.INVENTORY_ITEM_ID, --(3004862) circular BOM issue: also select destination id
--ROUND((MBC.USAGE_QUANTITY * l_requested_quantity ),6),
--4570421
ROUND ((decode (NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type, MSC_ATP_PVT.DISCRETE_ORG), 1, decode ( nvl(mbc.scaling_type, 1), 1, (MBC.USAGE_QUANTITY*l_requested_quantity),
2, MBC.USAGE_QUANTITY),
MSC_ATP_PVT.OPM_ORG, decode (nvl (mbc.scaling_type, 1), 0, MBC.USAGE_QUANTITY,
1, (MBC.USAGE_QUANTITY*l_requested_quantity),
2, MBC.USAGE_QUANTITY,
3, (MBC.USAGE_QUANTITY*l_requested_quantity),
4, (MBC.USAGE_QUANTITY*l_requested_quantity),
5, (MBC.USAGE_QUANTITY*l_requested_quantity))
)) --/NVL (mbc.component_yield_factor, 1) --4767982
,6),
C2.CALENDAR_DATE,
CEIL((NVL(MSC_ATP_PVT.G_ITEM_INFO_REC.FIXED_LT,0)+
NVL(MSC_ATP_PVT.G_ITEM_INFO_REC.VARIABLE_LT,0)* l_requested_quantity)*(1 + l_mso_lead_time_factor)),
DECODE(MBC.WIP_SUPPLY_TYPE, 6, l_def_wip_sup_type, MBC.WIP_SUPPLY_TYPE), -- phantoms not be supported for DRP plans
NVL(ceil(MSC_ATP_PVT.G_ITEM_INFO_REC.PRE_PRO_LT),0),
1, -- default for RCY Yield unsuppored for DRP plans.
l_def_atf_date, -- ATF will be NULL as a default for DRP plans.
I2.UOM_CODE, --bug3110023
--4570421
mbc.scaling_type,
mbc.scale_multiple,
mbc.scale_rounding_variance ,
mbc.rounding_direction,
mbc.component_yield_factor, --4570421
MBC.USAGE_QUANTITY*mbc.component_yield_factor --4775920
FROM MSC_SYSTEM_ITEMS I2,
MSC_CALENDAR_DATES C2,
MSC_CALENDAR_DATES C1,
MSC_BOM_COMPONENTS MBC,
MSC_BOMS BOMS
WHERE BOMS.PLAN_ID = p_plan_id
AND BOMS.SR_INSTANCE_ID = p_instance_id
AND BOMS.ORGANIZATION_ID = p_organization_id
AND BOMS.BILL_SEQUENCE_ID = l_bill_seq_id --(3004862) circular BOM issue: use cursor parameter
AND MBC.USAGE_QUANTITY > 0
AND MBC.BILL_SEQUENCE_ID = BOMS.BILL_SEQUENCE_ID
AND MBC.PLAN_ID = BOMS.PLAN_ID
AND MBC.SR_INSTANCE_ID = BOMS.SR_INSTANCE_ID
AND TRUNC(NVL(MBC.DISABLE_DATE, GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(MBC.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(C2.CALENDAR_DATE, sysdate, MSC_ATP_PVT.G_PTF_DATE)) -- bug 1404312
AND C1.CALENDAR_DATE = l_requested_date
AND C1.SR_INSTANCE_ID = BOMS.SR_INSTANCE_ID
AND C1.CALENDAR_CODE = MSC_ATP_PVT.G_ORG_INFO_REC.CAL_CODE
AND C1.EXCEPTION_SET_ID = MSC_ATP_PVT.G_ORG_INFO_REC.CAL_EXCEPTION_SET_ID
AND C2.SEQ_NUM = C1.PRIOR_SEQ_NUM - CEIL((NVL(MSC_ATP_PVT.G_ITEM_INFO_REC.FIXED_LT,0)+
NVL(MSC_ATP_PVT.G_ITEM_INFO_REC.VARIABLE_LT,0)* l_requested_quantity)*(1 + l_mso_lead_time_factor))
AND C2.CALENDAR_CODE = C1.CALENDAR_CODE
AND C2.SR_INSTANCE_ID = C1.SR_INSTANCE_ID
AND C2.EXCEPTION_SET_ID = MSC_ATP_PVT.G_ORG_INFO_REC.CAL_EXCEPTION_SET_ID
AND I2.INVENTORY_ITEM_ID = MBC.INVENTORY_ITEM_ID
AND I2.ORGANIZATION_ID =MBC.ORGANIZATION_ID
AND I2.PLAN_ID = BOMS.PLAN_ID
-- select only atpable components.
AND I2.atp_flag <> 'N'
AND I2.SR_INSTANCE_ID = BOMS.SR_INSTANCE_ID;
SELECT mbt.component_item_id,
(mbt.quantity * l_requested_quantity),
c2.calendar_date,
DECODE(l_wip_supply_type,
6, 0,
CEIL((NVL(mbt.fixed_lt,0)+
NVL(mbt.variable_lt,0) * l_requested_quantity)*(1 + l_mso_lead_time_factor))) lead_time,
wip_supply_type,
mbt.assembly_identifier,
mbt.component_identifier,
mbt.pre_process_lt,
-- krajan : 2400614
mbt.source_organization_id,
-- krajan : 2462661
mbt.atp_flag
FROM msc_bom_temp mbt,
msc_calendar_dates c2,
msc_calendar_dates c1,
msc_trading_partners tp
WHERE mbt.session_id = MSC_ATP_PVT.G_SESSION_ID
--AND mbt.assembly_identifier = MSC_ATP_PVT.G_ASSEMBLY_LINE_ID
AND mbt.assembly_identifier = MSC_ATP_PVT.G_COMP_LINE_ID
AND mbt.assembly_item_id = l_inventory_item_id
/* rajjain 3008611
* effective date should be greater than or equal to greatest of PTF date, sysdate and start date
* disable date should be less than or equal to greatest of PTF date, sysdate and start date*/
AND TRUNC(NVL(mbt.disable_date, GREATEST(sysdate, c2.calendar_date, MSC_ATP_PVT.G_PTF_DATE)+1)) >
TRUNC(GREATEST(sysdate, c2.calendar_date, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(mbt.effective_date) <=
TRUNC(GREATEST(sysdate, c2.calendar_date, MSC_ATP_PVT.G_PTF_DATE))
AND c1.calendar_date = l_requested_date
AND c1.sr_instance_id = tp.sr_instance_id
AND c1.calendar_code = tp.calendar_code
AND c1.exception_set_id = tp.calendar_exception_set_id
AND tp.sr_instance_id = p_instance_id
AND tp.sr_tp_id = p_organization_id
AND tp.partner_type = 3
AND c2.seq_num = c1.prior_seq_num -
DECODE(l_wip_supply_type,
6, 0,
CEIL((NVL(mbt.fixed_lt,0)+
NVL(mbt.variable_lt,0) * l_requested_quantity)*(1 + l_mso_lead_time_factor)))
AND c2.calendar_code = tp.calendar_code
AND c2.sr_instance_id = tp.sr_instance_id
AND c2.exception_set_id = tp.calendar_exception_set_id;
SELECT r.cfm_routing_flag, NVL(op.reverse_cumulative_yield,1)
INTO l_routing_type, l_first_op_RCY
FROM msc_routings r, msc_routing_operations op
WHERE r.plan_id = p_plan_id and
r.organization_id = p_organization_id and
r.sr_instance_id = p_instance_id and
-- r.assembly_item_id = l_inv_item_id and
-- (3004862) changed to l_explode_comp.component_identifier so that for i>1 phantom item's id is used.
r.assembly_item_id = l_explode_comp.component_identifier(i) and
r.routing_sequence_id = p_routing_seq_id and
--(ssurendr) Bug 2865389 Removed condition for Alternate Routing designator as
--we are accessing Routing by Routing sequance id.
--r.alternate_routing_designator IS NULL and
r.plan_id = op.plan_id and
r.sr_instance_id = op.sr_instance_id and
--r.organization_id = op.organization_id and
r.routing_sequence_id = op.routing_sequence_id and
NVL(r.first_op_seq_num,op.operation_seq_num) = op.operation_seq_num and -- bug4114765
rownum = 1; -- Bug 4143668, just pick one for case where r.first_op_seq_num is NULL
SELECT r.cfm_routing_flag, r.first_op_seq_num
INTO l_routing_type, l_first_op_seq_num
FROM msc_routings r
WHERE r.plan_id = p_plan_id
AND r.sr_instance_id = p_instance_id
AND r.routing_sequence_id = l_routing_seq_id; --4570421 replaced p_routing_seq_id by l_routing_seq_id
SELECT MIN(op.operation_seq_num)
INTO l_first_op_seq_num
FROM msc_routing_operations op
WHERE op.routing_sequence_id = p_routing_seq_id
AND op.sr_instance_id = p_instance_id
AND op.plan_id = p_plan_id;
SELECT DECODE(op.reverse_cumulative_yield,0,1,NVL(op.reverse_cumulative_yield,1)) --4694958
INTO l_first_op_RCY
FROM msc_routing_operations op
WHERE op.plan_id = p_plan_id
AND op.sr_instance_id = p_instance_id
AND op.routing_sequence_id = l_routing_seq_id --4570421 replaced p_routing_seq_id by l_routing_seq_id
AND op.operation_seq_num = l_first_op_seq_num
and op.operation_sequence_id = l_op_seq_id; --4570421
select identifier3
into l_atp_rec.demand_source_line
from mrp_atp_details_temp
where pegging_id = p_parent_pegging_id
and record_type = 3
and session_id = MSC_ATP_PVT.G_SESSION_ID;
l_atp_rec.insert_flag := p_insert_flag;
msc_sch_wb.atp_debug('Get_Supplier_Atp_Info: ' || 'insert_flag: '|| to_char(p_sup_atp_info_rec.insert_flag));
IF (NVL(p_sup_atp_info_rec.insert_flag, 0) <> 0) THEN
-- dsting clear sd details temp table
MSC_ATP_DB_UTILS.Clear_SD_Details_Temp();
SELECT rownum
INTO l_tolerence_defined
FROM msc_supplier_flex_fences
WHERE plan_id = p_sup_atp_info_rec.plan_id
AND sr_instance_id = p_sup_atp_info_rec.instance_id
AND organization_id = p_sup_atp_info_rec.organization_id
--s_cto_rearch
--AND inventory_item_id = p_sup_atp_info_rec.inventory_item_id
AND inventory_item_id = l_inv_item_id
AND supplier_id = p_sup_atp_info_rec.supplier_id
AND supplier_site_id = p_sup_atp_info_rec.supplier_site_id
AND rownum = 1;
SELECT tp.calendar_code
INTO l_calendar_code
FROM msc_trading_partners tp,
msc_plans mp
WHERE mp.plan_id = p_sup_atp_info_rec.plan_id
AND tp.sr_instance_id = mp.sr_instance_id
AND tp.partner_type = 3
AND tp.sr_tp_id = mp.organization_id;
IF (NVL(p_sup_atp_info_rec.insert_flag, 0) = 0) THEN
IF (l_tolerence_defined = 0) AND (MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y') THEN
---- we do summary approach only if tolerance is not defined
---- since one of the components for calculating tolerance is difference of sys_date and
---- request date, we might not get a right data if we include tolerance in summary data
-- Summary enhancement - check summary flag
SELECT summary_flag
INTO l_summary_flag
FROM msc_plans plans
WHERE plans.plan_id = p_sup_atp_info_rec.plan_id;
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO l_atp_period_tab, l_atp_qty_tab
FROM
(
select /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) */
sd_date,
sd_qty
from msc_atp_summary_sup
where plan_id = p_sup_atp_info_rec.plan_id
and sr_instance_id = p_sup_atp_info_rec.instance_id
and supplier_id = p_sup_atp_info_rec.supplier_id
and supplier_site_id = p_sup_atp_info_rec.supplier_site_id
--and sd_date >= l_plan_start_date
and sd_date BETWEEN l_plan_start_date
AND least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date) --4055719
and sd_qty <> 0
AND (inventory_item_id = l_inv_item_id OR
inventory_item_id in
(select inventory_item_id from msc_system_items msi
where msi.base_item_id = l_inv_item_id
and msi.plan_id = p_sup_atp_info_rec.plan_id
and msi.organization_id = p_sup_atp_info_rec.organization_id
and msi.base_item_id = l_inv_item_id))
UNION ALL
-- Summary enhancement : differences from non summary SQL: ship/rec cal changes pending
-- 1. No union with MSC_SUPPLIER_CAPACITIES
-- 2. MSC_PLANS included in the join to get latest refresh number
-- 3. Filter records based on refresh_number
--Fixing as a part of bug3709707 adding trunc so that 2 column are not seen in HP
SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
1, p.new_ship_date,
p.new_dock_date)) l_date, -- For ship_rec_cal
(NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
FROM msc_supplies p,
msc_plans pl -- For summary enhancement
WHERE (p.order_type IN (5, 2,60)
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 = p_sup_atp_info_rec.plan_id
AND p.sr_instance_id = p_sup_atp_info_rec.instance_id
AND p.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
AND (p.inventory_item_id = l_inv_item_id OR
p.inventory_item_id in
(select inventory_item_id from msc_system_items msi
where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
and msi.plan_id = p_sup_atp_info_rec.plan_id
and msi.organization_id = p_sup_atp_info_rec.organization_id
and msi.base_item_id = l_inv_item_id))
AND pl.plan_id = p.plan_id -- For summary enhancement
AND (p.refresh_number > pl.latest_refresh_number -- For summary enhancement
OR p.refresh_number = p_sup_atp_info_rec.refresh_number) -- For summary enhancement
AND Decode(p_sup_atp_info_rec.sup_cap_type, 1, p.new_ship_date,p.new_dock_date) --4055719
<= least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date)
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO l_atp_period_tab, l_atp_qty_tab
FROM
(
select /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) */
trunc( sd_date) SD_DATE, --4135752
sd_qty
from msc_atp_summary_sup
where plan_id = p_sup_atp_info_rec.plan_id
and sr_instance_id = p_sup_atp_info_rec.instance_id
and inventory_item_id = p_sup_atp_info_rec.inventory_item_id
and supplier_id = p_sup_atp_info_rec.supplier_id
and supplier_site_id = p_sup_atp_info_rec.supplier_site_id
--and sd_date >= l_plan_start_date
and sd_date BETWEEN l_plan_start_date
AND least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date) --4055719
and sd_qty <> 0
UNION ALL
-- Summary enhancement : differences from non summary SQL: ship/rec cal changes pending
-- 1. No union with MSC_SUPPLIER_CAPACITIES
-- 2. MSC_PLANS included in the join to get latest refresh number
-- 3. Filter records based on refresh_number
--Fixing as a part of bug3709707 adding trunc so that 2 column are not seen in HP
SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
1, p.new_ship_date,
p.new_dock_date)) l_date, -- For ship_rec_cal
(NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
FROM msc_supplies p,
msc_plans pl -- For summary enhancement
WHERE (p.order_type IN (5, 2, 60)
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 = p_sup_atp_info_rec.plan_id
AND p.sr_instance_id = p_sup_atp_info_rec.instance_id
AND p.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
AND p.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
AND pl.plan_id = p.plan_id -- For summary enhancement
AND (p.refresh_number > pl.latest_refresh_number -- For summary enhancement
OR p.refresh_number = p_sup_atp_info_rec.refresh_number) -- For summary enhancement
AND Decode(p_sup_atp_info_rec.sup_cap_type, 1, trunc(p.new_ship_date),trunc(p.new_dock_date)) --4055719 --4135752
<= trunc(least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date)) --4135752
)
GROUP BY SD_DATE
ORDER BY SD_DATE;
SELECT trunc(l_date), SUM(quantity) --4135752
BULK COLLECT INTO
l_atp_period_tab,
l_atp_qty_tab
FROM (
SELECT c.calendar_date l_date, s.capacity*(1+
DECODE(l_tolerence_defined, 0, 0,
NVL(MSC_ATP_FUNC.get_tolerance_percentage(
p_sup_atp_info_rec.instance_id,
p_sup_atp_info_rec.plan_id,
l_inv_item_id,
p_sup_atp_info_rec.organization_id,
p_sup_atp_info_rec.supplier_id,
p_sup_atp_info_rec.supplier_site_id,
-- ship_rec_cal
c.seq_num - p_sup_atp_info_rec.sysdate_seq_num),0))) quantity
FROM msc_calendar_dates c,
msc_supplier_capacities s
WHERE s.inventory_item_id = l_inv_item_id
AND s.sr_instance_id = p_sup_atp_info_rec.instance_id
AND s.plan_id = p_sup_atp_info_rec.plan_id
AND s.organization_id = p_sup_atp_info_rec.organization_id
AND s.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(s.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND c.calendar_date BETWEEN trunc(s.from_date)
--AND NVL(s.to_date,l_cutoff_date)
AND trunc(NVL(s.to_date,least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date))) --4055719
AND (c.seq_num IS NOT NULL OR p_sup_atp_info_rec.manufacturing_cal_code = MSC_CALENDAR.FOC)
AND c.calendar_code = l_calendar_code
AND c.exception_set_id = l_calendar_exception_set_id
AND c.sr_instance_id = s.sr_instance_id -- Changed from l_instance_id ?
AND c.calendar_date >= p_sup_atp_info_rec.sup_cap_cum_date
-- Supplier Capacity (SCLT) Accumulation starts from this date.
-- AND c.calendar_date >= l_plan_start_date -- bug 1169467
UNION ALL
/* Net out planned orders, purchase orders and purchase requisitions */
-- bug 1303196
--Fixing as a part of bug3709707 adding trunc so that 2 column are not seen in HP
SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
1, p.new_ship_date,
p.new_dock_date)) l_date, -- For ship_rec_cal
-- performance dsting rearrange signs to get rid of multiply times -1
(NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
FROM msc_supplies p
WHERE (p.order_type IN (5, 2, 60)
--include purchase orders based on profile option
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))
-- Supplier Capacity (SCLT) Accumulation Ignore Purchase Orders
-- WHERE p.order_type IN (5, 1, 2)
AND p.plan_id = p_sup_atp_info_rec.plan_id
AND p.sr_instance_id = p_sup_atp_info_rec.instance_id
-- 1214694 AND p.organization_id = p_sup_atp_info_rec.organization_id
AND p.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
-- Exclude Cancelled Supplies 2460645
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
-- Supplier Capacity (SCLT) Changes End
---only consider ATP inserted POs
--- Plan POs are tied to forecast.
AND ((p.inventory_item_id = l_inv_item_id and p.record_source=2) OR
p.inventory_item_id in
(select inventory_item_id from msc_system_items msi
where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
and msi.plan_id = p_sup_atp_info_rec.plan_id
and msi.organization_id = p_sup_atp_info_rec.organization_id
and msi.base_item_id = l_inv_item_id))
AND trunc(Decode(p_sup_atp_info_rec.sup_cap_type, 1, p.new_ship_date,p.new_dock_date)) --4055719 --4135752
<= trunc(least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date)))
GROUP BY l_date
ORDER BY l_date;
SELECT trunc(l_date), SUM(quantity) --4135752
BULK COLLECT INTO
l_atp_period_tab,
l_atp_qty_tab
FROM (
SELECT c.calendar_date l_date, s.capacity*(1+
DECODE(l_tolerence_defined, 0, 0,
NVL(MSC_ATP_FUNC.get_tolerance_percentage(
p_sup_atp_info_rec.instance_id,
p_sup_atp_info_rec.plan_id,
p_sup_atp_info_rec.inventory_item_id,
p_sup_atp_info_rec.organization_id,
p_sup_atp_info_rec.supplier_id,
p_sup_atp_info_rec.supplier_site_id,
-- ship_rec_cal
c.seq_num - p_sup_atp_info_rec.sysdate_seq_num),0))) quantity
FROM msc_calendar_dates c,
msc_supplier_capacities s
WHERE s.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
AND s.sr_instance_id = p_sup_atp_info_rec.instance_id
AND s.plan_id = p_sup_atp_info_rec.plan_id
AND s.organization_id = p_sup_atp_info_rec.organization_id
AND s.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(s.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND c.calendar_date BETWEEN trunc(s.from_date)
--AND NVL(s.to_date,l_cutoff_date)
AND trunc(NVL(s.to_date,least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date))) --4055719
AND (c.seq_num IS NOT NULL OR p_sup_atp_info_rec.manufacturing_cal_code = MSC_CALENDAR.FOC)
AND c.calendar_code = l_calendar_code
AND c.exception_set_id = l_calendar_exception_set_id
AND c.sr_instance_id = s.sr_instance_id -- Changed from l_instance_id ?
AND c.calendar_date >= trunc(p_sup_atp_info_rec.sup_cap_cum_date) --4135752
-- Supplier Capacity (SCLT) Accumulation starts from this date.
-- AND c.calendar_date >= l_plan_start_date -- bug 1169467
UNION ALL
/* Net out planned orders, purchase orders and purchase requisitions */
-- bug 1303196
--Fixing as a part of bug3709707 adding trunc so that 2 column are not seen in HP
SELECT TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
1, p.new_ship_date,
p.new_dock_date)) l_date, -- For ship_rec_cal
-- performance dsting rearrange signs to get rid of multiply times -1
(NVL(p.implement_quantity,0) - p.new_order_quantity) quantity
FROM msc_supplies p
WHERE (p.order_type IN (5, 2, 60)
--include purchase orders based on profile option
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))
-- Supplier Capacity (SCLT) Accumulation Ignore Purchase Orders
-- WHERE p.order_type IN (5, 1, 2)
AND p.plan_id = p_sup_atp_info_rec.plan_id
AND p.sr_instance_id = p_sup_atp_info_rec.instance_id
AND p.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
-- 1214694 AND p.organization_id = p_sup_atp_info_rec.organization_id
AND p.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
-- Exclude Cancelled Supplies 2460645
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2
AND trunc( Decode(p_sup_atp_info_rec.sup_cap_type, 1, p.new_ship_date,p.new_dock_date)) --4055719 --4135752
<= trunc(least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date)))
GROUP BY l_date
ORDER BY l_date;
ELSE -- now p_sup_atp_info_rec.insert_flag <> 0
MSC_ATP_DB_UTILS.Clear_SD_Details_temp();
INSERT INTO msc_atp_sd_details_temp (
ATP_level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
(SELECT
p_sup_atp_info_rec.level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_sup_atp_info_rec.scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_sup_atp_info_rec.organization_id col6,
l_null_num col7,
l_null_num col8,
p_sup_atp_info_rec.supplier_id col9,
p_sup_atp_info_rec.supplier_site_id col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
l_uom_code col16,
2 col17, -- supply
l_null_num col18,
l_null_char col19,
p_sup_atp_info_rec.instance_id col20,
l_null_num col21,
l_null_num col22,
l_null_num col23,
s.capacity*(1+ DECODE(l_tolerence_defined, 0, 0,
NVL(MSC_ATP_FUNC.get_tolerance_percentage(
p_sup_atp_info_rec.instance_id,
p_sup_atp_info_rec.plan_id,
l_inv_item_id,
p_sup_atp_info_rec.organization_id,
p_sup_atp_info_rec.supplier_id,
p_sup_atp_info_rec.supplier_site_id,
-- ship_rec_cal
c.seq_num - p_sup_atp_info_rec.sysdate_seq_num),0))) col24,
C.CALENDAR_DATE col25,
l_null_num col26,
l_null_char col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM msc_calendar_dates c,
msc_supplier_capacities s
WHERE s.inventory_item_id = l_inv_item_id
AND s.sr_instance_id = p_sup_atp_info_rec.instance_id
AND s.plan_id = p_sup_atp_info_rec.plan_id
AND s.organization_id = p_sup_atp_info_rec.organization_id
AND s.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(s.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND c.calendar_date BETWEEN trunc(s.from_date)
--AND NVL(s.to_date,l_cutoff_date)
AND trunc(NVL(s.to_date,least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date))) --4055719
AND (c.seq_num IS NOT NULL OR p_sup_atp_info_rec.manufacturing_cal_code = MSC_CALENDAR.FOC)
AND c.calendar_code = l_calendar_code
AND c.exception_set_id = l_calendar_exception_set_id
AND c.sr_instance_id = s.sr_instance_id -- Changed from l_instance_id ?
AND c.calendar_date >= trunc(p_sup_atp_info_rec.sup_cap_cum_date) --4135752
-- Supplier Capacity (SCLT) Accumulation starts from this date.
-- AND c.calendar_date >= l_plan_start_date -- bug 1169467
UNION ALL
SELECT
p_sup_atp_info_rec.level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_sup_atp_info_rec.scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_sup_atp_info_rec.organization_id col6,
l_null_num col7,
l_null_num col8,
p_sup_atp_info_rec.supplier_id col9,
p_sup_atp_info_rec.supplier_site_id col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
l_uom_code col16,
1 col17, -- demand
p.order_type col18,
l_null_char col19,
p_sup_atp_info_rec.instance_id col20,
l_null_num col21,
TRANSACTION_ID col22,
l_null_num col23,
-- performance dsting rearrange signs to get rid of multiply times - 1
(NVL(p.implement_quantity,0) - p.new_order_quantity) col24,
--Fixing as a part of bug3709707 adding trunc so that 2 column are not seen in HP
TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
1, p.new_ship_date,
p.new_dock_date)) col25, -- For ship_rec_cal
l_null_num col26,
--bug 4493399: show transaction id for PO
--p.order_number col27,
DECODE(p.ORDER_TYPE, 5, to_char(p.TRANSACTION_ID), p.order_number) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
-- Supplier Capacity (SCLT) Changes Begin
FROM msc_supplies p
WHERE (p.order_type IN (5, 2, 60)
--include purchase orders based on profile option
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))
-- Supplier Capacity (SCLT) Accumulation Ignore Purchase Orders
-- WHERE p.order_type IN (5, 1, 2)
AND p.plan_id = p_sup_atp_info_rec.plan_id
AND p.sr_instance_id = p_sup_atp_info_rec.instance_id
--AND p.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
-- 1214694 AND p.organization_id = p_sup_atp_info_rec.organization_id
AND p.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
-- Exclude Cancelled Supplies 2460645
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2 -- Bug 2460645
---we only consider ATP inserted PO for MOdels.
--Ignore Planning inserted POs for models as they would be tied to forecats
AND ((p.inventory_item_id = l_inv_item_id and p.record_source=2) OR
p.inventory_item_id in
(select inventory_item_id from msc_system_items msi
where msi.sr_instance_id = p_sup_atp_info_rec.instance_id
and msi.plan_id = p_sup_atp_info_rec.plan_id
and msi.organization_id = p_sup_atp_info_rec.organization_id
and msi.base_item_id = l_inv_item_id))
AND Decode(p_sup_atp_info_rec.sup_cap_type, 1, p.new_ship_date,p.new_dock_date) --4055719
<= least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date)
);
INSERT INTO msc_atp_sd_details_temp (
ATP_level,
Order_line_id,
Scenario_Id,
Inventory_Item_Id,
Request_Item_Id,
Organization_Id,
Department_Id,
Resource_Id,
Supplier_Id,
Supplier_Site_Id,
From_Organization_Id,
From_Location_Id,
To_Organization_Id,
To_Location_Id,
Ship_Method,
UOM_code,
Supply_Demand_Type,
Supply_Demand_Source_Type,
Supply_Demand_Source_Type_Name,
Identifier1,
Identifier2,
Identifier3,
Identifier4,
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
)
(SELECT
p_sup_atp_info_rec.level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_sup_atp_info_rec.scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_sup_atp_info_rec.organization_id col6,
l_null_num col7,
l_null_num col8,
p_sup_atp_info_rec.supplier_id col9,
p_sup_atp_info_rec.supplier_site_id col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
l_uom_code col16,
2 col17, -- supply
l_null_num col18,
l_null_char col19,
p_sup_atp_info_rec.instance_id col20,
l_null_num col21,
l_null_num col22,
l_null_num col23,
s.capacity*(1+ DECODE(l_tolerence_defined, 0, 0,
NVL(MSC_ATP_FUNC.get_tolerance_percentage(
p_sup_atp_info_rec.instance_id,
p_sup_atp_info_rec.plan_id,
p_sup_atp_info_rec.inventory_item_id,
p_sup_atp_info_rec.organization_id,
p_sup_atp_info_rec.supplier_id,
p_sup_atp_info_rec.supplier_site_id,
-- ship_rec_cal
c.seq_num - p_sup_atp_info_rec.sysdate_seq_num),0))) col24,
C.CALENDAR_DATE col25,
l_null_num col26,
l_null_char col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
FROM msc_calendar_dates c,
msc_supplier_capacities s
WHERE s.inventory_item_id = l_inv_item_id
AND s.sr_instance_id = p_sup_atp_info_rec.instance_id
AND s.plan_id = p_sup_atp_info_rec.plan_id
AND s.organization_id = p_sup_atp_info_rec.organization_id
AND s.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(s.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND c.calendar_date BETWEEN trunc(s.from_date)
--AND NVL(s.to_date,l_cutoff_date)
AND trunc(NVL(s.to_date,least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date))) --4055719
AND (c.seq_num IS NOT NULL OR p_sup_atp_info_rec.manufacturing_cal_code = MSC_CALENDAR.FOC)
AND c.calendar_code = l_calendar_code
AND c.exception_set_id = l_calendar_exception_set_id
AND c.sr_instance_id = s.sr_instance_id -- Changed from l_instance_id ?
AND c.calendar_date >= p_sup_atp_info_rec.sup_cap_cum_date
-- Supplier Capacity (SCLT) Accumulation starts from this date.
-- AND c.calendar_date >= l_plan_start_date -- bug 1169467
UNION ALL
SELECT
p_sup_atp_info_rec.level col1,
MSC_ATP_PVT.G_ORDER_LINE_ID col2,
p_sup_atp_info_rec.scenario_id col3,
l_null_num col4 ,
l_null_num col5,
p_sup_atp_info_rec.organization_id col6,
l_null_num col7,
l_null_num col8,
p_sup_atp_info_rec.supplier_id col9,
p_sup_atp_info_rec.supplier_site_id col10,
l_null_num col11,
l_null_num col12,
l_null_num col13,
l_null_num col14,
l_null_char col15,
l_uom_code col16,
1 col17, -- demand
p.order_type col18,
l_null_char col19,
p_sup_atp_info_rec.instance_id col20,
l_null_num col21,
TRANSACTION_ID col22,
l_null_num col23,
-- performance dsting rearrange signs to get rid of multiply times - 1
(NVL(p.implement_quantity,0) - p.new_order_quantity) col24,
--Fixing as a part of bug3709707 adding trunc so that 2 column are not seen in HP
TRUNC(Decode(p_sup_atp_info_rec.sup_cap_type,
1, p.new_ship_date,
p.new_dock_date)) col25, -- For ship_rec_cal
l_null_num col26,
--bug 4493399: show transaction id for PO
--p.order_number col27,
DECODE(p.ORDER_TYPE, 5, to_char(p.TRANSACTION_ID), p.order_number) col27,
l_null_num col28,
l_null_num col29,
l_sysdate,
FND_GLOBAL.User_ID,
l_sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID
-- Supplier Capacity (SCLT) Changes Begin
FROM msc_supplies p
WHERE (p.order_type IN (5, 2, 60)
--include purchase orders based on profile option
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))
-- Supplier Capacity (SCLT) Accumulation Ignore Purchase Orders
-- WHERE p.order_type IN (5, 1, 2)
AND p.plan_id = p_sup_atp_info_rec.plan_id
AND p.sr_instance_id = p_sup_atp_info_rec.instance_id
AND p.inventory_item_id = p_sup_atp_info_rec.inventory_item_id
-- 1214694 AND p.organization_id = p_sup_atp_info_rec.organization_id
AND p.supplier_id = p_sup_atp_info_rec.supplier_id
AND NVL(p.supplier_site_id, -1) = NVL(p_sup_atp_info_rec.supplier_site_id, -1)
AND trunc(Decode(p_sup_atp_info_rec.sup_cap_type, 1, p.new_ship_date,p.new_dock_date)) --4055719 --4135752
<= trunc(least(p_sup_atp_info_rec.last_cap_date,l_cutoff_date)) --4135752
-- Exclude Cancelled Supplies 2460645
AND NVL(P.DISPOSITION_STATUS_TYPE, 1) <> 2);
msc_sch_wb.atp_debug('Get_Supplier_Atp_Info: ' || 'after inserting into msc_atp_sd_details_temp');
END IF; -- p_sup_atp_info_rec.insert_flag <> 0
IF (NVL(p_sup_atp_info_rec.insert_flag, 0) <> 0) THEN
-- add one more entry to indicate infinite time fence date
-- and quantity.
MSC_SATP_FUNC.Extend_Atp_Period(x_atp_period, x_return_status);
IF NVL(p_sup_atp_info_rec.insert_flag, 0) <> 0 THEN
-- add one more entry to indicate infinite time fence date
-- and quantity.
x_atp_period.Cumulative_Quantity := l_atp_info.atp_qty;
IF (NVL(p_sup_atp_info_rec.insert_flag, 0) <> 0) THEN
-- add one more entry to indicate infinite time fence date
-- and quantity.
MSC_SATP_FUNC.Extend_Atp_Period(x_atp_period, x_return_status);
p_insert_flag IN NUMBER,
p_level IN NUMBER,
p_scenario_id IN NUMBER,
p_identifier IN NUMBER,
p_parent_pegging_id IN NUMBER,
x_requested_date_quantity OUT NoCopy NUMBER,
x_atp_date_this_level OUT NoCopy DATE,
x_atp_date_quantity_this_level OUT NoCopy NUMBER,
x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
x_return_status OUT NoCopy VARCHAR2
)
IS
i PLS_INTEGER := 1;
msc_sch_wb.atp_debug('Get_Transport_Cap_Atp_Info: ' || 'p_insert_flag = '||p_insert_flag);
SELECT NVL(unit_weight,1), NVL(unit_volume,1)
INTO l_unit_weight, l_unit_volume
FROM msc_system_items
WHERE plan_id = p_plan_id
AND organization_id = p_to_organization_id
AND inventory_item_id = p_inventory_item_id
AND sr_instance_id = p_dest_org_instance_id;
SELECT l_date, SUM(weight), SUM(volume)
BULK COLLECT INTO
l_atp_period_tab,
l_atp_qty_tab,
l_atp_qty_tab2
FROM (
SELECT c.calendar_date l_date,
s.weight_capacity weight,
s.volume_capacity volume
FROM msc_calendar_dates c,
msc_interorg_ship_methods s,
msc_trading_partners tp,
msc_plans p
WHERE s.plan_id = p_plan_id
AND s.from_organization_id = p_from_organization_id
AND s.to_organization_id = p_to_organization_id
AND s.ship_method = p_ship_method
AND s.sr_instance_id = p_source_org_instance_id
AND s.sr_instance_id2 = p_dest_org_instance_id
AND s.from_organization_id = tp.sr_tp_id
AND tp.sr_instance_id = NVL(s.sr_instance_id, s.sr_instance_id2)
AND c.calendar_date BETWEEN trunc(SYSDATE) and trunc(p.curr_cutoff_date) -- to_date changed to trunc to avoid GSCC error
AND c.calendar_code = tp.calendar_code
AND c.exception_set_id = tp.calendar_exception_set_id
AND p.plan_id = p_plan_id
UNION ALL
SELECT sup.new_schedule_date l_date,
-1*(sup.new_order_quantity)*l_unit_weight weight,
-1*(sup.new_order_quantity)*l_unit_volume volume
FROM msc_supplies sup
WHERE sup.plan_id = p_plan_id
AND sup.organization_id = p_to_organization_id
AND sup.sr_instance_id = p_dest_org_instance_id
AND sup.source_organization_id is not null
AND sup.source_organization_id = p_from_organization_id
AND sup.source_sr_instance_id = p_source_org_instance_id
AND sup.ship_method = p_ship_method
AND sup.inventory_item_id = p_inventory_item_id
AND sup.transaction_id <> (SELECT identifier3
FROM mrp_atp_details_temp
WHERE record_type = 3
AND pegging_id = p_parent_pegging_id
AND session_id = MSC_ATP_PVT.G_SESSION_ID
))
GROUP BY l_date
ORDER BY l_date;
IF NVL(p_insert_flag,0) <> 0 THEN -- p_insert_flag
-- add pegging info for weight demand
l_pegging_rec.session_id := MSC_ATP_PVT.G_SESSION_ID;
SELECT
col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col18,
col19,
col20,
col21,
col22,
col23,
col24,
col25,
col26,
col27,
col28,
col29
BULK COLLECT INTO
l_atp_supply_demand.Level,
l_atp_supply_demand.Identifier,
l_atp_supply_demand.Inventory_Item_Id,
l_atp_supply_demand.Request_Item_Id,
l_atp_supply_demand.Organization_Id,
l_atp_supply_demand.Department_Id,
l_atp_supply_demand.Resource_Id,
l_atp_supply_demand.Supplier_Id,
l_atp_supply_demand.Supplier_Site_Id,
l_atp_supply_demand.From_Organization_Id,
l_atp_supply_demand.From_Location_Id,
l_atp_supply_demand.To_Organization_Id,
l_atp_supply_demand.To_Location_Id,
l_atp_supply_demand.Ship_Method,
l_atp_supply_demand.Uom,
l_atp_supply_demand.Supply_Demand_Type,
l_atp_supply_demand.Supply_Demand_Source_Type,
l_atp_supply_demand.Supply_Demand_Source_Type_Name,
l_atp_supply_demand.Identifier1,
l_atp_supply_demand.Identifier2,
l_atp_supply_demand.Identifier3,
l_atp_supply_demand.Identifier4,
l_atp_supply_demand.Supply_Demand_Quantity,
l_atp_supply_demand.Supply_Demand_Date,
l_atp_supply_demand.Disposition_Type,
l_atp_supply_demand.Disposition_Name,
l_atp_supply_demand.Scenario_Id,
l_atp_supply_demand.Pegging_Id,
l_atp_supply_demand.End_Pegging_Id
FROM
(
SELECT
p_level col1,
p_identifier col2,
p_inventory_item_id col3,
null col4, -- request_item_id
null col5, -- organization_id
null col6, -- department_id
null col7, -- resource_id
null col8, -- supplier_id
null col9, -- supplier_site_id
p_from_organization_id col10,
null col11, -- from_location_id
p_to_organization_id col12,
null col13, -- to_location_id
p_ship_method col14,
s.weight_uom col15, -- uom
2 col16, -- supply
null col17, -- supply_demand_source_type
null col18, -- supply_demand_source_type_name
p_source_org_instance_id col19, -- identifier1
1 col20, -- identifier2, weight(1) or volume(2)
null col21, -- identifier3
p_dest_org_instance_id col22, -- identifier4
s.weight_capacity col23,
c.calendar_date col24,
null col25, -- disposition_type
null col26, -- disposition_name
p_scenario_id col27, -- scenario_id
null col28, -- pegging_id
null col29 -- end_pegging_id
FROM
msc_calendar_dates c,
msc_interorg_ship_methods s,
msc_trading_partners tp,
msc_plans p
WHERE
s.plan_id = p_plan_id
AND s.from_organization_id = p_from_organization_id
AND s.to_organization_id = p_to_organization_id
AND s.ship_method = p_ship_method
AND s.sr_instance_id = p_source_org_instance_id
AND s.sr_instance_id2 = p_dest_org_instance_id
AND s.from_organization_id = tp.sr_tp_id
AND tp.sr_instance_id = NVL(s.sr_instance_id, s.sr_instance_id2)
AND c.calendar_date BETWEEN trunc(SYSDATE) and trunc(p.curr_cutoff_date) -- to_date changed to trunc to avoid GSCC error
AND c.calendar_code = tp.calendar_code
AND c.exception_set_id = tp.calendar_exception_set_id
AND p.plan_id = p_plan_id
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_inventory_item_id col3,
null col4, -- request_item_id
null col5, -- organization_id
null col6, -- department_id
null col7, -- resource_id
null col8, -- supplier_id
null col9, -- supplier_site_id
p_from_organization_id col10,
null col11, -- from_location_id
p_to_organization_id col12,
null col13, -- to_location_id
p_ship_method col14,
s.weight_uom col15, -- uom
1 col16, -- demand
null col17, -- supply_demand_source_type
null col18, -- supply_demand_source_type_name
p_source_org_instance_id col19, -- identifier1
1 col20, -- identifier2, weight(1) or volume(2)
null col21, -- identifier3
p_dest_org_instance_id col22, -- identifier4
-1*(sup.new_order_quantity)*l_unit_weight col23,
sup.new_schedule_date col24,
null col25, -- disposition_type
null col26, -- disposition_name
p_scenario_id col27, -- scenairo_id
null col28, -- pegging_id
null col29 -- end_pegging_id
FROM msc_supplies sup,
msc_interorg_ship_methods s
WHERE sup.plan_id = p_plan_id
AND sup.organization_id = p_to_organization_id
AND sup.sr_instance_id = p_dest_org_instance_id
AND sup.source_organization_id is not null
AND sup.source_organization_id = p_from_organization_id
AND sup.source_sr_instance_id = p_source_org_instance_id
AND sup.ship_method = p_ship_method
AND sup.inventory_item_id = p_inventory_item_id
AND s.plan_id = p_plan_id
AND s.from_organization_id = p_from_organization_id
AND s.to_organization_id = p_to_organization_id
AND s.ship_method = p_ship_method
AND s.sr_instance_id = p_source_org_instance_id
AND s.sr_instance_id2 = p_dest_org_instance_id
)
ORDER BY col25;
SELECT
col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col18,
col19,
col20,
col21,
col22,
col23,
col24,
col25,
col26,
col27,
col28,
col29
BULK COLLECT INTO
l_atp_supply_demand2.Level,
l_atp_supply_demand2.Identifier,
l_atp_supply_demand2.Inventory_Item_Id,
l_atp_supply_demand2.Request_Item_Id,
l_atp_supply_demand2.Organization_Id,
l_atp_supply_demand2.Department_Id,
l_atp_supply_demand2.Resource_Id,
l_atp_supply_demand2.Supplier_Id,
l_atp_supply_demand2.Supplier_Site_Id,
l_atp_supply_demand2.From_Organization_Id,
l_atp_supply_demand2.From_Location_Id,
l_atp_supply_demand2.To_Organization_Id,
l_atp_supply_demand2.To_Location_Id,
l_atp_supply_demand2.Ship_Method,
l_atp_supply_demand2.Uom,
l_atp_supply_demand2.Supply_Demand_Type,
l_atp_supply_demand2.Supply_Demand_Source_Type,
l_atp_supply_demand2.Supply_Demand_Source_Type_Name,
l_atp_supply_demand2.Identifier1,
l_atp_supply_demand2.Identifier2,
l_atp_supply_demand2.Identifier3,
l_atp_supply_demand2.Identifier4,
l_atp_supply_demand2.Supply_Demand_Quantity,
l_atp_supply_demand2.Supply_Demand_Date,
l_atp_supply_demand2.Disposition_Type,
l_atp_supply_demand2.Disposition_Name,
l_atp_supply_demand2.Scenario_Id,
l_atp_supply_demand2.Pegging_Id,
l_atp_supply_demand2.End_Pegging_Id
FROM
(
SELECT
p_level col1,
p_identifier col2,
p_inventory_item_id col3,
null col4, -- request_item_id
null col5, -- organization_id
null col6, -- department_id
null col7, -- resource_id
null col8, -- supplier_id
null col9, -- supplier_site_id
p_from_organization_id col10,
null col11, -- from_location_id
p_to_organization_id col12,
null col13, -- to_location_id
p_ship_method col14,
s.volume_uom col15, -- uom
2 col16, -- supply
null col17, -- supply_demand_source_type
null col18, -- supply_demand_source_type_name
p_source_org_instance_id col19, -- identifier1
2 col20, -- identifier2, weight(1) or volume(2)
null col21, -- identifier3
p_dest_org_instance_id col22, -- identifier4
s.volume_capacity col23,
c.calendar_date col24,
null col25, -- disposition_type
null col26, -- disposition_name
p_scenario_id col27, -- scenairo_id
null col28, -- pegging_id
null col29 -- end_pegging_id
FROM
msc_calendar_dates c,
msc_interorg_ship_methods s,
msc_trading_partners tp,
msc_plans p
WHERE
s.plan_id = p_plan_id
AND s.from_organization_id = p_from_organization_id
AND s.to_organization_id = p_to_organization_id
AND s.ship_method = p_ship_method
AND s.sr_instance_id = p_source_org_instance_id
AND s.sr_instance_id2 = p_dest_org_instance_id
AND s.from_organization_id = tp.sr_tp_id
AND tp.sr_instance_id = NVL(s.sr_instance_id, s.sr_instance_id2)
AND c.calendar_date BETWEEN trunc(SYSDATE) and trunc(p.curr_cutoff_date) -- to_date changed to trunc to avoid GSCC error
AND c.calendar_code = tp.calendar_code
AND c.exception_set_id = tp.calendar_exception_set_id
AND p.plan_id = p_plan_id
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_inventory_item_id col3,
null col4, -- request_item_id
null col5, -- organization_id
null col6, -- department_id
null col7, -- resource_id
null col8, -- supplier_id
null col9, -- supplier_site_id
p_from_organization_id col10,
null col11, -- from_location_id
p_to_organization_id col12,
null col13, -- to_location_id
p_ship_method col14,
s.volume_uom col15, -- uom
1 col16, -- demand
null col17, -- supply_demand_source_type
null col18, -- supply_demand_source_type_name
p_source_org_instance_id col19, -- identifier1
2 col20, -- identifier2, weight(1) or volume(2)
null col21, -- identifier3
p_dest_org_instance_id col22, -- identifier4
-1*(sup.new_order_quantity)*l_unit_volume col23,
sup.new_schedule_date col24,
null col25, -- disposition_type
null col26, -- disposition_name
p_scenario_id col27, -- scenairo_id
null col28, -- pegging_id
null col29 -- end_pegging_id
FROM msc_supplies sup,
msc_interorg_ship_methods s
WHERE sup.plan_id = p_plan_id
AND sup.organization_id = p_to_organization_id
AND sup.sr_instance_id = p_dest_org_instance_id
AND sup.source_organization_id is not null
AND sup.source_organization_id = p_from_organization_id
AND sup.source_sr_instance_id = p_source_org_instance_id
AND sup.ship_method = p_ship_method
AND sup.inventory_item_id = p_inventory_item_id
AND s.plan_id = p_plan_id
AND s.from_organization_id = p_from_organization_id
AND s.to_organization_id = p_to_organization_id
AND s.ship_method = p_ship_method
AND s.sr_instance_id = p_source_org_instance_id
AND s.sr_instance_id2 = p_dest_org_instance_id
)
ORDER BY col25;