Oracle Applications Knowledge Sharing- Ajay Atre: 2015

Bills Receivable Oracle Application Financials.. by JAIPRAKASH DARODKAR (Finance Techno Functional Consultant)

Wednesday, January 14, 2015 by Ajay Atre

Introduction

Oracle Receivables provides a comprehensive solution to managing the entire life cycle of bills receivable: creation, acceptance, remittance, updates, history, and closing.

What is Bills Receivable?

Bills Receivable, Commerce, Promissory notes, bills of exchange, bonds and other evidences or securities which a merchant or trader holds, and which are

Payable to him.

A bill receivable is a document that your customer formally agrees to pay at some future date (the maturity date). The bill receivable document effectively replaces, for the related amount, the open debt exchanged for the bill. Bills receivable are often remitted for collection and used to secure short term funding.

Definition of Bills Receivable

· A Bills Receivable (BR) has characteristics of both a transaction and a receipt.

· It is like an invoice because it is a debit item that a customer owes you.

· It is like a receipt, because you can remit it to a bank to get payment

Objective :

In Day to Day Business activities, Companies are facing various challenges with respect to

1) Collection Analysis and Strategies for Chasing Late Payments

2) Late Payments of Invoices

3) Invoice Disputes

To overcome this, Bills Receivable can be used as alternative which has below features

1) More secure debit item than a regular invoice.

2) A legal document, binding the debtor (signer) to pay a certain sum at a future day or on demand.

3) Eliminate Collection Analysis and Strategies for Chasing Late Payments

4) Minimize Invoice Disputes

5) By entering into a BR agreement, the debtor promises to pay the creditor the specified amount at the specified date.

Three Types Of Bills Receivable

§ Accepted Bills Receivable (Signed BR)

§ Promissory Note (Drawee Issued)

§ Unsigned Bills Receivable

Contents of Bills Receivable

· Set-up

· Flow

· Creation

· Remittance

· BR Maturity and Risk Elimination Program

· Portfolio Management

· Summary of BR flow

· List of BR programs

· Accounting Entries

Major Setups

1) Auto Accounting

2) Transaction Types

3) Batch Source

4) Receipt Classes

5) Receipt Source

6) Remittance Banks

7) Customer

8) Customer-Drawee Site

9) Payment Details

Auto Accounting

SQL:

SELECT RAD.type,
       HOU.NAME,
       RADC.segment,
       RADC.table_name,
       RADC.constant
FROM   ra_account_defaults_all RAD,
       ra_account_default_segments RADC,
       hr_operating_units HOU
WHERE  RAD.type = 'BR_FACTOR'
       AND RAD.org_id = HOU.organization_id
       AND RAD.gl_default_id = RADC.gl_default_id
       AND HOU.organization_id = 204

Transaction Types

SQL :

SELECT *
FROM ra_cust_trx_types_all RCTT
WHERE RCTT.NAME = 'BR - Drawee Issued'

Receipt Classes

Receipt Source

SQL :

SELECT arc.receipt_class_id,
       arc.creation_method_code,
       arm.printed_name,
       arm.receipt_method_id
FROM   ar_receipt_classes ARC,
       ar_receipt_methods ARM
WHERE  arc.NAME = ‘Unsigned Bills Receivable’’
AND    arm.receipt_class_id = arc.receipt_class_id

Batch Source

SQL :

SELECT *
FROM   ra_batch_sources_all
WHERE  NAME = 'BR Automatic Numbering'
       AND org_id = 204

Customer - Drawee Site

SQL :

SELECT HCA.account_number CUSTOMER_NUMBER,
       HCA.cust_account_id, hp.party_id,
       HP.party_name,      hcsu.site_use_code,
       HPS.party_site_number,

     HL.address1 || ' ' || HL.address2,
       HCSU.status, HOU.NAME OU_Name,
       HOU.organization_id, HCSU.primary_flag
FROM   hz_parties HP,
       hz_cust_accounts HCA,
       hz_cust_acct_sites_all HCSA,
       hz_cust_site_uses_all hcsu,
       hz_locations hl,
       hz_party_sites hps,
       hr_operating_units HOU
WHERE  HCA.party_id = HP.party_id
       AND hcsa.cust_account_id = hca.cust_account_id
       AND hcsa.cust_acct_site_id = hcsu.cust_acct_site_id
       AND hl.location_id = hps.location_id
       AND hps.party_site_id = hcSA.party_site_id
       AND HCSU.org_id = HCSA.org_id
       AND HCSU.org_id = HOU.organization_id
       AND HCSU.site_use_code = 'DRAWEE'
       AND HCSU.status = 'A'
       AND HOU.organization_id = 204

Payment Details

SQL :

SELECT *

FROM

  (SELECT HP.PARTY_NAME CUSTOMER_NAME,

          HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER,

          CUST_ACCOUNT_ID

   FROM HZ_PARTIES HP,

        HZ_CUST_ACCOUNTS HCA

   WHERE HCA.PARTY_ID = HP.PARTY_ID) A,

  (SELECT RaCustRcptMthds.CUST_RECEIPT_METHOD_ID,

          RaCustRcptMthds.CUSTOMER_ID,

          RaCustRcptMthds.RECEIPT_METHOD_ID,

          RaCustRcptMthds.PRIMARY_FLAG,

          RaCustRcptMthds.START_DATE,

          RaCustRcptMthds.END_DATE,

          RaCustRcptMthds.SITE_USE_ID,

          RM.NAME AS NAME

   FROM RA_CUST_RECEIPT_METHODS RaCustRcptMthds,

        AR_RECEIPT_METHODS RM

   WHERE RM.RECEIPT_METHOD_ID = RaCustRcptMthds.RECEIPT_METHOD_ID ) QRSLT

WHERE QRSLT.customer_id = A.CUST_ACCOUNT_ID

  AND A.CUST_ACCOUNT_ID=1003

  AND QRSLT.NAME='BR Standard Remittance'

PROCESS FLOW

Step 1: Create Transaction for Customer and Receipt Method

SQL :

SELECT APS.trx_number,
       APS.amount_due_original,
       APS.amount_due_remaining
FROM   ar_payment_schedules_all aps
WHERE  customer_id = 1003
       AND org_id = 204
       AND APS.status = 'OP'
       AND EXISTS (SELECT 1
                   FROM   ra_customer_trx_all RCT
                   WHERE  RCT.customer_trx_id =

APS.customer_trx_id AND RCT.receipt_method_id = 1221)

Step 2: Creation of BR.

SQL :

1) SELECT *
FROM   ar_payment_schedules_all
WHERE  trx_number = '1042'
       AND class = 'BR'

2) SELECT *
FROM ra_customer_trx_all
WHERE customer_trx_id = 852744

Step 3: Click on Assignments / add transactions in BR

SQL :

SELECT *
FROM   (SELECT trx_number,
               (SELECT hca.account_number
                FROM   hz_cust_accounts hca
                WHERE  hca.cust_account_id = rct.bill_to_customer_id)
               acct_number,
               (SELECT status
                FROM   ar_payment_schedules_all aps
                WHERE  aps.customer_trx_id = rct.customer_trx_id
                       AND rownum = 1)                                trx_status
               ,
               (SELECT amount_due_remaining
                FROM   ar_payment_schedules_all aps
                WHERE  aps.customer_trx_id = rct.customer_trx_id
                       AND rownum = 1)
                      amount_due_remaining
        FROM   ra_customer_trx_all rct
        WHERE  receipt_method_id = 1221
               AND org_id = 204)
WHERE  trx_status = 'OP'
       AND acct_number = '1003'
       AND trx_number IN( '1041' )

Step 4: Complete the BR.

Status of BR changed to Pending Remittance

Invoice selected got closed

Activities against the Invoice

Tip : For SQL, Refer query above, remove condition trx_status=’OP’

Step 5: BILLS RECEIVABLES à REMITTANCES

Manual Create

Click on Actions

Concurrent program is submitted and completed

BR Status has now changed to Standard Remitted

Step 6: Run Maturity and Risk Programs

View Concurrent Requests

SQL :

SELECT ps.customer_trx_id,

       ps.payment_schedule_id,

       ps.due_date maturity_date,

       ps.reserved_type,

       ps.reserved_value,

       ps.amount_due_remaining,

       ps.tax_remaining,

       trh.gl_date,

       trh.transaction_history_id,

       trh.prv_trx_history_id,

       trh.status,

       trh.event,

       ps.org_id

FROM ar_transaction_history trh,

     ar_payment_schedules ps

WHERE ps.class = 'BR'

and ps.reserved_type in ('REMITTANCE','ADJUSTMENT')

Report Output :

Status of Bills Receivable is now Closed

Step 7: Portfolio Management

i) Query BR Document à Find

i) Click History

i) Click Details

i) Receipt Created

i) Click Apply

SQL :

SELECT rap.receivable_application_id,
       rap.cash_receipt_id,
       rap.gl_date,
       rap.apply_date,
       rap.org_id
FROM   ar_receivable_applications_all rap
WHERE  rap.applied_customer_trx_id = 852744
       AND rap.display = 'Y'
ORDER  BY rap.receivable_application_id DESC;

Accounting Entries

1) Invoice Created