[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT approval_status_type
INTO l_approval_status_type
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
UPDATE eng_engineering_changes
SET approval_status_type = 1,
approval_request_date = null,
approval_date = null,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE organization_id = p_organization_id
AND change_notice = p_change_notice;
UPDATE eng_revised_items
SET status_type = 1,
last_update_date = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE organization_id = p_organization_id
AND change_notice = p_change_notice
AND status_type = 4;
SELECT process_name
INTO l_process_name
FROM eng_change_type_processes
WHERE change_order_type_id = p_change_order_type_id
AND ( p_priority_code is NOT NULL
AND eng_change_priority_code = p_priority_code
AND organization_id = p_organization_id)
OR
(p_priority_code is NULL
AND eng_change_priority_code is NULL);
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT effectivity_control
FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
NOT IN ('CREATE', 'UPDATE', 'DELETE')
)
OR
( p_entity ='Bom_Rev' AND
NVL(p_transaction_type, FND_API.G_MISS_CHAR)
NOT IN ('CREATE', 'UPDATE')
)
THEN
IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
THEN
IF p_entity = 'Bom_Header'
THEN
Error_Handler.Add_Error_Token
( p_Message_Name => 'BOM_HEADER_TRANS_TYPE_INVALID'
, p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
, x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
);
SELECT alternate_bom_designator
FROM bom_structures_b
WHERE bill_sequence_id = p_bill_sequence_id;
SELECT display_name
FROM bom_structure_types_vl st
, bom_structures_b bsb
WHERE bsb.bill_sequence_id = l_bill_sequence_id
and bsb.structure_type_id = st.structure_type_id;
select meaning
from fnd_lookup_values
where LOOKUP_CODE = p_ITEM_TYPE
AND LOOKUP_TYPE = 'ITEM_TYPE'
AND LANGUAGE = USERENV('LANG');
SELECT component_reference_designator
FROM bom_reference_designators rd
, bom_components_b comp
WHERE comp.component_sequence_id = p_component_seq
AND rd.component_sequence_id = comp.component_sequence_id
AND ( (comp.implementation_date IS NULL AND
rd.change_notice = comp.change_notice
) OR
(comp.implementation_date is NOT NULL AND
(rd.acd_type IS NULL OR rd.acd_type <> 3)
)
)
order by 1 DESC;
SELECT concatenated_segments
INTO l_item_name
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT
revision_id
FROM
mtl_item_revisions_B mir,
bom_bill_of_materials bom
WHERE
mir.inventory_item_id = bom.assembly_item_id
AND mir.organization_id = bom.organization_id
AND bom.bill_sequence_id = p_bill_sequence_id
AND effectivity_date =
(SELECT max(mir1.effectivity_date)
FROM mtl_item_revisions_b mir1
WHERE mir1.inventory_item_id = mir.inventory_item_id
AND mir1.organization_id = mir.organization_id
AND mir1.effectivity_date <= p_effectivity_date
AND ROWNUM = 1);
SELECT
revision_id
FROM
mtl_item_revisions_B mir,
bom_bill_of_materials bom
WHERE
mir.inventory_item_id = p_component_item_id
AND mir.organization_id = bom.organization_id
AND bom.bill_sequence_id = p_bill_sequence_id
AND effectivity_date = (SELECT max(mir1.effectivity_date)
FROM mtl_item_revisions_b mir1
WHERE mir1.inventory_item_id = mir.inventory_item_id
AND mir1.organization_id = mir.organization_id
AND mir1.effectivity_date <= p_effectivity_date
AND ROWNUM = 1);
stmt2 := ' SELECT '||
' nvl(max(minor_revision_id),0) minor_revision_id '||
' FROM '||
' ego_minor_revisions emr, '||
' bom_bill_of_materials bom '||
' WHERE '||
' emr.pk1_value = to_char(bom.assembly_item_id) '||
' AND emr.pk2_value = to_char(bom.organization_id)' ||
' AND bom.bill_sequence_id = :bill_seq_id' ||
' and emr.pk3_value = :object_rev_id' ||
' and emr.obj_name = :object_type';
stmt1 := ' SELECT '||
'nvl(max(minor_revision_id),0) minor_revision_id ' ||
' FROM '||
' ego_minor_revisions emr, '||
' bom_bill_of_materials bom '||
' WHERE '||
' emr.pk1_value = to_char(:comp_item_id) ' ||
' AND emr.pk2_value = to_char(bom.organization_id) '||
' AND bom.bill_sequence_id = :bill_seq_id ' ||
' and emr.pk3_value = :comp_rev_id' ||
' and emr.obj_name = :object_type';
SELECT Structure_Type_Id
INTO l_GTIN_Id
FROM bom_structure_types_vl
WHERE Structure_Type_Name ='Packaging Hierarchy'
AND Structure_Type_Id = p_Structure_Type_Id;
SELECT Assembly_Item_id, Organization_Id into l_item_id, l_org_id
FROM Bom_Structures_b
WHERE Bill_Sequence_Id = p_bill_seq_id;
SELECT
ecp.policy_char_value INTO l_change_policy_char_val
FROM
MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP, Bom_Structures_b bsb
WHERE
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
AND ecp.policy_object_pk3_value = ITEM_DTLS.current_phase_id
AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
AND ecp.attribute_code = 'STRUCTURE_TYPE'
AND bsb.Structure_Type_id = ecp.attribute_number_value
AND bsb.Assembly_item_id = ITEM_DTLS.inventory_item_id
AND bsb.organization_id = ITEM_DTLS.organization_id
AND bsb.Bill_Sequence_id = p_bill_seq_id;
SELECT
ecp.policy_char_value INTO l_change_policy_char_val
FROM
MTL_SYSTEM_ITEMS ITEM_DTLS, ENG_CHANGE_POLICIES_V ECP
WHERE
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = ITEM_DTLS.lifecycle_id
AND ecp.policy_object_pk3_value = ITEM_DTLS.current_phase_id
AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
AND ecp.attribute_code = 'STRUCTURE_TYPE'
AND ecp.attribute_number_value = p_structure_type_id
AND ITEM_DTLS.inventory_item_id = p_item_id
AND ITEM_DTLS.organization_id = p_org_id;
SELECT EGO_DATA_SECURITY.CHECK_FUNCTION(1.0,viewPrivilegeName,'EGO_ITEM',inventoryItemId,
organizationId,null, null, null,partyId) INTO l_view_privilege
FROM DUAL;
SELECT EGO_DATA_SECURITY.CHECK_FUNCTION(1.0,editPrivilegeName,'EGO_ITEM',inventoryItemId,
organizationId,null, null, null,partyId) INTO l_edit_privilege
FROM DUAL;
SELECT ecp.policy_char_value
FROM
(SELECT NVL(mirb.lifecycle_id, msi.lifecycle_id) AS lifecycle_id,
NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
msi.item_catalog_group_id item_catalog_group_id,
msi.inventory_item_id, msi.organization_id , mirb.revision_id
FROM mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
WHERE msi.INVENTORY_ITEM_ID = p_item_id
AND msi.ORGANIZATION_ID = p_org_id
AND mirb.revision_id = nvl(p_rev_id,BOM_Revisions.Get_Item_Revision_Id_Fn('ALL','ALL',p_org_id, p_item_id, p_rev_date) )
AND (mirb.current_phase_id IS NOT NULL OR msi.current_phase_id IS NOT NULL)) ITEM_DTLS,
ENG_CHANGE_POLICIES_V ECP
WHERE
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = item_dtls.lifecycle_id
AND ecp.policy_object_pk3_value = item_dtls.phase_id
AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
AND ecp.attribute_code = 'STRUCTURE_TYPE'
AND ecp.attribute_number_value = p_structure_type_id;
SELECT revision
INTO l_rev_code
FROM mtl_item_revisions_b
WHERE revision_id = p_rev_id;
SELECT item_dtls.revision, ecp.policy_char_value
FROM
(SELECT NVL(mirb.lifecycle_id, msi.lifecycle_id) AS lifecycle_id,
NVL(mirb.current_phase_id , msi.current_phase_id) AS phase_id,
msi.item_catalog_group_id item_catalog_group_id,
msi.inventory_item_id, msi.organization_id , mirb.revision
FROM mtl_item_revisions_b mirb, MTL_SYSTEM_ITEMS_b msi
WHERE msi.INVENTORY_ITEM_ID = p_item_id
AND msi.ORGANIZATION_ID = p_org_id
AND mirb.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID
AND mirb.ORGANIZATION_ID = msi.ORGANIZATION_ID
AND mirb.revision >= nvl(p_start_rev,BOM_Revisions.Get_Item_Revision_Fn('ALL','ALL',p_org_id, p_item_id, p_effective_date) )
AND mirb.revision <= nvl(p_end_rev,decode(p_disable_date,null,mirb.revision,BOM_Revisions.Get_Item_Revision_Fn('ALL','ALL',p_org_id, p_item_id, p_disable_date)) )
AND (mirb.current_phase_id IS NOT NULL OR msi.current_phase_id IS NOT NULL)) ITEM_DTLS,
ENG_CHANGE_POLICIES_V ECP
WHERE
ecp.policy_object_pk1_value =
(SELECT TO_CHAR(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
WHERE EXISTS (SELECT olc.object_classification_code CatalogId
FROM EGO_OBJ_TYPE_LIFECYCLES olc
WHERE olc.object_id = (SELECT OBJECT_ID
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM')
AND olc.lifecycle_id = ITEM_DTLS.lifecycle_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
AND ROWNUM = 1
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = ITEM_DTLS.item_catalog_group_id)
AND ecp.policy_object_pk2_value = item_dtls.lifecycle_id
AND ecp.policy_object_pk3_value = item_dtls.phase_id
AND ecp.policy_object_name ='CATALOG_LIFECYCLE_PHASE'
AND ecp.attribute_object_name = 'EGO_CATALOG_GROUP'
AND ecp.attribute_code = 'STRUCTURE_TYPE'
AND ecp.attribute_number_value = p_structure_type_id
AND ecp.policy_char_value <> p_current_chg_pol
ORDER BY item_dtls.revision;
SELECT effectivity_date
INTO l_rev_date
FROM mtl_item_revisions_b
where revision_id = p_rev_id;
SELECT from_end_item_rev_id, to_end_item_rev_id, effectivity_date, disable_date
INTO l_start_rev_id, l_end_rev_id, l_effective_date, l_disable_date
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = p_component_sequence_id;
SELECT effectivity_date
INTO l_start_date
FROM mtl_item_revisions_b
WHERE revision_id = p_context_rev_id;
SELECT effectivity_control
INTO l_eff_ctrl
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = (SELECT bill_sequence_id
FROM bom_components_b
WHERE component_sequence_id = p_component_sequence_id
AND ROWNUM = 1);
SELECT from_end_item_rev_id, to_end_item_rev_id, effectivity_date, disable_date
INTO l_start_rev_id, l_end_rev_id, l_effective_date, l_disable_date
FROM BOM_COMPONENTS_B
WHERE component_sequence_id = p_component_sequence_id;
SELECT bill_sequence_id
from BOM_COMPONENTS_B
where component_sequence_id = p_comp_sequence_id;
Select structure_type_id
from BOM_STRUCTURES_B
where bill_sequence_id = p_bill_seq_id;
SELECT Bom_Inventory_Components_S.NEXTVAL INTO l_component_seqeunce_id FROM dual;
SELECT Effectivity_Control INTO l_effectivity_control FROM BOM_STRUCTURES_B
WHERE Bill_Sequence_Id = (SELECT Bill_Sequence_Id FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id);
SELECT Effectivity_Date INTO l_start_effectivity_date FROM MTL_ITEM_REVISIONS_B WHERE REVISION_ID = p_rev_id;
SELECT Disable_date INTO l_disable_date FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id;
SELECT SYSDATE INTO l_start_effectivity_date FROM dual;
SELECT To_End_Item_Rev_Id INTO l_end_rev_id FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id;
UPDATE bom_components_b
SET disable_date = p_disable_date
WHERE component_sequence_id = p_comp_seq_id;
UPDATE bom_components_b
SET to_end_item_rev_id = p_disable_rev_id
WHERE component_sequence_id = p_comp_seq_id;
INSERT INTO BOM_COMPONENTS_B
( SUPPLY_SUBINVENTORY
, OPERATION_LEAD_TIME_PERCENT
, REVISED_ITEM_SEQUENCE_ID
, COST_FACTOR
, REQUIRED_FOR_REVENUE
, HIGH_QUANTITY
, COMPONENT_SEQUENCE_ID
, PROGRAM_APPLICATION_ID
, WIP_SUPPLY_TYPE
, SUPPLY_LOCATOR_ID
, BOM_ITEM_TYPE
, 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
, COMPONENT_REMARKS
, EFFECTIVITY_DATE
, CHANGE_NOTICE
, IMPLEMENTATION_DATE
, DISABLE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PLANNING_FACTOR
, QUANTITY_RELATED
, SO_BASIS
, OPTIONAL
, MUTUALLY_EXCLUSIVE_OPTIONS
, INCLUDE_IN_COST_ROLLUP
, CHECK_ATP
, SHIPPING_ALLOWED
, REQUIRED_TO_SHIP
, INCLUDE_ON_SHIP_DOCS
, INCLUDE_ON_BILL_DOCS
, LOW_QUANTITY
, ACD_TYPE
, OLD_COMPONENT_SEQUENCE_ID
, BILL_SEQUENCE_ID
, REQUEST_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, PICK_COMPONENTS
, Original_System_Reference
, From_End_Item_Unit_Number
, To_End_Item_Unit_Number
, Eco_For_Production -- Added by MK
, Enforce_Int_Requirements
, Auto_Request_Material -- Added in 11.5.9 by ADEY
, Obj_Name -- Added by hgelli.
, pk1_value
, pk2_value
, Suggested_Vendor_Name --- Deepu
, Vendor_Id --- Deepu
, Unit_Price --- Deepu
, from_object_revision_id
, from_minor_revision_id
, from_end_item_rev_id
, to_end_item_rev_id
, component_item_revision_id
, basis_type
, common_component_sequence_id
)
SELECT comp_rec.SUPPLY_SUBINVENTORY
, comp_rec.OPERATION_LEAD_TIME_PERCENT
, comp_rec.REVISED_ITEM_SEQUENCE_ID
, comp_rec.COST_FACTOR
, comp_rec.REQUIRED_FOR_REVENUE
, comp_rec.HIGH_QUANTITY
, l_component_seqeunce_id
, comp_rec.PROGRAM_APPLICATION_ID
, comp_rec.WIP_SUPPLY_TYPE
, comp_rec.SUPPLY_LOCATOR_ID
, comp_rec.BOM_ITEM_TYPE
, comp_rec.OPERATION_SEQ_NUM
, comp_rec.COMPONENT_ITEM_ID
, sysdate
, comp_rec.LAST_UPDATED_BY
, sysdate
, comp_rec.CREATED_BY
, comp_rec.LAST_UPDATE_LOGIN
, comp_rec.ITEM_NUM
, comp_rec.COMPONENT_QUANTITY
, comp_rec.COMPONENT_YIELD_FACTOR
, comp_rec.COMPONENT_REMARKS
, l_start_effectivity_date
, comp_rec.CHANGE_NOTICE
, comp_rec.IMPLEMENTATION_DATE
, l_disable_date
, comp_rec.ATTRIBUTE_CATEGORY
, comp_rec.ATTRIBUTE1
, comp_rec.ATTRIBUTE2
, comp_rec.ATTRIBUTE3
, comp_rec.ATTRIBUTE4
, comp_rec.ATTRIBUTE5
, comp_rec.ATTRIBUTE6
, comp_rec.ATTRIBUTE7
, comp_rec.ATTRIBUTE8
, comp_rec.ATTRIBUTE9
, comp_rec.ATTRIBUTE10
, comp_rec.ATTRIBUTE11
, comp_rec.ATTRIBUTE12
, comp_rec.ATTRIBUTE13
, comp_rec.ATTRIBUTE14
, comp_rec.ATTRIBUTE15
, comp_rec.PLANNING_FACTOR
, comp_rec.QUANTITY_RELATED
, comp_rec.SO_BASIS
, comp_rec.OPTIONAL
, comp_rec.MUTUALLY_EXCLUSIVE_OPTIONS
, comp_rec.INCLUDE_IN_COST_ROLLUP
, comp_rec.CHECK_ATP
, comp_rec.SHIPPING_ALLOWED
, comp_rec.REQUIRED_TO_SHIP
, comp_rec.INCLUDE_ON_SHIP_DOCS
, comp_rec.INCLUDE_ON_BILL_DOCS
, comp_rec.LOW_QUANTITY
, comp_rec.ACD_TYPE
, comp_rec.OLD_COMPONENT_SEQUENCE_ID
, comp_rec.bill_sequence_id
, comp_rec.REQUEST_ID
, comp_rec.PROGRAM_ID
, comp_rec.PROGRAM_UPDATE_DATE
, comp_rec.PICK_COMPONENTS
, comp_rec.Original_System_Reference
, comp_rec.From_End_Item_Unit_Number
, comp_rec.To_End_Item_Unit_Number
, comp_rec.Eco_For_Production -- Added by MK
, comp_rec.Enforce_Int_Requirements
, comp_rec.Auto_Request_Material -- Added in 11.5.9 by ADEY
, comp_rec.Obj_Name -- Added by hgelli.
, comp_rec.pk1_value
, comp_rec.pk2_value
, comp_rec.Suggested_Vendor_Name --- Deepu
, comp_rec.Vendor_Id --- Deepu
, comp_rec.Unit_Price --- Deepu
, comp_rec.from_object_revision_id
, comp_rec.from_minor_revision_id
, l_start_rev_id
, l_end_rev_id
, comp_rec.component_item_revision_id
, comp_rec.basis_type
, comp_rec.common_component_sequence_id
FROM BOM_COMPONENTS_B comp_rec
WHERE comp_rec.component_sequence_id = p_comp_seq_id;
SELECT Count(1) INTO l_temp_num FROM bom_components_b WHERE component_sequence_id = l_component_seqeunce_id;
INSERT INTO BOM_REFERENCE_DESIGNATORS
( COMPONENT_REFERENCE_DESIGNATOR
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REF_DESIGNATOR_COMMENT
, CHANGE_NOTICE
, COMPONENT_SEQUENCE_ID
, ACD_TYPE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, Original_System_Reference
, common_component_sequence_id
)
SELECT
ref_desg.component_reference_designator
, SYSDATE
, ref_desg.LAST_UPDATED_BY
, SYSDATE
, ref_desg.CREATED_BY
, ref_desg.LAST_UPDATE_LOGIN
, DECODE( ref_desg.ref_designator_comment
, FND_API.G_MISS_CHAR
, NULL
, ref_desg.ref_designator_comment )
, ref_desg.change_notice
, l_component_seqeunce_id
, ref_desg.acd_type
, NULL /* Request Id */
, Bom_Globals.Get_Prog_AppId
, Bom_Globals.Get_Prog_Id
, SYSDATE
, ref_desg.attribute_category
, ref_desg.attribute1
, ref_desg.attribute2
, ref_desg.attribute3
, ref_desg.attribute4
, ref_desg.attribute5
, ref_desg.attribute6
, ref_desg.attribute7
, ref_desg.attribute8
, ref_desg.attribute9
, ref_desg.attribute10
, ref_desg.attribute11
, ref_desg.attribute12
, ref_desg.attribute13
, ref_desg.attribute14
, ref_desg.attribute15
, ref_desg.Original_System_Reference
, ref_desg.common_component_sequence_id
FROM BOM_REFERENCE_DESIGNATORS ref_desg
WHERE ref_desg.component_sequence_id = p_comp_seq_id;
INSERT INTO BOM_SUBSTITUTE_COMPONENTS
( SUBSTITUTE_COMPONENT_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, SUBSTITUTE_ITEM_QUANTITY
, COMPONENT_SEQUENCE_ID
, ACD_TYPE
, CHANGE_NOTICE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_UPDATE_DATE
, ATTRIBUTE_CATEGORY
, ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, PROGRAM_ID
, Original_System_Reference
, Enforce_Int_Requirements
, common_component_sequence_id
)
SELECT
sub_comp.substitute_component_id
, SYSDATE
, sub_comp.LAST_UPDATED_BY
, SYSDATE
, sub_comp.CREATED_BY
, sub_comp.LAST_UPDATE_LOGIN
, sub_comp.substitute_item_quantity
, l_component_seqeunce_id
, sub_comp.acd_type
, sub_comp.Change_Notice
, NULL /* Request Id */
, Bom_Globals.Get_Prog_AppId
, SYSDATE
, sub_comp.attribute_category
, sub_comp.attribute1
, sub_comp.attribute2
, sub_comp.attribute3
, sub_comp.attribute4
, sub_comp.attribute5
, sub_comp.attribute6
, sub_comp.attribute7
, sub_comp.attribute8
, sub_comp.attribute9
, sub_comp.attribute10
, sub_comp.attribute11
, sub_comp.attribute12
, sub_comp.attribute13
, sub_comp.attribute14
, sub_comp.attribute15
, Bom_Globals.Get_Prog_Id
, sub_comp.Original_System_Reference
, sub_comp.enforce_int_requirements
, sub_comp.common_component_sequence_id
FROM BOM_SUBSTITUTE_COMPONENTS sub_comp
WHERE sub_comp.component_sequence_id = p_comp_seq_id;
INSERT INTO bom_component_operations
(
COMP_OPERATION_SEQ_ID ,
OPERATION_SEQ_NUM ,
OPERATION_SEQUENCE_ID ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_LOGIN ,
COMPONENT_SEQUENCE_ID ,
BILL_SEQUENCE_ID ,
CONSUMING_OPERATION_FLAG ,
CONSUMPTION_QUANTITY ,
SUPPLY_SUBINVENTORY ,
SUPPLY_LOCATOR_ID ,
WIP_SUPPLY_TYPE ,
ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 ,
ATTRIBUTE2 ,
ATTRIBUTE3 ,
ATTRIBUTE4 ,
ATTRIBUTE5 ,
ATTRIBUTE6 ,
ATTRIBUTE7 ,
ATTRIBUTE8 ,
ATTRIBUTE9 ,
ATTRIBUTE10 ,
ATTRIBUTE11 ,
ATTRIBUTE12 ,
ATTRIBUTE13 ,
ATTRIBUTE14 ,
ATTRIBUTE15 ,
COMMON_COMPONENT_SEQUENCE_ID)
SELECT
bom_component_operations_s.NEXTVAL ,
comp_ops.OPERATION_SEQ_NUM ,
comp_ops.OPERATION_SEQUENCE_ID ,
comp_ops.LAST_UPDATE_DATE ,
comp_ops.LAST_UPDATED_BY ,
comp_ops.CREATION_DATE ,
comp_ops.CREATED_BY ,
comp_ops.LAST_UPDATE_LOGIN ,
l_component_seqeunce_id ,
comp_ops.BILL_SEQUENCE_ID ,
comp_ops.CONSUMING_OPERATION_FLAG ,
comp_ops.CONSUMPTION_QUANTITY ,
comp_ops.SUPPLY_SUBINVENTORY ,
comp_ops.SUPPLY_LOCATOR_ID ,
comp_ops.WIP_SUPPLY_TYPE ,
comp_ops.ATTRIBUTE_CATEGORY ,
comp_ops.ATTRIBUTE1 ,
comp_ops.ATTRIBUTE2 ,
comp_ops.ATTRIBUTE3 ,
comp_ops.ATTRIBUTE4 ,
comp_ops.ATTRIBUTE5 ,
comp_ops.ATTRIBUTE6 ,
comp_ops.ATTRIBUTE7 ,
comp_ops.ATTRIBUTE8 ,
comp_ops.ATTRIBUTE9 ,
comp_ops.ATTRIBUTE10 ,
comp_ops.ATTRIBUTE11 ,
comp_ops.ATTRIBUTE12 ,
comp_ops.ATTRIBUTE13 ,
comp_ops.ATTRIBUTE14 ,
comp_ops.ATTRIBUTE15 ,
comp_ops.COMMON_COMPONENT_SEQUENCE_ID
FROM BOM_COMPONENT_OPERATIONS comp_ops
WHERE comp_ops.component_sequence_id = p_comp_seq_id;
SELECT Effectivity_Control INTO l_effectivity_control FROM BOM_STRUCTURES_B
WHERE Bill_Sequence_Id = (SELECT Bill_Sequence_Id FROM BOM_COMPONENTS_B WHERE COMPONENT_SEQUENCE_ID = p_comp_seq_id);
SELECT Effectivity_Date INTO l_start_effectivity_date FROM MTL_ITEM_REVISIONS_B WHERE REVISION_ID = p_rev_id;
SELECT
bcb2.component_sequence_id INTO l_component_seqeunce_id
FROM
bom_components_b bcb1, bom_components_b bcb2
WHERE
bcb1.component_sequence_id = p_comp_seq_id
AND bcb1.bill_sequence_id = bcb2.bill_sequence_id
AND nvl(bcb1.obj_name,'EGO_ITEM') = nvl(bcb2.obj_name,'EGO_ITEM')
AND bcb1.pk1_value = bcb2.pk1_value
AND bcb1.operation_seq_num = bcb2.operation_seq_num
AND bcb2.Implementation_Date IS NOT NULL
AND bcb2.effectivity_date <= l_start_effectivity_date
AND (bcb2.disable_date IS NULL OR bcb2.disable_date > l_start_effectivity_date);
SELECT Revision INTO l_rev_code FROM MTL_ITEM_REVISIONS_B WHERE REVISION_ID = p_rev_id;
SELECT
bcb2.component_sequence_id INTO l_component_seqeunce_id
FROM
bom_components_b bcb1, bom_components_b bcb2, mtl_item_revisions_b mirb1, mtl_item_revisions_b mirb2
WHERE
bcb1.component_sequence_id = p_comp_seq_id
AND bcb1.bill_sequence_id = bcb2.bill_sequence_id
AND nvl(bcb1.obj_name,'EGO_ITEM') = nvl(bcb2.obj_name,'EGO_ITEM')
AND bcb1.pk1_value = bcb2.pk1_value
AND bcb1.operation_seq_num = bcb2.operation_seq_num
AND bcb2.Implementation_Date IS NOT NULL
AND bcb2.disable_date IS NULL
AND mirb1.revision_id = bcb2.from_end_item_rev_id
AND mirb2.revision_id = Nvl(bcb2.to_end_item_rev_id, bcb2.from_end_item_rev_id)
AND mirb1.revision <= l_rev_code
AND (bcb2.to_end_item_rev_id IS NULL OR mirb2.revision >= l_rev_code);
SELECT decode(MICG.item_catalog_group_id, NULL, NULL,
FND_FLEX_SERVER.GET_KFV_CONCAT_SEGS_BY_CCID('COMPACT',401,'MICG',101,MICG.item_catalog_group_id,NULL) )
INTO l_catalog_category_name
FROM mtl_item_catalog_groups MICG
-- FROM mtl_item_catalog_groups_kfv
WHERE item_catalog_group_id = p_item_catalog_group_id;
SELECT pending_from_ecn
INTO l_pend_from_ecn
FROM bom_structures_b
WHERE bill_sequence_id = p_bill_Seq_id;
SELECT STRUCTURE_TYPE_ID,PARENT_STRUCTURE_TYPE_ID
FROM BOM_STRUCTURE_TYPES_B
CONNECT BY PRIOR PARENT_STRUCTURE_TYPE_ID = STRUCTURE_TYPE_ID
START WITH STRUCTURE_TYPE_ID = p_structure_type_id;
SELECT dbms_utility.get_time INTO G_TIME_LOGGED(G_TOP) FROM dual;
SELECT dbms_utility.get_time INTO temp2 FROM dual;
FUNCTION check_chg_pol_for_delete(p_bill_seq_id IN NUMBER,
p_comp_seq_id IN NUMBER,
p_start_revision IN VARCHAR2,
p_end_revision IN VARCHAR2,
p_start_rev_id IN NUMBER,
p_end_rev_id IN NUMBER,
p_effective_date IN DATE,
p_disable_date IN DATE,
p_current_chg_pol IN VARCHAR2) RETURN VARCHAR2
IS
l_item_id NUMBER;
SELECT assembly_item_id, organization_id, structure_type_id
INTO l_item_id, l_org_id, l_str_type_id
FROM BOM_STRUCTURES_B
WHERE bill_sequence_id = p_bill_seq_id;
IF p_comp_seq_id IS NOT NULL --Component delete
THEN
RETURN Check_Change_Policy_Range (p_item_id => l_item_id,
p_org_id => l_org_id,
p_component_sequence_id => p_comp_seq_id,
p_current_chg_pol => p_current_chg_pol,
p_structure_type_id => l_str_type_id,
p_context_rev_id => null,
p_use_eco => 'N');
ELSE --structure delete
SELECT revision
INTO l_initial_rev
FROM (SELECT revision
FROM mtl_item_revisions_b
WHERE inventory_item_id = l_item_id
AND organization_id = l_org_id
AND implementation_date IS NOT NULL
ORDER BY effectivity_date)
WHERE ROWNUM = 1;
SELECT pk1_value, pk2_value
FROM bom_components_b
WHERE INSTR(','||p_comp_seq_ids||',', ','||component_sequence_id||',') > 0;
SELECT meaning
INTO l_meaning
FROM mfg_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;