GL Budget Interface and its process

GL Budget Interface and its process

For Budget interface can be entered by 2 ways, either

1.)   to GL_INTERFACE table To import multi-currency data (Currency conversion) or

2.)   to GL_BUDGET_INTERFACE to import multi-currency data (Currency conversion)

1.) The following process is explained from GL_INTERFACE table , Other sample codes will get from blog spot GL Interface and Its process

To import budget data (Budget):

Load budget data into the GL_INTERFACE table the same way you load regular data.

The only difference is that you must enter the value B in the ACTUAL_FLAG column

and the appropriate budget version ID in the BUDGET_VERSION_ID column of the

GL_INTERFACE  table.

 You must enter a valid period name for budget journal batches created by Journal

Import. Use the PERIOD_NAME column to enter a valid batch period whenever

you specify the value B in the ACTUAL_FLAG column of the GL_INTERFACE

table.

Note: All budget journals in a batch must have the same ledger.

2.) The following process is explained from GL_BUDGET_INTERFACE table

Procedure:

1) We populate the Staging Table

2) Performed Validation on Staging Table.

3) Populate GL_BUDGET_INTERFACE

4) Used Standard Concurrent Program : Upload Budget from Window

GL Budget Interface Steps

Interface Table:

GL_BUDGET_INTERFACE

Base Tables:

GL_BUDGETS

GL_BUDGET_ASSIGNMENTS

GL_BUDGET_TYPES

Standard Concurrent Program = Upload Budget

Create a Staging table based on the requirement

CREATE TABLE XXGL_BUDGET_STG

(

STG_BUDGET_NAME     VARCHAR2 (15),

STG_BUDGET_ENTITY_NAME  VARCHAR2 (15),

STG_CURRENCY_CODE   VARCHAR2 (15),

STG_FISCAL_YEAR        DATE,

STG_UPDATE_LOGIC_TYPE   VARCHAR2 (15),

STG_SET_OF_BOOKS_ID     NUMBER,

STG_BUDGET_VERSION_ID   NUMBER,

STG_PERIOD1_AMOUNT      NUMBER,

STG_PERIOD2_AMOUNT      NUMBER,

STG_PERIOD3_AMOUNT      NUMBER,

STG_PERIOD4_AMOUNT      NUMBER,

STG_PERIOD5_AMOUNT      NUMBER,

STG_PERIOD6_AMOUNT      NUMBER,

STG_SEGMENT1                VARCHAR2 (15),

STG_SEGMENT2                VARCHAR2 (15),

STG_SEGMENT3                VARCHAR2 (15),

STG_SEGMENT4                VARCHAR2 (15),

STG_SEGMENT5                VARCHAR2 (15),

STG_SEGMENT6                VARCHAR2 (15),

STG_VALIDATION              VARCHAR2 (15),

STG_ERROR                       VARCHAR2 (15));

Inserting Data into Staging Table:

Insert into XXGL_BUDGET_STG Values (

'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');

Moving the Data from Flat File to Base Table using SQL * LOADER:

options(skip=1)

load data

errors=5

infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'

Insert into table XXGL_BUDGET_STG

fields terminated by ','

optionally enclosed by '"'

Trailing nullcols(stg_budget_name,stg_budget_entity_name,stg_currency_code,

stg_fiscal_year,stg_update_logic_type,stg_set_of_books_id,stg_budget_version_id,

stg_period1_amount,stg_period2_amount,stg_period3_amount,stg_period4_amount,

stg_period5_amount,stg_period6_amount,stg_segment1,stg_segment2,stg_segment3,

stg_segment4,stg_segment5,stg_segment6,stg_validation,stg_error )

Create a Package with validations to move the data into Interface Tables

Write the code here for validation.

GL Budget Interface

This program lets you prepare and analyze your budget outside of General Ledger and then transfer your budget information into General Ledger. This enables you to perform your budgeting in the environment you choose, and still maintain the integrity of your database.

Interface Table:

GL_BUDGET_INTERFACE is used to upload budget data into your Oracle General Ledger application from an external source.

Each row includes one fiscal year’s worth of budget amounts for an account. When you load this table, you must supply all NOT NULL columns with data.

In addition, you must supply a valid account combination in the SEGMENT columns appropriate to your chart of accounts. Finally, you must supply the budget amounts in the appropriate AMOUNT columns.

The mandatory (not null) columns of the Interface table are:

·         BUDGET_NAME

·         BUDGET_ENTITY_NAME (the budget organization)

·         CURRENCY_CODE

·         FISCAL_YEAR

·         UPDATE_LOGIC_TYPE (A for Add, R for Replace)

Other important columns are:

·         SET_OF_BOOKS_ID

·         BUDGET_VERSION_ID

·         PERIOD1_AMOUNT through PERIOD60_AMOUNT

·         SEGMENT1 through SEGMENT30

Validations:

·         Budget Name and Budget Entity Name

·         Currency Code

·         Account Segments(Code Combination in GL_CODE_COMBINATIONS Table)

To upload a budget:

1.    Navigate to the Upload Budget window (Budgets > Enter > Upload).

2.    Enter the Budget and Budget Organization.

3.    click Upload.

General Ledger submits a concurrent process to upload budget information from the GL_BUDGET_INTERFACE table.

Budget Upload Validation:

Budget Upload validates all of your budget information for compatibility with General Ledger. Budget Upload also checks to make sure that the following conditions are true:

• Your account is assigned to a budget organization

• The budget entry type for your account is Entered

• Your budget is not Frozen

• Your budget organization is not Frozen

• Your budget fiscal year is open for your budget

Once updated, General Ledger automatically deletes the rows of budget records in the Budget Interface table.

Base Tables:

·         GL_BUDGETS

·         GL_BUDGET_ASSIGNMENTS

·         GL_BUDGET_TYPES

For more information see Oracle General Ledger User Guide