[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
print(' p_krpv_rec.LAST_UPDATED_BY,
print(' p_krpv_rec.LAST_UPDATE_DATE,
print(' p_krpv_rec.LAST_UPDATE_LOGIN */
end;
SELECT A.DATE_LAST_INTERIM_INTEREST_CAL,
B.CONTRACT_NUMBER,
B.START_DATE,
B.ORIG_SYSTEM_ID1,
B.ORIG_SYSTEM_SOURCE_CODE
INTO l_interest_date,
x_contract_number,
x_contract_start_date,
l_orig_system_id1,
l_orig_system_source_code
FROM OKL_K_HEADERS A,
OKC_K_HEADERS_B B
WHERE A.ID = p_chr_id
AND A.ID = B.ID;
SELECT 'Y'
INTO l_billable_stream_exists
FROM OKL_STREAMS A, OKL_STRM_TYPE_B C WHERE A.KHR_ID=p_chr_id
AND EXISTS (
SELECT 'X' FROM OKL_STRM_ELEMENTS B
WHERE B.STM_ID = A.ID
AND B.DATE_BILLED IS NOT NULL)
AND A.STY_ID = C.ID
AND C.BILLABLE_YN='Y'
AND A.SAY_CODE <> 'HIST'
AND ROWNUM < 2;
SELECT 'Y'
INTO l_billable_stream_exists
FROM OKL_STREAMS A, OKL_STRM_TYPE_B C WHERE A.KHR_ID=l_orig_system_id1
AND EXISTS (
SELECT 'X' FROM OKL_STRM_ELEMENTS B
WHERE B.STM_ID = A.ID
AND B.DATE_BILLED IS NOT NULL)
AND A.STY_ID = C.ID
AND C.BILLABLE_YN='Y'
AND A.SAY_CODE <> 'HIST'
AND ROWNUM < 2;
Select orig_system_source_code
From okc_k_headers_b chr
--where chr.orig_system_source_code = 'OKL_REBOOK'
where chr.id = p_chr_id;
SELECT MAX(EFFECTIVE_FROM_DATE)
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_id
AND EFFECTIVE_TO_DATE IS NULL;
SELECT
CHR.authoring_org_id,
CHR.inv_organization_id,
khr.deal_type,
pdt.id pid,
NVL(pdt.reporting_pdt_id, -1) report_pdt_id
FROM okc_k_headers_b CHR,
okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = CHR.id
AND CHR.id = chrId
AND khr.pdt_id = pdt.id;
SELECT
CHR.authoring_org_id,
CHR.inv_organization_id,
khr.deal_type,
pdt.id pid,
NVL(pdt.reporting_pdt_id, -1) report_pdt_id
FROM okc_k_headers_b CHR,
okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = CHR.id
AND CHR.id = chrId
AND khr.pdt_id = pdt.id;
SELECT
KHR_ID,
PARAMETER_TYPE_CODE,
EFFECTIVE_FROM_DATE,
EFFECTIVE_TO_DATE,
INTEREST_INDEX_ID,
BASE_RATE,
INTEREST_START_DATE,
ADDER_RATE,
MAXIMUM_RATE,
MINIMUM_RATE,
PRINCIPAL_BASIS_CODE,
DAYS_IN_A_MONTH_CODE,
DAYS_IN_A_YEAR_CODE,
INTEREST_BASIS_CODE,
RATE_DELAY_CODE,
RATE_DELAY_FREQUENCY,
COMPOUNDING_FREQUENCY_CODE,
CALCULATION_FORMULA_ID,
CATCHUP_BASIS_CODE,
CATCHUP_START_DATE,
CATCHUP_SETTLEMENT_CODE,
RATE_CHANGE_START_DATE,
RATE_CHANGE_FREQUENCY_CODE,
RATE_CHANGE_VALUE,
CONVERSION_OPTION_CODE,
NEXT_CONVERSION_DATE,
CONVERSION_TYPE_CODE,
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_K_Rate_Params_V
WHERE effective_from_date = NVL(p_effective_from_date,effective_from_date)
AND khr_id = p_khr_id
AND parameter_type_code = NVL(p_parameter_type_code,parameter_type_code);
x_krpv_rec.last_updated_by,
x_krpv_rec.last_update_date,
x_krpv_rec.last_update_login;
SELECT a.date_transaction_occurred,
b.date_last_interim_interest_cal
FROM okl_trx_contracts a,
okl_k_headers b
WHERE a.khr_id_new = p_chr_id
AND a.tcn_type = 'TRBK'
AND a.tsu_code = 'ENTERED'
--rkuttiya added for 12.1.1 Multi GAAP
AND a.representation_type = 'PRIMARY'
--
AND a.khr_id_new = b.id;
SELECT start_date
FROM OKC_K_HEADERS_B
WHERE ID = p_id;
SELECT COUNT(1) COUNT1
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_id
AND PARAMETER_TYPE_CODE = p_parameter_type_code
AND EFFECTIVE_TO_DATE IS NULL;
select rate.effective_from_date,
contract.orig_system_source_code,
lease.date_last_interim_interest_cal,
contract.orig_system_id1
FROM OKL_K_RATE_PARAMS rate,
OKC_K_HEADERS_B contract,
OKL_K_HEADERS lease
WHERE rate.khr_id = p_khr_id
AND rate.parameter_type_code = p_parameter_type_code
AND rate.effective_to_date is null
AND rate.khr_id = contract.id
AND contract.id = lease.id;
SELECT MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
FROM OKL_STRM_ELEMENTS STE
, OKL_STREAMS STM
, OKL_STRM_TYPE_V STY
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_B CHR
WHERE STM.ID = STE.STM_ID
AND STY.ID = STM.STY_ID
AND KHR.ID = STM.KHR_ID
AND CHR.ID = KHR.ID
AND STE.DATE_BILLED IS NOT NULL
AND CHR.ID = p_chr_id
AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose;
select count(1) into l_count
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_krpv_rec.khr_id
AND EFFECTIVE_FROM_DATE = p_krpv_rec.effective_from_date
AND PARAMETER_TYPE_CODE = p_krpv_rec.parameter_type_code;
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
OKL_KRP_PVT.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
UPDATE OKL_K_RATE_PARAMS
SET EFFECTIVE_TO_DATE = p_krpv_rec.effective_from_Date - 1
WHERE KHR_ID = p_krpv_rec.khr_id
AND PARAMETER_TYPE_CODE = p_krpv_rec.parameter_type_code
AND EFFECTIVE_FROM_DATE = l_effective_from_date
AND EFFECTIVE_TO_DATE IS NULL;
select count(1) into l_count
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_krpr_rec.khr_id
AND EFFECTIVE_FROM_DATE = p_krpr_rec.effective_from_date
AND PARAMETER_TYPE_CODE = p_krpr_rec.parameter_type_code;
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
OKL_KRP_PVT.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
select count(1) into l_count
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_krpar_rec.khr_id
AND EFFECTIVE_FROM_DATE = p_krpar_rec.effective_from_date
AND PARAMETER_TYPE_CODE = p_krpar_rec.parameter_type_code;
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
OKL_KRP_PVT.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
select count(1) into l_count
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_krpc_rec.khr_id
AND EFFECTIVE_FROM_DATE = p_krpc_rec.effective_from_date
AND PARAMETER_TYPE_CODE = p_krpc_rec.parameter_type_code;
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
OKL_KRP_PVT.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
PROCEDURE update_k_rate_params(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_krpv_rec IN krpv_rec_type,
x_krpv_rec OUT NOCOPY krpv_rec_type) IS
CURSOR txn_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
SELECT a.date_transaction_occurred,
b.date_last_interim_interest_cal
FROM okl_trx_contracts a,
okl_k_headers b
WHERE a.khr_id_new = p_chr_id
AND a.tcn_type = 'TRBK'
AND a.tsu_code = 'ENTERED'
--rkuttiya added for 12.1.1 Multi GAAP
AND a.representation_type = 'PRIMARY'
--
AND a.khr_id_new = b.id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Update_k_rate_params:Printing input p_krpv_rec...');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling update_row() ');
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After update_row() finished');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling cascade_lease_status_update...');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After cascade_lease_status_update finished ...');
PROCEDURE update_k_rate_params(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_krpr_rec IN krpr_rec_type,
x_krpr_rec OUT NOCOPY krpr_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
PROCEDURE update_k_rate_params(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_krpar_rec IN krpar_rec_type,
x_krpar_rec OUT NOCOPY krpar_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
PROCEDURE update_k_rate_params(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_krpc_rec IN krpc_rec_type,
x_krpc_rec OUT NOCOPY krpc_rec_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_K_RATE_PARAMS';
OKL_KRP_PVT.update_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec,
x_krpv_rec);
PROCEDURE delete_k_rate_params(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_krpdel_tbl IN krpdel_tbl_type) IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_RATE_PARAMS';
select rate.effective_to_date,
contract.sts_code,
contract.orig_system_source_code,
contract.orig_system_id1
FROM OKL_K_RATE_PARAMS rate,
OKC_K_HEADERS_B contract
WHERE rate.khr_id = p_khr_id
AND rate.parameter_type_code = p_parameter_type_code
AND rate.effective_from_date = p_effective_from_date
AND rate.khr_id = contract.id;
select rate.effective_from_date
FROM OKL_K_RATE_PARAMS rate
WHERE rate.khr_id = p_khr_id
AND rate.parameter_type_code = p_parameter_type_code
AND rate.effective_to_date IS NULL;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In delete_k_rate_params...');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Delete:rate_type=' || p_krpdel_tbl(i).rate_type);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Delete:khr_id=' || p_krpdel_tbl(i).khr_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Delete:effective_from_date=' || p_krpdel_tbl(i).effective_from_date);
p_msg_name => 'OKL_LLA_VAR_RATE_DELETE_ERR');
p_msg_name => 'OKL_LLA_VAR_RATE_DELETE_ERR1');
p_msg_name => 'OKL_LLA_VAR_RATE_DELETE_ERR2');
/*UPDATE OKL_K_RATE_PARAMS
SET EFFECTIVE_TO_DATE = NULL
WHERE KHR_ID = p_krpdel_tbl(i).khr_id
AND PARAMETER_TYPE_CODE = l_parameter_type_code
AND EFFECTIVE_TO_DATE = p_krpdel_tbl(i).effective_from_date - 1;*/
SELECT KHR_ID, PARAMETER_TYPE_CODE, EFFECTIVE_FROM_DATE,
EFFECTIVE_TO_DATE, INTEREST_INDEX_ID, BASE_RATE,
INTEREST_START_DATE, ADDER_RATE, MAXIMUM_RATE,
MINIMUM_RATE, PRINCIPAL_BASIS_CODE, DAYS_IN_A_MONTH_CODE,
DAYS_IN_A_YEAR_CODE, INTEREST_BASIS_CODE, RATE_DELAY_CODE,
RATE_DELAY_FREQUENCY, COMPOUNDING_FREQUENCY_CODE, CALCULATION_FORMULA_ID,
CATCHUP_BASIS_CODE, CATCHUP_START_DATE, CATCHUP_SETTLEMENT_CODE,
RATE_CHANGE_START_DATE, RATE_CHANGE_FREQUENCY_CODE, RATE_CHANGE_VALUE,
CONVERSION_OPTION_CODE, NEXT_CONVERSION_DATE, CONVERSION_TYPE_CODE,
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,
CATCHUP_FREQUENCY_CODE
INTO
l_krpv_rec.KHR_ID, l_krpv_rec.parameter_type_code, l_krpv_rec.effective_from_date,
l_krpv_rec.EFFECTIVE_TO_DATE, l_krpv_rec.INTEREST_INDEX_ID, l_krpv_rec.BASE_RATE,
l_krpv_rec.INTEREST_START_DATE, l_krpv_rec.ADDER_RATE, l_krpv_rec.MAXIMUM_RATE,
l_krpv_rec.MINIMUM_RATE, l_krpv_rec.PRINCIPAL_BASIS_CODE, l_krpv_rec.DAYS_IN_A_MONTH_CODE,
l_krpv_rec.DAYS_IN_A_YEAR_CODE, l_krpv_rec.INTEREST_BASIS_CODE, l_krpv_rec.RATE_DELAY_CODE,
l_krpv_rec.RATE_DELAY_FREQUENCY, l_krpv_rec.COMPOUNDING_FREQUENCY_CODE, l_krpv_rec.CALCULATION_FORMULA_ID,
l_krpv_rec.CATCHUP_BASIS_CODE, l_krpv_rec.CATCHUP_START_DATE, l_krpv_rec.CATCHUP_SETTLEMENT_CODE,
l_krpv_rec.RATE_CHANGE_START_DATE, l_krpv_rec.RATE_CHANGE_FREQUENCY_CODE, l_krpv_rec.RATE_CHANGE_VALUE,
l_krpv_rec.CONVERSION_OPTION_CODE, l_krpv_rec.NEXT_CONVERSION_DATE, l_krpv_rec.CONVERSION_TYPE_CODE,
l_krpv_rec.ATTRIBUTE_CATEGORY, l_krpv_rec.ATTRIBUTE1, l_krpv_rec.ATTRIBUTE2,
l_krpv_rec.ATTRIBUTE3, l_krpv_rec.ATTRIBUTE4, l_krpv_rec.ATTRIBUTE5,
l_krpv_rec.ATTRIBUTE6, l_krpv_rec.ATTRIBUTE7, l_krpv_rec.ATTRIBUTE8,
l_krpv_rec.ATTRIBUTE9, l_krpv_rec.ATTRIBUTE10, l_krpv_rec.ATTRIBUTE11,
l_krpv_rec.ATTRIBUTE12, l_krpv_rec.ATTRIBUTE13, l_krpv_rec.ATTRIBUTE14,
l_krpv_rec.ATTRIBUTE15, l_krpv_rec.CREATED_BY, l_krpv_rec.CREATION_DATE,
l_krpv_rec.LAST_UPDATED_BY, l_krpv_rec.LAST_UPDATE_DATE, l_krpv_rec.LAST_UPDATE_LOGIN,
l_krpv_rec.CATCHUP_FREQUENCY_CODE
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_krpdel_tbl(i).khr_id
AND parameter_type_code = l_parameter_type_code
AND effective_from_date = p_krpdel_tbl(i).effective_from_date;
UPDATE OKL_K_RATE_PARAMS
SET interest_index_id = null,
base_rate = null,
adder_rate = null,
minimum_rate = null,
maximum_rate = null,
principal_basis_code = null,
interest_basis_code = null,
interest_start_date = null,
days_in_a_month_code = null,
days_in_a_year_code = null,
rate_delay_code = null,
rate_delay_frequency = null,
compounding_frequency_code = null,
calculation_formula_id = null,
catchup_frequency_code = null,
catchup_start_date = null,
catchup_settlement_code = null,
catchup_basis_code = null,
rate_change_frequency_code = null,
rate_change_start_date = null,
rate_change_value = null
WHERE KHR_ID = p_krpdel_tbl(i).khr_id
AND parameter_type_code = l_parameter_type_code
AND effective_from_date = p_krpdel_tbl(i).effective_from_date;
UPDATE OKL_K_RATE_PARAMS
SET conversion_option_code = null,
next_conversion_date = null,
conversion_type_code = null
WHERE KHR_ID = p_krpdel_tbl(i).khr_id
AND parameter_type_code = l_parameter_type_code
AND effective_from_date = p_krpdel_tbl(i).effective_from_date;
OKL_KRP_PVT.delete_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_krpv_rec2);
p_msg_name => 'OKL_LA_VAR_RATE_DELETE_ERR2');
UPDATE OKL_K_RATE_PARAMS
SET EFFECTIVE_TO_DATE = NULL
WHERE KHR_ID = p_krpdel_tbl(i).khr_id
AND PARAMETER_TYPE_CODE = l_parameter_type_code
AND EFFECTIVE_TO_DATE = p_krpdel_tbl(i).effective_from_date - 1;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_K_RATE_PARAMS';
SELECT
CHR.authoring_org_id,
CHR.inv_organization_id,
khr.deal_type,
pdt.id pid,
NVL(pdt.reporting_pdt_id, -1) report_pdt_id
FROM okc_k_headers_b CHR,
okl_k_headers khr,
okl_products_v pdt
WHERE khr.id = CHR.id
AND CHR.id = chrId
AND khr.pdt_id = pdt.id(+);
SELECT
khr_id
,parameter_type_code
,effective_from_date
,effective_to_date
,interest_index_id
,base_rate
,interest_start_date
,adder_rate
,maximum_rate
,minimum_rate
,principal_basis_code
,days_in_a_month_code
,days_in_a_year_code
,interest_basis_code
,rate_delay_code
,rate_delay_frequency
,compounding_frequency_code
,calculation_formula_id
,catchup_basis_code
,catchup_start_date
,catchup_settlement_code
,rate_change_start_date
,rate_change_frequency_code
,rate_change_value
,conversion_option_code
,next_conversion_date
,conversion_type_code
,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
,catchup_frequency_code
FROM okl_k_rate_params
WHERE khr_id = p_id;
select id into l_pdt_id
from okl_products_v
where name = p_product_name;
SELECT parameter_type_code,
interest_index_id,
base_rate,
interest_start_date,
adder_rate,
maximum_rate,
minimum_rate,
principal_basis_code,
days_in_a_month_code,
days_in_a_year_code,
interest_basis_code,
effective_from_date,
effective_to_date,
rate_delay_code,
rate_delay_frequency,
compounding_frequency_code,
calculation_formula_id,
catchup_basis_code,
catchup_start_date,
catchup_settlement_code,
rate_change_start_date,
rate_change_frequency_code,
rate_change_value,
conversion_option_code,
next_conversion_date,
conversion_type_code
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_chr_id
AND parameter_type_code IN ('ACTUAL', 'CONVERSION')
ORDER BY effective_from_date, parameter_type_code;
SELECT meaning
FROM FND_LOOKUPS
WHERE lookup_type = 'OKL_VAR_RATE_PARAMS_TYPE'
AND lookup_code = p_lookup_code
AND nvl(enabled_flag,'N') = 'Y'
AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate);
SELECT COUNT(1) COUNT1
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_id
AND EFFECTIVE_TO_DATE IS NULL;
update OKL_K_RATE_PARAMS
SET interest_start_date = p_new_start_date,
rate_change_start_date = p_new_start_date,
catchup_start_date = nvl(l_catchup_start_date,catchup_start_date)
WHERE khr_id = p_chr_id
AND effective_from_date = l_effective_from_date
AND effective_to_date is NULL;
/*update_k_rate_params(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_krpv_rec => l_krpv_rec,
x_krpv_rec => x_krpv_rec);
update okl_k_rate_params
set effective_from_date = p_new_start_date
WHERE khr_id = p_chr_id
AND effective_from_date = l_effective_from_date
AND effective_to_date IS NULL;
update okl_k_rate_params
set effective_from_date = p_new_start_date
WHERE khr_id = p_chr_id
AND effective_from_date = l_effective_from_date
AND effective_to_date is NULL;
SELECT
QVE.DESCRIPTION
FROM
OKL_PDT_QUALITYS PQY
, OKL_PQY_VALUES QVE
WHERE
QVE.PQY_ID = PQY.ID
AND PQY.NAME = cp_name
AND QVE.VALUE = cp_value;
SELECT
FNDLUP.MEANING
FROM
FND_LOOKUPS FNDLUP
WHERE
FNDLUP.LOOKUP_TYPE = cp_lkp_type
AND FNDLUP.LOOKUP_CODE = cp_lkp_code
AND SYSDATE BETWEEN
NVL(FNDLUP.START_DATE_ACTIVE,SYSDATE)
AND NVL(FNDLUP.END_DATE_ACTIVE,SYSDATE);
SELECT
CHR.CONTRACT_NUMBER
, KHR.DATE_LAST_INTERIM_INTEREST_CAL INTEREST_PROCESSED_FLAG
FROM
OKL_K_HEADERS KHR
, OKC_K_HEADERS_B CHR
WHERE
CHR.ID = KHR.ID
AND KHR.ID = cp_chr_id;
SELECT
MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
, STY.STYB_PURPOSE_MEANING
FROM
OKL_STRM_ELEMENTS STE
, OKL_STREAMS STM
, OKL_STRM_TYPE_V STY
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_B CHR
WHERE
STM.ID = STE.STM_ID
AND STY.ID = STM.STY_ID
AND KHR.ID = STM.KHR_ID
AND CHR.ID = KHR.ID
AND STE.ACCRUED_YN ='Y'
AND CHR.ID = p_chr_id
AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose
GROUP BY STY.STYB_PURPOSE_MEANING;
SELECT
MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
, STY.STYB_PURPOSE_MEANING
FROM
OKL_STRM_ELEMENTS STE
, OKL_STREAMS STM
, OKL_STRM_TYPE_V STY
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_B CHR
WHERE
STM.ID = STE.STM_ID
AND STY.ID = STM.STY_ID
AND KHR.ID = STM.KHR_ID
AND CHR.ID = KHR.ID
AND STE.DATE_BILLED IS NOT NULL
AND CHR.ID = p_chr_id
AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose
GROUP BY STY.STYB_PURPOSE_MEANING;
SELECT
a.khr_id, a.parameter_type_code, a.effective_from_date,
a.effective_to_date, a.interest_index_id, a.base_rate,
a.interest_start_date, a.adder_rate, a.maximum_rate,
a.minimum_rate, a.principal_basis_code, a.days_in_a_month_code,
a.days_in_a_year_code, a.interest_basis_code, a.rate_delay_code,
a.rate_delay_frequency, a.compounding_frequency_code, a.calculation_formula_id,
a.catchup_basis_code, a.catchup_start_date, a.catchup_settlement_code,
a.rate_change_start_date, a.rate_change_frequency_code, a.rate_change_value,
a.conversion_option_code, a.next_conversion_date, a.conversion_type_code,
a.attribute_category, a.attribute1, a.attribute2,
a.attribute3, a.attribute4, a.attribute5,
a.attribute6, a.attribute7, a.attribute8,
a.attribute9, a.attribute10, a.attribute11,
a.attribute12, a.attribute13, a.attribute14,
a.attribute15, a.catchup_frequency_code
FROM OKL_K_RATE_PARAMS a
WHERE a.khr_id = p_id;
SELECT max(effective_to_date) effective_to_date
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_id;
DELETE OKL_K_RATE_PARAMS
WHERE KHR_ID = p_new_contract_id;
INSERT INTO okl_k_rate_params (
khr_id, parameter_type_code, effective_from_date,
effective_to_date, interest_index_id, base_rate,
interest_start_date, adder_rate, maximum_rate,
minimum_rate, principal_basis_code, days_in_a_month_code,
days_in_a_year_code, interest_basis_code, rate_delay_code,
rate_delay_frequency,compounding_frequency_code, calculation_formula_id,
catchup_basis_code, catchup_start_date, catchup_settlement_code,
rate_change_start_date, rate_change_frequency_code, rate_change_value,
conversion_option_code, next_conversion_date, conversion_type_code,
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,
catchup_frequency_code
) VALUES (
l_kkhr_id_tab(i), l_kparameter_type_code_tab(i), l_keffective_from_date_tab(i),
l_keffective_to_date_tab(i), l_kinterest_index_id_tab(i), l_kbase_rate_tab(i),
l_kinterest_start_date_tab(i), l_kadder_rate_tab(i), l_kmaximum_rate_tab(i),
l_kminimum_rate_tab(i), l_kprincipal_basis_code_tab(i), l_kdays_in_a_month_code_tab(i),
l_kdays_in_a_year_code_tab(i), l_kinterest_basis_code_tab(i), l_krate_delay_code_tab(i),
l_krate_delay_frequency_tab(i), l_kcompounding_frequ_code_tab(i), l_kcalculation_formula_id_tab(i),
l_kcatchup_basis_code_tab(i), l_kcatchup_start_date_tab(i), l_kcatchup_settlemen_code_tab(i),
l_krate_change_start_date_tab(i), l_krate_change_frequ_code_tab(i), l_krate_change_value_tab(i),
l_kconversion_option_code_tab(i), l_knext_conversion_date_tab(i), l_kconversion_type_code_tab(i),
l_kattribute_category_tab(i),l_kattribute1_tab(i), l_kattribute2_tab(i),
l_kattribute3_tab(i), l_kattribute4_tab(i), l_kattribute5_tab(i),
l_kattribute6_tab(i), l_kattribute7_tab(i), l_kattribute8_tab(i),
l_kattribute9_tab(i), l_kattribute10_tab(i), l_kattribute11_tab(i),
l_kattribute12_tab(i), l_kattribute13_tab(i), l_kattribute14_tab(i),
l_kattribute15_tab(i), fnd_global.user_id, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.login_id,
l_kcatchup_frequency_code_tab(i)
);
SELECT base_rate
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_id
AND PARAMETER_TYPE_CODE = 'ACTUAL'
AND EFFECTIVE_TO_DATE IS NULL;
SELECT crl.object1_id1
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT tl.name name,
stm.stream_type_purpose
FROM okl_strm_type_b stm,
OKL_STRM_TYPE_TL tl
WHERE tl.id = stm.id
AND tl.language = 'US'
AND stm.id = styid;
select id
INTO l_formula_id
FROM OKL_FORMULAE_B
WHERE NAME = p_name;
select rate.effective_from_date,
contract.orig_system_source_code,
lease.date_last_interim_interest_cal,
contract.orig_system_id1
FROM OKL_K_RATE_PARAMS rate,
OKC_K_HEADERS_B contract,
OKL_K_HEADERS lease
WHERE rate.khr_id = p_khr_id
AND rate.parameter_type_code = p_parameter_type_code
AND rate.effective_to_date is null
AND rate.khr_id = contract.id
AND contract.id = lease.id;
SELECT
MAX(STE.STREAM_ELEMENT_DATE) LAST_STREAM_DUE_DATE
--, STY.STYB_PURPOSE_MEANING
FROM
OKL_STRM_ELEMENTS STE
, OKL_STREAMS STM
, OKL_STRM_TYPE_V STY
, OKL_K_HEADERS KHR
, OKC_K_HEADERS_B CHR
WHERE
STM.ID = STE.STM_ID
AND STY.ID = STM.STY_ID
AND KHR.ID = STM.KHR_ID
AND CHR.ID = KHR.ID
AND STE.DATE_BILLED IS NOT NULL
AND CHR.ID = p_chr_id
AND STY.STREAM_TYPE_PURPOSE = p_stream_purpose;
SELECT
KHR_ID, l_parameter_type_code, p_effective_from_date,
EFFECTIVE_TO_DATE, INTEREST_INDEX_ID, BASE_RATE,
INTEREST_START_DATE, ADDER_RATE, MAXIMUM_RATE,
MINIMUM_RATE, PRINCIPAL_BASIS_CODE, DAYS_IN_A_MONTH_CODE,
DAYS_IN_A_YEAR_CODE, INTEREST_BASIS_CODE, RATE_DELAY_CODE,
RATE_DELAY_FREQUENCY, COMPOUNDING_FREQUENCY_CODE, CALCULATION_FORMULA_ID,
CATCHUP_BASIS_CODE, CATCHUP_START_DATE, CATCHUP_SETTLEMENT_CODE,
RATE_CHANGE_START_DATE, RATE_CHANGE_FREQUENCY_CODE, RATE_CHANGE_VALUE,
CONVERSION_OPTION_CODE, NEXT_CONVERSION_DATE, CONVERSION_TYPE_CODE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15, fnd_global.user_id, SYSDATE,
fnd_global.user_id, SYSDATE, fnd_global.user_id,
CATCHUP_FREQUENCY_CODE
INTO
x_krpv_rec.KHR_ID, x_krpv_rec.parameter_type_code, x_krpv_rec.effective_from_date,
x_krpv_rec.EFFECTIVE_TO_DATE, x_krpv_rec.INTEREST_INDEX_ID, x_krpv_rec.BASE_RATE,
x_krpv_rec.INTEREST_START_DATE, x_krpv_rec.ADDER_RATE, x_krpv_rec.MAXIMUM_RATE,
x_krpv_rec.MINIMUM_RATE, x_krpv_rec.PRINCIPAL_BASIS_CODE, x_krpv_rec.DAYS_IN_A_MONTH_CODE,
x_krpv_rec.DAYS_IN_A_YEAR_CODE, x_krpv_rec.INTEREST_BASIS_CODE, x_krpv_rec.RATE_DELAY_CODE,
x_krpv_rec.RATE_DELAY_FREQUENCY, x_krpv_rec.COMPOUNDING_FREQUENCY_CODE, x_krpv_rec.CALCULATION_FORMULA_ID,
x_krpv_rec.CATCHUP_BASIS_CODE, x_krpv_rec.CATCHUP_START_DATE, x_krpv_rec.CATCHUP_SETTLEMENT_CODE,
x_krpv_rec.RATE_CHANGE_START_DATE, x_krpv_rec.RATE_CHANGE_FREQUENCY_CODE, x_krpv_rec.RATE_CHANGE_VALUE,
x_krpv_rec.CONVERSION_OPTION_CODE, x_krpv_rec.NEXT_CONVERSION_DATE, x_krpv_rec.CONVERSION_TYPE_CODE,
x_krpv_rec.ATTRIBUTE_CATEGORY, x_krpv_rec.ATTRIBUTE1, x_krpv_rec.ATTRIBUTE2,
x_krpv_rec.ATTRIBUTE3, x_krpv_rec.ATTRIBUTE4, x_krpv_rec.ATTRIBUTE5,
x_krpv_rec.ATTRIBUTE6, x_krpv_rec.ATTRIBUTE7, x_krpv_rec.ATTRIBUTE8,
x_krpv_rec.ATTRIBUTE9, x_krpv_rec.ATTRIBUTE10, x_krpv_rec.ATTRIBUTE11,
x_krpv_rec.ATTRIBUTE12, x_krpv_rec.ATTRIBUTE13, x_krpv_rec.ATTRIBUTE14,
x_krpv_rec.ATTRIBUTE15, x_krpv_rec.CREATED_BY, x_krpv_rec.CREATION_DATE,
x_krpv_rec.LAST_UPDATED_BY, x_krpv_rec.LAST_UPDATE_DATE, x_krpv_rec.LAST_UPDATE_LOGIN,
x_krpv_rec.CATCHUP_FREQUENCY_CODE
FROM OKL_K_RATE_PARAMS
WHERE KHR_ID = p_khr_id
AND PARAMETER_TYPE_CODE = l_parameter_type_code
AND EFFECTIVE_FROM_DATE = l_effective_from_date
AND EFFECTIVE_TO_DATE IS NULL;
INSERT INTO OKL_K_RATE_PARAMS (
KHR_ID, PARAMETER_TYPE_CODE, EFFECTIVE_FROM_DATE,
EFFECTIVE_TO_DATE, INTEREST_INDEX_ID, BASE_RATE,
INTEREST_START_DATE, ADDER_RATE, MAXIMUM_RATE,
MINIMUM_RATE, PRINCIPAL_BASIS_CODE, DAYS_IN_A_MONTH_CODE,
DAYS_IN_A_YEAR_CODE, INTEREST_BASIS_CODE, RATE_DELAY_CODE,
RATE_DELAY_FREQUENCY, COMPOUNDING_FREQUENCY_CODE, CALCULATION_FORMULA_ID,
CATCHUP_BASIS_CODE, CATCHUP_START_DATE, CATCHUP_SETTLEMENT_CODE,
RATE_CHANGE_START_DATE, RATE_CHANGE_FREQUENCY_CODE, RATE_CHANGE_VALUE,
CONVERSION_OPTION_CODE, NEXT_CONVERSION_DATE, CONVERSION_TYPE_CODE,
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,
CATCHUP_FREQUENCY_CODE
)
VALUES (
x_krpv_rec.KHR_ID, x_krpv_rec.parameter_type_code, x_krpv_rec.effective_from_date,
x_krpv_rec.EFFECTIVE_TO_DATE, x_krpv_rec.INTEREST_INDEX_ID, x_krpv_rec.BASE_RATE,
x_krpv_rec.INTEREST_START_DATE, x_krpv_rec.ADDER_RATE, x_krpv_rec.MAXIMUM_RATE,
x_krpv_rec.MINIMUM_RATE, x_krpv_rec.PRINCIPAL_BASIS_CODE, x_krpv_rec.DAYS_IN_A_MONTH_CODE,
x_krpv_rec.DAYS_IN_A_YEAR_CODE, x_krpv_rec.INTEREST_BASIS_CODE, x_krpv_rec.RATE_DELAY_CODE,
x_krpv_rec.RATE_DELAY_FREQUENCY, x_krpv_rec.COMPOUNDING_FREQUENCY_CODE, x_krpv_rec.CALCULATION_FORMULA_ID,
x_krpv_rec.CATCHUP_BASIS_CODE, x_krpv_rec.CATCHUP_START_DATE, x_krpv_rec.CATCHUP_SETTLEMENT_CODE,
x_krpv_rec.RATE_CHANGE_START_DATE, x_krpv_rec.RATE_CHANGE_FREQUENCY_CODE, x_krpv_rec.RATE_CHANGE_VALUE,
x_krpv_rec.CONVERSION_OPTION_CODE, x_krpv_rec.NEXT_CONVERSION_DATE, x_krpv_rec.CONVERSION_TYPE_CODE,
x_krpv_rec.ATTRIBUTE_CATEGORY, x_krpv_rec.ATTRIBUTE1, x_krpv_rec.ATTRIBUTE2,
x_krpv_rec.ATTRIBUTE3, x_krpv_rec.ATTRIBUTE4, x_krpv_rec.ATTRIBUTE5,
x_krpv_rec.ATTRIBUTE6, x_krpv_rec.ATTRIBUTE7, x_krpv_rec.ATTRIBUTE8,
x_krpv_rec.ATTRIBUTE9, x_krpv_rec.ATTRIBUTE10, x_krpv_rec.ATTRIBUTE11,
x_krpv_rec.ATTRIBUTE12, x_krpv_rec.ATTRIBUTE13, x_krpv_rec.ATTRIBUTE14,
x_krpv_rec.ATTRIBUTE15, x_krpv_rec.CREATED_BY, x_krpv_rec.CREATION_DATE,
x_krpv_rec.LAST_UPDATED_BY, x_krpv_rec.LAST_UPDATE_DATE, x_krpv_rec.LAST_UPDATE_LOGIN,
x_krpv_rec.CATCHUP_FREQUENCY_CODE);
UPDATE OKL_K_RATE_PARAMS
SET EFFECTIVE_TO_DATE = p_effective_from_date - 1
WHERE KHR_ID = p_khr_id
AND PARAMETER_TYPE_CODE = l_parameter_type_code
AND EFFECTIVE_FROM_DATE = l_effective_from_date
AND EFFECTIVE_TO_DATE IS NULL;
SELECT IMPLICIT_INTEREST_RATE
FROM OKL_K_HEADERS
WHERE ID = p_id;
SELECT COUNT(1)
INTO l_base_count
FROM OKL_K_RATE_PARAMS
WHERE khr_id = p_khr_id
AND PARAMETER_TYPE_CODE = 'ACTUAL'
AND EFFECTIVE_TO_DATE IS NULL
AND BASE_RATE IS NULL;
UPDATE OKL_K_RATE_PARAMS
SET BASE_RATE = l_implicit_interest_rate
WHERE khr_id = p_khr_id
AND PARAMETER_TYPE_CODE = 'ACTUAL'
AND EFFECTIVE_TO_DATE IS NULL
AND BASE_RATE IS NULL;