[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUBSTR(description,1,240) FROM okc_k_headers_tl
WHERE ID =
( SELECT khr_id
FROM okl_k_headers
WHERE id = p_contract_id);
SELECT bill_to_site_use_id
FROM okc_k_headers_b
WHERE id =p_contract_id;
SELECT RULE_INFORMATION1
FROM okc_rule_groups_b rgp, okc_rules_b rul
WHERE rgp.id = rul.rgp_id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LANNTF'
AND rul.rule_information_category = 'LANNTF';
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT 'Y' FROM okc_k_headers_b chr
WHERE id = p_contract_id
AND 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 in ('LEASE','LOAN');
SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT count(stm.khr_id) remaining_payments
FROM okl_strm_elements ste
,okl_streams stm
,okl_strm_type_b sty
,okc_k_headers_b khr
WHERE stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
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.id = p_contract_id;
l_select Varchar2(2000);
,x_select => l_select );
l_select Varchar2(2000);
,x_select => l_select );
SELECT min(aps.due_date)
FROM okl_cnsld_ar_strms_b ocas
,ar_payment_schedules aps
WHERE ocas.khr_id = p_contract_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class = 'INV'
AND aps.due_date < sysdate
AND NVL(aps.amount_due_remaining, 0) > 0; */
SELECT min(aps.due_date)
FROM okl_bpd_tld_ar_lines_v ocas
,ar_payment_schedules aps
WHERE ocas.khr_id = p_contract_id
AND ocas.customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND aps.due_date < sysdate
AND NVL(aps.amount_due_remaining, 0) > 0;
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
FROM okl_cnsld_ar_strms_b ocas
,ar_payment_schedules aps
,okl_strm_type_v strm
WHERE ocas.khr_id = p_contract_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class ='INV'
AND aps.due_date < sysdate
and strm.id=ocas.sty_id
and strm.id <> p_sty_id; */
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
FROM okl_bpd_tld_ar_lines_v ocas
,ar_payment_schedules aps
,okl_strm_type_v strm
WHERE ocas.khr_id = p_contract_id
AND ocas.customer_trx_id = aps.customer_trx_id
AND aps.class ='INV'
AND aps.due_date < sysdate
and strm.id=ocas.sty_id
and strm.id <> p_sty_id;
SELECT amt,due_date
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM OKL_CNSLD_AR_STRMS_B LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.receivables_invoice_id = aps.customer_trx_id
GROUP BY khr_id, due_date ) amount_date
WHERE amount_date.days=(SELECT MIN(next_due.days)
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM OKL_CNSLD_AR_STRMS_B LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.receivables_invoice_id = aps.customer_trx_id
GROUP BY khr_id, due_date) next_due
WHERE khr_id = p_contract_id
AND SIGN(next_due.days) = -1); */
SELECT amt,due_date
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM OKL_BPD_TLD_AR_LINES_V LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.customer_trx_id = aps.customer_trx_id
GROUP BY khr_id, due_date ) amount_date
WHERE amount_date.days=(SELECT MIN(next_due.days)
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM OKL_BPD_TLD_AR_LINES_V LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.customer_trx_id = aps.customer_trx_id
GROUP BY khr_id, due_date) next_due
WHERE khr_id = p_contract_id
AND SIGN(next_due.days) = -1); */
SELECT SUM(aps.amount_due_original) amt,
aps.due_date due_date
FROM okl_bpd_tld_ar_lines_v LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.customer_trx_id = aps.customer_trx_id
AND lsm.khr_id = p_contract_id
AND due_date>SYSDATE
GROUP BY aps.due_date , lsm.khr_id
ORDER BY (due_date-SYSDATE) desc ;
SELECT amt,due_date
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM OKL_CNSLD_AR_STRMS_B LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.receivables_invoice_id = aps.customer_trx_id
GROUP BY khr_id, due_date ) amount_date
WHERE amount_date.days=(SELECT MIN(next_due.days)
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM OKL_CNSLD_AR_STRMS_B LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.receivables_invoice_id = aps.customer_trx_id
GROUP BY khr_id, due_date) next_due
WHERE khr_id = p_contract_id
AND SIGN(next_due.days) = 1); */
SELECT amt,due_date
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM okl_bpd_tld_ar_lines_v LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.customer_trx_id = aps.customer_trx_id
GROUP BY khr_id, due_date ) amount_date
WHERE amount_date.days=(SELECT MIN(next_due.days)
FROM (SELECT (SYSDATE-due_date) days
,aps.due_date due_date
,SUM(aps.amount_due_original) amt
,lsm.khr_id khr_id
FROM okl_bpd_tld_ar_lines_v LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.customer_trx_id = aps.customer_trx_id
GROUP BY khr_id, due_date) next_due
WHERE khr_id = p_contract_id
AND SIGN(next_due.days) = 1); */
SELECT SUM(aps.amount_due_original) amt,
aps.due_date due_date
FROM okl_bpd_tld_ar_lines_v LSM
,AR_PAYMENT_SCHEDULES APS
WHERE lsm.customer_trx_id = aps.customer_trx_id
AND lsm.khr_id = p_contract_id
AND due_date<=SYSDATE
GROUP BY aps.due_date , lsm.khr_id
ORDER BY (SYSDATE-due_date) asc;
SELECT SUM(fab.cost)
FROM fa_additions_b faa,fa_books fab,okc_k_lines_b cle,
okc_k_headers_b chr,okc_line_styles_b lse,
okc_k_items cim
WHERE faa.asset_id = fab.asset_id
AND cim.object1_id2 = '#'
AND cim.object1_id1 = faa.asset_id
AND cim.jtot_object1_code = 'OKX_ASSET'
AND cle.id = cim.cle_id
AND lse.lty_code = 'FIXED_ASSET'
AND cle.lse_id = lse.id
AND cle.dnz_chr_id = chr.id
AND chr.id = p_contract_id
GROUP BY chr.id ;
SELECT SUM(NVL(amount_due_remaining, 0))
FROM okl_bpd_leasing_payment_trx_v
WHERE contract_id = p_contract_id;
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
FROM okl_cnsld_ar_strms_b ocas
,ar_payment_schedules aps
,okl_strm_type_v strm
WHERE ocas.khr_id = p_contract_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class ='INV'
--AND aps.due_date < sysdate
and strm.id=ocas.sty_id
and strm.id <> p_sty_id;
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
FROM okl_bpd_tld_ar_lines_v ocas
,ar_payment_schedules aps
,okl_strm_type_v strm
WHERE ocas.khr_id = p_contract_id
AND ocas.customer_trx_id = aps.customer_trx_id
AND aps.class ='INV'
--AND aps.due_date < sysdate
and strm.id=ocas.sty_id
and strm.id <> p_sty_id;
SELECT khr.start_date,khr.end_date,okhr.term_duration
FROM OKL_K_HEADERS okhr ,okc_k_headers_v khr
WHERE okhr.id = khr.id
AND khr.id = p_contract_id;
SELECT NVL(SUM(cs.amount),0)
FROM okl_streams_v asv,okl_strm_type_v bs,okl_strm_elements_v cs
WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
AND bs.name = 'Residual Value'
AND cs.stream_element_date >= SYSDATE
AND asv.khr_id = p_contract_id;
SELECT NVL(SUM(cs.amount),0)
FROM okl_streams_v asv,okl_strm_type_v bs,okl_strm_elements_v cs
WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
AND bs.name = 'Rent'
AND cs.stream_element_date >= SYSDATE
AND asv.khr_id = p_contract_id ;
SELECT NVL(SUM(cs.amount),0)
FROM okl_streams_v asv,okl_strm_type_v bs,okl_strm_elements_v cs
WHERE cs.stm_id = asv.id AND bs.id = asv.sty_id
AND bs.name = 'Unearned Income'
AND cs.stream_element_date >= SYSDATE
AND asv.khr_id = p_contract_id ;
SELECT SUM(nvl(orv1.rule_information6,0))
FROM okc_rules_v orv1, okc_rule_groups_b org1
WHERE org1.dnz_chr_id = p_contract_id
AND org1.id = orv1.rgp_id
AND orv1.rule_information_category = 'SLL'
AND EXISTS
( SELECT 1 FROM okc_k_headers_v okhdr,okc_rules_v orv,OKL_STRMTYP_SOURCE_V stm
WHERE okhdr.id = org1.dnz_chr_id AND org1.rgd_code = 'LAEVEL'
AND org1.id = orv.rgp_id AND orv.rule_information_category ='SLH'
AND jtot_object1_code ='OKL_STRMTYP' AND object1_id1 = stm.id1
AND object1_id2 = stm.id2 AND stm.name ='RENT');
SELECT SUM(nvl(orv1.rule_information6,0))
FROM okc_rules_v orv1, okc_rule_groups_b org1
WHERE org1.dnz_chr_id = p_contract_id
AND org1.id = orv1.rgp_id
AND orv1.rule_information_category = 'SLL'
AND EXISTS
( SELECT 1 FROM okc_k_headers_v okhdr,okc_rules_v orv,OKL_STRMTYP_SOURCE_V stm
WHERE okhdr.id = org1.dnz_chr_id AND org1.rgd_code = 'LAEVEL'
AND org1.id = orv.rgp_id AND orv.rule_information_category ='SLH'
AND jtot_object1_code ='OKL_STRMTYP' AND object1_id1 = stm.id1
AND object1_id2 = stm.id2 AND stm.name ='SECURITY DEPOSIT');
SELECT SUM(nvl(orv1.rule_information6,0))
FROM okc_rules_v orv1, okc_rule_groups_b org1
WHERE org1.dnz_chr_id = p_contract_id
AND org1.id = orv1.rgp_id
AND orv1.rule_information_category = 'SLL'
AND EXISTS
( SELECT 1 FROM okc_k_headers_v okhdr,okc_rules_v orv,OKL_STRMTYP_SOURCE_V stm
WHERE okhdr.id = org1.dnz_chr_id AND org1.rgd_code = 'LAEVEL'
AND org1.id = orv.rgp_id AND orv.rule_information_category ='SLH'
AND jtot_object1_code ='OKL_STRMTYP' AND object1_id1 = stm.id1
AND object1_id2 = stm.id2 AND stm.name ='SECURITY DEPOSIT');
SELECT 'N'
FROM OKL_INS_POLICIES_B IPYB
WHERE IPYB.KHR_ID = p_contract_id
AND IPYB.IPY_TYPE <> 'OPTIONAL_POLICY'
AND IPYB.QUOTE_YN = 'N'
AND IPYB.ISS_CODE = 'ACTIVE'
AND SYSDATE BETWEEN IPYB.DATE_FROM AND IPYB.DATE_TO;
SELECT SUM(amount)
FROM iex_cure_payment_lines
WHERE chr_id = p_contract_id
AND status = 'CURES_IN_POSSESSION';
SELECT khr.deal_type
FROM okl_k_headers_v khr ,fnd_lookups fnd
WHERE fnd.lookup_type = 'OKL_BOOK_CLASS'
AND fnd.lookup_code = khr.deal_type
AND id = p_contract_id;