[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* This Procedure will delete the user attributes for components.
*/
Procedure Delete_Comp_User_Attrs(p_comp_seq_id IN NUMBER)
IS
BEGIN
delete from BOM_COMPONENTS_EXT_B
where component_sequence_id = p_comp_seq_id;
delete from BOM_COMPONENTS_EXT_TL
where component_sequence_id = p_comp_seq_id;
END Delete_Comp_User_Attrs;
select structures.bill_sequence_id
from ( select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id ) icc,
BOM_STRUCTURES_B structures
where structures.pk1_value = icc.item_catalog_group_id
and structures.obj_name = 'EGO_CATALOG_GROUP'
and rownum = 1;
select 1
into l_create_header
from dual
where exists (select 1
from EGO_MTL_CATALOG_GRP_VERS_B
where item_catalog_group_id = p_item_catalog_grp_id)
and not exists (select 1
from BOM_STRUCTURES_B
where pk1_value = p_item_catalog_grp_id
and obj_name = 'EGO_CATALOG_GROUP');
select bom_inventory_components_s.nextval
into l_bill_sequence_id
from dual;
insert into BOM_STRUCTURES_B
(BILL_SEQUENCE_ID,
SOURCE_BILL_SEQUENCE_ID,
COMMON_BILL_SEQUENCE_ID,
ORGANIZATION_ID,
ALTERNATE_BOM_DESIGNATOR,
ASSEMBLY_TYPE,
STRUCTURE_TYPE_ID,
EFFECTIVITY_CONTROL,
IS_PREFERRED,
OBJ_NAME,
PK1_VALUE,
PK2_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
select
l_bill_sequence_id,
l_bill_sequence_id,
l_bill_sequence_id,
ORGANIZATION_ID,
ALTERNATE_BOM_DESIGNATOR,
ASSEMBLY_TYPE,
STRUCTURE_TYPE_ID,
EFFECTIVITY_CONTROL,
IS_PREFERRED,
OBJ_NAME,
p_item_catalog_grp_id,
PK2_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
from BOM_STRUCTURES_B
where BILL_SEQUENCE_ID = icc_structure.bill_sequence_id ;
SELECT COMPONENT_SEQUENCE_ID
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_seq_id
and nvl(from_object_revision_id,0) = 0;
SELECT COMPONENT_SEQUENCE_ID
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_seq_id
and from_object_revision_id = p_version_seq_id
and nvl(parent_bill_seq_id,p_bill_seq_id) = p_bill_seq_id;
SELECT bill_sequence_id,
structure_type_id
INTO l_bill_seq_id,
l_structure_type_id
FROM BOM_STRUCTURES_B
WHERE pk1_value = p_item_catalog_grp_id
AND obj_name = 'EGO_CATALOG_GROUP';
delete_comp_user_attrs(component.component_sequence_id);
delete from bom_components_b
where bill_sequence_id = l_bill_seq_id
and nvl(from_object_revision_id,0) = 0;
select data_level_id
into l_data_level_id
from ego_data_level_b
where data_level_name = 'COMPONENTS_LEVEL'
and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
and application_id = 702;
select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
into l_new_component_seq_id
from dual;
Insert into BOM_COMPONENTS_B
(OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ITEM_NUM,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
WIP_SUPPLY_TYPE,
PICK_COMPONENTS,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
BOM_ITEM_TYPE,
ENFORCE_INT_REQUIREMENTS,
COMPONENT_ITEM_REVISION_ID,
PARENT_BILL_SEQ_ID,
AUTO_REQUEST_MATERIAL,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
FROM_OBJECT_REVISION_ID,
COMPONENT_REMARKS,
CHANGE_NOTICE,
BASIS_TYPE,
LOW_QUANTITY,
HIGH_QUANTITY)
select
BCB.OPERATION_SEQ_NUM,
BCB.COMPONENT_ITEM_ID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
BCB.ITEM_NUM,
BCB.COMPONENT_QUANTITY,
BCB.COMPONENT_YIELD_FACTOR,
BCB.EFFECTIVITY_DATE,
BCB.IMPLEMENTATION_DATE,
BCB.PLANNING_FACTOR,
BCB.QUANTITY_RELATED,
BCB.SO_BASIS,
BCB.OPTIONAL,
BCB.MUTUALLY_EXCLUSIVE_OPTIONS,
BCB.INCLUDE_IN_COST_ROLLUP,
BCB.CHECK_ATP,
BCB.SHIPPING_ALLOWED,
BCB.REQUIRED_TO_SHIP,
BCB.REQUIRED_FOR_REVENUE,
BCB.INCLUDE_ON_SHIP_DOCS,
l_new_component_seq_id,
l_bill_seq_id,
BCB.WIP_SUPPLY_TYPE,
BCB.PICK_COMPONENTS,
BCB.SUPPLY_SUBINVENTORY,
BCB.SUPPLY_LOCATOR_ID,
BCB.BOM_ITEM_TYPE,
BCB.ENFORCE_INT_REQUIREMENTS,
BCB.COMPONENT_ITEM_REVISION_ID,
BCB.PARENT_BILL_SEQ_ID,
BCB.AUTO_REQUEST_MATERIAL,
BCB.PK1_VALUE,
BCB.PK2_VALUE,
BCB.PK3_VALUE,
BCB.PK4_VALUE,
BCB.PK5_VALUE,
0,
BCB.COMPONENT_REMARKS,
BCB.CHANGE_NOTICE,
BCB.BASIS_TYPE,
BCB.LOW_QUANTITY,
BCB.HIGH_QUANTITY
from BOM_COMPONENTS_B BCB
where BCB.COMPONENT_SEQUENCE_ID = component.component_sequence_id
and BCB.BILL_SEQUENCE_ID = l_bill_seq_id;
SELECT component_sequence_id,
component_item_id,
item_num,
component_quantity,
component_item_revision_id,
parent_bill_seq_id,
pk1_value,
pk2_value,
pk3_value,
component_remarks,
change_notice,
quantity_related,
component_yield_factor,
enforce_int_requirements,
include_in_cost_rollup,
basis_type,
bom_item_type,
planning_factor,
supply_locator_id,
supply_subinventory,
auto_request_material,
wip_supply_type,
check_atp,
optional,
mutually_exclusive_options,
low_quantity,
high_quantity,
so_basis,
shipping_allowed,
include_on_ship_docs,
required_for_revenue,
required_to_ship,
pick_components
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_seq_id
and nvl(parent_bill_seq_id,bill_sequence_id) = bill_sequence_id
and from_object_revision_id = p_ver_seq_id;
select item_catalog_group_id,
parent_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id;
SELECT bill_sequence_id,
assembly_type,
pk2_value,
effectivity_control,
alternate_bom_designator,
structure_type_id
INTO l_bill_seq_id,
l_assembly_type,
l_pk2_value,
l_effectivity_control,
l_alternate_bom_designator,
l_structure_type_id
FROM BOM_STRUCTURES_B
WHERE pk1_value = p_item_catalog_grp_id
and obj_name = 'EGO_CATALOG_GROUP';
SELECT bill_sequence_id,
assembly_type,
pk2_value,
effectivity_control,
alternate_bom_designator,
structure_type_id
into l_parent_bill_seq_id,
l_par_assembly_type,
l_par_pk2_value,
l_par_effectivity_control,
l_par_alternate_bom_designator,
l_par_structure_type_id
FROM BOM_STRUCTURES_B
WHERE pk1_value = l_parent_catalog_grp_id
and obj_name = 'EGO_CATALOG_GROUP';
select data_level_id
into l_data_level_id
from ego_data_level_b
where data_level_name = 'COMPONENTS_LEVEL'
and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
and application_id = 702;
select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
into l_new_component_seq_id
from dual;
Insert into BOM_COMPONENTS_B
(OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ITEM_NUM,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
WIP_SUPPLY_TYPE,
PICK_COMPONENTS,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
BOM_ITEM_TYPE,
ENFORCE_INT_REQUIREMENTS,
COMPONENT_ITEM_REVISION_ID,
PARENT_BILL_SEQ_ID,
AUTO_REQUEST_MATERIAL,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
FROM_OBJECT_REVISION_ID,
COMPONENT_REMARKS,
CHANGE_NOTICE,
BASIS_TYPE,
LOW_QUANTITY,
HIGH_QUANTITY)
values(
1,
v_struct_comp(cntr).component_item_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
v_struct_comp(cntr).item_num,
v_struct_comp(cntr).component_quantity,
v_struct_comp(cntr).component_yield_factor,
sysdate,
sysdate,
v_struct_comp(cntr).planning_factor,
v_struct_comp(cntr).quantity_related,
v_struct_comp(cntr).so_basis,
v_struct_comp(cntr).optional,
v_struct_comp(cntr).mutually_exclusive_options,
v_struct_comp(cntr).include_in_cost_rollup,
v_struct_comp(cntr).check_atp,
v_struct_comp(cntr).shipping_allowed,
v_struct_comp(cntr).required_to_ship,
v_struct_comp(cntr).required_for_revenue,
v_struct_comp(cntr).include_on_ship_docs,
l_new_component_seq_id,
l_bill_seq_id,
v_struct_comp(cntr).wip_supply_type,
v_struct_comp(cntr).pick_components,
v_struct_comp(cntr).supply_subinventory,
v_struct_comp(cntr).supply_locator_id,
v_struct_comp(cntr).bom_item_type,
v_struct_comp(cntr).enforce_int_requirements,
v_struct_comp(cntr).component_item_revision_id,
decode(v_struct_comp(cntr).parent_bill_seq_id,null,l_bill_seq_id,v_struct_comp(cntr).parent_bill_seq_id),
v_struct_comp(cntr).auto_request_material,
v_struct_comp(cntr).pk1_value,
v_struct_comp(cntr).pk2_value,
v_struct_comp(cntr).component_item_revision_id,
p_version_seq_id,
v_struct_comp(cntr).component_remarks,
v_struct_comp(cntr).change_notice,
v_struct_comp(cntr).basis_type,
v_struct_comp(cntr).low_quantity,
v_struct_comp(cntr).high_quantity);
select concatenated_segments
into l_item_catalog_grp_name
from MTL_ITEM_CATALOG_GROUPS_B_KFV
where item_catalog_group_id = p_item_catalog_grp_id;
select pk1_value
into l_item_catalog_grp_id
from BOM_STRUCTURES_B
where bill_sequence_id = p_parent_bill_seq_id
and obj_name= 'EGO_CATALOG_GROUP';
select concatenated_segments
into l_item_catalog_grp_name
from MTL_ITEM_CATALOG_GROUPS_B_KFV
where item_catalog_group_id = l_item_catalog_grp_id;
select version_seq_id
into l_effective_version
from EGO_MTL_CATALOG_GRP_VERS_B
where item_catalog_group_id = p_item_catalog_grp_id
and version_seq_id <> 0
and nvl(p_start_date,sysdate) between start_active_date and nvl(end_active_date,nvl(p_start_date,sysdate+1));
SELECT parent_catalog_group_id
into l_parent_catalog_grp_id
FROM MTL_ITEM_CATALOG_GROUPS_B
WHERE item_catalog_group_id = p_item_catalog_grp_id;
* This Procedure will give whether Draft version has been updated or not.
*/
Function Is_Structure_Updated(p_item_catalog_grp_id IN NUMBER,
p_start_date IN DATE)
RETURN NUMBER
IS
Cursor get_components(p_bill_seq_id NUMBER,
p_ver_seq_id NUMBER)
IS
SELECT component_sequence_id,
component_item_id,
item_num,
component_quantity,
component_item_revision_id,
parent_bill_seq_id,
pk1_value,
pk2_value,
pk3_value,
component_remarks,
change_notice,
quantity_related,
component_yield_factor,
enforce_int_requirements,
include_in_cost_rollup,
basis_type,
bom_item_type,
planning_factor,
supply_locator_id,
supply_subinventory,
auto_request_material,
wip_supply_type,
check_atp,
optional,
mutually_exclusive_options,
low_quantity,
high_quantity,
so_basis,
shipping_allowed,
include_on_ship_docs,
required_for_revenue,
required_to_ship
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_seq_id
and nvl(parent_bill_seq_id,bill_sequence_id) = bill_sequence_id
and from_object_revision_id = p_ver_seq_id;
SELECT component_sequence_id,
component_item_id,
item_num,
component_quantity,
component_item_revision_id,
parent_bill_seq_id,
pk1_value,
pk2_value,
pk3_value,
component_remarks,
change_notice,
quantity_related,
component_yield_factor,
enforce_int_requirements,
include_in_cost_rollup,
basis_type,
bom_item_type,
planning_factor,
supply_locator_id,
supply_subinventory,
auto_request_material,
wip_supply_type,
check_atp,
optional,
mutually_exclusive_options,
low_quantity,
high_quantity,
so_basis,
shipping_allowed,
include_on_ship_docs,
required_for_revenue,
required_to_ship
from BOM_COMPONENTS_B
where bill_sequence_id = p_bill_seq_id
and from_object_revision_id = p_ver_seq_id;
select item_catalog_group_id,
parent_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id;
l_updated Number;
l_updated := 0;
l_updated := 1;
SELECT bill_sequence_id,
assembly_type,
pk2_value,
effectivity_control,
alternate_bom_designator,
structure_type_id
INTO l_bill_seq_id,
l_assembly_type,
l_pk2_value,
l_effectivity_control,
l_alternate_bom_designator,
l_structure_type_id
FROM BOM_STRUCTURES_B
WHERE pk1_value = p_item_catalog_grp_id
and obj_name = 'EGO_CATALOG_GROUP';
SELECT bill_sequence_id,
assembly_type,
pk2_value,
effectivity_control,
alternate_bom_designator,
structure_type_id
into l_parent_bill_seq_id,
l_par_assembly_type,
l_par_pk2_value,
l_par_effectivity_control,
l_par_alternate_bom_designator,
l_par_structure_type_id
FROM BOM_STRUCTURES_B
WHERE pk1_value = l_parent_catalog_grp_id
and obj_name = 'EGO_CATALOG_GROUP';
l_updated := 1;
l_updated := 1;
l_updated := compare_uda_values(v_released_struct_comp(cntr).component_sequence_id,
v_draft_struct_comp(l_draft_index).component_sequence_id);
if l_updated <> 0 then
exit;
l_updated := 1;
return l_updated;
END Is_Structure_Updated;
l_updated Number;
l_updated := 0;
select data_level_id
into l_data_level_id
from ego_data_level_b
where data_level_name = 'COMPONENTS_LEVEL'
and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
and application_id = 702;
select 1
into l_updated
from dual
where exists(
((select
ATTR_GROUP_ID,
C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
from bom_components_ext_b
where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)
minus
(select
ATTR_GROUP_ID,
C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
from bom_components_ext_b
where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_released_comp_seq_id))
union
((select
ATTR_GROUP_ID,
C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
from bom_components_ext_b
where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_released_comp_seq_id)
minus
(select
ATTR_GROUP_ID,
C_EXT_ATTR1,C_EXT_ATTR2,C_EXT_ATTR3,C_EXT_ATTR4,C_EXT_ATTR5,C_EXT_ATTR6,C_EXT_ATTR7,C_EXT_ATTR8,C_EXT_ATTR9,C_EXT_ATTR10,
C_EXT_ATTR11,C_EXT_ATTR12,C_EXT_ATTR13,C_EXT_ATTR14,C_EXT_ATTR15,C_EXT_ATTR16,C_EXT_ATTR17,C_EXT_ATTR18,C_EXT_ATTR19,C_EXT_ATTR20,
C_EXT_ATTR21,C_EXT_ATTR22,C_EXT_ATTR23,C_EXT_ATTR24,C_EXT_ATTR25,C_EXT_ATTR26,C_EXT_ATTR27,C_EXT_ATTR28,C_EXT_ATTR29,C_EXT_ATTR30,
C_EXT_ATTR31,C_EXT_ATTR32,C_EXT_ATTR33,C_EXT_ATTR34,C_EXT_ATTR35,C_EXT_ATTR36,C_EXT_ATTR37,C_EXT_ATTR38,C_EXT_ATTR39,C_EXT_ATTR40,
N_EXT_ATTR1,N_EXT_ATTR2,N_EXT_ATTR3,N_EXT_ATTR4,N_EXT_ATTR5,N_EXT_ATTR6,N_EXT_ATTR7,N_EXT_ATTR8,N_EXT_ATTR9,N_EXT_ATTR10,
N_EXT_ATTR11,N_EXT_ATTR12,N_EXT_ATTR13,N_EXT_ATTR14,N_EXT_ATTR15,N_EXT_ATTR16,N_EXT_ATTR17,N_EXT_ATTR18,N_EXT_ATTR19,N_EXT_ATTR20,
D_EXT_ATTR1,D_EXT_ATTR2,D_EXT_ATTR3,D_EXT_ATTR4,D_EXT_ATTR5,D_EXT_ATTR6,D_EXT_ATTR7,D_EXT_ATTR8,D_EXT_ATTR9,D_EXT_ATTR10,
UOM_EXT_ATTR1,UOM_EXT_ATTR2,UOM_EXT_ATTR3,UOM_EXT_ATTR4,UOM_EXT_ATTR5,UOM_EXT_ATTR6,UOM_EXT_ATTR7,UOM_EXT_ATTR8,UOM_EXT_ATTR9,UOM_EXT_ATTR10,
UOM_EXT_ATTR11,UOM_EXT_ATTR12,UOM_EXT_ATTR13,UOM_EXT_ATTR14,UOM_EXT_ATTR15,UOM_EXT_ATTR16,UOM_EXT_ATTR17,UOM_EXT_ATTR18,UOM_EXT_ATTR19,UOM_EXT_ATTR20
from bom_components_ext_b
where data_level_id = l_data_level_id and COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)));
return l_updated;
l_updated Number;
l_updated := 0;
select 1
into l_updated
from dual
where exists(
((select
component_item_id,component_quantity,component_item_revision_id,
component_remarks,change_notice,quantity_related,
component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
basis_type,bom_item_type,planning_factor,
supply_locator_id,supply_subinventory,auto_request_material,
wip_supply_type,check_atp,optional,
mutually_exclusive_options,low_quantity,high_quantity,
so_basis,shipping_allowed,include_on_ship_docs,
required_for_revenue,required_to_ship
from bom_components_b
where COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)
minus
(select
component_item_id,component_quantity,component_item_revision_id,
component_remarks,change_notice,quantity_related,
component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
basis_type,bom_item_type,planning_factor,
supply_locator_id,supply_subinventory,auto_request_material,
wip_supply_type,check_atp,optional,
mutually_exclusive_options,low_quantity,high_quantity,
so_basis,shipping_allowed,include_on_ship_docs,
required_for_revenue,required_to_ship
from bom_components_b
where COMPONENT_SEQUENCE_ID = p_released_comp_seq_id))
union
((select
component_item_id,component_quantity,component_item_revision_id,
component_remarks,change_notice,quantity_related,
component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
basis_type,bom_item_type,planning_factor,
supply_locator_id,supply_subinventory,auto_request_material,
wip_supply_type,check_atp,optional,
mutually_exclusive_options,low_quantity,high_quantity,
so_basis,shipping_allowed,include_on_ship_docs,
required_for_revenue,required_to_ship
from bom_components_b
where COMPONENT_SEQUENCE_ID = p_released_comp_seq_id)
minus
(select
component_item_id,component_quantity,component_item_revision_id,
component_remarks,change_notice,quantity_related,
component_yield_factor,enforce_int_requirements,include_in_cost_rollup,
basis_type,bom_item_type,planning_factor,
supply_locator_id,supply_subinventory,auto_request_material,
wip_supply_type,check_atp,optional,
mutually_exclusive_options,low_quantity,high_quantity,
so_basis,shipping_allowed,include_on_ship_docs,
required_for_revenue,required_to_ship
from bom_components_b
where COMPONENT_SEQUENCE_ID = p_draft_comp_seq_id)));
l_updated := 0;
if l_updated = 0 then
l_updated := compare_uda_values(p_draft_comp_seq_id,
p_released_comp_seq_id);
return l_updated;
select item_catalog_group_id
INTO l_catalog_group_id
from mtl_system_items_b
where inventory_item_id = p_inventory_item_id
and organization_id = p_organzation_id;
select revision_id,
revision,
effectivity_date,
(select nvl( min(b.effectivity_date)-(1/86400),to_date('9999/12/31 00:00:00',date_fmt)) end_date
from mtl_item_revisions_b b
where b.inventory_item_id = a.inventory_item_id
and b.organization_id = a.organization_id
and b.effectivity_date > a.effectivity_date) end_date
from mtl_item_revisions_b a
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id
order by effectivity_date;
v_item_revisions_tbl.delete;
v_rev_index.delete;
select * from bom_components_b
where bill_sequence_id = p_bill_seq_id;
SELECT count(1)
FROM bom_components_b bic
WHERE bill_sequence_id = p_bill_seq_id
AND component_sequence_id <> P_comp_seq_id
AND nvl(obj_name,'EGO_ITEM') = nvl(P_obj_type,'EGO_ITEM')
AND pk1_value = P_pk1_value
AND nvl(pk2_value,'-1') = nvl(P_pk2_value,'-1')
AND operation_seq_num = P_op_seq
AND ((P_obj_type IS NOT NULL AND
P_fromMinorRevisionId BETWEEN nvl(from_minor_revision_id,P_fromMinorRevisionId) AND nvl(to_minor_revision_id,P_fromMinorRevisionId))
OR (P_obj_type IS NULL AND P_toMinorRevCode IS NULL OR
P_toMinorRevCode >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(nvl(from_minor_revision_id,0)))
FROM mtl_item_revisions_b WHERE revision_id = FROM_OBJECT_REVISION_ID)
AND (to_object_revision_id IS NULL OR
P_fromMinorRevCode <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(nvl(to_minor_revision_id,9999999999999999)))
FROM mtl_item_revisions_b WHERE revision_id = TO_OBJECT_REVISION_ID))))
AND (change_notice is not null
and( implementation_date is not null and P_changeNotice is null OR
(implementation_date is null and change_notice = P_changeNotice
AND EXISTS( SELECT 1 from eng_revised_items eri
where eri.revised_item_sequence_id = bic.revised_item_sequence_id
and eri.bill_Sequence_id = bic.bill_Sequence_id )))
OR (change_notice is null and P_changeNotice is null))
AND (( EXISTS (SELECT null FROM mtl_item_revisions_b
WHERE inventory_item_id = P_endItemId AND organization_id = P_endItemOrgId
AND revision_id = from_end_item_rev_id)
AND ( P_toEndItemMinorRevCode IS NULL OR P_toEndItemMinorRevCode >= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(from_end_item_minor_rev_id))
FROM mtl_item_revisions_b WHERE revision_id = from_end_item_rev_id))
AND ( to_end_item_rev_id IS NULL OR P_fromEndItemMinorRevCode <= (SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(nvl(to_end_item_minor_rev_id,9999999999999999)))
FROM mtl_item_revisions_b WHERE revision_id = to_end_item_rev_id))));
SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),to_char(comp.FROM_END_ITEM_MINOR_REV_ID)),
inventory_item_id,
organization_id
into fromEndItemMinorRevCode,
endItemId,
endItemOrgId
FROM mtl_item_revisions_b
WHERE revision_id = comp.FROM_END_ITEM_REV_ID;
SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),nvl(to_char(comp.TO_END_ITEM_MINOR_REV_ID),'9999999999999999L')),
inventory_item_id,
organization_id
into toEndItemMinorRevCode,
endItemId,
endItemOrgId
FROM mtl_item_revisions_b
WHERE revision_id = comp.TO_END_ITEM_REV_ID;
SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),nvl(to_char(comp.FROM_MINOR_REVISION_ID),'9999999999999999L')),
inventory_item_id,
organization_id
into fromMinorRevCode,
endItemId,
endItemOrgId
FROM mtl_item_revisions_b
WHERE revision_id = comp.FROM_OBJECT_REVISION_ID;
SELECT concat(to_char(effectivity_date,'yyyymmddhh24miss'),nvl(to_char(comp.TO_MINOR_REVISION_ID),'9999999999999999L')),
inventory_item_id,
organization_id
into toMinorRevCode,
endItemId,
endItemOrgId
FROM mtl_item_revisions_b
WHERE revision_id = comp.TO_OBJECT_REVISION_ID;
select concatenated_segments into G_COMP_ITEM_NAME
from mtl_system_items_kfv where inventory_item_id = comp.PK1_VALUE
and organization_id = comp.pk2_value;
select revision_label into G_EFF_FROM
from mtl_item_revisions where inventory_item_id = G_INV_ITEM_ID
and organization_id = comp.pk2_value and revision_id = comp.FROM_END_ITEM_REV_ID;
select icc_str_comp.component_sequence_id,icc_str_comp.component_item_id,icc_str_comp.bill_sequence_id,0 is_component_present,
msiv.bom_item_type,msiv.eam_item_type,msiv.base_item_id,msiv.replenish_to_order_flag,msiv.pick_components_flag,
icc_str_comp.component_yield_factor,icc_str_comp.basis_type,msiv.ato_forecast_control,icc_str_comp.planning_factor,
icc_str_comp.optional,msiv.atp_components_flag,icc_str_comp.component_quantity,
icc_str_comp.required_to_ship,icc_str_comp.required_for_revenue
from (
select icc_str_components.component_sequence_id,
icc_str_components.component_item_id,
icc_structure.pk1_value,
icc_str_components.from_object_revision_id,
icc_str_components.bill_sequence_id,
icc_str_components.component_yield_factor,
icc_str_components.basis_type,
icc_str_components.planning_factor,
icc_str_components.optional,
icc_str_components.component_quantity,
icc_str_components.required_to_ship,
icc_str_components.required_for_revenue
from (select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id) icc,
bom_structures_b icc_structure,
bom_components_b icc_str_components
where icc_structure.pk1_value = icc.item_catalog_group_id
and icc_structure.pk2_value = p_organization_id
and icc_structure.obj_name = 'EGO_CATALOG_GROUP'
and icc_structure.structure_type_id = p_structure_type_id
and icc_structure.alternate_bom_designator = p_alt_desg
and icc_structure.assembly_type = 2
and icc_structure.effectivity_control = 4
and icc_structure.bill_sequence_id = icc_str_components.bill_sequence_id
and nvl(icc_str_components.parent_bill_seq_id,icc_str_components.bill_sequence_id) = icc_structure.bill_sequence_id) icc_str_comp,
mtl_system_items_vl msiv
where icc_str_comp.from_object_revision_id = EGO_ICC_STRUCTURE_PVT.Get_Effective_Version(icc_str_comp.pk1_value,p_rev_date)
and msiv.inventory_item_id = icc_str_comp.component_item_id
and msiv.organization_id = p_organization_id;
v_insert_comp_tbl component_rec;
select bill_sequence_id
into l_catalog_bill_sequence_id
from bom_structures_b
where pk1_value = to_char(l_catalog_group_id)
and pk2_value = to_char(p_organization_id)
and obj_name = 'EGO_CATALOG_GROUP'
and assembly_type = 2
and effectivity_control = 4
and structure_type_id = p_structure_type_id
and alternate_bom_designator = p_alt_desg;
select null
into l_catalog_bill_sequence_id
from bom_structures_b
where pk1_value in (select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = l_catalog_group_id)
and pk2_value = to_char(p_organization_id)
and obj_name = 'EGO_CATALOG_GROUP'
and assembly_type = 2
and effectivity_control = 4
and structure_type_id = p_structure_type_id
and alternate_bom_designator = p_alt_desg
and rownum = 1;
select bom_item_type,eam_item_type,base_item_id,replenish_to_order_flag,pick_components_flag,atp_components_flag
into l_assy_bom_item_type,l_assy_eam_item_type,l_assy_base_item_id,l_assy_replenish_to_order_flag,l_assy_pick_components_flag,
l_assy_atp_comp_flag
from MTL_SYSTEM_ITEMS_VL
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
select concatenated_segments into G_ASSY_ITEM_NAME
from mtl_system_items_kfv where inventory_item_id = p_inventory_item_id and organization_id = p_organization_id;
v_icc_comp_tbl.DELETE;
select concatenated_segments into G_COMP_ITEM_NAME
from mtl_system_items_kfv where inventory_item_id = component.component_item_id
and organization_id = p_organization_id;
If(rev_count <> 1 and v_insert_comp_tbl.count >= 1) then
For insert_comp_tbl_count in 1..v_insert_comp_tbl.count loop
l_item_comp_id := v_insert_comp_tbl(insert_comp_tbl_count).component_item_id;
if EGO_ICC_STRUCTURE_PVT.Compare_components(v_insert_comp_tbl(insert_comp_tbl_count).component_sequence_id,
v_icc_comp_tbl(l_item_comp_id).component_sequence_id) = 0 then
--Components with same attributes
l_stmt_no := 260;
If v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id is null then
v_icc_comp_tbl(l_item_comp_id).is_component_present := 1;
If v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id is null then
v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id := l_prev_rev_id;
If v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id is null then
v_insert_comp_tbl(insert_comp_tbl_count).to_revision_id := l_prev_rev_id;
v_insert_comp_tbl(l_ins_cntr).bill_sequence_id := v_icc_comp_tbl(l_icc_index).bill_sequence_id;
v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_icc_comp_tbl(l_icc_index).component_sequence_id;
v_insert_comp_tbl(l_ins_cntr).component_item_id := v_icc_comp_tbl(l_icc_index).component_item_id;
v_insert_comp_tbl(l_ins_cntr).from_revision_id := l_current_rev_id;
v_insert_comp_tbl(l_ins_cntr).to_revision_id := null;
select data_level_id
into l_data_level_id
from ego_data_level_b
where data_level_name = 'COMPONENTS_LEVEL'
and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
and application_id = 702;
for cntr in 1..v_insert_comp_tbl.count loop
l_item_seq_incr := l_item_seq_incr+to_number(l_item_seq_incr_prof);
select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
into l_new_component_seq_id
from dual;
Insert into BOM_COMPONENTS_B
(OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ITEM_NUM,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
WIP_SUPPLY_TYPE,
PICK_COMPONENTS,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
BOM_ITEM_TYPE,
ENFORCE_INT_REQUIREMENTS,
COMPONENT_ITEM_REVISION_ID,
PARENT_BILL_SEQ_ID,
AUTO_REQUEST_MATERIAL,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
FROM_END_ITEM_REV_ID,
TO_END_ITEM_REV_ID,
FROM_OBJECT_REVISION_ID,
TO_OBJECT_REVISION_ID,
INHERIT_FLAG,
COMPONENT_REMARKS,
CHANGE_NOTICE,
BASIS_TYPE,
LOW_QUANTITY,
HIGH_QUANTITY)
select
BCB.OPERATION_SEQ_NUM,
BCB.COMPONENT_ITEM_ID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_item_seq_incr,
BCB.COMPONENT_QUANTITY,
BCB.COMPONENT_YIELD_FACTOR,
BCB.EFFECTIVITY_DATE,
BCB.IMPLEMENTATION_DATE,
BCB.PLANNING_FACTOR,
BCB.QUANTITY_RELATED,
BCB.SO_BASIS,
BCB.OPTIONAL,
BCB.MUTUALLY_EXCLUSIVE_OPTIONS,
BCB.INCLUDE_IN_COST_ROLLUP,
BCB.CHECK_ATP,
BCB.SHIPPING_ALLOWED,
BCB.REQUIRED_TO_SHIP,
BCB.REQUIRED_FOR_REVENUE,
BCB.INCLUDE_ON_SHIP_DOCS,
l_new_component_seq_id,
p_bill_seq_id,
BCB.WIP_SUPPLY_TYPE,
BCB.PICK_COMPONENTS,
BCB.SUPPLY_SUBINVENTORY,
BCB.SUPPLY_LOCATOR_ID,
BCB.BOM_ITEM_TYPE,
BCB.ENFORCE_INT_REQUIREMENTS,
BCB.COMPONENT_ITEM_REVISION_ID,
null,
BCB.AUTO_REQUEST_MATERIAL,
BCB.PK1_VALUE,
BCB.PK2_VALUE,
BCB.PK3_VALUE,
BCB.PK4_VALUE,
BCB.PK5_VALUE,
v_insert_comp_tbl(cntr).from_revision_id,
v_insert_comp_tbl(cntr).to_revision_id,
v_insert_comp_tbl(cntr).from_revision_id,
v_insert_comp_tbl(cntr).to_revision_id,
1,
BCB.COMPONENT_REMARKS,
BCB.CHANGE_NOTICE,
BCB.BASIS_TYPE,
BCB.LOW_QUANTITY,
BCB.HIGH_QUANTITY
from BOM_COMPONENTS_B BCB
where BCB.COMPONENT_SEQUENCE_ID = v_insert_comp_tbl(cntr).component_sequence_id;
to_char(v_insert_comp_tbl(cntr).component_sequence_id)),
EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
to_char(v_insert_comp_tbl(cntr).bill_sequence_id)));
select structure_type_id,alternate_bom_designator,bill_sequence_id
from bom_structures_b
where
assembly_item_id = p_inventory_item_id
and organization_id = p_organization_id
and obj_name is null
and assembly_type = 2
and effectivity_control = 4
and source_bill_sequence_id = bill_sequence_id;
select icc_str_comp.component_sequence_id,icc_str_comp.component_item_id,icc_str_comp.bill_sequence_id,0 is_component_present,
msiv.bom_item_type,msiv.eam_item_type,msiv.base_item_id,msiv.replenish_to_order_flag,msiv.pick_components_flag,
icc_str_comp.component_yield_factor,icc_str_comp.basis_type,msiv.ato_forecast_control,icc_str_comp.planning_factor,
icc_str_comp.optional,msiv.atp_components_flag,icc_str_comp.component_quantity,
icc_str_comp.required_to_ship,icc_str_comp.required_for_revenue
from (
select icc_str_components.component_sequence_id,
icc_str_components.component_item_id,
icc_structure.pk1_value,
icc_str_components.from_object_revision_id,
icc_structure.bill_sequence_id,
icc_str_components.component_yield_factor,
icc_str_components.basis_type,
icc_str_components.planning_factor,
icc_str_components.optional,
icc_str_components.component_quantity,
icc_str_components.required_to_ship,
icc_str_components.required_for_revenue
from (select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id) icc,
bom_structures_b icc_structure,
bom_components_b icc_str_components
where icc_structure.pk1_value = icc.item_catalog_group_id
and icc_structure.pk2_value = p_organization_id
and icc_structure.obj_name = 'EGO_CATALOG_GROUP'
and icc_structure.structure_type_id = p_structure_type_id
and icc_structure.alternate_bom_designator = p_alt_desg
and icc_structure.assembly_type = 2
and icc_structure.effectivity_control = 4
and icc_structure.bill_sequence_id = icc_str_components.bill_sequence_id
and nvl(icc_str_components.parent_bill_seq_id,icc_str_components.bill_sequence_id) = icc_structure.bill_sequence_id) icc_str_comp,
mtl_system_items_vl msiv
where icc_str_comp.from_object_revision_id = EGO_ICC_STRUCTURE_PVT.Get_Effective_Version(icc_str_comp.pk1_value,p_rev_date)
and msiv.inventory_item_id = icc_str_comp.component_item_id
and msiv.organization_id = p_organization_id;
v_insert_comp_tbl component_rec;
v_update_comp_tbl component_rec;
v_delete_comp_tbl component_rec;
select bill_sequence_id,structure_type_id,alternate_bom_designator
into l_catalog_bill_sequence_id,l_catalog_str_type_id,G_ALTCODE
from bom_structures_b
where pk1_value = to_char(l_catalog_group_id)
and pk2_value = to_char(p_organization_id)
and obj_name = 'EGO_CATALOG_GROUP'
and assembly_type = 2
and effectivity_control = 4
and structure_type_id = structure.structure_type_id
and alternate_bom_designator = structure.alternate_bom_designator;
select structure_type_id,alternate_bom_designator
into l_catalog_str_type_id,G_ALTCODE
from bom_structures_b
where pk1_value in (select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = l_catalog_group_id)
and pk2_value = to_char(p_organization_id)
and obj_name = 'EGO_CATALOG_GROUP'
and assembly_type = 2
and effectivity_control = 4
and structure_type_id = structure.structure_type_id
and alternate_bom_designator = structure.alternate_bom_designator
and rownum = 1;
select max(ITEM_NUM) into l_item_seq_incr
from bom_components_b where bill_sequence_id = l_item_bill_sequence_id;
select bom_item_type,eam_item_type,base_item_id,replenish_to_order_flag,pick_components_flag,atp_components_flag
into l_assy_bom_item_type,l_assy_eam_item_type,l_assy_base_item_id,l_assy_replenish_to_order_flag,l_assy_pick_components_flag,
l_assy_atp_comp_flag
from MTL_SYSTEM_ITEMS_VL
where inventory_item_id = p_inventory_item_id
and organization_id = p_organization_id;
select concatenated_segments into G_ASSY_ITEM_NAME
from mtl_system_items_kfv where inventory_item_id = p_inventory_item_id and organization_id = p_organization_id;
select concatenated_segments into G_COMP_ITEM_NAME
from mtl_system_items_kfv where inventory_item_id = component.component_item_id
and organization_id = p_organization_id;
select bcb.component_sequence_id,bcb.component_item_id,bcb.from_end_item_rev_id,bcb.to_end_item_rev_id,bcb.inherit_flag
bulk collect into v_item_comp_tbl
from bom_components_b bcb
where bcb.bill_sequence_id = l_item_bill_sequence_id
and bcb.inherit_flag = 1
and (p_rev_date between EGO_ICC_STRUCTURE_PVT.get_revision_start_date(bcb.from_end_item_rev_id) and
EGO_ICC_STRUCTURE_PVT.get_revision_end_date(nvl(bcb.to_end_item_rev_id,-1)));
But we need to additionally insert a new row with modified attributes. * /
/* Say we are updating revision B's date. If loop handles below cases :
A - null, A - B, A - C Revision A can be any of the earlier revisions. */
-- Start: Process Components efeective from previous revisions
l_stmt_no := 270;
v_update_comp_tbl(l_upd_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
v_update_comp_tbl(l_upd_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
v_update_comp_tbl(l_upd_cntr).from_revision_id := v_item_comp_tbl(cntr).from_end_item_rev_id;
v_update_comp_tbl(l_upd_cntr).to_revision_id := l_prev_rev_id;
/* In case of A - C,A - null and next rev exists, Insert with from_rev_id as l_next_rev_id. For eg : C - C,C - null */
l_stmt_no := 290;
v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
v_insert_comp_tbl(l_ins_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
v_insert_comp_tbl(l_ins_cntr).from_revision_id := l_next_rev_id;
v_insert_comp_tbl(l_ins_cntr).to_revision_id := null;
v_insert_comp_tbl(l_ins_cntr).bill_seq_id := l_item_bill_sequence_id;
/* If to_rev_id is not null and greater than current rev, then insert Eg : C - null or C - D */
l_stmt_no := 300;
v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
v_insert_comp_tbl(l_ins_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
v_insert_comp_tbl(l_ins_cntr).from_revision_id := l_next_rev_id;
v_insert_comp_tbl(l_ins_cntr).to_revision_id := v_item_comp_tbl(cntr).to_end_item_rev_id;
v_insert_comp_tbl(l_ins_cntr).bill_seq_id := l_item_bill_sequence_id;
Delete_Comp_User_Attrs(v_item_comp_tbl(cntr).component_sequence_id);
delete from bom_components_b
where component_sequence_id = v_item_comp_tbl(cntr).component_sequence_id;
Inserting a row with from_rev_id as l_next_rev_id. For eg : C - C or C - null */
l_stmt_no := 330;
v_update_comp_tbl(l_upd_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
v_update_comp_tbl(l_upd_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
v_update_comp_tbl(l_upd_cntr).from_revision_id := l_next_rev_id;
v_update_comp_tbl(l_upd_cntr).to_revision_id := v_item_comp_tbl(cntr).to_end_item_rev_id;
/* Delete if component and its attributes which exists as B - null */
l_stmt_no := 340;
Delete_Comp_User_Attrs(v_item_comp_tbl(cntr).component_sequence_id);
delete from bom_components_b
where component_sequence_id = v_item_comp_tbl(cntr).component_sequence_id;
/* If to_rev_id is not null then update from_rev_id. For eg : C - null or C - D */
l_stmt_no := 350;
v_update_comp_tbl(l_upd_cntr).component_sequence_id := v_item_comp_tbl(cntr).component_sequence_id;
v_update_comp_tbl(l_upd_cntr).component_item_id := v_item_comp_tbl(cntr).component_item_id;
v_update_comp_tbl(l_upd_cntr).from_revision_id := l_next_rev_id;
v_update_comp_tbl(l_upd_cntr).to_revision_id := v_item_comp_tbl(cntr).to_end_item_rev_id;
v_insert_comp_tbl(l_ins_cntr).component_sequence_id := v_icc_comp_tbl(l_icc_index).component_sequence_id;
v_insert_comp_tbl(l_ins_cntr).component_item_id := v_icc_comp_tbl(l_icc_index).component_item_id;
v_insert_comp_tbl(l_ins_cntr).from_revision_id := p_revision_id;
v_insert_comp_tbl(l_ins_cntr).bill_seq_id := v_icc_comp_tbl(l_icc_index).bill_sequence_id;
v_insert_comp_tbl(l_ins_cntr).to_revision_id := l_next_rev_id;
v_insert_comp_tbl(l_ins_cntr).to_revision_id := p_revision_id;
select data_level_id
into l_data_level_id
from ego_data_level_b
where data_level_name = 'COMPONENTS_LEVEL'
and attr_group_type = 'BOM_COMPONENTMGMT_GROUP'
and application_id = 702;
for cntr in 1..v_insert_comp_tbl.count loop
l_stmt_no := 410;
select BOM_INVENTORY_COMPONENTS_S.NEXTVAL
into l_new_component_seq_id
from dual;
Insert into BOM_COMPONENTS_B
(OPERATION_SEQ_NUM,
COMPONENT_ITEM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ITEM_NUM,
COMPONENT_QUANTITY,
COMPONENT_YIELD_FACTOR,
EFFECTIVITY_DATE,
IMPLEMENTATION_DATE,
PLANNING_FACTOR,
QUANTITY_RELATED,
SO_BASIS,
OPTIONAL,
MUTUALLY_EXCLUSIVE_OPTIONS,
INCLUDE_IN_COST_ROLLUP,
CHECK_ATP,
SHIPPING_ALLOWED,
REQUIRED_TO_SHIP,
REQUIRED_FOR_REVENUE,
INCLUDE_ON_SHIP_DOCS,
COMPONENT_SEQUENCE_ID,
BILL_SEQUENCE_ID,
WIP_SUPPLY_TYPE,
PICK_COMPONENTS,
SUPPLY_SUBINVENTORY,
SUPPLY_LOCATOR_ID,
BOM_ITEM_TYPE,
ENFORCE_INT_REQUIREMENTS,
COMPONENT_ITEM_REVISION_ID,
PARENT_BILL_SEQ_ID,
AUTO_REQUEST_MATERIAL,
PK1_VALUE,
PK2_VALUE,
PK3_VALUE,
PK4_VALUE,
PK5_VALUE,
FROM_END_ITEM_REV_ID,
TO_END_ITEM_REV_ID,
FROM_OBJECT_REVISION_ID,
TO_OBJECT_REVISION_ID,
INHERIT_FLAG,
COMPONENT_REMARKS,
CHANGE_NOTICE,
BASIS_TYPE,
LOW_QUANTITY,
HIGH_QUANTITY)
select
BCB.OPERATION_SEQ_NUM,
BCB.COMPONENT_ITEM_ID,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
l_item_seq_incr,
BCB.COMPONENT_QUANTITY,
BCB.COMPONENT_YIELD_FACTOR,
BCB.EFFECTIVITY_DATE,
BCB.IMPLEMENTATION_DATE,
BCB.PLANNING_FACTOR,
BCB.QUANTITY_RELATED,
BCB.SO_BASIS,
BCB.OPTIONAL,
BCB.MUTUALLY_EXCLUSIVE_OPTIONS,
BCB.INCLUDE_IN_COST_ROLLUP,
BCB.CHECK_ATP,
BCB.SHIPPING_ALLOWED,
BCB.REQUIRED_TO_SHIP,
BCB.REQUIRED_FOR_REVENUE,
BCB.INCLUDE_ON_SHIP_DOCS,
l_new_component_seq_id,
l_item_bill_sequence_id,
BCB.WIP_SUPPLY_TYPE,
BCB.PICK_COMPONENTS,
BCB.SUPPLY_SUBINVENTORY,
BCB.SUPPLY_LOCATOR_ID,
BCB.BOM_ITEM_TYPE,
BCB.ENFORCE_INT_REQUIREMENTS,
BCB.COMPONENT_ITEM_REVISION_ID,
null,
BCB.AUTO_REQUEST_MATERIAL,
BCB.PK1_VALUE,
BCB.PK2_VALUE,
BCB.PK3_VALUE,
BCB.PK4_VALUE,
BCB.PK5_VALUE,
v_insert_comp_tbl(cntr).from_revision_id,
v_insert_comp_tbl(cntr).to_revision_id,
v_insert_comp_tbl(cntr).from_revision_id,
v_insert_comp_tbl(cntr).to_revision_id,
1,
BCB.COMPONENT_REMARKS,
BCB.CHANGE_NOTICE,
BCB.BASIS_TYPE,
BCB.LOW_QUANTITY,
BCB.HIGH_QUANTITY
from BOM_COMPONENTS_B BCB
where
BCB.COMPONENT_SEQUENCE_ID = v_insert_comp_tbl(cntr).component_sequence_id;
to_char(v_insert_comp_tbl(cntr).component_sequence_id)),
EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' ,
to_char(v_insert_comp_tbl(cntr).bill_seq_id)));
/* forall upd_index in v_update_comp_tbl.first..v_update_comp_tbl.last
update bom_components_b set
from_end_item_rev_id = v_update_comp_tbl(upd_index).from_revision_id,
to_end_item_rev_id = v_update_comp_tbl(upd_index).to_revision_id,
from_object_revision_id = v_update_comp_tbl(upd_index).from_revision_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where
component_sequence_id = v_update_comp_tbl(upd_index).component_sequence_id; */
if v_update_comp_tbl.exists(v_update_comp_tbl.first) then
for upd_index in v_update_comp_tbl.first..v_update_comp_tbl.last
loop
update bom_components_b
set
from_end_item_rev_id = v_update_comp_tbl(upd_index).from_revision_id,
to_end_item_rev_id = v_update_comp_tbl(upd_index).to_revision_id,
from_object_revision_id = v_update_comp_tbl(upd_index).from_revision_id,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where
component_sequence_id = v_update_comp_tbl(upd_index).component_sequence_id;
select effectivity_date
from mtl_item_revisions_b
where inventory_item_id = p_inv_item_id
and organization_id = p_organization_id;
select 1
into l_return_status
from dual
where exists (select 1
from (select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id) icc,
bom_structures_b icc_structure,
bom_components_b icc_str_components
where icc_structure.pk1_value = icc.item_catalog_group_id
and icc_structure.pk2_value = p_organization_id
and icc_structure.obj_name = 'EGO_CATALOG_GROUP'
and icc_structure.structure_type_id = p_structure_type_id
and icc_structure.alternate_bom_designator = p_alt_desig
and icc_structure.assembly_type = 2
and icc_structure.effectivity_control = 4
and icc_structure.bill_sequence_id = icc_str_components.bill_sequence_id
and nvl(icc_str_components.parent_bill_seq_id,icc_str_components.bill_sequence_id) = icc_structure.bill_sequence_id
and icc_str_components.from_object_revision_id = EGO_ICC_STRUCTURE_PVT.Get_Effective_Version(icc_structure.pk1_value,
revision.effectivity_date)
and rownum = 1);
select structures.bill_sequence_id
from ( select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior parent_catalog_group_id = item_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id ) icc,
BOM_STRUCTURES_B structures
where structures.pk1_value = icc.item_catalog_group_id
and structures.obj_name = 'EGO_CATALOG_GROUP'
and rownum = 1;
select 1
into l_create_header
from dual
where exists (select 1
from EGO_MTL_CATALOG_GRP_VERS_B
where item_catalog_group_id = p_item_catalog_grp_id)
and not exists (select 1
from BOM_STRUCTURES_B
where pk1_value = p_item_catalog_grp_id
and obj_name = 'EGO_CATALOG_GROUP');
select bom_inventory_components_s.nextval
into l_bill_sequence_id
from dual;
insert into BOM_STRUCTURES_B
(BILL_SEQUENCE_ID,
SOURCE_BILL_SEQUENCE_ID,
COMMON_BILL_SEQUENCE_ID,
ORGANIZATION_ID,
ALTERNATE_BOM_DESIGNATOR,
ASSEMBLY_TYPE,
STRUCTURE_TYPE_ID,
EFFECTIVITY_CONTROL,
IS_PREFERRED,
OBJ_NAME,
PK1_VALUE,
PK2_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN)
select
l_bill_sequence_id,
l_bill_sequence_id,
l_bill_sequence_id,
ORGANIZATION_ID,
ALTERNATE_BOM_DESIGNATOR,
ASSEMBLY_TYPE,
STRUCTURE_TYPE_ID,
EFFECTIVITY_CONTROL,
IS_PREFERRED,
OBJ_NAME,
p_item_catalog_grp_id,
PK2_VALUE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
from BOM_STRUCTURES_B
where BILL_SEQUENCE_ID = icc_structure.bill_sequence_id ;
select 1
into l_updatable
from dual
where not exists (select 1
from BOM_STRUCTURES_B bsb,
(select item_catalog_group_id
from mtl_item_catalog_groups_b
connect by prior item_catalog_group_id = parent_catalog_group_id
start with item_catalog_group_id = p_item_catalog_grp_id ) child_icc
where bsb.pk1_value = child_icc.item_catalog_group_id
and bsb.obj_name = 'EGO_CATALOG_GROUP'
and rownum = 1);
FND_MSG_PUB.Delete_Msg(null);
select 1
into l_dummy
from mtl_system_items
where inventory_item_id = p_component_item_id
and organization_id = p_organization_id
and rounding_control_type = 1;
SELECT stock_locator_control_code INTO l_org_locator_control
FROM mtl_parameters WHERE organization_id = p_organization_id;
SELECT location_control_code INTO l_item_locator_control
FROM mtl_system_items
WHERE organization_id = p_organization_id AND inventory_item_id = p_component_item_id;
SELECT LOCATOR_TYPE into l_sub_locator_control
FROM MTL_SECONDARY_INVENTORIES
WHERE ORGANIZATION_ID = p_organization_id and SECONDARY_INVENTORY_NAME = p_supply_subinventory;