[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Sync_flag (
p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
p_old_plan_id IN number,
x_return_status OUT NOCOPY varchar2
);
PROCEDURE Update_Summary_Flag (
p_plan_id IN number,
p_status IN number,
x_return_status OUT NOCOPY varchar2
);
select plan_id
into l_new_plan_id
from msc_plans
where copy_plan_id = p_old_plan_id;
Update_Summary_Flag ( p_new_plan_id,
G_SF_SYNC_RUNNING,
l_return_status);
conc_log (' Cannot update ATP Summary status flag.');
G_TFS_ERROR := 'Unable to update Plan Information';
select MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
into l_session_id
from dual;
select hsecs / 100
into l_downtime_start
from v$timer;
Update_Sync_flag ( l_atp_rec,
p_old_plan_id,
l_return_status
);
conc_log ('Unable to update sync flag ');
G_TFS_ERROR := 'Unable to update Plan Information';
select hsecs / 100
into l_downtime_end
from v$timer;
select MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
into l_session_id
from dual;
Update_Sync_flag ( l_atp_rec,
p_old_plan_id,
l_return_status
);
conc_log ('Unable to update sync flag ');
select MRP_ATP_SCHEDULE_TEMP_S.NEXTVAL
into l_session_id
from dual;
select hsecs / 100
into l_timer_start
from v$timer;
select hsecs / 100
into l_timer_end
from v$timer;
select
col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col18,
col19,
col20
bulk collect into
x_atp_rec.calling_module,
x_atp_rec.customer_id,
x_atp_rec.customer_site_id,
x_atp_rec.inventory_item_id,
x_atp_rec.source_organization_id,
x_atp_rec.quantity_ordered,
x_atp_rec.quantity_UOM,
x_atp_rec.requested_ship_date,
x_atp_rec.demand_class,
x_atp_rec.override_flag,
x_atp_rec.action,
x_atp_rec.instance_id,
x_atp_rec.identifier, -- SO Line ID
x_atp_rec.substitution_typ_code,
x_atp_rec.attribute_04, -- Refresh Number
x_atp_rec.delivery_lead_time,
x_atp_rec.attribute_08, -- Order Number
x_atp_rec.old_source_organization_id,
x_atp_rec.old_demand_class,
x_atp_rec.attribute_03 -- Temporarily store Demand_ID
from (
select
660 col1,
CUST_VIEW.sr_tp_id col2,
CUST_SITE_VIEW.sr_tp_site_id col3,
msi.sr_inventory_item_id col4,
md.organization_id col5,
md.using_requirement_quantity col6,
msi.uom_code col7,
md.using_assembly_demand_date col8,
md.demand_class col9,
'Y' col10, -- override flag
120 col11, -- rescheudle
md.sr_instance_id col12,
md.sales_order_line_id col13,
4 col14,
md.refresh_number col15,
0 col16,
SUBSTR(md.order_number,1,30) col17,
md.organization_id col18,
md.demand_class col19,
md.demand_id col20
from msc_demands md,
-- Inline view for Customers
( select mtil.sr_instance_id sr_instance_id,
mtil.tp_id tp_id,
max(mtil.sr_tp_id) sr_tp_id
from msc_tp_id_lid mtil
where mtil.partner_type = 2
and mtil.sr_company_id = -1
group by mtil.sr_instance_id, mtil.tp_id
) CUST_VIEW,
( select mtsil.sr_instance_id sr_instance_id,
mtsil.tp_site_id tp_site_id,
max(mtsil.sr_tp_site_id) sr_tp_site_id
from msc_tp_site_id_lid mtsil
where mtsil.partner_type = 2
and mtsil.sr_company_id = -1
group by mtsil.tp_site_id, mtsil.sr_instance_id
) CUST_SITE_VIEW,
msc_system_items msi
where md.plan_id = p_old_plan_id
and md.origination_type in (6,30)
and (md.demand_id, md.sr_instance_id) in (
(
-- Sales orders in old plan not in New plan
select max (oldp.demand_id),
oldp.sr_instance_id
from msc_demands oldp,
msc_plan_refreshes mpr
where oldp.plan_id = p_old_plan_id
and oldp.origination_type in (6,30)
and NVL(oldp.atp_synchronization_flag,-1) <> 1
and mpr.plan_id = p_new_plan_id
and oldp.refresh_number is not NULL
and oldp.refresh_number < mpr.apps_lrn
and oldp.sr_instance_id = mpr.sr_instance_id
and not exists (
select sales_order_line_id
from msc_demands md1
where md1.plan_id = p_new_plan_id
and md1.origination_type in (6,30)
and md1.sr_instance_id = oldp.sr_instance_id
and md1.sales_order_line_id = oldp.sales_order_line_id
and md1.using_assembly_item_id = oldp.using_assembly_item_id
)
group by oldp.sales_order_line_id,
oldp.using_assembly_item_id,
oldp.sr_instance_id
)
UNION
(
-- Demands in both plans but have been rescheduled
select max(oldp.demand_id),
oldp.sr_instance_id
from msc_demands oldp,
msc_demands newp
where oldp.plan_id = p_old_plan_id
and newp.plan_id = p_new_plan_id
and oldp.origination_type in (6,30)
and newp.origination_type in (6,30)
and oldp.sr_instance_id = newp.sr_instance_id
and oldp.sales_order_line_id = newp.sales_order_line_id
and oldp.using_assembly_item_id = newp.using_assembly_item_id
and oldp.demand_id = (
select max (md1.demand_id)
from msc_demands md1,
msc_plan_refreshes mpr1
where md1.origination_type in (6,30)
and md1.plan_id = p_old_plan_id
and md1.sr_instance_id = oldp.sr_instance_id
and NVL(md1.atp_synchronization_flag,-1) <> 1
and md1.refresh_number is not NULL
and md1.refresh_number < mpr1.apps_lrn
and md1.sr_instance_id = mpr1.sr_instance_id
and mpr1.plan_id = p_new_plan_id
and md1.sales_order_line_id = oldp.sales_order_line_id
and md1.using_assembly_item_id = oldp.using_assembly_item_id
)
and newp.demand_id = (
-- select demand id for a particular sales order line ID
select max (md2.demand_id)
from msc_demands md2
where md2.origination_type in (6,30)
and md2.plan_id = p_new_plan_id
and md2.sales_order_line_id = oldp.sales_order_line_id
and md2.using_assembly_item_id = oldp.using_assembly_item_id
and md2.sr_instance_id = oldp.sr_instance_id
)
-- Main check to see if order rescheduled goes here.
and ( oldp.demand_class <> newp.demand_class
or oldp.using_requirement_quantity <> newp.using_requirement_quantity
or oldp.organization_id <> newp.organization_id
or TRUNC ( NVL(oldp.old_demand_date, oldp.using_assembly_demand_date))
<>
TRUNC( NVL(newp.old_demand_date, newp.using_assembly_demand_date))
)
group by oldp.sales_order_line_id, oldp.using_assembly_item_id,
oldp.sr_instance_id
) -- Union
) -- Demand_ID in
and md.customer_id = CUST_VIEW.tp_id (+)
and md.sr_instance_id = CUST_VIEW.sr_instance_id (+)
and md.ship_to_site_id = CUST_SITE_VIEW.tp_site_id (+)
and md.sr_instance_id = CUST_SITE_VIEW.sr_instance_id (+)
and md.using_assembly_item_id = msi.inventory_item_id
and md.sr_instance_id = msi.sr_instance_id
and msi.plan_id = -1
and md.organization_id = msi.organization_id
and not exists (
select demand_source_header_id
from msc_sales_orders mso
where mso.sr_instance_id = md.sr_instance_id
and mso.demand_source_line = to_char(md.sales_order_line_id)
and mso.reservation_type = 1
and mso.inventory_item_id = md.using_assembly_item_id
and mso.completed_quantity > 0
)
order by md.last_update_date
); --select BULK COLLECT from
select
col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10,
col11,
col12,
col13,
col14,
col15,
col16,
col17,
col18,
col19,
col20,
-- Plan by request date changes
col21,
col22,
-- CTO Re-arch changes
col23,
col24,
col25,
col26,
col27, -- ATP Flag passed as Y always.
col28, -- Use session id for CTO Re-arch
col29, -- Ship Set Name
col30, -- Arrival Set Name
--col31, -- Insert FLag
col31, -- Insert FLag bug3330835
col32 -- bug 8473835
bulk collect into
x_atp_rec.calling_module,
x_atp_rec.customer_id,
x_atp_rec.customer_site_id,
x_atp_rec.inventory_item_id,
x_atp_rec.source_organization_id,
x_atp_rec.quantity_ordered,
x_atp_rec.quantity_UOM,
x_atp_rec.requested_ship_date,
x_atp_rec.demand_class,
x_atp_rec.override_flag,
x_atp_rec.action,
x_atp_rec.instance_id,
x_atp_rec.identifier, -- SO Line ID
x_atp_rec.substitution_typ_code,
x_atp_rec.attribute_04, -- Refresh Number
x_atp_rec.delivery_lead_time,
x_atp_rec.attribute_08, -- Order number
x_atp_rec.old_source_organization_id,
x_atp_rec.old_demand_class,
x_atp_rec.attribute_03, -- demand ID
-- Plan by request date changes
x_atp_rec.original_request_date, --original request date
x_atp_rec.requested_arrival_date,
-- CTO Re-arch changes
x_atp_rec.ATO_Model_Line_Id,
x_atp_rec.Top_Model_line_id,
x_atp_rec.ATO_Parent_Model_Line_Id,
x_atp_rec.Parent_line_id,
x_atp_rec.attribute_06, -- ATP Flag passed as Y always.
x_atp_rec.attribute_11, -- Use session id for CTO Re-arch
x_atp_rec.ship_set_name, -- Ship Set Name
x_atp_rec.arrival_set_name, -- Arrival Set Name
x_atp_rec.insert_flag, -- Insert Flag
x_atp_rec.demand_source_type -- bug 8473835
from (
select
660 col1,
NVL(CUST_VIEW.sr_tp_id, -999) col2,
NVL(CUST_SITE_VIEW.sr_tp_site_id, -999) col3,
msi.sr_inventory_item_id col4,
md.organization_id col5,
md.using_requirement_quantity col6,
msi.uom_code col7,
-- md.using_assembly_demand_date col8,
decode(order_date_type_code,
2, NULL,
NVL(md.schedule_ship_date,
md.using_assembly_demand_date)) col8, --plan by request Date, Promise Date Scheduled Date
md.demand_class col9,
'Y' col10, -- override flag
120 col11, -- rescheudle
md.sr_instance_id col12,
md.sales_order_line_id col13,
4 col14,
md.refresh_number col15,
md.intransit_lead_time col16,
--0 col16,
SUBSTR(md.order_number,1,30) col17,
md.organization_id col18,
md.demand_class col19,
md.demand_id col20,
decode(order_date_type_code,
1, md.request_ship_date,
md.request_date) col21, --added so that original request date is not lost
decode(order_date_type_code,
2, md.SCHEDULE_ARRIVAL_DATE,
NULL) col22, --plan by request Date, Promise Date Scheduled Date
-- CTO Re-arch changes
md.ato_line_id col23,
md.top_model_line_id col24,
md.parent_model_line_id col25,
md.link_to_line_id col26,
'Y' col27, -- ATP Flag passed as Y always.
md.atp_session_id col28, -- Use session id for CTO Re-arch
md.ship_set_name col29, -- Ship Set Name
md.arrival_set_name col30, -- Arrival Set Name
0 col31, -- Insert Flag
md.demand_source_type col32 -- bug 8473835
from msc_demands md,
-- Inline view for Customers
( select mtil.sr_instance_id sr_instance_id,
mtil.tp_id tp_id,
max(mtil.sr_tp_id) sr_tp_id
from msc_tp_id_lid mtil
where mtil.partner_type = 2
and mtil.sr_company_id = -1
group by mtil.sr_instance_id, mtil.tp_id
) CUST_VIEW,
( select mtsil.sr_instance_id sr_instance_id,
mtsil.tp_site_id tp_site_id,
max(mtsil.sr_tp_site_id) sr_tp_site_id
from msc_tp_site_id_lid mtsil
where mtsil.partner_type = 2
and mtsil.sr_company_id = -1
group by mtsil.sr_instance_id, mtsil.tp_site_id
) CUST_SITE_VIEW,
msc_system_items msi
where md.plan_id = p_old_plan_id
and md.origination_type in (6,30)
and (md.demand_id, md.sr_instance_id) in (
select max (md1.demand_id),
md1.sr_instance_id
from msc_demands md1,
msc_plan_refreshes mpr,
msc_plan_organizations mpo
where md1.plan_id = p_old_plan_id
and md1.origination_type in (6,30)
and mpr.plan_id = p_new_plan_id
and mpr.plan_id = mpo.plan_id
and md1.sr_instance_id = mpo.sr_instance_id
and md1.organization_id = mpo.organization_id
and md1.refresh_number > nvl(mpo.so_lrn, mpr.apps_lrn) -- Verify if mpo.so_lrn will always be populated
and md1.atp_synchronization_flag = 0
-- Removed inventory_item_id from group by to handle product substitution across re-scheduling of SO
--group by md1.sales_order_line_id, md1.inventory_item_id, md1.sr_instance_id
group by md1.sales_order_line_id, md1.sr_instance_id
)
and md.customer_id = CUST_VIEW.tp_id (+)
and md.sr_instance_id = CUST_VIEW.sr_instance_id (+)
and md.ship_to_site_id = CUST_SITE_VIEW.tp_site_id (+)
and md.sr_instance_id = CUST_SITE_VIEW.sr_instance_id (+)
and md.using_assembly_item_id = msi.inventory_item_id
and md.sr_instance_id = msi.sr_instance_id
and msi.plan_id = -1
and md.organization_id = msi.organization_id
order by md.last_update_date
);
INSERT INTO msc_cto_bom (
inventory_item_id,
line_id,
top_model_line_id,
ato_parent_model_line_id,
ato_model_line_id,
match_item_id,
wip_supply_type,
session_id,
bom_item_type,
quantity,
parent_line_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
sr_instance_id,
sr_inventory_item_id,
refresh_number,
plan_id)
/*
Changed to use msc_cto_bom instead to handle following cases:
1. In case ATP used model's ITF to promise, demands for lower components may not exist.
2. In "BUY" cases, demand for lower components may not exist for entire qty.
*/
SELECT distinct
inventory_item_id,
line_id,
top_model_line_id,
ato_parent_model_line_id,
ato_model_line_id,
match_item_id,
wip_supply_type,
p_session_id,
bom_item_type,
quantity,
parent_line_id,
G_FND_USER,
l_sysdate,
G_FND_USER,
l_sysdate,
sr_instance_id,
sr_inventory_item_id,
refresh_number,
NULL -- Plan ID
FROM msc_cto_bom
WHERE ato_model_line_id = x_atp_rec.ATO_Model_Line_Id(i)
AND session_id = x_atp_rec.attribute_11(i)
AND plan_id = p_old_plan_id
AND x_atp_rec.quantity_ordered(i) > 0
AND sr_instance_id = x_atp_rec.instance_id(i);
conc_debug ('No. of ATO Component records inserted: ' || SQL%ROWCOUNT);
INSERT INTO msc_cto_sources (
line_id,
organization_id,
supplier_id,
status_flag,
inventory_item_id,
sr_instance_id,
ato_line_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
supplier_site_code,
make_flag,
refresh_number,
plan_id,
session_id)
SELECT line_id,
organization_id,
supplier_id,
status_flag,
inventory_item_id,
sr_instance_id,
ato_line_id,
l_sysdate,
G_FND_USER,
G_FND_USER,
l_sysdate,
supplier_site_code,
make_flag,
refresh_number,
NULL, -- Plan ID
p_session_id
FROM msc_cto_sources
WHERE ato_line_id = x_atp_rec.ATO_Model_Line_Id(i)
AND session_id = x_atp_rec.attribute_11(i)
AND plan_id = p_old_plan_id
AND x_atp_rec.quantity_ordered(i) > 0
AND sr_instance_id = x_atp_rec.instance_id(i);
conc_debug ('No. of OSS Sourcing records inserted: ' || SQL%ROWCOUNT);
PROCEDURE Update_Sync_flag (
p_atp_table IN MRP_ATP_PUB.ATP_Rec_Typ,
p_old_plan_id IN number,
x_return_status OUT NOCOPY varchar2
) IS
l_counter number;
conc_debug ('----Update Sync Flag ----');
update msc_demands
set atp_synchronization_flag = 1,
last_updated_by = G_FND_USER,
last_update_login = G_FND_USER,
last_update_date = l_sysdate
where plan_id = p_old_plan_id
and sr_instance_id = p_atp_table.instance_id(l_counter)
and origination_type = 30
and sales_order_line_id = p_atp_table.identifier(l_counter)
and refresh_number <= p_atp_table.attribute_04(l_counter)
and demand_id <= p_atp_table.attribute_03(l_counter)
and organization_id = p_atp_table.source_organization_id(l_counter)
and NVL(atp_synchronization_flag, -1) <> 1;
conc_debug ('Rows Updated : ' || sql%rowcount);
/* Not Needed as component records aren't selected from msc_demands
-- CTO Re-arch changes, need to update demands for ATO Model's options/ OC's
conc_debug ('Updating CTO Component Demands');
update msc_demands
set atp_synchronization_flag = 1,
last_updated_by = G_FND_USER,
last_update_login = G_FND_USER,
last_update_date = l_sysdate
where plan_id = p_old_plan_id
and sr_instance_id = p_atp_table.instance_id(l_counter)
and ato_line_id = p_atp_table.identifier(l_counter)
and refresh_number <= p_atp_table.attribute_04(l_counter)
and p_atp_table.identifier(l_counter) <> p_atp_table.ato_model_line_id(l_counter)
and NVL(atp_synchronization_flag, -1) <> 1;
conc_debug ('Rows Updated : ' || sql%rowcount);
conc_log ('Error in Update Sync Flag');
END Update_Sync_flag;
/* update msc_plans
set plan_completion_date = NULL
where plan_id = p_old_plan_id;
Update_Summary_Flag ( P_new_plan_id,
G_SF_SYNC_DOWNTIME,
x_return_status
);
SELECT so_lrn, sr_instance_id, organization_id
BULK COLLECT INTO l_mpo_so_lrn, l_mpo_sr_instance_id, l_mpo_org_id
FROM msc_plan_organizations
WHERE plan_id = p_new_plan_id;
UPDATE msc_plan_organizations
SET so_lrn = l_mpo_so_lrn(j)
WHERE sr_instance_id = l_mpo_sr_instance_id(j)
AND organization_id = l_mpo_org_id(j)
AND plan_id = p_new_plan_id;
Update_Summary_Flag (p_new_plan_id, G_SF_SYNC_SUCCESS, x_return_status);
Update_Summary_Flag ( p_new_plan_id,
G_SF_SYNC_ERROR,
l_return_status
);
conc_log ('ATP Status Flag Update : ' || l_return_status);
PROCEDURE Update_Summary_Flag (
p_plan_id IN number,
p_status IN number,
x_return_status OUT NOCOPY varchar2
) IS
BEGIN
conc_debug ('------- Update_Summary_Flag -----');
update msc_plans
set summary_flag = p_status
where plan_id = p_plan_id;
conc_debug ('Cannot Update. Error : ' || sqlerrm);
END Update_Summary_Flag;
p_atp_tab.Insert_Flag.Extend(p_size);
p_atp_tab.ato_delete_flag.Extend(p_size);
select apps_lrn
into x_refresh_number
from msc_plan_refreshes
where plan_id = p_plan_id
and sr_instance_id = p_instance_id;
SELECT a.oracle_username
FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
WHERE a.oracle_id = b.oracle_id
AND b.application_id= 724;
SELECT 1
FROM all_objects
WHERE object_name like 'MSC_ATP_PLAN_SN'
AND owner = lv_msc_schema;