[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION mtl_catalog_group_update(
p_rowid IN ROWID
,p_process_flag IN NUMBER --Added for R12C
,p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_old_item_cat_grp_id IN NUMBER
,p_new_item_cat_grp_id IN NUMBER
,p_approval_status IN VARCHAR2
,p_item_number IN VARCHAR2
,p_transaction_id IN NUMBER
,p_prog_appid IN NUMBER
,p_prog_id IN NUMBER
,p_request_id IN NUMBER
,p_xset_id IN NUMBER --Adding for R12 C
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,x_err_text OUT NOCOPY VARCHAR2) RETURN INTEGER;
SELECT 'x'
FROM ego_obj_type_lifecycles eotl,
pa_ego_lifecycles_phases_v lc,
fnd_objects o
WHERE lc.proj_element_id = p_lifecycle_id
AND lc.proj_element_id = eotl.lifecycle_id
AND lc.object_type = 'PA_STRUCTURES'
AND eotl.object_id = o.object_id
AND eotl.object_classification_code in
( SELECT to_char(ic.item_catalog_group_id)
FROM mtl_item_catalog_groups_b ic
CONNECT BY PRIOR ic.parent_catalog_group_id = ic.item_catalog_group_id
START WITH ic.item_catalog_group_id = p_catalog_group_id
)
AND o.obj_name = 'EGO_ITEM';
SELECT 'x'
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 = p_lifecycle_id
AND pev_l.project_id = 0
AND plu.usage_type = 'PRODUCTS'
AND plu.lifecycle_id = pev_l.proj_element_id
AND pev_p.proj_element_id = p_lifecycle_phase_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 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 = p_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 'Y'
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM mtl_item_Catalog_groups_b
WHERE NVL(NEW_ITEM_REQUEST_REQD,'N') = 'Y'
AND item_Catalog_group_id = P_Catalog_Group_Id)
--4932389 : Perf fixes for lifecycle-phase-status queries.
OR EXISTS (SELECT NULL
FROM ego_lcphase_item_status status
,pa_ego_phases_v phase
WHERE status.phase_code = phase.phase_code
AND proj_element_id = cp_phase_id
AND status.item_status_code = cp_status_code);
PROCEDURE Update_Validation(
P_Org_Id IN NUMBER
,P_Item_Id IN NUMBER
,P_Lifecycle_Id IN NUMBER
,P_Phase_Id IN NUMBER
,P_Catalog_Group_Id IN NUMBER
,P_Status_Code IN VARCHAR2
,P_Rowid IN ROWID
,X_Error_Column OUT NOCOPY VARCHAR2
,X_Error_Code OUT NOCOPY VARCHAR2) IS
Cursor c_get_lifecycle_phase IS
SELECT lifecycle_id,
current_phase_id,
item_catalog_group_id,
approval_status -- Added for 4046435
FROM mtl_system_items_b
WHERE inventory_item_id = P_Item_Id
/*Changed for FP bug 8213894 with base bug 7492587*/
/* AND organization_id IN
(SELECT organization_id
FROM mtl_parameters
WHERE organization_id = master_organization_id); */
SELECT 'Y'
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM mtl_system_items_b
WHERE inventory_item_id = P_Item_Id
AND organization_id = P_Org_Id
AND NVL(approval_status,'A') <> 'A'
)
--4932389 : Perf fixes for lifecycle-phase-status queries.
OR EXISTS (SELECT NULL
FROM ego_lcphase_item_status status
,pa_ego_phases_v phase
WHERE status.phase_code = phase.phase_code
AND proj_element_id = cp_phase_id
AND status.item_status_code = cp_status_code);
SELECT display_sequence
FROM pa_ego_phases_v
WHERE proj_element_id = cp_phase_id ;
/* 3342860: Life Cycle now can be changed during Item update.
X_Error_Code := 'INV_IOI_INVALID_LC_CHANGE';
END Update_Validation;
SELECT Lifecycle_id, Current_Phase_Id
FROM mtl_system_items_b
WHERE inventory_item_id = cp_item_id
AND organization_id IN
(SELECT organization_id
FROM mtl_parameters
WHERE organization_id = master_organization_id)
UNION
SELECT Lifecycle_id, Current_Phase_Id
FROM mtl_system_items_interface
WHERE inventory_item_id = cp_item_id
AND process_flag = 4
AND organization_id IN
(SELECT organization_id
FROM mtl_parameters
WHERE organization_id = master_organization_id);
SELECT control_level
FROM mtl_item_attributes
WHERE attribute_name = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE';
ELSIF P_Transaction_Type ='UPDATE' THEN
Update_Validation(
P_Org_Id => P_Org_Id
,P_Item_Id => P_Item_Id
,P_Lifecycle_Id => P_Lifecycle_Id
,P_Phase_Id => P_Phase_Id
,P_Catalog_Group_Id => P_Catalog_Group_Id
,P_Status_Code => P_Status_Code
,P_Rowid => P_Rowid
,X_Error_Column => X_Error_Column
,X_Error_Code => X_Error_Code);
SELECT TL.FORM_LEFT_PROMPT ATTR_DISPLAY_NAME
INTO l_disp_name
FROM FND_DESCR_FLEX_COLUMN_USAGES FL_COL ,FND_DESCR_FLEX_COL_USAGE_TL TL
WHERE FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = p_attr_group_type
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = p_attr_group_name
AND FL_COL.APPLICATION_ID = 431
AND FL_COL.END_USER_COLUMN_NAME = p_attr_name
AND FL_COL.APPLICATION_ID = TL.APPLICATION_ID
AND FL_COL.DESCRIPTIVE_FLEXFIELD_NAME = TL.DESCRIPTIVE_FLEXFIELD_NAME
AND FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE = TL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FL_COL.APPLICATION_COLUMN_NAME = TL.APPLICATION_COLUMN_NAME
AND TL.LANGUAGE = USERENV('LANG');
SELECT rowid,
organization_id,
inventory_item_id,
lifecycle_id,
current_phase_Id,
inventory_item_status_code,
item_catalog_group_Id,
transaction_id,
transaction_type,
item_number,
unit_weight,
weight_uom_code,
style_item_flag,
trade_item_descriptor,
gdsn_outbound_enabled_flag,
primary_uom_code
FROM mtl_system_items_interface int
WHERE (int.organization_id = P_Org_Id OR P_All_Org = 1)
AND int.set_process_id = P_Set_id
AND int.process_flag = P_Process_Flag
AND EXISTS -- organization_id IN
(SELECT 1 --organization_id
FROM mtl_parameters mp
WHERE int.organization_id = mp.master_organization_id
AND mp.organization_id = mp.master_organization_id )
FOR UPDATE OF int.current_phase_id, int.process_flag;
SELECT rowid,
organization_id,
inventory_item_id,
lifecycle_id,
current_phase_Id,
inventory_item_status_code,
item_catalog_group_Id,
transaction_id,
transaction_type,
item_number,
unit_weight,
weight_uom_code,
style_item_flag,
trade_item_descriptor,
gdsn_outbound_enabled_flag,
primary_uom_code
FROM mtl_system_items_interface int
WHERE (int.organization_id = P_Org_Id OR P_All_Org = 1)
AND int.set_process_id = P_Set_id
AND int.process_flag = P_Process_Flag
AND NOT EXISTS -- organization_id NOT IN
(SELECT 1 -- organization_id
FROM mtl_parameters mp
WHERE int.organization_id = mp.master_organization_id
AND mp.organization_id = mp.master_organization_id)
FOR UPDATE OF int.current_phase_id,int.process_flag;
SELECT 'Y'
FROM fnd_objects
WHERE obj_name = 'EGO_ITEM';
SELECT item_catalog_group_Id,approval_status
,unit_weight, weight_uom_code,trade_item_descriptor
,gdsn_outbound_enabled_flag
,lifecycle_id, current_phase_id -- Bug 11768298
FROM mtl_system_items
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
SELECT 1 FROM ego_value_set_values_v
WHERE value_set_name = 'TradeItemDescVS'
AND internal_name = cp_pack_item_type;
SELECT base_uom_flag
FROM mtl_units_of_measure
WHERE uom_code = cp_primary_uom_code;
SELECT 'Y'
FROM DUAL
WHERE EXISTS ( SELECT NULL
FROM mtl_item_Catalog_groups_b
WHERE NVL(NEW_ITEM_REQUEST_REQD,'N') = 'Y'
AND item_Catalog_group_id = cp_catalog_group_id
)
AND EXISTS ( SELECT NULL
FROM mtl_system_items_interface
WHERE NVL(eng_item_flag,'N')='N'
AND rowid = cp_row_id);
ELSIF cur.transaction_type ='UPDATE' THEN
BEGIN
Update_Validation(
P_Org_Id => cur.organization_id
,P_Item_Id => cur.inventory_item_id
,P_Lifecycle_Id => cur.lifecycle_id
,P_Phase_Id => cur.current_phase_id
,P_Catalog_Group_Id => cur.item_catalog_group_id
,P_Status_Code => cur.inventory_item_status_code
,P_Rowid => cur.rowid
,X_Error_Column => X_Error_Column
,X_Error_Code => X_Error_Code);
AND cur.transaction_type = 'UPDATE'
AND cur.gdsn_outbound_enabled_flag = 'Y')
THEN
l_valid := EGO_GTIN_PVT.Validate_Unit_Wt_Uom(p_inventory_item_id => cur.inventory_item_id,
p_org_id => cur.organization_id );
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
IF ((cur.item_catalog_group_id IS NOT NULL) AND ( l_error_status <> 1 ) AND (cur.transaction_type = 'UPDATE'))THEN
/* Adding the ttype check and the NVL clause to the ICC change condition */
IF (NVL(l_old_catalog_group_id, -1) <> NVL(cur.item_catalog_group_id,-1)) THEN
/* Bug 5389029 - Passing Int Table values for Lifecycle/Phase */
EGO_INV_ITEM_CATALOG_PVT.Change_Item_Catalog (
P_INVENTORY_ITEM_ID => cur.inventory_item_id
,P_ORGANIZATION_ID => cur.organization_id
,P_CATALOG_GROUP_ID => l_old_catalog_group_id
,P_NEW_CATALOG_GROUP_ID => cur.item_catalog_group_id
,P_NEW_LIFECYCLE_ID => cur.lifecycle_id
,P_NEW_PHASE_ID => cur.current_phase_id
,P_NEW_ITEM_STATUS_CODE => cur.inventory_item_status_code
,P_COMMIT => FND_API.G_FALSE
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA );
l_return_status := INV_EGO_REVISION_VALIDATE.mtl_catalog_group_update (
p_rowid => cur.rowid
,p_process_flag => p_process_flag --Added for R12 C
,p_inventory_item_id => cur.inventory_item_id
,p_organization_id => cur.organization_id
,p_old_item_cat_grp_id => l_old_catalog_group_id
,p_new_item_cat_grp_id => cur.item_catalog_group_id
,p_approval_status => l_approval_status
,p_item_number => cur.item_number
,p_transaction_id => cur.transaction_id
,p_prog_appid => p_prog_appid
,p_prog_id => p_prog_id
,p_request_id => p_request_id
,p_xset_id => P_Set_Id --Added for R12 C
,p_user_id => p_user_id
,p_login_id => p_login_id
,x_err_text => x_err_text);
IF(((cur.lifecycle_id IS NOT NULL) OR (cur.current_phase_id IS NOT NULL)) AND (cur.transaction_type = 'UPDATE')) THEN
IF ( (NVL(l_old_lifecycle_id, -1) <> NVL(cur.lifecycle_id,-1)) OR (NVL(l_old_current_phase_id, -1) <> NVL(cur.current_phase_id,-1)) ) THEN
EGO_INV_ITEM_CATALOG_PVT.Change_Item_Lifecycle(
p_inventory_item_id => cur.inventory_item_id,
p_organization_id => cur.organization_id,
p_new_catalog_category_id => cur.item_catalog_group_id,
p_new_lifecycle_id => cur.lifecycle_id,
p_new_phase_id => cur.current_phase_id,
p_new_item_status_code => cur.inventory_item_status_code,
p_commit => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_data => x_msg_data,
x_msg_count => x_msg_count);
cur.transaction_type = 'UPDATE') OR (cur.transaction_type = 'CREATE'))
THEN
OPEN c_pack_item_type (cp_pack_item_type => cur.trade_item_descriptor);
IF (l_error_status <> 1 AND cur.transaction_type = 'UPDATE')
THEN
/* API call to resolve item in pack or not */
BOM_IMPLODER_PUB.IMPLODER_USEREXIT(
SEQUENCE_ID => null,
ENG_MFG_FLAG => 2,
ORG_ID => cur.organization_id,
IMPL_FLAG => 2,
DISPLAY_OPTION => 1,
LEVELS_TO_IMPLODE => 60,
OBJ_NAME => 'EGO_ITEM',
PK1_VALUE => cur.inventory_item_id,
PK2_VALUE => cur.organization_id,
PK3_VALUE => null,
PK4_VALUE => null,
PK5_VALUE => null,
IMPL_DATE => to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') ,
UNIT_NUMBER_FROM => 'N',
UNIT_NUMBER_TO => 'Y',
ERR_MSG => x_err_text,
ERR_CODE => l_error_logged,
ORGANIZATION_OPTION => 1,
ORGANIZATION_HIERARCHY => null,
SERIAL_NUMBER_FROM => null,
SERIAL_NUMBER_TO => null,
STRUCT_NAME => 'PIM_PBOM_S',
STRUCT_TYPE => 'Packaging Hierarchy',
PREFERRED_ONLY => 2 ,
USED_IN_STRUCTURE => l_item_in_pack);
/* For update of Trade Item Descriptor GDSN Post Processing is triggered */
EGO_GTIN_PVT.process_gtid_update ( p_inventory_item_id => cur.inventory_item_id
,p_organization_id => cur.organization_id
,p_trade_item_desc => cur.trade_item_descriptor
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
'INV_IOI_ERR_GTID_UPDATE_01',--bug 11894684, replace INV_IOI_ERR
X_Err_Text);
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
AND cur.transaction_type = 'UPDATE'
AND cur.gdsn_outbound_enabled_flag = 'Y')
THEN
l_valid := EGO_GTIN_PVT.Validate_Unit_Wt_Uom(p_inventory_item_id => cur.inventory_item_id,
p_org_id => cur.organization_id );
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
IF ((cur.item_catalog_group_id IS NOT NULL) AND ( l_error_status <> 1) AND (cur.transaction_type = 'UPDATE')) THEN
/* Adding the ttype check and the NVL clause to the ICC change condition */
IF (NVL(l_old_catalog_group_id,-1) <> NVL(cur.item_catalog_group_id,-1)) THEN
/* Bug 5389029 - Passing Int Table values for Lifecycle/Phase */
EGO_INV_ITEM_CATALOG_PVT.Change_Item_Catalog (
P_INVENTORY_ITEM_ID => cur.inventory_item_id
,P_ORGANIZATION_ID => cur.organization_id
,P_CATALOG_GROUP_ID => l_old_catalog_group_id
,P_NEW_CATALOG_GROUP_ID => cur.item_catalog_group_id
,P_NEW_LIFECYCLE_ID => cur.lifecycle_id
,P_NEW_PHASE_ID => cur.current_phase_id
,P_NEW_ITEM_STATUS_CODE => cur.inventory_item_status_code
,P_COMMIT => FND_API.G_FALSE
,X_RETURN_STATUS => X_RETURN_STATUS
,X_MSG_COUNT => X_MSG_COUNT
,X_MSG_DATA => X_MSG_DATA );
l_return_status := INV_EGO_REVISION_VALIDATE.mtl_catalog_group_update (
p_rowid => cur.rowid
,p_process_flag => p_process_flag --Added for R12 C
,p_inventory_item_id => cur.inventory_item_id
,p_organization_id => cur.organization_id
,p_old_item_cat_grp_id => l_old_catalog_group_id
,p_new_item_cat_grp_id => cur.item_catalog_group_id
,p_approval_status => l_approval_status
,p_item_number => cur.item_number
,p_transaction_id => cur.transaction_id
,p_prog_appid => p_prog_appid
,p_prog_id => p_prog_id
,p_request_id => p_request_id
,p_xset_id => P_Set_Id --Added for R12 C
,p_user_id => p_user_id
,p_login_id => p_login_id
,x_err_text => x_err_text);
cur.transaction_type = 'UPDATE')
OR cur.transaction_type = 'CREATE' ) )
THEN
OPEN c_pack_item_type (cp_pack_item_type => cur.trade_item_descriptor);
IF (l_error_status <> 1 AND cur.transaction_type = 'UPDATE')
THEN
/* API call to resolve item in pack or not */
BOM_IMPLODER_PUB.IMPLODER_USEREXIT(
SEQUENCE_ID => null,
ENG_MFG_FLAG => 2,
ORG_ID => cur.organization_id,
IMPL_FLAG => 2,
DISPLAY_OPTION => 1,
LEVELS_TO_IMPLODE => 60,
OBJ_NAME => 'EGO_ITEM',
PK1_VALUE => cur.inventory_item_id,
PK2_VALUE => cur.organization_id,
PK3_VALUE => null,
PK4_VALUE => null,
PK5_VALUE => null,
IMPL_DATE => to_char(SYSDATE,'YYYY/MM/DD HH24:MI:SS') ,
UNIT_NUMBER_FROM => 'N',
UNIT_NUMBER_TO => 'Y',
ERR_MSG => x_err_text,
ERR_CODE => l_error_logged,
ORGANIZATION_OPTION => 1,
ORGANIZATION_HIERARCHY => null,
SERIAL_NUMBER_FROM => null,
SERIAL_NUMBER_TO => null,
STRUCT_TYPE => 'Packaging Hierarchy',
PREFERRED_ONLY => 2 ,
USED_IN_STRUCTURE => l_item_in_pack);
/* For update of Trade Item Descriptor GDSN Post Processing is triggered */
EGO_GTIN_PVT.process_gtid_update ( p_inventory_item_id => cur.inventory_item_id
,p_organization_id => cur.organization_id
,p_trade_item_desc => cur.trade_item_descriptor
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
'INV_IOI_ERR_GTID_UPDATE_02',--bug 11894684, replace INV_IOI_ERR
X_Err_Text);
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
INV_EGO_REVISION_VALIDATE.Insert_Grants_And_UserAttr(P_Set_id);
SELECT TO_CHAR(party_id)
FROM ego_user_v
WHERE user_id = cp_user_id;
SELECT msii.rowid,
msii.organization_id,
msii.inventory_item_id,
msii.item_catalog_group_Id,
msii.inventory_item_status_code,
msii.lifecycle_id,
msii.current_phase_id,
msii.transaction_id,
msii.transaction_type,
msii.item_number, --5522789
mp.master_organization_id,
msii.created_by
FROM mtl_system_items_interface msii,
mtl_parameters mp
WHERE (msii.organization_id = P_Org_Id OR P_All_Org = 1)
AND msii.set_process_id = P_Set_id
AND msii.process_flag = P_Process_flag
AND msii.organization_id = mp.organization_id--Bug 5238510
FOR UPDATE OF process_flag;
SELECT item_catalog_group_id, approval_status,
inventory_item_status_code,lifecycle_id,
current_phase_id
FROM mtl_system_items_b
WHERE organization_id = cp_org_id
AND inventory_item_id = cp_item_id;
ELSIF cur.transaction_type ='UPDATE' THEN
-- Bug 10243450 : Created_By had null value for some excel update cases and would fail this check causing security to be skipped
IF ((nvl(cur.created_by,FND_GLOBAL.user_id)) <> -99 ) THEN
l_has_privilege := check_data_security(
p_function => 'EGO_EDIT_ITEM'
,p_object_name => 'EGO_ITEM'
,p_instance_pk1_value => cur.inventory_item_id
,p_instance_pk2_value => cur.organization_id
,P_User_Id => P_User_Id);
FND_MESSAGE.SET_NAME ( 'INV', 'INV_IOI_ITEM_UPDATE_PRIV');
l_error_code := 'INV_IOI_ERR_ITEM_UPDATE_PRIV';--bug 11894684, replace INV_IOI_ERR
l_error_code := 'INV_IOI_STATUS_UPDATE_PRIV';
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
PROCEDURE Insert_Grants_And_UserAttr (P_Set_id IN NUMBER) IS
CURSOR c_get_processed_records (cp_set_process_id NUMBER) IS
SELECT interface.inventory_item_id,
interface.item_catalog_group_id,
interface.organization_id,
interface.template_id,
interface.transaction_id,
interface.transaction_type,
interface.rowid
FROM mtl_system_items_interface interface
WHERE interface.set_process_id = cp_set_process_id
AND interface.process_flag = 4
AND interface.transaction_type = 'CREATE'
--4676088AND interface.transaction_type IN ('CREATE','UPDATE')
FOR UPDATE OF process_flag;
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 = cp_catalog_group_id;
SELECT grant_guid
FROM fnd_grants grants,fnd_menus menus, fnd_objects objects
WHERE grants.object_id = objects.object_id
AND grants.menu_id = menus.menu_id
AND objects.obj_name = cp_object_name
AND menus.menu_name = cp_menu_name
AND grants.instance_type = 'INSTANCE'
AND grants.instance_pk1_value = TO_CHAR(cp_inv_item_id)
AND grants.instance_pk2_value = TO_CHAR(cp_organization_id)
AND ((grants.grantee_type = 'USER' AND grants.grantee_key = cp_user_party_id_char ) OR
(grants.grantee_type = 'GROUP' AND grants.grantee_key = cp_group_party_id_char) OR
(grants.grantee_type = 'COMPANY' AND grants.grantee_key = cp_company_party_id_char) OR
(grants.grantee_type = 'GLOBAL' AND grants.grantee_key = cp_global_party_id_char)
)
AND start_date <= NVL(cp_end_date, start_date)
AND NVL(end_date,cp_start_date) >= cp_start_date;
SELECT party_id INTO l_party_id
FROM EGO_USER_V
WHERE USER_ID = FND_GLOBAL.User_ID;
INVPUTLI.info('INVEGRVB.Insert_Grants_And_UserAttr: inserting i grants for an Item'||cur.Inventory_Item_ID);
INVPUTLI.info('INVEGRVB.Insert_Grants_And_UserAttr: inserting Long description Userdefined Attribute.');
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = exc.rowid;
END Insert_Grants_And_UserAttr;
SELECT cat_temp.TEMPLATE_ID
FROM EGO_CAT_GRP_TEMPLATES cat_temp,
MTL_ITEM_TEMPLATES temp
WHERE cat_temp.CATALOG_GROUP_ID = cp_catalog_group_id
AND cat_temp.DEFAULT_FLAG = 'Y'
AND cat_temp.TEMPLATE_ID = temp.TEMPLATE_ID;
IF (p_attribute_name IS NULL) THEN --Insert
EGO_TEMPL_ATTRS_PUB.Sync_Template(
p_template_id => p_template_id,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_message_text => l_message_text);
ELSE --Update
EGO_TEMPL_ATTRS_PUB.Sync_Template_Attribute(
p_template_id => p_template_id,
p_attribute_name => p_attribute_name,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_message_text => l_message_text);
PROCEDURE Update_Attribute_Control_Level (
p_application_column_name IN VARCHAR2
,p_control_level IN NUMBER)
IS
l_return_status VARCHAR2(2000);
EGO_EXT_FWK_PUB.Update_Attribute_Control_Level(
p_api_version => 1,
p_application_id => 431,
p_descriptive_flexfield_name => 'EGO_MASTER_ITEMS' ,
p_application_column_name => p_application_column_name,
p_control_level => p_control_level,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_msg_data);
FOR cur in ( select substr(attribute_name, 18) att_name from MTL_ITEM_ATTRIBUTES WHERE STATUS_CONTROL_CODE IN (1,2) ) LOOP
EGO_EXT_FWK_PUB.Update_Attribute_Control_Level(
p_api_version => 1,
p_application_id => 431,
p_descriptive_flexfield_name => 'EGO_MASTER_ITEMS' ,
p_application_column_name => cur.att_name,
p_control_level => p_control_level,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_message_count,
x_msg_data => l_msg_data);
END Update_Attribute_Control_Level;
SELECT inventory_item_id
,organization_id
,item_catalog_group_id
,lifecycle_id
,current_phase_id
,transaction_id
,inventory_item_status_code
,rowid
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE (set_process_id = p_set_id OR set_process_id = p_set_id + 1000000000000)
AND transaction_type IN ('UPDATE', 'AUTO_CHILD')
AND process_flag = 4;
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 inventory_item_id
,organization_id
,revision_id
,lifecycle_id
,current_phase_id
,transaction_id
,rowid
FROM mtl_item_revisions_interface
WHERE (set_process_id = p_set_id OR set_process_id = p_set_id + 1000000000000)
AND transaction_type = 'UPDATE'
AND process_flag = 4;
SELECT lifecycle_id
,current_phase_id
FROM mtl_item_revisions_b
WHERE revision_id = cp_rev_id;
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
UPDATE mtl_item_revisions_interface
SET process_flag = 3
WHERE rowid = cur.rowid;
'SELECT count(*)
FROM dual
WHERE EXISTS(SELECT 1 FROM ego_cat_grp_templates
WHERE template_id = :p_template_id)'
INTO l_template_exists
USING p_template_id;
SELECT interface.inventory_item_id, interface.organization_id,
interface.item_catalog_group_id
FROM mtl_system_items_interface interface,
mtl_parameters mp
WHERE interface.SET_PROCESS_ID = p_set_process_id
AND interface.PROCESS_FLAG = 4
AND interface.TRANSACTION_TYPE = 'CREATE'
AND interface.ITEM_CATALOG_GROUP_ID IS NOT NULL
AND mp.ORGANIZATION_ID = interface.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID;
SELECT ICC.NEW_ITEM_REQUEST_REQD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = cp_item_catalog_group_id
ORDER BY LEVEL ASC;
SELECT nir_option
FROM ego_import_option_sets
WHERE batch_id = p_set_process_id;
SELECT selection_flag
FROM ego_import_copy_options
WHERE copy_option = 'NIR_OPTION'
AND batch_id = p_set_process_id;
UPDATE MTL_SYSTEM_ITEMS_B
SET INVENTORY_ITEM_STATUS_CODE='Pending'
,APPROVAL_STATUS = 'N'
,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
WHERE INVENTORY_ITEM_ID = nir_item.inventory_item_id
AND ORGANIZATION_ID = nir_item.organization_id;
UPDATE mtl_system_items_b
SET inventory_item_status_code = 'Pending',
approval_status = 'N',
current_phase_id = DECODE(lifecycle_id, NULL, NULL,
Get_Initial_Lifecycle_Phase(lifecycle_id))
WHERE (inventory_item_id, organization_id) IN
(SELECT interface.inventory_item_id, interface.organization_id
FROM mtl_system_items_interface interface,
mtl_parameters mp
WHERE interface.set_process_id = p_set_process_id
AND interface.process_flag = 4
AND interface.transaction_type = 'CREATE'
AND interface.item_catalog_group_id IS NOT NULL
AND mp.organization_id = interface.organization_id
AND mp.organization_id = mp.master_organization_id
AND 'Y' =
(SELECT icc.new_item_request_reqd
FROM mtl_item_catalog_groups_b icc
WHERE icc.new_item_request_reqd IS NOT NULL
AND icc.new_item_request_reqd <> 'I'
AND ROWNUM = 1
CONNECT BY PRIOR
icc.parent_catalog_group_id = icc.item_catalog_group_id
START WITH
icc.item_catalog_group_id = interface.item_catalog_group_id
));
UPDATE mtl_system_items_interface msii
SET msii.process_flag = 5
WHERE msii.ROWID IN
( SELECT interface.ROWID
FROM mtl_system_items_interface interface,
-- MTL_ITEM_CATALOG_GROUPS_B micb,
MTL_PARAMETERS mp
WHERE interface.SET_PROCESS_ID = p_set_process_id
AND interface.PROCESS_FLAG = 4
AND interface.TRANSACTION_TYPE = 'CREATE'
--AND interface.ITEM_CATALOG_GROUP_ID = micb.item_catalog_group_id
AND interface.ITEM_CATALOG_GROUP_ID IS NOT NULL
--AND micb.NEW_ITEM_REQUEST_REQD = 'Y'
AND mp.organization_id = interface.organization_id
AND interface.organization_id = mp.master_organization_id
AND 'Y' =
( SELECT ICC.NEW_ITEM_REQUEST_REQD
FROM MTL_ITEM_CATALOG_GROUPS_B ICC
WHERE ICC.NEW_ITEM_REQUEST_REQD IS NOT NULL
AND ICC.NEW_ITEM_REQUEST_REQD <> 'I'
AND ROWNUM = 1
CONNECT BY PRIOR ICC.PARENT_CATALOG_GROUP_ID = ICC.ITEM_CATALOG_GROUP_ID
START WITH ICC.ITEM_CATALOG_GROUP_ID = interface.ITEM_CATALOG_GROUP_ID ) );
UPDATE mtl_system_items_interface
SET process_flag = 4
WHERE set_process_id = p_set_process_id
AND process_flag = 5;
UPDATE mtl_system_items_interface
SET process_flag = 3,
change_id = NULL,
change_line_id = NULL
WHERE set_process_id = p_set_process_id
AND process_flag = 5;
UPDATE mtl_system_items_interface
SET process_flag = 3,
change_id = NULL,
change_line_id = NULL
WHERE set_process_id = p_set_process_id
AND process_flag = 5;
SELECT interface.INVENTORY_ITEM_ID,
interface.ITEM_CATALOG_GROUP_ID,
interface.ORGANIZATION_ID,
interface.TRANSACTION_ID,
interface.ITEM_NUMBER,
interface.rowid,
micb.NEW_ITEM_REQ_CHANGE_TYPE_ID,
mp.ORGANIZATION_CODE
FROM MTL_SYSTEM_ITEMS_INTERFACE interface,
MTL_ITEM_CATALOG_GROUPS_B micb,
MTL_PARAMETERS mp
WHERE interface.SET_PROCESS_ID = cp_set_process_id
AND interface.PROCESS_FLAG = 4
AND interface.TRANSACTION_TYPE = 'CREATE'
AND interface.ITEM_CATALOG_GROUP_ID = micb.ITEM_CATALOG_GROUP_ID
AND micb.NEW_ITEM_REQUEST_REQD = 'Y'
AND mp.ORGANIZATION_ID = interface.ORGANIZATION_ID
AND mp.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID --Bug 4517161
FOR UPDATE OF interface.INVENTORY_ITEM_ID;
UPDATE MTL_SYSTEM_ITEMS_B
SET INVENTORY_ITEM_STATUS_CODE='Pending'
,APPROVAL_STATUS = 'N'
,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
WHERE INVENTORY_ITEM_ID = i.inventory_item_id
AND ORGANIZATION_ID = i.organization_id;
l_dynamic_sql := 'select type_name from eng_change_order_types_vl '||
'where change_order_type_id = :type_id ';
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE rowid = i.rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE rowid = i.rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET CHANGE_ID = l_change_id
WHERE rowid = i.rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE set_process_id= p_set_process_id;
PROCEDURE Insert_Revision_UserAttr(P_Set_id IN NUMBER DEFAULT -999) IS
BEGIN
RETURN;
END Insert_Revision_UserAttr;
FUNCTION mtl_catalog_group_update(
p_rowid IN ROWID
,p_process_flag IN NUMBER --Added for R12C
,p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_old_item_cat_grp_id IN NUMBER
,p_new_item_cat_grp_id IN NUMBER
,p_approval_status IN VARCHAR2
,p_item_number IN VARCHAR2
,p_transaction_id IN NUMBER
,p_prog_appid IN NUMBER
,p_prog_id IN NUMBER
,p_request_id IN NUMBER
,p_xset_id IN NUMBER
,p_user_id IN NUMBER
,p_login_id IN NUMBER
,x_err_text OUT NOCOPY VARCHAR2) RETURN INTEGER IS
CURSOR c_get_nir_setup(cp_item_catalog_group_id IN NUMBER) IS
SELECT new_item_request_reqd
FROM mtl_item_catalog_groups_b
WHERE new_item_request_reqd IS NOT NULL
AND new_item_request_reqd <> 'I'
CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
START WITH item_catalog_group_id = cp_item_catalog_group_id
ORDER BY LEVEL ASC;
l_update_msb BOOLEAN := FALSE;
l_update_msb := FALSE;
l_dynamic_sql := 'SELECT st.status_type
,ch.approval_status_type
,ch.change_id
,ch.change_notice
FROM eng_engineering_changes ch
,eng_change_subjects sb
,eng_change_order_types_vl tp
,eng_change_statuses_vl st
WHERE tp.type_classification = :l_type_classification
AND tp.change_mgmt_type_code = :l_change_mgmt_type_code
AND ch.change_mgmt_type_code = tp.change_mgmt_type_code
AND ch.change_id = sb.change_id
AND sb.entity_name = :l_entity_name
AND st.status_code = ch.status_code
AND sb.pk1_value = :l_inv_item_id
AND sb.pk2_value = :l_org_id';
l_update_msb := TRUE;
l_update_msb := TRUE;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE rowid = p_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE rowid = p_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE rowid = p_rowid;
UPDATE MTL_SYSTEM_ITEMS_INTERFACE
SET process_flag = 3
WHERE rowid = p_rowid;
IF l_update_msb AND NOT l_error_occured THEN
UPDATE mtl_system_items_b
SET approval_status = 'A'
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
END mtl_catalog_group_update;
SELECT organization_code INTO l_org_code
FROM mtl_parameters
WHERE organization_id = p_organization_id;
SELECT new_item_req_change_type_id INTO l_type_id
FROM mtl_item_catalog_groups_b
WHERE item_catalog_group_id = p_item_catalog_group_id;
UPDATE MTL_SYSTEM_ITEMS_B
SET INVENTORY_ITEM_STATUS_CODE='Pending'
,APPROVAL_STATUS = 'N'
,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
l_dynamic_sql := 'select type_name from eng_change_order_types_vl '||
'where change_order_type_id = :type_id ';
UPDATE MTL_SYSTEM_ITEMS_B
SET INVENTORY_ITEM_STATUS_CODE='Pending'
,APPROVAL_STATUS = 'N'
,CURRENT_PHASE_ID = DECODE(LIFECYCLE_ID,NULL,NULL,Get_Initial_Lifecycle_Phase(LIFECYCLE_ID))
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = p_organization_id;
UPDATE mtl_system_items_interface
SET process_flag = 5
WHERE rowid = p_row_id;
UPDATE mtl_system_items_interface
SET process_flag = p_process_flag
WHERE rowid = p_row_id;
UPDATE mtl_system_items_interface
SET process_flag = 3,
change_id = NULL,
change_line_id = NULL
WHERE rowid = p_row_id;
UPDATE mtl_system_items_interface
SET process_flag = 3,
change_id = NULL,
change_line_id = NULL
WHERE rowid = p_row_id;
SELECT item_catalog_group_id,
style_item_id,
style_item_flag,
gdsn_outbound_enabled_flag,
inventory_item_id,
organization_id,
transaction_type,
request_id,
transaction_id
FROM mtl_system_items_interface
WHERE rowid = cp_row_id;
SELECT master_organization_id
FROM mtl_parameters
WHERE organization_id = cp_organization_id;
SELECT style_item_flag,style_item_id,
item_catalog_group_id
FROM mtl_system_items_b
WHERE inventory_item_id = cp_inventory_item_id
AND organization_id = cp_organization_id;
/* The Style/SKU status of the item cannot be updated - Sec 2.5.1 - Condition 1*/
IF ((l_transaction_type = 'UPDATE') AND (l_msii_style_item_flag <> l_msi_style_item_flag))
THEN
IF l_inv_debug_level IN(101, 102) THEN
INVPUTLI.info('INVEGRVB.validate_style_sku: Cannot update Style Item Flag ');
/* The Style Item for a given SKU item cannot be updated - Sec 2.5.1 - Condition 1.1*/
IF (l_transaction_type = 'UPDATE') AND (l_msii_style_item_id <> l_msi_style_item_id)
THEN
IF l_inv_debug_level IN(101, 102) THEN
INVPUTLI.info('INVEGRVB.validate_style_sku: Cannot update Style Item Id ');
(l_transaction_type = 'UPDATE' AND NVL(l_msi_icc_id,l_null_icc_id) <> NVL(l_msii_icc_id,l_null_icc_id)) )THEN
/* Styles to be created in only those ICCs that contain Variant AGs - Sec 2.5.1 -Condition 2 */
IF l_msii_style_item_flag = 'Y' THEN
SELECT COUNT(*) INTO l_sku_exists
FROM mtl_system_items_b
WHERE style_item_id = l_msii_inv_item_id
AND organization_id = l_msii_org_id;
SELECT count(*) INTO l_valid_icc
FROM ego_obj_attr_grp_assocs_v
WHERE variant = 'Y'
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 = l_msii_icc_id );
SELECT item_catalog_group_id INTO l_style_icc_id
FROM mtl_system_items_b
WHERE inventory_item_id = l_msii_style_item_id
AND organization_id = l_msii_org_id;
OR (l_transaction_type = 'UPDATE' AND l_msii_style_item_flag = 'N' AND l_msi_style_item_flag IS NULL) --bug 6345529
)
AND l_msii_org_id = l_master_org )THEN
/* The Variant attribute combination for SKU items must be unique - Sec 2.5.1 -Condition 8 */
--Added condition to avoid entry twice, from INVNIRIB and INVPVALB
IF (p_xset_id < 3000000000000 AND p_xset_id <> 3000000000000-999) THEN
l_error_logged := EGO_STYLE_SKU_ITEM_PVT.Validate_SKU_Variant_Usage (
p_intf_row_id => P_Row_Id
,x_sku_exists => l_sku_variant
,x_err_text => l_err_text
,x_var_attrs_missing => l_var_attrs_missing);
UPDATE mtl_system_items_interface
SET process_flag = 3
WHERE rowid = p_row_id;
SELECT 'X'
FROM ORG_ACCESS_VIEW
WHERE ORGANIZATION_ID = p_org_id
AND RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
AND RESP_APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID;