The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OKL_SERVICE_FEES_TL T
WHERE NOT EXISTS (
SELECT NULL
FROM OKL_SERVICE_FEES_ALL_B B --changed _TL to _B by rvaduri for MLS compliance.
WHERE B.ID = T.ID
);
UPDATE OKL_SERVICE_FEES_TL T SET (
NAME,
DESCRIPTION) = (SELECT
B.NAME,
B.DESCRIPTION
FROM OKL_SERVICE_FEES_TL B
WHERE B.ID = T.ID
AND B.LANGUAGE = T.SOURCE_LANG)
WHERE (
T.ID,
T.LANGUAGE)
IN (SELECT
SUBT.ID,
SUBT.LANGUAGE
FROM OKL_SERVICE_FEES_TL SUBB, OKL_SERVICE_FEES_TL SUBT
WHERE SUBB.ID = SUBT.ID
AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
AND (SUBB.NAME <> SUBT.NAME
OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR (SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL)
OR (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL)
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
));
INSERT INTO OKL_SERVICE_FEES_TL (
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
SELECT
B.ID,
L.LANGUAGE_CODE,
B.SOURCE_LANG,
B.SFWT_FLAG,
B.NAME,
B.DESCRIPTION,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN
FROM OKL_SERVICE_FEES_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS(
SELECT NULL
FROM OKL_SERVICE_FEES_TL T
WHERE T.ID = B.ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);
SELECT
ID,
SRV_CODE,
OBJECT_VERSION_NUMBER,
AMOUNT,
START_DATE,
END_DATE,
ORGANIZATION_ID,
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,
ORG_ID
FROM Okl_Service_Fees_B
WHERE okl_service_fees_b.id = p_id;
l_svf_rec.LAST_UPDATED_BY,
l_svf_rec.LAST_UPDATE_DATE,
l_svf_rec.LAST_UPDATE_LOGIN,
l_svf_rec.ORG_ID;
SELECT
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
NAME,
DESCRIPTION,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okl_Service_Fees_Tl
WHERE okl_service_fees_tl.id = p_id
AND okl_service_fees_tl.LANGUAGE = p_language;
l_okl_service_fees_tl_rec.LAST_UPDATED_BY,
l_okl_service_fees_tl_rec.LAST_UPDATE_DATE,
l_okl_service_fees_tl_rec.LAST_UPDATE_LOGIN;
SELECT
ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
SRV_CODE,
NAME,
DESCRIPTION,
AMOUNT,
START_DATE,
END_DATE,
ORGANIZATION_ID,
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_Service_Fees_V
WHERE okl_service_fees_v.id = p_id;
l_svfv_rec.LAST_UPDATED_BY,
l_svfv_rec.LAST_UPDATE_DATE,
l_svfv_rec.LAST_UPDATE_LOGIN;
IF (l_svfv_rec.last_updated_by = OKL_API.G_MISS_NUM) THEN
l_svfv_rec.last_updated_by := NULL;
IF (l_svfv_rec.last_update_date = OKL_API.G_MISS_DATE) THEN
l_svfv_rec.last_update_date := NULL;
IF (l_svfv_rec.last_update_login = OKL_API.G_MISS_NUM) THEN
l_svfv_rec.last_update_login := NULL;
SELECT id, srv_code, organization_id, end_date, start_date
FROM okl_service_fees_b
WHERE id = p_svfv_rec.id
OR organization_id = p_svfv_rec.organization_id AND id <> p_svfv_rec.id;
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;
PROCEDURE insert_row(
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_svf_rec IN svf_rec_type,
x_svf_rec OUT NOCOPY svf_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
INSERT INTO OKL_SERVICE_FEES_B(
id,
srv_code,
object_version_number,
amount,
start_date,
end_date,
organization_id,
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,
org_id)
VALUES (
l_svf_rec.id,
l_svf_rec.srv_code,
l_svf_rec.object_version_number,
l_svf_rec.amount,
l_svf_rec.start_date,
l_svf_rec.end_date,
l_svf_rec.organization_id,
l_svf_rec.attribute_category,
l_svf_rec.attribute1,
l_svf_rec.attribute2,
l_svf_rec.attribute3,
l_svf_rec.attribute4,
l_svf_rec.attribute5,
l_svf_rec.attribute6,
l_svf_rec.attribute7,
l_svf_rec.attribute8,
l_svf_rec.attribute9,
l_svf_rec.attribute10,
l_svf_rec.attribute11,
l_svf_rec.attribute12,
l_svf_rec.attribute13,
l_svf_rec.attribute14,
l_svf_rec.attribute15,
l_svf_rec.created_by,
l_svf_rec.creation_date,
l_svf_rec.last_updated_by,
l_svf_rec.last_update_date,
l_svf_rec.last_update_login,
l_svf_rec.organization_id);
END insert_row;
PROCEDURE insert_row(
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_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
SELECT *
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
INSERT INTO OKL_SERVICE_FEES_TL(
id,
LANGUAGE,
source_lang,
sfwt_flag,
name,
description,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
l_okl_service_fees_tl_rec.id,
l_okl_service_fees_tl_rec.LANGUAGE,
l_okl_service_fees_tl_rec.source_lang,
l_okl_service_fees_tl_rec.sfwt_flag,
l_okl_service_fees_tl_rec.name,
l_okl_service_fees_tl_rec.description,
l_okl_service_fees_tl_rec.created_by,
l_okl_service_fees_tl_rec.creation_date,
l_okl_service_fees_tl_rec.last_updated_by,
l_okl_service_fees_tl_rec.last_update_date,
l_okl_service_fees_tl_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_svfv_rec IN svfv_rec_type,
x_svfv_rec OUT NOCOPY svfv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
l_svfv_rec.LAST_UPDATE_DATE := l_svfv_rec.CREATION_DATE;
l_svfv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
l_svfv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
insert_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_svf_rec,
lx_svf_rec
);
insert_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_okl_service_fees_tl_rec,
lx_okl_service_fees_tl_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_svfv_tbl IN svfv_tbl_type,
x_svfv_tbl OUT NOCOPY svfv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_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_svfv_rec => p_svfv_tbl(i),
x_svfv_rec => x_svfv_tbl(i));
END insert_row;
SELECT OBJECT_VERSION_NUMBER
FROM OKL_SERVICE_FEES_B
WHERE ID = p_svf_rec.id
AND OBJECT_VERSION_NUMBER = p_svf_rec.object_version_number
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT OBJECT_VERSION_NUMBER
FROM OKL_SERVICE_FEES_B
WHERE ID = p_svf_rec.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_SERVICE_FEES_TL
WHERE ID = p_okl_service_fees_tl_rec.id
FOR UPDATE NOWAIT;
OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
PROCEDURE update_row(
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_svf_rec IN svf_rec_type,
x_svf_rec OUT NOCOPY svf_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
IF (x_svf_rec.last_updated_by = OKL_API.G_MISS_NUM)
THEN
x_svf_rec.last_updated_by := l_svf_rec.last_updated_by;
IF (x_svf_rec.last_update_date = OKL_API.G_MISS_DATE)
THEN
x_svf_rec.last_update_date := l_svf_rec.last_update_date;
IF (x_svf_rec.last_update_login = OKL_API.G_MISS_NUM)
THEN
x_svf_rec.last_update_login := l_svf_rec.last_update_login;
UPDATE OKL_SERVICE_FEES_B
SET SRV_CODE = l_def_svf_rec.srv_code,
OBJECT_VERSION_NUMBER = l_def_svf_rec.object_version_number,
AMOUNT = l_def_svf_rec.amount,
START_DATE = l_def_svf_rec.start_date,
END_DATE = l_def_svf_rec.end_date,
ORGANIZATION_ID = l_def_svf_rec.organization_id,
ATTRIBUTE_CATEGORY = l_def_svf_rec.attribute_category,
ATTRIBUTE1 = l_def_svf_rec.attribute1,
ATTRIBUTE2 = l_def_svf_rec.attribute2,
ATTRIBUTE3 = l_def_svf_rec.attribute3,
ATTRIBUTE4 = l_def_svf_rec.attribute4,
ATTRIBUTE5 = l_def_svf_rec.attribute5,
ATTRIBUTE6 = l_def_svf_rec.attribute6,
ATTRIBUTE7 = l_def_svf_rec.attribute7,
ATTRIBUTE8 = l_def_svf_rec.attribute8,
ATTRIBUTE9 = l_def_svf_rec.attribute9,
ATTRIBUTE10 = l_def_svf_rec.attribute10,
ATTRIBUTE11 = l_def_svf_rec.attribute11,
ATTRIBUTE12 = l_def_svf_rec.attribute12,
ATTRIBUTE13 = l_def_svf_rec.attribute13,
ATTRIBUTE14 = l_def_svf_rec.attribute14,
ATTRIBUTE15 = l_def_svf_rec.attribute15,
CREATED_BY = l_def_svf_rec.created_by,
CREATION_DATE = l_def_svf_rec.creation_date,
LAST_UPDATED_BY = l_def_svf_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_svf_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_svf_rec.last_update_login,
ORG_ID=l_def_svf_rec.organization_id
WHERE ID = l_def_svf_rec.id;
END update_row;
PROCEDURE update_row(
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_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
IF (x_okl_service_fees_tl_rec.last_updated_by = OKL_API.G_MISS_NUM)
THEN
x_okl_service_fees_tl_rec.last_updated_by := l_okl_service_fees_tl_rec.last_updated_by;
IF (x_okl_service_fees_tl_rec.last_update_date = OKL_API.G_MISS_DATE)
THEN
x_okl_service_fees_tl_rec.last_update_date := l_okl_service_fees_tl_rec.last_update_date;
IF (x_okl_service_fees_tl_rec.last_update_login = OKL_API.G_MISS_NUM)
THEN
x_okl_service_fees_tl_rec.last_update_login := l_okl_service_fees_tl_rec.last_update_login;
UPDATE OKL_SERVICE_FEES_TL
SET NAME = ldefoklservicefeestlrec.name,
DESCRIPTION = ldefoklservicefeestlrec.description,
SOURCE_LANG = ldefoklservicefeestlrec.source_lang,--Fix for 3637102
CREATED_BY = ldefoklservicefeestlrec.created_by,
CREATION_DATE = ldefoklservicefeestlrec.creation_date,
LAST_UPDATED_BY = ldefoklservicefeestlrec.last_updated_by,
LAST_UPDATE_DATE = ldefoklservicefeestlrec.last_update_date,
LAST_UPDATE_LOGIN = ldefoklservicefeestlrec.last_update_login
WHERE ID = ldefoklservicefeestlrec.id
AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);--Fix for bug 3637102
UPDATE OKL_SERVICE_FEES_TL
SET SFWT_FLAG = 'Y'
WHERE ID = ldefoklservicefeestlrec.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_svfv_rec IN svfv_rec_type,
x_svfv_rec OUT NOCOPY svfv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
l_svfv_rec.LAST_UPDATE_DATE := SYSDATE;
l_svfv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
l_svfv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
IF (x_svfv_rec.last_updated_by = OKL_API.G_MISS_NUM)
THEN
x_svfv_rec.last_updated_by := l_svfv_rec.last_updated_by;
IF (x_svfv_rec.last_update_date = OKL_API.G_MISS_DATE)
THEN
x_svfv_rec.last_update_date := l_svfv_rec.last_update_date;
IF (x_svfv_rec.last_update_login = OKL_API.G_MISS_NUM)
THEN
x_svfv_rec.last_update_login := l_svfv_rec.last_update_login;
update_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_okl_service_fees_tl_rec,
lx_okl_service_fees_tl_rec
);
update_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_svf_rec,
lx_svf_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_svfv_tbl IN svfv_tbl_type,
x_svfv_tbl OUT NOCOPY svfv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_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_svfv_rec => p_svfv_tbl(i),
x_svfv_rec => x_svfv_tbl(i));
END update_row;
PROCEDURE delete_row(
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_svf_rec IN svf_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
DELETE FROM OKL_SERVICE_FEES_B
WHERE ID = l_svf_rec.id;
END delete_row;
PROCEDURE delete_row(
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_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
DELETE FROM OKL_SERVICE_FEES_TL
WHERE ID = l_okl_service_fees_tl_rec.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_svfv_rec IN svfv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
delete_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_okl_service_fees_tl_rec
);
delete_row(
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_svf_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_svfv_tbl IN svfv_tbl_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_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_svfv_rec => p_svfv_tbl(i));
END delete_row;