ORACLE APPS TABLES

Receivables (TCA Tables)

 

HZ_PARTIES

PARTY_ID, PARTY_NUMBER
PARTY_NAME
PARTY_TYPE
VALIDATED_FLAG
ORIG_SYSTEM_REFERENCE, SIC_CODE
HQ_BRANCH_IND
CUSTOMER_KEY
TAX_REFERENCE
JGZZ_FISCAL_CODE, DUNS_NUMBER
TAX_NAME

HZ_CUST_ACCOUNTS

CUST_ACCOUNT_ID, PARTY_ID
ACCOUNT_NUMBER
ORIG_SYSTEM_REFERENCE , STATUS
CUSTOMER_TYPE
CUSTOMER_CLASS_CODE, PRIMARY_SALESREP_ID, SALES_CHANNEL_CODE,ORDER_TYPE_ID
PRICE_LIST_ID
SUBCATEGORY_CODE, TAX_CODE
FOB_POINT
FREIGHT_TERM
SHIP_PARTIAL
SHIP_VIA
WAREHOUSE_ID
PAYMENT_TERM_ID
TAX_HEADER_LEVEL_FLAG,  TAX_ROUNDING_RULE
CURRENT_BALANCE
ACCOUNT_ACTIVATION_DATE
CREDIT_CLASSIFICATION_CODE

HZ_PARTY_SITES

PARTY_SITE_ID
PARTY_ID
LOCATION_ID
PARTY_SITE_NUMBER
ORIG_SYSTEM_REFERENCE
START_DATE_ACTIVE
REGION ,  MAILSTOP
CUSTOMER_KEY_OSM
IDENTIFYING_ADDRESS_FLAG 

HZ_LOCATIONS

LOCATION_ID
ORIG_SYSTEM_REFERENCE, COUNTRY
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
CITY
POSTAL_CODE
STATE
PROVINCE
COUNTY

HZ_CUST_ACCT_RELATE_ALL

RELATED_CUST_ACCOUNT_ID
CUST_ACCOUNT_ID
RELATIONSHIP_TYPE
COMMENTS
CUSTOMER_RECIPROCAL_FLAG
STATUS
ORG_ID
BILL_TO_FLAG
SHIP_TO_FLAG
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID
PAYMENT_METHOD_LOOKUP_CODE

 

HZ_CUST_ACCT_SITES_ALL

CUST_ACCT_SITE_ID
CUST_ACCOUNT_ID
PARTY_SITE_ID
ORIG_SYSTEM_REFERENCE, STATUS
ORG_ID
BILL_TO_FLAG
MARKET_FLAG

HZ_CUST_SITE_USES_ALL

SITE_USE_ID
CUST_ACCT_SITE_ID
SITE_USE_CODE
PRIMARY_FLAG
STATUS
CONTACT_ID
BILL_TO_SITE_USE_ID
ORIG_SYSTEM_REFERENCE
SIC_CODE
PAYMENT_TERM_ID
GSA_INDICATOR
SHIP_PARTIAL
SHIP_VIA
FOB_POINT
ORDER_TYPE_ID
PRICE_LIST_ID
FREIGHT_TERM
WAREHOUSE_ID
TERRITORY_ID
TAX_CODE, LOCATION

 

HZ_ORGANIZATION_PROFILES

ORGANIZATION_PROFILE_ID
PARTY_ID
ORGANIZATION_NAME
DUNS_NUMBER
ENQUIRY_DUNS
CEO_NAME
CEO_TITLE
PRINCIPAL_NAME
PRINCIPAL_TITLE
LEGAL_STATUS
CONTROL_YR
EMPLOYEES_TOTAL
HQ_BRANCH_IND
BRANCH_FLAG
OOB_IND
LINE_OF_BUSINESS

 

HZ_CONTACT_POINTS

CONTACT_POINT_ID, CONTACT_POINT_TYPE
eg.. EMAIL, WEB, PHONE, STATUS, OWNER_TABLE_NAME eg.. HZ_PARTIES ,HZ_PARTY_SITES,OWNER_TABLE_ID ie.. ID of the above Table, PRIMARY_FLAG
ORIG_SYSTEM_REFERENCE
EDI_TRANSACTION_HANDLING
EDI_ID_NUMBER
EDI_PAYMENT_METHOD
EDI_PAYMENT_FORMAT
EDI_REMITTANCE_METHOD
EDI_REMITTANCE_INSTRUCTION
EDI_TP_HEADER_ID
EDI_ECE_TP_LOCATION_CODE
EMAIL_FORMAT
EMAIL_ADDRESS

HZ_ORG_CONTACT_ROLES

ORG_CONTACT_ROLE_ID
ORG_CONTACT_ID
ROLE_TYPE
CREATED_BY
ROLE_LEVEL
PRIMARY_FLAG
CREATION_DATE
ORIG_SYSTEM_REFERENCE
PRIMARY_CONTACT_PER_ROLE_TYPE
STATUS
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID

HZ_CUST_PROFILE_CLASSES

PROFILE_CLASS_ID
NAME
STATUS
COLLECTOR_ID
CREDIT_ANALYST_ID
CREDIT_CHECKING
TOLERANCE
DISCOUNT_TERMS
DUNNING_LETTERS
INTEREST_CHARGES
PREF_FUNCTIONAL_CURRENCY
STATEMENTS
CREDIT_BALANCE_STATEMENTS
DESCRIPTION
REVIEW_CYCLE_DAYS
OUTSIDE_REPORTING
STANDARD_TERMS
OVERRIDE_TERMS
DUNNING_LETTER_SET_ID

 HZ_ORG_CONTACTS

ORG_CONTACT_ID
PARTY_SITE_ID
PARTY_RELATIONSHIP_ID
TITLE
JOB_TITLE
MAIL_STOP
CONTACT_KEY
COMMENTS
CONTACT_NUMBER
DEPARTMENT_CODE
DEPARTMENT
DECISION_MAKER_FLAG
JOB_TITLE_CODE
MANAGED_BY
REFERENCE_USE_FLAG
RANK
ORIG_SYSTEM_REFERENCE
NATIVE_LANGUAGE
OTHER_LANGUAGE_1
OTHER_LANGUAGE_2
MAILING_ADDRESS_ID
MATCH_GROUP_ID
STATUS
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID

HZ_RELATIONSHIPS

RELATIONSHIP_ID
RELATIONSHIP_TYPE
SUBJECT_ID
SUBJECT_TYPE
SUBJECT_TABLE_NAME
OBJECT_ID
OBJECT_TYPE
OBJECT_TABLE_NAME
PARTY_ID
RELATIONSHIP_CODE
DIRECTIONAL_FLAG
COMMENTS
START_DATE
END_DATE
STATUS
CONTENT_SOURCE_TYPE
OBJECT_VERSION_NUMBER
CREATED_BY_MODULE
APPLICATION_ID
DIRECTION_CODE
PERCENTAGE_OWNERSHIP
ACTUAL_CONTENT_SOURCE

 HZ_CUST_PROFILE_AMTS

CUST_ACCT_PROFILE_AMT_ID
CUST_ACCOUNT_PROFILE_ID
CURRENCY_CODE
TRX_CREDIT_LIMIT
OVERALL_CREDIT_LIMIT
MIN_DUNNING_AMOUNT
MIN_DUNNING_INVOICE_AMOUNT
MAX_INTEREST_CHARGE
MIN_STATEMENT_AMOUNT
AUTO_REC_MIN_RECEIPT_AMOUNT
INTEREST_RATE
MIN_FC_BALANCE_AMOUNT
MIN_FC_INVOICE_AMOUNT
CUST_ACCOUNT_ID
SITE_USE_ID
EXPIRATION_DATE
OBJECT_VERSION_NUMBER

HZ_CUSTOMER_PROFILES

CUST_ACCOUNT_PROFILE_ID
CUST_ACCOUNT_ID
SITE_USE_ID
PROFILE_CLASS_ID
STATUS
COLLECTOR_ID
CREDIT_ANALYST_ID
CREDIT_CHECKING
NEXT_CREDIT_REVIEW_DATE
TOLERANCE
DISCOUNT_TERMS
DUNNING_LETTERS
INTEREST_CHARGES
PREF_FUNCTIONAL_CURRENCY
SEND_STATEMENTS
CREDIT_BALANCE_STATEMENTS
CREDIT_HOLD
CREDIT_RATING
RISK_CODE
STANDARD_TERMS
OVERRIDE_TERMS
DUNNING_LETTER_SET_ID
INTEREST_PERIOD_DAYS
PAYMENT_GRACE_DAYS
DISCOUNT_GRACE_DAYS
STATEMENT_CYCLE_ID 

RA_SALESREPS

SALESREP_ID
SALES_CREDIT_TYPE_ID
NAME
SALESREP_NUMBER
STATUS
START_DATE_ACTIVE
END_DATE_ACTIVE
SET_OF_BOOKS_ID
ORG_ID
EMAIL_ADDRESS
ASSIGNED_TO_USER_ID
COST_CENTER
CHARGE_TO_COST_CENTER , PERSON_ID
TYPE
COMMISSIONABLE_FLAG

AR_LOCATION_COMBINATIONS

LOCATION_ID
LOCATION_STRUCTURE_ID
ENABLED_FLAG
LOCATION_ID_SEGMENT_1
LOCATION_ID_SEGMENT_2
LOCATION_ID_SEGMENT_3
LOCATION_ID_SEGMENT_4
LOCATION_ID_SEGMENT_5
LOCATION_ID_SEGMENT_6
LOCATION_ID_SEGMENT_7
LOCATION_ID_SEGMENT_8
LOCATION_ID_SEGMENT_9
LOCATION_ID_SEGMENT_10

AR_LOCATION_VALUES

LOCATION_SEGMENT_ID
LOCATION_STRUCTURE_ID
LOCATION_SEGMENT_QUALIFIER
LOCATION_SEGMENT_VALUE
LOCATION_SEGMENT_DESCRIPTION
PARENT_SEGMENT_ID
LOCATION_SEGMENT_USER_VALUE
TAX_ACCOUNT_CCID
ORG_ID
INTERIM_TAX_CCID
ADJ_CCID
EDISC_CCID
UNEDISC_CCID

AR_LOCATION_RATES

LOCATION_RATE_ID
LOCATION_SEGMENT_ID
TAX_RATE
OVERRIDE_STRUCTURE_ID
FROM_POSTAL_CODE
TO_POSTAL_CODE
START_DATE
END_DATE
REQUEST_ID
PROGRAM_APPLICATION_ID
PROGRAM_ID
PROGRAM_UPDATE_DATE
LAST_UPDATE_LOGIN
OVERRIDE_RATE1~10

AR_SALES_TAX

SALES_TAX_ID
LOCATION_ID
TAX_RATE
LOCATION1_RATE
LOCATION2_RATE
LOCATION3_RATE, ATTRIBUTE_CATEGORY
ATTRIBUTE1~10
RATE_CONTEXT
ENABLED_FLAG
START_DATE
END_DATE
FROM_POSTAL_CODE
TO_POSTAL_CODE
TAX_ACCOUNT    

AP_INVOICES_ALL                => INVOICE PAYMENT

AP_PAYMENT_SCHEDULES_ALL => INVOICE PAYMENT

AP_INV_SELECTION_CRITERIA_ALL => INSTRUCTIONS AND BATCHES

AP_INVOICE_PAYMENTS_ALL => INVOICE PAYMENT

AP_PAYMENT_DISTRIBUTIONS_ALL

AP_BANK_ACCOUNTS => SUPPLIER AND CUSTOMER BANK ACCOUNTS INFORMATION

AP_BANK_ACCT_USES_ALL     => SUPPLIER AND CUSTOMER BANK ACCOUNTS INFORMATION

AP_BANK_BRANCHES ,AP_BANK_ACCOUNTS => BANK DETAILS IRRESPECTIVE OF SUPPLIER OR INTERNAL BANKS

AP_CHECKS_ALL        => PAYMENTS

AP_CHECK_STOCKS_ALL => PAYMENT DOCUMENTS

AP_CHECK_FORMATS

 

 

AP_SELECTED_INVOICES_ALL

AP_SELECTED_INVOICES_ALL is a temporary table that stores information about invoices selected for payment in a payment batch.Your Oracle Payables application inserts into this table after you initiate a payment batch. There will be one row for each invoice that Payables selects for payment

in the current payment batch. When you build payments in a payment batch, your Oracle Payables application uses information in this table to create rows in AP_SELECTED_INVOICE_CHECKS. Information from this table appears in the Modify Payment Batch window.


AP_SELECTED_INVOICE_CHECKS_ALL

AP_SELECTED_INVOICE_CHECKS_ALL is a temporary table that stores payment information during a payment batch. Your Oracle Payables application inserts into this table when you build payments in a payment batch. There will be one row for each payment issued during the current payment batch.

When you confirm a payment batch, your Oracle Payables application inserts these payments into AP_CHECKS_ALL and creates a payment file.

Within a payment batch, SELECTED_CHECK_ID in this table joins with PRINT_SELECTED_CHECK_ID and PAY_SELECTED_CHECK_ID in AP_SELECTED_INVOICES

to associate a selected invoice with its payment.

 

 

R12 Payables New Tables:

Table Names Usage
CE_BANK_ACCOUNTS Internal Bank  Accounts
CE_PAYMENT_DOCUMENTS Payment  Documents to be used for Printed type Payments
IBY_EXTERNAL_PAYEES_ALL Payee (Trading  Partner Info for paying invoices
IBY_PMT_INSTR_USES_ALL Used for Remit  to Bank Accounts
AP_INVOICE_LINES_ALL Invoice Lines
AP_ALLOCATION_RULES Invoice Lines
AP_INVOICE_LINES_ALL Invoice Lines
AP_ALLOCATION_RULES Invoice Lines
AP_ALLOCATION_RULE_LINES Invoice Lines
AP_ALLOCATION_RULE_LINES_GT Global Temp  Table for Invoice Lines – Allocations
AP_SELF_ASSESSED_TAX_DIST_ALL E-Business Tax
AP_PRODUCT_REGISTRATIONS Invoice Lines
AP_PRODUCT_SETUP Multi Org  Access Control
AP_TOLERANCE_TEMPLATES Support of  complex PO Contracts
AP_NEGOTIATION_HIST Invoice  Requests
AP_NEGOTIATION_HIST Holds  Resolution Workflow

 

Changed Tables

Table Name Feature Area Brief Description of
Change
AP_INVOICES_ALL Invoice Lines,
eBusiness Tax, Payments, SLA, Complex Work
POs…
Numerous  columns were added to this table due to almost all the projects done for R12, for list of columns added please refer to the TDD of the respective projects.
AP_INVOICE_DISTRIBUTIONS_ALL Invoice Lines,
eBusiness Tax…
Number of columns were added to this table due to almost all the projects done for R12, for list of columns added please refer to the TDD of the respective projects.
AP_INV_APRVL_HIST_ALL Holds  resolution workflow Hold_ID column  was added.
AP_CHECKS_ALL Payables  Payments Data Some new columns are added to AP_CHECKS_ALL table for maintaining real time synchronization with Oracle Payments and Cash Managements. Some existing
columns from the same tables have been made obsolete.
AP_HOLDS_ALL Holds  Resolution Workflow New Columns  Added were: Hold_id and wf_status.
AP_HOLD_CODES Holds  Resolution Workflow New Columns  Added were: hold_instruction, wait_before_notify_days, reminder_days
AP_INV_APRVL_HIST_ALL Holds
Resolution and Invoice Approval Workflow
This will be  used to record history for both the Invoice level Approvals and Invoice Line  level approvals. New Columns Added were:line_number, hold_id and history_type
AP_APINV_APPROVERS Invoice
Approval Workflow
New Columns  Added were:child_process_item_key, child_process_item_type, item_type and
item_key.

 

 

Obsolete Tables

Table Name Feature Area Replaced By
AP_BANK_BRANCHES Bank/Bank Branches CE_BANK_BRANCHES_V
AP_BANK_ACCOUNTS_ALL Bank Accounts
including Internal and External
CE_BANK_USES_OU_V/IBY_EXT_BANK_ACCOUNTS_V
AP_BANK_ACCOUNTS_USES_ALL Remit to Bank
Account Uses
AP_CHECK_STOCKS_ALL Payments  Documents CE_PAYMENT_DOCUMENTS
AP_CHECK_FORMATS Payments  Format
AP_TAX_RECVRY_RULES_ALL EBusiness Tax Replaced by relevant EBTax tables/views.
AP_TAX_RECVRY_RATES_ALL EBusiness Tax Replaced by relevant EBTax tables/views.
AP_CHRG_ALLOCATIONS_ALL Invoice Lines
- Allocations
This feature is replaced by distributions itself, distributions itself represent the
allocation of charges.
AP_TAX_DERIVATIONS EBusiness Tax Replaced by relevant EBTax tables/views.
AP_LINE_APRVL_HIST_ALL Invoice  Approval Workflow AP_INV_APRVL_HIST_ALL

 

Purchasing Tables: 

PO_VENDORS

VENDOR_ID
VENDOR_NAME
SUMMARY_FLAG
ENABLED_FLAG
EMPLOYEE_ID
VENDOR_TYPE_LOOKUP_CODE
CUSTOMER_NUM
ONE_TIME_FLAG
PARENT_VENDOR_ID
MIN_ORDER_AMOUNT
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
SHIP_VIA_LOOKUP_CODE
FREIGHT_TERMS_LOOKUP_CODE
FOB_LOOKUP_CODE
TERMS_ID
SET_OF_BOOKS_ID
CREDIT_STATUS_LOOKUP_CODE
CREDIT_LIMIT
ALWAYS_TAKE_DISC_FLAG
PAY_DATE_BASIS_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
PAYMENT_PRIORITY
INVOICE_CURRENCY_CODE
PAYMENT_CURRENCY_CODE
INVOICE_AMOUNT_LIMIT
EXCHANGE_DATE_LOOKUP_CODE
HOLD_ALL_PAYMENTS_FLAG
HOLD_FUTURE_PAYMENTS_FLAG
HOLD_REASON

PO_VENDOR_SITES_ALL   

VENDOR_SITE_ID
VENDOR_ID
VENDOR_SITE_CODE
PURCHASING_SITE_FLAG
RFQ_ONLY_SITE_FLAG
PAY_SITE_FLAG
ATTENTION_AR_FLAG
ADDRESS_LINE1
ADDRESS_LINE2
ADDRESS_LINE3
CITY
STATE
ZIP
PROVINCE
COUNTRY
AREA_CODE
PHONE
CUSTOMER_NUM
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
SHIP_VIA_LOOKUP_CODE
FREIGHT_TERMS_LOOKUP_CODE
FOB_LOOKUP_CODE
INACTIVE_DATE
FAX
FAX_AREA_CODE
TELEX
PAYMENT_METHOD_LOOKUP_CODE

PO_VENDOR_CONTACTS

VENDOR_CONTACT_ID
VENDOR_SITE_ID
INACTIVE_DATE
FIRST_NAME
MIDDLE_NAME
LAST_NAME
PREFIX
TITLE
MAIL_STOP
AREA_CODE
PHONE
DEPARTMENT
EMAIL_ADDRESS
URL
ALT_AREA_CODE
ALT_PHONE
FAX_AREA_CODE
FAX

PO_REQUISITION_HEADERS_ALL

REQUISITION_HEADER_ID
PREPARER_ID
SEGMENT1
SUMMARY_FLAG
ENABLED_FLAG
START_DATE_ACTIVE
END_DATE_ACTIVE
DESCRIPTION
AUTHORIZATION_STATUS
NOTE_TO_AUTHORIZER
TYPE_LOOKUP_CODE
TRANSFERRED_TO_OE_FLAG
ON_LINE_FLAG
PRELIMINARY_RESEARCH_FLAG
RESEARCH_COMPLETE_FLAG
PREPARER_FINISHED_FLAG
PREPARER_FINISHED_DATE
AGENT_RETURN_FLAG
AGENT_RETURN_NOTE
CANCEL_FLAG

PO_REQUISITION_LINES_ALL

REQUISITION_LINE_ID
REQUISITION_HEADER_ID
LINE_NUM
LINE_TYPE_ID
CATEGORY_ID
ITEM_DESCRIPTION
UNIT_MEAS_LOOKUP_CODE
UNIT_PRICE
QUANTITY
DELIVER_TO_LOCATION_ID
TO_PERSON_ID
SOURCE_TYPE_CODE
ITEM_ID
ITEM_REVISION
QUANTITY_DELIVERED
SUGGESTED_BUYER_ID
ENCUMBERED_FLAG
RFQ_REQUIRED_FLAG
NEED_BY_DATE
LINE_LOCATION_ID

PO_REQ_DISTRIBUTIONS_ALL

DISTRIBUTION_ID
REQUISITION_LINE_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
REQ_LINE_AMOUNT
REQ_LINE_QUANTITY
ENCUMBERED_FLAG
GL_ENCUMBERED_DATE
GL_ENCUMBERED_PERIOD_NAME

PO_HEADERS_ALL

PO_HEADER_ID
TYPE_LOOKUP_CODE
SEGMENT1
VENDOR_ORDER_NUM
SUMMARY_FLAG
ENABLED_FLAG
START_DATE_ACTIVE
END_DATE_ACTIVE
VENDOR_ID
VENDOR_SITE_ID
VENDOR_CONTACT_ID
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
AGENT_ID
TERMS_ID
SHIP_VIA_LOOKUP_CODE
FOB_LOOKUP_CODE

PO_LINES_ALL

PO_LINE_ID
PO_HEADER_ID
LINE_TYPE_ID
LINE_NUM
ITEM_ID
ITEM_REVISION
CATEGORY_ID
ITEM_DESCRIPTION
UNIT_MEAS_LOOKUP_CODE, QUANTITY_COMMITTED , COMMITTED_AMOUNT
ALLOW_PRICE_OVERRIDE_FLAG
NOT_TO_EXCEED_PRICE
LIST_PRICE_PER_UNIT
UNIT_PRICE
QUANTITY

PO_DISTRIBUTIONS_ALL

PO_DISTRIBUTION_ID
PO_HEADER_ID
PO_LINE_ID
LINE_LOCATION_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
QUANTITY_ORDERED
PO_RELEASE_ID
QUANTITY_DELIVERED,DESTINATION_TYPE_CODE, DESTINATION_ORGANIZATION_ID, DESTINATION_SUBINVENTORY

PO_LINE_LOCATIONS_ALL

LINE_LOCATION_ID
PO_HEADER_ID
PO_LINE_ID
QUANTITY
SHIP_TO_LOCATION_ID
NEED_BY_DATE
PROMISED_DATE
QUANTITY_RECEIVED
QUANTITY_ACCEPTED
QUANTITY_REJECTED
QUANTITY_BILLED
QUANTITY_CANCELLED
UNIT_MEAS_LOOKUP_CODE
PO_RELEASE_ID

PO_RELEASES_ALL

PO_RELEASE_ID
PO_HEADER_ID
RELEASE_NUM
AGENT_ID
RELEASE_DATE
REVISION_NUM
HOLD_DATE,HOLD_B,Y, ACCEPTANC_REQUIRED_FLAG ,APPROVED_FLAG , APPROVED_DATE
PRINT_COUNT
PRINTED_DATE
ACCEPTANCE_DUE_DATE

RCV_SHIPMENT_HEADERS

SHIPMENT_HEADER_ID
RECEIPT_SOURCE_CODE
VENDOR_ID
VENDOR_SITE_ID
ORGANIZATION_ID
SHIPMENT_NUM
RECEIPT_NUM
SHIP_TO_LOCATION_ID
BILL_OF_LADING
PACKING_SLIP
SHIPPED_DATE
FREIGHT_CARRIER_CODE
EXPECTED_RECEIPT_DATE
EMPLOYEE_ID
NUM_OF_CONTAINERS
WAYBILL_AIRBILL_NUM
COMMENTS

RCV_SHIPMENT_LINES

SHIPMENT_LINE_ID
SHIPMENT_HEADER_ID
LINE_NUM
CATEGORY_ID
QUANTITY_SHIPPED
QUANTITY_RECEIVED
UNIT_OF_MEASURE
ITEM_DESCRIPTION
ITEM_ID
ITEM_REVISION
VENDOR_ITEM_NUM
VENDOR_LOT_NUM
UOM_CONVERSION_RATE
SHIPMENT_LINE_STATUS_CODE
SOURCE_DOCUMENT_CODE
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
REQUISITION_LINE_ID
REQ_DISTRIBUTION_ID

RCV_TRANSACTIONS

TRANSACTION_ID
TRANSACTION_TYPE
TRANSACTION_DATE
QUANTITY
UNIT_OF_MEASURE
SHIPMENT_HEADER_ID
SHIPMENT_LINE_ID
USER_ENTERED_FLAG
INTERFACE_SOURCE_CODE
INTERFACE_SOURCE_LINE_ID
INV_TRANSACTION_ID
SOURCE_DOCUMENT_CODE
DESTINATION_TYPE_CODE
PRIMARY_QUANTITY
PRIMARY_UNIT_OF_MEASURE
UOM_CODE
EMPLOYEE_ID
PARENT_TRANSACTION_ID
PO_HEADER_ID
PO_RELEASE_ID
PO_LINE_ID
PO_LINE_LOCATION_ID
PO_DISTRIBUTION_ID
PO_REVISION_NUM
REQUISITION_LINE_ID

 

HR Tables:

HR_ORGANIZATION_INFORMATION

ORG_INFORMATION_ID
ORGANIZATION_ID
ORG_INFORMATION_CONTEXT
ORG_INFORMATION1 ~ 20 

HR_LEGAL_ENTITIES

ORGANIZATION_ID
BUSINESS_GROUP_ID
NAME
DATE_FROM
DATE_TO
SET_OF_BOOKS_ID
VAT_REGISTRATION_NUMBER

 

HR_ALL_ORGANIZATION_UNITS

ORGANIZATION_ID
NAME
BUSINESS_GROUP_ID
LOCATION_ID
DATE_FROM
INTERNAL_EXTERNAL_FLAG
INTERNAL_ADDRESS_LINE
TYPE 

HR_LOCATIONS_ALL

LOCATION_ID
LOCATION_CODE
ADDRESS_LINE_1
ADDRESS_LINE_2
ADDRESS_LINE_3
COUNTRY
DESCRIPTION
INVENTORY_ORGANIZATION_ID
OFFICE_SITE_FLAG
RECEIVING_SITE_FLAG
SHIP_TO_SITE_FLAG
BILL_TO_SITE_FLAG, SHIP_TO_LOCATION_ID
POSTAL_CODE
STYLE
DESIGNATED_RECEIVER_ID
IN_ORGANIZATION_FLAG
INACTIVE_DATE 

HR_OPERATING_UNITS

ORGANIZATION_ID
BUSINESS_GROUP_ID
NAME
DATE_FROM
DATE_TO
LEGAL_ENTITY_ID
SET_OF_BOOKS_ID 

ORG_ORGANIZATION_DEFINITIONS

ORGANIZATION_ID
BUSINESS_GROUP_ID
USER_DEFINITION_ENABLE_DATE
DISABLE_DATE
ORGANIZATION_CODE
ORGANIZATION_NAME
SET_OF_BOOKS_ID
CHART_OF_ACCOUNTS_ID
INVENTORY_ENABLED_FLAG
OPERATING_UNIT
LEGAL_ENTITY

                            Inventory Tables:

MTL_PARAMETERS

ORGANIZATION_ID
ORGANIZATION_CODE
MASTER_ORGANIZATION_ID
CALENDAR_CODE
DEFAULT_ATP_RULE_ID
DEFAULT_PICKING_RULE_ID
DEFAULT_LOCATOR_ORDER_VALUE
DEFAULT_SUBINV_ORDER_VALUE
NEGATIVE_INV_RECEIPT_CODE
STOCK_LOCATOR_CONTROL_CODE
MATL_INTERORG_TRANSFER_CODE
INTERORG_TRNSFR_CHARGE_PERCENT
SOURCE_ORGANIZATION_ID
SOURCE_SUBINVENTORY
SOURCE_TYPE
SERIAL_NUMBER_TYPE
AUTO_SERIAL_ALPHA_PREFIX
START_AUTO_SERIAL_NUMBER
AUTO_LOT_ALPHA_PREFIX
LOT_NUMBER_UNIQUENESS

MTL_SECONDARY_INVENTORIES

SECONDARY_INVENTORY_NAME
ORGANIZATION_ID
DESCRIPTION
SUBINVENTORY_TYPE
ASSET_INVENTORY
QUANTITY_TRACKED
INVENTORY_ATP_CODE
AVAILABILITY_TYPE
RESERVABLE_TYPE
LOCATOR_TYPE
PICKING_ORDER
DROPPING_ORDER
LOCATION_ID
STATUS_ID
DEFAULT_LOC_STATUS_ID
LPN_CONTROLLED_FLAG
PICK_METHODOLOGY
CARTONIZATION_FLAG
PREPROCESSING_LEAD_TIME
PROCESSING_LEAD_TIME
POSTPROCESSING_LEAD_TIME
SOURCE_TYPE
SOURCE_SUBINVENTORY
SOURCE_ORGANIZATION_ID
DEFAULT_COST_GROUP_ID
DEFAULT_COUNT_TYPE_CODE 

MTL_ITEM_SUB_INVENTORIES 

INVENTORY_ITEM_ID
ORGANIZATION_ID
SECONDARY_INVENTORY
PRIMARY_SUBINVENTORY_FLAG
PICKING_ORDER
MIN_MINMAX_QUANTITY
MAX_MINMAX_QUANTITY
INVENTORY_PLANNING_CODE
FIXED_LOT_MULTIPLE
MINIMUM_ORDER_QUANTITY
MAXIMUM_ORDER_QUANTITY
SOURCE_TYPE
SOURCE_ORGANIZATION_ID
SOURCE_SUBINVENTORY
ENCUMBRANCE_ACCOUNT
PREPROCESSING_LEAD_TIME
PROCESSING_LEAD_TIME
POSTPROCESSING_LEAD_TIME 

MTL_ITEM_LOCATIONS

INVENTORY_LOCATION_ID
ORGANIZATION_ID
SUBINVENTORY_CODE
DESCRIPTION
PHYSICAL_LOCATION_ID
PICK_UOM_CODE
DIMENSION_UOM_CODE
LENGTH
WIDTH
HEIGHT
LOCATOR_STATUS
STATUS_ID
INVENTORY_LOCATION_TYPE

FND Tables

 

FND_FLEX_VALUE_NORM_HIERARCHY

It stores information about multi level value hierarchies for independent and dependent value sets. Each row includes a value set name, a parent value, a flag to distinguish a child value from a parent value (RANGE_ATTRIBUTE), a low value for the range of child values, and a high value for the range of child values. Oracle Application Object Library uses this information to support multilevel hierarchy values.

The below tables are used to store the descriptive flexfield definition

 FND_DESCRIPTIVE_FLEXS

It stores setup information about descriptive flexfields. Each row includes the name of the table that contains the descriptive flexfield columns, the name and title of the flexfield, the identifier of the application with which the flexfield is registered, whether the flexfield is currently frozen, whether this is a protected descriptive flexfield, the name of the structure defining column for the flexfield (CONTEXT_COLUMN_NAME), and other information about how the flexfield is defined. You need one row for each descriptive flexfield in each application. Oracle Application Object Library uses this information to generate a compiled definition for a descriptive flexfield.

FND_DESCR_FLEX_COLUMN_USAGES

It stores the correspondences between application table columns and the descriptive flexfield segments. Each row includes an application identifier, the name of a descriptive flexfield, and a column name. The context field value, also known as the structure name, is in DESCRIPTIVE_FLEX_CONTEXT_CODE.

Each row also includes the segment name (END_USER_COLUMN_NAME), the display information about the segment such as prompts, display size, type of default value, whether the segment is required or not, whether the segment is part of a high, low segment pair, whether security is enabled for the segment, whether to display the segment or not, and the value set the segment uses. You need one row for each segment for each context value (structure), including global data element segments, for each descriptive flexfield of each application. Oracle Application Object Library uses this information to create a compiled descriptive flexfield definition to store in the FND_COMPILED_DESCRIPTIVE_FLEXS table

FND_CONCURRENT_PROGRAMS 

It stores information about concurrent programs. Each row includes a name and description of the concurrent program. Each row also includes the execution methods for the program (EXECUTION_METHOD_CODE), the argument method (ARGUMENT_METHOD_CODE), and whether the program is constrained (QUEUE_METHOD_CODE).

If the program is a special concurrent program that controls the concurrent managers, QUEUE_CONTROL_FLAG is set to Y. Each row also includes flags that indicate whether the program is enabled and defined as run alone, as well as values that specify the print style the concurrent manager should use to print program output, if any.

There are also values that identify the executable associated with the concurrent program and the application with which the executable is defined, and flags that specify whether the concurrent program is a parent of a report set, whether to save the output file, and whether a print style is required.

Information such as printer name and number of rows and columns on each page of the output file for the concurrent program is also included in the table. You need one row for each concurrent program in each application. Oracle Application Object Library uses this information to run concurrent programs FND_CONCURRENT_PROCESSES

It stores information about concurrent managers. Each row includes values that identify the ORACLE process, the operating system process, and the concurrent manager (QUEUE_APPLICATION_ID and CONCURRENT_QUEUE_ID). You need one row for each instance of a running concurrent manager (each process), as well as one row for the Internal Concurrent Manager.

Oracle Application Object Library uses this table to keep a history of concurrent managers. You should never update this table manually. You can use the Purge Concurrent Request and/or Managers Data program to delete history information periodically.

FND_EXECUTABLES 

It stores information about concurrent program executables. Each row includes an application identifier, an executable identifier, an executable name, and the name of the physical executable file as it resides on the operating system. The execution method associated with the executable identifies the tool that is needed to run the executable.

A subroutine name is only applicable to immediate concurrent programs and spawned concurrent programs that can be run either as spawned or immediate concurrent program. You need one row for each executable that you are going to submit as a concurrent program. Oracle Application Object Library uses this information to process concurrent requests

FND_FLEX_VALUE_SETS 

It stores information about the value sets used by both key and descriptive flexfields. Each row includes the application identifier, the name and description of the value set, the validation type of value set (F for Table, I for Independent, D for Dependent, N for None, P for Pair, U for Special), the data format type,the maximum and minimum values and precision for number format type value set.

Each row also contains flags that determine what size values can be in this value set, and whether flexfield value security and LOVs LongList feature are enabled for this value set.

NUMERIC_MODE_ENABLED_FLAG indicates whether Oracle Application Object Library should right–justify and zero–fill values that contain only the characters 0 through 9; it does not indicate that values in this value set are of type NUMBER. MAXIMUM_VALUE and MINIMUM_VALUE together do range checks on values.

If the value set is a dependent value set, PARENT_FLEX_VALUE_SET_ID identifies the independent value set the current dependent value set depends upon.Also if the value set is a dependent value set, DEPENDANT_DEFAULT_VALUE and DEPENDANT_DEFAULT_MEANING contain the default value and description that Oracle Application Object Library should automatically create in the dependent value set whenever you create a new value in the independent value set it depends upon. You need one row for each value set you have for your flexfields.

Oracle Application Object Library uses this information to assign groups of valid values to flexfield segments 

FND_FLEX_VALUE_HIERARCHIES 

It stores information about child value ranges for key flexfield segment values. Each row includes an identification of the parent value the range belongs to, as well as the low and high values that make up the range of child values.
FLEX_VALUE_SET_ID identifies the value set to which the parent value belongs. You need one row for each range of child values (you can have more than one row for each parent value). Oracle Application Object Library provides this information for applications reporting purposes.

Attribute columns on this inventory table are used as additional information columns known as Descriptive flexfields. The reason they do not have any specific column name is because each implementation of Oracle Apps can customize as to what is stored in this DFF.

To find out what is stored in these attribute columns…

1. find out the different dff defined on this table, how?

Easy way is to just query the fnd_descriptive_flexs_vl view and in the base_table column provide the desired table you wish to query for. Once I find run the query I get the below table that lists all the defined DFF on this table

Flex Title Code base
Items MTL_SYSTEM_ITEMS MTL_SYSTEM_ITEMS_B
Item Order Attributes BOM_ITEM_ORDER_ATTRIBUTES MTL_SYSTEM_ITEMS_B
Item Shipping Attributes BOM_ITEM_SHIPPING_ATTRIBUTES MTL_SYSTEM_ITEMS_B
JG_MTL_SYSTEM_ITEMS JG_MTL_SYSTEM_ITEMS MTL_SYSTEM_ITEMS_B
Master Items EGO_MASTER_ITEMS MTL_SYSTEM_ITEMS_B

2. Once you know the DFF title, go to

System Administrator -> Application -> FlexField -> Descriptive-> Segments

Query the form and enter the Flex title value from the above table and enter in the title field of this form.
Click on the Segments and there you see all the End user column names and the attribute associations.
But there is another easier way to find out the attribute and dff column names associations.
You can query the view fnd_descr_flex_col_usage_vl as demonstrated in the below query.

SELECT
dfc.end_user_column_name user_column_name,
dfc.column_seq_num column_sequence,
dfc.application_column_name table_column_name,
dfc.flex_value_set_id,
dff.application_table_name base_table,
dff.descriptive_flexfield_name flex_code,
dff.title flex_name
FROM fnd_descr_flex_col_usage_vl dfc,
fnd_descriptive_flexs_vl dff
WHERE dff.descriptive_flexfield_name = dfc.descriptive_flexfield_name
and dff.descriptive_flexfield_name =

I get the below list of columns and attributes

User Column Name Column Sequence Table Column Name
Drop Shipment 10 ATTRIBUTE10
Country of Origin 10 ATTRIBUTE1
Invoice UOM 15 ATTRIBUTE15
Harmonized Tarriff Code 20 ATTRIBUTE2
Business Class (y/n) 30 ATTRIBUTE3
Mac_ID Required 40 ATTRIBUTE4
Parent Item for DP 50 ATTRIBUTE14
ECCN 60 ATTRIBUTE9
CCATS 70 ATTRIBUTE12
Version 80 ATTRIBUTE11
Legacy Part Number 90 ATTRIBUTE6
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: