[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_CATEGORY_ID in NUMBER,
X_DESCRIPTION in VARCHAR2,
X_STRUCTURE_ID in NUMBER,
X_DISABLE_DATE in DATE,
X_WEB_STATUS in VARCHAR2,
X_SUPPLIER_ENABLED_FLAG in VARCHAR2,
X_SEGMENT1 in VARCHAR2,
X_SEGMENT2 in VARCHAR2,
X_SEGMENT3 in VARCHAR2,
X_SEGMENT4 in VARCHAR2,
X_SEGMENT5 in VARCHAR2,
X_SEGMENT6 in VARCHAR2,
X_SEGMENT7 in VARCHAR2,
X_SEGMENT8 in VARCHAR2,
X_SEGMENT9 in VARCHAR2,
X_SEGMENT10 in VARCHAR2,
X_SEGMENT11 in VARCHAR2,
X_SEGMENT12 in VARCHAR2,
X_SEGMENT13 in VARCHAR2,
X_SEGMENT14 in VARCHAR2,
X_SEGMENT15 in VARCHAR2,
X_SEGMENT16 in VARCHAR2,
X_SEGMENT17 in VARCHAR2,
X_SEGMENT18 in VARCHAR2,
X_SEGMENT19 in VARCHAR2,
X_SEGMENT20 in VARCHAR2,
X_SUMMARY_FLAG in VARCHAR2,
X_ENABLED_FLAG in VARCHAR2,
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
-- X_REQUEST_ID in NUMBER,
) is
cursor C is
select ROWID
from MTL_CATEGORIES_B
where CATEGORY_ID = X_CATEGORY_ID ;
insert into MTL_CATEGORIES_B (
CATEGORY_ID,
STRUCTURE_ID,
DISABLE_DATE,
WEB_STATUS,
SUPPLIER_ENABLED_FLAG,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
-- WH_UPDATE_DATE,
-- TOTAL_PROD_ID,
-- REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
) values (
X_CATEGORY_ID,
X_STRUCTURE_ID,
X_DISABLE_DATE,
X_WEB_STATUS,
X_SUPPLIER_ENABLED_FLAG,
X_SEGMENT1,
X_SEGMENT2,
X_SEGMENT3,
X_SEGMENT4,
X_SEGMENT5,
X_SEGMENT6,
X_SEGMENT7,
X_SEGMENT8,
X_SEGMENT9,
X_SEGMENT10,
X_SEGMENT11,
X_SEGMENT12,
X_SEGMENT13,
X_SEGMENT14,
X_SEGMENT15,
X_SEGMENT16,
X_SEGMENT17,
X_SEGMENT18,
X_SEGMENT19,
X_SEGMENT20,
X_SUMMARY_FLAG,
X_ENABLED_FLAG,
X_START_DATE_ACTIVE,
X_END_DATE_ACTIVE,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
-- X_WH_UPDATE_DATE,
-- X_TOTAL_PROD_ID,
-- X_REQUEST_ID,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN
);
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
X_CATEGORY_ID,
L.LANGUAGE_CODE,
userenv('LANG'),
X_DESCRIPTION,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN
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 = X_CATEGORY_ID
and T.LANGUAGE = L.LANGUAGE_CODE );
end INSERT_ROW;
select
STRUCTURE_ID,
DISABLE_DATE,
WEB_STATUS,
SUPPLIER_ENABLED_FLAG,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6,
SEGMENT7,
SEGMENT8,
SEGMENT9,
SEGMENT10,
SEGMENT11,
SEGMENT12,
SEGMENT13,
SEGMENT14,
SEGMENT15,
SEGMENT16,
SEGMENT17,
SEGMENT18,
SEGMENT19,
SEGMENT20,
SUMMARY_FLAG,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
-- WH_UPDATE_DATE,
-- TOTAL_PROD_ID,
-- REQUEST_ID,
from MTL_CATEGORIES_B
where CATEGORY_ID = X_CATEGORY_ID
for update of CATEGORY_ID nowait;
select
DESCRIPTION,
decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
from MTL_CATEGORIES_TL
where CATEGORY_ID = X_CATEGORY_ID
-- Commented out. All translation rows need to be locked.
-- and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
for update of CATEGORY_ID nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
procedure UPDATE_ROW
(
X_CATEGORY_ID in NUMBER,
X_DESCRIPTION in VARCHAR2,
X_STRUCTURE_ID in NUMBER,
X_DISABLE_DATE in DATE,
X_WEB_STATUS in VARCHAR2,
X_SUPPLIER_ENABLED_FLAG in VARCHAR2,
X_SEGMENT1 in VARCHAR2,
X_SEGMENT2 in VARCHAR2,
X_SEGMENT3 in VARCHAR2,
X_SEGMENT4 in VARCHAR2,
X_SEGMENT5 in VARCHAR2,
X_SEGMENT6 in VARCHAR2,
X_SEGMENT7 in VARCHAR2,
X_SEGMENT8 in VARCHAR2,
X_SEGMENT9 in VARCHAR2,
X_SEGMENT10 in VARCHAR2,
X_SEGMENT11 in VARCHAR2,
X_SEGMENT12 in VARCHAR2,
X_SEGMENT13 in VARCHAR2,
X_SEGMENT14 in VARCHAR2,
X_SEGMENT15 in VARCHAR2,
X_SEGMENT16 in VARCHAR2,
X_SEGMENT17 in VARCHAR2,
X_SEGMENT18 in VARCHAR2,
X_SEGMENT19 in VARCHAR2,
X_SEGMENT20 in VARCHAR2,
X_SUMMARY_FLAG in VARCHAR2,
X_ENABLED_FLAG in VARCHAR2,
X_START_DATE_ACTIVE in DATE,
X_END_DATE_ACTIVE in DATE,
X_ATTRIBUTE_CATEGORY in VARCHAR2,
X_ATTRIBUTE1 in VARCHAR2,
X_ATTRIBUTE2 in VARCHAR2,
X_ATTRIBUTE3 in VARCHAR2,
X_ATTRIBUTE4 in VARCHAR2,
X_ATTRIBUTE5 in VARCHAR2,
X_ATTRIBUTE6 in VARCHAR2,
X_ATTRIBUTE7 in VARCHAR2,
X_ATTRIBUTE8 in VARCHAR2,
X_ATTRIBUTE9 in VARCHAR2,
X_ATTRIBUTE10 in VARCHAR2,
X_ATTRIBUTE11 in VARCHAR2,
X_ATTRIBUTE12 in VARCHAR2,
X_ATTRIBUTE13 in VARCHAR2,
X_ATTRIBUTE14 in VARCHAR2,
X_ATTRIBUTE15 in VARCHAR2,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
-- X_REQUEST_ID in NUMBER,
)
IS
l_return_status VARCHAR2(1);
update MTL_CATEGORIES_B
set
STRUCTURE_ID = X_STRUCTURE_ID,
DISABLE_DATE = X_DISABLE_DATE,
WEB_STATUS = X_WEB_STATUS,
SUPPLIER_ENABLED_FLAG = X_SUPPLIER_ENABLED_FLAG,
SEGMENT1 = X_SEGMENT1,
SEGMENT2 = X_SEGMENT2,
SEGMENT3 = X_SEGMENT3,
SEGMENT4 = X_SEGMENT4,
SEGMENT5 = X_SEGMENT5,
SEGMENT6 = X_SEGMENT6,
SEGMENT7 = X_SEGMENT7,
SEGMENT8 = X_SEGMENT8,
SEGMENT9 = X_SEGMENT9,
SEGMENT10 = X_SEGMENT10,
SEGMENT11 = X_SEGMENT11,
SEGMENT12 = X_SEGMENT12,
SEGMENT13 = X_SEGMENT13,
SEGMENT14 = X_SEGMENT14,
SEGMENT15 = X_SEGMENT15,
SEGMENT16 = X_SEGMENT16,
SEGMENT17 = X_SEGMENT17,
SEGMENT18 = X_SEGMENT18,
SEGMENT19 = X_SEGMENT19,
SEGMENT20 = X_SEGMENT20,
SUMMARY_FLAG = X_SUMMARY_FLAG,
ENABLED_FLAG = X_ENABLED_FLAG,
START_DATE_ACTIVE = X_START_DATE_ACTIVE,
END_DATE_ACTIVE = X_END_DATE_ACTIVE,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
-- WH_UPDATE_DATE = X_WH_UPDATE_DATE,
-- TOTAL_PROD_ID = X_TOTAL_PROD_ID,
-- REQUEST_ID = X_REQUEST_ID,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
where
CATEGORY_ID = X_CATEGORY_ID;
update MTL_CATEGORIES_TL
set
DESCRIPTION = X_DESCRIPTION,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SOURCE_LANG = userenv('LANG')
where
CATEGORY_ID = X_CATEGORY_ID
and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
' ENI_ITEMS_STAR_PKG.Update_Categories '||
' ( '||
' p_api_version => 1.0 '||
' , p_init_msg_list => FND_API.g_TRUE '||
' , p_category_id => :X_CATEGORY_ID '||
' , p_structure_id => :X_STRUCTURE_ID '||
' , x_return_status => :l_return_status '||
' , x_msg_count => :l_msg_count '||
' , x_msg_data => :l_msg_data '||
' ); '||
,p_dml_type => 'UPDATE'
,p_category_id => X_CATEGORY_ID);
,p_dml_type => 'UPDATE'
,p_category_id => X_CATEGORY_ID
,p_structure_id => X_STRUCTURE_ID
,p_commit => true); -- @ for bug 14248843
end UPDATE_ROW;
procedure DELETE_ROW (
X_CATEGORY_ID in NUMBER
) is
begin
/*
fnd_message.set_name('INV', 'CANNOT_DELETE_RECORD');
raise_application_error( -20000, 'CANNOT_DELETE_RECORD' );
delete from MTL_CATEGORIES_TL
where CATEGORY_ID = X_CATEGORY_ID ;
delete from MTL_CATEGORIES_B
where CATEGORY_ID = X_CATEGORY_ID ;
end DELETE_ROW;
delete from MTL_CATEGORIES_TL T
where not exists
( select NULL
from MTL_CATEGORIES_B B
where B.CATEGORY_ID = T.CATEGORY_ID
);
update MTL_CATEGORIES_TL T set (
DESCRIPTION
) = ( select
B.DESCRIPTION
from MTL_CATEGORIES_TL B
where B.CATEGORY_ID = T.CATEGORY_ID
and B.LANGUAGE = T.SOURCE_LANG )
where (
T.CATEGORY_ID,
T.LANGUAGE
) in ( select
SUBT.CATEGORY_ID,
SUBT.LANGUAGE
from MTL_CATEGORIES_TL SUBB,
MTL_CATEGORIES_TL SUBT
where SUBB.CATEGORY_ID = SUBT.CATEGORY_ID
and SUBB.LANGUAGE = SUBT.SOURCE_LANG
and ( SUBB.DESCRIPTION <> SUBT.DESCRIPTION
or ( SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null )
or ( SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null ) )
);
insert into MTL_CATEGORIES_TL (
CREATED_BY,
LAST_UPDATE_LOGIN,
CATEGORY_ID,
DESCRIPTION,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
LANGUAGE,
SOURCE_LANG
) select
B.CREATED_BY,
B.LAST_UPDATE_LOGIN,
B.CATEGORY_ID,
B.DESCRIPTION,
B.LAST_UPDATE_DATE,
B.LAST_UPDATED_BY,
B.CREATION_DATE,
L.LANGUAGE_CODE,
B.SOURCE_LANG
from MTL_CATEGORIES_TL B,
FND_LANGUAGES L
where L.INSTALLED_FLAG in ('I', 'B')
and B.LANGUAGE = userenv('LANG')
and not exists
( select NULL
from MTL_CATEGORIES_TL T
where T.CATEGORY_ID = B.CATEGORY_ID
and T.LANGUAGE = L.LANGUAGE_CODE );
SELECT B.STRUCTURE_ID
INTO l_structure_id
FROM MTL_DEFAULT_CATEGORY_SETS A,
MTL_CATEGORY_SETS_B B
WHERE FUNCTIONAL_AREA_ID = (select lookup_code from mfg_lookups
where lookup_type = 'MTL_FUNCTIONAL_AREAS' and upper(meaning) = upper(x_upload_to_functional_area))
AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
SELECT ID_FLEX_NUM
INTO l_structure_id
FROM FND_ID_FLEX_STRUCTURES
WHERE APPLICATION_ID = (select application_id from fnd_application
where application_short_name =
x_application_short_name)
AND ID_FLEX_CODE = 'MCAT'
AND ID_FLEX_STRUCTURE_CODE = x_structure_code; /* Bug 6975120
SELECT category_id
INTO l_category_id
FROM mtl_categories_kfv
WHERE structure_id = l_structure_id
AND concatenated_segments = x_category_name;
UPDATE mtl_categories_tl
SET description = NVL(x_description, description)
, last_update_date = SYSDATE
, last_updated_by = f_luby
, last_update_login = 0
, source_lang = userenv('LANG')
WHERE category_id = l_category_id
AND userenv('LANG') IN (language, source_lang);
,X_LAST_UPDATE_DATE IN MTL_CATEGORIES_B.LAST_UPDATE_DATE%TYPE
,X_DESCRIPTION IN MTL_CATEGORIES_TL.DESCRIPTION%TYPE
,X_APPLICATION_SHORT_NAME IN VARCHAR2
,X_UPLOAD_TO_FUNCTIONAL_AREA IN VARCHAR2
) IS
l_category_set_id MTL_CATEGORY_SETS_B.CATEGORY_SET_ID%TYPE;
SELECT application_column_name,rownum
FROM fnd_id_flex_segments
WHERE application_id = (select application_id from fnd_application
where application_short_name =
x_application_short_name)
AND id_flex_code = 'MCAT'
AND id_flex_num = l_structure_id
AND enabled_flag = 'Y'
ORDER BY segment_num ASC;
SELECT CATEGORY_ID
FROM MTL_CATEGORIES_B_KFV
WHERE structure_id = cp_structure_id
AND CONCATENATED_SEGMENTS = cp_concatenated_segs;*/
SELECT A.CATEGORY_SET_ID, B.STRUCTURE_ID
INTO l_category_set_id, l_structure_id
FROM MTL_DEFAULT_CATEGORY_SETS A,
MTL_CATEGORY_SETS B
WHERE FUNCTIONAL_AREA_ID = (select lookup_code from mfg_lookups
where lookup_type = 'MTL_FUNCTIONAL_AREAS'
and upper(meaning) = upper(x_upload_to_functional_area))
AND A.CATEGORY_SET_ID = B.CATEGORY_SET_ID;
SELECT ID_FLEX_NUM
INTO l_structure_id
FROM FND_ID_FLEX_STRUCTURES
WHERE APPLICATION_ID = (select application_id from fnd_application
where application_short_name =
x_application_short_name)
AND ID_FLEX_CODE = 'MCAT'
AND ID_FLEX_STRUCTURE_CODE = x_structure_code;
FND_MESSAGE.SET_TOKEN('REASON','Category to be updated not found.');
INV_ITEM_CATEGORY_PUB.Update_Category (
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_ERRORCODE => l_errorcode,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data,
P_CATEGORY_REC => l_category_rec );