[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Update_cure_amounts(
p_contract_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2 )
IS
CURSOR c_get_cure_amts (p_contract_id IN NUMBER)
IS
SELECT cure_amount_id
,object_version_number
FROM okl_cure_amounts
WHERE chr_id = p_contract_id
AND STATUS = 'CURESINPROGRESS';
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : START ');
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : p_contract_id : '||p_contract_id);
SAVEPOINT UPDATE_CURE_AMOUNTS;
write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Calling Cure Amount Update Api');
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : l_camv_tbl(next_row).cure_amount_id : '||l_camv_tbl(next_row).cure_amount_id);
SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
INTO l_camv_tbl(next_row).request_id,
l_camv_tbl(next_row).program_application_id,
l_camv_tbl(next_row).program_id,
l_camv_tbl(next_row).program_update_date
FROM DUAL;
'no of records to be updated in Cure amounts '||l_camv_tbl.COUNT);
OKL_cure_amounts_pub.update_cure_amounts
( p_api_version => 1
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_camv_tbl => l_camv_tbl
,x_camv_tbl => x_camv_tbl
);
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : OKL_cure_amounts_pub.update_cure_amounts : '||l_return_status);
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : Update_cure_amounts : END ');
ROLLBACK TO UPDATE_CURE_AMOUNTS;
ROLLBACK TO UPDATE_CURE_AMOUNTS;
ROLLBACK TO UPDATE_CURE_AMOUNTS;
Fnd_Msg_Pub.ADD_EXC_MSG('OKL_VENDOR_REFUND_PVT','UPDATE_CURE_AMOUNTS');
End update_cure_amounts;
select nvl(sum(negotiated_amount),0), nvl(sum(received_amount),0)
from okl_cure_amounts
where chr_id =p_contract_id
and nvl(negotiated_amount,0) > 0
and nvl(received_amount,0) > 0
and status ='CURESINPROGRESS';
write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Before Inserting Cure Amounts');
Update_cure_amounts( p_contract_id =>p_contract_id,
x_return_status =>l_return_status,
x_msg_count =>l_msg_count,
x_msg_data =>l_msg_data );
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : Update_cure_amounts : '||l_return_status);
END IF; -- update_cure_amounts
SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL, Fnd_Global.PROG_APPL_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL, Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
INTO l_crsv_rec.request_id,
l_crsv_rec.program_application_id,
l_crsv_rec.program_id,
l_crsv_rec.program_update_date
FROM DUAL;
write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Before Inserting Cure Refunds');
OKL_cure_rfnd_stage_pub.insert_cure_refunds
( p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crsv_rec => l_crsv_rec
,x_crsv_rec => x_crsv_rec);
okl_debug_pub.logmessage('OKL_VENDOR_REFUND_PVT : CALC_CURE_REFUND : OKL_cure_rfnd_stage_pub.insert_cure_refunds : '||l_return_status);
write_log(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM, ' Error Inserting Cure Refunds');
'Done inserting cure refunds, cure received amount is '||l_received_amount || 'and negotiated amount is'||l_negotiated_amount);
SELECT count(*)
FROM okl_cnsld_ar_strms_b ocas
,ar_payment_schedules_all aps
WHERE ocas.khr_id = p_contract_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class ='INV'
AND (aps.actual_date_closed + p_grace_days) < sysdate
AND NVL(aps.amount_due_remaining, 0) = 0
AND not exists
(select xls1.lsm_id from
okl_xtl_sell_invs_v xls1
,okl_txl_ar_inv_lns_v til1
,okl_trx_ar_invoices_v tai1 where
tai1.id = til1.tai_id and
til1.id = xls1.til_id and
tai1.cpy_id IS NOT NULL and
xls1.lsm_id =ocas.id);*/
SELECT count(*)
FROM okl_bpd_tld_ar_lines_v ocas
,ar_payment_schedules_all aps
WHERE ocas.khr_id = p_contract_id
AND ocas.customer_trx_id = aps.customer_trx_id
AND aps.class = 'INV'
AND (aps.actual_date_closed + p_grace_days) < sysdate
AND NVL(aps.amount_due_remaining, 0) = 0
AND NOT EXISTS
--(select xls1.lsm_id from
(SELECT tld.id
FROM --okl_xtl_sell_invs_v xls1
okl_txd_ar_ln_dtls_b tld
,okl_txl_ar_inv_lns_v til1
,okl_trx_ar_invoices_v tai1
WHERE tai1.id = til1.tai_id
AND
--til1.id = xls1.til_id and
til1.id = tld.til_id_details
AND tai1.cpy_id IS NOT NULL
AND
--xls1.lsm_id =ocas.id);
SELECT prog.id program_id
,prog.contract_number program_number
,lease.id contract_id
,lease.contract_number contract_number
,rgp.rgd_code
,pty.object1_id1 vendor_id
FROM okc_k_headers_b prog,
okc_k_headers_b lease,
okl_k_headers khr,
okc_rule_groups_b rgp,
okc_k_party_roles_v pty,
OKX_VENDORS_V vnd
WHERE khr.id = lease.id
AND khr.khr_id = prog.id
AND prog.scs_code = 'PROGRAM'
AND lease.scs_code in ('LEASE','LOAN')
AND rgp.rgd_code = 'COCURP'
AND rgp.dnz_chr_id = prog.id
AND prog.id = pty.chr_id
AND pty.rle_code = 'OKL_VENDOR'
AND pty.object1_id1 = to_char(vnd.id1)
AND pty.object1_id2 = vnd.id2
AND lease.contract_number =nvl(p_contract_number,lease.contract_number)
and exists (select 1 from okl_cure_amounts cam
where cam.chr_id = lease.id
and cam.status = 'CURESINPROGRESS'
and nvl(negotiated_amount,0) > 0);
and exists (select 1 from okl_cure_reports cr
where cr.vendor_id = vnd.id1);
select count( ps.payment_schedule_id)
from ar_payment_schedules_all ps
,okl_cnsld_ar_strms_b stream
,okl_xtl_sell_invs_v xls
,okl_txl_ar_inv_lns_v til
,okl_trx_ar_invoices_v tai
where ps.class ='INV'
and ps.amount_due_remaining = 0
and stream.receivables_invoice_id = ps.customer_trx_id
and stream.id = xls.lsm_id
and tai.id = til.tai_id
and til.id = xls.til_id
and tai.cpy_id IS NOT NULL
and tai.khr_id = p_contract_id;*/
select count( ps.payment_schedule_id)
from ar_payment_schedules_all ps
,okl_bpd_tld_ar_lines_v stream
--,okl_xtl_sell_invs_v xls
,okl_txd_ar_ln_dtls_b tld
,okl_txl_ar_inv_lns_v til
,okl_trx_ar_invoices_v tai
where ps.class ='INV'
and ps.amount_due_remaining = 0
and stream.customer_trx_id = ps.customer_trx_id
--and stream.id = xls.lsm_id
and stream.tld_id = tld.id
and tai.id = til.tai_id
--and til.id = xls.til_id
and til.id = tld.til_id_details
and tai.cpy_id IS NOT NULL
and tai.khr_id = p_contract_id;
SELECT min(aps.due_date)
FROM okl_cnsld_ar_strms_b ocas
,ar_payment_schedules_all aps
,okc_k_headers_b chr
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
AND ocas.khr_id=chr.id
AND not exists
( select xls1.lsm_id from
okl_xtl_sell_invs_v xls1
,okl_txl_ar_inv_lns_v til1
,okl_trx_ar_invoices_v tai1 where
tai1.id = til1.tai_id
and til1.id = xls1.til_id and
tai1.cpy_id IS NOT NULL and
xls1.lsm_id =ocas.id);*/
SELECT min(aps.due_date)
FROM okl_bpd_tld_ar_lines_v ocas
,ar_payment_schedules_all aps
,okc_k_headers_b chr
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
AND ocas.khr_id=chr.id
AND not exists
--( select xls1.lsm_id from
( select tld.id from
--okl_xtl_sell_invs_v xls1
okl_txd_ar_ln_dtls_b tld
,okl_txl_ar_inv_lns_v til1
,okl_trx_ar_invoices_v tai1 where
tai1.id = til1.tai_id
--and til1.id = xls1.til_id and
and til1.id = tld.til_id_details and
tai1.cpy_id IS NOT NULL and
tld.id =ocas.tld_id);