[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| As CTO should not error out in its process becuase of failure in inserting
| in DBI atbles used for intelligence
|
|
| Modified : 18-FEB-2004 Sushant Sawant
| Fixed Bug 3441482
| Item Creation Code should not continue any further item processing
| for full configuration reuse.
|
|
| Modified : 02-MAR-2004 Sushant Sawant
| Fixed Bug 3472654
| provided check to see whether Config Item is enabled in all organizations
| where the model item is enabled for models with CIB = 3 and match = 'Y'.
|
|
| Modified : 02-APR-2004 Sushant Sawant
| Fixed Bug 3545019
| User created config for type3 model with match off
| changed order qty for option item and recreated config with match on
| This scenario errors out as bom_cto_src_orgs_b should be cleared
| for all partial reuse or no reuse scenarios. Data for type3 configs
| is stored in different formats.
| The fix will always clear bom_cto_src_orgs_b for partial reuse and no
| reuse scenarios to avoid the current issue.
|
|
| Modified : 13-APR-2004 Sushant Sawant
| Fixed Bug 3533192
| Similar configurations under different models should result in same config item
|
|
| 17-May-2004 Kiran Konada
| inserted ship_from_org-id from BCOL into the
| validation_org col on BCOL_GT
| code has been changed in CTO_REUSE for
| 3555026 to look at validation_org, and so
| validation-org cannot be null
|
*============================================================================*/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
select bcso.rcv_org_id, bcso.organization_id, bcol.config_creation, bcso.create_src_rules
, bcso.model_item_id , bcso.config_item_id
from bom_cto_order_lines bcol, bom_cto_src_orgs bcso
where bcol.ato_line_id = pTopAtoLineId
and bcol.bom_item_type = '1' and nvl( bcol.wip_supply_type , 1 ) <> '6'
and bcol.option_specific = 'N'
-- Bugfix 14270815: Sourcing rules should get created for CIB = 1,2 configs
-- even when they are matched.
-- Consider this scenario for a CIB = 1 model with no OSS:
-- Rule1: In M1: Tfr from M3
-- Rule2: In M2: Tfr from M3
-- Rule3: In M3: Make at M3
-- Create a SO in M1. It creates a new config C1. The rules Rule1 and Rule3
-- get assigned. Rule2 doesn't get assigned because it's not in the sourcing chain.
-- Create another SO in M2. It matches to C1. But because of this match <> 'Y'
-- condition, Rule2 doesn't get assigned to this config. This is wrong.
-- and bcol.perform_match <> 'Y'
and (bcol.config_creation in (1,2) or (bcol.config_creation = 3 and bcol.perform_match <> 'Y'))
-- Bugfix 8894392. For matched configs, no OSS processing happens. So the option_specific flag
-- value stays as N. Now for such configs, this cursor copied all the model's sourcing
-- rules without taking care of OSS.
and bcol.line_id = bcso.line_id ; /*Do not copy sourcing assignments for OSS Items*/
select bcso.rcv_org_id, bcso.organization_id, bcol.config_creation, bcso.create_src_rules
, bcso.inventory_item_id, bcso.config_item_id
from bom_cto_order_lines bcol, bom_cto_src_orgs bcso, bom_cto_model_orgs bcmo
where bcol.ato_line_id = pTopAtoLineId
and bcol.bom_item_type = '1' and nvl( bcol.wip_supply_type , 1 ) <> '6'
and bcol.option_specific = 'N'
and bcol.line_id = bcso.line_id
and bcso.reference_id is not null ;
select line_id,
inventory_item_id,
ship_from_org_id,
perform_match,
config_item_id,
config_creation, plan_level , link_to_line_id
from bom_cto_order_lines
where top_model_line_id = pTopAtoLineId;
delete /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
from bom_cto_order_lines_gt where ato_line_id = pTopAtoLineId ;
oe_debug_pub.add( ' Deleted from bom_cto_order_lines_gt ' || SQL%ROWCOUNT , 1 ) ;
select 'Y' into v_bcol_data_exists
from dual
where exists ( select line_id from bom_cto_order_lines
where line_id = pTopAtoLineId ) ;
oe_debug_pub.add('Create_And_Link_Item: ' || ' deleted ' || SQL%ROWCOUNT ||
' from bcol ' || to_char(pTopAtoLineId), 2);
oe_debug_pub.add( 'came into PRECONFIG UPDATE BCOL QUERY ' , 1 ) ;
update bom_cto_order_lines
set perform_match = 'Y'
where ato_line_id = pTopAtoLineId
and inventory_item_id in
( select inventory_item_id
from bom_cto_order_lines
where ato_line_id = pTopAtoLineId
and bom_item_type = '1'
and wip_supply_type <> 6
and perform_match = 'U'
group by inventory_item_id
having count(*) > 1
);
oe_debug_pub.add( 'PRECONFIG Similar Instance UPDATE BCOL QUERY count ' || SQL%ROWCOUNT , 1 ) ;
select count(*) into v_reuse_bcol_count from bom_cto_order_lines
where ato_line_id = pTopAtoLineId ;
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bcol.ship_from_org_id , bcolgt.ship_from_org_id
into v_bcol_ship_from_org_id, v_bcolgt_ship_from_org_id
from bom_cto_order_lines bcol, bom_cto_order_lines_gt bcolgt
where bcol.line_id = bcolgt.line_id and bcol.line_id = pTopAtoLineId ;
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
bom_cto_order_lines_gt bcolgt set option_specific =
( select option_specific from bom_cto_order_lines bcol
where bcolgt.line_id = bcol.line_id )
where bcolgt.ato_line_id = pTopAtoLineId ;
oe_debug_pub.add('Create_And_Link_Item: done oss flag update for reuse. ' , 5);
/* delete from bcol */
delete from bom_cto_order_lines where ato_line_id = pTopAtoLineId ;
insert into bom_cto_order_lines (
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION)
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,nvl( option_specific, 'N' )
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
from bom_cto_order_lines_gt
where ato_line_id = pTopAtoLineId ;
insert into bom_cto_order_lines_gt (
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
,VALIDATION_ORG)
select
LINE_ID
,HEADER_ID
,TOP_MODEL_LINE_ID
,LINK_TO_LINE_ID
,ATO_LINE_ID
,PARENT_ATO_LINE_ID
,INVENTORY_ITEM_ID
,SHIP_FROM_ORG_ID
,COMPONENT_SEQUENCE_ID
,COMPONENT_CODE
,ITEM_TYPE_CODE
,SCHEDULE_SHIP_DATE
,PLAN_LEVEL
,PERFORM_MATCH
,CONFIG_ITEM_ID
,BOM_ITEM_TYPE
,WIP_SUPPLY_TYPE
,ORDERED_QUANTITY
,ORDER_QUANTITY_UOM
,BATCH_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REUSE_CONFIG
,OPTION_SPECIFIC
,QTY_PER_PARENT_MODEL
,CONFIG_CREATION
,SHIP_FROM_ORG_ID --for bugfix3555026
from bom_cto_order_lines
where ato_line_id = pTopAtoLineId ;
select reuse_config, config_item_id , config_creation
into v_reuse_config_flag, v_reuse_config_item_id , v_reuse_config_creation
from bom_cto_order_lines
where line_id = pTopAtoLineId ;
select 'Y' into v_bcso_data_exists
from bom_cto_src_orgs
where top_model_line_id = pTopAtoLineId
and rownum = 1; -- Bug Fix 5532777
delete from bom_cto_src_orgs_b where top_model_line_id = pTopAtoLineId ;
' deleted from bcso_b as reuse is not applicable or doesnt exist '
|| to_char(sql%rowcount) , 5);
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
perform_match , config_item_id into x_match_found , x_top_matched_item_id
from bom_cto_order_lines_gt
where line_id = pTopAtoLineId ;
update bom_cto_order_lines bcol
set ( bcol.perform_match, bcol.config_item_id ) =
( select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bcol_gt.perform_match, bcol_gt.config_item_id
from bom_cto_order_lines_gt bcol_gt
where bcol.line_id = bcol_gt.line_id )
where bcol.ato_line_id = pTopAtoLineId ;
select concatenated_segment_delimiter
into lSegDel
from fnd_id_flex_structures
where application_id = 401
and id_flex_code = 'MSTK'
and id_flex_num = 101;
select line_id,
inventory_item_id,
config_item_id,
parent_ato_line_id,
config_creation
from bom_cto_order_lines
where ato_line_id = pTopAtoLineId
and ( bom_item_type = '1' )
and nvl(wip_supply_type,0) <> '6'
order by plan_level desc; -- added order by clause for wt/vol project
v_update_count number ;
select perform_match
into v_perform_match
from bom_cto_order_lines
where line_id = v_model_lines.line_id;
v_update_count := null ; /* this has to be initialized to null for the loop below */
if (v_parent_ato_line_id = v_ato_line_id or v_update_count = 0 ) then
exit;
update bom_cto_order_lines
set perform_match = 'U' /* Unsuccessful Match */
where line_id = v_parent_ato_line_id
and perform_match = 'Y'
returning parent_ato_line_id , ato_line_id
into v_parent_ato_line_id , v_ato_line_id ;
v_update_count := SQL%rowcount ;
|| ' upd count ' || v_update_count , 1);
/* update matched config in bcol and bcol_temp */
update bom_cto_order_lines
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id
returning config_creation into v_bcmo_config_orgs ;
'update bcol count::'||
SQL%ROWCOUNT , 1);
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
|| ' for ' || v_model_lines.line_id
|| ' config_orgs ' || v_bcmo_config_orgs
|| ' rows ' || SQL%ROWCOUNT, 1);
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol_gt ' || lXConfigId
|| ' for ' || v_model_lines.line_id
|| ' rows ' || SQL%ROWCOUNT, 1);
select group_reference_id into v_bcso_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs
set config_item_id = lXConfigId
where group_reference_id = v_bcso_group_reference_id ;
oe_debug_pub.add ('Create_All_items: matched item ' || 'updated bcmo ' || lXConfigId
|| ' line id info ' || v_model_lines.line_id
|| ' for ' || v_bcso_group_reference_id
|| ' rows ' || SQL%ROWCOUNT, 1);
update bom_cto_src_orgs_b
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcso_b ' || lXConfigId
|| ' for ' || v_model_lines.line_id
|| ' rows ' || SQL%ROWCOUNT, 1);
/* update newly created config in bcol and bcol_temp */
update bom_cto_order_lines
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id
returning perform_match into v_perform_match;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
|| ' for ' || v_model_lines.line_id , 1);
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol_gt ' || lXConfigId
|| ' for ' || v_model_lines.line_id , 1);
update bom_cto_src_orgs_b
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcso ' || lXConfigId
|| ' for ' || v_model_lines.line_id
|| ' config_creation ' || v_model_lines.config_creation
|| ' rows ' || SQL%ROWCOUNT, 1);
select group_reference_id into v_bcso_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs
set config_item_id = lXConfigId
where group_reference_id = v_bcso_group_reference_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcmo ' || lXConfigId
|| ' for ' || v_bcso_group_reference_id
|| ' rows ' || SQL%ROWCOUNT, 1);
select 1 /* BCMO not in synch with Model Item */ into v_model_item_status from dual
where exists ( select organization_id from mtl_system_items msi
where not exists
( select organization_id from bom_cto_model_orgs bcmo
where bcmo.config_item_id = lXConfigId
and bcmo.organization_id = msi.organization_id )
and msi.inventory_item_id = v_model_lines.inventory_item_id ) ;
select 1 /*Config not in synch with Model Item */ into v_config_item_status from dual
where exists ( select organization_id from mtl_system_items model
where not exists
( select organization_id from mtl_system_items config
where config.inventory_item_id = lXConfigId
and config.organization_id = model.organization_id )
and model.inventory_item_id = v_model_lines.inventory_item_id ) ;
select concatenated_segments into v_model_item_name
from mtl_system_items_kfv
where inventory_item_id = v_model_lines.inventory_item_id
and rownum = 1 ;
select concatenated_segments into v_config_item_name
from mtl_system_items_kfv
where inventory_item_id = lXConfigId
and rownum = 1 ;
/* update newly created config in bcol and bcol_temp */
update bom_cto_order_lines
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
|| ' for ' || v_model_lines.line_id , 1);
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol_gt ' || lXConfigId
|| ' for ' || v_model_lines.line_id , 1);
select group_reference_id into v_bcso_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs
set config_item_id = lXConfigId
where group_reference_id = v_bcso_group_reference_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcmo ' || lXConfigId
|| ' for ' || v_bcso_group_reference_id
|| ' rows ' || SQL%ROWCOUNT, 1);
update bom_cto_src_orgs_b
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcso ' || lXConfigId
|| ' for ' || v_model_lines.line_id
|| ' rows ' || SQL%ROWCOUNT, 1);
/* update newly created config in bcol and bcol_temp */
update bom_cto_order_lines
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
|| ' for ' || v_model_lines.line_id , 1);
update bom_cto_src_orgs_b
set config_item_id = lXConfigId
where line_id = v_model_lines.line_id ;
oe_debug_pub.add ('Create_All_items: ' || 'updated bcso ' || lXConfigId
|| ' for ' || v_model_lines.line_id
|| ' config_creation ' || v_model_lines.config_creation
|| ' rows ' || SQL%ROWCOUNT, 1);
CTO_CONFIG_ITEM_PK.g_wt_tbl.delete;
CTO_CONFIG_ITEM_PK.g_vol_tbl.delete;
select line_id, parent_ato_line_id
from bom_cto_order_lines
where bom_item_type = '1'
and ato_line_id = p_ato_line_id
and nvl(wip_supply_type,0) <> 6
order by plan_level desc;
select count(*) into v_bcol_count from bom_cto_order_lines
where ato_line_id = p_ato_line_id ;
select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
count(*) into v_bcol_gt_count from bom_cto_order_lines_gt
where ato_line_id = p_ato_line_id ;
select perform_match into l_perform_match
from bom_cto_order_lines
where line_id = l_next_rec.line_id ;
update bom_cto_order_lines set perform_match = 'U'
where perform_match = 'Y'
and line_id = l_next_rec.parent_ato_line_id ;
/* update the perform match column to 'U' so that this item is canned */
begin
update bom_cto_order_lines
set perform_match = 'U'
where line_id = l_next_rec.line_id
and perform_match = 'Y';
/* update the perform match column to 'U' so that no match
is attempted against its parent and it is canned
*/
begin
update bom_cto_order_lines
set perform_match = 'U'
where line_id = l_next_rec.parent_ato_line_id
and perform_match = 'Y';
update bom_cto_order_lines
set config_item_id = l_x_config_id
where line_id = l_next_rec.line_id;
oe_debug_pub.add( 'perform_match: bcol update ' || SQL%rowcount , 1 ) ;
update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt
set config_item_id = l_x_config_id
where line_id = l_next_rec.line_id;
oe_debug_pub.add( 'perform_match: bcol_gt update ' || SQL%rowcount , 1 ) ;
select config_creation , line_id , parent_ato_line_id
from bom_cto_order_lines
where ato_line_id = p_ato_line_id
and bom_item_type = '1' and nvl(wip_supply_type, 1 ) <> 6
order by plan_level desc ;