[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Allocation_Details(
p_session_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_instance_id IN NUMBER,
p_infinite_time_fence_date IN DATE,
p_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
p_plan_name IN VARCHAR2, -- bug 2771192
p_dest_inv_item_id IN NUMBER, -- For new allocation logic for time phased ATP
p_dest_family_item_id IN NUMBER, -- For new allocation logic for time phased ATP
x_return_status OUT NOCOPY VARCHAR2);
| o Calls Compute_Allocation_Details followed by Insert_Allocation_Details.
+-------------------------------------------------------------------------*/
PROCEDURE View_Allocation_Details(
p_session_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_instance_id IN NUMBER,
p_organization_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
-- local variables
l_request_date DATE;
SELECT MSC_CALENDAR.NEXT_WORK_DAY(p_organization_id, p_instance_id, 1, TRUNC(sysdate))
INTO l_request_date
FROM dual;
/* Call Insert_Allocation_Details to insert horizontal period information into temp table. */
Insert_Allocation_Details(p_session_id, p_inventory_item_id, p_organization_id,
p_instance_id, l_infinite_time_fence_date, l_atp_period, l_plan_info_rec.plan_name,
l_dest_inv_item_id, l_dest_family_item_id, -- For new allocation logic for time phased ATP
l_return_status);
msc_sch_wb.atp_debug('View_Allocation_Details: ' || 'Error occured in procedure Insert_Allocation_Details');
l_insert_count NUMBER;
SELECT msi.inventory_item_id, msi.uom_code,
msi.item_name, mtp.organization_code, msi.atp_flag
--, NVL(msi.rounding_control_type, 2)
INTO l_inv_item_id, l_uom_code,
l_inv_item_name,
l_org_code, l_atp_flag
--, G_ROUNDING_CONTROL_FLAG
FROM msc_system_items msi,
msc_trading_partners mtp
WHERE msi.plan_id = p_plan_id
AND msi.sr_instance_id = p_instance_id
AND msi.organization_id = p_organization_id
AND msi.sr_inventory_item_id = p_inventory_item_id
AND msi.organization_id = mtp.sr_tp_id
AND msi.sr_instance_id = mtp.sr_instance_id
AND mtp.partner_type=3;
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Error selecting inventory item id from msc_system_items: ' || to_char(sqlcode) || ':' || SQLERRM);
INSERT INTO MSC_ALLOC_TEMP(DEMAND_CLASS)
SELECT mv.demand_class
FROM msc_item_hierarchy_mv mv
WHERE mv.inventory_item_id = l_item_to_use
AND mv.organization_id = p_organization_id
AND mv.sr_instance_id = p_instance_id
AND p_request_date BETWEEN effective_date AND disable_date
AND mv.level_id = l_level_id;
MSC_ATP_PF.Insert_SD_Into_Details_Temp(
MSC_ATP_PF.Demand_Priority,
l_inv_item_id,
l_pf_dest_id,
p_inventory_item_id,
l_pf_sr_id,
p_organization_id,
--bug3671294 now we donot need this as we will join with msc_system_items
--l_inv_item_name,
l_org_code,
p_instance_id,
p_plan_id,
p_infinite_time_fence_date,
l_level_id,
p_session_id,
l_record_type,
l_scenario_id,
l_uom_code,
l_insert_count,
l_return_status
);
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Error occured in procedure Insert_SD_Into_Details_Temp');
INSERT INTO MRP_ATP_DETAILS_TEMP
(session_id, atp_level, inventory_item_id, organization_id, identifier1, identifier3,
supply_demand_type, supply_demand_date, supply_demand_quantity, supply_demand_source_type,
allocated_quantity, record_type, scenario_id, disposition_name, demand_class, char1,
uom_code, plan_id, inventory_item_name, organization_code,
ORIG_CUSTOMER_SITE_NAME,ORIG_CUSTOMER_NAME,ORIG_DEMAND_CLASS,ORIG_REQUEST_DATE ) --bug3263368
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
FROM
(SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
AD.PARENT_DEMAND_ID col6, -- Identifier3
1 col7, -- supply_demand_type
TRUNC(AD.DEMAND_DATE) col8, -- supply_demand_date
-1 * NVL(AD.DEMAND_QUANTITY,
AD.ALLOCATED_QUANTITY) col9, -- supply_demand_quantity
decode(AD.ORIGINATION_TYPE,-100,30,AD.ORIGINATION_TYPE) col10, -- supply_demand_source_type
-1 * AD.ALLOCATED_QUANTITY col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
AD.ORDER_NUMBER col14, -- disposition_name
AD.DEMAND_CLASS col15, -- demand_class
l_null_char col16, -- from_demand_class --Bug 3875786
l_uom_code col17, -- UOM Code
p_plan_id col18, -- Plan id
l_item_name_to_use col19, -- Item name --Bug 3823042
--l_inv_item_name col19, -- Item name
l_org_code col20, -- Organization code
MTPS.LOCATION col21, --bug3263368
MTP.PARTNER_NAME col22, --bug3263368
AD.DEMAND_CLASS col23, --bug3263368
AD.REQUEST_DATE col24 --bug3263368
FROM
MSC_ALLOC_DEMANDS AD,
MSC_ALLOC_TEMP TEMP,
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_item_to_use -- Bug 3823042
AND AD.ORGANIZATION_ID = p_organization_id
AND AD.ALLOCATED_QUANTITY <> 0
AND AD.DEMAND_CLASS = TEMP.DEMAND_CLASS
AND TRUNC(AD.DEMAND_DATE) < TRUNC(p_infinite_time_fence_date) -- Bug 3823042
AND AD.ORIGINATION_TYPE <> 52 -- Ignore copy SO and copy stealing records for allocation WB - summary enhancement
AND AD.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND AD.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
UNION ALL
SELECT p_session_id col1,
l_level_id col2,
p_inventory_item_id col3 ,
p_organization_id col4,
p_instance_id col5,
SA.PARENT_TRANSACTION_ID col6,
2 col7, -- supply
TRUNC(SA.SUPPLY_DATE) col8,
NVL(SA.SUPPLY_QUANTITY,
SA.ALLOCATED_QUANTITY) col9,
DECODE(SA.ORDER_TYPE,
46, 48, -- Change Supply due to Stealing to Supply Adjustment
47, 48, -- Change Demand due to Stealing to Supply Adjustment
SA.ORDER_TYPE) col10,
SA.ALLOCATED_QUANTITY col11,
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
DECODE(SA.ORDER_TYPE,
5, to_char(SA.PARENT_TRANSACTION_ID),
SA.ORDER_NUMBER) col14,
SA.DEMAND_CLASS col15,
SA.FROM_DEMAND_CLASS col16,
l_uom_code col17,
p_plan_id col18,
l_item_name_to_use col19, -- Item name --Bug 3823042
--l_inv_item_name col19, -- Item name
l_org_code col20, -- Organization code
MTPS.LOCATION col21, --bug3684383
MTP.PARTNER_NAME col22, --bug3684383
SA.DEMAND_CLASS col23, --bug3684383
l_null_date col24 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - null removed
FROM
MSC_ALLOC_SUPPLIES SA,
MSC_ALLOC_TEMP TEMP,
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_item_to_use -- Bug 3823042
AND SA.ORGANIZATION_ID = p_organization_id
AND SA.ALLOCATED_QUANTITY <> 0
AND SA.DEMAND_CLASS = TEMP.DEMAND_CLASS
AND TRUNC(SA.SUPPLY_DATE) < TRUNC(p_infinite_time_fence_date) -- Bug 3823042
AND SA.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3684383
AND SA.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3684383
);
l_insert_count := SQL%ROWCOUNT;
IF (l_insert_count = 0) THEN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'No s/d records could be inserted from msc_alloc tables into temp table');
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Before selecting supply/demand records from temp table into PL/SQL period table.');
SELECT
final.col1,
final.col2,
final.col3,
final.col4,
final.col5,
final.col6,
final.col7,
null,
p_inventory_item_id,
p_organization_id,
p_instance_id,
l_scenario_id,
l_level_id,
null, -- Initialize period end date with null
0, -- Initialize backward_forward_quantity with 0
0 -- Initialize cumulative quantity with 0
BULK COLLECT INTO
x_atp_period.Demand_Class,
x_atp_period.Period_Start_Date,
x_atp_period.Allocated_Supply_Quantity,
x_atp_period.Supply_Adjustment_Quantity,
x_atp_period.Total_Supply_Quantity,
x_atp_period.Total_Demand_Quantity,
x_atp_period.Period_Quantity,
x_atp_period.Total_Bucketed_Demand_Quantity,
x_atp_period.Inventory_Item_Id,
x_atp_period.Organization_Id,
x_atp_period.Identifier1,
x_atp_period.Scenario_Id,
x_atp_period.Level,
x_atp_period.Period_End_Date,
x_atp_period.Backward_Forward_Quantity,
x_atp_period.Cumulative_Quantity
FROM
(SELECT DEMAND_CLASS col1,
SUPPLY_DEMAND_DATE col2,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2,
DECODE(SUPPLY_DEMAND_SOURCE_TYPE,
48, 0,
ALLOCATED_QUANTITY),
0)) col3, -- Allocated Supply Quantity
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2,
DECODE(SUPPLY_DEMAND_SOURCE_TYPE,
48, ALLOCATED_QUANTITY,
0),
0)) col4, -- Supply Adjustment Quantity
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2, ALLOCATED_QUANTITY, 0)) col5, -- Total Supply
SUM(DECODE(SUPPLY_DEMAND_TYPE, 1, ALLOCATED_QUANTITY, 0)) col6, -- Total Demand
SUM(ALLOCATED_QUANTITY) col7 -- Period Quantity
FROM
MRP_ATP_DETAILS_TEMP
WHERE
SESSION_ID = p_session_id
AND RECORD_TYPE = l_record_type
GROUP BY
DEMAND_CLASS, SUPPLY_DEMAND_DATE
ORDER BY
DEMAND_CLASS, SUPPLY_DEMAND_DATE --5233538 10G issue
) final;
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'After selecting supply/demand records from temp table into PL/SQL period table.');
SELECT demand_class
BULK COLLECT INTO l_all_dc_list_tab
FROM MSC_ALLOC_TEMP;
msc_sch_wb.atp_debug('Insert_SD_Into_Details_Temp: ' || 'Error occured in procedure Populate_Original_Demand_Qty');
INSERT INTO MSC_ALLOC_TEMP(DEMAND_CLASS, PRIORITY, ALLOCATION_PERCENT)
SELECT mv.demand_class, mv.priority, mv.allocation_percent
FROM msc_item_hierarchy_mv mv
WHERE mv.inventory_item_id = l_item_to_use
AND mv.organization_id = p_organization_id
AND mv.sr_instance_id = p_instance_id
AND p_request_date BETWEEN effective_date AND disable_date
AND mv.level_id = l_level_id;
therefore, we do not select from_demand_class.
3. MSC_ALLOC_TEMP here stores allocation demand classes. We make a cartesian to split the supplies/demands
4. Finally we put only those records where allocated_quantity <> 0
*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Before copying supply/demand records from msc_supplies/msc_demands into temp tables.');
MSC_ATP_PF.Insert_SD_Into_Details_Temp(
MSC_ATP_PF.User_Defined_DC,
l_inv_item_id,
l_pf_dest_id,
p_inventory_item_id,
l_pf_sr_id,
p_organization_id,
--bug3671294 now we donot need this as we will join with msc_system_items
--l_inv_item_name,
l_org_code,
p_instance_id,
p_plan_id,
p_infinite_time_fence_date,
l_level_id,
p_session_id,
l_record_type,
l_scenario_id,
l_uom_code,
l_insert_count,
l_return_status
);
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Error occured in procedure Insert_SD_Into_Details_Temp');
INSERT INTO MRP_ATP_DETAILS_TEMP
(session_id, atp_level, inventory_item_id, organization_id, identifier1, identifier3,
supply_demand_type, supply_demand_date, supply_demand_quantity, supply_demand_source_type,
allocated_quantity, record_type, scenario_id, disposition_name, demand_class, uom_code,
inventory_item_name, organization_code, identifier2, identifier4,
ORIG_CUSTOMER_SITE_NAME,ORIG_CUSTOMER_NAME,ORIG_DEMAND_CLASS,ORIG_REQUEST_DATE ) --bug3263368
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
FROM
(SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
D.DEMAND_ID col6, -- Identifier3
1 col7, -- supply_demand_type
-- Bug 3823042
GREATEST(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)))), l_sys_next_date) col8,
--C.PRIOR_DATE col8, -- supply_demand_date
-1 * (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) col9, -- supply_demand_quantity -- Bug 3823042
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE) col10, -- supply_demand_source_type
-1* (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) * -- Bug 3823042
DECODE(decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), NULL),
30, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), NULL),
DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS))),
TEMP.DEMAND_CLASS, 1,
MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
D.DEMAND_ID,
TRUNC(DECODE(D.RECORD_SOURCE,
2, NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(D.IMPLEMENT_SHIP_DATE,NVL(D.FIRM_DATE,NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE))),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)))),
--c.prior_date,
D.USING_ASSEMBLY_ITEM_ID,
DECODE(D.SOURCE_ORGANIZATION_ID,
-23453, null,
D.SOURCE_ORGANIZATION_ID),
l_item_to_use,
p_organization_id,
p_instance_id,
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
30, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, /*c.prior_date,*/ 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)))), l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS))),
TEMP.DEMAND_CLASS,
l_level_id)) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id in disposition_name column
DECODE(D.ORIGINATION_TYPE,
1, to_char(D.DISPOSITION_ID),
D.ORDER_NUMBER) col14, -- disposition_name
TEMP.DEMAND_CLASS col15, -- demand_class
l_uom_code col16, -- UOM Code
l_item_name_to_use col17, -- Item name --Bug 3823042
--l_inv_item_name col17, -- Item name
l_org_code col18, -- Org code
TEMP.PRIORITY col19, -- sysdate priroty
TEMP.ALLOCATION_PERCENT col20, -- sysdate allocation percent
MTPS.LOCATION col21, --bug3263368
MTP.PARTNER_NAME col22, --bug3263368
D.DEMAND_CLASS col23, --bug3263368
DECODE(D.ORDER_DATE_TYPE_CODE,2,
D.REQUEST_DATE,D.REQUEST_SHIP_DATE) col24 --bug3263368
FROM
MSC_DEMANDS D,
--Bug 3823042, donot use msc_calendar_dates
--MSC_CALENDAR_DATES C,
MSC_ALLOC_TEMP TEMP,
MSC_TRADING_PARTNERS MTP, --bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE
D.PLAN_ID = p_plan_id
AND D.SR_INSTANCE_ID = p_instance_id
AND D.INVENTORY_ITEM_ID = l_item_to_use -- Bug 3823042
AND D.ORGANIZATION_ID = p_organization_id
--AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31)
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,52) -- Ignore copy SO
AND D.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
--Bug 3823042
/*
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND C.CALENDAR_DATE
BETWEEN
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
TRUNC(DECODE(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(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 (( D.ORIGINATION_TYPE = 4
AND C.SEQ_NUM IS NOT NULL) OR
( D.ORIGINATION_TYPE <> 4))
AND C.PRIOR_DATE < NVL(p_infinite_time_fence_date, C.PRIOR_DATE + 1)
*/
-- Bug 3823042, donot use msc_calendar_dates
AND TRUNC(DECODE(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(p_infinite_time_fence_date) -- Bug 3823042, pitf is not NULL in PDS case
-- bug 2763784 (ssurendr)
-- Should not select supply/demand where the original quantity itself is 0
AND (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) <> 0 -- Bug 3823042 , donot care about repetitive demands
UNION ALL
SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
S.TRANSACTION_ID col6, -- Identifier3
2 col7, -- supply_demand_type
-- Bug 3823042, donot use calendar_dates
--C.NEXT_DATE col8, -- supply_demand_date
GREATEST(TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)),l_sys_next_date) col8,
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY) col9, -- supply_demand_source_quantity
S.ORDER_TYPE col10, -- supply_demand_source_type
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY)
* DECODE(DECODE(S.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS,'-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null,
null,
l_item_to_use,
p_organization_id,
p_instance_id,
--C.NEXT_DATE,
-- Bug 3823042
TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)),
l_level_id,
S.DEMAND_CLASS), S.DEMAND_CLASS)),
TEMP.DEMAND_CLASS,
1,
NULL,
NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.DEMAND_CLASS,
-- Bug 3823042
--c.next_date,
TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE))),
1),
DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.DEMAND_CLASS,
-- Bug 3823042
--c.next_date,
TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE))),
NULL, 1, 0)
) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
DECODE(S.ORDER_TYPE,
5, to_char(S.TRANSACTION_ID),
S.ORDER_NUMBER) col14, -- disposition_name
TEMP.DEMAND_CLASS col15, -- demand_class
l_uom_code col16, -- UOM Code
l_item_name_to_use col17, -- Item Name --Bug 3823042
--l_inv_item_name col17, -- Item name
l_org_code col18, -- Org code
TEMP.PRIORITY col19, -- sysdate priroty
TEMP.ALLOCATION_PERCENT col20, -- sysdate allocation percent
l_null_char col21, --bug3263368 ORIG_CUSTOMER_SITE_NAME --Bug 3875786
l_null_char col22, --bug3263368 ORIG_CUSTOMER_NAME --Bug 3875786
l_null_char col23, --bug3263368 ORIG_DEMAND_CLASS --Bug 3875786
l_null_date col24 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - null removed
FROM
-- Bug 3823042
--MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ALLOC_TEMP TEMP
WHERE
S.PLAN_ID = p_plan_id
AND S.SR_INSTANCE_ID = p_instance_id
AND S.INVENTORY_ITEM_ID = l_item_to_use -- Bug 3823042
AND S.ORGANIZATION_ID = p_organization_id
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
/*
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
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 >= DECODE(S.ORDER_TYPE, 27, TRUNC(SYSDATE), 28, TRUNC(SYSDATE), C.NEXT_DATE)
AND C.NEXT_DATE < NVL(p_infinite_time_fence_date, C.NEXT_DATE + 1)
*/
AND TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)) >= -- Bug 3823042, Using TRUNC's wherever required
TRUNC(DECODE(S.ORDER_TYPE, 27,SYSDATE,
28, SYSDATE,
NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)))
AND TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)) < TRUNC(p_infinite_time_fence_date) -- Bug 3823042
);
l_insert_count := SQL%ROWCOUNT;
INSERT INTO MRP_ATP_DETAILS_TEMP
(session_id, atp_level, inventory_item_id, organization_id, identifier1, identifier3,
supply_demand_type, supply_demand_date, supply_demand_quantity, supply_demand_source_type,
allocated_quantity, record_type, scenario_id, disposition_name, demand_class, uom_code,
inventory_item_name, organization_code, identifier2, identifier4,
ORIG_CUSTOMER_SITE_NAME,ORIG_CUSTOMER_NAME,ORIG_DEMAND_CLASS,ORIG_REQUEST_DATE ) --bug3263368
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
FROM
(SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
D.DEMAND_ID col6, -- Identifier3
1 col7, -- supply_demand_type
GREATEST(C.CALENDAR_DATE,l_sys_next_date) col8, -- supply_demand_date
--C.PRIOR_DATE col8, -- supply_demand_date
-1 * DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) col9, -- supply_demand_quantity
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE) col10, -- supply_demand_source_type
-1* DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)))*
DECODE(decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), NULL),
30, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), NULL),
DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS))),
TEMP.DEMAND_CLASS, 1,
MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
D.DEMAND_ID,
C.CALENDAR_DATE,
D.USING_ASSEMBLY_ITEM_ID,
DECODE(D.SOURCE_ORGANIZATION_ID,
-23453, null,
D.SOURCE_ORGANIZATION_ID),
l_item_to_use,
p_organization_id,
p_instance_id,
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
30, decode(d.source_organization_id,
NULL, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
-23453, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)),
d.organization_id, DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS)), TEMP.DEMAND_CLASS),
DECODE(D.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS, '-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null, null, l_item_to_use, p_organization_id,
p_instance_id, C.CALENDAR_DATE, l_level_id, D.DEMAND_CLASS),
D.DEMAND_CLASS))),
TEMP.DEMAND_CLASS,
l_level_id)) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id in disposition_name column
DECODE(D.ORIGINATION_TYPE,
1, to_char(D.DISPOSITION_ID),
D.ORDER_NUMBER) col14, -- disposition_name
TEMP.DEMAND_CLASS col15, -- demand_class
l_uom_code col16, -- UOM Code
l_item_name_to_use col17, -- Item Name --Bug 3823042
--l_inv_item_name col17, -- Item name
l_org_code col18, -- Org code
TEMP.PRIORITY col19, -- sysdate priroty
TEMP.ALLOCATION_PERCENT col20, -- sysdate allocation percent
MTPS.LOCATION col21, --bug3263368
MTP.PARTNER_NAME col22, --bug3263368
D.DEMAND_CLASS col23, --bug3263368
DECODE(D.ORDER_DATE_TYPE_CODE,2,
D.REQUEST_DATE,D.REQUEST_SHIP_DATE) col24 --bug3263368
FROM
MSC_DEMANDS D,
MSC_CALENDAR_DATES C,
MSC_ALLOC_TEMP TEMP,
MSC_TRADING_PARTNERS MTP, --bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE
D.PLAN_ID = p_plan_id
AND D.SR_INSTANCE_ID = p_instance_id
AND D.INVENTORY_ITEM_ID = l_item_to_use -- Bug 3823042
AND D.ORGANIZATION_ID = p_organization_id
--AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31)
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,52) -- Ignore copy SO
AND D.SHIP_TO_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND C.CALENDAR_DATE
BETWEEN
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
TRUNC(DECODE(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(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 (( D.ORIGINATION_TYPE = 4
AND C.SEQ_NUM IS NOT NULL) OR
( D.ORIGINATION_TYPE <> 4))
AND C.CALENDAR_DATE < TRUNC(p_infinite_time_fence_date) -- Bug 3823042
-- bug 2763784 (ssurendr)
-- Should not select supply/demand where the original quantity itself is 0
AND DECODE(D.ORIGINATION_TYPE, 4, D.DAILY_DEMAND_RATE,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) <> 0
UNION ALL
SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
S.TRANSACTION_ID col6, -- Identifier3
2 col7, -- supply_demand_type
GREATEST(C.CALENDAR_DATE,l_sys_next_date) col8, -- supply_demand_date
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY) col9, -- supply_demand_source_quantity
S.ORDER_TYPE col10, -- supply_demand_source_type
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY)
* DECODE(DECODE(S.DEMAND_CLASS, null, null,
DECODE(TEMP.DEMAND_CLASS,'-1',
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
null,
null,
l_item_to_use,
p_organization_id,
p_instance_id,
C.CALENDAR_DATE,
l_level_id,
S.DEMAND_CLASS), S.DEMAND_CLASS)),
TEMP.DEMAND_CLASS,
1,
NULL,
NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.DEMAND_CLASS,
c.calendar_date), 1),
DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.DEMAND_CLASS,
C.CALENDAR_DATE),
NULL, 1, 0)
) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
DECODE(S.ORDER_TYPE,
5, to_char(S.TRANSACTION_ID),
S.ORDER_NUMBER) col14, -- disposition_name
TEMP.DEMAND_CLASS col15, -- demand_class
l_uom_code col16, -- UOM Code
l_item_name_to_use col17, -- Item name --Bug 3823042
--l_inv_item_name col17, -- Item name
l_org_code col18, -- Org code
TEMP.PRIORITY col19, -- sysdate priroty
TEMP.ALLOCATION_PERCENT col20, -- sysdate allocation percent
l_null_char col21, --bug3263368 ORIG_CUSTOMER_SITE_NAME --Bug 3875786
l_null_char col22, --bug3263368 ORIG_CUSTOMER_NAME --Bug 3875786
l_null_char col23, --bug3263368 ORIG_DEMAND_CLASS --Bug 3875786
l_null_date col24 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - null removed
FROM
MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ALLOC_TEMP TEMP
WHERE
S.PLAN_ID = p_plan_id
AND S.SR_INSTANCE_ID = p_instance_id
AND S.INVENTORY_ITEM_ID = l_item_to_use
AND S.ORGANIZATION_ID = p_organization_id
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
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
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
-- Bug 3823042, Using TRUNC's wherever required
AND C.CALENDAR_DATE >= TRUNC(DECODE(S.ORDER_TYPE, 27, SYSDATE, 28, SYSDATE, C.CALENDAR_DATE))
AND C.CALENDAR_DATE < TRUNC(p_infinite_time_fence_date) -- Bug 3823042
);
l_insert_count := SQL%ROWCOUNT;
IF (l_insert_count = 0) THEN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'No s/d records could be inserted from msc_supplies/demands tables into temp table');
SELECT demand_class
BULK COLLECT INTO l_dc_list_tab
FROM MSC_ALLOC_TEMP;
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Before selecting supply/demand records from temp table into PL/SQL period table.');
SELECT
final.col1,
final.col2,
final.col3,
final.col4,
final.col5,
null,
p_inventory_item_id,
p_organization_id,
p_instance_id,
l_scenario_id,
l_level_id,
null,
0,
0,
final.col6,
final.col7,
final.col8,
null, -- bug 3282426
final.col9,
final.col10
BULK COLLECT INTO
x_atp_period.Demand_Class,
x_atp_period.Period_Start_Date,
x_atp_period.Total_Supply_Quantity,
x_atp_period.Total_Demand_Quantity,
--x_atp_period.Total_Bucketed_Demand_Quantity, --time_phased_atp /*Bug 3263304*/
x_atp_period.Period_Quantity,
x_atp_period.Total_Bucketed_Demand_Quantity, --time_phased_atp /*Bug 3263304*/
x_atp_period.Inventory_Item_Id,
x_atp_period.Organization_Id,
x_atp_period.Identifier1,
x_atp_period.Scenario_Id,
x_atp_period.Level,
x_atp_period.Period_End_Date,
x_atp_period.Cumulative_Quantity,
x_atp_period.Demand_Adjustment_Quantity,
x_atp_period.Identifier2,
x_atp_period.Unallocated_Supply_Quantity,
x_atp_period.Unallocated_Demand_Quantity,
x_atp_period.Unalloc_Bucketed_Demand_Qty, -- bug 3282426
x_atp_period.Unallocated_Net_Quantity,
x_atp_period.Identifier4
FROM
(SELECT DEMAND_CLASS col1,
SUPPLY_DEMAND_DATE col2,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2, ALLOCATED_QUANTITY, 0)) col3,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 1, ALLOCATED_QUANTITY, 0)) col4,
SUM(ALLOCATED_QUANTITY) col5,
IDENTIFIER2 col6,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2, SUPPLY_DEMAND_QUANTITY, 0)) col7,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 1, SUPPLY_DEMAND_QUANTITY, 0)) col8,
SUM(SUPPLY_DEMAND_QUANTITY) col9,
IDENTIFIER4 col10
FROM MRP_ATP_DETAILS_TEMP
WHERE SESSION_ID = p_session_id
AND RECORD_TYPE = l_record_type
GROUP BY DEMAND_CLASS, SUPPLY_DEMAND_DATE,
IDENTIFIER2, IDENTIFIER4
ORDER BY IDENTIFIER2 ASC, -- Priority
IDENTIFIER4 DESC, -- Allocation percent
DEMAND_CLASS ASC, SUPPLY_DEMAND_DATE) final;
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'After selecting supply/demand records from temp table into PL/SQL period table.');
INSERT INTO MSC_ALLOC_HIERARCHY_TEMP( LEVEL_3_DEMAND_CLASS,
LEVEL_2_DEMAND_CLASS, LEVEL_1_DEMAND_CLASS, PARTNER_ID, PARTNER_SITE_ID,
LEVEL_3_DEMAND_CLASS_PRIORITY, ALLOCATION_PERCENT, CUSTOMER_NAME, CUSTOMER_SITE_NAME)
SELECT A.demand_class, B.demand_class, A.class, A.partner_id,
A.partner_site_id, A.priority, A.allocation_percent, mtp.partner_name, mtps.location
FROM msc_item_hierarchy_mv A, msc_item_hierarchy_mv B,
msc_trading_partners mtp, msc_trading_partner_sites mtps
WHERE A.inventory_item_id = l_item_to_use
AND A.organization_id = p_organization_id
AND A.sr_instance_id = p_instance_id
AND p_request_date BETWEEN A.effective_date AND A.disable_date
AND A.level_id = 3
AND B.inventory_item_id = A.inventory_item_id
AND B.organization_id = A.organization_id
AND B.sr_instance_id = A.sr_instance_id
AND p_request_date BETWEEN B.effective_date AND B.disable_date
AND B.level_id = 2
AND B.class = A.class
AND B.partner_id = A.partner_id
AND A.partner_id = mtp.partner_id (+)
AND A.partner_site_id = mtps.partner_site_id (+);
therefore, we do not select from_demand_class.
But we select partner_id and partner_site_id, class and demand_class.
3. Finally we put only those records where allocated_quantity <> 0
*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Before copying supply/demand records from msc_supplies/msc_demands into temp tables.');
MSC_ATP_PF.Insert_SD_Into_Details_Temp(
MSC_ATP_PF.User_Defined_CC,
l_inv_item_id,
l_pf_dest_id,
p_inventory_item_id,
l_pf_sr_id,
p_organization_id,
--bug3671294 now we donot need this as we will join with msc_system_items
--l_inv_item_name,
l_org_code,
p_instance_id,
p_plan_id,
p_infinite_time_fence_date,
l_level_id,
p_session_id,
l_record_type,
l_scenario_id,
l_uom_code,
l_insert_count,
l_return_status
);
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Error occured in procedure Insert_SD_Into_Details_Temp');
INSERT INTO MRP_ATP_DETAILS_TEMP
(session_id, atp_level, inventory_item_id, organization_id, identifier1, identifier3,
supply_demand_type, supply_demand_date, supply_demand_quantity, supply_demand_source_type,
allocated_quantity, record_type, scenario_id, disposition_name, demand_class, class, customer_id,
customer_site_id, uom_code, inventory_item_name, organization_code, identifier2, identifier4,
Customer_Name, Customer_Site_Name,
ORIG_CUSTOMER_SITE_NAME,ORIG_CUSTOMER_NAME,ORIG_DEMAND_CLASS,ORIG_REQUEST_DATE ) --bug3263368
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
FROM
(SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
D.DEMAND_ID col6, -- Identifier3
1 col7, -- supply_demand_type
-- Bug 3823042
--C.PRIOR_DATE col8, -- supply_demand_date
GREATEST(
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)))), l_sys_next_date) col8,
-1 * (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) col9, -- supply_demand_quantity -- Bug 3823042
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE) col10, -- supply_demand_source_type -- Bug 3823042
-1* (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) *
DECODE(DECODE(D.CUSTOMER_ID, NULL, NULL,
0, NULL,
decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
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)))),
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
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)))),
l_level_id, NULL),
NULL),
30, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
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)))),
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
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)))),
l_level_id, NULL),
NULL),
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(D.CUSTOMER_ID, D.SHIP_TO_SITE_ID,
l_item_to_use, p_organization_id, p_instance_id, /*c.prior_date*/
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)))),
l_level_id, NULL))),
TEMP.LEVEL_3_DEMAND_CLASS, 1,
MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
D.DEMAND_ID,
--c.prior_date,
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)))),
D.USING_ASSEMBLY_ITEM_ID,
DECODE(D.SOURCE_ORGANIZATION_ID,
-23453, null,
D.SOURCE_ORGANIZATION_ID),
l_item_to_use,
p_organization_id,
p_instance_id,
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
DECODE(D.CUSTOMER_ID, NULL, TEMP.LEVEL_3_DEMAND_CLASS,
0, TEMP.LEVEL_3_DEMAND_CLASS,
decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
TEMP.LEVEL_3_DEMAND_CLASS),
30, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
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)))),
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
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)))),
l_level_id, NULL),
TEMP.LEVEL_3_DEMAND_CLASS),
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(D.CUSTOMER_ID, D.SHIP_TO_SITE_ID,
l_item_to_use, p_organization_id, p_instance_id, /*c.prior_date,*/
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)))),
l_level_id, NULL))),
TEMP.LEVEL_3_DEMAND_CLASS,
l_level_id)) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id in disposition_name column
DECODE(D.ORIGINATION_TYPE,
1, to_char(D.DISPOSITION_ID),
D.ORDER_NUMBER) col14, -- disposition_name
TEMP.LEVEL_3_DEMAND_CLASS col15, -- demand_class
TEMP.LEVEL_1_DEMAND_CLASS col16, -- class
TEMP.PARTNER_ID col17, -- partner_id
TEMP.PARTNER_SITE_ID col18, -- partner_site_id
l_uom_code col19, -- UOM Code
l_item_name_to_use col20, -- Item name --Bug 3823042
--l_inv_item_name col20, -- Item name
l_org_code col21, -- Org code
TEMP.LEVEL_3_DEMAND_CLASS_PRIORITY col22, -- Level 3 priority
TEMP.ALLOCATION_PERCENT col23, -- Sysdate allocation percent
TEMP.customer_name col24, -- Customer Name
TEMP.customer_site_name col25, -- Customer Site Name
MTPS.LOCATION col26, --bug3263368
MTP.PARTNER_NAME col27, --bug3263368
D.DEMAND_CLASS col28, --bug3263368
DECODE(D.ORDER_DATE_TYPE_CODE,2,
D.REQUEST_DATE,D.REQUEST_SHIP_DATE) col29 --bug3263368
FROM
MSC_DEMANDS D,
-- Bug 3823042
--MSC_CALENDAR_DATES C,
MSC_ALLOC_HIERARCHY_TEMP TEMP,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE
D.PLAN_ID = p_plan_id
AND D.SR_INSTANCE_ID = p_instance_id
AND D.INVENTORY_ITEM_ID = l_item_to_use
AND D.ORGANIZATION_ID = p_organization_id
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,52) -- For summary enhancement
AND D.CUSTOMER_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
-- Bug 3823042
/*
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND C.CALENDAR_DATE
BETWEEN
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
TRUNC(DECODE(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(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 (( D.ORIGINATION_TYPE = 4
AND C.SEQ_NUM IS NOT NULL) OR
( D.ORIGINATION_TYPE <> 4))
AND C.PRIOR_DATE < NVL(p_infinite_time_fence_date, C.PRIOR_DATE + 1)
*/
AND TRUNC(DECODE(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(p_infinite_time_fence_date)
-- bug 2763784 (ssurendr)
-- Should not select supply/demand where the original quantity itself is 0
AND (D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)) <> 0 -- Bug 3823042, donot care about repititive demands
UNION ALL
SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
S.TRANSACTION_ID col6, -- Identifier3
2 col7, -- supply_demand_type
-- Bug 3823042
--C.NEXT_DATE col8, -- supply_demand_date
GREATEST(TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)),l_sys_next_date) col8,
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY) col9, -- supply_demand_source_quantity
S.ORDER_TYPE col10, -- supply_demand_source_type
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY)
* DECODE(DECODE(S.CUSTOMER_ID, NULL, NULL,
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
S.CUSTOMER_ID,
S.SHIP_TO_SITE_ID,
l_item_to_use,
p_organization_id,
p_instance_id,
--C.NEXT_DATE,
TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)),
l_level_id,
NULL)),
TEMP.LEVEL_3_DEMAND_CLASS,
1,
NULL,
NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.LEVEL_3_DEMAND_CLASS,
--c.next_date),
TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE))),
1),
DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.LEVEL_3_DEMAND_CLASS,
--c.next_date),
TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE))),
NULL, 1, 0)
) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
DECODE(S.ORDER_TYPE,
5, to_char(S.TRANSACTION_ID),
S.ORDER_NUMBER) col14, -- disposition_name
TEMP.LEVEL_3_DEMAND_CLASS col15, -- demand_class
TEMP.LEVEL_1_DEMAND_CLASS col16, -- class
TEMP.PARTNER_ID col17, -- partner_id
TEMP.PARTNER_SITE_ID col18, -- partner_site_id
l_uom_code col19, -- UOM Code
l_item_name_to_use col20, -- Item name --Bug 3823042
--l_inv_item_name col20, -- Item name
l_org_code col21, -- Org code
TEMP.LEVEL_3_DEMAND_CLASS_PRIORITY col22, -- Level 3 priority
TEMP.ALLOCATION_PERCENT col23, -- Sysdate allocation percent
TEMP.customer_name col24, -- Customer Name
TEMP.customer_site_name col25, -- Customer Site Name
l_null_char col26, --bug3263368 ORIG_CUSTOMER_SITE_NAME --Bug 3875786
l_null_char col27, --bug3263368 ORIG_CUSTOMER_NAME --Bug 3875786
l_null_char col28, --bug3263368 ORIG_DEMAND_CLASS --Bug 3875786
l_null_date COL29 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - null removed
FROM
-- Bug 3823042
--MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ALLOC_HIERARCHY_TEMP TEMP
WHERE
S.PLAN_ID = p_plan_id
AND S.SR_INSTANCE_ID = p_instance_id
AND S.INVENTORY_ITEM_ID = l_item_to_use
AND S.ORGANIZATION_ID = p_organization_id
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
/*
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
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 >= DECODE(S.ORDER_TYPE, 27, TRUNC(SYSDATE), 28, TRUNC(SYSDATE), C.NEXT_DATE)
AND C.NEXT_DATE < NVL(p_infinite_time_fence_date, C.NEXT_DATE + 1)
*/
-- Bug 3823042, Using TRUNC's wherever required
AND TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)) >=
TRUNC(DECODE(S.ORDER_TYPE, 27,SYSDATE,
28, SYSDATE,
NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)))
AND TRUNC(NVL(S.FIRM_DATE, S.NEW_SCHEDULE_DATE)) < TRUNC(p_infinite_time_fence_date) -- Bug 3823042
);
l_insert_count := SQL%ROWCOUNT;
INSERT INTO MRP_ATP_DETAILS_TEMP
(session_id, atp_level, inventory_item_id, organization_id, identifier1, identifier3,
supply_demand_type, supply_demand_date, supply_demand_quantity, supply_demand_source_type,
allocated_quantity, record_type, scenario_id, disposition_name, demand_class, class, customer_id,
customer_site_id, uom_code, inventory_item_name, organization_code, identifier2, identifier4,
Customer_Name, Customer_Site_Name,
ORIG_CUSTOMER_SITE_NAME,ORIG_CUSTOMER_NAME,ORIG_DEMAND_CLASS,ORIG_REQUEST_DATE ) --bug3263368
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
FROM
(SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
D.DEMAND_ID col6, -- Identifier3
1 col7, -- supply_demand_type
--C.PRIOR_DATE col8, -- supply_demand_date
GREATEST(C.CALENDAR_DATE,l_sys_next_date) col8, -- Supply_demand_date
-1 * DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) col9, -- supply_demand_quantity
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE) col10, -- supply_demand_source_type
-1* DECODE(D.ORIGINATION_TYPE,
4, D.DAILY_DEMAND_RATE,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0)))*
DECODE(DECODE(D.CUSTOMER_ID, NULL, NULL,
0, NULL,
decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
C.CALENDAR_DATE,
l_level_id, NULL),
NULL),
30, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
NULL),
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(D.CUSTOMER_ID, D.SHIP_TO_SITE_ID,
l_item_to_use, p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL))),
TEMP.LEVEL_3_DEMAND_CLASS, 1,
MSC_AATP_FUNC.Get_Item_Demand_Alloc_Percent(p_plan_id,
D.DEMAND_ID,
--c.prior_date,
C.CALENDAR_DATE,
D.USING_ASSEMBLY_ITEM_ID,
DECODE(D.SOURCE_ORGANIZATION_ID,
-23453, null,
D.SOURCE_ORGANIZATION_ID),
l_item_to_use,
p_organization_id,
p_instance_id,
decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
DECODE(D.CUSTOMER_ID, NULL, TEMP.LEVEL_3_DEMAND_CLASS,
0, TEMP.LEVEL_3_DEMAND_CLASS,
decode(decode(D.ORIGINATION_TYPE,-100,30,D.ORIGINATION_TYPE),
6, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
C.CALENDAR_DATE,
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date, */
C.CALENDAR_DATE,
l_level_id, NULL),
TEMP.LEVEL_3_DEMAND_CLASS),
30, decode(d.source_organization_id,
NULL, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
-23453, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
d.organization_id, MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
D.CUSTOMER_ID, D.SHIP_TO_SITE_ID, l_item_to_use,
p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL),
TEMP.LEVEL_3_DEMAND_CLASS),
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(D.CUSTOMER_ID, D.SHIP_TO_SITE_ID,
l_item_to_use, p_organization_id, p_instance_id, /*c.prior_date,*/
C.CALENDAR_DATE,
l_level_id, NULL))),
TEMP.LEVEL_3_DEMAND_CLASS,
l_level_id)) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- rajjain 04/25/2003 Bug 2771075
-- For Planned Order Demands We will populate disposition_id in disposition_name column
DECODE(D.ORIGINATION_TYPE,
1, to_char(D.DISPOSITION_ID),
D.ORDER_NUMBER) col14, -- disposition_name
TEMP.LEVEL_3_DEMAND_CLASS col15, -- demand_class
TEMP.LEVEL_1_DEMAND_CLASS col16, -- class
TEMP.PARTNER_ID col17, -- partner_id
TEMP.PARTNER_SITE_ID col18, -- partner_site_id
l_uom_code col19, -- UOM Code
l_item_name_to_use col20, -- Item name --Bug 3823042
--l_inv_item_name col20, -- Item name
l_org_code col21, -- Org code
TEMP.LEVEL_3_DEMAND_CLASS_PRIORITY col22, -- Level 3 priority
TEMP.ALLOCATION_PERCENT col23, -- Sysdate allocation percent
TEMP.customer_name col24, -- Customer Name
TEMP.customer_site_name col25, -- Customer Site Name
MTPS.LOCATION col26, --bug3263368
MTP.PARTNER_NAME col27, --bug3263368
D.DEMAND_CLASS col28, --bug3263368
DECODE(D.ORDER_DATE_TYPE_CODE,2,
D.REQUEST_DATE,D.REQUEST_SHIP_DATE) col29 --bug3263368
FROM
MSC_DEMANDS D,
MSC_CALENDAR_DATES C,
MSC_ALLOC_HIERARCHY_TEMP TEMP,
MSC_TRADING_PARTNERS MTP,--bug3263368
MSC_TRADING_PARTNER_SITES MTPS --bug3263368
WHERE
D.PLAN_ID = p_plan_id
AND D.SR_INSTANCE_ID = p_instance_id
AND D.INVENTORY_ITEM_ID = l_item_to_use
AND D.ORGANIZATION_ID = p_organization_id
AND D.ORIGINATION_TYPE NOT IN (5,7,8,9,11,15,22,28,29,31,52) -- For summary enhancement
AND D.CUSTOMER_SITE_ID = MTPS.PARTNER_SITE_ID(+) --bug3263368
AND D.CUSTOMER_ID = MTP.PARTNER_ID(+)--bug3263368
AND C.CALENDAR_CODE = l_calendar_code
AND C.EXCEPTION_SET_ID = l_calendar_exception_set_id
AND C.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND C.CALENDAR_DATE
BETWEEN
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
TRUNC(DECODE(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(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 (( D.ORIGINATION_TYPE = 4
AND C.SEQ_NUM IS NOT NULL) OR
( D.ORIGINATION_TYPE <> 4))
-- Bug 3823042 , prior_date to calendar_date
AND C.CALENDAR_DATE < TRUNC(p_infinite_time_fence_date)
-- bug 2763784 (ssurendr)
-- Should not select supply/demand where the original quantity itself is 0
AND DECODE(D.ORIGINATION_TYPE, 4, D.DAILY_DEMAND_RATE,
(D.USING_REQUIREMENT_QUANTITY - NVL(d.reserved_quantity, 0))) <> 0
UNION ALL
SELECT p_session_id col1, -- session_id
l_level_id col2, -- level_id
p_inventory_item_id col3, -- inventory_item_id
p_organization_id col4, -- organization_id
p_instance_id col5, -- Identifier1
S.TRANSACTION_ID col6, -- Identifier3
2 col7, -- supply_demand_type
--C.NEXT_DATE col8, -- supply_demand_date
GREATEST(C.CALENDAR_DATE,l_sys_next_date) col8, -- supply_demand_date
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY) col9, -- supply_demand_source_quantity
S.ORDER_TYPE col10, -- supply_demand_source_type
NVL(S.FIRM_QUANTITY,
S.NEW_ORDER_QUANTITY)
* DECODE(DECODE(S.CUSTOMER_ID, NULL, NULL,
MSC_AATP_FUNC.Get_Hierarchy_Demand_Class(
S.CUSTOMER_ID,
S.SHIP_TO_SITE_ID,
l_item_to_use,
p_organization_id,
p_instance_id,
--C.NEXT_DATE,
C.CALENDAR_DATE,
l_level_id,
NULL)),
TEMP.LEVEL_3_DEMAND_CLASS,
1,
NULL,
NVL(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.LEVEL_3_DEMAND_CLASS,
--c.next_date),
C.CALENDAR_DATE),
1),
DECODE(MSC_AATP_FUNC.Get_DC_Alloc_Percent(
p_instance_id,
S.inventory_item_id,
p_organization_id,
null,
null,
TEMP.LEVEL_3_DEMAND_CLASS,
--c.next_date),
C.CALENDAR_DATE),
NULL, 1, 0)
) col11, -- allocated_quantity
l_record_type col12, -- record_type
l_scenario_id col13, -- scenario_id
-- Bug 2771075. For Planned Orders, we will populate transaction_id
-- in the disposition_name column to be consistent with Planning.
DECODE(S.ORDER_TYPE,
5, to_char(S.TRANSACTION_ID),
S.ORDER_NUMBER) col14, -- disposition_name
TEMP.LEVEL_3_DEMAND_CLASS col15, -- demand_class
TEMP.LEVEL_1_DEMAND_CLASS col16, -- class
TEMP.PARTNER_ID col17, -- partner_id
TEMP.PARTNER_SITE_ID col18, -- partner_site_id
l_uom_code col19, -- UOM Code
l_item_name_to_use col20, -- Item name --Bug 3823042
--l_inv_item_name col20, -- Item name
l_org_code col21, -- Org code
TEMP.LEVEL_3_DEMAND_CLASS_PRIORITY col22, -- Level 3 priority
TEMP.ALLOCATION_PERCENT col23, -- Sysdate allocation percent
TEMP.customer_name col24, -- Customer Name
TEMP.customer_site_name col25, -- Customer Site Name
l_null_char col26, --bug3263368 ORIG_CUSTOMER_SITE_NAME --Bug 3875786
l_null_char col27, --bug3263368 ORIG_CUSTOMER_NAME --Bug 3875786
l_null_char col28, --bug3263368 ORIG_DEMAND_CLASS --Bug 3875786
l_null_date COL29 --bug3263368 ORIG_REQUEST_DATE -- Bug 3875786 - null removed
FROM
MSC_CALENDAR_DATES C,
MSC_SUPPLIES S,
MSC_ALLOC_HIERARCHY_TEMP TEMP
WHERE
S.PLAN_ID = p_plan_id
AND S.SR_INSTANCE_ID = p_instance_id
AND S.INVENTORY_ITEM_ID = l_item_to_use -- Bug 3823042
AND S.ORGANIZATION_ID = p_organization_id
AND NVL(S.DISPOSITION_STATUS_TYPE, 1) <> 2
AND NVL(S.FIRM_QUANTITY,S.NEW_ORDER_QUANTITY) <> 0
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
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
-- Bug 3823042 , next_date to calendar_date
AND C.CALENDAR_DATE >= TRUNC(DECODE(S.ORDER_TYPE, 27,SYSDATE, 28,SYSDATE, C.CALENDAR_DATE))
AND C.CALENDAR_DATE < TRUNC(p_infinite_time_fence_date)
);
l_insert_count := SQL%ROWCOUNT;
IF (l_insert_count = 0) THEN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'No s/d records inserted into temp table');
SELECT LEVEL_3_DEMAND_CLASS, LEVEL_1_DEMAND_CLASS, PARTNER_ID, PARTNER_SITE_ID
BULK COLLECT INTO l_dc_list_tab, l_class_tab, l_customer_id_tab, l_customer_site_id_tab
FROM MSC_ALLOC_HIERARCHY_TEMP;
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'Before selecting supply/demand records from temp table into PL/SQL period table.');
SELECT
final.col1,
final.col2,
final.col3,
final.col4,
final.col5,
null,
p_inventory_item_id,
p_organization_id,
p_instance_id,
l_scenario_id,
l_level_id,
null,
0,
0,
final.col6,
final.col7,
final.col8,
final.col9,
final.col10,
final.col11,
null, -- bug 3282426
final.col12,
final.col13
BULK COLLECT INTO
x_atp_period.Demand_Class,
x_atp_period.Period_Start_Date,
x_atp_period.Total_Supply_Quantity,
x_atp_period.Total_Demand_Quantity,
x_atp_period.Period_Quantity,
x_atp_period.Total_Bucketed_Demand_Quantity, --time_phased_atp
x_atp_period.Inventory_Item_Id,
x_atp_period.Organization_Id,
x_atp_period.Identifier1,
x_atp_period.Scenario_Id,
x_atp_period.Level,
x_atp_period.Period_End_Date,
x_atp_period.Cumulative_Quantity,
x_atp_period.Demand_Adjustment_Quantity,
x_atp_period.Identifier2,
x_atp_period.Identifier4,
x_atp_period.Class,
x_atp_period.Customer_Id,
x_atp_period.Unallocated_Supply_Quantity,
x_atp_period.Unallocated_Demand_Quantity,
x_atp_period.Unalloc_Bucketed_Demand_Qty, -- bug 3282426
x_atp_period.Unallocated_Net_Quantity,
x_atp_period.Customer_Site_Id
FROM
(SELECT DEMAND_CLASS col1,
SUPPLY_DEMAND_DATE col2,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2, ALLOCATED_QUANTITY, 0)) col3,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 1, ALLOCATED_QUANTITY, 0)) col4,
SUM(ALLOCATED_QUANTITY) col5,
IDENTIFIER2 col6,
IDENTIFIER4 col7,
CLASS col8,
CUSTOMER_ID col9,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 2, SUPPLY_DEMAND_QUANTITY, 0)) col10,
SUM(DECODE(SUPPLY_DEMAND_TYPE, 1, SUPPLY_DEMAND_QUANTITY, 0)) col11,
SUM(SUPPLY_DEMAND_QUANTITY) col12,
CUSTOMER_SITE_ID col13
FROM MRP_ATP_DETAILS_TEMP
WHERE SESSION_ID = p_session_id
AND RECORD_TYPE = l_record_type
GROUP BY DEMAND_CLASS, SUPPLY_DEMAND_DATE, IDENTIFIER2, IDENTIFIER4,
CLASS, CUSTOMER_ID, CUSTOMER_SITE_ID
ORDER BY trunc(IDENTIFIER2,-3), -- Customer class priority
CLASS, -- Customer class
trunc(IDENTIFIER2,-2), -- Customer priority
CUSTOMER_ID, -- Customer
IDENTIFIER2, -- Customer site priority
CUSTOMER_SITE_ID,SUPPLY_DEMAND_DATE) final;
msc_sch_wb.atp_debug('Compute_Allocation_Details: ' || 'After selecting supply/demand records from temp table into PL/SQL period table.');
/*--Insert_Allocation_Details-----------------------------------------------
| o Called by View_Allocation_Details after calling
| Compute_Allocation_Details.
| o Inserts period data in temp table and does totalling.
+-------------------------------------------------------------------------*/
PROCEDURE Insert_Allocation_Details(
p_session_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_organization_id IN NUMBER,
p_instance_id IN NUMBER,
p_infinite_time_fence_date IN DATE,
p_atp_period IN MRP_ATP_PUB.ATP_Period_Typ,
p_plan_name IN VARCHAR2, -- bug 2771192
p_dest_inv_item_id IN NUMBER, -- For new allocation logic for time phased ATP
p_dest_family_item_id IN NUMBER, -- For new allocation logic for time phased ATP
x_return_status OUT NOCOPY VARCHAR2)
IS
-- local variables
l_period_counter PLS_INTEGER;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || '*********Inside procedure Insert_Allocation_Details ********');
/* Insert ATP Period Information */
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before inserting period records into the temp table for demand priority');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will round off.');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
period_start_date,
period_end_date,
allocated_supply_quantity,
supply_adjustment_quantity,
total_supply_quantity,
total_demand_quantity,
period_quantity,
backward_forward_quantity,
cumulative_quantity,
plan_name -- bug 2771192
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
FLOOR(p_atp_period.allocated_supply_quantity(l_period_counter)),
FLOOR(p_atp_period.supply_adjustment_quantity(l_period_counter)),
FLOOR(p_atp_period.total_supply_quantity(l_period_counter)),
FLOOR(p_atp_period.total_demand_quantity(l_period_counter)),
FLOOR(p_atp_period.period_quantity(l_period_counter)),
FLOOR(p_atp_period.backward_forward_quantity(l_period_counter)),
FLOOR(p_atp_period.cumulative_quantity(l_period_counter)),
p_plan_name -- bug 2771192
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will not round off.');*/
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
period_start_date,
period_end_date,
allocated_supply_quantity,
supply_adjustment_quantity,
total_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- For time_phased_atp
period_quantity,
backward_forward_quantity,
cumulative_quantity,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
p_atp_period.allocated_supply_quantity(l_period_counter),
p_atp_period.supply_adjustment_quantity(l_period_counter),
p_atp_period.total_supply_quantity(l_period_counter),
p_atp_period.total_demand_quantity(l_period_counter),
p_atp_period.total_bucketed_demand_quantity(l_period_counter), -- For time_phased_atp
p_atp_period.period_quantity(l_period_counter),
p_atp_period.backward_forward_quantity(l_period_counter),
p_atp_period.cumulative_quantity(l_period_counter),
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After inserting period records into the temp table');
4. If period start date is infinite time fence date, then we insert INFINITE_NUMBER for supplies
and 0 for demands.
5. If period start date is not equal to infinite time fence date, then we do sum(quantity)
*/
-- Now do the summing for Total.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before the summing SQL for Total');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
period_start_date,
allocated_supply_quantity,
supply_adjustment_quantity,
total_supply_quantity,
--total_bucketed_demand_quantity, -- for time_phased_atp
--total_demand_quantity,
total_demand_quantity, --bug3519965
total_bucketed_demand_quantity, --bug3519965
period_quantity,
backward_forward_quantity,
cumulative_quantity,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
SELECT
p_session_id,
l_scenario_id,
l_level_id,
l_record_type,
p_inventory_item_id,
p_organization_id,
p_instance_id,
final.period_start_date,
final.allocated_supply_quantity,
final.supply_adjustment_quantity,
final.total_supply_quantity,
final.total_demand_quantity,
final.total_bucketed_demand_quantity, -- for time_phased_atp
final.period_quantity,
final.backward_forward_quantity,
final.cumulative_quantity,
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
FROM
(
SELECT
mapt.period_start_date period_start_date,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
madt.allocated_supply_quantity, 0))) allocated_supply_quantity,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
madt.supply_adjustment_quantity, 0))) supply_adjustment_quantity,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
madt.total_supply_quantity, 0))) total_supply_quantity,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
madt.total_demand_quantity, 0))) total_demand_quantity,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
--madt.total_demand_quantity, 0))) total_bucketed_demand_quantity, -- for time_phased_atp
madt.total_bucketed_demand_quantity, 0))) total_bucketed_demand_quantity, --bug3519965
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
madt.period_quantity, 0))) period_quantity,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(DECODE(mapt.period_start_date,
madt.period_start_date,
madt.backward_forward_quantity, 0))) backward_forward_quantity,
DECODE(mapt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
-- rajjain 02/13/2003 Bug 2795372
SUM(GREATEST(madt.cumulative_quantity, 0))) cumulative_quantity
FROM
MRP_ATP_DETAILS_TEMP madt,
(SELECT DISTINCT(period_start_date) period_start_date
FROM MRP_ATP_DETAILS_TEMP
WHERE session_id = p_session_id
AND record_type = l_record_type) mapt
WHERE
madt.session_id = p_session_id
AND madt.record_type = l_record_type
AND mapt.period_start_date BETWEEN madt.period_start_date
AND madt.period_end_date
GROUP BY
mapt.period_start_date
) final;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After the summing SQL for Total');
/* Insert ATP Period Information */
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before inserting period records into the temp table for demand class ATP');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will round off.');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
plan_name -- bug 2771192
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
FLOOR(p_atp_period.total_supply_quantity(l_period_counter)),
FLOOR(p_atp_period.total_demand_quantity(l_period_counter)),
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
FLOOR(p_atp_period.period_quantity(l_period_counter)),
FLOOR(p_atp_period.cumulative_quantity(l_period_counter)),
FLOOR(p_atp_period.Backward_Quantity(l_period_counter)),
FLOOR(p_atp_period.Demand_Adjustment_Quantity(l_period_counter)),
FLOOR(p_atp_period.Adjusted_Availability_Quantity(l_period_counter)),
p_plan_name -- bug 2771192
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will not round off.');*/
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- For time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
p_atp_period.total_supply_quantity(l_period_counter),
p_atp_period.total_demand_quantity(l_period_counter),
p_atp_period.total_bucketed_demand_quantity(l_period_counter), -- For time_phased_atp
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
p_atp_period.period_quantity(l_period_counter),
p_atp_period.cumulative_quantity(l_period_counter),
p_atp_period.Backward_Quantity(l_period_counter),
p_atp_period.Demand_Adjustment_Quantity(l_period_counter),
p_atp_period.Adjusted_Availability_Quantity(l_period_counter),
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will round off.');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
plan_name -- bug 2771192
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
FLOOR(p_atp_period.total_supply_quantity(l_period_counter)),
FLOOR(p_atp_period.total_demand_quantity(l_period_counter)),
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
FLOOR(p_atp_period.period_quantity(l_period_counter)),
FLOOR(p_atp_period.cumulative_quantity(l_period_counter)),
FLOOR(p_atp_period.Backward_Quantity(l_period_counter)),
FLOOR(p_atp_period.Demand_Adjustment_Quantity(l_period_counter)),
FLOOR(p_atp_period.Adjusted_Availability_Quantity(l_period_counter)),
FLOOR(p_atp_period.Adjusted_Cum_Quantity(l_period_counter)),
p_plan_name -- bug 2771192
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will not round off.');*/
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
p_atp_period.total_supply_quantity(l_period_counter),
p_atp_period.total_demand_quantity(l_period_counter),
p_atp_period.total_bucketed_demand_quantity(l_period_counter), -- For time_phased_atp
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
p_atp_period.period_quantity(l_period_counter),
p_atp_period.cumulative_quantity(l_period_counter),
p_atp_period.Backward_Quantity(l_period_counter),
p_atp_period.Demand_Adjustment_Quantity(l_period_counter),
p_atp_period.Adjusted_Availability_Quantity(l_period_counter),
p_atp_period.Adjusted_Cum_Quantity(l_period_counter),
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After inserting period records into the temp table');
3. If period start date is infinite time fence date, then we insert INFINITE_NUMBER for supplies
and 0 for demands.
4. If period start date is not equal to infinite time fence date, then we do sum(quantity)
*/
-- Now do the summing for Total.
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before the summing SQL for Total');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
period_start_date,
period_end_date,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
SELECT
p_session_id,
l_scenario_id,
l_level_id,
l_record_type,
p_inventory_item_id,
p_organization_id,
p_instance_id,
final.period_start_date,
final.period_end_date,
final.allocated_supply_quantity,
final.total_demand_quantity,
final.total_bucketed_demand_quantity, -- for time_phased_atp
final.period_quantity,
final.cumulative_quantity,
final.backward_quantity,
final.demand_adjustment_quantity,
final.adjusted_availability_quantity,
final.adjusted_cum_quantity,
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
FROM
(
SELECT
madt.period_start_date period_start_date,
madt.period_end_date period_end_date,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.allocated_supply_quantity)) allocated_supply_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_demand_quantity)) total_demand_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_bucketed_demand_quantity)) total_bucketed_demand_quantity, -- for time_phased_atp
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.period_quantity)) period_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.cumulative_quantity)) cumulative_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.backward_quantity)) backward_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.demand_adjustment_quantity)) demand_adjustment_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_availability_quantity)) adjusted_availability_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_cum_quantity)) adjusted_cum_quantity
FROM
MRP_ATP_DETAILS_TEMP madt
WHERE
madt.session_id = p_session_id
AND madt.record_type = l_record_type
AND madt.demand_class <> G_UNALLOCATED_DC
GROUP BY
period_start_date, period_end_date
) final;
UPDATE MRP_ATP_DETAILS_TEMP madt
SET (Actual_Allocation_Percent, Allocation_Percent)=
(SELECT mv.allocation_percent, mv.level_alloc_percent
FROM MSC_ITEM_HIERARCHY_MV mv
WHERE mv.Demand_Class = madt.Demand_Class
AND mv.Organization_Id = p_organization_id
AND mv.Sr_Instance_Id = p_instance_id
AND madt.Period_Start_Date between mv.Effective_Date and mv.Disable_Date
AND mv.Level_Id = madt.Atp_Level
AND mv.Inventory_Item_Id = Decode(sign(trunc(madt.Period_Start_Date) - G_ATF_Date),
1, p_dest_family_item_id,
Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
'Y', p_dest_inv_item_id,
p_dest_family_item_id)))
WHERE madt.Record_Type = 1
AND madt.Session_Id = p_session_id;
UPDATE MRP_ATP_DETAILS_TEMP madt
--rajjain 02/13/2003 Bug 2795636
--SET (Allocation_Percent, Actual_Allocation_Percent)=
SET (Actual_Allocation_Percent, Allocation_Percent)=
(SELECT mv.allocation_percent, mv.level_alloc_percent
FROM MSC_ITEM_HIERARCHY_MV mv
WHERE mv.Demand_Class = madt.Demand_Class
AND mv.Organization_Id = p_organization_id
AND mv.Sr_Instance_Id = p_instance_id
AND madt.Period_Start_Date between mv.Effective_Date and mv.Disable_Date
AND mv.Level_Id = madt.Atp_Level
AND mv.Inventory_Item_Id = p_dest_inv_item_id)
WHERE madt.Record_Type = 1
AND madt.Session_Id = p_session_id;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After the summing SQL for Total');
/* Insert ATP Period Information */
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before inserting customer site level period records into the temp table');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will round off.');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
class,
customer_id,
customer_site_id,
plan_name -- bug 2771192
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
FLOOR(p_atp_period.total_supply_quantity(l_period_counter)),
FLOOR(p_atp_period.total_demand_quantity(l_period_counter)),
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
FLOOR(p_atp_period.period_quantity(l_period_counter)),
FLOOR(p_atp_period.cumulative_quantity(l_period_counter)),
FLOOR(p_atp_period.Backward_Quantity(l_period_counter)),
FLOOR(p_atp_period.Demand_Adjustment_Quantity(l_period_counter)),
FLOOR(p_atp_period.Adjusted_Availability_Quantity(l_period_counter)),
p_atp_period.Class(l_period_counter),
p_atp_period.Customer_Id(l_period_counter),
p_atp_period.Customer_Site_Id(l_period_counter),
p_plan_name -- bug 2771192
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will not round off.');*/
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
class,
customer_id,
customer_site_id,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
p_atp_period.total_supply_quantity(l_period_counter),
p_atp_period.total_demand_quantity(l_period_counter),
p_atp_period.total_bucketed_demand_quantity(l_period_counter), -- For time_phased_atp
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
p_atp_period.period_quantity(l_period_counter),
p_atp_period.cumulative_quantity(l_period_counter),
p_atp_period.Backward_Quantity(l_period_counter),
p_atp_period.Demand_Adjustment_Quantity(l_period_counter),
p_atp_period.Adjusted_Availability_Quantity(l_period_counter),
p_atp_period.Class(l_period_counter),
p_atp_period.Customer_Id(l_period_counter),
p_atp_period.Customer_Site_Id(l_period_counter),
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will round off.');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
class,
customer_id,
customer_site_id,
plan_name -- bug 2771192
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
FLOOR(p_atp_period.total_supply_quantity(l_period_counter)),
FLOOR(p_atp_period.total_demand_quantity(l_period_counter)),
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
FLOOR(p_atp_period.period_quantity(l_period_counter)),
FLOOR(p_atp_period.cumulative_quantity(l_period_counter)),
FLOOR(p_atp_period.Backward_Quantity(l_period_counter)),
FLOOR(p_atp_period.Demand_Adjustment_Quantity(l_period_counter)),
FLOOR(p_atp_period.Adjusted_Availability_Quantity(l_period_counter)),
FLOOR(p_atp_period.Adjusted_Cum_Quantity(l_period_counter)),
p_atp_period.Class(l_period_counter),
p_atp_period.Customer_Id(l_period_counter),
p_atp_period.Customer_Site_Id(l_period_counter),
p_plan_name -- bug 2771192
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Will not round off.');*/
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
demand_class,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_start_date,
period_end_date,
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
class,
customer_id,
customer_site_id,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
VALUES
(
p_session_id,
p_atp_period.scenario_id(l_period_counter),
p_atp_period.level(l_period_counter),
l_record_type,
p_atp_period.inventory_item_id(l_period_counter),
p_atp_period.organization_id(l_period_counter),
p_atp_period.identifier1(l_period_counter),
p_atp_period.demand_class(l_period_counter),
p_atp_period.total_supply_quantity(l_period_counter),
p_atp_period.total_demand_quantity(l_period_counter),
p_atp_period.total_bucketed_demand_quantity(l_period_counter), -- For time_phased_atp
p_atp_period.period_start_date(l_period_counter),
p_atp_period.period_end_date(l_period_counter),
p_atp_period.period_quantity(l_period_counter),
p_atp_period.cumulative_quantity(l_period_counter),
p_atp_period.Backward_Quantity(l_period_counter),
p_atp_period.Demand_Adjustment_Quantity(l_period_counter),
p_atp_period.Adjusted_Availability_Quantity(l_period_counter),
p_atp_period.Adjusted_Cum_Quantity(l_period_counter),
p_atp_period.Class(l_period_counter),
p_atp_period.Customer_Id(l_period_counter),
p_atp_period.Customer_Site_Id(l_period_counter),
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
);
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After inserting customer site level period records into the temp table');
3. If period start date is infinite time fence date, then we insert INFINITE_NUMBER for supply
columns and 0 for demand columns.
4. If period start date is not equal to infinite time fence date, then we do sum(quantity)
5. The grouping is done on class, customer_id, period_start_date and period_end_date
*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before the summing SQL for customer level');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
period_start_date,
period_end_date,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
class,
customer_id,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
SELECT
p_session_id,
l_scenario_id,
final.level_id,
l_record_type,
p_inventory_item_id,
p_organization_id,
p_instance_id,
final.period_start_date,
final.period_end_date,
final.allocated_supply_quantity,
final.total_demand_quantity,
final.total_bucketed_demand_quantity, -- for time_phased_atp
final.period_quantity,
final.cumulative_quantity,
final.backward_quantity,
final.demand_adjustment_quantity,
final.adjusted_availability_quantity,
final.adjusted_cum_quantity,
final.class,
final.customer_id,
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
FROM
(
SELECT
madt.class class,
madt.customer_id customer_id,
2 level_id,
madt.period_start_date period_start_date,
madt.period_end_date period_end_date,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.allocated_supply_quantity)) allocated_supply_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_demand_quantity)) total_demand_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_bucketed_demand_quantity)) total_bucketed_demand_quantity, -- for time_phased_atp
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.period_quantity)) period_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.cumulative_quantity)) cumulative_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.backward_quantity)) backward_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.demand_adjustment_quantity)) demand_adjustment_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_availability_quantity)) adjusted_availability_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_cum_quantity)) adjusted_cum_quantity
FROM
MRP_ATP_DETAILS_TEMP madt
WHERE
madt.session_id = p_session_id
AND madt.record_type = l_record_type
AND madt.ATP_Level = 3
AND madt.demand_class <> G_UNALLOCATED_DC
GROUP BY
class, customer_id, period_start_date, period_end_date
) final;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Error occured while doing sub-Total for customer level');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After the summing SQL for Customer level');
2. We do sum over the customer level records inserted by the earlier SQL.
3. We do a direct sum for all columns because data for all customers will be present for all dates.
4. If period start date is infinite time fence date, then we insert INFINITE_NUMBER for supply
columns and 0 for demand columns.
5. If period start date is not equal to infinite time fence date, then we do sum(quantity)
6. The grouping is done on class, period_start_date and period_end_date
*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before the summing SQL for customer class level');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
period_start_date,
period_end_date,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
class,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
SELECT
p_session_id,
l_scenario_id,
final.level_id,
l_record_type,
p_inventory_item_id,
p_organization_id,
p_instance_id,
final.period_start_date,
final.period_end_date,
final.allocated_supply_quantity,
final.total_demand_quantity,
final.total_bucketed_demand_quantity, -- for time_phased_atp
final.period_quantity,
final.cumulative_quantity,
final.backward_quantity,
final.demand_adjustment_quantity,
final.adjusted_availability_quantity,
final.adjusted_cum_quantity,
final.class,
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
FROM
(
SELECT
madt.class class,
1 level_id,
madt.period_start_date period_start_date,
madt.period_end_date period_end_date,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.allocated_supply_quantity)) allocated_supply_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_demand_quantity)) total_demand_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_bucketed_demand_quantity)) total_bucketed_demand_quantity, -- for time_phased_atp
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.period_quantity)) period_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.cumulative_quantity)) cumulative_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.backward_quantity)) backward_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.demand_adjustment_quantity)) demand_adjustment_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_availability_quantity)) adjusted_availability_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_cum_quantity)) adjusted_cum_quantity
FROM
MRP_ATP_DETAILS_TEMP madt
WHERE
madt.session_id = p_session_id
AND madt.record_type = l_record_type
AND madt.ATP_Level = 2
GROUP BY
class, period_start_date, period_end_date
) final;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Error occured while doing sub-Total for customer class level');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After the summing SQL for Customer class level');
2. We do sum over the customer class level records inserted by the earlier SQL.
3. We do a direct sum for all columns because data for all classes will be present for all dates.
4. If period start date is infinite time fence date, then we insert INFINITE_NUMBER for supply
columns and 0 for demand columns.
5. If period start date is not equal to infinite time fence date, then we do sum(quantity)
6. The grouping is done on period_start_date and period_end_date
*/
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Before the summing SQL at grand total level');
INSERT INTO MRP_ATP_DETAILS_TEMP
(
session_id,
scenario_id,
atp_level,
record_type,
inventory_item_id,
organization_id,
identifier1,
period_start_date,
period_end_date,
allocated_supply_quantity,
total_demand_quantity,
total_bucketed_demand_quantity, -- for time_phased_atp
period_quantity,
cumulative_quantity,
backward_quantity,
demand_adjustment_quantity,
adjusted_availability_quantity,
adjusted_cum_quantity,
plan_name, -- bug 2771192
aggregate_time_fence_date -- for time_phased_atp
)
SELECT
p_session_id,
l_scenario_id,
final.level_id,
l_record_type,
p_inventory_item_id,
p_organization_id,
p_instance_id,
final.period_start_date,
final.period_end_date,
final.allocated_supply_quantity,
final.total_demand_quantity,
final.total_bucketed_demand_quantity, -- for time_phased_atp
final.period_quantity,
final.cumulative_quantity,
final.backward_quantity,
final.demand_adjustment_quantity,
final.adjusted_availability_quantity,
final.adjusted_cum_quantity,
p_plan_name, -- bug 2771192
G_ATF_DATE -- for time_phased_atp
FROM
(
SELECT
-1 level_id,
madt.period_start_date period_start_date,
madt.period_end_date period_end_date,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.allocated_supply_quantity)) allocated_supply_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_demand_quantity)) total_demand_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.total_bucketed_demand_quantity)) total_bucketed_demand_quantity, -- for time_phased_atp
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.period_quantity)) period_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.cumulative_quantity)) cumulative_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.backward_quantity)) backward_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
0,
SUM(madt.demand_adjustment_quantity)) demand_adjustment_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_availability_quantity)) adjusted_availability_quantity,
DECODE(madt.period_start_date,
p_infinite_time_fence_date,
MSC_ATP_PVT.INFINITE_NUMBER,
SUM(madt.adjusted_cum_quantity)) adjusted_cum_quantity
FROM
MRP_ATP_DETAILS_TEMP madt
WHERE
madt.session_id = p_session_id
AND madt.record_type = l_record_type
AND madt.ATP_Level = 1
GROUP BY
period_start_date, period_end_date
) final;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Error occured while doing grand-Total for customer class case');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After the summing SQL at grand total level level');
UPDATE MRP_ATP_DETAILS_TEMP madt
--rajjain 02/13/2003 Bug 2795636
--SET (Allocation_Percent, Actual_Allocation_Percent)=
SET (Actual_Allocation_Percent, Allocation_Percent)=
(SELECT mv.allocation_percent, mv.level_alloc_percent
FROM MSC_ITEM_HIERARCHY_MV mv
WHERE mv.Class = madt.Class
AND nvl(mv.Partner_Id, -23453) = nvl(madt.Customer_Id, -23453)
AND nvl(mv.Partner_Site_Id, -23453) = nvl(madt.Customer_Site_Id, -23453)
AND mv.Organization_Id = p_organization_id
AND mv.Sr_Instance_Id = p_instance_id
AND madt.Period_Start_Date between mv.Effective_Date and mv.Disable_Date
AND mv.Level_Id = madt.Atp_Level
AND mv.Inventory_Item_Id = Decode(sign(trunc(madt.Period_Start_Date) - G_ATF_Date),
1, p_dest_family_item_id,
Decode(MSC_ATP_PVT.G_MEM_RULE_WITHIN_ATF,
'Y', p_dest_inv_item_id,
p_dest_family_item_id)))
WHERE madt.Record_Type = 1
AND madt.Session_Id = p_session_id;
UPDATE MRP_ATP_DETAILS_TEMP madt
--rajjain 02/13/2003 Bug 2795636
--SET (Allocation_Percent, Actual_Allocation_Percent)=
SET (Actual_Allocation_Percent, Allocation_Percent)=
(SELECT mv.allocation_percent, mv.level_alloc_percent
FROM MSC_ITEM_HIERARCHY_MV mv
WHERE mv.Class = madt.Class
AND nvl(mv.Partner_Id, -23453) = nvl(madt.Customer_Id, -23453)
AND nvl(mv.Partner_Site_Id, -23453) = nvl(madt.Customer_Site_Id, -23453)
AND mv.Organization_Id = p_organization_id
AND mv.Sr_Instance_Id = p_instance_id
AND madt.Period_Start_Date between mv.Effective_Date and mv.Disable_Date
AND mv.Level_Id = madt.Atp_Level
AND mv.Inventory_Item_Id = p_dest_inv_item_id)
WHERE madt.Record_Type = 1
AND madt.Session_Id = p_session_id;
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'After the summing for Total for customer class allocation');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || '*********End of procedure Insert_Allocation_Details ********');
msc_sch_wb.atp_debug('Insert_Allocation_Details: ' || 'Error in Insert_Allocation_Details: ' || to_char(sqlcode) || ':' || SQLERRM);
END Insert_allocation_Details;
select meaning
from mfg_lookups
where lookup_type = 'MTL_DEMAND_INTERFACE_ERRORS'
and lookup_code = p_error_code;
msc_sch_wb.atp_debug ('Adjust_Cum: ' || 'Updated Unallocated Cum date:qty - '||
p_atp_period.Period_Start_Date(i) ||' : '|| p_atp_period.Adjusted_Cum_Quantity(i) );