Oracle General Ledger

Oracle General Ledger

GL INTERFACE TABLES

GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE
GL_IEA_INTERFACE
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY

GL BASE TABLES

FND_FLEX_VALUES_TL - FND_FLEX_VALUES - GL_SETS_OF_BOOKS - GL_IMPORT_REFERENCES - GL_DAILY_RATES - GL_JE_LINES - GL_PERIODS - GL_JE_HEADERS - GL_JE_BATCHES - GL_BALANCES - GL_CODE_COMBINATIONS

GL Budget Upload Interface

Pre-Req: set of books • flex field value sets • code combinations

Validations: Currency code • set of books id • budget entity name (budget organization)

Interface tables: GL_BUDGET_INTERFACE

Base tables: GL_BUDGETS • GL_BUDGET_ASSIGNMENTS • GL_BUDGET_TYPES

Concurrent Program Details: General Ledger Super user à Budgets à Enter à Upload

GL Daily Rates Interface

Pre-Req: currencies • conversion rate types

Validations: From Currency & to Currency, user_conversion_type

Interface tables: GL_DAILY_RATES_INTERFACE

Base tables: GL_DAILY_RATES • GL_DAILY_CONVERSION_TYPES

Concurrent Program Details: N/A à The insert, update, or deletion of rates in GL_DAILY_RATES is done automatically by database triggers on the GL_DAILY_RATES_INTERFACE. All that is required is to develop program to populate the interface table with daily rates information.

Tip: MODE_FLAG column is important (D= Delete, I = Insert, U = Update)

GL Journal Import Interface (GL Interface)

This will import journals from other sub ledger applications like Receivables, Payables into General Ledger.

Pre-Req: set of books • flex field value sets • code combinations valid & enabled • currencies • categories • journal sources •Open periods •Avoid duplicates

Validations: From Currency & to Currency, user_conversion_type

Interface tables: GL_INTERFACE

Base tables: GL_JE_HEADERS • GL_JE_LINES • GL_JE_BACTHES

Concurrent Program Details: Journal Import & Journal Posting (GL_BALANCES)

Tip: ACTUAL_FLAG column can be ‘A’ –Actual amount, ‘B’ – Budget amount & ‘E’ - Encumbrance amount. STATUS should be ‘NEW’ for new records.

Basics

• Setting up Accounting Periods and Calendars
• Defining the Accounting Flexfield Segments
• Defining General Ledger Security Rules
• Performing General Ledger Inquiries
• Setting Up General Ledger Journals
• Setting Journal Profile Options
• Setting Up Journal AutoReversal Criteria and Defining AutoPost Criteria
• Opening and Closing GL Accounting Periods
• Setting Up and Using Multi-Currency Accounting
• Defining Budgets and Budget Organizations
• Setting Budgetary Controls
• Performing Budget Inquiries
• Using the ADI Budget Wizard

FAQ

What is the use of GL_Interface?

A) Gl_Interface is the primary interface table of General ledger. It acts as an interface between data originating from other modules such as AP,AR, Legacy data and the Gl Base tables.

What is Actual Flag?

A) Actual flag represents the Journal type.
A-Actual
B-Budget
E- Encumbrance.

What is Encumbrance?

A) It is a process of Reservation of funds for anticipated expenditure from a budget. Encumbrance integrates GL, Purchasing and Payables modules.

How many Key Flex Fields are there in General Ledger?

A)One. Accounting Key Flex Field.

How many types of Budgets are there?

A) Two Types.
Expenditure Budgets
Revenue Budgets.

What are Spot Rate, Corporate Rate, Transaction Calendar and Accounting Calendar?

Spot Rate:

An exchange rate which you enter to perform conversion based on the rate on a specific date. It applies to the immediate delivery of currency.
Corporate Rate:
An Exchange rate that we define to standardize rates for our company. This rate is the standard market rate determined by the senior financial management for use through out the organization.
User Rate:
Conversion rate that is defined by the user.
EMU Fixed Rate: An exchange rate that is provided automatically by the General Ledger while entering journals. It uses a foreign currency that has a fixed relationship with the euro.
Transaction Calendar: Defines the business days and holidays for any calendar.
Accounting Calendar: Defines different types of calendars namely Fiscal, Federal Fiscal, Month etc.

What is Security Rule?

Security Rules are defined to control the access of a flexfield segment value (Financial information) at a responsibility level.

What are Cross Validation & ADI?

CVS – Cross validate segments – Allows only valid code combinations.
ADI – Allow dynamic inserts. – Allows any code combination irrespective of validity.
ADI would prevail if both of CVS and ADI are checked.

What is Translation?

A) Translation is a process used to convert functional currency to other reporting currencies at the account balances level.

What is Revaluation?

A) It is process used to revalue assets and liabilities denominated in foreign currency into functional currency based on period end exchange rate we specify. Unrealized gains/losses are resulted because of exchange rate fluctuations which are recorded in unrealized gain/loss account in GL.

What is FSG (Financial Statement Generator)?

A) Financial statement generator feature helps us to generate reports such as balance sheets and income statements with out programming. It also provides a high degree of control on the rows, columns, contents and calculations on the report. Different components such as row set, column set, content set, row order, display set have to be defined before a statement is generated, of which row set and column set are mandatory.

What is Consolidation?

A) Consolidation is a period-end process of combining the financial results of separate business subsidiaries with the parent company to form a single combined statement of financial results.

At what level General Ledger data is secured?

A) GL data is secured at Set of Book level. Sub ledger module data is secured at Responsibility level (i.e., at Operating Unit Level).

understanding BOM Interface

A Guide to Using the Bill of Material (BOM) Open Interface

This paper serves as a reference when using the BOM Open Interface.

It defines the tables used in importing bills and explains the mandatory,

derived and optional columns. Refer to the Oracle Manufacturing, Distribution,

Sales and Service Open Interfaces Manual (A-57332) for a complete set of

instructions.

1. TABLES.

You need to populate following interface tables with data from your legacy

system:

BOM_BILL_OF_MTLS_INTERFACE

BOM_INVENTORY_COMPS_INTERFACE

BOM_ASSY_COMMENTS_INTERFACE

BOM_REF_DESGS_INTERFACE

BOM_SUB_COMPS_INTERFACE|

MTL_ITEM_REVISIONS_INTERFACE

Once you load the data into the interface tables, you can launch the

Bill and Routing Interface program from the Import Bills and Routings

form in Oracle Bills of Material or Oracle Engineering. This program

assigns values, validates the data you include, and then imports the

new bills of material (BOMs).

You can optionally create an item revision when you import a BOM, by

inserting a value for a revision at the same time you insert your BOM data.

If you enter a value in the REVISION column of the BOM_BILL_OF_MTLS_INTERFACE

table, the Bill and Routing Interface program inserts a row into the

MTL_ITEM_REVISIONS_INTERFACE table. In order to assign multiple item revisions,

it is better to insert data directly into the MTL_ITEM_REVISIONS_INTERFACE

table.

In order to import a BOM with components, you need to populate:

BOM_BILL_OF_MTLS_INTERFACE

BOM_INVENTORY_COMPS_INTERFACE

With these two tables, you can create BOM header information and assign

component details.

If you want to assign standard comments, reference designators, and

substitute components to your BOM, you need to populate:

BOM_ASSY_COMMENTS_INTERFACE

BOM_REF_DESGS_INTERFACE

BOM_SUB_COMPS_INTERFACE

PROCESS_FLAG

The column PROCESS_FLAG indicates the current state of processing for a row

in the interface table. All inserted rows must have the PROCESS_FLAG set to 1.

After populating the data into the interface tables, run the Bill and

Routing Interface program. The program assigns and validates all rows

with a status of 1 (Pending), and then imports them into the production

tables. If the assign or validate procedure fails for a row, the program

sets the PROCESS_FLAG to 3 (Assign/Validation Failed) for that row.

The successful rows continue through the process of importing into the

production tables. If a row fails on import, the program assigns a value of

4 (Import Failed) to the PROCESS_FLAG. Successfully imported rows have a

PROCESS_FLAG value of 7 (Import Succeeded).

2. TRANSACTION AND REQUEST ID'S.

The Bill and Routing Interface program automatically updates the

TRANSACTION_ID and REQUEST_ID columns in each of the interface tables.

The column TRANSACION_ID stores a unique id for each row in the interface

table and the REQUEST_ID column stores the concurrent request id number.

3. IMPORT CONSIDERATIONS.

Even though you can import bills and routings simultaneously, all

routing operations must exist before you can assign a component to an

operation. If a routing does not exist, you cannot assign an operation

sequence to a component on a BOM.

You can simultaneously import primary and alternate BOMs. Since the Bill and

Routing Interface program validates data the same way the Define Routing or

Define Engineering Routing form verifies data, you cannot define an alternate

bill if the primary bill does not exist. Therefore, you should import primary

BOMs before importing alternate BOMs. If the program tries to validate an

alternate bill before validating the primary bill, the record fails.

4. BOM_BILL_OF_MTLS_INTERFACE TABLE.

a. REQUIRED COLUMNS FOR BOM_BILL_OF_MTLS_INTERFACE.

You must always enter values for the following required columns

when you insert rows into the BOM_BILL_OF_MTLS_INTERFACE table:

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

ASSEMBLY_TYPE

PROCESS_FLAG

If you create an alternate BOM, you must also enter a value in the

ALTERNATE_BOM_DESIGNATOR column.

If the BOM you import references a common BOM, you must enter a value

in the COMMON_ORGANIZATION_ID and COMMON_ASSEMBLY_ITEM_ID columns, or

you can enter a value in the COMMON_BILL_SEQUENCE_ID column. If the

bill does not reference a common bill, the Bill and Routing interface

program defaults the value of the BILL_SEQUENCE_ID for the

COMMON_BILL_SEQUENCE_ID.

You can specify in the ASSEMBLY_TYPE column whether the BOM is a

manufacturing BOM or an engineering BOM. If you do not include a value

for this column, Oracle Bills of Material defaults a value of 1

(manufacturing), and creates a manufacturing BOM. To create an

engineering bill, you must enter a value of 2 (engineering) for the

ASSEMBLY_TYPE column.

For each new row you insert into the BOM_BILL_OF_MTLS_INTERFACE table,

you should set the PROCESS_FLAG to 1 (Pending).

b. DERIVED/DEFAULTED VALUES FOR BOM_BILL_OF_MTLS_INTERFACE.

The Bill and Routing Interface program derives or defaults most of the

data required to create a manufacturing or an engineering BOM. The

Bill and Routing Interface program derives or defaults the columns

using the same logic as the Define Bill of Material form or the Define

Engineering Bill of Material form. When you populate a column in the

interface table, the program imports the row with the data you included

and does not default a value.

BOM_BILL_OF_MTLS_INTERFACE Derived or Defaulted Value

ASSEMBLY_ITEM_ID From ITEM_NUMBER

ORGANIZATION_ID From ORGANIZATION_CODE

LAST_UPDATE_DATE System Date

LAST_UPDATE_BY Userid

CREATION_DATE System Date

CREATED_BY Userid

COMMON_ASSEMBLY_ITEM_ID From COMMON_ITEM_NUMBER

ASSEMBLY_TYPE 1

COMMON_BILL_SEQUENCE_ID Sequence BOM_INVENTORY_COMPONENTS_S

COMMON_ORGANIZATION_ID From COMMON_ORG_CODE

REQUEST_ID From FND_CONCURRENT_REQUESTS

5. BOM_INVENTORY_COMPS_INTERFACE TABLE.

a. REQUIRED COLUMNS FOR BOM_INVENTORY_COMPS_INTERFACE TABLE.

Each imported record must have a value for the following columns:

PROCESS_FLAG

COMPONENT_ITEM_ID

COMPONENT_SEQUENCE_ID

OPERATION_SEQ_NUM

EFFECTIVITY_DATE

BILL_SEQUENCE_ID

You must also specify a value in the ALTERNATE_BOM_DESIGNATOR column

if you assign components to an alternate BOM and have not entered a

value for the BILL_SEQUENCE_ID column.

When you insert rows into BOM_INVENTORY_COMPS_INTERFACE, you must set

the PROCESS_FLAG to 1 (Pending) for the Bill and Routing Interface

program to process the record.

b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_INVENTORY_COMPS_INTERFACE

The Bill and Routing Interface program derives or defaults most of the

data required to assign components to a BOM. You can optionally include

a value for derived or defaulted columns, as well as data for any of the

other columns in the interface. The interface program uses the same

logic to derive or default column values in the

BOM_INVENTORY_COMPS_INTERFACE table as it does in the

BOM_BILL_OF_MTLS_INTERFACE table. When you populate a column in the

interface table, the program imports the row with the data you included

and does not default a value. However, if you do not enter data in a

derived or defaulted column, the program automatically imports the row

with the derived or defaulted value.

BOM_BILLS_OF_MTLS_INTERFACE Derived or Defaulted Value

COMPONENT_ITEM_ID From COMPONENT_ITEM_NUMBER

LAST_UPDATE_DATE System Date

LAST_UPDATE_BY Userid

CREATION_DATE System Date

CREATED_BY Userid

ITEM_NUM 1

COMPONENT_QUANTITY 1

COMPONENT_YIELD_FACTOR 1

PLANNING_FACTOR 100

QUANTITY_RELATED 2

SO_BASIS 2

OPTIONAL 2

MUTUALLY_EXCLUSIVE_OPTIONS 2

INCLUDE_IN_COST_ROLLUP 1

CHECK_ATP 2

REQUIRED_TO_SHIP 2

REQUIRED_FOR_REVENUE 2

INCLUDE_ON_SHIP_DOC 2

COMPONENT_SEQUENCE_ID Sequence, BOM_INVENTORY_COMPONENTS_S

BILL_SEQUENCE_ID From BOM_BILL_OF_MTLS_INTERFACE or

BOM_BILL_OF_MATERIALS

WIP_SUPPLY_TYPE 1

SUPPLY_LOCATOR_ID From LOCATION_NAME

ASSEMBLY_ITEM_ID From ASSEMBLY_ITEM_NUMBER

ORGANIZATION_ID From ORGANIZATION_CODE

SUBSTITUTE_COMP_ID From SUBSTITUTE_COMP_NUMBER

REQUEST_ID From FND_CONCURRENT_REQUEST

6. IMPORTING ADDITIONAL BILL INFORMATION

When you create BOMs and assign components using the Bill and Routing Interface

program, you can also import additional BOM information using three different

interface tables. You can import standard comments for each BOM using the

BOM_ASSY_COMMENTS_INTERFACE table. You can assign component reference

designators using the BOM_REF_DESGS_INTERFACE table and substitute components

using the BOM_SUB_COMPS_INTERFACE table.

You can assign standard comments to any bill of material type. However, only

standard components assigned to standard, model, and option class BOMs can have

reference designators and substitute components.

If you insert data in the BOM_REF_DESGS_INTERFACE or BOM_SUB_COMPS_INTERFACE

tables for a planning bill, the Bill and Routing Interface program fails to

import the record and sets the PROCESS_FLAG to 3 (Assign/Validation Failed).

7. BOM_ASSY_COMMENTS_INTERFACE TABLE.

a. REQUIRED COLUMNS FOR THE BOM_ASSY_COMMENTS_INTERFACE TABLE.

To import data into the BOM_ASSY_COMMENTS_INTERFACE table, you must

assign a value to the following columns:

STANDARD_REMARKS_DESIGNATOR

BILL_SEQUENCE_ID

PROCESS_FLAG

b. DERIVED/DEFAULTED COLUMN VALUES FOR BOM_ASSY_COMMENTS_INTERFACE.

After inserting data into the BOM_ASSY_COMMENTS_INTERFACE table, the

Bill and Routing Interface program derives the value for the

BILL_SEQUENCE_ID column if you assign values to the columns:

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

ALTERNATE_BOM_DESIGNATOR

8. BOM_REF_DESGS_INTERFACE AND BOM_SUBS_COMPS_INTERFACE TABLES.

a. REQUIRED COLUMNS FOR BOM_REF_DESGS_INTERFACE TABLE.

You can only import data into the BOM_REF_DESGS_INTERFACE table for

standard components assigned to standard, model and option class BOMs.

You must assign values to the following columns:

COMPONENT_REFERENCE_DESIGNATOR

COMPONENT_SEQUENCE_ID

PROCESS_FLAG

b. REQUIRED COLUMNS FOR BOM_SUBS_COMPS_INTERFACE TABLE.

You can only import data into the BOM_SUBS_COMPS_INTERFACE table for

standard components assigned to standard, model and option class BOMs.

You must assign values to the following columns:

SUBSTITUTE_COMPONENT_ID

SUBSTITUTE_ITEM_QUANTITY

COMPONENT_SEQUENCE_ID

PROCESS_FLAG

c. DERIVED/DEFAULT VALUES FOR BOM_REF_DESGS_INTERFACE AND

BOM_SUB_COMPS_INTERFACE.

After inserting data into the BOM_REF_DESGS_INTERFACE or

BOM_SUB_COMPS_INTERFACE tables, the Bill and Routing Interface program

derives the value for the BILL_SEQUENCE_ID column if you assign values

to the following columns:

ASSEMBLY_ITEM_ID

ORGANIZATION_ID

ALTERNATE_BOM_DESIGNATOR

The program also assigns a value for the COMPONENT_SEQUENCE_ID column

if you enter values into the following columns:

BILL_SEQUENCE_ID

COMPONENT_ITEM_ID

OPERATION_SEQ_NUM

EFFECTIIVITY_DATE

9. VALIDATING INTERFACE TABLE ROWS.

After you load the BOM and component data, the Bill and Routing Interface

program validates the required data for the six interface tables. BOM

validation insures that each row has an included or defaulted value for all the

required columns and verifies the same way as the Define Bill of Material form

and the Define Engineering Bill of Material form validate manually entered

bills. For example, you cannot import a standard bill and assign model, option

class or planning items as components.

If the Bill and Routing Interface program cannot assign a value to a row or

validate that row, the program sets the PROCESS_FLAG to 3 (Assign/Validation

Failed) and inserts a row in the MTL_INTERFACE_ERRORS table.

To identify the error message for a failed row, the program automatically

populates the UNIQUE_ID column in the error interface table with the same

value as the TRANSACTION_ID value. Each error has a value for the

MESSAGE_NAME and REQUEST_ID columns in the error interface table. The

MESSAGE_NAME column corresponds to messages stored in the Oracle Application

Message Dictionary. The REQUEST_ID column stores the concurrent request id.

If the program detects any internal database error, the program stores the

internal error in the MESSAGE_NAME column and stores the specific database

error message in the ERROR_MESSAGE column.

If you import a BOM with multiple components and one of the components fails

validation, then the bill will be created without the failed component. If,

however, the row in the BOM_BILL_OF_MTLS_INTERFACE table fails, the BOM and

and all of its details are not imported.

10. CORRECTING FAILED ROWS.

You can review and report rows in the interface tables using SQL*Plus

or any custom report you develop. Since all rows in the interface

table have a value for PROCESS_FLAG, you can easily identify records

that are successfully imported into Oracle Bill of Material and Oracle

Engineering, or records that failed validation or import. You can also

identify individual records by the unique value for the TRANSACTION_ID

column.

You can update any row from the interface tables using SQL*Plus. If you

update a row to resolve invalid data, you must set the PROCESS_FLAG to 1

(Pending) for that row.

If you delete a failed row and insert a replacement row, you should set

the PROCESS_FLAG to 1 (Pending) for the new row. When you resubmit the Bill

and Routing Interface program, all rows pending validation are processed.

Following are Interface and there respective tables Used in Oracle BOM and WIP

Oracle Bill of Material

Open Bills of Material Interface

1. BOM_BILL_OF_MTLS_INTERFACE 2. BOM_INVENTORY_COMPS_INTERFACE

3. BOM_REF_DESGS_INTERFACE 4. BOM_SUB_COMPS_INTERFACE

5. MTL_ITEM_REVISIONS_INTERFACE

Open Bills of Material Tables

1. bom_bill_of_materials 2. bom_inventory_components 3. bom_reference_designators

4. bom_substitute_components 5. mtl_item_revisions

Oracle Routing

Open Routing Interface

1. BOM_OP_ROUTINGS_INTERFACE 2. BOM_OP_SEQUENCES_INTERFACE

3. BOM_OP_RESOURCES_INTERFACE 4. MTL_RTG_ITEM_REVS_INTERFACE

Open Routing Tables

1. bom_operational_routings 2. bom_operation_sequences 3. bom_operation_resources