[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Summary_Flag (
p_plan_id IN number,
p_status IN number,
x_return_status OUT NOCOPY varchar2
) IS
BEGIN
IF PG_DEBUG in ('Y', 'C') THEN
msc_util.msc_log ('*****--- Update_Summary_Flag ---*****');
update msc_plans
set summary_flag = p_status
where plan_id = p_plan_id;
msc_util.msc_log ('Cannot Update. Error : ' || sqlerrm);
END Update_Summary_Flag;
SELECT
req.plan_id, req.sr_instance_id,
req.organization_id, req.assembly_item_id, req.transaction_id,
req.resource_id,
req.department_id,
-- Bug 3321897 For Line Based Resources,
-- Resource_ID is not NULL but -1
DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
REQ.RESOURCE_HOURS) RESOURCE_HOURS,
-- Bug 3321897 For Line Based Resources,
-- Resource_ID is not NULL but -1
DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- Bug 3348095
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS))) DAILY_RESOURCE_HOURS,
-- For ATP created records use resource_hours
-- End Bug 3348095
TRUNC(start_date) start_date,
TRUNC(end_date) end_date,
-- Bug 3321897 For Line Based Resources,
-- Resource_ID is not NULL but -1
-- Bug 3455997
-- For lot based resources either the complete req. is relieved or none at all.
( DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
REQ.RESOURCE_HOURS) * decode (atp_peg_tab.supply_quantity(i),0 ,0,
decode(basis_type,
1, ABS(atp_peg_tab.relief_quantity(i))/atp_peg_tab.supply_quantity(i),
2, FLOOR (ABS(atp_peg_tab.relief_quantity(i))/atp_peg_tab.supply_quantity(i) )))
* SIGN (atp_peg_tab.relief_quantity(i)) ) RELIEF_QUANTITY,
-- End Bug 3455997
-- Bug 3321897 For Line Based Resources,
-- Resource_ID is not NULL but -1
-- Bug 3455997
-- For lot based resources either the complete req. is relieved or none at all.
( DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- Bug 3348095
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS)))
-- For ATP created records use resource_hours
-- End Bug 3348095
* decode (atp_peg_tab.supply_quantity(i),0 ,0,
Decode (basis_type,
1, ABS(atp_peg_tab.relief_quantity(i))/atp_peg_tab.supply_quantity (i),
2, FLOOR(ABS(atp_peg_tab.relief_quantity(i))/atp_peg_tab.supply_quantity(i))))
* SIGN (atp_peg_tab.relief_quantity(i)) ) DAILY_RELIEF_QTY
-- End Bug 3455997
BULK COLLECT
INTO res_peg_tab.plan_id, res_peg_tab.sr_instance_id, res_peg_tab.organization_id,
res_peg_tab.inventory_item_id, res_peg_tab.supply_id, res_peg_tab.resource_id,
res_peg_tab.department_id, res_peg_tab.resource_hours, res_peg_tab.daily_resource_hours,
res_peg_tab.start_date, res_peg_tab.end_date, res_peg_tab.relief_quantity,
res_peg_tab.daily_relief_qty
FROM msc_resource_requirements req
WHERE req.plan_id = atp_peg_tab.plan_id(i)
AND req.sr_instance_id = atp_peg_tab.sr_instance_id(i)
AND req.organization_id = atp_peg_tab.organization_id(i)
AND req.supply_id = atp_peg_tab.supply_id(i)
AND req.assembly_item_id = atp_peg_tab.inventory_item_id(i)
-- Bug 3362558 Exclude Department Id -1
-- No need to fetch resource_consumption that is 0.
AND REQ.DEPARTMENT_ID <> -1
AND ( DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
REQ.RESOURCE_HOURS) > 0
OR
DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
DECODE(REQ.END_DATE, NULL, REQ.RESOURCE_HOURS,
-- Bug 3348095
DECODE(REQ.record_source, 2, REQ.RESOURCE_HOURS,
REQ.DAILY_RESOURCE_HOURS))) > 0
-- For ATP created records use resource_hours
-- End Bug 3348095
)
-- End Bug 3362558
-- Bug 3455997 Ensure that relief_quantity is greater than 0.
-- For lot based resources either the complete req. is relieved or none at all.
AND ( DECODE(REQ.RESOURCE_ID, -1, REQ.LOAD_RATE,
REQ.RESOURCE_HOURS) * decode (atp_peg_tab.supply_quantity(i),0 ,0,
decode(basis_type,
1, ABS(atp_peg_tab.relief_quantity(i))/atp_peg_tab.supply_quantity(i),
2, FLOOR (ABS(atp_peg_tab.relief_quantity(i))/atp_peg_tab.supply_quantity(i) )))
) > 0
-- Here the SIGN multiplier is not used to
-- Ensure that relief_quantity is greater than 0.
-- End Bug 3455997
AND atp_peg_tab.bom_item_type(i) = 4
AND atp_peg_tab.base_item_id(i) IS NOT NULL
AND atp_peg_tab.relief_type(i) = 2
;
SELECT msi.plan_id, msi.sr_instance_id,
fcst.inventory_item_id, fcst.parent_item_id,
msi.organization_id, fcst.organization_id fcst_organization_id,
-- Bug 3562722 forecast_demand_id is immaterial since SO will get
-- get consumed against one forecast set.
-1 forecast_demand_id, fcst.sales_order_id, sales_order_qty,
-- Forecast Qty is not used in ATP Post Plan Pegging computation
SUM(fcst.forecast_qty) forecast_qty, SUM(fcst.consumed_qty) consumed_qty,
SUM(NVL(fcst.overconsumption_qty, 0)) overconsumption_qty,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
-- Bug 3701093
1, -- Overconsumption of Model Forecast happens
decode(fcst.inventory_item_id, fcst.parent_item_id, 0, -- config first
atp_peg_tab.base_item_id(k), 1, -- model second
fcst.inventory_item_id) -- others later
-- End Bug 3562722
BULK COLLECT
-- Bug 3417410 Collect into return record.
INTO x_fcst_data_tab.plan_id, x_fcst_data_tab.sr_instance_id,
x_fcst_data_tab.inventory_item_id, x_fcst_data_tab.parent_item_id,
x_fcst_data_tab.organization_id, x_fcst_data_tab.fcst_organization_id,
x_fcst_data_tab.fcst_demand_id, x_fcst_data_tab.sales_order_id,
x_fcst_data_tab.sales_order_qty, x_fcst_data_tab.forecast_qty,
x_fcst_data_tab.consumed_qty, x_fcst_data_tab.overconsumption_qty,
x_fcst_data_tab.bom_item_type, x_fcst_data_tab.fixed_lt,
x_fcst_data_tab.variable_lt,
-- Bug 3701093, flag overconsumption
x_fcst_data_tab.cons_config_mod_flag, l_item_typ
-- End Bug 3417410 Collect into return record.
FROM msc_forecast_updates fcst, msc_system_items msi
WHERE fcst.sr_instance_id = atp_peg_tab.sr_instance_id(k)
AND fcst.plan_id = atp_peg_tab.plan_id(k)
AND (fcst.organization_id = atp_peg_tab.organization_id(k)
OR fcst.organization_id = -1)
-- First check for local forecast
-- CTO_PF_PRJ changes. Use end_demand_id
AND (fcst.sales_order_id = atp_peg_tab.original_demand_id(k)
OR
fcst.sales_order_id = atp_peg_tab.end_demand_id(k)
)
-- CTO_PF_PRJ
AND fcst.parent_item_id = atp_peg_tab.inventory_item_id(k)
AND msi.plan_id = fcst.plan_id
AND msi.sr_instance_id = fcst.sr_instance_id
AND msi.organization_id = atp_peg_tab.organization_id(k)
AND (msi.wip_supply_type = 6 or msi.bom_item_type = 1)
-- only phantom items or models are obtained
-- forecast updates may contain lower level models and configuration
-- items. Ensure that they are not obtained
AND msi.inventory_item_id = decode(msi.bom_item_type,
--1, atp_peg_tab.base_item_id(k),
4, decode(msi.base_item_id,
NULL, fcst.inventory_item_id,
fcst.parent_item_id),
fcst.inventory_item_id )
-- Bug 3562722 Setup a SUM and group by since SO could get consumed
-- through combination of several forecasts.
GROUP BY msi.plan_id, msi.sr_instance_id,
fcst.inventory_item_id, fcst.parent_item_id,
msi.organization_id, fcst.organization_id ,
-1 , fcst.sales_order_id, sales_order_qty,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
-- Bug 3701093
1, -- Overconsumption of Model/OC Forecast happens
decode(fcst.inventory_item_id, fcst.parent_item_id, 0, -- config first
atp_peg_tab.base_item_id(k), 1, -- model second
fcst.inventory_item_id) -- others later
-- End Bug 3562722
ORDER BY decode(fcst.inventory_item_id, fcst.parent_item_id, 0,
atp_peg_tab.base_item_id(k), 1,
fcst.inventory_item_id)
;
SELECT fcst1.plan_id,
fcst1.sr_instance_id,
fcst1.inventory_item_id,
fcst1.parent_item_id,
fcst1.organization_id,
fcst1.fcst_organization_id fcst_organization_id,
-1 forecast_demand_id,
fcst1.sales_order_id,
fcst1.sales_order_qty,
SUM(fcst1.forecast_qty) forecast_qty,
SUM(fcst1.consumed_qty) consumed_qty,
SUM(nvl(fcst1.overconsumption_qty, 0)) overconsumption_qty,
fcst1.bom_item_type,
fcst1.fixed_lead_time,
fcst1.variable_lead_time,
1,
decode(fcst1.inventory_item_id, fcst1.parent_item_id, 0, atp_peg_tab.base_item_id(k), 1, fcst1.inventory_item_id)
BULK COLLECT
INTO x_fcst_data_tab.plan_id,
x_fcst_data_tab.sr_instance_id,
x_fcst_data_tab.inventory_item_id,
x_fcst_data_tab.parent_item_id,
x_fcst_data_tab.organization_id,
x_fcst_data_tab.fcst_organization_id,
x_fcst_data_tab.fcst_demand_id,
x_fcst_data_tab.sales_order_id,
x_fcst_data_tab.sales_order_qty,
x_fcst_data_tab.forecast_qty,
x_fcst_data_tab.consumed_qty,
x_fcst_data_tab.overconsumption_qty,
x_fcst_data_tab.bom_item_type,
x_fcst_data_tab.fixed_lt,
x_fcst_data_tab.variable_lt,
x_fcst_data_tab.cons_config_mod_flag,
l_item_typ
FROM
(SELECT msi.plan_id,
msi.sr_instance_id,
fcst.inventory_item_id,
fcst.parent_item_id,
msi.organization_id,
fcst.organization_id fcst_organization_id,
-1 forecast_demand_id,
fcst.sales_order_id,
sales_order_qty,
fcst.forecast_qty forecast_qty,
fcst.consumed_qty consumed_qty,
nvl(fcst.overconsumption_qty, 0) overconsumption_qty,
msi.bom_item_type,
msi.fixed_lead_time,
msi.variable_lead_time,
1,
decode(fcst.inventory_item_id, fcst.parent_item_id, 0, atp_peg_tab.base_item_id(k), 1, fcst.inventory_item_id)
FROM msc_forecast_updates fcst,
msc_system_items msi
WHERE fcst.sr_instance_id = atp_peg_tab.sr_instance_id(k)
AND fcst.plan_id = atp_peg_tab.plan_id(k)
AND(fcst.organization_id = atp_peg_tab.organization_id(k) OR fcst.organization_id = -1)
AND(fcst.sales_order_id = atp_peg_tab.original_demand_id(k) OR fcst.sales_order_id = atp_peg_tab.end_demand_id(k))
AND fcst.parent_item_id = atp_peg_tab.inventory_item_id(k)
AND msi.plan_id = fcst.plan_id
AND msi.sr_instance_id = fcst.sr_instance_id
AND msi.organization_id = atp_peg_tab.organization_id(k)
AND(msi.bom_item_type = 1 --bug 9184226
OR(msi.wip_supply_type = 6
AND msi.bom_item_type <> 1
AND (msi.bom_item_type <> 4 OR (msi.bom_item_type = 4 AND msi.base_item_id IS NULL)) )
)
AND msi.inventory_item_id = fcst.inventory_item_id
UNION ALL
SELECT msi.plan_id,
msi.sr_instance_id,
fcst.inventory_item_id,
fcst.parent_item_id,
msi.organization_id,
fcst.organization_id fcst_organization_id,
-1 forecast_demand_id,
fcst.sales_order_id,
sales_order_qty,
fcst.forecast_qty forecast_qty,
fcst.consumed_qty consumed_qty,
nvl(fcst.overconsumption_qty, 0) overconsumption_qty,
msi.bom_item_type,
msi.fixed_lead_time,
msi.variable_lead_time,
1,
decode(fcst.inventory_item_id, fcst.parent_item_id, 0, atp_peg_tab.base_item_id(k), 1, fcst.inventory_item_id)
FROM msc_forecast_updates fcst,
msc_system_items msi
WHERE fcst.sr_instance_id = atp_peg_tab.sr_instance_id(k)
AND fcst.plan_id = atp_peg_tab.plan_id(k)
AND(fcst.organization_id = atp_peg_tab.organization_id(k) OR fcst.organization_id = -1)
AND(fcst.sales_order_id = atp_peg_tab.original_demand_id(k) OR fcst.sales_order_id = atp_peg_tab.end_demand_id(k))
AND fcst.parent_item_id = atp_peg_tab.inventory_item_id(k)
AND msi.plan_id = fcst.plan_id
AND msi.sr_instance_id = fcst.sr_instance_id
AND msi.organization_id = atp_peg_tab.organization_id(k)
AND msi.wip_supply_type = 6
AND msi.bom_item_type = 4 --bug 9184226
AND msi.base_item_id IS NOT NULL
AND msi.inventory_item_id = fcst.parent_item_id) fcst1
GROUP BY fcst1.plan_id,
fcst1.sr_instance_id,
fcst1.inventory_item_id,
fcst1.parent_item_id,
fcst1.organization_id,
fcst1.fcst_organization_id,
-1,
fcst1.sales_order_id,
sales_order_qty,
fcst1.bom_item_type,
fcst1.fixed_lead_time,
fcst1.variable_lead_time,
1,
decode(fcst1.inventory_item_id, fcst1.parent_item_id, 0, atp_peg_tab.base_item_id(k), 1, fcst1.inventory_item_id)
ORDER BY decode(fcst1.inventory_item_id, fcst1.parent_item_id, 0, atp_peg_tab.base_item_id(k), 1, fcst1.inventory_item_id);
SELECT msi.plan_id, msi.sr_instance_id,
fcst.inventory_item_id, fcst.parent_item_id,
msi.organization_id, fcst.organization_id fcst_organization_id,
-1 forecast_demand_id, fcst.sales_order_id, sales_order_qty,
SUM(fcst.forecast_qty) forecast_qty, SUM(fcst.consumed_qty) consumed_qty,
SUM(NVL(fcst.overconsumption_qty,0)) overconsumption_qty,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
2 -- only config item is consumed
BULK COLLECT
INTO x_fcst_data_tab.plan_id, x_fcst_data_tab.sr_instance_id,
x_fcst_data_tab.inventory_item_id, x_fcst_data_tab.parent_item_id,
x_fcst_data_tab.organization_id, x_fcst_data_tab.fcst_organization_id,
x_fcst_data_tab.fcst_demand_id, x_fcst_data_tab.sales_order_id,
x_fcst_data_tab.sales_order_qty, x_fcst_data_tab.forecast_qty,
x_fcst_data_tab.consumed_qty, x_fcst_data_tab.overconsumption_qty,
x_fcst_data_tab.bom_item_type, x_fcst_data_tab.fixed_lt,
x_fcst_data_tab.variable_lt, x_fcst_data_tab.cons_config_mod_flag
FROM msc_forecast_updates fcst, msc_system_items msi
WHERE fcst.sr_instance_id = atp_peg_tab.sr_instance_id(k)
AND fcst.plan_id = atp_peg_tab.plan_id(k)
AND (fcst.organization_id = atp_peg_tab.organization_id(k)
OR fcst.organization_id = -1)
-- First check for local forecast
-- CTO_PF_PRJ changes. Use end_demand_id
AND (fcst.sales_order_id = atp_peg_tab.original_demand_id(k)
OR
fcst.sales_order_id = atp_peg_tab.end_demand_id(k)
)
-- CTO_PF_PRJ
AND fcst.inventory_item_id = atp_peg_tab.inventory_item_id(k)
-- Only get records where config item's forecast is consumed
AND fcst.forecast_qty > 0
AND (fcst.consumed_qty + NVL(fcst.overconsumption_qty,0)) > 0
-- Bug 3805136 atp_peg_tab.allocated_quantity(k)
-- and there is no overconsumption of corresponding base model's forecast.
AND msi.plan_id = fcst.plan_id
AND msi.sr_instance_id = fcst.sr_instance_id
AND msi.organization_id = atp_peg_tab.organization_id(k)
AND msi.inventory_item_id = fcst.inventory_item_id
GROUP BY msi.plan_id, msi.sr_instance_id,
fcst.inventory_item_id, fcst.parent_item_id,
msi.organization_id, fcst.organization_id ,
-1 , fcst.sales_order_id, sales_order_qty,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
2 -- only config item is consumed
-- Bug 3805136 (Uncomment HAVING clause)
HAVING SUM(fcst.consumed_qty) + SUM(NVL(fcst.overconsumption_qty, 0))
>= fcst.sales_order_qty
;
SELECT msi.plan_id, msi.sr_instance_id,
msi.inventory_item_id, atp_peg_tab.inventory_item_id(k),
msi.organization_id, msi.organization_id fcst_organization_id,
-1 forecast_demand_id, atp_peg_tab.original_demand_id(k) sales_order_id,
0 sales_order_qty, 0 forecast_qty, 0 consumed_qty,
atp_peg_tab.allocated_quantity(k) overconsumption_qty,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
-- Bug 3701093
0 -- No forecast Consumption Happens,
-- Generating data based on allocated quantities.
BULK COLLECT
-- Bug 3417410 Collect into local record.
INTO l_fcst_data_tab.plan_id, l_fcst_data_tab.sr_instance_id,
l_fcst_data_tab.inventory_item_id, l_fcst_data_tab.parent_item_id,
l_fcst_data_tab.organization_id, l_fcst_data_tab.fcst_organization_id,
l_fcst_data_tab.fcst_demand_id, l_fcst_data_tab.sales_order_id,
l_fcst_data_tab.sales_order_qty, l_fcst_data_tab.forecast_qty,
l_fcst_data_tab.consumed_qty, l_fcst_data_tab.overconsumption_qty,
l_fcst_data_tab.bom_item_type, l_fcst_data_tab.fixed_lt,
l_fcst_data_tab.variable_lt,
-- Bug 3701093 Model overconsumption does not happen
l_fcst_data_tab.cons_config_mod_flag
-- Just get the data from msc_system_items
-- and default the rest of the values.
-- End Bug 3417410 Collect into local record.
FROM msc_system_items msi
WHERE msi.plan_id = atp_peg_tab.plan_id(k)
AND msi.sr_instance_id = atp_peg_tab.sr_instance_id(k)
AND msi.organization_id = atp_peg_tab.organization_id(k)
AND (msi.wip_supply_type = 6 or msi.bom_item_type = 1)
-- only phantom model info is obtained
AND msi.inventory_item_id = atp_peg_tab.base_item_id(k)
;
/* Old code that links to the BOM DELETED
*/
EXCEPTION
WHEN OTHERS THEN
IF PG_DEBUG in ('Y', 'C') THEN
msc_util.msc_log ('Get_Forecast_Consumption. Error : ' || sqlerrm);
p_insert_table IN VARCHAR2,
p_user_id IN NUMBER,
p_sysdate IN DATE,
x_return_status OUT NOCOPY VARCHAR2
)
IS
l_sql_stmt VARCHAR2(800);
msc_util.msc_log(' Insert Table parameter : ' || p_insert_table );
msc_util.msc_log(' Inserting Demands');
l_sql_stmt_1 := 'INSERT INTO ' || p_insert_table || -- actually the insert table parameter.
' (reference_item_id, inventory_item_id, plan_id,
sr_instance_id, organization_id, sales_order_line_id,
demand_source_type, end_demand_id, bom_item_type,
sales_order_qty, transaction_date, demand_id,
demand_quantity, disposition_id, demand_class,
consumed_qty, overconsumption_qty, supply_id, supply_quantity,
allocated_quantity,
relief_type, relief_quantity,
pegging_id, prev_pegging_id, end_pegging_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id, offset_type)
SELECT mapt.reference_item_id, mapt.inventory_item_id, mapt.plan_id,
mapt.sr_instance_id, mapt.organization_id,
mapt.sales_order_line_id, mapt.demand_source_type, mapt.end_demand_id,
mapt.bom_item_type, mapt.sales_order_qty, mapt.transaction_date,
mapt.demand_id , mapt.demand_quantity,
mapt.disposition_id,
NVL(mv.demand_class, :l_def_dmd_class) demand_class ,
mapt.consumed_qty, mapt.overconsumption_qty,
mapt.supply_id, mapt.supply_quantity,
mapt.allocated_quantity , :l_relief_type,
mapt.relief_quantity ,
mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id,
:p_user_id, :p_sysdate, :p_user_id, :p_sysdate,
mv.partner_id, mv.partner_site_id customer_site_id,1
FROM msc_atp_peg_temp mapt, msc_item_hierarchy_mv mv
WHERE mapt.plan_id = :p_plan_id
AND mapt.relief_type = 3
AND mapt.inventory_item_id = mv.inventory_item_id(+)
AND mapt.organization_id = mv.organization_id (+)
AND mapt.sr_instance_id = mv.sr_instance_id (+)
AND mapt.transaction_date >= mv.effective_date (+)
AND mapt.transaction_date <= mv.disable_date (+)
AND mapt.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1 '
;
msc_util.msc_log('Create_Pre_Allocation_Reliefs: Number of Demand rows inserted '||
SQL%ROWCOUNT);
msc_util.msc_log(' Inserting Supplies');
l_sql_stmt_1 := 'INSERT INTO ' || p_insert_table || -- actually the insert table parameter.
' (reference_item_id, inventory_item_id, plan_id,
sr_instance_id, organization_id, sales_order_line_id,
demand_source_type, end_demand_id, bom_item_type,
sales_order_qty, transaction_date, demand_id,
demand_quantity, disposition_id, demand_class,
consumed_qty, overconsumption_qty, supply_id, supply_quantity,
allocated_quantity,
relief_type, relief_quantity,
pegging_id, prev_pegging_id, end_pegging_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id, offset_type)
SELECT mapt.reference_item_id, mapt.inventory_item_id, mapt.plan_id,
mapt.sr_instance_id, mapt.organization_id,
mapt.sales_order_line_id, mapt.demand_source_type, mapt.end_demand_id,
mapt.bom_item_type, mapt.sales_order_qty, mapt.transaction_date,
mapt.demand_id , mapt.demand_quantity,
mapt.disposition_id,
NVL(mv.demand_class, :l_def_dmd_class) demand_class ,
mapt.consumed_qty, mapt.overconsumption_qty,
mapt.supply_id, mapt.supply_quantity,
mapt.allocated_quantity , :l_relief_type,
mapt.relief_quantity ,
mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id,
:p_user_id, :p_sysdate, :p_user_id, :p_sysdate,
mv.partner_id, mv.partner_site_id customer_site_id,1
FROM msc_atp_peg_temp mapt, msc_item_hierarchy_mv mv
WHERE mapt.plan_id = :p_plan_id
AND mapt.relief_type = 2
AND mapt.inventory_item_id = mv.inventory_item_id(+)
AND mapt.organization_id = mv.organization_id (+)
AND mapt.sr_instance_id = mv.sr_instance_id (+)
AND mapt.transaction_date >= mv.effective_date (+)
AND mapt.transaction_date <= mv.disable_date (+)
AND mapt.demand_class = mv.demand_class (+)
AND mv.level_id (+) = -1 '
;
msc_util.msc_log('Create_Pre_Allocation_Reliefs: Number of Supply rows inserted '||
SQL%ROWCOUNT);
SELECT NVL(pre_alloc_hash_size, -1),
NVL(pre_alloc_sort_size, -1),
NVL(pre_alloc_parallel_degree, 1)
INTO l_hash_size,
l_sort_size,
l_parallel_degree
FROM msc_atp_parameters
WHERE rownum = 1;
INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
reference_item_id, base_item_id,
inventory_item_id,
plan_id,
sr_instance_id,
organization_id,
end_item_usage,
bom_item_type, fixed_lt, variable_lt,
sales_order_line_id,
demand_source_type,--cmro
-- CTO_PF_PRJ changes.
end_demand_id,
sales_order_qty,
process_seq_id, supply_id,
supply_date,
supply_quantity,
allocated_quantity, tot_relief_qty,
supply_type,
firm_planned_type,
release_status,
exclude_flag, -- All other cases exclude
end_pegging_id, pegging_id, prev_pegging_id,
fcst_organization_id, forecast_qty,
consumed_qty, overconsumption_qty )
SELECT DISTINCT
c_items_rec.inventory_item_id, msi.base_item_id,
peg2.inventory_item_id,
peg2.plan_id,
peg2.sr_instance_id,
peg2.organization_id,
peg2.end_item_usage,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
c_items_rec.sales_order_line_id,
c_items_rec.demand_source_type, --cmro
-- CTO_PF_PRJ changes.
NVL(d1.original_demand_id, d1.demand_id),
NULL sales_order_qty, -- will be used to factor sales_order_qty,
SUP.process_seq_id, SUP.transaction_id supply_id,
SUP.new_schedule_date supply_date,
SUP.new_order_quantity supply_qty,
peg2.allocated_quantity, NULL tot_relief_qty,
SUP.order_type,
SUP.firm_planned_type,
SUP.release_status, -- 1 released
DECODE (SUP.order_type, 5,
-- order type is 5 proceed with further checks
(DECODE(SUP.firm_planned_type, 1, 1, -- 1 firm, others not firm
-- order is not firmed proceed with further checks
-- Bug 3717618 Use "quantity_in_process"
-- instead of incorrect release_status
(DECODE(SIGN (NVL(SUP.implemented_quantity, 0) +
NVL(SUP.quantity_in_process, 0) -
NVL(SUP.firm_quantity,SUP.new_order_quantity)),
0, 1, -- equal then flag as released
1, 1, -- positive then flag as released
0)) -- 0 otherwise not released.
-- End Bug 3717618
)),
1) exclude_flag, -- All other cases exclude
peg2.end_pegging_id, peg2.pegging_id, peg2.prev_pegging_id,
NULL fcst_organization_id, NULL forecast_qty,
NULL consumed_qty, NULL overconsumption_qty
FROM
msc_demands d1,
msc_full_pegging peg1 ,
msc_full_pegging peg2 ,
msc_supplies SUP,
msc_system_items msi
WHERE d1.plan_id = p_plan_id
AND d1.sr_instance_id = c_items_rec.sr_instance_id
AND d1.inventory_item_id = c_items_rec.inventory_item_id
AND d1.origination_type IN (6,30)
AND d1.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(d1.demand_source_type,100,d1.demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --cmro
AND peg1.plan_id = d1.plan_id
AND peg1.sr_instance_id = d1.sr_instance_id
AND peg1.organization_id = d1.organization_id
AND peg1.demand_id = d1.demand_id
AND peg2.plan_id = peg1.plan_id
AND peg2.end_pegging_id = peg1.pegging_id
-- Bug 3344032 On further investigation the outer join will not be needed.
-- since the sr_instance_id join can be commented out.
--AND peg2.sr_instance_id = peg1.sr_instance_id (+) -- outer join to get all instances
-- Bug 3319810 Match the item_id as well.
AND peg2.inventory_item_id = peg1.inventory_item_id
-- Get the supplies corresponding to pegging
AND SUP.transaction_id = peg2.transaction_id
AND SUP.plan_id = peg2.plan_id
AND SUP.sr_instance_id = peg2.sr_instance_id
AND SUP.organization_id = peg2.organization_id
-- Join to msc_system_items to filter out items
-- from the pegging.
AND msi.plan_id = SUP.plan_id
AND msi.sr_instance_id = SUP.sr_instance_id
AND msi.inventory_item_id = SUP.inventory_item_id
AND msi.organization_id = SUP.organization_id
-- Restrict supplies to items that are configuration items
AND msi.bom_item_type = 4
AND msi.base_item_id is not null
AND msi.replenish_to_order_flag = 'Y'
-- Bug 3717618 Remove exclusion of firmed or released supplies here.
-- This will be handled during creation of Offset Data.
-- This data is needed for getting the pegging chain.
-- AND DECODE (SUP.order_type, 5,
-- order type is 5 proceed with further checks
-- (DECODE(SUP.firm_planned_type, 1, 1, -- 1 firm, others not firm
-- order is not firmed proceed with further checks
-- (DECODE(SIGN (NVL(SUP.implemented_quantity, 0) +
-- NVL(SUP.quantity_in_process, 0) -
-- NVL(SUP.firm_quantity,SUP.new_order_quantity)),
-- 0, 1, -- equal then flag as released
-- 1, 1, -- positive then flag as released
-- 0)) -- 0 otherwise not released.
-- )),
-- 1) <> 1 -- Exclude un-necessary supplies.
-- End Bug 3717618
;
UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt -- outer table
SET
(end_demand_id
) =
( SELECT end_demand_id
FROM msc_atp_detail_peg_temp madpti -- Inner table
WHERE madpti.plan_id = madpt.plan_id
AND madpti.sr_instance_id = madpt.sr_instance_id
AND madpti.reference_item_id = madpt.inventory_item_id
AND madpti.sales_order_line_id = madpt.sales_order_line_id
AND madpti.demand_source_type = madpt.demand_source_type
AND madpti.pegging_id = madpt.end_pegging_id
AND madpti.prev_pegging_id IS NULL
)
WHERE madpt.plan_id = p_plan_id
AND madpt.sr_instance_id = c_items_rec.sr_instance_id
AND madpt.reference_item_id = c_items_rec.inventory_item_id
AND madpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND madpt.demand_source_type
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) -- CMRO
;
UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt
SET
(demand_id,
demand_date,
demand_quantity,
disposition_id,
demand_class,
demand_type,
original_demand_id,
order_number
) =
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
( SELECT d.demand_id,
decode(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2, NVL(D.PLANNED_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE),
NVL(D.SCHEDULE_SHIP_DATE,D.USING_ASSEMBLY_DEMAND_DATE)) demand_date,
-- plan by request date
decode(d.origination_type, 4, d.daily_demand_rate,
d.using_requirement_quantity) demand_quantity,
-- Bug 3319810 Set the disposition_id for top level supply pegging.
NVL(d.disposition_id, peg.transaction_id) disposition_id,
NVL(d.demand_class, '-1') demand_class,
d.origination_type,
NVL(d.original_demand_id, d.demand_id) original_demand_id,
decode(d.origination_type, 1,
to_char(d.disposition_id), d.order_number)
FROM --msc_atp_detail_peg_temp madpt,
msc_full_pegging peg,
msc_demands d
WHERE madpt.plan_id = p_plan_id
AND madpt.sr_instance_id = c_items_rec.sr_instance_id
AND madpt.reference_item_id = c_items_rec.inventory_item_id
AND madpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(madpt.demand_source_type,100,madpt.demand_source_type,-1)
= decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --CMRO
AND peg.plan_id = madpt.plan_id
AND peg.sr_instance_id = madpt.sr_instance_id
AND peg.organization_id = madpt.organization_id
AND peg.pegging_id = madpt.pegging_id
AND peg.end_pegging_id = madpt.end_pegging_id
AND peg.inventory_item_id = madpt.inventory_item_id
-- Get the demands corresponding to pegging
AND d.plan_id = peg.plan_id
AND d.sr_instance_id = peg.sr_instance_id
AND d.organization_id = peg.organization_id
AND d.inventory_item_id = peg.inventory_item_id
AND d.demand_id = peg.demand_id)
WHERE madpt.plan_id = p_plan_id
AND madpt.sr_instance_id = c_items_rec.sr_instance_id
AND madpt.reference_item_id = c_items_rec.inventory_item_id
AND madpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(madpt.demand_source_type,100,madpt.demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) -- CMRO
-- Bug 3750638
-- Collect Supplies into Supplies PL/SQL Array.
RETURNING
inventory_item_id,
sr_instance_id,
base_item_id,
sales_order_line_id,
demand_source_type,
end_demand_id,
supply_id,
pegging_id,
end_pegging_id
BULK COLLECT INTO
L_Config_Sup.INVENTORY_ITEM_ID,
L_Config_Sup.SR_INSTANCE_ID,
L_Config_Sup.BASE_ITEM_ID,
L_Config_Sup.SALES_ORDER_LINE_ID,
L_Config_Sup.DEMAND_SOURCE_TYPE,
L_Config_Sup.END_DEMAND_ID,
L_Config_Sup.SUPPLY_ID,
L_Config_Sup.PEGGING_ID,
L_Config_Sup.END_PEGGING_ID
;
INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
reference_item_id, base_item_id,
inventory_item_id,
plan_id,
sr_instance_id,
organization_id,
end_item_usage,
bom_item_type, fixed_lt, variable_lt,
sales_order_line_id,
demand_source_type,--cmro
-- CTO_PF_PRJ changes.
end_demand_id,
-- CTO-PF
atf_date,
product_family_id,
sales_order_qty,
demand_id,
demand_date,
demand_quantity,
disposition_id,
demand_class,
demand_type,
original_demand_id,
process_seq_id, supply_id,
supply_date,
supply_quantity,
allocated_quantity, tot_relief_qty,
supply_type,
firm_planned_type,
release_status,
exclude_flag, -- All other cases exclude
order_number,
end_pegging_id, pegging_id, prev_pegging_id,
fcst_organization_id, forecast_qty,
consumed_qty, overconsumption_qty )
SELECT
adpt.reference_item_id, msi.base_item_id,
peg1.inventory_item_id,
peg1.plan_id,
peg1.sr_instance_id,
peg1.organization_id,
peg1.end_item_usage,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
adpt.sales_order_line_id,
adpt.demand_source_type,--cmro
-- CTO_PF_PRJ changes.
adpt.end_demand_id,
-- CTO-PF
msi.aggregate_time_fence_date,
msi.product_family_id,
NULL sales_order_qty, -- will be used to factor sales_order_qty,
d1.demand_id,
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
DECODE(
d1.RECORD_SOURCE,
2,
NVL(d1.SCHEDULE_SHIP_DATE,d1.USING_ASSEMBLY_DEMAND_DATE),
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2,
(NVL(d1.IMPLEMENT_SHIP_DATE,NVL(d1.FIRM_DATE,NVL(d1.PLANNED_SHIP_DATE,d1.USING_ASSEMBLY_DEMAND_DATE)))),
NVL(d1.SCHEDULE_SHIP_DATE,d1.USING_ASSEMBLY_DEMAND_DATE))), --plan by request Date, Promise Date Scheduled Date
decode(d1.origination_type, 4, d1.daily_demand_rate,
d1.using_requirement_quantity) demand_quantity,
d1.disposition_id,
NVL(d1.demand_class, adpt.demand_class) demand_class,
d1.origination_type,
-- Bug 3362558 use pegging's original demand_id
NVL(d1.original_demand_id, adpt.original_demand_id) original_demand_id,
-- Begin Bug 3319810
-- Use pegging's supply data instead of msc_atp_detail_peg_temp.
adpt.process_seq_id,
--adpt.supply_id,
--adpt.supply_date,
--adpt.supply_quantity,
--adpt.allocated_quantity, NULL tot_relief_qty,
--adpt.supply_type,
peg1.transaction_id supply_id,
-- Bug 3750638 Keep the supply date NULL for configs.
DECODE(NVL(msi.base_item_id, -2353), -2353, peg1.supply_date, NULL) supply_date,
peg1.supply_quantity,
peg1.allocated_quantity, NULL tot_relief_qty,
peg1.supply_type,
-- End Bug 3319810
adpt.firm_planned_type,
adpt.release_status, -- 1 released
0 exclude_flag, -- Include the demands as a default.
decode(d1.origination_type, 1, to_char(d1.disposition_id), d1.order_number) order_number,
peg1.end_pegging_id, peg1.pegging_id, peg1.prev_pegging_id,
NULL fcst_organization_id, NULL forecast_qty,
NULL consumed_qty, NULL overconsumption_qty
FROM
msc_atp_detail_peg_temp adpt,
msc_full_pegging peg1 ,
msc_demands d1,
msc_system_items msi
WHERE adpt.plan_id = p_plan_id
AND adpt.reference_item_id = c_items_rec.inventory_item_id
AND adpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(adpt.demand_source_type,100,adpt.demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --CMRO
-- Bug 3750638 Apply Config Item Array Filters
AND adpt.sr_instance_id = L_Config_Sup.SR_INSTANCE_ID(n_idx) -- outer join to get all instances
AND adpt.inventory_item_id = L_Config_Sup.INVENTORY_ITEM_ID(n_idx)
AND adpt.supply_id = L_Config_Sup.SUPPLY_ID(n_idx)
AND adpt.end_demand_id = L_Config_Sup.END_DEMAND_ID(n_idx)
AND adpt.pegging_id = L_Config_Sup.PEGGING_ID(n_idx)
AND adpt.end_pegging_id = L_Config_Sup.END_PEGGING_ID(n_idx)
AND adpt.base_item_id = L_Config_Sup.BASE_ITEM_ID(n_idx)
AND adpt.inventory_item_id <> adpt.reference_item_id
-- End Bug 3750638
-- Link up pegging with config_item info in msc_atp_detail_peg_temp.
AND peg1.plan_id = adpt.plan_id
AND peg1.sr_instance_id = adpt.sr_instance_id
AND peg1.end_pegging_id = adpt.end_pegging_id
AND peg1.prev_pegging_id = adpt.pegging_id
AND peg1.inventory_item_id = adpt.inventory_item_id
-- End Bug 3750638 Supply filter
-- AND peg1.supply_type = 5
-- Further control if necessary for performance will be added later.
-- AND peg1.transaction_id = adpt.supply_id
-- Get the demands corresponding to pegging
AND d1.plan_id = peg1.plan_id
AND d1.sr_instance_id = peg1.sr_instance_id
AND d1.organization_id = peg1.organization_id
AND d1.demand_id = peg1.demand_id
-- Bug 3750638 The lower level item could also be sourced from muliple orgs.
-- AND d1.inventory_item_id <> adpt.inventory_item_id
-- Get all the items which peg to the supply using the disposition_id.
AND d1.disposition_id = adpt.supply_id
AND d1.using_requirement_quantity > 0
-- Join to msc_system_items to get items data
AND msi.plan_id = d1.plan_id
AND msi.sr_instance_id = d1.sr_instance_id
AND msi.inventory_item_id = d1.inventory_item_id
AND msi.organization_id = d1.organization_id
;
INSERT INTO MSC_ATP_DETAIL_PEG_TEMP (
reference_item_id, base_item_id,
inventory_item_id,
plan_id,
sr_instance_id,
organization_id,
end_item_usage,
bom_item_type, fixed_lt, variable_lt,
sales_order_line_id,
demand_source_type,--cmro
-- CTO_PF_PRJ changes.
end_demand_id,
-- CTO-PF
atf_date,
product_family_id,
sales_order_qty,
demand_id,
demand_date,
demand_quantity,
disposition_id,
demand_class,
demand_type,
original_demand_id,
process_seq_id, supply_id,
supply_date,
supply_quantity,
allocated_quantity, tot_relief_qty,
supply_type,
firm_planned_type,
release_status,
exclude_flag, -- All other cases exclude
order_number,
end_pegging_id, pegging_id, prev_pegging_id,
fcst_organization_id, forecast_qty,
consumed_qty, overconsumption_qty )
SELECT
adpt.reference_item_id, msi.base_item_id,
peg1.inventory_item_id,
peg1.plan_id,
peg1.sr_instance_id,
peg1.organization_id,
peg1.end_item_usage,
msi.bom_item_type, msi.fixed_lead_time, msi.variable_lead_time,
adpt.sales_order_line_id,
adpt.demand_source_type,--cmro
-- CTO_PF_PRJ changes.
adpt.end_demand_id,
-- CTO-PF
msi.aggregate_time_fence_date,
msi.product_family_id,
NULL sales_order_qty, -- will be used to factor sales_order_qty,
d1.demand_id,
-- Bug 3550296 and 3574164. IMPLEMENT_DATE AND DMD_SATISFIED_DATE are changed to
-- IMPLEMENT_SHIP_DATE and PLANNED_SHIP_DATE resp.
DECODE(
d1.RECORD_SOURCE,
2,
NVL(d1.SCHEDULE_SHIP_DATE,d1.USING_ASSEMBLY_DEMAND_DATE),
DECODE(MSC_ATP_PVT.G_HP_DEMAND_BUCKETING_PREF,
2,
(NVL(d1.IMPLEMENT_SHIP_DATE,NVL(d1.FIRM_DATE,NVL(d1.PLANNED_SHIP_DATE,d1.USING_ASSEMBLY_DEMAND_DATE)))),
NVL(d1.SCHEDULE_SHIP_DATE,d1.USING_ASSEMBLY_DEMAND_DATE))), --plan by request Date, Promise Date Scheduled Date
decode(d1.origination_type, 4, d1.daily_demand_rate,
d1.using_requirement_quantity) demand_quantity,
d1.disposition_id,
NVL(d1.demand_class, adpt.demand_class) demand_class,
d1.origination_type,
-- Bug 3362558 use pegging's original demand_id
NVL(d1.original_demand_id, adpt.original_demand_id) original_demand_id,
-- Begin Bug 3319810
-- Use pegging's supply data instead of msc_atp_detail_peg_temp.
adpt.process_seq_id,
--adpt.supply_id,
--adpt.supply_date,
--adpt.supply_quantity,
--adpt.allocated_quantity, NULL tot_relief_qty,
--adpt.supply_type,
peg1.transaction_id supply_id,
-- Bug 3750638 Keep the supply date NULL for configs.
DECODE(NVL(msi.base_item_id, -2353), -2353, peg1.supply_date, NULL) supply_date,
peg1.supply_quantity,
peg1.allocated_quantity, NULL tot_relief_qty,
peg1.supply_type,
-- End Bug 3319810
adpt.firm_planned_type,
adpt.release_status, -- 1 released
0 exclude_flag, -- Include the demands as a default.
decode(d1.origination_type, 1, to_char(d1.disposition_id), d1.order_number) order_number,
peg1.end_pegging_id, peg1.pegging_id, peg1.prev_pegging_id,
NULL fcst_organization_id, NULL forecast_qty,
NULL consumed_qty, NULL overconsumption_qty
FROM
msc_atp_detail_peg_temp adpt,
msc_full_pegging peg1 ,
msc_demands d1,
msc_system_items msi,
msc_process_effectivity proc,
msc_bom_components mbc
WHERE adpt.plan_id = p_plan_id
AND adpt.reference_item_id = c_items_rec.inventory_item_id
AND adpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(adpt.demand_source_type,100,adpt.demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --CMRO
-- Bug 3750638 Apply Config Item Array Filters
AND adpt.sr_instance_id = L_Config_Sup.SR_INSTANCE_ID(n_idx) -- outer join to get all instances
AND adpt.inventory_item_id = L_Config_Sup.INVENTORY_ITEM_ID(n_idx)
AND adpt.supply_id = L_Config_Sup.SUPPLY_ID(n_idx)
AND adpt.end_demand_id = L_Config_Sup.END_DEMAND_ID(n_idx)
AND adpt.pegging_id = L_Config_Sup.PEGGING_ID(n_idx)
AND adpt.end_pegging_id = L_Config_Sup.END_PEGGING_ID(n_idx)
AND adpt.base_item_id = L_Config_Sup.BASE_ITEM_ID(n_idx)
AND adpt.supply_date is not NULL
-- End Bug 3750638
-- Link up pegging with config_item info in msc_atp_detail_peg_temp.
AND peg1.plan_id = adpt.plan_id
AND peg1.sr_instance_id = adpt.sr_instance_id
AND peg1.end_pegging_id = adpt.end_pegging_id
AND peg1.prev_pegging_id = adpt.pegging_id
AND peg1.inventory_item_id <> adpt.reference_item_id
-- End Bug 3750638 Supply filter
-- AND peg1.supply_type = 5
-- Further control if necessary for performance will be added later.
-- AND peg1.transaction_id = adpt.supply_id
-- Get the demands corresponding to pegging
AND d1.plan_id = peg1.plan_id
AND d1.sr_instance_id = peg1.sr_instance_id
AND d1.organization_id = peg1.organization_id
AND d1.demand_id = peg1.demand_id
-- Bug 3750638 The lower level item could also be sourced from muliple orgs.
-- AND d1.inventory_item_id <> adpt.inventory_item_id
-- Get all the items which peg to the supply using the disposition_id.
AND d1.disposition_id = adpt.supply_id
AND d1.using_requirement_quantity > 0
-- Join to msc_system_items to get items data
AND msi.plan_id = d1.plan_id
AND msi.sr_instance_id = d1.sr_instance_id
AND msi.inventory_item_id = d1.inventory_item_id
AND msi.organization_id = d1.organization_id
-- Join to msc_process_effectivity to get the bill sequence
AND proc.plan_id = adpt.plan_id
AND proc.process_sequence_id = adpt.process_seq_id
-- Join to msc_bom_components to exclude any exploded items underneath phantoms
AND mbc.plan_id = msi.plan_id
AND mbc.sr_instance_id = msi.sr_instance_id
AND mbc.organization_id = msi.organization_id
AND mbc.bill_sequence_id = proc.bill_sequence_id
-- manufacture in same org.
AND mbc.inventory_item_id = msi.inventory_item_id
AND mbc.using_assembly_id = adpt.inventory_item_id
AND mbc.organization_id = adpt.organization_id
;
select hsecs
into l_timestamp
from v$timer;
UPDATE MSC_ATP_DETAIL_PEG_TEMP madpt
SET
( process_seq_id, supply_id,
supply_date,
supply_quantity,
allocated_quantity, tot_relief_qty,
supply_type,
firm_planned_type,
release_status,
exclude_flag
) =
( SELECT SUP.process_seq_id, SUP.transaction_id supply_id,
SUP.new_schedule_date supply_date,
SUP.new_order_quantity supply_qty,
peg.allocated_quantity, NULL tot_relief_qty,
SUP.order_type,
SUP.firm_planned_type,
SUP.release_status, -- 1 released
DECODE (SUP.order_type, 5,
-- order type is 5 proceed with further checks
(DECODE(SUP.firm_planned_type, 1, 1, -- 1 firm, others not firm
-- order is not firmed proceed with further checks
-- Bug 3717618 Use "quantity_in_process"
-- instead of incorrect release_status
(DECODE(SIGN (NVL(SUP.implemented_quantity, 0) +
NVL(SUP.quantity_in_process, 0) -
NVL(SUP.firm_quantity,SUP.new_order_quantity)),
0, 1, -- equal then flag as released
1, 1, -- positive then flag as released
0)) -- 0 otherwise not released.
-- End Bug 3717618
)),
1) exclude_flag
FROM --msc_atp_detail_peg_temp madpt,
msc_full_pegging peg,
msc_supplies SUP
WHERE madpt.plan_id = p_plan_id
AND madpt.sr_instance_id = c_items_rec.sr_instance_id
AND madpt.reference_item_id = c_items_rec.inventory_item_id
AND madpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(madpt.demand_source_type,100,madpt.demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --CMRO
AND madpt.inventory_item_id <> madpt.reference_item_id
-- Bug 3750638
-- Date is used to filter out other records.
AND madpt.supply_date IS NULL
AND peg.plan_id = madpt.plan_id
AND peg.sr_instance_id = madpt.sr_instance_id
AND peg.organization_id = madpt.organization_id
AND peg.pegging_id = madpt.pegging_id
AND peg.end_pegging_id = madpt.end_pegging_id
AND peg.inventory_item_id = madpt.inventory_item_id
-- Bug 3750638 also filter on supply_id
AND peg.transaction_id = madpt.supply_id
-- Get the supplies corresponding to pegging
AND SUP.plan_id = peg.plan_id
AND SUP.sr_instance_id = peg.sr_instance_id
AND SUP.organization_id = peg.organization_id
AND SUP.inventory_item_id = peg.inventory_item_id
AND SUP.order_type = 5
AND SUP.transaction_id = peg.transaction_id )
WHERE madpt.plan_id = p_plan_id
-- Bug 3750638 Comment out sr_instance_id to support multi-instance plans.
AND madpt.sr_instance_id = c_items_rec.sr_instance_id
AND madpt.reference_item_id = c_items_rec.inventory_item_id
AND madpt.sales_order_line_id = c_items_rec.sales_order_line_id
AND decode(madpt.demand_source_type,100,madpt.demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --CMRO;
l_insert_stmt VARCHAR2(8000);
SELECT distinct msi.item_name, msi.inventory_item_id, msi.sr_inventory_item_id,
msi.sr_instance_id, msi.base_item_id, d.sales_order_line_id,
decode(d.demand_source_type, 100, -- cmro fix
d.demand_source_type, -1) demand_source_type, -- cmro fix
d.demand_class, d.demand_id -- Bug 3319810 Add the sales_order demand class
FROM msc_system_items msi, msc_demands d
WHERE msi.plan_id = p_plan_id
AND msi.bom_item_type = 4
AND msi.base_item_id is NOT NULL
AND msi.replenish_to_order_flag = 'Y'
AND d.plan_id = msi.plan_id
AND d.sr_instance_id = msi.sr_instance_id
AND d.inventory_item_id = msi.inventory_item_id
AND d.organization_id = msi.organization_id
AND d.using_requirement_quantity > 0
AND d.origination_type in (6, 30)
;
l_insert_temp_table VARCHAR2(30);
SELECT a.oracle_username,
sysdate,
FND_GLOBAL.USER_ID
INTO l_msc_schema,
l_sysdate,
l_user_id
FROM fnd_oracle_userid a,
fnd_product_installations b
WHERE a.oracle_id = b.oracle_id
AND b.application_id = 724;
SELECT NVL(pre_alloc_hash_size, -1),
NVL(pre_alloc_sort_size, -1),
NVL(pre_alloc_parallel_degree, 1)
INTO l_hash_size,
l_sort_size,
l_parallel_degree
FROM msc_atp_parameters
WHERE rownum = 1;
SELECT count(*)
INTO l_count
--bug 2495962: Change refrence from dba_xxx to all_xxx tables
--FROM dba_tab_partitions
FROM all_tab_partitions
WHERE table_name = l_table_name
AND partition_name = l_partition_name
AND table_owner = l_msc_schema;
SELECT 1
INTO l_count
FROM msc_system_items msi, msc_demands d
WHERE msi.plan_id = p_plan_id
AND msi.bom_item_type = 4
AND msi.base_item_id is NOT NULL
AND msi.replenish_to_order_flag = 'Y'
AND d.plan_id = msi.plan_id
AND d.sr_instance_id = msi.sr_instance_id
AND d.inventory_item_id = msi.inventory_item_id
AND d.organization_id = msi.organization_id
AND d.using_requirement_quantity > 0
AND d.origination_type in (6, 30)
AND ROWNUM = 1 ;
DELETE MSC_ATP_PEGGING where plan_id = p_plan_id;
msc_util.msc_log('not a shared plan partition, insert data into temp tables');
SELECT t.tablespace_name, NVL(i.def_tablespace_name, t.tablespace_name)
INTO l_tbspace, l_ind_tbspace
FROM all_tab_partitions t,
all_part_indexes i
WHERE t.table_owner = l_msc_schema
AND t.table_name = 'MSC_ATP_PEGGING'
AND t.partition_name = l_partition_name
AND i.owner (+) = t.table_owner
AND i.table_name (+) = t.table_name
AND rownum = 1;
l_insert_stmt := 'CREATE TABLE ' || l_temp_table
|| ' TABLESPACE ' || l_tbspace
|| ' PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)'
|| ' as select * from MSC_ATP_PEGGING where 1=2 ';
l_insert_stmt := 'CREATE TABLE ' || l_temp_table || '(
plan_id NUMBER NOT NULL,
sr_instance_id NUMBER NOT NULL,
reference_item_id NUMBER NOT NULL,
inventory_item_id NUMBER NOT NULL,
organization_id NUMBER NOT NULL,
sales_order_line_id NUMBER NOT NULL,
bom_item_type NUMBER,
sales_order_qty NUMBER,
transaction_date NUMBER,
demand_id NUMBER,
demand_quantity NUMBER,
disposition_id NUMBER,
demand_class VARCHAR2(34) ,
consumed_qty NUMBER,
overconsumption_qty NUMBER,
supply_id NUMBER,
supply_quantity NUMBER,
allocated_quantity NUMBER,
resource_id NUMBER,
department_id NUMBER,
resource_hours NUMBER,
daily_resource_hours NUMBER,
start_date NUMBER,
end_date NUMBER,
relief_type NUMBER,
relief_quantity NUMBER,
daily_relief_qty NUMBER,
pegging_id NUMBER,
prev_pegging_id NUMBER,
end_pegging_id NUMBER,
created_by NUMBER NOT NULL,
creation_date DATE NOT NULL,
last_updated_by NUMBER NOT NULL,
last_update_date DATE NOT NULL,
customer_id NUMBER,
customer_site_id NUMBER,
DEMAND_SOURCE_TYPE NUMBER, --cmro
-- CTO_PF_PRJ changes.
end_demand_id NUMBER
)
TABLESPACE ' || l_tbspace || '
--NOLOGGING
PCTFREE 0 STORAGE(INITIAL 40K NEXT 5M PCTINCREASE 0)';
STATEMENT => l_insert_stmt,
OBJECT_NAME => l_temp_table);
STATEMENT => l_insert_stmt,
OBJECT_NAME => l_temp_table);
select hsecs
into l_timestamp
from v$timer;
select hsecs
into l_timestamp
from v$timer;
SELECT
reference_item_id, base_item_id,
inventory_item_id,
plan_id,
sr_instance_id,
organization_id,
end_item_usage,
bom_item_type, fixed_lt, variable_lt,
sales_order_line_id,
demand_source_type,--cmro
sales_order_qty,
demand_id,
demand_date,
demand_quantity,
disposition_id,
demand_class,
demand_type,
original_demand_id,
process_seq_id, supply_id,
supply_date,
supply_quantity,
allocated_quantity, tot_relief_qty,
supply_type,
firm_planned_type,
release_status,
exclude_flag, -- All other cases exclude
order_number,
end_pegging_id, pegging_id, prev_pegging_id,
fcst_organization_id, forecast_qty,
consumed_qty, overconsumption_qty,
-- CTO_PF_PRJ changes.
end_demand_id,
--CTO-PF
atf_date,
product_family_id
BULK COLLECT INTO
atp_peg_det.reference_item_id, atp_peg_det.base_item_id,
atp_peg_det.inventory_item_id, atp_peg_det.plan_id, atp_peg_det.sr_instance_id,
atp_peg_det.organization_id, atp_peg_det.end_item_usage, atp_peg_det.bom_item_type,
atp_peg_det.fixed_lt, atp_peg_det.variable_lt,
atp_peg_det.sales_order_line_id,atp_peg_det.demand_source_type,atp_peg_det.sales_order_qty, --cmro
atp_peg_det.demand_id, atp_peg_det.demand_date,
atp_peg_det.demand_quantity, atp_peg_det.disposition_id,
atp_peg_det.demand_class, atp_peg_det.demand_type, atp_peg_det.original_demand_id,
atp_peg_det.process_seq_id, atp_peg_det.supply_id, atp_peg_det.supply_date,
atp_peg_det.supply_quantity, atp_peg_det.allocated_quantity,
atp_peg_det.tot_relief_qty, atp_peg_det.supply_type,
atp_peg_det.firm_planned_type, atp_peg_det.release_status,
atp_peg_det.exclude_flag, atp_peg_det.order_number,
atp_peg_det.end_pegging_id, atp_peg_det.pegging_id, atp_peg_det.prev_pegging_id,
atp_peg_det.fcst_organization_id, atp_peg_det.forecast_qty,
atp_peg_det.consumed_qty, atp_peg_det.overconsumption_qty,
-- CTO_PF_PRJ changes.
atp_peg_det.end_demand_id,
-- CTO-PF
atp_peg_det.atf_date,
atp_peg_det.product_family_id
FROM
msc_atp_detail_peg_temp
WHERE plan_id = p_plan_id
AND sr_instance_id (+) = c_items_rec.sr_instance_id -- outer join to get all instances
AND reference_item_id = c_items_rec.inventory_item_id
AND sales_order_line_id = c_items_rec.sales_order_line_id
AND Decode(demand_source_type,100,demand_source_type,-1)
=decode(c_items_rec.demand_source_type,
100,
c_items_rec.demand_source_type,
-1) --CMRO
ORDER BY
end_pegging_id, prev_pegging_id,
demand_date, supply_date,
pegging_id DESC -- prev_pegging_id, pegging_id DESC ???
;
select hsecs
into l_timestamp
from v$timer;
select hsecs
into l_timestamp
from v$timer;
select hsecs
into l_timestamp
from v$timer;
select hsecs
into l_timestamp
from v$timer;
msc_util.msc_log('ATP pegging data will be inserted into ' || l_temp_table);
msc_util.msc_log('Total Records to be inserted ' || l_row_count);
last_updated_by,
last_update_date )
VALUES (
:l_plan_id,
:l_sr_instance_id,
:l_reference_item_id,
:l_inventory_item_id,
:l_organization_id,
:l_sales_order_line_id,
:l_demand_source_type,--cmro
-- CTO_PF_PRJ changes. use end_demand_id
:l_end_demand_id,
:l_bom_item_type,
:l_ales_order_qty,
:l_transaction_date,
:l_demand_id,
:l_demand_quantity,
:l_disposition_id,
:l_demand_class,
:l_consumed_qty,
:l_overconsumption_qty,
:l_supply_id,
:l_supply_quantity,
:l_allocated_quantity,
:l_resource_id,
:l_department_id,
:l_resource_hours,
:l_daily_resource_hours,
:l_start_date,
:l_end_date,
:l_relief_type,
:l_relief_quantity,
:l_daily_relief_qty,
:l_pegging_id,
:l_prev_pegging_id,
:l_end_pegging_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate )';
last_updated_by,
last_update_date )
VALUES (
:l_plan_id,
:l_sr_instance_id,
:l_reference_item_id,
:l_inventory_item_id,
:l_organization_id,
:l_sales_order_line_id,
:l_demand_source_type,--cmro
-- CTO_PF_PRJ changes. use end_demand_id
:l_end_demand_id,
-- CTO-PF
:l_atf_date,
:l_product_family_id,
:l_bom_item_type,
:l_ales_order_qty,
:l_transaction_date,
:l_demand_id,
:l_demand_quantity,
:l_disposition_id,
:l_demand_class,
:l_consumed_qty,
:l_overconsumption_qty,
:l_supply_id,
:l_supply_quantity,
:l_allocated_quantity,
:l_resource_id,
:l_department_id,
:l_resource_hours,
:l_daily_resource_hours,
:l_start_date,
:l_end_date,
:l_relief_type,
:l_relief_quantity,
:l_daily_relief_qty,
:l_pegging_id,
:l_prev_pegging_id,
:l_end_pegging_id,
:l_user_id,
:l_sysdate,
:l_user_id,
:l_sysdate )';
l_insert_temp_table := l_global_temp_table;
l_ins_sql_stmt := 'INSERT INTO '|| l_insert_temp_table || l_sql_stmt_2;
l_insert_temp_table := l_temp_table;
l_ins_sql_stmt := 'INSERT INTO '|| l_insert_temp_table || l_sql_stmt_1;
END LOOP; -- Insert Loop
l_sql_stmt_1 := 'INSERT INTO ' || l_temp_table || '(
plan_id,
sr_instance_id,
reference_item_id,
inventory_item_id,
organization_id,
sales_order_line_id,
demand_source_type,--cmro
-- CTO_PF_PRJ changes. use end_demand_id
end_demand_id,
bom_item_type,
sales_order_qty,
transaction_date,
demand_id,
demand_quantity,
disposition_id,
demand_class,
consumed_qty,
overconsumption_qty,
supply_id,
supply_quantity,
allocated_quantity,
resource_id,
department_id,
resource_hours,
daily_resource_hours,
start_date,
end_date,
relief_type,
relief_quantity,
daily_relief_qty,
pegging_id,
prev_pegging_id,
end_pegging_id,
created_by,
creation_date,
last_updated_by,
last_update_date )
SELECT
plan_id,
sr_instance_id,
reference_item_id,
inventory_item_id,
organization_id,
sales_order_line_id,
demand_source_type,--cmro
-- CTO_PF_PRJ changes. use end_demand_id
end_demand_id,
bom_item_type,
sales_order_qty,
transaction_date,
demand_id,
demand_quantity,
disposition_id,
demand_class,
consumed_qty,
overconsumption_qty,
supply_id,
supply_quantity,
allocated_quantity,
resource_id,
department_id,
resource_hours,
daily_resource_hours,
start_date,
end_date,
relief_type,
relief_quantity,
daily_relief_qty,
pegging_id,
prev_pegging_id,
end_pegging_id,
created_by,
creation_date,
last_updated_by,
last_update_date
FROM
msc_atp_peg_temp
WHERE plan_id = :p_plan_id
AND end_demand_id = :l_end_demand_id
AND relief_type in (2, 3) ';
SELECT NVL(summary_flag,1), compile_designator
INTO l_summary_flag, l_plan_name
FROM msc_plans
WHERE plan_id = p_plan_id;
DELETE from msc_atp_pegging
where plan_id = p_plan_id;
msc_util.msc_log ('Post_Plan_Pegging: Number of rows deleted '|| SQL%ROWCOUNT);
Update_Summary_Flag ( p_plan_id,
MSC_POST_PRO.G_SF_ATPPEG_RUNNING,
l_return_status);
'Error occured in procedure Update_Summary_Flag');
select logfile_name
into l_log_file
from fnd_concurrent_requests
where request_id = fnd_global.conc_request_id;
SELECT mrp_atp_schedule_temp_s.currval
INTO l_session_id
FROM dual;
SELECT mrp_atp_schedule_temp_s.nextval
INTO l_session_id
FROM dual;
Update_Summary_Flag ( p_plan_id,
MSC_POST_PRO.G_SF_ATPPEG_COMPLETED,
l_return_status);
'Error occured in procedure Update_Summary_Flag');
Update_Summary_Flag ( p_plan_id,
NVL(l_summary_flag, 1),
l_return_status);
'Error occured in procedure Update_Summary_Flag');
SELECT msc_demands_s.nextval ,
map.relief_quantity ,
NVL(map.original_date, map.transaction_date) transaction_date ,
-- End CTO_PF_PRJ_2 Impacts
map.reference_item_id,
map.sr_instance_id,
map.organization_id,
-- CTO_PF_PRJ_2 Impacts
NVL(map.original_item_id, map.inventory_item_id) inventory_item_id,
-- End CTO_PF_PRJ_2 Impacts
map.demand_class,
d.customer_id,
d.customer_site_id,
d.ship_to_site_id,
d.dmd_satisfied_date,
-- Bug 3890723 Introduce a pegging_id array to track pegging
-- For filtering out released/firmed supplies instead of disposition_id array.
map.disposition_id,
map.prev_pegging_id,
-- End Bug 3890723
map.demand_id,
map.sales_order_line_id,
-- CTO_PF_PRJ Impacts
NVL(map.supply_id, -1) supply_id,
map.ORIGINAL_ITEM_ID,
map.ORIGINAL_DATE
-- End CTO_PF_PRJ Impacts
BULK COLLECT
INTO x_demand_id,
l_demand_qty,
l_demand_date,
l_reference_item_id,
l_instance_id, -- Bug 3629191, Return it as out parameter
l_organization_id,
x_inv_item_id,
l_demand_class,
l_customer_id,
l_customer_site_id,
l_ship_to_site_id,
l_dmd_satisfied_date,
-- Bug 3890723 Introduce a pegging_id array to track pegging
-- For filtering out released/firmed supplies instead of disposition_id array.
l_disposition_id, -- Array retained as FYI for demand offsets.
l_pegging_id,
-- End Bug 3890723
l_offset_demand_id,
l_so_line_ids,
-- CTO_PF_PRJ Impacts
l_supply_id,
l_original_item_id,
l_original_demand_date
-- End CTO_PF_PRJ Impacts
FROM
msc_atp_pegging map,
msc_demands d
WHERE map.plan_id = p_plan_id
--AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
AND map.sales_order_line_id in (p_identifier, p_config_line_id)
-- CTO_PF_PRJ_2 Impacts
AND map.relief_type in (decode(map.offset_type,1,5,3), 7) --l_offset_type POD
-- End CTO_PF_PRJ_2 Impacts
-- Bug 3890723 Use only pegging_id as a filter
-- ATP created transactions will not have disposition_id populated.
-- Bug 3717618 Only offset demands pegged to supplies that are relieved
-- using filter on disposition_id
-- AND (map.disposition_id, map.prev_pegging_id) IN
AND (map.prev_pegging_id) IN
(SELECT map2.pegging_id
-- End Bug 3890723
FROM msc_atp_pegging map2
WHERE map2.plan_id = p_plan_id
AND map2.sales_order_line_id in (p_identifier, p_config_line_id)
AND DECODE(map2.demand_source_type,100,map2.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO
AND map2.offset_supply_id IS NOT NULL
AND map2.relief_type = decode(map2.offset_type,1,6,2) --l_sup_offset_type
)
-- End Bug 3717618
AND ABS(map.relief_quantity) > C_ZERO_APPROXIMATOR
-- Bug 3761824 Use Precision figure while creating ofsets.
AND DECODE(d.demand_source_type,100,d.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO
AND d.plan_id (+) = map.plan_id
AND d.organization_id (+) = map.organization_id
AND d.demand_id (+) = map.demand_id
AND d.inventory_item_id (+) = map.inventory_item_id
;
INSERT INTO msc_demands (
DEMAND_ID,
USING_REQUIREMENT_QUANTITY,
USING_ASSEMBLY_DEMAND_DATE,
DEMAND_TYPE,
ORIGINATION_TYPE,
USING_ASSEMBLY_ITEM_ID,
PLAN_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
DEMAND_SOURCE_TYPE,--cmro
SALES_ORDER_LINE_ID,
SR_INSTANCE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
DEMAND_CLASS,
REFRESH_NUMBER,
ORDER_NUMBER,
CUSTOMER_ID,
CUSTOMER_SITE_ID,
SHIP_TO_SITE_ID,
RECORD_SOURCE, -- For plan order pegging
-- 24x7
ATP_SYNCHRONIZATION_FLAG,
DMD_SATISFIED_DATE,
DISPOSITION_ID
-- ,OFFSET_DEMAND_ID
)
VALUES (x_demand_id(i),
l_demand_qty(i),
--bug 3328421: Add at the end of the day
trunc(l_demand_date(i)) + MSC_ATP_PVT.G_END_OF_DAY ,
--l_demand_date(i),
1 , -- discrete demand
60, -- offset demand
l_reference_item_id(i), -- inventory_item_id
p_plan_id,
l_organization_id(i),
x_inv_item_id(i),
p_demand_source_type,--cmro
l_so_line_ids(i),
l_instance_id(i),
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
l_demand_class(i),
p_refresh_number,
p_order_number,
l_customer_id(i),
l_customer_site_id(i),
l_ship_to_site_id(i),
2,
0,
l_dmd_satisfied_date(i),
l_disposition_id(i)
-- ,l_offset_demand_id(i)
);
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of rows inserted '||
SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Add_Offset_Demands: before insert into'||
' msc_alloc_demands');
INSERT INTO MSC_ALLOC_DEMANDS(
PLAN_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DEMAND_CLASS,
DEMAND_DATE,
PARENT_DEMAND_ID,
ALLOCATED_QUANTITY,
ORIGINATION_TYPE,
ORDER_NUMBER,
DEMAND_SOURCE_TYPE,--cmro
SALES_ORDER_LINE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
refresh_number,
-- CTO_PF_PRJ_2 Impacts
ORIGINAL_ITEM_ID,
ORIGINAL_DEMAND_DATE,
ORIGINAL_ORIGINATION_TYPE,
PF_DISPLAY_FLAG
-- END CTO_PF_PRJ_2 Impacts
)
SELECT
map.plan_id,
map.inventory_item_id,
map.organization_id,
map.sr_instance_id,
map.demand_class,
map.transaction_date,
x_demand_id(i),
NVL(map.relief_quantity, 0),
Decode(map.relief_type, 7, 51, 60),
--60,
p_order_number,
p_demand_source_type,--cmro
map.sales_order_line_id,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
p_refresh_number,
-- CTO_PF_PRJ_2 Impacts
l_original_item_id(i),
l_original_demand_date(i),
Decode(map.relief_type, 7, 60, NULL),
--Decode(map.relief_type, 7, 51, 60),
Decode(map.relief_type, 7, 1, NULL)
-- pf_display_flag = 1 when offseting bucketed demand.
-- NULL will be the default value.
-- END CTO_PF_PRJ_2 Impacts
FROM msc_atp_pegging map
WHERE map.sr_instance_id = l_instance_id(i)
AND map.plan_id = p_plan_id
AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO
AND map.sales_order_line_id in (p_identifier, p_config_line_id)
AND map.relief_type in (decode(map.offset_type,1,5,3), 7) -- POD
AND NVL(map.original_item_id, map.inventory_item_id) =
NVL(l_original_item_id(i), x_inv_item_id(i))
AND map.relief_quantity = l_demand_qty(i)
AND NVL(map.original_date, l_sysdate) = NVL(l_original_demand_date(i), l_sysdate)
-- FOR ATP created records only relieve PF, do not offset other demands
-- as they are set to 0 already in Delete_Row.
AND NVL(map.supply_id, 100) = l_supply_id(i)
--AND NVL(map.supply_id, DECODE(map.relief_type, 7, -1, 100)) = l_supply_id(i)
-- Bug 3890723 Use pegging_id array to track pegging
-- For filtering out released/firmed supplies instead of disposition_id array.
-- Bug 3717618 Only offset demands pegged to supplies that are relieved
-- using filter on disposition_id
-- AND map.disposition_id = l_disposition_id(i)
AND map.prev_pegging_id = l_pegging_id(i)
-- End Bug 3890723
AND map.demand_id = l_offset_demand_id(i);
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of Family rows inserted '||
'For Demand id '|| l_offset_demand_id(i) ||
' with offset/relief_type 5 or 7 is ' || SQL%BULK_ROWCOUNT(i));
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of Family and Alloc rows inserted '||
'with offset/relief_type = 7 or ' ||l_offset_type || 'is ' || SQL%ROWCOUNT);
update msc_alloc_supplies
set allocated_quantity = 0
where plan_id = p_plan_id
and sr_instance_id = l_instance_id(1)
and ato_model_line_id = p_identifier
and DECODE(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO;
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of stealing rows updated '||
SQL%ROWCOUNT);
INSERT INTO MSC_ALLOC_DEMANDS(
PLAN_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DEMAND_CLASS,
DEMAND_DATE,
PARENT_DEMAND_ID,
ALLOCATED_QUANTITY,
ORIGINATION_TYPE,
ORDER_NUMBER,
DEMAND_SOURCE_TYPE,--cmro
SALES_ORDER_LINE_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
refresh_number,
-- CTO_PF_PRJ_2 Impacts
ORIGINAL_ITEM_ID,
ORIGINAL_DEMAND_DATE,
ORIGINAL_ORIGINATION_TYPE,
PF_DISPLAY_FLAG
-- END CTO_PF_PRJ_2 Impacts
)
SELECT
map.plan_id,
map.inventory_item_id,
map.organization_id,
map.sr_instance_id,
map.demand_class,
map.transaction_date,
x_demand_id(i),
NVL(map.relief_quantity, 0),
Decode(map.relief_type, 7, 51, 60),
--60,
p_order_number,
p_demand_source_type,--cmro
map.sales_order_line_id,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
p_refresh_number,
-- CTO_PF_PRJ_2 Impacts
l_original_item_id(i),
l_original_demand_date(i),
Decode(map.relief_type, 7, 60, NULL),
--Decode(map.relief_type, 7, 51, 60),
1 -- Always 1 for PF.
-- END CTO_PF_PRJ_2 Impacts
FROM msc_atp_pegging map
WHERE map.sr_instance_id = l_instance_id(i)
AND map.plan_id = p_plan_id
AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO
AND map.sales_order_line_id in (p_identifier, p_config_line_id)
AND map.relief_type = 7 -- POD for family item.
AND NVL(map.original_item_id, map.inventory_item_id) =
NVL(l_original_item_id(i), x_inv_item_id(i))
AND map.relief_quantity = l_demand_qty(i)
AND NVL(map.original_date, l_sysdate) = NVL(l_original_demand_date(i), l_sysdate)
AND NVL(map.supply_id, -1) = l_supply_id(i)
-- Bug 3890723 Use pegging_id array to track pegging
-- For filtering out released/firmed supplies instead of disposition_id array.
-- Bug 3717618 Only offset demands pegged to supplies that are relieved
-- using filter on disposition_id
-- AND map.disposition_id = l_disposition_id(i)
AND map.prev_pegging_id = l_pegging_id(i)
-- End Bug 3890723
AND map.demand_id = l_offset_demand_id(i);
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of Family rows inserted '||
'For Demand id '|| l_offset_demand_id(i) ||
' with offset/relief_type = 7 is ' || SQL%BULK_ROWCOUNT(i));
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of Family rows inserted '||
'with offset/relief_type = 7 is ' || SQL%ROWCOUNT);
'error in insert row: sqlcode = '|| to_char(my_sqlcode));
SELECT msc_supplies_s.nextval,
map.sr_instance_id,
map.organization_id,
map.inventory_item_id,
map.relief_quantity relief_quantity,
map.transaction_date,
-- Bug 3717618 Fetch pegging as well.
map.pegging_id,
-- Bug 3381464 Get original supply id
s.transaction_id orig_supply_id,
s.supplier_id,
s.supplier_site_id,
s.source_supplier_id,
s.source_supplier_site_id,
s.source_sr_instance_id,
s.source_organization_id,
s.process_seq_id,
s.firm_planned_type,
s.demand_class,
s.customer_id,
s.ship_to_site_id,
s.transaction_id,
s.new_ship_date,
s.new_dock_date
BULK COLLECT
INTO x_supply_id,
l_instance_id,
l_organization_id,
x_inv_item_id,
l_supply_qty,
l_supply_date,
-- Bug 3717618 Introduce a pegging_id array to track pegging
-- For filtering out, released/firmed supplies and pegging both are needed.
l_pegging_id,
-- Bug 3381464 Get original supply id
l_orig_supply_id,
l_supplier_id,
l_supplier_site_id,
l_src_supplier_id,
l_src_supplier_site_id,
l_src_instance_id,
l_src_org_id,
l_process_seq_id,
l_firm_planned_type,
l_demand_class,
l_customer_id,
l_ship_to_site_id,
l_offset_supply_id,
l_ship_date,
l_dock_date
FROM
msc_atp_pegging map,
msc_supplies s
WHERE map.plan_id = p_plan_id
--AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO;
(SELECT supply_id, pegging_id
FROM msc_atp_pegging mapeg1
WHERE plan_id = p_plan_id
AND DECODE(mapeg1.demand_source_type,100,mapeg1.demand_source_type,-1)
=decode(p_demand_source_type, 100,
p_demand_source_type, -1) --CMRO
AND sales_order_line_id in (p_identifier, p_config_line_id)
AND relief_type in (1, DECODE(offset_type, 1, 6, 2))
START WITH plan_id = p_plan_id
AND DECODE(mapeg1.demand_source_type,100,mapeg1.demand_source_type,-1)
=decode(p_demand_source_type, 100,
p_demand_source_type, -1) --CMRO
AND sales_order_line_id in (p_identifier, p_config_line_id)
AND relief_type in (1, DECODE(offset_type, 1, 6, 2))
AND supply_id in
(SELECT transaction_id
FROM msc_supplies S
WHERE S.plan_id = mapeg1.plan_id
AND S.sr_instance_id = mapeg1.sr_instance_id
AND S.transaction_id = mapeg1.supply_id
AND S.inventory_item_id = mapeg1.inventory_item_id
AND ((S.firm_planned_type = 1) -- firmed
OR -- released
NVL(S.implemented_quantity, 0) +
NVL(S.quantity_in_process, 0) >=
NVL(S.firm_quantity,S.new_order_quantity)
)
)
CONNECT BY prev_pegging_id = prior pegging_id
AND plan_id = p_plan_id
AND DECODE(mapeg1.demand_source_type,100,mapeg1.demand_source_type,-1)
=decode(p_demand_source_type, 100,
p_demand_source_type, -1)
AND sales_order_line_id in (p_identifier, p_config_line_id)
AND relief_type in (1, DECODE(offset_type, 1, 6, 2))
-- The Connect By clause sub_query traverses the pegging chain
-- and helps in eliminating all supplies pegged to the firmed or released supply
)
-- End Bug 3717618
AND ABS(map.relief_quantity) > C_ZERO_APPROXIMATOR
-- Bug 3761824 Use Precision figure while creating ofsets.
AND s.sr_instance_id = map.sr_instance_id
AND s.plan_id = map.plan_id
AND s.organization_id = map.organization_id
AND s.transaction_id = map.supply_id
AND s.inventory_item_id = map.inventory_item_id
AND s.order_type = 5
;
INSERT into MSC_SUPPLIES (
plan_id,
transaction_id,
organization_id,
sr_instance_id,
inventory_item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
new_schedule_date,
disposition_status_type,
order_type,
new_order_quantity,
order_number,
supplier_id,
supplier_site_id,
source_supplier_id,
source_supplier_site_id,
source_sr_instance_id,
source_organization_id,
process_seq_id,
firm_planned_type,
demand_class,
customer_id,
ship_to_site_id,
record_source,
refresh_number,
new_ship_date,
new_dock_date
-- ,offset_supply_id
)
VALUES (p_plan_id,
x_supply_id(i),
l_organization_id(i),
l_instance_id(i),
x_inv_item_id(i),
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
--bug 3328421: Add at the end of the day
TRUNC(l_supply_date(i)) + MSC_ATP_PVT.G_END_OF_DAY,
--l_supply_date(i),
1, -- 1512366: open status.
60, -- offset sypply_type
l_supply_qty(i),
p_order_number,
l_supplier_id(i),
l_supplier_site_id(i),
l_src_supplier_id(i),
l_src_supplier_site_id(i),
l_src_instance_id(i),
l_src_org_id(i),
l_process_seq_id(i),
l_firm_planned_type(i),
l_demand_class(i),
l_customer_id(i),
l_ship_to_site_id(i),
2, -- ATP created record
p_refresh_number,
l_ship_date(i),
l_dock_date(i)
-- ,l_offset_supply_id
);
msc_sch_wb.atp_debug('Add_Offset_Supplies: Number of rows inserted '||
SQL%ROWCOUNT);
UPDATE msc_atp_pegging map1
SET offset_supply_id = x_supply_id(i)
WHERE map1.plan_id = p_plan_id
AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO;
msc_sch_wb.atp_debug('Add_Offset_Supplies: Number of rows Updated '|| SQL%ROWCOUNT);
' before insert into' || ' msc_alloc_supplies');
INSERT INTO MSC_ALLOC_SUPPLIES(
PLAN_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
SR_INSTANCE_ID,
DEMAND_CLASS,
SUPPLY_DATE,
PARENT_TRANSACTION_ID,
ALLOCATED_QUANTITY,
ORDER_TYPE,
SALES_ORDER_LINE_ID,
refresh_number,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE
)
SELECT
map.plan_id,
map.inventory_item_id,
map.organization_id,
map.sr_instance_id,
map.demand_class,
map.transaction_date,
x_supply_id(i),
NVL(map.relief_quantity, 0),
60,
map.sales_order_line_id,
p_refresh_number,
l_user_id,
l_sysdate,
l_user_id,
l_sysdate
FROM msc_atp_pegging map
WHERE map.sr_instance_id = l_instance_id(i)
AND map.plan_id = p_plan_id
AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO;
msc_sch_wb.atp_debug('Add_Offset_Demands: Number of rows inserted '||
'For Supply id '|| l_offset_supply_id(i) ||
' with offset/relief_type = 6 is ' || SQL%BULK_ROWCOUNT(i));
msc_sch_wb.atp_debug('Add_Offset_Supplies: Number of Alloc rows inserted '||
'with offset/relief_type = ' ||l_offset_type || 'is ' || SQL%ROWCOUNT);
UPDATE msc_atp_pegging map1
SET offset_supply_id = NULL
WHERE map1.plan_id = p_plan_id
AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO;
msc_sch_wb.atp_debug('Add_Offset_Supplies: Number of rows Updated '||
SQL%ROWCOUNT);
'error in insert row: sqlcode = '|| to_char(my_sqlcode));
SELECT
supply_id,
sr_instance_id,
msc_resource_requirements_s.nextval,
organization_id,
inventory_item_id,
basis_type,
operation_seq_num,
parent_id,
resource_seq_num,
resource_id,
department_id,
start_date,
end_date,
relief_quantity,
daily_relief_qty,
load_rate,
assigned_units, -- 0 originally.
std_op_code
BULK COLLECT
INTO
l_supply_id,
l_instance_id,
x_res_transactions,
l_organization_id,
x_inv_item_id,
l_basis_type,
l_op_seq_num,
l_parent_id,
l_resource_seq_num,
l_resource_id,
l_department_id,
l_start_date,
l_end_date,
l_resource_hours,
l_daily_resource_hours,
l_load_rate,
l_assigned_units,
l_std_op_code
FROM
(SELECT DISTINCT
-- Bug 3381464 Obtain the Offset Suuply_ID
map2.offset_supply_id supply_id,
-- REQ.supply_id,
-- This ensures that offset resource requirements are pegged to offset supplies.
-- End Bug 3381464.
map.sr_instance_id,
REQ.transaction_id,
map.organization_id,
map.inventory_item_id,
REQ.basis_type,
REQ.operation_seq_num,
REQ.parent_id,
REQ.resource_seq_num,
map.resource_id,
map.department_id,
NVL(map.start_date, REQ.start_date) start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
map.end_date,
map.relief_quantity,
map.daily_relief_qty,
decode(map.resource_id,-1,map.relief_quantity,to_number(null)) load_rate,
REQ.assigned_units, -- 0 originally.
REQ.std_op_code
FROM msc_atp_pegging map,
msc_resource_requirements REQ,
-- Bug 3381464 -- Join to Pegging to obtain offset supply ids.
msc_atp_pegging map2
WHERE map.plan_id = p_plan_id
--AND map.sr_instance_id = p_instance_id -- removed to support multiple instances in plan.
AND DECODE(map.demand_source_type,100,map.demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1) --CMRO;
INSERT into msc_resource_requirements
(plan_id,
supply_id,
transaction_id,
organization_id,
sr_instance_id,
assembly_item_id,
basis_type,
operation_seq_num,
parent_id,
record_source,
resource_seq_num,
resource_id,
department_id,
refresh_number,
start_date,
end_date,
resource_hours,
daily_resource_hours,
load_rate,
assigned_units,
supply_type, -- 1510686
std_op_code, --resource batching
last_update_date,
last_updated_by,
creation_date,
created_by)
VALUES (p_plan_id,
l_supply_id(i),
x_res_transactions(i),
l_organization_id(i),
l_instance_id(i),
x_inv_item_id(i),
l_basis_type(i),
l_op_seq_num(i),
l_parent_id(i),
2, -- ATP generated record.
l_resource_seq_num(i),
l_resource_id(i),
l_department_id(i),
p_refresh_number,
l_start_date(i),
l_end_date(i),
l_resource_hours(i),
l_daily_resource_hours(i),
l_load_rate(i),
l_assigned_units(i), -- 0 originally.
60, -- for Resources offset supply type is applicable.
l_std_op_code(i),
l_sysdate,
l_user_id,
l_sysdate,
l_user_id ) ;
msc_sch_wb.atp_debug('Add_Offset_Resource_Reqs: Number of rows inserted '||
SQL%ROWCOUNT);
DELETE msc_demands
WHERE
-- Bug 3629191 :All where clause except plan_id and demand_id are commmented
/* sr_instance_id = p_instance_id
(SELECT sr_instance_id
FROM msc_atp_pegging
WHERE plan_id = p_plan_ids(m)
AND sales_order_line_id = p_identifiers(m)
AND DECODE(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type(m),
100,
p_demand_source_type(m),
-1) --CMRO;
msc_sch_wb.atp_debug('For Demand id '|| p_del_demand_ids(m)||': updated '||
SQL%BULK_ROWCOUNT(m)||' records');
msc_sch_wb.atp_debug('Remove_Offset_Demands: before delete from ' ||
' msc_alloc_demands');
DELETE msc_alloc_demands
WHERE
-- Bug 3629191 :All where clause except plan_id and parent_demand_id are commmented
/*sr_instance_id IN
(SELECT sr_instance_id
FROM msc_atp_pegging
WHERE plan_id = p_plan_ids(m)
AND sales_order_line_id = p_identifiers(m)
AND DECODE(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type(m),
100,
p_demand_source_type(m),
-1) --CMRO;
msc_sch_wb.atp_debug('For Demand id '|| p_del_demand_ids(m)||': updated '||
SQL%BULK_ROWCOUNT(m)||' records');
UPDATE msc_atp_pegging map1
SET offset_supply_id = NULL
WHERE map1.plan_id = p_atp_peg_supplies_plan_ids(m)
-- Bug 3629191: where clause on demand_source_type, sales_order_line_id
-- and inventory_item_id are removed
/*AND DECODE(map1.demand_source_type,100,map1.demand_source_type,-1)
=decode(p_demand_source_type(m),
100,
p_demand_source_type(m),
-1) --CMRO;
msc_sch_wb.atp_debug('Remove_Offset_Supplies: Number of rows Updated '||
SQL%ROWCOUNT);
DELETE msc_supplies
WHERE
-- Bug 3629191 :All where clause except and transaction_id are commmented
/* sr_instance_id = p_instance_id
(SELECT sr_instance_id
FROM msc_atp_pegging
WHERE plan_id = p_plan_ids(m)
AND sales_order_line_id = p_identifiers(m)
AND DECODE(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type(m),
100,
p_demand_source_type(m),
-1) --CMRO;
msc_sch_wb.atp_debug('For Supply id '|| p_del_supply_ids(m)||': updated '||
SQL%BULK_ROWCOUNT(m)||' records');
msc_sch_wb.atp_debug('Remove_Offset_Supplies: before delete from ' ||
' msc_alloc_supplies');
DELETE msc_alloc_supplies
WHERE
-- Bug 3629191 :All where clause except and parent_transaction_id are commmented
/* sr_instance_id = p_instance_id
(SELECT sr_instance_id
FROM msc_atp_pegging
WHERE plan_id = p_plan_ids(m)
-- CTO_PF_PRJ_2 Changes Use Relief Type
AND relief_type = l_offset_type
-- End CTO_PF_PRJ_2 Changes
AND sales_order_line_id = p_identifiers(m)
AND DECODE(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type(m),
100,
p_demand_source_type(m),
-1) --CMRO;
msc_sch_wb.atp_debug('For Supply id '|| p_del_supply_ids(m)||': updated '||
SQL%BULK_ROWCOUNT(m)||' records');
DELETE msc_resource_requirements
WHERE
-- Bug 3629191 :All where clause except and transaction_id are commmented
/*sr_instance_id = p_instance_id
(SELECT sr_instance_id
FROM msc_atp_pegging
WHERE plan_id = p_plan_ids(m)
AND sales_order_line_id = p_identifiers(m)
AND DECODE(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type(m),
100,
p_demand_source_type(m),
-1) --CMRO;
msc_sch_wb.atp_debug('For Transaction id '|| p_del_resrc_reqs(m)||': updated '||
SQL%BULK_ROWCOUNT(m)||' records');
INSERT INTO msc_atp_pegging
(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity, relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
original_item_id, original_date,
customer_id, customer_site_id, offset_type)
SELECT mapt.reference_item_id, alocd.inventory_item_id, alocd.plan_id,
alocd.sr_instance_id, alocd.organization_id,
mapt.sales_order_line_id, mapt.demand_source_type,
msi.bom_item_type, alocd.demand_date transaction_date,
alocd.parent_demand_id demand_id, alocd.demand_quantity, mapt.disposition_id,
alocd.demand_class, mapt.supply_id,
mapt.supply_quantity, alocd.allocated_quantity, 7 relief_type,
LEAST(ABS(mapt.relief_quantity), alocd.allocated_quantity) * SIGN(mapt.relief_quantity) *
alocd.allocated_quantity/NVL(mapt.allocated_quantity,alocd.allocated_quantity) relief_quantity,
LEAST(ABS(mapt.daily_relief_qty), alocd.allocated_quantity) * SIGN(mapt.relief_quantity) *
alocd.allocated_quantity/NVL(mapt.allocated_quantity,alocd.allocated_quantity) daily_relief_qty,
mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id, mapt.end_demand_id,
mapt.created_by, mapt.creation_date, mapt.last_updated_by, mapt.last_update_date,
mapt.inventory_item_id, mapt.transaction_date,
alocd.customer_id, alocd.ship_to_site_id, DECODE(MSC_ATP_PVT.G_HIERARCHY_PROFILE, 2, 2,
DECODE(MSC_ATP_PVT.G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 1)
)
) offset_type
FROM msc_atp_peg_temp mapt, msc_alloc_demands alocd,
msc_system_items msi, msc_item_hierarchy_mv alloc
WHERE mapt.reference_item_id = p_reference_item_id
AND mapt.plan_id = p_plan_id
AND mapt.sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
--AND mapt.demand_source_type = p_demand_source_type
AND mapt.end_demand_id = p_end_demand_id
AND mapt.relief_type = DECODE(MSC_ATP_PVT.G_HIERARCHY_PROFILE, 2, 3,
DECODE(MSC_ATP_PVT.G_ALLOCATION_METHOD, 2, 3,
decode(alloc.inventory_item_id, null, 2 , 5)
)
) --l_dmd_offset_typ
AND alocd.plan_id = mapt.plan_id
AND alocd.sr_instance_id = mapt.sr_instance_id
AND alocd.organization_id = mapt.organization_id
AND alocd.original_item_id = mapt.inventory_item_id
AND alocd.parent_demand_id = mapt.demand_id
AND msi.plan_id = alocd.plan_id
AND msi.sr_instance_id = alocd.sr_instance_id
AND msi.organization_id = alocd.organization_id
AND msi.inventory_item_id = alocd.inventory_item_id
AND msi.sr_instance_id = alloc.sr_instance_id(+)
AND msi.organization_id = alloc.organization_id(+)
AND msi.inventory_item_id = alloc.inventory_item_id(+)
;
INSERT INTO msc_atp_pegging
(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity, relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
original_item_id, original_date,
customer_id, customer_site_id, offset_type)
SELECT mapt.reference_item_id, alocd.inventory_item_id, alocd.plan_id,
alocd.sr_instance_id, alocd.organization_id,
mapt.sales_order_line_id, mapt.demand_source_type,
msi.bom_item_type, alocd.demand_date transaction_date,
alocd.parent_demand_id demand_id, alocd.demand_quantity, mapt.disposition_id,
alocd.demand_class, mapt.supply_id,
mapt.supply_quantity, alocd.allocated_quantity, 7 relief_type,
LEAST(ABS(mapt.relief_quantity), alocd.allocated_quantity) * SIGN(mapt.relief_quantity) *
alocd.allocated_quantity/NVL(mapt.allocated_quantity,alocd.allocated_quantity) relief_quantity,
LEAST(ABS(mapt.daily_relief_qty), alocd.allocated_quantity) * SIGN(mapt.relief_quantity) *
alocd.allocated_quantity/NVL(mapt.allocated_quantity,alocd.allocated_quantity) daily_relief_qty,
mapt.pegging_id, mapt.prev_pegging_id, mapt.end_pegging_id, mapt.end_demand_id,
mapt.created_by, mapt.creation_date, mapt.last_updated_by, mapt.last_update_date,
mapt.inventory_item_id, mapt.transaction_date,
alocd.customer_id, alocd.ship_to_site_id, 2
FROM msc_atp_peg_temp mapt, msc_alloc_demands alocd,
msc_system_items msi
WHERE mapt.reference_item_id = p_reference_item_id
AND mapt.plan_id = p_plan_id
AND mapt.sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
--AND mapt.demand_source_type = p_demand_source_type
AND mapt.end_demand_id = p_end_demand_id
AND mapt.relief_type = l_dmd_offset_typ
AND alocd.plan_id = mapt.plan_id
AND alocd.sr_instance_id = mapt.sr_instance_id
AND alocd.organization_id = mapt.organization_id
AND alocd.original_item_id = mapt.inventory_item_id
AND alocd.parent_demand_id = mapt.demand_id
AND msi.plan_id = alocd.plan_id
AND msi.sr_instance_id = alocd.sr_instance_id
AND msi.organization_id = alocd.organization_id
AND msi.inventory_item_id = alocd.inventory_item_id;
msc_sch_wb.atp_debug('Create_PF_Atp_Pegging: Number of rows inserted Stage-1 '||
SQL%ROWCOUNT);
INSERT INTO msc_atp_pegging
(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity,
resource_id, department_id, resource_hours, end_date,
relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id, offset_type)
SELECT reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id, demand_source_type, bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity,
resource_id, department_id, resource_hours, end_date,
relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id, offset_type
FROM msc_atp_peg_temp mapt
WHERE mapt.reference_item_id = p_reference_item_id
AND mapt.plan_id = p_plan_id
AND mapt.sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
--AND mapt.demand_source_type = p_demand_source_type
AND mapt.end_demand_id = p_end_demand_id
-- Process everything except PF member and family demands.
AND mapt.inventory_item_id NOT IN
(SELECT NVL(original_item_id, inventory_item_id)
FROM msc_atp_pegging
WHERE reference_item_id = p_reference_item_id
AND plan_id = p_plan_id
AND sales_order_line_id = NVL(p_config_order_line_id, p_model_order_line_id)
--AND demand_source_type = p_demand_source_type
AND end_demand_id = p_end_demand_id
AND relief_type = 7
);
msc_sch_wb.atp_debug('Create_PF_Atp_Pegging: Number of rows inserted Stage-2 '||
SQL%ROWCOUNT);
l_insert_temp_table VARCHAR2(30);
l_insert_temp_table := 'MSC_ATP_PEG_TEMP';
l_insert_temp_table := 'MSC_ATP_PEGGING';
DELETE from msc_atp_pegging
WHERE plan_id = p_old_plan_id
AND relief_type > 0
AND sales_order_line_id in (NVL(p_config_order_line_id, -1), p_model_order_line_id)
AND decode(demand_source_type,100,demand_source_type,-1)
=decode(p_demand_source_type,
100,
p_demand_source_type,
-1); --CMRO
msc_sch_wb.atp_debug('Create_Atp_Pegging: Number of rows deleted '||
SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Create_Atp_Pegging: l_insert_temp_table = '|| l_insert_temp_table);
l_sql_stmt_1 := 'INSERT INTO ' || l_insert_temp_table ||
'(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id,demand_source_type, bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity,
resource_id, department_id, resource_hours, end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id)
SELECT dest_inv_item_id , NVL(DEST_INV_ITEM_ID, INVENTORY_ITEM_ID),
identifier2 plan_id, identifier1 sr_instance_id,
NVL(RECEIVING_ORGANIZATION_ID, organization_id) ,
NVL(:p_config_order_line_id, :p_model_order_line_id) sales_order_line_id,
:p_demand_source_type,--cmro
--bug 3328421
--NULL bom_item_type, NVL(required_date, supply_demand_date) transaction_date,
NULL bom_item_type, NVL(actual_supply_demand_date, supply_demand_date) transaction_date,
-- identifier3 contains the demand_id
-- supply_demand_quantity contains the demand_quantity
DECODE (pegging_type, :l_peg_type1,
identifier3, NULL) demand_id,
DECODE (pegging_type, :l_peg_type2,
supply_demand_quantity, NULL) supply_demand_quantity,
NULL disposition_id, demand_class,
-- identifier3 contains the transaction_id
-- supply_demand_quantity contains the supply_quantitiy
-- For ATP created pegging the allocated_qty is the same as supply_qty
DECODE (pegging_type,
:l_peg_type3, identifier3,
:l_peg_type4, identifier3,
:l_peg_type5, identifier3,
NULL) supply_id,
DECODE (pegging_type,
:l_peg_type6, supply_demand_quantity,
:l_peg_type7, supply_demand_quantity,
:l_peg_type8, supply_demand_quantity,
NULL) supply_quantity,
DECODE (pegging_type,
:l_peg_type9, supply_demand_quantity,
:l_peg_type10, supply_demand_quantity,
:l_peg_type11, supply_demand_quantity,
NULL) allocated_quantity,
resource_id, department_id,
DECODE (pegging_type,
:l_peg_type12, supply_demand_quantity,
NULL) resource_hours,
--bug 3328421
--NVL(required_date, supply_demand_date) start_date,
NVL(actual_supply_demand_date, supply_demand_date) end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
DECODE (pegging_type,
:l_peg_type13,
decode(pegging_id, end_pegging_id, 1, :l_dmd_offset_typ),
-- pegging_id is same as end_pegging_id then SO
-- otherwise POD
:l_peg_type14, :l_sup_offset_typ, -- PO
:l_peg_type15, :l_sup_offset_typ, -- PO
:l_peg_type16, 4 , -- REQ
:l_peg_type17, :l_sup_offset_typ )
relief_type,
decode(pegging_id, end_pegging_id, 0,
-1 * supply_demand_quantity ) relief_quantity,
NULL daily_relief_qty,
pegging_id, parent_pegging_id, end_pegging_id,
DECODE (pegging_type, :l_peg_type18,
identifier3, NULL) end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id
FROM mrp_atp_details_temp
WHERE pegging_id = :p_identifier
AND identifier1 = :p_instance_id
AND record_type = 3
and session_id = :l_session_id
and model_sd_flag = 1' -- ensure that we only obtain pegging for things in the order.
;
msc_sch_wb.atp_debug('Create_Atp_Pegging: Insert SQL Statement '||
l_sql_stmt_1);
l_sql_stmt_1 := 'INSERT INTO ' || l_insert_temp_table ||
'(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id,demand_source_type, bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity,
resource_id, department_id, resource_hours, end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id, offset_type)
SELECT peg.dest_inv_item_id , NVL(peg.DEST_INV_ITEM_ID, peg.INVENTORY_ITEM_ID),
peg.identifier2 plan_id, peg.identifier1 sr_instance_id,
NVL(peg.RECEIVING_ORGANIZATION_ID, peg.organization_id) ,
NVL(:p_config_order_line_id, :p_model_order_line_id) sales_order_line_id,
:p_demand_source_type,--cmro
--bug 3328421
--NULL bom_item_type, NVL(required_date, supply_demand_date) transaction_date,
NULL bom_item_type, NVL(peg.actual_supply_demand_date, peg.supply_demand_date) transaction_date,
-- identifier3 contains the demand_id
-- supply_demand_quantity contains the demand_quantity
DECODE (peg.pegging_type, :l_peg_type1,
peg.identifier3, NULL) demand_id,
DECODE (peg.pegging_type, :l_peg_type2,
peg.supply_demand_quantity, NULL) supply_demand_quantity,
NULL disposition_id, peg.demand_class,
-- identifier3 contains the transaction_id
-- supply_demand_quantity contains the supply_quantitiy
-- For ATP created pegging the allocated_qty is the same as supply_qty
DECODE (peg.pegging_type,
:l_peg_type3, peg.identifier3,
:l_peg_type4, peg.identifier3,
:l_peg_type5, peg.identifier3,
NULL) supply_id,
DECODE (peg.pegging_type,
:l_peg_type6, peg.supply_demand_quantity,
:l_peg_type7, peg.supply_demand_quantity,
:l_peg_type8, peg.supply_demand_quantity,
NULL) supply_quantity,
DECODE (peg.pegging_type,
:l_peg_type9, peg.supply_demand_quantity,
:l_peg_type10, peg.supply_demand_quantity,
:l_peg_type11, peg.supply_demand_quantity,
NULL) allocated_quantity,
peg.resource_id, peg.department_id,
DECODE (peg.pegging_type,
:l_peg_type12, peg.supply_demand_quantity,
NULL) resource_hours,
--bug 3328421
--NVL(required_date, supply_demand_date) start_date,
NVL(peg.actual_supply_demand_date, peg.supply_demand_date) end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
DECODE (peg.pegging_type,
:l_peg_type13,
decode(peg.pegging_id, peg.end_pegging_id, 1, DECODE(:G_HIERARCHY_PROFILE, 2, 3,
DECODE(:G_ALLOCATION_METHOD, 2, 3,
decode(alloc.inventory_item_id, null, 2 , 5)
)
)),
-- pegging_id is same as end_pegging_id then SO
-- otherwise POD
:l_peg_type14, DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 6)
)
), -- PO
:l_peg_type15, DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 6)
)
), -- PO
:l_peg_type16, 4 , -- REQ
:l_peg_type17, DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 6)
)
) )
relief_type,
decode(peg.pegging_id, peg.end_pegging_id, 0,
-1 * peg.supply_demand_quantity ) relief_quantity,
NULL daily_relief_qty,
peg.pegging_id, peg.parent_pegging_id, peg.end_pegging_id,
DECODE (peg.pegging_type, :l_peg_type18,
peg.identifier3, NULL) end_demand_id,
peg.created_by, peg.creation_date, peg.last_updated_by, peg.last_update_date,
peg.customer_id, peg.customer_site_id,
DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 1)
)
) offset_type
FROM mrp_atp_details_temp peg,
msc_item_hierarchy_mv alloc,
msc_system_items lid
WHERE peg.pegging_id = :p_identifier
AND peg.identifier1 = :p_instance_id
and peg.session_id = :l_session_id
AND peg.record_type = 3
and lid.sr_inventory_item_id = peg.inventory_item_id
and lid.sr_instance_id = peg.identifier1
and lid.organization_id = peg.organization_id
and lid.plan_id = peg.identifier2
and alloc.inventory_item_id(+) = lid.inventory_item_id
AND alloc.sr_instance_id(+) = lid.sr_instance_id
and alloc.organization_id(+) = lid.organization_id
and peg.model_sd_flag = 1' -- ensure that we only obtain pegging for things in the order.
;
msc_sch_wb.atp_debug('Create_Atp_Pegging: Insert SQL Statement 1 '||
l_sql_stmt_1);
msc_sch_wb.atp_debug('Create_Atp_Pegging: Number of rows inserted-1 '||
SQL%ROWCOUNT);
SELECT DEST_INV_ITEM_ID, identifier2,
-- Bug 3334643 Track the plan_id
-- CTO_PF_PRJ Get End Demand Id
DECODE (pegging_type, MSC_ATP_PVT.ORG_DEMAND,
identifier3, NULL) end_demand_id
INTO l_reference_item_id, l_plan_id, l_end_demand_id
-- End CTO_PF_PRJ Get End Demand Id
-- Bug 3334643 Track the plan_id
FROM mrp_atp_details_temp
WHERE pegging_id = p_identifier
AND identifier1 = p_instance_id
AND record_type = 3
AND model_sd_flag = 1
AND session_id = MSC_ATP_PVT.G_SESSION_ID;
l_sql_stmt_1 := 'INSERT INTO ' || l_insert_temp_table ||
'(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id,demand_source_type,bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity,
resource_id, department_id, resource_hours, end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id)
SELECT :l_reference_item_id , NVL(DEST_INV_ITEM_ID, INVENTORY_ITEM_ID),
identifier2 plan_id, identifier1 sr_instance_id,
NVL(RECEIVING_ORGANIZATION_ID, organization_id),
NVL(:p_config_order_line_id, :p_model_order_line_id) sales_order_line_id,
:p_demand_source_type, --cmro
--3328421
--NULL bom_item_type, NVL(required_date, supply_demand_date) transaction_date,
NULL bom_item_type, NVL(actual_supply_demand_date, supply_demand_date) transaction_date,
-- identifier3 contains the demand_id
-- supply_demand_quantity contains the demand_quantity
DECODE (pegging_type, :l_peg_type1,
identifier3, NULL) demand_id,
DECODE (pegging_type, :l_peg_type2,
supply_demand_quantity, NULL) supply_demand_quantity,
NULL disposition_id, demand_class,
-- identifier3 contains the transaction_id
-- supply_demand_quantity contains the supply_quantitiy
-- For ATP created pegging the allocated_qty is the same as supply_qty
DECODE (pegging_type,
:l_peg_type3, identifier3,
:l_peg_type4, identifier3,
:l_peg_type5, identifier3,
:l_peg_type6, identifier3, -- REQ
NULL) supply_id,
DECODE (pegging_type,
:l_peg_type7, supply_demand_quantity,
:l_peg_type8, supply_demand_quantity,
:l_peg_type9, supply_demand_quantity,
NULL) supply_quantity,
DECODE (pegging_type,
:l_peg_type10, supply_demand_quantity,
:l_peg_type11, supply_demand_quantity,
:l_peg_type12, supply_demand_quantity,
NULL) allocated_quantity,
resource_id, department_id,
DECODE (pegging_type,
:l_peg_type13, supply_demand_quantity,
NULL) resource_hours,
--bug 3328421
--NVL(required_date, supply_demand_date) start_date,
NVL(actual_supply_demand_date, supply_demand_date) end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
DECODE (pegging_type,
:l_peg_type14,
decode(pegging_id, end_pegging_id, 1, :l_dmd_offset_typ),
-- pegging_id is same as end_pegging_id then SO
-- otherwise POD
:l_peg_type15, :l_sup_offset_typ, -- PO
:l_peg_type16, :l_sup_offset_typ, -- PO
:l_peg_type17, 4 , -- REQ
:l_peg_type18, :l_sup_offset_typ )
relief_type,
decode(pegging_id, end_pegging_id, 0,
-1 * supply_demand_quantity ) relief_quantity,
NULL daily_relief_qty,
pegging_id, parent_pegging_id, end_pegging_id, :l_end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id
FROM mrp_atp_details_temp
WHERE pegging_id <> :p_identifier
AND record_type in (3, 4)
and session_id = :l_session_id
and model_sd_flag = 1 -- ensure that we only obtain pegging for things in the order.
-- Bug 3334643 Ensure that line is a PDS line.
and identifier2 > 0
START WITH pegging_id = :p_identifier
AND session_id = :l_session_id
AND record_type = 3
CONNECT BY parent_pegging_id = prior pegging_id
AND session_id = prior session_id
AND record_type in (3,4)';
msc_sch_wb.atp_debug('Create_Atp_Pegging: Insert SQL Statement '||
l_sql_stmt_1);
l_sql_stmt_1 := 'INSERT INTO ' || l_insert_temp_table ||
'(reference_item_id, inventory_item_id, plan_id, sr_instance_id,
organization_id, sales_order_line_id,demand_source_type,bom_item_type, --cmro
transaction_date, demand_id, demand_quantity,
disposition_id, demand_class, supply_id, supply_quantity,
allocated_quantity,
resource_id, department_id, resource_hours, end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
relief_type, relief_quantity, daily_relief_qty,
pegging_id, prev_pegging_id, end_pegging_id, end_demand_id,
created_by, creation_date, last_updated_by, last_update_date,
customer_id, customer_site_id, offset_type)
SELECT :l_reference_item_id , NVL(peg.DEST_INV_ITEM_ID, peg.INVENTORY_ITEM_ID),
peg.identifier2 plan_id, peg.identifier1 sr_instance_id,
NVL(peg.RECEIVING_ORGANIZATION_ID, peg.organization_id),
NVL(:p_config_order_line_id, :p_model_order_line_id) sales_order_line_id,
:p_demand_source_type, --cmro
--3328421
--NULL bom_item_type, NVL(required_date, supply_demand_date) transaction_date,
NULL bom_item_type, NVL(peg.actual_supply_demand_date, peg.supply_demand_date) transaction_date,
-- identifier3 contains the demand_id
-- supply_demand_quantity contains the demand_quantity
DECODE (peg.pegging_type, :l_peg_type1,
peg.identifier3, NULL) demand_id,
DECODE (peg.pegging_type, :l_peg_type2,
peg.supply_demand_quantity, NULL) supply_demand_quantity,
NULL disposition_id, peg.demand_class,
-- identifier3 contains the transaction_id
-- supply_demand_quantity contains the supply_quantitiy
-- For ATP created pegging the allocated_qty is the same as supply_qty
DECODE (peg.pegging_type,
:l_peg_type3, peg.identifier3,
:l_peg_type4, peg.identifier3,
:l_peg_type5, peg.identifier3,
:l_peg_type6, peg.identifier3, -- REQ
NULL) supply_id,
DECODE (peg.pegging_type,
:l_peg_type7, peg.supply_demand_quantity,
:l_peg_type8, peg.supply_demand_quantity,
:l_peg_type9, peg.supply_demand_quantity,
NULL) supply_quantity,
DECODE (peg.pegging_type,
:l_peg_type10, peg.supply_demand_quantity,
:l_peg_type11, peg.supply_demand_quantity,
:l_peg_type12, peg.supply_demand_quantity,
NULL) allocated_quantity,
peg.resource_id, peg.department_id,
DECODE (peg.pegging_type,
:l_peg_type13, peg.supply_demand_quantity,
NULL) resource_hours,
--bug 3328421
--NVL(required_date, supply_demand_date) start_date,
NVL(peg.actual_supply_demand_date, peg.supply_demand_date) end_date, -- start_date,
-- Bug 3443056, 3348095 ATP now tracks end date.
DECODE (peg.pegging_type,
:l_peg_type14,
decode(peg.pegging_id, peg.end_pegging_id, 1, DECODE(:G_HIERARCHY_PROFILE, 2, 3,
DECODE(:G_ALLOCATION_METHOD, 2, 3,
decode(alloc.inventory_item_id, null, 2 , 5)
)
)),
-- pegging_id is same as end_pegging_id then SO
-- otherwise POD
:l_peg_type15, DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 6)
)
), -- PO
:l_peg_type16, DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 6)
)
), -- PO
:l_peg_type17, 4 , -- REQ
:l_peg_type18, DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 6)
)
) )
relief_type,
decode(peg.pegging_id, peg.end_pegging_id, 0,
-1 * peg.supply_demand_quantity ) relief_quantity,
NULL daily_relief_qty,
peg.pegging_id, peg.parent_pegging_id, peg.end_pegging_id, :l_end_demand_id,
peg.created_by, peg.creation_date, peg.last_updated_by, peg.last_update_date,
peg.customer_id, peg.customer_site_id,
DECODE(:G_HIERARCHY_PROFILE, 2, 2,
DECODE(:G_ALLOCATION_METHOD, 2, 2,
decode(alloc.inventory_item_id, null, 2 , 1)
)
) offset_type
FROM mrp_atp_details_temp peg,
msc_item_hierarchy_mv alloc,
msc_system_items lid
WHERE peg.pegging_id <> :p_identifier
AND peg.record_type in (3, 4)
and peg.session_id = :l_session_id
and lid.sr_inventory_item_id = peg.inventory_item_id
and lid.sr_instance_id = peg.identifier1
and lid.organization_id = peg.organization_id
and lid.plan_id = peg.identifier2
and alloc.inventory_item_id(+) = lid.inventory_item_id
AND alloc.sr_instance_id(+) = lid.sr_instance_id
and alloc.organization_id(+) = lid.organization_id
and peg.model_sd_flag = 1 -- ensure that we only obtain pegging for things in the order.
-- Bug 3334643 Ensure that line is a PDS line.
and peg.identifier2 > 0
START WITH peg.pegging_id = :p_identifier
AND peg.session_id = :l_session_id
AND peg.record_type = 3
CONNECT BY peg.parent_pegging_id = prior peg.pegging_id
AND peg.session_id = prior peg.session_id
AND peg.record_type in (3,4)';
msc_sch_wb.atp_debug('Create_Atp_Pegging: Insert SQL Statement 2'||
l_sql_stmt_1);
msc_sch_wb.atp_debug('Create_Atp_Pegging: Number of rows inserted '||
SQL%ROWCOUNT);