[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure is called by the Update Existing Configurations batch
progam. It does the following:
1. Call procedures to populate bcol_upg based on the input params
2. Delete sourcing for canned configs not to be processed
3. Update bcol_upg with sequence numbers for batch processing
4. Call procedure to update items and sourcing
***********************************************************************/
PROCEDURE Update_Configs
(
errbuf OUT NOCOPY varchar2,
retcode OUT NOCOPY varchar2,
p_item IN number,
p_dummy IN varchar2,
p_dummy2 IN varchar2,
p_category_set_id IN number, --bugfix3397123
p_dummy3 IN number, --bugfix3397123
p_cat_id IN number,
p_config_id IN number,
p_changed_src IN varchar2,
p_open_lines IN varchar2,
p_upgrade_mode In Number
) IS
/*
Redundant cursor
CURSOR c_seq(l_seq number) IS
select distinct sequence
from bom_cto_order_lines_upg
where sequence = l_seq;
select distinct ato_line_id
from bom_cto_order_lines_upg
where status = 'CTO_SRC'
and line_id = ato_line_id;
WriteToLog('Begin Update Existing Configurations with Debug Level: '||gDebugLevel);
update_atp_attributes(
p_item => p_item,
p_cat_id => p_cat_id,
p_config_id => p_config_id,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
WriteToLog('Update Existing Configurations completed with SUCCESS');
delete from bom_cto_src_orgs_b
where line_id in (
select bcolu.line_id
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id is not null
and not exists (
select 'exists'
from oe_order_lines_all oel
where oel.line_id = bcolu.line_id));
WriteToLog('Rows deleted from bcso_b::'|| sql%rowcount, 1);
WriteToLog('Rows deleted from bcol_upg::'|| sql%rowcount, 1);
select assignment_set_id
into l_cto_aset_id
from mrp_assignment_sets
where assignment_set_name = 'CTO Configuration Updates';
INSERT INTO mrp_assignment_sets
(assignment_set_id ,
assignment_set_name,
description,
created_by,
last_updated_by,
creation_date,
last_update_date
)
VALUES
( MRP_ASSIGNMENT_SETS_S.nextval,
'CTO Configuration Updates',
'Exclusively for use by CTO. Used during Upgrade Concurrent programs',
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
sysdate
)
returning assignment_set_id INTO l_cto_aset_id;
WriteToLog('Assignment set name::'|| 'CTO Configuration Updates', 2);
delete from mrp_sr_assignments
where assignment_set_id = l_cto_aset_id;
WriteToLog('Rows deleted from cto assignment set::'|| sql%rowcount, 1);
delete from mrp_sr_assignments
where assignment_set_id = l_mrp_aset_id
and inventory_item_id in
(select config_item_id
from bom_ato_configurations bac
where not exists
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id
and rownum = 1) -- bug 13876670
-- and not on open order lines
and not exists
(select 'exists'
from oe_order_lines_all oel,
bom_cto_order_lines bcol
where bcol.config_item_id = bac.config_item_id
and bcol.ato_line_id = oel.ato_line_id
and nvl(oel.open_flag, 'N') = 'Y'
and oel.item_type_code='CONFIG'
and rownum = 1) -- bug 13876670
-- and item is not pre-configured
and not exists
(select 'pc'
from mtl_system_items msi
where msi.inventory_item_id = bac.config_item_id
-- bug 13876670
and msi.auto_created_config_flag = 'N'
and msi.organization_id = bac.organization_id
and rownum =1
));
WriteToLog('New Msg: Sourcing deleted::'||sql%rowcount, 2);
delete from mrp_sr_assignments
where assignment_set_id = l_mrp_aset_id
and inventory_item_id in
(
select /*+ leading(mcat bac) */ DISTINCT config_item_id -- bug 13876670 added hint
from bom_ato_configurations bac,
mtl_item_categories mcat
where bac.base_model_id = mcat.inventory_item_id
and mcat.category_id = p_cat_id
-- bug 13876670
and mcat.category_set_id = p_category_set_id
and not exists
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id
and rownum = 1 -- 13876670
)
and NOT EXISTS -- bug 13876670
(select 'exists'
from oe_order_lines_all oel,
bom_cto_order_lines bcol
where bcol.config_item_id = bac.config_item_id
and bcol.ato_line_id = oel.ato_line_id
and bcol.config_item_id = oel.inventory_item_id
and oel.item_type_code = 'CONFIG'
and open_flag = 'Y'
and rownum = 1 -- 13876670
)
and not exists
(select /*+ no_unnest push_subq */ 'pc'
from mtl_system_items msi
where msi.inventory_item_id = bac.config_item_id
and msi.auto_created_config_flag = 'N'
-- 13876670
and msi.organization_id = bac.organization_id
and rownum = 1
)
);
WriteToLog('New Msg: Sourcing deleted::'||sql%rowcount, 2);
delete from mrp_sr_assignments
where assignment_set_id = l_mrp_aset_id
and inventory_item_id = p_config_id
--Bugfix 14359805
--Exists in BAC
and exists
(select 'exists'
from bom_ato_configurations bac
where bac.config_item_id = p_config_id)
-- not being upgraded
and not exists
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = p_config_id
and rownum = 1 -- bug 13876670
)
-- and not on open order lines
and not exists /* bug 3399310 sushant changed the query to identify config item exists */
(select 'exists'
from oe_order_lines_all oel,
bom_cto_order_lines bcol
where bcol.config_item_id = p_config_id
and bcol.line_id = oel.ato_line_id
and oel.item_type_code = 'CONFIG'
and nvl(oel.open_flag, 'N') = 'Y'
and rownum = 1 -- bug 13876670
)
-- and item is not pre-configured
and not exists
(select 'pc'
from mtl_system_items msi
where msi.inventory_item_id = p_config_id
-- bug 13876670
and msi.auto_created_config_flag = 'N'
and rownum = 1
);
WriteToLog('New Msg: Sourcing deleted::'||sql%rowcount, 2);
delete from bom_cto_order_lines_upg bcol1
where ato_line_id not in (select max(bcol2.ato_line_id)
from bom_cto_order_lines_upg bcol2
where bcol2.config_item_id is not null
group by bcol2.config_item_id
);
DELETE
FROM bom_cto_order_lines_upg bcol1
WHERE rowid IN
(SELECT rowid
FROM
(SELECT rowid,
--row_number() over(PARTITION BY bcol2.config_item_id ORDER BY bcol2.ato_line_id DESC) rnk
row_number() over(PARTITION BY bcol2.config_item_id, bcol2.ship_from_org_id ORDER BY bcol2.ato_line_id DESC) rnk
FROM bom_cto_order_lines_upg bcol2
WHERE bcol2.config_item_id IS NOT NULL
)
WHERE rnk <> 1
);
WriteToLog('New Msg: Rows deleted from bcolu:: ' ||sql%rowcount, 2);
select count(*)
into l_bcolu_count
from bom_cto_order_lines_upg;
select count(*)
into l_bcolu_count
from bom_cto_order_lines_upg
where status <>'ERROR';
update bom_cto_order_lines_upg bcolu
set bcolu.sequence = l_seq_temp
where bcolu.ato_line_id in
(select ato_line_id
from bom_cto_order_lines_upg bcolu2
where bcolu2.ato_line_id = bcolu2.line_id
and bcolu2.status IN ('UPG')
and rownum < G_BATCH_SIZE + 1
and bcolu2.sequence is null);
WriteToLog('Going for a second update of sequence numbers', 4);
SELECT config_item_id, Max(SEQUENCE)
BULK COLLECT INTO l_cfg_itm_tbl, l_seq_tbl
FROM bom_cto_order_lines_upg
WHERE config_item_id IS NOT NULL
GROUP BY config_item_id
HAVING Count(DISTINCT SEQUENCE) > 1;
WriteToLog('Count of rows to be updated:: '|| l_cfg_itm_tbl.count, 1);
UPDATE bom_cto_order_lines_upg bcol1
SET bcol1.SEQUENCE = l_seq_tbl(i)
WHERE ato_line_id IN ( SELECT distinct ato_line_id
FROM bom_cto_order_lines_upg bcol2
WHERE bcol2.config_item_id = l_cfg_itm_tbl(i)
);
WriteToLog('Rows updated::'|| sql%rowcount, 1);
Cto_Update_Items_Pk.Update_Items_And_Sourcing(
p_changed_src => p_changed_src
, p_cat_id => p_cat_id
, p_upgrade_mode => p_upgrade_mode
--Bugfix 10240482: Passing the new parameter p_max_seq
, p_max_seq => l_max_seq
, xReturnStatus => l_return_status
, xMsgCount => l_msg_count
, xMsgData => l_msg_data);
WriteToLog('ERROR: Update_items_and_sourcing returned unexpected error');
WriteToLog('ERROR: Update_items_and_sourcing returned expected error');
WriteToLog('Update_items_and_sourcing returned success', 3);
WriteToLog('update_configs: About to generate bom batch ID', 5);
WriteToLog('update_configs: ' || 'Failed in set_bom_batch_id with unexp error.', 1);
WriteToLog('update_configs:: '||to_char(l_seq));
select 'exists'
into l_exists
from bom_cto_order_lines_upg
where sequence = l_seq
and rownum = 1;
WriteToLog('update_configs:: No_Data_Found for l_seq:'|| l_seq, 1);
select assignment_set_id
into l_cto_aset_id
from mrp_assignment_sets
where assignment_set_name = 'CTO Configuration Updates';
delete from mrp_sr_assignments
where assignment_set_id = l_cto_aset_id;
WriteToLog('Rows deleted from CTO Seeded Assignment Set::' ||sql%rowcount, 2);
delete from mtl_item_categories
where category_id = p_cat_id;
WriteToLog('Rows deleted from category::'||sql%rowcount, 2);
WriteToLog('Update Existing Configurations completed with WARNING');
WriteToLog('Update Existing Configurations completed with SUCCESS');
WriteToLog('ERROR: Exp error in CTO_Update_Configs_Pk.Update_Configs:: '|| l_stmt_num ||'::'||sqlerrm);
WriteToLog('Update Existing Configurations completed with ERROR.', 1);
WriteToLog('ERROR: Unexp error in CTO_Update_Configs_Pk.Update_Configs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
WriteToLog('Update Existing Configurations completed with ERROR');
WriteToLog('ERROR: Others error in CTO_Update_Configs_Pk.Update_Configs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
WriteToLog('Update Existing Configurations completed with ERROR.', 1);
END update_configs;
select distinct bac.config_item_id config_id
from bom_ato_configurations bac,
mtl_system_items msi
where NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
and bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3'; -- bug 13362916 removed nvl for performance
select distinct bac.config_item_id config_id
from bom_ato_configurations bac,
mtl_system_items msi
-- item attribute is 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
-- and is top parent with attribute 3
and NOT EXISTS
(select 'exists'
from bom_ato_configurations bac2
, mtl_system_items msi2
where bac.config_item_id = bac2.component_item_id
and bac2.base_model_id = msi2.inventory_item_id
and bac2.organization_id = msi2.organization_id
and msi2.config_orgs = '3') -- bug 13362916 removed nvl for performance
-- and not already in bcol_upg
and NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id);
WriteToLog('Match profile is No and you chose not to update existing configurations. No configurations will be updated.');
-- select all open order lines having config items with attribute in (2,3)
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 1', 3);
select count(line_id)
into l_count
from bom_cto_order_lines_upg;
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, config_creation
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'N' --PERFORM_MATCH
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(mtl.config_orgs, '1')
--changed the where clause to use a subquery
--bugfix 3841575
from bom_cto_order_lines bcol2
, mtl_system_items mtl
-- select entire configuration
where mtl.inventory_item_id = bcol2.inventory_item_id
and mtl.organization_id = bcol2.ship_from_org_id
and bcol2.ato_line_id in
(select distinct bcol1.ato_line_id
from bom_cto_order_lines bcol1
, oe_order_lines_all oel
, mtl_system_items msi
-- for configs whose models have attr=2,3
where bcol1.config_item_id is not null
and bcol1.inventory_item_id = msi.inventory_item_id
and bcol1.ship_from_org_id = msi.organization_id
and msi.config_orgs in ('2', '3') -- bug 13362916 removed nvl for performance
-- and are on open order lines
and bcol1.line_id = oel.line_id
and oel.open_flag = 'Y'); -- bug 13362916 removed NVL
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select all open order lines having canned config items with attribute = 3
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 2', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, config_creation
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'Y' --PERFORM_MATCH /* Sushant Made changes for identifying matched items */
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(msi.config_orgs, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, bom_ato_configurations bac
, oe_order_lines_all oel
, mtl_system_items msi
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
-- and exists in bcol
and bac.config_item_id = bcol1.config_item_id
-- on open order lines
and bcol1.line_id = oel.line_id
and oel.open_flag = 'Y' -- bug 13362916 removed NVL
and bcol2.ato_line_id = bcol1.ato_line_id;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select additional config items with attribute = 3 on closed order lines
-- populate into bcol_upg
-- mark as UPG
--
-- Commenting as part of Bugfix 8894392
-- Reasoning: Suppose I had an OSS setup and the configs that have that setup
-- are all on the closed SO lines. Now, I want to change the OSS for model in
-- such a way that the shipping warehouse on these closed lines becomes an
-- invalid org as per new OSS rules. Now when I run UEC for the old configs,
-- the UEC ended in error saying ship from org is not valid. Thus even though
-- the lines are closed, I cannot change the OSS setup on the model to make
-- the old warehouse invalid.
-- Changed the logic. Now we do not pick up any configs on closed lines. If a
-- matched CIB = 3 config is not found on any open lines, we look for the config
-- in bom_ato_configurations table.
-- Another change is the use of decode while populating perform_match flag.
-- This flag is now populated using this decode statement:
-- decode(bcol.perform_match, 'C', 'Y', bcol.perform_match). This is done to make
-- the behaviour of custom match similar to standard match. A lot of irregularities
-- arose because of different treatment of custom and standard match in UEC. An
-- example is: bcmo and bcso get populated differently for perform_match = C and
-- perform_match = Y. This resulted in wrong results.
/*WriteToLog('sql 3', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, config_creation
)
select distinct
bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, bcol.PERFORM_MATCH --7201878
--, 'Y' --PERFORM_MATCH /* Sushant made changes to identify matched items */
/*, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, 99 -- matched item on closed line
, bcol.PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(msi.config_orgs, '1')
from bom_ato_configurations bac
, bom_cto_order_lines bcol
, mtl_system_items msi
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and nvl(msi.config_orgs, '1') = '3'
-- and not already in bcol_upg
and NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
-- select first ato_line_id in bcol
and bcol.ato_line_id =
(select bcol1.ato_line_id
from bom_cto_order_lines bcol1
where bcol1.config_item_id = bac.config_item_id
-- pick up only if config is at top level
and bcol1.line_id = bcol1.ato_line_id
and rownum = 1)
;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);*/
-- select all individual (not top level) config items in bac having item attribute = 3 and not in bcol_upg
-- populate into bcol_upg from bcol or bac
-- mark as UPG
-- mark with program_id = 99 to indicate that it was populated from bac
--
WriteToLog('sql 3', 2);
select 'Y'
into l_exists
from bom_cto_order_lines_upg
where config_item_id = v_bac.config_id
and rownum = 1;
-- select all open order lines
-- populate into bcol_upg
-- mark as UPG
-- TEST THIS!!
--
WriteToLog('sql 5', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, CONFIG_CREATION
)
select distinct
bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, decode(bcol.perform_match, 'C', 'Y', bcol.perform_match) -- Bugfix 8894392
--, bcol.PERFORM_MATCH --7201878
--, 'N' --PERFORM_MATCH
, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, bcol.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(msi.CONFIG_ORGS, '1')
from bom_cto_order_lines bcol
, oe_order_lines_all oel
, mtl_system_items msi
-- select all configs on open order lines
where bcol.ato_line_id = oel.ato_line_id
and oel.open_flag = 'Y' -- bug 13362916 removed NVL
and bcol.inventory_item_id = msi.inventory_item_id
and bcol.ship_from_org_id = msi.organization_id
and oel.item_type_code = 'CONFIG' ; /* added condition for bug 3599397 */
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select all open order lines having canned config items with attribute = 3
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 6', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, CONFIG_CREATION
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'Y' --PERFORM_MATCH /* Sushant made changes to identify matched items */
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(msi.CONFIG_ORGS, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, bom_ato_configurations bac
, oe_order_lines_all oel
, mtl_system_items msi
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
-- and exists in bcol
and bac.config_item_id = bcol1.config_item_id
-- on open order lines
and bcol1.line_id = oel.line_id
and oel.open_flag = 'Y' -- bug 13362916 removed nvl
and bcol2.ato_line_id = bcol1.ato_line_id
;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select additional TOP LEVEL config items with attribute = 3 on closed order lines
-- populate into bcol_upg
-- mark as UPG
--
-- commenting as part of Bugfix 8894392
/*WriteToLog('sql 7', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, CONFIG_CREATION
)
select distinct
bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, bcol.PERFORM_MATCH --7201878
--, 'N' --PERFORM_MATCH /* Sushant made changes to identify matched items */
/*, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, 99 -- matched item on closed line
, bcol.PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(msi.CONFIG_ORGS, '1')
from bom_ato_configurations bac
, bom_cto_order_lines bcol
, mtl_system_items msi
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and nvl(msi.config_orgs, '1') = '3'
-- and is top parent with attribute 3
and NOT EXISTS
(select 'exists'
from bom_ato_configurations bac2
, mtl_system_items msi2
where bac.config_item_id = bac2.component_item_id
and bac2.base_model_id = msi2.inventory_item_id
and bac2.organization_id = msi2.organization_id
and nvl(msi2.config_orgs, '1') = '3')
-- and not already in bcol_upg
and NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
-- select first ato_line_id in bcol
and bcol.ato_line_id =
(select bcol1.ato_line_id
from bom_cto_order_lines bcol1
where bcol1.config_item_id = bac.config_item_id
-- pick up only if config is at top level
and bcol1.line_id = bcol1.ato_line_id
and rownum = 1)
;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);*/
-- select all top level config items in bac having item attribute = 3 and not in bcol_upg
-- populate into bcol_upg from bcol or bac
-- mark as UPG
-- mark with program_id = 99 to indicate that it was populated from bac
--
WriteToLog('sql 8', 3);
select 'exists'
into l_exists
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = v_bac_top.config_id
and rownum = 1;
select /*+ ORDERED */ distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
from mtl_item_categories mcat, --Bugfix 6617686: Changed the order of tables
mtl_system_items msi,
bom_ato_configurations bac
where NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
and bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
-- and base model is in CTO category
and mcat.inventory_item_id = msi.inventory_item_id
and mcat.organization_id = msi.organization_id
and mcat.category_id = p_cat_id;
select distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
from mtl_item_categories mcat, --Bugfix 6617686 Changed the order of tables
mtl_system_items msi,
bom_ato_configurations bac
-- item attribute is 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3'
-- and base model is in CTO category
and mcat.inventory_item_id = msi.inventory_item_id
and mcat.organization_id = msi.organization_id
and mcat.category_id = p_cat_id
-- bug 13876670
and mcat.category_set_id = p_category_set_id
-- and is top parent with attribute 3
and NOT EXISTS
(select /*+ no_unnest push_subq */ 'exists' -- bug 13876670 added hint
from bom_ato_configurations bac2
, mtl_system_items msi2
where bac.config_item_id = bac2.component_item_id
and bac2.base_model_id = msi2.inventory_item_id
and bac2.organization_id = msi2.organization_id
and msi2.config_orgs = '3')
-- and not already in bcol_upg
and NOT EXISTS
(select /*+ index(bcolu BOM_CTO_ORDER_LINES_UPG_N1) */ 'exists' -- 13362916 added hint
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id);
WriteToLog('Match profile is No and you chose not to update existing configurations. No configurations will be updated.');
-- select all open order lines having config items in l_cat_id with attribute in (2,3)
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 1', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, config_creation
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'N' --bcol2.PERFORM_MATCH
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --bcol2.REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol2.SEQUENCE
, nvl(msi.config_orgs, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, oe_order_lines_all oel
, mtl_system_items msi
, mtl_item_categories mcat
-- select entire configuration
where bcol2.ato_line_id = bcol1.ato_line_id
and bcol1.config_item_id is not null
-- for configs whose models are in CTO category
and mcat.inventory_item_id = bcol1.inventory_item_id
and mcat.organization_id = bcol1.ship_from_org_id
and mcat.category_id = p_cat_id
-- for configs whose models have attr=2,3
and bcol1.inventory_item_id = msi.inventory_item_id
and bcol1.ship_from_org_id = msi.organization_id
and msi.config_orgs in ('2', '3') -- bug 13362916 removed nvl for performance
-- and are on open order lines
and bcol1.line_id = oel.ato_line_id /* changed line_id to ato_line_id */
and oel.open_flag = 'Y' -- bug 13362916 removed NVL
and oel.item_type_code = 'CONFIG' ; /* added check for config linked to oe */
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select all open order lines having canned config items with attribute = 3
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 2', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, config_creation
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'Y' --bcol2.PERFORM_MATCH /* Sushant made changes to identify matched items */
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --bcol2.REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol2.SEQUENCE
, nvl(msi.config_orgs, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, bom_ato_configurations bac
, oe_order_lines_all oel
, mtl_system_items msi
, mtl_item_categories mcat
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
-- and exists in bcol
and bac.config_item_id = bcol1.config_item_id
-- for configs whose models are in CTO category
and mcat.inventory_item_id = bcol1.inventory_item_id
and mcat.organization_id = bcol1.ship_from_org_id
and mcat.category_id = p_cat_id
-- on open order lines
and bcol1.line_id = oel.ato_line_id /* changed line_id to ato_line_id */
and oel.open_flag = 'Y' -- bug 13362916 removed NVL
and bcol2.ato_line_id = bcol1.ato_line_id
and oel.item_type_code = 'CONFIG' ; /* added check for config linked to oe */
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select additional config items with attribute = 3 on closed order lines
-- populate into bcol_upg
-- mark as UPG
--
-- commenting as part of Bugfix 8894392
/*WriteToLog('sql 3', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, config_creation
)*/
--select /*+ ORDERED*/ distinct --Bugfix 6617686 Added a hint
/*bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, bcol.PERFORM_MATCH --7201878
--, 'N' --bcol.PERFORM_MATCH
, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, 99 -- matched item on closed line
, bcol.PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --bcol.REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol.SEQUENCE
, nvl(msi.config_orgs, '1')
from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
, mtl_system_items msi
, bom_ato_configurations bac
, bom_cto_order_lines bcol
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and nvl(msi.config_orgs, '1') = '3'
-- and base model is in CTO category
and mcat.inventory_item_id = msi.inventory_item_id
and mcat.organization_id = msi.organization_id
and mcat.category_id = p_cat_id
-- and not already in bcol_upg
and NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
-- select first ato_line_id in bcol
and bcol.ato_line_id =
(select bcol1.ato_line_id
from bom_cto_order_lines bcol1
where bcol1.config_item_id = bac.config_item_id
-- pick up only if config is at top level
and bcol1.line_id = bcol1.ato_line_id
and rownum = 1)
;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);*/
-- select all individual (not top level) config items in bac having item attribute = 3 and not in bcol_upg
-- populate into bcol_upg from bcol or bac
-- mark as UPG
-- mark with program_id = 99 to indicate that it was populated from bac
--
WriteToLog('sql 4', 3);
select 'Y'
into l_exists
from bom_cto_order_lines_upg
where config_item_id = v_bac.config_id
and rownum = 1;
-- select all open order lines
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 5', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, CONFIG_CREATION
)
select distinct
bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, decode(bcol.perform_match, 'C', 'Y', bcol.perform_match) -- Bugfix 8894392
--, bcol.PERFORM_MATCH --7201878
--, 'N' --bcol.PERFORM_MATCH
, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, bcol.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --bcol.REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol.SEQUENCE
, nvl(msi2.CONFIG_ORGS, '1')
from bom_cto_order_lines bcol
, oe_order_lines_all oel
, mtl_system_items msi2
-- select all configs on open order lines
where bcol.ato_line_id = oel.ato_line_id
and bcol.inventory_item_id = msi2.inventory_item_id
and bcol.ship_from_org_id = msi2.organization_id
and oel.open_flag = 'Y' -- 13362916 removed NVL
and oel.ato_line_id in -- bug 6617686 connect using oel rather than bcol to get better filtering
(select /*+ leading(MCAT) */ distinct bcol2.ato_line_id --Bugfix 6617686 Added a hint
from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
, mtl_system_items msi
, bom_cto_order_lines bcol2
where bcol2.config_item_id is not null
and bcol2.inventory_item_id = msi.inventory_item_id
and bcol2.ship_from_org_id = msi.organization_id
-- and base model is in CTO category
and mcat.inventory_item_id = msi.inventory_item_id
and mcat.organization_id = msi.organization_id
and mcat.category_id = p_cat_id
-- bug 13876670
and mcat.category_set_id = p_category_set_id)
and oel.item_type_code = 'CONFIG' ;/* original bug detected, added condition for bug 3599397 */
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select all open order lines having canned config items with attribute = 3 and assigned to CTO category
-- populate into bcol_upg
-- mark as UPG
-- TEST THIS!!
--
WriteToLog('sql 6', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, CONFIG_CREATION
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'Y' --bcol2.PERFORM_MATCH /* Sushant made changes to identify matched items */
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --bcol2.REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol2.SEQUENCE
, nvl(msi.CONFIG_ORGS, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, bom_ato_configurations bac
, oe_order_lines_all oel
, mtl_system_items msi
, mtl_item_categories mcat
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and msi.config_orgs = '3' -- bug 13362916 removed nvl for performance
-- and exists in bcol
and bac.config_item_id = bcol1.config_item_id
-- on open order lines
and bcol1.line_id = oel.line_id
and oel.open_flag = 'Y' -- bug 13362916 removed nvl
and bcol2.ato_line_id = bcol1.ato_line_id
-- and base model is in CTO category
and mcat.inventory_item_id = msi.inventory_item_id
and mcat.organization_id = msi.organization_id
and mcat.category_id = p_cat_id
-- bug 13876670
and mcat.category_set_id = p_category_set_id;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select additional TOP LEVEL config items with attribute = 3 on closed order lines
-- populate into bcol_upg
-- mark as UPG
--
-- commenting as part of Bugfix 8894392
/*WriteToLog('sql 7', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, CONFIG_CREATION
)*/
--select /*+ leading(MCAT) */ distinct
/*bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, bcol.PERFORM_MATCH --7201878
--, 'Y' --bcol.PERFORM_MATCH /* Sushant made changes to identify matched items */
/*, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, 99 -- matched item on closed line
, bcol.PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --bcol.REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol.SEQUENCE
, nvl(msi.CONFIG_ORGS, '1')
from bom_ato_configurations bac
, bom_cto_order_lines bcol
, mtl_system_items msi
, mtl_item_categories mcat
-- base model has item attr = 3
where bac.base_model_id = msi.inventory_item_id
and bac.organization_id = msi.organization_id
and nvl(msi.config_orgs, '1') = '3'
-- and base model is in CTO category
and mcat.inventory_item_id = msi.inventory_item_id
and mcat.organization_id = msi.organization_id
and mcat.category_id = p_cat_id
-- and is top parent with attribute 3
and NOT EXISTS
(select 'exists'
from bom_ato_configurations bac2
, mtl_system_items msi2
where bac.config_item_id = bac2.component_item_id
and bac2.base_model_id = msi2.inventory_item_id
and bac2.organization_id = msi2.organization_id
and nvl(msi2.config_orgs, '1') = '3')
-- and not already in bcol_upg
and NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
-- select first ato_line_id in bcol
and bcol.ato_line_id =
(select bcol1.ato_line_id
from bom_cto_order_lines bcol1
where bcol1.config_item_id = bac.config_item_id
-- pick up only if config is at top level
and bcol1.line_id = bcol1.ato_line_id
and rownum = 1)
;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);*/
-- select all top level config items in bac having item attribute = 3 and not in bcol_upg
-- populate into bcol_upg from bcol or bac
-- mark as UPG
-- mark with program_id = 99 to indicate that it was populated from bac
--
WriteToLog('sql 8', 3);
select 'exists'
into l_exists
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = v_bac_top.config_id
and rownum = 1;
WriteToLog('Match profile is No and you chose not to update existing configurations. No configurations will be updated.');
select nvl(msi.config_orgs, '1')
into l_attribute
from mtl_system_items msi
where msi.inventory_item_id =
(select msi2.base_item_id
from mtl_system_items msi2
where msi2.inventory_item_id = p_config_id
and rownum = 1)
and rownum = 1;
-- select all open order lines having this config item
-- populate into bcol_upg
-- mark as UPG
--
WriteToLog('sql 1', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, CONFIG_CREATION
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.perform_match -- Sushant Changed as part of bug 3472654 'N'
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --bcol2.REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol2.SEQUENCE
, nvl(msi.config_orgs, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, oe_order_lines_all oel
, mtl_system_items msi
, oe_order_lines_all oel2 --bugfix 3371155
-- select entire configuration
where bcol2.ato_line_id = bcol1.ato_line_id
-- to get item attribute
and msi.inventory_item_id = bcol2.inventory_item_id
and msi.organization_id = bcol2.ship_from_org_id
-- for this config
and bcol1.config_item_id = p_config_id
-- and are on open order lines
and bcol1.line_id = oel.line_id
and oel.open_flag = 'Y' -- bug 13362916 removed nvl
--bugfix 3371155
and bcol1.ato_line_id = oel2.ato_line_id
and oel2.item_type_code = 'CONFIG'
--end 3371155
;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select all open order lines having this canned config item, only if it has attribute = 3
-- populate into bcol_upg
-- mark as UPG
--
IF (l_attribute = 3) THEN
WriteToLog('sql 2', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
--, SEQUENCE
, CONFIG_CREATION
)
select distinct
bcol2.ATO_LINE_ID
, bcol2.BATCH_ID
, bcol2.BOM_ITEM_TYPE
, bcol2.COMPONENT_CODE
, bcol2.COMPONENT_SEQUENCE_ID
, bcol2.CONFIG_ITEM_ID
, bcol2.INVENTORY_ITEM_ID
, bcol2.ITEM_TYPE_CODE
, bcol2.LINE_ID
, bcol2.LINK_TO_LINE_ID
, bcol2.ORDERED_QUANTITY
, bcol2.ORDER_QUANTITY_UOM
, bcol2.PARENT_ATO_LINE_ID
, decode(bcol2.perform_match, 'C', 'Y', bcol2.perform_match) -- Bugfix 8894392
--, bcol2.PERFORM_MATCH --7201878
--, 'Y' --bcol2.PERFORM_MATCH /* Sushant made change to identify matched items */
, bcol2.PLAN_LEVEL
, bcol2.SCHEDULE_SHIP_DATE
, bcol2.SHIP_FROM_ORG_ID
, bcol2.TOP_MODEL_LINE_ID
, bcol2.WIP_SUPPLY_TYPE
, bcol2.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol2.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol2.CREATED_BY
, bcol2.LAST_UPDATE_LOGIN
, bcol2.REQUEST_ID
, bcol2.PROGRAM_APPLICATION_ID
, bcol2.PROGRAM_ID
, sysdate --PROGRAM_UPDATE_DATE
, bcol2.OPTION_SPECIFIC
, 'N' --bcol2.REUSE_CONFIG
, bcol2.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
--, bcol2.SEQUENCE
, nvl(msi.config_orgs, '1')
from bom_cto_order_lines bcol1
, bom_cto_order_lines bcol2
, bom_ato_configurations bac
, oe_order_lines_all oel
, mtl_system_items msi
where bac.config_item_id = p_config_id
-- and exists in bcol
and bac.config_item_id = bcol1.config_item_id
-- on open order lines
and bcol1.line_id = oel.line_id
and oel.open_flag = 'Y' -- bug 13362916. removed nvl
and bcol2.ato_line_id = bcol1.ato_line_id
-- to get item attribute
and msi.inventory_item_id = bcol2.inventory_item_id
and msi.organization_id = bcol2.ship_from_org_id;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
-- select this config item on closed order lines, only if attribute = 3 and not already in bcol_upg
-- populate into bcol_upg
-- mark as UPG
--
-- commenting as part of Bugfix 8894392
/*IF (l_attribute = 3) THEN
WriteToLog('sql 3', 3);
insert into bom_cto_order_lines_upg
(
ATO_LINE_ID
, BATCH_ID
, BOM_ITEM_TYPE
, COMPONENT_CODE
, COMPONENT_SEQUENCE_ID
, CONFIG_ITEM_ID
, INVENTORY_ITEM_ID
, ITEM_TYPE_CODE
, LINE_ID
, LINK_TO_LINE_ID
, ORDERED_QUANTITY
, ORDER_QUANTITY_UOM
, PARENT_ATO_LINE_ID
, PERFORM_MATCH
, PLAN_LEVEL
, SCHEDULE_SHIP_DATE
, SHIP_FROM_ORG_ID
, TOP_MODEL_LINE_ID
, WIP_SUPPLY_TYPE
, HEADER_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, OPTION_SPECIFIC
, REUSE_CONFIG
, QTY_PER_PARENT_MODEL
, STATUS
, CONFIG_CREATION
)
select distinct
bcol.ATO_LINE_ID
, bcol.BATCH_ID
, bcol.BOM_ITEM_TYPE
, bcol.COMPONENT_CODE
, bcol.COMPONENT_SEQUENCE_ID
, bcol.CONFIG_ITEM_ID
, bcol.INVENTORY_ITEM_ID
, bcol.ITEM_TYPE_CODE
, bcol.LINE_ID
, bcol.LINK_TO_LINE_ID
, bcol.ORDERED_QUANTITY
, bcol.ORDER_QUANTITY_UOM
, bcol.PARENT_ATO_LINE_ID
, bcol.PERFORM_MATCH --7201878
--, 'Y' --bcol.PERFORM_MATCH /* Sushant made changes to identify matched items */
/*, bcol.PLAN_LEVEL
, bcol.SCHEDULE_SHIP_DATE
, bcol.SHIP_FROM_ORG_ID
, bcol.TOP_MODEL_LINE_ID
, bcol.WIP_SUPPLY_TYPE
, bcol.HEADER_ID
, sysdate --LAST_UPDATE_DATE
, bcol.LAST_UPDATED_BY
, sysdate --CREATION_DATE
, bcol.CREATED_BY
, bcol.LAST_UPDATE_LOGIN
, bcol.REQUEST_ID
, bcol.PROGRAM_APPLICATION_ID
, 99 -- matched item on closed line
, bcol.PROGRAM_UPDATE_DATE
, bcol.OPTION_SPECIFIC
, 'N' --bcol.REUSE_CONFIG
, bcol.QTY_PER_PARENT_MODEL
, 'UPG' --STATUS
, nvl(msi.config_orgs, '1')
from bom_ato_configurations bac
, bom_cto_order_lines bcol
, mtl_system_items msi
where bac.config_item_id = p_config_id
and NOT EXISTS
(select 'exists'
from bom_cto_order_lines_upg bcolu
where bcolu.config_item_id = bac.config_item_id)
-- select first ato_line_id in bcol
and bcol.ato_line_id =
(select bcol1.ato_line_id
from bom_cto_order_lines bcol1
where bcol1.config_item_id = bac.config_item_id
-- pick up only if config is at top level
and bcol1.line_id = bcol1.ato_line_id
and rownum = 1)
-- to get item attribute
and msi.inventory_item_id = bcol.inventory_item_id
and msi.organization_id = bcol.ship_from_org_id;
WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
select 'Y'
into l_exists
from bom_cto_order_lines_upg
where config_item_id = p_config_id
and rownum = 1;
select
bom_cto_order_lines_s1.nextval, -- line_id
substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)), -- inventory_item_id
bac.component_item_id, -- header_id::storing comp_item_id here for intermediate processing
bac.component_code, -- component_code
msi.bom_item_type, -- bom_item_type
msi.primary_uom_code, -- order_quantity_uom
bac.component_quantity, -- ordered_quantity
bac.component_quantity, -- per_quantity
sysdate, -- schedule_ship_date
'N' , -- option_specific BUGFIX 3602292 defaulted this value to N as model will not have option_specific_sourced flag.
nvl(msi.config_orgs, '1'), -- config_orgs
sysdate, -- creation_date
nvl(Fnd_Global.USER_ID, -1), -- created_by
sysdate, -- last_update_date
nvl(Fnd_Global.USER_ID, -1), -- last_updated_by
cto_update_configs_pk.bac_program_id, -- program_id
'Y', -- perform_match /* Sushant made changes to identify matched items */
'N', -- reuse_config
bac.organization_id
from bom_ato_configurations bac,
mtl_system_items msi
where bac.config_item_id = p_config_id
-- and bac.component_item_id <> bac.base_model_id -- not pick up top model
and msi.inventory_item_id = substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)) -- bac.component_item_id
and msi.organization_id = bac.organization_id;
select base_model_id
into l_base_model_id
from bom_ato_configurations
where config_item_id = p_config_id
and rownum = 1;
t_bcol(l_index).last_update_date,
t_bcol(l_index).last_updated_by,
t_bcol(l_index).program_id,
t_bcol(l_index).perform_match,
t_bcol(l_index).reuse_config,
t_bcol(l_index).ship_from_org_id;
select bom_cto_order_lines_s1.nextval
into l_header_id
from dual;
INSERT INTO bom_cto_order_lines_upg(
HEADER_ID ,
LINE_ID ,
LINK_TO_LINE_ID ,
ATO_LINE_ID ,
PARENT_ATO_LINE_ID ,
TOP_MODEL_LINE_ID ,
PLAN_LEVEL ,
WIP_SUPPLY_TYPE ,
PERFORM_MATCH ,
BOM_ITEM_TYPE ,
COMPONENT_CODE ,
COMPONENT_SEQUENCE_ID ,
CONFIG_ITEM_ID ,
INVENTORY_ITEM_ID ,
ITEM_TYPE_CODE ,
BATCH_ID ,
ORDERED_QUANTITY ,
ORDER_QUANTITY_UOM ,
SCHEDULE_SHIP_DATE ,
SHIP_FROM_ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
QTY_PER_PARENT_MODEL,
OPTION_SPECIFIC,
REUSE_CONFIG,
STATUS,
SEQUENCE,
CONFIG_CREATION
)
VALUES (
t_bcol(i).header_id,
t_bcol(i).line_id,
t_bcol(i).link_to_line_id,
t_bcol(i).ato_line_id,
t_bcol(i).parent_ato_line_id,
t_bcol(i).top_model_line_id,
t_bcol(i).plan_level,
t_bcol(i).wip_supply_type,
'Y', -- perform_match BUGFIX 3567693
t_bcol(i).bom_item_type,
t_bcol(i).component_code,
t_bcol(i).component_sequence_id,
t_bcol(i).config_item_id,
t_bcol(i).inventory_item_id,
decode(t_bcol(i).line_id, t_bcol(i).ato_line_id, 'MODEL', decode(t_bcol(i).bom_item_type, '4', 'OPTION', 'CLASS')),
null, -- batch_id
t_bcol(i).ordered_quantity,
t_bcol(i).order_quantity_uom,
t_bcol(i).schedule_ship_date,
t_bcol(i).ship_from_org_id,
t_bcol(i).last_update_date,
t_bcol(i).last_updated_by,
t_bcol(i).creation_date,
t_bcol(i).created_by,
t_bcol(i).last_update_login,
null, -- request_id
null, -- program_application_id
t_bcol(i).program_id,
null, -- program_update_date
t_bcol(i).qty_per_parent_model,
t_bcol(i).option_specific,
'N',
'UPG',
null,
t_bcol(i).config_creation
);
WriteToLog('populate_bcolu_from_bac: Inserted ' || t_bcol(i).line_id);
select 'Y'
into l_exists
from bom_cto_order_lines_upg
where config_item_id = p_config_id
and rownum = 1;
WriteToLog('populate_bcolu_from_bac: inserting in bcolu in status ERROR');
INSERT INTO bom_cto_order_lines_upg(
HEADER_ID ,
LINE_ID ,
ATO_LINE_ID ,
PARENT_ATO_LINE_ID ,
TOP_MODEL_LINE_ID ,
PERFORM_MATCH ,
BOM_ITEM_TYPE ,
COMPONENT_CODE ,
CONFIG_ITEM_ID ,
INVENTORY_ITEM_ID ,
ITEM_TYPE_CODE ,
BATCH_ID ,
ORDERED_QUANTITY ,
ORDER_QUANTITY_UOM ,
SCHEDULE_SHIP_DATE ,
SHIP_FROM_ORG_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
REQUEST_ID ,
PROGRAM_APPLICATION_ID ,
PROGRAM_ID ,
PROGRAM_UPDATE_DATE ,
QTY_PER_PARENT_MODEL,
OPTION_SPECIFIC,
REUSE_CONFIG,
STATUS,
SEQUENCE,
CONFIG_CREATION
)
VALUES (
t_bcol(i).header_id,
t_bcol(i).line_id,
l_top_model_line_id,
l_top_model_line_id,
l_top_model_line_id,
'Y', -- perform_match
t_bcol(i).bom_item_type,
t_bcol(i).component_code,
t_bcol(i).config_item_id,
t_bcol(i).inventory_item_id,
decode(t_bcol(i).line_id, l_top_model_line_id, 'MODEL', decode(t_bcol(i).bom_item_type, '4', 'OPTION', 'CLASS')),
null, -- batch_id
t_bcol(i).ordered_quantity,
t_bcol(i).order_quantity_uom,
t_bcol(i).schedule_ship_date,
t_bcol(i).ship_from_org_id,
t_bcol(i).last_update_date,
t_bcol(i).last_updated_by,
t_bcol(i).creation_date,
t_bcol(i).created_by,
t_bcol(i).last_update_login,
null, -- request_id
null, -- program_application_id
t_bcol(i).program_id,
null, -- program_update_date
t_bcol(i).qty_per_parent_model,
t_bcol(i).option_specific,
'N',
'ERROR',
null,
t_bcol(i).config_creation
);
select
bom_cto_order_lines_s1.nextval, -- line_id
substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)), -- inventory_item_id
bac.component_item_id, -- header_id::storing comp_item_id here for intermediate processing
substr(bac.component_code, (instr(bac.component_code, '-', 1)+1)), -- component_code
--bac.config_item_id, -- config_item_id
msi.bom_item_type, -- bom_item_type
msi.primary_uom_code, -- order_quantity_uom
bac.component_quantity, -- ordered_quantity
bac.component_quantity, -- per_quantity
sysdate, -- schedule_ship_date
nvl(to_char(msi.option_specific_sourced),'N'), -- option_specific --bugfix3845686
nvl(msi.config_orgs, '1'), -- config_orgs
sysdate, -- creation_date
nvl(Fnd_Global.USER_ID, -1), -- created_by
sysdate, -- last_update_date
nvl(Fnd_Global.USER_ID, -1), -- last_updated_by
cto_update_configs_pk.bac_program_id, -- program_id
bac.organization_id --Bugfix 10240482
from bom_ato_configurations bac,
mtl_system_items msi
where bac.config_item_id = l_curr_config_id
and bac.component_item_id <> bac.base_model_id --not pick up top model
and msi.inventory_item_id = bac.component_item_id
and msi.organization_id = bac.organization_id;
select nvl(msi.config_orgs, '1'), inventory_item_id
into l_item_attr, l_child_model_id
from mtl_system_items msi
where msi.inventory_item_id = (select base_item_id --bugfix3845686
from mtl_system_items
where inventory_item_id = t_bcol(l_parent_index).header_id
and rownum =1)
--and msi.organization_id = t_bcol(l_parent_index).ship_from_org_id;
select substrb(concatenated_segments,1,50) name
into l_child_model_name
from mtl_system_items_kfv msi
where msi.inventory_item_id = l_child_model_id
and rownum=1;/* Fixed bug 3529482 */
t_bcol(l_index).last_update_date,
t_bcol(l_index).last_updated_by,
t_bcol(l_index).program_id,
t_bcol(l_index).ship_from_org_id; --Bugfix 10240482;
v_raw_line_id.delete;
select
bic.wip_supply_type,
bic.component_sequence_id
into p_t_bcol(v_item).wip_supply_type,
p_t_bcol(v_item).component_sequence_id
from bom_bill_of_materials bbom,
bom_inventory_components bic
where bbom.bill_sequence_id =
(select common_bill_sequence_id
from bom_bill_of_materials
where assembly_item_id = p_t_bcol(v_parent_item).inventory_item_id
and alternate_bom_designator is null
and rownum = 1)
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and bic.component_item_id = p_t_bcol(v_item).inventory_item_id
and rownum = 1;
v_raw_line_id.delete;
p_t_bcol.delete;
select distinct substrb(concatenated_segments,1,50) name,msi.inventory_item_id
item_id
from bom_cto_order_lines_upg bcolu,
mtl_system_items_kfv msi
where bcolu.config_item_id is not null
and bcolu.config_item_id = msi.inventory_item_id
and bcolu.ship_from_org_id = msi.organization_id
and ((bcolu.config_creation = '3'
and exists (select 'exists'
from bom_cto_order_lines_upg bcolu1
where bcolu1.config_item_id = bcolu.config_item_id
and bcolu1.status = 'MRP_SRC'
and rownum = 1))
or (bcolu.config_creation <> '3'
and not exists (select 'exists'
from bom_cto_order_lines_upg bcolu1
where bcolu1.config_item_id = bcolu.config_item_id
and bcolu1.status <> 'MRP_SRC')))
order by 1; -- Modified by Renga for bug 3930047
select distinct substrb(concatenated_segments,1,50) name,
msi.inventory_item_id item_id
from bom_cto_order_lines_upg bcolu,
mtl_system_items_kfv msi
where bcolu.config_item_id is not null
and bcolu.config_item_id = msi.inventory_item_id
and bcolu.ship_from_org_id = msi.organization_id
and not exists (select 'exists'
from bom_cto_order_lines_upg bcolu1
where bcolu1.config_item_id = bcolu.config_item_id
and bcolu1.status = 'MRP_SRC')
order by 1; -- Modified by Renga for bug 3930047
select distinct substrb(concatenated_segments,1,50) name,
msi.inventory_item_id item_id,
oeh.order_number,
decode(bcolu.status, 'MRP_SRC', 'was successfully processed', 'errored out') status
from bom_cto_order_lines_upg bcolu,
mtl_system_items_kfv msi,
oe_order_lines_all oel,
oe_order_headers_all oeh
where bcolu.config_item_id is not null
and bcolu.config_item_id = msi.inventory_item_id
and bcolu.ship_from_org_id = msi.organization_id
and config_creation <> '3'
and exists (select 'exists'
from bom_cto_order_lines_upg bcolu1
where bcolu1.config_item_id = bcolu.config_item_id
and bcolu1.status = 'MRP_SRC')
and exists (select 'exists'
from bom_cto_order_lines_upg bcolu1
where bcolu1.config_item_id = bcolu.config_item_id
and bcolu1.status <> 'MRP_SRC')
and oel.line_id = bcolu.ato_line_id
and oel.header_id = oeh.header_id
order by name, status;
SELECT bom_item_type,
wip_supply_type,
config_creation,
config_item_id,
inventory_item_id,
parent_ato_line_id,
ato_line_id,
line_id,
ship_from_org_id
FROM bom_cto_order_lines_upg
WHERE ato_line_id in ( SELECT DISTINCT bupg1.ato_line_id
FROM bom_cto_order_lines_upg bupg1
WHERE bupg1.config_creation = 3);
UPDATE bom_cto_order_lines_upg
SET status = 'ERROR'
WHERE ato_line_id = t_ato_line_id(j);
oe_debug_pub.add('Updated '||sql%rowcount||'lines with error status',1);
WriteToLog('ERROR: Unexp error in CTO_Update_Configs_Pk.Check_invalid_configurations:: '|| l_stmt_num ||'::'||sqlerrm, 1);
WriteToLog('Update Existing Configurations completed with ERROR');
Procedure update_atp_attributes(
p_item IN Number,
p_cat_id IN Number,
p_config_id IN Number,
x_return_status OUT NOCOPY varchar2,
x_msg_data OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number) is
Begin
WriteToLog(' Entering Update_atp_attributes procedure');
update mtl_system_items_b msic
set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
from mtl_system_items_b msim
where msim.inventory_item_id = msic.base_item_id
and msim.organization_id = msic.organization_id)
where msic.base_item_id is not null
and 'x'= (select 'x'
from mtl_system_items_b msim1
where msim1.inventory_item_id = msic.base_item_id
and msim1.organization_id = msic.organization_id);
WriteToLog(' Number of records updated = '||sql%rowcount);
update mtl_system_items_b msic
set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
from mtl_system_items_b msim
where msim.inventory_item_id = msic.base_item_id
and msim.organization_id = msic.organization_id)
where msic.inventory_item_id in (select msi.inventory_item_id
from mtl_system_items_b msi,
mtl_item_categories mcat
where msi.base_item_id = mcat.inventory_item_id
and mcat.category_id = p_cat_id)
and exists (select 'x' from mtl_system_items_b msim
where msim.inventory_item_id = msic.base_item_id
and msim.organization_id = msic.organization_id);
WriteToLog(' Number of records updated = '||sql%rowcount);
update mtl_system_items_b msic
set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
from mtl_system_items_b msim
where msim.inventory_item_id = msic.base_item_id
and msim.organization_id = msic.organization_id)
where msic.inventory_item_id = p_config_id
and exists (select 'x' from mtl_system_items_b msim
where msim.inventory_item_id = msic.base_item_id
and msim.organization_id = msic.organization_id);
WriteToLog(' Number of records updated = '||sql%rowcount);
End Update_atp_attributes;