Deriving Oracle GL Account Code Combination ID’s (CCID’s) through APIs


1] FND_FLEX_EXT.GET_COMBINATION_ID:

This API Finds combination_id for given set of key flexfield segment values. Segment values must be input in segments(1) – segments(n_segments) in the order displayed.

It also creates a new combination if it is valid and the flexfield allows dynamic inserts and the combination does not already exist. It commit the transaction soon after calling this function since if a combination is created it will prevent other users creating similar combinations on any flexfield until a commit is issued.

It performs all checks on values including security and cross-validation. Value security rules will be checked for the current user identified in the FND_GLOBAL package.

Generally pass in SYSDATE for validation date. If validation date is null, this function considers expired values valid and checks all cross-validation rules even if they are outdated.

This function returns TRUE if combination valid or FALSE and sets error message using FND_MESSAGE utility on error or if invalid. If this function returns FALSE, use GET_MESSAGE to get the text of the error message in the language of the database, or GET_ENCODED_MESSAGE to get the error message in a language-independent encoded format.

The Combination_id output may be NULL if combination is invalid.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_application_short_name VARCHAR2(240);
  l_key_flex_code          VARCHAR2(240);
  l_structure_num          NUMBER;
  l_validation_date        DATE;
  n_segments               NUMBER;
  SEGMENTS                 APPS.FND_FLEX_EXT.SEGMENTARRAY;
  l_combination_id         NUMBER;
  l_data_set               NUMBER;
  l_return                 BOOLEAN;
  l_message                VARCHAR2(240);
BEGIN
  l_application_short_name := 'SQLGL';
  l_key_flex_code          := 'GL#';

  SELECT id_flex_num
  INTO l_structure_num
  FROM apps.fnd_id_flex_structures
  WHERE ID_FLEX_CODE        = 'GL#'
  AND ID_FLEX_STRUCTURE_CODE=<ACCOUNTING_FLEXFIELD>;

  l_validation_date        := SYSDATE;
  n_segments               := 6;
  segments(1)              := '00101';
  segments(2)              := '28506';
  segments(3)              := '00000';
  segments(4)              := '09063';
  segments(5)              := '00000';
  segments(6)              := '00000';
  l_data_set               := NULL;

  l_return := FND_FLEX_EXT.GET_COMBINATION_ID(
                    application_short_name => l_application_short_name,
                    key_flex_code          => l_key_flex_code,
                    structure_number       => l_structure_num,
                    validation_date        => l_validation_date,
                    n_segments             => n_segments,
                    segments               => segments,
                    combination_id         => l_combination_id,
                    data_set               => l_data_set
                    );
  l_message:= FND_FLEX_EXT.GET_MESSAGE;

  IF l_return THEN
    DBMS_OUTPUT.PUT_LINE('l_Return = TRUE');
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID = ' || l_combination_id);
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error: '||l_message);
  END IF;
END;

2] FND_FLEX_EXT.get_ccid:

This API gets combination id for the specified key flexfield segments.It is identical to get_combination_id() except this function takes segment values in a string concatenated by the segment  delimiter for this flexfield, and returns a positive combination id if valid or 0 on error.

3] FND_FLEX_KEYVAL.VALIDATE_SEGS:

These key flexfields server validations API are a low level interface to key flexfields validation.  They are designed to allow access to all the flexfields functionality, and to allow the user to get only the information they need in return.  Because of their generality, these functions are more difficult to use than those in the FND_FLEX_EXT package.  Oracle strongly suggests using the functions in FND_FLEX_EXT package if at all possible.

This function finds combination from given segment values.  Segments are passed in as a concatenated string in increasing order of segment_number (display order).

Various Operations that can be performed are:

  • ‘FIND_COMBINATION’ – Combination must already exist.
  • ‘CREATE_COMBINATION’ – Combination is created if doesn’t exist.
  • ‘CREATE_COMB_NO_AT’ – same as create_combination but does not use an autonomous transaction.
  • ‘CHECK_COMBINATION’ – Checks if combination valid, doesn’t create.
  • ‘DEFAULT_COMBINATION’ – Returns minimal default combination.
  • ‘CHECK_SEGMENTS’ – Validates segments individually.

If validation date is NULL checks all cross-validation rules. It returns TRUE if combination valid or FALSE and sets error message on server if invalid. Use the default values if you do not want any special functionality.

Example: (Tested in R12.1.3)

SET serveroutput ON;
DECLARE
  l_segment1   GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2   GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3   GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4   GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5   GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6   GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination BOOLEAN;
  l_cr_combination    BOOLEAN;
  l_ccid       GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1                 VARCHAR2(240);
  p_error_msg2                 VARCHAR2(240);
BEGIN
  l_segment1  := '00101';
  l_segment2  := '28506';
  l_segment3  := '00000';
  l_segment4  := '14302';
  l_segment5  := '00455';
  l_segment6  := '00000';
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;
  BEGIN
    SELECT id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;
  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid:=NULL;
  END;
  IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
  ELSE
  DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
        DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);
      ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;
Advertisements

Few Interesting Questions on Oracle GL Journals Entry


Few Interesting Questions on Oracle GL Journals Entry


Is There a Report That Displays Information of One Specific Journal Entry Unposted/Posted?

No. General Ledger reports display information of journal batches posted or unposted. However, you can use the below sql query to find information of a particular journal entry.

select  b.je_batch_id batch_id ,
        h.je_header_id header_id ,
        l.je_line_num line ,
        l.code_combination_id ccid ,
        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||
        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||
        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||
        '.' || g.segment10 combination ,
        l.entered_dr entered_dr,
        l.entered_cr entered_cr,
        l.accounted_dr accounted_dr,
        l.accounted_cr accounted_cr,
        l.status
from    gl_je_lines l,
        gl_je_headers h,
        gl_je_batches b,
        gl_code_combinations g
where   b.je_batch_id = h.je_batch_id
        and h.je_header_id = &je_header_id
        and l.je_header_id = h.je_header_id
        and h.je_batch_id = b.je_batch_id
        and l.code_combination_id = g.code_combination_id
order by h.je_header_id, l.je_line_num;

Can a Posted General Ledger Journal Entry be deleted?

After a journal entry is posted, it cannot be deleted. Posted journal entries cannot be deleted because that would eliminate the audit trail. To nullify the accounting effect of the posted journal entry, you can reverse it.

When can not a journal batch be deleted or modified?

A journal batch cannot be deleted or modified under the following circumstances:

a. The source is frozen

b. Funds have been reserved for the batch

c. Funds are in the process of being reserved for the batch

d. The batch is in the process of being posted

e. The batch is posted

f. The batch is approved

g. The batch is in the process of being approved

A journal batch should not be updated if it comes from a sub-ledger.

Changing accounting information in a journal that originated in a sub-module will unsynchronize the accounting information between the ledger and the sub-ledger. Instead of changing the sub-ledger journal, define a new journal to adjust the accounting information if necessary.

A journal batch that has funds reversed cannot be updated because the funds would not be re-reserved appropriately.

Which report shows details of a posted journal batch?

Journals – General (180 Char) and Journals – General (132 Char)

Is possible to restrict users from entering negative amounts in journal lines?

Unfortunately, it is not possible to restrict users from entering negative amounts in journal entry lines.

How to set up journal approval in General Ledger?

This is set up using Oracle Workflow Builder. The basics steps to setup Journal Approval are as below

a) Enable Journal Approval at the Ledger level
b) Setup Journal Sources for Journal Approval
c) Configure the profile options that control how the approval list will be built
d) Define Employees and Supervisors
e) Define Approval limits for approvers
f) Associate the employees to Oracle Apps users
g) Optional Workflow Configuration

For more information refer metalink notes: ID 176459.1 & ID 278349.1

How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?

  1. Query the Journal that needs the spreadsheet attachment.

  2. Click on the paperclip on the tool bar.

  3. Fill the following fields in the Attachment form.

     Category    –  Choose Journal from LOV

     Description –  optional

     Data Type   –  OLE Object from the LOV

  4. Right click on the large white portion of the Attachment form choose ‘Insert Object’ from the drop box.

  5. When the Insert Object Form appears check “create from file” and click on “Browse” to choose the file that should be attached from the directory structure.

  6. Save.

How do you easily copy a journal entry from one set of books to another?

There is no standard feature to copy journal entries between sets of books. However, there are some alternatives. Refer Metalink note: ID 204082.1

How to prevent user’s ability to reverse unposted journals?

For 11i, there is not a method to prevent users from reversing unposted journals. This is intended functionality to incorporate the maximum flexibility that users may require. However you can limit user access to journal reversals through user menus set up in Sys Admin responsibility.

This functionality changed in R12 – see Note 734848.1 In Release 12, a batch must be posted before it can be reversed.

How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?

If you routinely generate and post large numbers of journal reversals as part of your month end closing and opening procedures, you can save time and reduce entry errors by using Automatic Journal Reversal to automatically generate and post your journal reversals.

First you define journal reversal criteria for journal categories. Journal reversal criteria let you specify the reversal method, period and date. You can also choose to enable automatic generation and posting of journals.

When you create a journal entry you want to automatically reverse, specify a journal category that has assigned reversal criteria. Your journal will be reversed based on the method, period and date criteria you defined for that journal category.

In Release 12, a reversal journal that is Unposted cannot be modified. Why?

This is the expected functionality in Release 12. However the profile GL: Edit Reverse Journals can be set to allow the modification. Refer metalink note: ID 567641.1

Reversing journal was deleted from the system, how can you still reverse the original journal?

General Ledger does not allow you to reverse a journal entry twice. . Refer metalink note: ID 145043.1 for details.

A journal entry with a source set up for automatic reversal is not reversed. Why?

General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted manually.

A journal has been created and is unposted.  The following period has a reversing journal for the original journal and it is posted. Why it is so?

This is currently the functionality of the application to allow the reversing journal to be posted even if the original journal is not.

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

GL Tables


GL Tables

General Ledger tables can be grossly classified into following 5 categories. Here are few important tables in each category.

Ledgers Tables:

GL_LEDGERS: Stores information about the ledgers defined in the Accounting Setup Manager and the ledger sets defined in the Ledger Set form. Each row includes the ledger or ledger set name, short name, description, ledger currency, calendar, period type, chart of accounts, and other information.

GL_CODE_COMBINATIONS: Stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application.

Period Tables:

GL_PERIODS: Stores information about the accounting periods you define using the Accounting Calendar form.

GL_PERIOD_SETS: Stores the calendars you define using the Accounting Calendar form.

GL_PERIOD_TYPES: Stores the period types you define using the Period Types form. Each row includes the period type name, the number of periods per fiscal year, and other information.

Journal Tables:

GL_JE_BATCHES: Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information.

GL_JE_HEADERS: Stores journal entries. There is a one-to-many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry.

GL_JE_LINES: Stores the journal entry lines that you enter in the Enter Journals form. There is a one-to-many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line.

GL_JE_SOURCES: Stores journal entry source names and descriptions. Each journal entry in your Oracle General Ledger application is assigned a source name to indicate how it was created. This table corresponds to the Journal Sources form.

GL_JE_CATEGORIES: Stores journal entry categories. Each row includes the category name and description.

Conversion and consolidation tables:

GL_CONSOLIDATION: Stores information about your consolidation mappings. Each row includes a mapping’s ID, name, description, and other information. This table corresponds to the first window of the Consolidation Mappings form. You need one row for each consolidation mapping you define.

GL_CONSOLIDATION_ACCOUNTS: Stores the account ranges that you enter when you consolidate balances using the Transfer Consolidation Data form. This table corresponds to the Account Ranges window of the Transfer Consolidation Data form.

GL_DAILY_RATES: Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type.

GL_DAILY_BALANCES: Stores daily aggregate balances for detail and summary balance sheet accounts in sets of books with average balances enabled.

Budgeting tables:

GL_BUDGET_TYPES: Stores information about budget types. Oracle General Ledger supports only one budget type, ‘STANDARD’. Therefore, this table always contains only one row.

GL_BUDGET_ASSIGNMENTS: Stores the accounts that are assigned to each budget organization. Each row includes the currency assigned to the account and the entry code for the account. The entry code is either ‘E’ for entered or ‘C’ for calculated. This table corresponds to the Account Assignments window of the Define Budget Organization form.

GL_BUDGET_INTERIM: It is used internally by Oracle General Ledger applications to post budget balances to the GL_BALANCES table. Rows are added to this table whenever you run the budget posting program. The budget posting program updates the appropriate budget balances in GL_BALANCES based on the rows in this table, and then deletes the rows in this table that it used.

Interface Tables:

GL_INTERFACE: It is used to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches.

GL_INTERFACE_CONTROL: It is used to control Journal Import execution. Whenever you start Journal Import from the Import Journals form, a row is inserted into this table for each source and group id that you specified. When Journal Import completes, it deletes these rows from the table.

GL_BUDGET_INTERFACE: It is used to upload budget data into your Oracle General Ledger application from a spreadsheet program or other external source. Each row includes one fiscal year’s worth of budget amounts for an account.

Query to find accounting flexfield structure


Query to find accounting flexfield structure

select sob.name Ledger_Name
, sob.ledger_id Ledger_Id
, sob.chart_of_accounts_id coa_id
, fifst.id_flex_structure_name struct_name
, ifs.segment_name
, ifs.application_column_name column_name
, sav1.attribute_value BALANCING
, sav2.attribute_value COST_CENTER
, sav3.attribute_value NATURAL_ACCOUNT
, sav4.attribute_value INTERCOMPANY
, sav5.attribute_value SECONDARY_TRACKING
, sav6.attribute_value GLOBAL
, ffvs.flex_value_set_name
, ffvs.flex_value_set_id
from fnd_id_flex_structures fifs
, fnd_id_flex_structures_tl fifst
, fnd_segment_attribute_values sav1
, fnd_segment_attribute_values sav2
, fnd_segment_attribute_values sav3
, fnd_segment_attribute_values sav4
, fnd_segment_attribute_values sav5
, fnd_segment_attribute_values sav6
, fnd_id_flex_segments ifs
, fnd_flex_value_sets ffvs
, gl_ledgers sob
where 1=1
and fifs.id_flex_code = 'GL#'
and fifs.application_id = fifst.application_id
and fifs.id_flex_code = fifst.id_flex_code
and fifs.id_flex_num = fifst.id_flex_num
and fifs.application_id = ifs.application_id
and fifs.id_flex_code = ifs.id_flex_code
and fifs.id_flex_num = ifs.id_flex_num
and sav1.application_id = ifs.application_id
and sav1.id_flex_code = ifs.id_flex_code
and sav1.id_flex_num = ifs.id_flex_num
and sav1.application_column_name = ifs.application_column_name
and sav2.application_id = ifs.application_id
and sav2.id_flex_code = ifs.id_flex_code
and sav2.id_flex_num = ifs.id_flex_num
and sav2.application_column_name = ifs.application_column_name
and sav3.application_id = ifs.application_id
and sav3.id_flex_code = ifs.id_flex_code
and sav3.id_flex_num = ifs.id_flex_num
and sav3.application_column_name = ifs.application_column_name
and sav4.application_id = ifs.application_id
and sav4.id_flex_code = ifs.id_flex_code
and sav4.id_flex_num = ifs.id_flex_num
and sav4.application_column_name = ifs.application_column_name
and sav5.application_id = ifs.application_id
and sav5.id_flex_code = ifs.id_flex_code
and sav5.id_flex_num = ifs.id_flex_num
and sav5.application_column_name = ifs.application_column_name
and sav6.application_id = ifs.application_id
and sav6.id_flex_code = ifs.id_flex_code
and sav6.id_flex_num = ifs.id_flex_num
and sav6.application_column_name = ifs.application_column_name
and sav1.segment_attribute_type = 'GL_BALANCING'
and sav2.segment_attribute_type = 'FA_COST_CTR'
and sav3.segment_attribute_type = 'GL_ACCOUNT'
and sav4.segment_attribute_type = 'GL_INTERCOMPANY'
and sav5.segment_attribute_type = 'GL_SECONDARY_TRACKING'
and sav6.segment_attribute_type = 'GL_GLOBAL'
and ifs.id_flex_num = sob.chart_of_accounts_id
and ifs.flex_value_set_id = ffvs.flex_value_set_id
and sob.ledger_id =
nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.ledger_id)
order by sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

Note: The query is tested in R12 Environment.

GL Budget Interface


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