[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/*select ato_line_id
into p_ato_line_id
from bom_cto_order_lines
where config_item_id = p_item_id
and rownum = 1;*/
select line_id
into l_line_id
from bom_cto_order_lines bcol
where config_item_id = p_item_id
and exists (select 'x'
from oe_order_lines_all oel
where oel.header_id = bcol.header_id
and oel.ato_line_id = bcol.ato_line_id
and oel.item_type_code = 'CONFIG')
and rownum = 1;
select bom_explosion_temp_s.nextval
into x_grp_id
from dual;
select organization_id
into p_organization_id
from bom_bill_of_materials
where assembly_item_id = p_item_id
and rownum = 1;
select bom_explosion_temp_s.nextval
into x_grp_id
from dual;
select line_id
into l_line_id
from bom_cto_order_lines bcol
where config_item_id = p_item_id
and exists (select 'x'
from oe_order_lines_all oel
where oel.header_id = bcol.header_id
and oel.ato_line_id = bcol.ato_line_id
and oel.item_type_code = 'CONFIG')
and rownum = 1;
select bom_explosion_temp_s.nextval
into x_grp_id
from dual;
select organization_id
into p_organization_id
from bom_bill_of_materials
where assembly_item_id = p_item_id
and rownum = 1;
select bom_explosion_temp_s.nextval
into x_grp_id
from dual;
select to_number( nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , org_id) , ship_from_org_id))
from oe_order_lines_all
where line_id = p_line_id;
-- insert details from order lines
lStmtNumber := 20;
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
line_id,
primary_uom_code,
group_id)
select
1, -- top_bill_sequence_id
1, -- bill_sequence_id
l_oeval_org_id, -- organization_id
l_sort, -- sort
bcol2.config_item_id, -- assembly_item_id
decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
1, -- optional
bcol1.plan_level - bcol2.plan_level, -- plan_level
bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
bcol1.line_id, -- line_id
bcol1.order_quantity_uom, --primary_uom_code
p_grp_id
from
bom_cto_order_lines bcol1 -- component
,bom_cto_order_lines bcol2 -- parent model
where bcol1.parent_ato_line_id = p_line_id
and bcol1.parent_ato_line_id <> bcol1.line_id
and bcol2.line_id = p_line_id
UNION
select
1, -- top_bill_sequence_id
1, -- bill_sequence_id
l_oeval_org_id, -- organization_id
l_sort, -- sort
bcol1.config_item_id, -- assembly_item_id
bcol1.inventory_item_id,-- component_item_id
1, -- optional
bcol1.plan_level - bcol1.plan_level,
bcol1.ordered_quantity/bcol1.ordered_quantity, -- comp qty
'N', -- config flag
bcol1.line_id, -- line_id
bcol1.order_quantity_uom, --primary_uom_code
p_grp_id
from
bom_cto_order_lines bcol1
where bcol1.line_id = p_line_id
;
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
line_id,
primary_uom_code,
group_id)
select
1, -- top_bill_sequence_id
1, -- bill_sequence_id
l_oeval_org_id, -- organization_id
l_sort, -- sort
bcol2.config_item_id, -- assembly_item_id
decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
1, -- optional
bcol1.plan_level - bcol2.plan_level + bet.plan_level, -- plan_level
bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
bcol1.line_id, -- line_id
bcol1.order_quantity_uom, --primary_uom_code
p_grp_id
from
bom_cto_order_lines bcol1 -- component
,bom_cto_order_lines bcol2 -- parent model
,bom_explosion_temp bet
where bcol1.parent_ato_line_id = bet.line_id
and bcol2.line_id = bet.line_id
and bet.group_id = p_grp_id
and bet.sort_order = to_char(l_sort - 1)
and nvl(bet.configurator_flag, 'N') = 'Y'
UNION
select
1, -- top_bill_sequence_id
1, -- bill_sequence_id
l_oeval_org_id, -- organization_id
l_sort, -- sort
bcol1.config_item_id, -- assembly_item_id
bcol1.inventory_item_id,-- component_item_id
1, -- optional
bcol1.plan_level - bcol1.plan_level + bet.plan_level,
bcol1.ordered_quantity/bcol1.ordered_quantity, -- comp qty
'N', -- config flag
bcol1.line_id, -- line_id
bcol1.order_quantity_uom, --primary_uom_code
p_grp_id
from
bom_cto_order_lines bcol1
,bom_explosion_temp bet
where bcol1.line_id = bet.line_id
and bet.group_id = p_grp_id
and bet.sort_order = to_char(l_sort - 1)
and nvl(bet.configurator_flag, 'N') = 'Y'
;
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
line_id,
primary_uom_code,
group_id)
select
1, -- top_bill_sequence_id
1, -- bill_sequence_id
l_oeval_org_id, -- organization_id
l_sort, -- sort
bcol2.config_item_id, -- assembly_item_id
decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
1, -- optional
bcol1.plan_level - bcol2.plan_level, -- plan_level
bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
bcol1.line_id, -- line_id
bcol1.order_quantity_uom, --primary_uom_code
p_grp_id
from
bom_cto_order_lines bcol1 -- component
,bom_cto_order_lines bcol2 -- parent model
where bcol1.parent_ato_line_id = p_line_id
and bcol1.parent_ato_line_id <> bcol1.line_id
and bcol1.config_item_id is not null
and bcol2.line_id = p_line_id
;
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
line_id,
primary_uom_code,
group_id)
select
1, -- top_bill_sequence_id
1, -- bill_sequence_id
l_oeval_org_id, -- organization_id
l_sort, -- sort
bcol2.config_item_id, -- assembly_item_id
decode(bcol1.config_item_id, null, bcol1.inventory_item_id, bcol1.config_item_id), -- component_item_id
1, -- optional
bcol1.plan_level - bcol2.plan_level + bet.plan_level, -- plan_level
bcol1.ordered_quantity/bcol2.ordered_quantity, -- comp qty
decode(bcol1.config_item_id, null, 'N', 'Y'), -- config flag
bcol1.line_id, -- line_id
bcol1.order_quantity_uom, --primary_uom_code
p_grp_id
from
bom_cto_order_lines bcol1 -- component
,bom_cto_order_lines bcol2 -- parent model
,bom_explosion_temp bet
where bcol1.parent_ato_line_id = bet.line_id
and bcol1.config_item_id is not null
and bcol2.line_id = bet.line_id
and bet.group_id = p_grp_id
and bet.sort_order = to_char(l_sort - 1)
and nvl(bet.configurator_flag, 'N') = 'Y'
;
oe_debug_pub.add('get_config_details_bcol:rows inserted into bom_expl_temp::'||to_char(sql%rowcount));
rows inserted into bom_expl_temp::'||to_char(sql%rowcount));
-- insert top level config BOM
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
primary_uom_code,
group_id,
basis_type ) /* LBM Project change */
select distinct
bic.bill_sequence_id,
bic.bill_sequence_id,
p_organization_id,
to_char(l_sort), -- sort
p_item_id,
bic.component_item_id,
bic.optional_on_model, -- optional
nvl(bic.plan_level, 0),
bic.component_quantity,
decode(msi.base_item_id, NULL, 'N', decode(nvl(bic.model_comp_seq_id, bic.last_update_login), 0, 'N', NULL, 'N', abs(nvl(bic.model_comp_seq_id, bic.last_update_login)), 'Y', 'N')), -- config_flag
msi.primary_uom_code, -- primary_uom_code
p_grp_id,
bic.basis_type /* LBM Project change */
from
bom_inventory_components bic,
bom_bill_of_materials bbom,
mtl_system_items msi
where bbom.assembly_item_id = p_item_id
and bbom.organization_id = p_organization_id
and bbom.alternate_bom_designator is null
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and nvl(bic.optional_on_model,2) = 1
and msi.inventory_item_id = bic.component_item_id
and msi.organization_id = p_organization_id;
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
primary_uom_code,
group_id,
basis_type) /* LBM Project change */
select distinct
bic.bill_sequence_id,
bic.bill_sequence_id,
bbom.organization_id,
to_char(l_sort), -- sort
bet.component_item_id,
bic.component_item_id,
bic.optional_on_model, -- optional
decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
bic.component_quantity,
decode(msi2.base_item_id, NULL, 'N', decode(nvl(bic.model_comp_seq_id, bic.last_update_login), 0, 'N', NULL, 'N', abs(nvl(bic.model_comp_seq_id, bic.last_update_login)), 'Y', 'N')), -- config_flag
msi2.primary_uom_code, -- primary_uom_code
p_grp_id,
bic.basis_type /* LBM Project change */
from
bom_inventory_components bic,
bom_bill_of_materials bbom,
bom_explosion_temp bet,
mtl_system_items msi, -- bet component join
mtl_system_items msi2 -- bic component join
where
bbom.assembly_item_id = bet.component_item_id
and bbom.organization_id =
(select bbom1.organization_id
from bom_bill_of_materials bbom1
where bbom1.assembly_item_id = bet.component_item_id
and bbom1.alternate_bom_designator is null
and rownum = 1)
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and nvl(bic.optional_on_model,1) = 1
and bet.group_id = p_grp_id
and bet.sort_order = to_char(l_sort - 1)
and bet.component_item_id = msi.inventory_item_id
and bbom.organization_id = msi.organization_id
and nvl(bet.configurator_flag, 'N') = 'Y'
and msi.base_item_id is not null
and msi.replenish_to_order_flag = 'Y'
and msi2.inventory_item_id = bic.component_item_id
and msi2.organization_id = bbom.organization_id;
-- insert configs from top level config BOM
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
primary_uom_code,
group_id,
basis_type) /* LBM Project change */
select distinct
bic.bill_sequence_id,
bic.bill_sequence_id,
p_organization_id,
to_char(l_sort), -- sort
p_item_id,
bic.component_item_id,
bic.optional_on_model, -- optional
nvl(bic.plan_level, 0),
bic.component_quantity,
'Y', -- config flag
msi.primary_uom_code, -- primary_uom_code
p_grp_id,
bic.basis_type /* LBM Project change */
from
bom_inventory_components bic,
bom_bill_of_materials bbom,
mtl_system_items msi
where bbom.assembly_item_id = p_item_id
and bbom.organization_id = p_organization_id
and bbom.alternate_bom_designator is null
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and nvl(bic.model_comp_seq_id, bic.last_update_login) = abs(nvl(bic.model_comp_seq_id, bic.last_update_login))
and msi.inventory_item_id = bic.component_item_id
and msi.organization_id = p_organization_id
and msi.base_item_id is not null;
insert into bom_explosion_temp(
top_bill_sequence_id, -- not null
bill_sequence_id, -- not null
organization_id, -- not null
sort_order, -- not null
assembly_item_id,
component_item_id,
optional,
plan_level, -- not null
component_quantity,
configurator_flag,
primary_uom_code,
group_id,
basis_type ) /* LBM Project change */
select distinct
bic.bill_sequence_id,
bic.bill_sequence_id,
bbom.organization_id,
to_char(l_sort), -- sort
bet.component_item_id,
bic.component_item_id,
bic.optional_on_model, -- optional
decode(bic.plan_level,null,(bet.plan_level+1),(bic.plan_level+bet.plan_level)),
bic.component_quantity,
'Y', -- config flag
msi.primary_uom_code, -- primary_uom_code
p_grp_id,
bic.basis_type /* LBM Project change */
from
bom_inventory_components bic,
bom_bill_of_materials bbom,
bom_explosion_temp bet,
mtl_system_items msi
where
bbom.assembly_item_id = bet.component_item_id
and bbom.organization_id =
(select bbom1.organization_id
from bom_bill_of_materials bbom1
where bbom1.assembly_item_id = bet.component_item_id
and bbom1.alternate_bom_designator is null
and rownum = 1)
and bbom.common_bill_sequence_id = bic.bill_sequence_id
and nvl(bic.model_comp_seq_id, bic.last_update_login) = abs(nvl(bic.model_comp_seq_id, bic.last_update_login))
and bet.group_id = p_grp_id
and bet.sort_order = to_char(l_sort - 1)
and bic.component_item_id = msi.inventory_item_id
and bbom.organization_id = msi.organization_id
and msi.base_item_id is not null
and msi.replenish_to_order_flag = 'Y';
select component_item_id
, configurator_flag
, component_quantity
, primary_uom_code
from bom_explosion_temp
where group_id = x_group_id
and nvl(optional, 1) = 1
and assembly_item_id = l_item_id;
select base_item_id,
build_in_wip_flag,
Primary_uom_code
into l_base_item_id,
l_build_in_wip,
l_prim_uom
from mtl_system_items
where inventory_item_id = p_config_item_id
and organization_id = p_from_organization_id; -- org to create the AR