Thursday, February 20, 2014

Base Tables for Oracle Procure To Pay Cycle (P2P)

Base Tables for Oracle Procure To Pay Cycle (P2P)



Procure To Pay Cycle




















Requisition
1)po_requisition_headers_all ( segment1 column in table represent requisition number)
2)po_requisition_lines_all
3)po_req_distributions_all
Note: requisition_header_id is the link between po_requisition_headers_all and po_requisition_lines_all
requisition_line_id is the link between po_requisition_lines_all and po_req_distributions_all

Purchase order
1)po_headers_all
2)po_lines_all
3)po_distributions_all
4)po_line_locations_all
Note: po_header_id is the link between all these tables

Receipt
1)rcv_shipment_headers
2)rcv_shipment_lines
3)rcv_transactions
Note: shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_line po_header_id   is the link between rcv_shipment_headers and rcv_transaction

Invoices
1)ap_invoices_all
2)ap_invoice_distributions_all
Note: invoice_id is the link between ap_invoices_all and ap_invoice_distributions_all

Payments
1)ap_checks_all
2)ap_invoice_payments_all
3)ap_payment_schedules_all

Payment Reconcillation
1)ap_banks
2)ap_bank_branches

GL Transfer
1)gl_periods
2)gl_period_status
3)gl_set_of_books


Sunday, February 2, 2014

BASE TABLES IN ORACLE APPS

                              BASE TABLES IN ORACLE APPS



Also Refer to the article 'Some links and tables of  Oracle Apps modules' published in the month of Oct-2013
Oracle Apps Base Tables and Interface Tables

INVENTORY BASE TABLES           INVENTORY INTERFACE TABLES
==================================================================  
MTL_SYSTEM_ITEMS_B                MTL_SYSTEM_ITEMS_INTERFACE         
MTL_ITEM_CATEGORIES              MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_LOCATIONS                MTL_ITEM_REVISION_INTERFACE             
MTL_ITEM_REVISIONS_B             MTL_INTERFACE_ERRORS
MTL_PARAMETERS
MTL_ITEM_SUB_INVENTORIES
MTL_ONHAND_QUANTITIES
MTL_CATEGORY_SETS_B
CST_ITEM_COSTS
CST_COST_TYPES
MTL_MATERIAL_TRANSACTIONS
MTL_TRANSACTION_TYPES
MTL_SECONDARY_INVENTORIES
MTL_SERIAL_NUMBERS
MTL_LOT_NUMBERS
MTL_TRANSACTION_ACCOUNTS

able
Description
MTL_PARAMETERS
It maintains a set of default options like general ledger accounts; locator, lot, and serial controls, inter-organization options, costing method, etc. for each organization defined in Oracle Inventory. Each organization’s item master organization (MASTER_ORGANIZATION_ID) and costing organization (COST_ORGANIZATION_ID) are maintained here.
MTL_SYSTEM_ITEMS_B
This is the definition table for items. This table holds the definitions for inventory items, engineering items, and purchasing items. The primary key for an item is the INVENTORY_ITEM_ID and ORGANIZATION_ID. Therefore, the same item can be defined in more than one organization. Items now support multilingual description. MLS is implemented with a pair of tables: MTL_SYSTEM_ITEMS_B and MTL_SYSTEM_ITEMS_TL. Translations table (MTL_SYSTEM_ITEMS_TL) holds item Description and Long Description in multiple languages.
MTL_ITEM_STATUS
This is the definition table for material status codes. Status code is a required item attribute. It indicates the status of an item, i.e., Active, Pending, Obsolete.
MTL_UNITS_OF_MEASURE_TL
This is the definition table for both the 25-character and the 3-character units of measure. The base_uom_flag indicates if the unit of measure is the primary unit of measure for the uom_class. Oracle Inventory uses this table to keep track of the units of measure used to transact an item.
MTL_ITEM_LOCATIONS
This is the definition table for stock locators. The associated attributes describe which subinventory this locator belongs to, what the locator physical capacity is, etc.
MTL_ITEM_CATEGORIES
This table stores inventory item assignments to categories within a category set. For each category assignment, this table stores the item, the category set, and the category. Items always may be assigned to multiple category sets. However, depending on the Multiple Assignments Allowed attribute value in a given category set definition, an item can be assigned to either many or only one category in that category set.
MTL_CATEGORIES_B
This is the code combinations table for item categories. Items are grouped into categories within the context of a category set to provide flexible grouping schemes. Item categories now support multilingual category description. MLS is implemented with a pair of tables: MTL_CATEGORIES_B and MTL_CATEGORIES_TL. MTL_CATEGORIES_TL table holds translated Description for Categories.
MTL_CATEGORY_SETS_B
It contains the entity definition for category sets. A category set is a categorization scheme for a group of items. Items may be assigned to different categories in different category sets to represent the different groupings of items used for different
purposes. An item may be assigned to only one category within a category set, however. STRUCTURE_ID identifies the flexfield structure associated with the category set. Category Sets now support multilingual category set name and description. MLS is implemented with a pair of tables: MTL_CATEGORY_SETS_B and MTL_CATEGORY_SETS_TL. MTL_CATEGORY_SETS_TL table holds translated Name and Description for Category Sets.
MTL_DEMAND
This table stores demand and reservation information used in Available To Promise, Planning and other Manufacturing functions. There are three major row types stored in the table: Summary Demand rows,
Open Demand Rows, and Reservation Rows.
MTL_SECONDARY_INVENTORIES
This is the definition table for the subinventory. A subinventory is a section of inventory, i.e., raw material, finished goods, etc. Subinventories are assigned to items (in a many to one relationship), indicating a list of valid places where this item will physically exist in inventory.
MTL_ONHAND_QUANTITIES
It stores quantity on hand information by control level and location. It is maintained as a stack of receipt records, which are consumed by issue transactions in FIFO order. The quantity on hand of an item at any particular control level and location can be found by summing TRANSACTION_QUANTITY for all records that match the criteria.
MTL_TRANSACTION_TYPES
It contains seeded transaction types and the user defined ones. USER_DEFINED_FLAG will distinguish the two. The table also stores the TRANSACTION_ACTION_ID and TRANSACTION_SOURCE_TYPE_ID that is associated with each transaction type.
MTL_MATERIAL_TRANSACTIONS
This table stores a record of every material transaction or cost update performed in Inventory. Records are inserted into this table either through the transaction processor or by the standard cost update program. The columns TRANSACTION_TYPE_ID, TRANSACTION_ACTION_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_SOURCE_ID and TRANSACTION_SOURCE_NAME describe what the transaction is and against what entity it was performed.
MTL_ITEM_ATTRIBUTES
This table stores information on item attributes. Each
row in the table corresponds to an attribute. The table stores the attribute name, the corresponding user-friendly name seen by the users, and the kind of validation enforced on the attribute.
MTL_ITEM_CATALOG_GROUPS_B
This is the code combinations table for item catalog groups. An item catalog group consists of items that can be described by the same set of descriptive elements or item properties. When an item is associated with an item catalog group, the item inherits the descriptive elements for that group which then behave like additional item attributes.
MTL_ITEM_REVISIONS_B
It stores revision levels for an inventory item. When an item is defined a starting revision record is written out to this table, so every item will at least have one starting revision.
MTL_ITEM_TEMPLATES_B
This is the definition table for item templates. It
contains the user-defined name (TEMPLATE_NAME) and description (DESCRIPTION) ONLY for backward compatibility. You can use a template to set certain item attributes.
MTL_DESCRIPTIVE_ELEMENTS
It stores the descriptive element definitions for an item catalog group. Descriptive elements are defining properties used to describe in the catalog group.
MTL_DESCR_ELEMENT_VALUES
It stores the descriptive element values for a specific item. When an item is associated with a particular item catalog group, one row per descriptive element (for that catalog group) is inserted into this table.
ORG_ACCT_PERIODS
It holds the open and closed financial periods for organizations.
MTL_CUSTOMER_ITEMS
It stores customer item information for a specific customer. Each record can be defined at one of the following levels: Customer, Address Category, and Address. The customer item definition is organization independent.
MTL_SYSTEM_ITEMS_INTERFACE
It temporarily stores the definitions for inventory items, engineering items and purchasing items before loading this information into Oracle Inventory.
MTL_TRANSACTIONS_INTERFACE
It allows calling applications to post material transactions (movements, issues, receipts etc. to Oracle Inventory  transaction module.
MTL_ITEM_REVISIONS_INTERFACE
It temporarily stores revision levels for an inventory item before loading this information into Oracle Inventory.
MTL_ITEM_CATEGORIES_INTERFACE
This table temporarily stores data about inventory item assignments to category sets and categories before loading this information into Oracle Inventory.
MTL_DESC_ELEM_VAL_INTERFACE
This table temporarily stores descriptive element values for an item that is associated with an item catalog group before loading this information into Oracle Inventory.
MTL_DEMAND_INTERFACE
It is the interface point between non-Inventory applications and the Inventory demand module. Records inserted into this table are processed by the Demand Manager concurrent program.
MTL_INTERFACE_ERRORS
It stores errors that occur during the item interface process reporting where the errors occurred along with the error messages.

ORACLE Projects


Table
Description
PA_PROJECTS_ALL
It stores the highest units of work defined in Oracle Projects.
PA_PROJECT_ASSETS_ALL
It contains assets information defined for capital projects.
PA_PROJECT_ASSIGNMENTS
It stores details of all Assignments for a project.
PA_PROJECT_CLASSES
It contains the class codes of class categories that are used to classify projects.
PA_PROJECT_ROLE_TYPES
Implementation-defined responsibilities or positions assigned to employees on projects are stored here.
PA_PROJECT_STATUSES
It stores valid project status codes.
PA_PROJECT_TYPES_ALL
It stores implementation-defined project classifications that supply default information and drive some project processing.
PA_TASKS
It contains user-defined subdivisions of project work.
PA_TASK_TYPES
It stores implementation-defined classifications of task.
PA_TRANSACTION_INTERFACE_ALL
It is an interface table to import transactions from external sources into Oracle Projects.
PA_TRANSACTION_SOURCES
It stores implementation-defined sources of imported transactions originating in an external system.
PA_IMPLEMENTATIONS_ALL
It contains information about the configuration of an Oracle Projects installation.
PA_ACTION_SETS
It stores action set templates as well as action sets belonging to an object, such as projects, requirements, etc.
PA_ACTION_SET_LINES
It stores action set lines that belong to an action set or an action set template.
PA_ACTION_SET_TYPES
It stores attributes of action set types.
PA_AGREEMENTS_ALL
It has customer contracts that serve as the basis for work authorization.
PA_AGREEMENT_TYPES
Implementation-defined classifications of customer agreements.
PA_BILL_RATES_ALL
Information about bill rates and markups of standard bill rate schedules.
PA_BUDGETS
It stores budgets information.
PA_BUDGET_LINES
It stores detail lines of project and task budgets.
PA_BUDGET_TYPES
It contains implementation-defined classifications of types of budgets used for different business purposes.
PA_CLASS_CATEGORIES
It stores implementation-defined categories for classifying projects.
PA_CLASS_CODES
It stores implementation-defined values within class categories that can be used to classify projects.
PA_EVENTS
It stores entries assigned to tasks that generate revenue and/or billing but are not directly related to expenditure items.
PA_EVENT_TYPES
It stores implementation-defined classifications of events.
PA_EXPENDITURES_ALL
Groups of expenditure items incurred by employees or organizations for an expenditure period.
PA_EXPENDITURE_CATEGORIES
Implementation-defined groupings of expenditure types by type of cost.
PA_EXPENDITURE_ITEMS_ALL
It contains the smallest units of expenditure charged to projects and tasks.
PA_EXPENDITURE_TYPES
Implementation-defined classifications of expenditures charged to projects and tasks.
PA_PERIODS_ALL
Implementation-defined periods against which project performance is measured.
PA_RBS_DENORM
This table stores normalized resource breakdown structure information.
PA_RBS_ELEMENTS
This table stores the RBS element information and the parent-child relationship.
PA_RESOURCES
It contains resources used in budgeting and project summary amounts.
PA_ROLE_LISTS
It stores lists of roles defined with the system.
PA_SCHEDULES
It displays the schedule details for requirements and assignments. It also displays calendar schedules.




PO REQ BASE TABLES                             PO REQ INTERFACE TABLES
==================================================================
PO_REQUISITION_HEADERS_ALL       PO_REQUISITIONS_INTERFACE_ALL
PO_REQUISITION_LINES_ALL              PO_REQ_DIST_INTERFACE_ALL
PO_REQ_DISTRIBUTIONS_ALL 



PO BASE TABLES                                   PO INTERFACE TABLES
==================================================================
PO_HEADERS_ALL                                 PO_HEADERS_INTERFACE
PO_LINES_ALL                                        PO_LINES_INTERFACE
PO_LINE_LOCATIONS_ALL                  PO_DISTRIBUTIONS_INTERFACE
PO_DISTRIBUTIONS_ALL                     PO_INTERFACE_ERRORS            
PO_RELEASES_ALL
PO_AGENTS
PO_ACTION_HISTORY

PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS





RCV_SHIPMENT_HEADERS                RCV_HEADERS_INTERFACE
RCV_SHIPMENT_LINES                       RCV_TRANSACTIONS_INTERFACE
RCV_TRANSACTIONS


PAYBLES BASE TABLES                        PAYBLES INTERFACE TABLES 
                         
=======================================================
AP_INVOICES_ALL                                 AP_INVOICES_INTERFACE                 
AP_INVOICE_LINES_ALL                      AP_INVOICE_LINES_INTERFACE
AP_INVOICE_DISTRIBUTIONS_ALL
AP_INVOICE_PAYMENTS_ALL            AP_INTERFECE_REJECTIONS

AP_PAYMENT_DISTRIBUTIONS_ALL     AP_SUPPLIERS_INT
AP_PAYMENT_HISTORY_ALL                  AP_SUPPLIER_SITES_INT
AP_PAYMENT_SCHEDULES_ALL            AP_SUP_SITE_CONTACT_INT

AP_AE_HEADERS_ALL
AP_AE_LINES_ALL
AP_BATCHES_ALL
AP_TERMS
AP_LOOKUP_CODES
AP_ACCOUNTING_EVENTS_ALL
AP_BANK_ACCOUNTS_ALL
AP_BANK_BRANCHES
AP_CHECKS_ALL
AP_HOLDS_AL
AP_CARD_CODES
AP_EXPENSE_REPORTS_ALL
AP_EXPENSE_REPORT_HEADERS_ALL
AP_EXPENSE_REPORT_LINES_ALL





OM BASE TABLES                                    OM INTERFACE TABLES
=======================================================

OE_ORDER_HEADERS_ALL                  OE_HEADERS_IFACE_ALL
OE_ORDER_LINES_ALL                         OE_LINES_IFACE_ALL
OE_ORDER_HOLDS_ALL                       OE_ACTIONS_IFACE_ALL
OE_ORDER_SOURCES
OE_TRANSACTION_TYPES_TL/ALL
OE_PAYMENT_TYPES_TL


WSH_DELIVERY_DETAILS
WSH_NEW_DELIVERIES
WSH_DELIVERY_ASSIGNMENTS

OE_ORDER_HOLDS

RA_SALESREPS
OE_TRANSACTION_TYPES_TL
AR_CUSTOMERS
RA_TERMS

QP_LIST_HEADERS
QP_LIST_LINES
QP_PRICING_ATTRIBUTES

OE_ORDER_HEADERS_ALL oe_order_pub.process_order
OE_ORDER_LINES_ALL
OE_SALES_CREDITS




GL BASE TABLES                              GL INTERFACE TABLES
=======================================================                           
GL_JE_HEADERS                               GL_INTERFACE
GL_JE_LINES                                      GL_INTERFACE_HISTORY
GL_JE_CATEGORIES                         GL_DAILY_RATES_INTERFACE  
GL_JE_SOURCES                                GL_BUDGET_INTERFACE
GL_SETS_OF_BOOKS                         GL_IEA_INTERFACE
GL_DAILY_RATES
GL_CODE_COMBINATIONS
GL_BALANCES
GL_PERIODS
GL_PERIOD_SETS
GL_CURRENCIES
GL_HISTORICAL_RATES
GL_LOOKUPS   

  

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_PARTIES                                      HZ_PARTY_INERFACE      
HZ_CUST_ACCOUNTS                    HZ_PARTY_INTERFACE_ERRORS
HZ_CUST_ACCT_SITES_ALL         RA_CONTACT_PHONES_INT_ALL
HZ_CUST_SITE_USES_ALL            RA_CUSTOMER_PROFILES_INT_ALL        
HZ_PARTY_SITES                             RA_INTERFACE_DISTRIBUTIONS_ALL
HZ_LOCATIONS                                RA_INTERFACE_ERRORS_ALL
HZ_PARTY_SITE_USES                    RA_INTERFACE_LINES_ALL
HZ_CUSTOMER_PROFILES            RA_INTERFACE_SALESCREDITS_ALL
HZ_ORGANIZATION_PROFILES    AR_PAYMENTS_INTERFACE_ALL
HZ_PERSON_PROFILES                  AR_TAX_INTERFACE  
RA_CUSTOMER_TRX                              RCV_HEADERS_INTERFACE
RA_CUSTOMER_TRX_PARTIAL_V       RCV_TRANSACTIONS_INTERFACE
RA_CUSTOMER_TRX_LINES_ALL
RA_CUSTOMERS
AR_PAYMENT_SCHEDULES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUST_TRX_TYPES_ALL
RA_BATCH_SOURCES_ALL
AR_VAT_TAX_ALL
RA_TERMS
AR_PERIODS
AR_PERIOD_TYPES




 AR Tables Diagrammatic relation 




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



Key FND Tables in Oracle Application






















Here there are few key FND tables that we use in our AOL queries.

FND_APPLICATION: 
 Stores applications registered with Oracle Application Object Library.
FND_APPLICATION_TL: 
Stores translated information about all the applications registered with Oracle Application Object Library.
FND_APP_SERVERS: 
This table will track the servers used by the E-Business Suite system.
FND_ATTACHED_DOCUMENTS: 
Stores information relating a document to an application entity.
FND_CONCURRENT_PROCESSES: 
Stores information about concurrent managers.
FND_CONCURRENT_PROCESSORS: 
Stores information about immediate (subroutine) concurrent program libraries.
FND_CONCURRENT_PROGRAMS: 
Stores information about concurrent programs. Each row includes a name and description of the concurrent program.
FND_CONCURRENT_PROGRAMS_TL: 
Stores translated information about concurrent programs in each of the installed languages.
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_CURRENCIES: 
Stores information about currencies.
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_DESCRIPTIVE_FLEXS: 
Stores setup information about descriptive flexfields.
FND_DESCRIPTIVE_FLEXS_TL: 
Stores translated setup information about descriptive flexfields.
FND_DOCUMENTS: 
Stores language-independent information about a document.
FND_EXECUTABLES: 
Stores information about concurrent program executables.
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_LANGUAGES: 
Stores information regarding languages and dialects.
FND_MENUS: 
It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.
FND_MENUS_TL: 
Stores translated information about the menus in FND_MENUS.
FND_MENU_ENTRIES: 
Stores information about individual entries in the menus in FND_MENUS.
FND_PROFILE_OPTIONS: 
Stores information about user profile options.
FND_REQUEST_GROUPS: 
Stores information about report security groups.
FND_REQUEST_SETS: 
Stores information about report sets.
FND_RESPONSIBILITY: 
Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.
FND_RESPONSIBILITY_TL: 
Stores translated 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_SECURITY_GROUPS: 
Stores information about security groups used to partition data in a Service Bureau architecture.
FND_SEQUENCES: 
Stores information about the registered sequences in your applications.
FND_TABLES: 
Stores information about the registered tables in your applications.
FND_TERRITORIES: 
Stores information for countries, alternatively known as territories.
FND_USER: 
Stores information about application users.
FND_VIEWS: 
Stores information about the registered views in your applications.