[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_atp_count
from mrp_atp_schedule_temp
where session_id = p_session_id
and order_line_id = NVL(ato_model_line_id, order_line_id)
--bug 3378648
and status_flag in (99,4) --4658238
---Bug 3687934
--- GOP for non-atpable items: We go to destination if source organization is not provided.
and (NVL(atp_flag, 'N') <> 'N' or atp_components_flag <> 'N' or source_organization_id is null);
select distinct
mast.order_line_id,
mast.Parent_line_id,
mast.ATO_Model_Line_Id,
mast.Top_Model_line_id,
mast.inventory_item_id,
mast.Component_Code,
mast.Component_Sequence_ID,
mast.validation_org,
mast.Quantity_Ordered,
mast.UOM_CODE,
-- 3555026: pass source organization id to CTO only when call comes from SO pad
decode(NVL(mast.calling_module, -1), -1, null, mast.source_organization_id)
bulk collect into
l_cto_lines_for_match.line_id,
l_cto_lines_for_match.LINK_TO_LINE_ID,
l_cto_lines_for_match.ATO_LINE_ID,
l_cto_lines_for_match.TOP_MODEL_LINE_ID,
l_cto_lines_for_match.INVENTORY_ITEM_ID,
l_cto_lines_for_match.COMPONENT_CODE,
l_cto_lines_for_match.COMPONENT_SEQUENCE_ID ,
l_cto_lines_for_match.VALIDATION_ORG,
l_cto_lines_for_match.ORDERED_QUANTITY,
l_cto_lines_for_match.ORDER_QUANTITY_UOM,
--pass source org to CTO
l_cto_lines_for_match.SHIP_FROM_ORG_ID
from mrp_atp_schedule_temp mast
where Session_id = p_session_id and
--bug 3378648: Look only at ATP inserted data
status_flag in (99,4) and --4658238
Ato_model_line_id in
(select mast_1.ato_model_line_id from
mrp_atp_schedule_temp mast_1
where mast_1.session_id = p_session_id
--bug 3378648
and status_flag in (99,4) --4658238
and mast_1.order_line_id = mast_1.ato_model_line_id
and (mast_1.atp_flag <> 'N' or mast_1.atp_components_flag <> 'N')
and mast_1.QUANTITY_ORDERED > 0)
order by mast.order_line_id; --required by cto fix 5971615
UPDATE mrp_atp_schedule_temp
SET ATO_Parent_Model_Line_Id = l_cto_lines_for_match.gop_parent_ato_line_id(i),
match_item_id = l_cto_lines_for_match.config_item_id(i),
wip_supply_type = l_cto_lines_for_match.wip_supply_type(i),
oss_error_code = l_cto_lines_for_match.oss_error_code(i),
error_code = l_cto_lines_for_match.oss_error_code(i)
WHERE session_id = p_session_id
--bug 3378648:
and status_flag in (99,4) --4658238
and order_line_id = l_cto_lines_for_match.line_id(i);
msc_sch_wb.atp_debug(' After Update of CTO data');
msc_sch_wb.atp_debug('Lines updated := ' || SQL%ROWCOUNT);
update msc_cto_sources
set status_flag = 2
where ato_line_id in (select order_line_id
from mrp_atp_schedule_temp
where session_id = p_session_id
and order_line_id = ato_model_line_id);
msc_sch_wb.atp_debug('Number of rows updated := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Delete CTO Sources locally');
delete msc_cto_sources
where line_id in (select order_line_id
from mrp_atp_schedule_temp
where session_id = p_session_id
and order_line_id = ato_model_line_id);
msc_sch_wb.atp_debug('Number of rows deleted := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Update CTO Sources across DB');
l_sql_stmt := 'Update msc_cto_sources' || l_dblink;
|| ' where ato_line_id in (select order_line_id '
|| ' from mrp_atp_schedule_temp '
|| ' where session_id = :p_session_id '
|| ' and order_line_id = ato_model_line_id)';
msc_sch_wb.atp_debug('Number of rows updated := ' || SQL%ROWCOUNT);
msc_sch_wb.atp_debug('Now Insert New data');
msc_sch_wb.atp_debug('Now Insert New data in Local Tbale');
insert into msc_cto_sources
(line_id,
inventory_item_id,
organization_id,
supplier_id,
supplier_site_code,
status_flag,
sr_instance_id,
ato_line_id,
make_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
refresh_number,
session_id)
values
( p_cto_sources.line_id(i),
p_cto_sources.Inventory_item_id(i),
p_cto_sources.Org_id(i),
p_cto_sources.Vendor_id(i),
p_cto_sources.Vendor_site(i),
1,
p_instance_id,
p_cto_sources.ato_line_id(i),
p_cto_sources.make_flag(i),
l_user_id,
l_sysdate,
l_user_id,
l_sysdate,
MSC_ATP_PVT.G_REFRESH_NUMBER,
p_session_id);
msc_sch_wb.atp_debug('After Insert New data in Local Table');
msc_sch_wb.atp_debug('Number of rows inserted := ' || SQL%ROWCOUNT);
l_sql_stmt := 'Insert into msc_cto_sources' || l_dblink;
CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE,
MAKE_FLAG, refresh_number, session_id)
Select LINE_ID, ORGANIZATION_ID, SUPPLIER_ID,
SUPPLIER_SITE_CODE, STATUS_FLAG, INVENTORY_ITEM_ID,
SR_INSTANCE_ID, ATO_LINE_ID, CREATION_DATE,
CREATED_BY, LAST_UPDATED_BY, LAST_UPDATE_DATE,
MAKE_FLAG, refresh_number, session_id from msc_cto_sources
where session_id = :p_session_id
and line_id in (select order_line_id
from mrp_atp_schedule_temp
where session_id = :p_session_id
--bug 3378648
and status_flag = 99
and order_line_id = ato_model_line_id)';
SELECT msi.sr_inventory_item_id,
--4570421
--round(mbc.usage_quantity * p_quantity, 6),
ROUND ((decode (NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,1), MSC_ATP_PVT.DISCRETE_ORG, decode ( nvl(mbc.scaling_type, 1), 1, (MBC.USAGE_QUANTITY*p_quantity),
2, MBC.USAGE_QUANTITY),
MSC_ATP_PVT.OPM_ORG, decode (nvl (mbc.scaling_type, 1), 0, MBC.USAGE_QUANTITY,
1, (MBC.USAGE_QUANTITY*p_quantity),
2, MBC.USAGE_QUANTITY,
3, (MBC.USAGE_QUANTITY*p_quantity),
4, (MBC.USAGE_QUANTITY*p_quantity),
5, (MBC.USAGE_QUANTITY*p_quantity))
))--/NVL (mbc.component_yield_factor, 1) --4767982
,6),
msi.atp_flag,
msi.atp_components_flag,
msi.aggregate_time_fence_date, -- For time_phased_atp
msi.bom_item_type,
msi.fixed_lead_time,
msi.variable_lead_time,
msi.inventory_item_id,
msi.uom_code,
--4570421
mbc.scaling_type,
mbc.scale_multiple,
mbc.scale_rounding_variance,
mbc.rounding_direction,
mbc.component_yield_factor, --4570421
MBC.USAGE_QUANTITY*mbc.component_yield_factor, --4775920
NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,1) --4775920
BULK COLLECT INTO
x_mand_comp_info_rec.sr_inventory_item_id,
x_mand_comp_info_rec.quantity,
x_mand_comp_info_rec.atp_flag,
x_mand_comp_info_rec.atp_components_flag,
x_mand_comp_info_rec.atf_date, --For time_phased_atp
x_mand_comp_info_rec.bom_item_type,
x_mand_comp_info_rec.fixed_lead_time,
x_mand_comp_info_rec.variable_lead_time,
x_mand_comp_info_rec.dest_inventory_item_id,
x_mand_comp_info_rec.uom_code,
--4570421
x_mand_comp_info_rec.scaling_type,
x_mand_comp_info_rec.scale_multiple,
x_mand_comp_info_rec.scale_rounding_variance,
x_mand_comp_info_rec.rounding_direction,
x_mand_comp_info_rec.component_yield_factor, --4570421
x_mand_comp_info_rec.usage_qty, --4775920
x_mand_comp_info_rec.organization_type --4775920
FROM MSC_SYSTEM_ITEMS MSI,
MSC_BOM_COMPONENTS MBC
WHERE Mbc.plan_id = p_plan_id
AND mbc.sr_instance_id = p_instance_id
AND mbc.bill_sequence_id = l_bill_seq_id
AND mbc.using_assembly_id = l_inventory_item_id
AND mbc.organization_id = p_organization_id
AND mbc.optional_component = 2 --- choose mandatory comps
-- do not honor atp_flag for smcs
---AND mbc.ATP_FLAG = 1 --- chose ATPable components
AND mbc.USAGE_QUANTITY > 0
AND msi.inventory_item_id = mbc.inventory_item_id
AND msi.organization_Id = mbc.organization_id
AND msi.plan_id = mbc.plan_id
AND msi.sr_instance_id = mbc.sr_instance_id
AND msi.bom_item_type = 4 -- chose always standard comp as option class will be passed by OM
AND (msi.atp_flag <> 'N' or msi.atp_components_flag <> 'N')
--4137608
-- effective date should be greater than or equal to greatest of PTF date, sysdate and request date
-- disable date should be less than or equal to greatest of PTF date, sysdate and request date
AND TRUNC(NVL(MBC.DISABLE_DATE, GREATEST(p_request_date, l_sysdate, MSC_ATP_PVT.G_PTF_DATE)+1)) >=
TRUNC(GREATEST(p_request_date, l_sysdate, MSC_ATP_PVT.G_PTF_DATE))
AND TRUNC(MBC.EFFECTIVITY_DATE) <=
TRUNC(GREATEST(p_request_date, l_sysdate, MSC_ATP_PVT.G_PTF_DATE));
select organization_id,
tp_id,
partner_site_id,
make_flag
bulk collect into
l_cto_source_list.organization_id,
l_cto_source_list.supplier_id,
l_cto_source_list.supplier_site_id,
l_cto_source_list.make_flag
from
(
select mcs.organization_id,
/* bug 3628958: if null is directly used in union clause then error is raised that datatype do not match
null tp_id,
null partner_site_id,
*/
to_number(null) tp_id,
to_number(null) partner_site_id,
make_flag
from msc_cto_sources mcs
where mcs.line_id = p_line_ids(1)
and mcs.sr_instance_id = p_instance_id
--and mcs.status_flag = 1
and mcs.session_id = p_session_id
and mcs.organization_id is not null
UNION ALL
--bug 3628958
--select null organization_id,
select to_number(null) organization_id,
mtil.tp_id,
mtps.partner_site_id,
make_flag
from msc_cto_sources mcs,
msc_tp_id_lid mtil,
msc_trading_partner_sites mtps
where mcs.line_id = p_line_ids(1)
and mcs.sr_instance_id = p_instance_id
--and mcs.status_flag = 1
and mcs.session_id = p_session_id
and mcs.supplier_id is not null
and mcs.supplier_site_code is not null
and mcs.supplier_id = mtil.sr_tp_id
and mtil.partner_type = 1
and mcs.sr_instance_id = mtil.sr_instance_id
and mtil.tp_id = mtps.partner_id
and mtps.partner_type = 1
and mcs.supplier_site_code = mtps.tp_site_code
);
/* select nvl(mcs.organization_id,0),
nvl(mtil.tp_id,0),
nvl(mtps.partner_site_id, 0),
make_flag
bulk collect into
l_cto_source_list.organization_id,
l_cto_source_list.supplier_id,
l_cto_source_list.supplier_site_id,
l_cto_source_list.make_flag
from msc_cto_sources mcs,
msc_tp_id_lid mtil,
msc_trading_partner_sites mtps
where mcs.line_id = p_line_ids(1)
and mcs.sr_instance_id = p_instance_id
and mcs.status_flag = 1
and ( mcs.organization_id is not null
or ( mcs.supplier_id is not null
and mcs.supplier_site_code is not null
and mcs.supplier_id = mtil.sr_tp_id
and mtil.partner_type = 1
and mcs.sr_instance_id = mtil.sr_instance_id
and mtil.tp_id = mtps.partner_id
and mtps.partner_type = 1
and mcs.supplier_site_code = mtps.tp_site_code
));
select count(distinct mcs.line_id)
into l_item_count
from msc_cto_sources mcs,
msc_ship_set_temp msst
where mcs.line_id = msst.line_id
and mcs.sr_instance_id = p_instance_id
--and mcs.status_flag = 1;
select mcs.line_id,
nvl(mcs.organization_id, -1),
mcs.supplier_id
bulk collect into
l_line_id,
l_org_id,
l_sup_id
from msc_cto_sources mcs,
msc_ship_set_temp msst
where mcs.line_id = msst.line_id
and mcs.sr_instance_id = p_instance_id
--and mcs.status_flag = 1;
select nvl(mcs.organization_id,0),
null,
null,
null
bulk collect into
l_cto_source_list.organization_id,
l_cto_source_list.supplier_id,
l_cto_source_list.supplier_site_id,
l_cto_source_list.make_flag
from msc_cto_sources mcs,
msc_ship_set_temp msst
where mcs.line_id = msst.line_id
and mcs.sr_instance_id = p_instance_id
--and mcs.status_flag = 1
and mcs.session_id = p_session_id
and mcs.organization_id is not null
-- here we dont link on suppliers as we could have
--more than one item only at top level. Since drop ship is not supported
-- we can safely ignore suppliers
group by mcs.organization_id
having count(*) = l_item_count;
delete msc_cto_bom where session_id = p_session_id;
insert into msc_cto_bom
(SR_INVENTORY_ITEM_ID,
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,
sr_instance_id,
refresh_number)
SELECT distinct
mast.inventory_item_id sr_inventory_item_id,
mil.inventory_item_id inventory_item_id,
mast.ORDER_LINE_ID,
mast.top_model_line_id,
mast.ato_parent_model_line_id,
mast.ato_model_line_id,
mast.match_item_id,
mast.wip_supply_type,
mast.session_id,
mast.BOM_ITEM_TYPE,
mast.QUANTITY_ORDERED,
mast.parent_line_id,
MSC_ATP_PVT.G_INSTANCE_ID,
p_refresh_number
FROM mrp_atp_schedule_temp mast,
msc_item_id_lid mil
where session_id = p_session_id
--bug 3378648
and status_flag = 99
and ato_model_line_id is not null -- transfer ATO model enteties only;
msc_sch_wb.atp_debug('Rows Inserted := ' || SQL%ROWCOUNT);
l_sql_stmt := 'Insert into Msc_CTO_Bom' || l_dblink;
' Select * from Msc_CTO_Bom where session_id = :p_session_id';
select fixed_lead_time, variable_lead_time
into l_fixed_lt, l_variable_lt
from msc_system_items
where plan_id = p_plan_id
and sr_instance_id = p_instance_id
and sr_inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
SELECT mcb.sr_INVENTORY_ITEM_ID,
(mcb.quantity / p_parent_so_quantity) * p_request_quantity ,
c2.calendar_date,
l_lead_time,
mcb.wip_supply_type,
mcb.LINE_ID,
mcb.parent_line_id,
mcb.TOP_MODEL_LINE_ID,
mcb.ATO_PARENT_MODEL_LINE_ID,
mcb.ATO_MODEL_LINE_ID,
mcb.MATCH_ITEM_ID,
mcb.BOM_ITEM_TYPE,
mcb.quantity,
NVL(msi.fixed_lead_time, 0),
NVL(msi.variable_lead_time, 0),
mcb.oss_error_code,
msi.atp_flag,
msi.atp_components_flag,
msi.aggregate_time_fence_date, -- For time_phased_atp
msi.inventory_item_id,
msi.uom_code, --bug3110023
mbc.usage_quantity*mbc.component_yield_factor, --4775920
NVL (MSC_ATP_PVT.G_ORG_INFO_REC.org_type,1) --4775920
BULK COLLECT INTO
p_comp_rec.inventory_item_id,
p_comp_rec.comp_usage,
p_comp_rec.requested_date,
p_comp_rec.lead_time,
p_comp_rec.wip_supply_type,
p_comp_rec.assembly_identifier,
p_comp_rec.parent_line_id,
p_comp_rec.TOP_MODEL_LINE_ID,
p_comp_rec.ATO_PARENT_MODEL_LINE_ID,
p_comp_rec.ATO_MODEL_LINE_ID,
p_comp_rec.MATCH_ITEM_ID,
p_comp_rec.BOM_ITEM_TYPE,
p_comp_rec.parent_so_quantity,
p_comp_rec.fixed_lt,
p_comp_rec.variable_lt,
p_comp_rec.oss_error_code,
p_comp_rec.atp_flag,
p_comp_rec.atp_components_flag,
p_comp_rec.atf_date, -- For time_phased_atp
p_comp_rec.dest_inventory_item_id,
p_comp_rec.comp_uom, --bug3110023
p_comp_rec.usage_qty, --4775920
p_comp_rec.organization_type --4775920
FROM msc_cto_bom mcb,
msc_calendar_dates c1,
msc_calendar_dates c2,
msc_trading_partners tp,
msc_system_items msi,
msc_bom_components mbc,
MSC_OPERATION_COMPONENTS MOC
WHERE mcb.session_id = p_session_id
AND mcb.sr_instance_id = MSC_ATP_PVT.G_INSTANCE_ID -- this is the instance id of the calling module
AND mcb.PARENT_LINE_ID = p_line_id
AND mcb.sr_inventory_item_id = msi.sr_inventory_item_id (+)
AND p_organization_id = msi.organization_id (+)
AND p_instance_id = msi.sr_instance_id (+)
AND p_plan_id = msi.plan_id(+)
---bug 3644238: truncate date else appropriate date wouldn't be found in msc_calendar tables.
AND c1.calendar_date = trunc(p_request_date)
AND c1.sr_instance_id = tp.sr_instance_id
AND c1.calendar_code = tp.calendar_code
AND c1.exception_set_id = tp.calendar_exception_set_id
AND tp.sr_instance_id = p_instance_id -- instance id of the org id from which we are calling
AND tp.sr_tp_id = p_organization_id
AND tp.partner_type = 3
AND c2.seq_num = c1.prior_seq_num - l_lead_time
AND c2.calendar_code = tp.calendar_code
AND c2.sr_instance_id = tp.sr_instance_id
AND c2.exception_set_id = tp.calendar_exception_set_id
and mbc.inventory_item_id = msi.inventory_item_id ---4570421
and mbc.plan_id = msi.plan_id
and mbc.sr_instance_id = msi.sr_instance_id
and mbc.bill_sequence_id = l_bill_seq_id
and MOC.PLAN_ID(+) = p_plan_id --4929084
and MOC.SR_INSTANCE_ID(+) = p_instance_id
and MOC.ORGANIZATION_ID(+) = p_organization_id
and MOC.BILL_SEQUENCE_ID(+) = l_bill_seq_id
and MOC.ROUTING_SEQUENCE_ID(+) = l_routing_seq_id
and MOC.COMPONENT_SEQUENCE_ID(+) = mbc.COMPONENT_SEQUENCE_ID
and MOC.OPERATION_SEQUENCE_ID(+) = l_op_seq_id;
Delete from msc_cto_sources
where sr_instance_id = p_instance_id
and ato_line_id = p_atp_rec.identifier(i)
and status_flag = 2;
Delete from msc_cto_sources
where sr_instance_id = p_instance_id
and ato_line_id = p_atp_rec.identifier(i)
and status_flag = 1;
UPDATE msc_cto_sources
set status_flag = 1
where sr_instance_id = p_instance_id
and ato_line_id = p_atp_rec.identifier(i)
and status_flag = 2;
Select atp_flag, atp_components_flag, sr_inventory_item_id
into l_atp_flag, l_atp_comp_flag, x_model_sr_inv_id
from msc_system_items msi
where msi.inventory_item_id = p_base_model_id
and msi.sr_instance_id = p_instance_id
and msi.plan_id = p_plan_id
and msi.organization_id = p_organization_id;
msc_sch_wb.atp_debug('After Selecting process effectivity');
SELECT NVL(mbc.atp_flag, 2)
INTO x_check_model_capacity_flag
from msc_bom_components mbc
where mbc.BILL_SEQUENCE_ID = l_bill_seq_id
and mbc.PLAN_ID = p_plan_id
and mbc.SR_INSTANCE_ID = p_instance_id
and mbc.ORGANIZATION_ID = p_organization_id
and mbc.INVENTORY_ITEM_ID = p_base_model_id;
msc_sch_wb.atp_debug('After Selectng bom level atp flag');