The following lines contain the word 'select', 'insert', 'update' or 'delete':
CAT_NOT_INSERTED EXCEPTION ;
select userenv('LANG')
into l_Source_Lang
from dual ;
GOTO No_Insert;
select MTL_CATEGORIES_S.nextval
into l_New_Category_ID
from dual ;
INSERT INTO MTL_CATEGORIES_B
(
CATEGORY_ID ,
STRUCTURE_ID ,
DISABLE_DATE ,
SEGMENT1 ,
SEGMENT2 ,
SEGMENT3 ,
SEGMENT4 ,
SEGMENT5 ,
SEGMENT6 ,
SEGMENT7 ,
SEGMENT8 ,
SEGMENT9 ,
SEGMENT10 ,
SEGMENT11 ,
SEGMENT12 ,
SEGMENT13 ,
SEGMENT14 ,
SEGMENT15 ,
SEGMENT16 ,
SEGMENT17 ,
SEGMENT18 ,
SEGMENT19 ,
SEGMENT20 ,
SUMMARY_FLAG ,
ENABLED_FLAG ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
l_New_Category_ID ,
BOM_PFI_PVT.G_PF_Structure_ID ,
null ,
SI.SEGMENT1 ,
SI.SEGMENT2 ,
SI.SEGMENT3 ,
SI.SEGMENT4 ,
SI.SEGMENT5 ,
SI.SEGMENT6 ,
SI.SEGMENT7 ,
SI.SEGMENT8 ,
SI.SEGMENT9 ,
SI.SEGMENT10 ,
SI.SEGMENT11 ,
SI.SEGMENT12 ,
SI.SEGMENT13 ,
SI.SEGMENT14 ,
SI.SEGMENT15 ,
SI.SEGMENT16 ,
SI.SEGMENT17 ,
SI.SEGMENT18 ,
SI.SEGMENT19 ,
SI.SEGMENT20 ,
'N' ,
'Y' ,
sysdate ,
v_user_id ,
sysdate ,
v_user_id ,
null
FROM MTL_SYSTEM_ITEMS_B SI
WHERE INVENTORY_ITEM_ID = l_item_id
AND ORGANIZATION_ID = l_org_id ;
RAISE CAT_NOT_INSERTED;
insert into MTL_CATEGORIES_TL (
CATEGORY_ID,
LANGUAGE,
SOURCE_LANG,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
) select
l_New_Category_ID ,
L.LANGUAGE_CODE ,
l_Source_Lang ,
l_Description ,
sysdate ,
v_user_id ,
sysdate ,
v_user_id ,
null
from FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and not exists
( select NULL
from MTL_CATEGORIES_TL T
where T.CATEGORY_ID = l_New_Category_ID
and T.LANGUAGE = L.LANGUAGE_CODE );
<>
NULL;
WHEN CAT_NOT_INSERTED THEN
p_Cat_Create_Num := 0 ;
l_return_err := 'BOM_PFI_PVT.Create_PF_Category: cannot insert category' ;
p_Delete_Cat_Tbl IN OUT NOCOPY Delete_Category_Tbl_Type
)
IS
l_return_sts NUMBER := 0 ;
p_Delete_Cat_Tbl( p_Cat_Num ).item_id := p_item_id ;
p_Delete_Cat_Tbl( p_Cat_Num ).org_id := p_org_id ;
PROCEDURE Delete_PF_Category
( p_return_sts IN OUT NOCOPY NUMBER ,
p_return_err IN OUT NOCOPY VARCHAR2 ,
p_Cat_Num IN OUT NOCOPY BINARY_INTEGER ,
p_Delete_Cat_Tbl IN OUT NOCOPY Delete_Category_Tbl_Type
)
IS
l_return_sts NUMBER := 0 ;
l_item_id := p_Delete_Cat_Tbl( l_Cat_Ind ).item_id ;
l_org_id := p_Delete_Cat_Tbl( l_Cat_Ind ).org_id ;
DELETE FROM MTL_CATEGORIES_TL
WHERE CATEGORY_ID = l_category_id ;
DELETE FROM MTL_CATEGORIES_B
WHERE CATEGORY_ID = l_category_id ;
l_return_err := 'BOM_PFI_PVT.Delete_PF_Category: ' || l_return_err ;
l_return_err := 'BOM_PFI_PVT.Delete_PF_Category: ' || SQLERRM ;
END Delete_PF_Category;
INSERT INTO MTL_ITEM_CATEGORIES
( INVENTORY_ITEM_ID ,
ORGANIZATION_ID ,
CATEGORY_SET_ID ,
CATEGORY_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
SELECT
l_item_id ,
l_org_id ,
G_PF_Category_Set_ID ,
l_category_id ,
sysdate ,
v_user_id ,
sysdate ,
v_user_id ,
null
FROM dual
WHERE NOT EXISTS
( SELECT 'x'
FROM MTL_ITEM_CATEGORIES icat
WHERE icat.INVENTORY_ITEM_ID = l_item_id
AND icat.ORGANIZATION_ID = l_org_id
AND icat.CATEGORY_SET_ID = G_PF_Category_Set_ID
);
DELETE FROM MTL_ITEM_CATEGORIES
WHERE INVENTORY_ITEM_ID = p_item_id
AND ORGANIZATION_ID = p_org_id
AND CATEGORY_SET_ID = G_PF_Category_Set_ID ;
SELECT MASTER_ORGANIZATION_ID INTO l_master_org_id
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_org_id ;
SELECT MASTER_ORGANIZATION_ID INTO l_master_org_id
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID = p_org_id ;
select application_column_name
from FND_ID_FLEX_SEGMENTS
where application_id = 401
and id_flex_code = 'MSTK'
and id_flex_num = 101
and enabled_flag = 'Y'
order by segment_num;
select application_column_name
from FND_ID_FLEX_SEGMENTS
where application_id = 401
and id_flex_code = 'MCAT'
and id_flex_num = G_PF_Structure_ID
and enabled_flag = 'Y'
order by segment_num;