Interfaces in Oracle EBS
Interfaces are the tables provided by Oracle applications to determine which data elements or fields required according to the standard tables or the base tables of oracle applications which are used to store customers, suppliers, invoices etc.
For the most of the interface tables application is going to provide import programs.
Staging Tables must be defined according to the interface table and flat files
PL/SQL Procedures: this program must be defined according to mapping
Data Mapping
· The data mapping has to be determined according to our interface tables. According to data mapping we specify which columns are required from legacy system
· Which columns data must be converted (Data Conversions)
· Which columns are extra in legacy system which are required in Oracle apps. Sol: DFF in legacy system
· Specify the validations required for rows and columns
Interface = Transfer Data
There are two types of Interfaces
1. Inbound Interface
2. Outbound Interface
Inbound Interface: If the data is to be uploaded from any legacy system to Oracle apps the interface is called Inbound
Outbound Interfaces: If any data is supposed to be sent from Oracle to any other system is called Outbound.
I have explained with examples for AP
In AP we have two types of Interfaces
1. Payables Open Invoice Interface: Import Invoice info into AP from any source/application
2. Supplier Interface: Import Suppliers Information into AP from any source/Application
What is Table? Within Interface we have tables. Ex: From AP to GL the data is flown through Interface tables. If we want to stop the data within Interface tables we can stop, the data within Interface table is held until import into Application. When transferring data from AP to GL, first we have to send the data to Interface tables and next step is to import into application through base tables in GL. We do have specific tables for each application which we call as Base Tables.
Interface Tables Vs Bases Tables
Interface Tables can be seen within Interface; within interface the data is stored on temporary purpose. Let us say I am transferring data from one application to another by using interface, when transferring data, I keep the data in Interface table for some time and then import the same into application. That is how we can store the data in interface for temp purpose. When we talk about the Base Table, every application has a Base Table which stores the data permanently. When you create Invoice, supplier, Bank etc system stores the data in Base Tables only. When transferring the data, it travels through Interface Tables
Base Tables in AP
· AP_INVOICES_ALL
· AP_INVOICE_LINES_ALL
· AP_INVOICE_DISTRIBUTION_ALL
· AP_INVOICE_PAYMENTS_ALL
· AP_PAYMENT_SCHEDULES_ALL
· AP_CHECKS_ALL
· AP_SUPPLIERS
· AP_SUPPLIER_SITES_ALL
Note: If you notice almost all tables end with ALL, except Suppliers. Wherever you see table ending with ALL, which means the records stored are specific to operating unit. As you know AP is a subledger which is maintained at OU level. AP_SUPPLERS- this is global because supplier header is not OU specific but can be shared across any OU. Sites are specific to OU.
How to get table from front end?
Go to Invoice Workbench- query any invoice
Place the cursor at Invoice à Help à Record History à here you can see the Table name as AP_INVOICE_V
Note: here we see V as suffix instead of All. The reason being V stands for View, Oracle has made some required fields as viewable which ends with V. so AP_INVOICES_ALL contains all the columns in the table (for ex: 100 columns) where as AP_INVOICE_V contains only few which are required (may be 30)
Interfaces and Interface Tables by Application
Oracle General Ledger Interfaces
Interface Name | Interface Tables | Usage |
Journals Interface | GL_INTERFACE | To import journals info into GL |
Daily rate interface | GL_DAILY_RATES_INTERFACE | To import daily rates info into GL |
Budget Interface | GL_BUDGET_INTERFACE | To import budget journal info into GL |
Oracle Accounts Payables Interfaces
Interface Name | Interface Tables |
Payables Open Invoice Interface | AP_INVOICE_INTERFACE |
AP_INVOICE_LINES_INTERFACE | |
Supplier interface | AP_SUPPLIERS_INT |
AP_SUPPLIER_SITES_INT | |
AP_SUP_SITE_CONTACTS_INT |
Oracle Accounts Receivables Interfaces
Interface Name | Interface Tables |
Auto Invoice Interface | RA_INTERFACE_LINES_ALL |
RA_INTERFACE_DISTRIBUTIONS | |
RA_INTERFACE_SALESCREDITS | |
RA_INTERFACE_ERRORS | |
Customers Interface | RA_CUSTOMERS_INTERFACE |
RA_CUSTOMER_PROFILES_INTERFACE | |
RA_CUST_PAY_METHOD_INTERFACE | |
RA_CUSTOMER_BANKS_INT_ALL | |
RA_CONTACT_PHONES_INT_ALL | |
Auto Lockbox Interface | AR_PAYMENTS_INTERFACE_ALL |
Oracle Cash Management Interfaces
Interface Name | Interface Tables |
Bank Statement Interface | CE_STATEMENT_HEADERS_INT_ALL |
CE_STATEMENT_LINES_INTERFACE |
Oracle Fixed Assets Interfaces
Interface Name | Interface Tables |
Mass Additions Interface | FA_MASS_ADDITIONS |
Important FND tables in oracle apps r12
FND stands for foundation tables which is combination of AOL, SYSTEM, ADMINISTRATOR, MODULES tables and is placed under FND_TOP. Below i have listed few key FND tables that along with the little description of what is it used for :
FND_APP_SERVERS : This table will track the servers used by the E-Business Suite system.
FND_DATABASES : It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.
FND_DATABASE_INSTANCES : Stores instance specific information. Every database has one or more instance.
FND_ATTACHED_DOCUMENTS : Stores information relating a document to an application entity.
FND_DOCUMENTS : Stores language-independent information about a document.
FND_CURRENCIES : Stores information about the currencies.
FND_LANGUAGES : Stores information regarding languages and dialects.
FND_TERRITORIES : Stores information for countries, alternatively known as territories.
FND_CONCURRENT_PROCESSES : Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS : Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS(_TL) : Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_QUEUES : Stores information about concurrent managers.
FND_CONCURRENT_QUEUE_SIZE : Stores information about the number of requests a concurrent manager can process at once, according to its work shift.
FND_CONCURRENT_REQUESTS : Stores information about individual concurrent requests.
FND_CONCURRENT_REQUEST_CLASS : Stores information about concurrent request types.
FND_CONC_REQ_OUTPUTS : This table stores output files created by Concurrent Request.
FND_EXECUTABLES : Stores information about concurrent program executables.
FND_DESCRIPTIVE_FLEXS(_TL) : Stores setup information about descriptive flexfields.
FND_FLEX_VALUES : Stores valid values for key and descriptive flexfield segments.
FND_FLEX_VALUE_SETS : Stores information about the value sets used by both key and descriptive flexfields.
FND_LOOKUPS : Stores information about lookup types.
FND_LOOKUP_VALUES : Stores meaning values and codes for lookup types.
FND_MENUS(_TL) : It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENU_ENTRIES : Stores information about individual entries in the menus in FND_MENUS.
FND_REQUEST_GROUPS : Stores information about report security groups.
FND_REQUEST_SETS : Stores information about report sets.
FND_RESPONSIBILITY(_TL) : Stores information about responsibilities.
FND_RESP_FUNCTIONS : Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.
FND_PROFILE_OPTIONS : Stores information about profile options.
FND_SECURITY_GROUPS : Stores information about security groups used to partition data in a Service Bureau architecture.
FND_USER : Stores information about application users.
FND_APPLICATION(_TL) : Stores applications registered with Oracle Application Object Library.
FND_SEQUENCES : Stores information about the registered sequences in your applications.
FND_VIEWS : Stores information about the registered views in your applications.
FND_TABLES : Stores information about the registered tables in your applications.
How to query tables from Database(in Vision instance)?
We can use TOAD or SQL * PLUS to query. TOAD is mostly used one
Open the TOAD application. This will ask SCHEMA, Username and password.
For entire Oracle Database the schema name is APPS, for each application we have a separate schema. For AP the schema name is AP, for AR the schema name is AR. In the same way if you want to connect to entire database we have to use the schema name as APPS.
Database name should be VIS
When you are working in real time you can get these credentials for client from DBAs.
Click Connect
Let us query the Invoice header information by using table “AP_INVOICES_ALL”
Query to give
SELECT * FROM AP_INVOIES_ALL;
Press Control + Enter which will execute the query and retrieve all the records (all columns) pertain to this table
If you want to query only INR currency invoices, then you need to write if condition
General Ledger (GL) KEY Tables with explanation
GL_SETS_OF_BOOKS
GL_SETS_OF_BOOKS stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.
GL_IMPORT_REFERENCES
GL_IMPORT_REFERENCES stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.
GL_DAILY_RATES
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. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table
GL_PERIODS
GL_PERIODS stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows in this table.
GL_JE_HEADERS
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. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted. Other statuses indicate that an error condition was found. CONVERSION_FLAG equal to ’N’ indicates that you manually changed a converted amount in the Journal Entry Lines zone of a foreign currency journal entry. In this case, the posting program does not re–convert your foreign amounts. This can happen only if your user profile option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there is only one balancing segment value in your journal entry. If there is more than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in your journal entry.
GL_JE_LINES
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. STATUS is ’U’ for unposted or ’P’ for posted.
GL_JE_BATCHES
GL_JE_BATCHES stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.
GL_BALANCES
GL_BALANCES 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.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.
GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.
An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.
For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance.Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.
Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is not
used.
GL_CODE_COMBINATIONS
GL_CODE_COMBINATIONS stores valid account combinations for each Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting or detail budgeting is allowed, and others.
Segment values are stored in the SEGMENT columns. Note that each Accounting Flexfield structure may use different SEGMENT columns within the table to store the flexfield value combination. Moreover, the SEGMENT columns that are used are not guaranteed to be in any order.
The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment of each Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID NULL.
Key Tables in Oracle Fixed Assets
FA_DEPRN_PERIODS
FA_DEPRN_SUMMARY
FA_ADDITIONS_B
FA_BOOKS
FA_CATEGORIES_B
FA_DEPRN_DETAIL
FA_DEPRN_PERIODS contains information about your depreciation periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book. PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when you opened and closed each book’s depreciation period. Each time you run the depreciation program, it closes the current period by setting PERIOD_CLOSE_DATE to the system date. It also opens the next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row. CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in FA_CALENDAR_PERIODS.
FA_DEPRN_SUMMARY contains depreciation information for your assets. Each time you run the depreciation program, it inserts one row into this table for each asset. PERIOD_COUNTER is the period for which you ran the depreciation program. DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period. YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period. DEPRN_RESERVE is the total accumulated depreciation for this asset. DEPRN_SOURCE_CODE tells you what program created the row BOOKS Created by the Depreciation Books form, Quick Additions form, or the post mass additions program when you enter a new asset. DEPRN Created by the depreciation program when you run depreciation. ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset’s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.
For assets that use a diminishing value method, the ADJUSTED_COST is the beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset’s net book value at the time of the adjustment or revaluation. BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted rate to give you the flat rate for the fiscal year. The depreciation program uses this rate to calculate depreciation for an asset. This only applies to assets that use both a flat–rate depreciation method and bonus depreciation.
FA_ADDITIONS_B contains descriptive information to help you identify your assets. Oracle Assets does not use this table to calculate depreciation. When you add an asset, Oracle Assets inserts a row into this table and into FA_ASSET_HISTORY. When you change the asset information stored in this table, Oracle Assets updates it in this table. It also creates a new row in FA_ASSET_HISTORY. When you perform a unit retirement, Oracle Assets reduces the CURRENT_UNITS by the units retired. UNIT_ADJUSTMENT_FLAG is set to YES by the Additions form if you change the number of units for an asset. The Transfers form resets it to NO after you reassign the remaining units. FA_ADJUSTMENTS stores information that Oracle Assets needs to create journal entries for transactions. The posting program creates journal entries for regular depreciation expense from information in FA_DEPRN_DETAIL. Oracle Assets inserts a row in this table for the debit and credit sides of a financial transaction. All the rows for the same transaction have the same value in the TRANSACTION_HEADER_ID column. The SOURCE_TYPE_CODE column tells you which program created the adjustment:
– ADDITION Depreciation program
– ADJUSTMENT Expensed or Amortized Adjustment User Exit
– CIP ADDITION Depreciation program
– CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit
– CIP RETIREMENT Gain/loss program
– DEPRECIATION Depreciation program (Retroactive transactions and expensed depreciation adjustments)
– RETIREMENT Gain/loss program
– RECLASS Reclassification user exit
– TRANSFER Transfers form
– TAX Reserve Adjustments form
– REVALUATION Mass revaluation program
The ADJUSTMENT_TYPE column tells you which type of account Oracle Assets adjusts. DEBIT_CREDIT_FLAG is DR if the amount is a debit and CR if the amount is a credit. ADJUSTMENT_AMOUNT is the amount debited or credited to the account. ANNUALIZED_ADJUSTMENT is the adjustment amount for a period times the number of periods in a fiscal year. The depreciation program uses it to calculate the depreciation adjustment for an asset when you perform multiple retroactive transactions on the asset. Oracle Assets calculates ADJUSTMENT_PER_PERIOD by dividing the ADJUSTMENT_AMOUNT for a retroactive transaction by the number of periods between the period you entered the transaction and the period that it was effective. For current period transactions, this column is zero. PERIOD_COUNTER_CREATED IS the period that you entered the adjustment into Oracle Assets. PERIOD_COUNTER_ADJUSTED is the period to which the adjustment applies. It is the same as PERIOD_COUNTER_CREATED, unless you enter a reserve adjustment, in which case PERIOD_COUNTER_ADJUSTED is the last period of the fiscal year to which the adjustment applies. CODE_COMBINATION_ID indicates the Accounting Flex field combination Oracle Assets debits or credits for all transactions except reclassifications and intercompany transfers. This CODE_COMBINATION_ID is generated using the Account Generator, and the posting program does not perform any further processing.
FA_BOOKS contains the information that Oracle Assets needs to calculate depreciation. When you initially add an asset, Oracle Assets inserts one row into the table. This becomes the ”active” row for the asset. Whenever you use the Depreciation Books form to change the asset’s depreciation information, or if you retire or reinstate it, Oracle Assets inserts another row into the table, which then becomes the new ”active” row, and marks the previous row as obsolete.
At any point in time, there is only one ”active” row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a report for a prior accounting period, Oracle Assets selects the row that was active during that period. You can identify the active row for an asset in a book because it is the only one whose DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_OUT are NULL. When Oracle Assets terminates a row, the DATE_INEFFECTIVE and TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and TRANSACTION_HEADER_IN of the new row, respectively. This means that you can easily identify rows affected by the same transaction because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs. When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the DATE_EFFECTIVE is the system date. When you retire an asset, Oracle Assets inserts a new row to reduce the COST by the amount retired. When you reinstate an asset, Oracle Assets inserts a new row to increase the COST by the COST_RETIRED in the corresponding row in FA_RETIREMENTS.RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread depreciation over the remaining life of an asset after an amortization or revaluation. If you perform a revaluation or an amortized adjustment, Oracle Assets resets the Rate Adjustment Factor to prorate the remaining recoverable net book value over the remaining life. This fraction is calculated as [Recoverable Cost – what Depreciation Reserve would be]/Recoverable Cost. The depreciation program uses this value to adjust the depreciation rate for an asset.
FA_CATEGORIES_B stores information about your asset categories. This table provides default information when you add an asset. The depreciation program does not use this information to calculate depreciation. The Asset Categories form inserts one row in this table for each asset category you define. The Application Object Library table FND_ID_FLEX_SEGMENTS stores information about which column in this table is used for each segment.
FA_DEPRN_DETAIL contains the depreciation amounts that the depreciation program charges to the depreciation expense account in each distribution line.
Oracle Assets uses this information to create depreciation expense journal entries for your general ledger. The depreciation program inserts one row per distribution line for an asset each time you run depreciation.
No comments:
Post a Comment