[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
INTO x_organization_id
from mtl_parameters
where organization_id = x_organization_id;
SELECT organization_id
INTO x_organization_id
from mtl_parameters
where organization_code = p_organization_code;
SELECT itm.inventory_item_id, itm.approval_status, itm_num.concatenated_segments
INTO x_inventory_item_id, x_approval_status, x_item_number
FROM mtl_system_items_b itm, mtl_system_items_b_kfv itm_num
WHERE itm.inventory_item_id = x_inventory_item_id
AND itm.organization_id = p_organization_id
AND itm_num.inventory_item_id = itm.inventory_item_id
AND itm_num.organization_id = itm.organization_id;
SELECT itm.inventory_item_id, itm.approval_status, itm_num.concatenated_segments
INTO x_inventory_item_id, x_approval_status, x_item_number
FROM mtl_system_items_b itm, mtl_system_items_b_kfv itm_num
WHERE itm_num.organization_id = p_organization_id
AND itm_num.concatenated_segments = x_item_number
AND itm.inventory_item_id = itm_num.inventory_item_id
AND itm.organization_id = itm_num.organization_id;
SELECT revision_id, revision
INTO x_revision_id, x_revision
FROM mtl_item_revisions_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision_id = x_revision_id;
SELECT revision_id, revision
INTO x_revision_id, x_revision
FROM mtl_item_revisions_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id
AND revision = x_revision;
SELECT party_id, party_name
INTO x_party_id, x_party_name
FROM hz_parties
WHERE party_id = x_party_id
AND party_type = l_hz_party_type;
SELECT party_id, party_name
INTO x_party_id, x_party_name
FROM hz_parties
WHERE party_name = x_party_name
AND party_type = l_hz_party_type;
SELECT instance_set_id
INTO x_instance_set_id
FROM fnd_object_instance_sets
WHERE instance_set_id = x_instance_set_id
AND object_id = (SELECT object_id FROM fnd_objects WHERE obj_name = G_EGO_ITEM);
SELECT instance_set_id
INTO x_instance_set_id
FROM fnd_object_instance_sets_vl
WHERE display_name = p_set_disp_name
AND object_id = (SELECT object_id FROM fnd_objects WHERE obj_name = G_EGO_ITEM);
SELECT menu_id, menu_name
INTO x_menu_id, x_menu_name
FROM fnd_menus
WHERE menu_id = x_menu_id
AND type = p_menu_type;
SELECT menu_id, menu_name
INTO x_menu_id, x_menu_name
FROM fnd_menus_vl
WHERE user_menu_name = p_user_menu_name
AND type = p_menu_type;
SELECT name
INTO l_dummy_char
FROM PA_EGO_LIFECYCLES_V
WHERE proj_element_id = p_proj_element_id;
SELECT name
INTO l_dummy_char
FROM PA_EGO_PHASES_V
WHERE proj_element_id = p_proj_element_id;
l_policy_check_sql:= 'SELECT ''Y'''||
' FROM MTL_SYSTEM_ITEMS_B MSI,'||
' MTL_ITEM_REVISIONS_B MIR,'||
' ENG_CHANGE_POLICIES_V ECP'||
' WHERE '||
' MSI.INVENTORY_ITEM_ID = :1'||
' AND MSI.ORGANIZATION_ID = :2'||
' AND MSI.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID'||
' AND MSI.ORGANIZATION_ID = MIR.ORGANIZATION_ID '||
' AND MSI.LIFECYCLE_ID IS NOT NULL'||
' AND (MSI.APPROVAL_STATUS IS NULL OR MSI.APPROVAL_STATUS =''A'') '||
' AND 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 = :3 '||
' AND olc.lifecycle_id = MSI.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 = MSI.item_catalog_group_id '||
' )'||
' AND ECP.ATTRIBUTE_OBJECT_NAME = ''EGO_CATALOG_GROUP'' '||
' AND ECP.ATTRIBUTE_CODE = ''ATTRIBUTE_GROUP'' '||
' AND ECP.POLICY_OBJECT_NAME =''CATALOG_LIFECYCLE_PHASE'' '||
' AND ECP.POLICY_CHAR_VALUE IS NOT NULL '||
' AND ECP.POLICY_CHAR_VALUE IN (''CHANGE_ORDER_REQUIRED'' ,''NOT_ALLOWED'')'||
' AND ECP.ATTRIBUTE_NUMBER_VALUE =:4 '||
' AND ( '||
' ( (:5 IS NOT NULL '||
' AND MIR.REVISION_ID = :6)'||
' AND ECP.POLICY_OBJECT_PK2_VALUE = NVL(MIR.LIFECYCLE_ID, MSI.LIFECYCLE_ID) '||
' AND ECP.POLICY_OBJECT_PK3_VALUE = NVL(MIR.CURRENT_PHASE_ID, MSI.CURRENT_PHASE_ID) '||
' ) '||
' OR '||
' ( ECP.POLICY_OBJECT_PK2_VALUE = MSI.LIFECYCLE_ID '||
' AND ECP.POLICY_OBJECT_PK3_VALUE = MSI.CURRENT_PHASE_ID '||
' ))';
l_delete_sql VARCHAR2(200);
SELECT OBJECT_ID into l_object_id FROM fnd_objects WHERE obj_name ='EGO_ITEM';
SELECT segment1 INTO l_item_number FROM MTL_SYSTEM_ITEMS_B WHERE inventory_item_id=p_inventory_item_id;
SELECT PEP.NAME
INTO l_current_life_cycle
FROM MTL_SYSTEM_ITEMS_B MSI
,PA_EGO_LIFECYCLES_V PEP
WHERE MSI.INVENTORY_ITEM_ID =p_inventory_item_id
AND MSI.ORGANIZATION_ID = p_organization_id
AND MSI.LIFECYCLE_ID = PEP.PROJ_ELEMENT_ID;
SELECT PEP.NAME
INTO l_current_phase_name
FROM MTL_SYSTEM_ITEMS_B MSI
,PA_EGO_PHASES_V PEP
WHERE MSI.INVENTORY_ITEM_ID = p_inventory_item_id
AND MSI.ORGANIZATION_ID = p_organization_id
AND MSI.CURRENT_PHASE_ID = PEP.PROJ_ELEMENT_ID;
SELECT item_catalog_group_id
INTO l_policy_cat_id
FROM (SELECT 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, mtl_system_items_b MSI
WHERE olc.object_id = l_object_id
AND olc.lifecycle_id = MSI.lifecycle_id
AND MSI.inventory_item_id = p_inventory_item_id
AND MSI.organization_id = p_organization_id
AND olc.object_classification_code = ic.item_catalog_group_id
)
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id
=(SELECT item_catalog_group_id
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id=p_inventory_item_id
)
) CAT_HIER
WHERE ROWNUM = 1;
SELECT concatenated_segments
INTO l_current_category_name
FROM MTL_ITEM_CATALOG_GROUPS_KFV
WHERE ITEM_CATALOG_GROUP_ID = l_policy_cat_id;
l_token_table.DELETE();
SELECT organization_code
,master_organization_id
FROM mtl_parameters
WHERE organization_id = cp_org_id;
SELECT S.CATEGORY_SET_ID,
S.CATEGORY_ID
FROM MTL_ITEM_CATEGORIES S
WHERE S.INVENTORY_ITEM_ID = CP_ITEM_ID
AND S.ORGANIZATION_ID = CP_ORG_ID
AND EXISTS
(SELECT 'X'
FROM MTL_DEFAULT_CATEGORY_SETS D
WHERE D.CATEGORY_SET_ID = S.CATEGORY_SET_ID
AND (D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.MRP_PLANNING_CODE, 6, 0, 3 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.ENG_ITEM_FLAG, 'Y', 6, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( NVL(G_Item_Rec.EAM_ITEM_TYPE, 0), 0, 0, 9 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 10, 'WARRANTY' , 10, 'SUBSCRIPTION' , 10, 'USAGE' , 10, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 4, 'WARRANTY' , 4, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )));
CURSOR DEFAULT_CAT_ASSIGN_UPDATE ( CP_ITEM_ID NUMBER
,CP_ORG_ID NUMBER ) IS
SELECT S.CATEGORY_SET_ID,
S.CATEGORY_ID
FROM MTL_ITEM_CATEGORIES S,
MTL_CATEGORY_SETS_B D
WHERE S.INVENTORY_ITEM_ID = CP_ITEM_ID
AND S.CATEGORY_SET_ID = D.CATEGORY_SET_ID
AND S.ORGANIZATION_ID = CP_ORG_ID
AND (D.CONTROL_LEVEL = 1
OR EXISTS
(SELECT 'X'
FROM MTL_DEFAULT_CATEGORY_SETS D
WHERE D.CATEGORY_SET_ID = S.CATEGORY_SET_ID
AND (D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.MRP_PLANNING_CODE, 6, 0, 3 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.ENG_ITEM_FLAG, 'Y', 6, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( NVL(G_Item_Rec.EAM_ITEM_TYPE, 0), 0, 0, 9 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 10, 'WARRANTY' , 10, 'SUBSCRIPTION' , 10, 'USAGE' , 10, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CONTRACT_ITEM_TYPE_CODE, 'SERVICE' , 4, 'WARRANTY' , 4, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
OR D.FUNCTIONAL_AREA_ID = DECODE( G_Item_Rec.INTERNAL_ORDER_FLAG, 'Y', 11, 0 ))
)) ;
SELECT REVISION_ID
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = CP_ITEM_ID
AND ORGANIZATION_ID = CP_ORG_ID ;
l_Error_tbl.DELETE;
l_Item_rec_in.ALLOW_ITEM_DESC_UPDATE_FLAG:= G_Item_Rec.ALLOW_ITEM_DESC_UPDATE_FLAG;
EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT
,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
,p_organization_code => l_org_code_rec.ORGANIZATION_CODE
,p_inventory_item_id => l_Item_rec_out.INVENTORY_ITEM_ID
,p_item_number => l_Item_rec_in.ITEM_NUMBER
,p_item_description => l_Item_rec_in.DESCRIPTION
,x_msg_data => l_msg_data
,x_return_status => l_event_return_status);
ELSIF ( G_Item_Rec.Transaction_Type = 'UPDATE' ) THEN
-----------------------------------------------------------------------
-- added for bug 7431714
SELECT item_catalog_group_id
INTO l_curr_icc_id
FROM MTL_SYSTEM_ITEMS_B
WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
AND organization_id = l_Item_rec_in.ORGANIZATION_ID;
DELETE
FROM ego_mtl_sy_items_ext_b
WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
AND attr_group_id NOT IN
(SELECT ATTR_GROUP_ID
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V AGV,
FND_OBJECTS FO
WHERE AGV.OBJECT_ID = FO.OBJECT_ID
AND AGV.OBJECT_NAME ='EGO_ITEM'
AND AGV.CLASSIFICATION_CODE IS NOT NULL
AND AGV.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 = l_Item_rec_in.ITEM_CATALOG_GROUP_ID
)
UNION ALL
SELECT ATTR_GROUP_ID
FROM EGO_ATTR_GROUPS_V
WHERE APPLICATION_ID = 431
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND (ATTR_GROUP_NAME = 'ItemDetailDesc'
OR ATTR_GROUP_NAME = 'ItemDetailImage')
);
DELETE
FROM ego_mtl_sy_items_ext_tl
WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
AND attr_group_id NOT IN
(SELECT ATTR_GROUP_ID
FROM EGO_OBJ_ATTR_GRP_ASSOCS_V AGV,
FND_OBJECTS FO
WHERE AGV.OBJECT_ID = FO.OBJECT_ID
AND AGV.OBJECT_NAME ='EGO_ITEM'
AND AGV.CLASSIFICATION_CODE IS NOT NULL
AND AGV.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 = l_Item_rec_in.ITEM_CATALOG_GROUP_ID
)
UNION ALL
SELECT ATTR_GROUP_ID
FROM EGO_ATTR_GROUPS_V
WHERE APPLICATION_ID = 431
AND ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND (ATTR_GROUP_NAME = 'ItemDetailDesc'
OR ATTR_GROUP_NAME = 'ItemDetailImage')
);
INV_Item_GRP.Update_Item
(
p_commit => p_commit
, p_Item_rec => l_Item_rec_in
, p_Revision_rec => l_revision_rec
, p_Template_Id => l_Template_Id
, p_Template_Name => l_Template_Name
, x_Item_rec => l_Item_rec_out
, x_return_status => l_return_status
, x_Error_tbl => l_Error_tbl
);
SELECT CONCATENATED_SEGMENTS, DESCRIPTION
INTO l_item_number, l_item_desc
FROM MTL_SYSTEM_ITEMS_KFV
WHERE inventory_item_id = l_Item_rec_in.INVENTORY_ITEM_ID
AND organization_id = l_Item_rec_in.ORGANIZATION_ID;
FOR DEFAULT_CAT_ASSIGN_REC IN DEFAULT_CAT_ASSIGN_UPDATE(CP_ITEM_ID => l_Item_rec_out.INVENTORY_ITEM_ID ,CP_ORG_ID => l_Item_rec_out.ORGANIZATION_ID)
LOOP
l_cat_match := FND_API.G_FALSE;
EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT
,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
,p_organization_code => l_org_code_rec.ORGANIZATION_CODE
,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
,p_item_number => l_item_number
,p_item_description => l_item_desc
,x_msg_data => l_msg_data
,x_return_status => l_event_return_status);
SELECT REVISION_ID INTO l_revision_id_out
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = l_Item_rec_out.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = l_Item_rec_in.ORGANIZATION_ID
AND REVISION = l_revision_rec.Revision_Code ;
/*Removed the call for updates to child orgs
Will be raising events for explicit actions only*/
--Call ICX APIs
BEGIN
INV_ITEM_EVENTS_PVT.Invoke_ICX_APIs(
p_entity_type => 'ITEM'
,p_dml_type => 'UPDATE'
,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
,p_item_number => l_item_number
,p_item_description => l_item_desc
,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
,p_organization_code => l_org_code_rec.ORGANIZATION_CODE );
EGO_ITEM_PUB.Update_Item_Attr_Ext(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => l_init_msg_list,
P_COMMIT => P_COMMIT,
P_INVENTORY_ITEM_ID => l_Item_rec_in.INVENTORY_ITEM_ID,
P_ITEM_CATALOG_GROUP_ID => l_Item_rec_in.ITEM_CATALOG_GROUP_ID,
x_return_status => l_return_status,
X_MSG_COUNT => l_msg_count);
SELECT item.concatenated_segments
,item.description
,item.organization_id
,rev.revision_id
FROM mtl_system_items_b_kfv item
,mtl_item_revisions_b rev
WHERE item.inventory_item_id = cp_item_id
AND item.organization_id = cp_org_id
AND rev.inventory_item_id = cp_item_id
AND rev.organization_id = cp_org_id;
l_Error_tbl.DELETE;
SELECT MASTER_ORGANIZATION_ID INTO l_master_org
FROM mtl_parameters
WHERE ORGANIZATION_ID = l_Item_rec_in.ORGANIZATION_ID;
UPDATE MTL_SYSTEM_ITEMS_TL ASSIGNEE
SET (ASSIGNEE.DESCRIPTION, ASSIGNEE.LONG_DESCRIPTION, ASSIGNEE.SOURCE_LANG)
= (SELECT DESCRIPTION, LONG_DESCRIPTION, SOURCE_LANG
FROM MTL_SYSTEM_ITEMS_TL MASTER
WHERE INVENTORY_ITEM_ID = l_Item_rec_in.INVENTORY_ITEM_ID
AND ORGANIZATION_ID =L_MASTER_ORG
AND MASTER."LANGUAGE" = ASSIGNEE."LANGUAGE")
WHERE INVENTORY_ITEM_ID = l_Item_rec_in.INVENTORY_ITEM_ID
AND ORGANIZATION_ID = l_Item_rec_in.ORGANIZATION_ID;
EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event(
p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT
,p_organization_id => l_Item_rec_in.ORGANIZATION_ID
,p_organization_code => l_Item_rec_in.ORGANIZATION_CODE
,p_inventory_item_id => l_Item_rec_in.INVENTORY_ITEM_ID
,p_item_number => l_item_rev_rec.concatenated_segments
,p_item_description => l_item_rev_rec.description
,x_msg_data => l_msg_data
,x_return_status => l_event_return_status);
SELECT DISPLAY_NAME
INTO l_label
FROM EGO_VALUE_SET_VALUES_V
WHERE VALUE_SET_NAME = 'DescSource'
AND INTERNAL_NAME = 'D';
SELECT ATTR_GROUP_ID
INTO l_attr_group_id
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE APPLICATION_ID = 431
AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc';
/*SELECT DISPLAY_NAME
INTO l_label
FROM EGO_VALUE_SET_VALUES_V
WHERE VALUE_SET_NAME = 'DescSource'
AND INTERNAL_NAME = 'D';*/
INSERT INTO EGO_MTL_SY_ITEMS_EXT_B
(
EXTENSION_ID
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,ITEM_CATALOG_GROUP_ID
,ATTR_GROUP_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,N_EXT_ATTR1
,C_EXT_ATTR1
-- ,C_EXT_ATTR2 -- commented out as a part of Bug 4906499
,C_EXT_ATTR4
,DATA_LEVEL_ID --Added for bug 6155995
)
SELECT EGO_EXTFWK_S.NEXTVAL
,MTL.ORGANIZATION_ID
,MTL.INVENTORY_ITEM_ID
,NVL(MTL.ITEM_CATALOG_GROUP_ID, -1)
,l_attr_group_id
,1
,SYSDATE
,1
,SYSDATE
,10
,'D'
-- ,l_label --commented out as a part of Bug 4906499
,'AsText'
,43102 --Added for bug 6155995
FROM MTL_SYSTEM_ITEMS_INTERFACE MTL
WHERE MTL.SET_PROCESS_ID = p_set_process_id
AND MTL.PROCESS_FLAG = 4
AND MTL.TRANSACTION_TYPE = 'CREATE';
INSERT INTO EGO_MTL_SY_ITEMS_EXT_TL
(
EXTENSION_ID
,ORGANIZATION_ID
,INVENTORY_ITEM_ID
,ITEM_CATALOG_GROUP_ID
,ATTR_GROUP_ID
,SOURCE_LANG
,LANGUAGE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,TL_EXT_ATTR2 -- Added as a part of Bug 4906499
,DATA_LEVEL_ID --Added for bug 6155995
)
SELECT EXT.EXTENSION_ID
,EXT.ORGANIZATION_ID
,EXT.INVENTORY_ITEM_ID
,EXT.ITEM_CATALOG_GROUP_ID
,EXT.ATTR_GROUP_ID
,USERENV('LANG')
,L.LANGUAGE_CODE
,EXT.CREATED_BY
,EXT.CREATION_DATE
,EXT.LAST_UPDATED_BY
,EXT.LAST_UPDATE_DATE
,NVL(M.MESSAGE_TEXT,'Long Description')
,43102 --Added for bug 6155995
FROM MTL_SYSTEM_ITEMS_INTERFACE MTL
,EGO_MTL_SY_ITEMS_EXT_B EXT
,FND_LANGUAGES L
,FND_NEW_MESSAGES M
WHERE MTL.SET_PROCESS_ID = p_set_process_id
AND MTL.PROCESS_FLAG = 4
AND MTL.TRANSACTION_TYPE = 'CREATE'
AND MTL.ORGANIZATION_ID = EXT.ORGANIZATION_ID
AND MTL.INVENTORY_ITEM_ID = EXT.INVENTORY_ITEM_ID
AND EXT.ATTR_GROUP_ID = l_attr_group_id
AND L.INSTALLED_FLAG IN ('I', 'B')
AND MESSAGE_NAME = 'EGO_ITEM_LONG_DESCRIPTION'
AND M.LANGUAGE_CODE = L.LANGUAGE_CODE
AND M.APPLICATION_ID = 431;
SELECT ITEM_CATALOG_GROUP_ID
,PARENT_CATALOG_GROUP_ID
FROM MTL_ITEM_CATALOG_GROUPS_B
CONNECT BY PRIOR PARENT_CATALOG_GROUP_ID = ITEM_CATALOG_GROUP_ID
START WITH ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
SELECT CONCATENATED_SEGMENTS
INTO l_token_table(1).TOKEN_VALUE
FROM MTL_ITEM_CATALOG_GROUPS_KFV
WHERE ITEM_CATALOG_GROUP_ID = p_catalog_group_id;
SELECT 'HZ_PARTY:'||TO_CHAR(PARTY_ID)
INTO l_party_id
FROM EGO_USER_V
WHERE USER_NAME = FND_GLOBAL.USER_NAME;
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 ORGANIZATION_CODE
INTO l_org_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT NVL(ITEM_CATALOG_GROUP_ID, -1)
INTO l_item_catalog_group_id
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
l_select_sql VARCHAR2(32767);
SELECT party_id, party_name
FROM ego_user_v
WHERE user_id = cp_user_id;
SELECT party_name
FROM hz_parties
WHERE party_id = cp_party_id;
l_select_sql :=
' SELECT 1 '||
' FROM MTL_SYSTEM_ITEMS MSIB '||
' WHERE MSIB.INVENTORY_ITEM_ID = :1'||
' AND MSIB.ORGANIZATION_ID = :2'||
' AND ' ||l_sec_predicate;
code_debug(' Priv Query '||l_select_sql);
OPEN c_priv_cursor FOR l_select_sql USING p_inventory_item_id,p_organization_id;
SELECT concatenated_segments
INTO l_dummy_char
FROM mtl_system_items_b_kfv
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id;
SELECT name
INTO l_dummy_char
FROM hr_all_organization_units_vl
WHERE organization_id = p_organization_id;
,EGO_ITEM_PUB.G_TTYPE_DELETE
,EGO_ITEM_PUB.G_TTYPE_UPDATE
)
OR
(p_transaction_type = EGO_ITEM_PUB.G_TTYPE_CREATE
AND( (p_role_name IS NULL AND p_role_id IS NULL)
OR
p_party_type IS NULL
OR
p_party_type NOT IN
(EGO_ITEM_PUB.G_USER_PARTY_TYPE
,EGO_ITEM_PUB.G_GROUP_PARTY_TYPE
,EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE
,EGO_ITEM_PUB.G_ALL_USERS_PARTY_TYPE
)
OR
(p_party_type IN (EGO_ITEM_PUB.G_GROUP_PARTY_TYPE
,EGO_ITEM_PUB.G_COMPANY_PARTY_TYPE
,EGO_ITEM_PUB.G_USER_PARTY_TYPE)
AND p_party_name IS NULL
AND p_party_id IS NULL
)
OR
p_instance_type IS NULL
OR
p_instance_type NOT IN
(EGO_ITEM_PUB.G_INSTANCE_TYPE_SET
,EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE
)
OR
(p_instance_type = EGO_ITEM_PUB.G_INSTANCE_TYPE_SET
AND
( (p_instance_set_id IS NULL AND p_instance_set_name IS NULL)
OR
p_inventory_item_id IS NOT NULL
OR
p_organization_id IS NOT NULL
)
)
OR
(p_instance_type = EGO_ITEM_PUB.G_INSTANCE_TYPE_INSTANCE
AND
( (p_inventory_item_id IS NULL AND p_item_number IS NULL)
OR
(p_organization_id IS NULL AND p_organization_code IS NULL)
OR
(p_instance_set_id IS NOT NULL OR p_instance_set_name IS NOT NULL)
)
)
)
)
OR
(p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE
AND (x_grant_guid IS NULL)
)
OR
(p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE
AND (x_grant_guid IS NULL)
)
) THEN
--
-- inalid parameters passed
--
code_debug (l_api_version ||' invalid parameters passed ');
IF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_DELETE
,EGO_ITEM_PUB.G_TTYPE_UPDATE) THEN
BEGIN
SELECT instance_type, instance_set_id, instance_pk1_value,
instance_pk2_value, start_date, end_date
INTO l_instance_type, l_instance_set_id, l_pk1_value,
l_pk2_value, l_start_date, l_end_date
FROM fnd_grants
WHERE grant_guid = x_grant_guid
AND object_id = (SELECT object_id FROM fnd_objects WHERE obj_name = G_EGO_ITEM);
IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
fnd_message.set_name (G_APP_NAME, 'EGO_NO_REC_UPDATE');
fnd_message.set_name (G_APP_NAME, 'EGO_NO_REC_DELETE');
code_debug(l_api_name ||' user does not have privilege to update the roles on item');
code_debug(l_api_name ||' user has privilege to update the roles on item');
code_debug(l_api_name ||' user does not have function privilege to update roles in instance set');
code_debug(l_api_name ||' user has function privilege to update the roles in instance set');
IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_DELETE THEN
--
-- delete the grant given
--
code_debug(l_api_name||' calling EGO_SECURITY_PUB.revoke_grant ' );
ELSIF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
--
-- update the grant given
--
IF date_check (p_start_date => l_sysdate
,p_end_date => l_end_date
,p_validation_type => G_GT_VAL
) THEN
code_debug (l_api_version ||' grant is already end dated ');
code_debug (l_api_version ||' overlap grant found for update ');
code_debug(l_api_name ||' user does not have function privilege to update the roles on item');
SELECT item_catalog_group_id, lifecycle_id,
current_phase_id, inventory_item_status_code
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
SELECT itm.item_catalog_group_id, rev.lifecycle_id, rev.current_phase_id, itm.inventory_item_status_code
FROM mtl_system_items_b itm, mtl_item_revisions_b rev
WHERE itm.inventory_item_id = cp_inventory_item_id
AND itm.organization_id = cp_organization_id
AND rev.inventory_item_id = itm.inventory_item_id
AND rev.organization_id = itm.organization_id --changed = rev.organization_id to itm.organization_id bug 7324207
AND rev.revision_id = cp_revision_id; --changed =rev.revision_id to cp_revision_id bug 7324207
SELECT p1.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P1
WHERE P1.PROJ_ELEMENT_ID = cp_phase_id;
SELECT p1.proj_element_id, p1.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P1, PA_PROJ_ELEMENT_VERSIONS P2
WHERE P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
AND P2.PROJ_ELEMENT_ID = cp_lifecycle_id
AND P1.display_sequence >
(SELECT P3.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P3
WHERE P3.PROJ_ELEMENT_ID = cp_phase_id
AND P3.PARENT_STRUCTURE_VERSION_ID = P1.parent_structure_version_id
)
ORDER BY p1.DISPLAY_SEQUENCE ASC;
SELECT p1.proj_element_id, p1.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P1, PA_PROJ_ELEMENT_VERSIONS P2
WHERE P1.PARENT_STRUCTURE_VERSION_ID = P2.ELEMENT_VERSION_ID
AND P2.PROJ_ELEMENT_ID = cp_lifecycle_id
AND P1.display_sequence <
(SELECT P3.display_sequence
FROM PA_PROJ_ELEMENT_VERSIONS P3
WHERE P3.PROJ_ELEMENT_ID = cp_phase_id
AND P3.PARENT_STRUCTURE_VERSION_ID = P1.parent_structure_version_id
)
ORDER BY p1.DISPLAY_SEQUENCE DESC;
p_transaction_type NOT IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
,EGO_ITEM_PUB.G_TTYPE_DELETE
,EGO_ITEM_PUB.G_TTYPE_PROMOTE
,EGO_ITEM_PUB.G_TTYPE_DEMOTE
,EGO_ITEM_PUB.G_TTYPE_CHANGE_STATUS
)
OR
( p_transaction_type NOT IN (EGO_ITEM_PUB.G_TTYPE_UPDATE
,EGO_ITEM_PUB.G_TTYPE_DELETE)
AND
(p_revision IS NOT NULL OR p_revision_id IS NOT NULL)
AND
p_status IS NOT NULL
)
OR
(p_inventory_item_id IS NULL AND p_item_number IS NULL)
OR
(p_organization_id IS NULL AND p_organization_code IS NULL)
) THEN
--
-- inalid parameters passed
--
code_debug (l_api_version ||' invalid parameters passed ');
IF p_transaction_type = EGO_ITEM_PUB.G_TTYPE_UPDATE THEN
l_effective_date := NVL(p_new_effective_date,l_sysdate);
SELECT name
INTO l_org_name
FROM hr_all_organization_units_vl
WHERE organization_id = l_organization_id;
SELECT concatenated_segments
INTO l_dummy_char
FROM MTL_ITEM_CATALOG_GROUPS_KFV
WHERE ITEM_CATALOG_GROUP_ID = (
SELECT item_catalog_group_id
FROM (SELECT 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, fnd_objects o
WHERE o.obj_name = G_EGO_ITEM
AND olc.object_id = o.object_id
AND olc.lifecycle_id = l_curr_lifecycle_id
AND olc.object_classification_code = l_curr_cc_id
)
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = l_curr_cc_id
) CAT_HIER
WHERE ROWNUM = 1
);
ELSIF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE, EGO_ITEM_PUB.G_TTYPE_DELETE) THEN
-- check if the user has privilege to update the item.
IF p_phase_id IS NOT NULL THEN
-- user changing phase
OPEN c_get_phase_seq(cp_phase_id => l_curr_phase_id);
code_debug(l_api_name ||' user does not have privilege to update the existing change '||p_transaction_type);
ELSIF p_transaction_type IN (EGO_ITEM_PUB.G_TTYPE_UPDATE, EGO_ITEM_PUB.G_TTYPE_DELETE) THEN
EGO_ITEM_LC_IMP_PC_PUB.Modify_Pending_Phase_Change
(p_api_version => p_api_version
,p_commit => FND_API.G_FALSE
,p_transaction_type => p_transaction_type
,p_inventory_item_id => l_inventory_item_id
,p_organization_id => l_organization_id
,p_revision_id => l_revision_id
,p_lifecycle_id => p_lifecycle_id
,p_phase_id => p_phase_id
,p_status_code => p_status
,p_change_id => NULL
,p_change_line_id => NULL
,p_effective_date => p_effective_date
,p_new_effective_date => l_effective_date
,p_perform_security_check => FND_API.G_FALSE
,x_return_status => x_return_status
,x_errorcode => l_dummy_char
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
code_debug (l_api_name ||' cannot create/modify/delete pending phase change '||x_msg_data);
' SELECT EgoNewItemReqSetupEO.ITEM_CATALOG_GROUP_ID, '||
' EgoNewItemReqSetupEO.PARENT_CATALOG_GROUP_ID, '||
' DECODE(EgoNewItemReqSetupEO.ITEM_NUM_GEN_METHOD, '||
' null, DECODE(EgoNewItemReqSetupEO.PARENT_CATALOG_GROUP_ID, null, ''U'', ''I''), '||
' EgoNewItemReqSetupEO.ITEM_NUM_GEN_METHOD) ITEM_NUM_GEN_METHOD, '||
' EgoNewItemReqSetupEO.ITEM_NUM_SEQ_NAME, '||
' EgoNewItemReqSetupEO.PREFIX, '||
' EgoNewItemReqSetupEO.SUFFIX '||
' FROM MTL_ITEM_CATALOG_GROUPS_B EgoNewItemReqSetupEO '||
' CONNECT BY PRIOR EgoNewItemReqSetupEO.PARENT_CATALOG_GROUP_ID = '||
' EgoNewItemReqSetupEO.ITEM_CATALOG_GROUP_ID '||
' START WITH EgoNewItemReqSetupEO.ITEM_CATALOG_GROUP_ID = :ITEM_CATALOG_GROUP_ID ';
SELECT 'x'
FROM mtl_system_items_b_kfv
WHERE concatenated_segments = cp_item_number
AND organization_id = cp_organization_id;
, position => 3 -- select position --
, c_tab => l_itemgen_method_table -- table of chars --
, cnt => 2500 -- rows requested --
, lower_bound => 1 -- start at --
);
, position => 4 -- select position --
, c_tab => l_itemgen_seq_table -- table of chars --
, cnt => 2500 -- rows requested --
, lower_bound => 1 -- start at --
);
, position => 5 -- select position --
, c_tab => l_itemgen_prefix_table -- table of chars --
, cnt => 2500 -- rows requested --
, lower_bound => 1 -- start at --
);
, position => 6 -- select position --
, c_tab => l_itemgen_suffix_table -- table of chars --
, cnt => 2500 -- rows requested --
, lower_bound => 1 -- start at --
);
l_new_itemgen_sql := l_new_itemgen_sql || ' SELECT ';
SELECT TEMPLATE_ID
INTO l_default_template
FROM EGO_CAT_GRP_TEMPLATES
WHERE CATALOG_GROUP_ID = p_category_id
AND DEFAULT_FLAG = 'Y'
AND ROWNUM = 1;
SELECT PARENT_CATALOG_GROUP_ID
INTO l_parent_id
FROM MTL_ITEM_CATALOG_GROUPS_B
WHERE ITEM_CATALOG_GROUP_ID = p_category_id;
SELECT ITEM_CATALOG_GROUP_ID
INTO l_item_catalog_group_id
FROM MTL_SYSTEM_ITEMS_B
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
SELECT AGV_NAME
FROM EGO_FND_DSC_FLX_CTX_EXT EXT1
WHERE EXT1.AGV_NAME = cp_agv_name
AND EXT1.ATTR_GROUP_ID NOT IN (SELECT ATTR_GROUP_ID
FROM EGO_FND_DSC_FLX_CTX_EXT EXT2
WHERE EXT2.AGV_NAME = cp_agv_name
AND EXT2.APPLICATION_ID = cp_application_id
AND EXT2.DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND EXT2.DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name);
SELECT OBJECT_NAME
FROM SYS.ALL_OBJECTS
WHERE OBJECT_NAME = cp_agv_name
AND OBJECT_NAME NOT IN (SELECT AGV_NAME
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE AGV_NAME = cp_agv_name
AND APPLICATION_ID = cp_application_id
AND DESCRIPTIVE_FLEXFIELD_NAME = cp_attr_group_type
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = cp_attr_group_name
);
UPDATE ego_fnd_dsc_flx_ctx_ext
SET agv_name = UPPER(l_views_to_process(i).ITEM_ATTR_AGV_NAME)
WHERE application_id = 431
AND descriptive_flexfield_name = 'EGO_ITEMMGMT_GROUP'
AND descriptive_flex_context_code = l_views_to_process(i).ITEM_ATTR_GROUP_NAME;
UPDATE ego_fnd_dsc_flx_ctx_ext
SET agv_name = UPPER(l_views_to_process(i).TP_ATTR_AGV_NAME)
WHERE application_id = 431
AND descriptive_flexfield_name = 'EGO_ITEM_TP_EXT_ATTRS'
AND descriptive_flex_context_code = l_views_to_process(i).TP_ATTR_GROUP_NAME;
SELECT ATTR_GROUP_ID, MULTI_ROW
INTO l_temp_num, l_multi_row_ag
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_views_to_process(i).ITEM_ATTR_GROUP_NAME;
SELECT ATTR_GROUP_ID
INTO l_temp_num
FROM EGO_FND_DSC_FLX_CTX_EXT
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEM_TP_EXT_ATTRS'
AND APPLICATION_ID = 431
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = l_views_to_process(i).TP_ATTR_GROUP_NAME;
l_dynamic_sql := ' SELECT TABLE_NAME, COLUMN_NAME '||
' FROM SYS.ALL_TAB_COLUMNS '||
' WHERE TABLE_NAME = :1 ';
l_final_view_query := ' SELECT ';
' AS SELECT '||l_final_view_col_list||
' FROM '||p_tp_agv_name||
' UNION ALL '||
' SELECT '||l_final_view_col_list||
' FROM (SELECT NULL PARTY_SITE_ID, '||l_item_agv_alias||'.* , '||
' '||l_item_agv_alias||'.ORGANIZATION_ID MASTER_ORGANIZATION_ID '||
' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
' ) ';
' SELECT '||l_final_view_col_list||
' FROM '||p_tp_agv_name||' '||l_tp_agv_alias||
' WHERE NOT EXISTS ( SELECT ''X'' '||
' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
' WHERE INVENTORY_ITEM_ID = '||l_tp_agv_alias||'.INVENTORY_ITEM_ID '||
' AND ORGANIZATION_ID = '||l_tp_agv_alias||'.MASTER_ORGANIZATION_ID ) '||
' UNION ALL '||
' SELECT '||l_final_view_col_list||
' FROM (SELECT NULL PARTY_SITE_ID, '||l_item_agv_alias||'.* , '||
' '||l_item_agv_alias||'.ORGANIZATION_ID MASTER_ORGANIZATION_ID '||
' FROM '||p_item_attr_agv_name||' '||l_item_agv_alias||
' WHERE EXISTS ( SELECT ''X'' '||
' FROM '||p_tp_agv_name||' '||l_tp_agv_alias||
' WHERE INVENTORY_ITEM_ID = '||l_item_agv_alias||'.INVENTORY_ITEM_ID '||
' AND MASTER_ORGANIZATION_ID = '||l_item_agv_alias||'.ORGANIZATION_ID '||
' ) '||
' ) ';
SELECT A.ORGANIZATION_ID ORGANIZATION_ID
,A.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
,A.REVISION_ID REVISION_ID
,A.APPLICATION_ID APPLICATION_ID
,A.ITEM_CATALOG_GROUP_ID ITEM_CATALOG_GROUP_ID
,A.OBJECT_NAME OBJECT_NAME
,A.ATTRIBUTE_GROUP_TYPE ATTRIBUTE_GROUP_TYPE
FROM THE (SELECT CAST(p_item_attr_def_tab AS "SYSTEM".EGO_ITEM_ATTR_DEFAULT_TABLE)
FROM dual) A
ORDER BY INVENTORY_ITEM_ID;
l_attr_groups_to_exclude := 'SELECT ATTR_GROUP_ID FROM '||
' EGO_FND_DSC_FLX_CTX_EXT WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE ' ||
' LIKE ''EGOINT_GDSN%'' AND APPLICATION_ID = 431 ' ||
' AND DESCRIPTIVE_FLEXFIELD_NAME = ''EGO_ITEMMGMT_GROUP'' ' ;