[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OKL_FE_CRIT_CAT_DEF_TL t
WHERE NOT EXISTS(SELECT NULL
FROM OKL_FE_CRIT_CAT_DEF_B b
WHERE b.crit_cat_def_id = t.crit_cat_def_id);
UPDATE OKL_FE_CRIT_CAT_DEF_TL t
SET(crit_cat_desc) = (SELECT
-- LANGUAGE,
-- B.LANGUAGE,
b.crit_cat_desc
FROM OKL_FE_CRIT_CAT_DEF_TL b
WHERE b.crit_cat_def_id = t.crit_cat_def_id
AND b.language = t.source_lang)
WHERE (t.crit_cat_def_id, t.language) IN(SELECT subt.crit_cat_def_id ,subt.language
FROM OKL_FE_CRIT_CAT_DEF_TL subb ,OKL_FE_CRIT_CAT_DEF_TL subt
WHERE subb.crit_cat_def_id = subt.crit_cat_def_id AND subb.language = subt.language AND ( -- SUBB.LANGUAGE <> SUBT.LANGUAGE OR
subb.crit_cat_desc <> subt.crit_cat_desc OR (subb.language IS NOT NULL
AND subt.language IS NULL)
OR (subb.crit_cat_desc IS NULL AND subt.crit_cat_desc IS NOT NULL)));
INSERT INTO OKL_FE_CRIT_CAT_DEF_TL
(crit_cat_def_id,
language,
source_lang,
sfwt_flag,
crit_cat_desc,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT b.crit_cat_def_id,
l.language_code,
b.source_lang,
b.sfwt_flag,
b.crit_cat_desc,
b.CREATED_BY,
b.CREATION_DATE,
b.LAST_UPDATED_BY,
b.LAST_UPDATE_DATE,
b.LAST_UPDATE_LOGIN
FROM OKL_FE_CRIT_CAT_DEF_TL b
,fnd_languages l
WHERE l.installed_flag IN('I', 'B')
AND b.language = userenv('LANG')
AND NOT EXISTS(SELECT NULL
FROM OKL_FE_CRIT_CAT_DEF_TL t
WHERE t.crit_cat_def_id = b.crit_cat_def_id AND t.language = l.language_code);
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
p_to.last_updated_by := p_from.last_updated_by;
p_to.last_update_date := p_from.last_update_date;
p_to.last_update_login := p_from.last_update_login;
SELECT object_version_number
FROM okl_fe_crit_cat_def_b
WHERE crit_cat_def_id = p_eccb_rec.crit_cat_def_id
AND object_version_number = p_eccb_rec.object_version_number
FOR UPDATE OF object_version_number NOWAIT;
SELECT object_version_number
FROM okl_fe_crit_cat_def_b
WHERE crit_cat_def_id = p_eccb_rec.crit_cat_def_id;
okl_api.set_message(g_fnd_app, g_form_record_deleted);
okl_api.set_message(g_app_name, g_record_logically_deleted);
SELECT *
FROM okl_fe_crit_cat_def_tl
WHERE crit_cat_def_id = p_ecctl_rec.crit_cat_def_id
FOR UPDATE NOWAIT;
okl_api.set_message(g_fnd_app, g_form_record_deleted);
SELECT crit_cat_def_id
,object_version_number
,crit_cat_name
,ecc_ac_flag
,orig_crit_cat_def_id
,value_type_code
,data_type_code
,enabled_yn
,seeded_yn
,function_id
,source_yn
,sql_statement
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_crit_cat_def_b
WHERE okl_fe_crit_cat_def_b.crit_cat_def_id = p_id;
,l_eccb_rec.last_updated_by
,l_eccb_rec.last_update_date
,l_eccb_rec.last_update_login ;
SELECT crit_cat_def_id
,language
,source_lang
,sfwt_flag
,crit_cat_desc
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_crit_cat_def_tl
WHERE okl_fe_crit_cat_def_tl.crit_cat_def_id = p_id
AND okl_fe_crit_cat_def_tl.language = p_language;
,l_ecctl_rec.last_updated_by
,l_ecctl_rec.last_update_date
,l_ecctl_rec.last_update_login ;
SELECT crit_cat_def_id
,object_version_number
,ecc_ac_flag
,orig_crit_cat_def_id
,crit_cat_name
,crit_cat_desc
,sfwt_flag
,value_type_code
,data_type_code
,enabled_yn
,seeded_yn
,function_id
,source_yn
,sql_statement
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_crit_cat_def_v
WHERE okl_fe_crit_cat_def_v.crit_cat_def_id = p_id;
,l_eccv_rec.last_updated_by
,l_eccv_rec.last_update_date
,l_eccv_rec.last_update_login ;
IF (l_eccv_rec.last_updated_by = okl_api.g_miss_num) THEN
l_eccv_rec.last_updated_by := NULL;
IF (l_eccv_rec.last_update_date = okl_api.g_miss_date) THEN
l_eccv_rec.last_update_date := NULL;
IF (l_eccv_rec.last_update_login = okl_api.g_miss_num) THEN
l_eccv_rec.last_update_login := NULL;
PROCEDURE insert_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccb_rec IN okl_eccb_rec
,x_eccb_rec OUT NOCOPY okl_eccb_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'insert_row_b';
INSERT INTO okl_fe_crit_cat_def_b
(crit_cat_def_id
,object_version_number
,crit_cat_name
,ecc_ac_flag
,orig_crit_cat_def_id
,value_type_code
,data_type_code
,enabled_yn
,seeded_yn
,function_id
,source_yn
,sql_statement
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES (l_eccb_rec.crit_cat_def_id
,l_eccb_rec.object_version_number
,l_eccb_rec.crit_cat_name
,l_eccb_rec.ecc_ac_flag
,l_eccb_rec.orig_crit_cat_def_id
,l_eccb_rec.value_type_code
,l_eccb_rec.data_type_code
,l_eccb_rec.enabled_yn
,l_eccb_rec.seeded_yn
,l_eccb_rec.function_id
,l_eccb_rec.source_yn
,l_eccb_rec.sql_statement
,l_eccb_rec.created_by
,l_eccb_rec.creation_date
,l_eccb_rec.last_updated_by
,l_eccb_rec.last_update_date
,l_eccb_rec.last_update_login);
END insert_row;
PROCEDURE insert_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_ecctl_rec IN okl_ecctl_rec
,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'insert_row_tl';
SELECT *
FROM fnd_languages
WHERE installed_flag IN('I', 'B');
INSERT INTO okl_fe_crit_cat_def_tl
(crit_cat_def_id
,language
,source_lang
,sfwt_flag
,crit_cat_desc
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES (l_ecctl_rec.crit_cat_def_id
,l_ecctl_rec.language
,l_ecctl_rec.source_lang
,l_ecctl_rec.sfwt_flag
,l_ecctl_rec.crit_cat_desc
,l_ecctl_rec.created_by
,l_ecctl_rec.creation_date
,l_ecctl_rec.last_updated_by
,l_ecctl_rec.last_update_date
,l_ecctl_rec.last_update_login);
END insert_row;
PROCEDURE insert_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccv_rec IN okl_eccv_rec
,x_eccv_rec OUT NOCOPY okl_eccv_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'v_insert_row';
l_eccv_rec.last_update_date := sysdate;
l_eccv_rec.last_updated_by := fnd_global.user_id;
l_eccv_rec.last_update_login := fnd_global.login_id;
insert_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_eccb_rec
,lx_eccb_rec);
insert_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_ecctl_rec
,lx_ecctl_rec);
END insert_row;
PROCEDURE insert_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccv_tbl IN okl_eccv_tbl
,x_eccv_tbl OUT NOCOPY okl_eccv_tbl) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'tbl_insert_row';
insert_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_eccv_rec => p_eccv_tbl(i)
,x_eccv_rec => x_eccv_tbl(i));
END insert_row;
PROCEDURE update_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccb_rec IN okl_eccb_rec
,x_eccb_rec OUT NOCOPY okl_eccb_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'update_row_b';
UPDATE okl_fe_crit_cat_def_b
SET crit_cat_def_id = l_eccb_rec.crit_cat_def_id
,object_version_number = l_eccb_rec.object_version_number + 1
,crit_cat_name = l_eccb_rec.crit_cat_name
,ecc_ac_flag = l_eccb_rec.ecc_ac_flag
,orig_crit_cat_def_id = l_eccb_rec.orig_crit_cat_def_id
,value_type_code = l_eccb_rec.value_type_code
,data_type_code = l_eccb_rec.data_type_code
,enabled_yn = l_eccb_rec.enabled_yn
,seeded_yn = l_eccb_rec.seeded_yn
,function_id = l_eccb_rec.function_id
,source_yn = l_eccb_rec.source_yn
,sql_statement = l_eccb_rec.sql_statement
,created_by = l_eccb_rec.created_by
,creation_date = l_eccb_rec.creation_date
,last_updated_by = l_eccb_rec.last_updated_by
,last_update_date = l_eccb_rec.last_update_date
,last_update_login = l_eccb_rec.last_update_login
WHERE crit_cat_def_id = l_eccb_rec.crit_cat_def_id;
END update_row;
PROCEDURE update_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_ecctl_rec IN okl_ecctl_rec
,x_ecctl_rec OUT NOCOPY okl_ecctl_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'update_row_tl';
UPDATE okl_fe_crit_cat_def_tl
SET crit_cat_def_id = l_ecctl_rec.crit_cat_def_id
,source_lang = l_ecctl_rec.source_lang
,crit_cat_desc = l_ecctl_rec.crit_cat_desc
,created_by = l_ecctl_rec.created_by
,creation_date = l_ecctl_rec.creation_date
,last_updated_by = l_ecctl_rec.last_updated_by
,last_update_date = l_ecctl_rec.last_update_date
,last_update_login = l_ecctl_rec.last_update_login
WHERE crit_cat_def_id = l_ecctl_rec.crit_cat_def_id;
UPDATE okl_fe_crit_cat_def_tl
SET sfwt_flag = 'Y'
WHERE crit_cat_def_id = l_ecctl_rec.crit_cat_def_id
AND source_lang <> userenv('LANG');
END update_row;
PROCEDURE update_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccv_rec IN okl_eccv_rec
,x_eccv_rec OUT NOCOPY okl_eccv_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'v_update_row';
l_eccv_rec.last_update_date := sysdate;
l_eccv_rec.last_updated_by := fnd_global.user_id;
l_eccv_rec.last_update_login := fnd_global.login_id;
update_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_eccb_rec
,lx_eccb_rec);
update_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_ecctl_rec
,lx_ecctl_rec);
END update_row;
PROCEDURE update_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccv_tbl IN okl_eccv_tbl
,x_eccv_tbl OUT NOCOPY okl_eccv_tbl) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'tbl_update_row';
update_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_eccv_rec => p_eccv_tbl(i)
,x_eccv_rec => x_eccv_tbl(i));
END update_row;
PROCEDURE delete_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccb_rec IN okl_eccb_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'delete_row_b';
DELETE FROM okl_fe_crit_cat_def_b
WHERE crit_cat_def_id = l_eccb_rec.crit_cat_def_id;
END delete_row;
PROCEDURE delete_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_ecctl_rec IN okl_ecctl_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'delete_row_tl';
DELETE FROM okl_fe_crit_cat_def_tl
WHERE crit_cat_def_id = l_ecctl_rec.crit_cat_def_id;
END delete_row;
PROCEDURE delete_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccv_rec IN okl_eccv_rec) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'v_delete_row';
delete_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_eccb_rec);
delete_row(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_ecctl_rec);
END delete_row;
PROCEDURE delete_row(p_api_version IN number
,p_init_msg_list IN varchar2 DEFAULT okl_api.g_false
,x_return_status OUT NOCOPY varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY varchar2
,p_eccv_tbl IN okl_eccv_tbl) IS
l_api_version CONSTANT number := 1;
l_api_name CONSTANT varchar2(30) := 'tbl_delete_row';
delete_row(p_api_version => p_api_version
,p_init_msg_list => okl_api.g_false
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_eccv_rec => p_eccv_tbl(i));
END delete_row;
p_last_update_date IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
f_luby NUMBER; -- entity owner in file
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT LAST_UPDATED_BY, LAST_UPDATE_DATE
INTO db_luby, db_ludate
FROM OKL_FE_CRIT_CAT_DEF_TL
where crit_cat_def_id = p_eccv_rec.crit_cat_def_id
and USERENV('LANG') =language;
UPDATE OKL_FE_CRIT_CAT_DEF_TL
SET
CRIT_CAT_DESC = p_eccv_rec.crit_cat_desc,
LAST_UPDATE_DATE = f_ludate,
LAST_UPDATED_BY = f_luby,
LAST_UPDATE_LOGIN = 0,
SOURCE_LANG = USERENV('LANG')
WHERE CRIT_CAT_DEF_ID = to_number(p_eccv_rec.crit_cat_def_id)
AND USERENV('LANG') IN (language,source_lang);
p_last_update_date IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
id NUMBER;
f_ludate DATE; -- entity update date in file
db_ludate DATE; -- entity update date in db
f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
SELECT ID , LAST_UPDATED_BY, LAST_UPDATE_DATE
INTO id, db_luby, db_ludate
FROM OKL_FE_CRIT_CAT_DEF_B
where crit_cat_def_id = p_eccv_rec.crit_cat_def_id;
UPDATE okl_fe_crit_cat_def_b
SET crit_cat_def_id = p_eccv_rec.crit_cat_def_id
,object_version_number = p_eccv_rec.object_version_number + 1
,crit_cat_name = p_eccv_rec.crit_cat_name
,ecc_ac_flag = p_eccv_rec.ecc_ac_flag
,orig_crit_cat_def_id = p_eccv_rec.orig_crit_cat_def_id
,value_type_code = p_eccv_rec.value_type_code
,data_type_code = p_eccv_rec.data_type_code
,enabled_yn = p_eccv_rec.enabled_yn
,seeded_yn = p_eccv_rec.seeded_yn
,function_id = p_eccv_rec.function_id
,source_yn = p_eccv_rec.source_yn
,sql_statement = p_eccv_rec.sql_statement
,last_updated_by = f_luby
,last_update_date = f_ludate
,last_update_login = 0
WHERE crit_cat_def_id = p_eccv_rec.crit_cat_def_id;
UPDATE OKL_FE_CRIT_CAT_DEF_TL
SET
CRIT_CAT_DESC = p_eccv_rec.crit_cat_desc,
LAST_UPDATE_DATE = f_ludate,
LAST_UPDATED_BY = f_luby,
LAST_UPDATE_LOGIN = 0,
SOURCE_LANG = USERENV('LANG')
WHERE CRIT_CAT_DEF_ID = TO_NUMBER(p_eccv_rec.crit_cat_def_id)
AND USERENV('LANG') IN (language,source_lang);
INSERT INTO OKL_FE_CRIT_CAT_DEF_TL
(CRIT_CAT_DEF_ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
CRIT_CAT_DESC,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT
TO_NUMBER(p_eccv_rec.crit_cat_def_id),
L.LANGUAGE_CODE,
USERENV('LANG'),
decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
p_eccv_rec.crit_cat_desc,
f_luby,
f_ludate,
f_luby,
f_ludate,
0
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I','B')
AND NOT EXISTS
(SELECT NULL
FROM OKL_FE_CRIT_CAT_DEF_TL TL
WHERE TL.CRIT_CAT_DEF_ID = TO_NUMBER(p_eccv_rec.crit_cat_def_id)
AND TL.LANGUAGE = L.LANGUAGE_CODE );
INSERT INTO okl_fe_crit_cat_def_b
(crit_cat_def_id
,object_version_number
,crit_cat_name
,ecc_ac_flag
,orig_crit_cat_def_id
,value_type_code
,data_type_code
,enabled_yn
,seeded_yn
,function_id
,source_yn
,sql_statement
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES (p_eccv_rec.crit_cat_def_id
,p_eccv_rec.object_version_number
,p_eccv_rec.crit_cat_name
,p_eccv_rec.ecc_ac_flag
,p_eccv_rec.orig_crit_cat_def_id
,p_eccv_rec.value_type_code
,p_eccv_rec.data_type_code
,p_eccv_rec.enabled_yn
,p_eccv_rec.seeded_yn
,p_eccv_rec.function_id
,p_eccv_rec.source_yn
,p_eccv_rec.sql_statement
,f_luby
,f_ludate
,f_luby
,f_ludate
,0);
INSERT INTO OKL_FE_CRIT_CAT_DEF_TL
(CRIT_CAT_DEF_ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
CRIT_CAT_DESC,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
SELECT
p_eccv_rec.crit_cat_def_id,
L.LANGUAGE_CODE,
userenv('LANG'),
decode(L.LANGUAGE_CODE,userenv('LANG'),'N','Y'),
p_eccv_rec.crit_cat_desc,
f_luby,
f_ludate,
f_luby,
f_ludate,
0
FROM FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I','B')
AND NOT EXISTS
(SELECT 1
FROM OKL_FE_CRIT_CAT_DEF_TL TL
WHERE TL.CRIT_CAT_DEF_ID = TO_NUMBER(p_eccv_rec.crit_cat_def_id)
AND TL.LANGUAGE = L.LANGUAGE_CODE);
p_last_update_date IN VARCHAR2) IS
l_api_version CONSTANT number := 1;
p_last_update_date => p_last_update_date,
x_return_status => l_return_status);
p_last_update_date => p_last_update_date,
x_return_status => l_return_status);