[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 2. Update these tables with the config_item_id
| 3. Copy sourcing rule assignments from model to config item
|
|HISTORY : Created on 04-OCT-2003 by Sushant Sawant
|
| Modified on 09-JAN-2004 by Sushant Sawant
| Fixed Bug# 3349142
| fixed insert into bcso for dropship/procure/no assignment set scenarios.
|
|
| Modified on 12-FEB-2004 by Sushant Sawant
| Fixed Bug# 3418684
| Changed logic to not fork processing based in source_type
| Supply Chain will be traversed for CIB = 1,2 irrespective of source_type
|
| Modified : 02-MAR-2004 Sushant Sawant
| Fixed Bug 3472654
| upgrades for matched config from CIB = 1 or 2 to 3 were not performed properly.
| data was not transformed to bcmo.
| perform_match check includes 'Y' and 'U'
|
|
| Modified : 17-MAR-2004 Sushant Sawant
| Fixed bug 3504744.
| bom_parameter may not exist for some organizations.
|
| Modified : 29-APR-2004 Sushant Sawant
| Fixed bug 3598139
| changed cursor c_parent_src_orgs to account for buy models and their children as
| create_bom flag may not be set to 'Y' for such models.
|
|
| Modified : 14-MAY-2004 Sushant Sawant
| Fixed bug 3484511.
|
|
| Modified : 14-MAY-2004 Sushant Sawant
| Fixed bug 3640783. Sourcing across Operating Units with PO and OE
| validation org as part of the supply chain for CIB = 1 results in errors.
| This issue has been addressed as part of this fix.
|
| modfieid 26-JUL-2004 Kiran Konada
| 3785158
| values were not incremented properly corrected
|
| Modified : 14-APR-2005 Sushant Sawant
| Fixed bug fp bug 4227127. This is fp for bug 4162642.
| Exception handling added for call to get_other_orgs.
| Exception handling added to get_other_orgs procedure.
| Original issue of handling sparse and or empty array after deleting orgs from
| the validation org list was already handled in 11.5.10 as part of bug 3640783.
|
| Modified : 05-Jul-2005 Renga Kannan
| Modified for MOAC project
+-----------------------------------------------------------------------------*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_MSUTIL_PUB';
procedure insert_type3_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER
, p_config_item_id in NUMBER default null ) ;
procedure insert_type3_bcmo_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER) ;
procedure insert_type3_referenced_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER
, p_config_item_id in NUMBER default null ) ;
select line_id,
ato_line_id,
inventory_item_id,
plan_level,
config_creation,
perform_match,
config_item_id,
option_specific
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;
select distinct bcso.organization_id
from bom_cto_src_orgs bcso,
bom_cto_order_lines bcol
where bcol.line_id = lLineId
and bcol.parent_ato_line_id = bcso.line_id
and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3' , '4') ) ; /* 3598139 Buy Models may not have a bom */
select line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
create_src_rules,
organization_type,
group_reference_id
from bom_cto_src_orgs
where top_model_line_id = pTopAtoLineId;
select line_id , inventory_item_id , config_creation from bom_cto_order_lines
where bom_item_type = 1 and nvl(wip_supply_type, 1) <> 6
and ato_line_id = pTopAtoLineId order by plan_level ;
select ship_from_org_id
into lShipFromOrgId
from bom_cto_order_lines
where line_id = v_model_lines.line_id;
oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_bcso ' || v_model_lines.line_id , 1 ) ;
CTO_MSUTIL_PUB.insert_type3_bcso( pTopAtoLineId
, v_model_lines.line_id
, v_model_lines.inventory_item_id ) ;
oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_referenced_bcso ' || v_model_lines.line_id , 1 ) ;
CTO_MSUTIL_PUB.insert_type3_referenced_bcso( pTopAtoLineId
, v_model_lines.line_id
, v_model_lines.inventory_item_id
, v_model_lines.config_item_id) ;
oe_debug_pub.add( '$$$$$$$$ GOING TO CALL insert_type3_bcmo_bcso ' || v_model_lines.line_id , 1 ) ;
CTO_MSUTIL_PUB.insert_type3_bcmo_bcso( pTopAtoLineId
, v_model_lines.line_id
, v_model_lines.inventory_item_id ) ;
oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs set create_bom = 'N'
where group_reference_id = v_group_reference_id ;
oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
update bom_cto_src_orgs_b set create_bom = 'N'
where line_id = v_model_lines.line_id ;
oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs set create_bom = 'Y'
where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
-- bugfix 4274446 : Check create_config_bom parameter
and exists
( select 1 from bom_parameters
where organization_id = v_orgs_list(i)
and nvl(create_config_bom,'N') = 'Y' );
oe_debug_pub.add( 'UPDATED BCMO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
update bom_cto_src_orgs_b set create_bom = 'Y'
where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
-- bugfix 4274446 : Check create_config_bom parameter
and exists
( select 1 from bom_parameters
where organization_id = v_orgs_list(i)
and nvl(create_config_bom,'N') = 'Y' );
oe_debug_pub.add( 'UPDATED BCSO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list ) ;
CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId,
v_current_model_line_id,
v_current_model_item_id,
x_orgs_list ) ;
CTO_MSUTIL_PUB.insert_all_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id ) ;
oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
/*Update Create_BOM Flag for Shared Costing Organizations */
update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
where bcso_b.top_model_line_id = pTopAtoLineId
and bcol.ato_line_id = pTopAtoLineId
and bcol.line_id = bcso_b.line_id
and bcol.config_creation in ( 1 , 2 )
and mp.organization_id = bcso_b.organization_id
and mp.organization_id <> mp.cost_organization_id
and bcso_b.create_bom = 'Y' )
and exists ( select 1 from bom_parameters bp
where bp.organization_id = bcso_b1.organization_id
and bp.create_config_bom = 'Y' ) ;
oe_debug_pub.add( '$$$$$$$$ Create BOM flag updated for Shared Costing Organizations ' || to_char(sql%rowcount) , 1 ) ;
/* print debug output for Shared Cost update */
if( sql%rowcount > 0 ) then
FOR v_debug IN c_debug LOOP
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
to_char(v_debug.model_item_id)||' '||
nvl(to_char(v_debug.rcv_org_id),null)||' '||
to_char(v_debug.organization_id)||' '||
nvl(v_debug.create_bom, null)||' '||
nvl(v_debug.create_src_rules, null) || ' ' ||
nvl(v_debug.organization_type, null) || ' ' ||
nvl(v_debug.group_reference_id , null), 2);
select line_id,
ato_line_id,
inventory_item_id,
plan_level,
config_creation,
perform_match,
config_item_id,
option_specific
from bom_cto_order_lines_upg
where ato_line_id = pTopAtoLineId
and bom_item_type = 1
and nvl(wip_supply_type,0) <> 6
order by plan_level;
select distinct bcso.organization_id
from bom_cto_src_orgs bcso,
bom_cto_order_lines_upg bcol
where bcol.line_id = lLineId
and bcol.parent_ato_line_id = bcso.line_id
and bcso.create_bom = 'Y';
select line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
create_src_rules
from bom_cto_src_orgs
where top_model_line_id = pTopAtoLineId;
select line_id , inventory_item_id , config_creation, config_item_id
from bom_cto_order_lines_upg
where bom_item_type = 1 and nvl(wip_supply_type, 1) <> 6
and ato_line_id = pTopAtoLineId order by plan_level ;
select ship_from_org_id
into lShipFromOrgId
from bom_cto_order_lines_upg
where line_id = v_model_lines.line_id;
CTO_MSUTIL_PUB.insert_type3_referenced_bcso( pTopAtoLineId
, v_model_lines.line_id
, v_model_lines.inventory_item_id
, v_model_lines.config_item_id) ;
CTO_MSUTIL_PUB.insert_type3_bcso( pTopAtoLineId
, v_model_lines.line_id
, v_model_lines.inventory_item_id
, v_model_lines.config_item_id) ;
oe_debug_pub.add( 'OSS ORGS for Create BOM UPDATE ' , 1 ) ;
select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs set create_bom = 'N'
where group_reference_id = v_group_reference_id ;
oe_debug_pub.add( 'UPDATED BCMO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
update bom_cto_src_orgs_b set create_bom = 'N'
where line_id = v_model_lines.line_id ;
oe_debug_pub.add( 'UPDATED BCSO create_bom = N for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
select group_reference_id into v_group_reference_id from bom_cto_src_orgs_b
where line_id = v_model_lines.line_id ;
update bom_cto_model_orgs set create_bom = 'Y'
where group_reference_id = v_group_reference_id and organization_id = v_orgs_list(i)
-- bugfix 4274446 : Check create_config_bom parameter
and exists
( select 1 from bom_parameters
where organization_id = v_orgs_list(i)
and nvl(create_config_bom,'N') = 'Y' );
oe_debug_pub.add( 'UPDATED BCMO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
update bom_cto_src_orgs_b set create_bom = 'Y'
where line_id = v_model_lines.line_id and organization_id = v_orgs_list(i)
-- bugfix 4274446 : Check create_config_bom parameter
and exists
( select 1 from bom_parameters
where organization_id = v_orgs_list(i)
and nvl(create_config_bom,'N') = 'Y' );
oe_debug_pub.add( 'UPDATED BCSO create_bom = Y for line id ' || v_model_lines.line_id || ' rows ' || SQL%ROWCOUNT , 1 ) ;
oe_debug_pub.add( '$$$$$$$$ Going to update Create BOM flag for Shared Costing Organizations ' , 1 ) ;
/*Update Create_BOM Flag for Shared Costing Organizations */
update bom_cto_src_orgs_b bcso_b1 set create_bom = 'Y'
where ( organization_id , line_id ) in ( select mp.cost_organization_id , bcso_b.line_id
from mtl_parameters mp, bom_cto_src_orgs_b bcso_b, bom_cto_order_lines bcol
where bcso_b.top_model_line_id = pTopAtoLineId
and bcol.ato_line_id = pTopAtoLineId
and bcol.line_id = bcso_b.line_id
and bcol.config_creation in ( 1 , 2 )
and mp.organization_id = bcso_b.organization_id
and mp.organization_id <> mp.cost_organization_id
and bcso_b.create_bom = 'Y' )
and exists ( select 1 from bom_parameters bp
where bp.organization_id = bcso_b1.organization_id
and bp.create_config_bom = 'Y' ) ;
oe_debug_pub.add( '$$$$$$$$ Create BOM flag updated for Shared Costing Organizations ' || to_char(sql%rowcount) , 1 ) ;
/* print debug output for Shared Cost update */
if( sql%rowcount > 0 ) then
FOR v_debug IN c_debug LOOP
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('populate_plan_level: ' || to_char(v_debug.line_id)||' '||
to_char(v_debug.model_item_id)||' '||
nvl(to_char(v_debug.rcv_org_id),null)||' '||
to_char(v_debug.organization_id)||' '||
nvl(v_debug.create_bom, null)||' '||
nvl(v_debug.create_src_rules, null), 2);
CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id, v_t_org_list , v_current_config_item_id) ;
CTO_MSUTIL_PUB.insert_val_into_bcso( pTopAtoLineId,
v_current_model_line_id,
v_current_model_item_id,
x_orgs_list ) ;
CTO_MSUTIL_PUB.insert_all_into_bcso( pTopAtoLineId, v_current_model_line_id, v_current_model_item_id , v_current_config_item_id) ;
select 'Y'
from bom_cto_src_orgs bcso
where line_id = pLineId
and model_item_id = pModelItemId
and rcv_org_id = l_curr_src_org;
select ato_line_id,
program_id
into lTopAtoLineId,
lProgramId
from bom_cto_order_lines
where line_id = pLineId;
select ato_line_id,parent_ato_line_id, nvl(program_id,0)
, config_creation , perform_match , option_specific /* added by sushant for preconfigure bom identification */
into lTopAtoLineId,l_parent_ato_line_id, lProgramId
, lConfigCreation, lPerformMatch , lOptionSpecific
from bom_cto_order_lines
where line_id = pLineId;
Select organization_type
Into l_source_type
from bom_cto_src_orgs bcso
where bcso.line_id = l_parent_ato_line_id
and ( bcso.create_bom = 'Y' or bcso.organization_type in ( '3', '2'))
and organization_id = pRcvOrgId ;
select source_type_code
into v_source_type_code
from oe_order_lines_all
where line_id = pLineId ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type,
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
pRcvOrgId,
pRcvOrgId,
-- 'Y' , /* this statement is executed for lower buy models */
'N' , /* create bom should be no for org type 4 */
-- decode( bp.create_config_bom , 'Y', decode( bbom.common_bill_sequence_id , null ,'N' , 'Y') , 'N' ) , -- create_bom
'N' , /* cost rollup should be no for org type 4 */
-- decode( l_source_type , 4 , 'N' , 6 , 'N' , 'Y' ) , -- cost_rollup
l_source_type, -- org_type is used to store the source_type
p_config_item_id, -- config_item_id
decode(l_curr_assg_type,6,'Y',3,'Y','N'), -- create_src_rules
NULL, -- rank, n/a
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 bom_parameters bp, bom_bill_of_materials bbom
where bp.organization_id = pRcvOrgId
and bp.organization_id = bbom.organization_id (+)
and pModelItemId = bbom.assembly_item_id (+)
and bbom.alternate_bom_designator is null
and NOT EXISTS
(select NULL
from bom_cto_src_orgs_b
where line_id = pLineId
and organization_id = pRcvOrgId
and rcv_org_id = pRcvOrgId
and model_item_id = pModelItemId);
oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for procured child model same org id, rcv org id ' || SQL%rowcount
|| ' at stmt ' || to_char(lStmtNumber) ,2);
oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
|| ' line ' || to_char(pLineId) ,2);
select planning_make_buy_code
into l_make_buy_code
from MTL_SYSTEM_ITEMS
where inventory_item_id = pModelItemId
and organization_id = pRcvOrgId;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
ltopatolineid ,
plineid ,
pmodelitemid ,
null ,
l_curr_src_org,
decode( bp.create_config_bom , 'Y',
decode( bbom.common_bill_sequence_id , null ,'N' , 'Y') , 'N' ) , -- create_bom
'Y', -- cost_rollup
l_source_type, -- org_type is used to store the source type
p_config_item_id, -- config_item_id
decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
l_curr_rank,
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 bom_parameters bp, bom_bill_of_materials bbom
where bp.organization_id = pRcvOrgId
and bp.organization_id = bbom.organization_id (+)
and pModelItemId = bbom.assembly_item_id (+)
and bbom.alternate_bom_designator is null
and NOT EXISTS
(select NULL
from bom_cto_src_orgs_b
where line_id = pLineId
and organization_id = pRcvOrgId
and model_item_id = pModelItemId);
oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || SQL%ROWCOUNT || ' at stmt ' || to_char(lStmtNumber) ,2);
oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
|| ' line ' || to_char(plineid) , 2 );
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type,
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select
lTopAtoLineId,
pLineId,
pModelItemId,
pRcvOrgId,
pRcvOrgId,
'N' , /* this statement is executed when there are onyly transfer from sourcing rules in shipping org */
/*decode( bp.create_config_bom , 'Y', decode( bbom.common_bill_sequence_id , null ,'N' , 'Y') , 'N' ) , -- create_bom */
decode( l_source_type , 4 , 'N' , 6 , 'N' , 'Y' ) , -- cost_rollup
l_source_type, -- org_type is used to store the source_type
p_config_item_id, -- config_item_id
decode(l_curr_assg_type,6,'Y',3,'Y','N'), -- create_src_rules
NULL, -- rank, n/a
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 bom_parameters bp, bom_bill_of_materials bbom
where bp.organization_id = pRcvOrgId
and bp.organization_id = bbom.organization_id (+)
and pModelItemId = bbom.assembly_item_id (+)
and bbom.alternate_bom_designator is null
and NOT EXISTS
(select NULL
from bom_cto_src_orgs_b
where line_id = pLineId
and organization_id = pRcvOrgId
and rcv_org_id = pRcvOrgId
and model_item_id = pModelItemId);
oe_debug_pub.add('Get_All_Item_Orgs: ' || 'Inserted in BCSO for transfer same org id, rcv org id ' || SQL%rowcount
|| ' at stmt ' || to_char(lStmtNumber) ,2);
oe_debug_pub.add('Get_All_Iitem_Orgs: ' || 'Inserted into BCSO ' || ' for model ' || to_char( pmodelitemid )
|| ' line ' || to_char(plineid) ,2);
select nvl( option_specific , 'N' ) into v_option_specific from bom_cto_order_lines
where line_id = p_line_id ;
select nvl( option_specific , 'N' ) into v_option_specific from bom_cto_order_lines_upg
where line_id = p_line_id ;
oe_debug_pub.add( 'going to insert bcso for type 1,2 ' , 1 ) ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id,
p_line_id,
p_model_item_id,
p_organization_id,
l_curr_src_org,
decode( l_source_type , 2 ,
decode( bp.create_config_bom, 'Y', 'Y' , 'N' )
, 'N' ), -- create_bom
'Y', -- cost_rollup
l_source_type, -- org_type is used to store the source type
p_config_item_id , -- config_item_id
decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
l_curr_rank,
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 bom_parameters bp
where bp.organization_id = l_curr_src_org
and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
(select NULL
from bom_cto_src_orgs_b
where line_id = p_line_id
and rcv_org_id = p_organization_id
and organization_id = l_curr_src_org
and organization_type = l_source_type
and model_item_id = p_model_item_id );
oe_debug_pub.add( 'inserted bcso for type 1,2 ' || SQL%rowcount , 1 ) ;
oe_debug_pub.add( 'inserted bcso for type 1,2 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id,
p_line_id,
p_model_item_id,
p_organization_id,
l_curr_src_org,
decode( v_100_procured , 'Y' , 'N' ,
decode( bp.create_config_bom, 'Y', 'Y' , 'N')
) ,-- create_bom /* 100 % procured will be 'N' */
'Y', -- cost_rollup
l_source_type, -- org_type is used to store the source type
p_config_item_id, -- config_item_id
decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
l_curr_rank,
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 bom_parameters bp
where bp.organization_id = l_curr_src_org
and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
(select NULL
from bom_cto_src_orgs_b
where line_id = p_line_id
and rcv_org_id = p_organization_id
and organization_id = l_curr_src_org
and organization_type = l_source_type
and model_item_id = p_model_item_id );
oe_debug_pub.add( 'inserted bcso for type 3 ' || SQL%rowcount , 1 ) ;
oe_debug_pub.add( 'inserted bcso for type 3 rcv ' || p_organization_id || ' org ' || l_curr_src_org , 1 ) ;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_model_item_id
AND organization_id = p_organization_id ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id ,
p_line_id ,
p_model_item_id ,
p_organization_id, /* will work for end of chain source or no source */
p_organization_id,
decode( v_100_procured , 'Y' , 'N' , decode( bp.create_config_bom , 'Y',
decode(bom.assembly_item_id, null , 'N', 'Y')
, 'N')) , -- create_bom
'Y', -- cost_rollup
l_source_type, -- org_type is used to store the source type
p_config_item_id , -- config_item_id
decode(l_curr_assg_type, 6, 'Y', 3, 'Y', 'N'),
l_curr_rank,
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 bom_bill_of_materials bom, bom_parameters bp
where p_organization_id = bp.organization_id
and p_model_item_id = bom.assembly_item_id(+)
and bp.organization_id = bom.organization_id(+)
and bom.alternate_bom_designator is null
and NOT EXISTS /* NOT EXISTS should be there to check whether same org is reached thru other paths */
(select NULL
from bom_cto_src_orgs_b
where line_id = p_line_id
and rcv_org_id = p_organization_id
and organization_id = p_organization_id
and organization_type = l_source_type
and model_item_id = p_model_item_id ) ;
oe_debug_pub.add( 'inserted bcso for end of chain ' || SQL%rowcount , 1 ) ;
oe_debug_pub.add( 'inserted bcso for end of chain ' || p_organization_id ||
' org ' || p_organization_id
, 1 ) ;
x_orgs_tbl.delete(p_organization_id); --Bugfix 7522447/7410091
oe_debug_pub.add('process_sourcing_chain: ' || 'Org deleted from collection: '||to_char(p_organization_id), 1);
select distinct
source_organization_id,
sourcing_rule_id,
nvl(source_type,1) ,
rank,
assignment_id,
assignment_type
from mrp_sources_v msv
where msv.assignment_set_id = gMrpAssignmentSet
and msv.inventory_item_id = p_inventory_item_id
and msv.organization_id = p_organization_id
-- and nvl(msv.source_type,1) <> 3 commented by Renga for BUY odel
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate) -- Nvl fun is added by Renga Kannan on 05/05/2001
and nvl(disable_date, sysdate+1) > sysdate;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT planning_make_buy_code
INTO l_make_buy_code
FROM MTL_SYSTEM_ITEMS
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
select assignment_set_id into lUPGAssignmentSet
from mrp_assignment_sets
where assignment_set_name = 'CTO Configuration Updates' ;
-- added another filter condition assignment_id is not null to select
-- only explicit sourcing rules from mrp_sources_v view definition
BEGIN
select distinct assignment_id, assignment_type
into lAssignmentId, lAssignmentType
from mrp_sources_v msv
where msv.assignment_set_id = lMrpAssignmentSet
and msv.inventory_item_id = pModelItemId
and msv.organization_id = pRcvOrgId
and effective_date <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate
and assignment_type in (3,6)
and assignment_id is not null;
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS
WHERE ASSIGNMENT_ID = lAssignmentId;*/
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS A
WHERE ASSIGNMENT_ID = lAssignmentId
AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
FROM MRP_SR_ASSIGNMENTS B
--Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
--WHERE a.assignment_set_id = b.assignment_set_id and
WHERE (a.assignment_set_id = b.assignment_set_id or
b.assignment_set_id = lUPGAssignmentSet) and
a.assignment_type = b.assignment_type and
nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
b.sourcing_rule_type = a.sourcing_rule_type and
b.inventory_item_id = pConfigId and
nvl(b.category_id,-1) = nvl(a.category_id,-1) and
rownum = 1);
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS A
WHERE ASSIGNMENT_ID = lAssignmentId
AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
FROM MRP_SR_ASSIGNMENTS B
--Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
--WHERE a.assignment_set_id = b.assignment_set_id and
WHERE (a.assignment_set_id = b.assignment_set_id or
b.assignment_set_id = lUPGAssignmentSet) and
a.assignment_type = b.assignment_type and
nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
b.sourcing_rule_type = a.sourcing_rule_type and
b.inventory_item_id IS NULL and
nvl(b.category_id,-1) = nvl(a.category_id,-1) and
rownum = 1);
SELECT mrp_sr_assignments_s.nextval
INTO lConfigAssignmentId
FROM DUAL;
lAssignmentTbl(1).Last_Updated_By := lAssignmentRec.Last_Updated_By;
lAssignmentTbl(1).Last_Update_Date := lAssignmentRec.Last_Update_Date;
lAssignmentTbl(1).Last_Update_Login := lAssignmentRec.Last_Update_Login;
lAssignmentTbl(1).Program_Update_Date := lAssignmentRec.Program_Update_Date;
-- call mrp API to insert rec into assignment set
--
lStmtNum := 60;
select
/*
nvl(rcv.receipt_organization_id,assg.organization_id),
src.source_organization_id,
assg.customer_id,
assg.ship_to_site_id,
src.VENDOR_ID,
vend.VENDOR_SITE_code,
src.RANK,
src.ALLOCATION_PERCENT,
src.SOURCE_TYPE,
assg.sourcing_rule_id,
rcv.sr_receipt_id,
src.sr_source_id,
*/
--Bugfix 13029577: Adding a distinct. This sql returns same assignment_id multiple
--times if there is a global transfer from sourcing rule from multiple orgs. Ex.
--Let the rule be:
--Transfer from M1:50%, M2:30%, M3:15%, M4:5%
--For this sourcing rule, there would be 4 records in table mrp_sr_source_org for one
--value of sr_receipt_id.
--The result is that the same assignment is attempted multiple times. MRP API
--process_assignment throws ORA-00001: unique constraint (MRP.MRP_SR_ASSIGNMENTS_U2)
--violated error.
distinct
assg.assignment_id,
assg.assignment_type
from
mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
mrp_sourcing_rules rule,
po_vendor_sites_all vend
where
assg.assignment_set_id = c_def_assg_set
and assg.inventory_item_id = c_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and assg.sourcing_rule_id = rule.sourcing_rule_id
and rule.planning_active = 1
and rcv.effective_date <= sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.SR_RECEIPT_ID = src.sr_receipt_id
and src.vendor_site_id = vend.vendor_site_id(+) ;
select assignment_set_id,
assignment_type,
organization_id,
customer_id,
ship_to_site_id,
sourcing_rule_type,
category_id
from mrp_sr_assignments
where assignment_set_id = c_def_assg_set
and inventory_item_id = c_item_id;
select assignment_set_id into lUPGAssignmentSet
from mrp_assignment_sets
where assignment_set_name = 'CTO Configuration Updates' ;
select distinct assignment_id, assignment_type
into lAssignmentId, lAssignmentType
from mrp_sources_v msv
where msv.assignment_set_id = lMrpAssignmentSet
and msv.inventory_item_id = pModelItemId
and msv.organization_id = pRcvOrgId
and effective_date <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate
and assignment_type in (3,6);
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS
WHERE ASSIGNMENT_ID = lAssignmentId;*/
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS A
WHERE ASSIGNMENT_ID = lAssignmentId
AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
FROM MRP_SR_ASSIGNMENTS B
--Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
--WHERE a.assignment_set_id = b.assignment_set_id and
WHERE (a.assignment_set_id = b.assignment_set_id or
b.assignment_set_id = lUPGAssignmentSet) and
a.assignment_type = b.assignment_type and
nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
b.sourcing_rule_type = a.sourcing_rule_type and
b.inventory_item_id = pConfigId and
nvl(b.category_id,-1) = nvl(a.category_id,-1) and
rownum = 1);
SELECT ASSIGNMENT_ID
, ASSIGNMENT_SET_ID
, ASSIGNMENT_TYPE
, ATTRIBUTE1
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE_CATEGORY
, CATEGORY_ID
, CATEGORY_SET_ID
, CREATED_BY
, CREATION_DATE
, CUSTOMER_ID
, INVENTORY_ITEM_ID
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, ORGANIZATION_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, SECONDARY_INVENTORY
, SHIP_TO_SITE_ID
, SOURCING_RULE_ID
, SOURCING_RULE_TYPE
into lAssignmentRec.ASSIGNMENT_ID
, lAssignmentRec.ASSIGNMENT_SET_ID
, lAssignmentRec.ASSIGNMENT_TYPE
, lAssignmentRec.ATTRIBUTE1
, lAssignmentRec.ATTRIBUTE10
, lAssignmentRec.ATTRIBUTE11
, lAssignmentRec.ATTRIBUTE12
, lAssignmentRec.ATTRIBUTE13
, lAssignmentRec.ATTRIBUTE14
, lAssignmentRec.ATTRIBUTE15
, lAssignmentRec.ATTRIBUTE2
, lAssignmentRec.ATTRIBUTE3
, lAssignmentRec.ATTRIBUTE4
, lAssignmentRec.ATTRIBUTE5
, lAssignmentRec.ATTRIBUTE6
, lAssignmentRec.ATTRIBUTE7
, lAssignmentRec.ATTRIBUTE8
, lAssignmentRec.ATTRIBUTE9
, lAssignmentRec.ATTRIBUTE_CATEGORY
, lAssignmentRec.CATEGORY_ID
, lAssignmentRec.CATEGORY_SET_ID
, lAssignmentRec.CREATED_BY
, lAssignmentRec.CREATION_DATE
, lAssignmentRec.CUSTOMER_ID
, lAssignmentRec.INVENTORY_ITEM_ID
, lAssignmentRec.LAST_UPDATED_BY
, lAssignmentRec.LAST_UPDATE_DATE
, lAssignmentRec.LAST_UPDATE_LOGIN
, lAssignmentRec.ORGANIZATION_ID
, lAssignmentRec.PROGRAM_APPLICATION_ID
, lAssignmentRec.PROGRAM_ID
, lAssignmentRec.PROGRAM_UPDATE_DATE
, lAssignmentRec.REQUEST_ID
, lAssignmentRec.SECONDARY_INVENTORY
, lAssignmentRec.SHIP_TO_SITE_ID
, lAssignmentRec.SOURCING_RULE_ID
, lAssignmentRec.SOURCING_RULE_TYPE
FROM MRP_SR_ASSIGNMENTS A
WHERE ASSIGNMENT_ID = lAssignmentId
AND NOT EXISTS(SELECT /*+ INDEX(B MRP_SR_ASSIGNMENTS_U2)*/ 1
FROM MRP_SR_ASSIGNMENTS B
--Bugfix 14270815: Checking for the config assignment in lUPGAssignmentSet also.
--WHERE a.assignment_set_id = b.assignment_set_id and
WHERE (a.assignment_set_id = b.assignment_set_id or
b.assignment_set_id = lUPGAssignmentSet) and
a.assignment_type = b.assignment_type and
nvl(b.organization_id,-1) = nvl(a.organization_id,-1) and
nvl(b.customer_id,-1) = nvl(a.customer_id,-1) and
nvl(b.ship_to_site_id,-1) = nvl(a.ship_to_site_id,-1) and
b.sourcing_rule_type = a.sourcing_rule_type and
b.inventory_item_id IS NULL and
nvl(b.category_id,-1) = nvl(a.category_id,-1) and
rownum = 1);
SELECT mrp_sr_assignments_s.nextval
INTO lConfigAssignmentId
FROM DUAL;
lAssignmentTbl(1).Last_Updated_By := lAssignmentRec.Last_Updated_By;
lAssignmentTbl(1).Last_Update_Date := lAssignmentRec.Last_Update_Date;
lAssignmentTbl(1).Last_Update_Login := lAssignmentRec.Last_Update_Login;
lAssignmentTbl(1).Program_Update_Date := lAssignmentRec.Program_Update_Date;
-- call mrp API to insert rec into assignment set
--
lStmtNum := 60;
oe_debug_pub.add('lAssignmentRec.LAST_UPDATED_BY:' || lAssignmentRec.LAST_UPDATED_BY );
oe_debug_pub.add('lAssignmentRec.LAST_UPDATE_DATE:' || lAssignmentRec.LAST_UPDATE_DATE );
oe_debug_pub.add('lAssignmentRec.LAST_UPDATE_LOGIN:' || lAssignmentRec.LAST_UPDATE_LOGIN );
oe_debug_pub.add('lAssignmentRec.PROGRAM_UPDATE_DATE:' || lAssignmentRec.PROGRAM_UPDATE_DATE );
oe_debug_pub.add('lAssignmentTbl(1).Last_Updated_By:' || lAssignmentTbl(1).Last_Updated_By);
oe_debug_pub.add('lAssignmentTbl(1).Last_Update_Date:' || lAssignmentTbl(1).Last_Update_Date);
oe_debug_pub.add('lAssignmentTbl(1).Last_Update_Login:' || lAssignmentTbl(1).Last_Update_Login);
oe_debug_pub.add('lAssignmentTbl(1).Program_Update_Date:' || lAssignmentTbl(1).Program_Update_Date);
select assignment_set_name into assign_set_name
from mrp_Assignment_sets
where assignment_set_id = gMrpAssignmentSet ;
procedure insert_val_into_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER
, p_t_org_list in CTO_MSUTIL_PUB.org_list
, p_config_item_id in number default null)
is
i number ;
oe_debug_pub.add( '$$$ insert val into bcso ' , 1 ) ;
select source_type_code into v_source_type_code
from oe_order_lines_all
where line_id = p_top_ato_line_id ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id ,
p_model_line_id ,
p_model_item_id ,
null ,
p_t_org_list(i),
'N', -- create_bom
'N', -- cost_rollup
/* commented for dropship decode( v_source_type_code , 'INTERNAL' , NULL ,
decode( p_top_ato_line_id, p_model_line_id, '5', '6' )) , -- org_type used for source type
*/
NULL,
p_config_item_id, -- config_item_id
'N',
NULL , /* rank */
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_b
where line_id = p_model_line_id
and model_item_id = p_model_item_id
and organization_id = p_t_org_list(i) );
end insert_val_into_bcso ;
procedure insert_all_into_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER
, p_config_item_id in NUMBER default null)
is
v_source_type_code varchar2(20) ;
oe_debug_pub.add( '$$$ insert all into bcso ' , 1 ) ;
select source_type_code into v_source_type_code
from oe_order_lines_all
where line_id = p_top_ato_line_id ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id ,
p_model_line_id ,
p_model_item_id ,
null ,
msi.organization_id,
'N', -- create_bom
'N', -- cost_rollup
/* commented for dropship
decode( v_source_type_code , 'INTERNAL' , NULL ,
decode( p_top_ato_line_id, p_model_line_id, '5', '6' )) , -- org_type used for source type
*/
NULL,
p_config_item_id, -- config_item_id
'N',
NULL , /* rank */
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 mtl_system_items msi
where msi.inventory_item_id = p_model_item_id
and NOT EXISTS
(select NULL
from bom_cto_src_orgs_b
where line_id = p_model_line_id
and model_item_id = msi.inventory_item_id
and organization_id = msi.organization_id );
oe_debug_pub.add( '$$$ insert all into bcso ' || SQL%rowcount , 1 ) ;
end insert_all_into_bcso ;
procedure insert_type3_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER
, p_config_item_id in NUMBER default null )
is
begin
oe_debug_pub.add( '$$$ insert type3 bcso ' , 1 ) ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id ,
p_model_line_id ,
p_model_item_id ,
null ,
msi.organization_id,
decode( bp.create_config_bom , 'Y',
decode(bom.assembly_item_id, msi.inventory_item_id, 'Y', 'N')
, 'N') , -- create_bom
decode(bp.organization_id , null , 'N' , 'Y') , -- cost_rollup
decode( msi.planning_make_buy_code, 2, 3 , 2 ) , -- org_type should be 3(buy) for buy items else 2(make)
p_config_item_id, -- config_item_id
'N', -- create_src_rules
NULL , /* rank */
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 mtl_system_items msi, bom_bill_of_materials bom, bom_parameters bp
where msi.inventory_item_id = p_model_item_id
and msi.inventory_item_id = bom.assembly_item_id(+)
and msi.organization_id = bom.organization_id(+)
and bom.alternate_bom_designator is null
and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */
and NOT EXISTS
(select NULL
from bom_cto_src_orgs_b
where line_id = p_model_line_id
and model_item_id = msi.inventory_item_id
and organization_id = msi.organization_id );
oe_debug_pub.add( '$$$ insert type3 bcso ' || SQL%rowcount , 1 ) ;
end insert_type3_bcso ;
procedure insert_type3_bcmo_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER)
is
v_group_reference_id number(10);
oe_debug_pub.add( '$$$ insert type3 bcmo bcso ' , 1 ) ;
select bom_cto_model_orgs_s1.nextval into v_group_reference_id from dual ;
insert into bom_cto_model_orgs
(
reference_id,
group_reference_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
bom_cto_model_orgs_s1.nextval,
v_group_reference_id,
p_model_item_id ,
null ,
msi.organization_id,
decode( bp.create_config_bom , 'Y',
decode(bom.assembly_item_id, msi.inventory_item_id, 'Y', 'N')
, 'N') , -- create_bom
decode(bp.organization_id , null , 'N' , 'Y') , -- cost_rollup
decode( msi.planning_make_buy_code, 2, 3 , 2 ) , -- org_type should be 3(buy) for buy items else 2(make)
NULL, -- config_item_id
'N',
NULL , /* rank */
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 mtl_system_items msi, bom_bill_of_materials bom, bom_parameters bp
where msi.inventory_item_id = p_model_item_id
and msi.inventory_item_id = bom.assembly_item_id(+)
and msi.organization_id = bom.organization_id(+)
and bom.alternate_bom_designator is null
and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */;
(select NULL
from bom_cto_model_orgs bcmo
where bcmo.model_item_id = msi.inventory_item_id
and bcmo.organization_id = msi.organization_id );
oe_debug_pub.add( '$$$ insert type3 bcmo bcmo ' || SQL%rowcount , 1 ) ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
group_reference_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id ,
p_model_line_id ,
v_group_reference_id,
p_model_item_id ,
null ,
-1, -- organization_id is -1 for type 3 matched
null, -- create_bom
'Y', -- cost_rollup
NULL , -- org_type is used to store the source type
NULL, -- config_item_id
NULL,
NULL , /* rank */
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_b
where line_id = p_model_line_id );
oe_debug_pub.add( '$$$ insert type3 bcmo bcso ' || SQL%rowcount , 1 ) ;
end insert_type3_bcmo_bcso ;
procedure insert_type3_referenced_bcso( p_top_ato_line_id in NUMBER
, p_model_line_id in NUMBER
, p_model_item_id in NUMBER
, p_config_item_id in NUMBER default null )
is
v_group_reference_id number(10);
oe_debug_pub.add( '$$$ insert type3 referenced bcso ' , 1 ) ;
oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_top_ato_line_id:' || p_top_ato_line_id) ;
oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_model_line_id:' || p_model_line_id) ;
oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_model_item_id:' || p_model_item_id) ;
oe_debug_pub.add( '$$$ insert type3 referenced bcso: p_config_item_id:' || p_config_item_id) ;
select group_reference_id into v_group_reference_id from bom_cto_model_orgs
where config_item_id = p_config_item_id and rownum = 1 ; /* all records have the same group reference id */
select bom_cto_model_orgs_s1.nextval into v_group_reference_id from dual ;
insert into bom_cto_model_orgs
(
reference_id,
group_reference_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
bom_cto_model_orgs_s1.nextval,
v_group_reference_id,
p_model_item_id ,
null ,
msi.organization_id,
decode( bp.create_config_bom , 'Y',
decode(bom.assembly_item_id, msi.inventory_item_id, 'Y', 'N')
, 'N') , -- create_bom
decode(bp.organization_id , null , 'N' , 'Y') , -- cost_rollup
decode( msi.planning_make_buy_code, 2, 3 , 2 ) , -- org_type should be 3(buy) for buy items else 2(make)
p_config_item_id, -- config_item_id
'N',
NULL , /* rank */
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 mtl_system_items msi, bom_bill_of_materials bom, bom_parameters bp
where msi.inventory_item_id = p_model_item_id
and msi.inventory_item_id = bom.assembly_item_id(+)
and msi.organization_id = bom.organization_id(+)
and bom.alternate_bom_designator is null
and msi.organization_id = bp.organization_id (+) /* added for bug 3504744 */;
oe_debug_pub.add('$$$ insert type3 referenced bcso: Rows in bcmo:' || lCnt , 1 ) ;
insert into bom_cto_src_orgs_b
(
top_model_line_id,
line_id,
group_reference_id,
model_item_id,
rcv_org_id,
organization_id,
create_bom,
cost_rollup,
organization_type, -- Used to store the source type
config_item_id,
create_src_rules,
rank,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
program_application_id,
program_id,
program_update_date
)
select -- distinct
p_top_ato_line_id ,
p_model_line_id ,
v_group_reference_id,
p_model_item_id ,
null ,
-1, -- organization_id is -1 for type3 matched
null, -- create_bom
'Y', -- cost_rollup /* TYPE3 rollup can be avoided for matched items */
NULL , -- org_type is used to store the source type
p_config_item_id, -- config_item_id
NULL,
NULL , /* rank */
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_b
where line_id = p_model_line_id );
oe_debug_pub.add('$$$ insert type3 referenced bcso: Rows in bcso:' || lCnt , 1 ) ;
end insert_type3_referenced_bcso ;
select organization_id bcso_org_id
from bom_cto_src_orgs
where line_id = pModelLineId;
xOrgLst.DELETE;
select inventory_item_id,
nvl(config_creation,1)
into xModelItemId,
l_config_creation
from bom_cto_order_lines_upg
where line_id=pModelLineId;
select inventory_item_id,
nvl(config_creation,1)
into xModelItemId,
l_config_creation
from bom_cto_order_lines
where line_id=pModelLineId;
select organization_id
BULK COLLECT into xOrgLst
from mtl_system_items
where inventory_item_id = xModelItemId
and organization_id not in (
select organization_id
from bom_cto_src_orgs
where line_id = pModelLineId);
select distinct ou_id
BULK COLLECT INTO xOrgLst
from
( select distinct to_number(nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',ood.operating_unit),-99)) ou_id
from inv_organization_info_v ood,
bom_cto_src_orgs bcso
where ( ood.organization_id = bcso.organization_id
or
ood.organization_id = bcso.rcv_org_id
)
and bcso.line_id = pModelLineId
UNION
select distinct nvl(inventory_organization_id,-99) ou_id
from financials_system_params_all
where org_id in
(
select distinct ood.operating_unit
from inv_organization_info_v ood,
bom_cto_src_orgs bcso
where ( ood.organization_id = bcso.organization_id
or
ood.organization_id = bcso.rcv_org_id
)
and bcso.line_id = pModelLineId
)
UNION /* added for bug 4291847. item should be enabled in validation org of operating unit where the order was entered */
( select
to_number(nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',oel.org_id),-99)) ou_id
from oe_order_lines_all oel where oel.line_id = pModelLineid )
);
select 'Y' into l_chk_org
from dual
where EXISTS (
select 1
from bom_cto_src_orgs
where line_id = pModelLineId
and organization_type in (3,5));
select global_agreement_flag,org_id
into l_ga_flag,l_own_org
from po_headers_all
where po_header_id = l_doc_header_id ;
select 'Y' into l_chk_own_oper_unit
from dual
where EXISTS (
select po_header_id
from po_ga_org_assignments
where enabled_flag = 'Y'
and organization_id in (
select odd.operating_unit
from inv_organization_info_v odd,
bom_cto_src_orgs bcso
where bcso.line_id = pModelLineId
and bcso.organization_type in (3,5)
and odd.organization_id = bcso.organization_id));
select inventory_organization_id
into l_own_pov_org
from financials_system_params_all
where org_id = l_own_org;
xOrgLst.delete(x4);
Also, Orgs that could be subsequently deleted may get copied during earlier iterations.
*/
if( xOrgLst.count > 0 ) then
for x6 in xOrgLst.First..xOrgLst.Last loop
if xOrgLst.exists(x6) then
oe_debug_pub.add ('get_other_orgs:'||'Copied Org ('||x6||') = '||xOrglst(x6),5);
v_ship_org_bom_update number ;
select 'Y' into v_procured_models_exist from dual
where exists
( select * from bom_cto_src_orgs_b
where line_id = p_line_id
and model_item_id = p_model_item_id
and nvl(organization_type , 2 ) in( '3' , '4' ) );
select 'Y' into v_bom_created from dual
where exists
( select * from bom_cto_src_orgs_b where line_id = p_line_id
and model_item_id = p_model_item_id
and create_bom = 'Y' );
select organization_id into v_receiving_org
from bom_cto_src_orgs
where line_id = p_line_id and organization_id = rcv_org_id
and organization_type in ( '3' , '4' ) and rownum = 1 ;
oe_debug_pub.add( ' Org to be Updated ' || to_char(v_receiving_org) , 1);
oe_debug_pub.add( ' Assigning Ship Org as Org to be Updated ' || to_char(v_receiving_org) , 1);
update bom_cto_src_orgs_b
set create_bom = 'Y' /* , organization_type = l_source_type */
where line_id = p_line_id
and model_item_id = p_model_item_id
and organization_id = v_receiving_org
and rcv_org_id = v_receiving_org
and exists
( select * from bom_parameters bp, bom_bill_of_materials bbom
where bp.organization_id = v_receiving_org
and bbom.organization_id = bp.organization_id
and bbom.assembly_item_id = p_model_item_id
and bp.create_config_bom = 'Y' ) ;
oe_debug_pub.add( ' updated for org ' || v_receiving_org || ' rcv org ' || v_receiving_org ) ;
oe_debug_pub.add( ' Records updated ' || SQL%ROWCOUNT ) ;
v_ship_org_bom_update := SQL%ROWCOUNT ;
if( v_ship_org_bom_update = 0 ) then
oe_debug_pub.add( ' need to create bom in any org as shipping org does not have model bom or bom param' , 1);
update bom_cto_src_orgs_b
set create_bom = 'Y' /* , organization_type = l_source_type */
where line_id = p_line_id
and model_item_id = p_model_item_id
and rcv_org_id in (
select bp.organization_id
from bom_parameters bp, bom_bill_of_materials bbom
where bbom.organization_id = bp.organization_id
and bbom.assembly_item_id = p_model_item_id
and bp.create_config_bom = 'Y' )
and rownum = 1 ;
oe_debug_pub.add( ' updated in any shipping org Records updated ' || SQL%ROWCOUNT ) ;
Select distinct mp1.master_organization_id
Bulk Collect into
x_orgs_list
from mtl_parameters mp1,
bom_cto_src_orgs bcso
where bcso.line_id = p_model_line_id
and bcso.organization_id = mp1.organization_id
and mp1.master_organization_id not in
( Select organization_id
from bom_cto_src_orgs
where line_id = p_model_line_id);
select organization_id
from mtl_system_items_b
where inventory_item_id = pConfigId;