General Ledger API's for data upload

General Ledger API's for data upload

1. Budget Upload - Four methods are available for adding budget transactions:
i. ADI - spreadsheet based upload. ADI uses budget wizards and budget templates.
ii. Manual Entry and Update - Direct update to budget amount forms.
iii. Budget Interface - Third-party budget source -> GL_BUDGET_INTERFACE -> Run Budget Upload.
iv. GL Interface - Third-party budget source -> GL_INTERFACE -> Journal Import Process.
 Differebce between iii and iv is that budget interface table  has a column for each budget period in current open fiscal year while GL interface table has a individual line for each budget period in current open fiscal year.

2. Importing Journals - Three methods - i. ADI ii. Manual Entry and Updating iii. GL Interface.

3. Loading Daily Rates - Two methods -  
i. Manual in daily rates form
ii Through GL_DAILY_RATES_INTERFACE
GL_DAILY_RATES_INTERFACE is used to create, update and delete daily conversion rates in GL_DAILY_RATES table.

4. Generating Intercompany Transactions - Global Intercompany System (GIS) is a control location for entering subsidiaries intecompany transactions. Both sender and receiver subsidiaries are required ti approve the transaction before posting.

There are three methods for entering these transactions - Manual, Automatic transaction line generation and import transactions through the GIS open interface table GL_IEA_INTERFACE. 
Auto Accounting Rules are applicable to automatic and interface based transaction entry.

3C's of Accounting in Oracle EBS

In Release 12, "Set of Books" have been Substituted by "Ledgers".
In previous versions, set of books consists of,
C = Chart of Accounts
C = Calendar
C = Currency

In Release 12, ledgers Consists of
C = Chart of Accounts
C = Calendar
C = Currency
C = Accounting Method or Convention

Selection of Accounting method is nothing new for people using earlier versions of Oracle Applications, it is carried out at the Subledger level. The available methods were Accrual and Cash in previous versions.

In Release 12, the selection of Accounting method is done at the ledger creation level itself. And this is the only place where the accounting method is selected and not in any other place. Accounting method can be changed any time, it will not affect the existing transactions.

The (Seeded) Accounting methods in Release 12,
1) Accrual with Encumbrance Accounting
2) Cash with Encumbrance Accounting
3) Standard Accrual
4) Standard Cash
5) US Federal Accounting

The basis of Selection of Accounting methods:
All upgraded, non-public-sector ledgers will have a Subledger accounting method assigned called Standard Accrual or Standard Cash. This is same as the Cash and Accrual Accounting in 11i.

All upgraded public sector ledgers will have a subledger accounting method assigned called Encumbrance Accrual or Encumbrance Cash. This come in to place when we use purchasing modules, and transfer of encumbrance journals becomes necessary.

For Federal customers, all upgraded ledgers will have the US Federal Accounting subledger accounting method assigned.

Note: You cannot delete the Subledger accounting method but you can change the Subledger accounting method and Subledger accounting options at any time. And also you can create a new Subledger method and attach it to your ledger.

Accounts Payable Month-End process

Accounts Payable Month-End process

1. Submit and Review Invoices Validation and On-Hold report.
2. Resolve Holds or Roll over invoices to next month.
3. Submit 'Payment transfer to General Ledger' program to transfer invoice and payment accounting information to GL. This program auto-generates Payables Accounting process request which create all the accounting entries for Oracle Payables.
4. Next reconcile month-end number and sweep any unposted invoice to next month.
5. After reconciliation, close current period and open next period.
6. An AP reconciliation is required  to record reconciling items.

Other Adhoc activities which are required to be performed - 
1. Perparing all journal entries pertaining to AP transactions, accured AP, and adjustments for AP aging collections.
2. Posting AP transactions to the GL.
3. Reconciling AP accounts to the GL.
4. Verifying current AP aging.
5. Locating and voiding missing checks.
6. Correcting account distribution errors.

Oracle Apps: Month-End Module Closing Process

Month-End Module Closing Process

Sequence of module closure is important

It is done for generating/reporting financial results which inturn helps in better management decisions making. 12 Accounting period of 4 or 5 weeks. 13th and 14th adjustment period for year end set at 31st Dec. Normally starts on Monday of first week of the month and take 4-5 days to complete.

Order of closure of module during month-end sub-ledger:

1. Cash Management - Standard Bank reconciliation tasks
2. AP - After CM 
3. AR - After CM
4. PO - After AP
5. FA - After AP and AR
6. Inventory - After CM, AP, AR, PO and FA
7. GL - After CM, AP, AR, PO, FA and INV
8. GL month-end (Operational SOB) - Consolidation operational SOB must be closed before running consolidation.

COGS Accounting

COGS Accounting : 

In R11i, COGS was being recognized soon after ship confirm. After ship confirm, User runs Interface Trip Stop (ITS). ITS calls OM interface and Inventory Interface. Inventory Interface calls COGS Account Generator WF.

In R12, COGS are recognized along with revenue. After shipping, Inventory tables are updated with deferred COGS account. After Invoicing, COGS account generator updates the COGS account.

Excepiton - Ship only-lines (No invoice will be created) needs to be handled differently.

Navigation - Deferred COGS account setup:

Inventory -> Setup -> Organization -> Parameters -> other accounts

Accounting Entries : 

When order shipped - 
Inventory Valuation Account - Credit
Deferred COGS Account - Debit

Once Revenue Recognized then 

1. Run the Revenue Recognition Information Collection Program. It collects the percentage of Revenue Recognized based on AR events for a date range.
2. Run the Generate COGS Recognition events. This will create COGS recognition for each sales order where the revenue recognition percentage is not equal to COGS recognition percentage.

Navition - For running programs:

Cost -> COGS Recognition -> Collect Revenue Recognition
Cost -> COGS Recognition -> Generate COGS Recognition events 
Cost -> View Transaction -> Material Transactions -> Distributions

Accounting Entries : 

After revenue recognition - 
Deferred COGS Account - Credit (Actual Revenue)
COGS - Debit (Actual Revenue)

Interface vs Data migration vs Data Conversion

Data Migration - is moving required and large volume of data from client's legacy/custom system to new system.

Data Conversion - is converting data from one structural form to another to suit the requirement of the system to which it is migrated.

Interface - are programs for connection between two systems in order to synchronize the data. These can be batch, real-time or manual. Used repeatedly so should be efficient. Can be triggered by an event to run as concurrent program or scheduled to run at certain time.

Standard Import Programs from Oracle - 
1. GL: Journal Import
2. AP: Payables Open Interface 
3. AR: Customer Interface
4. AR: Autoinvoice
5. INV: Item Import

Ways to interface data into Oracle Applications - 
Simple ways - 
1. From Screen manually
2. Oracle Open System Interface
3. Store in database directly
Complex ways - 
4. 3rd Party Softwares for direct DB update
5. Taviz (formerly SmartDB) which is a EAI tool. 
6. Crossroads
7. See beyond (formerly STC)
8. Vitria
9. Data Loader - They have macro enabled spreadsheet kind of tool.
10.More4Apps
11. EDI - Electronic Data Interchange

Most of these are used for master data migration.

Relation Between Requisition and PO

Relation Between Requisition and PO

SELECT prha.segment1 "REQ NUM", pha.segment1 "PO NUM"
  FROM po_headers_all pha,
       po_distributions_all pda,
       po_req_distributions_all prda,
       po_requisition_lines_all prla,
       po_requisition_headers_all prha
 WHERE     pha.po_header_id = pda.po_header_id
       AND pda.req_distribution_id = prda.distribution_id
       AND prda.requisition_line_id = prla.requisition_line_id
       AND prla.requisition_header_id = prha.requisition_header_id;