[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM OKL_INS_POLICIES_TL T
WHERE NOT EXISTS (
SELECT NULL
FROM OKL_INS_POLICIES_ALL_B B
WHERE B.ID =T.ID
);
UPDATE OKL_INS_POLICIES_TL T SET(
DESCRIPTION,
ENDORSEMENT,
COMMENTS,
CANCELLATION_COMMENT) = (SELECT
B.DESCRIPTION,
B.ENDORSEMENT,
B.COMMENTS,
B.CANCELLATION_COMMENT
FROM OKL_INS_POLICIES_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_INS_POLICIES_TL SUBB, OKL_INS_POLICIES_TL SUBT
WHERE SUBB.ID = SUBT.ID
AND SUBB.LANGUAGE = SUBT.LANGUAGE
AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
OR SUBB.ENDORSEMENT <> SUBT.ENDORSEMENT
OR SUBB.COMMENTS <> SUBT.COMMENTS
OR SUBB.CANCELLATION_COMMENT <> SUBT.CANCELLATION_COMMENT
OR (SUBB.LANGUAGE IS NOT NULL AND SUBT.LANGUAGE IS NULL)
OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
OR (SUBB.ENDORSEMENT IS NULL AND SUBT.ENDORSEMENT IS NOT NULL)
OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
OR (SUBB.CANCELLATION_COMMENT IS NULL AND SUBT.CANCELLATION_COMMENT IS NOT NULL)
));
INSERT INTO OKL_INS_POLICIES_TL (
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
DESCRIPTION,
ENDORSEMENT,
COMMENTS,
CANCELLATION_COMMENT,
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.DESCRIPTION,
B.ENDORSEMENT,
B.COMMENTS,
B.CANCELLATION_COMMENT,
B.CREATED_BY,
B.CREATION_DATE,
B.LAST_UPDATED_BY,
B.LAST_UPDATE_DATE,
B.LAST_UPDATE_LOGIN
FROM OKL_INS_POLICIES_TL B, FND_LANGUAGES L
WHERE L.INSTALLED_FLAG IN ('I', 'B')
AND B.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (
SELECT NULL
FROM OKL_INS_POLICIES_TL T
WHERE T.ID = B.ID
AND T.LANGUAGE = L.LANGUAGE_CODE
);
SELECT 'x'
FROM okl_ins_policies_v
WHERE ipy_type = 'THIRD_PARTY_POLICY'
AND ipy_type = p_ipyv_rec.ipy_type
AND policy_number = p_ipyv_rec.policy_number
AND ID <> p_ipyv_rec.id
AND ISU_ID = p_ipyv_rec.isu_id;
SELECT
ID,
IPY_TYPE,
DESCRIPTION,
ENDORSEMENT,
SFWT_FLAG,
CANCELLATION_COMMENT,
COMMENTS,
NAME_OF_INSURED,
POLICY_NUMBER,
CALCULATED_PREMIUM,
PREMIUM,
COVERED_AMOUNT,
DEDUCTIBLE,
ADJUSTMENT,
PAYMENT_FREQUENCY,
CRX_CODE,
IPF_CODE,
ISS_CODE,
IPE_CODE,
DATE_TO,
DATE_FROM,
DATE_QUOTED,
DATE_PROOF_PROVIDED,
DATE_PROOF_REQUIRED,
CANCELLATION_DATE,
DATE_QUOTE_EXPIRY,
ACTIVATION_DATE,
QUOTE_YN,
ON_FILE_YN,
PRIVATE_LABEL_YN,
AGENT_YN,
LESSOR_INSURED_YN,
LESSOR_PAYEE_YN,
KHR_ID,
KLE_ID,
IPT_ID,
IPY_ID,
INT_ID,
ISU_ID,
INSURANCE_FACTOR,
FACTOR_CODE,
FACTOR_VALUE,
AGENCY_NUMBER,
AGENCY_SITE_ID,
SALES_REP_ID,
AGENT_SITE_ID,
ADJUSTED_BY_ID,
TERRITORY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROGRAM_ID,
ORG_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
LEASE_APPLICATION_ID,
LEGAL_ENTITY_ID
FROM Okl_Ins_Policies_V
WHERE okl_ins_policies_v.id = p_id;
l_ipyv_rec.program_update_date,
l_ipyv_rec.program_application_id,
l_ipyv_rec.request_id,
l_ipyv_rec.object_version_number,
l_ipyv_rec.created_by,
l_ipyv_rec.creation_date,
l_ipyv_rec.last_updated_by,
l_ipyv_rec.last_update_date,
l_ipyv_rec.last_update_login,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
l_ipyv_rec.lease_application_id,
l_ipyv_rec.legal_entity_id;
SELECT
ID,
IPY_TYPE,
NAME_OF_INSURED,
POLICY_NUMBER,
INSURANCE_FACTOR,
FACTOR_CODE,
CALCULATED_PREMIUM,
PREMIUM,
COVERED_AMOUNT,
DEDUCTIBLE,
ADJUSTMENT,
PAYMENT_FREQUENCY,
CRX_CODE,
IPF_CODE,
ISS_CODE,
IPE_CODE,
DATE_TO,
DATE_FROM,
DATE_QUOTED,
DATE_PROOF_PROVIDED,
DATE_PROOF_REQUIRED,
CANCELLATION_DATE,
DATE_QUOTE_EXPIRY,
ACTIVATION_DATE,
QUOTE_YN,
ON_FILE_YN,
PRIVATE_LABEL_YN,
AGENT_YN,
LESSOR_INSURED_YN,
LESSOR_PAYEE_YN,
KHR_ID,
KLE_ID,
IPT_ID,
IPY_ID,
INT_ID,
ISU_ID,
FACTOR_VALUE,
AGENCY_NUMBER,
AGENCY_SITE_ID,
SALES_REP_ID,
AGENT_SITE_ID,
ADJUSTED_BY_ID,
TERRITORY_CODE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROGRAM_ID,
ORG_ID,
PROGRAM_UPDATE_DATE,
PROGRAM_APPLICATION_ID,
REQUEST_ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
LEASE_APPLICATION_ID,
LEGAL_ENTITY_ID
FROM Okl_Ins_Policies_B
WHERE okl_ins_policies_b.id = p_id;
l_ipy_rec.program_update_date,
l_ipy_rec.program_application_id,
l_ipy_rec.request_id,
l_ipy_rec.object_version_number,
l_ipy_rec.created_by,
l_ipy_rec.creation_date,
l_ipy_rec.last_updated_by,
l_ipy_rec.last_update_date,
l_ipy_rec.last_update_login,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
l_ipy_rec.lease_application_id,
l_ipy_rec.legal_entity_id;
SELECT
ID,
LANGUAGE,
SOURCE_LANG,
SFWT_FLAG,
DESCRIPTION,
ENDORSEMENT,
COMMENTS,
CANCELLATION_COMMENT,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM Okl_Ins_Policies_Tl
WHERE okl_ins_policies_tl.id = p_id;
l_okl_ins_policies_tl_rec.last_updated_by,
l_okl_ins_policies_tl_rec.last_update_date,
l_okl_ins_policies_tl_rec.last_update_login;
IF (l_ipyv_rec.last_updated_by = OKC_API.G_MISS_NUM ) THEN
l_ipyv_rec.last_updated_by := NULL;
IF (l_ipyv_rec.last_update_date = OKC_API.G_MISS_DATE ) THEN
l_ipyv_rec.last_update_date := NULL;
IF (l_ipyv_rec.last_update_login = OKC_API.G_MISS_NUM ) THEN
l_ipyv_rec.last_update_login := NULL;
SELECT 'x'
FROM FND_TERRITORIES_VL
WHERE territory_code = p_ipyv_rec.territory_code;
SELECT 'x'
FROM FND_LOOKUPS
WHERE LOOKUP_code = p_ipyv_rec.ipf_code
AND LOOKUP_TYPE = G_FND_LOOKUP_PAYMENT_FREQ
AND l_system_date BETWEEN NVL(start_date_active,l_system_date)
AND NVL(end_date_active,l_system_date);
SELECT 'x'
FROM OKL_INS_PARTYSITES_V
WHERE site_id = p_ipyv_rec.agent_site_id ;
SELECT 'x'
FROM OKL_INS_PARTYSITES_V
WHERE SITE_ID = p_ipyv_rec.agency_site_id
AND PARTY_ID = p_ipyv_rec.isu_id;
SELECT 'x' --Bug:3825159
FROM HZ_PARTIES PRT
WHERE PRT.CATEGORY_CODE = 'INSURANCE_AGENT'
AND PRT.party_id = p_ipyv_rec.int_id ;
SELECT 'x' --Bug:3825159
FROM HZ_PARTIES PRT
WHERE PRT.CATEGORY_CODE = 'INSURER'
AND PRT.PARTY_ID = p_ipyv_rec.isu_id ;
SELECT 'x'
FROM OKX_INS_PROVIDER_V
WHERE PARTY_ID = p_ipyv_rec.isu_id ;
SELECT 'x'
FROM OKL_INS_PRODUCTS_V
WHERE ID = p_ipyv_rec.ipt_id ;
SELECT 'x'
FROM OKL_INS_POLICIES_V
WHERE id = p_ipyv_rec.ipy_id ;
SELECT 'x'
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = p_ipyv_rec.ipe_code
AND LOOKUP_TYPE = G_FND_LOOKUP_INS_POLICY_TYPE
AND l_system_date BETWEEN NVL(start_date_active,l_system_date)
AND NVL(end_date_active,l_system_date);
SELECT 'x'
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = p_ipyv_rec.crx_code
AND LOOKUP_TYPE = G_FND_LOOKUP_INS_CANCEL_REASON
AND l_system_date BETWEEN NVL(start_date_active,l_system_date)
AND NVL(end_date_active,l_system_date);
SELECT 'x'
FROM FND_LOOKUPS
WHERE LOOKUP_code = p_ipyv_rec.iss_code
AND LOOKUP_TYPE = G_FND_LOOKUP_INS_STATUS
AND l_system_date BETWEEN NVL(start_date_active,l_system_date)
AND NVL(end_date_active,l_system_date);
SELECT 'x'
FROM OKL_K_LINES_V
WHERE id = p_ipyv_rec.kle_id ;
SELECT 'x'
FROM OKL_K_HEADERS_V
WHERE id = p_ipyv_rec.khr_id ;
SELECT 'x'
FROM OKL_LEASE_APPLICATIONS_V
WHERE id = p_ipyv_rec.lease_application_id ;
SELECT 'x'
FROM FND_LOOKUPS
WHERE LOOKUP_code = p_ipyv_rec.ipy_type
AND LOOKUP_TYPE = G_FND_LOOKUP_POLICY_TYPE;
PROCEDURE validate_last_updated_by(x_return_status OUT NOCOPY VARCHAR2,p_ipyv_rec IN ipyv_rec_type ) IS
l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
IF p_ipyv_rec.last_updated_by = OKC_API.G_MISS_NUM OR p_ipyv_rec.last_updated_by IS NULL
THEN
OKC_API.set_message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'last_updated_by');
END validate_last_updated_by;
PROCEDURE validate_last_update_date(x_return_status OUT NOCOPY VARCHAR2,p_ipyv_rec IN ipyv_rec_type ) IS
l_return_status VARCHAR2(1) := Okc_Api.G_RET_STS_SUCCESS;
IF p_ipyv_rec.last_update_date = OKC_API.G_MISS_DATE OR p_ipyv_rec.last_update_date IS NULL
THEN
OKC_API.set_message(G_APP_NAME,G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'last_update_date');
END validate_last_update_date;
validate_last_updated_by(x_return_status => l_return_status,
p_ipyv_rec => p_ipyv_rec);
validate_last_update_date(x_return_status => l_return_status,
p_ipyv_rec => p_ipyv_rec);
SELECT 'x'
FROM okl_ins_policies_v
WHERE khr_id = p_ipyv_rec.khr_id
AND ID <> p_ipyv_rec.ID
AND IPY_TYPE ='THIRD_PARTY_POLICY';
p_to.program_update_date := p_from.program_update_date;
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.program_update_date := p_from.program_update_date;
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 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipy_rec IN ipy_rec_type,
x_ipy_rec OUT NOCOPY ipy_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
SELECT NVL(DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),l_ipy_rec.request_id),
NVL(DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),l_ipy_rec.program_application_id),
NVL(DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),l_ipy_rec.program_id),
DECODE(DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,l_ipy_rec.program_update_date,SYSDATE),
MO_GLOBAL.GET_CURRENT_ORG_ID()
INTO x_ipy_rec.request_id,
x_ipy_rec.program_application_id,
x_ipy_rec.program_id,
x_ipy_rec.program_update_date,
l_org_id -- Change by zrehman for Bug#6363652 9-Oct-2007
FROM dual;
INSERT INTO OKL_INS_POLICIES_B(
id,
ipy_type,
name_of_insured,
policy_number,
insurance_factor,
factor_code,
calculated_premium,
premium,
covered_amount,
deductible,
adjustment,
payment_frequency,
crx_code,
ipf_code,
iss_code,
ipe_code,
date_to,
date_from,
date_quoted,
date_proof_provided,
date_proof_required,
cancellation_date,
date_quote_expiry,
activation_date,
quote_yn,
on_file_yn,
private_label_yn,
agent_yn,
lessor_insured_yn,
lessor_payee_yn,
khr_id,
kle_id,
ipt_id,
ipy_id,
int_id,
isu_id,
factor_value,
agency_number,
agency_site_id,
sales_rep_id,
agent_site_id,
adjusted_by_id,
territory_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
program_id,
org_id,
program_update_date,
program_application_id,
request_id,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
lease_application_id,
legal_entity_id)
VALUES (
l_ipy_rec.id,
l_ipy_rec.ipy_type,
l_ipy_rec.name_of_insured,
l_ipy_rec.policy_number,
l_ipy_rec.insurance_factor,
l_ipy_rec.factor_code,
l_ipy_rec.calculated_premium,
l_ipy_rec.premium,
l_ipy_rec.covered_amount,
l_ipy_rec.deductible,
l_ipy_rec.adjustment,
l_ipy_rec.payment_frequency,
l_ipy_rec.crx_code,
l_ipy_rec.ipf_code,
l_ipy_rec.iss_code,
l_ipy_rec.ipe_code,
l_ipy_rec.date_to,
l_ipy_rec.date_from,
l_ipy_rec.date_quoted,
l_ipy_rec.date_proof_provided,
l_ipy_rec.date_proof_required,
l_ipy_rec.cancellation_date,
l_ipy_rec.date_quote_expiry,
l_ipy_rec.activation_date,
l_ipy_rec.quote_yn,
l_ipy_rec.on_file_yn,
l_ipy_rec.private_label_yn,
l_ipy_rec.agent_yn,
l_ipy_rec.lessor_insured_yn,
l_ipy_rec.lessor_payee_yn,
l_ipy_rec.khr_id,
l_ipy_rec.kle_id,
l_ipy_rec.ipt_id,
l_ipy_rec.ipy_id,
l_ipy_rec.int_id,
l_ipy_rec.isu_id,
l_ipy_rec.factor_value,
l_ipy_rec.agency_number,
l_ipy_rec.agency_site_id,
l_ipy_rec.sales_rep_id,
l_ipy_rec.agent_site_id,
l_ipy_rec.adjusted_by_id,
l_ipy_rec.territory_code,
l_ipy_rec.attribute_category,
l_ipy_rec.attribute1,
l_ipy_rec.attribute2,
l_ipy_rec.attribute3,
l_ipy_rec.attribute4,
l_ipy_rec.attribute5,
l_ipy_rec.attribute6,
l_ipy_rec.attribute7,
l_ipy_rec.attribute8,
l_ipy_rec.attribute9,
l_ipy_rec.attribute10,
l_ipy_rec.attribute11,
l_ipy_rec.attribute12,
l_ipy_rec.attribute13,
l_ipy_rec.attribute14,
l_ipy_rec.attribute15,
l_ipy_rec.program_id,
l_ipy_rec.org_id,
l_ipy_rec.program_update_date,
l_ipy_rec.program_application_id,
l_ipy_rec.request_id,
l_ipy_rec.object_version_number,
l_ipy_rec.created_by,
l_ipy_rec.creation_date,
l_ipy_rec.last_updated_by,
l_ipy_rec.last_update_date,
l_ipy_rec.last_update_login,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
l_ipy_rec.lease_application_id,
l_ipy_rec.legal_entity_id);
END insert_row;
PROCEDURE insert_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_okl_ins_policies_tl_rec IN okl_ins_policies_tl_rec_type,
x_okl_ins_policies_tl_rec OUT NOCOPY okl_ins_policies_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_INS_POLICIES_TL(
id,
language,
source_lang,
sfwt_flag,
description,
endorsement,
comments,
cancellation_comment,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES (
l_okl_ins_policies_tl_rec.id,
l_okl_ins_policies_tl_rec.language,
l_okl_ins_policies_tl_rec.source_lang,
l_okl_ins_policies_tl_rec.sfwt_flag,
l_okl_ins_policies_tl_rec.description,
l_okl_ins_policies_tl_rec.endorsement,
l_okl_ins_policies_tl_rec.comments,
l_okl_ins_policies_tl_rec.cancellation_comment,
l_okl_ins_policies_tl_rec.created_by,
l_okl_ins_policies_tl_rec.creation_date,
l_okl_ins_policies_tl_rec.last_updated_by,
l_okl_ins_policies_tl_rec.last_update_date,
l_okl_ins_policies_tl_rec.last_update_login);
END insert_row;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_rec IN ipyv_rec_type,
x_ipyv_rec OUT NOCOPY ipyv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
l_ipyv_rec.LAST_UPDATE_DATE := l_ipyv_rec.CREATION_DATE;
l_ipyv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_ipyv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
MO_GLOBAL.GET_CURRENT_ORG_ID()
INTO x_ipyv_rec.request_id,
x_ipyv_rec.program_application_id,
x_ipyv_rec.program_id,
x_ipyv_rec.program_update_date,
l_org_id ---- Change by zrehman for Bug#6363652 9-Oct-2007
FROM dual;
insert_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_ipy_rec,
lx_ipy_rec
);
insert_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_okl_ins_policies_tl_rec,
lx_okl_ins_policies_tl_rec
);
END insert_row;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_tbl IN ipyv_tbl_type,
x_ipyv_tbl OUT NOCOPY ipyv_tbl_type,
px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
insert_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_error_rec.error_type,
x_msg_count => l_error_rec.msg_count,
x_msg_data => l_error_rec.msg_data,
p_ipyv_rec => p_ipyv_tbl(i),
x_ipyv_rec => x_ipyv_tbl(i));
END insert_row;
PROCEDURE insert_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_tbl IN ipyv_tbl_type,
x_ipyv_tbl OUT NOCOPY ipyv_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 => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_tbl => p_ipyv_tbl,
x_ipyv_tbl => x_ipyv_tbl,
px_error_tbl => l_error_tbl);
END insert_row;
SELECT OBJECT_VERSION_NUMBER
FROM OKL_INS_POLICIES_B
WHERE ID = p_ipy_rec.id
AND OBJECT_VERSION_NUMBER = p_ipy_rec.object_version_number
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT OBJECT_VERSION_NUMBER
FROM OKL_INS_POLICIES_B
WHERE ID = p_ipy_rec.id;
OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
SELECT *
FROM OKL_INS_POLICIES_TL
WHERE ID = p_okl_ins_policies_tl_rec.id
FOR UPDATE NOWAIT;
OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
PROCEDURE update_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipy_rec IN ipy_rec_type,
x_ipy_rec OUT NOCOPY ipy_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
IF (x_ipy_rec.program_update_date = OKC_API.G_MISS_DATE)
THEN
x_ipy_rec.program_update_date := l_ipy_rec.program_update_date;
IF (x_ipy_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_ipy_rec.last_updated_by := l_ipy_rec.last_updated_by;
IF (x_ipy_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_ipy_rec.last_update_date := l_ipy_rec.last_update_date;
IF (x_ipy_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_ipy_rec.last_update_login := l_ipy_rec.last_update_login;
SELECT NVL(DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),l_ipy_rec.request_id),
NVL(DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),l_ipy_rec.program_application_id),
NVL(DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),l_ipy_rec.program_id),
DECODE(DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,l_ipy_rec.program_update_date,SYSDATE),
MO_GLOBAL.GET_CURRENT_ORG_ID() INTO x_ipy_rec.request_id,
x_ipy_rec.program_application_id,
x_ipy_rec.program_id,
x_ipy_rec.program_update_date,
x_ipy_rec.org_id FROM dual;
UPDATE OKL_INS_POLICIES_B
SET IPY_TYPE = l_def_ipy_rec.ipy_type,
NAME_OF_INSURED = l_def_ipy_rec.name_of_insured,
POLICY_NUMBER = l_def_ipy_rec.policy_number,
INSURANCE_FACTOR = l_def_ipy_rec.insurance_factor,
FACTOR_CODE = l_def_ipy_rec.factor_code,
CALCULATED_PREMIUM = l_def_ipy_rec.calculated_premium,
PREMIUM = l_def_ipy_rec.premium,
COVERED_AMOUNT = l_def_ipy_rec.covered_amount,
DEDUCTIBLE = l_def_ipy_rec.deductible,
ADJUSTMENT = l_def_ipy_rec.adjustment,
PAYMENT_FREQUENCY = l_def_ipy_rec.payment_frequency,
CRX_CODE = l_def_ipy_rec.crx_code,
IPF_CODE = l_def_ipy_rec.ipf_code,
ISS_CODE = l_def_ipy_rec.iss_code,
IPE_CODE = l_def_ipy_rec.ipe_code,
DATE_TO = l_def_ipy_rec.date_to,
DATE_FROM = l_def_ipy_rec.date_from,
DATE_QUOTED = l_def_ipy_rec.date_quoted,
DATE_PROOF_PROVIDED = l_def_ipy_rec.date_proof_provided,
DATE_PROOF_REQUIRED = l_def_ipy_rec.date_proof_required,
CANCELLATION_DATE = l_def_ipy_rec.cancellation_date,
DATE_QUOTE_EXPIRY = l_def_ipy_rec.date_quote_expiry,
ACTIVATION_DATE = l_def_ipy_rec.activation_date,
QUOTE_YN = l_def_ipy_rec.quote_yn,
ON_FILE_YN = l_def_ipy_rec.on_file_yn,
PRIVATE_LABEL_YN = l_def_ipy_rec.private_label_yn,
AGENT_YN = l_def_ipy_rec.agent_yn,
LESSOR_INSURED_YN = l_def_ipy_rec.lessor_insured_yn,
LESSOR_PAYEE_YN = l_def_ipy_rec.lessor_payee_yn,
KHR_ID = l_def_ipy_rec.khr_id,
KLE_ID = l_def_ipy_rec.kle_id,
IPT_ID = l_def_ipy_rec.ipt_id,
IPY_ID = l_def_ipy_rec.ipy_id,
INT_ID = l_def_ipy_rec.int_id,
ISU_ID = l_def_ipy_rec.isu_id,
FACTOR_VALUE = l_def_ipy_rec.factor_value,
AGENCY_NUMBER = l_def_ipy_rec.agency_number,
AGENCY_SITE_ID = l_def_ipy_rec.agency_site_id,
SALES_REP_ID = l_def_ipy_rec.sales_rep_id,
AGENT_SITE_ID = l_def_ipy_rec.agent_site_id,
ADJUSTED_BY_ID = l_def_ipy_rec.adjusted_by_id,
TERRITORY_CODE = l_def_ipy_rec.territory_code,
ATTRIBUTE_CATEGORY = l_def_ipy_rec.attribute_category,
ATTRIBUTE1 = l_def_ipy_rec.attribute1,
ATTRIBUTE2 = l_def_ipy_rec.attribute2,
ATTRIBUTE3 = l_def_ipy_rec.attribute3,
ATTRIBUTE4 = l_def_ipy_rec.attribute4,
ATTRIBUTE5 = l_def_ipy_rec.attribute5,
ATTRIBUTE6 = l_def_ipy_rec.attribute6,
ATTRIBUTE7 = l_def_ipy_rec.attribute7,
ATTRIBUTE8 = l_def_ipy_rec.attribute8,
ATTRIBUTE9 = l_def_ipy_rec.attribute9,
ATTRIBUTE10 = l_def_ipy_rec.attribute10,
ATTRIBUTE11 = l_def_ipy_rec.attribute11,
ATTRIBUTE12 = l_def_ipy_rec.attribute12,
ATTRIBUTE13 = l_def_ipy_rec.attribute13,
ATTRIBUTE14 = l_def_ipy_rec.attribute14,
ATTRIBUTE15 = l_def_ipy_rec.attribute15,
PROGRAM_ID = l_def_ipy_rec.program_id,
ORG_ID = l_def_ipy_rec.org_id,
PROGRAM_UPDATE_DATE = l_def_ipy_rec.program_update_date,
PROGRAM_APPLICATION_ID = l_def_ipy_rec.program_application_id,
REQUEST_ID = l_def_ipy_rec.request_id,
OBJECT_VERSION_NUMBER = l_def_ipy_rec.object_version_number,
CREATED_BY = l_def_ipy_rec.created_by,
CREATION_DATE = l_def_ipy_rec.creation_date,
LAST_UPDATED_BY = l_def_ipy_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_ipy_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_ipy_rec.last_update_login,
-- Bug: 4567777 PAGARG new column for Lease Application Functionality impact
LEASE_APPLICATION_ID = l_def_ipy_rec.lease_application_id,
LEGAL_ENTITY_ID = l_def_ipy_rec.legal_entity_id
WHERE ID = l_def_ipy_rec.id;
END update_row;
PROCEDURE update_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_okl_ins_policies_tl_rec IN okl_ins_policies_tl_rec_type,
x_okl_ins_policies_tl_rec OUT NOCOPY okl_ins_policies_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
IF (x_okl_ins_policies_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_okl_ins_policies_tl_rec.last_updated_by := l_okl_ins_policies_tl_rec.last_updated_by;
IF (x_okl_ins_policies_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_okl_ins_policies_tl_rec.last_update_date := l_okl_ins_policies_tl_rec.last_update_date;
IF (x_okl_ins_policies_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_okl_ins_policies_tl_rec.last_update_login := l_okl_ins_policies_tl_rec.last_update_login;
UPDATE OKL_INS_POLICIES_TL
SET DESCRIPTION = l_def_okl_ins_policies_tl_rec.description,
SOURCE_LANG = l_def_okl_ins_policies_tl_rec.source_lang, --Added for bug 3637102
ENDORSEMENT = l_def_okl_ins_policies_tl_rec.endorsement,
COMMENTS = l_def_okl_ins_policies_tl_rec.comments,
CANCELLATION_COMMENT = l_def_okl_ins_policies_tl_rec.cancellation_comment,
CREATED_BY = l_def_okl_ins_policies_tl_rec.created_by,
CREATION_DATE = l_def_okl_ins_policies_tl_rec.creation_date,
LAST_UPDATED_BY = l_def_okl_ins_policies_tl_rec.last_updated_by,
LAST_UPDATE_DATE = l_def_okl_ins_policies_tl_rec.last_update_date,
LAST_UPDATE_LOGIN = l_def_okl_ins_policies_tl_rec.last_update_login
WHERE ID = l_def_okl_ins_policies_tl_rec.id
AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);--Added LANGUAGE for fixing 3637102
UPDATE OKL_INS_POLICIES_TL
SET SFWT_FLAG = 'Y'
WHERE ID = l_def_okl_ins_policies_tl_rec.id
AND SOURCE_LANG <> USERENV('LANG');
END update_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_rec IN ipyv_rec_type,
x_ipyv_rec OUT NOCOPY ipyv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
l_ipyv_rec.LAST_UPDATE_DATE := SYSDATE;
l_ipyv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
l_ipyv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
IF (x_ipyv_rec.program_update_date = OKC_API.G_MISS_DATE)
THEN
x_ipyv_rec.program_update_date := l_db_ipyv_rec.program_update_date;
IF (x_ipyv_rec.last_updated_by = OKC_API.G_MISS_NUM)
THEN
x_ipyv_rec.last_updated_by := l_db_ipyv_rec.last_updated_by;
IF (x_ipyv_rec.last_update_date = OKC_API.G_MISS_DATE)
THEN
x_ipyv_rec.last_update_date := l_db_ipyv_rec.last_update_date;
IF (x_ipyv_rec.last_update_login = OKC_API.G_MISS_NUM)
THEN
x_ipyv_rec.last_update_login := l_db_ipyv_rec.last_update_login;
SELECT NVL(DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),l_ipyv_rec.request_id),
NVL(DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),l_ipyv_rec.program_application_id),
NVL(DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),l_ipyv_rec.program_id),
DECODE(DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),NULL,l_ipyv_rec.program_update_date,SYSDATE),
MO_GLOBAL.GET_CURRENT_ORG_ID() INTO x_ipyv_rec.request_id,
x_ipyv_rec.program_application_id,
x_ipyv_rec.program_id,
x_ipyv_rec.program_update_date,
x_ipyv_rec.org_id FROM dual;
update_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_ipy_rec,
lx_ipy_rec
);
update_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_okl_ins_policies_tl_rec,
lx_okl_ins_policies_tl_rec
);
END update_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_tbl IN ipyv_tbl_type,
x_ipyv_tbl OUT NOCOPY ipyv_tbl_type,
px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
update_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_error_rec.error_type,
x_msg_count => l_error_rec.msg_count,
x_msg_data => l_error_rec.msg_data,
p_ipyv_rec => p_ipyv_tbl(i),
x_ipyv_rec => x_ipyv_tbl(i));
END update_row;
PROCEDURE update_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_tbl IN ipyv_tbl_type,
x_ipyv_tbl OUT NOCOPY ipyv_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 => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_tbl => p_ipyv_tbl,
x_ipyv_tbl => x_ipyv_tbl,
px_error_tbl => l_error_tbl);
END update_row;
PROCEDURE delete_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipy_rec IN ipy_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
DELETE FROM OKL_INS_POLICIES_B
WHERE ID = p_ipy_rec.id;
END delete_row;
PROCEDURE delete_row(
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_okl_ins_policies_tl_rec IN okl_ins_policies_tl_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
DELETE FROM OKL_INS_POLICIES_TL
WHERE ID = p_okl_ins_policies_tl_rec.id;
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_rec IN ipyv_rec_type) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
delete_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_okl_ins_policies_tl_rec
);
delete_row(
p_init_msg_list,
l_return_status,
x_msg_count,
x_msg_data,
l_ipy_rec
);
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_tbl IN ipyv_tbl_type,
px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
l_api_version CONSTANT NUMBER := 1;
l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
delete_row (
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_error_rec.error_type,
x_msg_count => l_error_rec.msg_count,
x_msg_data => l_error_rec.msg_data,
p_ipyv_rec => p_ipyv_tbl(i));
END delete_row;
PROCEDURE delete_row(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ipyv_tbl IN ipyv_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 => OKC_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_tbl => p_ipyv_tbl,
px_error_tbl => l_error_tbl);
END delete_row;