[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dm_node, media_id, dm_folder_path, file_name, dm_document_id
INTO l_master_item_node_id, l_master_item_media_id, l_master_item_folder_path, l_master_item_file_name, l_master_item_dm_doc_id
FROM fnd_documents
WHERE document_id = p_master_org_document_id;
SELECT protocol INTO l_item_attachment_protocol FROM dom_repositories WHERE id = l_master_item_node_id;
SELECT b.document_id, nvl(a.media_id , a.dm_document_id)
INTO l_child_org_document_id, l_child_item_media_id
FROM fnd_documents a, fnd_attached_documents b
WHERE b.document_id = a.document_id AND
b.pk2_value = p_item_id AND
b.pk1_value = p_child_org_id AND
b.pk3_value = p_item_revision_id AND
( (l_master_item_media_id IS NOT NULL AND a.media_id = l_master_item_media_id) OR -- file
(l_master_item_media_id IS NULL AND a.dm_document_id = l_master_item_dm_doc_id))AND -- folder
a.dm_node = l_master_item_node_id;
SELECT b.document_id, a.dm_folder_path, a.file_name
INTO l_child_org_document_id, l_child_item_folder_path, l_child_item_file_name
FROM fnd_documents a, fnd_attached_documents b
WHERE b.document_id = a.document_id AND
b.pk2_value = p_item_id AND
b.pk1_value = p_child_org_id AND
b.pk3_value = p_item_revision_id AND
(a.dm_folder_path || '/' || a.file_name) = l_master_folder_and_file AND
a.dm_node = l_master_item_node_id;
SELECT eec.change_id
FROM eng_engineering_changes eec
, eng_change_obj_relationships ecor
WHERE eec.change_id = ecor.object_to_id1
AND ecor.relationship_code IN ( 'PROPAGATED_TO', 'TRANSFERRED_TO' )
AND ecor.object_to_name ='ENG_CHANGE'
AND ecor.object_to_id3 = p_local_organization_id
AND ecor.change_id = p_change_id;
SELECT fad.attached_document_id, fad.status
FROM fnd_documents_vl fdv, fnd_attached_documents fad
WHERE fdv.document_id = fad.document_id
AND fad.entity_name = p_local_entity_name
AND fad.pk1_value= p_local_pk1_value
AND fad.pk2_value = p_local_pk2_value
AND fad.pk3_value = p_local_pk3_value
AND (fdv.document_id = p_global_document_id
OR
(fdv.document_id <> p_global_document_id
AND ( Nvl(fdv.dm_folder_path, '*NULL*'), fdv.dm_node, fdv.file_name)=
(SELECT Nvl(dm_folder_path, '*NULL*'), dm_node, file_name
FROM fnd_documents_vl
WHERE document_id = p_global_document_id)
)
OR
(fdv.document_id <> p_global_document_id
AND fdv.dm_node = 0
AND (fdv.file_name, fdv.datatype_id) =
(SELECT file_name, datatype_id
FROM fnd_documents_vl
WHERE document_id = p_global_document_id)
)
);
SELECT eac.action_type, eac.attachment_id, eac.source_document_id, eac.SOURCE_VERSION_LABEL
, eac.SOURCE_PATH, eac.DEST_DOCUMENT_ID, eac.DEST_VERSION_LABEL, eac.DEST_PATH
, eac.CHANGE_DOCUMENT_ID, eac.entity_name, eac.pk1_value, eac.pk2_value, eac.pk3_value
, decode(eac.action_type, 'ATTACH', eac.source_document_id,fad.document_id) document_id
, eac.FAMILY_ID, eac.REPOSITORY_ID, eac.PK4_VALUE, eac.PK5_VALUE, eac.NEW_FILE_NAME
, eac.DESCRIPTIOn, eac.NEW_DESCRIPTIOn, eac.NEW_CATEGORY_ID
FROM eng_attachment_changes eac, fnd_attached_documents fad
WHERE eac.change_id = p_change_id -- 4517503
AND eac.revised_item_sequence_id = cp_revised_item_sequence_id
AND eac.attachment_id = fad.attached_document_id(+)
AND (eac.entity_name = 'MTL_ITEM_REVISIONS'
OR (eac.entity_name = 'MTL_SYSTEM_ITEMS'
AND NOT EXISTS (SELECT 1 FROM MTL_PARAMETERS MP
WHERE MP.organization_id = g_global_org_id
AND MP.master_organization_id = MP.organization_id)
))
AND NOT EXISTS (SELECT 1 FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = p_change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.revised_line_type = Eng_Propagation_Log_Util.G_REV_LINE_ATCH_CHG
AND ecpm.revised_line_id1 = eac.change_document_id
AND ecpm.entity_action_status = 3);
SELECT fad.category_id, fad.status, fad.document_id
, fad.attached_document_id, fdv.file_name, fad.last_updated_by
, fdv.dm_type, fdv.datatype_id, fdv.document_id source_document_id
FROM fnd_attached_documents fad, fnd_documents_vl fdv
WHERE fad.attached_document_id = cp_attached_document_id
AND fad.document_id = fdv.document_id
AND cp_attach_action_type <> 'ATTACH'
UNION ALL
SELECT eac.category_id, eac.previous_status, eac.source_document_id
, eac.attachment_id, eac.file_name, eac.last_updated_by
, eac.dm_type, eac.datatype_id, eac.source_document_id
FROM eng_attachment_changes eac, fnd_documents_vl fdv
WHERE eac.change_document_id = cp_change_document_id
AND eac.source_document_id = fdv.document_id
AND cp_attach_action_type = 'ATTACH';
select revision_id
into l_pk3_value from mtl_item_revisions
where inventory_item_id = (select revised_item_id
from eng_revised_items
where revised_item_sequence_id = p_revised_item_unexp_rec.Revised_Item_Sequence_Id)
and revised_item_sequence_id = p_revised_item_unexp_rec.Revised_Item_Sequence_Id;
Eng_Propagation_log_Util.Debug_Log(G_LOG_STMT, 'Inserting into eng_attachment_changes..');
SELECT ENG_ATTACHMENT_CHANGES_S.nextval
INTO l_change_document_id
FROM dual;
INSERT INTO eng_attachment_changes (
CHANGE_ID
, REVISED_ITEM_SEQUENCE_ID
, ACTION_TYPE
, ATTACHMENT_ID
, SOURCE_document_ID
, SOURCE_VERSION_LABEL
, SOURCE_PATH
, DEST_DOCUMENT_ID
, DEST_VERSION_LABEL
, DEST_PATH
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
, CHANGE_DOCUMENT_ID
, FILE_NAME
, CATEGORY_ID
, ATTACHED_USER_ID
, PREVIOUS_STATUS
, FAMILY_ID
, REPOSITORY_ID
, DM_TYPE
, ENTITY_NAME
, PK1_VALUE
, PK2_VALUE
, PK3_VALUE
, PK4_VALUE
, PK5_VALUE
, NEW_FILE_NAME
, DESCRIPTION
, NEW_DESCRIPTION
, NEW_CATEGORY_ID
, DATATYPE_ID
) VALUES (
l_local_change_id
, p_revised_item_unexp_rec.revised_item_sequence_id
, ac.action_type
, decode(l_local_attachment_id, -1, ac.attachment_id, l_local_attachment_id)
, cad.source_document_id
, ac.SOURCE_VERSION_LABEL
, ac.SOURCE_PATH
, ac.DEST_DOCUMENT_ID
, ac.DEST_VERSION_LABEL
, ac.DEST_PATH
, sysdate
, FND_GLOBAL.USER_ID
, sysdate
, FND_GLOBAL.USER_ID
, FND_GLOBAL.USER_ID
, l_change_document_id
, cad.file_name
, cad.category_id
, cad.last_updated_by
, cad.status
, ac.FAMILY_ID
, ac.REPOSITORY_ID
, cad.DM_TYPE
, ac.ENTITY_NAME
, p_revised_item_unexp_rec.organization_id
, ac.PK2_VALUE
, l_pk3_value
, ac.PK4_VALUE
, ac.PK5_VALUE
, ac.NEW_FILE_NAME
, ac.DESCRIPTION
, ac.NEW_DESCRIPTION
, ac.NEW_CATEGORY_ID
, cad.DATATYPE_ID
);
/* update the commoned information in bom_bill_of_materials */
update bom_bill_of_materials set
common_bill_sequence_id = p_to_sequence_id,
common_organization_id = NULL,
common_assembly_item_id = NULL
where bill_sequence_id = p_to_sequence_id;
select 1 into l_item_sourced from dual
where exists (select 1 from mrp_sources_v
where assignment_set_id = FND_PROFILE.VALUE( 'MRP_DEFAULT_ASSIGNMENT_SET' )
and organization_id = g_global_org_id
and inventory_item_id = p_revised_item_id
and source_organization_id = p_local_org_id
and source_type = 2);
SELECT 1
FROM (SELECT nvl(mir.lifecycle_id,msi.lifecycle_id) lifecycle_id,
nvl(mir.current_phase_id,msi.current_phase_id) current_phase_id, msi.item_catalog_group_id,
mir.inventory_item_id, mir.organization_id
FROM MTL_ITEM_REVISIONS mir ,MTL_SYSTEM_ITEMS msi
WHERE mir.INVENTORY_ITEM_ID = p_inventory_item_id
AND mir.ORGANIZATION_ID = p_organization_id
AND mir.revision_id = p_revision_id
AND msi.INVENTORY_ITEM_ID = mir.INVENTORY_ITEM_ID
AND msi.ORGANIZATION_ID = mir.ORGANIZATION_ID) 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 = 'ATTACHMENT'
AND ecp.ATTRIBUTE_NUMBER_VALUE = p_category_id
AND ecp.policy_char_value = 'NOT_ALLOWED';
SELECT eac.action_type, eac.entity_name, eac.pk1_value, eac.pk2_value
, eac.pk3_value, eac.file_name, fad.status, eac.change_document_id
, decode(eac.action_type, 'ATTACH', eac.source_document_id,fad.document_id) document_id,eac.category_id
FROM eng_attachment_changes eac, fnd_attached_documents fad
WHERE eac.change_id = p_change_id
AND eac.revised_item_sequence_id = p_rev_item_sequence_id
AND eac.attachment_id = fad.attached_document_id(+)
AND (eac.entity_name = 'MTL_ITEM_REVISIONS'
OR (eac.entity_name = 'MTL_SYSTEM_ITEMS'
AND NOT EXISTS (SELECT 1 FROM MTL_PARAMETERS MP
WHERE MP.organization_id = p_global_organization_id
AND MP.master_organization_id = MP.organization_id)
))
AND NOT EXISTS (SELECT 1 FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = p_change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.revised_line_type = Eng_Propagation_Log_Util.G_REV_LINE_ATCH_CHG
AND ecpm.revised_line_id1 = eac.change_document_id
AND ecpm.entity_action_status = 3);
SELECT 1
FROM eng_attachment_changes eac
WHERE eac.change_id = p_change_id
AND eac.revised_item_sequence_id = p_rev_item_sequence_id
AND eac.entity_name = 'MTL_SYSTEM_ITEMS'
AND EXISTS(SELECT 1 FROM MTL_PARAMETERS MP
WHERE MP.organization_id = p_global_organization_id
AND MP.master_organization_id = MP.organization_id)
AND NOT EXISTS (SELECT 1 FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = p_change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.revised_line_type = Eng_Propagation_Log_Util.G_REV_LINE_ATCH_CHG
AND ecpm.revised_line_id1 = eac.change_document_id
AND ecpm.entity_action_status = 3);
Error_Handler.Delete_Message(p_entity_id => Eco_Error_Handler.G_ATCH_LEVEL);
l_mesg_token_tbl.delete;
SELECT revision
INTO l_local_revision
FROM MTL_ITEM_REVISIONS
WHERE revision_id = p_local_line_rev_id;
l_Token_Tbl.delete;
SELECT revision
INTO l_global_revision
FROM MTL_ITEM_REVISIONS
WHERE revision_id = p_global_current_item_rev_id;
l_Token_Tbl.delete;
l_Token_Tbl.delete;
SELECT meaning
INTO l_action_name
FROM fnd_lookups
WHERE lookup_type = 'DOM_CHANGE_ACTION_TYPES'
AND lookup_code = ac.action_type;
l_Token_Tbl.delete;
l_Token_Tbl.delete;
SELECT meaning
INTO l_status_name
FROM fnd_lookups
WHERE lookup_type = 'DOM_ATTACHED_DOC_STATUS'
AND lookup_code = l_local_att_status; -- Bug 3599366
l_Token_Tbl.delete;
l_token_tbl.delete;
l_token_tbl.delete;
SELECT eri.revised_item_id, eri.revised_item_sequence_id, eri.alternate_bom_designator
, eri.change_notice, eri.from_end_item_rev_id, eri.from_end_item_strc_rev_id
, bsb.source_bill_sequence_id, eri.bill_sequence_id
FROM eng_revised_items eri, bom_structures_b bsb
WHERE eri.bill_sequence_id = bsb.bill_sequence_id
AND ((eri.revised_item_sequence_id = v_rev_item_seq_id
AND eri.bill_sequence_id IS NOT NULL
AND (eri.transfer_or_copy IS NULL
OR (eri.transfer_or_copy IS NOT NULL
AND eri.enable_item_in_local_org IS NOT NULL
AND nvl(eri.transfer_or_copy_bill, 2) = 2
AND nvl(eri.transfer_or_copy_routing, 2) = 2)
)
)
-- Commented for bug 4946796
/*OR (eri.parent_revised_item_seq_id = v_rev_item_seq_id
AND eri.transfer_or_copy is null)*/);
select distinct eri.revised_item_id, msikfv.concatenated_segments item_name
from eng_revised_items eri, mtl_system_items_b_kfv msikfv
where eri.revised_item_id = msikfv.inventory_item_id
and eri.organization_id = msikfv.organization_id
and eri.parent_revised_item_seq_id = v_rev_item_seq_id
and (eri.transfer_or_copy is not null and eri.transfer_or_copy = 'T')
and eri.enable_item_in_local_org = 'Y'
and eri.revised_item_id not in (SELECT inventory_item_id
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = eri.revised_item_id
AND organization_id = p_local_org_id);*/
select *
from eng_revised_items
where (parent_revised_item_seq_id = p_rev_item_seq_id
or revised_item_sequence_id = p_rev_item_seq_id)
and transfer_or_copy is not null
AND revised_item_sequence_id NOT IN (SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE parent_revised_item_seq_id = p_rev_item_seq_id
AND (transfer_or_copy = 'L' OR transfer_or_copy = 'O')
AND 1 = p_status_master_controlled);*/
SELECT G_VAL_TRUE
into l_local_revised_item_exists
FROM eng_revised_items eri1
WHERE eri1.change_notice = p_change_notice
AND eri1.organization_id = p_local_org_id
AND EXISTS
(SELECT 1
FROM eng_revised_items eri2
WHERE revised_item_sequence_id = p_rev_item_seq_id
AND eri2.organization_id = p_local_org_id -- bug 10146196 added
AND eri2.revised_item_id = eri1.revised_item_id
AND eri2.scheduled_date = eri1.scheduled_date
AND NVL(eri2.alternate_bom_designator, 'primary') = NVL(eri1.alternate_bom_designator, 'primary'))
AND eri1.parent_revised_item_seq_id IS NULL
AND eri1.status_type <> 5
AND ROWNUM < 2;
SELECT 1
into l_item_exists_in_org_flag
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_revised_item_id
AND organization_id = p_local_org_id;
SELECT concatenated_segments
into l_revised_item_number
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = p_revised_item_id
AND organization_id = g_global_org_id;
l_token_tbl.delete;
l_item_error_table.delete;
SELECT 1
INTO l_use_up_item_exists
FROM mtl_system_items
WHERE inventory_item_id = p_use_up_item_id
AND organization_id = p_local_org_id;
select bill_sequence_id, source_bill_sequence_id
into l_local_bill_sequence_id, l_comn_bill_sequence_id
FROM BOM_BILL_OF_MATERIALS
WHERE assembly_item_id = sl.revised_item_id
AND organization_id = p_local_org_id
AND nvl(alternate_bom_designator, 'PRIMARY') = nvl(sl.alternate_bom_designator, 'PRIMARY');
SELECT 1, 1
INTO l_structure_exists_flag, l_create_bill_for_item
FROM dual
WHERE NOT EXISTS (SELECT 1
FROM bom_inventory_components
WHERE change_notice = sl.change_notice
AND revised_item_sequence_id = sl.revised_item_sequence_id
AND acd_type IN (2,3));
l_token_tbl.delete;
l_token_tbl.delete;
l_token_tbl.delete;
l_item_error_table.delete;
select 1
into l_item_exists_in_org_flag
FROM mtl_system_items_b
WHERE inventory_item_id = tolc.revised_item_id
AND organization_id = p_local_org_id;
select concatenated_segments
into l_temp_item_name
from mtl_system_items_b_kfv
where inventory_item_id = tolc.revised_item_id
and organization_id = p_global_org_id;
select bill_sequence_id, common_bill_sequence_id, assembly_type
into l_local_bill_sequence_id, l_comn_bill_sequence_id, l_eng_bill_flag
FROM BOM_BILL_OF_MATERIALS
WHERE assembly_item_id = tolc.revised_item_id
AND organization_id = p_local_org_id
AND nvl(alternate_bom_designator, 'PRIMARY') = nvl(tolc.alternate_bom_designator, 'PRIMARY');
select decode(count(*),0,'N','Y') --bug 14573265
into l_eco_chg_exists
from bom_inventory_components
where change_notice = p_change_notice
and pk2_value = p_local_org_id; --bug 14051321, add org_id to avoid error ORA-01422
l_token_tbl.delete;
SELECT *
FROM eng_changes_v
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id;
SELECT *
FROM eng_change_order_revisions
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id;
SELECT *
FROM eng_revised_items
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id
AND transfer_or_copy is NULL;
SELECT *
FROM eng_revised_components
WHERE change_notice = p_change_notice
AND ACD_TYPE = 3
AND revised_item_sequence_id in
(SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id);
SELECT *
FROM bom_inventory_components
WHERE change_notice = p_change_notice
AND revised_item_sequence_id in
(SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id);
SELECT *
FROM bom_substitute_components
WHERE change_notice = p_change_notice
AND component_sequence_id in
(SELECT component_sequence_id
FROM bom_inventory_components
WHERE change_notice = p_change_notice
AND revised_item_sequence_id in
(SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id));
SELECT *
FROM bom_reference_designators
WHERE change_notice = p_change_notice
AND component_sequence_id in
(SELECT component_sequence_id
FROM bom_inventory_components
WHERE change_notice = p_change_notice
AND revised_item_sequence_id in
(SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE change_notice = p_change_notice
AND organization_id = l_org_hierarchy_level_id));
SELECT us.user_name FROM fnd_user us, hz_parties pa
WHERE ((us.employee_id IS NOT NULL AND us.employee_id = pa.person_identifier))
AND pa.party_id = v_party_id
union all
SELECT us.user_name
FROM fnd_user us, hz_parties pa
WHERE (us.employee_id IS NULL AND (us.person_party_id= pa.party_id or (us.person_party_id is null and us.supplier_id = pa.party_id)))
AND pa.party_id = v_party_id;
SELECT MP.organization_id
INTO l_org_hierarchy_level_id
FROM HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI1
, MTL_PARAMETERS MP
WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1 = 'INV'
AND HOI1.ORG_INFORMATION2 = 'Y'
AND HOU.NAME = p_org_hierarchy_level;
/*SELECT organization_id
INTO l_org_hierarchy_level_id
FROM org_organization_definitions
WHERE organization_name = p_org_hierarchy_level;*/
SELECT organization_code
INTO l_org_code
FROM mtl_parameters
WHERE organization_id = l_org_id;
SELECT count(*)
into temp_count
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = l_org_id;
SELECT CHANGE_ORDER_TYPE
INTO l_change_type_code
FROM eng_change_order_types_v --11.5.10 Changes
WHERE change_order_type_id = eco_rec.change_order_type_id;
SELECT name
INTO l_department_name
FROM hr_all_organization_units
WHERE organization_id = eco_rec.responsible_organization_id;
SELECT approval_list_name
INTO l_approval_list_name
FROM eng_ecn_approval_lists
WHERE approval_list_id = eco_rec.approval_list_id;
SELECT employee_num
INTO l_requestor_name
FROM mtl_employees_view
WHERE organization_id = l_org_hierarchy_level_id
AND employee_id = eco_rec.requestor_id;
SELECT name
into l_Project_Number
FROM pa_projects_all
WHERE project_id = eco_rec.PROJECT_ID;
SELECT task_number
into l_Task_Number
FROM pa_tasks
WHERE TASK_ID = eco_rec.TASK_ID;
SELECT status_name
INTO l_eco_status_name
FROM eng_change_statuses_vl
WHERE status_code = l_status_type;
SELECT concatenated_segments
INTO l_revised_item_number
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = ri.revised_item_id
AND organization_id = l_org_id;
SELECT ALTERNATE_DESIGNATOR_CODE
INTO l_alternate_bom
FROM BOM_ALTERNATE_DESIGNATORS
WHERE ORGANIZATION_ID = l_org_id
AND ALTERNATE_DESIGNATOR_CODE = ri.alternate_bom_designator;
select 1
INTO l_alternate_bom
FROM BOM_BILL_OF_MATERIALS
WHERE assembly_item_id = ri.revised_item_id
AND organization_id = l_org_id
AND alternate_bom_designator is NULL;
SELECT concatenated_segments
INTO l_use_up_item_name
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = ri.use_up_item_id
AND organization_id = l_org_id;
SELECT DESCRIPTION
INTO l_rev_description
FROM mtl_item_revisions
WHERE inventory_item_id = ri.revised_item_id
AND organization_id = ri.organization_id
AND revision = ri.new_item_revision ;
l_revised_item_tbl(i).Updated_Revised_Item_Revision := NULL;
l_revised_item_tbl(i).update_wip := ri.update_wip;
SELECT msi.concatenated_segments,eri.new_item_revision
INTO l_revised_item_name,l_new_item_revision
FROM mtl_system_items_b_kfv msi,
eng_revised_items eri
WHERE eri.revised_item_sequence_id = rcd.revised_item_sequence_id
AND eri.revised_item_id = msi.inventory_item_id
AND msi.organization_id = l_org_id;
SELECT concatenated_segments
INTO l_component_item_name
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = rcd.component_item_id
AND organization_id = l_org_id;
SELECT CONCATENATED_SEGMENTS
INTO l_location_name
FROM mtl_item_locations_kfv
WHERE inventory_location_id = rcd.supply_locator_id;
select assembly_item_id
into item_id
from bom_bill_of_materials
where bill_sequence_id = rcd.bill_sequence_id;
select alternate_bom_designator
into l_alternate_bom
from bom_bill_of_materials
where bill_sequence_id = rcd.bill_sequence_id;
select bill_sequence_id
into bill_id
from bom_bill_of_materials
where assembly_item_id = item_id
and organization_id = l_org_id
and NVL(alternate_bom_designator,'-999')= NVL(l_alternate_bom,'-999'); /* for bug 9368374 */
select operation_seq_num,trunc(effectivity_date)
into old_operation_seq_num,l_old_effectivity_date
from bom_inventory_components
where COMPONENT_SEQUENCE_ID = rcd.OLD_COMPONENT_SEQUENCE_ID;
select max(component_sequence_id)
into component_seq_id
from bom_inventory_components
where ((trunc(effectivity_date) = l_old_effectivity_date) OR
(rcd.effectivity_date between
trunc(effectivity_date) and
NVL(disable_date, rcd.effectivity_date + 1)))
-- Bug 3041105 : Commenting code to pick unimplemented components
-- and implementation_date IS NOT NULL
and component_item_id = rcd.COMPONENT_ITEM_ID
and bill_sequence_id = bill_id
and operation_seq_num = old_operation_seq_num;
select effectivity_date
into old_effectivity_date
from bom_inventory_components
where component_sequence_id = component_seq_id;
SELECT msi.concatenated_segments,eri.new_item_revision
INTO l_revised_item_name,l_new_item_revision
FROM mtl_system_items_b_kfv msi,
eng_revised_items eri
WHERE eri.revised_item_sequence_id = rc.revised_item_sequence_id
AND eri.revised_item_id = msi.inventory_item_id
AND msi.organization_id = l_org_id;
/* SELECT concatenated_segments -- Commented for bug-5725081
INTO l_component_item_name
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = rc.component_item_id
AND organization_id = l_org_id;
SELECT msit.concatenated_segments,
DECODE(msif.primary_unit_of_measure,
msit.primary_unit_of_measure,
rc.component_quantity,
inv_convert.INV_UM_CONVERT(rc.component_item_id,
NULL,
rc.component_quantity,
NULL,
NULL,
msif.primary_unit_of_measure,
msit.primary_unit_of_measure
)
),
DECODE(msif.primary_unit_of_measure,
msit.primary_unit_of_measure,
rc.low_quantity,
DECODE(rc.low_quantity, NULL, NULL,
inv_convert.INV_UM_CONVERT(
rc.component_item_id,
NULL,
rc.low_quantity,
NULL,
NULL,
msif.primary_unit_of_measure,
msit.primary_unit_of_measure
)
)
),
DECODE(msif.primary_unit_of_measure,
msit.primary_unit_of_measure,
rc.high_quantity,
DECODE(rc.high_quantity, NULL, NULL,
inv_convert.INV_UM_CONVERT(
rc.component_item_id,
NULL,
rc.high_quantity,
NULL,
NULL,
msif.primary_unit_of_measure,
msit.primary_unit_of_measure
)
)
)
INTO l_component_item_name,
v_component_quantity_to,
v_component_low_quantity,
v_component_high_quantity
FROM mtl_system_items_b_kfv MSIF ,
mtl_system_items_b_kfv MSIT
WHERE msif.inventory_item_id = msit.inventory_item_id
AND msif.inventory_item_id = rc.component_item_id
AND msit.organization_id = l_org_id
AND msif.organization_id = l_org_hierarchy_level_id ;
SELECT CONCATENATED_SEGMENTS
INTO l_location_name
FROM mtl_item_locations_kfv
WHERE inventory_location_id = rc.supply_locator_id;
select alternate_bom_designator
into l_alternate_bom
from bom_bill_of_materials
where bill_sequence_id = rc.bill_sequence_id;
select assembly_item_id
into item_id
from bom_bill_of_materials
where bill_sequence_id = rc.bill_sequence_id;
select bill_sequence_id
into bill_id
from bom_bill_of_materials
where assembly_item_id = item_id
and organization_id = l_org_id
and NVL(alternate_bom_designator,'-999') = NVL(l_alternate_bom,'-999'); /* for bug 9368374 */
select operation_seq_num,trunc(effectivity_date)
into old_operation_seq_num,l_old_effectivity_date
from bom_inventory_components
where COMPONENT_SEQUENCE_ID = rc.OLD_COMPONENT_SEQUENCE_ID;
select max(component_sequence_id)
into component_seq_id
from bom_inventory_components
where ((trunc(effectivity_date) = l_old_effectivity_date) OR
(rc.effectivity_date between
trunc(effectivity_date) and
NVL(disable_date, rc.effectivity_date + 1))
)
-- Bug 3041105 : Commenting code to pick unimplemented components
-- and implementation_date IS NOT NULL
and component_item_id = rc.COMPONENT_ITEM_ID
and bill_sequence_id = bill_id
and operation_seq_num = old_operation_seq_num;
select effectivity_date
into old_effectivity_date
from bom_inventory_components
where component_sequence_id = component_seq_id;
SELECT msi.concatenated_segments,eri.new_item_revision
INTO l_revised_item_name1,l_new_item_revision
FROM mtl_system_items_b_kfv msi,
eng_revised_items eri,
bom_inventory_components bic
WHERE bic.component_sequence_id = sc.component_sequence_id
AND eri.revised_item_sequence_id = bic.revised_item_sequence_id
AND eri.revised_item_id = msi.inventory_item_id
AND msi.organization_id = l_org_id;
SELECT concatenated_segments,bic.effectivity_date,
bic.operation_seq_num
INTO l_component_item_name1,l_effectivity_date,
l_operation_seq_num
FROM mtl_system_items_b_kfv msi,
bom_inventory_components bic
WHERE bic.component_sequence_id = sc.component_sequence_id
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = l_org_id;
/* SELECT concatenated_segments -- Commented for bug-5725081
INTO l_substitute_component_name
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = sc.substitute_component_id
AND organization_id = l_org_id;
SELECT msit.concatenated_segments, -- Added for bug-5725081
DECODE(msif.primary_unit_of_measure,
msit.primary_unit_of_measure,
sc.substitute_item_quantity ,
inv_convert.INV_UM_CONVERT(sc.substitute_component_id,
NULL,
sc.substitute_item_quantity,
NULL,
NULL,
msif.primary_unit_of_measure,
msit.primary_unit_of_measure)
)
INTO l_substitute_component_name,
v_substitute_item_quantity
FROM mtl_system_items_b_kfv MSIF,
mtl_system_items_b_kfv MSIT
WHERE msif.inventory_item_id = msit.inventory_item_id
AND msif.inventory_item_id = sc.substitute_component_id
AND msit.organization_id = l_org_id
AND msif.organization_id = l_org_hierarchy_level_id;
SELECT bom.alternate_bom_designator
INTO l_alternate_bom
FROM bom_inventory_components bic,
bom_structures_b bom
WHERE bic.component_sequence_id = sc.component_sequence_id
AND bic.bill_sequence_id = bom.bill_sequence_id;
SELECT msi.concatenated_segments,eri.new_item_revision,
bic.effectivity_date,bic.operation_seq_num
INTO l_revised_item_name2,l_new_item_revision,
l_effectivity_date,l_operation_seq_num
FROM mtl_system_items_b_kfv msi,
eng_revised_items eri,
bom_inventory_components bic
WHERE bic.component_sequence_id = rd.component_sequence_id
AND eri.revised_item_sequence_id = bic.revised_item_sequence_id
AND eri.revised_item_id = msi.inventory_item_id
AND msi.organization_id = l_org_id;
SELECT concatenated_segments
INTO l_component_item_name2
FROM mtl_system_items_b_kfv msi,
bom_inventory_components bic
WHERE bic.component_sequence_id = rd.component_sequence_id
AND msi.inventory_item_id = bic.component_item_id
AND msi.organization_id = l_org_id;
SELECT bom.alternate_bom_designator
INTO l_alternate_bom
FROM bom_inventory_components bic,
bom_structures_b bom
WHERE bic.component_sequence_id = rd.component_sequence_id
AND bic.bill_sequence_id = bom.bill_sequence_id;
SELECT change_id INTO l_new_change_id
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = l_org_id;
X_last_update_login => '',
X_program_application_id=> '',
X_program_id => '',
X_request_id => ''
);
SELECT component_item_id, supply_locator_id, bill_sequence_id, old_component_sequence_id
, effectivity_date , attribute_category, ACD_TYPE, change_notice, disable_date
, component_remarks, operation_seq_num, attribute1, attribute2, attribute3
, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10
, attribute11, attribute12, attribute13, attribute14, attribute15, item_num
, component_quantity, planning_factor, component_yield_factor, include_in_cost_rollup
, wip_supply_type, so_basis, basis_type, 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, from_end_item_unit_number
, TO_END_ITEM_UNIT_NUMBER, ORIGINAL_SYSTEM_REFERENCE
FROM bom_components_b
WHERE component_sequence_id = p_component_sequence_id
UNION ALL
SELECT component_item_id, supply_locator_id, bill_sequence_id, old_component_sequence_id
, effectivity_date , attribute_category, ACD_TYPE, change_notice, disable_date
, component_remarks, OPERATION_SEQUENCE_NUM, attribute1, attribute2, attribute3
, attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10
, attribute11, attribute12, attribute13, attribute14, attribute15, item_num
, component_quantity, planning_factor, component_yield_factor, include_in_cost_rollup
, wip_supply_type, so_basis, basis_type, 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, from_end_item_unit_number
, TO_END_ITEM_UNIT_NUMBER, ORIGINAL_SYSTEM_REFERENCE FROM eng_revised_components
WHERE component_sequence_id = p_component_sequence_id
AND acd_type = 3;
SELECT *
FROM bom_substitute_components
WHERE change_notice = p_revised_item_rec.eco_name
AND component_sequence_id = p_component_sequence_id;
SELECT *
FROM bom_reference_designators
WHERE change_notice = p_revised_item_rec.eco_name
AND component_sequence_id = p_component_sequence_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
l_token_tbl.delete;
SELECT CONCATENATED_SEGMENTS
INTO l_location_name
FROM mtl_item_locations_kfv
WHERE inventory_location_id = rc.supply_locator_id;
select assembly_item_id
into item_id
from bom_bill_of_materials
where bill_sequence_id = rc.bill_sequence_id;
select bill_sequence_id
into bill_id
from bom_bill_of_materials
where assembly_item_id = item_id
and organization_id = p_local_organization_id
and nvl(ALTERNATE_BOM_DESIGNATOR, 'primary') = nvl(p_revised_item_rec.alternate_bom_code,'primary');
select operation_seq_num,trunc(effectivity_date)
into old_operation_seq_num,l_old_effectivity_date
from bom_inventory_components
where COMPONENT_SEQUENCE_ID = rc.OLD_COMPONENT_SEQUENCE_ID;
select max(component_sequence_id)
into component_seq_id
from bom_inventory_components
where ((trunc(effectivity_date) = l_old_effectivity_date) OR
(rc.effectivity_date between
trunc(effectivity_date) and
NVL(disable_date, rc.effectivity_date + 1))
)
-- Bug 3041105 : Commenting code to pick unimplemented components
-- and implementation_date IS NOT NULL
and component_item_id = rc.COMPONENT_ITEM_ID
and bill_sequence_id = bill_id
and operation_seq_num = old_operation_seq_num;
select effectivity_date
into old_effectivity_date
from bom_inventory_components
where component_sequence_id = component_seq_id;
l_item_error_table.delete;
l_item_error_table.delete;
l_token_tbl.delete;
END LOOP; -- End of loop of revised components Only one is selected
SELECT G_VAL_EXISTS
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
SELECT eohp.SCHEDULE_IMMEDIATELY_FLAG
FROM ENG_ORG_HIERARCHY_POLICIES eohp, ENG_TYPE_ORG_HIERARCHIES etoh, eng_engineering_changes eec
WHERE eec.change_id = p_local_change_id
AND eohp.organization_id = eec.organization_id
AND eohp.SCHEDULE_IMMEDIATELY_FLAG = 'Y'
AND eohp.change_type_org_hierarchy_id = etoh.change_type_org_hierarchy_id
AND etoh.change_type_id = eec.change_order_type_id
AND etoh.organization_id = p_organization_id
AND EXISTS (SELECT 1
FROM per_organization_structures hier
WHERE hier.name = p_org_hierarchy_name
AND etoh.hierarchy_id = hier.organization_structure_id);
SELECT els.status_code
FROM eng_lifecycle_statuses els
WHERE els.entity_id1 = p_local_change_id
AND els.entity_name = 'ENG_CHANGE'
AND els.sequence_number =
(SELECT min(sequence_number)
FROM eng_lifecycle_statuses els1, eng_change_statuses_vl ecs1
WHERE els1.entity_id1 = p_local_change_id
AND els1.entity_name = 'ENG_CHANGE'
AND els1.status_code = ecs1.status_code
AND ((ecs1.status_type = 4 AND l_schedule_immediately = 'Y')
OR nvl(l_schedule_immediately, 'N') = 'N'))
AND EXISTS (SELECT 1
FROM eng_engineering_changes
WHERE change_id = els.entity_id1
AND status_type = 0);
SELECT status_type
INTO l_new_status_type
FROM eng_change_statuses
WHERE status_code = l_status_code
AND rownum = 1;
UPDATE eng_revised_items
SET status_code = l_status_code,
status_type = l_new_status_type,
last_update_date = sysdate,
last_updated_by = FND_PROFILE.VALUE('USER_ID'),
last_update_login = FND_PROFILE.VALUE('LOGIN_ID')
WHERE change_id = p_local_change_id;
SELECT *
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
SELECT *
FROM eng_change_order_revisions
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id
AND nvl(start_date, sysdate) > sysdate; -- Added this condtion as only future revisions need to be fetched
SELECT party_name
FROM hz_parties
WHERE party_id = cp_party_id
AND party_type = 'GROUP';
SELECT party.party_id, party.party_name, party.party_type
FROM HZ_PARTIES party, fnd_grants grants, fnd_objects obj
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.GRANTEE_ORIG_SYSTEM_ID = party.party_id
AND (
(grants.GRANTEE_ORIG_SYSTEM = 'HZ_PARTY' AND grants.grantee_type ='USER' AND party.party_type= 'PERSON')
OR (grants.GRANTEE_ORIG_SYSTEM = 'HZ_GROUP' AND grants.grantee_type ='GROUP' AND party.party_type= 'GROUP')
)
AND grants.start_date <= SYSDATE
AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
AND grants.instance_type = 'INSTANCE'
AND grants.menu_id = cp_assignee_role_id
AND grants.instance_pk1_value = cp_item_id
AND grants.instance_pk2_value = cp_org_id
AND ROWNUM = 1;
SELECT party.party_id, party.party_name, party.party_type
FROM HZ_PARTIES party, fnd_grants grants, fnd_objects obj
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.GRANTEE_ORIG_SYSTEM_ID = party.party_id
AND (
(grants.GRANTEE_ORIG_SYSTEM = 'HZ_PARTY' AND grants.grantee_type ='USER' AND party.party_type= 'PERSON')
OR (grants.GRANTEE_ORIG_SYSTEM = 'HZ_GROUP' AND grants.grantee_type ='GROUP' AND party.party_type= 'GROUP')
)
AND grants.start_date <= SYSDATE
AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
AND grants.instance_type= 'SET'
AND grants.menu_id = cp_assignee_role_id
AND grants.instance_set_id IN ( SELECT instance_set.instance_set_id
FROM fnd_object_instance_sets instance_set, mtl_system_items_b item1
WHERE instance_set.object_id = grants.object_id
AND instance_set.instance_set_name = 'EGO_ORG_CAT_ITEM_' ||
to_char(item1.organization_id) || '_' || to_char(item1.ITEM_CATALOG_GROUP_ID)
AND item1.INVENTORY_ITEM_ID= cp_item_id
AND item1.ORGANIZATION_ID = cp_org_id )
AND ROWNUM = 1;
SELECT party.party_id, party.party_name, party.party_type
FROM HZ_PARTIES party, fnd_grants grants, fnd_objects obj
WHERE obj.obj_name = 'EGO_ITEM'
AND grants.object_id = obj.object_id
AND grants.GRANTEE_ORIG_SYSTEM_ID = party.party_id
AND (
(grants.GRANTEE_ORIG_SYSTEM = 'HZ_PARTY' AND grants.grantee_type ='USER' AND party.party_type= 'PERSON')
OR (grants.GRANTEE_ORIG_SYSTEM = 'HZ_GROUP' AND grants.grantee_type ='GROUP' AND party.party_type= 'GROUP')
)
AND grants.start_date <= SYSDATE
AND NVL(grants.end_date, SYSDATE+1) >= TRUNC(SYSDATE)
AND grants.instance_type= 'SET'
AND grants.menu_id = cp_assignee_role_id
AND grants.instance_set_id IN ( SELECT instance_set.instance_set_id
FROM fnd_object_instance_sets instance_set
WHERE instance_set.object_id = grants.object_id
AND instance_set.instance_set_name = 'EGO_ORG_ITEM_' ||cp_org_id)
AND ROWNUM = 1;
SELECT us.user_name FROM fnd_user us, hz_parties pa
WHERE ((us.employee_id IS NOT NULL AND us.employee_id = pa.person_identifier))
AND pa.party_id = v_party_id
union all
SELECT us.user_name
FROM fnd_user us, hz_parties pa
WHERE (us.employee_id IS NULL AND (us.person_party_id = pa.party_id or (us.person_party_id is null and us.supplier_id = pa.party_id)))
AND pa.party_id = v_party_id;
SELECT ecotv.CHANGE_ORDER_TYPE
, ecmtv.name
, ecotv.default_assignee_type
, ecotv.default_assignee_id
INTO l_change_type_code
, l_change_mgmt_type
, l_default_assignee_type
, l_assignee_role_id
FROM eng_change_order_types_v ecotv
, eng_change_mgmt_types_vl ecmtv
WHERE ecotv.change_order_type_id = eco_rec.change_order_type_id
AND ecotv.CHANGE_MGMT_TYPE_CODE = ecmtv.CHANGE_MGMT_TYPE_CODE;
SELECT name
INTO l_department_name
FROM hr_all_organization_units
WHERE organization_id = eco_rec.responsible_organization_id;
SELECT name
into l_Project_Number
FROM pa_projects_all
WHERE project_id = eco_rec.PROJECT_ID;
SELECT task_number
INTO l_Task_Number
FROM pa_tasks
WHERE TASK_ID = eco_rec.TASK_ID;
SELECT PPE.ELEMENT_NUMBER
INTO l_task_number1
FROM PA_PROJ_ELEMENTS PPE
WHERE PPE.PROJECT_ID = Eco_rec.PROJECT_ID
AND PPE.PROJ_ELEMENT_ID = Eco_rec.TASK_ID;
SELECT 1
INTO l_sched_immediately
FROM ENG_ORG_HIERARCHY_POLICIES
WHERE organization_id = p_local_organization_id
AND SCHEDULE_IMMEDIATELY_FLAG = 'Y'
AND change_type_org_hierarchy_id =
(SELECT change_type_org_hierarchy_id
FROM ENG_TYPE_ORG_HIERARCHIES
WHERE change_type_id = l_change_type_id
AND organization_id = p_organization_id
AND hierarchy_id = (SELECT organization_structure_id
FROM per_organization_structures
WHERE name = p_org_hierarchy_name));
SELECT ecs.status_name
INTO l_status_name
FROM eng_change_statuses_vl ecs
WHERE ecs.status_code = 0;
select sub1.pk1_value, sub1.pk2_value, sub2.pk3_value, sub2.entity_name
INTO l_pk1_value, l_pk2_value, l_pk3_value, l_entity_name
from eng_change_subjects sub1,eng_change_subjects sub2
where sub1.change_id = sub2.change_id
and sub1.change_id = p_change_id
and sub1.change_line_id is null
and sub2.change_line_id is null
and sub1.entity_name = 'EGO_ITEM'
and ((sub2.entity_name = 'EGO_ITEM_REVISION' and sub2.subject_level =1 and sub1.subject_level=2 )
or (sub2.entity_name = sub1.entity_name and sub2.subject_level = sub1.subject_level and sub1.subject_level=1 ))
and rownum =1;
SELECT concatenated_segments
INTO l_pk1_name
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = l_pk1_value
AND organization_id = p_local_organization_id;
select revision
into l_pk3_name
from mtl_item_revisions
where inventory_item_id = l_pk1_value
AND organization_id = p_local_organization_id
and revision = ( select revision
from mtl_item_revisions
where revision_id = l_pk3_value);
SELECT control_level
FROM MTL_ITEM_ATTRIBUTES
WHERE ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
l_rev_label mtl_item_revisions.revision_label%type; -- Fixed bug10255737, can update revision label correctly
SELECT use_up_item_id
, revised_item_id
, enable_item_in_local_org
, transfer_or_copy
, transfer_or_copy_item
, alternate_bom_designator
, revised_item_sequence_id
FROM eng_revised_items
WHERE revised_item_sequence_id = p_revised_item_sequence_id;
SELECT *
FROM eng_revised_items
WHERE (revised_item_sequence_id = cp_revised_item_sequence_id)
/*OR parent_revised_item_seq_id = cp_revised_item_sequence_id)
AND revised_item_sequence_id NOT IN
(SELECT revised_item_sequence_id
FROM eng_revised_items
WHERE parent_revised_item_seq_id = cp_revised_item_sequence_id
AND (transfer_or_copy = 'L' OR transfer_or_copy = 'O')
AND 1 = l_status_master_controlled
)
ORDER BY parent_revised_item_seq_id DESC*/;
SELECT *
FROM eng_revised_components erc
WHERE erc.change_notice = p_change_notice
AND erc.ACD_TYPE = 3
AND erc.revised_item_sequence_id = cp_revised_item_sequence_id
AND NOT EXISTS (SELECT 1 FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = p_change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.revised_line_type = Eng_Propagation_Log_Util.G_REV_LINE_CMP_CHG
AND ecpm.revised_line_id1 = erc.component_sequence_id
AND ecpm.entity_action_status IN (3,4))
AND EXISTS (SELECT 1 FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id = erc.revised_item_sequence_id
AND eri.bill_sequence_id = erc.bill_sequence_id);
SELECT *
FROM bom_components_b bcb
WHERE change_notice = p_change_notice
AND revised_item_sequence_id = cp_revised_item_sequence_id
AND NOT EXISTS (SELECT 1 FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = p_change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.revised_line_type = Eng_Propagation_Log_Util.G_REV_LINE_CMP_CHG
AND ecpm.revised_line_id1 = bcb.component_sequence_id
AND ecpm.entity_action_status IN (3,4))
AND EXISTS (SELECT 1 FROM eng_revised_items eri
WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
AND eri.bill_sequence_id = bcb.bill_sequence_id);
SELECT concatenated_segments
INTO l_revised_item_number
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = ri.revised_item_id
AND organization_id = p_local_organization_id;
SELECT concatenated_segments
INTO l_use_up_item_name
FROM mtl_system_items_b_kfv
WHERE inventory_item_id = ri.use_up_item_id
AND organization_id = p_local_organization_id;
SELECT pl.plan_name
INTO l_use_up_plan_name
FROM mrp_bom_plan_name_lov_v pl
WHERE pl.item_id = ri.use_up_item_id
AND pl.organization_id = p_local_organization_id
AND pl.plan_name = ri.use_up_plan_name;
SELECT BOM_REVISIONS.get_item_revision_fn('ALL', 'ALL', p_local_organization_id,
ri.revised_item_id, SYSDATE) revision ,
BOM_REVISIONS.get_item_revision_id_fn('ALL', 'ALL', p_local_organization_id,
ri.revised_item_id, SYSDATE) revision_id
INTO l_current_local_revision, l_current_local_revision_id
FROM dual;
select 1
into l_new_revision_exists
from mtl_item_revisions
where revision = l_new_item_revision
and inventory_item_id = ri.revised_item_id
and organization_id = p_local_organization_id;
l_token_tbl.delete;
select bill_sequence_id
into l_local_bill_sequence_id
FROM BOM_BILL_OF_MATERIALS
WHERE assembly_item_id = ri.revised_item_id
AND organization_id = p_local_organization_id
AND nvl(alternate_bom_designator, 'PRIMARY') = nvl(ri.alternate_bom_designator, 'PRIMARY');
select bsr.revision
into l_current_struc_revision
from should use minor revision table bsr
where bsr.bill_sequence_id = l_local_bill_sequence_id
and bsr.object_revision_id = l_current_local_revision_id
and bsr.effective_date = (select max(effective_date)
from should use minor revision table
where structure_revision_id = bsr.structure_revision_id
and bsr.effective_date < sysdate);
select 1
into l_new_revision_exists
from should use minor revision table
where bill_sequence_id = l_local_bill_sequence_id
and object_revision_id = l_current_local_revision_id
and revision = l_new_struc_revision;
select msikfv.concatenated_segments, mir.revision
into l_from_end_item_name, l_from_end_item_revision
from mtl_system_items_b_kfv msikfv, mtl_item_revisions mir
where mir.revision_id = ri.from_end_item_rev_id
and mir.inventory_item_id = msikfv.inventory_item_id
and mir.organization_id = msikfv.organization_id;
select bsr.revision, bbm.alternate_bom_designator
into l_from_end_item_minor_rev, l_from_end_item_alternate
from should use minor revision table bsr, bom_bill_of_materials bbm
where bsr.structure_revision_id = ri.from_end_item_strc_rev_id
and bsr.bill_sequence_id = bbm.bill_sequence_id;
l_sql_stmt := 'SELECT LP.DISPLAY_SEQUENCE, LP.NAME '
|| 'FROM pa_ego_phases_v LP, MTL_System_items_vl msiv '
|| 'WHERE LP.PROJ_ELEMENT_ID = msiv.CURRENT_PHASE_ID '
|| 'AND msiv.INVENTORY_ITEM_ID = :1 '
|| 'AND msiv.ORGANIZATION_ID = :2 ';
l_sql_stmt := 'SELECT LP.DISPLAY_SEQUENCE, LP.NAME '
|| 'FROM pa_ego_phases_v LP '
|| 'WHERE LP.PROJ_ELEMENT_ID = :1 ';
SELECT BSTV.structure_type_name
INTO l_structure_type_name
FROM bom_structures_b BSB,
bom_structure_types_vl BSTV
WHERE BSB.structure_type_id = BSTV.structure_type_id
AND BSB.bill_sequence_id = ri.bill_sequence_id;
SELECT DESCRIPTION, revision_label
INTO l_rev_description, l_rev_label
FROM mtl_item_revisions
WHERE inventory_item_id = ri.revised_item_id
AND organization_id = ri.organization_id
AND revision = l_new_item_revision ;
l_revised_item_tbl(l_revised_item_count).New_Revision_Label := l_rev_label; -- Fixed bug10255737, can update revision label correctly
l_revised_item_tbl(l_revised_item_count).Updated_Revised_Item_Revision := NULL;
l_revised_item_tbl(l_revised_item_count).update_wip := ri.update_wip;
l_revised_item_tbl(l_revised_item_count).selection_option := ri.selection_option;
l_revised_item_tbl(l_revised_item_count).selection_date := ri.selection_date;
l_revised_item_tbl(l_revised_item_count).selection_unit_number := ri.selection_unit_number;
select revision_id
into l_local_line_rev_id
from mtl_item_revisions
where inventory_item_id = (select revised_item_id
from eng_revised_items
where revised_item_sequence_id = l_revised_item_unexp_rec.revised_item_sequence_id)
and revised_item_sequence_id = l_revised_item_unexp_rec.revised_item_sequence_id;
SELECT EXTENSION_ID
FROM ENG_CHANGES_EXT_B
WHERE CHANGE_ID = v_change_id;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT change_id
FROM eng_engineering_changes
WHERE change_notice = p_change_notice
AND organization_id = p_organization_id;
SELECT eri.revised_item_sequence_id
FROM eng_revised_items eri
WHERE eri.change_id = l_global_change_id
AND transfer_or_copy is NULL
AND NOT EXISTS
(SELECT 1
FROM eng_change_propagation_maps ecpm
WHERE ecpm.change_id = eri.change_id
AND ecpm.local_organization_id = p_local_organization_id
AND ecpm.revised_item_sequence_id = eri.revised_item_sequence_id
AND ecpm.entity_name = Eng_Propagation_Log_Util.G_ENTITY_REVISED_ITEM
AND ecpm.entity_action_status IN (Eng_Propagation_Log_Util.G_PRP_PRC_STS_SUCCESS, Eng_Propagation_Log_Util.G_PRP_PRC_STS_EXCL_TTM));
SELECT object_to_id1
INTO l_propagated_to_chg_id
FROM eng_change_obj_relationships
WHERE relationship_code = 'PROPAGATED_TO'
AND object_to_name ='ENG_CHANGE'
AND change_id = l_global_change_id
AND object_to_id2 = p_organization_id
AND object_to_id3 = p_local_organization_id;
SELECT EGO_EXTFWK_S.NEXTVAL
INTO l_ext_id_nextval
FROM DUAL;
INSERT INTO ENG_CHANGES_EXT_B (
EXTENSION_ID ,
CHANGE_ID ,
CHANGE_TYPE_ID ,
ATTR_GROUP_ID ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
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 ,
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 ,
D_EXT_ATTR1 ,
D_EXT_ATTR2 ,
D_EXT_ATTR3 ,
D_EXT_ATTR4 ,
D_EXT_ATTR5 ,
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_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 ,
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 ,
D_EXT_ATTR6 ,
D_EXT_ATTR7 ,
D_EXT_ATTR8 ,
D_EXT_ATTR9 ,
D_EXT_ATTR10
) SELECT
l_ext_id_nextval ,
l_propagated_to_chg_id ,
CHANGE_TYPE_ID ,
ATTR_GROUP_ID ,
Eng_Globals.Get_User_Id ,
sysdate ,
Eng_Globals.Get_User_Id ,
sysdate ,
Eng_Globals.Get_Login_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 ,
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 ,
D_EXT_ATTR1 ,
D_EXT_ATTR2 ,
D_EXT_ATTR3 ,
D_EXT_ATTR4 ,
D_EXT_ATTR5 ,
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_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 ,
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 ,
D_EXT_ATTR6 ,
D_EXT_ATTR7 ,
D_EXT_ATTR8 ,
D_EXT_ATTR9 ,
D_EXT_ATTR10
FROM ENG_CHANGES_EXT_B
WHERE CHANGE_ID = l_global_change_id
AND EXTENSION_ID = ri.EXTENSION_ID;
INSERT INTO ENG_CHANGES_EXT_TL (
EXTENSION_ID ,
CHANGE_ID ,
CHANGE_TYPE_ID ,
ATTR_GROUP_ID ,
SOURCE_LANG ,
LANGUAGE ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
CREATED_BY ,
CREATION_DATE ,
TL_EXT_ATTR1 ,
TL_EXT_ATTR2 ,
TL_EXT_ATTR3 ,
TL_EXT_ATTR4 ,
TL_EXT_ATTR5 ,
TL_EXT_ATTR6 ,
TL_EXT_ATTR7 ,
TL_EXT_ATTR8 ,
TL_EXT_ATTR9 ,
TL_EXT_ATTR10 ,
TL_EXT_ATTR11 ,
TL_EXT_ATTR12 ,
TL_EXT_ATTR13 ,
TL_EXT_ATTR14 ,
TL_EXT_ATTR15 ,
TL_EXT_ATTR16 ,
TL_EXT_ATTR17 ,
TL_EXT_ATTR18 ,
TL_EXT_ATTR19 ,
TL_EXT_ATTR20 ,
TL_EXT_ATTR21 ,
TL_EXT_ATTR22 ,
TL_EXT_ATTR23 ,
TL_EXT_ATTR24 ,
TL_EXT_ATTR25 ,
TL_EXT_ATTR26 ,
TL_EXT_ATTR27 ,
TL_EXT_ATTR28 ,
TL_EXT_ATTR29 ,
TL_EXT_ATTR30 ,
TL_EXT_ATTR31 ,
TL_EXT_ATTR32 ,
TL_EXT_ATTR33 ,
TL_EXT_ATTR34 ,
TL_EXT_ATTR35 ,
TL_EXT_ATTR36 ,
TL_EXT_ATTR37 ,
TL_EXT_ATTR38 ,
TL_EXT_ATTR39 ,
TL_EXT_ATTR40
) SELECT
l_ext_id_nextval ,
l_propagated_to_chg_id ,
CHANGE_TYPE_ID ,
ATTR_GROUP_ID ,
SOURCE_LANG ,
LANGUAGE ,
sysdate ,
Eng_Globals.Get_User_Id ,
Eng_Globals.Get_Login_id ,
Eng_Globals.Get_User_Id ,
sysdate ,
TL_EXT_ATTR1 ,
TL_EXT_ATTR2 ,
TL_EXT_ATTR3 ,
TL_EXT_ATTR4 ,
TL_EXT_ATTR5 ,
TL_EXT_ATTR6 ,
TL_EXT_ATTR7 ,
TL_EXT_ATTR8 ,
TL_EXT_ATTR9 ,
TL_EXT_ATTR10 ,
TL_EXT_ATTR11 ,
TL_EXT_ATTR12 ,
TL_EXT_ATTR13 ,
TL_EXT_ATTR14 ,
TL_EXT_ATTR15 ,
TL_EXT_ATTR16 ,
TL_EXT_ATTR17 ,
TL_EXT_ATTR18 ,
TL_EXT_ATTR19 ,
TL_EXT_ATTR20 ,
TL_EXT_ATTR21 ,
TL_EXT_ATTR22 ,
TL_EXT_ATTR23 ,
TL_EXT_ATTR24 ,
TL_EXT_ATTR25 ,
TL_EXT_ATTR26 ,
TL_EXT_ATTR27 ,
TL_EXT_ATTR28 ,
TL_EXT_ATTR29 ,
TL_EXT_ATTR30 ,
TL_EXT_ATTR31 ,
TL_EXT_ATTR32 ,
TL_EXT_ATTR33 ,
TL_EXT_ATTR34 ,
TL_EXT_ATTR35 ,
TL_EXT_ATTR36 ,
TL_EXT_ATTR37 ,
TL_EXT_ATTR38 ,
TL_EXT_ATTR39 ,
TL_EXT_ATTR40
FROM ENG_CHANGES_EXT_TL
WHERE CHANGE_ID = l_global_change_id
AND EXTENSION_ID = ri.EXTENSION_ID;
SELECT eclo.local_organization_id
FROM eng_engineering_changes eec
, eng_change_local_orgs eclo
, hr_all_organization_units org
, hr_organization_information hoi
, mtl_parameters mp
WHERE eec.change_notice = p_change_notice
AND eec.organization_id = p_organization_id
AND eclo.change_id = eec.change_id
AND org.organization_id = hoi.organization_id
AND org.organization_id = mp.organization_id
AND hoi.org_information1 = 'INV'
AND hoi.org_information2 = 'Y' -- inventory enabled flag
AND hoi.org_information_context = 'CLASS'
-- expiration check
AND org.organization_id = eclo.local_organization_id
AND (org.date_to >= SYSDATE OR org.date_to IS NULL)
-- inv security access check
AND (NOT EXISTS(SELECT 1 FROM ORG_ACCESS acc
WHERE acc.organization_id = eclo.local_organization_id )
OR EXISTS(SELECT 1 FROM ORG_ACCESS acc
WHERE acc.organization_id = eclo.local_organization_id
AND acc.responsibility_id = TO_NUMBER(fnd_profile.value('RESP_ID'))));
SELECT MP.organization_id
FROM HR_ORGANIZATION_UNITS HOU
, HR_ORGANIZATION_INFORMATION HOI1
, MTL_PARAMETERS MP
WHERE HOU.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND HOI1.ORG_INFORMATION1 = 'INV'
AND HOI1.ORG_INFORMATION2 = 'Y'
AND HOU.NAME = p_org_hierarchy_level;
SELECT change_notice, organization_id
FROM eng_engineering_changes
WHERE change_id = p_change_id;
SELECT eng_change_propagation_maps_s.nextval
INTO l_change_map_id
FROM DUAL;
INSERT INTO eng_change_propagation_maps(
change_propagation_map_id
, change_id
, request_id
, local_organization_id
, entity_name
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, entity_action_status
)
VALUES(
l_CHANGE_MAP_ID
, p_CHANGE_ID
, p_request_id
, l_org_code_list(l_org_count)
, Eng_Propagation_Log_Util.G_ENTITY_CHANGE--'ENG_CHANGE'
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, FND_GLOBAL.LOGIN_ID
, Eng_Propagation_Log_Util.G_PRP_PRC_STS_NOACTION
);
UPDATE eng_change_propagation_maps
SET request_id = p_request_id
, creation_date = SYSDATE
, created_by = FND_GLOBAL.USER_ID
, last_update_date = SYSDATE
, last_updated_by = FND_GLOBAL.USER_ID
, last_update_login = FND_GLOBAL.LOGIN_ID
WHERE change_propagation_map_id = l_change_map_id;