[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_insert_flag IN NUMBER,
x_atp_info OUT NoCopy MRP_ATP_PVT.ATP_Info,
x_atp_period OUT NoCopy MRP_ATP_PUB.ATP_Period_Typ,
x_atp_supply_demand OUT NoCopy MRP_ATP_PUB.ATP_Supply_Demand_Typ,
p_get_mat_in_rec IN MSC_ATP_REQ.get_mat_in_rec,
p_refresh_number IN NUMBER, -- For summary enhancement
p_request_item_id IN NUMBER, -- For time_phased_atp
p_atf_date IN DATE) -- For time_phased_atp
IS
l_infinite_time_fence_date DATE;
msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_insert_flag =' || p_insert_flag );
SELECT inventory_item_id, uom_code
INTO l_inv_item_id, l_uom_code
FROM msc_system_items
WHERE plan_id = p_plan_id
AND sr_instance_id = p_instance_id
AND organization_id = p_organization_id
AND sr_inventory_item_id = p_inventory_item_id;
msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'Error selecting uom_code for the item');
IF (NVL(p_insert_flag, 0) = 0 ) THEN
-- we don't want details
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'p_insert_flag : 0');
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO
l_current_atp.atp_period,
l_current_atp.atp_qty
FROM
(
SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
SD_DATE, SD_QTY
FROM MSC_ATP_SUMMARY_SD S
WHERE S.PLAN_ID = p_plan_id
AND S.SR_INSTANCE_ID = p_instance_id
AND S.INVENTORY_ITEM_ID = l_inv_item_id
AND S.ORGANIZATION_ID = p_organization_id
AND S.DEMAND_CLASS = NVL(p_demand_class, S.DEMAND_CLASS)
AND S.SD_DATE < l_infinite_time_fence_date
UNION ALL
SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
0,NVL(AD.OLD_ALLOCATED_QUANTITY,0), -- For summary enhancement --5283809
-1 * AD.ALLOCATED_QUANTITY) SD_QTY
FROM MSC_ALLOC_DEMANDS AD,
MSC_PLANS P -- For summary enhancement
WHERE AD.PLAN_ID = p_plan_id
AND AD.SR_INSTANCE_ID = p_instance_id
AND AD.INVENTORY_ITEM_ID = l_inv_item_id
AND AD.ORGANIZATION_ID = p_organization_id
AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS)
AND TRUNC(AD.DEMAND_DATE) < l_infinite_time_fence_date
AND P.PLAN_ID = AD.PLAN_ID
AND (AD.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR AD.REFRESH_NUMBER = p_refresh_number)
-- since repetitive schedule demand is not supported in this case
-- join to msc_calendar_dates is not needed.
UNION ALL
SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
decode(SA.ALLOCATED_QUANTITY, -- Consider deleted stealing records as dummy demands
0, -1 * (NVL(OLD_ALLOCATED_QUANTITY,0)), -- For summary enhancement --5283809
SA.ALLOCATED_QUANTITY) SD_QTY
FROM MSC_ALLOC_SUPPLIES SA,
MSC_PLANS P -- For summary enhancement
WHERE SA.PLAN_ID = p_plan_id
AND SA.SR_INSTANCE_ID = p_instance_id
AND SA.INVENTORY_ITEM_ID = l_inv_item_id
AND SA.ORGANIZATION_ID = p_organization_id
AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS)
AND TRUNC(SA.SUPPLY_DATE) < l_infinite_time_fence_date
AND P.PLAN_ID = SA.PLAN_ID
AND (SA.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR SA.REFRESH_NUMBER = p_refresh_number)
)
GROUP BY SD_DATE
ORDER BY SD_DATE;--4698199
SELECT SD_DATE,
SUM(SD_QTY)
BULK COLLECT INTO
l_current_atp.atp_period,
l_current_atp.atp_qty
FROM (
SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
-1 * AD.ALLOCATED_QUANTITY SD_QTY
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.SR_INSTANCE_ID = p_instance_id
AND AD.INVENTORY_ITEM_ID = l_inv_item_id
AND AD.ORGANIZATION_ID = p_organization_id
AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS)
AND TRUNC(AD.DEMAND_DATE) < l_infinite_time_fence_date
-- since repetitive schedule demand is not supported in this case
-- join to msc_calendar_dates is not needed.
UNION ALL
SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
SA.ALLOCATED_QUANTITY SD_QTY
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.SR_INSTANCE_ID = p_instance_id
AND SA.INVENTORY_ITEM_ID = l_inv_item_id
AND SA.ORGANIZATION_ID = p_organization_id
AND SA.ALLOCATED_QUANTITY <> 0
AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS)
-- fixed as part of time_phased_atp chagnes
AND TRUNC(SA.SUPPLY_DATE) >= DECODE(SA.ORIGINAL_ORDER_TYPE,
27, TRUNC(SYSDATE),
28, TRUNC(SYSDATE),
TRUNC(SA.SUPPLY_DATE))
AND TRUNC(SA.SUPPLY_DATE) < l_infinite_time_fence_date
)
GROUP BY SD_DATE
ORDER BY SD_DATE;--4698199
ELSE -- IF (NVL(p_insert_flag, 0) = 0 ) THEN
-- IF (NVL(p_insert_flag, 0) <> 0 )
-- OR p_scenario_id = -1
-- get the details
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,
Allocated_Quantity, -- fixed as part of time_phased_atp
Supply_Demand_Quantity,
Supply_Demand_Date,
Disposition_Type,
Disposition_Name,
Pegging_Id,
End_Pegging_Id,
Pf_Display_Flag,
Original_Demand_Quantity,
Original_Demand_Date,
Original_Item_Id,
Original_Supply_Demand_Type,
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_inventory_item_id col4 ,
p_request_item_id col5,
p_organization_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,
l_uom_code col16,
1 col17, -- demand
--AD.ORIGINATION_TYPE col18,
DECODE(AD.ORIGINATION_TYPE, -100, 30,AD.ORIGINATION_TYPE) col18, --5027568
l_null_char col19,
AD.SR_INSTANCE_ID col20,
l_null_num col21,
AD.PARENT_DEMAND_ID col22,
l_null_num col23,
-1 * AD.ALLOCATED_QUANTITY col24,
-1* NVL(AD.Demand_Quantity, AD.ALLOCATED_QUANTITY), -- fixed as part of time_phased_atp
TRUNC(AD.DEMAND_DATE) col25,
l_null_num col26,
AD.ORDER_NUMBER col27,
l_null_num col28,
l_null_num col29,
AD.Pf_Display_Flag,
-1* NVL(AD.Demand_Quantity, AD.ALLOCATED_QUANTITY),
AD.Original_Demand_Date,
AD.Original_Item_Id,
AD.Original_Origination_Type
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_ID,
MTPS.LOCATION, --bug3263368
MTP.PARTNER_NAME, --bug3263368
AD.DEMAND_CLASS, --bug3263368
AD.REQUEST_DATE --bug3263368
FROM MSC_ALLOC_DEMANDS AD,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE AD.PLAN_ID = p_plan_id
AND AD.SR_INSTANCE_ID = p_instance_id
AND AD.INVENTORY_ITEM_ID = l_inv_item_id
AND AD.ORGANIZATION_ID = p_organization_id
AND AD.DEMAND_CLASS = NVL(p_demand_class, AD.DEMAND_CLASS )
AND AD.ORIGINATION_TYPE <> 52
AND TRUNC(AD.DEMAND_DATE) < l_infinite_time_fence_date
AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
-- since repetitive schedule demand is not supported in this case
-- join to msc_calendar_dates is not needed.
UNION ALL
SELECT p_level col1,
p_identifier col2,
p_scenario_id col3,
p_inventory_item_id col4 ,
p_request_item_id col5,
p_organization_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,
l_uom_code col16,
2 col17, -- supply
SA.ORDER_TYPE col18,
l_null_char col19,
SA.SR_INSTANCE_ID col20,
l_null_num col21,
SA.PARENT_TRANSACTION_ID col22,
l_null_num col23,
SA.ALLOCATED_QUANTITY col24,
NVL(SA.Supply_Quantity, SA.ALLOCATED_QUANTITY), -- fixed as part of time_phased_atp
TRUNC(SA.SUPPLY_DATE) col25,
l_null_num col26,
DECODE(SA.ORDER_TYPE, 5, to_char(SA.PARENT_TRANSACTION_ID), SA.ORDER_NUMBER) col27,
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
l_null_num col28,
l_null_num col29,
l_null_num,
l_null_num,
to_date(null),
SA.Original_Item_Id,
SA.Original_Order_Type
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_ID,
MTPS.LOCATION, --bug3684383
MTP.PARTNER_NAME, --bug3684383
SA.DEMAND_CLASS, --bug3684383
null --bug3684383
FROM MSC_ALLOC_SUPPLIES SA,
MSC_TRADING_PARTNERS MTP,--bug3684383
MSC_TRADING_PARTNER_SITES MTPS --bug3684383
WHERE SA.PLAN_ID = p_plan_id
AND SA.SR_INSTANCE_ID = p_instance_id
AND SA.INVENTORY_ITEM_ID = l_inv_item_id
AND SA.ORGANIZATION_ID = p_organization_id
AND SA.ALLOCATED_QUANTITY <> 0
AND SA.DEMAND_CLASS = NVL(p_demand_class, SA.DEMAND_CLASS )
-- fixed as part of time_phased_atp chagnes
AND TRUNC(SA.SUPPLY_DATE) >= DECODE(SA.ORIGINAL_ORDER_TYPE,
27, TRUNC(SYSDATE),
28, TRUNC(SYSDATE),
TRUNC(SA.SUPPLY_DATE))
AND TRUNC(SA.SUPPLY_DATE) < l_infinite_time_fence_date
AND SA.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3684383
AND SA.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3684383
);
msc_sch_wb.atp_debug('Item_Pre_Allocated_Atp: ' || 'after selecting sd data into msc_atp_sd_details_temp');
END IF; -- NVL(p_insert_flag, 0) = 0
IF NVL(p_insert_flag, 0) <> 0 THEN
-- add one more entry to indicate infinite time fence date and quantity.
x_atp_period.Cumulative_Quantity := x_atp_info.atp_qty;
SELECT NVL(rounding_control_type,2), inventory_item_id
INTO l_round_flag, l_inv_item_id
FROM msc_system_items I
WHERE I.sr_inventory_item_id = p_inventory_item_id
AND I.sr_instance_id = p_instance_id
AND I.plan_id = p_plan_id
AND I.organization_id = p_organization_id;
SELECT cal.next_date
INTO l_sys_next_date
FROM msc_calendar_dates cal
WHERE cal.exception_set_id = l_calendar_exception_set_id
AND cal.calendar_code = l_calendar_code
AND cal.calendar_date = TRUNC(sysdate)
AND cal.sr_instance_id = p_instance_id ;
SELECT mv.priority, mv.level_id, mv.class, mv.partner_id
INTO l_priority, l_level_id, l_class, l_partner_id
FROM msc_item_hierarchy_mv mv
WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
AND mv.organization_id = p_organization_id
AND mv.sr_instance_id = p_instance_id
--bug 3589115: if allocation rule is not valid on request date then pick one applicable on sysdate
AND GREATEST(p_requested_date, l_sys_next_date) BETWEEN effective_date AND disable_date
AND mv.demand_class = p_demand_class
AND mv.level_id = -1;
SELECT mv.priority, mv.level_id, mv.class, mv.partner_id
INTO l_priority, l_level_id, l_class, l_partner_id
FROM msc_item_hierarchy_mv mv
WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
AND mv.organization_id = p_organization_id
AND mv.sr_instance_id = p_instance_id
--bug 3589115: if allocation rule is not valid on request date then pick one applicable on sysdate
AND GREATEST(p_requested_date, l_sys_next_date) BETWEEN effective_date AND disable_date
AND mv.demand_class = p_demand_class
AND mv.level_id <> -1;
SELECT mv.demand_class, mv.priority
BULK COLLECT INTO l_demand_class_tab, l_demand_class_priority_tab
FROM msc_item_hierarchy_mv mv
WHERE mv.inventory_item_id = l_item_to_use /* New allocation logic for time_phased_atp changes*/
AND mv.organization_id = p_organization_id
AND mv.sr_instance_id = p_instance_id
--bug 3589115: if allocation rule is not valid on request date then pick one applicable on sysdate
AND GREATEST(p_requested_date, l_sys_next_date) BETWEEN effective_date AND disable_date
AND mv.priority > l_priority
AND mv.level_id = l_level_id
ORDER BY mv.priority desc , mv.allocation_percent asc, mv.demand_class desc;
INSERT INTO msc_alloc_temp(demand_class)
VALUES (l_demand_class_tab(i));
SELECT summary_flag
INTO l_summary_flag
FROM msc_plans plans
WHERE plans.plan_id = p_plan_id;
msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'right before the huge select statement');
SELECT SD_DATE,
SUM(SD_QTY),
DEMAND_CLASS
BULK COLLECT INTO
l_atp_period_tab,
l_atp_qty_tab,
l_atp_dc_tab
FROM
(
SELECT /*+ INDEX(S MSC_ATP_SUMMARY_SD_U1) */
SD_DATE, SD_QTY, DEMAND_CLASS
FROM MSC_ATP_SUMMARY_SD S
WHERE S.PLAN_ID = p_plan_id
AND S.SR_INSTANCE_ID = p_instance_id
AND S.INVENTORY_ITEM_ID = l_inv_item_id
AND S.ORGANIZATION_ID = p_organization_id
AND S.DEMAND_CLASS IN (
SELECT demand_class
FROM msc_alloc_temp
WHERE demand_class IS NOT NULL)
AND S.SD_DATE < l_infinite_time_fence_date
UNION ALL
SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
decode(AD.ALLOCATED_QUANTITY, -- Consider unscheduled orders as dummy supplies
0, nvl(OLD_ALLOCATED_QUANTITY,0), --4658238 -- For summary enhancement
-1 * AD.ALLOCATED_QUANTITY) SD_QTY,
AD.DEMAND_CLASS
FROM MSC_ALLOC_DEMANDS AD,
MSC_PLANS P -- For summary enhancement
WHERE AD.PLAN_ID = p_plan_id
AND AD.SR_INSTANCE_ID = p_instance_id
AND AD.INVENTORY_ITEM_ID = l_inv_item_id
AND AD.ORGANIZATION_ID = p_organization_id
AND AD.DEMAND_CLASS IN (
SELECT demand_class
FROM msc_alloc_temp
WHERE demand_class IS NOT NULL)
--bug3693892 added trunc
AND trunc(AD.DEMAND_DATE) < l_infinite_time_fence_date
AND P.PLAN_ID = AD.PLAN_ID
AND (AD.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR AD.REFRESH_NUMBER = p_refresh_number)
UNION ALL
SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
decode(SA.ALLOCATED_QUANTITY, -- Consider deleted stealing records as dummy demands
0, -1 * (NVL(OLD_ALLOCATED_QUANTITY,0)), -- For summary enhancement --5283809
SA.ALLOCATED_QUANTITY) SD_QTY ,
SA.DEMAND_CLASS
FROM MSC_ALLOC_SUPPLIES SA,
MSC_PLANS P -- For summary enhancement
WHERE SA.PLAN_ID = p_plan_id
AND SA.SR_INSTANCE_ID = p_instance_id
AND SA.INVENTORY_ITEM_ID = l_inv_item_id
AND SA.ORGANIZATION_ID = p_organization_id
AND SA.DEMAND_CLASS IN (
SELECT demand_class
FROM msc_alloc_temp
WHERE demand_class IS NOT NULL)
--bug3693892 added trunc
AND trunc(SA.SUPPLY_DATE) < l_infinite_time_fence_date
AND P.PLAN_ID = SA.PLAN_ID
AND (SA.REFRESH_NUMBER > P.LATEST_REFRESH_NUMBER
OR SA.REFRESH_NUMBER = p_refresh_number)
)
GROUP BY DEMAND_CLASS, SD_DATE
ORDER BY DEMAND_CLASS, SD_DATE;--4698199 --5353882
SELECT SD_DATE,
SUM(SD_QTY),
DEMAND_CLASS
BULK COLLECT INTO
l_atp_period_tab,
l_atp_qty_tab,
l_atp_dc_tab
FROM
(
SELECT TRUNC(AD.DEMAND_DATE) SD_DATE,
-1 * AD.ALLOCATED_QUANTITY SD_QTY,
AD.DEMAND_CLASS
FROM MSC_ALLOC_DEMANDS AD
WHERE AD.PLAN_ID = p_plan_id
AND AD.SR_INSTANCE_ID = p_instance_id
AND AD.INVENTORY_ITEM_ID = l_inv_item_id
AND AD.ORGANIZATION_ID = p_organization_id
AND AD.ORIGINATION_TYPE <> 52 -- Ignore copy SO and copy stealing records for summary enhancement
AND AD.DEMAND_CLASS IN (
SELECT demand_class
FROM msc_alloc_temp
WHERE demand_class IS NOT NULL)
--bug3693892 added trunc
AND trunc(AD.DEMAND_DATE) < l_infinite_time_fence_date
UNION ALL
SELECT TRUNC(SA.SUPPLY_DATE) SD_DATE,
SA.ALLOCATED_QUANTITY SD_QTY,
SA.DEMAND_CLASS
FROM MSC_ALLOC_SUPPLIES SA
WHERE SA.PLAN_ID = p_plan_id
AND SA.SR_INSTANCE_ID = p_instance_id
AND SA.INVENTORY_ITEM_ID = l_inv_item_id
AND SA.ORGANIZATION_ID = p_organization_id
AND SA.ALLOCATED_QUANTITY <> 0
-- fixed as part of time_phased_atp chagnes
AND TRUNC(SA.SUPPLY_DATE) >= DECODE(SA.ORIGINAL_ORDER_TYPE,
27, TRUNC(SYSDATE),
28, TRUNC(SYSDATE),
TRUNC(SA.SUPPLY_DATE))
AND SA.DEMAND_CLASS IN (
SELECT demand_class
FROM msc_alloc_temp
WHERE demand_class IS NOT NULL)
--bug3693892 added trunc
AND trunc(SA.SUPPLY_DATE) < l_infinite_time_fence_date
)
GROUP BY DEMAND_CLASS, SD_DATE
ORDER BY DEMAND_CLASS, SD_DATE;--4698199 --5353882
msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after huge select, l_atp_period_tab.COUNT : ' || l_atp_period_tab.COUNT);
INSERT INTO msc_alloc_temp(supply_demand_date)
VALUES (l_atp_period_tab(i));
SELECT supply_demand_date --sd_date
BULK COLLECT INTO
l_period_tab
FROM
(
SELECT DISTINCT supply_demand_date --sd_date
FROM msc_alloc_temp
WHERE supply_demand_date IS NOT NULL
--- for substitution we want to consider supplies only within substitution widow
--- filter out dates after substitution window
and supply_demand_date <= l_substitution_end_date
ORDER BY supply_demand_date --sd_date
);
DELETE msc_alloc_temp;
msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'before inserting Stealing Info' || l_used_dc_tab(i));
msc_sch_wb.atp_debug('Get_Forward_Material_Atp: ' || 'after insert into msc_alloc_supplies-Stealing Info' || l_transaction_id);