1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

apps ar floe

Discussion in 'Oracle HRMS & Payroll' started by sridar, Jun 8, 2011.

  1. sridar

    sridar Guest

    1. Overview of Transactions entered into Receivables.

    This is to show how various transactions entered into Receivables (AR) affect different tables in the database. The transactions that will be covered are:-

    O Invoices
    O Credit Memos
    O On Account Credits
    O Receipts


    2. Invoices

    When you enter an invoice records are inserted into the following tables:-

    o RA_CUSTOMER_TRX_ALL (Invoice Header)
    o RA_CUSTOMER_TRX_LINES_ALL (Line Details)
    o RA_CUST_TRX_LINE_GL_DIST_ALL (Information for posting to GL)
    o RA_CUST_TRX_LINES_SALESREPS_ALL (Sales Credit Information)

    Once an Invoice has been marked, as "Complete" then it will also have at least one record in the table: -

    O AR_PAYMENT_SCHEDULES_ALL (Running Totals for Invoice
    Amounts)

    Normally each invoice will only have 1 payment schedule record but there may be more if the invoice is to be paid in installments - 1 record per installment.




    +------------+ +------------+
    | | /| |
    | TRX |---------------<-| PAYMENT |
    | | \| SCHEDULE |
    +------------+ +------------+
    | |
    | |
    ^ +-------+
    /|\ |
    +------------+ |
    | | |
    | TRX_LINES | |
    _| | |
    / +------------+ |
    | | | |
    \__/ | |
    ^ |
    /|\ |
    +------------+
    | |
    | GL_DIST |
    | |
    +------------+


    2.1 RA_CUSTOMER_TRX_ALL

    Invoice Header information such as the customer details, invoice number, date, where payment should be sent etc.

    1 Record per Invoice

    Key = CUSTOMER_TRX_ID (generated from sequence RA_CUSTOMER_TRX_S)

    Important Fields

    TRX_NUMBER - User Entered Invoice Number
    CUST_TRX_TYPE_ID - Foreign key to RA_CUST_TRX_TYPES
    BILL_TO_CUSTOMER_ID - Foreign Key to RA_CUSTOMERS
    SHIP_TO_CUSTOMER_ID - Foreign key to RA_CUSTOMERS
    TRX_DATE - Invoice Date
    COMPLETE_FLAG - Y or N


    2.2 RA_CUSTOMER_TRX_LINES_ALL

    Line details, what is being invoiced and how much of it, what tax is
    Applicable.


    At least 1 record per Invoice.

    Key = RA_CUSTOMER_TRX_LINE_ID (from sequence RA_CUSTOMER_TRX_LINES_S)

    Important Fields

    CUSTOMER_TRX_ID - Foreign key to RA_CUSTOMER_TRX
    LINE_TYPE - LINE, TAX, FREIGHT
    QUANTITY_INVOICED - Line Quantity
    UNIT_SELLING_PRICE - Price per unit
    EXTENDED_AMOUNT - Quantity * Price
    LINK_TO_CUST_TRX_LINE_ID - Null for LINE, for TAX and FREIGHT
    Lines contains the
    RA_CUSTOMER_TRX_LINE_ID of
    Associated LINE record

    2.3 RA_CUST_TRX_LINE_GL_DIST_ALL

    Information that needs to be posted across to the General Ledger.

    1 record for each line record plus 1 for the Invoice Header

    Key = CUST_TRX_LINE_GL_DIST_ID (from sequence)

    Important Fields

    CUSTOMER_TRX_ID - Foreign key to RA_CUSTOMER_TRX
    CUSTOMER_TRX_LINE_ID - Foreign key to RA_CUSTOMER_TRX_LINES
    Or Null if this relates to Invoice
    Header.
    AMOUNT - Value of distribution entered
    Currency
    ACCOUNTED_AMOUNT - Value of distribution - book currency
    CODE_COMBINATION_ID - Foreign key to GL_CODE_COMBINATIONS
    POSTING_CONTROL_ID - -3 if unposted
    GL_DATE - Accounting date.
    GL_POSTED_DATE - Date Invoice posted to GL.


    2.4 RA_CUST_TRX_LINE_SALESREPS_ALL

    Who gets credited for the sale represented by this invoice? Not many people use this functionality and few problems reported on it, so I'll skip it.

    2.5 AR_PAYMENT_SCHEDULES_ALL

    Keep running a total of the Invoice amounts for line, tax, and freight. Records both the original amounts and the amounts remaining - i.e. money expected from the customer. Problems often show up in this table because whenever transactions are applied to Invoices they update the rows in this table.

    Normally 1 record per Invoice.

    Key = PAYMENT_SCHEDULE_ID (from sequence AR_PAYMENT_SCHEDULES_S)

    Important Fields

    CUSTOMER_TRX_ID - Foreign key to RA_CUSTOMER_TRX
    STATUS - (Open) or (closed)
    ACTUAL_DATE_CLOSED - 31-DEC-4712 if still open, otherwise
    Date payment schedule closed.
    GL_DATE_CLOSED - 31-DEC-4712 if still open, otherwise
    The gl date of the closing
    Transaction.
    AMOUNT_DUE_ORIGINAL - Invoice Total Amount
    AMOUNT_DUE_REMAINING - Total Amount outstanding for Invoice.
    AMOUNT_LINE_ITEMS_ORIGINAL - Sum of Invoice Line amounts.
    AMOUNT_LINE_ITEMS_REMAINING - Outstanding Line amounts.
    TAX_ORIGINAL - Total Tax for Invoice.
    TAX_REMAINING - Outstanding Tax for Invoice
    FREIGHT_ORIGINAL - Total Freight For Invoice
    FREIGHT_REMAINING - Outstanding Freight
    AMOUNT_APPLIED - Total payments applied to this
    Invoice.
    AMOUNT_CREDITED - Total Credit Memos applied.
    AMOUNT_ADJUSTED - Total value of adjustments.

    The payment schedule should balance within itself, i.e. the following
    Calculations should be true: -

    AMOUNT_DUE_ORIGINAL = AMOUNT_LINE_ITEMS_ORIGINAL
    + TAX_ORIGINAL
    + FREIGHT_ORIGINAL

    AMOUNT_DUE_REMAINING = AMOUNT_LINE_ITEMS_REMAINING
    + TAX_REMAINING
    + FREIGHT_REMAINING

    AMOUNT_DUE_ORIGINAL = AMOUNT_DUE_REMAINING
    + AMOUNT_APPLIED
    - AMOUNT_CREDITED
    + AMOUNT_ADJUSTED


    3. Credit Memos

    This section refers to Credit Memos that are entered directly against a
    Particular invoice. They are stored in the same tables as the invoices. When you add such a credit it inserts records into the following tables.


    O RA_CUSTOMER_TRX_ALL (CM Header)
    O RA_CUSTOMER_TRX_LINES_ALL (CM Line Details)
    O RA_CUST_TRX_LINE_GL_DIST_ALL (GL Posting Info)
    O AR_PAYMENT_SCHEDULES_ALL (CM Running Totals)
    O AR_RECEIVABLE_APPLICATIONS_ALL (How much of CM applied to
    Invoice)

    It will also update the following records on the corresponding Invoice to reduce the outstanding balance by the amount of the credit.

    O AR_PAYMENT_SCHEDULES_ALL (Invoices Running Totals)

    If the profile option "Use Invoice accounting for Credit Memos" is Yes the Credit lines and distribution lines will be copied from those of the invoice being credited.

    +-------------------------+
    | AR_PAYMENT_SCHEDULES |
    +------------+ | +------------+ |
    | | | | | |
    | TRX |--------------------| CM | |
    | | | | |------+ |
    +------------+ | +------------+ | |
    | | | | | INVOICE | |
    | | | | | | |
    ^ +-------+ | | +------------+ |
    /|\ | +------|--------|---------+
    +------------+ | | |
    | | | | |
    | TRX_LINES | | | |
    _| | | +------------+
    / +------------+ | | |
    | | | | |APPLICATIONS|
    \__/ | | | |
    ^ | +------------+
    /|\ |
    +------------+
    | |
    | GL_DIST |
    | |
    +------------+




    3.1 RA_CUSTOMER_TRX_ALL

    Similar information is held for Credits as for Invoices at the header level.


    3.2 RA_CUSTOMER_TRX_LINES_ALL

    Again similar info is held at the line level except that the following fields will also be populated: -

    QUANTITY_CREDITED
    PREVIOUS_CUSTOMER_TRX_ID (Invoices_
    Customer_trx_id)
    PREVIOUS_CUSTOMER_TRX_LINE_ID (Invoice_line_id)
    And QUANTITY_INVOICED will be NULL.



    3.3 RA_CUST_TRX_LINE_GL_DIST_ALL

    Similar to Invoice distribution lines.





    3.4 AR_PAYMENT_SCHEDULES_ALL (for the Credit Memo)

    If the credit memo is fully applied to the Invoice then the payment schedule will reflect that. All of the remaining amount fields will be 0, and it will have a closed status.


    3.5 AR_RECEIVABLE_APPLICATIONS_ALL

    This is the key table used to link the credit memo and Invoice together. A record will be inserted here which will be posted to the Ledger and has the following characteristics: -

    Key = RECEIVABLE_APPLICATION_ID (from sequence)

    Important Fields

    CUSTOMER_TRX_ID - Key to Credit Memos Header Record
    CASH_RECEIPT_ID - NULL
    STATUS - 'APP' lied
    APPLIED_CUSTOMER_TRX_ID - Key to Invoices Header Record
    APPLIED_CUSTOMER_TRX_LINE_ID - Key to Invoices Line Record
    APPLIED_PAYMENT_SCHEDULE_ID - Key to Invoices Payment
    Schedule
    AMOUNT_APPLIED - Total amount of Credit applied.
    LINE_APPLIED - Line amount of Credit applied.
    TAX_APPLIED - Tax amount of credit applied.
    FREIGHT_APPLIED - Freight amount of credit applied
    GL_POSTED_DATE - When posted to GL


    3.6 AR_PAYMENT_SCHEDULES_ALL (for the Invoice)

    The various remaining amount fields are reduced by the amount of the credit applied. The AMOUNT_CREDITED contains a running total of credit memos applied against this invoice.

    4. On Account Credits

    Credits that are entered against a specific customer, but not against a
    Specific invoice are referred to as 'On Account' credits. In terms of the data stored in the tables they are very similar to Credit Memos, the main differences being: -

    O When first entered the payment schedule will be fully
    Remaining.

    O When first entered no records are inserted into
    AR_RECEIVABLE_APPLICATIONS_ALL.

    O The line records will have NULL values in
    PREVIOUS_CUSTOMER_TRX_ID
    PREVIOUS_CUSTOMER_TRX_LINE_ID

    O The distribution lines have to be typed in as there is no
    Invoice to copy them from.

    It is because of the last point that on account credits can later be applied against any invoice and later reapplied to different invoices. Credits that were entered against a specific invoice will have lines and distributions that specifically match that invoice, so cannot be reapplied to other invoices.



    5. Cash Receipts

    When a receipt is entered the following tables are inserted into:-

    o AR_CASH_RECEIPTS_ALL
    o AR_CASH_RECEIPT_HISTORY_ALL
    o AR_PAYMENT_SCHEDULES_ALL
    o AR_RECEIVABLE_APPLICATIONS_ALL

    When it is applied to an Invoice an additional pair of records is inserted into AR_RECEIVABLE_APPLICATIONS_ALL and the Invoices AR_PAYMENT_SCHEDULE_ALL record is updated.










    +------------+ +------------+
    | | | |
    | RECEIPT |------------------| PAYMENT |
    | | | SCHEDULE |
    +------------+ +------------+
    | |
    | |
    ^ ^
    /|\ /|\
    +------------+ +------------+
    | | | |
    | RECEIPT | |APPLICATIONS|
    | HISTORY | | |
    +------------+ +------------+


    5.1 AR_CASH_RECEIPTS_ALL

    This stores the basic receipt information, 1 record per receipt.

    Key = CASH_RECEIPT_ID (from sequence AR_CASH_RECEIPTS_S)

    Important Fields

    AMOUNT - Value of receipt in entered currency
    RECEIPT_NUMBER - Payment Number entered by user.
    STATUS - (APP) lied, (UNAPP) lied, (REV) erased
    Payment (STOP) payment, (NSF)
    Insufficient funds.
    It will only change to APP once the
    Whole amount of the receipt is
    Applied.
    REVERSAL_DATE - NULL unless receipt reversed
    PAY_FROM_CUSTOMER - Contains CUSTOMER_ID for RA_CUSTOMERS


    5.2 AR_CASH_RECEIPT_HISTORY_ALL

    Used mainly for automatic receipts, for manually entered receipts this table will normally contain 1 record per receipt. Information from this table is posted to GL. Reversing a receipt inserts a new row into the table.

    Key = CASH_RECEIPT_HISTORY_ID (from sequence)

    Important Fields

    CASH_RECEIPT_ID - Foreign key to AR_CASH_RECEIPTS
    Record.
    STATUS - CLEARED for manually input
    Receipts.
    GL_DATE - Accounting date
    ACCOUNT_CODE_COMBINATION_ID - Key to GL_CODE_COMBINATIONS
    POSTING_CONTROL_ID - -3 if unposted
    REVERSAL_POSTING_CONTROL_ID - NULL unless payment reversed
    CURRENT_RECORD_FLAG - Y if this is latest record
    PRV_STAT_CASH_RECEIPT_HIST_ID- Key to previous receipt history
    Record.

    5.3 AR_PAYMENT_SCHEDULES_ALL

    Each receipt has a record in this table to keep a running total of how much has been applied. The details are similar to those held on Invoices, but there are some significant differences.

    Key = PAYMENT_SCHEDULE_ID (from sequence)

    Important Fields

    CUSTOMER_TRX_ID - NULL
    CASH_RECEIPT_ID - Foreign key to AR_CASH_RECEIPTS
    Record.
    AMOUNT_DUE_ORIGINAL - Total amount of receipt (usually
    Negative)
    AMOUNT_DUE_REMAINING - Unapplied amount of receipt.
    AMOUNT_APPLIED - How much of this receipt is applied.
    STATUS - (Open) or (Closed). Will only be
    Closed if AMOUNT_DUE_REMAINING is
    Zero.

    All of the fields holding LINE, TAX and FREIGHT amounts are NULL.

    5.4 AR_RECEIVABLE_APPLICATIONS

    One record is inserted into this table when the receipt is first created; all further inserts here are done in pairs, as the receipt is applied/unapplied to invoices. i.e.

    Record 1 UNAPP 700

    {Record 2 UNAPP -200
    {Record 3 APP 200 cross-referenced to the Invoice

    {Record 4 UNAPP -500
    {Record 5 APP 500 cross-referenced to 2nd Invoice

    The sum of the amounts on records that have a particular status should add up to the running totals on the payment schedules, but with the opposite sign.

    i.e. In the example above

    AR_PAYMENT_SCHEDULES.AMOUNT_DUE_ORIGINAL = -700
    AR_PAYMENT_SCHEDULES.AMOUNT_DUE_REMAINING = 0
    AR_PAYMENT_SCHEDULES.AMOUNT_APPLIED = -700

    UNAPP = 700 -200 -500 = 0
    APP = 200 + 500 = 700

    Statuses of these records can be: -

    UNAPP - Unapplied
    APP - Applied
    ACC - On Account
    UNID - Unidentified (Customer Not known)

    You can see the application records if you query the invoice/credit/receipt in the Transaction History form.

    Oracle Receivables Transaction History 28-JUN-95
    +- Customer ------------------------------------------------------------+
    | Name Rolls Royce Aerospace Number 1000 Location 1000 |
    +--- Transaction ----------------------------------------------------------+
    | Number 10001 Original Amount 1,175.00 Past Due 136 |
    | Type Invoice Open Amount 240.00 Last Dunning 12-JUN-95|
    | Class Invoice Dispute Amount 0.00 Last Contacts |
    | Date 13-JAN-1995 Discount Amount 0.00 PO Number |
    |Due Date 12-FEB-1995 Pending Adj 0.00 Document No |
    |Currency USD Selected For Receipt No Transaction [ ] |
    +-- Transaction Activity ----------------------------------------------------+
    | Type Class Number Reference Line GL_Date Date Amount[ ]|
    | Cash Recei 88 Applied 31-JAN-95 17-JUN-95 700.00 |
    | Cred Credi 10004 27-JAN-95 17-JUN-95 235.00 |
    | |
    +-- Adjustment Activity ------------------------------------------------------+
    | |
    | |
    | |
    +-----------------------------------------------------------------------------+
    | Choose Next Screen |
    +---------------------------------------------------------------------------+

    5.5 AR_PAYMENT_SCHEDULE (Invoice)

    When the receipt is applied to an Invoice it updates the invoices payment
    Schedule record. The remaining amount fields of the record are reduced by the
    Amount of the payment. If the remaining amount is reduced to zero then the
    Invoices payment schedule will be closed.

    For Example, if a 200.00 receipt is applied to a 1175.00 invoice which includes
    175.00 of tax the invoices payment schedule is adjusted as follows:-

    Before After

    AMOUNT_DUE_REMAINING 1175.00 975.00
    AMOUNT_LINE_ITEMS_REMAINING 1000.00 800.00
    TAX_REMAINING 175.00 175.00
    FREIGHT_REMAINING 0.00 0.00

    Note that receipts are applied in a fixed sequence: -

    1. Line Amounts
    2. Tax Amounts
    3. Freight Amounts

    I.e. The TAX_REMAINING figure will only start to decrease when the
    AMOUNT_LINE_ITEMS_REMAINING is zero.

    This causes problems when trying to apply credits against partly paid invoices.
    You may need to unapplied any payments before you can apply the credit, then
    Reapply the payment again.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Did you mean to write an article?