[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT concatenated_segments
INTO l_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT name
INTO l_org_name
FROM hr_all_organization_units_vl
WHERE organization_id = p_organization_id;
SELECT revision
INTO l_revision
FROM mtl_item_revisions_b
WHERE revision_id = p_item_revision_id;
SELECT pev_p.PROJ_ELEMENT_ID
FROM PA_PROJ_ELEMENT_VERSIONS pev_l,
PA_LIFECYCLE_USAGES plu,
PA_PROJ_ELEMENT_VERSIONS pev_p,
PA_PROJ_ELEMENTS PPE_P,
PA_PROJECT_STATUSES pc
WHERE pev_l.OBJECT_TYPE = 'PA_STRUCTURES'
AND pev_l.PROJ_ELEMENT_ID = cp_lifecycle_id
AND pev_l.PROJECT_ID = 0
AND plu.USAGE_TYPE = 'PRODUCTS'
AND plu.LIFECYCLE_ID = pev_l.PROJ_ELEMENT_ID
AND pev_l.ELEMENT_VERSION_ID = pev_p.PARENT_STRUCTURE_VERSION_ID
AND pev_p.PROJ_ELEMENT_ID = ppe_p.PROJ_ELEMENT_ID
AND ppe_p.PHASE_CODE = pc.PROJECT_STATUS_CODE
AND (pc.START_DATE_ACTIVE IS NULL OR pc.START_DATE_ACTIVE <= SYSDATE)
AND (pc.END_DATE_ACTIVE IS NULL OR pc.END_DATE_ACTIVE >= SYSDATE)
ORDER BY pev_p.DISPLAY_SEQUENCE;
SELECT rowid, revision, revision_id, lifecycle_id, current_phase_id, organization_id
FROM mtl_item_revisions_b item_rev
WHERE inventory_item_id = cp_item_id
AND EXISTS
(SELECT P2.ORGANIZATION_ID
FROM MTL_PARAMETERS P1,
MTL_PARAMETERS P2
WHERE P1.ORGANIZATION_ID = cp_org_id
AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
AND p2.organization_id = item_rev.organization_id
)
AND lifecycle_id IS NOT NULL
AND current_phase_id IS NOT NULL;
UPDATE mtl_item_revisions_b
SET lifecycle_id = l_item_rev_lifecycle_id,
current_phase_id = l_item_rev_def_phase_id,
last_update_date = l_sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE rowid = Item_Rev_Record.rowid;
code_debug(l_api_name||': Delete project associations ');
DELETE EGO_ITEM_PROJECTS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = Item_Rev_Record.organization_id
-- AND REVISION = Item_Rev_Record.revision
AND revision_id = Item_Rev_Record.revision_id
AND ASSOCIATION_TYPE = 'EGO_ITEM_PROJ_ASSOC_TYPE'
AND ASSOCIATION_CODE = 'LIFECYCLE_TRACKING';
SELECT rowid,
organization_id,
lifecycle_id,
current_phase_id,
inventory_item_status_code,
item_catalog_group_id,
description,
concatenated_segments
FROM mtl_system_items_kfv item -- changed for Business Event Enh.
WHERE inventory_item_id = cp_item_id
AND EXISTS
(SELECT P2.ORGANIZATION_ID
FROM MTL_PARAMETERS P1,
MTL_PARAMETERS P2
WHERE P1.ORGANIZATION_ID = cp_org_id
AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
AND P2.ORGANIZATION_ID = item.ORGANIZATION_ID
);
SELECT item_catalog_group_id,
lifecycle_id,
current_phase_id,
inventory_item_status_code
FROM mtl_system_items_b item
WHERE inventory_item_id = cp_item_id
AND organization_id = cp_org_id;
UPDATE mtl_system_items_b
SET lifecycle_id = DECODE(p_new_lifecycle_id,-1,NULL,p_new_lifecycle_id),
current_phase_id = DECODE(p_new_phase_id,-1,NULL,p_new_phase_id),
last_update_date = l_sysdate,
last_updated_by = l_user_id,
last_update_login = l_login_id
WHERE rowid = Item_Record.rowid;
SELECT control_level into l_control_level
from mtl_item_attributes
where attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
-- Bug 6241605: If the phase has been changed and the same status is selected same as old then
-- phase record should be inserted in the pending item status table.So commenting out the nex line.
--l_phase_rec := FALSE;
INSERT INTO mtl_pending_item_status(
inventory_item_id,
organization_id,
effective_date,
implemented_date,
pending_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
lifecycle_id,
phase_id,
status_code)
VALUES(
p_inventory_item_id,
Item_Record.organization_id,
l_sysdate,
l_implemented_date,
l_pending_flag,
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
DECODE(p_new_lifecycle_id,-1,NULL,p_new_lifecycle_id),
DECODE(p_new_phase_id,-1,NULL,p_new_phase_id),
p_new_item_status_code );
INV_ITEM_STATUS_PUB.Update_Pending_Status(
p_api_version => 1.0
,p_org_id => Item_Record.organization_id
,p_item_id => p_inventory_item_id
,p_init_msg_list => NULL
,p_commit => NULL
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
SELECT ORGANIZATION_CODE INTO l_org_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
,p_Organization_Id => p_Organization_Id
,p_organization_code => l_org_code
,p_Inventory_Item_Id => p_inventory_item_id
,p_item_number => l_item_number
,p_item_description => l_item_description
,x_msg_data => l_msg_data
,x_return_status => l_event_ret_status
);
,p_dml_type => 'UPDATE'
,p_inventory_item_id => p_inventory_item_id
,p_item_number => l_item_number
,p_item_description => l_item_description
,p_organization_id => p_Organization_Id
,p_organization_code => l_org_code );
SELECT rowid,
organization_id,
lifecycle_id,
current_phase_id,
inventory_item_status_code,
concatenated_segments, --added for business events.
description
FROM mtl_system_items_kfv
WHERE inventory_item_id = cp_item_id;
SELECT rowid,
revision, --3031284
lifecycle_id,
current_phase_id
FROM mtl_item_revisions_b
WHERE organization_id = cp_org_id
AND inventory_item_id = cp_item_id
AND lifecycle_id IS NOT NULL
FOR UPDATE OF lifecycle_id,current_phase_id;
SELECT organization_id
FROM mtl_parameters
WHERE organization_id = master_organization_id
and organization_id = NVL(cp_org_id, organization_id);
SELECT item_catalog_group_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 = cp_old_id)
MINUS
(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 = cp_new_id));
UPDATE mtl_system_items_b
SET item_catalog_group_id = DECODE(p_new_catalog_group_id,-1,NULL,p_new_catalog_group_id)
WHERE inventory_item_id = p_inventory_item_id;
DELETE ego_mtl_sy_items_ext_b ext
WHERE inventory_item_id = p_inventory_item_id
AND EXISTS (SELECT NULL
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = ext.attr_group_id
AND classification_code IN
(SELECT to_char(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_catalog_group_id))
AND NOT EXISTS (SELECT NULL
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = ext.attr_group_id
AND classification_code IN
(SELECT to_char(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_new_catalog_group_id));
DELETE ego_mtl_sy_items_ext_tl extl
WHERE inventory_item_id = p_inventory_item_id
AND EXISTS (SELECT NULL
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = extl.attr_group_id
AND classification_code IN
(SELECT to_char(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_catalog_group_id))
AND NOT EXISTS (SELECT NULL
FROM ego_obj_attr_grp_assocs_v
WHERE attr_group_id = extl.attr_group_id
AND classification_code IN
(SELECT to_char(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_new_catalog_group_id));
UPDATE ego_mtl_sy_items_ext_b
SET item_catalog_group_id = p_new_catalog_group_id
WHERE inventory_item_id = p_inventory_item_id;
UPDATE ego_mtl_sy_items_ext_tl
SET item_catalog_group_id = p_new_catalog_group_id
WHERE inventory_item_id = p_inventory_item_id;
DELETE fnd_attached_documents docs
WHERE pk2_value = to_char(p_inventory_item_id)
AND entity_name IN ('MTL_ITEM_REVISIONS','MTL_SYSTEM_ITEMS')
AND EXISTS (SELECT NULL
FROM ego_objtype_attach_cats
WHERE attach_category_id = docs.category_id
AND classification_code IN
(SELECT to_char(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_catalog_group_id))
AND NOT EXISTS (SELECT NULL
FROM ego_objtype_attach_cats
WHERE attach_category_id = docs.category_id
AND classification_code IN
(SELECT to_char(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_new_catalog_group_id));
UPDATE MTL_SYSTEM_ITEMS_B
SET APPROVAL_STATUS = p_new_approval_status
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT ORGANIZATION_CODE INTO l_org_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT CONCATENATED_SEGMENTS, DESCRIPTION
INTO l_item_number, l_item_desc
FROM mtl_system_items_kfv
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
,p_Inventory_Item_Id => p_inventory_item_id
,p_Organization_Id => p_Organization_Id
,p_organization_code => l_org_code
,p_item_number => l_item_number
,p_item_description => l_item_desc
,x_msg_data => l_msg_data
,x_return_status => l_event_ret_status
);
,p_dml_type => 'UPDATE'
,p_inventory_item_id => p_inventory_item_id
,p_organization_id => p_Organization_Id
,p_organization_code => l_org_code );
UPDATE mtl_item_revisions_b
SET lifecycle_id = NULL,
current_phase_id = NULL
WHERE rowid = revision_record.rowid;
DELETE EGO_ITEM_PROJECTS
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND REVISION = revision_record.revision
AND ORGANIZATION_ID = Item_Record.organization_id
AND ASSOCIATION_TYPE = 'EGO_ITEM_PROJ_ASSOC_TYPE'
AND ASSOCIATION_CODE = 'LIFECYCLE_TRACKING' ;
SELECT item_catalog_group_id, lifecycle_id, current_phase_id
FROM mtl_system_items_b
WHERE inventory_item_id = cp_item_id
AND organization_id = cp_org_id;
SELECT itm.item_catalog_group_id, NVL(rev.lifecycle_id,itm.lifecycle_id) lifecycle_id,
NVL(rev.current_phase_id,itm.current_phase_id) current_phase_id
FROM mtl_system_items_b itm, mtl_item_revisions_b rev
WHERE rev.inventory_item_id = cp_item_id
AND rev.organization_id = cp_org_id
AND rev.revision_id = cp_rev_id
AND itm.inventory_item_id = rev.inventory_item_id
AND itm.organization_id = rev.organization_id;
SELECT item_catalog_group_id
FROM mtl_item_catalog_groups
WHERE item_catalog_group_id = cp_curr_cc_id
AND item_catalog_group_id IN
(SELECT item_catalog_group_id
FROM mtl_item_catalog_groups
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = cp_new_cc_id
);
SELECT organization_id
FROM ego_item_projects item_proj
WHERE inventory_item_id = cp_item_id
AND EXISTS
(SELECT P2.ORGANIZATION_ID
FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
WHERE P1.ORGANIZATION_ID = cp_org_id
AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
AND P2.ORGANIZATION_ID = item_proj.organization_id
)
-- AND revision IS NULL
AND revision_id IS NULL
AND association_type = 'EGO_ITEM_PROJ_ASSOC_TYPE'
AND association_code = 'LIFECYCLE_TRACKING';
SELECT organization_id
FROM ego_item_projects rev_proj
WHERE inventory_item_id = cp_item_id
AND EXISTS
(SELECT P2.ORGANIZATION_ID
FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
WHERE P1.ORGANIZATION_ID = cp_org_id
AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
AND P2.ORGANIZATION_ID = rev_proj.organization_id
)
-- AND EXISTS
-- (SELECT 'X'
-- FROM mtl_item_revisions_b
-- WHERE revision_id = cp_rev_id
-- AND revision = rev_proj.revision
-- )
AND revision_id = cp_rev_id
AND association_type = 'EGO_ITEM_PROJ_ASSOC_TYPE'
AND association_code = 'LIFECYCLE_TRACKING';
SELECT organization_id
FROM mtl_pending_item_status mpis
WHERE inventory_item_id = cp_item_id
AND EXISTS
(SELECT P2.ORGANIZATION_ID
FROM MTL_PARAMETERS P1, MTL_PARAMETERS P2
WHERE P1.ORGANIZATION_ID = cp_org_id
AND P1.MASTER_ORGANIZATION_ID = P2.MASTER_ORGANIZATION_ID
AND P2.ORGANIZATION_ID = mpis.organization_id
)
AND nvl(revision_id, -1) = nvl(cp_rev_id, nvl(revision_id,-1))
AND pending_flag = 'Y'
AND implemented_date IS NULL
AND phase_id IS NOT NULL;
DELETE EGO_ITEM_PROJECTS proj
WHERE inventory_item_id = p_inventory_item_id
-- AND revision IS NULL
AND revision_id IS NULL
AND EXISTS
(SELECT 'X'
FROM mtl_parameters p1, mtl_parameters p2
WHERE p1.organization_id = p_organization_id
AND p1.master_organization_id = p2.master_organization_id
AND p2.organization_id = proj.organization_id
)
AND association_type = 'EGO_ITEM_PROJ_ASSOC_TYPE'
AND association_code = 'LIFECYCLE_TRACKING' ;
INSERT INTO mtl_pending_item_status(
inventory_item_id,
organization_id,
status_code,
revision_id,
effective_date,
implemented_date,
pending_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
lifecycle_id,
phase_id)
VALUES(
p_inventory_item_id,
p_organization_id,
p_item_status_code,
p_revision_id,
l_sysdate,
l_sysdate,
'N',
l_sysdate,
l_user_id,
l_sysdate,
l_user_id,
p_lifecycle_id,
p_lifecycle_phase_id);
SELECT inventory_item_id, organization_id, revision_id
FROM mtl_item_revisions_b item_rev
WHERE item_rev.inventory_item_id = cp_item_id
AND EXISTS
(SELECT 'X'
FROM mtl_parameters p1, mtl_parameters p2
WHERE p1.organization_id = cp_org_id
AND p1.master_organization_id = p2.master_organization_id
AND p2.organization_id = item_rev.organization_id
)
AND item_rev.lifecycle_id IS NULL
AND item_rev.current_phase_id IS NULL;
l_dynamic_sql := 'SELECT change_notice FROM eng_engineering_changes WHERE change_id = :1' ;
l_dyn_sql_pend_chg_rev := ' SELECT organization_id FROM eng_revised_items change ' ||
' WHERE change.revised_item_id = :1 ' ||
' AND change.organization_id = :2 ' ||
' AND change.change_notice <> :3 ' ||
' AND change.revised_item_sequence_id <> :4 ' ||
' AND change.current_item_revision_id = :5 ' ||
' AND change.status_type NOT IN (5, 6) ' ||
' AND ' ||
' ( change.NEW_ITEM_REVISION_ID IS NOT null ' || --this CO creates a revision
' OR EXISTS ' ||
-- 4177523 DM changes through bug 4045666
-- from CM side to store the pending doc changes in eng_attachment_changes
-- ' (SELECT ''X'' FROM eng_attachment_changes ENG, fnd_attached_documents doc ' ||
-- ' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
-- ' AND eng.attachment_id = doc.attached_document_id ' ||
-- ' AND doc.entity_name = ''MTL_ITEM_REVISIONS'' AND doc.pk1_value = to_char(change.organization_id) ' ||
-- ' AND doc.pk2_value = to_char(:6) AND doc.pk3_value = to_char(:7)) ' ||
' (SELECT ''X'' FROM eng_attachment_changes ENG ' ||
' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
' AND eng.entity_name = ''MTL_ITEM_REVISIONS'''||
' AND eng.pk1_value = to_char(change.organization_id) ' ||
' AND eng.pk2_value = to_char(:6) '||
' AND eng.pk3_value = to_char(:7)) ' ||
-- 4177523 DM changes through bug 4045666 added condition for structure changes
' OR EXISTS ' ||
' (SELECT ''X'' FROM bom_components_b bom_comp '||
' WHERE bom_comp.revised_item_sequence_id = change.revised_item_sequence_id '||
' AND bom_comp.bill_sequence_id = change.bill_sequence_id '||
' AND bom_comp.obj_name IS NULL '||
' AND bom_comp.implementation_date IS NULL) '||
' OR EXISTS ' ||
' (SELECT ''X'' FROM ego_items_attrs_changes_b attr_chg, ego_obj_ag_assocs_b assoc, fnd_objects obj ' ||
' WHERE attr_chg.change_line_id = change.revised_item_sequence_id ' ||
-- 3710038 check for the attributes in the hierarchy
-- AND to_char(attr_chg.item_catalog_group_id) = assoc.classification_code
' AND assoc.classification_code IN ( ' ||
' SELECT TO_CHAR(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 = attr_chg.item_catalog_group_id ) ' ||
' AND attr_chg.attr_group_id = assoc.attr_group_id ' ||
' AND assoc.data_level = ''ITEM_REVISION_LEVEL'' AND assoc.enabled_flag = ''Y''' ||
' AND assoc.object_id = obj.object_id AND obj.obj_name = ''EGO_ITEM'') ' ||
' OR EXISTS ' || --this CO has Related Doc Change
' (SELECT NULL ' ||
' FROM eng_relationship_changes ' ||
' WHERE ENTITY_ID = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id ' ||
' AND ENTITY_NAME=''ITEM'' ' ||
' AND FROM_ENTITY_NAME = ''EGO_ITEM_REVISION'' ) )';
l_dynamic_sql := 'SELECT organization_id ' ||
'FROM eng_revised_items change ' ||
'WHERE revised_item_id = :1' ||
'AND change_notice <> :2'||
'AND revised_item_sequence_id <> :3' ||
'AND EXISTS ' ||
' (SELECT ''X'' FROM mtl_parameters p1, mtl_parameters p2 ' ||
' WHERE p1.organization_id = :4 ' ||
' AND p1.master_organization_id = p2.master_organization_id ' ||
' AND p2.organization_id = change.organization_id )' ||
' AND status_type NOT IN (5,6) ' ||
' AND ' ||
' ( EXISTS ' ||
' (SELECT ''X'' FROM ego_mfg_part_num_chgs ' ||
' WHERE change_line_id = change.revised_item_sequence_id ) ' ||
' OR EXISTS ' ||
' (SELECT ''X'' FROM eng_attachment_changes ' ||
' WHERE revised_item_sequence_id = change.revised_item_sequence_id) ' ||
' OR EXISTS ' ||
' (SELECT ''X'' FROM ego_items_attrs_changes_b attr_chg ' ||
' WHERE change_line_id = change.revised_item_sequence_id ) ' ||
' OR EXISTS ' || --this CO has Operational Attribute Change
' (SELECT NULL ' ||
' FROM ego_mtl_sy_items_chg_b ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id) ' ||
' OR EXISTS ' || --this CO has GTIN Single Change
' (SELECT NULL ' ||
' FROM ego_gtn_attr_chg_b ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id) ' ||
' OR EXISTS ' || --this CO has GTIN Multi Change
' (SELECT NULL ' ||
' FROM ego_gtn_mul_attr_chg_b ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id) ' ||
' OR EXISTS ' || --this CO has Related Doc Change
' (SELECT NULL ' ||
' FROM eng_relationship_changes ' ||
' WHERE ENTITY_ID = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id ' ||
' AND ENTITY_NAME=''ITEM'') ' ||
' OR EXISTS ' || --this CO has Structure Changes
' (SELECT NULL ' ||
' FROM bom_components_b ' ||
' WHERE revised_item_sequence_id = change.revised_item_sequence_id) ) ';
l_dynamic_sql := ' SELECT organization_id FROM eng_revised_items change ' ||
' WHERE change.revised_item_id = :1 ' ||
' AND change.change_notice <> :2 ' ||
' AND change.revised_item_sequence_id <> :3 ' ||
' AND EXISTS ' ||
' (SELECT ''X'' FROM mtl_parameters p1, mtl_parameters p2 ' ||
' WHERE p1.organization_id = :4 ' ||
' AND p1.master_organization_id = p2.master_organization_id ' ||
' AND p2.organization_id = change.organization_id ' ||
' ) ' ||
' AND change.status_type NOT IN (5, 6 ) ' ||
' AND ( ' ||
' EXISTS (SELECT ''X'' FROM ego_mfg_part_num_chgs ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' ) OR EXISTS ' ||
-- 4177523 DM changes through bug 4045666
-- from CM side to store the pending doc changes in eng_attachment_changes
-- ' (SELECT ''X'' FROM eng_attachment_changes ENG, fnd_attached_documents doc ' ||
-- ' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
-- ' AND eng.attachment_id = doc.attached_document_id ' ||
-- ' AND doc.entity_name = ''MTL_SYSTEM_ITEMS'' ' ||
-- ' AND doc.pk1_value = to_char(change.organization_id) ' ||
-- ' AND doc.pk2_value = to_char(:5) ' ||
' (SELECT ''X'' FROM eng_attachment_changes ENG ' ||
' WHERE eng.revised_item_sequence_id = change.revised_item_sequence_id ' ||
' AND eng.entity_name = ''MTL_SYSTEM_ITEMS'' ' ||
' AND eng.pk1_value = to_char(change.organization_id) ' ||
' AND eng.pk2_value = to_char(:5) ' ||
' ) OR EXISTS ' ||
' (SELECT ''X'' FROM ego_items_attrs_changes_b attr_chg, ego_obj_ag_assocs_b assoc, fnd_objects obj ' ||
' WHERE attr_chg.change_line_id = change.revised_item_sequence_id ' ||
-- 3710038 check for the attributes in the hierarchy
-- AND to_char(attr_chg.item_catalog_group_id) = assoc.classification_code
' AND assoc.classification_code IN (' ||
' SELECT TO_CHAR(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 = attr_chg.item_catalog_group_id ) ' ||
' AND attr_chg.attr_group_id = assoc.attr_group_id ' ||
' AND assoc.data_level = ''ITEM_LEVEL'' AND assoc.object_id = obj.object_id ' ||
' AND obj.obj_name = ''EGO_ITEM'' AND assoc.enabled_flag = ''Y'' ) ' ||
' OR EXISTS ' || --this CO has Operational Attribute Change
' (SELECT NULL ' ||
' FROM ego_mtl_sy_items_chg_b ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id) ' ||
' OR EXISTS ' || --this CO has GTIN Single Change
' (SELECT NULL ' ||
' FROM ego_gtn_attr_chg_b ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id) ' ||
' OR EXISTS ' || --this CO has GTIN Multi Change
' (SELECT NULL ' ||
' FROM ego_gtn_mul_attr_chg_b ' ||
' WHERE change_line_id = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id) ' ||
' OR EXISTS ' || --this CO has Related Doc Change
' (SELECT NULL ' ||
' FROM eng_relationship_changes ' ||
' WHERE ENTITY_ID = change.revised_item_sequence_id ' ||
' AND change_id = change.change_id ' ||
' AND ENTITY_NAME=''ITEM'') ' ||
' OR EXISTS ' || --this CO has Structure Changes
' (SELECT NULL ' ||
' FROM bom_components_b ' ||
' WHERE revised_item_sequence_id = change.revised_item_sequence_id) ) ';