[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| config item and its child and insert them into cst_sc_lists |
| |
| |
| ARGUMENTS |
| Input : Config Item : Select this config item |
| Base Model Item : All configs for this base model |
| Item Created Days Ago : All configs created in last "n" |
| days. |
| Organization : Calculate cost for all configs in|
| this org. |
| HISTORY |
| Date Author Comments |
| --------- -------- ---------------------------------------------------- |
| 10/27/2003 KSARKAR creation of body CTO_COST_ROLLUP_CONC_PK |
| |
| 08/06/2004 Sushant Sawant Modified |
| Bugfix 3777922 |
| Changed code to process sourcing for parent before |
| child config item. This will ensure child sourcing |
| starts from the end manufacturing org of the parent config |
| Only Top config items will be picked from main cursor for |
| upgrade scenario as get_config_details will return child |
| configurations |
|
| Bugfix 3784283
| Cost Rollup will be performed in batches of approx 100 records
| Total# of records should be >= 100 to be considered as a batch
| A batch will consist of parent configs and all their children.
| A logical break will consider parents and all their children
| A savepoint will be created after each batch is processed.
| A rollback to the previous savepoint will be performed for an
| erroneous batch. Processing will continue for remaining records.
| A summary of successful/failed configuration items will be
| provided at the end of the program.
|
| |
| |
| |
| 11/23/2004 Sushant Sawant Modified |
| bugfix 3941383 |
| cost rollup for child configuration is not performed in |
| the root sourcing org if child model has 100% transfer from
| sourcing rule.
| |
| |
| |
| 11/23/2004 Sushant Sawant Modified |
| bugfix 3975083 |
| Optional cost rollup process fails when processing multiple |
| batches. |
| Modified the code to reinitialize the index variable to |
| collect data for next batch in cfg_item_array array. |
| |
| |
*==========================================================================================*/
gMrpAssignmentSet number ;
select msi.organization_id
from mtl_system_items msi
where msi.inventory_item_id = x_config_item
and msi.inventory_item_status_code <>
( select nvl(bom_delete_status_code,'-99') -- bug fix 5276658
from bom_parameters bp
where bp.organization_id =msi.organization_id);
select distinct bet.component_item_id,msi_b.config_orgs,bet.plan_level
from bom_explosion_temp bet
, mtl_system_items msi
, mtl_system_items msi_b
where bet.group_id = xgrp_id
and bet.component_item_id = msi.inventory_item_id
and bet.organization_id = msi.organization_id
and msi_b.inventory_item_id = msi.base_item_id
and msi_b.organization_id = msi.organization_id
ORDER BY plan_level asc;
select distinct organization_id
from bom_cto_src_orgs_gt
where config_item_id = x_config_item ;
select distinct organization_id
from bom_cto_src_orgs_gt
where config_item_id = x_config_item
and rcv_org_id = organization_id ;
select distinct msi.inventory_item_id,
nvl(msi_b.config_orgs,'1')
from mtl_system_items msi,
mtl_system_items msi_b
where msi.base_item_id is not null
and msi.base_item_id = msi_b.inventory_item_id
and msi.organization_id = msi_b.organization_id
and msi.inventory_item_status_code <>
( select bom_delete_status_code
from bom_parameters bp
where bp.organization_id =msi.organization_id
)
and (p_model_id is null or
msi.base_item_id = p_model_id)
and (p_config_item_id is null or
msi.inventory_item_id = p_config_item_id)
and (p_org_id is null or
msi.organization_id = p_org_id)
and (p_num_of_days is null or
msi.creation_date > ( trunc(sysdate) - p_num_of_days ))
ORDER BY 1; */
sql_stmt := 'select distinct msi.inventory_item_id, ' ||
' nvl(msi_b.config_orgs,''1'') ' ||
' from mtl_system_items msi, ' ||
' mtl_system_items msi_b ' ||
' where msi.base_item_id is not null ' ||
' and msi.base_item_id = msi_b.inventory_item_id ' ||
' and msi.organization_id = msi_b.organization_id ' ||
' and msi.inventory_item_status_code <> ' ||
' ( select bom_delete_status_code ' ||
' from bom_parameters bp ' ||
' where bp.organization_id =msi.organization_id ' ||
' )';
select distinct msi.inventory_item_id,
nvl( msi_b.config_orgs , '1')
from bom_cto_order_lines_upg bcol_upg,
mtl_system_items msi,
mtl_system_items msi_b
where bcol_upg.line_id = bcol_upg.ato_line_id /* bugfix 3777922 */
and bcol_upg.config_item_id = msi.inventory_item_id
and msi.base_item_id is not null
and msi.base_item_id = msi_b.inventory_item_id
and msi.organization_id = msi_b.organization_id
and bcol_upg.inventory_item_id = msi_b.inventory_item_id
and msi.inventory_item_status_code <>
( select bom_delete_status_code
from bom_parameters bp
where bp.organization_id = msi.organization_id
)
ORDER BY 1;
delete from bom_cto_src_orgs_gt ;
WriteToLog('deleted from bom_cto_src_orgs_gt : '|| sql%rowcount );
insert into bom_cto_src_orgs_gt
(
config_item_id,
organization_id,
rcv_org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
l_config_item,
p_org_id,
null , /* this is intentionally null to indicate origin 100%transfer from org */
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs_gt
where rcv_org_id = p_org_id
and organization_id = p_org_id
and config_item_id = l_config_item );
insert into bom_cto_src_orgs_gt
(
config_item_id,
organization_id,
rcv_org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
l_child_config_item,
l_parent_org_id, -- bugfix 3777922
null , -- bugfix 3777922 /* this is intentionally null for origin 100% transfer from org*/
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs_gt
where rcv_org_id = l_parent_org_id -- bugfix 3777922
and organization_id = l_parent_org_id -- bugfix 3777922
and config_item_id = l_child_config_item ) ; -- bugfix 3941383
cfg_item_arr.delete ;
SELECT substrb(kfv.concatenated_segments,1,35),
mp.organization_code
INTO l_config_description, l_org_code
FROM mtl_system_items_kfv kfv, mtl_parameters mp
WHERE kfv.inventory_item_id = succ_item_arr(curr_count).cfg_item_id
AND kfv.organization_id = succ_item_arr(curr_count).cfg_org_id
AND kfv.organization_id = mp.organization_id;
SELECT substrb(kfv.concatenated_segments,1,35),
mp.organization_code
INTO l_config_description, l_org_code
FROM mtl_system_items_kfv kfv, mtl_parameters mp
WHERE kfv.inventory_item_id = fail_item_arr(curr_count).cfg_item_id
AND kfv.organization_id = fail_item_arr(curr_count).cfg_org_id
AND kfv.organization_id = mp.organization_id;
select distinct 'Y'
into l_circular_src
from bom_cto_src_orgs_gt
where config_item_id = p_config_item_id
and rcv_org_id = l_curr_src_org;
WriteToLog( 'going to insert bcso for type 1,2 ') ;
insert into bom_cto_src_orgs_gt
(
config_item_id,
organization_id,
rcv_org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
p_config_item_id,
l_curr_src_org,
p_organization_id,
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs_gt
where rcv_org_id = p_organization_id
and organization_id = l_curr_src_org
and config_item_id = p_config_item_id );
WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
WriteToLog( 'inserted' || sql%rowcount || 'records in bcso for type 1,2.') ;
WriteToLog( 'inserted bcso for type 1,2 rcv_org = ' || p_organization_id || ', src_org = ' || l_curr_src_org) ;
insert into bom_cto_src_orgs_gt
(
config_item_id,
organization_id,
rcv_org_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
p_config_item_id,
l_curr_src_org,
p_organization_id,
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs_gt
where rcv_org_id = p_organization_id
and organization_id = l_curr_src_org
and config_item_id = p_config_item_id );
WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
WriteToLog( 'inserted' || sql%rowcount || 'records in bcso for type 3.') ;
WriteToLog( 'inserted bcso for type 3 rcv_org = ' || p_organization_id || ', src_org = ' || l_curr_src_org) ;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_config_item_id
AND organization_id = p_organization_id ;
insert into bom_cto_src_orgs_gt
(
config_item_id,
rcv_org_id,
organization_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select p_config_item_id ,
p_organization_id, -- will work for end of chain source or no source
p_organization_id,
sysdate, -- creation_date
gUserId, -- created_by
sysdate, -- last_update_date
gUserId, -- last_updated_by
gLoginId, -- last_update_login
null, -- program_application_id,??
null, -- program_id,??
sysdate -- program_update_date
from dual
where NOT EXISTS
(select NULL
from bom_cto_src_orgs_gt
where rcv_org_id = p_organization_id
and organization_id =p_organization_id
and config_item_id = p_config_item_id );
WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
WriteToLog( 'inserted bcso for end of chain ' || SQL%rowcount) ;