Key Tables for General Ledger

Key Tables for General Ledger

Key Tables for General Ledger
Key Table Name	Key Table Description
GL_ACCOUNT_HIERARCHIES	It stores lists of the detail accounts associated with each summary account. When you create a summary account, the list of its detail accounts is stored in this table, one detail account per row. This table stores one such list for each summary account.
GL_ALLOC_BATCHES	It stores information about Mass Allocation and Mass Budget batches. Each row includes a batch’s unique ID, name, status, and description. This table corresponds to the Define Mass Allocations and Define Mass Budget windows of the Define Mass Allocations and Define Mass Budgets forms.
GL_ALLOC_FORMULAS	It stores information about Mass Allocation and Mass Budget formulas.
GL_ALLOC_FORMULA_LINES	It stores information about Mass Allocation and Mass Budget formula lines. This table corresponds to the Formula window of the Define Mass Allocations and Define Mass Budgets forms. There are four or five lines associated with each Mass Budget formula, and there are five lines associated with each Mass Allocation formula.
GL_ALLOC_HISTORY	It stores one row for each batch produced by generating Mass Allocation and Mass Budget journals. Use information in this table to determine when you generated your Mass Allocation and Mass Budget journals, and for which accounting periods.
GL_BALANCES	It stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
GL_BUDGETS	It stores information about your budgets. Each row includes a budget’s name, first and last periods, date created, and status. This table corresponds to the Define Budget form. Oracle General Ledger supports only one budget type (’STANDARD’), so you can uniquely identify a row with only the budget name. The CURRENT_VERSION_ID column is not currently used.
GL_BUDGET_ASSIGNMENTS	It stores the accounts that are assigned to each budget organization.
GL_BUDGET_ASSIGNMENT_RANGES	It stores the account ranges that you want to assign to a budget organization.
GL_INTERFACE	It is the table you use to import journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals form to create journal batches. You must supply values for all NOT NULL columns.

GL_DAILY_RATES_INTERFACE is the interface you use to create, update, and delete daily conversion rates. Customers should use this table to load rates into the GL_DAILY_RATES table.
Foreign Keys
Primary Key Table Primary Key Column Foreign Key Column
FND_CURRENCIES CURRENCY_CODE FROM_CURRENCY
FND_CURRENCIES CURRENCY_CODE TO_CURRENCY
FND_USER USER_ID USER_ID
GL_DAILY_CONVERSION_TYPES USER_CONVERSION_TYPE USER_CONVERSION_TYPE
Not null columns
FROM_CURRENCY
TO_CURRENCY
FROM_CONVERSION_DATE
TO_CONVERSION_DATE
USER_CONVERSION_TYPE
CONVERSION_RATE
MODE_FLAG

GL_IEA_INTERFACE is used to import data into the Global Intercompany System from external systems. The data entered into this table is processed by the GIS Import program.
Foreign Keys
Primary Key Table Primary Key Column Foreign Key Column
FND_CONCURRENT _REQUESTS REQUEST_ID REQUEST_ID
FND_CURRENCIES CURRENCY_CODE CURRENCY_CODE
GL_CODE_COMBINATIONS CODE_COMBINATION_ID SENDER_CODE_COMBINATION_ID
GL_CODE_COMBINATIONS CODE_COMBINATION_ID SENDER_CODE_COMBINATION_ID
GL_IEA_SUBSIDIARIES SUBSIDIARY_ID SENDER_SUBSIDIARY_ID
GL_IEA_SUBSIDIARIES SUBSIDIARY_ID RECEIVER_SUBSIDIARY_ID
GL_IEA_TRANSACTION_TYPES TRANSACTION_TYPE_ID TRANSACTION_TYPE_ID
NOT NULL Columns
GROUP_ID
TRANSACTION_TYPE_ID
TRANSACTION_STATUS_CODE
CURRENCY_CODE
GL_DATE
SENDER_SUBSIDIARY_ID
RECEIVER_SUBSIDIARY_ID
LINE_TYPE

How to view org-specific data in a Multi-Org environment

Background:

This article explain how to access org-specific data from Sqlplus in Multi-Org (MOAC) environment

Solution:
You have to run the following:

begin
 fnd_global.apps_initialize(&user_id,&responsibility_id,&responsibility_application_id);
 mo_global.init('&product_short_name');
end;

To find the user_id and responsibility_id:
select user_id 
  from fnd_user 
 where user_name = <username>;

select responsibility_id 
  from fnd_responsibility_tl
 where responsibility_name = <responsibility_name>;

Example:

select user_id
  from fnd_user
 where user_name = 'USER1';
-- returns 12345 (say)

select responsibility_id
  from fnd_responsibility_tl
 where responsibility_name = 'Receivables Manager';
-- returns 20678

begin
 fnd_global.apps_initialize(12345, 20678, 222);
 mo_global.init('AR');
end;

After the above, if the user USER1 has access to the responsibility 'Receivables Manager'  org specific data can be viewed from sqlplus.

Note:
Alternatively, you can set the Org Context.
Provide operating_unit_id for the following.

exec apps.fnd_client_info.set_org_context ('&org_id');

List of all Responsibilites and their associated Menus

Background:

While defining Responsibility, we assign a Menu to it.
When running the following query, it lists all responsibilities and their associated Menu's.

Query:
SELECT DISTINCT frt.responsibility_name, fmt.user_menu_name
  FROM apps.fnd_responsibility_tl frt,
       apps.fnd_responsibility fr,
       apps.fnd_menus_tl fmt,
       apps.fnd_menus fm,
       apps.fnd_application_tl fat,
       apps.fnd_application fa
 WHERE     frt.responsibility_id(+) = fr.responsibility_id
       AND fr.menu_id = fmt.menu_id
       AND fr.menu_id = fm.menu_id
       AND fat.application_id = fa.application_id
       AND fa.application_id = fr.application_id
       AND frt.LANGUAGE = 'US';

Query to find Values of a lookup

Business Case:

It is tedious to check values of a lookup from the application.
Use this query to find the values to find out from database.

Queries:
Lookup Information:

select *
  from fnd_lookup_types_VL
 Where lookup_type = '&Enter_lookup_type';

=> Tip:You can use the above table to find lookups available in the system

Values of a lookup:

select *
  from fnd_lookup_values
 Where lookup_type = '&Enter_lookup_type';

Script to find Concurrent Programs of a Request Set and Vice Versa

Background:

To run bunch of concurrent programs sequentially or parallel we use Request Set

Query 1:
Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

Query 2:

Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

Setting a profile option from backend - Example

Background:

Sets the value of a profile option permanently to the database, at any level.
Note: This routine will NOT actually commit the changes. The caller must commit.
returns TRUE if successful, FALSE if failure.

Syntax:
FND_PROFILE.SAVE(<Profile_Option_Name>, <Profile_Option_Value>, <Level SITE/APPL/RESP/USER>, <Level_Value>, <Level_Value_App_id>);

Parameters Explanation:
Profile_Option_Name : Profile name you are setting 
Profile_Option_Value : Profile value you are setting
Level                          : Level that you're setting at. It could be 'SITE', 'APPL','RESP', or 'USER' 
Level_Value               : Level value that you are setting at,
                                    e.g. user id for 'USER' level. This is not used at site level
Level_Value_App_id  : Only used for 'RESP' level. Application_Id of the responsibility.

Useful tables: 
select * from fnd_profile_options;
select * from fnd_profile_option_values;

Examples:
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'SITE');
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'APPL', 321532);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'RESP', 321532, 345234);
FND_PROFILE.SAVE('P_NAME', 'P_VAL', 'USER', 123321);