How to Use Flexfields in Custom forms

There are several complicated things you may need to do with flexfields in your forms but one of them is simple and very common: create a foreign key on the accounting flexfield. It is easy to get lost in the Oracle Application Developer's Guide because it tries to cover comprehensively what can be done with flexfields. But there is nothing to show you the basics step-by-step, which is what we will attempt in this article.

The  end result of what we show is illustrated in Figure 1: one field that brings up the standard flexfield popup when the user asks for a list of values.

A basic conceptual understanding of what flexfields are is necessary in order to understand what follows. There is a very good 2-3 page explanation in the chapter "Flexfields" of the oracle application Developer's Guide.

Step 1: Create a FK Column

First you need to define a column in the base table maintained by your form. This column will be the foreign key on the unique ID of the combination table, in our case: GL_CODE_COMBINATIONS. So you need a number(38) mandatory column in your table.

Step 2: Create a Flexfield hidden item

In your form you need to create a hidden item for the FK column. This field needs to be hidden, that is set to canvas null. It should use the TEXT_ITEM property class which comes from the Oracle Application template. Set the query length to 2000 just to be on the safe side. Figure 2 shows an example of such hidden ID.

 

Figure 2: Hidden field to hold the Flexfield reference.

Step 3: Create a Flexfield display item

Now we want to create a non-database text_item that will display the concatenated values of the segments of the accounting field. This item should use the TEXT_ITEM property class and be assigned to the appropriate canvas where you want users to see it. You then need to assign to it the dummy LOV 'ENABLE_LIST_LAMP' which comes from the Oracle Application template. Make sure that property 'Validate from list' is set to No. This ensures that the List lamp works properly for your flexfield. Figure 3 shows an example of such displayed field. We only show the most relevant item properties.

 

Figure 3: Displayed field to show the concatenated values of the Flexfield.

Step 4: Create the Flexfield definition

Then there is a little bit of PL/SQL to do in order to define your flexfield. This is done with the WHEN-NEW-FORM-INSTANCE trigger. It is always a good practice to do the actual work in a procedure or package as is illustrated in listing A. There are several reasons for this but this is out of the scope of this article. Note: the code in listing A is an abbreviated version of the real code: we have left out the comments and the error handling in order to keep this article as short as possible.

Listing A: Dynamic Flexfield definition

procedure initialize is     cursor get_cao is        select  to_char(id_flex_num)        from    fnd_id_flex_structures        where   id_flex_structure_code = 'ACCOUNTING_FLEXFIELD'                and id_flex_code = 'GL#';     v_cao    varchar2(1000);            begin    open get_cao;    fetch get_cao into v_cao;    if get_cao%notfound then        v_cao := '101';    end if;    close get_cao;       app_standard.event('WHEN-NEW-FORM-INSTANCE');    fnd_key_flex.define(      BLOCK=>'COMBO',      FIELD=>'ACCOUNT_FLEXFIELD',      APPL_SHORT_NAME=>'SQLGL',      CODE=>'GL#',      NUM=>v_cao,      ID=>'CODE_COMBINATION_ID',      DESCRIPTION=>'',      TITLE=>'____ Your nice user friendly title here _____',      VALIDATE=>'FULL',      QBE_IN=>'Y',      DERIVE_ALWAYS=>'Y',      updateable => '',      VRULE=>'\\nSUMMARY_FLAG\\nI\\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\\nN',      where_clause => 'summary_flag != ''Y''',      QUERY_SECURITY =>'Y');  end initialize;

Step 5: Code several event triggers

Then, all that is left to do is to program a few events:

• WHEN-VALIDATE-ITEM
• WHEN-NEW-ITEM-INSTANCE
• POST-QUERY
• PRE-QUERY
• KEY-LISTVAL

The code is shown in Listing B.

We always try to put this code in the form level triggers as it is more convenient and consistent than doing it at block or item level, especially when you have more than one foreign key flexfield in the form. Only in the case of very large and complicated forms would we do otherwise.

If you are updating someone else's form, you may need to check that no block or item triggers are overriding your form level triggers. Check also the execution style of your form level triggers. Whether your trigger should fire in mode Before, After or Override will depend on your context.
Listing B: Event programming for the Flexfields.

WHEN-VALIDATE-ITEM    if ( :system.mode = 'NORMAL' ) then       fnd_flex.event( 'WHEN-VALIDATE-ITEM' );    end if;WHEN-NEW-ITEM-INSTANCE    app_standard.event('WHEN-NEW-ITEM-INSTANCE');    fnd_flex.event('WHEN-NEW-ITEM-INSTANCE' );POST-QUERY--Loads the flexfields (in our case, it populates--the concatenated field on execute query).    FND_FLEX.EVENT('POST-QUERY');PRE-QUERY--If you don't do this, whatever query criteria you may enter in-- the concatenated flex field, it is not taken into account.    FND_FLEX.EVENT('PRE-QUERY' );KEY-LISTVAL    APP_STANDARD.EVENT('KEY-LISTVAL');    FND_FLEX.EVENT('KEY-LISTVAL' );

AP invoice interface

http://oracleoverview.com/ap-invoice-interface-ap_invoices_interface-tables-38

AP invoice interface is used  to import vendor invoices into Oracle applications from legacy systems.

AP invoice interface tables:

This is the open interface table for importing AP Invoices from external sources (Flat Files ) and stores invoices header information .  There is one row for each invoice you import. Oracle Payables application uses this information to create invoice header information when Payables Open Interface program is submitted. Data in the AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE table to create Payables Invoice, Invoice lines, Distributions and Schedule payments. Data in this table can be viewed and edited using ‘Open Interface Invoices’ window. The Payables Open Interface program validates each record in this interface table selected for import, and if the record contains valid data then the program creates a Payables Invoice.

AP_INVOICES_INTERFACE Important columns : 
INVOICE_ID (Required) : Unique identifier for this invoice within this batch. Same value should be populated in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data as belonging to the same invoice.
INVOICE_NUM (Required) :  Enter the invoice number that needs to be assigned to the invoice created in Payables from this record.
INVOICE_TYPE_LOOKUP_CODE (Optional) : Type of invoice: Credit or Standard.
INVOICE DATE (Optional) : Date of the invoice. If you do not enter a value, the system uses the date you submit Payables Open Interface Import as the invoice date.
PO_NUMBER (Optional)  : Purchase order number from PO_HEADERS.SEGMENT1. This column needs to be populated if invoice to be matched with an purchase order.
VENDOR_ID & VENDOR_SITE_ID (Required) : VENDOR_ID is unique identifier for a supplier and VENDOR_SITE_ID is Internal supplier site identifier. Supplier of the invoice to be derived by value in one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME, VENDOR_SITE_ID or PO_NUMBER.
VENDOR_NUM & VENDOR_NAME (Optional) : Supplier number and name. You must identify the supplier by entering a value for one of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER.
INVOICE_AMOUNT (Required) : Amount of the invoice.
INVOICE_CURRENCY_CODE (Optional)  : Currency code for the invoice. If you want to create foreign currency invoices, enter a currency code that is different from your functional currency.
EXCHANGE_RATE (Optional) :  This column is required if you enter a foreign currency code in the INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE.
TERMS_ID (Optional) : Internal identifier for the payment terms.
DESCRIPTION (Optional) : Enter the description that you want to assign to the invoice created from this record.
SOURCE (Required) : Source of the invoice data. If you import EDI invoices from the Oracle EDI Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader, use a QuickCode with the type Source that you have defined in the QuickCodes window in Payables.
2] AP_INVOICE_LINES_INTERFACE
This is the lines interface table for the AP Invoice Open Interface and it is used in conjunction with AP_INVOICE_INTERFACE table. AP_INVOICE_LINES_INTERFACE stores information used to create one or more invoice distributions. Note that one row in this table may create, during the import process, more than one invoice distribution.
Important columns: 
INVOICE_ID (Required) :Enter the INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table. 
INVOICE_LINE_ID : This value is not required. You can enter a unique number for each invoice line of an invoice.
LINE_NUMBER (Optional) : You can enter a unique number to identify the line.
LINE_TYPE_LOOKUP_CODE (Required) : Enter the lookup code for the type of invoice distribution that you want Payables Open Interface Import to create from this record. The code you enter must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in the AP_LOOKUP_CODES table.
AMOUNT (Required) : The invoice distribution amount. If you are matching to a purchase order, the AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice distributions does not equal the amount of the invoice that has the same INVOICE_ID, then Payables Open Interface Import will reject the invoice.

Concurrent program:   Payables Open Interface Import

Parameters:
Source: Choose the source of the invoices from the list of values. Use EDI Gateway, Credit Card, or a Source type QuickCode you defined in the Payables QuickCodes window.
Group: To limit the import to invoices with a particular Group ID, enter the Group ID. The Group must exactly match the GROUP_ID in the Payables Open Interface tables.
Batch Name: Payables groups the invoices created from the invoices you import and creates an invoice batch with the batch name you enter. You can enter a batch name only if you have enabled the Use Batch Control Payables option, and if you have enabled the Use Batch Control Payables option, you must enter a batch name. If you use a batch name and some invoices are rejected during the import process, you can import the corrected invoices into the same batch if you enter the exact batch name during the subsequent import.
Hold Name: If you want to place all invoices on hold at the time of import, enter an Invoice Hold Reason. You can define your own hold reasons in the Invoice Approvals window.
Hold Reason: Payables displays the Invoice Hold Reason Description.
GL Date: If you want to assign a specific GL Date to all invoices, enter a GL Date. If you do not enter a value here, the system will assign a GL Date based on the GL Date Payables option.
Purge: Enter Yes if you want Payables to delete all successfully imported invoice records that match the Source and Group ID of this import. Payables does not delete any invoice data for which it has not yet created invoices. If you want to purge later, you can use the Payables Open Interface Purge Program.

You can go to Payables > Invoices > Entry > Open Interface Invoices to check the details of Invoice and Invoice Lines from the front end.

OBIEE 11.1.1.6.0 RCU Installation

Database Installation and Configuration:

If you installed database already on your machine, you can skip to section.

Before we starting the installation of RCU, we need to choose that which database we are going to use in our application. Here I am giving how to run RCU in Oracle database on our local machine. It is good to install Oracle Database enterprise or standard edition to take full advantage of database for datatypes like spatial data or features like OLAP.

Prerequisites:
Here I am giving the installation steps that how I have installed RCU in my windows 32bit Operating system. If you have 64 bit OS, no worry the steps are same.
• Minimum of 4GB RAM required, 3 GB RAM would work but, system would be
slow.
• Minimum of 10GB hard disk memory required.
• not use any directory name with space while installing/working with Oracle
DB and BI11g.


Installing RCU:
BI 11g is now standardized with Oracle fusion middleware and hence leverage database for metadata storage just like any other middleware product. So here we are going to install BI11g metadata schema on oracle database you just installed and configured. Same process can be leveraged to install metadata schema on other supported databases as well.
If you have not done already, please download BI11g installation files and RCU installation files. Please make sure all the zipped files have downloaded correctly and unzipped into a single folder.
We can download the software from below Download page:
http://www.oracle.com/technetwork/middleware/bi-enterprise-edition/downloads/bus-intelligence-11g-165436.html


Now we will start the installation of metadata schema using RCU.

From unzipped folder for RCU, run rcu.bat file to start the utility: rcu.bat file is located under rcuHome\BIN directory.

RCU utility will launch the GUI based wizard to guide you through the steps. Click Next to continue.

Select create from the below screen and click Next

In the below step Make sure database type is Oracle Database and enter following information to continue.
For e.g:
Host Name: localhost
Port: 1521
Service Name: ORCL
Username: sys
Role: sysdba
Password:  MyPassword123

since we are using oracle database, RCU will show a warning message that we can just ignore and can continue the installation.

Click OK to continue once RCU validates and initializes database configuration.

In the next step it will ask for the prefix to create the metadata schemas. By default it is showing 'DEV'. If we want to change the prefix we can  change it.
From the below window select appropriate schemas which we need to install.
I have selected only two which is showing in the screenshot

Click 'Next' from the above screen to create schema and the click 'OK' once RCU shows schema creation prerequisites check completed.

In the next step Enter password for both the schema as you like with the password conditions (such as alphabet, numeric and with special character) and click 'Next' to continue.

Click 'Next' with the above step to have RCU create the selected schemas

Click 'OK' to continue creation of tablespace.

Once tablespace created, click 'OK' to continue.

Now RCU will create required schema objects within the tablespace just created. So Click 'Create' with next step and continue

Click 'close' to finish RCU wizard once schemas are created successfully.

with the above step the installation of RCU is completed
Note:

Remember the password which we have given for all the  users/schemas so far and we will continue to use the same password. We will need this password for BI installation as well to let BI installer knows where the metadata schemas located and what is the password to connect to the schema. Now we can login to the oracle database and can whether that two schems (DEV_BIPLATFORM, DEV_MDS) is available.

OBIEE 11.1.1.7.0 Installation Steps

This post will guide us through the  step by step installation of OBIEE 11.1.1.7.0. Here I don't find any difference in RCU installation between in previous version with this version. Still if you want to know the RCU installation steps click here

Step1:

Download the OBIEE 11g version from here or from oracle e-delivery site and extract all the four disk into a single folder and click setup.exe from the Disk1

Universal Installer window will start and will check for the initial set up and system requirement.

After the successful precheck the first step of our OBIEE 11.1.1.7.0 starts with the below window. Just Click 'Next'

Step2: 
If you have oracle Support username and password then update then use that in this step or just select first option 'Skip Software updates' and click 'Next'

Step3:
Select the appropriate installation step here. I am going to install all the component of BI so selected here 'Enterprise Install' and then click 'Next'

Step4:
This step will just check operating system certification and physical memory. Just click 'Next' after the check passed through

Step5:
As It is a new installation, select the option 'Create New BI System' and type password for weblogic User.
Note: Save or remember the password because this is the password going to be used to access BI system, EM, Console and answers later on..
and then click 'Next'

Step6:
In this step we have to select the actual folder where we want to install BI 11g. So here I have created an folder in C:\ driver and selected here by browsing the home location in this window. Remaining boxes will be automatically filled once we selected the home location then click 'Next'

With the above window we will get warning like below screen. Just Click 'Yes and click 'Next' with the above screen

Step7:
In this step we would know what are the components going to be installed. If you are really going to user 'RTD' and Essbase Suite the leave it selected or Just uncheck with the below screen and click 'Next'

Step8:
In this step we have to select the database where we have installed our RCU. I have done in oracle database so selected the oracle database in database type

Connection String: dbservername:portnumber:servicename
BIPLATFORM Name: DEV_BIPLATFORM
BIPLATFORM Pwd: Admin123

Note: This username and password would be same which we have given when we installed RCU

Step9:
Same steps like the previous step but here with DEV_MDS username and password. After entering all the details just click 'Next'

Step10:
Just Select 'Auto Port Configuration' if you want to install with the default port numbers that oracle do the installation and then click 'Next'

Step11:
If we have oracle support email id we could mention with this step or just deselect the option click 'Yes' with the warning screen and click 'Next' with the below step

Step12:
With this step just click 'Install' so that the actual installation will be started with the below screen:

Step13:
Just watch the installation progress with this step

It take a while even the progress bar shows 100%. Post installation script might be take some time so be patient the steps will pass through.

Step14:
After successful installation the configuration steps will start. It takes a while to finish all the configuration so wait to finish and then just click 'Next' when the configuration shows 100%

Step15:
So actual installation and configuration are over with the above step. If you want to save the path details for middleware home, domain home and others click 'Save' with the below screen and save it in a file for reference. and the just click 'Finish' to complete the installation.

Now browse through the answers, EM and console and Play with BI.

Oracle Form Compilation in R12

Form Compilation

Corresponding value in the array. The key can be integer or a string.

Follow the instructions below to compile your forms in Release 12
1:- Log into the forms tier.
2:- Set the applications environment
3:- Ensure that the $FORMS_PATH includes $AU_TOP/resource and $AU_TOP/resource/stub, for example


echo $FORMS_PATH

/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/resource:
/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/resource/stub

 Compile the form
 If you are using forms customization (CUSTOM.pll) then run the command below to compile the form.


frmcmp_batch.sh module=<path to fmb file> userid=APPS/APPS output_file=<full path to fmx output file> module_type=form compile_all=special

For eg:-

frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXX.fmb userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXX.fmx module_type=form compile_all=special

If you are NOT using forms customizations, then run the command below to compile the form.

frmcmp_batch.sh module=<path to fmb file> userid=APPS/APPS output_file=<full path to fmx output file> module_type=form

For example.

frmcmp_batch.sh module=/u01/oracle/TEST/apps/apps_st/appl/au/12.0.0/forms/US/XXX.fmb userid=APPS/APPS output_file=/u01/oracle/TEST/apps/apps_st/appl/inv/12.0.0/forms/US/XXX.fmx module_type=form

Important Tables in Oracle APPS

ONT- Order Management
Table Name Description 
OE_ORDER_HEADERS_ALL OE_ORDER_HEADERS_ALL stores header information for orders in Order Management.
OE_ORDER_LINES_ALL OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management. 
OE_ORDER_SOURCES Feeder System Names that create orders in Order Management tables. 
OE_ORDER_HOLDS_ALL This table stores information of all the orders and lines that are on hold and the link to hold sources and hold releases. 
OE_SALES_CREDITS This table stores information about sales credits. 
OE_TRANSACTION_TYPES_ALL This table stores information about the order and line transaction types 
WSH_DELIVERY_ASSIGNMENTS Delivery Assignments 
WSH_DELIVERY_DETAILS Delivery Details 
WSH_NEW_DELIVERIES Deliveries 
WSH_TRIPS Trips 
WSH_TRIP_STOPS Trip Stops 

PO - Purchasing
Table Name Description 
PO_ACTION_HISTORY Document approval and control action history table 
PO_AGENTS Buyers table 
PO_DISTRIBUTIONS_ALL Purchase order distributions 
PO_HEADERS_ALL Document headers (for purchase orders, purchase agreements, quotations, RFQs) 
PO_LINES_ALL Purchase document lines (for purchase orders, purchase agreements, quotations, RFQs) 
PO_LINE_LOCATIONS_ALL Document shipment schedules (for purchase orders, purchase agreements, quotations, RFQs) 
PO_RELEASES_ALL Purchase order releases 
PO_LINES_ARCHIVE_ALL Archived purchase order lines 
PO_LINE_LOCATIONS_ARCHIVE_ALL Archived purchase order shipments 
PO_HEADERS_ARCHIVE_ALL Archived purchase orders 
PO_LINE_TYPES_B Line types 
PO_RELEASES_ARCHIVE_ALL Archived releases 
PO_REQUISITION_HEADERS_ALL Requisition headers 
PO_REQUISITION_LINES_ALL Requisition lines 
PO_REQ_DISTRIBUTIONS_ALL Requisition distributions 
RCV_TRANSACTIONS Receiving transactions 
RCV_SHIPMENT_HEADERS Shipment and receipt header information 
RCV_SHIPMENT_LINES Receiving shipment line information 

INV – Inventory
Table Name Description 
MTL_CATEGORIES_B Code combinations table for Item Category 
MTL_CATEGORY_SETS_B Category Sets 
MTL_CUSTOMER_ITEMS Customer item Information 
MTL_CUSTOMER_ITEM_XREFS Relationships between customer items and inventory items 
MTL_DEMAND Sales order demand and reservations 
MTL_DEMAND_HISTORIES Sales order demand and reservations 
MTL_ITEM_LOCATIONS Definitions for stock locators 
MTL_ITEM_REVISIONS_B Item revisions 
MTL_ITEM_TEMPLATES_B Item template definitions 
MTL_ITEM_TEMPL_ATTRIBUTES Item attributes and attribute values for a template 
MTL_LOT_NUMBERS Lot number definitions 
MTL_MATERIAL_TRANSACTIONS Material transaction table 
MTL_MATERIAL_TRANSACTIONS_TEMP Temporary table for processing material transactions 
MTL_ONHAND_QUANTITIES_DETAIL FIFO quantities by control level and receipt 
MTL_PARAMETERS Inventory control options and defaults 
MTL_RESERVATIONS Reservations 
MTL_SECONDARY_INVENTORIES Subinventory definitions 
MTL_SECONDARY_LOCATORS Item-subinventory-locator assignments 
MTL_SERIAL_NUMBERS Serial number definitions 
MTL_SYSTEM_ITEMS_B Inventory item definitions 
MTL_TRANSACTION_ACCOUNTS Material transaction distributions 
MTL_TRANSACTION_TYPES Inventory Transaction Types Table 
MTL_TXN_REQUEST_HEADERS Move Order headers table 
MTL_TXN_REQUEST_LINES Move order lines table 
MTL_UNIT_TRANSACTIONS Serial number transactions 


GL- General Ledger
Table Name Description 
GL_CODE_COMBINATIONS Stores valid account combinations 
GL_SETS_OF_BOOKS Stores information about the sets of books 
GL_IMPORT_REFERENCES Stores individual transactions from subledgers 
GL_DAILY_RATES Stores the daily conversion rates for foreign currency 

Transactions 
GL_PERIODS Stores information about the accounting periods 
GL_JE_HEADERS Stores journal entries 
GL_JE_LINES Stores the journal entry lines that you enter in the Enter Journals form 
GL_JE_BATCHES Stores journal entry batches 
GL_BALANCES Stores actual, budget, and encumbrance balances for detail and summary accounts 
GL_BUDGETS Stores Budget definitions 
GL_INTERFACE Import journal entry batches 
GL_BUDGET_INTERFACE Upload budget data from external sources 
GL_DAILY_RATES_INTERFACE Import daily conversion rates 

AR- Accounts Receivables
Table Name Description 
RA_CUST_TRX_TYPES_ALL Transaction type for invoices, commitments and credit memos 
RA_CUSTOMER_TRX_ALL Header-level information about invoices, debit memos, chargebacks, commitments and credit memos 
RA_CUSTOMER_TRX_LINES_ALL Invoice, debit memo, chargeback, credit memo and commitment lines 
RA_CUST_TRX_LINE_GL_DIST_ALL Accounting records for revenue, unearned revenue and unbilled receivables 
RA_CUST_TRX_LINE_SALESREPS_ALL Sales credit assignments for transactions 
AR_ADJUSTMENTS_ALL Pending and approved invoice adjustments 
RA_BATCHES_ALL 
AR_CASH_RECEIPTS_ALL Detailed receipt information 
AR_CASH_RECEIPT_HISTORY_ALL History of actions and status changes in the life cycle of a receipt 
AR_PAYMENT_SCHEDULES_ALL All transactions except adjustments and miscellaneous cash receipts 
AR_RECEIVABLE_APPLICATIONS_ALL Accounting information for cash and credit memo applications 
AR_TRANSACTION_HISTORY_ALL Life cycle of a transaction 
HZ_CUST_ACCOUNTS Stores information about customer accounts. 
HZ_CUSTOMER_PROFILES Credit information for customer accounts and customer account sites 
HZ_CUST_ACCT_SITES_ALL Stores all customer account sites across all operating units 
HZ_CUST_ACCT_RELATE_ALL Relationships between customer accounts 
HZ_CUST_CONTACT_POINTS This table is no longer used 
HZ_CUST_PROF_CLASS_AMTS Customer profile class amount limits for each currency 
HZ_CUST_SITE_USES_ALL Stores business purposes assigned to customer account sites. 
HZ_LOCATIONS Physical addresses 
HZ_ORG_CONTACTS People as contacts for parties 
HZ_ORG_CONTACT_ROLES Roles played by organization contacts 
HZ_PARTIES Information about parties such as organizations, people, and groups 
HZ_PARTY_SITES Links party to physical locations 
HZ_PARTY_SITE_USES The way that a party uses a particular site or address 
HZ_RELATIONSHIPS Relationships between entities 
HZ_RELATIONSHIP_TYPES Relationship types 

CE- Cash Management
Table Name Description 
CE_BANK_ACCOUNTS This table contains bank account information. Each bank account must be affiliated with one bank branch. 
CE_BANK_ACCT_BALANCES This table stores the internal bank account balances 
CE_BANK_ACCT_USES_ALL This table stores information about your bank account uses. 
CE_STATEMENT_HEADERS Bank statements 
CE_STATEMENT_LINES Bank statement lines 
CE_STATEMENT_HEADERS_INT Open interface for bank statements 
CE_STATEMENT_LINES_INTERFACE Open interface for bank statement lines 
CE_TRANSACTION_CODES Bank transaction codes

AP- Accounts Payables
Table Name Description 
AP_ACCOUNTING_EVENTS_ALL Accounting events table 
AP_AE_HEADERS_ALL Accounting entry headers table 
AP_AE_LINES_ALL Accounting entry lines table 
AP_BANK_ACCOUNTS_ALL Bank Account Details 
AP_BANK_ACCOUNT_USES_ALL Bank Account Uses Information 
AP_BANK_BRANCHES Bank Branches 
AP_BATCHES_ALL Summary invoice batch information 
AP_CHECKS_ALL Supplier payment data 
AP_HOLDS_ALL Invoice hold information 
AP_INVOICES_ALL Detailed invoice records 
AP_INVOICE_LINES_ALL AP_INVOICE_LINES_ALL contains records for invoice lines entered manually, generated automatically or imported from the Open Interface. 
AP_INVOICE_DISTRIBUTIONS_ALL Invoice distribution line information 
AP.AP_INVOICE_PAYMENTS_ALL Invoice payment records 
AP_PAYMENT_DISTRIBUTIONS_ALL Payment distribution information 
AP_PAYMENT_HISTORY_ALL Maturity and reconciliation history for

Payments 
AP_PAYMENT_SCHEDULES_ALL Scheduled payment information on invoices 
AP_INTERFACE_REJECTIONS Information about data that could not be loaded by Payables Open Interface Import 
AP_INVOICES_INTERFACE Information used to create an invoice using Payables Open Interface Import 
AP_INVOICE_LINES_INTERFACE Information used to create one or more invoice distributions 
AP_SUPPLIERS AP_SUPPLIERS stores information about your supplier level attributes. 
AP_SUPPLIER_SITES_ALL AP_SUPPLIER_SITES_ALL stores information about your supplier site level attributes. 
AP_SUPPLIER_CONTACTS Stores Supplier Contacts 

FA – Fixed Assets
Table Name Description 
FA_ADDITIONS_B Descriptive information about assets 
FA_ADJUSTMENTS Information used by the posting program to generate journal entry lines in the general ledger 
FA_ASSET_HISTORY Historical information about asset reclassifications and unit adjustments 
FA_ASSET_INVOICES Accounts payable and purchasing information for each asset 
FA_BOOKS Financial information of each asset 
FA_BOOK_CONTROLS Control information that affects all assets in a depreciation book 
FA_CALENDAR_PERIODS Detailed calendar information 
FA_CALENDAR_TYPES General calendar information 
FA_CATEGORIES_B Default financial information for asset categories 
FA_CATEGORY_BOOKS Default financial information for an asset category and depreciation book combination 
FA_DEPRN_DETAIL Depreciation amounts charged to the depreciation expense account in each distribution line 
FA_DEPRN_PERIODS Information about each depreciation period 
FA_DEPRN_EVENTS Information about depreciation accounting events. 
FA_DEPRN_SUMMARY Depreciation information at the asset level 
FA_DISTRIBUTION_ACCOUNTS Table to store account ccids for all distributions for a book 
FA_DISTRIBUTION_DEFAULTS Distribution set information 
FA_DISTRIBUTION_HISTORY Employee, location, and Accounting Flexfield values assigned to each asset 
FA_DISTRIBUTION_SETS Header information for distribution sets 
FA_FORMULAS Depreciation rates for formula-based methods 
FA_LOCATIONS Location flexfield segment value combinations 
FA_MASS_ADDITIONS Information about assets that you want to automatically add to Oracle Assets from another system 
FA_METHODS Depreciation method information 
FA_RETIREMENTS Information about asset retirements and reinstatements 

HRMS- Human Resource Management System
Table Name Description 
HR_ALL_ORGANIZATION_UNITS Organization unit definitions. 
HR_ALL_POSITIONS_F Position definition information. 
HR_LOCATIONS_ALL Work location definitions. 
PER_ADDRESSES Address information for people 
PER_ALL_PEOPLE_F DateTracked table holding personal information for employees, applicants and other people. 
PER_ALL_ASSIGNMENTS_F Allocated Tasks 
PER_ANALYSIS_CRITERIA Flexfield combination table for the personal analysis key flexfield. 
PER_ASSIGNMENT_EXTRA_INFO Extra information for an assignment. 
PER_ASSIGNMENT_STATUS_TYPES Predefined and user defined assignment status types. 
PER_CONTRACTS_F The details of a persons contract of employment 
PER_CONTACT_RELATIONSHIPS Contacts and relationship details for dependents, beneficiaries, emergency contacts, parents etc. 
PER_GRADES Grade definitions for a business group. 
PER_JOBS Jobs defined for a Business Group 
PER_PAY_BASES Definitions of specific salary bases 
PER_PAY_PROPOSALS Salary proposals and performance review information for employee assignments 
PER_PEOPLE_EXTRA_INFO Extra information for a person 
PER_PERIODS_OF_PLACEMENT Periods of placement details for a non-payrolled worker 
PER_PERIODS_OF_SERVICE Period of service details for an employee. 
PER_PERSON_ANALYSES Special information types for a person 
PER_PERSON_TYPES Person types visible to specific Business Groups. 
PER_PERSON_TYPE_USAGES_F Identifies the types a person may be. 
PER_PHONES PER_PHONES holds phone numbers for current and ex-employees, current and ex-applicants and employee contacts. 
PER_SECURITY_PROFILES Security profile definitions to restrict user access to specific HRMS records 

PAY- Payroll
Table Name Description 
PAY_ACTION_INFORMATION Archived data stored by legislation 
PAY_ALL_PAYROLLS_F Payroll group definitions. 
PAY_ASSIGNMENT_ACTIONS Action or process results, showing which assignments have been processed by a specific payroll action, or process. 
PAY_ELEMENT_CLASSIFICATIONS Element classifications for legislation and information needs. 
PAY_ELEMENT_ENTRIES_F Element entry list for each assignment. 
PAY_ELEMENT_ENTRY_VALUES_F Actual input values for specific element entries. 
PAY_ELEMENT_LINKS_F Eligibility rules for an element type. 
PAY_ELEMENT_TYPES_F Element definitions. 
PAY_ELEMENT_TYPE_USAGES_F Used to store elements included or excluded from a defined run type. 
PAY_ORG_PAYMENT_METHODS_F Payment methods used by a Business Group. 
PAY_PAYMENT_TYPES Types of payment that can be processed by the system. 
PAY_PAYROLL_ACTIONS Holds information about a payroll process. 
PAY_PEOPLE_GROUPS People group flexfield information. 
PAY_PERSONAL_PAYMENT_METHODS_F Personal payment method details for an employee. 
PAY_RUN_RESULTS Result of processing a single element entry. 
PAY_RUN_RESULT_VALUES Result values from processing a single element entry. 
PAY_SECURITY_PAYROLLS List of payrolls and security profile access rules. 
PAY_INPUT_VALUES_F Input value definitions for specific elements. 

BOM – Bills Of Material 
Table Name Description 
BOM_DEPARTMENTS Departments 
BOM_DEPARTMENT_CLASSES Department classes 
BOM_DEPARTMENT_RESOURCES Resources associated with departments 
BOM_OPERATIONAL_ROUTINGS Routings 
BOM_OPERATION_NETWORKS Routing operation networks 
BOM_OPERATION_RESOURCES Resources on operations 
BOM_OPERATION_SEQUENCES Routing operations 
BOM_OPERATION_SKILLS 
BOM_RESOURCES Resources, overheads, material cost codes, and material overheads 
BOM_STANDARD_OPERATIONS Standard operations 
BOM_ALTERNATE_DESIGNATORS Alternate designators 
BOM_COMPONENTS_B Bill of material components 
BOM_STRUCTURES_B Bills of material 
BOM_STRUCTURE_TYPES_B Structure Type master table 

WIP – Work in Process 
Table Name Description 
WIP_DISCRETE_JOBS Discrete jobs 
WIP_ENTITIES Information common to jobs and schedules 
WIP_LINES Production lines 
WIP_MOVE_TRANSACTIONS Shop floor move transactions 
WIP_MOVE_TXN_ALLOCATIONS Move transaction allocations for repetitive schedules 
WIP_OPERATIONS Operations necessary for jobs and schedules 
WIP_OPERATION_NETWORKS Operation dependency 
WIP_OPERATION_OVERHEADS Overheads for operations in an average costing organization 
WIP_OPERATION_RESOURCES Resources necessary for operations 
WIP_OPERATION_YIELDS This table keeps all costing information for operation yield costing. 
WIP_TRANSACTIONS WIP resource transactions 
WIP_TRANSACTION_ACCOUNTS Debits and credits due to resource transactions 

FND – Appication Object Library
Table Name Description 
FND_APPLICATION Applications registered with Oracle Application Object Library 
FND_CONCURRENT_PROGRAMS Concurrent programs 
FND_CONCURRENT_REQUESTS Concurrent requests information 
FND_CURRENCIES Currencies enabled for use at your site 
FND_DATA_GROUPS Data groups registered with Oracle Application Object Library 
FND_FLEX_VALUES Valid values for flexfield segments 
FND_FLEX_VALUE_HIERARCHIES Child value ranges for key flexfield segment values 
FND_FLEX_VALUE_SETS Value sets used by both key and descriptive flexfields 
FND_FORM Application forms registered with Oracle Application Object Library 
FND_FORM_FUNCTIONS Functionality groupings 
FND_ID_FLEXS Registration information about key flexfields 
FND_ID_FLEX_SEGMENTS Key flexfield segments setup information and correspondences between table columns and key flexfield segments 
FND_ID_FLEX_STRUCTURES Key flexfield structure information 
FND_LOOKUP_TYPES Oracle Application Object Library QuickCodes 
FND_LOOKUP_VALUES QuickCode values 
FND_MENUS New menu tabl for Release 10SC 
FND_PROFILE_OPTIONS User profile options 
FND_PROFILE_OPTION_VALUES Values of user profile options defined at different profile levels 
FND_REQUEST_SETS Reports sets 
FND_REQUEST_SET_PROGRAMS Reports within report sets 
FND_REQUEST_SET_STAGES Stores request set stages 
FND_RESPONSIBILITY Responsibilities 
FND_RESP_FUNCTIONS Function Security 
FND_USER Application users 

JA - Asia/Pacific Localizations
Table Name Description 
JAI_CMN_BOE_HDRS Stores BOE header info when a BOE Invoice is created through IL 
JAI_CMN_BOE_DTLS Detail table for BOE Invoices 
JAI_CMN_TAXES_ALL Master table for Localization Taxes 
JAI_CMN_TAX_CTGS_ALL Stores tax categories and their link to excise ITEM classes. 
JAI_CMN_TAX_CTG_LINES Stores the tax lines for defined tax categories 
JAI_CMN_VENDOR_SITES Stores excise account related information about vendors. 
JAI_RGM_DEFINITIONS Stores regime information. 
JAI_RGM_TAXES This table stores tax details for transactions having TCS tax type. 
JAI_CMN_RG_23AC_I_TRXS Stores Information of RG23A/C records and known as Quantity Register. 
JAI_CMN_RG_23AC_II_TRXS Stores Information of RG23A/C Part II Details. Also known as Amount Register 
JAI_CMN_RG_23D_TRXS Quantity register for Trading Organizations 
JAI_CMN_RG_BALANCES Store the current balances of RG23A, RG23C and PLA Registers 
JAI_CMN_RG_PLA_TRXS Stores the Transaction Information of PLA Register. 
JAI_CMN_RG_PLA_HDRS Stores PLA header Infomation when a PLA invoice is created in AP module 
JAI_CMN_RG_PLA_DTLS Stores PLA Detail Information when a PLA Invoice is created in AP Module 

QP – Advanced Pricing 
Table Name Description 
QP_LIST_HEADERS_B QP_LIST_HEADERS_B stores the header information for all lists. List types can be, for example, Price Lists, Discount Lists or Promotions. 
QP_LIST_LINES QP_LIST_LINES stores all list lines for lists in QP_LIST_HEADERS_B. 
QP_PRICE_FORMULAS_B QP_PRICE_FORMULAS_B stores the pricing formula header information. 
QP_PRICE_FORMULA_LINES QP_PRICE_FORMULA_LINES stores each component that makes up the formula. 
QP_PRICING_ATTRIBUTES QP_PRICING_ATTRIBUTES stores product information and pricing attributes. 
QP_QUALIFIERS QP_QUALIFIERS stores qualifier attribute information. 

XLA - Subledger Accounting 
Table Name Description 
XLA_EVENTS The XLA_EVENTS table record all information related to a specific event. This table is created as a type XLA_ARRAY_EVENT_TYPE. 
XLA_TRANSACTION_ENTITIES The table XLA_ENTITIES contains information about sub-ledger document or transactions. 
XLA_AE_HEADERS The XLA_AE_HEADERS table stores subledger journal entries. There is a one-to-many relationship between accounting events and journal entry headers. 
XLA_AE_LINES The XLA_AE_LINES table stores the subledger journal entry lines. There is a one-to-many relationship between subledger journal entry headers and subledger journal entry lines. 
XLA_DISTRIBUTION_LINKS The XLA_DISTRIBUTION_LINKS table stores the link between transactions and subledger journal entry lines. 
XLA_ACCOUNTING_ERRORS The XLA_ACCOUNTING_ERRORS table stores the errors encountered during execution of the Accounting Program. 
XLA_ACCTG_METHODS_B The XLA_ACCTG_METHODS_B table stores Subledger Accounting Methods (SLAM) across products. SLAMs provided by development are not chart of accounts specific. Enabled SLAMs are assigned to ledgers. 
XLA_EVENT_TYPES_B The XLA_EVENT_TYPES_B table stores all event types that belong to an event class. 
XLA_GL_LEDGERS This table contains ledger information used by subledger accounting.