[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_Custom_Information(p_atp_rec IN MRP_ATP_PUB.ATP_Rec_Typ,
p_session_id IN NUMBER
)
IS
i number;
msc_sch_wb.atp_debug('Enter Update_Custom_Information');
update mrp_atp_schedule_temp
Set
inventory_item_name = p_atp_rec.inventory_item_name(i),
source_organization_id = p_atp_rec.source_organization_id(i),
source_organization_code = p_atp_rec.source_organization_code(i),
delivery_lead_time = p_atp_rec.delivery_lead_time(i),
freight_carrier = p_atp_rec.freight_carrier(i),
ship_method = p_atp_rec.ship_method(i),
scheduled_ship_date = p_atp_rec.ship_date(i), -- different
available_quantity = p_atp_rec.available_quantity(i),
requested_date_quantity = p_atp_rec.requested_date_quantity(i),
group_ship_date = p_atp_rec.group_ship_date(i),
group_arrival_date = p_atp_rec.group_arrival_date(i),
error_code = p_atp_rec.error_code(i),
end_pegging_id = p_atp_rec.end_pegging_id(i),
scheduled_arrival_date = p_atp_rec.arrival_date(i),
request_item_id = p_atp_rec.request_item_id(i),
request_item_name = p_atp_rec.request_item_name(i),
req_item_req_date_qty = p_atp_rec.req_item_req_date_qty(i),
req_item_available_date_qty = p_atp_rec.req_item_available_date_qty(i),
req_item_available_date = p_atp_rec.req_item_available_date(i),
sales_rep = p_atp_rec.sales_rep(i),
customer_contact = p_atp_rec.customer_contact(i)
WHERE session_id = p_session_id
and order_line_id = p_atp_rec.identifier(i)
--same line id may be shared by different items in case of ATO. So we add followig condition
and inventory_item_id = p_atp_rec.inventory_item_id(i);
msc_sch_wb.atp_debug('Number of rows update := ' ||SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Exit Update_Custom_Information');
msc_sch_wb.atp_debug('Error Orrured in Update_Custom_Information := ' || SQLERRM);
END Update_Custom_Information;
select wf.name
from ra_site_uses_all rsua,
wf_roles wf,
fnd_user fnd,
ra_contacts ra
where ra.customer_id = p_cust_id
and rsua.site_use_id = p_cust_site_id
and ra.address_id = rsua.address_id
and ra.contact_id = fnd.customer_id
and fnd.start_date <= sysdate
and ( fnd.end_date IS NULL OR fnd.end_date >= trunc(sysdate))
and wf.orig_system = 'FND_USR'
and wf.orig_system_id = fnd.user_id
and wf.STATUS = 'ACTIVE';
select wf.name
from wf_roles wf,
fnd_user fnd,
hz_cust_account_roles hcar
where hcar.cust_account_id = p_cust_id
and hcar.cust_acct_site_id = p_cust_site_id
and hcar.cust_account_role_id = fnd.customer_id
and fnd.start_date <= trunc(sysdate)
and ( fnd.end_date IS NULL OR fnd.end_date >= trunc(sysdate))
and wf.orig_system = 'FND_USR'
and wf.orig_system_id = fnd.user_id
and wf.STATUS = 'ACTIVE';
select min(wf.name)
into l_sales_rep
from wf_roles wf,
oe_order_lines_all oe
where oe.line_id = p_atp_rec.identifier(i)
and wf.orig_system= 'PER'
and wf.orig_system_id= oe.salesrep_id
and wf.status='ACTIVE';
insert into msc_oe_data_temp
(
seq_id,
order_line_id,
oe_flag,
internal_org_id,
session_id
)
values
(
msc.msc_oe_data_temp_s.NEXTVAL,
l_atp_rec.identifier(i),
decode(l_atp_rec.oe_flag(i),'Y',(Select decode(MSC_ATP_PVT.G_INV_CTP, 5, l_atp_rec.OE_FLAG(i),
decode( prha.interface_source_code, 'MRP', 'Y', 'MSC', 'Y','CTO', 'Y', 'CTO-LOWER LEVEL', 'Y', 'N')) --4889943
from po_requisition_headers_all prha
where prha.requisition_header_id = l_atp_rec.attribute_01(i))), --5008194/FP 5054154
decode(l_atp_rec.oe_flag(i),'Y', (Select po.destination_organization_id
from po_requisition_lines_all po,
oe_order_lines_all oe
where oe.source_document_line_id = po.requisition_line_id
and oe.line_id = l_atp_rec.identifier(i)),NULL), --5008194/FP 5054154
p_session_id
);
msc_sch_wb.atp_debug('Call_ATP: Inserted data into msc_oe_data_temp');
msc_sch_wb.atp_debug('Records selected in mrp_oe_data_temp : ' || SQL%ROWCOUNT);
select oe_flag,internal_org_id,seq_id
bulk collect into
l_atp_rec.oe_flag,
l_atp_rec.internal_org_id,
l_atp_rec.attribute_11
from msc_oe_data_temp
where session_id = p_session_id
order by seq_id;
msc_sch_wb.atp_debug('Call_ATP: selected data back into the l_atp_rec');
SELECT assignment_set_id
INTO l_assign_set_id
FROM msc_apps_instances
WHERE instance_id = l_instance_id;
SELECT instance_id
INTO l_instance_id
FROM mrp_ap_apps_instances;
SELECT instance_id, a2m_dblink
INTO l_instance_id, l_a2m_dblink
FROM mrp_ap_apps_instances;
SELECT mrp_ap_refresh_s.nextval
INTO l_refresh_number
FROM dual;
DELETE msc_regions_temp
WHERE session_id = p_session_id;
msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || '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_ATP_No_Commit: ' || 'Rows deleted from msc_atp_src_profile_temp : '||sql%rowcount);
msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'ATO Delete Flag Case - setting G_CTO_FLAG to 3');
/* SELECT mrp_ap_refresh_s.nextval
INTO l_refresh_number
FROM dual;
SELECT count(*)
into l_rac_count
from gv$instance;
MSC_ATP_PUB.Update_Custom_Information(l_atp_rec_temp,
p_session_id);
msc_sch_wb.atp_debug('Call_ATP_No_Commit: ' || 'Extending ATO_Delete Flag');
x_atp_rec.ato_delete_flag := p_atp_rec.ato_delete_flag;
select summary_flag
into l_summary_flag
from mrp_atp_details_temp
where session_id = p_session_id
and summary_flag = 'Y'
and record_type = 3 --record_type check added for performance
and rownum = 1;
SELECT DECODE( NVL(l_summary_flag, 'N'), 'Y', mrp_ap_refresh_s.nextval, (NVL(lrn, -1) +1))
INTO l_end_refresh_number
FROM mrp_ap_apps_instances;
plsql_block := 'BEGIN MSC_ATP_PUB.UPDATE_TABLES'
||'@'||l_a2m_dblink||'(
:p_summary_flag,
:p_end_refresh_number,
:p_refresh_number,
:p_session_id);
UPDATE_TABLES(l_summary_flag,l_end_refresh_number,l_refresh_number,p_session_id);
delete from msc_regions_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('Rows deleted from msc_regions_temp:'|| sql%rowcount);
delete from msc_atp_src_profile_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('Rows deleted from msc_atp_src_profile_temp:'|| sql%rowcount);
delete from msc_regions_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('Rows deleted from msc_regions_temp:'|| sql%rowcount);
delete from msc_atp_src_profile_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('Rows deleted from msc_atp_src_profile_temp:'|| sql%rowcount);
NULL, -- G_DB_PROFILE, dsting insert into source not dest
p_session_id,
x_atp_rec,
x_atp_supply_demand,
x_atp_period,
x_atp_details,
MSC_ATP_UTILS.REQUEST_MODE,
x_return_status,
x_msg_data,
x_msg_count);
delete from msc_regions_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('Rows deleted from msc_regions_temp:'|| sql%rowcount);
delete from msc_atp_src_profile_temp where session_id = p_session_id;
msc_sch_wb.atp_debug('Rows deleted from msc_atp_src_profile_temp:'|| sql%rowcount);
NULL, -- G_DB_PROFILE, dsting insert into src not dest
p_session_id,
x_atp_rec,
x_atp_supply_demand,
x_atp_period,
x_atp_details,
MSC_ATP_UTILS.REQUEST_MODE,
x_return_status,
x_msg_data,
x_msg_count);
PROCEDURE UPDATE_TABLES (p_summary_flag IN VARCHAR2,
p_end_refresh_number IN NUMBER ,
p_refresh_number IN NUMBER ,
p_session_id IN NUMBER)
IS
l_end_refresh_number NUMBER := NULL;
msc_sch_wb.atp_debug('Inside Procedure UPDATE_TABLES');
msc_sch_wb.atp_debug('UPDATE_TABLES : p_end_refresh_number ' || p_end_refresh_number);
msc_sch_wb.atp_debug('UPDATE_TABLES : p_refresh_number ' || p_refresh_number);
msc_sch_wb.atp_debug('UPDATE_TABLES : p_summary_flag ' || p_summary_flag);
msc_sch_wb.atp_debug('UPDATE_TABLES : p_session_id ' || p_session_id);
select max(decode(p.summary_flag,
MSC_POST_PRO.G_SF_NET_SUMMARY_RUNNING, p_refresh_number+1,
p.latest_refresh_number))
into l_end_refresh_number
from msc_plans p,
msc_demands d,
mrp_atp_details_temp madt
where d.plan_id = p.plan_id
and d.refresh_number = p_refresh_number
and d.plan_id = madt.identifier2
and d.demand_id = madt.identifier3
and d.sr_instance_id = madt.identifier1
and madt.session_id = p_session_id
-- and madt.supply_demand_type = 1
-- Bug 3629191
-- Supply_demand_type check is removed as it will filter out record in Unscheduling case.
-- It will not fetch unwanted records (supply etc) as there is a filter on refresh number.
-- Sql performance will be same after removing the check
and madt.record_type = 3
and NVL(madt.identifier2, -1) > 0
and madt.identifier3 is not NULL
and madt.identifier1 is not NULL;
msc_sch_wb.atp_debug('No of rows selected ' || SQL%ROWCOUNT );
msc_sch_wb.atp_debug('Update 5 tables');
SELECT identifier2,
identifier3,
identifier1
BULK COLLECT INTO
l_identifier2,
l_identifier3,
l_identifier1
FROM MRP_ATP_DETAILS_TEMP
where session_id = p_session_id
and record_type = 3
and NVL(identifier2, -1) > 0
and identifier3 is not NULL
and identifier1 is not NULL;
msc_sch_wb.atp_debug('No of rows selected by BULK SELECT ' || SQL%ROWCOUNT );
update msc_demands
set refresh_number = p_end_refresh_number
where refresh_number = p_refresh_number
and plan_id = l_identifier2(i)
and demand_id = l_identifier3(i)
and sr_instance_id = l_identifier1(i);
msc_sch_wb.atp_debug('No of rows updated:msc_demands ' || SQL%ROWCOUNT );
update msc_supplies
set refresh_number = p_end_refresh_number
where refresh_number = p_refresh_number
and plan_id = l_identifier2(i)
and transaction_id = l_identifier3(i)
and sr_instance_id = l_identifier1(i);
msc_sch_wb.atp_debug('No of rows updated: msc_supplies ' || SQL%ROWCOUNT );
update msc_resource_requirements
set refresh_number = p_end_refresh_number
where refresh_number = p_refresh_number
and plan_id = l_identifier2(i)
and transaction_id = l_identifier3(i)
and sr_instance_id = l_identifier1(i);
msc_sch_wb.atp_debug('No of rows updated: msc_resource_requirements ' || SQL%ROWCOUNT );
update msc_alloc_demands
set refresh_number = p_end_refresh_number
where refresh_number = p_refresh_number
and plan_id = l_identifier2(i)
and parent_demand_id = l_identifier3(i)
and sr_instance_id = l_identifier1(i);
msc_sch_wb.atp_debug('No of rows updated: msc_alloc_demands ' || SQL%ROWCOUNT );
update msc_alloc_supplies
set refresh_number = p_end_refresh_number
where refresh_number = p_refresh_number
and plan_id = l_identifier2(i)
and parent_transaction_id = l_identifier3(i)
and sr_instance_id = l_identifier1(i);
msc_sch_wb.atp_debug('No of rows updated: msc_alloc_supplies ' || SQL%ROWCOUNT );
msc_sch_wb.atp_debug('Update 1 Table');
update msc_demands
set refresh_number = p_end_refresh_number
--we need to update POD for model components
where origination_type in (6,30,1)
--refresh number will be populated either for SO or POD ofmodel entities
and refresh_number is not null
and (plan_id, demand_id, sr_instance_id) in
( select identifier2,
identifier3,
identifier1
from mrp_atp_details_temp
where session_id = p_session_id
and supply_demand_type = 1
and record_type = 3
and NVL(identifier2, -1) > 0
and identifier3 is not NULL
and identifier1 is not NULL
);
msc_sch_wb.atp_debug('No of rows updated: msc_demands ' || SQL%ROWCOUNT );
END UPDATE_TABLES;