[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure update_parent_oss_line(p_parent_ato_line_id In Number,
x_return_status OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY Varchar2
);
tree and update the valid nodes.
*/
Procedure update_Source_tree(p_line_id IN Number,
p_end_org IN Number,
x_return_status OUT NOCOPY Varchar2,
x_msg_data OUT NOCOPY varchar2,
x_msg_count OUT NOCOPY Number
);
select 'Y'
into l_oss_defined
from dual
where exists (select 'x'
from bom_cto_oss_components);
Select
nvl(perform_match,'N'),
nvl(reuse_config,'N'),
config_creation,
ship_from_org_id,
program_id
into l_perform_match,
l_reuse_config,
l_config_creation,
l_ship_from_org_id,
l_program_id
from bom_cto_order_lines_gt bcol
where line_id = p_ato_line_id;
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
option_specific_sourced
into l_option_specific
from mtl_system_items msi,
bom_cto_order_lines_gt bcol
where msi.inventory_item_id = bcol.config_item_id
and line_id = p_ato_line_id
and rownum =1; /* Bugfix 3472654 */
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
'Y'
into l_valid_ship_from_org
from bom_cto_order_lines_gt bcol,
mtl_system_items msi
where line_id = p_ato_line_id
and msi.inventory_item_id = bcol.config_item_id
and msi.organization_id = bcol.ship_from_org_id
and msi.option_specific_sourced is not null
and bcol.ship_from_org_id in
(select assg.organization_id
from mrp_sr_assignments assg,
mrp_sr_receipt_org rcv,
mrp_sr_source_org src
where assg.inventory_item_id = bcol.config_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and rcv.effective_date <= sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.SR_RECEIPT_ID = src.sr_receipt_id
union
select src.source_organization_id
from mrp_sr_assignments assg,
mrp_sr_receipt_org rcv,
mrp_sr_source_org src
where assg.inventory_item_id = bcol.config_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and rcv.effective_date <= sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.SR_RECEIPT_ID = src.sr_receipt_id);
Select 'x'
into l_valid_preconfig_org
from bom_cto_order_lines_gt bcol,
mtl_system_items msi
where line_id = p_ato_line_id
and msi.inventory_item_id = bcol.config_item_id
and msi.organization_id = bcol.ship_from_org_id
and msi.option_specific_sourced is not null
and bcol.ship_from_org_id in
(select assg.organization_id org_id
from mrp_sr_assignments assg,
mrp_sr_receipt_org rcv,
mrp_sr_source_org src
where assg.inventory_item_id = bcol.config_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and rcv.effective_date <= sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.SR_RECEIPT_ID = src.sr_receipt_id
and src.source_type in (2,3)
union
select src.source_organization_id org_id
from mrp_sr_assignments assg,
mrp_sr_receipt_org rcv,
mrp_sr_source_org src
where assg.inventory_item_id = bcol.config_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and rcv.effective_date <= sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.SR_RECEIPT_ID = src.sr_receipt_id
and src.source_organization_id not in
(Select assg.organization_id
from mrp_sr_assignments assg,
mrp_sr_receipt_org rcv,
mrp_sr_source_org src
Where assg.inventory_item_id = bcol.config_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
and rcv.effective_date <=sysdate
and nvl(rcv.disable_date,sysdate+1)>sysdate
and rcv.sr_receipt_id = src.sr_receipt_id
)
);
But we don't update the bcol date with the oss flag. As we have not updated
the oss flag, later part of the program is copying the sourcing rule from model
assuming that this is not a oss config. To avoid this issue, we will flag all
the matched config with its oss value from mtl_system_items to bcol so that
we won't have this issue.
*/
update bom_cto_order_lines_gt bcolgt
set option_specific = (select option_specific_sourced
from mtl_system_items
where inventory_item_id = bcolgt.config_item_id
and rownum = 1)
where config_item_id is not null
and ato_line_id = p_ato_line_id;
delete /*+ INDEX (bom_cto_oss_source_gt BOM_CTO_OSS_SOURCE_GT_N1) */
from bom_cto_oss_source_gt
where ato_line_id = p_ato_line_id;
delete /*+ INDEX (bom_cto_oss_orgslist_gt BOM_CTO_OSS_ORGSLIST_GT_N1) */
from bom_cto_oss_orgslist_gt
where ato_line_id = p_ato_line_id;
update_oss_in_bcol(
p_ato_line_id => p_ato_line_id,
x_oss_exists => x_oss_exists,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
update bom_cto_order_lines bcol
set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
decode(option_specific,'4','3',option_specific)
from bom_cto_order_lines_gt bcol_gt
where bcol_gt.line_id = bcol.line_id)
where bcol.ato_line_id = p_ato_line_id;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_UPG_N4) */ bom_cto_order_lines_upg bcol
set option_specific = (select /*+ INDEX (bcol_gt BOM_CTO_ORDER_LINES_GT_U1) */
decode(option_specific,'4','3',option_specific)
from bom_cto_order_lines_gt bcol_gt
where bcol_gt.line_id = bcol.line_id)
where bcol.ato_line_id = p_ato_line_id;
select 'Y'
into /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1) */
l_valid_ship_from_org
from bom_cto_order_lines_gt bcol
where line_id = p_ato_line_id
and (option_specific is null
or ship_from_org_id in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_ato_line_id
and valid_flag = 'Y'
union
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
source_org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_ato_line_id
and valid_flag = 'Y'));
Select 'Y'
into l_valid_preconfig_org
from
(Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
distinct nvl(source_org_id,rcv_org_id) org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_ato_line_id
and valid_flag in( 'P','Y')
and source_type in (2,3)
union
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
distinct source_org_id org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_ato_line_id
and valid_flag in ('P','Y')
and source_org_id not in (
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_ato_line_id
and valid_flag in( 'P','Y')))
Where org_id = l_ship_from_org_id
and rownum = 1;
delete /*+ INDEX (bom_cto_oss_orgslist_gt BOM_CTO_OSS_ORGSLIST_GT_N1) */
from bom_cto_oss_orgslist_gt
where ato_line_id = p_ato_line_id;
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
count(*)
into l_comp_count
from bom_cto_oss_components ossc,
bom_cto_order_lines_gt bcol
where ossc.model_item_id = p_model_item_id
and ossc.option_item_id = bcol.inventory_item_id
and bcol.parent_ato_line_id = p_model_line_id
and exists (select 'x' from bom_cto_oss_orgs_list ossl
where ossl.oss_comp_seq_id = ossc.oss_comp_seq_id);
Insert into bom_cto_oss_orgslist_gt
(
line_id, /* Model Line id */
inventory_item_id,/* Model item id */
organization_id, /* Organization Id */
ato_line_id /* Ato line id */
)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
p_model_line_id Line_id,
p_model_item_id inventory_item_id,
ossl.organization_id organization_id,
p_ato_line_id
from bom_cto_oss_components ossc,
bom_cto_oss_orgs_list ossl,
bom_cto_order_lines_gt bcol
where
ossc.model_item_id = p_model_item_id
and ossc.option_item_id = bcol.inventory_item_id
and bcol.parent_ato_line_id = p_model_line_id
and ossc.oss_comp_seq_id = ossl.oss_comp_seq_id
and ossl.organization_id is not null
group by organization_id
having count(*) = l_comp_count;
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: After first Insert',5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of of orgs inserted in temp table ='||l_org_count,5);
Insert into bom_cto_oss_orgslist_gt(
line_id,
inventory_item_id,
vendor_id,
vendor_site_code,
ato_line_id )
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
p_model_line_id line_id,
p_model_item_id inventory_item_id,
ossl.vendor_id vendor_id,
decode(ossl.vendor_site_code,null,'-1',
ossl.vendor_site_code) vendor_site_code,
p_ato_line_id ato_line_id
from bom_cto_oss_components ossc,
bom_cto_oss_orgs_list ossl,
bom_cto_order_lines_gt bcol
where
bcol.parent_ato_line_id = p_model_line_id
and ossc.model_item_id = p_model_item_id
and ossc.option_item_id = bcol.inventory_item_id
and ossc.oss_comp_seq_id = ossl.oss_comp_seq_id
and ossl.vendor_id is not null
group by vendor_id,
decode(vendor_site_code,null,'-1',vendor_site_code)
having count(*) = l_comp_count;
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: After Second insert..',5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of Vendors inserted into temp = '||l_vendor_count);
Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set oss_src.valid_flag = 'Y'
where oss_src.line_id = p_model_line_id
and ((oss_src.source_org_id in
(select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt oss_lis
where oss_lis.line_id = p_model_line_id)
or (nvl(oss_src.vendor_id,-1),nvl(oss_src.vendor_site_code,-1)) in
(select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
nvl(vendor_id,-99),vendor_site_code
from bom_cto_oss_orgslist_gt oss_lis
where oss_lis.line_id = p_model_line_id)
)
)
and (oss_src.rcv_org_id is null or
oss_src.rcv_org_id in (
select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt oss_lis
where line_id = p_model_line_id)
);
insert into bom_cto_oss_source_gt
(
inventory_item_id,
line_id,
config_item_id,
rcv_org_id,
source_org_id,
customer_id,
ship_to_site_id,
vendor_id,
vendor_site_code,
rank,
allocation,
reuse_flag,
source_type,
valid_flag,
leaf_node
)
Select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
p_model_item_id,
p_model_line_id,
p_config_item_id,
oss_lis.organization_id,
oss_lis.organization_id,
null,
null,
null,
null,
1,
100,
'N',
2, /* Make at source type */
'Y', /* Valid flag */
'Y' /* Leaf node */
from bom_cto_oss_orgslist_gt oss_lis,
mtl_system_items msi
where oss_lis.organization_id not in
(select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
nvl(rcv_org_id,-1)
from bom_cto_oss_source_gt oss_src
where oss_src.line_id = p_model_line_id
union
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
nvl(source_org_id,-1)
from bom_cto_oss_source_gt oss_src
where oss_src.line_id = p_model_line_id
and valid_flag = 'Y'
)
and oss_lis.line_id = p_model_line_id
and msi.inventory_item_id = oss_lis.inventory_item_id
and msi.organization_id = oss_lis.organization_id
and msi.planning_make_buy_code = 1;
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Number of 100% rules inserted ='
||sql%rowcount);
Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
count(*)
into l_valid_count
from bom_cto_oss_source_gt oss_src
where valid_flag = 'Y'
and line_id = p_model_line_id;
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set leaf_node = 'Y'
where leaf_node is null
and line_id = p_model_line_id
and valid_flag = 'Y'
and source_org_id not in (
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_model_line_id
and valid_flag = 'Y');
/* Renga: Is it required to have delete here
*/
/* Renga: Things about match and re-use case for parent configs
*/
If PG_DEBUG <> 0 Then
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_OSS_CONFIG: Coming out of PRUNE_OSS_CONFIG API',5);
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
option_specific
into l_option_specific
from bom_cto_order_lines_gt
where line_id = p_model_line_id;
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = 'N'
where line_id = p_model_line_id
and valid_flag is null;
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of records updated in source table = '
||sql%rowcount,5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = null
where line_id = p_model_line_id
and valid_flag = 'Y';
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of records updated in source table = '
||sql%rowcount,5);
select /*+ INDEX(bcol BOM_CTO_ORDER_LINES_GT_N3) */
count(*)
into l_oss_child_count
from bom_cto_order_lines_gt bcol
where parent_ato_line_id = p_model_line_id
and line_id <> p_model_line_id /* We should igonre the current row */
and option_specific in ('1','2','3')
and not exists(select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
'x'
from bom_cto_oss_source_gt oss_src
where line_id = bcol.line_id
and rcv_org_id is null
and nvl(valid_flag,'N') = 'Y'
and option_specific = '3');
delete /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N1) */
from bom_cto_oss_orgslist_gt oss_lis
where ato_line_id = p_ato_line_id;
insert into bom_cto_oss_orgslist_gt(
Inventory_item_id,
line_id,
organization_id,
ato_line_id )
select
p_model_item_id,
p_model_line_id,
organization_id,
p_ato_line_id
from (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
oss_src.line_id line_id,
oss_src.rcv_org_id organization_id
from bom_cto_oss_source_gt oss_src,
bom_cto_order_lines_gt bcol
where bcol.parent_ato_line_id = p_model_line_id
and bcol.parent_ato_line_id <> bcol.line_id
and bcol.option_specific in ('1','2','3')
and oss_src.line_id = bcol.line_id
and oss_src.valid_flag = 'Y'
and not exists ( Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
'x'
from bom_cto_oss_source_gt oss_src1
where oss_src1.line_id = oss_src.line_id
and bcol.option_specific = '3'
and nvl(valid_flag,'N') = 'Y'
and rcv_org_id is null)
Union
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) INDEX (bcol BOM_CTO_ORDER_LINES_GT_N3) */
oss_src.line_id line_id,
oss_src.source_org_id organization_id
from bom_cto_oss_source_gt oss_src,
bom_cto_order_lines_gt bcol
where bcol.parent_ato_line_id = p_model_line_id
and bcol.parent_ato_line_id <> bcol.line_id
and bcol.option_specific in ('1','2','3')
and oss_src.line_id = bcol.line_id
and oss_src.valid_flag = 'Y'
and not exists ( Select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
'x'
from bom_cto_oss_source_gt oss_src1
where oss_src1.line_id = oss_src.line_id
and bcol.option_specific = '3'
and nvl(valid_flag,'N') = 'Y'
and rcv_org_id is null)
)
group by organization_id
having count(*) = l_oss_child_count;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
set option_specific = null
where line_id = p_model_line_id;
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Rows updated = '||sql%rowcount,1);
Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
count(*)
into l_item_rule_count
from bom_cto_oss_source_gt oss_src
where line_id = p_model_line_id
and customer_id is null
and rcv_org_id is null
and nvl(valid_flag,'Y') <> 'N';
'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set leaf_node = 'Y'
where line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N'
and ( source_type in (2,3)
or source_org_id not in
(Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N'
)
);
Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt
set valid_flag = 'Y'
where line_id = p_model_line_id
and leaf_node = 'Y'
and nvl(valid_flag,'Y') <> 'N'
and (source_type = 3 or
source_org_id in (select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt oss_list
where line_id = p_model_line_id
)
)
Returning rcv_org_id,source_org_id Bulk collect into l_source_org_tbl,l_rcv_org_tbl;
oe_debug_pub.add(lpad(' ',g_pg_level)||'PRUNE_PARENT_OSS_CONFIG: Number of updated records ='||l_source_org_tbl.count,5);
update_Source_tree(p_line_id => p_model_line_id,
p_end_org => l_source_org_tbl(i),
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
'PRUNE_PARENT_OSS: Before inserting 100% make at rules',5);
Insert into bom_cto_oss_source_gt
(
inventory_item_id,
line_id,
rcv_org_id,
source_org_id,
customer_id,
ship_to_site_id,
vendor_id,
vendor_site_code,
rank,
allocation,
reuse_flag,
source_type,
valid_flag,
leaf_node
)
select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) INDEX(bcol BOM_CTO_ORDER_LINES_GT_U1*/
p_model_item_id,
p_model_line_id,
oss_lis.organization_id,
oss_lis.organization_id,
null,
null,
null,
null,
1,
100,
null,
2,
'Y',
'Y'
from bom_cto_oss_orgslist_gt oss_lis,
mtl_system_items msi,
bom_cto_order_lines_gt bcol
where
bcol.line_id = p_model_line_id
and bcol.option_specific = '3'
and oss_lis.organization_id not in (
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
nvl(source_org_id, -1)
from bom_cto_oss_source_gt oss_src
where valid_flag = 'Y'
and line_id = p_model_line_id
union
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
nvl(rcv_org_id,-1)
from bom_cto_oss_source_gt oss_src
where valid_flag = 'Y'
and line_id = p_model_line_id)
and oss_lis.line_id = p_model_line_id
and oss_lis.organization_id = msi.organization_id
and msi.inventory_item_id = bcol.inventory_item_id
and msi.planning_make_buy_code = 1;
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
count(*)
into l_valid_source_count
from bom_cto_oss_source_gt oss_src
where line_id = p_model_line_id
and valid_flag = 'Y';
select line_id,
inventory_item_id,
config_item_id,
option_specific,
config_creation,
perform_match,
reuse_config
from bom_cto_order_lines
where ato_line_id = p_Ato_line_id
and option_specific in ('1','2','3')
and p_mode = 'ACC'
union
select line_id,
inventory_item_id,
config_item_id,
option_specific,
config_creation,
perform_match,
reuse_config
from bom_cto_order_lines_upg
where ato_line_id = p_Ato_line_id
and option_specific in ('1','2','3')
and p_mode = 'UPG'
and (p_changed_src = 'Y' or config_creation=3);
Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
oss_src.inventory_item_id inventory_item_id,
oss_src.line_id line_id,
oss_src.rcv_org_id rcv_org_id,
oss_src.source_org_id source_org_id,
oss_src.vendor_id vendor_id,
oss_src.vendor_site_code vendor_site_code,
oss_src.rank rank,
oss_src.allocation allocation,
oss_src.reuse_flag reuse_flag,
oss_src.valid_flag valid_flag,
oss_src.leaf_node leaf_node,
oss_src.sr_receipt_id sr_receipt_id,
oss_src.sr_source_id sr_source_id,
oss_src.config_item_id config_item_id,
oss_src.source_type source_type,
src_asg.assignment_type assignment_type,
src_asg.assignment_set_id assignment_set_id,
src_asg.assignment_id assignment_id,
src_asg.attribute1 attribute1,
src_asg.attribute2 attribute2,
src_asg.attribute3 attribute3,
src_asg.attribute4 attribute4,
src_asg.attribute5 attribute5,
src_asg.attribute6 attribute6,
src_asg.attribute7 attribute7,
src_asg.attribute8 attribute8,
src_asg.attribute9 attribute9,
src_asg.attribute10 attribute10,
src_asg.attribute11 attribute11,
src_asg.attribute12 attribute12,
src_asg.attribute13 attribute13,
src_asg.attribute14 attribute14,
src_asg.attribute15 attribute15,
src_asg.attribute_category attribute_category,
src_asg.category_id category_id,
src_asg.category_set_id category_set_id,
src_asg.customer_id customer_id,
src_asg.organization_id organization_id,
src_asg.secondary_inventory secondary_inventory,
src_asg.ship_to_site_id ship_to_site_id,
src_asg.sourcing_rule_type sourcing_rule_type,
src_asg.sourcing_rule_id sourcing_rule_id
from bom_cto_oss_source_gt oss_src,
mrp_sr_assignments src_asg
where oss_src.line_id = p_line_id
and nvl(oss_src.reuse_flag,'Y') = 'N'
and valid_flag = 'P'
and src_asg.assignment_id = oss_src.assignment_id
and nvl(src_asg.organization_id,-1) not in (select nvl(organization_id,-1)
from mrp_sr_assignments src_asg1
where inventory_item_id = p_config_item_id
and assignment_set_id = G_def_assg_set)
order by oss_src.line_id,
oss_src.assignment_id,
oss_src.sr_receipt_id,
oss_src.rank;
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id,
source_org_id,
allocation,
rank,
config_item_id
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag = 'P'
and leaf_node = 'Y'
and assignment_id is null
and nvl(rcv_org_id,-1) not in (select nvl(organization_id,-1)
from mrp_sr_assignments
where inventory_item_id = p_config_item_id
and assignment_set_id = G_def_assg_set);
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
distinct assignment_id
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag = 'P'
and nvl(reuse_flag,'Y') = 'Y'
and nvl(rcv_org_id,-1) not in (select nvl(organization_id,-1)
from mrp_sr_assignments
where inventory_item_id = p_config_item_id
and assignment_set_id = G_def_assg_set);
select assignment_set_id
into G_def_assg_set
from mrp_assignment_sets
where assignment_set_name = 'CTO Configuration Updates';
l_receiving_org_tbl.delete;
l_receiving_org_val_tbl.delete;
l_shipping_org_tbl.delete;
l_shipping_org_val_tbl.delete;
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = 'P'
where line_id = oss_model_lines_rec.line_id
and valid_flag = 'Y';
oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
||sql%rowcount,5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = 'P'
where line_id = oss_model_lines_rec.line_id
and valid_flag = 'Y'
and rcv_org_id in (select rcv_org_id
from bom_cto_src_orgs
where line_id = oss_model_lines_rec.line_id
and organization_type is not null);
oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
||sql%rowcount,5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = 'P'
where line_id = oss_model_lines_rec.line_id
and valid_flag = 'Y'
and rcv_org_id is null
and exists (select rcv_org_id
from bom_cto_src_orgs
where line_id = oss_model_lines_rec.line_id
and organization_type is not null
and rcv_org_id not in (select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src
where line_id = oss_model_lines_rec.line_id
and valid_flag = 'P'));
oe_debug_pub.add(lpad(' ',g_pg_level)||'CREATE_OSS_SOURCING_RULE: Number of lines updated in bom_cto_oss_source_gt ='
||sql%rowcount,5);
update /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src1
set reuse_flag = 'N'
where line_id = oss_model_lines_rec.line_id
and valid_flag = 'P'
and (oss_src1.assignment_id is null or exists (select/*+ INDEX (oss_src2 BOM_CTO_OSS_SOURCE_GT_N2) */
'x'
from bom_cto_oss_source_gt oss_src2
where oss_src2.line_id = oss_src1.line_id
and oss_src2.source_rule_id = oss_src1.source_rule_id
and nvl(oss_src2.valid_flag,'N') = 'N'
)
);
'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
l_receiving_org_tbl.delete;
l_receiving_org_val_tbl.delete;
l_shipping_org_tbl.delete;
l_shipping_org_val_tbl.delete;
/* Delete all the existing data from the record structure.
This record structure will be populated with the new sourcing
rule information
*/
/* The following sql will populate the data for sourcing
rule record type
*/
l_stmt_num := 130;
select attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
organization_id,
planning_active,
'CTO*'||bom_Cto_oss_source_rule_s1.nextval,
Sourcing_rule_type,
MRP_Globals.G_OPR_CREATE,
1
-- mrp_sourcing_rules_s.nextval
Into
l_sourcing_rule_rec.attribute1,
l_sourcing_rule_rec.attribute2,
l_sourcing_rule_rec.attribute3,
l_sourcing_rule_rec.attribute4,
l_sourcing_rule_rec.attribute5,
l_sourcing_rule_rec.attribute6,
l_sourcing_rule_rec.attribute7,
l_sourcing_rule_rec.attribute8,
l_sourcing_rule_rec.attribute9,
l_sourcing_rule_rec.attribute10,
l_sourcing_rule_rec.attribute11,
l_sourcing_rule_rec.attribute12,
l_sourcing_rule_rec.attribute13,
l_sourcing_rule_rec.attribute14,
l_sourcing_rule_rec.attribute15,
l_sourcing_rule_rec.attribute_category,
l_sourcing_rule_rec.organization_id,
l_sourcing_rule_rec.planning_active,
l_sourcing_rule_rec.sourcing_rule_name,
l_sourcing_rule_rec.Sourcing_rule_type,
l_sourcing_rule_rec.Operation,
l_sourcing_rule_rec.status
-- l_sourcing_rule_rec.sourcing_rule_id
From mrp_sourcing_rules
where sourcing_rule_id = source_tree_rec.sourcing_rule_id;
select attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
disable_date,
sysdate,
receipt_organization_id,
MRP_Globals.G_OPR_CREATE,
l_sourcing_rule_rec.sourcing_rule_id
into
l_receiving_org_tbl(rcv_count).attribute1,
l_receiving_org_tbl(rcv_count).attribute2,
l_receiving_org_tbl(rcv_count).attribute3,
l_receiving_org_tbl(rcv_count).attribute4,
l_receiving_org_tbl(rcv_count).attribute5,
l_receiving_org_tbl(rcv_count).attribute6,
l_receiving_org_tbl(rcv_count).attribute7,
l_receiving_org_tbl(rcv_count).attribute8,
l_receiving_org_tbl(rcv_count).attribute9,
l_receiving_org_tbl(rcv_count).attribute10,
l_receiving_org_tbl(rcv_count).attribute11,
l_receiving_org_tbl(rcv_count).attribute12,
l_receiving_org_tbl(rcv_count).attribute13,
l_receiving_org_tbl(rcv_count).attribute14,
l_receiving_org_tbl(rcv_count).attribute15,
l_receiving_org_tbl(rcv_count).attribute_category,
l_receiving_org_tbl(rcv_count).disable_date,
l_receiving_org_tbl(rcv_count).effective_date,
l_receiving_org_tbl(rcv_count).receipt_organization_id,
l_receiving_org_tbl(rcv_count).operation,
l_receiving_org_tbl(rcv_count).sourcing_rule_id
from mrp_sr_receipt_org
where sr_receipt_id = source_tree_rec.sr_receipt_id;
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
sum(allocation)
into l_rank_sum
from bom_cto_oss_source_gt oss_src
where line_id = source_tree_rec.line_id
and source_rule_id = source_tree_rec.sourcing_rule_id
and rank = source_tree_rec.rank
and valid_flag = 'P';
select attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
secondary_inventory,
ship_method,
MRP_Globals.G_OPR_CREATE,
rcv_count-1,
vendor_site_id
into
l_shipping_org_tbl(i).attribute1,
l_shipping_org_tbl(i).attribute2,
l_shipping_org_tbl(i).attribute3,
l_shipping_org_tbl(i).attribute4,
l_shipping_org_tbl(i).attribute5,
l_shipping_org_tbl(i).attribute6,
l_shipping_org_tbl(i).attribute7,
l_shipping_org_tbl(i).attribute8,
l_shipping_org_tbl(i).attribute9,
l_shipping_org_tbl(i).attribute10,
l_shipping_org_tbl(i).attribute11,
l_shipping_org_tbl(i).attribute12,
l_shipping_org_tbl(i).attribute13,
l_shipping_org_tbl(i).attribute14,
l_shipping_org_tbl(i).attribute15,
l_shipping_org_tbl(i).attribute_category,
l_shipping_org_tbl(i).secondary_inventory,
l_shipping_org_tbl(i).ship_method,
l_shipping_org_tbl(i).operation,
l_shipping_org_tbl(i).receiving_org_index,
l_shipping_org_tbl(i).vendor_site_id
from mrp_sr_source_org
where sr_source_id = source_tree_rec.sr_source_id;
oe_debug_pub.add('Vendor site id inserted = '||l_shipping_org_tbl(i).vendor_site_id,5);
'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
l_receiving_org_tbl.delete;
l_receiving_org_val_tbl.delete;
l_shipping_org_tbl.delete;
l_shipping_org_val_tbl.delete;
if we need to create 100% make at rule or not. When we have inserted a row for 100% make at
we will insert with source_rule_id as null. Check if there is any row exists with that .
*/
l_stmt_num := 200;
select 'CTO*'||bom_cto_oss_source_rule_s1.nextval
into l_sourcing_rule_rec.sourcing_rule_name
from dual;
select mrp_sourcing_rules_s.nextval
into l_sourcing_rule_rec.sourcing_rule_id
from dual;
l_receiving_org_tbl.delete;
l_shipping_org_tbl.delete;
'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
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 = oss_reused_assg_rec.assignment_id;
lAssignmentTbl(asg_count).Last_Updated_By := lAssignmentRec.Last_Updated_By;
lAssignmentTbl(asg_count).Last_Update_Date := lAssignmentRec.Last_Update_Date;
lAssignmentTbl(asg_count).Last_Update_Login := lAssignmentRec.Last_Update_Login;
lAssignmentTbl(asg_count).Program_Update_Date := lAssignmentRec.Program_Update_Date;
'CREATE_OSS_SOURCING_RULES: Exepected error occurred in update_oss_in_bcol API',5);
'CREATE_OSS_SOURCING_RULES: Un Exepected error occurred in update_oss_in_bcol API',5);
update mtl_system_items msi
set msi.option_specific_sourced = (select bcol.option_specific
from bom_cto_order_lines_upg bcol
where bcol.ato_line_id= p_ato_line_id
and bcol.config_item_id = msi.inventory_item_id
)
where msi.inventory_item_id in (select config_item_id
from bom_cto_order_lines_upg
where ato_line_id = p_ato_line_id
and bom_item_type = 1
and option_specific in ('1','2','3')
);
update mtl_system_items msi
set msi.option_specific_sourced = (select bcol.option_specific
from bom_cto_order_lines bcol
where bcol.ato_line_id=p_ato_line_id
and bcol.config_item_id =msi.inventory_item_id
)
where msi.inventory_item_id in (select config_item_id
from bom_cto_order_lines
where ato_line_id = p_ato_line_id
and bom_item_type = 1
and option_specific in ('1','2','3')
);
Procedure update_Source_tree(p_line_id IN Number,
p_end_org IN Number,
x_return_status OUT NOCOPY Varchar2 ,
x_msg_data OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number
) is
l_rcv_org_id Number;
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Inside Update Source Tree API',5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Line id ='||p_line_id,5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Org id ='||p_end_org,5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = 'Y'
where source_org_id = p_end_org
and line_id = p_line_id
and nvl(leaf_node,'N') <> 'Y'
returning rcv_org_id bulk collect into l_org_tbl;
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_SOURCE_TREE: Number parent orgs = '||l_org_tbl.count,5);
update_source_tree(p_line_id => p_line_id,
p_end_org => l_org_tbl(i),
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count
);
'UPDATE_SOURCE_TREE: Exepected error occurred in update_oss_in_bcol API',5);
'UPDATE_SOURCE_TREE: Un Exepected error occurred in update_oss_in_bcol API',5);
End update_Source_tree;
Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
source_org_id
from bom_cto_oss_source_gt oss_src
where customer_id is null
and rcv_org_id is null
and line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N';
FOR all for select to improve the performance. Revisit this part
*/
For global_orgs_rec in global_orgs_cur
Loop
Begin
/* The following sql may not be needed as this will
be part of find_leaf_node itself
*/
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
source_org_id
bulk collect into l_source_org_id
from bom_cto_oss_source_gt oss_src
where rcv_org_id = global_orgs_rec.source_org_id
and line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N';
'PRUNE_ITEM_LEVEL_RULE: Exepected error occurred in update_oss_in_bcol API',5);
'PRUNE_ITEM_LEVEL_RULE: Un Exepected error occurred in update_oss_in_bcol API',5);
Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
count(*)
into l_valid_count
from bom_cto_oss_source_gt oss_src
where line_id = p_model_line_id
and valid_flag ='Y';
/* The following update will find all the
valid leaf nodes.
1. This will mark all the buy nodes as valid node.
2 Mark all the end nodes which are of the type xfer as valid
3. Mark all the make at nodes for whihc the org is part of intersection
list as valid nodes.
*/
Update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set leaf_node = 'Y',
valid_flag = 'Y'
where
line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N'
and
(source_type = 3
or ( Source_type = 2
and source_org_id in (select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt OSS_LIS
where line_id = p_model_line_id)
)
or ( source_type = 1
and source_org_id not in
(Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
nvl(rcv_org_id,-1)
from bom_cto_oss_source_gt oss_src
where line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N')
)
)
returning rcv_org_id bulk collect into l_source_org_tbl;
oe_debug_pub.add(lpad(' ',g_pg_level)||'Prune_item_level_rule Updated
valid leaf nodes = '||l_source_org_tbl.count,1);
and update all the parents as valid
*/
For i in l_source_org_tbl.first..l_source_org_tbl.last
Loop
If l_source_org_tbl(i) is not null then
Traverse_up_tree(p_model_line_id => p_model_line_id,
p_source_org_id => l_source_org_tbl(i),
p_valid_flag => 'Y',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
'PRUNE_ITEM_LEVEL_RULE: Exepected error occurred in update_oss_in_bcol API',5);
'PRUNE_ITEM_LEVEL_RULE: Un Exepected error occurred in update_oss_in_bcol API',5);
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
set option_specific = 4
where line_id = p_model_line_id;
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
source_org_id,
source_type
bulk collect into
l_source_org_id,
l_source_type
from bom_cto_oss_source_gt oss_src
where rcv_org_id = p_source_org_id
and line_id = p_model_line_id
and nvl(valid_flag,'Y') <> 'N';
'FIND_LEAF_NODE: Exepected error occurred in update_oss_in_bcol API',5);
'FIND_LEAF_NODE: Un Exepected error occurred in update_oss_in_bcol API',5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set leaf_node = 'Y',
valid_flag = 'Y'
where line_id = p_model_line_id
and source_org_id = p_source_org_id
and rcv_org_id = p_rcv_org_id
and nvl(valid_flag,'Y') <> 'N'
and rcv_org_id in (select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt oss_lis
where line_id = p_model_line_id);
'FIND_LEAF_NODE: Exepected error occurred in update_oss_in_bcol API',5);
'FIND_LEAF_NODE: Un Exepected error occurred in update_oss_in_bcol API',5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set leaf_node = 'Y',
valid_flag = 'Y'
where line_id = p_model_line_id
and source_org_id = p_source_org_id
and rcv_org_id = p_rcv_org_id
and nvl(valid_flag,'Y') <> 'N'
and rcv_org_id in (select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt oss_lis
where line_id = p_model_line_id);
'FIND_LEAF_NODE: Exepected error occurred in update_oss_in_bcol API',5);
'FIND_LEAF_NODE: Un Exepected error occurred in update_oss_in_bcol API',5);
update /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
bom_cto_oss_source_gt oss_src
set valid_flag = 'Y'
where line_id = p_model_line_id
and source_org_id = p_source_org_id
and nvl(valid_flag,'Y') <> 'N'
and source_type <> 2 /*Exclude make rules...*/
returning source_org_id,rcv_org_id bulk collect into l_rcv_org_tbl, l_org_id_tbl;
oe_debug_pub.add(lpad(' ',g_pg_level)||'TRAVERSE_TREE_UP: Number of parents updated = '||l_org_id_tbl.count,5);
'TRAVERSE_UP_TREE: Exepected error occurred in update_oss_in_bcol API',5);
'TRAVERSE_UP_TREE: Un Exepected error occurred in update_oss_in_bcol API',5);
delete from bom_cto_oss_source_gt ;
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records delete in source_gt = '
||sql%rowcount,5);
delete from bom_cto_oss_orgslist_gt;
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records delete in source_gt = '
||sql%rowcount,5);
Select 'X'
into l_check_flag
from dual
where exists (select 'x' from bom_cto_oss_components);
select line_id,
inventory_item_id,
ato_line_id,
organization_id,
vendor_id,
vendor_site_code,
make_flag
bulk collect into
x_oss_orgs_list.line_id,
x_oss_orgs_list.inventory_item_id,
x_oss_orgs_list.ato_line_id,
x_oss_orgs_list.org_id,
x_oss_orgs_list.vendor_id,
x_oss_orgs_list.vendor_site,
x_oss_orgs_list.make_flag
from bom_cto_oss_orgslist_gt;
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_OSS_ORGS_LIST: Number of records insert to output structure ='||sql%rowcount,4);
Select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N5) */
distinct bcol1.ato_line_id
from bom_cto_order_lines_gt bcol1
where exists (select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N3) */
'X'
from bom_cto_oss_components ossc,
bom_cto_order_lines_gt bcol2
where bcol2.parent_ato_line_id = bcol1.line_id
and ossc.model_item_id = bcol1.inventory_item_id
and ossc.option_item_id = bcol2.inventory_item_id)
and bcol1.bom_item_type = '1'
and bcol1.wip_supply_type <> 6;
select /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) */
line_id,
ato_line_id,
option_specific,
inventory_item_id,
config_item_id,
perform_match,
config_creation
from bom_cto_order_lines_gt
where ato_line_id = p_ato_line_id
and option_specific in ('1','2','3')
order by plan_level desc;
update_oss_in_bcol(
p_ato_line_id => oss_top_model_rec.ato_line_id,
x_oss_exists => x_oss_exists,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
'GET_OSS_ORGS_LIST: Exepected error occurred in update_oss_in_bcol API',5);
'GET_OSS_ORGS_LIST: Un Exepected error occurred in update_oss_in_bcol API',5);
'GET_CONFIGURATIONS_ORG: Exepected error occurred in update_oss_in_bcol API',5);
'GET_CONFIGURATIONS_ORG: Un Exepected error occurred in update_oss_in_bcol API',5);
delete from bom_cto_oss_orgslist_gt;
update bom_cto_oss_source_gt ossgt1
set reuse_flag = 'N'
where rcv_org_id is not null
and valid_flag = 'Y'
and not exists (select 'x'
from bom_cto_oss_source_gt ossgt2
where ossgt1.line_id = ossgt2.line_id
and ossgt2.rcv_org_id = ossgt1.rcv_org_id
and ossgt2.source_type = 2
and ossgt2.valid_flag = 'Y');
update bom_cto_oss_source_gt ossgt1
set reuse_flag = 'Y'
where rcv_org_id is not null
and valid_flag = 'Y'
and exists (select/*+ INDEX (ossgt2 BOM_CTO_OSS_SOURCE_GT_N2) */
'x'
from bom_cto_oss_source_gt ossgt2
where ossgt1.line_id = ossgt2.line_id
and ossgt2.rcv_org_id = ossgt1.rcv_org_id
and ossgt2.source_type = 2
and ossgt2.valid_flag = 'Y');
INSERT into bom_cto_oss_orgslist_gt(
Inventory_item_id,
line_id,
ato_line_id,
organization_id,
vendor_id,
vendor_site_code,
make_flag)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
oss_src.inventory_item_id,
oss_src.line_id,
bcol.ato_line_id,
oss_src.rcv_org_id,
-- oss_src.vendor_id,
-- oss_src.vendor_site_code,
to_number(null), --3894241
null,
reuse_flag
from bom_cto_oss_source_gt oss_src,
bom_cto_order_lines_gt bcol
where bcol.line_id = oss_src.line_id
and oss_error_code is null
and oss_src.valid_flag = 'Y'
and oss_src.rcv_org_id is not null
and nvl(bcol.option_specific,'4') <> '4'
union
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
oss_src.inventory_item_id,
oss_src.line_id,
bcol.ato_line_id,
oss_src.source_org_id,
-- oss_src.vendor_id,
-- oss_src.vendor_site_code,
to_number(null), --3894241
null,
null
from bom_cto_oss_source_gt oss_src,
bom_cto_order_lines_gt bcol
where bcol.line_id = oss_src.line_id
and bcol.option_specific is not null
and oss_error_code is null
and oss_src.valid_flag = 'Y'
and oss_src.source_org_id is not null
and oss_src.source_org_id not in (select /*+ INDEX (oss_src1 BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src1
where oss_src1.line_id = oss_src.line_id
and valid_flag = 'Y'
)
and nvl(bcol.option_specific,'4') <> '4'
union
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
oss_src.inventory_item_id,
oss_src.line_id,
bcol.ato_line_id,
to_number(null),--3894241
oss_src.vendor_id,
oss_src.vendor_site_code,
null
from bom_cto_oss_source_gt oss_src,
bom_cto_order_lines_gt bcol
where bcol.line_id = oss_src.line_id
and bcol.option_specific is not null
and oss_error_code is null
and oss_src.valid_flag = 'Y'
and oss_src.vendor_id is not null
and nvl(bcol.option_specific,'4') <> '4';
/* The following sql will insert all the orgs and vendors from sourcing assignments
and sourcing rules for ato item and matched configuration item
*/
/* Renga Kannan: Changed ship_from_org_id reference to validation_org.
* Here is the story. ATP team will not pass the ship
* from org in the case of Global ATP. This has been decided
* at the very end of our ST cycle and aggreed to pass null
* value for ship from org in the case of Global ATP.
* Since the ship from org id can be null, OSS code should not
* depend on ship from org id in this API. But, ATP will
* populate validtion org for the order line in all the cases.
* Since we are using ship from org as an orbitrary org fo
* getting option_specific_sourced flag value, We can use
* validation_org instead.
* */
INSERT into bom_cto_oss_orgslist_gt(
Inventory_item_id,
line_id,
ato_line_id,
organization_id,
vendor_id,
vendor_site_code)
select /*+ FULL(bcol) */
bcol.config_item_id,
bcol.line_id,
bcol.ato_line_id,
src.source_organization_id,
src.VENDOR_ID,
vend.VENDOR_SITE_CODE
from mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
bom_cto_order_lines_gt bcol,
mtl_system_items msi,
ap_supplier_sites_all vend
where
bcol.ato_line_id = bcol.line_id
and nvl(bcol.wip_supply_type,-1) <> 6
and bcol.top_model_line_id is null
and msi.inventory_item_id = bcol.config_item_id
and msi.organization_id = bcol.validation_org
and msi.option_specific_sourced in('1','2','3')
and assg.assignment_set_id = p_assignment_id
and assg.customer_id is null
and assg.inventory_item_id = msi.inventory_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
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(+)
and not exists (select 'X'
from mrp_sr_assignments
where inventory_item_id = bcol.config_item_id
and organization_id is null
and msi.option_specific_sourced = 3)
UNION
select /*+ FULL(bcol) */
bcol.config_item_id,
bcol.line_id,
bcol.ato_line_id,
assg.organization_id,
to_number(null), --3894241
null
from mrp_sr_assignments assg,
bom_cto_order_lines_gt bcol,
mtl_system_items msi
where
bcol.ato_line_id = bcol.line_id
and nvl(bcol.wip_supply_type,-1)<> 6
and bcol.top_model_line_id is null
and msi.inventory_item_id = bcol.config_item_id
and msi.organization_id = bcol.validation_org
and msi.option_specific_sourced in ('1','2','3')
and assg.assignment_set_id = p_assignment_id
and assg.customer_id is null
and assg.inventory_item_id = msi.inventory_item_id
and not exists (select 'X'
from mrp_sr_assignments
where inventory_item_id = bcol.config_item_id
and organization_id is null
and msi.option_specific_sourced = 3)
UNION
select /*+ FULL(bcol) */
bcol.config_item_id,
bcol.line_id,
bcol.ato_line_id,
src.source_organization_id,
src.VENDOR_ID,
vend.VENDOR_SITE_CODE
from mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
bom_cto_order_lines_gt bcol,
mtl_system_items msi,
ap_supplier_sites_all vend
where
bcol.config_item_id is not null
and bcol.top_model_line_id is not null
and (bcol.perform_match in ('Y','C') or bcol.reuse_config = 'Y')
and bcol.config_creation = '3'
and nvl(bcol.wip_supply_type,-1) <> 6
and msi.inventory_item_id = bcol.config_item_id
and msi.organization_id = bcol.validation_org
and msi.option_specific_sourced in('1','2','3')
and assg.assignment_set_id = p_assignment_id
and assg.customer_id is null
and assg.inventory_item_id = msi.inventory_item_id
and assg.sourcing_rule_id = rcv.sourcing_rule_id
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(+)
and not exists (select 'X'
from mrp_sr_assignments
where inventory_item_id = bcol.config_item_id
and organization_id is null
and msi.option_specific_sourced = 3)
UNION
select /*+ FULL(bcol) */
bcol.config_item_id,
bcol.line_id,
bcol.ato_line_id,
assg.organization_id,
to_number(null),--bugfix3894241
null
from mrp_sr_assignments assg,
bom_cto_order_lines_gt bcol,
mtl_system_items msi
where
bcol.config_item_id is not null
and bcol.top_model_line_id is not null
and (bcol.perform_match in ('Y','C') or bcol.reuse_config = 'Y')
and bcol.config_creation = '3'
and nvl(bcol.wip_supply_type,-1)<> 6
and bcol.top_model_line_id is null
and msi.inventory_item_id = bcol.config_item_id
and msi.organization_id = bcol.validation_org
and msi.option_specific_sourced in ('1','2','3')
and assg.assignment_set_id = p_assignment_id
and assg.customer_id is null
and assg.inventory_item_id = msi.inventory_item_id
and not exists (select 'X'
from mrp_sr_assignments
where inventory_item_id = bcol.config_item_id
and organization_id is null
and msi.option_specific_sourced = 3);
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ATO_ITEM_ORGS: Number of records inserted = '||sql%rowcount);
Procedure update_oss_in_bcol(
p_ato_line_id IN Number,
x_oss_exists OUT NOCOPY Varchar2,
x_return_status OUT NOCOPY Varchar2,
x_msg_data OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number) is
l_parent_ato_line_id Number;
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
line_id,
ato_line_id,
parent_ato_line_id,
option_specific,
perform_match
from bom_cto_order_lines_gt bcol
where ato_line_id = p_ato_line_id;
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
line_id,
ato_line_id,
parent_ato_line_id,
option_specific
from bom_cto_order_lines_gt bcol
where ato_line_id = p_ato_line_id
and option_specific = '1'
order by plan_level desc;
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: In UPDATE_OSS_BCOL API',5);
update /*+ INDEX (bcol1 BOM_CTO_ORDER_LINES_GT_N1) */
bom_cto_order_lines_gt bcol1
set option_specific = '1'
where
ato_line_id = p_ato_line_id
and exists (select /*+ INDEX (bcol2 BOM_CTO_ORDER_LINES_GT_N3) */
'X'
from bom_cto_oss_components ossc,
bom_cto_order_lines_gt bcol2,
bom_cto_oss_orgs_list ossl
where bcol2.parent_ato_line_id = bcol1.line_id
and ossc.model_item_id = bcol1.inventory_item_id
and ossc.option_item_id = bcol2.inventory_item_id
and ossl.oss_comp_Seq_id = ossc.oss_comp_seq_id)
and nvl(bcol1.wip_supply_type,-1) <> 6 /* Talk to Sushant Sawant */
and bcol1.bom_item_type = 1
returning parent_ato_line_id bulk collect into l_parent_ato_line_tbl;
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Number of OSS configuratinos = '
||sql%rowcount,5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: No OSS configuration exists..',5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Caching BCOL Data',5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Falgging OSS for parents',5);
update_parent_oss_line(p_parent_ato_line_id => oss_line_rec.parent_ato_line_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
Update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
set option_specific = g_parent_rec.option_specific(i)
where line_id = g_parent_rec.line_id(i);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Number of parent records updated = '
||g_parent_rec.line_id.count,5);
/* The following update statement will update all the rows where config item is
matched and the config the item attribute is set to 3. In these cases, the
opiton speicific source will be taken from config item. That will replace
the flag determined earlier.
*/
l_stmt_num := 50;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
bom_cto_order_lines_gt bcol
set bcol.option_specific = (select msi.option_specific_sourced
from mtl_system_items msi
where msi.inventory_item_id = bcol.config_item_id
and rownum =1)
where bcol.perform_match = 'Y' /* We need to add config creation condition here */
and bcol.config_creation = '3'
and bcol.ato_line_id = p_ato_line_id;
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL: Number of matched configs with attribute settting 3 ='
||sql%rowcount,5);
g_parent_rec.line_id.delete;
g_parent_rec.option_specific.delete;
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL::exp error::'
||to_char(l_stmt_num)
||'::'||sqlerrm,1);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL::exp error::'
||to_char(l_stmt_num)||'::'||sqlerrm,1);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_OSS_IN_BCOL::exp error::'
||to_char(l_stmt_num)||'::'||sqlerrm,1);
End update_oss_in_bcol;
******************** UPDATE_PARENT_OSS_LINE ***********************
*/
Procedure update_parent_oss_line(p_parent_ato_line_id In Number,
x_return_status OUT NOCOPY Varchar2,
x_msg_count OUT NOCOPY Number,
x_msg_data OUT NOCOPY Varchar2) is
l_parent_ato_line_id Number;
g_parent_rec is a recor of tables and is used for bulk update
later
*/
If PG_DEBUG <> 0 Then
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE: Inside UPDATE_PARENT_OSS_LIEN API',5);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE: Line id = '||g_bcol_tbl(p_parent_ato_line_id).line_id,5);
update_parent_oss_line(p_parent_ato_line_id => l_parent_ato_line_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE::exp error::'
||to_char(l_stmt_num)
||'::'||sqlerrm,1);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE::exp error::'
||to_char(l_stmt_num)||'::'||sqlerrm,1);
oe_debug_pub.add(lpad(' ',g_pg_level)||'UPDATE_PARENT_OSS_LINE::exp error::'
||to_char(l_stmt_num)||'::'||sqlerrm,1);
End Update_parent_oss_line;
Insert into bom_cto_oss_source_gt
(
Inventory_item_id,
Line_id,
ato_line_id,
config_item_id,
Rcv_org_id,
Source_org_id,
Customer_id,
Ship_to_site_id,
Vendor_id,
Vendor_site_code,
rank,
Allocation,
Source_type,
source_rule_id,
sr_receipt_id,
sr_source_id,
assignment_id
)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
bcol.inventory_item_id,
bcol.line_id,
p_ato_line_id,
null,
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,
assg.assignment_id
from
mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
mrp_sourcing_rules rule,
po_vendor_sites_all vend,
bom_cto_order_lines_gt bcol
where
assg.assignment_set_id = g_def_assg_set
and bcol.ato_line_id = p_ato_line_id
and bcol.config_item_id is null
and bcol.option_specific in ('1','2','3')
and assg.inventory_item_id = bcol.inventory_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(+);
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of records inserted in 1st sql ='||sql%rowcount,5);
Insert into bom_cto_oss_source_gt
(
Inventory_item_id,
Line_id,
ato_line_id,
config_item_id,
Rcv_org_id,
Source_org_id,
Customer_id,
Ship_to_site_id,
Vendor_id,
Vendor_site_code,
rank,
Allocation,
Source_type,
source_rule_id,
sr_receipt_id,
sr_source_id,
assignment_id,
valid_flag
)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
bcol.inventory_item_id,
bcol.line_id,
p_ato_line_id,
bcol.config_item_id,
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,
assg.assignment_id,
'Y'
from
mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
mrp_sourcing_rules rule,
po_vendor_sites_all vend,
bom_cto_order_lines_gt bcol
where
assg.assignment_set_id = g_def_assg_set
and bcol.ato_line_id = p_ato_line_id
and bcol.config_creation = 3
and bcol.option_specific in ('1','2','3')
and bcol.config_item_id is not null
and assg.inventory_item_id = bcol.config_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(+);
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of records inserted in 2nd sql ='||sql%rowcount,5);
Insert into bom_cto_oss_source_gt
(
Inventory_item_id,
Line_id,
config_item_id,
Rcv_org_id,
Source_org_id,
Customer_id,
Ship_to_site_id,
Vendor_id,
Vendor_site_code,
rank,
Allocation,
Source_type,
source_rule_id,
sr_receipt_id,
sr_source_id,
assignment_id
)
select
p_item_id,
p_line_id,
p_config_item_id,
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,
assg.assignment_id
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 = g_def_assg_set
and assg.inventory_item_id = p_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(+);
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_SOURCING_DATA: Number of assignment records inserted = '
||sql%rowcount,5);
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
line_id,
ato_line_id,
option_specific,
inventory_item_id,
config_item_id,
perform_match,
config_creation
from bom_cto_order_lines_gt bcol
where ato_line_id = p_ato_line_id
and option_specific in ('1','2','3')
and not (perform_match = 'Y' and config_creation = '3') -- 4093235
order by plan_level desc;
delete /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N1) */
from bom_cto_oss_orgslist_gt oss_lis
where ato_line_id = p_ato_line_id;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
set oss_error_code = x_exp_error_code
where line_id = oss_model_rec.line_id;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
Set oss_error_code = 360
where line_id = oss_model_rec.ato_line_id;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
set oss_error_code = x_exp_error_code
where line_id = oss_model_rec.line_id;
update /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
bom_cto_order_lines_gt bcol
set oss_error_code = 360
where line_id = oss_model_rec.ato_line_id;
delete /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
from bom_cto_order_lines_gt bcol
where ato_line_id = p_ato_line_id;
INSERT into bom_cto_order_lines_gt(
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,
OPTION_SPECIFIC,
REUSE_CONFIG,
QTY_PER_PARENT_MODEL,
CONFIG_CREATION
)
Select /*+ INDEX (bcol_upg BOM_CTO_ORDER_LINES_UPG_N4) */
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,
OPTION_SPECIFIC,
REUSE_CONFIG,
QTY_PER_PARENT_MODEL,
CONFIG_CREATION
from bom_cto_order_lines_upg bcol_upg
where ato_line_id = p_ato_line_id;
oe_debug_pub.add(lpad(' ',g_pg_level)||'COPY_TO_BCOL_TEMP: Number of lines inserted in to temp '
|| sql%rowcount,5);
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
line_id,
inventory_item_id,
option_specific,
parent_ato_line_id,
ato_line_id
from bom_cto_order_lines_gt bcol
where ato_line_id = p_ato_line_id
and nvl(wip_supply_type,-1) <> '6'
and bom_item_type = '1'
and option_specific in ('1','2','3')
and config_creation <> '3'
order by plan_level;
select /*+ INDEX (oss_lis BOM_CTO_OSS_ORGSLIST_GT_N2) */
organization_id
from bom_cto_oss_orgslist_gt oss_lis
where line_id = p_line_id;
delete /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N1) */
from bom_cto_oss_source_gt oss_src where ato_line_id = p_ato_line_id;
Insert
into bom_cto_oss_orgslist_gt(
line_id,
organization_id,
ato_line_id
)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_U1) */
-1,
ship_from_org_id,
p_ato_line_id
from bom_cto_order_lines_gt bcol
where line_id = p_ato_line_id;
g_assg_list.delete;
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting the assignments into temp table',5);
Insert into bom_cto_oss_source_gt
(
Inventory_item_id,
Line_id,
ato_line_id,
config_item_id,
Rcv_org_id,
Source_org_id,
Customer_id,
Ship_to_site_id,
Vendor_id,
Vendor_site_code,
rank,
Allocation,
Source_type,
source_rule_id,
sr_receipt_id,
sr_source_id,
assignment_id
)
select
assg.inventory_item_id,
x_assg_list.line_id(i),
p_ato_line_id,
null,
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,
assg.assignment_id
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 = g_def_assg_set
and assg.assignment_id = x_assg_list.assignment_id(i)
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(+);
oe_debug_pub.add(lpad(' ',g_pg_level)||'GET_ORDER_SOURCING_DATA: Before inserting Model attribute 3 lines',5);
Insert into bom_cto_oss_source_gt
(
Inventory_item_id,
Line_id,
ato_line_id,
config_item_id,
Rcv_org_id,
Source_org_id,
Customer_id,
Ship_to_site_id,
Vendor_id,
Vendor_site_code,
rank,
Allocation,
Source_type,
source_rule_id,
sr_receipt_id,
sr_source_id,
assignment_id
)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
bcol.inventory_item_id,
bcol.line_id,
p_ato_line_id,
null,
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,
assg.assignment_id
from
mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
mrp_sourcing_rules rule,
po_vendor_sites_all vend,
bom_cto_order_lines_gt bcol
where
assg.assignment_set_id = g_def_assg_set
and bcol.ato_line_id = p_ato_line_id
and bcol.config_creation = 3
and (nvl(bcol.perform_match,'N') = 'N' or nvl(bcol.reuse_config,'N') = 'N')
and assg.inventory_item_id = bcol.inventory_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(+);
Insert into bom_cto_oss_source_gt
(
Inventory_item_id,
Line_id,
ato_line_id,
config_item_id,
Rcv_org_id,
Source_org_id,
Customer_id,
Ship_to_site_id,
Vendor_id,
Vendor_site_code,
rank,
Allocation,
Source_type,
source_rule_id,
sr_receipt_id,
sr_source_id,
assignment_id,
Valid_flag /* 4093235 */
)
select /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N1) */
bcol.inventory_item_id,
bcol.line_id,
p_ato_line_id,
bcol.config_item_id,
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,
assg.assignment_id,
'Y'
from
mrp_sr_receipt_org rcv,
mrp_sr_source_org src,
mrp_sr_assignments assg,
mrp_sourcing_rules rule,
po_vendor_sites_all vend,
bom_cto_order_lines_gt bcol
where
assg.assignment_set_id = g_def_assg_set
and bcol.ato_line_id = p_ato_line_id
and bcol.config_creation = 3
and (nvl(bcol.perform_match,'N') = 'Y' or nvl(bcol.reuse_config,'N') = 'Y')
and assg.inventory_item_id = bcol.config_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
source_organization_id,
organization_id,
sourcing_rule_id,
nvl(source_type,1) source_type,
assignment_type,
assignment_id
from mrp_sources_v msv
where msv.assignment_set_id = g_def_assg_set
and msv.inventory_item_id = p_item_id
and msv.organization_id = p_org_id
and nvl(effective_date,sysdate) <= nvl(disable_date, sysdate)
and nvl(disable_date, sysdate+1) > sysdate
and assignment_id is not null;
insert into bom_cto_oss_orgslist_gt(
line_id,
organization_id,
ato_line_id
)
values (
p_line_id,
src_rec.organization_id,
p_ato_line_id
);
insert into bom_cto_oss_orgslist_gt(
line_id,
organization_id,
ato_line_id
)
values (
p_line_id,
p_org_id,
p_ato_line_id
);
g_source_org_stk.delete(p_org_id);
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
source_org_id,
source_type
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag = 'Y'
and rcv_org_id = p_organization_id;
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
source_org_id,
source_type
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag = 'Y'
and rcv_org_id is null;
Select planning_make_buy_code
into x_source_type
from mtl_system_items
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
Select org_id
bulk collect into x_orgs_list
from
(Select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
distinct nvl(source_org_id,rcv_org_id) org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag in( 'P','Y')
and source_type in (2,3)
union
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
distinct source_org_id org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag in ('P','Y')
and source_org_id not in (
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
rcv_org_id
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id
and valid_flag in( 'P','Y')));
select /*+ INDEX (oss_src BOM_CTO_OSS_SOURCE_GT_N2) */
inventory_item_id,
rcv_org_id,
source_org_id,
customer_id,
vendor_id,
vendor_site_code,
rank,
allocation,
source_type,
reuse_flag,
valid_flag,
leaf_node
from bom_cto_oss_source_gt oss_src
where line_id = p_line_id;
select line_id,
ato_line_id,
inventory_item_id,
organization_id,
vendor_id,
vendor_site_code,
make_flag
from bom_cto_oss_orgslist_gt
where line_id = p_line_id;