[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
SELECT LTE.name late_policy
, KHR.currency_code
, ARL.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, KHR.contract_number
, ARL.CONTRACT_ID CONTRACT_ID
, sum(NVL(ARL.AMOUNT_DUE_ORIGINAL,0)) AMOUNT_APPLIED
, max(greatest(TRUNC((FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,AR_PAY.DUE_DATE)))),TRUNC(AR_PAY.DUE_DATE))) DUE_DATE
, max(TRUNC(AR_REC.APPLY_DATE)) APPLY_DATE
,max('AR-INVOICE') invoice_flag
FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_REC
, AR_PAYMENT_SCHEDULES_ALL AR_PAY
, OKL_BPD_AR_INV_LINES_V ARL
, OKL_K_HEADERS_FULL_V KHR
, OKC_RULE_GROUPS_B RGP
, OKC_RULES_B RUL
, okc_rules_b rul_exm
,okc_rules_b rul_hld
, OKL_LATE_POLICIES_V LTE
WHERE
AR_REC.APPLY_DATE > (AR_PAY.DUE_DATE + nvl(LTE.late_int_grace_period, 0))
AND ARL.RECEIVABLES_INVOICE_ID = AR_PAY.CUSTOMER_TRX_ID
AND AR_PAY.PAYMENT_SCHEDULE_ID = AR_REC.APPLIED_PAYMENT_SCHEDULE_ID
AND AR_PAY.CLASS = 'INV'
AND AR_PAY.status = 'CL'
AND AR_REC.STATUS = 'APP'
AND AR_REC.APPLICATION_TYPE = 'CASH'
AND ARL.CONTRACT_ID = khr.id
AND ARL.late_int_assess_date IS NULL
AND NVL(ARL.late_int_ass_yn, 'N') = 'N'
and khr.id = rgp.dnz_chr_id
and rgp.rgd_code = 'LALIGR'
and khr.id = rul.dnz_chr_id
and rgp.id = rul.rgp_id
and rul.rule_information_category = 'LALCIN'
and rul.rule_information1 = lte.id
and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'INT')
and khr.id = rul_exm.dnz_chr_id
and rgp.id = rul_exm.rgp_id
and rul_exm.rule_information_category = 'LALIEX'
and NVL(rul_exm.rule_information1, 'N') = 'N'
and not exists (select 1 from okl_strm_type_exempt_v sty_exm
where lte.id = sty_exm.lpo_id
and ARL.sty_id = sty_exm.sty_id
and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
and khr.id = rul_hld.dnz_chr_id
and rgp.id = rul_hld.rgp_id
and rul_hld.rule_information_category = 'LAHUDT'
and TRUNC(NVL(FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,sysdate)), sysdate - 1)) < trunc(sysdate)
and ((TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) IS NOT NULL
AND TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) < TRUNC(AR_REC.APPLY_DATE))
OR TRUNC(AR_REC.APPLY_DATE) IS NULL
OR rul_hld.rule_information1 IS NULL)
group by
LTE.name
, KHR.currency_code
, KHR.contract_number
, ARL.CONTRACT_ID
, ARL.RECEIVABLES_INVOICE_ID
,'AR-INVOICE'
union
SELECT LTE.name late_policy
, KHR.currency_code
, CNSLD.RECEIVABLES_INVOICE_ID RECEIVABLES_INVOICE_ID
, KHR.contract_number
, CNSLD.KHR_ID CONTRACT_ID
, sum(NVL(AR_REC.AMOUNT_APPLIED,0)) AMOUNT_APPLIED
, max(greatest(TRUNC((FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,AR_PAY.DUE_DATE)))),TRUNC(AR_PAY.DUE_DATE))) DUE_DATE
, max(TRUNC(AR_REC.APPLY_DATE)) APPLY_DATE
, max('CONS-INVOICE') invoice_flag
FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_REC
, AR_PAYMENT_SCHEDULES_ALL AR_PAY
, OKL_CNSLD_AR_STRMS_B CNSLD
, OKL_K_HEADERS_FULL_V KHR
, OKC_RULE_GROUPS_B RGP
, OKC_RULES_B RUL
, okc_rules_b rul_exm
,okc_rules_b rul_hld
, OKL_LATE_POLICIES_V LTE
WHERE
AR_REC.APPLY_DATE > (AR_PAY.DUE_DATE + nvl(LTE.late_int_grace_period, 0)) AND
CNSLD.RECEIVABLES_INVOICE_ID = AR_PAY.CUSTOMER_TRX_ID
AND AR_PAY.PAYMENT_SCHEDULE_ID = AR_REC.APPLIED_PAYMENT_SCHEDULE_ID
AND AR_PAY.CLASS = 'INV'
AND AR_PAY.status = 'CL'
AND AR_REC.STATUS = 'APP'
AND AR_REC.APPLICATION_TYPE = 'CASH'
and CNSLD.KHR_ID = khr.id
AND CNSLD.late_int_assess_date IS NULL
AND NVL(CNSLD.late_int_ass_yn, 'N') = 'N'
and khr.id = rgp.dnz_chr_id
and rgp.rgd_code = 'LALIGR'
and khr.id = rul.dnz_chr_id
and rgp.id = rul.rgp_id
and rul.rule_information_category = 'LALCIN'
and rul.rule_information1 = lte.id
and (lte.late_policy_type_code = 'LCT' or lte.late_policy_type_code = 'INT')
and khr.id = rul_exm.dnz_chr_id
and khr.authoring_org_id = MO_GLOBAL.get_current_org_id
and rgp.id = rul_exm.rgp_id
and rul_exm.rule_information_category = 'LALIEX'
and NVL(rul_exm.rule_information1, 'N') = 'N'
and not exists (select 1 from okl_strm_type_exempt_v sty_exm
where lte.id = sty_exm.lpo_id
and CNSLD.sty_id = sty_exm.sty_id
and NVL(sty_exm.late_policy_exempt_yn, 'N') = 'Y')
and khr.id = rul_hld.dnz_chr_id
and rgp.id = rul_hld.rgp_id
and rul_hld.rule_information_category = 'LAHUDT'
and TRUNC(NVL(FND_DATE.canonical_to_date(NVL(rul_hld.rule_information1,sysdate)), sysdate - 1)) < trunc(sysdate)
and ((TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) IS NOT NULL
AND TRUNC((FND_DATE.canonical_to_date(rul_hld.rule_information1))) < TRUNC(AR_REC.APPLY_DATE))
OR TRUNC(AR_REC.APPLY_DATE) IS NULL
OR rul_hld.rule_information1 IS NULL)
group by LTE.name
, KHR.currency_code
, KHR.contract_number
, CNSLD.KHR_ID
, CNSLD.RECEIVABLES_INVOICE_ID
,'CONS-INVOICE';
SELECT LTE.LATE_POLICY_TYPE_CODE, LTE.LATE_INT_ALLOWED_YN, LTE.LATE_INT_FIXED_YN
, NVL(LTE.LATE_INT_RATE,0) LATE_INT_RATE
, NVL(LTE.ADDER_RATE,0) ADDER_RATE
, NVL(LTE.LATE_INT_GRACE_PERIOD,0) LATE_INT_GRACE_PERIOD
, NVL(LTE.LATE_INT_MINIMUM_BALANCE,0) LATE_INT_MINIMUM_BALANCE
, NVL(LTE.MINIMUM_LATE_INTEREST,0) MINIMUM_LATE_INTEREST
, NVL(LTE.MAXIMUM_LATE_INTEREST,9999999999) MAXIMUM_LATE_INTEREST
, NVL(IDX.value,0) INDEX_RATE
, NVL(LTE.DAYS_IN_YEAR, 'ACTUAL') DAYS_IN_YEAR
FROM OKL_LATE_POLICIES_V LTE
, OKL_INDEX_VALUES IDX
WHERE LTE.NAME = cp_name
AND LTE.idx_id = IDX.idx_id(+)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(IDX.DATETIME_VALID, SYSDATE)) AND TRUNC(NVL(IDX.DATETIME_INVALID, SYSDATE));
-- Consolidated stream update Cursor
------------------------------------------------------------
-- vdamerla: bug:6342067 : Get the late int data
-- cursor for consolidated invoices
CURSOR l_cons_lsm_cur(l_RECEIVABLES_INVOICE_ID IN NUMBER, l_khr_id in number) IS
SELECT lsm.id
, lsm.LATE_CHARGE_ASS_YN
, lsm.LATE_CHARGE_ASSESS_DATE
FROM OKL_CNSLD_AR_STRMS_B lsm,
OKL_CNSLD_AR_LINES_B lln,
OKL_CNSLD_AR_HDRS_B cnr,
okl_bpd_leasing_payment_trx_v lpt
WHERE
lpt.RECEIVABLES_INVOICE_ID=l_RECEIVABLES_INVOICE_ID
and cnr.id = lpt.consolidated_invoice_id
and lln.cnr_id = cnr.id
and lsm.lln_id = lln.id
and lsm.KHR_ID = l_khr_id
FOR UPDATE OF lsm.LATE_CHARGE_ASS_YN, lsm.LATE_CHARGE_ASSESS_DATE;
SELECT ID
, LATE_CHARGE_ASS_YN
, LATE_CHARGE_ASSESS_DATE
FROM OKL_BPD_AR_INV_LINES_V
WHERE RECEIVABLES_INVOICE_ID = l_id
AND CONTRACT_ID=l_contract_id
FOR UPDATE OF LATE_CHARGE_ASS_YN, LATE_CHARGE_ASSESS_DATE;
SELECT okl_sif_seq.nextval
FROM dual;
SELECT id FROM okl_strm_type_b where stream_type_purpose = cp_purpose;
SELECT stm.id
FROM okl_streams_v stm
WHERE stm.khr_id = cp_khr_id
AND stm.sty_id = cp_sty_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y';
SELECT stm.id
FROM okl_streams_v stm
WHERE stm.khr_id = cp_khr_id
AND nvl(stm.kle_id, -99) = nvl(cp_kle_id, -99)
AND stm.sty_id = cp_sty_id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y';
SELECT nvl(max(se_line_number), 0) se_line_number
FROM okl_strm_elements_v
WHERE stm_id = cp_stm_id;
select lsm.id cnsld_strm_id,
pol.khr_id,lsm.kle_id
from okl_cnsld_ar_strms_b lsm
, okl_cnsld_ar_hdrs_b cnr
, okl_cnsld_ar_lines_b lln
, okl_pool_contents_v pk
, okl_pools pol
where lsm.RECEIVABLES_INVOICE_ID = l_cons_rec_inv_id
and lln.cnr_id = cnr.id
and lsm.lln_id = lln.id
and lsm.khr_id = l_khr_id
and lsm.khr_id = pk.khr_id
and nvl(lsm.kle_id, -99) = nvl(pk.kle_id, -99)
and lsm.sty_id = pk.sty_id
and pk.pol_id = pol.id
and pol.status_code='ACTIVE' -- Added vdamerla for bug 6064374
and pk.status_code = 'ACTIVE' --Added by bkatraga for bug 6983321
and trunc(cnr.date_consolidated) between trunc(pk.streams_from_date) and trunc(pk.streams_to_date)
AND pk.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
SELECT arl.id cnsld_strm_id
,pol.khr_id, pk.kle_id kle_id
FROM okl_bpd_ar_inv_lines_v arl
,okl_bpd_ar_invoices_v arv
,okl_pool_contents_v pk
,okl_pools pol
WHERE arl.RECEIVABLES_INVOICE_ID = cp_con_rec_inv_id
AND arl.contract_id = cp_contract_id
AND arl.contract_id = pk.khr_id
AND nvl(arl.contract_line_id, -99) = nvl(pk.kle_id, -99)
AND arl.sty_id = pk.sty_id
AND pk.pol_id = pol.id
and pol.status_code='ACTIVE' -- Added vdamerla for bug 6064374
and pk.status_code = 'ACTIVE' --Added by bkatraga for bug 6983321
AND arv.invoice_id = arl.invoice_id
AND trunc(arv.date_consolidated) between trunc(pk.streams_from_date) and trunc(pk.streams_to_date)
AND pk.status_code = Okl_Pool_Pvt.G_POC_STS_ACTIVE; --Added by VARANGAN -Pool Contents Impact(Bug#6658065)
SELECT sel.source_id
FROM okl_bpd_ar_inv_lines_v arl
,okl_strm_elements_v sel
WHERE arl.id = cp_stream_id
AND arl.sel_id = sel.id;
SELECT FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,NVL(DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),null)
INTO l_last_updated_by
,l_last_update_login
,l_request_id
FROM dual;
Okl_Sel_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_selv_rec,
lx_selv_rec);
Okl_Sel_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_selv_rec,
lx_selv_rec);
Okl_Sel_Pvt.insert_row(
p_api_version,
p_init_msg_list,
x_return_status,
x_msg_count,
x_msg_data,
l_selv_rec,
lx_selv_rec);
Update OKL_TXD_AR_LN_DTLS_B
set LATE_INT_ASS_YN = 'Y'
, LATE_INT_ASSESS_DATE = SYSDATE
,last_updated_by = l_last_updated_by
,last_update_date = sysdate
,last_update_login = l_last_update_login
,request_id = l_request_id
WHERE CURRENT OF l_AR_lsm_cur;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Update Unsuccessful.');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Updated.');
Update okl_cnsld_ar_strms_b
set LATE_INT_ASS_YN = 'Y'
, LATE_INT_ASSESS_DATE = SYSDATE
,last_updated_by = l_last_updated_by
,last_update_date = sysdate
,last_update_login = l_last_update_login
,request_id = l_request_id
WHERE CURRENT OF l_cons_lsm_cur;
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Update Unsuccessful.');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' -- Internal Consolidation Record Updated.');