[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT request_status_code,
object1_id1,
jtot_object1_code
FROM okl_trx_requests
where id=a_id;
SELECT sis_code
FROM okl_stream_interfaces
WHERE ID=a_id;
SELECT ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
CHR_ID_RESPONSE,
CHR_ID_AWARD,
INV_ORGANIZATION_ID,
STS_CODE,
QCL_ID,
SCS_CODE,
CONTRACT_NUMBER,
CURRENCY_CODE,
CONTRACT_NUMBER_MODIFIER,
ARCHIVED_YN,
DELETED_YN,
CUST_PO_NUMBER_REQ_YN,
PRE_PAY_REQ_YN,
CUST_PO_NUMBER,
SHORT_DESCRIPTION,
COMMENTS,
DESCRIPTION,
DPAS_RATING,
COGNOMEN,
TEMPLATE_YN,
TEMPLATE_USED,
DATE_APPROVED,
DATETIME_CANCELLED,
AUTO_RENEW_DAYS,
DATE_ISSUED,
DATETIME_RESPONDED,
NON_RESPONSE_REASON,
NON_RESPONSE_EXPLAIN,
RFP_TYPE,
CHR_TYPE,
KEEP_ON_MAIL_LIST,
SET_ASIDE_REASON,
SET_ASIDE_PERCENT,
RESPONSE_COPIES_REQ,
DATE_CLOSE_PROJECTED,
DATETIME_PROPOSED,
DATE_SIGNED,
DATE_TERMINATED,
DATE_RENEWED,
TRN_CODE,
START_DATE,
END_DATE,
AUTHORING_ORG_ID,
BUY_OR_SELL,
ISSUE_OR_RECEIVE,
ESTIMATED_AMOUNT,
ESTIMATED_AMOUNT_RENEWED,
CURRENCY_CODE_RENEWED,
UPG_ORIG_SYSTEM_REF,
UPG_ORIG_SYSTEM_REF_ID,
APPLICATION_ID,
ORIG_SYSTEM_SOURCE_CODE,
ORIG_SYSTEM_ID1,
ORIG_SYSTEM_REFERENCE1,
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 okc_k_headers_v chrv
WHERE chrv.id = p_id;
x_chrv_rec.DELETED_YN,
x_chrv_rec.CUST_PO_NUMBER_REQ_YN,
x_chrv_rec.PRE_PAY_REQ_YN,
x_chrv_rec.CUST_PO_NUMBER,
x_chrv_rec.SHORT_DESCRIPTION,
x_chrv_rec.COMMENTS,
x_chrv_rec.DESCRIPTION,
x_chrv_rec.DPAS_RATING,
x_chrv_rec.COGNOMEN,
x_chrv_rec.TEMPLATE_YN,
x_chrv_rec.TEMPLATE_USED,
x_chrv_rec.DATE_APPROVED,
x_chrv_rec.DATETIME_CANCELLED,
x_chrv_rec.AUTO_RENEW_DAYS,
x_chrv_rec.DATE_ISSUED,
x_chrv_rec.DATETIME_RESPONDED,
x_chrv_rec.NON_RESPONSE_REASON,
x_chrv_rec.NON_RESPONSE_EXPLAIN,
x_chrv_rec.RFP_TYPE,
x_chrv_rec.CHR_TYPE,
x_chrv_rec.KEEP_ON_MAIL_LIST,
x_chrv_rec.SET_ASIDE_REASON,
x_chrv_rec.SET_ASIDE_PERCENT,
x_chrv_rec.RESPONSE_COPIES_REQ,
x_chrv_rec.DATE_CLOSE_PROJECTED,
x_chrv_rec.DATETIME_PROPOSED,
x_chrv_rec.DATE_SIGNED,
x_chrv_rec.DATE_TERMINATED,
x_chrv_rec.DATE_RENEWED,
x_chrv_rec.TRN_CODE,
x_chrv_rec.START_DATE,
x_chrv_rec.END_DATE,
x_chrv_rec.AUTHORING_ORG_ID,
x_chrv_rec.BUY_OR_SELL,
x_chrv_rec.ISSUE_OR_RECEIVE,
x_chrv_rec.ESTIMATED_AMOUNT,
x_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
x_chrv_rec.CURRENCY_CODE_RENEWED,
x_chrv_rec.UPG_ORIG_SYSTEM_REF,
x_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
x_chrv_rec.APPLICATION_ID,
x_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
x_chrv_rec.ORIG_SYSTEM_ID1,
x_chrv_rec.ORIG_SYSTEM_REFERENCE1,
x_chrv_rec.ATTRIBUTE_CATEGORY,
x_chrv_rec.ATTRIBUTE1,
x_chrv_rec.ATTRIBUTE2,
x_chrv_rec.ATTRIBUTE3,
x_chrv_rec.ATTRIBUTE4,
x_chrv_rec.ATTRIBUTE5,
x_chrv_rec.ATTRIBUTE6,
x_chrv_rec.ATTRIBUTE7,
x_chrv_rec.ATTRIBUTE8,
x_chrv_rec.ATTRIBUTE9,
x_chrv_rec.ATTRIBUTE10,
x_chrv_rec.ATTRIBUTE11,
x_chrv_rec.ATTRIBUTE12,
x_chrv_rec.ATTRIBUTE13,
x_chrv_rec.ATTRIBUTE14,
x_chrv_rec.ATTRIBUTE15,
x_chrv_rec.CREATED_BY,
x_chrv_rec.CREATION_DATE,
x_chrv_rec.LAST_UPDATED_BY,
x_chrv_rec.LAST_UPDATE_DATE,
x_chrv_rec.LAST_UPDATE_LOGIN;
SELECT
ID,
OBJECT_VERSION_NUMBER,
ISG_ID,
KHR_ID,
PDT_ID,
AMD_CODE,
DATE_FIRST_ACTIVITY,
GENERATE_ACCRUAL_YN,
GENERATE_ACCRUAL_OVERRIDE_YN,
DATE_REFINANCED,
CREDIT_ACT_YN,
TERM_DURATION,
CONVERTED_ACCOUNT_YN,
DATE_CONVERSION_EFFECTIVE,
SYNDICATABLE_YN,
SALESTYPE_YN,
DATE_DEAL_TRANSFERRED,
DATETIME_PROPOSAL_EFFECTIVE,
DATETIME_PROPOSAL_INEFFECTIVE,
DATE_PROPOSAL_ACCEPTED,
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,
PRE_TAX_YIELD,
AFTER_TAX_YIELD,
IMPLICIT_INTEREST_RATE,
IMPLICIT_NON_IDC_INTEREST_RATE,
TARGET_PRE_TAX_YIELD,
TARGET_AFTER_TAX_YIELD,
TARGET_IMPLICIT_INTEREST_RATE,
TARGET_IMPLICIT_NONIDC_INTRATE,
DATE_LAST_INTERIM_INTEREST_CAL,
DEAL_TYPE,
PRE_TAX_IRR,
AFTER_TAX_IRR,
EXPECTED_DELIVERY_DATE,
ACCEPTED_DATE,
PREFUNDING_ELIGIBLE_YN,
REVOLVING_CREDIT_YN
FROM OKL_K_HEADERS_V
WHERE OKL_K_HEADERS_V.id = p_id;
l_khrv_rec.LAST_UPDATED_BY,
l_khrv_rec.LAST_UPDATE_DATE,
l_khrv_rec.LAST_UPDATE_LOGIN,
l_khrv_rec.PRE_TAX_YIELD,
l_khrv_rec.AFTER_TAX_YIELD,
l_khrv_rec.IMPLICIT_INTEREST_RATE,
l_khrv_rec.IMPLICIT_NON_IDC_INTEREST_RATE,
l_khrv_rec.TARGET_PRE_TAX_YIELD,
l_khrv_rec.TARGET_AFTER_TAX_YIELD,
l_khrv_rec.TARGET_IMPLICIT_INTEREST_RATE,
l_khrv_rec.TARGET_IMPLICIT_NONIDC_INTRATE,
l_khrv_rec.DATE_LAST_INTERIM_INTEREST_CAL,
l_khrv_rec.DEAL_TYPE,
l_khrv_rec.PRE_TAX_IRR,
l_khrv_rec.AFTER_TAX_IRR,
l_khrv_rec.EXPECTED_DELIVERY_DATE,
l_khrv_rec.ACCEPTED_DATE,
l_khrv_rec.PREFUNDING_ELIGIBLE_YN,
l_khrv_rec.REVOLVING_CREDIT_YN
;
SELECT
'SYNDICATION'
FROM
okc_k_headers_b chr
WHERE
EXISTS ( SELECT 'x' FROM okc_k_items cim
WHERE cim.object1_id1 = to_char(chr.id) AND
EXISTS ( SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id AND
lse.lty_code = 'SHARED' AND cle.id = cim.cle_id ) AND
EXISTS ( SELECT 'x' FROM okc_k_headers_b chr2
WHERE chr2.id = cim.dnz_chr_id AND
chr2.scs_code = 'SYNDICATION' AND
chr2.sts_code not in ('TERMINATED','ABANDONED') ) ) AND
chr.scs_code = 'LEASE' AND
id = p_khr_id;
select '1'
from okc_rules_b
where dnz_chr_id = p_khr_id
and rule_information_category = 'LAFCTG'
and (rule_information1 is not null
or rule_information2 is not null
or rule_information3 is not null);
SELECT contract_number
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT okl_rbk_seq.nextval
INTO l_seq_no
FROM DUAL;
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
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_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
PROCEDURE update_hdr_info(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_working_copy_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
p_start_date IN OKL_K_HEADERS_FULL_V.START_DATE%TYPE,
p_end_date IN OKL_K_HEADERS_FULL_V.END_DATE%TYPE,
p_term_duration IN OKL_K_HEADERS_FULL_V.TERM_DURATION%TYPE
)
IS
l_khrv_rec khrv_rec_type;
update_failed EXCEPTION;
RAISE update_failed;
RAISE update_failed;
okl_contract_pub.update_contract_header(
p_api_version => 1.0,
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_restricted_update => OKC_API.G_FALSE,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec
);
RAISE update_failed;
WHEN update_failed THEN
x_return_status := OKC_API.G_RET_STS_ERROR;
END update_hdr_info;
SELECT count(sele.amount)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL PAYMENT'
AND str.say_code = 'CURR'
--multigaap changes
AND str.ACTIVE_YN = 'Y'
AND str.PURPOSE_CODE is NULL
--end multigaap changes
AND str.khr_id = c_khr_id
and sele.DATE_BILLED is not null;
SELECT sum(sele.amount)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL BALANCE'
AND str.say_code = 'CURR'
--multigaap changes
AND str.ACTIVE_YN = 'Y'
AND str.PURPOSE_CODE is NULL
--end multigaap changes
AND str.khr_id = c_khr_id
and sele.stream_element_date = (select min(sele.stream_element_date)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL PAYMENT'
AND str.say_code = 'CURR'
--multigaap changes
AND str.ACTIVE_YN = 'Y'
AND str.PURPOSE_CODE is NULL
--end multigaap changes
AND str.khr_id = c_khr_id
AND sele.DATE_BILLED is null);
SELECT sum(sele.amount)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL BALANCE'
AND str.say_code = 'CURR'
--multigaap changes
AND str.ACTIVE_YN = 'Y'
AND str.PURPOSE_CODE is NULL
--end multigaap changes
AND str.khr_id = c_khr_id
and sele.stream_element_date = (select max(sele.stream_element_date)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL PAYMENT'
AND str.say_code = 'CURR'
--multigaap changes
AND str.ACTIVE_YN = 'Y'
AND str.PURPOSE_CODE is NULL
--end multigaap changes
AND str.khr_id = c_khr_id
AND sele.DATE_BILLED is not null);
SELECT count(sele.amount)
FROM okl_strm_elements sele,
okl_streams str,
okl_strm_type_v sty
WHERE sele.stm_id = str.id
AND str.sty_id = sty.id
AND UPPER(sty.name) = 'PRINCIPAL PAYMENT'
AND str.say_code = 'CURR'
--multigaap changes
AND str.ACTIVE_YN = 'Y'
AND str.PURPOSE_CODE is NULL
--end multigaap changes
AND str.kle_id = c_kle_id
and sele.DATE_BILLED is null;
SELECT orig_system_id1
FROM okc_k_lines_b
where id=c_kle_id;
SELECT line.id line_id
FROM okl_k_lines_full_v line,
okc_line_styles_v lse
WHERE line.lse_id=lse.id
and lse.lty_code='FREE_FORM1'
and dnz_chr_id=l_khr_id;
SELECT count(sel.amount)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE sty.name = 'PRINCIPAL PAYMENT'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code is NULL
AND stm.kle_id = c_line_id
AND sel.stm_id = stm.id
AND sel.stream_element_date >
( SELECT NVL(MAX(sel.stream_element_date), c_pay_date)
FROM okl_strm_elements sel,okl_streams stm,
okl_strm_type_v sty
WHERE sty.name = 'PRINCIPAL PAYMENT'
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code is NULL
AND stm.kle_id = c_line_id
AND sel.stm_id = stm.id
AND sel.stream_element_date <= c_pay_date);
select rl.id
from okc_rule_groups_v rg,
okc_rules_v rl
where rl.rgp_id = rg.id
and rl.dnz_chr_id = rg.dnz_chr_id
and rg.cle_id = c_line_id
and rg.rgd_code = 'LALEVL'
and rl.rule_information_category = 'LASLH'
and rl.dnz_chr_id = c_khr_id
and rl.object1_id1=(select id from okl_strm_type_b where code='RENT');
select rl.object1_id1 frequency
,nvl(rl.rule_information10,'N') Arrears
from okc_rule_groups_v rg,
okc_rules_v rl
where rl.rgp_id = rg.id
and rl.dnz_chr_id = rg.dnz_chr_id
and rg.cle_id = c_line_id
and rg.rgd_code = 'LALEVL'
and rl.rule_information_category = 'LASLL'
and rl.dnz_chr_id = c_khr_id
and rl.object2_id1=c_rent_slh_id
and rownum = 1;
SELECT COUNT(*)
FROM okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
AND sttyp.stream_type_purpose = p_stream_type
AND rgp.dnz_chr_id = p_khr_id;
SELECT cle.id, cle.lse_id, lse.lty_code
FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND cle.sts_code = 'BOOKED'
AND lse.lty_code = 'FREE_FORM1'
AND chr_id = cp_chr_id;
SELECT rgp.cle_id cle_id,
sttyp.id1 sty_id,
sttyp.code stream_type,
tuom.id1 frequency,
sll_rul.rule_information1 seq_num,
sll_rul.rule_information2 start_date,
sll_rul.rule_information3 period_in_months,
sll_rul.rule_information5 advance_periods,
sll_rul.rule_information6 amount,
sll_rul.rule_information10 due_arrears_yn,
sll_rul.rule_information7 stub_days,
sll_rul.rule_information8 stub_amount,
rgp.dnz_chr_id khr_id
FROM okl_time_units_v tuom,
okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE tuom.id1 = sll_rul.object1_id1
AND sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
AND rgp.cle_id = cp_cle_id
AND sttyp.id1 = cp_sty_id
ORDER BY stream_type, start_date;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
PROCEDURE delete_payments(
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_request_id IN NUMBER)
AS
SUBTYPE cafv_rec_type IS okl_cash_flows_pub.cafv_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'delete_payments';
SELECT id
FROM OKL_CASH_FLOW_OBJECTS
WHERE source_id = cp_request_id;
SELECT id
FROM OKL_CASH_FLOWS
WHERE cfo_id=cp_cfo_id
AND dnz_qte_id=cp_request_id;
SELECT id
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id=cp_caf_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_PRINCIPAL_PAYDOWN_PVT.delete_payments','Begin(+)');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_CS_PRINCIPAL_PAYDOWN_PVT.delete_payments.',
'p_request_id :'||p_request_id);
OKL_CASH_FLOW_LEVELS_PUB.delete_cash_flow_level(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_rec => lp_cflv_rec);
okl_cash_flows_pub.delete_cash_flow(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_rec => lp_cafv_rec);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_CS_PRINCIPAL_PAYDOWN_PVT.delete_payments','End(-)');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_CS_PRINCIPAL_PAYDOWN_PVT.delete_payments ',
'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_CS_PRINCIPAL_PAYDOWN_PVT.delete_payments ',
'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_CS_PRINCIPAL_PAYDOWN_PVT.delete_payments ',
'EXCEPTION :'||sqlerrm);
END delete_payments;
SELECT implicit_interest_rate
FROM okl_k_headers
where id = c_khr_id;
SELECT implicit_interest_rate
FROM okl_k_lines
where id = c_line_id;
SELECT sum(sll_rul.rule_information8)
FROM okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
AND rgp.cle_id = c_cle_id
AND sttyp.id1 = c_sty_id;
SELECT KLE_ID
,NUMBER_OF_PERIODS
,amount
,frequency_code
,decode(arrears,'N','ADVANCE','ARREARS') advance_or_arrears
,start_date
,stub_days
,stub_amount
,advance_payments
FROM okl_cs_ppd_payments_uv
WHERE request_id = c_req_id
AND kle_id=c_kle_id
AND sty_id= c_sty_id
ORDER BY start_date;
SELECT cle.id kle_id
FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND cle.sts_code = 'BOOKED'
AND lse.lty_code = 'FREE_FORM1'
AND chr_id = cp_chr_id;
SELECT KLE_ID
,START_DATE
,NUMBER_OF_PERIODS
,frequency_code
,ADVANCE_PAYMENTS
,arrears
,amount
,stub_days
,stub_amount
FROM okl_cs_ppd_payments_uv
WHERE request_id = c_req_id
AND kle_id=c_kle_id
and start_date > c_start_date
AND sty_id = c_sty_id
ORDER BY start_date;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT distinct
frequency_code
,arrears
,DECODE(frequency_code,'M',30,'Q',90,'S',180,'A',360)
FROM okl_cs_ppd_payments_uv
WHERE request_id = c_req_id
AND kle_id=c_kle_id
AND sty_id=c_rent_sty_id
AND stub_days is null;
select object_version_number
from OKL_TRX_REQUESTS
where id = c_trq_id ;
-- Call the public API for insertion here.
okl_trx_requests_pub.insert_trx_requests(
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_trqv_tbl => p_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Insert Request:'|| x_return_status);
--Call update here.
okl_trx_requests_pub.update_trx_requests(
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_trqv_tbl => l_upd_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Update Request:'|| x_return_status);
--Delete all the payments for this request.
delete_payments(
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_request_id => p_trqv_tbl(1).id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'After Delete PAyments:'|| x_return_status);
okl_trx_requests_pub.update_trx_requests(
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_trqv_tbl => l_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
--Call update here.
okl_trx_requests_pub.update_trx_requests(
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_trqv_tbl => l_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
okl_trx_requests_pub.update_trx_requests(
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_trqv_tbl => l_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
l_csm_loan_levels_tbl.delete;
--Now update the Status and the Transaction id in the CS Request Table.
-- fetch the object version no from the db and update
open c_get_object_ver(x_trqv_tbl(1).id);
--Call update here.
okl_trx_requests_pub.update_trx_requests(
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_trqv_tbl => l_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
PROCEDURE update_ppd_request(
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_trqv_rec IN okl_trx_requests_pub.trqv_rec_type
,x_trqv_rec OUT NOCOPY okl_trx_requests_pub.trqv_rec_type)
AS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PPD_REQUEST';
SELECT object_Version_number
FROM okl_trx_requests
WHERE id=a_id;
-- the update API.
--The status should be present in the FND_LOOKUP.
IF (l_trqv_rec.request_status_code = 'PROCESS_COMPLETE' ) THEN
l_trqv_rec.request_status_code := 'COMPLETE';
okl_trx_requests_pub.update_trx_requests(
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_trqv_rec => l_trqv_rec,
x_trqv_rec => x_trqv_rec);
END update_ppd_request;
SELECT id
FROM okl_trx_types_tl
WHERE name = p_try_name
AND language = 'US';
SELECT sty.id
FROM okl_strm_type_tl styt, okl_strm_type_b sty
WHERE styt.name = p_sty_name
AND styt.language = 'US'
AND sty.id = styt.id
AND sty.start_date <= TRUNC(SYSDATE)
AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);
SELECT pdt_id
FROM okl_k_headers
WHERE id = p_khr_id;
okl_trx_ar_invoices_pub.insert_trx_ar_invoices(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_taiv_rec => i_taiv_rec,
x_taiv_rec => r_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => i_tilv_rec,
x_tilv_rec => r_tilv_rec);
SELECT ID
,OBJECT_VERSION_NUMBER
FROM OKL_TRX_REQUESTS
WHERE REQUEST_TYPE_CODE='PRINCIPAL_PAYDOWN'
AND REQUEST_STATUS_CODE NOT IN ('ACCEPTED','REJECTED'
,'ERROR','PROCESSED','CANCELLED'
,'REBOOK_IN_PROCESS','REBOOK_COMPLETE')
AND DNZ_KHR_ID = c_khr_id;
--Insert a log message here for cancelling the request.
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Cancel PPD with Id:'|| l_trqv_rec.id);
okl_trx_requests_pub.update_trx_requests(
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_trqv_rec => l_trqv_rec,
x_trqv_rec => x_trqv_rec);
SELECT SOURCE_TRX_ID
FROM OKL_TRX_CONTRACTS
WHERE ID=c_trx_id;
SELECT ID
FROM OKL_TRX_CONTRACTS
WHERE ID=c_trx_id
AND TCN_TYPE='PPD';
SELECT ID,object_Version_number
FROM OKL_TRX_REQUESTS
WHERE TCN_ID=c_trx_id;
--Update the Request to Rebook Complete.
--Get the request details.
l_trqv_rec.id := l_req_id;
okl_trx_requests_pub.update_trx_requests( 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_trqv_rec => l_trqv_rec,
x_trqv_rec => lx_trqv_rec);
-- 3. Apply the selected receipt on the generated invoices.
-- 4. Update the status of the request to Processed.
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling WF');
SELECT count(id)
FROM okl_trx_requests
where request_type_code='PRINCIPAL_PAYDOWN'
and dnz_khr_id = a_khr_id
and payment_date > a_effective_date
and request_status_code IN ('ACCEPTED','REBOOK_IN_PROCESS'
,'REBOOK_COMPLETE','PROCESSED');
SELECT count(id)
FROM okl_trx_requests
where request_type_code='PRINCIPAL_PAYDOWN'
and id=a_id;
SELECT cfo.id
FROM okl_cash_flow_objects cfo, OKL_TRX_QTE_CF_OBJECTS qco
WHERE cfo.id = qco.cfo_id
AND cfo.oty_code = cp_oty_code
AND cfo.source_table = cp_source_table
AND cfo.source_id = cp_source_id
AND qco.base_source_id = cp_base_src_id;
okl_cash_flow_objects_pub.insert_cash_flow_object(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cfov_rec => lp_cfov_rec,
x_cfov_rec => lx_cfov_rec);
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT object_Version_number
,payment_amount
,payment_date
FROM okl_trx_requests
WHERE id=a_id;
SELECT COUNT(*)
FROM okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
AND sttyp.stream_type_purpose = p_stream_type
AND rgp.dnz_chr_id = p_khr_id;
--Update the Request with the status of complete.
l_trqv_rec.request_status_code := 'COMPLETE';
okl_trx_requests_pub.update_trx_requests( 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_trqv_rec => l_trqv_rec,
x_trqv_rec => lx_trqv_rec);
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT id
FROM OKL_TRX_QTE_CF_OBJECTS
WHERE qte_id=cp_qte_id
AND cfo_id=cp_cfo_id
AND base_source_id=cp_base_src_id;
SELECT id
FROM OKL_CASH_FLOWS
WHERE cfo_id=cp_cfo_id
AND dnz_qte_id=cp_qte_id
AND sty_id = cp_sty_id;
SELECT id
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id=cp_caf_id
AND start_date=cp_start_date;
SELECT method_of_calculation_code
FROM okl_trx_requests
WHERE id = cp_trq_id;
SELECT id,number_of_periods,start_date
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id=cp_caf_id
AND amount = cp_amount
AND stub_days IS null;
OKL_TRX_QTE_CF_OBJECTS_PUB.insert_quote_cf_object(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qcov_rec => lp_qcov_rec,
x_qcov_rec => lx_qcov_rec);
okl_cash_flows_pub.insert_cash_flow(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_rec => lp_cafv_rec,
x_cafv_rec => lx_cafv_rec);
okl_cash_flows_pub.update_cash_flow(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_rec => lp_cafv_rec,
x_cafv_rec => lx_cafv_rec);
OKL_CASH_FLOW_LEVELS_PUB.insert_cash_flow_level(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_rec => lp_cflv_rec,
x_cflv_rec => lx_cflv_rec);
OKL_CASH_FLOW_LEVELS_PUB.update_cash_flow_level(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_rec => lp_cflv_rec,
x_cflv_rec => lx_cflv_rec);
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT id
FROM OKL_TRX_QTE_CF_OBJECTS
WHERE qte_id=cp_qte_id
AND cfo_id=cp_cfo_id
AND base_source_id=cp_base_src_id;
SELECT id
FROM OKL_CASH_FLOWS
WHERE cfo_id=cp_cfo_id
AND dnz_qte_id=cp_qte_id
AND sty_id = cp_sty_id;
SELECT id
FROM OKL_CASH_FLOW_LEVELS
WHERE caf_id=cp_caf_id
AND start_date=cp_start_date;
OKL_TRX_QTE_CF_OBJECTS_PUB.insert_quote_cf_object(p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qcov_rec => lp_qcov_rec,
x_qcov_rec => lx_qcov_rec);
okl_cash_flows_pub.insert_cash_flow(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_rec => lp_cafv_rec,
x_cafv_rec => lx_cafv_rec);
okl_cash_flows_pub.update_cash_flow(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cafv_rec => lp_cafv_rec,
x_cafv_rec => lx_cafv_rec);
OKL_CASH_FLOW_LEVELS_PUB.insert_cash_flow_level(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_rec => lp_cflv_rec,
x_cflv_rec => lx_cflv_rec);
OKL_CASH_FLOW_LEVELS_PUB.update_cash_flow_level(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cflv_rec => lp_cflv_rec,
x_cflv_rec => lx_cflv_rec);
SELECT currency_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT object_Version_number
,payment_amount
,payment_date
FROM okl_trx_requests
WHERE id=a_id;
SELECT dnz_khr_id
,kle_id
,sty_id
,frequency_code
,Arrears
,number_of_periods
,amount
,stub_days
,stub_amount
,start_date
,advance_payments
FROM OKL_CS_PPD_PAYMENTS_UV
WHERE request_id = a_id
order by kle_id,sty_id,start_date;
SELECT dnz_khr_id
,payment_date
,payment_amount
,object_Version_number
,request_status_code
FROM okl_trx_requests
WHERE id=a_id;
SELECT base_source_id
FROM okl_trx_qte_cf_objects
WHERE qte_id=a_id;
okl_trx_requests_pub.update_trx_requests( 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_trqv_rec => l_trqv_rec,
x_trqv_rec => lx_trqv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updated Request to ACCEPTED');
okl_trx_requests_pub.update_trx_requests( 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_trqv_rec => l_post_trqv_rec,
x_trqv_rec => lx_trqv_rec);
SELECT contract_number
from okc_k_headers_b
where id=c_id;
SELECT payment_date
,receipt_id
FROM okl_trx_requests
where id=c_req_id;
SELECT receipt_date
FROM OKL_CS_PPD_RECEIPTS_UV
WHERE receipt_id = c_rcpt_id;
SELECT id
,dnz_khr_id
,payment_date
,payment_amount
,object_Version_number
,request_status_code,
paydown_type
FROM okl_trx_requests
WHERE id=a_id;
SELECT base_source_id
FROM okl_trx_qte_cf_objects
WHERE qte_id=a_id;
SELECT id
FROM okl_trx_types_tl
WHERE LANGUAGE = 'US'
AND name = 'Loan Paydown';
SELECT id
FROM okl_trx_types_tl
WHERE LANGUAGE = 'US'
AND name = 'Principal Paydown';
SELECT currency_code,
authoring_org_id
FROM okl_k_headers_full_v
WHERE id = p_chr_id;
SELECT okl_sif_seq.nextval
FROM dual;
SELECT SE_LINE_NUMBER
FROM OKL_STRM_ELEMENTS_V
WHERE stm_id = c_stm_id
ORDER BY SE_LINE_NUMBER DESC;
SELECT rgp.cle_id cle_id,
sttyp.id1 sty_id,
sttyp.code stream_type,
tuom.id1 frequency,
sll_rul.id rule_id,
sll_rul.rule_information1 seq_num,
to_date(sll_rul.rule_information2,'YYYY/MM/DD HH24:MI:SS') start_date,
sll_rul.rule_information3 period_in_months,
sll_rul.rule_information5 advance_periods,
sll_rul.rule_information6 amount,
sll_rul.rule_information10 due_arrears_yn,
sll_rul.rule_information7 stub_days,
sll_rul.rule_information8 stub_amount,
rgp.dnz_chr_id khr_id
FROM okl_time_units_v tuom,
okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE tuom.id1 = sll_rul.object1_id1
AND sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
and rgp.dnz_chr_id = p_contract_id
AND sttyp.id1 = p_sty_id;
SELECT COUNT(*)
FROM okl_time_units_v tuom,
okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE tuom.id1 = sll_rul.object1_id1
AND sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
and rgp.dnz_chr_id = p_contract_id
AND sttyp.id1 = p_sty_id;
SELECT max(to_date(sll_rul.rule_information2,'YYYY/MM/DD HH24:MI:SS')) start_date
FROM okl_time_units_v tuom,
okc_rules_b sll_rul,
okl_strmtyp_source_v sttyp,
okc_rules_b slh_rul,
okc_rule_groups_b rgp
WHERE tuom.id1 = sll_rul.object1_id1
AND sll_rul.object2_id1 = to_char(slh_rul.id)
AND sll_rul.rgp_id = rgp.id
AND sll_rul.rule_information_category = 'LASLL'
AND sttyp.id1 = slh_rul.object1_id1
AND slh_rul.rgp_id = rgp.id
AND slh_rul.rule_information_category = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
and rgp.dnz_chr_id = p_contract_id
AND sttyp.id1 = p_sty_id;
SELECT *
FROM OKC_K_HEADERS_B
WHERE ID = p_contract_id;
okl_trx_requests_pub.update_trx_requests( 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_trqv_rec => l_trqv_rec,
x_trqv_rec => lx_trqv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updated Request to ACCEPTED');
l_pym_tbl(1).update_type := 'CREATE';
l_pym_tbl(2).update_type := 'CREATE';
l_pym_tbl(1).update_type := 'CREATE';
l_pym_tbl(3).update_type := 'CREATE';
l_pym_tbl(i).update_type := 'DELETE';
p_update_type => 'DELETE',
x_rulv_tbl => l_rulv_tbl_type );
l_pym_tbl(i).update_type := 'CREATE';
l_pym_tbl(i).update_type := 'CREATE';
l_pym_tbl(i).update_type := 'CREATE';
l_pym_tbl(i).update_type := 'CREATE';
l_pym_tbl(i).update_type := 'CREATE';
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'update_type '||l_pym_tbl(j).update_type);
p_update_type => 'CREATE',
x_rulv_tbl => l_rulv_tbl_type );
okl_trx_requests_pub.update_trx_requests( 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_trqv_rec => l_trqv_rec,
x_trqv_rec => lx_trqv_rec);
-- 3. Apply the selected receipt on the generated invoices.
-- 4. Update the status of the request to Processed. --Call BPD API to create AR journal entries
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before calling WF');