Oracle Application R12 credit memo:
Credit memo is documents which is issued by seller to buyer against return or exchange, billing error or price adjustment.
In oracle application, when seller issues credit memo against the invoices of the buyer, the liablity of buyer to pay bills is removed.
We can create credit memo in front end by going to AR->Cash Receipt as credit memo.
We need to make sure the right amount , buyer name and billing address so that credit memo gets applied to wrigth place.
Technically we can apply credit memo by using following oracle supplied credit memo.
The following program is for demo purpose only.The program fetches the late fees, check if payment made withinn xxx days .If payment made then it applies credit memo for those late fees invoices.
declare
v_return_status varchar2 (1);
p_count number;
v_msg_count number;
v_msg_data varchar2 (2000);
v_request_id number;
l_cm_lines_tbl arw_cmreq_cover.cm_line_tbl_type_cover;
l_customer_trx_id number;
cm_trx_id number;
v_interface_header_rec arw_cmreq_cover.pq_interface_rec_type;
ind number;
l_trx_number varchar2 (30):= 0;
l_org_id number:= 0;
v_trx_date date;
l_payment_flag boolean;
/*the cursor fetches all of the late fees type invoice*/
cursor c_fetch_late_fees ( p_org_id number default null, p_cust_name varchar2 default null, p_sales_order_no varchar2 default null, p_trx_number varchar default null ) is
select
rct.customer_trx_id,
rct.trx_number invoice_number,
rct.recurred_from_trx_number,
rct.reason_code,
arps.amount_due_original balance_original,
arps.amount_due_remaining balance_due_remaining,
hp.party_name bill_to_customer,
case
rctt.type
when
'cb'
then
'chargeback'
when
'cm'
then
'credit memo'
when
'dm'
then
'debit memo'
when
'dep'
then
'deposit'
when
'guar'
then
'guarantee'
when
'inv'
then
'invoice'
when
'pmt'
then
'receipt'
else
'invoice'
end
invoice_class, rct.invoice_currency_code currency, rct.trx_date inv_date, rctd.gl_date gl_date,
(
select
name
from
apps.ra_terms rat
where
rat.term_id = rct.term_id
)
terms,
rctt.name order_type,
rct.attribute15 installment_no,
rct.attribute6,
rct.attribute7,
rct.attribute8,
rct.attribute9,
rct.attribute14,
rct.attribute15,
rct.ct_reference,
owtt.product_type
from
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctd,
hz_parties hp,
hz_cust_accounts_all hca,
ra_cust_trx_types_all rctt,
hr_operating_units hou,
ar_payment_schedules_all arps
where
rct.customer_trx_id = rctd.customer_trx_id
and rct.customer_trx_id = rctl.customer_trx_id
and rctl.customer_trx_line_id = rctd.customer_trx_line_id
and rct.bill_to_customer_id = hca.cust_account_id
and arps.status = 'op'
and arps.class = 'inv'
and okch.bill_to_site_use_id = rct.bill_to_site_use_id
and hp.party_id = hca.party_id
and rct.cust_trx_type_id = rctt.cust_trx_type_id
and rct.org_id = rctt.org_id
and rct.org_id = hou.organization_id
and arps.customer_trx_id( + ) = rct.customer_trx_id
and rct.org_id = nvl (p_org_id, rct.org_id)
and rct.trx_number = nvl (p_trx_number, rct.trx_number)
and hp.party_name = nvl (p_cust_name, hp.party_name)
and nvl (rct.ct_reference, 'xx') = nvl (nvl (p_sales_order_no, rct.ct_reference), 'xx')
group by
rct.trx_number,
arps.amount_due_original,
arps.amount_due_remaining,
hp.party_name,
rctt.type,
rct.invoice_currency_code,
rct.trx_date,
rctd.gl_date,
rct.term_id,
rctt.name,
rct.attribute6,
rct.attribute7,
rct.attribute8,
rct.attribute9,
rct.attribute14,
rct.attribute15,
rct.ct_reference,
rct.reason_code,
rct.recurred_from_trx_number,
rct.customer_trx_id,
recurred_from_trx_number,
product_type
order by
inv_date desc ;
/*the cursor fetches all of the invoices, for which late fees has been generated */
cursor c_fetch_related_invoice (p_org_id varchar2, p_trx_date date, p_ct_reference varchar2) is
select
trx_number,
trx_date,
interface_header_attribute9,
ct_reference,
amount_due_remaining,
set_of_books_id
from
(
select
rct.trx_number,
rct.cust_trx_type_id,
rct.trx_date,
rct.set_of_books_id,
rct.batch_id,
rct.batch_source_id,
rct.bill_to_customer_id,
rct.bill_to_site_use_id,
rct.term_id,
rct.invoice_currency_code,
rct.attribute6,
rct.attribute7,
rct.attribute8,
rct.attribute9,
rct.attribute11,
rct.attribute14,
rct.attribute15,
rct.interface_header_attribute6,
rct.interface_header_attribute7,
rct.interface_header_context,
rct.interface_header_attribute14,
rct.interface_header_attribute9,
rct.status_trx,
rct.org_id,
rctl.amount_due_original,
rctl.amount_due_remaining,
rctl.amount_includes_tax_flag,
rct.ct_reference
from
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ar_payment_schedules_all aprs
where
rct.customer_trx_id = rctl.customer_trx_id
and rct.customer_trx_id = aprs.customer_trx_id
and rct.trx_date = p_trx_date
and rct.batch_source_id = xx
and rct.cust_trx_type_id = xx
and rct.invoice_currency_code = xx
and aprs.class = 'inv'
order by
trx_date desc
)
order by
trx_date desc ;
/*the cursor fetches if invoice has been paid or not */
cursor check_invoice_paid_yn (p_trx_number varchar2, p_ct_refernce varchar2) is
select
rct.trx_number invoice_no,
rct.ct_reference
from
ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
ra_customer_trx_all rct
where
ara.status = 'app'
and ara.cash_receipt_id = acr.cash_receipt_id
and ara.applied_customer_trx_id = rct.customer_trx_id
and rct.trx_number in p_trx_number
and rct.ct_reference = p_ct_refernce ;
lc check_invoice_paid_yn % rowtype;
cursor c_inv (p_trx_number varchar2, p_ct_refernce varchar2 ) is
select
rct.trx_number,
rct.customer_trx_id,
rctl.customer_trx_line_id,
rctl.quantity_invoiced,
unit_selling_price
from
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl
where
rct.customer_trx_id = rctl.customer_trx_id
and trx_number = p_trx_number
and ct_reference = p_ct_refernce
and line_type = 'line';
begin
for i_late_fees in c_fetch_late_fees loop
begin
if i_late_fees.product_type = 'xx'
then
mo_global.set_policy_context ('s', xx);
l_org_id:= xx;
elsif i_late_fees.product_type in
(
'xx', 'xx'
)
then
mo_global.set_policy_context ('s', xx);
l_org_id:= xx;
end
if;
exception
when
others
then
dbms_output.put_line('error setting org' || sqlerrm || sqlcode);
end
;
select
to_date(i_late_fees.inv_date) - 5 into v_trx_date
from
dual;
for l_related_invoice in c_fetch_related_invoice (l_org_id , v_trx_date , i_late_fees.ct_reference ) loop l_payment_flag:= false;
open check_invoice_paid_yn (p_trx_number => l_related_invoice.trx_number, p_ct_refernce => l_related_invoice.ct_reference );
fetch check_invoice_paid_yn into lc;
if check_invoice_paid_yn % found
then
l_payment_flag:= true;
elsif check_invoice_paid_yn % notfound
then
l_payment_flag:= false;
end
if;
close check_invoice_paid_yn;
if l_payment_flag = true
then
for lc_inv in c_inv (l_trx_number, i_late_fees.ct_reference) loop dbms_output.put_line ('invoking credit memo creation process for trx_no and ct_refernce are ' || l_trx_number || ' and ' || i_late_fees.ct_reference);
ind := 1;
l_customer_trx_id := lc_inv.customer_trx_id;
l_cm_lines_tbl (ind).customer_trx_line_id := lc_inv.customer_trx_line_id;
l_cm_lines_tbl (ind).quantity_credited := lc_inv.quantity_invoiced * - 1;
l_cm_lines_tbl (ind).price := lc_inv.unit_selling_price;
l_cm_lines_tbl (ind).extended_amount := lc_inv.quantity_invoiced * lc_inv.unit_selling_price * - 1;
end
loop;
ar_credit_memo_api_pub.create_request ( -- standard api parameters
p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false-- credit memo request parameters
, p_customer_trx_id => l_customer_trx_id, p_line_credit_flag => 'y', p_cm_line_tbl => l_cm_lines_tbl, p_cm_reason_code => 'return', p_skip_workflow_flag => 'y', p_batch_source_name => 'xx', p_interface_attribute_rec => v_interface_header_rec, p_credit_method_installments => null, p_credit_method_rules => null, x_return_status => v_return_status, x_msg_count => v_msg_count, x_msg_data => v_msg_data, x_request_id => v_request_id );
* / commit;
dbms_output.put_line ('message count ' || v_msg_count);
if v_msg_count = 1
then
dbms_output.put_line ('l_msg_data ' || v_msg_data);
elsif v_msg_count > 1
then
loop p_count := p_count + 1;
v_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
if v_msg_data is null
then
exit;
end
if;
dbms_output.put_line ('message' || p_count || ' ---' || v_msg_data);
end
loop;
end
if;
if v_return_status <> 's'
then
dbms_output.put_line ('failed');
else
select
cm_customer_trx_id into cm_trx_id
from
ra_cm_requests_all
where
request_id = v_request_id;
dbms_output.put_line (' cm trx_id = ' || cm_trx_id);
-- you can issue a commit; at this point if you want to save the created credit memo to the database
-- commit;
end
if;
end
if;
end
loop;
end
loop;
end
;
