[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** the operation (insert, update ,delete)
*/
PROCEDURE AddfailMsg
( p_object IN VARCHAR2,
p_operation IN VARCHAR2 ) IS
BEGIN
fnd_message.set_name('OKL', 'OKL_FAILED_OPERATION');
SELECT application_id
FROM fnd_application_vl
WHERE application_short_name = x_short_name;
SELECT msg.message_number
FROM fnd_new_messages msg, fnd_languages_vl lng
WHERE msg.message_name = x_msg
and msg.application_id = x_id
and lng.LANGUAGE_CODE = msg.language_code
and lng.language_id = x_lang_id;
SELECT chr.authoring_org_id
FROM okc_k_headers_b chr
WHERE id = p_khr_id;
SELECT hru.set_of_books_id
FROM HR_OPERATING_UNITS HRU
WHERE ORGANIZATION_ID = p_org_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = 'Disbursement'
AND LANGUAGE = USERENV('LANG');
SELECT id
FROM okl_strm_type_tl
WHERE name = 'CURE'
AND LANGUAGE = USERENV('LANG');
select refund_number
from okl_cure_refunds
where refund_number =p_refund_number;
select a.application_id
from FND_APPLICATION a
where APPLICATION_SHORT_NAME = 'OKL';
OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES(
p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tapv_rec => lp_tapv_rec,
x_tapv_rec => lx_tapv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES : '||x_return_status);
OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tplv_rec => lp_tplv_rec,
x_tplv_rec => lx_tplv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS : '||x_return_status);
OKL_cure_refunds_pub.insert_cure_refunds(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crfv_rec => lp_crfv_rec
,x_crfv_rec => lx_crfv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : OKL_cure_refunds_pub.insert_cure_refunds : '||l_return_status);
select khr_id from okl_k_headers
where 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_all aps
WHERE ocas.khr_id = p_contract_id
AND ocas.receivables_invoice_id = aps.customer_trx_id
AND aps.class IN ('INV','CM')
AND (aps.due_date + p_grace_days) < sysdate
AND NVL(aps.amount_due_remaining, 0) > 0;*/
SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
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 IN ('INV','CM')
AND (aps.due_date + p_grace_days) < sysdate
AND NVL(aps.amount_due_remaining, 0) > 0;
SELECT st.khr_id contract_id,
cn.contract_number
FROM okl_xtl_sell_invs_v xls,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b cn,
ar_payment_schedules_all ps,
ar_receivable_applications_all arapp,
okl_cnsld_ar_strms_b st
WHERE st.id = xls.lsm_id
AND st.receivables_invoice_id = ps.customer_trx_id
AND ps.class IN('INV', 'CM')
AND arapp.applied_payment_schedule_id = ps.payment_schedule_id
AND cn.id = st.khr_id(+)
AND tai.id = til.tai_id
AND til.id = xls.til_id
AND tai.cpy_id IS NOT NULL
and st.khr_id = p_pay_cure_refunds_rec.chr_id;
SELECT st.khr_id contract_id,
cn.contract_number
FROM okl_xtl_sell_invs_v xls,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_all_b cn,
ar_payment_schedules_all ps,
ar_receivable_applications_all arapp,
okl_cnsld_ar_strms_b st,
okc_k_party_roles_b pty,
okc_rules_b rul,
okc_k_headers_b CHR,
po_vendors pvn,
po_vendor_sites_all pvs
WHERE st.id = xls.lsm_id
AND st.receivables_invoice_id = ps.customer_trx_id
AND ps.class IN('INV', 'CM')
AND arapp.applied_payment_schedule_id = ps.payment_schedule_id
AND cn.id = st.khr_id(+)
AND tai.id = til.tai_id
AND til.id = xls.til_id
AND tai.cpy_id IS NOT NULL
AND rul.dnz_chr_id = CHR.id
AND rul.rule_information_category = 'COVNAG'
AND CHR.id = pty.chr_id
AND rle_code = 'OKL_VENDOR'
AND pty.object1_id1 = pvn.vendor_id
AND pvn.vendor_id = pvs.vendor_id
AND pvs.vendor_site_id = rul.rule_information1
AND CHR.id = cn.id
AND CHR.scs_code = 'PROGRAM'
AND pvs.vendor_site_id = p_pay_cure_refunds_rec.vendor_site_id
AND cn.currency_code = p_pay_cure_refunds_rec.currency;
SELECT st.khr_id contract_id,
cn.contract_number
FROM okl_xtl_sell_invs_v xls,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_b cn,
ar_payment_schedules_all ps,
ar_receivable_applications_all arapp,
okl_cnsld_ar_strms_b st,
okc_k_party_roles_b pty,
okc_rules_b rul,
okc_k_headers_all_b CHR,
po_vendors pvn
WHERE st.id = xls.lsm_id
AND st.receivables_invoice_id = ps.customer_trx_id
AND ps.class IN('INV', 'CM')
AND arapp.applied_payment_schedule_id = ps.payment_schedule_id
AND cn.id = st.khr_id(+)
AND tai.id = til.tai_id
AND til.id = xls.til_id
AND tai.cpy_id IS NOT NULL
AND rul.dnz_chr_id = CHR.id
AND rul.rule_information_category = 'COVNAG'
AND CHR.id = pty.chr_id
AND rle_code = 'OKL_VENDOR'
AND pty.object1_id1 = pvn.vendor_id
AND CHR.id = cn.id
AND CHR.scs_code = 'PROGRAM'
AND pvn.vendor_id = p_pay_cure_refunds_rec.vendor_id
AND cn.currency_code = p_pay_cure_refunds_rec.currency;
select contract_id,refund_amount_due
from okl_cure_refunds_dtls_uv
where vendor_site_id =p_pay_cure_refunds_rec.vendor_site_id and
contract_currency_code =p_pay_cure_refunds_rec.currency;
select contract_id,refund_amount_due
from okl_cure_refunds_dtls_uv
where vendor_id =p_pay_cure_refunds_rec.vendor_id and
contract_currency_code =p_pay_cure_refunds_rec.currency;
select refund_header_number
from okl_cure_refund_headers_b
where refund_header_number =p_refund_header_number;
OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr : '||l_return_status);
PROCEDURE update_refund_hdr
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
,p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
cursor c_get_tap_ids (p_cure_refund_header_id IN NUMBER ) is
select a.tap_id,
a.cure_refund_id,
a.object_version_number,
b.invoice_number
from okl_cure_refunds a, okl_trx_ap_invoices_b b
where cure_refund_header_id =p_cure_refund_header_id
and a.tap_id =b.id;
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_REFUND_HDR';
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : START ');
SAVEPOINT UPDATE_REFUND_HDR;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'no of records to be updated in TAP'||
lp_tapv_tbl.COUNT);
okl_trx_ap_invoices_pub.update_trx_ap_invoices(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_tapv_tbl => lp_tapv_tbl
,x_tapv_tbl => lx_tapv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : okl_trx_ap_invoices_pub.update_trx_ap_invoices : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tap records');
p_operation => 'UPDATE' );
OKL_cure_refunds_pub.update_cure_refunds(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crfv_tbl => lp_crfv_tbl
,x_crfv_tbl => lx_crfv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : OKL_cure_refunds_pub.update_cure_refunds : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated CRF records');
p_operation => 'UPDATE' );
OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully updated Cure refund '||
'header table');
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : END ');
ROLLBACK TO UPDATE_REFUND_HDR;
ROLLBACK TO UPDATE_REFUND_HDR;
ROLLBACK TO UPDATE_REFUND_HDR;
Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','UPDATE_REFUND_HDR');
END update_refund_hdr;
PROCEDURE delete_refund_hdr
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
,p_refund_header_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_tap_ids (p_cure_refund_header_id IN NUMBER ) is
select crf.tap_id,
crf.cure_refund_id,
crf.object_version_number,
til.id til_id
from okl_cure_refunds crf,
okl_txl_ap_inv_lns_b til
where cure_refund_header_id =p_cure_refund_header_id
and til.tap_id =crf.tap_id;
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
l_api_name CONSTANT VARCHAR2(50) := 'DELETE_REFUND_HDR';
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : START ');
SAVEPOINT DELETE_REFUND_HDR;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'no of records to be updated in TAP'||
lp_tapv_tbl.COUNT);
okl_trx_ap_invoices_pub.delete_trx_ap_invoices(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_tapv_tbl => lp_tapv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : okl_trx_ap_invoices_pub.delete_trx_ap_invoices : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully deleted tap records');
p_operation => 'DELETE' );
okl_txl_ap_inv_lns_pub.delete_txl_ap_inv_lns (
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_tplv_tbl => lp_tplv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : okl_txl_ap_inv_lns_pub.delete_txl_ap_inv_lns : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully deleted tap records');
p_operation => 'DELETE' );
OKL_cure_refunds_pub.delete_cure_refunds(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crfv_tbl => lp_crfv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : OKL_cure_refunds_pub.delete_cure_refunds : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully deleted CRF records');
p_operation => 'DELETE' );
OKL_cure_rfnd_hdr_pub.delete_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : OKL_cure_rfnd_hdr_pub.delete_cure_rfnd_hdr : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully deleted Cure refund '||
'header table');
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : END ');
ROLLBACK TO DELETE_REFUND_HDR;
ROLLBACK TO DELETE_REFUND_HDR;
ROLLBACK TO DELETE_REFUND_HDR;
Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','DELETE_REFUND_HDR');
END delete_refund_hdr;
select currency_code from OKC_K_HEADERS_b
where id =l_khr_id;
SELECT currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
FROM okl_k_headers
WHERE id = l_khr_id;
select tai.try_id,
til.sty_id,
til.id,
tai.khr_id,
tai.date_invoiced,
tai.amount,
tai.currency_code
from
okl_trx_ar_invoices_b tai,
okl_txl_ar_inv_lns_b til,
okl_cure_refunds crf
where tai.id =til.tai_id
and tai.id =crf.tai_id
and crf.cure_refund_header_id =p_refund_header_id;*/
select tai.id tai_id,
tai.try_id try_id,
txd.sty_id sty_id,
txd.id txd_id,
tai.khr_id khr_id,
tai.date_invoiced date_invoiced,
tai.amount amount,
tai.currency_code currency_code
from okl_trx_ar_invoices_b tai,
okl_txl_ar_inv_lns_b til,
okl_txd_ar_ln_dtls_b txd,
okl_cure_refunds crf
where crf.cure_refund_header_id = p_refund_header_id
and tai.id = crf.tai_id
and tai.id = til.tai_id
and til.id = txd.til_id_details ;
SELECT khr.pdt_id,
chr.scs_code --Bug# 4622198
FROM okl_k_headers khr,
okc_k_headers_b chr --Bug# 4622198
WHERE chr.id = khr.id --Bug# 4622198
and khr.id = p_chr_id;
select crf.tap_id,tap.invoice_number,
crs.object_version_number
,crs.cure_refund_Stage_id
,crf.tai_id
from okl_cure_refunds crf,okl_trx_ap_invoices_b tap
,okl_cure_refund_stage crs
where crf.cure_refund_header_id =p_cure_refund_header_id
and crf.tap_id =tap.id
and crs.cure_refund_stage_id =crf.cure_refund_stage_id;
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
select tap.id tap_id,
tap.try_id try_id,
til.sty_id sty_id,
til.id id,
tap.date_invoiced date_invoiced,
tap.amount amount,
tap.currency_code currency_code,
til.khr_id khr_id
from okl_trx_ap_invoices_b tap,
okl_txl_ap_inv_lns_b til,
okl_cure_refunds crf
where crf.cure_refund_header_id = p_cure_refund_header_id
and tap.id = til.tap_id
and crf.tap_id = tap.id;
SELECT khr.pdt_id
FROM okl_k_headers khr
WHERE khr.id = p_chr_id;
SELECT currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
FROM okl_k_headers
WHERE id = l_khr_id;
select currency_code from OKC_K_HEADERS_b
where id =l_khr_id;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'no of records to be updated in TAP'||
lp_tapv_tbl.COUNT);
okl_trx_ap_invoices_pub.update_trx_ap_invoices(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_tapv_tbl => lp_tapv_tbl
,x_tapv_tbl => lx_tapv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : okl_trx_ap_invoices_pub.update_trx_ap_invoices : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tap records');
p_operation => 'UPDATE' );
okl_trx_ar_invoices_pub.update_trx_ar_invoices(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_taiv_tbl => lp_taiv_tbl
,x_taiv_tbl => lx_taiv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : okl_trx_ar_invoices_pub.update_trx_ar_invoices : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tai records');
p_operation => 'UPDATE' );
OKL_cure_rfnd_stage_pub.update_cure_refunds(
p_api_version => 1.0
,p_init_msg_list =>'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crsv_tbl => lp_crsv_tbl
,x_crsv_tbl => xp_crsv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : OKL_cure_rfnd_stage_pub.update_cure_refunds : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund stage' );
p_operation => 'UPDATE' );
OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully updated Cure refund '||
'header table');
select pos.vendor_site_id,
pos.vendor_site_code,
pos.email_address,
crh.cure_refund_header_id
from po_vendor_sites pos,
okl_cure_refund_headers_b crh,
okl_cure_refunds crl
where crh.vendor_site_id =pos.vendor_site_id
and crh.cure_refund_header_id=p_refund_header_id
and rownum <2;
select count(1)
into l_role_exists
from WF_LOCAL_ROLES
where name = l_role_name;
SELECT okl_wf_item_s.nextval
FROM dual;
is select crh.refund_header_number
,crh.disbursement_amount
,pov.vendor_name
from okl_cure_refund_headers_b crh,
po_vendors pov,
po_vendor_sites_All povs
where crh.vendor_site_id =povs.vendor_site_id
and pov.vendor_id =povs.vendor_id
and crh.cure_refund_header_id =p_refund_header_id;
select wfr.name
from fnd_user fuser,wf_roles wfr
where orig_system = 'PER'
and wfr.orig_system_id =fuser.employee_id
and fuser.user_id =p_user_id;
select count(*) into l_offset_count
from okl_cure_refunds
where offset_contract is not null
and cure_refund_header_id =p_refund_header_id;
l_parameter_list.DELETE;
SELECT receivables_invoice_id
FROM okl_txd_ar_ln_dtls_v
WHERE id = p_tld_id;
SELECT receivables_invoice_id
FROM okl_txl_ar_inv_lns_v
WHERE id = p_til_id;
SELECT c.minimum_accountable_unit,
c.PRECISION
FROM fnd_currencies c
WHERE c.currency_code = cp_currency_code;
SELECT c.minimum_accountable_unit,
c.PRECISION
FROM fnd_currencies c,
okl_trx_ar_invoices_b b
WHERE c.currency_code = b.currency_code
AND b.khr_id = p_khr_id;
SELECT NVL(inv_organization_id, -99)
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT id FROM okl_trx_types_tl
WHERE name = 'Credit Memo' AND LANGUAGE = USERENV('LANG');
SELECT sty.id
FROM okl_strm_type_tl styt, okl_strm_type_b sty
WHERE styt.name = 'CURE' AND styt.language = 'US'
AND sty.id = styt.id AND sty.start_date <= TRUNC(SYSDATE)
AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);
SELECT c.receipt_method_id
FROM ra_cust_receipt_methods c
WHERE c.cust_receipt_method_id = cp_cust_rct_mthd;
SELECT a.cust_account_id cust_account_id,
a.cust_acct_site_id cust_acct_site_id,
a.payment_term_id payment_term_id
FROM okx_cust_site_uses_v a,
okx_customer_accounts_v c
WHERE a.id1 = cp_site_use_id
AND a.site_use_code = cp_site_use_code
AND c.id1 = a.cust_account_id;
SELECT c.standard_terms standard_terms
FROM hz_customer_profiles c
WHERE c.cust_account_id = cp_cust_id
AND c.site_use_id = cp_site_use_id
UNION
SELECT c1.standard_terms standard_terms
FROM hz_customer_profiles c1
WHERE c1.cust_account_id = cp_cust_id
AND c1.site_use_id IS NULL
AND NOT EXISTS (
SELECT '1'
FROM hz_customer_profiles c2
WHERE c2.cust_account_id = cp_cust_id
AND c2.site_use_id = cp_site_use_id);
select khr_id from okl_k_headers where id= p_contract_id;
select object_version_number from okl_cure_refunds
where cure_refund_id =p_cure_refund_id;
select BILL_TO_SITE_USE_ID
from okc_k_party_roles_b
where dnz_chr_id = p_program_id
and RLE_CODE ='OKL_VENDOR';
p_operation => 'INSERT' );
okl_trx_ar_invoices_pub.INSERT_trx_ar_invoices
(p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_taiv_rec => lp_taiv_rec,
x_taiv_rec => xp_taiv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_trx_ar_invoices_pub.INSERT_trx_ar_invoices : '||l_return_status);
p_operation => 'INSERT' );
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
(p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tilv_rec => lp_tilv_rec,
x_tilv_rec => xp_tilv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns : '||l_return_status);
p_operation => 'INSERT' );
okl_tld_pvt.insert_row(
p_api_version => 1.0,
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_tldv_rec => lp_tldv_rec,
x_tldv_rec => xp_tldv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_tld_pvt.insert_row : '||l_return_status);
p_operation => 'UPDATE' );
OKL_cure_refunds_pub.update_cure_refunds(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crfv_rec => lp_crfv_rec
,x_crfv_rec => lx_crfv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : OKL_cure_refunds_pub.update_cure_refunds : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated CRF records');
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
select crf.chr_id,
crf.disbursement_amount,
crf.offset_contract,
crf.offset_amount,
crf.object_version_number,
crf.cure_refund_id,
crh.vendor_site_id,
crh.payment_term_id,
crh.payment_method,
crh.currency_code,
crh.refund_due_date
from okl_cure_refund_headers_b crh,
okl_cure_refunds crf
where crh.cure_refund_header_id =p_cure_refund_header_id
and crh.cure_refund_header_id =crf.cure_refund_header_id;
SELECT khr.pdt_id
FROM okl_k_headers khr
WHERE khr.id = p_chr_id;
SELECT chr.authoring_org_id
FROM okc_k_headers_b chr
WHERE id = p_khr_id;
SELECT hru.set_of_books_id
FROM HR_OPERATING_UNITS HRU
WHERE ORGANIZATION_ID = p_org_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = 'Disbursement'
AND LANGUAGE = USERENV('LANG');
SELECT id
FROM okl_strm_type_tl
WHERE name = 'CURE'
AND LANGUAGE = USERENV('LANG');
select a.application_id
from FND_APPLICATION a
where APPLICATION_SHORT_NAME = 'OKL';
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'update cure refunds ');
OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES(
p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tapv_rec => lp_tapv_rec,
x_tapv_rec => lx_tapv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES : '||x_return_status);
OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
p_api_version => 1.0,
p_init_msg_list => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tplv_rec => lp_tplv_rec,
x_tplv_rec => lx_tplv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS : '||x_return_status);
p_operation => 'UPDATE' );
OKL_cure_refunds_pub.update_cure_refunds(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crfv_rec => lp_crfv_rec
,x_crfv_rec => lx_crfv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : OKL_cure_refunds_pub.update_cure_refunds : '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated CRF records');
select object_version_number,
refund_header_number,
refund_status
from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
select count(cure_refund_id) from okl_cure_refunds where
cure_refund_header_id =p_refund_header_id;
** 2) Update Cure Refund hdr - WAITING FOR APPROVAL '
**/
OPEN c_check_dtls (p_refund_header_id);
p_operation => 'UPDATE' );
OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: approve_cure_refunds : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
is select crf.cure_refund_stage_id,
crf.tai_id, crf.tap_id,
crs.object_version_number
from okl_cure_refunds crf,
okl_cure_refund_stage crs
where crf.cure_refund_header_id =p_refund_header_id
and crs.cure_refund_stage_id=crf.cure_refund_stage_id;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated TAP ' );
1) update tai -status to 'REJECTED'--if offset contract is populated
2) update tap -status to 'REJECTED'
4)update cure_refund_stage -status back to 'ENTERED'
5) update cure_refund_headers -status to 'REJECTED'
**/
IF PG_DEBUG < 11 THEN
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' cure refund header id ' ||
p_refund_header_id);
p_operation => 'UPDATE' );
okl_trx_ap_invoices_pub.update_trx_ap_invoices(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_tapv_tbl => lp_tapv_tbl
,x_tapv_tbl => lx_tapv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : okl_trx_ap_invoices_pub.update_trx_ap_invoices : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tap records');
p_operation => 'UPDATE' );
okl_trx_ar_invoices_pub.update_trx_ar_invoices(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_taiv_tbl => lp_taiv_tbl
,x_taiv_tbl => lx_taiv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : okl_trx_ar_invoices_pub.update_trx_ar_invoices : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tai records');
p_operation => 'UPDATE' );
OKL_cure_rfnd_stage_pub.update_cure_refunds(
p_api_version => 1.0
,p_init_msg_list =>'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_crsv_tbl => lp_crsv_tbl
,x_crsv_tbl => xp_crsv_tbl);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : OKL_cure_rfnd_stage_pub.update_cure_refunds : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
p_operation => 'UPDATE' );
OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
called from the workflow to update cure refunds based on
the approval
**/
PROCEDURE set_approval_status (itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2) IS
l_api_version NUMBER := 1;
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_approval_status : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error in update of cure refund to PENDINGI' ||l_message);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
called from the workflow to update cure refunds based on
the approval
**/
PROCEDURE set_reject_status (itemtype in varchar2,
itemkey in varchar2,
actid in number,
funcmode in varchar2,
result out nocopy varchar2) IS
l_api_version NUMBER := 1;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
select refund_header_number
from okl_cure_refund_headers_b
where refund_header_number =p_refund_header_number;
OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'T'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_headers : OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr : '||l_return_status);
PROCEDURE update_refund_headers
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
,p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
cursor c_get_tap_ids (p_cure_refund_header_id IN NUMBER ) is
select a.tap_id,
a.cure_refund_id,
a.object_version_number,
b.invoice_number
from okl_cure_refunds a, okl_trx_ap_invoices_b b
where cure_refund_header_id =p_cure_refund_header_id
and a.tap_id =b.id;
select object_version_number from okl_cure_refund_headers_b
where cure_refund_header_id =p_cure_refund_header_id;
l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_REFUND_HEADERS';
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_headers : START');
SAVEPOINT UPDATE_REFUND_HEADERS;
p_operation => 'UPDATE' );
OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
p_api_version => 1.0
,p_init_msg_list => 'F'
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_chdv_rec => lp_chdv_rec
,x_chdv_rec => lx_chdv_rec);
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_headers : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr :'||l_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully updated Cure refund '||
'header table');
okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_headers : END');
ROLLBACK TO UPDATE_REFUND_HEADERS;
ROLLBACK TO UPDATE_REFUND_HEADERS;
ROLLBACK TO UPDATE_REFUND_HEADERS;
Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','UPDATE_REFUND_HEADERS');
END update_refund_headers;
PROCEDURE update_refund_details
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
,p_pay_cure_refunds_tbl IN pay_cure_refunds_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
)IS
BEGIN
null;
END update_refund_details ;
PROCEDURE delete_refund_details
( p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
,p_pay_cure_refunds_tbl IN pay_cure_refunds_tbl_type
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
BEGIN
null;
END delete_refund_details;
select a.contract_number,
b.offset_amount
from okl_cure_refunds b, okc_k_headers_b a
where a.id =b.offset_contract
and b.cure_refund_header_id =p_cure_refund_header_id;