The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(plans.plan_type, 4, 2,
DECODE(daily_material_constraints, 1, 1,
DECODE(daily_resource_constraints, 1, 1,
DECODE(weekly_material_constraints, 1, 1,
DECODE(weekly_resource_constraints, 1, 1,
DECODE(period_material_constraints, 1, 1,
DECODE(period_resource_constraints, 1, 1, 2)
)
)
)
)
)
),
DECODE(NVL(fnd_profile.value('MSO_BATCHABLE_FLAG'),'N'), 'Y', DECODE(plans.plan_type, 4, 0,2,0,
DECODE(daily_material_constraints, 1, 1,
DECODE(daily_resource_constraints, 1, 1,
DECODE(weekly_material_constraints, 1, 1,
DECODE(weekly_resource_constraints, 1, 1,
DECODE(period_material_constraints, 1, 1,
DECODE(period_resource_constraints, 1, 1, 2)
)
)
)
)
)
), 0)
INTO l_optimized_plan,l_constrain_plan
FROM msc_designators desig,
msc_plans plans
WHERE plans.plan_id = p_plan_id
AND desig.designator = plans.compile_designator
AND desig.sr_instance_id = plans.sr_instance_id
AND desig.organization_id = plans.organization_id;
l_insert_item_exception NUMBER := 0;
insert into msc_exception_details (
exception_detail_id,
exception_type,
quantity,
date1,-- Demand Satisfaction Date
number1, -- Demand ID
number3, -- Demand date quantity
plan_id,
organization_id,
inventory_item_id,
resource_id,
department_id,
sr_instance_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
number4 -- Origination
)
values (
msc_exception_details_s.nextval,
p_exception_rec.exception_type,
p_exception_rec.quantity,
p_exception_rec.demand_satisfy_date, -- This is a misnomer. This actually contains demand date.
p_exception_rec.demand_id,
p_exception_rec.quantity_satisfied,
p_exception_rec.plan_id,
p_exception_rec.organization_id,
p_exception_rec.inventory_item_id,
-1,
-1,
p_exception_rec.sr_instance_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
MSC_ATP_EXCEPTIONS.G_ATP_EXCEPTION_ORIGIN_CODE
);
msc_sch_wb.atp_debug ('AEX: Inserted details record. Now updating item record');
select 1
into l_tmp_var
from msc_item_exceptions
where plan_id = p_exception_rec.plan_id
and organization_id = p_exception_rec.organization_id
and sr_instance_id = p_exception_rec.sr_instance_id
and inventory_item_id = p_exception_rec.inventory_item_id
and exception_type = p_exception_rec.exception_type;
l_insert_item_exception := 1;
l_insert_item_exception := 0;
if (l_insert_item_exception = 1 ) THEN
BEGIN
-- insert into msc_item_exceptions
insert into msc_item_exceptions (
plan_id,
organization_id,
sr_instance_id,
inventory_item_id,
exception_type,
exception_group,
-- exception_count, bug 2795053-reopen (ssurendr): Count removed
last_update_date,
last_updated_by,
creation_date,
created_by
)
values (
p_exception_rec.plan_id,
p_exception_rec.organization_id,
p_exception_rec.sr_instance_id,
p_exception_rec.inventory_item_id,
p_exception_rec.exception_type,
p_exception_rec.exception_group,
-- 1, bug 2795053-reopen (ssurendr): Count removed.
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID
);
msc_sch_wb.atp_debug ('AEX: Inserted item exception record');
msc_sch_wb.atp_debug('Add_Exception: Error while inserting new exception type');