Unapply AR trascations

 Oracle Application unapply credit memo

As discussed in my preovious post, credit memo is issued to nullify pending receivbale.If the payment already applied to the transaction then we first make sure transaction is unapplied and then only apply credit memo in those transactions

The process to unapply credit memo can be done using api:

AR_RECEIPT_API_PUB.unapply

                                                                ( p_api_version => 1.0,

                                                                p_init_msg_list => FND_API.G_TRUE,

                                                                p_commit => FND_API.G_TRUE,

                                                                p_validation_level => FND_API.G_VALID_LEVEL_FULL,

                                                                x_return_status => l_return_status,

                                                                x_msg_count => v_msg_count,

                                                                x_msg_data => v_msg_data,

                                                                p_cash_receipt_id => LC_REC.cash_receipt_id,

                                                                p_customer_trx_id=>I_LATE_FEES.customer_trx_id,

                                                                p_reversal_gl_date => sysdate

                                                            

                                                                );

Oracle Application Credit Memo R12

 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

;

Order To Cash Cycle Flow with queries

SN	Process	Remarks	Query
1	Enter Order	Flow status : Header: Entered line: Entered	SELECT HEADER_ID ,ORDER_NUMBER ,FLOW_STATUS_CODE ,ORDER_CATEGORY_CODE ,BOOKED_FLAG ,ORG_ID FROM oe_order_headers_all WHERE order_number = 66465	SELECT HEADER_ID ,LINE_ID ,LINE_TYPE_ID ,FLOW_STATUS_CODE ,LINE_NUMBER ,ORDERED_ITEM ,ORDERED_QUANTITY ,UNIT_LIST_PRICE_PER_PQTY FROM OE_ORDER_LINES_ALL WHERE 1=1 AND HEADER_ID = 192535
2	Book Order	Header :Booked Line: Awaiting Shipping Record(s) will be created in the table WSH_DELIVERY_DETAILS with RELEASED_STATUS=’R’ (Ready to Release) Also Record(s) will be inserted into WSH_DELIVERY_ASSIGNMENTS. SELECT * FROM WSH_DELIVERY_ASSIGNMENTS At the same time DEMAND INTERFACE PROGRAM runs in the background and inserts into MTL_DEMAND select * from MTL_DEMAND BackGround Program: Demand Interface program	SELECT HEADER_ID ,ORDER_NUMBER ,FLOW_STATUS_CODE ,ORDER_CATEGORY_CODE ,BOOKED_FLAG ,ORG_ID FROM oe_order_headers_all WHERE order_number = 66465	SELECT HEADER_ID ,LINE_ID ,LINE_TYPE_ID ,FLOW_STATUS_CODE ,LINE_NUMBER ,ORDERED_ITEM ,ORDERED_QUANTITY ,UNIT_LIST_PRICE_PER_PQTY FROM OE_ORDER_LINES_ALL WHERE 1=1 AND HEADER_ID = 192535
3	Pick Release	Header :Booked Line : Pick Confirmed If Auto create Delivery is set to ‘Yes’ then a new record is created in the table WSH_NEW_DELIVERIES DELIVERY_ID is populated in the table WSH_DELIVERY_ASSIGNMENTS. The RELEASED_STATUS in WSH_DELIVERY_DETAILS would be now set to ‘Y’ (Pick Confirmed) if Auto Pick Confirm is set to Yes otherwise RELEASED_STATUS is ‘S’ (Release to Warehouse). Back Ground Program: Pick Slip Report Shipping Execution Report Auto Pack Report Line: Pick Confirmed	SELECT DELIVERY_DETAIL_ID ,SOURCE_HEADER_ID ,SOURCE_LINE_ID ,CUSTOMER_ID ,INVENTORY_ITEM_ID ,RELEASED_STATUS FROM WSH_DELIVERY_DETAILS WHERE 1=1 AND SOURCE_HEADER_ID = 192535 ----------------------------------------------- SELECT * FROM WSH_NEW_DELIVERIES WHERE 1=1 AND DELIVERY_ID = 3773405 ORDER BY CREATION_DATE DESC -------------------------------------------------- SELECT * FROM WSH_DELIVERY_ASSIGNMENTS WHERE 1=1 AND DELIVERY_DETAIL_ID = 3962503 ORDER BY CREATION_DATE DESC
4	Pick Confirm	Header: Booked Line : Pick Confirmed
5	Ship Confirm	Header Booked Line : Shipped RELEASED_STATUS in WSH_DELIVERY_DETAILS would be ‘C’ (Ship Confirmed) FLOW_STATUS_CODE in OE_ORDER_HEADERS_ALL would be “BOOKED“ FLOW_STATUS_CODE in OE_ORDER_LINES_ALL would be “SHIPPED“ Back Ground Programss: Interface Trip Stop Commercial Invoice Packing Slip Report Bill Of Lading	SELECT HEADER_ID ,ORDER_NUMBER ,FLOW_STATUS_CODE ,ORDER_CATEGORY_CODE ,BOOKED_FLAG ,ORG_ID FROM oe_order_headers_all WHERE order_number = 66435 ------------------------------ SELECT HEADER_ID ,LINE_ID ,LINE_TYPE_ID ,FLOW_STATUS_CODE ,LINE_NUMBER ,ORDERED_ITEM ,ORDERED_QUANTITY ,UNIT_LIST_PRICE_PER_PQTY FROM OE_ORDER_LINES_ALL WHERE 1=1 AND HEADER_ID = 192535 --------------------------------- SELECT DELIVERY_DETAIL_ID ,SOURCE_HEADER_ID ,SOURCE_LINE_ID ,CUSTOMER_ID ,INVENTORY_ITEM_ID ,RELEASED_STATUS FROM WSH_DELIVERY_DETAILS WHERE 1=1 AND SOURCE_HEADER_ID = 192535
6	Invoice	Header :closed lines: Closed INTERFACE_LINE_CONTEXT     =     ’ORDER ENTRY’ INTERFACE_LINE_ATTRIBUTE1=     Order_number INTERFACE_LINE_ATTRIBUTE3=     Delivery_id RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number. RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number. Background Programs: Auto Invoice Master Programs Auto Invoice Import Programs	SELECT * FROM RA_INTERFACE_LINES_ALL WHERE 1=1 AND INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND INTERFACE_LINE_ATTRIBUTE1 = 66435 AND INTERFACE_LINE_ATTRIBUTE3 = 3773405 ------------------------------------ SELECT CUSTOMER_TRX_ID ,TRX_NUMBER ,INTERFACE_HEADER_ATTRIBUTE1 ,INTERFACE_HEADER_ATTRIBUTE2 ,INTERFACE_HEADER_ATTRIBUTE3 ,INTERFACE_HEADER_CONTEXT ,STATUS_TRX FROM RA_CUSTOMER_TRX_ALL WHERE 1=1 AND INTERFACE_HEADER_CONTEXT = 'ORDER ENTRY' AND INTERFACE_HEADER_ATTRIBUTE1 = '66435' AND INTERFACE_HEADER_ATTRIBUTE3 = 3773405 ORDER BY CREATION_DATE DESC ------------------------------------------------------------------ SELECT * FROM RA_CUSTOMER_TRX_LINES_ALL WHERE 1=1 AND TRX_NUMBER = ’10037546’ AND INTERFACE_LINE_CONTEXT = 'ORDER ENTRY' AND INTERFACE_LINE_ATTRIBUTE1 = 66435 AND INTERFACE_LINE_ATTRIBUTE3 = 3773405 ORDER BY CREATION_DATE DESC
7	Receipt		Underlying tables: SELECT * FROM AR_CASH_RECEIPTS_ALL WHERE 1=1 AND RECEIPT_NUMBER = 'R10037546' ORDER BY CREATION_DATE DESC -------------------------------- SELECT * FROM AR_RECEIVABLE_APPLICATIONS_ALL WHERE 1=1 AND CASH_RECEIPT_ID = 113993 ORDER BY CREATION_DATE DESC -------------------------------- SELECT * FROM AR_PAYMENT_SCHEDULES_ALL WHERE 1=1 AND CASH_RECEIPT_ID = 113993 ORDER BY CREATION_DATE DESC
8	Transfer To GL
9	Journal Import
10	Posting