Table level Data flow for Order to Cash Flow (Oracle Apps)

In this blog I have highlight some of the KEY tables involved in Order to Cash table. There are lot of database table involves in this flow , but I have highlighted the key table and link between these tables.It help you while desiging the reports &queries. These tables are
OE_ORDER_HEADERS_ALL                  OE_ORDER_LINES_ALL 
WSH_DELIVERY_DETAILS                    WSH_DELIVERY_ASSIGNMENTS
WSH_NEW-DELIVERIES                         WSH_DELIVERY_LEGS
WSH_TRIP_STOPS                                   WSH_TRIPS
M TL_TRX_REQUEST_LINES                MTL_MATERIAL_TRANSACTION_TEMP
OE_SETS                                                     OE_LINE_SETS                     
PO_LINES_ALL                                         MTL_RESERVATIONS
MTL_MATERIAL_TRANSACTIONS_ALL
Data Flow Diagram

In case you need sql Queries , please feel free to contact me.

Important Oracle Apps Tables

Trading Partners Tables
=======================
select * from ece_tp_headers;
select * from ece_tp_details;

db objects Tales
====================
select * from dba_objects;
select * from user_objects;
select * from all_source;

Server Directories Tables
===========================
select * from v$parameter where name like '%utl_file%';

URL table
===============
select * from icx_parameters;

Instance Name Table
===================
 select INSTANCE_NAME  from V$INSTANCE;

AP, AR, GL, INV, PA, FA, PO, TCA, Workflow - Interface and Base table in Oracle Apps R12

Main Interface and Base table in Oracle Apps R12

GL Module (General Ledger Module tables in Oracle Apps R12)
Interface Table	Base Table
gl_interface gl_budget_interface	gl_je_batches gl_je_headers gl_je_lines gl_je_sources gl_je_categories gl_sets_of_books gl_daily_rates gl_balances gl_periods gl_period_sets gl_code_conbinations

AR Module (Account Receivable Module tables in Oracle Apps R12)
Interface Table	Base Table
ra_customers_interface_all   ra_contact_phones_int_all ra_customer_profiles_int_all	hz_parties hz_cust_accounts hz_cust_acct_sites_all hz_cust_sit_use_all hz_party_sites hz_locations hz_party_site_uses hz_customer_profiles hz_organization_profiles hz_person_profiles
ra_interface_lines_all ra_interface_distributions_all ra_interface_salescredits_all	ra_customer_trx_all ra_customer_trx_lines_all ra_cust_trx_line_gl_dist_all ra_cust_trx_types_all ar_payment_schedules_all ra_batch_sources_All ar_vat_tax_all ra_terms ar_periods ar_period_types

OM Module (Order Management Module tables in Oracle Apps R12)
Interface Table	Base Table
oe_headers_iface_all oe_lines_iface_all oe_actions_iface_all	oe_order_headers_all oe_order_lines_all oe_order_sources oe_transaction_types_tl oe_transaction_types_all

INV Module (Inventory Module tables in Oracle Apps R12)
Interface Table	Base Table
mtl_system_items_interface mtl_item_categories_interface mtl_item_revisions_interface mtl_interface_errors	mtl_system_items_b mtl_system_items_tl mtl_item_locations mtl_item_categories mtl_item_revisions_b mtl_parameters hr_all_organizations_units cst_item_costs qp_list_headers jft_rs_salesreps

Workflow Tables tables in Oracle Apps R12
Interface Table	Base Table
	wf_items wf_process_activities wf_item_activity_statuses wf_item_types_tl

PO Module (Purchase Order tables in Oracle Apps R12)
Interface Table	Base Table
PO REQUISITIONS po_requistitions_interface_all po_req_dist_interface_all PURCHASE ORDERS po_headers_interface po_lines_interface po_distributions_interface	PO VENDORS po_vendors po_vendor_sites_all po_vendor_contacts PO REQUISITIONS po_requisition_headers_all po_requisition_lines_all po_req_distributions_all PURCHASE ORDERS po_headers_all po_lines_all po_line_locations_all po_distributions_all

AP Module (Account Payables tables in Oracle Apps R12)
Interface Table	Base Table
ap_invoices_interface ap_invoice_lines_interface	ap_invoices_all ap_invoice_distributions_all ap_payments_schedules_all

FA Module (Fixed Asset tables in Oracle Apps R12)
Interface Table	Base Table
fa_inv_interface fa_production_interface fa_mass_additions	fa_additions_b fa_additions_tl fa_books fa_deprn_summary fa_deprn_details fa_deprn_history fa_retirements

PA  Module (Project Accounting in Oracle Apps R12)
Interface Table	Base Table
	pa_tasks pa_expenditure_items_all pa_expenditures_all pa_transaction_constrols pa_projects_all pa_budget_versions pa_resource_assigments

TCA Architecture (Trading Community Architecture tables in Oracle Apps R12)
Interface Table	Base Table
	hz_parties hz_cust_accounts hz_cust_acct_sites_all hz_cust_sit_use_all hz_party_sites hz_locations hz_party_site_uses hz_customer_profiles hz_organization_profiles hz_person_profiles

“A GUIDE FOR ORACLE E-BUSINESS SUITE” –A complete Solution and Learning platform For Oracle Apps R12/11i, Training, India Localization, Technical, P2P, O2C, Drop-shipment, AR/AP Cycle, Implementation, Bug or error in Oracle Apps R12/11i, Up-gradation, Support, Alert, Personalization, Discoverer management, OracleApps R12/11i Module, Finance, SCM, MRP, ASCP, WIP, BOM, FA, AP, AR, OM, INV, AOL, MOAC, TCA structure, Project Accounting, CM, Functional,Oracle DBA, ERP, Techno-functional, OAF, Oracle Apps Interview Question and Error in Oracle apps R12/11i. For More Information Visit on www.OracleAppsGuide.com Or Subscribe your email-id on OracleAppsGuide

Register Custom Tables in Oracle Apps

Register Custom Table In Oracle Applications

All you technical people out there very well know the purpose of creating custom table. Its basically when we need to store data before pulling into standard table (In case of inbound interface) or putting extract of data to share with external entity (In case of outbound interface). All these activities can be accomplished without registering custom table in oracle apps.

If you have a need to use these custom tables in standard functionality in frond end like using in Alerts / Audits, you must register them. Otherwise your custom table will not be visible in front end.

Lets start this by creating a sample custom table.

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 
Connected as c_apps
 
SQL> 
SQL> CREATE TABLE C_FND_INTERFACES
  2  ( INTERFACE_ID            NUMBER               NOT NULL PRIMARY KEY,
  3    SERVICE_CHANNEL         VARCHAR2(64 BYTE)    NOT NULL,
  4    SOURCE                  VARCHAR2(64 BYTE) NOT NULL,
  5    ATTRIBUTE1              VARCHAR2(2000 BYTE),
  6    ATTRIBUTE2              VARCHAR2(2000 BYTE),  7    ATTRIBUTE3              VARCHAR2(2000 BYTE),
  8    ATTRIBUTE4              VARCHAR2(2000 BYTE),
  9    ATTRIBUTE5              VARCHAR2(2000 BYTE),
 10    ATTRIBUTE6              VARCHAR2(2000 BYTE),
 11    ATTRIBUTE7              VARCHAR2(2000 BYTE),
 12    ATTRIBUTE8              VARCHAR2(2000 BYTE),
 13    ATTRIBUTE9              VARCHAR2(2000 BYTE),
 14    ATTRIBUTE10             VARCHAR2(2000 BYTE),
 15    STATUS                  VARCHAR2(50 BYTE)    NOT NULL,
 16    ERROR_MESSAGE           VARCHAR2(2000 BYTE),
 17    CREATED_BY              NUMBER               NOT NULL,
 18    CREATION_DATE           DATE                 NOT NULL,
 19    LAST_UPDATED_BY         NUMBER               NOT NULL,
 20    LAST_UPDATE_DATE        DATE                 NOT NULL,
 21    REQUEST_ID              NUMBER,
 22    BATCH_ID                NUMBER
 23  )
 24  TABLESPACE C_APPS
 25  PCTUSED    0
 26  PCTFREE    10
 27  INITRANS   1
 28  MAXTRANS   255
 29  STORAGE    (
 30              INITIAL          64K
 31              NEXT             1M
 32              MINEXTENTS       1
 33              MAXEXTENTS       UNLIMITED
 34              PCTINCREASE      0
 35              BUFFER_POOL      DEFAULT
 36             )
 37  LOGGING
 38  NOCACHE
 39  NOPARALLEL
 40  /
 
Table created
 
SQL> 

You would have noticed that table is created under custom schema. Make sure you create synonym under APPS schema.

There are 4 steps involved in registering table in Oracle Applications

#1 - Register Table

ad_dd.register_table 
(p_appl_short_name IN VARCHAR2, -- Application Short Name
 p_table_name      IN VARCHAR2, -- Table Name
 p_table_type      IN VARCHAR2, -- Table Type. Use 'T' if transaction table. 'S' for seed data table.
 p_next_extent     IN NUMBER DEFAULT 512,
 p_pct_free        IN NUMBER DEFAULT 10, -- % of space in each of the table block reserved for future updates(1-99)
 p_pct_used        IN NUMBER DEFAULT 70) -- MINimum percentage of used space IN each data block of the table (1-99)
                                       ; -- The sum of p_pct_free and p_pct_used must be less than 100

Sample Script

EXECUTE ad_dd.register_table 

(p_appl_short_name =>  'C_APPS',
 p_table_name      =>  'C_FND_INTERFACES',
 p_table_type      =>  'T',
 p_next_extent     =>  512,
 p_pct_free        =>  10,
 p_pct_used        =>  70);
                             

#2 - Register Column

ad_dd.register_column

(p_appl_short_name IN VARCHAR2, -- Application Short Name
 p_table_name      IN VARCHAR2, -- Table Name
 p_column_name     IN VARCHAR2, -- Column Name
 p_column_seq      IN NUMBER,   -- Sequence NUMBER of Column in table
 p_column_type     IN VARCHAR2, -- Column Type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
 p_column_width    IN NUMBER,   -- Colum Size,Can use 9 for DATE columns, 38 for NUMBER columns
 p_nullable        IN VARCHAR2, -- Use 'N' if mandatory or 'Y' if it allows null values
 p_translate       IN VARCHAR2, -- 'N' if the values are not translated
 p_precision       IN NUMBER DEFAULT NULL, -- NUMBER of digits in a NUMBER    
 p_scale           IN NUMBER DEFAULT NULL);-- NUMBER of digits to the right of the decimal point

Sample Script

EXECUTE ad_dd.register_column
(p_appl_short_name =>   'C_APPS'
 p_table_name      =>   'C_FND_INTERFACES'
 p_column_name     =>   'interface_id'
 p_column_seq      =>   'c_fnd_interfaces_seq'
 p_column_type     =>   NUMBER
 p_column_width    =>   10
 p_nullable        =>   'N'
 p_translate       =>   'N'
 p_precision       =>   NULL
 p_scale           =>   NULL);

#3 - Register Primary Key

ad_dd.register_primary_key
(p_appl_short_name IN VARCHAR2,  -- Application Short Name
 p_key_name        IN VARCHAR2,  -- Primary Key Name
 p_table_name      IN VARCHAR2,  -- Table Name
 p_description     IN VARCHAR2,  -- Primary key Description
 p_key_type        IN VARCHAR2,  -- (D/S)Developer/Surrogate,DEFAULTs to null
 p_audit_flag      IN VARCHAR2,  -- (Y/N)DEFAULTs to null
 p_enabled_flag    IN VARCHAR2); -- (Y/N)DEFAULTs to null

Sample Script

EXECUTE ad_dd.register_primary_key
(p_appl_short_name =>   'C_APPS',
 p_key_name        =>   'INTERFACE_ID_PK',
 p_table_name      =>   'C_FND_INTERFACES',
 p_description     =>   'Interface ID Primary Key',
 p_key_type        =>   'S',
 p_audit_flag      =>   'Y',
 p_enabled_flag    =>   'Y');

#4 - Register Primary Key Column

ad_dd.register_primary_key_column
(p_appl_short_name IN VARCHAR2,
 p_key_name        IN VARCHAR2,
 p_tab_name        IN VARCHAR2,
 p_col_name        IN VARCHAR2,
 p_col_sequence    IN NUMBER);

Sample Script

EXECUTE ad_dd.register_primary_key_column
(p_appl_short_name =>  'C_APPS',
 p_key_name        =>  'INTERFACE_ID_PK',
 p_tab_name        =>  'C_FND_INTERFACES',
 p_col_name        =>  'INTERFACE_ID',
 p_col_sequence    =>  '1');

Let’s go and validate in application.

Navigate to Application Developer –> Application –> Database –> Table
F11 –> Enter your table_name ‘C_FND_INTERFACES’ –> Ctrl + F11.

 

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.

HZ(TCA) tables in Oracle Receivables

This article describes few important HZ tables in AR and their relationships with each other.

HZ_PARTIES:

The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. The primary key for this table is PARTY_ID.

Few Important Columns are

• PARTY_ID: Party identifier
• PARTY_NUMBER: Unique identification number for this party
• PARTY_NAME: Name of the party
• PARTY_TYPE: The party type can only be Person, Organization, Group or Relationship.

HZ_PARTY_SITES:

The HZ_PARTY_SITES table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. The primary key for this table is PARTY_SITE_ID.

Few Important Columns are

• PARTY_SITE_ID: Party site identifier.
• PARTY_ID: Identifier for the party. Foreign key to the HZ_PARTIES table.
• LOCATION_ID: Identifier for the party site. Foreign key to the HZ_LOCATIONS table.
• PARTY_SITE_NUMBER: Party site number.
• PARTY_SITE_NAME: User-defined name for the site.
• ADDRESSEE: Addressee information.

HZ_LOCATIONS:

The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts. The primary key for this table is LOCATION_ID.

Few Important Columns are

• LOCATION_ID: Unique identifier for this location
• COUNTRY: Country code from the TERRITORY_CODE column in the FND_TERRITORY table
• ADDRESS1: First line for address
• ADDRESS2: Second line for address
• ADDRESS3: Third line for address
• ADDRESS4: Fourth line for address
• CITY: City
• POSTAL_CODE: Postal Code
• STATE: State
• ADDRESS_KEY: Derived key that facilitates fuzzy searches

HZ_CUST_ACCOUNTS:

The HZ_CUST_ACCOUNTS table stores information about customer accounts , or business relationships that the deploying company establishes with a party of type Organization or Person. This table focuses on business relationships and how transactions are conducted in the relationship. Since a party can have multiple customer accounts, this table might contain several records for a single party. For example, an individual person can establish a personal account, family account, and a professional account for a consulting practice. The primary key for this table is CUST_ACCOUNT_ID.

Few Important Columns are

• CUST_ACCOUNT_ID: Customer account identifier
• PARTY_ID: A foreign key to the HZ_PARTY table.
• ACCOUNT_NUMBER: Account Number
• CUSTOMER_TYPE: Receivables lookup code for the CUSTOMER_TYPE attribute. I for internal customers, R for revenue generating external customers.
• CUSTOMER_CLASS_CODE: Customer class identifier

HZ_CUST_ACCT_SITES_ALL:

The HZ_CUST_ACCT_SITES_ALL table stores all customer account sites across all operating units. Customer account sites are addresses, for customer accounts, where the deploying company does business with its customers. One customer account can have multiple customer account sites, and customer account sites for one customer account can belong to multiple operating units. The primary key for this table is CUST_ACCT_SITE_ID.

Few Important Columns are

• CUST_ACCT_SITE_ID: Customer site identifier
• CUST_ACCOUNT_ID: Identifier for a customer account. Foreign key to the HZ_CUST_ACCOUNTS table
• PARTY_SITE_ID: Identifier for a party site. Foreign key to the HZ_PARTY_SITES table
• BILL_TO_FLAG: Indicates if this is a Bill-To site.
• SHIP_TO_FLAG: Indicates if this is a Ship-To site.
• MARKET_FLAG: Indicates if this is a Marketing site.

HZ_CUST_SITE_USES_ALL:

The HZ_CUST_SITE_USES_ALL table stores business purposes assigned to customer account sites, for example Bill-To, Ship-To, and Statements. Each customer account site can have one or more purposes. This table is a child of the HZ_CUST_ACCT_SITES_ALL table, with the foreign
key CUST_ACCT_SITE_ID. The HZ_CUST_SITE_USES_ALL table also stores operating unit identifier, though the HZ_CUST_ACCT_SITES_ALL table itself stores the operating unit for customer account sites. The primary key for this table is SITE_USE_ID.

Few Important Columns are

• SITE_USE_ID: Site use identifier
• CUST_ACCT_SITE_ID: Identifier for the customer account site. Foreign key to the HZ_CUST_ACCT_SITES_ALL table
• SITE_USE_CODE: Business purpose assigned to customer site account, such as Bill-To, Market, and Statements.
• PRIMARY_FLAG: Indicates if this site is the primary site for this customer account. Y for the primary customer account site. N for other customer account sites.

HZ_CUSTOMER_PROFILES:

The HZ_CUSTOMER_PROFILES table stores information about the credit characteristics of a single customer account or a customer account site or a party. A profile class defined in the
HZ_CUSTOMER_PROFILE_CLASSES table can be used to provide default values for the attributes in this table. The primary key for this table is CUST_ACCOUNT_PROFILE_ID.

Few Important Columns are

• CUST_ACCOUNT_PROFILE_ID: Unique identifier of this customer profile
• CUST_ACCOUNT_ID: Identifier for the Customer Account. Foreign key to the HZ_CUST_ACCOUNTS table.
• STATUS: Indicates whether the profile is active or inactive

HZ_CUST_PROFILE_CLASSES:

The HZ_CUST_PROFILE_CLASSES table stores information about the credit characteristics that are common across a group of customer accounts. The characteristics specified in this table can be used as default characteristics for similar customer accounts. The primary key for this table is PROFILE_CLASS_ID.

HZ_PARTY_RELATIONSHIPS:

The HZ_PARTY_RELATIONSHIPS table stores information about relationships between parties.