[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OKL_FE_STD_RT_TMP_ALL_TL t
WHERE NOT EXISTS(SELECT NULL
FROM OKL_FE_STD_RT_TMP_ALL_B b
WHERE b.std_rate_tmpl_id = t.std_rate_tmpl_id);
UPDATE OKL_FE_STD_RT_TMP_ALL_TL t
SET(template_desc) = (SELECT
-- LANGUAGE,
-- B.LANGUAGE,
b.template_desc
FROM OKL_FE_STD_RT_TMP_ALL_TL b
WHERE b.std_rate_tmpl_id = t.std_rate_tmpl_id
AND b.language = t.source_lang)
WHERE (t.std_rate_tmpl_id, t.language) IN(SELECT subt.std_rate_tmpl_id ,subt.language
FROM OKL_FE_STD_RT_TMP_ALL_TL subb ,OKL_FE_STD_RT_TMP_ALL_TL subt
WHERE subb.std_rate_tmpl_id = subt.std_rate_tmpl_id AND subb.language = subt.language AND ( -- SUBB.LANGUAGE <> SUBT.LANGUAGE OR
subb.template_desc <> subt.template_desc OR (subb.language IS NOT NULL
AND subt.language IS NULL)
OR (subb.template_desc IS NULL AND subt.template_desc IS NOT NULL)));
INSERT INTO OKL_FE_STD_RT_TMP_ALL_TL
(std_rate_tmpl_id
,language
,source_lang
,sfwt_flag
,template_desc
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN)
SELECT b.std_rate_tmpl_id
,l.language_code
,b.source_lang
,b.sfwt_flag
,b.template_desc
,b.CREATED_BY
,b.CREATION_DATE
,b.LAST_UPDATED_BY
,b.LAST_UPDATE_DATE
,b.LAST_UPDATE_LOGIN
FROM OKL_FE_STD_RT_TMP_ALL_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_STD_RT_TMP_ALL_TL t
WHERE t.std_rate_tmpl_id = b.std_rate_tmpl_id AND t.language = l.language_code);
SELECT 'x'
FROM okl_fe_std_rt_tmp_all_b
WHERE std_rate_tmpl_id = p_orig_srt_id;
SELECT 'x'
FROM okl_indices
WHERE id = p_index_id;
SELECT std_rate_tmpl_id
,template_name
,object_version_number
,org_id
,currency_code
,rate_card_yn
,pricing_engine_code
,orig_std_rate_tmpl_id
,rate_type_code
,frequency_code
,index_id
,default_yn
,sts_code
,effective_from_date
,effective_to_date
,srt_rate
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_std_rt_tmp_all_b
WHERE okl_fe_std_rt_tmp_all_b.std_rate_tmpl_id = p_id;
,l_srtb_rec.last_updated_by
,l_srtb_rec.last_update_date
,l_srtb_rec.last_update_login ;
SELECT std_rate_tmpl_id
,template_desc
,language
,source_lang
,sfwt_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_std_rt_tmp_all_tl
WHERE okl_fe_std_rt_tmp_all_tl.std_rate_tmpl_id = p_id
AND okl_fe_std_rt_tmp_all_tl.language = p_language;
,l_srttl_rec.last_updated_by
,l_srttl_rec.last_update_date
,l_srttl_rec.last_update_login ;
SELECT std_rate_tmpl_id
,template_name
,template_desc
,object_version_number
,org_id
,currency_code
,rate_card_yn
,pricing_engine_code
,orig_std_rate_tmpl_id
,rate_type_code
,frequency_code
,index_id
,default_yn
,sts_code
,effective_from_date
,effective_to_date
,srt_rate
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
FROM okl_fe_std_rt_tmp_v
WHERE okl_fe_std_rt_tmp_v.std_rate_tmpl_id = p_id;
,l_srtv_rec.last_updated_by
,l_srtv_rec.last_update_date
,l_srtv_rec.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;
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;
IF (l_srtv_rec.last_updated_by = okl_api.g_miss_num) THEN
l_srtv_rec.last_updated_by := NULL;
IF (l_srtv_rec.last_update_date = okl_api.g_miss_date) THEN
l_srtv_rec.last_update_date := NULL;
IF (l_srtv_rec.last_update_login = okl_api.g_miss_num) THEN
l_srtv_rec.last_update_login := NULL;
SELECT object_version_number
FROM okl_fe_std_rt_tmp_all_b
WHERE std_rate_tmpl_id = p_srtb_rec.std_rate_tmpl_id
AND object_version_number = p_srtb_rec.object_version_number
FOR UPDATE OF object_version_number NOWAIT;
SELECT object_version_number
FROM okl_fe_std_rt_tmp_all_b
WHERE std_rate_tmpl_id = p_srtb_rec.std_rate_tmpl_id;
,g_form_record_deleted);
,g_record_logically_deleted);
SELECT *
FROM okl_fe_std_rt_tmp_all_tl
WHERE std_rate_tmpl_id = p_srttl_rec.std_rate_tmpl_id
FOR UPDATE NOWAIT;
,g_form_record_deleted);
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_srtb_rec IN okl_srtb_rec
,x_srtb_rec OUT NOCOPY okl_srtb_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
INSERT INTO okl_fe_std_rt_tmp_all_b
(std_rate_tmpl_id
,template_name
,object_version_number
,org_id
,currency_code
,rate_card_yn
,pricing_engine_code
,orig_std_rate_tmpl_id
,rate_type_code
,frequency_code
,index_id
,default_yn
,sts_code
,effective_from_date
,effective_to_date
,srt_rate
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES (l_srtb_rec.std_rate_tmpl_id
,l_srtb_rec.template_name
,l_srtb_rec.object_version_number
,l_srtb_rec.org_id
,l_srtb_rec.currency_code
,l_srtb_rec.rate_card_yn
,l_srtb_rec.pricing_engine_code
,l_srtb_rec.orig_std_rate_tmpl_id
,l_srtb_rec.rate_type_code
,l_srtb_rec.frequency_code
,l_srtb_rec.index_id
,l_srtb_rec.default_yn
,l_srtb_rec.sts_code
,l_srtb_rec.effective_from_date
,l_srtb_rec.effective_to_date
,l_srtb_rec.srt_rate
,l_srtb_rec.attribute_category
,l_srtb_rec.attribute1
,l_srtb_rec.attribute2
,l_srtb_rec.attribute3
,l_srtb_rec.attribute4
,l_srtb_rec.attribute5
,l_srtb_rec.attribute6
,l_srtb_rec.attribute7
,l_srtb_rec.attribute8
,l_srtb_rec.attribute9
,l_srtb_rec.attribute10
,l_srtb_rec.attribute11
,l_srtb_rec.attribute12
,l_srtb_rec.attribute13
,l_srtb_rec.attribute14
,l_srtb_rec.attribute15
,l_srtb_rec.created_by
,l_srtb_rec.creation_date
,l_srtb_rec.last_updated_by
,l_srtb_rec.last_update_date
,l_srtb_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_srttl_rec IN okl_srttl_rec
,x_srttl_rec OUT NOCOPY okl_srttl_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
SELECT *
FROM fnd_languages
WHERE installed_flag IN('I', 'B');
INSERT INTO okl_fe_std_rt_tmp_all_tl
(std_rate_tmpl_id
,template_desc
,language
,source_lang
,sfwt_flag
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login)
VALUES (l_srttl_rec.std_rate_tmpl_id
,l_srttl_rec.template_desc
,l_srttl_rec.language
,l_srttl_rec.source_lang
,l_srttl_rec.sfwt_flag
,l_srttl_rec.created_by
,l_srttl_rec.creation_date
,l_srttl_rec.last_updated_by
,l_srttl_rec.last_update_date
,l_srttl_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_srtv_rec IN okl_srtv_rec
,x_srtv_rec OUT NOCOPY okl_srtv_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
l_srtv_rec.last_update_date := SYSDATE;
l_srtv_rec.last_updated_by := fnd_global.user_id;
l_srtv_rec.last_update_login := fnd_global.login_id;
insert_row(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srtb_rec
,lx_srtb_rec);
insert_row(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srttl_rec
,lx_srttl_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_srtv_tbl IN okl_srtv_tbl
,x_srtv_tbl OUT NOCOPY okl_srtv_tbl) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'v_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_srtv_rec => p_srtv_tbl(i)
,x_srtv_rec => x_srtv_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_srtb_rec IN okl_srtb_rec
,x_srtb_rec OUT NOCOPY okl_srtb_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
UPDATE okl_fe_std_rt_tmp_all_b
SET std_rate_tmpl_id = l_def_srtb_rec.std_rate_tmpl_id
,template_name = l_def_srtb_rec.template_name
,object_version_number = l_def_srtb_rec.object_version_number + 1
,org_id = l_def_srtb_rec.org_id
,currency_code = l_def_srtb_rec.currency_code
,rate_card_yn = l_def_srtb_rec.rate_card_yn
,pricing_engine_code = l_def_srtb_rec.pricing_engine_code
,orig_std_rate_tmpl_id = l_def_srtb_rec.orig_std_rate_tmpl_id
,rate_type_code = l_def_srtb_rec.rate_type_code
,frequency_code = l_def_srtb_rec.frequency_code
,index_id = l_def_srtb_rec.index_id
,default_yn = l_def_srtb_rec.default_yn
,sts_code = l_def_srtb_rec.sts_code
,effective_from_date = l_def_srtb_rec.effective_from_date
,effective_to_date = l_def_srtb_rec.effective_to_date
,srt_rate = l_def_srtb_rec.srt_rate
,attribute_category = l_def_srtb_rec.attribute_category
,attribute1 = l_def_srtb_rec.attribute1
,attribute2 = l_def_srtb_rec.attribute2
,attribute3 = l_def_srtb_rec.attribute3
,attribute4 = l_def_srtb_rec.attribute4
,attribute5 = l_def_srtb_rec.attribute5
,attribute6 = l_def_srtb_rec.attribute6
,attribute7 = l_def_srtb_rec.attribute7
,attribute8 = l_def_srtb_rec.attribute8
,attribute9 = l_def_srtb_rec.attribute9
,attribute10 = l_def_srtb_rec.attribute10
,attribute11 = l_def_srtb_rec.attribute11
,attribute12 = l_def_srtb_rec.attribute12
,attribute13 = l_def_srtb_rec.attribute13
,attribute14 = l_def_srtb_rec.attribute14
,attribute15 = l_def_srtb_rec.attribute15
,created_by = l_def_srtb_rec.created_by
,creation_date = l_def_srtb_rec.creation_date
,last_updated_by = l_def_srtb_rec.last_updated_by
,last_update_date = l_def_srtb_rec.last_update_date
,last_update_login = l_def_srtb_rec.last_update_login
WHERE std_rate_tmpl_id = l_def_srtb_rec.std_rate_tmpl_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_srttl_rec IN okl_srttl_rec
,x_srttl_rec OUT NOCOPY okl_srttl_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'update_row';
UPDATE okl_fe_std_rt_tmp_all_tl
SET std_rate_tmpl_id = l_def_srttl_rec.std_rate_tmpl_id
,template_desc = l_def_srttl_rec.template_desc
,language = l_def_srttl_rec.language
,source_lang = l_def_srttl_rec.source_lang
,sfwt_flag = l_def_srttl_rec.sfwt_flag
,created_by = l_def_srttl_rec.created_by
,creation_date = l_def_srttl_rec.creation_date
,last_updated_by = l_def_srttl_rec.last_updated_by
,last_update_date = l_def_srttl_rec.last_update_date
,last_update_login = l_def_srttl_rec.last_update_login
WHERE std_rate_tmpl_id = l_def_srttl_rec.std_rate_tmpl_id
AND language = l_def_srttl_rec.language;
UPDATE okl_fe_std_rt_tmp_all_tl
SET sfwt_flag = 'Y'
WHERE std_rate_tmpl_id = l_def_srttl_rec.std_rate_tmpl_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_srtv_rec IN okl_srtv_rec
,x_srtv_rec OUT NOCOPY okl_srtv_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'v_insert_row';
l_srtv_rec.last_update_date := SYSDATE;
l_srtv_rec.last_updated_by := fnd_global.user_id;
l_srtv_rec.last_update_login := fnd_global.login_id;
IF (x_srtv_rec.last_updated_by IS NULL) THEN
x_srtv_rec.last_updated_by := l_srtv_rec.last_updated_by;
IF (x_srtv_rec.last_update_date IS NULL) THEN
x_srtv_rec.last_update_date := l_srtv_rec.last_update_date;
IF (x_srtv_rec.last_update_login IS NULL) THEN
x_srtv_rec.last_update_login := l_srtv_rec.last_update_login;
update_row(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srtb_rec
,lx_srtb_rec);
update_row(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srttl_rec
,lx_srttl_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_srtv_tbl IN okl_srtv_tbl
,x_srtv_tbl OUT NOCOPY okl_srtv_tbl) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'v_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_srtv_rec => p_srtv_tbl(i)
,x_srtv_rec => x_srtv_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_srtb_rec IN okl_srtb_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
DELETE FROM okl_fe_std_rt_tmp_all_b
WHERE std_rate_tmpl_id = l_srtb_rec.std_rate_tmpl_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_srttl_rec IN okl_srttl_rec) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
DELETE FROM okl_fe_std_rt_tmp_all_tl
WHERE std_rate_tmpl_id = l_srttl_rec.std_rate_tmpl_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_srtv_rec IN okl_srtv_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
,x_return_status
,x_msg_count
,x_msg_data
,l_srtb_rec);
delete_row(p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_srttl_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_srtv_tbl IN okl_srtv_tbl) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'v_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_srtv_rec => p_srtv_tbl(i));
END delete_row;