[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_delete_demand_flag PLS_INTEGER;
l_atp_insert_rec MRP_ATP_PVT.AtpRec;
l_delete_atp_rec MSC_ATP_PVT.DELETE_ATP_REC; --3720018
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
l_fst_src_pegging_ids.delete;
l_DEMAND_CLASS_ATP_FLAG.DELETE;
l_REQ_ATP_DATE.DELETE;
l_REQ_DATE_QTY.DELETE;
select assignment_set_id
into lv_assign_set_id -- Use new local variable
from msc_assignment_sets
where sr_instance_id = p_instance_id
and sr_assignment_set_id = p_assign_set_id;
SELECT ltrim(rtrim(M2A_DBLINK)), summary_flag
INTO MSC_ATP_PVT.G_DATABASE_LINK, l_summary_flag
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
msc_sch_wb.atp_debug('Schedule: ' || 'Site is using backlog workbench, update tables and turnoff summary for ODS case');
update msc_apps_instances
set summary_flag = 200;
MSC_ATP_DB_UTILS.call_delete_row (
p_instance_id,
p_atp_table,
p_refresh_number,
l_delete_atp_rec,
l_return_status);
msc_sch_wb.atp_debug('Schedule: ' || 'Error in Call_delete_row');
IF (l_delete_atp_rec.error_code(1) = MSC_ATP_PVT.TRY_ATP_LATER) OR
(l_delete_atp_rec.error_code(1) = MSC_ATP_PVT.SUMM_CONC_PROG_RUNNING) THEN
l_atp_table.error_code(1) := l_delete_atp_rec.error_code(1);
msc_sch_wb.atp_debug('Schedule:l_delete_atp_rec.off_demand_instance_id.count ' || l_delete_atp_rec.off_demand_instance_id.count);
msc_sch_wb.atp_debug('Schedule:l_delete_atp_rec.off_supply_instance_id.count ' || l_delete_atp_rec.off_supply_instance_id.count);
msc_sch_wb.atp_debug('Schedule:l_delete_atp_rec.off_res_instance_id.count ' || l_delete_atp_rec.off_res_instance_id.count);
l_atp_table.error_code := l_delete_atp_rec.error_code; --3720018
l_atp_table.attribute_07 := l_delete_atp_rec.attribute_07; --3720018
l_old_plan_id := l_delete_atp_rec.old_plan_id; --3720018
// select assignment_set_id
// into l_assign_set_id
// from msc_assignment_sets
// where sr_instance_id = p_instance_id
// and sr_assignment_set_id = p_assign_set_id;
SELECT so_tbl_status,
NVL(summary_flag, 1),
apps_ver
INTO l_so_tbl_status,
l_summary_flag,
MSC_ATP_PVT.G_APPS_VER -- 2300767
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
DELETE MSC_SHIP_SET_TEMP;
INSERT INTO MSC_SHIP_SET_TEMP(inventory_item_id,
line_id)
VALUES
(p_atp_table.inventory_item_id(l_count),
p_atp_table.identifier(l_count));
msc_sch_wb.atp_debug('Rows inserted in MSC_SHIP_SET_TEMP := ' || SQL%ROWCOUNT);
select count(*) into l_org_collected
from msc_trading_partners
where sr_tp_id = l_atp_table.internal_org_id(i)
and sr_instance_id = p_instance_id
and partner_type = 3;
SELECT TP_ID
INTO MSC_ATP_PVT.G_PARTNER_ID
FROM msc_tp_id_lid tp
WHERE tp.SR_TP_ID = l_atp_table.customer_id(i)
AND tp.SR_INSTANCE_ID = p_instance_id
AND tp.PARTNER_TYPE = 2;
SELECT TP_SITE_ID
INTO MSC_ATP_PVT.G_PARTNER_SITE_ID
FROM msc_tp_site_id_lid tpsite
WHERE tpsite.SR_TP_SITE_ID = l_atp_table.customer_site_id(i)
AND tpsite.SR_INSTANCE_ID = p_instance_id
AND tpsite.PARTNER_TYPE = 2;
SELECT so_tbl_status,
NVL(summary_flag, 1)
INTO l_so_tbl_status,
l_summary_flag
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
SELECT so_tbl_status,
NVL(summary_flag, 1)
INTO l_so_tbl_status,
l_summary_flag
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
G_FW_CTP_PEGGING_ID.DELETE;
G_FW_STEAL_PEGGING_ID.DELETE;
l_ctp_parent_peg_id.DELETE;
l_steal_atp_peg_id.DELETE;
G_FW_CTP_PEGGING_ID.DELETE; --as this has to point to the top level for each iteration
G_FW_STEAL_PEGGING_ID.DELETE;
l_atp_rec.insert_flag := l_atp_table.Insert_Flag(i);
MSC_ATP_DB_UTILS.Delete_Pegging(l_atp_peg_id);
MSC_ATP_DB_UTILS.Delete_SD_Rec(l_ctp_parent_peg_id,l_return_status);
msc_sch_wb.atp_debug('Schedule: ' || 'Error occured in procedure Delete_SD_Rec');
MSC_ATP_DB_UTILS.Delete_SD_Rec(l_steal_atp_peg_id,l_return_status);
msc_sch_wb.atp_debug('Schedule: ' || 'Error occured in procedure Delete_SD_Rec');
MSC_ATP_DB_UTILS.Delete_Pegging(G_FW_PEGGING_ID);
update mrp_atp_details_temp
set constrained_path = 1
where record_type = 3
and session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id in
(select distinct madt.pegging_id
from mrp_atp_details_temp madt
where record_type = 3
and session_id = MSC_ATP_PVT.G_SESSION_ID
start with madt.pegging_id in (
select pegging_id
from mrp_atp_details_temp madt1
where session_id = MSC_ATP_PVT.G_SESSION_ID
and record_type = 3
and end_pegging_id = MSC_ATP_PVT.G_DEMAND_PEGGING_ID
and constraint_type is not null)
connect by madt.pegging_id = PRIOR parent_pegging_id);
UPDATE mrp_atp_details_temp
set constrained_path = l_constraint_path_flag
where record_type = l_peg_record_type
and session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id in
(select pegging_id
from mrp_atp_details_temp
where record_type = l_peg_record_type
and session_id = MSC_ATP_PVT.G_SESSION_ID
start with session_id = MSC_ATP_PVT.G_SESSION_ID
and record_type = l_peg_record_type
and end_pegging_id = MSC_ATP_PVT.G_DEMAND_PEGGING_ID
and constraint_type is not null
connect by pegging_id = PRIOR parent_pegging_id
and record_type = l_peg_record_type
and session_id = MSC_ATP_PVT.G_SESSION_ID);
l_atp_insert_rec := l_atp_rec;
l_atp_insert_rec.inventory_item_id := l_pf_dest_id;
l_atp_insert_rec.request_item_id := l_mem_dest_id;
l_atp_insert_rec.origination_type := 30;
MSC_ATP_DB_UTILS.Update_SD_Date(l_atp_rec.demand_source_line ,
p_instance_id,
l_sd_date,
l_plan_id,
l_sd_qty,
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG(i),
MSC_ATP_PVT.G_REQ_ATP_DATE(i),
MSC_ATP_PVT.G_REQ_DATE_QTY(i), -- Bug 1501787
l_dmd_satisfied_date, -- bug 2795053-reopen (ssurendr)
l_atp_rec.used_available_quantity, -- For time_phased_atp bug3409973
l_atp_rec.atf_date, -- For time_phased_atp
l_atp_rec.atf_date_quantity, -- For time_phased_atp
l_atp_rec.arrival_date, -- For ship_rec_cal
l_order_date_type, -- For ship_rec_cal
l_atp_rec.latest_acceptable_date, -- For ship_rec_cal
l_atp_rec.ship_set_name, -- For Plan by Request Date
l_atp_rec.arrival_set_name, -- For Plan by Request Date
l_atp_rec.override_flag, -- For Plan by Request Date
l_original_req_arrival_date, -- For Plan by Request Date
l_bkwd_pass_atf_date_qty, -- For time_phased_atp bug3397904
l_atp_insert_rec -- For bug 3226083
);
MSC_ATP_PROC.Update_Set_SD_Dates(l_atp_table,l_req_arr_date);
SELECT identifier3
INTO l_ship_arrival_date_rec.demand_id
FROM mrp_atp_details_temp
WHERE pegging_id = MSC_ATP_PVT.G_DEMAND_PEGGING_ID
AND session_id = MSC_ATP_PVT.G_SESSION_ID
AND record_type = 3;
l_atp_insert_rec := l_atp_rec;
l_atp_insert_rec.inventory_item_id := l_pf_dest_id;
l_atp_insert_rec.request_item_id := l_mem_dest_id;
l_atp_insert_rec.origination_type := 30;
MSC_ATP_DB_UTILS.Update_SD_Date(l_atp_rec.demand_source_line ,
p_instance_id,
l_atp_rec.ship_date,
--s_cto_rearch do not honor atp_lead time
/*
MSC_CALENDAR.DATE_OFFSET(
l_atp_rec.organization_id,
p_instance_id,
1,
l_atp_rec.ship_date,
-NVL(l_atp_rec.atp_lead_time, 0)),
*/
l_plan_id,
l_sd_qty,
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG(i),
MSC_ATP_PVT.G_REQ_ATP_DATE(i),
MSC_ATP_PVT.G_REQ_DATE_QTY(i), -- Bug 1501787
l_dmd_satisfied_date, -- bug 2795053-reopen (ssurendr)
l_atp_rec.used_available_quantity, -- For time_phased_atp bug3409973
l_atp_rec.atf_date, -- For time_phased_atp
l_atp_rec.atf_date_quantity, -- For time_phased_atp
l_atp_rec.arrival_date, -- For ship_rec_cal
l_order_date_type, -- For ship_rec_cal
l_atp_rec.latest_acceptable_date,
l_atp_rec.ship_set_name,
l_atp_rec.arrival_set_name,
l_atp_rec.override_flag,
l_original_req_arrival_date,
l_bkwd_pass_atf_date_qty, -- For time_phased_atp bug3397904
l_atp_insert_rec -- For bug 3226083
); -- For ship_rec_cal
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
MSC_ATP_DB_UTILS.Update_SD_Date(p_atp_table.Identifier(m),
p_instance_id, l_group_date, l_temp_plan_id,null,
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG(i),
MSC_ATP_PVT.G_REQ_ATP_DATE(i),
MSC_ATP_PVT.G_REQ_DATE_QTY(i), -- Bug 1501787
l_group_date, -- bug 2795053-reopen (ssurendr)
null, -- For time_phased_atp
l_atp_table.atf_date(m), -- For time_phased_atp
null, -- For time_phased_atp
l_group_arrival_date, -- For ship_rec_cal
l_order_date_type, -- For ship_rec_cal
l_atp_table.latest_acceptable_date(m), -- For ship_rec_cal
l_atp_table.ship_set_name(m), --Plan by request date
l_atp_table.arrival_set_name(m), --Plan by request date
l_atp_table.override_flag(m), --Plan by request date
l_original_req_arrival_date, --Plan by request date
null --l_bkwd_pass_atf_date_qty bug3397904
);
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
MSC_ATP_PROC.Update_Set_SD_Dates(x_atp_table,l_req_arr_date); --Plan by request date
l_delete_demand_flag := 0;
l_delete_demand_flag := 1;
msc_sch_wb.atp_debug('Schedule: ' || 'rajjain - Single call to Undo_Delete_Row');
MSC_ATP_DB_UTILS.Undo_Delete_Row(l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
--subst
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids, -- For summary enhancement
l_delete_atp_rec.del_copy_demand_plan_ids, -- For summary enhancement
l_time_phased_set, -- For time_phased_atp
-- CTO ODR and Simplified Pegging
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands ,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type, --cmro
-- End CTO ODR and Simplified Pegging
l_delete_atp_rec.atp_peg_demands_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_supplies_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_res_reqs_plan_ids, --Bug 3629191
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
IF l_delete_atp_rec.del_copy_demand_ids IS NOT NULL and l_delete_atp_rec.del_copy_demand_ids.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_COPY_DEMAND_ID.count;
MSC_ATP_PVT.G_COPY_DEMAND_ID.extend(l_delete_atp_rec.del_copy_demand_ids.count);
MSC_ATP_PVT.G_COPY_DEMAND_PLAN_ID.extend(l_delete_atp_rec.del_copy_demand_ids.count);
FOR i in 1..l_delete_atp_rec.del_copy_demand_ids.count LOOP
MSC_ATP_PVT.G_COPY_DEMAND_ID(l_count + i) := l_delete_atp_rec.del_copy_demand_ids(i);
MSC_ATP_PVT.G_COPY_DEMAND_PLAN_ID(l_count + i) := l_delete_atp_rec.del_copy_demand_plan_ids(m);
IF l_delete_atp_rec.del_demand_ids IS NOT NULL and l_delete_atp_rec.del_demand_ids.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_demand_ids.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_demand_ids.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_demand_ids.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_demand_ids ' || l_delete_atp_rec.del_demand_ids.count);
FOR i in 1..l_delete_atp_rec.del_demand_ids.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := p_instance_id;
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_demand_ids(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.del_plan_ids(i);
IF l_delete_atp_rec.del_atp_peg_demands IS NOT NULL and l_delete_atp_rec.del_atp_peg_demands.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_atp_peg_demands.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_atp_peg_demands.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_atp_peg_demands.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_atp_peg_demands.count ' || l_delete_atp_rec.del_atp_peg_demands.count);
FOR i in 1..l_delete_atp_rec.del_atp_peg_demands.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := l_delete_atp_rec.off_demand_instance_id(i);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_atp_peg_demands(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.atp_peg_demands_plan_ids(i);
IF l_delete_atp_rec.del_atp_peg_supplies IS NOT NULL and l_delete_atp_rec.del_atp_peg_supplies.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_atp_peg_supplies.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_atp_peg_supplies.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_atp_peg_supplies.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_atp_peg_supplies.count ' || l_delete_atp_rec.del_atp_peg_supplies.count );
FOR i in 1..l_delete_atp_rec.del_atp_peg_supplies.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := l_delete_atp_rec.off_supply_instance_id(i);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_atp_peg_supplies(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.atp_peg_supplies_plan_ids(i);
IF l_delete_atp_rec.del_atp_peg_res_reqs IS NOT NULL and l_delete_atp_rec.del_atp_peg_res_reqs.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_atp_peg_res_reqs.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_atp_peg_res_reqs.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_atp_peg_res_reqs.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_atp_peg_res_reqs.count ' || l_delete_atp_rec.del_atp_peg_res_reqs.count );
FOR i in 1..l_delete_atp_rec.del_atp_peg_res_reqs.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := l_delete_atp_rec.off_res_instance_id(i);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_atp_peg_res_reqs(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.atp_peg_res_reqs_plan_ids(i);
/*-- we now insert new picture for each session. Hence we do not need to update old picture
IF l_inv_ctp = 4 and MSC_ATP_CTO.G_MODEL_IS_PRESENT_IN_SET =1
AND NVL(MSC_ATP_PVT.G_SYNC_ATP_CHECK, 'N') = 'N' THEN
IF (x_atp_table.action(1) = ATPQUERY or
((x_atp_table.Action(1) = DEMANDMODIFY OR
x_atp_table.Action(1) = DEMANDADD OR
x_atp_table.Action(1) = DMDRSVADD)
AND NVL(x_atp_table.error_code(1), -1) not in (0, 61))) THEN
--failure case
MSC_ATP_CTO.Maintain_OS_Sourcing(p_instance_id,
x_atp_table,
MSC_ATP_CTO.FAIL);
IF l_delete_atp_rec.del_demand_ids IS NOT NULL and l_delete_atp_rec.del_demand_ids.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_demand_ids.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_demand_ids.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_demand_ids.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_demand_ids ' || l_delete_atp_rec.del_demand_ids.count);
FOR i in 1..l_delete_atp_rec.del_demand_ids.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := p_instance_id;
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_demand_ids(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.del_plan_ids(i);
IF l_delete_atp_rec.del_atp_peg_demands IS NOT NULL and l_delete_atp_rec.del_atp_peg_demands.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_atp_peg_demands.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_atp_peg_demands.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_atp_peg_demands.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_atp_peg_demands.count ' || l_delete_atp_rec.del_atp_peg_demands.count);
FOR i in 1..l_delete_atp_rec.del_atp_peg_demands.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := l_delete_atp_rec.off_demand_instance_id(i);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_atp_peg_demands(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.atp_peg_demands_plan_ids(i);
IF l_delete_atp_rec.del_atp_peg_supplies IS NOT NULL and l_delete_atp_rec.del_atp_peg_supplies.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_atp_peg_supplies.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_atp_peg_supplies.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_atp_peg_supplies.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_atp_peg_supplies.count ' || l_delete_atp_rec.del_atp_peg_supplies.count );
FOR i in 1..l_delete_atp_rec.del_atp_peg_supplies.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := l_delete_atp_rec.off_supply_instance_id(i);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_atp_peg_supplies(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.atp_peg_supplies_plan_ids(i);
IF l_delete_atp_rec.del_atp_peg_res_reqs IS NOT NULL and l_delete_atp_rec.del_atp_peg_res_reqs.COUNT > 0 THEN
l_count := MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.count;
MSC_ATP_PVT.G_OFF_INSTANCE_ID.extend(l_delete_atp_rec.del_atp_peg_res_reqs.count);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID.EXTEND(l_delete_atp_rec.del_atp_peg_res_reqs.count);
MSC_ATP_PVT.G_OFF_PLAN_ID.EXTEND(l_delete_atp_rec.del_atp_peg_res_reqs.count);
msc_sch_wb.atp_debug('Schedule: l_delete_atp_rec.del_atp_peg_res_reqs.count ' || l_delete_atp_rec.del_atp_peg_res_reqs.count );
FOR i in 1..l_delete_atp_rec.del_atp_peg_res_reqs.count LOOP
MSC_ATP_PVT.G_OFF_INSTANCE_ID(l_count + i) := l_delete_atp_rec.off_res_instance_id(i);
MSC_ATP_PVT.G_OFF_DEM_TRANS_ID(l_count + i) := l_delete_atp_rec.del_atp_peg_res_reqs(i);
MSC_ATP_PVT.G_OFF_PLAN_ID(l_count + i) := l_delete_atp_rec.atp_peg_res_reqs_plan_ids(i);
DELETE from msc_atp_pegging
WHERE plan_id = l_old_plan_id(i)
AND sales_order_line_id in (
NVL( x_atp_table.Config_item_line_id(i), -1),
x_atp_table.ATO_Model_Line_Id(i) );
msc_sch_wb.atp_debug('Schedule: Number of rows deleted '||
' from Atp Pegging ' || SQL%ROWCOUNT);
MSC_ATP_PVT.G_PEGGING_FOR_SET.DELETE;
MSC_ATP_PVT.G_DEMAND_CLASS_ATP_FLAG.DELETE;
MSC_ATP_PVT.G_REQ_ATP_DATE.DELETE;
MSC_ATP_PVT.G_REQ_DATE_QTY.DELETE;
msc_sch_wb.atp_debug('Schedule: ' || 'rajjain - Single call to Undo_Delete_Row');
MSC_ATP_DB_UTILS.Undo_Delete_Row(l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
--subst
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids, -- For summary enhancement
l_delete_atp_rec.del_copy_demand_plan_ids, -- For summary enhancement
l_time_phased_set, -- For time_phased_atp
-- CTO ODR and Simplified Pegging
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands ,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type, --cmro
-- End CTO ODR and Simplified Pegging
l_delete_atp_rec.atp_peg_demands_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_supplies_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_res_reqs_plan_ids, --Bug 3629191
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
SELECT pegging_id INTO l_tmp_pegging_id FROM mrp_atp_details_temp
WHERE session_id = MSC_ATP_PVT.G_SESSION_ID
AND order_line_id = x_atp_table.identifier(i)
AND parent_pegging_id is NULL
AND record_type in (3,4);
msc_sch_wb.atp_debug('Schedule: ' || 'single call to Undo_Delete_Row');
MSC_ATP_DB_UTILS.Undo_Delete_Row(l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
--subst
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids, -- For summary enhancement
l_delete_atp_rec.del_copy_demand_plan_ids, -- For summary enhancement
l_time_phased_set, -- For time_phased_atp
-- CTO ODR and Simplified Pegging
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands ,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type, --cmro
-- End CTO ODR and Simplified Pegging
l_delete_atp_rec.atp_peg_demands_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_supplies_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_res_reqs_plan_ids, --Bug 3629191
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
SELECT pegging_id INTO l_tmp_pegging_id FROM mrp_atp_details_temp
WHERE session_id = MSC_ATP_PVT.G_SESSION_ID
AND order_line_id = x_atp_table.identifier(i)
AND parent_pegging_id is NULL
AND record_type in (3,4);
msc_sch_wb.atp_debug('Schedule: ' || 'Select EXCEPTION KR :invalid obj');
msc_sch_wb.atp_debug('Schedule: ' || 'rajjain - Single call to Undo_Delete_Row');
MSC_ATP_DB_UTILS.Undo_Delete_Row(l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
--subst
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids, -- For summary enhancement
l_delete_atp_rec.del_copy_demand_plan_ids, -- For summary enhancement
l_time_phased_set, -- For time_phased_atp
-- CTO ODR and Simplified Pegging
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands ,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type, --cmro
-- End CTO ODR and Simplified Pegging
l_delete_atp_rec.atp_peg_demands_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_supplies_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_res_reqs_plan_ids, --Bug 3629191
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
SELECT pegging_id INTO l_tmp_pegging_id FROM mrp_atp_details_temp
WHERE session_id = MSC_ATP_PVT.G_SESSION_ID
AND order_line_id = x_atp_table.identifier(i)
AND parent_pegging_id is NULL
AND record_type in (3,4);
msc_sch_wb.atp_debug('Schedule: ' || 'rajjain - Single call to Undo_Delete_Row');
MSC_ATP_DB_UTILS.Undo_Delete_Row(l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
--subst
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids, -- For summary enhancement
l_delete_atp_rec.del_copy_demand_plan_ids, -- For summary enhancement
l_time_phased_set, -- For time_phased_atp
-- CTO ODR and Simplified Pegging
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands ,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type, --cmro
-- End CTO ODR and Simplified Pegging
l_delete_atp_rec.atp_peg_demands_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_supplies_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_res_reqs_plan_ids, --Bug 3629191
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
SELECT pegging_id INTO l_tmp_pegging_id FROM mrp_atp_details_temp
WHERE session_id = MSC_ATP_PVT.G_SESSION_ID
AND order_line_id = x_atp_table.identifier(i)
AND parent_pegging_id is NULL
AND record_type in (3,4);
msc_sch_wb.atp_debug('Schedule: ' || 'Select EXCEPTION KR: others ');
msc_sch_wb.atp_debug('Schedule: ' || 'rajjain - Single call to Undo_Delete_Row');
MSC_ATP_DB_UTILS.Undo_Delete_Row(l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
--subst
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids, -- For summary enhancement
l_delete_atp_rec.del_copy_demand_plan_ids, -- For summary enhancement
l_time_phased_set, -- For time_phased_atp
-- CTO ODR and Simplified Pegging
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands ,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type, --cmro
-- End CTO ODR and Simplified Pegging
l_delete_atp_rec.atp_peg_demands_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_supplies_plan_ids, --Bug 3629191
l_delete_atp_rec.atp_peg_res_reqs_plan_ids, --Bug 3629191
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
l_atp_insert_rec MRP_ATP_PVT.AtpRec;
l_update_PO_mode NUMBER := MSC_ATP_PVT.UNDO;
msc_sch_wb.atp_debug('ATP_Check: ' || 'Insert_Flag:' || to_char(p_atp_record.Insert_Flag) );
Added by avjain to capture original request date to insert in msc-demands
original_request_date corresponds to request date for 24*7 ATP which gives
the request date
*/
l_original_req_ship_date := nvl(p_atp_record.original_request_date,l_requested_ship_date);
SELECT NVL(r.demand_class_atp_flag, 0)
INTO l_demand_class_flag
FROM msc_system_items I,
msc_trading_partners P,
msc_atp_rules R
WHERE I.sr_instance_id = p_atp_record.instance_id
AND I.organization_id = p_atp_record.organization_id
AND I.sr_inventory_item_id = p_atp_record.inventory_item_id
AND I.plan_id = -1
AND I.organization_id = P.sr_tp_id
AND P.sr_instance_id = I.sr_instance_id
AND P.partner_type = 3 --- organization
AND I.sr_instance_id = R.sr_instance_id
AND R.rule_id = NVL(I.atp_rule_id, P.default_atp_rule_id);
l_atp_insert_rec.instance_id := p_atp_record.instance_id;
l_atp_insert_rec.inventory_item_id := C_ITEM_INFO_REC.dest_inv_item_id;
/*l_atp_insert_rec.inventory_item_id := MSC_ATP_FUNC.get_inv_item_id
(p_atp_record.instance_id,
--- 2152184
l_request_item_id,
--p_atp_record.request_item_id,
null,
p_atp_record.organization_id);
l_atp_insert_rec.organization_id := p_atp_record.organization_id;
l_atp_insert_rec.identifier := p_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=nvl(p_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(p_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_line := p_atp_record.demand_source_line;
l_atp_insert_rec.demand_source_delivery :=
p_atp_record.demand_source_delivery;
l_atp_insert_rec.quantity_ordered :=
p_atp_record.quantity_ordered;
l_atp_insert_rec.requested_ship_date := l_requested_ship_date;
l_atp_insert_rec.demand_class := p_atp_record.demand_class;
l_atp_insert_rec.refresh_number := p_refresh_number; -- summary enhancement p_atp_record.refresh_number;
l_atp_insert_rec.origination_type := 30;
l_atp_insert_rec.order_number := p_atp_record.order_number;
l_atp_insert_rec.ship_set_name := p_atp_record.ship_set_name;
l_atp_insert_rec.arrival_set_name := p_atp_record.arrival_set_name;
l_atp_insert_rec.original_request_ship_date :=l_original_req_ship_date;
l_atp_insert_rec.old_demand_id := p_atp_record.old_demand_id;
l_atp_insert_rec.old_demand_id := null;
l_atp_insert_rec.session_id:= MSC_ATP_PVT.G_SESSION_ID;
l_atp_insert_rec.delivery_lead_time := l_delivery_lead_time;
MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
p_plan_id,
l_demand_class_flag,
l_demand_id);
l_atp_insert_rec.instance_id := p_atp_record.instance_id;
l_atp_insert_rec.inventory_item_id := PF_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.request_item_id := C_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.atf_date := p_atp_record.atf_date;
l_atp_insert_rec.atf_date_quantity := 0; -- check this??
l_atp_insert_rec.requested_date_quantity := l_requested_date_quantity;
l_atp_insert_rec.organization_id := p_atp_record.organization_id;
l_atp_insert_rec.identifier := p_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=nvl(p_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(p_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_delivery :=
p_atp_record.demand_source_delivery;
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.requested_ship_date := l_requested_ship_date;
l_atp_insert_rec.demand_class := p_atp_record.demand_class;
l_atp_insert_rec.refresh_number := p_refresh_number; -- summary enhancement p_atp_record.refresh_number;
l_atp_insert_rec.order_number := p_atp_record.order_number;
l_atp_insert_rec.origination_type := 47;
msc_sch_wb.atp_debug('ATP_Check: ' || 'DRP origination_type ='|| l_atp_insert_rec.origination_type);
l_atp_insert_rec.origination_type := 1;
l_atp_insert_rec.old_demand_id := null;
l_atp_insert_rec.Top_Model_line_id := p_atp_record.Top_Model_line_id;
l_atp_insert_rec.ATO_Parent_Model_Line_Id := p_atp_record.ATO_Parent_Model_Line_Id;
l_atp_insert_rec.Parent_line_id := p_atp_record.Parent_line_id;
l_atp_insert_rec.ATO_Model_Line_Id := p_atp_record.ATO_Model_Line_Id;
l_atp_insert_rec.wip_supply_type := p_atp_record.wip_supply_type;
l_atp_insert_rec.mand_comp_flag := p_atp_record.mand_comp_flag;
l_atp_insert_rec.wip_supply_type := p_atp_record.mand_comp_flag;
l_atp_insert_rec.ship_set_name := p_atp_record.ship_set_name;
l_atp_insert_rec.arrival_set_name := p_atp_record.arrival_set_name;
l_atp_insert_rec.original_request_ship_date :=l_original_req_ship_date;
l_atp_insert_rec.ship_method := p_atp_record.ship_method;
l_atp_insert_rec.ship_method := null;
l_atp_insert_rec.session_id:= MSC_ATP_PVT.G_SESSION_ID;
l_atp_insert_rec.delivery_lead_time := l_delivery_lead_time;
MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
p_plan_id,
l_demand_class_flag,
l_demand_id);
l_atp_insert_rec.instance_id := p_atp_record.instance_id;
l_atp_insert_rec.inventory_item_id := PF_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.request_item_id := C_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.atf_date := p_atp_record.atf_date;
l_atp_insert_rec.atf_date_quantity := 0; -- check this??
l_atp_insert_rec.requested_date_quantity := l_requested_date_quantity;
l_atp_insert_rec.organization_id := p_atp_record.organization_id;
l_atp_insert_rec.identifier := p_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=nvl(p_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(p_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_delivery :=
p_atp_record.demand_source_delivery;
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.requested_ship_date := l_requested_ship_date;
l_atp_insert_rec.demand_class := p_atp_record.demand_class;
l_atp_insert_rec.refresh_number := p_refresh_number; -- summary enhancement p_atp_record.refresh_number;
l_atp_insert_rec.order_number := p_atp_record.order_number;
l_atp_insert_rec.origination_type := 30; --1; 5088719
l_atp_insert_rec.old_demand_id := null;
l_atp_insert_rec.Top_Model_line_id := p_atp_record.Top_Model_line_id;
l_atp_insert_rec.ATO_Parent_Model_Line_Id := p_atp_record.ATO_Parent_Model_Line_Id;
l_atp_insert_rec.Parent_line_id := p_atp_record.Parent_line_id;
l_atp_insert_rec.ATO_Model_Line_Id := p_atp_record.ATO_Model_Line_Id;
l_atp_insert_rec.wip_supply_type := p_atp_record.wip_supply_type;
l_atp_insert_rec.mand_comp_flag := p_atp_record.mand_comp_flag;
l_atp_insert_rec.wip_supply_type := p_atp_record.mand_comp_flag;
l_atp_insert_rec.ship_set_name := p_atp_record.ship_set_name;
l_atp_insert_rec.arrival_set_name := p_atp_record.arrival_set_name;
l_atp_insert_rec.original_request_ship_date :=l_original_req_ship_date;
l_atp_insert_rec.ship_method := p_atp_record.ship_method;
l_atp_insert_rec.ship_method := null;
l_atp_insert_rec.session_id:= MSC_ATP_PVT.G_SESSION_ID;
l_atp_insert_rec.delivery_lead_time := l_delivery_lead_time;
l_mat_atp_info_rec.insert_flag := p_atp_record.insert_flag;
l_atp_insert_rec.instance_id := p_atp_record.instance_id;
l_atp_insert_rec.inventory_item_id := C_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.inventory_item_id := PF_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.request_item_id := C_ITEM_INFO_REC.dest_inv_item_id;
l_atp_insert_rec.atf_date := p_atp_record.atf_date;
l_atp_insert_rec.atf_date_quantity := l_atf_date_qty;
l_atp_insert_rec.requested_date_quantity := l_requested_date_quantity;
/*l_atp_insert_rec.inventory_item_id := MSC_ATP_FUNC.get_inv_item_id
(p_atp_record.instance_id,
--2152184
--p_atp_record.request_item_id,
l_request_item_id,
null,
p_atp_record.organization_id);
l_atp_insert_rec.organization_id := p_atp_record.organization_id;
l_atp_insert_rec.identifier := p_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=nvl(p_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(p_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_delivery :=
p_atp_record.demand_source_delivery;
l_atp_insert_rec.quantity_ordered := LEAST(GREATEST(l_requested_date_quantity, 0),
p_atp_record.quantity_ordered);
msc_sch_wb.atp_debug('ATP_Check: ' || 'l_atp_insert_rec.quantity_ordered : '||
l_atp_insert_rec.quantity_ordered);
l_atp_insert_rec.quantity_ordered := LEAST(GREATEST(l_requested_date_quantity, 0),
p_atp_record.quantity_ordered);
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
msc_sch_wb.atp_debug('ATP_Check: ' || 'l_atp_insert_rec.quantity_ordered : '||
l_atp_insert_rec.quantity_ordered);
l_atp_insert_rec.requested_ship_date := l_requested_ship_date;
l_atp_insert_rec.demand_class := p_atp_record.demand_class;
l_atp_insert_rec.refresh_number := p_refresh_number; -- summary enhancement p_atp_record.refresh_number;
l_atp_insert_rec.order_number := p_atp_record.order_number;
l_atp_insert_rec.origination_type := 30;
l_atp_insert_rec.demand_source_line := p_atp_record.demand_source_line;
l_atp_insert_rec.old_demand_id := p_atp_record.old_demand_id;
l_atp_insert_rec.origination_type := 47;
msc_sch_wb.atp_debug('ATP_Check: ' || 'DRP origination_type ='|| l_atp_insert_rec.origination_type);
l_atp_insert_rec.origination_type := 1;
l_atp_insert_rec.demand_source_line := p_atp_record.demand_source_line;
l_atp_insert_rec.old_demand_id := p_atp_record.old_demand_id;
l_atp_insert_rec.old_demand_id := null;
l_atp_insert_rec.Top_Model_line_id := p_atp_record.Top_Model_line_id;
l_atp_insert_rec.ATO_Parent_Model_Line_Id := p_atp_record.ATO_Parent_Model_Line_Id;
l_atp_insert_rec.Parent_line_id := p_atp_record.Parent_line_id;
l_atp_insert_rec.ATO_Model_Line_Id := p_atp_record.ATO_Model_Line_Id;
l_atp_insert_rec.wip_supply_type := p_atp_record.wip_supply_type;
l_atp_insert_rec.mand_comp_flag := p_atp_record.mand_comp_flag;
l_atp_insert_rec.ship_set_name := p_atp_record.ship_set_name;
l_atp_insert_rec.arrival_set_name := p_atp_record.arrival_set_name;
l_atp_insert_rec.original_request_ship_date :=l_original_req_ship_date;
msc_sch_wb.atp_debug('ATP_Check: ' || 'latest_acceptable_date: '|| l_atp_insert_rec.latest_acceptable_date);
msc_sch_wb.atp_debug('ATP_Check: ' || 'latest_acceptable_date: '|| l_atp_insert_rec.original_request_ship_date);
l_atp_insert_rec.wip_supply_type := p_atp_record.mand_comp_flag;
l_atp_insert_rec.ship_method := p_atp_record.ship_method;
l_atp_insert_rec.ship_method := null;
msc_sch_wb.atp_debug('ATP_Check: ' || 'l_atp_insert_rec.ship_method : '|| l_atp_insert_rec.ship_method);
l_atp_insert_rec.session_id:= MSC_ATP_PVT.G_SESSION_ID;
l_atp_insert_rec.delivery_lead_time := l_delivery_lead_time;
MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
p_plan_id,
l_demand_class_flag,
l_demand_id);
IF p_atp_record.insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_atp_pegging_id,
MSC_ATP_PVT.G_DEMAND_PEGGING_ID);
g_atp_record.insert_flag := p_atp_record.insert_flag;
MSC_ATP_DB_UTILS.delete_pegging( l_atp_pegging_id);
msc_sch_wb.atp_debug('ATP_Check: ' || 'Update POD for Primary Component');
MSC_ATP_DB_UTILS.Update_Pegging(l_pegging_id,
null,
p_atp_record.quantity_ordered-
l_sub_requested_date_quantity );
update msc_demands
set USING_REQUIREMENT_QUANTITY =
MSC_ATP_UTILS.Truncate_Demand((USING_REQUIREMENT_QUANTITY-
greatest(l_sub_requested_date_quantity, 0))) -- 5598066
where demand_id = l_demand_id
and plan_id = p_plan_id
returning inventory_item_id into l_inventory_item_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'number of rows updated := ' || SQL%ROWCOUNT);
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := l_atp_insert_rec.quantity_ordered - greatest(l_sub_requested_date_quantity, 0);
l_atp_insert_rec.requested_date_quantity := l_atp_insert_rec.requested_date_quantity - greatest(l_sub_requested_date_quantity, 0);
l_atp_insert_rec,
p_plan_id,
l_demand_id,
l_return_status
);
update msc_alloc_demands
set allocated_quantity = (allocated_quantity -
greatest(l_sub_requested_date_quantity, 0))
where parent_demand_id = l_demand_id --updating for primary only
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'number of rows updated := ' || SQL%ROWCOUNT);
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty + greatest(l_sub_requested_date_quantity, 0)
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and inventory_item_id = l_inventory_item_id
and organization_id = p_atp_record.organization_id
and sd_date = trunc(l_requested_ship_date);
g_atp_record.insert_flag := p_atp_record.insert_flag;
update msc_demands
set USING_REQUIREMENT_QUANTITY = p_atp_record.quantity_ordered
where plan_id = p_plan_id and
demand_id = l_demand_id;
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.requested_date_quantity := l_atp_insert_rec.requested_date_quantity + nvl(l_stealing_qty, 0);
l_atp_insert_rec.atf_date_quantity := l_atp_insert_rec.atf_date_quantity + nvl(l_mem_stealing_qty, 0);
l_atp_insert_rec,
p_plan_id,
l_demand_id,
l_return_status
);
/* update atf date quantity */
p_atp_record.atf_date_quantity := l_atp_insert_rec.atf_date_quantity;
update msc_alloc_demands
set allocated_quantity = p_atp_record.quantity_ordered
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
update msc_demands
set USING_REQUIREMENT_QUANTITY =
(p_atp_record.quantity_ordered - l_stealing_qty)
where demand_id = l_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'after insert row, 1');
update msc_demands
set using_requirement_quantity = MSC_ATP_UTILS.Truncate_Demand(p_atp_record.requested_date_quantity + l_stealing_requested_date_qty)
where demand_id = l_demand_id
and plan_id = p_plan_id;
--using the same insert rec we prepared earlier
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered - l_net_demand;
l_atp_insert_rec.requested_date_quantity := GREATEST(p_atp_record.requested_date_quantity, 0)
+ nvl(l_stealing_requested_date_qty, 0);
l_atp_insert_rec.atf_date_quantity := NVL(p_atp_record.atf_date_quantity, 0);
l_atp_insert_rec,
p_plan_id,
l_demand_id,
l_return_status
);
update msc_alloc_demands
set allocated_quantity = p_atp_record.requested_date_quantity + l_stealing_requested_date_qty
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
p_atp_record.atf_date_quantity := NVL(l_atp_insert_rec.requested_date_quantity, 0) + NVL(l_atf_date_qty, 0);
UPDATE msc_demands
SET USING_ASSEMBLY_DEMAND_DATE = l_atp_date_this_level
--ngoel 8/30/2000 Check if this needs to be changed to l_atp_date_this_level.
WHERE demand_id = l_demand_id
AND plan_id = p_plan_id;
-- This piece of code moves the demand date which is also done in procedure update_sd_date
-- Since update_sd_date was not called for last line of SLSO ATO model, therefore, the summary
-- picture was not matching with details picture for last line.
-- On commenting this line, this piece of code is never executed for SLSO ATO model lines
-- making the summary picture consistent with details picture for all the lines.
---bug 3098127: the following condition was removed by the bug 2738280.
-- this shouldn't have been removed.
IF (p_parent_pegging_id is null) OR
((p_parent_pegging_id is not null AND
p_search = FORWARD_SCHEDULING)) THEN
-- dsting ATO 2465370
-- in case of override for CTO, do not adjust the demand date
--bug 3564511: This condition is not necessary as we always want to
-- adjust dates
--IF NVL(G_LINE_OVERRIDE_FLAG, 'N') = 'N'
--OR (NVL(C_ITEM_INFO_REC.cto_source_org_id, -1) <> p_atp_record.organization_id
--AND p_parent_pegging_id is not null)
--THEN
--bug 3328421: required date contains the original request date
-- actual_supply_demand_date contain the actual sd date
--update mrp_atp_details_temp set required_date = TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY
update mrp_atp_details_temp set actual_supply_demand_date =
TRUNC(l_atp_date_this_level)--4460369 + MSC_ATP_PVT.G_END_OF_DAY
where session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id = l_pegging_id
and record_type = 3;
msc_sch_wb.atp_debug('ATO update details_temp 1: ' || l_pegging_id || ' date: ' || l_atp_date_this_level);
msc_sch_wb.atp_debug('Update demand with demand id := ' || l_demand_id);
msc_sch_wb.atp_debug('Update demand on date : = ' || l_atp_date_this_level);
UPDATE msc_sales_orders
SET REQUIREMENT_DATE = TRUNC(l_atp_date_this_level) --4460369+ MSC_ATP_PVT.G_END_OF_DAY
WHERE sr_instance_id = p_atp_record.instance_id
AND DEMAND_ID = l_demand_id
returning inventory_item_id into l_inventory_item_id;
UPDATE msc_demands
SET
--start changes for plan by request date
USING_ASSEMBLY_DEMAND_DATE =
DECODE(ORIGINATION_TYPE,
6, decode(MSC_ATP_PVT.G_PLAN_INFO_REC.schedule_by_date_type,
MSC_ATP_PVT.G_SCHEDULE_SHIP_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_SCHEDULE_ARRIVAL_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_SHIP_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_ARRIVAL_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_REQUEST_SHIP_DATE_LEGEND,
l_original_req_ship_date,
MSC_ATP_PVT.G_REQUEST_ARRIVAL_DATE_LEGEND,
l_original_req_ship_date,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY),
30, decode(MSC_ATP_PVT.G_PLAN_INFO_REC.schedule_by_date_type,
MSC_ATP_PVT.G_SCHEDULE_SHIP_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_SCHEDULE_ARRIVAL_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_SHIP_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_ARRIVAL_DATE_LEGEND,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_REQUEST_SHIP_DATE_LEGEND,
l_original_req_ship_date,
MSC_ATP_PVT.G_REQUEST_ARRIVAL_DATE_LEGEND,
l_original_req_ship_date,
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY),
TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY),
schedule_ship_date = DECODE(ORIGINATION_TYPE,
6, TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
30, TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
NULL),
promise_ship_date = DECODE(ORIGINATION_TYPE,
6, TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
30, TRUNC(l_atp_date_this_level) + MSC_ATP_PVT.G_END_OF_DAY,
NULL)
--end of plan by request date enhanc
WHERE demand_id = l_demand_id
AND plan_id = p_plan_id
returning inventory_item_id into l_inventory_item_id;
update msc_alloc_demands
set DEMAND_DATE = l_atp_date_this_level
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'update old date demand');
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty + p_atp_record.quantity_ordered
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and organization_id = p_atp_record.organization_id
and inventory_item_id = l_inventory_item_id
and sd_date = trunc(l_requested_ship_date);
msc_sch_wb.atp_debug('ATP_Check: ' || 'update demand on new date');
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty - p_atp_record.quantity_ordered
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and organization_id = p_atp_record.organization_id
and inventory_item_id = l_inventory_item_id
and sd_date = trunc(l_atp_date_this_level);
msc_sch_wb.atp_debug('ATP_Check: ' || 'insert demand on new date');
MSC_ATP_DB_UTILS.INSERT_SUMMARY_SD_ROW(p_plan_id,
p_atp_record.instance_id,
p_atp_record.organization_id,
l_inventory_item_id,
l_atp_date_this_level,
-1 * p_atp_record.quantity_ordered,
'@@@');
IF NVL(p_atp_record.insert_flag,0) <> 0 THEN
IF MSC_ATP_PVT.G_INV_CTP = 4 THEN
IF (MSC_ATP_PVT.G_ALLOCATED_ATP = 'Y') AND
(MSC_ATP_PVT.G_ALLOCATION_METHOD = 2) THEN
-- Link the Supply Demand Details for
--first Demand class to the pegging details
IF l_atp_period_first.pegging_id.COUNT > 0 THEN
MSC_ATP_UTILS.copy_mrp_sd_recs(
l_atp_period_first.pegging_id(1),
l_atp_pegging_id);
IF p_atp_record.insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_atp_pegging_id,
MSC_ATP_PVT.G_DEMAND_PEGGING_ID);
Select DECODE(pl.planning_time_fence_flag,
1, trunc(NVL(itm.planning_time_fence_date, l_ptf_date)), -- Bug 3535999 - l_sysdate)),
l_ptf_date), -- Bug 3535999 - l_sysdate),
pl.planning_time_fence_flag -- Bug 3525582
into l_ptf_date, l_ptf_enabled -- Bug 3525582
from msc_system_items itm,
msc_plans pl
where itm.plan_id = p_plan_id
and itm.sr_instance_id = p_atp_record.instance_id
and itm.organization_id = p_atp_record.organization_id
--and itm.sr_inventory_item_id = p_atp_record.inventory_item_id
and itm.sr_inventory_item_id = nvl(p_atp_record.request_item_id,p_atp_record.inventory_item_id) --Bug-5160663
and pl.plan_id = itm.plan_id
and pl.sr_instance_id = itm.sr_instance_id;
UPDATE msc_demands
SET USING_REQUIREMENT_QUANTITY = 0
WHERE demand_id = l_demand_id
AND plan_id = p_plan_id
returning inventory_item_id into l_inventory_item_id;
update msc_alloc_demands
set ALLOCATED_QUANTITY = 0
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'in sumary mode, update msc-demands');
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty + p_atp_record.quantity_ordered
where plan_id = p_plan_id
and inventory_item_id = l_inventory_item_id
and sr_instance_id = p_atp_record.instance_id
and organization_id = p_atp_record.organization_id
and sd_date = trunc(l_requested_ship_date);
msc_sch_wb.atp_debug('ATP_Check: ' || 'demand updated for demand id = '|| l_demand_id);
IF p_atp_record.insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_future_pegging_id,
MSC_ATP_PVT.G_DEMAND_PEGGING_ID);
update mrp_atp_details_temp
set error_code = MSC_ATP_PVT.OSS_ERROR_AT_THIS_LEVEL
where session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id = l_pegging_id
and record_type = 3;
SELECT planning_make_buy_code INTO l_make_buy_cd
FROM MSC_SYSTEM_ITEMS I
--bug3842035 Using member item id instead of family
--WHERE I.sr_inventory_item_id = p_atp_record.inventory_item_id and
WHERE I.sr_inventory_item_id = p_atp_record.request_item_id and
I.sr_instance_id = p_atp_record.instance_id and
I.organization_id = p_atp_record.organization_id and
I.plan_id = p_plan_id ;
SELECT nvl(postprocessing_lead_time, 0)
INTO l_atp_rec.delivery_lead_time
FROM msc_system_items
WHERE plan_id = p_plan_id
AND organization_id = l_atp_rec.organization_id
AND sr_instance_id = l_atp_rec.instance_id
AND inventory_item_id = l_inv_item_id; -- 1665483
SELECT trunc(cutoff_date),
DECODE(plan_type, 4, 1,
DECODE(daily_material_constraints, 1, NVL(enforce_pur_lt_constraints, 2),
DECODE(daily_resource_constraints, 1, NVL(enforce_pur_lt_constraints, 2),
DECODE(weekly_material_constraints, 1, NVL(enforce_pur_lt_constraints, 2),
DECODE(weekly_resource_constraints, 1, NVL(enforce_pur_lt_constraints, 2),
DECODE(period_material_constraints, 1, NVL(enforce_pur_lt_constraints, 2),
DECODE(period_resource_constraints, 1, NVL(enforce_pur_lt_constraints, 2), 1)
)
)
)
)
)
),
sr_instance_id,
trunc(plan_start_date),
organization_id
INTO MSC_ATP_PVT.G_PLAN_INFO_REC.plan_cutoff_date,
MSC_ATP_PVT.G_PLAN_INFO_REC.enforce_pur_lead_time,
MSC_ATP_PVT.G_PLAN_INFO_REC.sr_instance_id,
MSC_ATP_PVT.G_PLAN_INFO_REC.plan_start_date,
MSC_ATP_PVT.G_PLAN_INFO_REC.organization_id
FROM msc_plans
WHERE plan_id = p_plan_id;
SELECT max(trunc(NVL(to_date,G_PLAN_INFO_REC.plan_cutoff_date))) --4055719
INTO l_atp_rec.last_cap_date
FROM msc_supplier_capacities s
WHERE s.inventory_item_id = NVL(C_ITEM_INFO_REC.base_item_id,
C_ITEM_INFO_REC.dest_inv_item_id)
AND s.sr_instance_id = l_sources.instance_id(j)
AND s.plan_id = p_plan_id
AND s.organization_id = p_atp_record.organization_id
AND s.supplier_id = l_sources.supplier_id(j)
AND NVL(s.supplier_site_id, -1) = NVL(l_sources.supplier_site_id(j), -1);
select nvl(processing_lead_time, l_process_lt), supplier_lead_time_date
into l_process_lt, g_sup_cap_cum_date
from msc_item_suppliers
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and organization_id = p_atp_record.organization_id
--bug 3373598: Use model's inv id for config item.
-- base_item_id will be populated only for ATO items
and inventory_item_id = NVL(C_ITEM_INFO_REC.base_item_id, l_inv_item_id)
and supplier_id = l_sources.Supplier_Id(j)
and supplier_site_id = l_sources.Supplier_site_Id(j);
l_atp_rec.insert_flag := p_atp_record.insert_flag;
--update mrp_atp_details_temp set required_date =
update mrp_atp_details_temp set actual_supply_demand_date =
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY
where session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id = l_parent_pegging_id
and record_type = 3;
msc_sch_wb.atp_debug('ATO update details_temp date single lev: ' || p_atp_record.ship_date);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
p_atp_record.ship_date,
0,
l_atp_rec.supplier_id,
l_atp_rec.supplier_site_id,
null,
null, -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
--update mrp_atp_details_temp set required_date =
update mrp_atp_details_temp set actual_supply_demand_date =
TRUNC(l_ship_date) + MSC_ATP_PVT.G_END_OF_DAY
where session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id = l_parent_pegging_id
and record_type = 3;
msc_sch_wb.atp_debug('ATO update details_temp date go down: ' || l_ship_date);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_future_pegging_id,
p_plan_id,
p_atp_record.ship_date,
0,
l_atp_rec.supplier_id,
l_atp_rec.supplier_site_id,
null,
null, -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
--bug 2798667: The ATO components are sourced on l_ship_date
--p_atp_record.ship_date,
l_ship_date,
-- dsting 2754446
-- least(l_atp_rec.available_quantity, l_atp_rec.quantity_ordered),
least(l_prim_uom_avail_qty, l_PO_qty),
l_atp_rec.supplier_id,
l_atp_rec.supplier_site_id,
l_new_dock_date, -- Bug 3241766
l_new_ship_date, -- Bug 3241766
l_sugg_start_date, -- Bug 3241766
l_order_date, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
MSC_ATP_PVT.UNDO, -- dsting 2754446
l_conversion_rate -- dsting 2754446
); -- dock date
l_update_PO_mode := MSC_ATP_PVT.INVALID;
l_update_PO_mode := MSC_ATP_PVT.UNDO;
msc_sch_wb.atp_debug('ATP_Check: ' || 'set l_update_PO_mode ' || l_update_PO_mode);
-- then we update the planned order. If l_update_PO_mode is INVALID
-- then we remove the subtree. Otherwise, diagnostic atp is on and
-- this is the last source, so we leave the planned order as is to
-- balance the parent demand and only update the pegging
-- dsting 2819716. we always add planned orders now
-- so we should always update them too.
-- IF l_constraint_flag <> 1 THEN
IF l_update_PO_mode = MSC_ATP_PVT.INVALID OR
l_sources.source_type.next(j) is not NULL OR
MSC_ATP_PVT.G_DIAGNOSTIC_ATP <> 1
THEN
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('l_PO_qty: ' || l_PO_qty);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
null,
-- dsting 2754446
-- least(greatest(l_atp_rec.requested_date_quantity, 0),
-- l_atp_rec.quantity_ordered),
least(greatest(l_prim_uom_req_date_qty, 0),
l_PO_qty),
l_atp_rec.supplier_id,
l_atp_rec.supplier_site_id,
null, -- l_atp_rec.requested_ship_date, no need to update -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
l_update_PO_mode,
l_conversion_rate
); -- dock date
IF l_update_PO_mode = MSC_ATP_PVT.UNDO THEN
l_last_good_pegging_id := l_pegging_id;
MSC_ATP_DB_UTILS.Update_Pegging(l_pegging_id, null,
least(greatest(l_atp_rec.requested_date_quantity, 0)));
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
null,
-- dsting 2754446
-- least(greatest(l_atp_rec.requested_date_quantity, 0),
-- l_atp_rec.quantity_ordered),
--least(greatest(l_prim_uom_req_date_qty, 0),
-- l_PO_qty),
--NVL(l_PO_qty,0),
NULL,
l_atp_rec.supplier_id,
l_atp_rec.supplier_site_id,
null, -- l_atp_rec.requested_ship_date, no need to update -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
l_update_PO_mode,
l_conversion_rate
); -- dock date
update msc_demands
set USING_REQUIREMENT_QUANTITY =
(USING_REQUIREMENT_QUANTITY -
LEAST(l_net_demand, 0))
where demand_id = l_demand_id
and plan_id = p_plan_id;
-- inserted in assembly_item_id column in msc_resource_requirements
p_atp_record.organization_id,
l_pegging_id,
l_PO_qty, -- 2754446
--l_net_demand,
l_requested_ship_date,
p_refresh_number,
p_atp_record.insert_flag,
p_search,
p_atp_record.demand_class,
--(ssurendr) Bug 2865389 Added Routing Sequence id
--and Bill sequence id for OPM fix.
l_routing_seq_id,
l_bill_seq_id,
p_atp_record.ship_date, -- Bug 2814872
p_atp_record.demand_source_line, -- CTO ODR
l_avail_assembly_qty,
l_res_atp_date,
l_res_atp_period,
l_res_atp_supply_demand,
l_return_status );
p_atp_record.insert_flag,
p_search,
p_assign_set_id,
-- (ssurendr) Bug 2865389 Added Routing Sequence id
--and Bill sequence id for OPM fix.
l_routing_seq_id,
l_bill_seq_id,
p_atp_record.inventory_item_id, -- For time_phased_atp
p_atp_record.atf_date, -- For time_phased_atp
p_atp_record.manufacturing_cal_code, -- For ship_rec_cal
l_avail_assembly_qty,
l_comp_atp_date,
l_comp_atp_period,
l_comp_atp_supply_demand,
l_return_status,
--s_cto_rearch
l_get_comp_info_rec,
-- CTO Phase 1 #16, #17
p_atp_record.order_number,
l_op_seq_id --4570421
);
-- If this is the last source we should only update the pegging and not
-- the actual PO in case of diagnostic atp
IF l_avail_PO_qty < l_net_demand THEN -- 2754446
--IF l_avail_assembly_qty < l_net_demand THEN
l_last_good_pegging_id := l_pegging_id;
MSC_ATP_DB_UTILS.Update_Pegging(l_pegging_id, null, l_avail_PO_qty); -- 2869830
-- MSC_ATP_DB_UTILS.Update_Pegging(l_pegging_id, null, l_avail_assembly_qty);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
null,
l_avail_PO_qty, -- 2869830
--l_avail_assembly_qty,
null,
null,
null,
null, -- Bug 3241766
--bug 3755704: Pass start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
MSC_ATP_PVT.UNDO);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
null,
l_avail_PO_qty,
--l_avail_assembly_qty,
null,
null,
null,
null, -- Bug 3241766
--bug 3755704: pass start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
-- inserted in assembly_item_id column in msc_resource_requirements
p_atp_record.organization_id,
l_pegging_id,
l_res_demand,
l_requested_ship_date,
p_refresh_number,
p_atp_record.insert_flag,
p_search,
p_atp_record.demand_class,
--(ssurendr) Bug 2865389 Added Routing Sequence id
--and Bill sequence id for OPM fix.
l_routing_seq_id,
l_bill_seq_id,
p_atp_record.ship_date, -- Bug 2814872
p_atp_record.demand_source_line, -- CTO ODR
l_avail_assembly_qty,
l_res_atp_date,
l_res_atp_period,
l_res_atp_supply_demand,
l_return_status );
p_atp_record.insert_flag,
p_search,
p_assign_set_id,
--(ssurendr) Bug 2865389 Added Routing Sequence id
--and Bill sequence id for OPM fix.
l_routing_seq_id,
l_bill_seq_id,
p_atp_record.inventory_item_id, -- For time_phased_atp
p_atp_record.atf_date, -- For time_phased_atp
p_atp_record.manufacturing_cal_code, -- For ship_rec_cal
l_avail_assembly_qty,
l_comp_atp_date,
l_comp_atp_period,
l_comp_atp_supply_demand,
l_return_status,
--s_cto_rearch
l_get_comp_info_rec,
-- CTO Phase 1 #16, #17
p_atp_record.order_number,
l_op_seq_id --4570421
);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
null,
0,
null,
null,
null,
null, -- Bug 3241766
--3755704: Pass start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
null,
0,
null,
null,
null,
null, -- Bug 3241766
--bug 3755704: Pass start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
--- OSFM Changes: update coproduct supply
l_coproducts_flag := FND_Profile.value('MSC_PLAN_COPRODUCTS');
-- inserted in assembly_item_id column in msc_resource_requirements
p_atp_record.organization_id,
l_pegging_id,
l_PO_qty, -- 2754446
--l_net_demand,
l_requested_ship_date,
p_refresh_number,
p_atp_record.insert_flag,
p_search,
p_atp_record.demand_class,
--(ssurendr) Bug 2865389 Added Routing Sequence id
--and Bill sequence id for OPM fix.
l_routing_seq_id,
l_bill_seq_id,
p_atp_record.ship_date, -- Bug 2814872
p_atp_record.demand_source_line, -- CTO ODR
l_avail_assembly_qty,
l_res_atp_date,
l_res_atp_period,
l_res_atp_supply_demand,
l_return_status );
p_atp_record.insert_flag,
p_search,
p_assign_set_id,
--(ssurendr) Bug 2865389 Added Routing Sequence id
--and Bill sequence id for OPM fix.
l_routing_seq_id,
l_bill_seq_id,
p_atp_record.inventory_item_id, -- For time_phased_atp
p_atp_record.atf_date, -- For time_phased_atp
p_atp_record.manufacturing_cal_code, -- For ship_rec_cal
l_avail_assembly_qty,
l_comp_atp_date,
l_comp_atp_period,
l_comp_atp_supply_demand,
l_return_status,
--s_cto_rearch
l_get_comp_info_rec,
-- CTO Phase 1 #16, #17
p_atp_record.order_number,
l_op_seq_id --4570421
);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
p_atp_record.ship_date,
0,
null,
null,
null,
null, -- Bug 3241766
--bug 3755704: Pass start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
MSC_ATP_DB_UTILS.Update_Planned_Order(l_future_pegging_id,
p_plan_id,
p_atp_record.ship_date,
0,
null,
null,
null,
null, -- Bug 3241766
--bug 3755704: Pass start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
MSC_ATP_DB_UTILS.Update_Planned_Order(l_pegging_id,
p_plan_id,
--bug 2798667: this should be l_temp_date
--p_atp_record.ship_date,
l_temp_date,
null,
null,
null,
null,
null, -- Bug 3241766
--bug 3755704: populate start date and order date
l_start_date,
l_order_date,
--null, -- Bug 3241766
--null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id); -- Bug 3293163
--- OSFM Changes: update coproduct supply
l_coproducts_flag := FND_Profile.value('MSC_PLAN_COPRODUCTS');
Update the pegging with the ATP+CTP Quantity of the pegging.
l_temp_net_demand - l_net_demand; gives us the CTP quantity
MSC_ATP_DB_UTILS.Update_Pegging( l_substitutes_rec.pegging_id(l_index),
null,
l_atp_ctp_qty );
l_atp_insert_rec.instance_id := p_atp_record.instance_id;
l_atp_insert_rec.inventory_item_id := l_substitutes_rec.pf_item_id(l_index);
l_atp_insert_rec.request_item_id := l_substitutes_rec.inventory_item_id(l_index);
l_atp_insert_rec.atf_date_quantity := l_substitutes_rec.atf_date_quantity(l_index);
l_atp_insert_rec.organization_id := p_atp_record.organization_id;
l_atp_insert_rec.identifier := p_atp_record.identifier;
l_atp_insert_rec.demand_source_type:=
nvl(p_atp_record.demand_source_type, 2);
l_atp_insert_rec.demand_source_header_id :=
nvl(p_atp_record.demand_source_header_id, -1);
l_atp_insert_rec.demand_source_delivery :=
p_atp_record.demand_source_delivery;
l_atp_insert_rec.quantity_ordered:= LEAST(
l_atp_ctp_qty,
l_substitutes_rec.quantity_ordered(l_index));
l_atp_insert_rec.requested_ship_date := p_atp_record.requested_ship_date;
l_atp_insert_rec.demand_class := p_atp_record.demand_class;
l_atp_insert_rec.refresh_number := p_refresh_number;
l_atp_insert_rec.order_number := p_atp_record.order_number;
l_atp_insert_rec.origination_type := 1;
MSC_ATP_DB_UTILS.Add_Mat_Demand(l_atp_insert_rec,
-- l_plan_id, use p_plan_id in case of Make CTP.
nvl(l_plan_id,p_plan_id), -- bug#10428207
0,
l_demand_id);
UPDATE mrp_atp_details_temp
SET identifier3 = l_demand_id
WHERE pegging_id = l_substitutes_rec.pegging_id(l_index)
AND session_id = MSC_ATP_PVT.G_SESSION_ID
AND record_type = 3;
MSC_ATP_DB_UTILS.Update_Pegging( l_substitutes_rec.pegging_id(l_index),
null,
l_atp_ctp_qty );
update msc_demands
set USING_REQUIREMENT_QUANTITY = MSC_ATP_UTILS.Truncate_Demand(l_atp_ctp_qty) -- 5598066
where demand_id = l_substitutes_rec.demand_id(l_index)
and plan_id = p_plan_id
returning inventory_item_id
into l_inventory_item_id;
update msc_alloc_demands
set allocated_quantity = l_atp_ctp_qty
where parent_demand_id = l_substitutes_rec.demand_id(l_index) --l_demand_id
--where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty + l_atp_ctp_qty
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and inventory_item_id = l_inventory_item_id
and organization_id = p_atp_record.organization_id
and sd_date = trunc(l_requested_ship_date);
-- dsting diag_atp update the last good pegging record to make supply/demand match
IF PG_DEBUG in ('Y', 'C') THEN
msc_sch_wb.atp_debug('diag_atp l_pegging_id ' || l_pegging_id);
MSC_ATP_DB_UTILS.Update_Planned_Order(l_last_good_pegging_id,
p_plan_id,
null,
l_net_demand + l_subst_existing_PO_qty,
null,
null,
null,
null, -- Bug 3241766
null, -- Bug 3241766
null, -- Bug 3241766
C_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
PF_ITEM_INFO_REC.dest_inv_item_id, -- Bug 3293163
3);
update msc_demands
set USING_REQUIREMENT_QUANTITY =
MSC_ATP_UTILS.Truncate_Demand((USING_REQUIREMENT_QUANTITY-l_net_demand)) -- 5598066
where demand_id = l_demand_id
and plan_id = p_plan_id
returning inventory_item_id into l_inventory_item_id;
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered - l_net_demand;
l_atp_insert_rec.requested_date_quantity := l_atp_insert_rec.quantity_ordered;
l_atp_insert_rec.atf_date_quantity := GREATEST(NVL(p_atp_record.atf_date_quantity,
0), 0); --7185041
l_atp_insert_rec,
p_plan_id,
l_demand_id,
l_return_status
);
update msc_alloc_demands
set allocated_quantity = allocated_quantity -l_net_demand
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'in sumary mode, update msc-demands');
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty + l_net_demand
where plan_id = p_plan_id
and inventory_item_id = l_inventory_item_id
and sr_instance_id = p_atp_record.instance_id
and organization_id = p_atp_record.organization_id
and sd_date = trunc(l_requested_ship_date);
l_atp_insert_rec.quantity_ordered := p_atp_record.quantity_ordered;
l_atp_insert_rec.requested_date_quantity := p_atp_record.combined_requested_date_qty;
l_atp_insert_rec.atf_date_quantity := GREATEST(NVL(p_atp_record.atf_date_quantity,
0), 0); --7185041
l_atp_insert_rec,
p_plan_id,
l_demand_id,
l_return_status
);
/*update msc_demands
set USING_REQUIREMENT_QUANTITY =
(USING_REQUIREMENT_QUANTITY-l_net_demand)
where demand_id = l_demand_id
and plan_id = p_plan_id;*/
update mrp_atp_details_temp
set supply_demand_quantity =
(supply_demand_quantity - l_net_demand)
where session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id = l_parent_pegging_id
and record_type = 3;
SELECT supply_demand_quantity
INTO l_peg_qty
FROM mrp_atp_details_temp
WHERE session_id = MSC_ATP_PVT.G_SESSION_ID
AND pegging_id = l_parent_pegging_id
AND record_type = 3;
MSC_ATP_DB_UTILS.Update_Pegging(l_parent_pegging_id,
null,
(l_peg_qty - l_net_demand));
update msc_demands
set USING_REQUIREMENT_QUANTITY =
MSC_ATP_UTILS.Truncate_Demand((USING_REQUIREMENT_QUANTITY-l_net_demand)) -- 5598066
where demand_id = l_demand_id
and plan_id = p_plan_id
returning inventory_item_id into l_inventory_item_id;
Update PF Bucketed Demands to qty we could find in backward case*/
--5211558 we want to update the demands only in first pass and not in further passes
IF l_time_phased_atp = 'Y' and MSC_ATP_PVT.G_OPTIONAL_FW is null THEN
MSC_ATP_PF.Update_PF_Bucketed_Demands(
p_plan_id,
l_demand_id,
l_requested_ship_date,
p_atp_record.atf_date,
p_atp_record.quantity_ordered,
p_atp_record.combined_requested_date_qty,
l_return_status
);
msc_sch_wb.atp_debug('ATP_Check: ' || 'Error occured in procedure Update_PF_Bucketed_Demands');
update msc_alloc_demands
set allocated_quantity = allocated_quantity - l_net_demand
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'update sd table for component');
msc_sch_wb.atp_debug('ATP_Check: ' || 'in sumary mode, update msc-demands');
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty + l_net_demand
where plan_id = p_plan_id
and inventory_item_id = l_inventory_item_id
and sr_instance_id = p_atp_record.instance_id
and organization_id = p_atp_record.organization_id
and sd_date = trunc(l_requested_ship_date);
--update mrp_atp_details_temp set required_date =
update mrp_atp_details_temp set actual_supply_demand_date =
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY
where session_id = MSC_ATP_PVT.G_SESSION_ID
and pegging_id = l_parent_pegging_id
and record_type = 3;
msc_sch_wb.atp_debug('ATO update details_temp 12: ' || l_parent_pegging_id || ' date: ' || p_atp_record.ship_date);
UPDATE msc_demands
--SET USING_ASSEMBLY_DEMAND_DATE = NVL(p_atp_record.ship_date,
-- USING_ASSEMBLY_DEMAND_DATE)
-- For bug 2259824, move the demand to the end of day
----start changes for plan by request date
SET USING_ASSEMBLY_DEMAND_DATE =
DECODE(ORIGINATION_TYPE,
6, decode(MSC_ATP_PVT.G_PLAN_INFO_REC.schedule_by_date_type,
MSC_ATP_PVT.G_SCHEDULE_SHIP_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_SCHEDULE_ARRIVAL_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_SHIP_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_ARRIVAL_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_REQUEST_SHIP_DATE_LEGEND,
l_original_req_ship_date,
MSC_ATP_PVT.G_REQUEST_ARRIVAL_DATE_LEGEND,
l_original_req_ship_date,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY),
30, decode(MSC_ATP_PVT.G_PLAN_INFO_REC.schedule_by_date_type,
MSC_ATP_PVT.G_SCHEDULE_SHIP_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_SCHEDULE_ARRIVAL_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_SHIP_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_PROMISE_ARRIVAL_DATE_LEGEND,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
MSC_ATP_PVT.G_REQUEST_SHIP_DATE_LEGEND,
l_original_req_ship_date,
MSC_ATP_PVT.G_REQUEST_ARRIVAL_DATE_LEGEND,
l_original_req_ship_date,
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY),
TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY),
schedule_ship_date =
DECODE(ORIGINATION_TYPE,
6, TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
30, TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
NULL),
promise_ship_date =
DECODE(ORIGINATION_TYPE,
6, TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
30, TRUNC(p_atp_record.ship_date) + MSC_ATP_PVT.G_END_OF_DAY,
NULL)
--end changes for plan by request date
WHERE demand_id = l_demand_id
AND plan_id = p_plan_id
returning inventory_item_id into l_inventory_item_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'number of rows updated := ' || SQL%ROWCOUNT);
update msc_alloc_demands
set demand_date = p_atp_record.ship_date
where parent_demand_id = l_demand_id
and plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'Update summary for old date');
UPDATE /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ MSC_ATP_SUMMARY_SD
set sd_qty = (sd_qty + p_atp_record.quantity_ordered)
where sr_instance_id = p_atp_record.instance_id and
inventory_item_id = l_inventory_item_id and
organization_id = p_atp_record.organization_id and
sd_date = trunc(l_requested_ship_date) and
plan_id = p_plan_id;
msc_sch_wb.atp_debug('ATP_Check: ' || 'update the qty on new date ');
update /*+ INDEX(msc_atp_summary_sd MSC_ATP_SUMMARY_SD_U1) */ msc_atp_summary_sd
set sd_qty = sd_qty - p_atp_record.quantity_ordered
where plan_id = p_plan_id and
sr_instance_id = p_atp_record.instance_id and
inventory_item_id = l_inventory_item_id and
organization_id = p_atp_record.organization_id and
sd_date = trunc(p_atp_record.ship_date);
msc_sch_wb.atp_debug('ATP_Check: ' || 'insert qty on new date');
MSC_ATP_DB_UTILS.INSERT_SUMMARY_SD_ROW(p_plan_id,
p_atp_record.instance_id,
p_atp_record.organization_id,
l_inventory_item_id,
p_atp_record.ship_date,
-1 * p_atp_record.quantity_ordered,
'@@@');
l_sup_atp_info_rec.insert_flag := p_atp_record.insert_flag;
msc_sch_wb.atp_debug('ATP_Check: ' || 'Update summary table , backward case');
msc_sch_wb.atp_debug('ATP_Check: ' || 'First try to update');
update /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_sup_U1) *//* msc_atp_summary_sup
set sd_qty = sd_qty - least(l_requested_date_quantity, p_atp_record.quantity_ordered)
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and inventory_item_id = l_inv_item_id
and supplier_id = p_atp_record.supplier_id
and supplier_site_id = p_atp_record.supplier_site_id
and sd_date = trunc(l_requested_ship_date);
msc_sch_wb.atp_debug('ATP_Check: ' || 'After update of msc_atp_summary_sup');
msc_sch_wb.atp_debug('ATP_Check: ' || ' Number of rows updated := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('ATP_Check: ' || 'update failed, insert a row');
insert into /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) *//* msc_atp_summary_sup
(plan_id,
inventory_item_id,
supplier_id,
supplier_site_id,
sr_instance_id,
sd_date,
sd_qty,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
VALUES
(p_plan_id,
l_inv_item_id,
p_atp_record.supplier_id,
p_atp_record.supplier_site_id,
p_atp_record.instance_id,
trunc(l_requested_ship_date),
-1 * least(l_requested_date_quantity, p_atp_record.quantity_ordered),
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID);
update /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) *//* msc_atp_summary_sup
set sd_qty = sd_qty - least(l_requested_date_quantity, p_atp_record.quantity_ordered)
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and inventory_item_id = l_inv_item_id
and supplier_id = p_atp_record.supplier_id
and supplier_site_id = p_atp_record.supplier_site_id
and sd_date = trunc(l_requested_ship_date);
msc_sch_wb.atp_debug('ATP_Check: ' || 'Update summary table, forward case');
msc_sch_wb.atp_debug('ATP_Check: ' || 'First try to update');
msc_sch_wb.atp_debug('ATP_Check: ' || 'First try to update');
update /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) *//* msc_atp_summary_sup
set sd_qty = sd_qty - p_atp_record.quantity_ordered
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and inventory_item_id = l_inv_item_id
and supplier_id = p_atp_record.supplier_id
and supplier_site_id = p_atp_record.supplier_site_id
and sd_date = trunc(l_atp_date_this_level);
msc_sch_wb.atp_debug('ATP_Check: ' || 'After update of msc_atp_summary_sup');
msc_sch_wb.atp_debug('ATP_Check: ' || ' Number of rows updated := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('ATP_Check: ' || 'update failed, insert a row');
insert /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) *//* into msc_atp_summary_sup
(plan_id,
inventory_item_id,
supplier_id,
supplier_site_id,
sr_instance_id,
sd_date,
sd_qty,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY
)
VALUES
(p_plan_id,
l_inv_item_id,
p_atp_record.supplier_id,
p_atp_record.supplier_site_id,
p_atp_record.instance_id,
trunc(l_atp_date_this_level),
-1 * p_atp_record.quantity_ordered,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID);
update /*+ INDEX(msc_atp_summary_sup MSC_ATP_SUMMARY_SUP_U1) *//* msc_atp_summary_sup
set sd_qty = sd_qty - p_atp_record.quantity_ordered
where plan_id = p_plan_id
and sr_instance_id = p_atp_record.instance_id
and inventory_item_id = l_inv_item_id
and supplier_id = p_atp_record.supplier_id
and supplier_site_id = p_atp_record.supplier_site_id
and sd_date = trunc(l_atp_date_this_level) ;
IF p_atp_record.insert_flag <> 0 THEN
MSC_ATP_DB_UTILS.move_SD_temp_into_mrp_details(l_pegging_id,
MSC_ATP_PVT.G_DEMAND_PEGGING_ID);
l_delete_atp_rec MSC_ATP_PVT.DELETE_ATP_REC ;--3720018
select 'x'
into l_temp
from mrp_atp_schedule_temp
where session_id = p_session_id
group by order_line_id
having count(*) > 1;
MSC_ATP_DB_UTILS.call_delete_row (
p_instance_id,
p_atp_table,
p_refresh_number,
l_delete_atp_rec,
l_return_status);
msc_sch_wb.atp_debug('Call_Schedule: l_delete_atp_rec.error_code(1): ' || l_delete_atp_rec.error_code(1));
msc_sch_wb.atp_debug('Call_Schedule: ' || 'Error in Call_delete_row');
IF (l_delete_atp_rec.error_code(1) = MSC_ATP_PVT.TRY_ATP_LATER) OR
(l_delete_atp_rec.error_code(1) = MSC_ATP_PVT.SUMM_CONC_PROG_RUNNING) THEN
x_atp_table := p_atp_table;
x_atp_table.error_code(i) := l_delete_atp_rec.error_code(1);
select count(*)
into l_same_org_count
from msc_system_items i
where i.plan_id = x_plan_info_rec.plan_id
and i.organization_id = l_atp_input_table.internal_org_id(m)
and i.sr_instance_id = p_instance_id
and i.sr_inventory_item_id = l_atp_input_table.inventory_item_id(m);
select count(*) into l_feeder_count
from msc_system_items
where plan_id in ( select plans.plan_id input_plan_id
from msc_plan_schedules sched,
msc_designators md,
msc_plans plans
where sched.plan_id = x_plan_info_rec.plan_id
and sched.input_type = 1
and sched.designator_type in (2,3,4,5)
and sched.input_schedule_id = md.designator_id
-- we need not consider plans collected from source
and md.collected_flag = 2
and md.designator = plans.compile_designator
and md.sr_instance_id = plans.sr_instance_id
and md.organization_id = plans.organization_id
)
and organization_id = l_atp_input_table.internal_org_id(m)
and sr_instance_id = p_instance_id
and sr_inventory_item_id = l_atp_input_table.inventory_item_id(m);
SELECT ltrim(rtrim(M2A_DBLINK))
INTO l_dblink
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
IF (l_delete_atp_rec.del_demand_ids IS NOT NULL AND l_delete_atp_rec.del_demand_ids.Count > 0) OR
(l_delete_atp_rec.del_ods_demand_ids IS NOT NULL AND l_delete_atp_rec.del_ods_demand_ids.Count > 0) THEN
MSC_ATP_DB_UTILS.Undo_Delete_Row(
l_delete_atp_rec.del_identifiers,
l_delete_atp_rec.del_plan_ids,
p_instance_id,
l_delete_atp_rec.del_demand_ids,
l_delete_atp_rec.del_inv_item_ids,
l_delete_atp_rec.del_copy_demand_ids,
l_delete_atp_rec.del_copy_demand_plan_ids,
l_delete_atp_rec.time_phased_set,
l_delete_atp_rec.del_atp_peg_items,
l_delete_atp_rec.del_atp_peg_demands,
l_delete_atp_rec.del_atp_peg_supplies,
l_delete_atp_rec.del_atp_peg_res_reqs,
l_delete_atp_rec.del_demand_source_type,
l_delete_atp_rec.atp_peg_demands_plan_ids,
l_delete_atp_rec.atp_peg_supplies_plan_ids,
l_delete_atp_rec.atp_peg_res_reqs_plan_ids,
l_delete_atp_rec.del_ods_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_demand_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_demand_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_inv_item_ids, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_dem_src_type, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_atp_refresh_no, --3720018, added for support of rescheduling in ODS
l_delete_atp_rec.del_ods_cto_atp_refresh_no --3720018, added for support of rescheduling in ODS
);
DELETE msc_regions_temp
WHERE session_id = p_session_id;
msc_sch_wb.atp_debug('Call_Schedule: ' || 'Rows deleted from msc_regions_temp : '||sql%rowcount);
DELETE msc_atp_src_profile_temp
WHERE session_id = p_session_id;
msc_sch_wb.atp_debug('Call_Schedule: ' || 'Rows deleted from msc_atp_src_profile_temp : '||sql%rowcount);
msc_sch_wb.atp_debug('Call_Schedule: ' || 'Before calling MSC_ATP_PROC.Delete_Copy_Demand');
IF MSC_ATP_PVT.G_SUMMARY_FLAG = 'Y' THEN --bug4291375 Delete_Copy_Demand should be called when summary is ON.
MSC_ATP_DB_UTILS.Delete_Copy_Demand(MSC_ATP_PVT.G_COPY_DEMAND_ID,
MSC_ATP_PVT.G_COPY_DEMAND_PLAN_ID,
G_TIME_PHASED_SET,
l_return_status);
INSERT INTO MRP_ATP_DETAILS_TEMP(
session_id,
identifier1,
identifier2,
identifier3,
record_type,
scenario_id,
summary_flag
)
VALUES(
p_session_id,
G_OFF_INSTANCE_ID(i),
G_OFF_PLAN_ID(i),
G_OFF_DEM_TRANS_ID(i),
3,
1,
'Y'
);
SELECT MAX(supply_demand_date)
INTO l_avail_date
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
AND order_line_id = x_atp_table.identifier(k)
AND parent_pegging_id = end_pegging_id
AND supply_demand_type = 2
AND record_type = 3
AND inventory_item_id = NVL(x_atp_table.match_item_id(k), x_atp_table.inventory_item_id(k));
SELECT NVL(sum(NVL(allocated_quantity, 0)), 0)
INTO l_stealing_qty
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
AND order_line_id = x_atp_table.identifier(k)
AND parent_pegging_id = end_pegging_id
AND char1 IS NOT NULL
AND record_type = 3
AND inventory_item_id = NVL(x_atp_table.match_item_id(k), x_atp_table.inventory_item_id(k));
SELECT p_session_id ||':'||mrp_atp_schedule_temp_s.nextval,
DECODE(x_atp_table.Requested_Ship_Date(k),
NULL, x_atp_table.Requested_Arrival_Date(k),
x_atp_table.Requested_Ship_Date(k)),
identifier3,
identifier2
INTO l_wf_item_key,
l_req_ship_date,
l_exception_rec.demand_id,
l_plan_id
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
AND order_line_id = x_atp_table.identifier(k)
AND inventory_item_id = NVL(x_atp_table.match_item_id(k), x_atp_table.inventory_item_id(k))
AND record_type = 3
AND parent_pegging_id IS NULL;
SELECT identifier3,
identifier2
INTO l_exception_rec.demand_id,
l_plan_id
FROM mrp_atp_details_temp
WHERE session_id = p_session_id
AND order_line_id = x_atp_table.identifier(k)
AND inventory_item_id = NVL(x_atp_table.match_item_id(k), x_atp_table.inventory_item_id(k))
AND record_type = 3
AND parent_pegging_id IS NULL;
Selecting inventory_item_id from msc_system_items*/
BEGIN
-- Bug 3444096, use x_atp_table.match_item_id for match cases in CTO
Select inventory_item_id
into l_inventory_item_id
from msc_system_items
where plan_id = l_plan_id
and sr_instance_id = l_instance_id
and organization_id = x_atp_table.source_organization_id(k)
and sr_inventory_item_id = NVL(x_atp_table.match_item_id(k), x_atp_table.inventory_item_id(k));
msc_sch_wb.atp_debug('Call_Schedule: ' || 'Error occured while selecting inventory_item_id : ' || sqlerrm);
select ltrim(rtrim(substr(value, instr(value,',',-1,1)+1)))
into x_session_loc_des
from v$parameter where name= 'utl_file_dir';
SELECT spid
INTO l_spid
FROM v$process
WHERE addr = (SELECT paddr FROM v$session
WHERE audsid=userenv('SESSIONID'));
SELECT value
INTO x_trace_loc_des
from v$parameter
WHERE name = 'user_dump_dest'; --ATP Debug Workflow
SELECT ltrim(rtrim(M2A_DBLINK))
INTO l_dblink
FROM msc_apps_instance_nodes
WHERE instance_id = p_instance_id and
node_id = p_node_id;
SELECT NVL(l_dblink,ltrim(rtrim(M2A_DBLINK)))
INTO l_dblink
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
SELECT summary_flag, apps_ver
INTO l_summary_flag, MSC_ATP_PVT.G_APPS_VER
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
select profile_value,profile_name
bulk collect into l_profile_value,l_profile_name
from MSC_ATP_SRC_PROFILE_TEMP
where session_id = p_session_id;
SELECT spid
INTO l_spid
FROM v$process
WHERE addr = (SELECT paddr FROM v$session
WHERE audsid=userenv('SESSIONID'));
SELECT ltrim(rtrim(M2A_DBLINK))
INTO l_dblink
FROM msc_apps_instance_nodes
WHERE instance_id = p_instance_id and
node_id = p_node_id;
SELECT NVL(l_dblink,ltrim(rtrim(M2A_DBLINK)))
INTO l_dblink
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
SELECT summary_flag, apps_ver
INTO l_summary_flag, MSC_ATP_PVT.G_APPS_VER
FROM msc_apps_instances
WHERE instance_id = p_instance_id;
msc_sch_wb.atp_debug('Call_Schedule_Remote: ' || 'Site is using backlog workbench, update tables and turnoff ODS summary');
update msc_apps_instances
set summary_flag = 200;
SELECT seq_num
INTO l_seq_num
FROM msc_calendar_dates
WHERE calendar_code = p_calendar_code
AND seq_num is not null --This date will always be there only case is if ITF is non working day.
AND calendar_date = trunc(p_calendar_date)
AND sr_instance_id = p_instance_id
AND exception_set_id = -1;
SELECT calendar_date
INTO l_date
FROM msc_calendar_dates
WHERE calendar_code = p_calendar_code
AND seq_num = p_seq_num --Seq should always be there if calendar is build till ITF.
AND sr_instance_id = p_instance_id
AND exception_set_id = -1;