[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(1)
INTO l_Style_Item_Count
FROM MTL_SYSTEM_ITEMS_B
WHERE STYLE_ITEM_FLAG = 'Y'
AND ITEM_CATALOG_GROUP_ID IN
( SELECT ITEM_CATALOG_GROUP_ID
FROM MTL_ITEM_CATALOG_GROUPS_B
CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
START WITH ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
);
SELECT COUNT(1)
INTO l_SKU_Item_Count
FROM MTL_SYSTEM_ITEMS_B
WHERE STYLE_ITEM_FLAG = 'N'
AND ITEM_CATALOG_GROUP_ID IN
( SELECT ITEM_CATALOG_GROUP_ID
FROM MTL_ITEM_CATALOG_GROUPS_B
CONNECT BY PRIOR ITEM_CATALOG_GROUP_ID = PARENT_CATALOG_GROUP_ID
START WITH ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
);
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
( SET_PROCESS_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
TEMPLATE_ID,
COPY_ITEM_ID,
COPY_REVISION_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
DESCRIPTION,
LONG_DESCRIPTION,
PRIMARY_UOM_CODE,
PRIMARY_UNIT_OF_MEASURE,
ITEM_TYPE,
INVENTORY_ITEM_STATUS_CODE,
ALLOWED_UNITS_LOOKUP_CODE,
ITEM_CATALOG_GROUP_ID,
BOM_ENABLED_FLAG,
ENG_ITEM_FLAG,
WEIGHT_UOM_CODE,
UNIT_WEIGHT,
ITEM_NUMBER,
STYLE_ITEM_FLAG,
STYLE_ITEM_ID,
STYLE_ITEM_NUMBER,
GDSN_OUTBOUND_ENABLED_FLAG,
TRADE_ITEM_DESCRIPTOR,
COPY_ORGANIZATION_ID
)
VALUES
( p_set_process_id,
p_Process_Flag,
p_Transaction_Type,
p_Template_Id,
p_copy_inventory_item_Id ,
p_copy_revision_Id,
p_inventory_item_id ,
p_organization_id,
p_description,
p_long_description ,
p_primary_uom_code,
p_primary_unit_of_measure,
p_item_type,
p_inventory_item_status_code,
p_allowed_units_lookup_code,
p_item_catalog_group_id,
p_bom_enabled_flag,
p_eng_item_flag ,
p_weight_uom_code ,
p_unit_weight ,
p_Item_Number,
p_Style_Item_Flag,
p_Style_Item_Id,
p_Style_item_number,
p_Gdsn_Outbound_Enabled_Flag,
p_Trade_Item_Descriptor,
l_copy_from_organization_id
);
SELECT A.SET_PROCESS_ID SET_PROCESS_ID ,
A.PROCESS_FLAG PROCESS_FLAG ,
A.TRANSACTION_TYPE TRANSACTION_TYPE ,
A.TEMPLATE_ID TEMPLATE_ID ,
A.COPY_INVENTORY_ITEM_ID COPY_INVENTORY_ITEM_ID ,
A.COPY_REVISION_ID COPY_REVISION_ID ,
A.INVENTORY_ITEM_ID INVENTORY_ITEM_ID ,
A.ORGANIZATION_ID ORGANIZATION_ID ,
A.DESCRIPTION DESCRIPTION ,
A.LONG_DESCRIPTION LONG_DESCRIPTION ,
A.PRIMARY_UOM_CODE PRIMARY_UOM_CODE ,
A.PRIMARY_UNIT_OF_MEASURE PRIMARY_UNIT_OF_MEASURE ,
A.ITEM_TYPE ITEM_TYPE ,
A.INVENTORY_ITEM_STATUS_CODE INVENTORY_ITEM_STATUS_CODE ,
A.ALLOWED_UNITS_LOOKUP_CODE ALLOWED_UNITS_LOOKUP_CODE ,
A.TRACKING_QUANTITY_IND TRACKING_QUANTITY_IND ,
A.ONT_PRICING_QTY_SOURCE ONT_PRICING_QTY_SOURCE ,
A.SECONDARY_DEFAULT_IND SECONDARY_DEFAULT_IND ,
A.DUAL_UOM_DEVIATION_HIGH DUAL_UOM_DEVIATION_HIGH ,
A.DUAL_UOM_DEVIATION_LOW DUAL_UOM_DEVIATION_LOW ,
A.SECONDARY_UOM_CODE SECONDARY_UOM_CODE ,
A.LIFECYCLE_ID LIFECYCLE_ID ,
A.CURRENT_PHASE_ID CURRENT_PHASE_ID ,
A.ITEM_CATALOG_GROUP_ID ITEM_CATALOG_GROUP_ID ,
A.BOM_ENABLED_FLAG BOM_ENABLED_FLAG ,
A.ENG_ITEM_FLAG ENG_ITEM_FLAG ,
A.WEIGHT_UOM_CODE WEIGHT_UOM_CODE ,
A.UNIT_WEIGHT UNIT_WEIGHT ,
A.ITEM_NUMBER ITEM_NUMBER ,
A.STYLE_ITEM_FLAG STYLE_ITEM_FLAG ,
A.STYLE_ITEM_ID STYLE_ITEM_ID ,
A.GDSN_OUTBOUND_ENABLED_FLAG GDSN_OUTBOUND_ENABLED_FLAG ,
A.TRADE_ITEM_DESCRIPTOR TRADE_ITEM_DESCRIPTOR ,
A.TRANSACTION_ID TRANSACTION_ID,
A.SOURCE_SYSTEM_REFERENCE SOURCE_SYSTEM_REFERENCE
FROM THE (SELECT CAST( p_Item_Intf_Data_Tab AS EGO_ITEM_INTF_DATA_TAB) FROM DUAL)
A;
SELECT 'Y' INTO l_org_assignment
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = l_organization_id
AND ORGANIZATION_ID <> MASTER_ORGANIZATION_ID;
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
( SET_PROCESS_ID ,
PROCESS_FLAG ,
TRANSACTION_TYPE ,
TEMPLATE_ID ,
COPY_ITEM_ID ,
COPY_REVISION_ID ,
INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
DESCRIPTION ,
LONG_DESCRIPTION ,
PRIMARY_UOM_CODE ,
PRIMARY_UNIT_OF_MEASURE ,
ITEM_TYPE ,
INVENTORY_ITEM_STATUS_CODE ,
ALLOWED_UNITS_LOOKUP_CODE ,
TRACKING_QUANTITY_IND ,
ONT_PRICING_QTY_SOURCE ,
SECONDARY_DEFAULT_IND ,
DUAL_UOM_DEVIATION_HIGH ,
DUAL_UOM_DEVIATION_LOW ,
SECONDARY_UOM_CODE ,
LIFECYCLE_ID ,
CURRENT_PHASE_ID ,
ITEM_CATALOG_GROUP_ID ,
BOM_ENABLED_FLAG ,
ENG_ITEM_FLAG ,
WEIGHT_UOM_CODE ,
UNIT_WEIGHT ,
ITEM_NUMBER ,
STYLE_ITEM_FLAG ,
STYLE_ITEM_ID ,
GDSN_OUTBOUND_ENABLED_FLAG ,
TRADE_ITEM_DESCRIPTOR,
TRANSACTION_ID,
SOURCE_SYSTEM_REFERENCE,
SOURCE_SYSTEM_ID,
COPY_ORGANIZATION_ID
)
VALUES
( l_set_process_id ,
l_process_flag ,
l_transaction_type ,
l_template_id ,
l_copy_inventory_item_id ,
l_copy_revision_id ,
l_inventory_item_id ,
l_organization_id ,
l_description ,
l_long_description ,
l_primary_uom_code ,
l_primary_unit_of_measure ,
l_item_type ,
l_inventory_item_status_code ,
l_allowed_units_lookup_code ,
l_tracking_quantity_ind ,
l_ont_pricing_qty_source ,
l_secondary_default_ind ,
l_dual_uom_deviation_high ,
l_dual_uom_deviation_low ,
l_secondary_uom_code ,
l_lifecycle_id ,
l_current_phase_id ,
l_item_catalog_group_id ,
l_bom_enabled_flag ,
l_eng_item_flag ,
l_weight_uom_code ,
l_unit_weight ,
l_item_number ,
l_style_item_flag ,
l_style_item_id ,
l_gdsn_outbound_enabled_flag ,
l_trade_item_descriptor,
l_transaction_id,
l_source_system_reference,
EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID,
l_copy_from_organization_id
);
* is unique. It also inserts the record if combination does not exists
* This API sets x_sku_exists as TRUE if combination already exists
* This API sets x_sku_exists as FALSE if combination is not found
* This API sets x_var_attrs_missing as TRUE if some variant attribute
* values are missing.
*
* This API returns 0 if no unexpected errors are there, else
* returns the SQLCODE
*
* This API assumes that INVENTORY_ITEM_ID will be present in the intf table
*/
FUNCTION Validate_SKU_Variant_Usage( p_intf_row_id IN ROWID
, x_sku_exists OUT NOCOPY BOOLEAN
, x_var_attrs_missing OUT NOCOPY BOOLEAN
, x_err_text OUT NOCOPY VARCHAR2
)
RETURN INTEGER IS
CURSOR c_attr_values(c_batch_id NUMBER, c_item_id NUMBER, c_org_id NUMBER, c_item_number VARCHAR2,c_category_id NUMBER)
IS
SELECT
AG_EXT.ATTR_GROUP_ID,
FL_COL.END_USER_COLUMN_NAME,
ATTR_EXT.ATTR_ID,
(CASE ATTR_EXT.DATA_TYPE
WHEN 'C' THEN INTF.ATTR_VALUE_STR
WHEN 'A' THEN INTF.ATTR_VALUE_STR
WHEN 'N' THEN To_Char(INTF.ATTR_VALUE_NUM)
WHEN 'X' THEN To_Char(INTF.ATTR_VALUE_DATE)
WHEN 'Y' THEN To_Char(INTF.ATTR_VALUE_DATE)
END) ATTR_VALUE
FROM
EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
EGO_FND_DF_COL_USGS_EXT ATTR_EXT,
FND_DESCR_FLEX_COLUMN_USAGES FL_COL,
EGO_ITM_USR_ATTR_INTRFC INTF
WHERE AG_EXT.APPLICATION_ID = ATTR_EXT.APPLICATION_ID
AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
AND ATTR_EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
AND ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
AND ATTR_EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME /* DATABASE_COLUMN */
AND AG_EXT.VARIANT = 'Y'
AND INTF.ATTR_GROUP_INT_NAME(+) = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND INTF.ATTR_GROUP_TYPE(+) = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME
AND INTF.ATTR_INT_NAME (+) = FL_COL.END_USER_COLUMN_NAME
AND INTF.DATA_SET_ID (+) = c_batch_id
AND INTF.PROCESS_STATUS (+) = 2
AND INTF.INVENTORY_ITEM_ID (+) = c_item_id /* OR INTF.ITEM_NUMBER (+)= c_item_number*/
AND INTF.ORGANIZATION_ID (+) = c_org_id
AND AG_EXT.ATTR_GROUP_ID IN (SELECT A.ATTR_GROUP_ID
FROM EGO_OBJ_AG_ASSOCS_B a
WHERE A.CLASSIFICATION_CODE IN (SELECT To_Char(micg.ITEM_CATALOG_GROUP_ID)
FROM MTL_ITEM_CATALOG_GROUPS_B micg
CONNECT BY PRIOR micg.PARENT_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
START WITH micg.ITEM_CATALOG_GROUP_ID = c_category_id
)
)
ORDER BY ATTR_EXT.ATTR_ID;
SELECT SET_PROCESS_ID, INVENTORY_ITEM_ID, ITEM_NUMBER, ORGANIZATION_ID, ITEM_CATALOG_GROUP_ID, STYLE_ITEM_ID
INTO l_batch_id, l_sku_item_id, l_item_number, l_org_id, l_category_id, l_style_item_id
FROM MTL_SYSTEM_ITEMS_INTERFACE
WHERE ROWID = p_intf_row_id;
INSERT INTO EGO_SKU_VARIANT_ATTR_USAGES
(
ORGANIZATION_ID,
STYLE_ITEM_ID,
CONCATENATED_VA_SEGMENTS,
SKU_ITEM_ID,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY
)
VALUES
(
l_org_id,
l_style_item_id,
l_concat_value,
l_sku_item_id,
l_login_id,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id
);
Debug_Conc_Log('Inserted Successfully');
INSERT INTO ego_style_variant_attr_vs
( inventory_item_id,
value_set_id,
attribute_id,
last_update_login,
creation_date,
created_by
)
SELECT p_inventory_item_id,
fl_col.flex_value_set_id,
attr_ext.attr_id,
l_login_id,
l_sysdate,
l_user_id
FROM EGO_FND_DSC_FLX_CTX_EXT AG_EXT,
EGO_FND_DF_COL_USGS_EXT ATTR_EXT,
FND_DESCR_FLEX_COLUMN_USAGES FL_COL
WHERE AG_EXT.APPLICATION_ID = ATTR_EXT.APPLICATION_ID
AND AG_EXT.DESCRIPTIVE_FLEXFIELD_NAME = ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
AND AG_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
AND ATTR_EXT.APPLICATION_ID = FL_COL.APPLICATION_ID
AND ATTR_EXT.DESCRIPTIVE_FLEXFIELD_NAME = FL_COL.DESCRIPTIVE_FLEXFIELD_NAME /* AG_TYPE*/
AND ATTR_EXT.DESCRIPTIVE_FLEX_CONTEXT_CODE = FL_COL.DESCRIPTIVE_FLEX_CONTEXT_CODE /* AG_NAME*/
AND ATTR_EXT.APPLICATION_COLUMN_NAME = FL_COL.APPLICATION_COLUMN_NAME /* DATABASE_COLUMN */
AND AG_EXT.VARIANT = 'Y'
AND AG_EXT.ATTR_GROUP_ID IN (SELECT A.ATTR_GROUP_ID
FROM EGO_OBJ_AG_ASSOCS_B a
WHERE A.CLASSIFICATION_CODE IN (SELECT To_Char(micg.ITEM_CATALOG_GROUP_ID)
FROM MTL_ITEM_CATALOG_GROUPS_B micg
CONNECT BY PRIOR micg.PARENT_CATALOG_GROUP_ID = micg.ITEM_CATALOG_GROUP_ID
START WITH micg.ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id
)
);
SELECT assoc.ATTR_GROUP_ID
FROM
EGO_OBJ_AG_ASSOCS_B assoc,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE assoc.ATTR_GROUP_ID = AG_EXT.ATTR_GROUP_ID
AND NVL(ag_ext.VARIANT, 'N') = 'Y'
AND NVL(assoc.ENABLED_FLAG, 'Y') = 'Y'
AND assoc.DATA_LEVEL_ID = l_item_data_level_id
AND assoc.OBJECT_ID = l_itm_obj_id
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 = p_item_catalog_group_id
);
SELECT assoc.ATTR_GROUP_ID
FROM
EGO_OBJ_AG_ASSOCS_B assoc,
EGO_FND_DSC_FLX_CTX_EXT ag_ext
WHERE assoc.ATTR_GROUP_ID = AG_EXT.ATTR_GROUP_ID
AND NVL(ag_ext.VARIANT, 'N') = 'Y'
AND NVL(assoc.ENABLED_FLAG, 'Y') = 'Y'
AND assoc.DATA_LEVEL_ID = l_item_data_level_id
AND assoc.OBJECT_ID = l_itm_obj_id
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 = p_parent_catalog_group_id
UNION ALL
SELECT TO_CHAR(p_item_catalog_group_id) FROM DUAL
);
SELECT OBJECT_ID INTO l_itm_obj_id
FROM FND_OBJECTS
WHERE OBJ_NAME = 'EGO_ITEM';
SELECT DATA_LEVEL_ID INTO l_item_data_level_id
FROM EGO_DATA_LEVEL_B
WHERE ATTR_GROUP_TYPE = 'EGO_ITEMMGMT_GROUP'
AND APPLICATION_ID = 431
AND DATA_LEVEL_NAME = 'ITEM_LEVEL';
SELECT COUNT(1)
INTO l_style_item_count
FROM MTL_SYSTEM_ITEMS_B
WHERE STYLE_ITEM_FLAG = 'Y'
AND ITEM_CATALOG_GROUP_ID = p_item_catalog_group_id;
l_new_variants.DELETE(l_index);
l_existing_variants.DELETE(l_index);
PROCEDURE Insert_Fake_Row_For_Item( p_commit IN VARCHAR2 DEFAULT G_FALSE
,p_batch_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_item_number IN VARCHAR2
,p_style_item_flag IN VARCHAR2
,p_style_item_id IN NUMBER
,p_item_catalog_group_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2)
IS
BEGIN
INSERT INTO MTL_SYSTEM_ITEMS_INTERFACE
( SET_PROCESS_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
ITEM_CATALOG_GROUP_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
ITEM_NUMBER,
STYLE_ITEM_FLAG,
STYLE_ITEM_ID,
TRANSACTION_ID,
SOURCE_SYSTEM_ID,
CONFIRM_STATUS
)
VALUES
( p_batch_id,
1,
'SYNC',
p_item_catalog_group_id,
p_inventory_item_id,
p_organization_id,
p_item_number,
p_style_item_flag,
p_style_item_id,
MTL_SYSTEM_ITEMS_INTERFACE_S.NEXTVAL,
EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID,
'FK'
);
END Insert_Fake_Row_For_Item;
SELECT MENU_ID, USER_MENU_NAME INTO l_menu_id, l_menu_disp_name
FROM FND_MENUS_VL
WHERE MENU_NAME = p_role_name;
SELECT ORGANIZATION_CODE INTO l_org_code
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_organization_id;
SELECT OBJECT_ID INTO l_object_id
FROM FND_OBJECTS
WHERE OBJ_NAME = 'EGO_ITEM';
INSERT INTO EGO_ITEM_PEOPLE_INTF
( DATA_SET_ID,
PROCESS_STATUS,
TRANSACTION_TYPE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
GRANTEE_PARTY_ID,
INTERNAL_ROLE_NAME,
GRANTEE_TYPE,
START_DATE,
END_DATE,
ORGANIZATION_CODE,
DISPLAY_ROLE_NAME,
SOURCE_SYSTEM_ID,
CREATED_BY
)
SELECT
p_batch_id,
1,
'CREATE',
msib.INVENTORY_ITEM_ID,
p_organization_id,
p_grantee_party_id,
p_role_name,
p_grantee_type,
SYSDATE,
p_end_date,
l_org_code,
l_menu_disp_name,
l_ss_id,
-99
FROM MTL_SYSTEM_ITEMS_B msib, MTL_PARAMETERS mp
WHERE msib.STYLE_ITEM_ID = p_style_item_id
AND msib.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND mp.ORGANIZATION_ID = p_organization_id
AND NOT EXISTS (SELECT NULL FROM FND_GRANTS fg
WHERE fg.INSTANCE_TYPE = 'INSTANCE'
AND fg.INSTANCE_PK1_VALUE = TO_CHAR(msib.INVENTORY_ITEM_ID)
AND fg.INSTANCE_PK2_VALUE = TO_CHAR(p_organization_id)
AND fg.OBJECT_ID = l_object_id
AND NVL(fg.END_DATE, SYSDATE) >= SYSDATE
AND fg.MENU_ID = l_menu_id
AND fg.GRANTEE_TYPE = p_grantee_type
AND fg.GRANTEE_KEY = 'HZ_PARTY:'||p_grantee_party_id
);
* This method inserts Category assignment records for SKUs in the mtl categories interface table.
*/
PROCEDURE Propagate_Category_To_SKUs ( p_commit IN VARCHAR2 DEFAULT G_FALSE
,p_batch_id IN NUMBER
,p_style_item_id IN NUMBER
,p_organization_id IN NUMBER
,p_category_set_id IN NUMBER
,p_category_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_data OUT NOCOPY VARCHAR2)
IS
l_ss_id NUMBER := EGO_IMPORT_PVT.G_PDH_SOURCE_SYSTEM_ID;
INSERT INTO MTL_ITEM_CATEGORIES_INTERFACE
( SET_PROCESS_ID,
PROCESS_FLAG,
TRANSACTION_TYPE,
INVENTORY_ITEM_ID,
ORGANIZATION_ID,
CATEGORY_SET_ID,
CATEGORY_ID,
SOURCE_SYSTEM_ID,
CREATED_BY
)
SELECT
p_batch_id,
1,
'CREATE',
msib.INVENTORY_ITEM_ID,
p_organization_id,
p_category_set_id,
p_category_id,
l_ss_id,
-99
FROM MTL_SYSTEM_ITEMS_B msib, MTL_PARAMETERS mp
WHERE msib.STYLE_ITEM_ID = p_style_item_id
AND msib.ORGANIZATION_ID = mp.MASTER_ORGANIZATION_ID
AND mp.ORGANIZATION_ID = p_organization_id
AND NOT EXISTS (SELECT NULL FROM MTL_ITEM_CATEGORIES mic
WHERE mic.INVENTORY_ITEM_ID = msib.INVENTORY_ITEM_ID
AND mic.ORGANIZATION_ID = p_organization_id
AND mic.CATEGORY_SET_ID = p_category_set_id
AND mic.CATEGORY_ID = p_category_id
);