[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(LINE_APPLIED), 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
AND APPLICATION_TYPE = 'CASH'
AND STATUS = 'APP';
SELECT MAX(apply_date)
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
AND APPLICATION_TYPE = 'CASH'
AND STATUS = 'APP';
SELECT NVL(SUM(LINE_APPLIED), 0)
FROM AR_RECEIVABLE_APPLICATIONS_ALL RAA
,OKL_BPD_TLD_AR_LINES_V ARL
WHERE RAA.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
AND RAA.APPLICATION_TYPE = 'CASH'
AND RAA.STATUS = 'APP'
AND RAA.applied_customer_trx_id = arl.customer_trx_id
AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
WHERE PAY.INVESTOR_AGREEMENT_ID IS NULL
AND PAY.TLD_ID = ARL.TLD_ID
AND PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
SELECT MAX(raa.apply_date)
FROM AR_RECEIVABLE_APPLICATIONS_ALL RAA
,OKL_BPD_TLD_AR_LINES_V ARL
WHERE RAA.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
AND RAA.APPLICATION_TYPE = 'CASH'
AND RAA.STATUS = 'APP'
AND RAA.applied_customer_trx_id = arl.customer_trx_id
AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
WHERE PAY.INVESTOR_AGREEMENT_ID IS NULL
AND PAY.TLD_ID = ARL.TLD_ID
AND PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
SELECT id FROM
OKL_PARTY_PAYMENT_HDR
WHERE dnz_chr_id = cp_khr_id
AND NVL(cle_id, -99) = cp_kle_id
AND passthru_term = cp_term;
PROCEDURE invoice_insert (
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_receivables_invoice_id IN NUMBER,
p_tapv_rec IN okl_tap_pvt.tapv_rec_type,
p_tplv_rec IN okl_tpl_pvt.tplv_rec_type,
x_tapv_rec OUT NOCOPY okl_tap_pvt.tapv_rec_type)
IS
-----------------------------------------------------------------
-- Declare Process Variable
-----------------------------------------------------------------
l_api_name CONSTANT VARCHAR2(30) := 'INVOICE_INSERT';
SELECT khr.pdt_id
FROM okl_k_headers khr
WHERE khr.id = p_khr_id;
SELECT currency_code
,currency_conversion_type
,currency_conversion_rate
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = cp_khr_id;
print_line ( '******** IN PROCEDURE INVOICE_INSERT ********');
-- Insert Invoice Headers
------------------------------------------------------------
--Start code added by pgomes on 02/12/2003
--get contract currency parameters
--l_khr_id := p_tapv_rec.khr_id ;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPIDB.pls call Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices ');
Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices(
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_tapv_rec => l_tapv_rec
,x_tapv_rec => lx_tapv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPIDB.pls call Okl_Trx_Ap_Invoices_Pub.insert_trx_ap_invoices ');
-- Insert Invoice Line
------------------------------------------------------------
l_tplv_rec.tap_id := lx_tapv_rec.id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRPIDB.pls call OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns ');
OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns(
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_tplv_rec => l_tplv_rec
,x_tplv_rec => lx_tplv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRPIDB.pls call OKL_TXL_AP_INV_LNS_PUB.insert_txl_ap_inv_lns ');
-- Derive and Insert Distribution Line
------------------------------------------------------------
print_line ( ' -- Creating Distributions. Supplied parameters:');
UPDATE Okl_Trx_Ap_Invoices_B
SET TRX_STATUS_CODE = 'ERROR'
WHERE id = lx_tapv_rec.id;
print_line ( ' -- Updated Header Record with Id '||lx_tapv_rec.id || ' with ERROR Status');
print_line ( '******** EXITING PROCEDURE INVOICE_INSERT ********');
END invoice_insert;
SELECT ppd.vendor_id,
ppd.pay_site_id,
NVL(ppd.payment_term_id, pvs.terms_id) payment_term_id,
NVL(ppd.payment_method_code, pvs.payment_method_lookup_code) payment_method_code,
NVL(ppd.pay_group_code, pvs.pay_group_lookup_code) pay_group_code,
ppd.payment_basis,
TRUNC(NVL(ppd.payment_start_date, khr.start_date)) payment_start_date,
ppd.payment_frequency,
NVL(ppd.remit_days, 0) remit_days,
ppd.disbursement_basis,
ppd.disbursement_fixed_amount,
ppd.disbursement_percent,
ppd.processing_fee_basis,
ppd.processing_fee_fixed_amount,
ppd.processing_fee_percent
--ppd.processing_fee_formula
FROM okl_party_payment_hdr pph,
okl_party_payment_dtls ppd,
okc_k_headers_b khr,
po_vendor_sites pvs
WHERE pph.id = cp_pph_id
AND pph.id = ppd.payment_hdr_id
AND pph.dnz_chr_id = khr.id
AND ppd.pay_site_id = pvs.vendor_site_id;
SELECT trx_number
FROM ra_customer_trx_all
WHERE CUSTOMER_TRX_ID = p_receivables_invoice_id;
SELECT RAA.receivable_application_id
,RAA.LINE_APPLIED
,RAA.apply_date
,ARL.tld_id tld_id
FROM AR_RECEIVABLE_APPLICATIONS_ALL RAA
,OKL_BPD_TLD_AR_LINES_V ARL
WHERE RAA.APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
AND RAA.APPLICATION_TYPE = 'CASH'
AND RAA.STATUS = 'APP'
AND RAA.applied_customer_trx_id = arl.customer_trx_id
AND NOT EXISTS (SELECT 1 FROM OKL_INVESTOR_PAYOUT_SUMMARY_B PAY
WHERE PAY.INVESTOR_AGREEMENT_ID IS NULL
AND PAY.TLD_ID = ARL.TLD_ID
AND PAY.RECEIVABLE_APPLICATION_ID = RAA.RECEIVABLE_APPLICATION_ID);
SELECT id INTO l_try_id
FROM okl_trx_types_tl
WHERE name = 'Disbursement'
AND LANGUAGE= 'US' ;
l_tap_id_tbl.delete;
l_lsm_rcpt_tbl.delete;
invoice_insert (
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_receivables_invoice_id => l_disb_rec.receivables_invoice_id,
P_tapv_rec => l_tapv_rec,
p_tplv_rec => l_tplv_rec,
x_tapv_rec => lx_tapv_rec);
print_line ( ' -- Inserted Pay Invoices');
print_line ( '*=> ERROR : Inserting Pay Invoices');
update okl_trx_ap_invoices_b
set TRX_STATUS_CODE = 'ERROR'
,object_version_number = object_version_number + 1
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID
,request_id = NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
where id = l_tap_id_tbl(id_ind);
INSERT INTO okl_investor_payout_summary_b
( ID,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
INVESTOR_AGREEMENT_ID,
INVESTOR_LINE_ID,
--rkuttiya R12 B Billing Architecture commented out following
--LSM_ID,
--rkuttiya R12 B Billing Architecture added following
TLD_ID,
--
RECEIVABLE_APPLICATION_ID
)
VALUES
(
l_idh_id,
1,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.USER_ID,
SYSDATE,
Fnd_Global.LOGIN_ID,
null, --inv_lease_k_rec.Investor_Agreement_id,
null, --share_rec.TOP_LINE_ID,
--rkuttiya R12 B Billing Architecture commented out following
-- l_lsm_rcpt_tbl(lsm_rcpt_id_ind).lsm_id,
--rkuttiya R12 B Billing Architecture added following
l_lsm_rcpt_tbl(lsm_rcpt_id_ind).tld_id,
--
l_lsm_rcpt_tbl(lsm_rcpt_id_ind).receivable_application_id
);
PROCEDURE update_invoice_pay_status(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_tld_id IN NUMBER
,p_status IN VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INVOICE_PAY_STATUS';
print_line ( '******** IN PROCEDURE UPDATE_INVOICE_PAY_STATUS ********');
Okl_Cnsld_Ar_Strms_Pub.update_cnsld_ar_strms
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_lsmv_rec
,r_lsmv_rec);
okl_txd_ar_ln_dtls_pub.update_txd_ar_ln_dtls
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_txdv_rec
,lx_txdv_rec);
/* Okl_Cnsld_Ar_Strms_Pub.update_cnsld_ar_strms
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_lsmv_rec
,r_lsmv_rec); */
okl_txd_ar_ln_dtls_pub.update_txd_ar_ln_dtls
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,l_txdv_rec
,lx_txdv_rec);
print_line ( '******** EXITING PROCEDURE UPDATE_INVOICE_PAY_STATUS ********');
END update_invoice_pay_status;
update_invoice_pay_status(
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_tld_id => l_disb_rec.tld_id,
p_status => l_return_status);
print_line ('=====> Successfully Updated Consolidated Invoice Stream : pay status code');
update okl_strm_elements
set date_disbursed = trunc(sysdate)
,object_version_number = object_version_number + 1
,last_updated_by = FND_GLOBAL.USER_ID
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID
,request_id = NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
where id = l_disb_rec.sel_id;
SELECT
NULL cnr_id
,arl.receivables_invoice_number
,tai.set_of_books_id
,arv.org_id
,arv.date_consolidated
,arv.currency_code
,tai.khr_id
,til.kle_id
,arl.amount
,arl.sty_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- ,arl.id lsm_id
,tld.id tld_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
,arl.receivables_invoice_id
,null sel_id
,pph.id pph_id
,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
,pph.payout_basis
,null payout_basis_formula
,khr.contract_number
-- 30-OCT-2006 ANSETHUR R12B - Legal Entity
,tai.legal_entity_id
FROM okc_k_headers_b khr
,okc_k_lines_b kle
--added for evergreen change request 08_nov_2005
,okc_line_styles_b lse
,okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_trx_ar_invoices_v tai
,okl_txl_ar_inv_lns_v til
,okl_txd_ar_ln_dtls_b tld
,okl_party_payment_hdr pph
WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khr.id = kle.dnz_chr_id
AND kle.id = til.kle_id
AND til.id = tld.til_id_details
AND arl.receivables_invoice_id > 0
--rkuttiya commented and changed for Billing Architecture
--AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
--start fix for bug 5040815 by pgomes 24-mar-2006
AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
--rkuttiya R12 B Billing Architecture commented out the following
--AND lsm.lln_id = lln.id
--AND lln.cnr_id = cnr.id
--AND cnr.trx_status_code = 'PROCESSED'
--
--rkuttiya R12 B BIlling added the following
AND til.tai_id = tai.id
AND til.id = arl.til_id_details
AND tai.trx_status_code = 'PROCESSED'
AND arv.invoice_id = arl.receivables_invoice_id
--
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
AND pph.payout_basis = 'BILLING'
AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
OR
trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
AND NVL (TRUNC(p_to_date), SYSDATE+10000)
UNION ALL
SELECT
NULL cnr_id
,arl.receivables_invoice_number
,tai.set_of_books_id
,arv.org_id
,arv.date_consolidated
,arv.currency_code
,tai.khr_id
,til.kle_id
,arl.amount
,arl.sty_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- ,arl.id lsm_id
,tld.id tld_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
,arl.receivables_invoice_id
,null sel_id
,pph.id pph_id
,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
,pph.payout_basis
,null payout_basis_formula
,khr.contract_number
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,tai.legal_entity_id
FROM okc_k_headers_b khr
,okc_k_lines_b kle
,okc_k_lines_b sub_kle
--added for evergreen change request 08_nov_2005
,okc_line_styles_b lse
,okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_trx_ar_invoices_v tai
,okl_txl_ar_inv_lns_v til
,okl_txd_ar_ln_dtls_b tld
,okl_party_payment_hdr pph
WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khr.id = sub_kle.dnz_chr_id
AND sub_kle.chr_id is null
--rkuttiya commented for R12 B Billing Architecture
-- AND sub_kle.id = lsm.kle_id
AND sub_kle.id = til.kle_id
AND til.id = tld.til_id_details
--
AND arl.receivables_invoice_id > 0
AND (tld.pay_status_code is NULL OR tld.pay_status_code = 'ERROR')
--start fix for bug 5040815 by pgomes 24-mar-2006
AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
--rkuttiya commented following for R12 B Billing Architecture
--AND lsm.lln_id = lln.id
-- AND lln.cnr_id = cnr.id
--AND cnr.trx_status_code = 'PROCESSED'
--rkuttiya added for R12 B Billing Architecture
AND til.tai_id = tai.id
AND til.id = arl.til_id_details
AND arv.invoice_id = arl.receivables_invoice_id
AND tai.trx_status_code = 'PROCESSED'
--
AND khr.id = kle.chr_id
AND sub_kle.cle_id = kle.id
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
AND pph.payout_basis = 'BILLING'
--rkuttiya R12 B Billing Architecture replaced arv.date_consolidated by arv.date_consolidated
AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
OR
trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
AND NVL (TRUNC(p_to_date), SYSDATE+10000);
SELECT
NULL cnr_id
,arl.receivables_invoice_number
,tai.set_of_books_id
,arv.org_id
,okl_pay_invoices_disb_pvt.receipt_date(arl.receivables_invoice_id) receipt_date
,arv.currency_code
,tai.khr_id
,til.kle_id
,okl_pay_invoices_disb_pvt.receipt_amount(arl.receivables_invoice_id) amount
,arl.sty_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- ,arl.id lsm_id
,tld.id tld_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
,arl.receivables_invoice_id
,null sel_id
,pph.id pph_id
,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
,pph.payout_basis
,null payout_basis_formula
,khr.contract_number
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,tai.legal_entity_id
FROM okc_k_headers_b khr
,okc_k_lines_b kle
--added for evergreen change request 08_nov_2005
,okc_line_styles_b lse
,okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_trx_ar_invoices_v tai
,okl_txl_ar_inv_lns_v til
,okl_txd_ar_ln_dtls_b tld
,okl_party_payment_hdr pph
,ar_payment_schedules_all aps
WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khr.id = kle.dnz_chr_id
AND kle.id = til.kle_id
AND arl.receivables_invoice_id > 0
--rkuttiya R12 B Billing Architecture commented the following code and added code replacing lsm by arl
--AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
AND til.id = tld.til_id_details
AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
--
--start fix for bug 5040815 by pgomes 24-mar-2006
AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
--rkuttiya R12 B Billing Architecture commented out the following
--AND lsm.lln_id = lln.id
--AND lln.cnr_id = cnr.id
--AND cnr.trx_status_code = 'PROCESSED'
--rkuttiya added for Billing Architecture
AND til.tai_id = tai.id
AND til.id = arl.til_id_details
AND tai.trx_status_code = 'PROCESSED'
AND arv.invoice_id = arl.receivables_invoice_id
--
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
AND pph.payout_basis = 'FULL_RECEIPT'
AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
OR
trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
AND arl.receivables_invoice_id = aps.customer_trx_id
AND aps.class = 'INV'
AND aps.status = 'CL'
AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
AND NVL (TRUNC(p_to_date), SYSDATE+10000)
UNION ALL
SELECT
NULL cnr_id
,arl.receivables_invoice_number
,tai.set_of_books_id
,arv.org_id
,okl_pay_invoices_disb_pvt.receipt_date(arl.receivables_invoice_id) receipt_date
,arv.currency_code
,tai.khr_id
,til.kle_id
,okl_pay_invoices_disb_pvt.receipt_amount(arl.receivables_invoice_id) amount
,arl.sty_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- ,arl.id lsm_id
,tld.id tld_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
,arl.receivables_invoice_id
,null sel_id
,pph.id pph_id
,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
,pph.payout_basis
,null payout_basis_formula
,khr.contract_number
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,tai.legal_entity_id
FROM okc_k_headers_b khr
,okc_k_lines_b kle
,okc_k_lines_b sub_kle
--added for evergreen change request 08_nov_2005
,okc_line_styles_b lse
,okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_trx_ar_invoices_v tai
,okl_txl_ar_inv_lns_v til
,okl_txd_ar_ln_dtls_b tld
,okl_party_payment_hdr pph
,ar_payment_schedules_all aps
WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khr.id = sub_kle.dnz_chr_id
AND sub_kle.chr_id is null
--rkuttiya commented for R12 B Billing Architecture
--AND sub_kle.id = lsm.kle_id
--rkuttiya R12 Billing Architecture
AND sub_kle.id = til.kle_id
--
AND arl.receivables_invoice_id > 0
--rkuttiya commented for R12 B Billing Architecture
--AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
--rkuttiya added R12B Billing Architecture
AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
--start fix for bug 5040815 by pgomes 24-mar-2006
AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
--rkuttiya commented for R12 B Billing Architecture
--AND lsm.lln_id = lln.id
--AND lln.cnr_id = cnr.id
--AND cnr.trx_status_code = 'PROCESSED'
--rkuttiya added for R12 B Billing Architecture
AND til.tai_id = tai.id
AND til.id = arl.til_id_details
AND tai.trx_status_code = 'PROCESSED'
AND arv.invoice_id = arl.receivables_invoice_id
--
AND khr.id = kle.chr_id
AND sub_kle.cle_id = kle.id
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
AND pph.payout_basis = 'FULL_RECEIPT'
AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
OR
trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
AND arl.receivables_invoice_id = aps.customer_trx_id
AND aps.class = 'INV'
AND aps.status = 'CL'
AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
AND NVL (TRUNC(p_to_date), SYSDATE+10000);
SELECT
NULL cnr_id
,arl.receivables_invoice_number
,tai.set_of_books_id
,arv.org_id
,okl_pay_invoices_disb_pvt.partial_receipt_date(arl.receivables_invoice_id) partial_receipt_date
,arv.currency_code
,tai.khr_id
,til.kle_id
,okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) amount
,arl.sty_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- ,arl.id lsm_id
,tld.id tld_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
,arl.receivables_invoice_id
,null sel_id
,pph.id pph_id
,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
,pph.payout_basis
,null payout_basis_formula
,khr.contract_number
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,tai.legal_entity_id
FROM okc_k_headers_b khr
,okc_k_lines_b kle
--added for evergreen change request 08_nov_2005
,okc_line_styles_b lse
,okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_trx_ar_invoices_v tai
,okl_txl_ar_inv_lns_v til
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
,okl_txd_ar_ln_dtls_b tld
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
,okl_party_payment_hdr pph
,ar_payment_schedules_all aps
WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khr.id = kle.dnz_chr_id
AND kle.id = til.kle_id
AND arl.receivables_invoice_id > 0
--AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
--start fix for bug 5040815 by pgomes 24-mar-2006
AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
--rkuttiya R12 B Billing Architecture commented the following
--AND lsm.lln_id = lln.id
--AND lln.cnr_id = cnr.id
--AND cnr.trx_status_code = 'PROCESSED'
AND til.tai_id = tai.id
AND til.id = arl.til_id_details
AND tai.trx_status_code = 'PROCESSED'
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
AND til.id = tld.til_id_details
AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
AND arv.invoice_id = arl.receivables_invoice_id
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
AND pph.payout_basis = 'PARTIAL_RECEIPT'
AND okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) <> 0
AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
OR
trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
AND arl.receivables_invoice_id = aps.customer_trx_id
AND aps.class = 'INV'
AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
AND NVL (TRUNC(p_to_date), SYSDATE+10000)
UNION ALL
SELECT
NULL cnr_id
,arl.receivables_invoice_number
,tai.set_of_books_id
,arv.org_id
,okl_pay_invoices_disb_pvt.partial_receipt_date(arl.receivables_invoice_id) partial_receipt_date
,arv.currency_code
,tai.khr_id
,til.kle_id
,okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) amount
,arl.sty_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- ,arl.id lsm_id
,tld.id tld_id
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
,arl.receivables_invoice_id
, null sel_id
,pph.id pph_id
,NVL(pph.passthru_stream_type_id, arl.sty_id) passthru_stream_type_id
,pph.payout_basis
,null payout_basis_formula
,khr.contract_number
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
,tai.legal_entity_id
FROM okc_k_headers_b khr
,okc_k_lines_b kle
,okc_k_lines_b sub_kle
--added for evergreen change request 08_nov_2005
,okc_line_styles_b lse
,okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_trx_ar_invoices_v tai
,okl_txl_ar_inv_lns_v til
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
,okl_txd_ar_ln_dtls_b tld
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
,okl_party_payment_hdr pph
,ar_payment_schedules_all aps
WHERE khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khr.id = sub_kle.dnz_chr_id
AND sub_kle.chr_id is null
AND sub_kle.id = til.kle_id
AND arl.receivables_invoice_id > 0
--start fix for bug 5040815 by pgomes 24-mar-2006
AND arl.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
--AND (lsm.pay_status_code is NULL OR lsm.pay_status_code = 'ERROR')
--rkuttiya R12 B Billing Architecture commented out following
--AND lsm.lln_id = lln.id
--AND lln.cnr_id = cnr.id
--AND cnr.trx_status_code = 'PROCESSED'
--rkuttiya R12 B BIlling Architecture added following
AND til.tai_id = tai.id
AND til.id = arl.til_id_details
AND tai.trx_status_code = 'PROCESSED'
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts starts
AND til.id = tld.til_id_details
AND (tld.pay_status_code IS NULL OR tld.pay_status_code = 'ERROR')
-- 05/31/07 ansethur added for R12B Billing architecture Passthrough impacts ends
AND arv.invoice_id = arl.receivables_invoice_id
--
AND khr.id = kle.chr_id
AND sub_kle.cle_id = kle.id
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
AND pph.payout_basis = 'PARTIAL_RECEIPT'
AND okl_pay_invoices_disb_pvt.partial_receipt_amount(arl.receivables_invoice_id) <> 0
AND trunc(arv.date_consolidated) >= trunc(NVL(pph.passthru_start_date, arv.date_consolidated))
AND (trunc(arv.date_consolidated) <= trunc(kle.end_date) AND pph.passthru_term = 'BASE'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
OR
trunc(arv.date_consolidated) > trunc(kle.end_date) AND pph.passthru_term = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id)
AND arl.receivables_invoice_id = aps.customer_trx_id
AND aps.class = 'INV'
AND trunc(arv.date_consolidated) BETWEEN NVL (TRUNC(p_from_date), SYSDATE-10000)
AND NVL (TRUNC(p_to_date), SYSDATE+10000);
SELECT null cnr_id,
null consolidated_invoice_number,
hou.set_of_books_id,
khr.authoring_org_id org_id,
ste.stream_element_date,
khr.currency_code currency_code,
stm.khr_id khr_id,
stm.kle_id kle_id,
ste.amount amount,
stm.sty_id sty_id,
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- null lsm_id,
null tld_id,
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
null receivables_invoice_id,
ste.id sel_id,
pph.id pph_id,
NVL(pph.passthru_stream_type_id, stm.sty_id) passthru_stream_type_id,
pph.payout_basis,
null payout_basis_formula,
khr.contract_number,
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
khl.legal_entity_id
FROM okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
okl_k_headers khl,
hr_operating_units hou,
okc_k_lines_b kle,
--added for evergreen change request 08_nov_2005
okc_line_styles_b lse,
okc_statuses_b khs,
okc_statuses_b kls,
okl_party_payment_hdr pph
WHERE trunc(ste.stream_element_date) >=
trunc(NVL (p_from_date, ste.stream_element_date))
AND trunc(ste.stream_element_date) <=
trunc((NVL (p_to_date, SYSDATE) ))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_disbursed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
--start fix for bug 5040815 by pgomes 24-mar-2006
AND stm.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','TERMINATED')
AND khr.authoring_org_id = hou.organization_id
AND khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND kle.id = stm.kle_id
AND kle.sts_code = kls.code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
AND pph.payout_basis = 'DUE_DATE'
AND trunc(ste.stream_element_date) >= trunc(NVL(pph.passthru_start_date, ste.stream_element_date))
AND trunc(ste.stream_element_date) <= trunc(kle.end_date)
AND pph.passthru_term = 'BASE'
UNION ALL
SELECT null cnr_id,
null consolidated_invoice_number,
hou.set_of_books_id,
khr.authoring_org_id org_id,
ste.stream_element_date,
khr.currency_code currency_code,
stm.khr_id khr_id,
stm.kle_id kle_id,
ste.amount amount,
stm.sty_id sty_id,
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- null lsm_id,
null tld_id,
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
null receivables_invoice_id,
ste.id sel_id,
pph.id pph_id,
NVL(pph.passthru_stream_type_id, stm.sty_id) passthru_stream_type_id,
pph.payout_basis,
null payout_basis_formula,
khr.contract_number,
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
khl.legal_entity_id
FROM okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
okl_k_headers khl,
hr_operating_units hou,
okc_k_lines_b kle,
okc_k_lines_b sub_kle,
--added for evergreen change request 08_nov_2005
okc_line_styles_b lse,
okc_statuses_b khs,
okc_statuses_b kls,
okl_party_payment_hdr pph
WHERE trunc(ste.stream_element_date) >=
trunc(NVL (p_from_date, ste.stream_element_date))
AND trunc(ste.stream_element_date) <=
trunc((NVL (p_to_date, SYSDATE) ))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_disbursed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
--start fix for bug 5040815 by pgomes 24-mar-2006
AND stm.sty_id NOT IN (SELECT id FROM okl_strm_type_v
WHERE stream_type_purpose in ('LATE_FEE', 'LATE_INTEREST'))
--end fix for bug 5040815 by pgomes 24-mar-2006
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','TERMINATED')
AND khr.authoring_org_id = hou.organization_id
AND khr.contract_number = NVL(p_contract_number, khr.contract_number)
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khr.id = sub_kle.dnz_chr_id
AND sub_kle.chr_id IS NULL
AND sub_kle.id = stm.kle_id
AND sub_kle.cle_id = kle.id
AND kle.sts_code = kls.code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND kle.dnz_chr_id = pph.dnz_chr_id
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'BASE') = pph.id
AND pph.payout_basis = 'DUE_DATE'
AND trunc(ste.stream_element_date) >= trunc(NVL(pph.passthru_start_date, ste.stream_element_date))
AND trunc(ste.stream_element_date) <= trunc(kle.end_date)
AND pph.passthru_term = 'BASE';
SELECT null cnr_id,
null consolidated_invoice_number,
hou.set_of_books_id,
okch.authoring_org_id org_id,
kle.start_date transaction_date,
okch.currency_code,
oklh.id khr_id,
kle.id kle_id,
null amount,
null sty_id,
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts starts
-- null lsm_id
null tld_id,
-- 05/31/07 ansethur changes for R12B Billing architecture Passthrough impacts ends
null receivables_invoice_id,
null sel_id,
pph.id pph_id,
pph.passthru_stream_type_id,
pph.payout_basis,
pph.payout_basis_formula,
okch.contract_number,
-- 01-NOV-2006 ANSETHUR R12B - Legal Entity
oklh.legal_entity_id
FROM okl_k_headers oklh,
okc_k_headers_b okch,
hr_operating_units hou,
okc_k_lines_b kle,
--added for evergreen change request 08_nov_2005
okc_line_styles_b lse,
okl_party_payment_hdr pph
WHERE oklh.id = okch.id
AND okch.contract_number = NVL (p_contract_number, okch.contract_number)
AND okch.scs_code IN ('LEASE', 'LOAN')
AND okch.sts_code = 'EVERGREEN'
AND oklh.deal_type IS NOT NULL
AND okch.authoring_org_id = hou.organization_id
AND oklh.id = kle.dnz_chr_id
AND kle.sts_code = 'EVERGREEN'
--added for evergreen change request 08_nov_2005
AND kle.lse_id = lse.id
AND kle.dnz_chr_id = pph.dnz_chr_id
--added for evergreen change request 08_nov_2005
AND OKL_PAY_INVOICES_DISB_PVT.get_kle_party_pmt_hdr(kle.dnz_chr_id,kle.id,lse.lty_code,'EVERGREEN') = pph.id
--commented for evergreen change request 08_nov_2005
--AND kle.id = pph.cle_id
AND pph.passthru_term = 'EVERGREEN'
AND pph.payout_basis = 'FORMULA';
l_disb_tbl.delete;
l_disb_tbl.delete;
l_disb_tbl.delete;
l_disb_tbl.delete;
l_disb_tbl.delete;
SELECT NVL(SUM(NVL(TAP.AMOUNT,0)),0)
FROM OKL_TRX_AP_INVOICES_B TAP
,OKL_TXL_AP_INV_LNS_ALL_B TPL
WHERE TAP.ID = TPL.TAP_ID
AND TAP.TRX_STATUS_CODE = 'PROCESSED' -- push to AP
AND TRUNC(DATE_INVOICED) <= TRUNC(p_trx_date)
AND
( EXISTS
(
-- indirect refer from MLA contract's credit line
SELECT 1 -- op chrid
FROM OKC_K_HEADERS_ALL_B KHR_OP
WHERE KHR_OP.ID = TPL.KHR_ID -- link
AND EXISTS (
SELECT 1 -- MLA id
FROM OKC_K_HEADERS_ALL_B KHR,
OKC_GOVERNANCES MLA_GOV
WHERE KHR.ID = MLA_GOV.CHR_ID_REFERRED
AND KHR.SCS_CODE = 'MASTER_LEASE'
AND MLA_GOV.DNZ_CHR_ID = KHR_OP.ID -- link
AND EXISTS (
SELECT 1 -- credit line id
FROM OKC_K_HEADERS_ALL_B CRD,
OKC_GOVERNANCES CRD_GOV
WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
AND CRD.STS_CODE = 'ACTIVE'
AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
AND CRD.ID = p_creditline_id
)
)
)
OR
EXISTS
(
-- non-MLA contracts direct associated with credit line
SELECT 1 -- op chrid
FROM OKC_K_HEADERS_ALL_B KHR
WHERE KHR.ID = TPL.KHR_ID -- link
AND KHR.SCS_CODE <> 'MASTER_LEASE'
AND EXISTS (
SELECT 1 -- credit line id
FROM OKC_K_HEADERS_ALL_B CRD,
OKC_GOVERNANCES CRD_GOV
WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
AND CRD.STS_CODE = 'ACTIVE'
AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
AND CRD.ID = p_creditline_id
)
)
)
;
select 1 -- Revloving line of credit line
from okl_k_headers REV
where rev.id = p_creditline_id
and REV.REVOLVING_CREDIT_YN = 'Y'
;
SELECT
NVL(SUM(NVL(PS.AMOUNT_APPLIED,0)),0)
FROM
AR_PAYMENT_SCHEDULES_ALL PS,
--rkuttiya R12 B Billing Architecture commented
--OKL_CNSLD_AR_STRMS_B ST,
okl_bpd_ar_inv_lines_v ST,
--OKL_STRM_TYPE_TL SM,
okl_strm_type_v SM,
OKC_K_HEADERS_B CN
WHERE
PS.CLASS IN ('INV') AND
ST.INVOICE_ID = PS.CUSTOMER_TRX_ID AND
SM.ID = ST.STY_ID AND
--SM.LANGUAGE = USERENV ('LANG') AND
CN.ID = ST.CONTRACT_ID AND
--SM.NAME = 'PRINCIPAL PAYMENT' AND
SM.stream_type_purpose = 'PRINCIPAL_PAYMENT' AND
TRUNC(NVL(PS.TRX_DATE, SYSDATE)) <= TRUNC(p_trx_date)
AND
( EXISTS
(
-- indirect refer from MLA contract's credit line
SELECT 1 -- op chrid
FROM OKC_K_HEADERS_ALL_B KHR_OP
WHERE KHR_OP.ID = CN.ID -- link
AND EXISTS (
SELECT 1 -- MLA id
FROM OKC_K_HEADERS_ALL_B KHR,
OKC_GOVERNANCES MLA_GOV
WHERE KHR.ID = MLA_GOV.CHR_ID_REFERRED
AND KHR.SCS_CODE = 'MASTER_LEASE'
AND MLA_GOV.DNZ_CHR_ID = KHR_OP.ID -- link
AND EXISTS (
SELECT 1 -- credit line id
FROM OKC_K_HEADERS_ALL_B CRD,
OKC_GOVERNANCES CRD_GOV
WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
AND CRD.STS_CODE = 'ACTIVE'
AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
AND CRD.ID = p_creditline_id
)
)
)
OR
EXISTS
(
-- non-MLA contracts direct associated with credit line
SELECT 1 -- op chrid
FROM OKC_K_HEADERS_ALL_B KHR
WHERE KHR.ID = CN.ID -- link
AND KHR.SCS_CODE <> 'MASTER_LEASE'
AND EXISTS (
SELECT 1 -- credit line id
FROM OKC_K_HEADERS_ALL_B CRD,
OKC_GOVERNANCES CRD_GOV
WHERE CRD.ID = CRD_GOV.CHR_ID_REFERRED
AND CRD.STS_CODE = 'ACTIVE'
AND KHR.ID = CRD_GOV.DNZ_CHR_ID -- link
AND CRD.ID = p_creditline_id
)
)
)
;