The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(MAXIMUM_BOM_LEVEL)
INTO max_level
FROM BOM_PARAMETERS
WHERE (org_id = -1
or
(org_id <> -1 and ORGANIZATION_ID = org_id)
);
SELECT
COMPONENT_CODE,
LOOP_FLAG,
PLAN_LEVEL
FROM BOM_SMALL_EXPL_TEMP
WHERE GROUP_ID = c_group_id
AND LOOP_FLAG = 1;
SELECT NVL( TOP_ALTERNATE_DESIGNATOR, 'none' ), ORGANIZATION_ID
INTO top_alt, org_id
FROM BOM_SMALL_EXPL_TEMP
WHERE GROUP_ID = grp_id
AND ROWNUM = 1
AND PLAN_LEVEL = 0;
SELECT
substrb(MIF.ITEM_NUMBER || ' ' || BBM.ALTERNATE_BOM_DESIGNATOR,1,16)
INTO cur_msgstr
FROM MTL_ITEM_FLEXFIELDS MIF, BOM_BILL_OF_MATERIALS BBM
WHERE MIF.ORGANIZATION_ID = BBM.ORGANIZATION_ID
AND MIF.ITEM_ID = BBM.ASSEMBLY_ITEM_ID
AND BBM.ASSEMBLY_ITEM_ID = cur_item_id
AND BBM.ORGANIZATION_ID = org_id
AND (
((top_alt = 'none') AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL)
OR
((top_alt <> 'none')
AND (
( EXISTS ( SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM1
WHERE BBM1.ORGANIZATION_ID = org_id
AND BBM1.ASSEMBLY_ITEM_ID = cur_item_id
AND BBM1.ALTERNATE_BOM_DESIGNATOR = top_alt)
AND BBM.ALTERNATE_BOM_DESIGNATOR = top_alt
)
OR
( NOT EXISTS (SELECT NULL FROM BOM_BILL_OF_MATERIALS BBM2
WHERE BBM2.ORGANIZATION_ID = org_id
AND BBM2.ASSEMBLY_ITEM_ID = cur_item_id
AND BBM2.ALTERNATE_BOM_DESIGNATOR = top_alt)
AND BBM.ALTERNATE_BOM_DESIGNATOR IS NULL
)
)
)
);
Select BET.bill_sequence_id curBSI,
BET.component_sequence_id curCSI,
BET.component_item_id curCII,
BET.common_bill_sequence_id curCBSI,
msi1.organization_id t_master_org_id,
msi1.primary_uom_code t_master_uom,
msi2.primary_uom_code t_child_uom
from BOM_SMALL_EXPL_TEMP BET, bom_bill_of_materials bbm, mtl_system_items msi1, mtl_system_items msi2
where BET.bill_sequence_id <> BET.common_bill_sequence_id
and bbm.bill_sequence_id = BET.common_bill_sequence_id
and msi1.inventory_item_id = BET.component_item_id
and msi1.organization_id = bbm.organization_id
and msi2.inventory_item_id = BET.component_item_id
and msi2.organization_id = BET.organization_id
and BET.group_id = grp_id;
select conversion_rate
from mtl_uom_conversions_view
where primary_uom_code = t_master_uom and
uom_code = t_child_uom and
inventory_item_id = t_inv_id and
organization_id = t_master_org_id;
insert into bom_small_expl_temp
(
group_id,
bill_sequence_id,
component_sequence_id,
organization_id,
top_item_id,
component_item_id,
plan_level,
extended_quantity,
basis_type,
component_quantity,
sort_order,
program_update_date,
top_bill_sequence_id,
component_code,
loop_flag,
top_alternate_designator,
bom_item_type,
parent_bom_item_type
)
select
grp_id,
bom.bill_sequence_id,
NULL,
org_id,
item_id,
item_id,
0,
expl_qty,
1,
1,
lpad('1', X_SortWidth, '0'),
sysdate,
bom.bill_sequence_id,
nvl(comp_code, lpad(item_id, 16, '0')),
2,
alt_desg,
msi.bom_item_type,
msi.bom_item_type
from bom_bill_of_materials bom, mtl_system_items msi
where bom.assembly_item_id = item_id
and bom.organization_id = org_id
and nvl(alternate_bom_designator, 'NONE') =
nvl(alt_desg, 'NONE')
and msi.organization_id = org_id
and inventory_item_id = item_id;
select count(*) into cnt
from mtl_parameters
where organization_id = cost_organization_id
and organization_id = org_id;
select msi.primary_uom_code, msi.organization_id into
t_master_uom, t_master_org_id
from mtl_system_items msi, bom_bill_of_materials bbm
where cr.curCBSI = bbm.bill_sequence_id and
bbm.organization_id = msi.organization_id and
msi.inventory_item_id = cr.curCII;
select msi.primary_uom_code into t_child_uom
from mtl_system_items msi
where msi.inventory_item_id = cr.curCII and
msi.organization_id = cr.curOI;
select conversion_rate into t_conversion_rate
from mtl_uom_conversions_view
where primary_uom_code = t_master_uom and
uom_code = t_child_uom and
inventory_item_id = cr.curCII and
organization_id = t_master_org_id;
/* Bug 9355186 : Individual updates converted into bulk update
if is_cost_organization <> 'Y' then
UPDATE BOM_SMALL_EXPL_TEMP
SET item_cost = item_cost*t_conversion_rate
WHERE group_id = cr.curGI and
component_sequence_id = cr.curCSI and
bill_sequence_id = cr.curBSI and
common_bill_sequence_id = cr.curCBSI;
UPDATE BOM_SMALL_EXPL_TEMP
SET component_quantity = trunc(component_quantity/t_conversion_rate, 22), --Bug 9173185 fix
extended_quantity = extended_quantity/t_conversion_rate,
-- item_cost = item_cost*t_conversion_rate,
primary_uom_code = cr.curPUC
WHERE group_id = cr.curGI and
component_sequence_id = cr.curCSI and
bill_sequence_id = cr.curBSI and
common_bill_sequence_id = cr.curCBSI;
UPDATE /*+ index(BOM_SMALL_EXPL_TEMP BOM_SMALL_EXPL_TEMP_n1) */ BOM_SMALL_EXPL_TEMP
SET
-- Bug 2157325 Begin
-- If cost_organization is Master organization then the item cost should be
-- calculated by multiplying the conversion_rate.
item_cost = decode(is_cost_organization,'Y',item_cost,item_cost*t_conversion_rate(i)),
component_quantity = trunc(component_quantity/t_conversion_rate(i), 22), --Bug 8977128 fix
extended_quantity = extended_quantity/t_conversion_rate(i)
WHERE group_id = grp_id and
component_sequence_id = curCSI(i) and
bill_sequence_id = curBSI(i) and
common_bill_sequence_id = curCBSI(i);
SELECT
TOP_BILL_SEQUENCE_ID ,
BILL_SEQUENCE_ID ,
COMMON_BILL_SEQUENCE_ID ,
ORGANIZATION_ID ,
COMPONENT_SEQUENCE_ID ,
COMPONENT_ITEM_ID ,
BASIS_TYPE ,
COMPONENT_QUANTITY ,
PLAN_LEVEL ,
EXTENDED_QUANTITY ,
SORT_ORDER ,
GROUP_ID ,
TOP_ALTERNATE_DESIGNATOR ,
COMPONENT_YIELD_FACTOR ,
TOP_ITEM_ID ,
COMPONENT_CODE ,
INCLUDE_IN_ROLLUP_FLAG ,
LOOP_FLAG ,
PLANNING_FACTOR ,
OPERATION_SEQ_NUM ,
BOM_ITEM_TYPE ,
PARENT_BOM_ITEM_TYPE ,
ASSEMBLY_ITEM_ID ,
WIP_SUPPLY_TYPE ,
ITEM_NUM ,
EFFECTIVITY_DATE ,
DISABLE_DATE ,
IMPLEMENTATION_DATE ,
OPTIONAL ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
COMPONENT_REMARKS ,
CHANGE_NOTICE ,
OPERATION_LEAD_TIME_PERCENT,
MUTUALLY_EXCLUSIVE_OPTIONS ,
CHECK_ATP ,
REQUIRED_TO_SHIP ,
REQUIRED_FOR_REVENUE ,
INCLUDE_ON_SHIP_DOCS ,
LOW_QUANTITY ,
HIGH_QUANTITY ,
SO_BASIS ,
OPERATION_OFFSET ,
CURRENT_REVISION ,
LOCATOR ,
CONTEXT ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
ITEM_COST ,
EXTEND_COST_FLAG
FROM bom_small_expl_temp
WHERE
Organization_id = l_organization_id
AND GROUP_ID = l_group_id;
SELECT count (*) into l_org_count from
per_organization_structures
WHERE INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_ACCESS(Org_hierarchy_name)='Y'
AND name = Org_hierarchy_name;
SELECT organization_name into l_org_name
FROM org_organization_definitions
WHERE organization_id = l_organization_id;
SELECT assembly_item_id INTO c_assembly_item_id
FROM bom_bill_of_materials
WHERE assembly_item_id = l_assembly_item_id
AND organization_id = t_org_code_list(I)
AND nvl(ALTERNATE_BOM_DESIGNATOR,'NONE')=
nvl(Alternate_bm_designator,'NONE') ;
SELECT COST_TYPE_ID into l_cst_type_id
FROM cst_item_cost_type_v
WHERE inventory_item_id = Assembly_item_id
AND cost_type_id = c_Cost_type_id
AND organization_id = t_org_code_list(I);
SELECT MAXIMUM_BOM_LEVEL INTO max_level
FROM BOM_PARAMETERS
WHERE ORGANIZATION_ID = t_org_code_list(I);
SELECT bom_explosion_temp_s.nextval
INTO l_group_id from dual;
DELETE from bom_small_expl_temp where group_id =l_group_id;
DELETE from bom_small_expl_temp where group_id =l_group_id;
bom_export_tab.delete;
bom_export_tab.delete;
bom_export_tab.delete;
bom_export_tab.delete;
bom_export_tab.delete;
bom_export_tab.delete ;
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = P_item_id
AND organization_id = P_organization_id;
SELECT organization_code
INTO l_org_code
FROM mtl_parameters
WHERE organization_id = P_organization_id;
SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = P_locator_id
AND organization_id = P_organization_id;
SELECT specific_assembly_comment,
assembly_type,
common_assembly_item_id,
common_organization_id,
original_system_reference,
alternate_bom_designator,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM bom_bill_of_materials
WHERE bill_sequence_id = p_bill_sequence_id;
SELECT revision,
description,
effectivity_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM mtl_item_revisions
WHERE inventory_item_id = P_assembly_item_id
AND organization_id = P_organization_id;
SELECT effectivity_date,
disable_date,
operation_seq_num,
acd_type,
item_num,
basis_type,
component_quantity,
planning_factor,
component_yield_factor,
include_in_cost_rollup,
wip_supply_type,
so_basis,
optional,
mutually_exclusive_options,
check_atp,
shipping_allowed,
required_to_ship,
required_for_revenue,
include_on_ship_docs,
quantity_related,
supply_subinventory,
low_quantity,
high_quantity,
component_remarks,
from_end_item_unit_number,
to_end_item_unit_number,
enforce_int_requirements,
supply_locator_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM bom_inventory_components
WHERE bill_sequence_id = P_bill_sequence_id
AND component_sequence_id = P_component_sequence_id
AND component_item_id = P_component_item_id;
SELECT implementation_date,
substitute_component_id,
substitute_item_quantity,
enforce_int_requirements,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM bom_substitute_components_v
WHERE component_sequence_id = P_component_sequence_id;
SELECT component_reference_designator,
implementation_date,
ref_designator_comment,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM bom_reference_designators_v
WHERE component_sequence_id = P_component_sequence_id;
SELECT operation_seq_num,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15
FROM bom_component_operations
WHERE component_sequence_id = P_component_sequence_id;
SELECT organization_id
FROM mtl_parameters
WHERE organization_code = P_organization_code;
SELECT inventory_item_id
FROM mtl_system_items
WHERE segment1 = P_assembly_item_name
AND organization_id = l_organization_id;
G_Header_Record_id_Tbl.DELETE;
G_bom_header_tbl.DELETE;
G_bom_revisions_tbl.DELETE;
G_bom_components_tbl.DELETE;
G_bom_ref_designators_tbl.DELETE;
G_bom_sub_components_tbl.DELETE;
G_bom_comp_ops_tbl.DELETE;
X_bom_header_tbl.DELETE;
X_bom_revisions_tbl.DELETE;
X_bom_components_tbl.DELETE;
X_bom_ref_designators_tbl.DELETE;
X_bom_sub_components_tbl.DELETE;
X_bom_comp_ops_tbl.DELETE;
X_bom_header_tbl.DELETE;
X_bom_revisions_tbl.DELETE;
X_bom_components_tbl.DELETE;
X_bom_ref_designators_tbl.DELETE;
X_bom_sub_components_tbl.DELETE;
X_bom_comp_ops_tbl.DELETE;
X_bom_header_tbl.DELETE;
X_bom_revisions_tbl.DELETE;
X_bom_components_tbl.DELETE;
X_bom_ref_designators_tbl.DELETE;
X_bom_sub_components_tbl.DELETE;
X_bom_comp_ops_tbl.DELETE;