R12 Accounts Payable Useful Queries

Get Invoice Details

select aia.invoice_id, aia.invoice_num,gcc.segment1,gcc.concatenated_segments,aia.creation_date, aia.invoice_date,aia.pay_group_lookup_code,aia.source,aia.*
aia.org_id,hou.name,aia.source, to_char(aia.creation_date, 'MM-YYYY'),aia.payment_method_code,aia.invoice_currency_code,aia.pay_group_lookup_code,aia.invoice_num,gcc.segment1,gcc.concatenated_segments
from
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.gl_code_combinations_kfv gcc
where
aia.invoice_id = ail.invoice_id
and aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aia.org_id = <ENTER ORG ID>
and aid.dist_code_combination_id = gcc.code_combination_id
--and gcc.segment1 = '<ENTER SEGMENT 1>'
--and aia.source = '<ENTER SOURCE>'
and aia.cancelled_date is null
and aia.payment_status_flag = 'Y'
and aia.invoice_num in ('<ENTER INVOIC NUMBER>')
and aia.creation_date >trunc(sysdate-10) -- For invoices created in some time in past

;




Get the Invoice and Payment Details (For Paid Invoices)

select
aia.org_id,aia.invoice_id,aia.vendor_id,aca.check_number, aia.invoice_num,aia.invoice_amount,aia.amount_paid,aia.discount_amount_taken, aca.amount "payment_amount",gcc.concatenated_segments,aia.creation_date, aia.invoice_date, aia.payment_method_code, aba.bank_account_name,aca.check_id
from
apps.ap_invoices_all aia,
apps.ap_invoice_lines_all ail,
apps.ap_invoice_distributions_all aid,
apps.gl_code_combinations_kfv gcc,
apps.gl_code_combinations_kfv gcc1,
apps.ap_invoice_payments_all aip,
apps.ap_checks_all aca
,apps.ce_bank_accounts aba
,apps.CE_BANK_ACCT_USES_ALL abu
,apps.hr_operating_units hou

where
aia.invoice_id = ail.invoice_id
and aia.invoice_id = aid.invoice_id
and ail.line_number = aid.invoice_line_number
and aip.invoice_id = aia.invoice_id
and aip.check_id = aca.check_id
and aid.dist_code_combination_id = gcc1.code_combination_id
and aca.CE_BANK_ACCT_USE_ID = abu.BANK_ACCT_USE_ID
and abu.bank_account_id = aba.bank_account_id
and aia.org_id = hou.organization_id
and aia.org_id = <ORG_ID>
and aba.asset_code_combination_id = gcc.code_combination_id
and aia.creation_date >trunc(sysdate-90)
--and gcc.segment1 = <COMPANY>

--and aia.invoice_currency_code = <CURR>
--and aia.payment_method_code = <PAYMENT METHOD>
--and aia.vendor_id in (<ENTER VENDOR IDs>)
and aia.cancelled_date is null
and aia.payment_status_flag = 'Y'
order by aia.org_id, to_char(aia.creation_date, 'MM-YYYY')
;

To Find Invoices on Hold

select aha.* from apps.ap_invoices_all aia,
apps.ap_holds_all aha
where
aia.invoice_id = aha.invoice_id
and aia.org_id = <Enter ORG ID>
and aha.hold_lookup_code = <ENTER HOLD CODE> -- Holds Codes can be found in table : AP_HOLD_CODES
--and aia.VENDOR_SITE_ID in (<ENTER VENDOR SITE ID>)
;

Holds Codes can be found in table: AP_HOLD_CODES

Query to Get Bank account details

select cba.bank_account_type,cbb.bank_name,cba.*
from
apps.ce_bank_accounts cba,
apps.cefv_bank_branches cbb,
apps.CE_BANK_ACCT_USES_ALL cbu
where
cba.bank_branch_id = cbb.bank_branch_id
and cba.bank_account_id = cbu.bank_account_id
AND ( cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE) )
;

Bank account and payment template analysis

SELECT xep.name                                       legal_entity_name, 

       ou.name                                        "Operating Unit", 

       cba.bank_account_name                          "Bank Account Name", 

       cba.bank_account_num                           "Bank Account Number", 

       NVL(cba.attribute1, cba.currency_code)         currency, 

       cba.multi_currency_allowed_flag                "Multi Currency Flag", 

       cba.account_classification                     "Account Classification", 

       bb.bank_name                                   "Bank Name", 

       bb.bank_branch_type                            "Bank Branch Type", 

       bb.bank_branch_name                            "Bank Branch Name", 

       bb.bank_branch_number                          "Bank Branch Number", 

       bb.eft_swift_code                              "Swift Code", 

       bau.ap_use_enable_flag                         bank_setup_ap_use_flag, 

       bau.ar_use_enable_flag                         bank_setup_ar_use_flag, 

       cba.iban_number,

       gcf.concatenated_segments       "ASSET GL Code Combination", 

       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, apg.vendor_pay_group|| 

                     ',')) .extract('//text()'), 

                       ',') 

        FROM   (SELECT DISTINCT apg.vendor_pay_group, 

                                bank_account_id 

                FROM   apps.ap_payment_templates apt, 

                       apps.ap_pay_group apg 

                WHERE  1 = 1 

                   AND apg.template_id = apt.template_id 

                   AND NVL(apt.inactive_date, SYSDATE + 1) > SYSDATE) apg 

        WHERE  bank_account_id = cba.bank_account_id) used_in_ap_pay_groups, 

       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, arm.name|| CHR(10)|| 

                     ',')) .extract('//text()'), ',') 

        FROM   apps.ar_receipt_method_accounts_all arma, 

               apps.ar_receipt_methods arm 

        WHERE  arm.receipt_method_id = arma.receipt_method_id 

           AND arma.remit_bank_acct_use_id = bau.bank_acct_use_id) 

                                                      used_in_ar_recipt_mthods 

FROM   apps.ce_bank_accounts cba, 

       apps.ce_bank_acct_uses_all bau, 

       apps.cefv_bank_branches bb, 

       apps.hr_operating_units ou, 

       apps.xle_entity_profiles xep, 

       apps.gl_code_combinations_kfv gcf 

WHERE  cba.bank_account_id = bau.bank_account_id 

   AND cba.bank_branch_id = bb.bank_branch_id 

   AND ou.organization_id = bau.org_id 

   AND cba.asset_code_combination_id = gcf.code_combination_id 

   AND ( cba.end_date IS NULL 

          OR cba.end_date > TRUNC(SYSDATE) ) 

   AND ou.default_legal_context_id = xep.legal_entity_id 

ORDER  BY ( cba.bank_account_num ); 

LINK PAYMENT AND INTERNAL BANK ACCOUNT

select 

aisc.checkrun_name,

apt.template_name,

apt.payment_method_code,

app.system_profile_code,

--api.payment_currency_code,

cb.bank_name,

cba.bank_account_name,

cba.currency_code,

cba.multi_currency_allowed_flag,

cba.payment_multi_currency_flag,

cba.zero_amount_allowed,

from

APPS.AP_INV_SELECTION_CRITERIA_all aisc,

apps.ap_checks_all aca,

apps.ap_payment_templates apt,

apps.IBY_ACCT_PMT_PROFILES_B app,

apps.ce_bank_accounts cba,

apps.cefv_bank_branches cb

where 1=1

and aca.checkrun_id= aisc.checkrun_id

and aisc.template_id = apt.template_id

and apt.payment_profile_id = app.payment_profile_id

--and app.payment_profile_id = api.payment_profile_id

and aisc.bank_account_id = cba.bank_account_id

and cba.bank_branch_id = cb.bank_branch_id

--and aisc.org_id = api.org_id

--and app.system_profile_code = <PAYMENT PROCESS PROFILE>

and aca.check_id in (<ENTER CHECK ID>)

The Trial Balance and its Significance in the Accounting Process

The Trial Balance and its Significance in the Accounting Process

Trial Balance is a list of closing balances of ledger accounts on a certain date and is the first step towards the preparation of financial statements. It is usually prepared at the end of an accounting period to assist in the drafting of financial statements. The report is primarily used to ensure that the total of all debits equals the total of all credits, which means that there are no unbalanced journal entries in the accounting system. Ledger balances are segregated into debit balances and credit balances. If all accounting entries are recorded correctly and all the ledger balances are accurately extracted, the total of all debit balances appearing in the trial balance must equal to the sum of all credit balances.

Trial balance ensures that for every debit entry recorded, a corresponding credit entry has been recorded in the books in accordance with the double entry concept of accounting. If the totals of the trial balance do not agree, the differences may be investigated and resolved before financial statements are prepared.

Ledger accounts are closed at the end of each accounting period by calculating the totals of debit and credit sides of a ledger. The difference between the sum of debits and credits is known as the closing balance. This is the amount which is posted in the trial balance


If there are subsidiaries in an organization that report their results to a parent company, the parent may request an ending trial balance from each subsidiary, which it uses to prepare consolidated results for the entire company.


Types of Trial Balance

Un-Adjusted trial balance
When the trial balance is first printed, it is called the un-adjusted trial balance.

Adjusted trial balance
Then, when the accounting team corrects any errors found and makes adjustments to bring the financial statements into compliance with an accounting framework (such as GAAP or IFRS), the report is called the adjusted trial balance.

Post-closing trial balance
The adjusted trial balance is typically printed and stored in the year-end book, which is then archived. Finally, after the period has been closed, the report is called the post-closing trial balance.


Trial Balance Format
The initial trial balance report contains the following columns:
1. Account number
2. Account name
3. Ending debit balance (if any)
4. Ending credit balance (if any)
Each line item only contains the ending balance in an account. All accounts having an ending balance are listed in the trial balance;


Limitations of a trial balance
Trial Balance only confirms that the total of all debit balances match the total of all credit balances. Trial balance totals may agree in spite of errors. An example would be an incorrect debit entry being offset by an equal credit entry. Likewise, a trial balance gives no proof that certain transactions have not been recorded at all because in such case, both debit and credit sides of a transaction would be omitted causing the trial balance totals to still agree. Types of accounting errors and their effect on trial balance are more fully discussed in the section on Suspense Accounts.

How to prepare a Trial Balance
Following Steps are involved in the preparation of a Trial Balance:
1. All Ledger Accounts are closed at the end of an accounting period.
2. Ledger balances are posted into the trial balance.
3. Trial Balance is prepared and errors are identified.
4. Erred Entries may be posted to Suspense Account Unless Approrpiate Rectification is identified.
5. Errors identified earlier are rectified by posting corrective entries.
6. After the Posting the Adjustments are incorporated in Traila balance. They can be specifically noted in order to highlight the Adjustments.

Important Interview Question - Oracle Accounts Payables

Can you give a sample Process Flow for Procure to Pay Cycle?

Ans) Process flow for Procure to pay will go through two departments
(Commercial & Finance)
Procure - Commercial Department The following steps involve to procure any item
1. Received Requisition from concern Department
2. Request for Quotation from Suppliers at least three
3. Finalize the best Quotation by keeping in mind about our companies standard
4. Check the Budget for the same
5. Negotiate with supplier for more economic pricing and finalize the payment terms
6. Process the PO and forward to the supplier to supply the goods and services 

Pay Cycle - Finance Department
The following steps need to be fulfil
1. Invoice should be match with PO
2. Invoice should has all the supporting documents such as PO copy,Delivery note duly signed by reciever (our staff who authorized to received goods / store keeper)
3. If the invoice is for services then it should be forwarded to the concern department head or project manager for his confirmation of work done and his approval
4. Even if it not the services invoice, it should forwarded to the concern person's approval who request the PO for the same
5. Finance can reject the invoice if it is not budgeted and ask for the reasons.
6. After receiving all the confirmation and approvals from the concern department heads the invoice will be update in to the accounting system first in order to avoid any duplication of Invoice and PO (it shown on accounting package if the invoice is duplicate if not, at least it tells you if the PO already used or cancel)
7. Finance approved the invoice and process the payment base on payment terms with the supplier.

Explain about Oracle Accounts Payable Application. 
Ans)The Accounts Payable application component records and manages accounting data for all
vendors. It is also an integral part of the purchasing system: Deliveries and invoices are
managed according to vendors. The system automatically triggers postings in response to the
operative transactions. In the same way, the system supplies the Cash Management application
component with figures from invoices in order to optimize liquidity planning. 

How many types of purchase order types/agreements are there?
 
A) Standard Purchase Order: You generally create standard purchase orders for one-time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions. If you use encumbrance accounting, the purchase order may be encumbered since the required information is known

B) Planned PO : A planned purchase order is a long-term agreement committing to buy it
items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities and estimated cost.
EX: Buying goods for Christmas from a specific dealer.

C) Contract PO : You create contract purchase agreement with your supplier to agree on specific terms and conditions without indicating the goods and services that you will be purchasing i.e. for $ amount you must supply this much quantity. You can later issue standard PO referencing your contracts and you can encumber these purchase orders if you use encumbrance accounting.

D) Blanket PO : You create blanket purchase agreements when you know the detail of goods or services you plan to buy from a specific supplier in a period , but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.
A Blanket Purchase Agreement is a sort of contract between the you and ur supplier about the price at which you will purchase the items from the supplier in future. Here you enter the price of the item not the quantity of the items. When you create the release you enter the quantity of the items. The price is not updatable in the release. The quantity * price makes the Released Amount. Now suppose your contract with your supplier is such that you can only purchase the items worth a fixed amount against the contract. 

What is a Payment Method, what are different types?


A funds disbursement payment method is a medium by which the first party payer, or deploying company, makes a payment to a third party payee, such as a supplier. You can use a payment method to pay one or more suppliers. Oracle Payments supports several payment methods for funds disbursement, including the following:

·        Check

·        Electronic

·        wire

·        Clearing

Check:

You can pay with a manual payment, a Quick payment, or in a payment batch.


Electornic:

Electronic An electronic funds transfer to the bank of a supplier.You create electronic payments either through the e- Commerce Gateway, or by delivering a payment batch file to your bank. For both methods, Payables creates a file during payment batch creation. If you are using the e-Commerce Gateway to create the file of payments, an EDI translator is required to create the EDI Formatted file prior to delivering it to your bank.For electronic funds transfers, the file is formatted and delivered to your ap.out directory for delivery to your bank.

Wire:

Wire Funds transfer initiated be contacting the bank and requesting wire payment to the bank of a supplier. A payment method where you pay invoices outside of Payables by notifying your bank that you want to debit your account and credit your supplier’s account with appropriate funds. You provide your bank with your supplier’s bank information, and your bank sends you confirmation of your transaction. Your supplier’s bank sends your supplier confirmation of the payment. You then record the transaction manually.

Clearing:

Clearing Payment for invoices transferred from another entity within the company without creating a payment document.Payment method you use to account for intercompany expenses when you do not actually disburse funds through banks. You do not generate a payment document with the Clearing payment method. When you enter the invoice, you enter Clearing for the payment method.You can record a Clearing payment using a Manual type payment only. 

What do you mean by pay through date?

Pay Through Date. Payables selects all approved and unpaid invoices that have a due date on or before the Pay Through Date. You cannot update this field after invoice selection for a payment batch

What are different status in payment batch?

ANS – Status  (Payment Batches window only). Payables displays the status of the payment batch. Payables displays the status in red if there is an error, for example, if the concurrent manager goes down during a process.

1.     Selecting : Payables in Identifying the invoices which needs to be selected for payment on the basis of Criteria defined in Payment template

2.     Building. Payables is determining which invoices will be paid by each payment document.

3.     Built. Payables has determined which invoices will be paid with each payment document. You can now review the Preliminary Payment Register, Modify the Payment Batch, or Format the Payment Batch.

4.     Cancelled. You have cancelled the payment batch.

5.     Cancelling. Payables is cancelling the payment batch.

6.     Confirmed. You have confirmed the payment batch.

7.     Confirming. Payables is either confirming or partially confirming the payment batch based on the action you selected in the Confirm Payment Batch window.

8.     Formatted. Payables has completed formatting your payments and has created the output file that you can use to print checks or, if you are making electronic payments, you can deliver the output file to the e-Commerce Gateway or your bank for processing.

9.     Formatting. Payables has created the output file that you can use to print checks or, if you are making EFT payments, you can deliver the output file to your bank for processing.

10. Modified. Payables has modified the payment batch based on the modifications you made in the Modify Payment Batch window.

11. Modifying. Payables is modifying the payment batch based on the modifications you made in the Modify Payment Batch window.

12. Rebuilding. You have modified a payment batch, and Payables is rebuilding the modified payment batch.

13. Restarting. You have confirmed a partial payment batch and have chosen Restart Payment Batch in the Confirm Payment Batch window. Payables is rebuilding and reformatting the remaining portion of the payment batch.

14. Selected. Payables has selected invoices that match the payment batch criteria you entered.

15. Unstarted. The payment batch is unstarted.

 Explain The concept of Automatic Offset?

If you enter invoices for expenses or asset purchases for more than one balancing segment, you might want to use Automatic Offsets to keep your Payables transaction accounting entries balanced.
If you do not use Automatic Offsets, Payables creates a single liability accounting entry for invoice transactions (if you use accrual basis accounting) and a single cash type accounting entry for payment transactions.
When you use Automatic Offsets, Payables automatically creates balancing accounting entries for your transactions. The GL account that each of the offsetting accounting entry is charged to depends on which method you use, Balancing or Account:

·                      Balancing. Payables builds the offsetting GL account by taking the balancing segment (usually the cost center code) from the invoice distribution and overlaying it onto the appropriate default GL account, for example the Liability account from the supplier site.

·                      Account. The Account method takes the opposite approach with one segment (the designated account segment) being retained from the default GL account and all other segments being retained from the invoice distribution.

What is an ERS (Pay on Receipt) ? How is it setup?

Payment on Receipt enables you to automatically create standard, unapproved invoices for payment of goods based on receipt transactions. Invoices are created using a combination of receipt and purchase order information, eliminating duplicate manual data entry and ensuring accurate and timely data processing. Payment on Receipt is also known as Evaluated Receipt Settlement (ERS) and Self Billing.

You can automatically create invoices with multiple items and distribution lines, and include tax.

You define which supplier sites participate in Payment on Receipt and enforce matching rules to ensure the proper payments are made to the suppliers.

Amount – Payment on Receipt builds invoices with the following information: Determined by multiplying the Quantity received by the Purchase Order Item Unit Price.

Payment Terms  - Defaulted from the purchase order payment terms or from the supplier site payment terms, depending on your Oracle Public Sector Payables setup.

Tax  - Based on Tax Codes on each purchase order shipment, or the default tax hierarchy in Payables.

If the purchase order currency and the supplier site Payment Currency (in the Supplier Sites window) are not fixed–rate currencies (for example, not euro–related currencies), Payment on Receipt builds the invoices this way, regardless of the supplier site Invoice Currency:

Invoice Currency – Defaulted from the purchase order Currency.

Payment Currency – Defaulted from the purchase order Currency.

What is the relevance of Withholding tax group?

Use this window to define withholding tax groups that include multiple Withholding Tax type tax codes. You can assign the same tax code to more than one group. When you assign a withholding tax group to an invoice or distribution, Payables calculates invoice withholding tax based on every tax code in the withholding tax group. For example, you assign a withholding tax group to an invoice or distribution if you need to withhold taxes at both the local and country level, each withheld at different rates and remitted to different tax authorities. You define and assign to the invoice or distribution a Withholding Tax Group that includes both taxes.

You rank all of the tax codes in a withholding tax group when you define the group. When you enter an invoice and enter a withholding tax group, Payables calculates the taxes in order of rank. Lower ranked taxes are applied to the amount of the invoice or distribution amount less the previous withholding tax amounts.

What are different rate structure for Withholding Tax ?

Period Limit. After you pay a certain amount for a withholding tax in a period, Payables does withhold further taxes. For example, for each special calendar period, Payables withholds no more than $10,000.

If you select this value you must enter values for the Period Limit, and Calendar fields. You cannot enter values for the Amount Basis and Period Basis fields.

Flat Rate. The withholding tax has no amount or period limits. If you select this value you cannot enter a value in the Amount Basis, Period Basis, and Period Limit fields.

Amount Ranges. The tax rate depends on how much you have already paid during a time period. Base the paid amount on either the gross amount of total paid invoice amounts, or on the total amount of tax withheld. The time period can be per withholding tax calendar period or per invoice. For example, define a tax that for each invoice that withholds at a rate of 10% until you have paid $1000 in tax, after which it withholds at 15%. If you select this value you must enter values for the Amount Basis and Period Basis fields. If you select Period as your Period Basis, you must also select a Calendar. You cannot enter a value for Period Limit.

What is Prepayment and what are different type of Prepayments?

Pre-payment invoice are used when there is advance payment made to Suppliers against purchases or Employees for Travel expenses (Impress amount).

Pre payments are two types:

    1.Temporary pre-payment

    2.Permanent pre-payment.

Temporary pre payments are adjusted against the future purchase invoice. Whereas we cannot adjust Permanent pre payments against future purchases. This payment we can receive when the contract cancelled with the supplier. We can convert Permanent pre-payment into Temporary pre-payment. After conversion we can use that to adjust against future invoices.

Permanent - which is used for long term deposit. Ex:-Fixed deposit, Term deposit

Temporary-which is used for short term advance. Ex:-Advance to supplier

Can we automatically ‘Close’ the Purchase order without receiving the full quantity?

The Receipt Close Tolerance lets you specify a quantity percentage within which Purchasing closes a partially received shipment. For example, if your Receipt Close Tolerance is 5% and you receive 96% of an expected shipment, Purchasing automatically closes this shipment for receiving.

What is 2-way, 3-way, 4-way matching?

Oracle Payables shares purchase order information from your purchasing system to enable online matching with invoices. Invoiced or billed items are matched to the original purchase orders to ensure that you pay only for the goods or services you ordered and/or received.

Two–Way: Purchase order and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Three–Way: Purchase order, receipt, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

Four–Way: Purchase order, receipt, accepted, and invoice quantities must match within tolerance before the corresponding invoice can be paid.

What is the difference between 'Accrue On Receipt' and 'Accrue at Period End'?

A: Accrue On Receipt means that when a receipt is saved, accrual transactions are immediately recorded and sent to the general ledger interface. This is also known as "online" accruals. Accrue at Period End means that when a receipt is saved, the accrual transactions are not immediately recorded and sent to the general ledger; instead, the accounting entries are generated and sent at the end of the month by running the Receipt Accruals - Period-End Process.

All items with a destination type of either Inventory and Outside Processing are accrued on receipt. For items with a destination type of Expense, you have the option of accruing on receipt or at period end.

General Ledger - Accounts Payables Drill Down - Data Base

General Ledger - Accounts Payables Drill Down - Data Base Query


As Part of Regular GL , Accounts Payables Designing and Troubleshooting, It is required to have a good Understanding of the Links between Various General Ledger and Accounts Payables Table involving Sub-ledger accounting tables, Below is a select statement which provides you the link between GENERAL LEDGER, ACCOUNTS PAYABLES AND  SUB-LEDGER ACCOUNTING Data base Tables

select --distinct aia.invoice_num
GL.NAME,GJH.JE_HEADER_ID,GJH.JE_CATEGORY,GJH.JE_SOURCE,GJH.NAME,GJH.STATUS, GLL.JE_LINE_NUM,GCC.CONCATENATED_SEGMENTS,XAH.GL_TRANSFER_STATUS_CODE,XDL.ROUNDING_CLASS_CODE,XDL.APPLIED_TO_ENTITY_CODE
,AIA.INVOICE_ID,AIA.INVOICE_NUM,AIA.INVOICE_AMOUNT,AIL.LINE_NUMBER,AIL.LINE_TYPE_LOOKUP_CODE,AIL.AMOUNT,GL.CURRENCY_CODE "MRC CURRENCY",GLL.ACCOUNTED_DR,GLL.ACCOUNTED_CR
--,8,GIR.*,8,XAH.*,8,XAL.*,8,XDL.*
from
APPS.GL_JE_HEADERS GJH,
APPS.GL_JE_LINES GLL,
APPS.GL_CODE_COMBINATIONS_KFV GCC,
APPS.GL_IMPORT_REFERENCES GIR,
APPS.GL_LEDGERS GL,
APPS.XLA_AE_LINES XAL,
APPS.XLA_AE_HEADERS XAH,
APPS.XLA_DISTRIBUTION_LINKS XDL,
APPS.AP_INVOICE_DISTRIBUTIONS_ALL AID,
APPS.AP_INVOICE_LINES_ALL AIL,
APPS.AP_INVOICES_ALL AIA
WHERE 1=1
AND GJH.LEDGER_ID = GL.LEDGER_ID
AND GJH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GJH.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND AID.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AIA.INVOICE_ID
AND AIL.INVOICE_ID = AID.INVOICE_ID
AND AIL.LINE_NUMBER = AID.INVOICE_LINE_NUMBER
AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AID.INVOICE_DISTRIBUTION_ID
AND TRUNC(GJH.CREATION_DATE) > TRUNC(SYSDATE-25)
AND UPPER(GJH.JE_SOURCE) LIKE UPPER('Payable%')
AND AIA.INVOICE_NUM LIKE 'EXP360319%'
AND GL.LEDGER_CATEGORY_CODE NOT IN ('ALC','SECONDARY')
order by aia.invoice_num;

Accounts Payables Accounting Flow

Accounts Payables Accounting Flow

Below are few of the key concepts about Accounts Payables Accounting. Accounts Payables have multiple Flows which will be elaborated below. Even though the accounting engine has been moved to Sub Ledger accounting in r12, the Core Concepts of Payables accounting remain same.

Standard Accounts Payable Invoice/ Payment Accounting

One of the most commonly seen scenarios in Accounts payables is Standard Invoice and Payment. Below are the accounting entries for them

Standard invoice creation:

               Dr. Expense / Item Expense / Misc. Expense

                              Cr. Supplier / Liability

The “Expense / Item Expense / Misc. Expense” are derived from the Payables Invoice Distribution where as “Supplier / Liability” Account id derived from Supplier’s Site, from the liability account code combination on that particular site. Although the liability account is defaulted from Payables’ Financial Setup, but you can change the account code according to your need. We can even change the liability account code combination on the Invoice Workbench by enabling the Column from Folders option.

Payment of the Standard invoice:

               Dr. Supplier / Liability

                              Cr. Bank / Cash / Cash Clearing

Now comes the payment, the liability account is fetched from the supplier whose invoice is being paid, the cash clearing or cash account is fetched from the bank you select during the payment. This account is defined during the Bank Account Setup.

PREPAYMENTS / ADVANCES:

Prepayment Invoice creation:

Dr.  Prepaid Expense / Advance Paid

               Cr. Supplier / Liability

Prepayment invoice Payment:

               Dr. Supplier / Liability

                              Cr.  Bank / Cash / Cash Clearing

Standard Invoice creation:

               Dr. Expense / Item Expense / Misc. Expense

                              Cr. Supplier / Liability

Prepayment Application to Standard invoice

               Dr. Supplier / Liability

               Cr. Prepaid Expense / Advance Paid

INVOICES with “TRACK AS ASSETS”:

Track as Asset” is a functionality for moving the items from Oracle Payables to Oracle Assets. It is a check box on the Invoice Line TAB and can be enabled on Distribution Line using the Folder View option. When you check this box and run the “Mass Addition Create Report” from Payables, the items on invoice line or distribution line moves to Oracle Assets. 

Invoice creation:

Dr. Asset Clearing
                              Dr. Supplier / Liability

Invoice transferred to oracle Assets:

               Dr. Asset

                              Cr. Asset Clearing

INVOICE with WITHHOLDING TAX

In the Payables invoices with Withholding tax Scenarios, accounting entry for the WHT payables or Liability account is selected from the supplier defined as a Tax Authority. The WHT expense is picked from the WHT setup.

Taking an example of withholding at time of Payment

Invoice Creation:

Dr. Expense / Item Expense / Misc. Expense

                              Cr. Supplier / Liability

Payment with Withholding Tax 

               Dr. Supplier / Liability

                              Cr. Bank / Cash / Cash Clearing

                              Cr. Withholding Tax

WITHHOLDING TAX INVOICE

               Dr. WHT Expense

                              Cr. Withholding Liability

Legal Entity in R12

Legal Entity:

Legal Entity represents a legal company for which Fiscal and Tax reports are prepared. A Legal Entity can Model to an Enterprise, a Designated Legal unit which is recognized by legal authorities in a country. Legal entity is a business entity that is known to exist to the outside world. Internal organizations or divisions are not legal entities themselves, but are just part of it. In the Multi Organization structure various legal entities could share the same ledger. Both legal entities and operating units are associated with the ledger and the relationship between the legal entity and operating unit is derived based on the ledger.

A legal Entity can be associated to Single Ledger, Multiple Legal Entities can be Associated to a Ledger.

A Legal Entity can be defined using two different Navigations.

• Legal Entity pages of Accounting Setup Manager
• Legal Entity pages in Legal Entity Configurator

The only difference between the above two is that Balancing Segment Value can be assigned to legal entities using Accounting Setup Manager.

How Legal Entity Is Mapped to Different Functionalities in Oracle Application R12:

• Accounting Structures --> Legal Entity is mapped to Balancing Segment Values and Ledgers
• E Business Tax --> Legal entity is used to identify the Jurisdiction and the parties and where they are registered or located to determine the Tax applicability
• Bank Accounts --> In Oracle Applications R12 Legal Entity is the Owner of the particular bank account be it a Internal or Remittance bank account. And can be used by The Operating Unit which shares the same ledger as that of the Owning Legal entity.
• Payables and Receivables -->  the Legal Entity is stamped on the transactions and used to identify the Legal Owner for Legal reporting as well as Tax calculation. The transactions exists within an OU and that OU has a ledger which will account the transactions, if a transaction has more than one LE associated with it then a hierarchy of LE derivation is used to default an LE. Ideally The Legal entity is the owner of that transaction.
• Intercompany Accounts and Intercompany Processing Rules --> Intercompany journals involve balancing segment values that map to different legal entities. These journals are balanced for each legal entity by using their intercompany accounts.

Designing Payables Interface

Designing Payables Interface

Designing a Payables interface is a very important task of any Oracle Applications Payables Implementation. Hence it is always required to create a payables Interface.

Payables Interface has significantly changed from 11i to R12.

Even though the R12 has Three levels of details on a payables Invoice which are

Invoice Header

Invoice Line

Invoice Distribution.

The Payables Interface Still continue to have two level of details, which are

Invoice Header

Invoice Line.

Below is a Insert script which can be leveraged for Inserting records in Payables Interface Header Table. AP_INVOICES_INTERFACE

Payables Invoice Header Interface Insert

INSERT

INTO apps.ap_invoices_interface

  (

    invoice_id,

    invoice_type_lookup_code,

    invoice_date,

    invoice_num,

    vendor_id,

    vendor_site_id,

    invoice_amount,

    source,

    org_id,

    INVOICE_CURRENCY_CODE,

    EXCHANGE_RATE_TYPE,

    --TERMS_NAME,

    DESCRIPTION,

    GLOBAL_ATTRIBUTE18,

    GLOBAL_ATTRIBUTE19,

    GLOBAL_ATTRIBUTE20,

    --GROUP_ID,

    --PAY_GROUP_LOOKUP_CODE,

    GL_DATE

  )

  VALUES

  (

    AP.AP_INVOICES_INTERFACE_S.NEXTVAL

   ,'STANDARD'

   ,SYSDATE --<invoice date>

   ,'KTICAP081801'

   ,24699    --<vendor_id validated against po_vendors.vendor_id>,

   ,35433     --<vendor_site_id validated against po_vendor_sites.vendor_site_id>,

   ,111     --<invoice_amount>

   ,<SOURCE>

   ,<ORG_ID>

   ,'USD'

   ,'Corporate'

   ,'KTEST for ICO'

   ,'Services In Country'

   ,'01-AUG-2014'

   ,'18-AUG-2014'

   ,trunc(Sysdate)

  );

 

Below is a Insert script which can be leveraged for Inserting records in Payables Interface Header Table. AP_INVOICE_LINES_INTERFACE

Payables Invoice Lines Interface Insert

INSERT

INTO APPS.ap_invoice_lines_interface

  (

    invoice_id

    ,invoice_line_id

    ,line_number

    ,line_type_lookup_code

    ,amount

    ,accounting_date

    ,DESCRIPTION

   --,PO_HEADER_ID

   --,PO_DISTRIBUTION_ID

   --,SHIP_TO_LOCATION_CODE

    ,DIST_CODE_COMBINATION_ID

    ,ATTRIBUTE_CATEGORY

    ,ATTRIBUTE1

    ,ATTRIBUTE2

    ,ATTRIBUTE15

  --  ,GLOBAL_ATTRIBUTE_CATEGORY

    ,GLOBAL_ATTRIBUTE18

    ,GLOBAL_ATTRIBUTE19

    ,GLOBAL_ATTRIBUTE20

    --,PROJECT_ID

    --,TASK_ID

    --,EXPENDITURE_TYPE

    --,EXPENDITURE_ITEM_DATE

    --,EXPENDITURE_ORGANIZATION_ID

    ,ORG_ID

   

    ,CONTROL_AMOUNT

    ,TAX_CLASSIFICATION_CODE

  )

  VALUES

  (

    AP.AP_INVOICES_INTERFACE_S.CURRVAL

   ,AP.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL

   ,1       

   ,'ITEM'   --<ITEM>/<FREIGHT>/<TAX>

   ,100     

   ,SYSDATE

   ,'KT ICO INVOICE DIST DESCRIPTION'

   ,'2906440' --'<Derive from Code Combination>'

   ,<ATTRIBUTE CONTEXT>

   ,'12-AUG-2014'

   ,'BIL'

   ,'987654321'

   --,'JE.IL.APXINWKB.GOOD_SERVICES'

   ,'Services In Country'

   ,'01-AUG-2014'

   ,'18-AUG-2014'

   ,<ORG_ID>

   ,'11'

,<TAX CODE>

  );