AP Module

Oracle Payables has following major processes:

1. Setting up suppliers
2. Entering and approving invoices
3. Paying invoices
4. Creating accounting entries
5. Sending transactions to the general ledger

Type of Invoices in AP:

1. Standard: An invoice from a supplier representing an amount due for goods or services purchased (standard invoices can be matched to either a PO or receipt, or not matched)
2. Credit Memo: A memo from a supplier representing a credit amount toward goods or services for which you have already been invoiced
3. Debit Memo: An invoice you enter to record a credit for a supplier who does not send you a credit memo
4. Expense Report: An invoice representing an amount due to an employee for business-related expenses
5. Prepayment: A type of invoice you enter to pay an advance payment for expenses to a supplier or employee
6. Withholding Tax Invoices: This is the invoice created on an original invoice to withhold certain amount from the original invoice and pay this amount to a 3rd party (usually Government) later.
7.  Recurring Invoices: for fixed amount and fixed duration, we will select recurring

             invoices. Like rent accounts and so on.

     8.    Mixed Type Invoice: for company miscellaneous expenses we will create mixed

            Type.

     9.    Quick Match Invoice: we will match the invoice either with PO or invoice with

           receipt, as the matching payments will be done.

   

    10.  PO Default: we will give the PO Number as per the PO amount invoice will be

           Generated.

  

How to create the invoices:

Go to Invoices -> Invoices

Note: By Default the payment option is check.

You can check the Invoice details from backend

select * from ap_invoices_all where invoice_id = &Invoice_Id

OR

select * from ap_invoices_all where invoice_num='&Invoice_Num'

You can find the invoice_id for the invoice you have created from the front end

Go to the invoice screen and go to Help->Diagnostic->Examine

Then In Examine-Field-Prompt type Invoice Id then it will give you the invoice id of the screen shown.

Next step.

Distributions -> Enter the type and amount

This distribution is nothing but the total amount of items listed here and which should be equal to the header’s amount. In distribution we can list all of items with the respective amount

We can see the distribution of our items in the below query:

select * from ap_invoice_distributions_all where invoice_id = &Invoice_id

Now we need to validate out invoices.

Go to the action tab Actions....1 tab in the invoices screen(Header screen) and check the validate check box and then click OK.

To remove the withholding tax:

need to go to the supplier and go to the withholding tab à Then search for your supplier and uncheck the withholding box, then it will not show you again.

Note: If you don’t want to allow the withholding tax then we need to go to the supplier and search for our supplier and then need to go in the invoices and withholding tax tab and then uncheck the tab…..similarly we can do this for the sites tab in the same screen.

If we do some validation/re-validation, then one record will be created into “ap_accounting_events_all” table

select * from ap_accounting_events_all where source_id= &Invoice_id

Note:

Source_id is ‘invoice_id’ in case of Invoices and ‘check_id’ in case of Payments.

If we give some wrong inputs in “Distribution” screen as shown in the below screenshot:

Here our total invoice is for rupees 200 and again we are adding here 100 rupees more i.e this distribution amount is exceeding the total invoice amount. So we need to validate it again and when we revalidate one extra record will be added in ap_accounting_events_all with the column “EVENT_TYPE_CODE” as “INVOICE ADJUSTMENT” as seen in the below query:

select * from ap_accounting_events_all where source_id= &Invoice_Id

To reverse this transaction we need to click on the reverse 1 tab then it will add one more record, which will be the reverse of the previous distribution line.

Note: After any addition or deletion in the item list or in accounting we need to re-validate our transaction.

After this we need to validate.

Now we need to pay for our Invoices: PAYMENTS

Go to Payables –> Payments -> Payments

When we want to create a single payment, rather than an entire payment batch, we either record a manual payment we generated outside of Payables, or we can create a Quick payment (single computer generated payment).

Then click to the click “Enter/Adjust Invoice”

Then go to the Invoice Number and query for your invoice:

Now take the check id from the query :

select * from ap_invoice_payments_all where invoice_id = &Invoice_id

and put this check id in the below query :

select * from ap_checks_all where check_id = &check_id

ACCOUNTING:

We need to do the accounting:
Go to the Invoices à Invoices

Search for your invoice Num –> Dhee_Ballu

Go To Actions…1 then check the Create Accounting Box:

2^nd Method:

Go to the View -> Request
Then search for the Reports as “Payables Accounting Process”



Now give the account “From date” and account “To date”:

Then Click on OK and submit the request.

Select * from ap_accounting_events_all where source_id= &invoice_id

to get the “ACCOUNTING_EVENT_ID” here it is (78350 78351)
Go and check these entries in the ap_ae_headers_all

Select * from ap_ae_headers_all where ACCOUNTING_EVENT_ID IN (78350,78351)

Now take AE_HEADER_ID from the ap_ae_headers_all (above query) and check in the ap_ae_lines_all

Select * from ap_ae_lines_all where AE_HEADER_ID IN (74631,74632)

Now we need to put all our transaction to the General Ledger, which is the common repository of all the modules that finally gives the info about the profit and loss / balance sheet.

We need to run the concurrent program for transferring the accounting into the GL and the name of the request is “Payables Transfer to General Ledger” so query for this and

We need to enter the following as mandatory parameter

From date: e.g sysdate

To date: e.g Sysdate

Submit Journal Import: Yes

Transfer to GL Interface: In Detail

Important tables in AP modules

 Invoice Table-

·         ap_invoices_batches_all

·         ap_invoices_all

·         ap_invoce_lines_all

·         ap_invoice_distributions_all

Supplier Table 11i	Supplier Table R12
po_vendors po_vendor_sites_all po_vendor_contacts	ap_suppliers ap_supplier_sites_all ap_supplier_contact

Payment Table

·         ap_invoice_payments_all

·         ap_inv_selection_criteria_all

·         ap_checks_all

Accounting Table

·         ap_accounting_events

·         ap_ae_headers_all

·         ap_ae_lines_all

Payment terms table

·         ap_terms

·         ap_terms_lines

Hold_Tables

·         ap_holds_all

·         ap_hold_lines

·         ap_realease

Tolerance table

·         ap_tolrance.

Reporting Table

·         ap_reporting_ntities

·         ap_reporting_entities_lines

Distribution Set Table

·         ap_distribution_sets

·         ap_distribution_set_lines_all

Bank table

·         ap_bannks_branches_all

·         ap_bank_accounts_all

·         ap_bank_account_uses_all

AR Module

The process flow in Oracle Receivables is as follows:

• Entering Transactions (Manual or import)
• Creating receipts.
• Applying receipts to transactions.
• Adjustments/write-offs,if any.
• Posting.
• Reporting.

Setting up Oracle Receivables

•         Pre-requisites

• Define Set of Books (Required)

                        Assign your Set of Books to a Responsibility

                        Define your Accounting Flexfield

                        Define your Calendar Period Types

                        Define your Calendar Periods

  Define your Currency

• Define Organizations (Required)

                        Define at least one organization to use Receivables.

• Define Key Flexfields (Required)

                        Define Territory and Sales Tax Location Flexfield Structure

•         Customer and Customer Sites.

• A customer is an organization or person with whom you have a selling relationship.
• A customer address is a party site used in the context of a customer account for billing, shipping, or other purposes.
• Can maintain separate customer profiles, addresses, and contacts for each customer account.

Defining Customer and Customer Addresses

•         There are two windows:

•         Customer – Standard window

•         Customer Addresses window

Transactions

•          Transaction Classes

•         Invoice

•         Credit Memo

•         Debit Memo

•         Chargeback

•         Guarantee     

•         Deposit                      

•         Transaction Sources (defined by the users)

•         Manual Invoice

•         PROJECTS INVOICES

•         PA Internal Invoice

•         Conversion etc.

•         Entering Transactions

• Can be entered in two ways : manually or imported (from other Oracle modules like PA or legacy system)
• Has to be entered in a open or future period.
• GL Date should be in a open period.
• Customer should have at least one Bill-To site defined.

Creating Receipts

 Two types of receipts in Receivables:

·         Cash receipts: Payment (such as cash or a check) that you receive from your customers for goods or services.

·         Miscellaneous receipts: Revenue earned from investments,interest, refunds, and stock sales.

·         Enter receipts and apply them to transactions in either Open or Future accounting periods.

·         Can apply receipts to invoices, debit memos, deposits, on–account credits, and chargebacks.

·         Can partially or fully apply a receipt to a single debit item or to several debit items.

·         If the transaction type does not allow overapplication, then you cannot enter an amount that would reverse the sign of the debit item.

·         If the transaction type specifies Natural Application only, then you must enter an amount that brings the balance due closer to zero.

·          

     Receipt Application Types

• On-account Receipts - Payments where you intentionally apply all or part of the payment amount to a customer without reference to a debit item. On–account examples include prepayments and deposits.
• Unapplied Receipts - The status of a receipt for which you can identify the customer,but you have not applied or placed on account all or part of the payment.
• Unidentified – This is a receipt that has been entered, but not identified to a particular customer.
• Applied – This is a receipt that has been applied to a particular transaction.

Receipt Status

A receipt can have one of the following statuses:

• Approved: This receipt has been approved for automatic receipt creation. This status is only valid for automatic receipts.
• Confirmed: For manually entered receipts, this status indicates the receipt belongs to a receipt class that requires remittance. For automatic receipts, this status indicates the receipt has been confirmed.
• Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.
• Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.
• Reversed: This receipt has been reversed. You can reverse a receipt when your customer stops payment on a receipt, if a receipt comes from an account with non–sufficient funds or if you want to re–enter and reapply it in Receivables. You can reverse cash receipts and miscellaneous receipts.

Table that holds AR Invoice data

The following tables can give most of the invoice information.

• RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
• The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
• RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
• RA_CUSTOMERS - Customer information
• RA_CUST_TRX_TYPES_ALL - Customer Transaction Type
• AR_PAYMENT_SCHEDULES_ALL
• RA_CUSTOMER_TRX_LINES_ALL - Transaction Line information
• MTL_SYSTEM_ITEMS - Base table for item

The ER Diagram for a customer Transaction can be easily understood as:

Customer Payment

These are the main tables which hold Customer Payment information

·         AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.

·         AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.

·         Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.

·         AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.

·         AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.

·         AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.

·         Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:

Accounting Link between the tables

For Invoice:

ra_customers (This is for capturing customers information)

ra_customer_trx_all,

ra_cust_trx_types_all,

ar_payment_schedules_all,

ra_customer_trx_lines_all

and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .

ra_cust_trx_types_all.type IN ('INV')

For the Receipts:

ar_receivable_applications_all,

ra_customer_trx_all,

ra_customer_trx_lines_all,

ar_cash_receipts_all

ar_receivable_applications_all.application_type = 'CASH',

ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,

ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id