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>)

Project Accounting Basics

Project Accounting Basics

Project: A project is a primary unit of work that can be broken down into one or more tasks.

Task: It is the small unit of work created under the project against which transaction can be created.

Note: we cannot create transaction/expenditure items directly on the project, hence it is mandatory for each project to have at least one task.

 Project Classes:

Oracle PA has provided three pre defined project classes.

• Indirect projects – Track overhead activities and costs of the organization. Note: Does not generate revenue and invoicing process.
• Capital projects – Track asset development activities and costs, and costs are capitalized as one or more assets.
• Contract projects – Contract projects created with external customer to track cost, revenue, and billing.

 Project Type:

The project type controls how Oracle Project Foundation creates and processes projects, and is a primary classification for the projects your business manages. You must set up at least one project type to create projects. You must set up project types for each operating unit.

•  Examples of Indirect project: Admin Project, Transport Facility etc.
• Example of Contract project: External Project, Intercompany project etc.

Expenditure category: It is the category created to classify the different types of cost an organization may incur.

Revenue category: It is the category created to classify the different types of revenue an organization may earn.

Expenditure type:It is the identification of cost that is associated to an expenditure item.It is assigned to the expenditure category and revenue category during the setups.

Expenditure class: It is the classification for an expenditure type as how the expenditure type can be used to create transaction. When an expenditure type is classified for a certain type then only those expenditure class transactions are allowed to enter.

 In Oracle Project Costing we have predefined expenditure classes.

 Labor:

Straight Time: For standard time entry. We use the cost rate multiple with number of hours.

Overtime: Labor cost calculated using a premium cost rate multiplied by hours.

 Non-labor project costs:

Expense Reports – Expense reports from Oracle Payables or Oracle Internet Expenses. You cannot enter expense reports directly into Oracle Project Costing. Expense reports that you import into Oracle Project Costing must be fully accounted prior to import.

Usages – You must specify the non-labor resource for every usage item you charge to a project. For each expenditure type classified by a Usage expenditure type class, you also define non-labor resources and organizations that own each non-labor resource.

Supplier Invoices – Supplier invoices, discounts, and payments from Oracle Payables or an external system, and receipt accruals from Oracle Purchasing.

Miscellaneous Transaction – Miscellaneous Transactions are used to track miscellaneous project costs. Examples of uses for miscellaneous transactions are:

• Fixed assets depreciation
• Allocations
• Interest charges

Burden Transaction – Burden transactions track burden costs that are calculated in an external system or calculated by Oracle Project Costing as separate, summarized transactions. These costs are created as a separate expenditure item that has a burdened cost amount, but has a quantity and raw cost value of zero. You can adjust burden transactions that are not system-generated.

Work In Process – You use this expenditure type class for Oracle Project Manufacturing WIP transactions that you interface from Manufacturing to Oracle Project Costing. You can also use this expenditure type class when you import other manufacturing costs via Transaction Import or when you enter transactions via pre-approved batch entry.

 Inventory – This expenditure type class is used for the following transactions:

• Oracle Project Manufacturing transactions that you import from Manufacturing or Oracle Inventory.
• Oracle Inventory Issues and Receipts that you import from Oracle Inventory in a manufacturing or non-manufacturing installation.

 Example:

Sr No	Expenditure Category	Revenue category	Expenditure type	Expenditure Class
1	Labor Time	Labor Revenue	Standard_Time	Straight Time
2	Vendor Cost	Service Revenue	Supplier_cost	Supplier Invoices
3	Travel Cost	Travel Revenue	Travel Cost	Expense Report & Supplier Invoices
4	Misc Cost	Misc Revenue	Misc_Transactions	Miscellaneous and usages

In the above examples for line number 1, time can be entered for expenditure type “Standard_Time”.

Similarly for line number 2 & 4 the respective expenditure class transactions can be entered.

For line number 3 supplier invoices & expense reports can be entered as it has been classified for two different classes.

Types of Currencies

Transaction amounts are stored in the following currencies:

• Transaction Currency:The currency in which a project transaction occurs.
•  Expenditure Functional Currency: The functional currency of the expenditure operating unit.
• Project Functional Currency: The functional currency of the operating unit that owns the project.
• Project Currency: The user–defined project currency.

When you enter transactions in a currency that is different from functional currency or project currency, Oracle Project Costing must convert the transaction amount to the functional and project currencies. To convert transaction currencies, Oracle Project Costing must first determine the exchange rate type and exchange rate date.

 Task Type:

• Billable Item: It is a functionality associated at the task level identifying the expenditure items created against the task to be billed to the client.
• Non Billable Item: It is functionality associated at the task level identifying the expenditure items created on the task is only to record the cost incurred against it.

Note: We can adjust the task at the expenditure item window to make the non billable as billable and vice versa. This is called as expenditure item adjustments.

Employee/Job bill rate Override: In case a different rate needs to be updated for a particular job or employee the same can be updated at the project or task level.

The employee name needs to be entered along with the bill rate, currency and period applicable. The rate at the task level overwrites the rates at the project level and the rates at the project level overwrites the rate of the bill rate schedule attached at the project level.

Cost rate: It is the rate defined at the job or employee level to derive at the basic cost for a particular expenditure item. The cost rate scheduled are defined in the OU functional currency.

The cost rate can be defined at two levels:

• In the setups -> Expenditure -> Rate schedule.
• Overrides: Labor Costing Overrides

( Navigation: PA Responsibility->Setup->Costing->Labor->Labor Costing Overrides.)

Bill rate: It is the rate defined at the job or employee level to derive at an amount which will be billed to the client.

The bill rate can be defined at two levels:

1. In the setups -> Expenditure -> Rate schedule.
2. Overrides: At the project level as employee or job override.

•  For employee override: Navigation: Project -> Options -> – Bill Rates and Discount Overrides -> Employee Bill Rate and Discount Overrides
•  For job override: Navigation: Project -> Options ->   Bill Rates and Discount Overrides -> Job Bill Rate and Discount Overrides

Transfer Price Rules and Schedule: Transfer price rules control the calculation of transfer prices for labor and non-labor cross charged transactions. To drive transfer price calculation for cross charge transactions between the provider and receiver, use the Transfer Price Schedule window to assign labor or non-labor (or both) transfer price rules to the provider and receiver pair on a schedule line

ICB: It is a process, where we have different legal entities involved, and the OUs under these LE owns the employees and projects.

• The OU to which the employee is assigned is called PROVIDER OU.
• The OU to which owns the project is called RECEIVER OU.

 Note: In ICB process, an internal invoice is generated in provider OU.

Accounts Receivables Accounting Flow

Accounts Receivables Accounting Flow

Receivables creates default accounts for revenue, receivable, freight, tax, unearned revenue, unbilled receivable, finance charges, and AutoInvoice clearing (suspense) accounts using the information specified in your AutoAccounting structure.

AR Transactions

When a regular AR invoice is entered, Receivables creates the following journal entry:

        DR Receivables

                       CR Revenue

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

AR Receipt

When cash is received, Receivables creates the following journal entry:

        DR Cash

                       CR Receivables

Other Scenarios for Accounts Receivables Accounting Are

Bill in Arrears

If you enter an invoice with a Bill in Arrears invoicing rule, Receivables creates the following journal entry:

In the first period of Rule:

        DR Unbilled Receivables

                       CR Revenue

In all periods of Rule, for the portion that is recognized:

        DR Receivables

                       CR Unbilled Receivables

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

Bill in Advance

If you enter an invoice with a Bill in Advance invoicing rule, Receivables creates the following journal entries.

In the first period of the rule:

        DR Receivables

                       CR Unearned Revenue

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

In all periods of the rule for the portion that is recognized.

        DR Unearned Revenue

                       CR Revenue

Accounts Receivables Credit Memos

When you credit an invoice, debit memo, or chargeback, Receivables creates the following journal entry:

        DR Revenue

        DR Tax (if you credit tax)

        DR Freight (if you credit freight)

                       CR Receivables (Credit Memo)

        DR Receivables (Credit Memo)

                       CR Receivables (Invoice)

When you credit a commitment, Receivables creates the following journal entries:

        DR Revenue

                       CR Receivables

Commitments

When you enter a deposit, Receivables creates the following journal entry:

        DR Receivables (Deposit)

                       CR Unearned Revenue

When you enter an invoice against this deposit, Receivables creates the following journal entries:

        DR Receivables (Invoice)

                       CR Revenue

                       CR Tax (if you charge tax)

                       CR Freight (if you charge freight)

        DR Unearned Revenue

                       CR Receivables (Invoice)

When you apply an invoice to a deposit, Receivables creates a receivable adjustment against the invoice. Receivables use the account information you specified in your AutoAccounting structure to create these entries.

When cash is received against this deposit, Receivables creates the following journal entry:

        DR Cash

                       CR Receivables (Deposit)

Receipts

When you enter a receipt and fully apply this receipt to an invoice, Receivables creates the following journal entry:

        DR Cash

                       CR Receivables

When you enter an unapplied receipt, Receivables creates the following journal entry:

        DR Cash

                       CR Unapplied

When you enter an unidentified receipt, Receivables creates the following journal entry:

        DR Cash

                       CR Unidentified

When you enter an on-account receipt, Receivables creates the following journal entry:

        DR Cash

                       CR On-Account

When your receipt includes a discount, Receivables creates the following journal entry:

        DR Receivables

                       CR Revenue

        DR Cash

                       CR Receivables

        DR Earned/Unearned Discount

                       CR Receivables

Receivables uses the default Cash, Unapplied, Unidentified, On-Account, Unearned, and Earned accounts that you specified in the Remittance Banks window for this receipt class.

When you enter a receipt and combine it with an on-account credit (which increases the balance of the receipt), Receivables creates the following journal entry:

        DR Cash

                       CR Unapplied Cash

To close the receivable on the credit memo and increase the unapplied cash balance, Receivables creates the following journal entry:

        DR Receivables

                       CR Unapplied Cash

When you enter a receipt and combine it with a negative adjustment, Receivables creates the following journal entries:

        DR Cash

                       CR Receivables (Invoice)

        DR Write-Off

                       CR Receivables (Invoice)

Set up a Write-Off account when defining your Receivables Activity.

When you enter a receipt and combine it with a positive adjustment, Receivables creates the following journal entries:

        DR Cash

                       CR Receivables (Invoice)

        DR Receivables (Invoice)

                       CR Write-Off

When you enter a receipt and combine it with a Chargeback, Receivables creates the following journal entries:

        DR Cash

                       CR Receivables (Invoice)

        DR Receivables (Chargeback)

                       CR Receivables (Invoice)

        DR Chargeback

                       CR Receivables (Chargeback)

Set up a Chargeback account when defining your Receivables Activity.

How to Define Segment Values in Accounting Flex field in Release 12.2

How to Define Segment Values in Accounting Flex field in Release 12.2 

Not able to Search Value sets in R12.2

In Release 12.2 Value set Insert/Update depends on Security access. To Insert/Update the Accounting Flex filed segment values for a specific user, User need to follow below steps

By Default in R12.2 a user will not be able to see any values in the value set / Key Flexfield values Form and will receive an error

FRM-40212: Invalid value for field FLEX_VALUE_SET-NAME

To Insert/Update the Accounting Flex filed segment values for a specific user, User need to be provided with appropriate Grants.

Please follow below steps toad Grants for User to Insert and update Value Sets.

Responsibility : Functional Administrator

Go to Navigation : Security à Grants à  Create Grant

1.Name            :Give Name as Required

2.Grantee Type :Select as Specific User

3.Grantee         :Select the Required User Name from List of Values

4.Object           :Select Object as ''Flexfield Value Set Security Object(1)'' from List of Values

5.Set               :Select the Set as ''Flexfield Value Set Security Insert/Update Set'' from List of values .

6. Click on ''Apply'' Button

Add the Object Data Context : All Rows

Select the Set as ''Flexfield Value Set Security Insert/Update Set'' from List of values

After creating the grants from functional administrator, we will be able to perform the following in Value set and Key Flex field Values form:

i) Access the values in Name field LOV, when we Find Values By Value Set.
ii) Access the values in Application,Title,Structure & Segment field LOV's, when we Find Values By Key 
Flexfield
iii) Access the values in Application,Title,Structure & Segment field LOV's, when we Find Values By 
Descriptive Flexfield
iv) Access the values in Application, Name & Parameter LOV's, when we Find Values By Concurrent Program.

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.