Oracle Projects Migration/ Data Conversion


During my work on a projects conversion, I came accross this very nice document. This document is very informative and so sharing with you all. Happy reading!

Main Article:

In this article I will be explaining the general steps involved in any Conversion/Data Migration of Oracle Projects module.

At the end of this article, you would have learned:

  • Stages in Oracle Projects Conversion.
  • How to setup the Oracle Projects module for the conversion/Data Migration.
  • Options for the Load (flat file, csv, or direct Loads).
  • Oracle Projects AMG APIs needed to perform the Conversion.
  • Testing the Conversion Process.
  • Verifying the Conversion Process.

Scenario:

Company ‘XYZ’ is using a Project Management and Accounting Software for years long. The Management has decided to move from their existing system to Oracle Projects module because of its vast functionality and integration with other financial modules.

How to deal with it?

Now the question arises: What data to migrate from the legacy system to Oracle Projects?. Well, it depends upon the type of projects.

If the Projects are used for Internal Administration and tracking of costs, you may want to migrate the existing projects, tasks(the work break down structure), Cost Budgets, Cost (Timecards, Employee Expenses, Miscellaneous Expenses) etc.

If the Projects are used for billing the Clients for the work done (Typical Contract Projects), then you may want to Revenue, Agreements (Contracts), Revenue budgets and Invoices in addition to the above data.

Once the decision is made to which data to migrate, then the next step would be setting up the Oracle Projects for the conversion purpose, which we will see in detail sooner. Once the System has been setup, the technical elements (programs, concurrent processes etc) have to be created in order to migrate the data from Legacy System to Oracle Projects.

Stages in Oracle Projects Conversion

1.     The First Stage is to obtain the data from the Legacy System which needs to be migrated to Oracle Projects.

2.     The Second Stage will be most crucial step in the process which is to massage the data according to the Oracle Projects Conversion Interface (Programs built using AMG APIs). This Step is indeed time consuming, manual labor intensive to massage and rectify the errors etc. But completing this step successfully pays dividends in the consecutive processes / Stages.

3.     The Third Stage is uploading the data obtained from legacy systems into the Staging Area (Staging Tables created to hold the data temporarily till it gets migrated into Oracle Projects). Once the data is uploaded to the Staging tables, the programs built for migration (We will see how to build these programs in detail) will validate the Staging Table data to confirm that it is in compliance with the Projects Conversion Program (The AMG APIs used in the programs indeed needs data in certain format, also the data should be validated against the Oracle Projects Setup. For instance, when migrating the cost or hours from legacy to Projects, we might need to validate if the expenditure type is already setup in Oracle Projects, if the expenditure type is not setup, the program/APIs will throw an error. So it is always better to capture these kind of scenarios in the Validation Step of the Migration.

The Second Stage and Third Stages are repetitive until you get the valid data from the legacy system which can be migrated into Oracle Projects without any errors or issues.

The Fourth Stage is the actual migration process which will migrate the data from the Staging Tables to the Oracle Projects Base tables. Once this step is done, the projects, tasks and other data are available in Oracle Projects for use.

Before going through the stages, we will look at some of the basic setups that need to be done in Oracle Projects.

Oracle Projects Setup For Conversion

Product Code:

The Product Code needs to be setup in Oracle Projects in the AMG Gateway – Source Products Form in the Oracle Projects Implementation Super User Responsibility. This setup is mandatory since this product code needs to be passed when using the Oracle Projects AMG APIs

Project Types and Project Templates:

The project types and project templates for conversion projects need to be setup up. This is a mandatory setup since while migrating projects we need to tell the APIs which project template/type the projects use.

For Contract Projects, setup the Contract Project Type Template. For administrative or internal projects, setup the Indirect Project Type templates.

If you are migrating Cost and Revenue Budgets, then the Plan Types need to be attached to the templates in order to create the budgets for the migrated projects.

Implementation Option Setup:

Project Numbering: This implementation option is by default set to ‘Automatic’ which means when creating projects in Oracle Projects, the project number is automatically derived and users are not required to provide any project numbers. This option is best suitable when creating projects in Oracle Projects. But when migrating the projects from the third party systems, there is an option to migrate the projects with the same project number as in the legacy system. This is not mandatory but is recommended since it will be easy to refer back the projects in the source system using the project numbers.

In order to pass the project number to the Migration program, this implementation option needs to be setup to ‘Manual’. Once the migration is done, this setup can be reverted back to ‘Automatic’.

Setup Transaction Source:

The Transaction Source needs to be setup in Oracle Projects in the Transaction Sources form in Oracle Projects Implementation Super User Responsibility. This is a mandatory setup for the Costs/hours migration from the legacy system to Oracle Projects. We need to tell the migration API’s what the source system is and how the data is handled when it is imported to Oracle Projects.

Setup Expenditure Types:

Expenditure Types are needed to categorize the cost/hours when it is imported to Oracle Projects. This is a mandatory setup for Cost/hours migration. We need to tell the system which expenditure type the cost/hour belongs to.

Setup Employee Cost Rates:

Setting up cost rates for employees is not mandatory. But if you need to cost the hours that are migrated in the system, the labor cost distribution process in Oracle Projects do need the rates setup in order to calculate the costs.

But if you are migrating the costs directly from the legacy instead of hours then this step is not needed. But ideally the cost rates are required in a general production scenario wherein the employees/contractors enter their timecards.

You can setup job rate schedule, employee level rate schedule or employee level overrides. Alternatively, the costing client extension can be setup to calculate the cost according to the business scenario.

Refer to the Oracle Projects User Guide for how to setup the employee cost rates.

First Stage: Obtain Data from Legacy System

The first stage deals with obtaining the data from the legacy system in the desired format. The data can be obtained in the form of flat text file or comma separated file csv, tab delimited file or file with any delimiters. Generally tab delimited files are recommended since comma separated files behave strange when there is a comma in the data itself.

If there is a database link created between the Source Legacy database and the Oracle Projects Database then the data can be obtained directly using the select statements against the Source DB from within the Oracle Projects DB. But this method is not preferred as it is more performance intensive when it comes to selecting large data over the network.

For Projects Migration, generally 2 files are obtained. One file for Projects Data and the other file for Tasks Data.

For Transaction Migration, single file is enough with all the cost/hours data.

For Cost/Revenue Budget migration, single file is enough with all the Budgets Data.

Create SQL Loader concurrent program which will upload the obtained data into the Oracle Staging Tables.

Also it is always the best practice to create a control table in the Staging area, which will control the data migration. For example your control table might look like the one below:

Parameter Type Parameter Parameter Value
Template Contract Contract_Template
Template Indirect Indirect_Template
Expenditure Type Hours Labor
Expenditure Type Expenses Employee_Expense
Transaction Transaction Source Legacy1
Product Code Product code LEGACY1
Project Publish Workplan Yes
Project Baseline Workplan Yes
Cost Budget Baseline Yes
Revenue Budget Baseline Yes

This control table is looked upon by the migration program. So whenever there is a change in the templates, expenditure types it is easy to change this control table instead of the code. So the advise is never hard code any values in the code, always handle it using the control table.

Also it will be better to have a form based on this table, so that this table data can be changed from the front end.

Second Stage & Third Stage: Validate and Format the Data

I am coupling the second and third stage because both are interdependent. Validating data is very important and it prevents some of the time consuming tasks in actual migration such as trouble shooting the errors due to the invalid data.

Below are some of the key validations that need to be done before doing the actual migration.

Projects/Tasks Migration:

Though the projects and tasks are in different staging tables, the migration of projects/tasks is doing using a single program. We can always migrate projects and tasks separately, but the issue is with the performance when adding task by task to each project. So it always better to create projects and tasks together because of the bulk loading of tasks.

Project/Task – Setup Validations: 

  • Validate the Product code is setup.
  • Validate if the required Project Templates are setup.
  • Validate if the Project Numbering is set to ‘Manual’ for creating projects with the   predefined project numbers.

Project/Task Data Validations:

  • Validate if the project name is unique. Project with the same name should not exist in Oracle Projects.
  • Validate if the project number is unique. Project with the same number should not exist in Oracle Projects.
  • Validate if the project long name is unique. Project with the same long name should not exist in Oracle.
  • Validate the project reference(this field is mandatory in the projects file, it can be the projects identifier of the source project or project number of the source project, but it has to be unique in the source system as well. This field needs to be populated in all the converted projects in order to track back and identify the project in the source system)
  • Project name and project number should be 30 chars in length. Project long name should be 240 chars in length. Project Description should be 250 chars in length. Project description is not a mandatory field when creating project.
  • Check if the project has a project manager and the project manager is active in Oracle HR and has an assignment and a Job assigned. Also the project manager has to be active from the project start date, else you cannot create a project with that project manager.
  • In case of contract projects, check if the customer of the project is a valid customer defined and with a valid Bill To site assigned.

Apart from the above necessary validations, you may have to validate the additional data such as Projects DFF Data you may want to populate with your custom field values. For example you may want to populate the Project cost center value in the Segment1 of the Project DFF. In such case you have to validate if the cost center value is a valid value for that Segment1 (sometimes you may have attached an LOV to that segment1, so in that case, the cost center has to be validated against that LOV Values).

For tasks, values for task types, work type, task manager has to be validated. Task types and work types have to be defined in Oracle Projects before the task with those values are migrated, else the task will not be created.

Cost/hours validation

Setup Validations:

  • Validate if the Transaction source is setup.
  • Validate if the Expenditure type is setup.

Data Validations:

  • Check if the hours value is greater than zero.
  • Check if the employee number is valid in HR and is active on the timecard date.
  • If the transaction source is setup as costed, then the cost has provided while migrating the transactions. If the transaction source is setup as accounted, then the code combination ids need to be provided when migrating transactions.

Apart from the above validations, you may want to validate the additional DFF segments that you are going to populate for that expenditure item.

Budgets Validation

Setup Validations:

  • Validate the project template has the required financial plan type attached. Financial plans are the project management versions of the Budget types in the Forms applications.
  • Budget amount has to be greater than zero.
  • There is no need to create revenue budgets if the ‘Baseline funding without budget’ option is checked at the project or project type level. Whenever the funding is created for the contract project and is baselined, the revenue budget is automatically created and baselined. If that option is not checked, it is necessary that a revenue budget with the same amount as the funding amount needs to be created and baselined in order to baseline the funding.

Data Validations:

The cost budget for the project can be from the source system’s budgeting system. If there is no budgeting in the source system, a cost budget with the total cost of the project can be created in Oracle Projects.

For revenue budgets, it has to be equal to the funding amount of the project. If there is no funding amount in the source system, the sum of the revenue amount can be the funding amount and it is the revenue budget amount as well.

Agreements and Funding Validation

Data Validations:

  • Agreement type should be valid.
  • Agreement Amount should be greater than zero.
  • Hard Limits can be setup according to business rules. If the hard limits are setup for revenue and invoice then the revenue and invoice has to be within the funding limits for that project.
  • Funding amount has to be within the Agreement amount.
  • If the funding at the top task level, then the ‘Customer at top task’ has to be enabled and the customer should have been assigned at the top task.
  • Funding amount should be same as the Revenue budget amount which in general will be same as the total revenue amount for that project. If there are no hard limits then the revenue or invoice can exceed the funding amounts.

Records which fail the above validations have to be rectified before doing the actual migration.

Revenue and Invoice Validations

Data Validations:

  • Project / Task should already been converted to Oracle.
  • Event amount should be non zero.
  • For revenue event revenue amount should be populated.
  • For invoice event invoice amount should be populated.

Generally for a project, the total revenue is obtained from the source system and is created as a revenue event for that project. The total invoiced amount is calculated per project and an invoice event is created for each project.

Once these events are created successfully in the system, the Generate Draft Revenue process and Generate Draft Invoice process needs to be run so that the desired revenue and invoices are generated.

The revenue and invoice automatic approval and release client extensions can be used to automatically release the revenue when it is generated and approve/release invoices respectively.

If the revenue amounts are already interfaced to General Ledger (GL) through a different interface, then uncheck the ‘Interface Revenue to GL’ option in the implementation options and run the ‘Interface Revenue to GL’ process in Oracle projects. This will turn the flags in the revenue records as accepted in GL, though it is not interfaced. Once this is done, revert back the implementation option back to its original state.

If the invoice amounts are already interfaced to Accounts Receivables (AR) by different means, it is not desired to interface the projects invoices to AR again since it will double the invoice amount in AR. In this case, we do not have an implementation option like we had for Revenue. So a script can be created to update the Invoice’s flag to Accepted State. Alternatively the generated projects invoices can be interfaced to AR, tied back to Oracle and then the invoices can be deleted in AR.

Stage 4: Actual Migration

Once the data is validated, the program for conversion is executed to migrate the data into oracle projects base tables. There might be still errors due to AMG APIs which has to analyzed and resolved. But the chances of such AMG API issues are just below 10% in any migration (based on my experience in Oracle Projects Conversion).

Below is a table with Conversion and which AMG APIs are used for that conversion:

Conversion

AMG APIs

Projects/Tasks Conversion PA_PROJECT_PUB.CREATE_PROJECT
Budgets Conversion PA_BUDGET_PU B.CREATE_DRAFT_BUDGET, PA_BUDGET_PUB.BASELINE_BUDGET
Agreements PA_AGREEMENT_PUB.CREATE_AGREEMENT
Funding PA_AGREEMENT_PUB.ADD_FUNDING
Revenue/Invoice PA_EVENT_PUB.CREATE_BILLING_EVENT
User Defined Attributes (UDA) PA_PROJECT_PUB.LOAD_EXTENSIBLE_ATTRIBUTE

For Transactions (cost/hours) migration, there is no APIs to create the expenditures in Oracle. The pa_transaction_interface_all table needs to be populated with the migration data and once it is populated, the PRC: Transaction Import process with the Transaction source as parameter needs to be run in Oracle Projects. All invalid records need to be rectified in order to migrate all the transactions.

The rejected records can be found in the same interface table with the transfer_status_code as ‘R’.

Conversion Tips:

1.     Make sure the templates are defined properly and exactly the way it is needed. Once the projects are created using the templates and the template was wrongly defined, then it takes ages to rectify the converted projects.

2.     Create the conversion program to operate in two modes: Validate, Run. A concurrent process with a parameter called mode accepting Validate/Run can be created. So the same concurrent program can be used to validate as well as run the actual migration.

3.     It is a good practice to have source Project id / Project Number as parameter to the projects conversion program. This will allow us to test the conversion for a single project and validate the data for that project.

4.     The validation process can write the invalid records to the output file. So once validation process completes, the output will have all the invalid records which needs to be rectified.

5.     Create a separate concurrent program to know the status of the already running migration process. If you want to know where the migration process is in terms of the number of records migrated, number of records rejected etc. If the volume of the migration data is huge, then it is likely possible that the conversion programs may run for hours. So in these scenarios this concurrent program can be helpful in finding the status of that migration process.

6.     For Transactions migration, the custom program written to populate the interface table can kick off the PRC: Transaction Import process and wait for its completion. Once the transaction import completes, the custom process can print the invalid records from the interface table to the output file.

7.     There are APIs to publish and baseline the workplans created as a part of projects migration. But these APIs need to be used with care. There are lot of performance issues and bugs when using these APIs.

Original Post

Author: Sathish Raju

Website: http://www.projectsaccounting.com

Advertisements

Item Conversion – A Technical Note


This conversion is done for import items from any source into Oracle Inventory and Oracle Engineering.

Description:

When you import items through the Item Interface, you create new items in your item master organization or assign existing items to additional organizations. You can specify values for all the item attributes, or you can specify just a few attributes and let the remainder default or remain null. Here we are importing items with template.

Interface Table:

  • The interface table for Item Import is MTL_SYSTEM_ITEMS_INTERFACE and it contains every column in the Inventory item master table, MTL_SYSTEM_ITEMS.
  • The columns in the item interface correspond directly to those in the item master table.
  • Except for ITEM_NUMBER or SEGMENTn columns, ORGANIZATION_CODE or ORGANIZATION_ID, DESCRIPTION, PROCESS_FLAG, and TRANSACTION_TYPE, all other columns are optional, either because they have defaults that can be derived, or because the corresponding attributes are optional and may be left null.
  • If you are importing revision details for your new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. This table is used only for revision information, and is not mandatory.

Required Data:

ITEM_NUMBER or SEGMENTn columns

Every row in the item interface table must identify the item and organization. To identify the item when importing it, you may specify either the ITEM_NUMBER or SEGMENTn columns—the Item Interface generates the INVENTORY_ITEM_ID for you.

ORGANIZATION_ID or ORGANIZATION_CODE

You need to specify either the ORGANIZATION_ID or ORGANIZATION_CODE that identifies the organization.

DESCRIPTION

When you import a new item, you are also required to specify the DESCRIPTION.

TRANSACTION_TYPE & PROCESS_FLAG

There are two other columns the Item Interface uses to manage processing. They are TRANSACTION_TYPE, which tells the Item Interface how to handle the row, and PROCESS_FLAG, which indicates the current status of the row.

Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface. The Item Interface uses the PROCESS_FLAG to indicate whether processing of the row succeeded or failed. When a row is ready to be processed, give the PROCESS_FLAG a value of 1 (Pending), so that the Item Interface can pick up the row and process it into the production tables.

Meaning of PROCESS_FLAG Values:

Code Code Meaning
1 Pending
2 Assign complete
3 Assign/validation failed
4 Validation succeeded; import failed
5 Import in process
7 Import succeeded

Note:

When more than one of these columns has been entered and they conflict, ITEM_NUMBER overrides SEGMENTn and ORGANIZATION_ID overrides ORGANIZATION_CODE. It is strongly recommended that you use SEGMENT column instead of ITEM_NUMBER.

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting.

Derived Data:

Many columns have defaults that the Item Interface uses when you leave that column null in the item interface table. Please refer Oracle Manufacturing APIs and Open Interfaces Manual for more details.

Validations:

1] Validation for organization code (standard table: ORG_ORGANIZATION_DEFINITIONS)

2] Validation for Item_number (standard table: mtl_system_items_b)

3] Validation for Description

4] Validation for Primary Unit of Measure (standard table: MTL_UNITS_OF_MEASURE)

5] Validation for Template Name (standard table: mtl_item_templates)

6] Validation for Item Type (standard table: FND_COMMON_LOOKUPS)

7] Validation for ENCUMBRANCE_ACCOUNT (standard table: gl_code_combinations)

8] Validation for EXPENSE_ACCOUNT (standard table: gl_code_combinations)

Note: You can add more validation as per your business requirement.

Record Insertion:

Through your custom program you can insert the below columns in the interface table. Again the list is a sample one; you can add additional columns as your business requirement.

Columns inserted:

  • ORGANIZATION_ID                     –>Taken from ORG_ORGANIZATION_DEFINITIONS table
  • ORGANIZATION_CODE               –>Taken from Staging table
  • LAST_UPDATE_DATE                 –>sysdate
  • LAST_UPDATED_BY                   –>fnd_global.user_id
  • CREATION_DATE                        –>sysdate
  • CREATED_BY                             –>fnd_global.user_id
  • LAST_UPDATE_LOGIN                 –>fnd_global.login_id
  • DESCRIPTION                             –>Taken from Staging table
  • SEGMENT1                                 –>Taken from Staging table
  • PRIMARY_UOM_CODE                 –>Taken from Staging table
  • PRIMARY_UNIT_OF_MEASURE     –>Taken from MTL_UNITS_OF_MEASURE
  • ITEM_TYPE                                  –>NULL
  • TEMPLATE_NAME                       –>Taken from Staging table
  • TEMPLATE_ID                             –>Taken from mtl_item_templates        
  • MIN_MINMAX_QUANTITY             –>Taken from Staging table
  • MAX_MINMAX_QUANTITY            –>Taken from Staging table
  • LIST_PRICE_PER_UNIT                –>Taken from Staging table
  • ITEM_CATALOG_GROUP_ID        –>Taken from Staging table
  • SET_PROCESS_ID                       –>1
  • PROCESS_FLAG                          –>1
  • TRANSACTION_TYPE                  –>‘CREATE’

Standard Concurrent Program:

After you insert valid data into Interface table, you can go to Items > Import > Import Items and run the standard concurrent program. Here is the parameter form.

1] All Organizations:

  • Yes: Run the interface for all organization codes in the item interface table.
  • No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:

  • Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
  • No:  Do not validate items in the interface table.

3] Process Items:

  • Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
  • No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:

  • Yes: Delete successfully processed items from the item interface tables.
  • No: Leave all rows in the item interface tables.

5] Process Set:

Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.

Working with failed interface rows:

If a row fails validation, the Item Interface sets the PROCESS_FLAG to 3 (Assign/validation failed) and inserts a row in the interface errors table, MTL_INTERFACE_ERRORS. To identify the error message for the failed row, the program automatically populates the TRANSACTION_ID column in this table with the TRANSACTION_ID value from the corresponding item interface table.

The UNIQUE_ID column in MTL_INTERFACE_ERRORS is populated from the sequence MTL_SYSTEM_ITEMS_INTERFACE_S. Thus, for a given row, the sequence of errors can be determined by examining UNIQUE_ID for a given TRANSACTION_ID.

You should resolve errors in the sequence that they were found by the interface, that is, in increasing order of UNIQUE_ID for any TRANSACTION_ID.

Resubmitting an Errored Row:

During Item Interface processing, rows can error out either due to validation (indicated by PROCESS_FLAG = 3 in MTL_SYSTEM_ITEMS_INTERFACE and the corresponding error in MTL_INTERFACE_ERRORS) or due to an Oracle Error.

When an Oracle Error is encountered, the processing is stopped and everything is rolled back to the previous save point. This could be at PROCESS_FLAG = 1, 2, 3, or 4.

When you encounter rows errored out due to validations, you must first fix the row corresponding to the error with the appropriate value. Then reset PROCESS_FLAG = 1, INVENTORY_ITEM_ID = null, and TRANSACTION_ID = null. Then resubmit the row for reprocessing.

Useful Query:

Select
  SEGMENT1,
   DESCRIPTION,
   PROCESS_FLAG,
   SET_PROCESS_ID,
   INVENTORY_ITEM_ID,
   ORGANIZATION_ID,
   ORGANIZATION_CODE,
   CREATION_DATE,
   ITEM_TYPE,
   UNIT_OF_ISSUE,
   TEMPLATE_ID,
   TEMPLATE_NAME,
   EXPENSE_ACCOUNT,
   ENCUMBRANCE_ACCOUNT,
   PRIMARY_UOM_CODE,
   PRIMARY_UNIT_OF_MEASURE,
   MIN_MINMAX_QUANTITY,
   MAX_MINMAX_QUANTITY,
   TAX_CODE,
   REQUEST_ID
from
    MTL_SYSTEM_ITEMS_INTERFACE
order by CREATION_DATE;
----------------------------
Select
   ORGANIZATION_ID,
   UNIQUE_ID,
   REQUEST_ID ,
   TABLE_NAME ,
   COLUMN_NAME,
   ERROR_MESSAGE
   CREATION_DATE,
   MESSAGE_TYPE
from
    MTL_INTERFACE_ERRORS
order by CREATION_DATE;

Useful Metalink Note:

  1. FAQ for Item Import [ID 109628.1]
  2. Error in Validating MTL_SYSTEM_ITEMS_INTERFACE [ID 1057175.6]
  3. A Guideline to IOI Error Messages and Solutions [ID 52746.1]
  4. How to Import Item Costs Using the Item Open Interface (IOI) [ID 191376.1]
  5. Item Import Performance Tips [ID 66496.1]
  6. INCOIN: Basic Steps for Researching Failed Item Imports [ID 552683.1]
  7. Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]
  8. How To Prepare to Run Parallel Runs of the Item Import Interface INCOIN [ID 842767.1]
  9. Item Import Gives Error: “The Default Primary Unit of Measure Is Invalid” [ID 789927.1]
  10. Understanding Item Import and Debugging Problems with Item Import [ID 268968.1]
  11. How to create a Category Set and Assign Items to Categories [ID 423551.1]
  12. Item API Or Concurrent Manager, Which One Is Best For Item Creation? [ID 760498.1]

Few Concepts on General Ledger


Few Concepts on General Ledger

What is General Ledger?

The Oracle General Ledger is the central repository of accounting information. The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help people inside and outside the organization make decisions.

 General Ledger Accounting Cycle:

  1. Open period
  2. Create/reverse journal entries
  3. Post
  4. Review
  5. Revalue
  6. Translate
  7. Consolidate
  8. Review/correct balances
  9. Run accounting reports
  10. Close accounting period

What are Set of Books?

A set of books determines the functional currency, account structure, and accounting calendar for each company or group of companies. It is replaced by the Ledger Sets in R12.

Set of Books consists of the following Three elements

  • Chart of Accounts: COA can be designed to match the Organizational Structure and dimensions of the business.
  • Currency:  GL enables to define one currency as Functional Currency and use other currencies for transactions.
  • Calendar: Calendar has to be defined to control the accounting year and its periods.

Types of Journal Entries:

Within Oracle General Ledger, you can work with the following types of journal entries:

Manual Journal Entries

The basic journal entry type is used for most accounting transactions. Examples include adjustments and reclassifications.

Reversing Journal Entries

Reversing journal entries are created by reversing an existing journal entry. You can reverse any journal entry and post it to the current or any future open accounting period.

Recurring Journal Entries

Recurring journal entries are defined once, then are repeated for each subsequent accounting period you generate. You can use recurring journal entries to define automatic consolidating and eliminating entries. Examples include intercompany debt, bad debt expense, and periodic accruals.

Mass Allocations

Mass Allocations are journal entries that utilize a single journal entry formula to allocate balances across a group of cost centers, departments, divisions or other segments. Examples include rent expense allocated by headcount or administrative costs allocated by machine labor hours.

Foreign Currency Concepts:

The three key foreign currency concepts in Oracle General Ledger are:

Conversion

Conversion refers to foreign currency transactions that are immediately converted at the time of entry to the functional currency of the set of books in which the transaction takes place.

Revaluation

Revaluation adjusts liability or asset accounts that may be materially understated or overstated at the end of a period due to a fluctuation in the exchange rate between the time the transaction was entered and the end of the period.

Translation

Translation refers to the act of restating an entire set of books or balances for a company from the functional currency to a foreign currency.

What are Financial Statement Generator Reports (FSG)?

Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible tool you can use to build your own custom reports without programming. You can define custom financial reports, such as income statements and balance sheets, online with complete control over the rows, columns, and content of your report. You can control account assignments, headings, descriptions, format, and calculations in addition to the actual content. The reusable report components make building reports quick and easy. You can copy a report component from one report, make minor edits, then apply the report component to a new report without having to create a new report from scratch.

What is Applications Desktop Integrator(ADI)?

Applications Desktop Integrator combines the power of Oracle General Ledger journal entry, budgeting, and report creation, submission, publishing, and analysis within an Excel spreadsheet environment.

Journal Components:

Every journal entry in Oracle General Ledger has three components.

  • Every journal entry belongs to a batch. You create a batch of journal entries by entering a name, control total and description for the batch.
  • This step is optional. If you do not enter batch information, Oracle General Ledger automatically creates one batch for each journal entry, defaulting the name and the latest open period.
  • All journal entries in a batch share the same period.
  • Entering a batch control total and description are optional.
  • If you do not enter a batch name, you must recall the journal entry by date.
  • Batch information is stored in the GL_JE_BATCHES table.

Journal Header Information

  • The header information identifies common details for a single journal entry, such as name, effective date, source, category, currency, description, and control total.
  • Group related lines into journal entries
  • All lines in a journal entry must share the same currency and category.
  • If no journal entry-level information is entered, Oracle General Ledger assigns a default name, category, and the functional currency.
  • Header information is stored in the GL_JE_HEADERS table.

Journal Line Information

  • Journal lines specify the accounting information for the journal entry.
  • Total debits must equal total credits for a journal entry for all journal entries except budget journal entries and statistical journal entries.
  • Description for each line can be entered optionally.
  • Information for journal entry lines is stored in the GL_JE_LINES table.

Journal Posting Methods:

You have three methods to post journal batches.

Batch Posting: Navigate to the Post Journals window to post a group of journal batches.

(N) Journals > Post

Manual Posting: Select the More Actions button from either the Journals window or the Batch window to post a journal batch at the time of entry. This option is available only if the profile option Journals: Allow Posting During Journal Entry has been set to Yes.

When you post journals, Oracle General Ledger posts all journals in a batch. You cannot post individual journal entries in a batch.

(N) Journals > Enter (B) More Actions

Automatic Posting: Run the AutoPost program to post journal batches automatically based on a schedule you define.

(N) Setup > Journals > AutoPost