Interface Tables and Programs

Appl	Type	Exact Program Name	Interface Tables
GL	Journals	Journal Import	GL_INTERFACE
GL	Budgets	Upload Budget Amounts	GL_BUDGET_INTERFACE
GL	Daily Rates	Trigger - GL_DAILY_RATES_INTERFACE_ASIT	GL_DAILY_RATES_INTERFACE
AP	Invoices	Payables Open Interface Import	AP_INVOICES_INTERFACE AP_INVOICE_LINES_INTERFACE
AP	Supplier	Supplier Open Interface Import	AP_SUPPLIERS_INT
AP	Supplier Sites	Supplier Sites Open Interface Import	AP_SUPPLIER_SITES_INT
AP	Supplier Contacts	Supplier Site Contacts Open Interface Import	AP_SUP_SITE_CONTACT_INT
AR	Transactions	Autoinvoice Import Program or Autoinvoice Master Program	RA_INTERFACE_LINES_ALL RA_INTERFACE_DISTRIBUTIONS_ALL RA_INTERFACE_SALESCREDITS_ALL
AR	Receipts	Process Lockboxes	AR_PAYMENTS_INTERFACE_ALL AR_INTERIM_CASH_RECEIPTS_ALL  AR_INTERIM_CASH_RECEIPT_LINES_ALL
AR	Customers	Customer Interface	RA_CUSTOMERS_INTERFACE RA_CUSTOMER_PROFILES_INTERFACE RA_CONTACT_PHONES_INTERFACE RA_CUSTOMER_BANKS_INTERFACE RA_CUST_PAY_METHOD_INTERFACE
CM	Bank Statement	Bank Statement Import & AutoReconciliation	CE_STATEMENT_HEADERS_INT_ALL CE_STATEMENT_LINES_INTERFACE
PO	Requisitions	Requisition Import	PO_REQUISITIONS_INTERFACE_ALL PO_REQ_DIST_INTERFACE_ALL
PO	Purchase Orders	Import Standard Purchase Orders	PO_HEADERS_INTERFACE PO_LINES_INTERFACE PO_DISTRIBUTIONS_INTERFACE
PO	Receipts	Receiving Transaction Processor	RCV_HEADERS_INTERFACE RCV_TRANSACTIONS_INTERFACE
OM	Sales Order	Order Import	OE_HEADERS_IFACE_ALL OE_LINES_IFACE_ALL OE_ACTIONS_IFACE_ALL OE_ORDER_CUST_IFACE_ALL OE_PRICE_ADJS_IFACE_ALL OE_PRICE_ATTS_IFACE_ALL
INV	Items	Import Items	MTL_SYSTEM_ITEMS_INTERFACE MTL_ITEM_REVISIONS_INTERFACE MTL_ITEM_CATEGORIES_INTERFACE
INV	Customer Items	Import Customer Items	MTL_CI_INTERFACE
INV	Customer Item Cross References	Import Customer Item Cross References	MTL_CI_XREFS_INTERFACE
INV	Material Transactions	Process transaction interface	MTL_TRANSACTIONS_INTERFACE MTL_MTL_TRANSACTION_LOTS_INTERFACE MTL_SERIAL_NUMBERS_INTERFACE
INV	Costs	Cost Import Process	CST_ITEM_CST_DTLS_INTERFACE CST_RESOURCE_COSTS_INTERFACE CST_RES_OVERHEADS_INTERFACE CST_DEPT_OVERHEADS_INTERFACE

Lookup Tables

SELECT lookup_type, description
  FROM apps.fnd_lookup_types_vl
 WHERE lookup_type LIKE '%ICSO%'

SELECT *
  FROM apps.fnd_lookup_values
 WHERE lookup_type = 'RSOL_PREVENTION_HOLDS'

Alert Tables

SELECT alert_name,
       description,
       start_date_active,
       DECODE (alert_condition_type,  'P', 'Periodic',  'E', 'Event')
          ALERT_TYPE,
       frequency_type,
       insert_flag,
       update_flag
  FROM APPS.alr_alerts
 WHERE     enabled_flag = 'Y'
       AND end_date_active IS NULL
       AND alert_name LIKE 'AA%'
     
SELECT *
  FROM apps.ALR_ACTIONS_V
 WHERE subject LIKE 'XX%Notification%'

R12 Table Changes

Suppliers:

New R12 tables  -> Old 11i Tables

AP_SUPPLIERS - replaces PO_VENDORS

AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY (Payments) and HZ (TCA):

IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.

HZ_PARTIES - Party data for the suppliers.

HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices:

Additional table in R12: AP_INVOICE_LINES_ALL

Allocations - AP_CHRG_ALLOCATIONS_ALL is obsolete in R12

Taxes:

Functionality provided by E-Business Tax

New tables in R12

ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)

ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)

ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)

ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments:

Functionality moved to central Payments (IBY)

New IBY tables in R12:

IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information

Accounting:

Functionality moved to SubLedger Accounting (SLA)

New R12 tables:

XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL 

XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL

XLA_AE_LINES-> replaces AP_AE_LINES_ALL

XLA_DISTRIBUTION_LINKS

Trial Balance:

New R12 Table

XLA_TRIAL_BALANCES

AP_LIABILITY_BALANCE-> not used in new R12 transactions

AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts:

Functionality moved to Cash Management.

CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL

CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

Table Registration in APPS

We can register custom application tables using a PL/SQL routine in the AD_DD package. Below are the Procedures in the AD_DD Package

Procedure register_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

Procedure register_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

Procedure delete_table
(p_appl_short_name in varchar2,
p_tab_name in varchar2);

Procedure delete_column
(p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);

Parameter Description
p_appl_short_name:
The application short name of the application that owns the table (usually your custom application).
p_tab_name:
The name of the table (in uppercase letters).
p_tab_type:
Use 'T' if it is a transaction table (almost all application tables), or 'S' for a "seed data" table (used only by Oracle Applications products).
p_pct_free:
The percentage of space in each of the table's blocks reserved for future updates to the table (1-99). The sum of p_pct_free and p_pct_used must be less than 100.
p_pct_used:
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.
p_col_name:
The name of the column (in uppercase letters).
p_col_seq:
The sequence number of the column in the table (the order in which the column appears in the table definition).
p_col_type:
The column type ('NUMBER', 'VARCHAR2', 'DATE', etc.).
p_col_width:
The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns (unless it has a specific width).
p_nullable:
Use 'N' if the column is mandatory or 'Y' if the column allows null values.
p_translate:
Use 'Y' if the column values will be translated for an Oracle Applications product release (used only by Oracle Applications products) or 'N' if the values are not translated (most application columns).
p_next_extent:
The next extent size, in kilobytes. Do not include the 'K'.
p_precision:
The total number of digits in a number.
p_scale:
The number of digits to the right of the decimal point in a number

Base Tables for Data Extraction

GL Journals

GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES
GL_CODE_COMBINATIONS
AP Suppliers

PO_VENDORS
PO_VENDOR_SITES_ALL
PO_VENDOR_CONTACTS
----------------
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS

AP Invoices

AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
AP_TERMS
AP_HOLDS_ALL
PA_PROJECTS_ALL
PA_TASKS
PO_VENDORS

AR Customers
HZ_CUST_ACCOUNTS
HZ_PARTIES
HZ_CUST_ACCT_SITES_ALL
HZ_LOCATIONS
HZ_PARTY_SITES
HZ_CUST_SITE_USES_ALL

AR Customer Profiles

HZ_CUSTOMER_PROFILES
HZ_CUST_PROFILE_CLASSES
HZ_CUST_PROFILE_AMTS
AR_COLLECTORS
RA_TERMS
RA_GROUPING_RULES
AR_STATEMENT_CYCLES

AR Customer Contacts
RA_CONTACTS
RA_PHONES

AR Transactions
AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_ALL
RA_CUST_TRX_TYPES_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_TERMS
HR_OPERATING_UNITS

AR Receipts

AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_All
AR_RECEIVABLE_APPLICATIONS_ALL
AR_RECEIPT_METHODS
AR_RECEIPT_CLASSES
AR_PAYMENT_SCHEDULES_ALL

PO Standard

PO_HEADERS_ALL
PO_LINES_V
PO_LINE_LOCATIONS_ALL
PO_DISTRIBUTIONS_V

PO Releases

PO_RELEASES_ALL

PO Requisitions
PO_REQUISITION_HEADERS_V
PO_REQUISITION_LINES_V

Project Data

PA_PROJECTS_ALL
PA_TASKS
PA_PROJECT_PARTIES
PA_PROJECT_ROLE_TYPES_B

Project Transaction Data
PA_EXPENDITURE_ITEMS_ALL
PA_COST_DISTRIBUTION_LINES_ALL
PA_EXPENDITURES_ALL
PA_EXPENDITURE_COMMENTS
PA_PROJECTS_ALL
PA_TASKS

INV Items
MTL_SYSTEM_ITEMS_B
ORG_ORGANIZATION_DEFINITIONS

INV Item Categories
MTL_CATEGORY_SETS_TL
MTL_CATEGORIES
MTL_ITEM_CATEGORIES
MTL_SYSTEM_ITEMS
ORG_ORGANIZATION_DEFINITIONS
MTL_ORGANIZATIONS

INV Item SubInventory
 
MTL_ITEM_SUB_INVENTORIES
ORG_ORGANIZATION_DEFINITIONS
MTL_SYSTEM_ITEMS_B
MTL_ITEM_LOCATIONS
MTL_SECONDARY_LOCATORS

Attachments
FND_DOCUMENTS_LONG_TEXT
FND_DOCUMENTS_SHORT_TEXT
FND_DOCUMENT_CATEGORIES_TL
FND_DOCUMENT_DATATYPES
FND_DOCUMENTS_TL
FND_DOCUMENTS
FND_ATTACHED_DOCUMENTS
FND_DOC_CATEGORY_USAGES