HZ_ORGANIZATION_PROFILES
HZ_ORGANIZATION_PROFILES stores credit
rating, financial statistics, socioeconomic and corporate linkage information
for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.
HZ_PARTIES
HZ_PARTIES stores information about parties such as
organizations, people, and groups, including the identifying address
information for the party.
HZ_PERSON_PROFILES
HZ_PERSON_PROFILES stores detail information about
people.
HZ_PARTY_SITES
HZ_PARTY_SITES stores information about the
relationship between Parties and Locations. The same party can have multiple
party sites. Physical addresses are stored in HZ_LOCATIONS.
HZ_ORG_CONTACTS
HZ_ORG_CONTACTS stores information about people as
contacts for organizations or other people. The table includes information
about the person in the context of the organization, such as job title, or as
associated with another person, such as manager.
HZ_CONTACT_POINTS
HZ_CONTACT_POINTS stores electronic methods of
communicating with entities such as parties and party sites. Each record in
this table represents a different means of contacting an entity.
HZ_LOCATIONS
HZ_LOCATIONS stores information about physical
locations.
AP_BANK_ACCOUNT_USES_ALL
AP_BANK_ACCOUNT_USES_ALL stores information for the
internal and external bank accounts you define in Oracle Payables and Oracle
Receivables applications.
HZ_CUSTOMER_PROFILES
HZ_CUSTOMER_PROFILES stores credit information for
customer accounts and customer account sites.
HZ_CUST_CONTACT_POINTS
HZ_CUST_CONTACT_POINTS stores customer contact
point information.
RA_CONTACT_PHONES_INT_ALL
This is one of the Oracle Receivables Customer
Interface tables used to import contact and telephone information for your
customers’ addresses and business purposes. Oracle Receivables uses this
information to create records in RA_CONTACTS and RA_PHONES. You enter one row
for each contact or telephone number. Oracle
Receivables deletes all information from this table after successfully
completing your data conversion. For more information on customer conversion,
please refer to the Oracle Receivables User’s Guide. INSERT_UPDATE_FLAG stores
’I’ if your are inserting a new record or ’U’ if you are updating an existing
record. VALIDATED_FLAG stores ’Y’ for Yes and ’N’ for No to indicate if this
record has been validated.
RA_CUST_RECEIPT_METHODS
This table has a row for each Payment Method that
is assigned to a customer and/or customer site.
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNTS stores information about customer
relationships. If a party becomes a customer, information about the customer
account is stored in this table. You can establish multiple customer
relationships with a single party, so each party can have multiple customer
account records in this table.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_ACCT_SITES_ALL stores information about
customer sites. One customer account can have multiple sites. The address is
maintained in HZ_LOCATIONS.
HZ_CUST_SITE_USES_ALL
HZ_CUST_SITE_USES_ALL stores information about site
uses or business purposes. A single customer site can have multiple site uses,
such as bill to or ship to, and each site use is stored as a record in this
table.
RA_CUST_PAY_METHOD_INT_ALL
This table contains Customer Interface data for
Payment Methods. Each row represents one Payment Method that will be assigned
to a customer.
RA_CUSTOMERS_INTERFACE_ALL
This is one of the Oracle Receivables Customer
Interface tables used to import customer, address, customer profiles, and site
use information. Oracle Receivables uses this information to create records in
RA_CUSTOMERS, RA_ADDRESSES_ALL,
RA_CUSTOMER_RELATIONSHIPS_ALL and
RA_SITE_USES_ALL. Oracle Receivables deletes all information from this table
after successfully importing the customer data. For more information on
customer conversion, please refer to the Oracle Receivables User’s Guide.
INTERFACE_STATUS is used by Customer Interface to store any error messages that
apply to the interface record. Please refer to the Oracle Applications Message
Reference Manual for a list of Customer Interface error messages. REQUEST_ID
stores the request identifier when you run Customer Interface.
INSERT_UPDATE_FLAG INSERT_UPDATE_FLAG stores ’I’ if you are inserting a new row
or ’U’ if you are updating an existing record. VALIDATED_FLAG stores ’Y’ for
Yes and ’N’ for No to indicate if this record has been validated. The primary
key for this table consists of three columns, ORIG_SYSTEM_CUSTOMER_REF,
ORIG_SYSTEM_ADDRESS_REF, and SITE_USE_CODE.
RA_CUSTOMER_PROFILES_INT_ALL
This table is used by the Customer Interface
program to store customer profile information. If you are entering a new
customer, you must either pass a customer profile class that already exists in
Oracle Receivables or pass customer profile values. You do not have to enter
values in this table if you are not entering a new customer or assigning
customer profile information to customer addresses.
Customer
AP_BANK_BRANCHES
AP_BANK_BRANCHES contains information about the
bank branches you define when you set up your banks. You need one row for each
bank branch you use. One bank branch may have multiple bank accounts. This
table corresponds to the Bank Branch region of the Banks window.
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNTS_ALL contains information about
your bank accounts. You need one row for each bank account you define. Each
bank account must be affiliated with one bank branch. When you initiate an
automatic payment batch, enter a manual check, or create a Quick payment, you
can select a bank account that you define in this table.
This table corresponds to the Bank Accounts window.
AP_BANK_ACCOUNT_USES_ALL
AP_BANK_ACCOUNT_USES_ALL stores information for the
internal and external bank accounts you define in Oracle Payables and Oracle
Receivables applications.
AR_CREDIT_HISTORIES
This table stores information about a customer’s
credit profile (changes made to AR_CUSTOMER_PROFILES). Each row can include
changes to a customer’s credit status, credit limit, and outstanding balances.
The primary key for this table is CREDIT_HISTORY_ID.
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNTS stores information about customer
relationships. If a party becomes a customer, information about the customer
account is stored in this table. You can establish multiple customer
relationships with a single party, so each party can have multiple customer
account records in this table.
HZ_CUST_ACCT_RELATE_ALL
HZ_CUST_ACCT_RELATE_ALL stores information about
relationships between customer accounts. A flag indicates whether a
relationship is reciprocal.
HZ_PARTIES
HZ_PARTIES stores information about parties such as
organizations, people, and groups, including the identifying address
information for the party.
HZ_PARTY_RELATIONSHIPS
HZ_PARTY_RELATIONSHIPS stores the relationships
between parties. A flag indicates whether the relationship is directional.
HZ_ORG_CONTACTS
HZ_ORG_CONTACTS stores information about people as
contacts for organizations or other people. The table includes information
about the person in the context of the organization, such as job title, or as
associated with another person, such as manager.
HZ_SUSPENSION_ACTIVITY
HZ_SUSPENSION_ACTIVITY stores information about the
date as of which that service is no longer provided to a customer account or
customer account site.
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCOUNT_ROLES stores information about the
roles that parties perform in customer accounts.
HZ_ROLE_RESPONSIBILITY
HZ_ROLE_RESPONSIBILITY stores responsibilities for
parties related to the roles that the parties play in an account.
HZ_CUST_CONTACT_POINTS
HZ_CUST_CONTACT_POINTS stores customer contact
point information.
HZ_CONTACT_POINTS
HZ_CONTACT_POINTS stores electronic methods of
communicating with entities such as parties and party sites. Each record in
this table represents a different means of contacting an entity.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_ACCT_SITES_ALL stores information about
customer sites. One customer account can have multiple sites. The address is
maintained in HZ_LOCATIONS.
HZ_CUST_SITE_USES_ALL
HZ_CUST_SITE_USES_ALL stores information about site
uses or business purposes. A single customer site can have multiple site uses,
such as bill to or ship to, and each site use is stored as a record in this
table.
HZ_BILLING_PREFERENCES
HZ_BILLING_PREFERENCES describes the invoicing
format preferred by customer accounts or customer account sites.
RA_TAX_EXEMPTIONS_ALL
This table stores tax exemptions for either
customers and sites or items. Each tax exemption is for a particular tax code
and a particular percentage of exemption. For example, a customer site can be
100% exempt from a particular tax code.
HZ_CUST_PROF_CLASS_AMTS
HZ_CUST_PROF_CLASS_AMTS stores customer profile
class amount limits for each currency.
HZ_CUST_PROFILE_CLASSES
HZ_CUST_PROFILE_CLASSES stores standard credit
profile classes. You can assign a credit profile class to a customer account so
that the profile class information defaults for your customer account. You use
profile classes to determine a customer account’s payment terms, grouping
rules, dunning letter sets, statement cycles, and AutoCash rule sets.
HZ_CUSTOMER_PROFILES
HZ_CUSTOMER_PROFILES stores credit information for
customer accounts and customer account sites.
HZ_CUST_PROFILE_AMTS
HZ_CUST_PROFILE_AMTS stores profile amount limits
for every currency defined for a customer account or customer account site
profile. For each currency, you can define the currency rates and limits,
including Minimum Invoice Balance for Finance Charges, Minimum Dunning Amount,
and Credit Limit.
Customer Usage
AR_PAYMENT_SCHEDULES_ALL
This table stores all transactions except
adjustments and miscellaneous cash receipts. Oracle Receivables updates this
table when activity occurs against an invoice, debit memo, chargeback, credit
memo, on account credit, or receipt. Oracle Receivables groups different
transactions by the column CLASS. These classes include invoice (INV), debit
memos (DM), guarantees (GUAR), credit memos (CM), deposits (DEP), chargebacks
(CB), and receipts (PMT). Transaction classes determine which columns in this
table Oracle Receivables updates when a
transaction occurs, and whether a transaction
relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL
table. AR_PAYMENT_SCHEDULES_ALL joins to the
RA_CUSTOMER_TRX_ALL table for non–payment
transaction entries such as the creation of credit memos, debit memos,
invoices, chargebacks, or deposits. AR_PAYMENT_SCHEDULES_ALL uses the foreign
key CUSTOMER_TRX_ID to join to the
RA_CUSTOMER_TRX_ALL table for these transactions.
AR_PAYMENT_SCHEDULES_ALL joins to the AR_CASH_RECEIPTS_ALL table for
invoice–related payment transactions using the foreign key CASH_RECEIPT_ID.
When a receipt is applied, Oracle Receivables updates AMOUNT_APPLIED, STATUS
and AMOUNT_DUE_REMAINING. STATUS changes from ’OP’ to ’CL’ for any transaction
that has an AMOUNT_DUE_REMAINING value of 0. ACTUAL_DATE_CLOSED and
GL_DATE_CLOSED are populated with the date of the latest transaction. For a
receipt, the amount due remaining includes on account and unapplied amounts.
Oracle Receivables stores debit items such as invoices, debit memos,
chargebacks, deposits, and guarantees as positive numbers in the
AMOUNT_DUE_REMAINING and AMOUNT_DUE_ORIGINAL columns. Credit items such as
credit memos and receipts are stored as negative numbers. In Release 10,
receipts can be confirmed or not confirmed as designated by the CONFIRMED_FLAG
column. The sum of the AMOUNT_DUE_REMAINING column for a customer for all
confirmed payment schedules reflects the current customer balance. If this
amount is negative, then this column indicates the credit balance amount
currently available for this customer. For invoices with split terms, one
record is created in RA_CUSTOMER_TRX_ALL and one record is stored in
AR_PAYMENT_SCHEDULES_ALL for each installment. In AR_PAYMENT_SCHEDULES_ALL,
DUE_DATE and AMOUNT_DUE_REMAINING can differ for each installment of a split
term invoice. Each installment is differentiated by the TERMS_SEQUENCE_NUMBER
column. If you create a debit memo reversal when you reverse a receipt, Oracle
Receivables creates a new payment schedule record for the debit memo and fills
in REVERSED_CASH_RECEIPT_ID with the CASH_RECEIPT_ID of the receipt that was
reversed. Oracle Receivables creates a new payment schedule record when you
create a chargeback in the Receipts window. ASSOCIATED_CASH_RECEIPT_ID is the
cash receipt of the payment you entered when you created the chargeback in this
window. GL_DATE_CLOSED indicates the general ledger date on which your
transaction was closed. This column identifies which transactions Oracle
Receivables selects when it displays current and overdue debit items in the
aging reports. The aging reports also utilize the current balances in
AMOUNT_DUE_REMAINING to display outstanding amounts for current and overdue
debit items. ACTUAL_DATE_CLOSED gives the date on which you applied a payment
or credit to an open transaction that set AMOUNT_DUE_REMAINING to 0 for that
transaction. Oracle Receivables uses ACTUAL_DATE_CLOSED to determine which
transactions to include when you print statements. The primary key for this
table is PAYMENT_SCHEDULE_ID, which identifies the transaction that created the
row.
AR_CORR_PAY_SCHED_ALL
This table stores one record for each invoice
selected for dunning by Oracle Receivables. Each row includes invoice and
correspondence information. Oracle Receivables uses this information to store
which customer invoices were dunned. Detailed information about a customer’s
dunning letter can be found in AR_CORRESPONDENCES_ALL. The primary key for this
table is CORRESPONDENCE_PAY_SCHED_ID.
AR_CORRESPONDENCES_ALL
This table stores one record for each dunning
letter you send to a customer. Each row includes dunning letter information,
date, customer, and site use. Oracle Receivables stores detailed data for your
dunning letter in AR_DUNNING_LETTERS. Oracle Receivables uses this information
to store which letter was sent to your customer on a specific date. The primary
key for this table is
CORRESPONDENCE_ID.
AR_CUSTOMER_CALL_TOPICS_ALL
This table stores information about the topic of
customer calls, such as the outcome of the call, the customer’s response, and
the follow–up date. Each row includes specific collection information regarding
your call topic. Oracle Receivables uses this information to review customer
calls in the Record A Call, Call History, and
Collection reports. The primary key for this table is CUSTOMER_CALL_TOPIC_ID.
RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commitment,
and credit memo header information. Each row includes general invoice
information such as customer, transaction type, and printing instructions. You
need one row for each invoice, debit memo, commitment, and credit memo you
create in Oracle Receivables. Invoices, debit memos, credit memos, and
commitments are all distinguished by their transaction types stored in
RA_CUST_TRX_TYPES_ALL. If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID
stores the customer transaction identifier of the invoice you credited. In the
case of on account credits, which are not related to any invoice at creation,
PREVIOUS_CUSTOMER_TRX_ID is null. If you created an invoice against a
commitment, Oracle Receivables stores the customer transaction identifier of
the commitment in
INITIAL_CUSTOMER_TRX_ID, otherwise it is null.
COMPLETE_FLAG stores ’Y’ for Yes and ’N’ for No to indicate if your invoice is
complete. When you complete an invoice, Oracle Receivables
creates your payment schedules and updates any
commitments against this invoice. Before an invoice can be completed, it must
have at least one invoice line, revenue records must exist for each line and
add up to the line amount, and a sales tax record must exist for each line.
SOLD_TO_CUSTOMER_ID, SOLD_TO_SITE_USE_ID, BILL_TO_CUSTOMER_ID,
BILL_TO_SITE_USE_ID,SHIP_TO_SITE_USE_ID, PRINTING_OPTION, PRINTING_PENDING,
TERM_ID, REMIT_TO_ADDRESS_ID, PRIMARY_SALES_REP_ID,
and INVOICE_CURRENCY_CODE are required even though they are null allowed. The
primary key for this table is CUSTOMER_TRX_ID.
AR_CALL_ACTIONS
This table stores information about each call
action you enter in the Customer Calls window. Each row includes the action and
amount of a call action. Oracle Receivables stores the rest of the call action
information in other tables. AR_NOTES stores information you enter in the Call
Action MemoPad, while AR_ACTION_NOTIFICATIONS links CALL_ACTION_ID with
employee name. You need one row for each call action you enter. Oracle
Receivables uses this information to let collectors know what actions to take
against customer accounts. This information is visible on–line in the Scheduler
window and in the Call Actions Report. The primary key for this table is
CALL_ACTION_ID.
AR_CUSTOMER_CALLS_ALL
This table stores information about customers calls
including the bill–to site, promise date, and reason for the call. Each row
includes information about contacts and response to the call. You can review
customer calls in the Customer Calls window. The primary key for this table is
CUSTOMER_CALL_ID.
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNTS stores information about customer
relationships. If a party becomes a customer, information about the customer
account is stored in this table. You can establish multiple customer
relationships with a single party, so each party can have multiple customer
account records in this table.
AR_RECEIPT_METHODS
This table stores information about Payment
Methods, receipt attributes that you define and assign to Receipt Classes to
account for receipts and their applications. For automatically created
receipts, a Payment Method defines the rules for creating these receipts. For
manually created receipts, a Payment Method defines a user–definable type for
the receipt. Each Payment Method is associated with a set of bank accounts,
which forms the set of bank accounts you can assign to your receipt. For
example, if you normally receive Lockbox transmissions from bank ABC and bank
DEF, you might create a Payment Method called LOCKBOX and assign bank accounts
from bank ABC and bank DEF to this Payment Method.
HZ_CONTACT_POINTS
HZ_CONTACT_POINTS stores electronic methods of
communicating with entities such as parties and party sites. Each record in
this table represents a different means of contacting an entity.
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCOUNT_ROLES stores information about the
roles that parties perform in customer accounts.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_ACCT_SITES_ALL stores information about
customer sites. One customer account can have multiple sites. The address is
maintained in HZ_LOCATIONS.
RA_CUST_RECEIPT_METHODS
This table has a row for each Payment Method that
is assigned to a customer and/or customer site.
HZ_CUST_CONTACT_POINTS
HZ_CUST_CONTACT_POINTS stores customer contact
point information.
HZ_CUST_SITE_USES_ALL
HZ_CUST_SITE_USES_ALL stores information about site
uses or business purposes. A single customer site can have multiple site uses,
such as bill to or ship to, and each site use is stored as a record in this
table.
RA_REMIT_TOS_ALL
This table stores information to link a remit–to
address with a state and country. You need one row for each country and state
combination that you want to associate with a remit to address. Oracle
Receivables uses this information to default your remit–to address during
invoice and commitment entry. COUNTRY is required even though it is null
allowed.
RA_TERRITORIES
This table stores territory information that is
assigned to salespersons in the RA_SALESREP_TERRITORIES table.
AR_CONS_INV_ALL
This table stores information about a consolidated
billing invoice. A consolidated billing invoice is a bill you can send to a
customer that contains all invoices created during a period that you define
(for example, one month).
Transaction
AR_VAT_TAX_ALL_B
This table contains tax codes that are defined in
the Tax Codes and Rates window. Each row represents a tax code and a tax rate
valid for the period between the START_DATE and the END_DATE.
RA_CUST_TRX_TYPES_ALL
This table stores information about each
transaction type used for invoices, commitments and credit memos. Each row
includes AutoAccounting information as well as standard defaults for the
resulting invoices. POST_TO_GL stores Y for Yes and N for No to indicate
whether this transaction can post to
your general ledger. ACCOUNTING_AFFECT_FLAG stores Y for Yes and N for No to
indicate whether this transaction can update your open receivables balances. If
this is Y, you can see this transactions in your agings. TYPE contains INV for
invoices, CM for credit memos, DM for debit memos, DEP for deposits, and GUAR
for guarantees. If AutoAccounting is based on transaction type, GL_ID_REV,
GL_ID_FREIGHT, and GL_ID_REC stores the default revenue, freight, and
receivables accounts. STATUS and CREDIT_MEMO_TYPE_ID are required even though
they are null allowed.
The primary key for this table is CUST_TRX_TYPE_ID.
RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commitment,
and credit memo header information. Each row includes general invoice
information such as customer, transaction type, and printing instructions. You
need one row for each invoice, debit memo, commitment, and credit memo you
create in Oracle Receivables. Invoices, debit memos, credit memos, and
commitments are all distinguished by their transaction types stored in
RA_CUST_TRX_TYPES_ALL. If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID
stores the customer transaction identifier of the invoice you credited. In the
case of on account credits, which are not related to any invoice at creation,
PREVIOUS_CUSTOMER_TRX_ID is null. If you created an invoice against a commitment,
Oracle Receivables stores the customer transaction identifier of the commitment
in INITIAL_CUSTOMER_TRX_ID, otherwise it is null. COMPLETE_FLAG stores ’Y’ for
Yes and ’N’ for No to indicate if your invoice is complete. When you complete
an invoice, Oracle Receivables creates your payment schedules and updates any
commitments against this invoice. Before an invoice can be completed, it must
have at least one invoice line, revenue records must exist for each line and
add up to the line amount, and a sales tax record must exist for each line.
SOLD_TO_CUSTOMER_ID, SOLD_TO_SITE_USE_ID, BILL_TO_CUSTOMER_ID,
BILL_TO_SITE_USE_ID, SHIP_TO_SITE_USE_ID, PRINTING_OPTION, PRINTING_PENDING,
TERM_ID, REMIT_TO_ADDRESS_ID, PRIMARY_SALES_REP_ID, and INVOICE_CURRENCY_CODE are
required even though they are null allowed.
The primary key for this table is CUSTOMER_TRX_ID.
RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit
memo, credit memo, and commitment lines. For example, an invoice can have one line
for Product A and another line for Product B. You need one row for each line.
Invoice, debit memo, credit memo, and commitment lines are distinguished by the
transaction type of the corresponding RA_CUSTOMER_TRX_ALL record. Also, credit
memos are required to have a value in PREVIOUS_CUSTOMER_TRX_LINE_ID, except on
account credits which are not related to specific invoices/invoice lines at
creation time, will not have values in this column. QUANTITY_ORDERED stores the
amount of product ordered. QUANTITY_INVOICED stores the amount of product
invoiced. For invoices entered through the window, QUANTITY_ORDERED and
QUANTITY_INVOICED must be the same. For invoices imported through AutoInvoice,
QUANTITY_ORDERED and QUANTITY_INVOICED can be different. If you enter a credit
memo, QUANTITY_CREDITED stores the amount of product credited. UOM_CODE stores
the unit of measure code as defined in MTL_UNITS_OF_MEASURE.
UNIT_STANDARD_PRICE stores the list price per unit for this transaction line.
UNIT_SELLING_PRICE stores the selling price per unit for this transaction line.
For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and
UNIT_SELLING_PRICE can be different. DESCRIPTION, TAXING_RULE, QUANTITY_ORDERED, UNIT_STANDARD_PRICE,
UOM_CODE, and UNIT_SELLING_PRICE are required even though they are null
allowed. LINE_TYPE differentiates between the different types of lines that are
stored in this table. LINE points to regular invoice lines that normally refer
to an item. TAX signifies that this is a tax line. The column
LINK_TO_CUST_TRX_LINE_ID references another row in this table that is the
invoice line associated with the row of type TAX. FREIGHT works the same way as
TAX but there you can have at most one FREIGHT type line per invoice line of
type LINE. You can also have one line of type FREIGHT that has a null
LINK_TO_CUST_TRX_LINE_ID (and this is referred to as header level freight).
CHARGES works just like the LINE type. A line_type of ’CB’ is created for a
Chargeback line. For every row in this table that belongs to a complete
transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y), there must be at least
one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting
information), even for non–postable transactions.
The primary key for this table is
CUSTOMER_TRX_LINE_ID.
RA_CUST_TRX_LINE_SALESREPS_ALL
This table stores sales credit assignments for
invoice lines. If you base your invoice distributions on sales credits, then
there is a mapping between the sales credit assignments in this table with RA_CUST_TRX_LINE_GL_DIST_ALL.
RA_CUST_TRX_LINE_GL_DIST_ALL
This table
stores the accounting records for revenue, unearned revenue and unbilled
receivables for each invoice or credit memo line. Each row includes the GL
account and the amount of the accounting entry. The AMOUNT column in this table
is required even though it is null allowed. You need one row for each
accounting distribution. You must have at least one (but you can have multiple)
accounting distributions for each invoice or credit memo line. Oracle
Receivables uses this information to post the proper amounts to your general
ledger. If your invoice or credit memo has a transaction type where Post to GL
is set to No, Oracle Receivables assigns Null to GL_DATE. If your
AutoAccounting is unable to complete your general ledger default accounts using
the AutoAccounting rules you define, incomplete general ledger accounts are
stored in CONCATENATED_SEGMENTS. If you are importing a transaction through
AutoInvoice and the general ledger date of your transaction is in a closed
accounting period, AutoInvoice uses the general ledger date of the first open
accounting period and stores the original general ledger date in
ORIGINAL_GL_DATE. ACCOUNT_CLASS defines which type of distribution row you are
on. The ACCOUNT_CLASS REC represents the receivable account and is for the
total amount of the invoice. There can be at most two REC rows. One that has a
ACCOUNT_SET_FLAG set to Y and the other has ACCOUNT_SET_FLAG set to N. Use
LATEST_REC_FLAG to join to the later of the two rows. ACCOUNT_SET_FLAG is Y if
this row is part of an account set. An account set is a set of rows that
represent a model distribution. Account sets are used for invoices with rules.
The rows represent how the actual distribution rows should be created and what
percentage of the actual distribution should be allocated to each account. For
invoices with rules, the distributions are not created when the invoice is
initially created. Instead, the invoices are created when the Revenue
Recognition program is run.
The primary key
for this table is CUST_TRX_LINE_GL_DIST_ID.
RA_BATCHES_ALL
This table
stores information about each batch of invoices you enter in Oracle
Receivables. Each row includes information about each batch belonging to a
batch source. TYPE contains the value ’INV’ for all records. The column STATUS
and BATCH_SOURCE_ID are required even though they are null allowed.
The primary key
for this table is BATCH_ID.
AR_BATCH_SOURCES_ALL
This table
stores information about your Receipt Batch Sources. Receipt Batch Sources
provide default values for the Receipt Class, bank account, and Payment Method
for each receipt in a batch. Oracle Receivables also uses this information to
automatically number your batch sources. All of the accounting information
stored in this table prior to release 10 has been moved to the
AR_RECEIPT_METHOD_ACCOUNTS table which is an intersection table for Payment
Methods and bank accounts. The primary key for this table is BATCH_SOURCE_ID,
which identifies the batch source that created the row.
AR_SALES_TAX
This table
stores compiled sales tax rates for each taxing authority defined in Oracle
Receivables. Each taxing authority may have multiple sales tax rates associated
with it differentiated by postal code and effectivity date range. Records can
only be created in this table by database triggers associated with the tables
AR_LOCATION_RATES and AR_LOCATION_COMBINATIONS. The table AR_LOCATION_RATES is
the source for all sales tax rates. Any records created in this table will
automatically be compiled into the composite rate, as long as every segment has
overlapping postal code and effectivity dates ranges. This table increases the
performance of the Oracle Receivables sales tax rate calculation program used
during invoice line creation.
MTL_SYSTEM_ITEMS_B
MTL_SYSTEM_ITEMS_B
is the definition table for items. This table holds the definitions for
inventory items, engineering items, and purchasing items. You can specify
item–related information in fields such as: Bill of Material, Costing,
Purchasing, Receiving, Inventory, Physical attributes, General Planning,
MPS/MRP Planning, Lead times, Work in Process, Order Management, and Invoicing.
You can set up
the item with multiple segments, since it is implemented as a flexfield. Use
the standard ’System Items’ flexfield that is shipped with the product to
configure your item flexfield. The flexfield code is MSTK.
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.
Each item is
initially defined in an item master organization. The user then assigns the
item to other organizations that need to recognize this item; a row is inserted
for each new organization the item is assigned to. Many columns such as
MTL_TRANSACTIONS_ENABLED_FLAG and BOM_ENABLED_FLAG correspond to item
attributes defined in the MTL_ITEM_ATTRIBUTES table. The attributes that are
available to the user depend on which Oracle applications are installed. The
table MTL_ATTR_APPL_DEPENDENCIES maintains the relationships between item
attributes and Oracle applications.
Two unit of
measure columns are stored in MTL_SYSTEM_ITEMS table. PRIMARY_UOM_CODE is the
3–character unit that is used throughout Oracle Manufacturing. PRIMARY_UNIT_OF_MEASURE
is the 25–character unit that is used throughout Oracle Purchasing.
RA_TERMS_B
This table
stores standard Payment Term information. You need one row for each Payment
Term you define in Oracle Receivables. Oracle Receivables uses this information
to calculate when a payment is due and any discounts given for early payment.
Oracle Receivables stores payment schedules in AR_PAYMENT_SCHEDULES_ALL.
RA_TERMS_LINES
This table
stores detailed line information for each Payment Term you define in RA_TERMS.
You need one row for each Payment Term line. Split Payment Terms will have more
than one row in this table for a given record in RA_TERMS. Oracle Receivables
uses this information to calculate when a payment is due. Discount information
is stored in the RA_TERMS_LINES_DISCOUNTS table.
RA_TERMS_LINES_DISCOUNTS
This table
stores discount information for each row in RA_TERMS_LINES. Each term line can
have multiple discount rows.
AR_MEMO_LINES_ALL_B
This table
stores information about standard memo lines for debit memos, on–account
credits, debit memo reversals, chargebacks, and invoices. Receivables uses this
table to get the Revenue Account if AutoAccounting is based on standard line
items. It also stores the tax code, unit standard price, unit of measure, and
standard invoicing and accounting rules for each standard memo line.
Sales
Tax Interface
AR_TAX_INTERFACE
This table is the table you use to import location,
postal code and sales tax rate information into Oracle Receivables. You insert
rows in this table and then use the Sales Tax Interface Program to create
records in AR_LOCATION_VALUES and AR_LOCATION_RATES. Each row can define a new
location and assign to it multiple postal code and effectivity date ranges.
Each range may have an optional sales tax rate. This is expressed by inserting
multiple rows into the AR_TAX_INTERFACE table. The concurrent program Sales Tax
Interface will validate rows in the table AR_TAX_INTERFACE as it loads or
updates records in AR_LOCATION_VALUES and AR_LOCATION_RATES. Sample SQL*Loader
control files are defined in the Oracle Receivables BIN directory for different
vendors of US Sales Tax jurisdictions and rates.
HZ_LOCATIONS
HZ_LOCATIONS stores information about physical
locations.
HZ_LOC_ASSIGNMENTS
HZ_LOC_ASSIGNMENTS stores the relationship between
a location and a tax authority that you defined in the table.
AR_LOCATION_COMBINATIONS. In a non multi–org environment, this table will
contain a tax authority for each location for which you want to calculate sales
tax. In a multi–org environment, this table may contain multiple records for
each location for which you want to calculate sales tax, one record per
organization. This may happen when you share the same party site among multiple
customer account sites across organizations. Rows in this table will be
automatically created when you create a customer account site in the
HZ_CUST_ACCT_SITE_ALL table. If the customer address does not exist within the
default country as defined by Oracle Receivables system options, the LOC_ID
will be set to NULL.
AR_LOCATION_VALUES
This table defines each jurisdiction that Oracle
Receivables uses to validate segments of a customer address and compile sales
tax rates. Locations exist within other parent locations. Oracle Receivables uses
the PARENT_SEGMENT_ID to enforce this business rule. The first or senior
segment of the Sales Tax Location Flexfield is exempt from this ’ownership’
rule. The Sales Tax Location Flexfield defines the structure that will be used
to relate one segment of an address to another. Rows are created in this table
automatically using either the Oracle Receivables Sales Tax Interface Program
or upon the creation of a new customer address. Rows can also be created
manually using the Tax Locations and Rates window.
AR_LOCATION_RATES
This table stores postal code ranges, effectivity
dates and sales tax rates for each jurisdiction defined in AR_LOCATION_VALUES.
Records are created in this table automatically using the Oracle Receivables
Sales Tax Interface Program or manually using the Tax Locations and Rates
window. Whenever new records are created or old ones are updated in this table,
database triggers recompile all of the associated sales tax rates into the
table AR_SALES_TAX. Only those locations and rates that have overlapping postal
code and effectivity date ranges for every segment of the Sales Tax Location
Flexfield will have corresponding rows created in the table AR_SALES_TAX.
AR_LOCATION_COMBINATIONS
This table stores the combinations of taxing
jurisdictions that together define a tax authority. Rows in this table can be
created manually using the Tax Authorities window or automatically from
database triggers against the table RA_ADDRESSES_ALL. Every address that exists
within the default country as defined by Oracle Receivables system parameters
will have a set of taxing jurisdictions automatically created for it. The
combination of these jurisdictions into an authority is also performed
automatically by database triggers. Whenever new records are created in
AR_LOCATION_COMBINATIONS, more database triggers automatically create compiled
sales tax rates for this taxing authority into the table AR_SALES_TAX. Only
those locations and rates that have overlapping postal code and effectivity
date ranges are summed into the single record in AR_SALES_TAX.
AR_SALES_TAX
This table stores compiled sales tax rates for each
taxing authority defined in Oracle Receivables. Each taxing authority may have
multiple sales tax rates associated with it differentiated by postal code and
effectivity date range. Records can only be created in this table by database
triggers associated with the tables AR_LOCATION_RATES and
AR_LOCATION_COMBINATIONS. The table AR_LOCATION_RATES is the source for all
sales tax rates. Any records created in this table will automatically be
compiled into the composite rate, as long as every segment has overlapping
postal code and effectivity dates ranges. This table increases the performance
of the Oracle Receivables sales tax rate calculation program used during
invoice line creation.
Receipts
AR_DISTRIBUTION_SETS_ALL
This
table stores general information about your distribution sets such as the name,
description, and status. The total percent allocated to your distribution set
lines must equal 100%. You use AR_DISTRIBUTION_SETS_ALL along with
AR_DISTRIBUTION_SET_LINES_ALL to automatically distribute your other receipt
payments. Oracle Receivables displays distribution sets as list of values
choices to speed data entry. The primary key for this table is DISTRIBUTION_SET_ID.
AR_DISTRIBUTION_SET_LINES_ALL
This table stores specific information about
individual distribution accounts. Each row joins specific accounting
information with receipt percentages. The total percent of all distribution set
lines must equal 100%. Oracle Receivables creates one row for each distribution
percent. AR_DISTRIBUTION_SETS_ALL stores information about your distribution
set. Oracle Receivables uses distribution set lines to speed data entry. The
primary key for this table is DISTRIBUTION_SET_ID.
AR_CASH_RECEIPTS_ALL
This table stores one record for each receipt that
you enter. Oracle Receivables creates records concurrently in the
AR_CASH_RECEIPT_HISTORY_ALL, AR_PAYMENT_SCHEDULES_ALL, and
AR_RECEIVABLE_APPLICATIONS tables for invoice–related receipts. For receipts
that are not related to invoices (Miscellaneous Receipts), Oracle Receivables
creates records in the AR_MISC_CASH_DISTRIBUTIONS table instead of the
AR_RECEIVABLE_APPLICATIONS_ALL table. Oracle Receivables associates a STATUS
with each receipt. These statuses include applied (APP), unapplied (UNAPP),
unidentified (UNID), non–sufficient funds (NSF), reversed receipt (REV), and
stop payment (STOP). Oracle Receivables does not update the status of a receipt
from UNAPP to APP until the entire amount of the receipt is either applied or
placed on account. A receipt can have a status of APP even if the entire
receipt amount is placed on account. In Release 10, the CODE_COMBINATION_ID
column was moved to the AR_CASH_RECEIPT_HISTORY_ALL table. Cash receipts now go
through a cycle of steps that include confirmation, remittance, and clearance.
Each step creates rows in the AR_CASH_RECEIPT_HISTORY table. The
CODE_COMBINATION_ID column in that table stores the accounts that are debited
and credited as part of these steps. RECEIVABLES_TRX_ID links the
AR_CASH_RECEIPTS_ALL table to the AR_RECEIVABLES_TRX_ALL table. This column
identifies the Receivables Activity you select when you enter Miscellaneous
Receipts. DISTRIBUTION_SET_ID links the AR_CASH_RECEIPTS_ALL table to the
AR_DISTRIBUTION_SETS_ALL table. This column identifies the distribution set and
the distribution set line accounts that are credited when you enter
Miscellaneous Receipts. CUSTOMER_BANK_ACCOUNT_ID replaced CUSTOMER_MICR_ID as a
pointer to the customer bank account. It is a foreign key to the AP_BANK_ACCOUNTS_ALL table to a bank account
with a type of EXTERNAL (meaning not one of your own bank accounts). GL_DATE
and REVERSAL_GL_DATE have also been moved to the AR_CASH_RECEIPT_HISTORY_ALL
table as each step has its own GL_DATE and accounting impact. The primary key
for this table is CASH_RECEIPT_ID, which identifies the receipt transaction
that created the row for the receipt.
AP_BANK_ACCOUNTS_ALL
AP_BANK_ACCOUNTS_ALL contains information about
your bank accounts. You need one row for each bank account you define. Each
bank account must be affiliated with one bank branch. When you initiate an
automatic payment batch, enter a manual check, or create a Quick payment, you can
select a bank account that you define in this table. This table corresponds to
the Bank Accounts window.
AR_MISC_CASH_DISTRIBUTIONS_ALL
This table stores all accounting entries for your
miscellaneous cash applications. Miscellaneous cash is non–revenue income such
as stock revenue, interest income, and investment income.
AR_CASH_RECEIPTS_ALL stores one record for each
payment while AR_MISC_CASH_DISTRIBUTIONS_ALL stores one record for each
distribution of the receipt. The primary key for this table is MISC_CASH_DISTRIBUTION_ID.
AR_INTERIM_CASH_RECEIPTS_ALL
This is a temporary table that stores entries for
receipts entered via QuickCash. Oracle Receivables creates one record for each
receipt. If you enter a receipt with a status of Unapplied, Unidentified, On
Account, or apply payment to one invoice, Oracle Receivables stores both
payment and application information. When the payment is applied to many
invoices, Oracle Receivables creates one record for each receipt application in
AR_INTERIM_CASH_RCPT_LINES_ALL. After you run the Post QuickCash program,
Oracle Receivables creates a record in AR_CASH_RECEIPTS_ALL for each receipt
and AR_RECEIVABLE_APPLICATIONS_ALL for each receipt application. Oracle
Receivables then deletes data from this table. The primary key for this table
is CASH_RECEIPT_ID.
AR_INTERIM_CASH_RCPT_LINES_ALL
This is a temporary table that stores entries for
each QuickCash receipt application. After you run the Post QuickCash program,
Oracle Receivables creates an entry in AR_RECEIVABLE_APPLICATIONS_ALL for each
application. Oracle Receivables then deletes data from this table. The primary
key for this table is CASH_RECEIPT_LINE_ID.
AR_RECEIPT_METHODS
This table stores information about Payment
Methods, receipt attributes that you define and assign to Receipt Classes to
account for receipts and their applications. For automatically created
receipts, a Payment Method defines the rules for creating these receipts. For
manually created receipts, a Payment Method defines a user–definable type for
the receipt. Each Payment Method is associated with a set of bank accounts,
which forms the set of bank accounts you can assign to your receipt. For
example, if you normally receive Lockbox transmissions from bank ABC and bank
DEF, you might create a Payment Method called LOCKBOX and assign bank accounts
from bank ABC and bank DEF to this Payment Method.
AR_DISTRIBUTIONS_ALL
This table stores the distributions generated by
the different steps in the life cycle of a cash receipt. This information was
moved from the AR_CASH_RECEIPT_HISTORY_ALL table as there could be more than
one account associated with each history row. The primary key for this table is
SOURCE_ID, SOURCE_TABLE, SOURCE_TYPE. In the current release, only the
SOURCE_TABLE is recognized.
AR_CASH_RECEIPT_HISTORY_ALL
This table contains each step in the life cycle of
a receipt. Each row represents one step. The status field tells you which step
the receipt has reached: APPROVED – This is only valid for an automatically
created receipt. This status indicates that the receipt has been approved for
automatic creation. CONFIRMED – This is only valid for an automatically created
receipt. This status indicates that the receipt has been confirmed by the
customer. REMITTED – This is valid for both automatically and manually created
receipts. This status indicates that the receipt has been remitted. CLEARED –
This is valid for both automatically and manually created receipts. This status
indicates that the receipt has been cleared. REVERSED – This is valid for both
automatically and manually created receipts. This status indicates that the
receipt has been reversed. The rows in this table are posted to the General
Ledger. Each rows debits the account represented by the
ACCOUNT_CODE_COMBINATION_ID column on the given GL_DATE and credits the account
on the given REVERSAL_GL_DATE (if one is present). Optionally, it will also
debit (on the GL_DATE) and credit (on the REVERSAL_GL_DATE) the account
represented by the BANK_CHARGE_ACCOUNT_CCID for the FACTOR_DISCOUNT_AMOUNT
which represents the difference between the remitted amount and the cleared
amount. POSTABLE_FLAG determines whether a row can be posted to General Ledger.
The CURRENT_RECORD_FLAG points you to the current row – that is, the current
status of the cash receipt.
AR_BATCHES_ALL
This table stores information about each receipt
batch that you create in Oracle Receivables. Each row includes information
about a specific batch such as batch source, status, batch type, control count,
and control amount. The BATCH_APPLIED_STATUS column stores the status of
your QuickCash batches in relation to running
PostBatch. Valid values are ’IN_PROCESS’, ’PROCESSED’, and ’POSTBATCH_WAITING’
(for rows that have not been processed by the Post QuickCash program). The TYPE
column has one of the following values: ’CASH’ for manually created batches;
’CREATION’ for batches that contain automatic receipts; ’REMITTANCE’ for
remittance batches; and ’CLEARANCE’ for clearance batches. The primary key for
this table is BATCH_ID.
AR_BATCH_SOURCES_ALL
This table stores information about your Receipt
Batch Sources. Receipt Batch Sources provide default values for the Receipt
Class, bank account, and Payment Method for each receipt in a batch. Oracle
Receivables also uses this information to automatically number your batch
sources. All of the accounting information stored in this table prior to
release 10 has been moved to the AR_RECEIPT_METHOD_ACCOUNTS table which is an
intersection table for Payment Methods and bank accounts. The primary key for
this table is BATCH_SOURCE_ID, which identifies the batch source that created
the row.
AR_LOCKBOXES_ALL
This table stores information about your lockboxes.
AutoLockbox uses your Lockbox definitions when transferring receipts from your
bank file into Oracle Receivables. The primary key for this table is
LOCKBOX_ID.
AR_TRANSMISSIONS_ALL
This table stores information about each Lockbox
transmission. Each row includes the original transmission request ID, the
transmission date, time, count, and amount. You use this information to review
the status of your transmissions. Possible statuses include New, Out of
Balance, and Closed. Oracle Receivables stores ’OOB’, ’CL’, and ’NEW’. The
primary key for this table is TRANSMISSION_REQUEST_ID.
AR_PAYMENTS_INTERFACE_ALL
This table stores imported lockbox information that
has not been validated. AutoLockbox creates one row in this table for each
record in a transmission. When you run the validation step of
AutoLockbox,Oracle Receivables transfers the information from the
AR_PAYMENTS_INTERFACE_ALL tables to the AR_INTERIM_CASH_RECEIPTS_ALL and
AR_INTERIM_CASH_RCPT_LINES_ALL tables. The primary key for this table is
TRANSMISSION_RECORD_ID.
Posting
AR_CASH_RECEIPT_HISTORY_ALL
This table contains each step in the life cycle of
a receipt. Each row represents one step. The status field tells you which step
the receipt has reached: APPROVED – This is only valid for an automatically
created receipt. This status indicates that the receipt has been approved for
automatic creation. CONFIRMED – This is only valid for an automatically created
receipt. This status indicates that the receipt has been confirmed by the
customer. REMITTED – This is valid for both automatically and manually created
receipts. This status indicates that the receipt has been remitted. CLEARED –
This is valid for both automatically and manually created receipts. This status
indicates that the receipt has been cleared. REVERSED – This is valid for both
automatically and manually created receipts. This status indicates that the
receipt has been reversed. The rows in this table are posted to the General
Ledger. Each rows debits the account represented by the
ACCOUNT_CODE_COMBINATION_ID column on the given GL_DATE and credits the account
on the given REVERSAL_GL_DATE (if one is present). Optionally, it will also
debit (on the GL_DATE) and credit (on the REVERSAL_GL_DATE) the account
represented by the BANK_CHARGE_ACCOUNT_CCID for the FACTOR_DISCOUNT_AMOUNT
which represents the difference between the remitted amount and the cleared
amount. POSTABLE_FLAG determines whether a row can be posted to General Ledger.
The CURRENT_RECORD_FLAG points you to the current row – that is, the current
status of the cash receipt.
AR_DISTRIBUTIONS_ALL
This table stores the distributions generated by
the different steps in the life cycle of a cash receipt. This information was
moved from the AR_CASH_RECEIPT_HISTORY_ALL table as there could be more than
one account associated with each history row. The primary key for this table is
SOURCE_ID, SOURCE_TABLE, SOURCE_TYPE. In the current release, only the
SOURCE_TABLE is recognized.
RA_CUST_TRX_LINE_GL_DIST_ALL
This table stores the accounting records for
revenue, unearned revenue and unbilled receivables for each invoice or credit
memo line. Each row includes the GL account and the amount of the accounting
entry. The AMOUNT column in this table is required even though it is null
allowed. You need one row for each accounting distribution. You must have at
least one (but you can have multiple) accounting distributions for each invoice
or credit memo line. Oracle Receivables uses this information to post the
proper amounts to your general ledger. If your invoice or credit memo has a
transaction type where Post to GL is set to No, Oracle Receivables assigns Null
to GL_DATE. If your AutoAccounting is unable to complete your general ledger
default accounts using the AutoAccounting rules you define, incomplete general
ledger accounts are stored in CONCATENATED_SEGMENTS. If you are importing a
transaction through AutoInvoice and the general ledger date of your transaction
is in a closed accounting period, AutoInvoice uses the general ledger date of
the first open accounting period and stores the original general ledger date in
ORIGINAL_GL_DATE. ACCOUNT_CLASS defines which type of distribution row you are
on. The ACCOUNT_CLASS REC represents the receivable account and is for the
total amount of the invoice. There can be at most two REC rows. One that has a
ACCOUNT_SET_FLAG set to Y and the other has ACCOUNT_SET_FLAG set to N. Use
LATEST_REC_FLAG to join to the later of the two rows. ACCOUNT_SET_FLAG is Y if
this row is part of an account set. An account set is a set of rows that
represent a model distribution. Account sets are used for invoices with rules.
The rows represent how the actual distribution rows should be created and what
percentage of the actual distribution should be allocated to each account. For
invoices with rules, the distributions are not created when the invoice is
initially created. Instead, the invoices are created when the Revenue
Recognition program is run. The primary key for this table is
CUST_TRX_LINE_GL_DIST_ID.
RA_CUSTOMER_TRX_LINES_ALL
This table stores information about invoice, debit
memo, credit memo, and commitment lines. For example, an invoice can have one
line for Product A and another line for Product B. You need one row for each
line. Invoice, debit memo, credit memo, and commitment lines are distinguished
by the transaction type of the corresponding RA_CUSTOMER_TRX_ALL record. Also,
credit memos are required to have a value in PREVIOUS_CUSTOMER_TRX_LINE_ID,
except on account credits which are not related to specific invoices/invoice
lines at creation time, will not have values in this column. QUANTITY_ORDERED
stores the amount of product ordered. QUANTITY_INVOICED stores the amount of
product invoiced. For invoices entered through the window, QUANTITY_ORDERED and
QUANTITY_INVOICED must be the same. For invoices imported through AutoInvoice,
QUANTITY_ORDERED and QUANTITY_INVOICED can be different. If you enter a credit
memo, QUANTITY_CREDITED stores the amount of product credited. UOM_CODE stores
the unit of measure code as defined in MTL_UNITS_OF_MEASURE.
UNIT_STANDARD_PRICE stores the list price per unit for this transaction line.
UNIT_SELLING_PRICE stores the selling price per unit for this transaction line.
For transactions imported through AutoInvoice, UNIT_STANDARD_PRICE and
UNIT_SELLING_PRICE can be different. DESCRIPTION, TAXING_RULE, QUANTITY_ORDERED,
UNIT_STANDARD_PRICE, UOM_CODE, and UNIT_SELLING_PRICE are required even though
they are null allowed. LINE_TYPE differentiates between the different types of
lines that are stored in this table. LINE points to regular invoice lines that
normally refer to an item. TAX signifies that this is a tax line. The column
LINK_TO_CUST_TRX_LINE_ID references another row in this table that is the
invoice line associated with the row of type TAX. FREIGHT works the same way as
TAX but there you can have at most one FREIGHT type l ine per invoice line of
type LINE. You can also have one line of type FREIGHT that has a null
LINK_TO_CUST_TRX_LINE_ID (and this is referred to as header level freight).
CHARGES works just like the LINE type. A line_type of ’CB’ is created for a
Chargeback line. For every row in this table that belongs to a complete
transaction (where RA_CUSTOMER_TRX.COMPLETE_FLAG = Y), there must be at least
one row in the table RA_CUST_TRX_LINE_GL_DIST (which stores accounting
information), even for non–postable transactions. The primary key for this
table is CUSTOMER_TRX_LINE_ID.
RA_CUSTOMER_TRX_ALL
This table stores invoice, debit memo, commitment,
and credit memo header information. Each row includes general invoice
information such as customer, transaction type, and printing instructions. You
need one row for each invoice, debit memo, commitment, and credit memo you
create in Oracle Receivables. Invoices, debit memos, credit memos, and
commitments are all distinguished by their transaction types stored in RA_CUST_TRX_TYPES_ALL.
If you entered a credit memo, PREVIOUS_CUSTOMER_TRX_ID stores the customer
transaction identifier of the invoice you credited. In the case of on account
credits, which are not related to any invoice at creation, PREVIOUS_CUSTOMER_TRX_ID
is null. If you created an invoice against a commitment, Oracle Receivables
stores the customer transaction identifier of the commitment in
INITIAL_CUSTOMER_TRX_ID, otherwise it is null. COMPLETE_FLAG stores ’Y’ for Yes
and ’N’ for No to indicate if your invoice is complete. When you complete an
invoice, Oracle Receivables creates your payment schedules and updates any
commitments against this invoice. Before an invoice can be completed, it must
have at least one invoice line, revenue records must exist for each line and
add up to the line amount, and a sales tax record must exist for each line.
SOLD_TO_CUSTOMER_ID, SOLD_TO_SITE_USE_ID, BILL_TO_CUSTOMER_ID,
BILL_TO_SITE_USE_ID, SHIP_TO_SITE_USE_ID, PRINTING_OPTION, PRINTING_PENDING,
TERM_ID, REMIT_TO_ADDRESS_ID, PRIMARY_SALES_REP_ID,
and INVOICE_CURRENCY_CODE are required even though they are null allowed. The
primary key for this table is CUSTOMER_TRX_ID.
AR_RECEIVABLE_APPLICATIONS_ALL
This table stores all accounting entries for your
cash and credit memo applications. Each row includes the amount applied,
status, and accounting flexfield information. Possible statuses of your
applications include APP, UNAPP, ACC, and UNID. You use this information to
determine the applications of your payments or credit memos. CONFIRMED_FLAG is
a denormalization from AR_CASH_RECEIPTS_ALL. If the cash receipt is not
confirmed, the applications of that receipt are not reflected in the payment
schedule of the transaction it is applied against. There are two kinds of applications:
CASH and CM (for credit memo applications). This is stored in the column
APPLICATION_TYPE. CASH applications represent applications of a cash receipt.
When a cash receipt is initially created, a row is created in this table that
has a status of UNAPP for the amount of the cash receipt. Each subsequent
application creates two rows – one with a status of APP for the amount being
applied to the invoice and one with status UNAPP for the negative of the amount
being applied. If you reverse a cash application, a row with status APP with
the inverse amount of the original application (i.e. the negative of the
original application amount) is created. The corresponding UNAPP rows is also
created which will have a positive amount (the same amount as the application
being reversed). For example: UNAPP 100 creation of a $100 cash receipt APP 60
application of $60 of this cash receipt UNAPP –60 this row takes away (debits)
unapplied APP –60 reversal of the $60 application UNAPP 60 this rows puts back
(credits) unapplied The sum of the AMOUNT_APPLIED column for CASH applications
should always equal the amount of the cash receipt. CM applications, on the
other hand, do not have rows of status UNAPP. They only use rows with a status
of APP. CASH_RECEIPT_ID stores the cash receipt identifier of the receipt you
entered. Oracle Receivables concurrently creates a record of this receipt in
the AR_CASH_RECEIPTS_ALL table. This column is null for a credit memo
application. CODE_COMBINATION_ID stores valid Accounting Flexfield segment
value combinations that will be credited in the General Ledger when this
application is posted. A negative value in AMOUNT_APPLIED becomes a debit. The
STATUS of a receivable application determines which flexfield account Oracle
Receivables uses. For example, if you enter a cash receipt of $500 as
Unidentified, Oracle Receivables creates a record in the
AR_RECEIVABLE_APPLICATIONS_ALL table with AMOUNT_APPLIED = 500 and STATUS =
’UNID’. Oracle Receivables uses the foreign key CODE_COMBINATION_ID to associate
this payment with the Unidentified flexfield account. CUSTOMER_TRX_ID,
CASH_RECEIPT_ID, and PAYMENT_SCHEDULE_ID identify the transaction that you are
actually applying. APPLIED_CUSTOMER_TRX_ID and APPLIED_PAYMENT_SCHEDULE_ID
identify the invoice or credit memo that receives the application. For example,
if you apply a receipt against an invoice, Oracle Receivables creates a record
in the AR_RECEIVABLE_APPLICATIONS_ALL table. The CASH_RECEIPT_ID and the
PAYMENT_SCHEDULE_ID of this record identify the receipt you are applying.
APPLIED_PAYMENT_SCHEDULE_ID and APPLIED_CUSTOMER_TRX_ID for this record belong
to the invoice that is receiving the application. If you apply a credit memo
against the invoice, Oracle Receivables creates a record in the AR_RECEIVABLE_APPLICATIONS_ALL
table that has the CUSTOMER_TRX_ID and the PAYMENT_SCHEDULE_ID of the credit
memo you are applying. The APPLIED_PAYMENT_SCHEDULE_ID and the
APPLIED_CUSTOMER_TRX_ID of this record belong to the invoice that is receiving
the application. If you combine an on account credit and a receipt, Oracle
Receivables creates a record in the AR_RECEIVABLE_APPLICATIONS_ALL table. The
PAYMENT_SCHEDULE_ID and the CASH_RECEIPT_ID of this record identify the
receipt. The APPLIED_PAYMENT_SCHEDULE_ID and the APPLIED_CUSTOMER_TRX_ID of
this record identify the on account credit that you are combining with the
receipt. The primary key for this table is RECEIVABLE_APPLICATION_ID, which
uniquely identifies the transaction that created the row.
AR_MISC_CASH_DISTRIBUTIONS_ALL
This table stores all accounting entries for your
miscellaneous cash applications. Miscellaneous cash is non–revenue income such
as stock revenue, interest income, and investment income. AR_CASH_RECEIPTS_ALL
stores one record for each payment while AR_MISC_CASH_DISTRIBUTIONS_ALL stores
one record for each distribution of the receipt. The primary key for this table
is MISC_CASH_DISTRIBUTION_ID.
AR_ADJUSTMENTS_ALL
This table stores information about your invoice
adjustments. Each row includes general information about the adjustment you are
making such as activity name, amount, accounting information, reason, and type
of adjustment. You need one row for each adjustment you are making to an
invoice. Oracle Receivables uses this information to update the AMOUNT_ADJUSTED
and AMOUNT_DUE_REMAINING columns in AR_PAYMENT_SCHEDULES_ALL. If you create an
adjustment through the Receipts window, Oracle Receivables fills in
ASSOCIATED_CASH_RECEIPT_ID. This stores the cash receipt identifier of the receipt
you entered when you created the adjustment to your invoice, debit memo, or
chargeback. The primary key for this table is ADJUSTMENT_ID.
FND_CURRENCIES
FND_CURRENCIES stores information about currencies.
Each row includes the currency code (CURRENCY_CODE) established by ISO
(International Standards Organization) standard, the name of the currency
(NAME), a flag to indicate whether the currency is enabled for use at your site
(ENABLED_FLAG), a flag to indicate if this is a currency or a statistical unit
(CURRENCY_FLAG), and the territory code of the issuing country
(ISSUING_TERRITORY_CODE). Each row also includes the number of digits to the
right of the decimal point (PRECISION), the extended precision
(EXTENDED_PRECISION), the symbol denoting the currency, a description of the
currency, and descriptive flexfield attribute columns. There is also
information on when the currency becomes active and inactive, and the minimum
accountable unit for the currency. You need one row for each currency defined
with Oracle Application Object Library. Oracle Application Object Library uses
this information to display dynamic currency values. You can also use this
information to assign a currency to a set of books.
GL_INTERFACE
GL_INTERFACE is the table you use to import journal
entry batches through Journal Import. You insert rows in this table and then
use the Import Journals form to create journal batches. You must supply values
for all NOT NULL columns. For a complete description of how to load this table,
see the Oracle General Ledger User Guide.
GL_JE_LINES
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.
STATUS is ’U’ for unposted or ’P’ for posted.
GL_JE_HEADERS
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. This table
corresponds to the Journals window of the Enter Journals form. STATUS is ’U’
for unposted, ’P’ for posted. Other statuses indicate that an error condition
was found. A complete list is below. CONVERSION_FLAG equal to ’N’ indicates
that you manually changed a converted amount in the Journal Entry Lines zone of
a foreign currency journal entry. In this case, the posting program does not
re–convert your foreign amounts. This can happen only if your user profile
option MULTIPLE_RATES_PER_JE is ’Yes’. BALANCING_SEGMENT_VALUE is null if there
is only one balancing segment value in your journal entry. If there is more
than one, BALANCING_SEGMENT_VALUE is the greatest balancing segment value in
your journal entry.
Following is a list of STATUS codes for this table:
– Bad rounding account
> Reserved for country – specific
functionality
< Reserved for country – specific
functionality
U Unposted
P Posted
1 Invalid currency code
2 Invalid source
3 Invalid category
4 Invalid set of books
5 Invalid set of books
6 (Actual) Unopened period
6 (Budget) Invalid budget version
6 (Encumbrance) Invalid encumbrance type
7 Invalid entry
8 Invalid entry
A Code combination does not exist
B Multiple lines have code combination
error
C Code combination: detail posting not
allowed
D Multiple lines have code combination
error
E Multiple lines have code combination
error
F Code combination not enabled
G Multiple lines have code combination
error
H Multiple lines have code combination
error
I Multiple lines have code combination
error
J Code combination not yet effective (date)
K Multiple lines have code combination
error
L Multiple lines have code combination
error
M Code combination past effective date
N Multiple lines have code combination
error
O Multiple lines have code combination
error
Q Multiple lines have code combination
error
R Multiple lines have code combination
error
T Multiple lines have code combination
error
V Multiple lines have code combination
error
Z Multiple lines have code combination
error
GL_JE_BATCHES
GL_JE_BATCHES stores journal entry batches. Each
row includes the batch name, description, status, running total debits and
credits, and other information. This table corresponds to the Batch window of
the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for
selected, ’I’ for in the process of being posted. Other values of status
indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify
an unposted journal entry batch. The posting program changes STATUS_VERIFIED to
’I’ when posting is in process and ’Y’ after posting is complete.
GL_CODE_COMBINATIONS
GL_CODE_COMBINATIONS stores valid account
combinations for each Accounting Flexfield structure within your Oracle General
Ledger application. Associated with each account are certain codes and flags,
including whether the account is enabled, whether detail posting or detail
budgeting is allowed, and others. Segment values are stored in the SEGMENT
columns. Note that each Accounting Flexfield structure may use different
SEGMENT columns within the table to store the flexfield value combination.
Moreover, the SEGMENT columns that are used are not guaranteed to be in any
order. The Oracle Application Object Library table FND_ID_FLEX_SEGMENTS stores
information about which column in this table is used for each segment of each
Accounting Flexfield structure. Summary accounts have SUMMARY_FLAG = ’Y’ and
TEMPLATE_ID not NULL. Detail accounts have SUMMARY_FLAG = ’N’ and TEMPLATE_ID
NULL.
GL_SETS_OF_BOOKS
GL_SETS_OF_BOOKS stores information about the sets
of books you define in your Oracle General Ledger application. Each row
includes the set of books name, description, functional currency, and other
information. This table corresponds to the Set of Books form.
Party
HZ_PARTIES
HZ_PARTIES stores information about parties
such as organizations, people, and groups, including the identifying address
information for the party.
HZ_PARTY_RELATIONSHIPS
HZ_PARTY_RELATIONSHIPS
stores the relationships between parties. A flag indicates whether the
relationship is directional.
HZ_REFERENCES
HZ_REFERENCES stores information about references
given for parties.
HZ_CREDIT_RATINGS
HZ_CREDIT_RATINGS stores information about the
credit ratings of
parties.
HZ_FINANCIAL_PROFILE
HZ_FINANCIAL_PROFILE stores information about the
financial accounts, other than those held in the company accounts, that belong
to parties.
HZ_CERTIFICATIONS
HZ_CERTIFICATIONS describes certifications given to
parties. A certification is an announcement, usually given after an evaluation,
that indicates how a party performed on the evaluation.
HZ_PARTY_SITES
HZ_PARTY_SITES
stores information about the relationship between Parties and Locations. The
same party can have multiple party sites. Physical addresses are stored in
HZ_LOCATIONS.
HZ_PARTY_SITE_USES
HZ_PARTY_SITE_USES stores information about how a
party uses a particular site or address. A party site can have multiple site
uses such as bill–to or ship–to.
HZ_TIMEZONES
HZ_TIMEZONES stores information about time zones.
HZ_LOCATIONS
HZ_LOCATIONS
stores information about physical locations.
HZ_TIMEZONE_MAPPING
HZ_TIMEZONE_MAPPING stores the mapping of address
elements to time zones.
HZ_LOC_ASSIGNMENTS
HZ_LOC_ASSIGNMENTS
stores the relationship between a location and a tax authority that you defined
in the table. AR_LOCATION_COMBINATIONS. In a non multi–org environment, this
table will contain a tax authority for each location for which you want to
calculate sales tax. In a multi–org environment, this table may contain
multiple records for each location for which you want to calculate sales tax,
one record per organization. This may happen when you share the same party site
among multiple customer account sites across organizations. Rows in this table
will be automatically created when you create a customer account site in the
HZ_CUST_ACCT_SITE_ALL table. If the customer address does not exist within the
default country as defined by Oracle Receivables system options, the LOC_ID
will be set to NULL.
AR_LOCATION_COMBINATIONS
This table stores the combinations of taxing
jurisdictions that together define a tax authority. Rows in this table can be
created manually using the Tax Authorities window or automatically from
database triggers against the table RA_ADDRESSES_ALL. Every address that exists
within the default country as defined by Oracle Receivables system parameters
will have a set of taxing jurisdictions automatically created for it. The
combination of these jurisdictions into an authority is also performed
automatically by database triggers. Whenever new records are created in
AR_LOCATION_COMBINATIONS, more database triggers automatically create compiled
sales tax rates for this taxing authority into the table AR_SALES_TAX. Only
those locations and rates that have overlapping postal code and effectivity date
ranges are summed into the single record in AR_SALES_TAX.
AR_LOCATION_VALUES
This table defines each jurisdiction that Oracle
Receivables uses to validate segments of a customer address and compile sales
tax rates. Locations exist within other parent locations. Oracle Receivables
uses the PARENT_SEGMENT_ID to enforce this business rule. The first or
senior segment of the Sales Tax Location Flexfield
is exempt from this ’ownership’ rule. The Sales Tax Location Flexfield defines
the structure that will be used to relate one segment of an address to another.
Rows are created in this table automatically using either the Oracle
Receivables Sales Tax Interface Program or upon the creation of a new customer
address. Rows can also be created manually using the Tax Locations and Rates
window.
FND_LANGUAGES
FND_TERRITORIES
Organization
HZ_PARTIES
HZ_PARTIES
stores information about parties such as organizations, people, and groups,
including the identifying address information for the party.
HZ_ORGANIZATION_PROFILES
HZ_ORGANIZATION_PROFILES stores credit
rating, financial statistics, socioeconomic and corporate linkage information
for business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.
HZ_SECURITY_ISSUED
HZ_SECURITY_ISSUED stores information about the
financial instruments such as stocks and bonds that have been issued by an
organization. These financial instruments may vary depending upon the stock
market in which they are offered.
HZ_STOCK_MARKETS
HZ_STOCK_MARKETS stores information that describes
recognized exchanges for buying and selling financial instruments.
HZ_INDUSTRIAL_CLASS_APP
HZ_INDUSTRIAL_CLASS_APP is an intersection table
that links industrial classifications stored in HZ_INDUSTRIAL_CLASSES to
parties stored in HZ_PARTIES.
HZ_FINANCIAL_REPORTS
HZ_FINANCIAL_REPORTS stores information about the
financial reports that describe the financial status of a party. The details of
these financial reports are stored in HZ_FINANCIAL_NUMBERS.
HZ_FINANCIAL_NUMBERS
HZ_FINANCIAL_NUMBERS stores the details of
financial reports. Each record in this table provides the detail to a financial
report stored in HZ_FINANCIAL_REPORTS.
HZ_INDUSTRIAL_REFERENCE
HZ_INDUSTRIAL_REFERENCE stores industrial
references for organizations.
HZ_ORGANIZATION_INDICATORS
HZ_ORGANIZATION_INDICATORS stores indicators
related to finance, legal, and business standing for business sites. The
primary key for this table is ORGANIZATION_INDICATOR_ID.
HZ_ORGANIZATION_PROFILES
HZ_ORGANIZATION_PROFILES stores credit rating,
financial statistics, socioeconomic and corporate linkage information for
business sites. The primary key for this table is ORGANIZATION_PROFILE_ID.
Person
HZ_PARTIES
HZ_PARTIES
stores information about parties such as organizations, people, and groups, including
the identifying address information for the party.
HZ_ORG_CONTACTS
HZ_ORG_CONTACTS stores information about people as
contacts for organizations or other people. The table includes information
about the person in the context of the organization, such as job title, or as
associated with another person, such as manager.
HZ_ORG_CONTACT_ROLES
HZ_ORG_CONTACT_ROLES stores the roles played by organization contacts.
Contacts can have multiple roles in an organization.
HZ_PARTY_RELATIONSHIPS
HZ_PARTY_RELATIONSHIPS stores the relationships between parties. A flag
indicates whether the relationship is directional.
HZ_CITIZENSHIP
HZ_CITIZENSHIP stores information about a person’s claimed nationality.
A person can have more than one citizenship in their lifetime and can have
multiple citizenships at the same time.
HZ_EDUCATION
HZ_EDUCATION stores information about a person’s reported attendance at
a school.
HZ_EMPLOYMENT_HISTORY
HZ_EMPLOYMENT_HISTORY provides a person’s employment history
information.
HZ_WORK_CLASS
HZ_WORK_CLASS stores classifications or groupings of work activities.
Each record in this table assigns a class of work to a person’s employment
history which is stored in HZ_EMPLOYMENT_HISTORY.
HZ_PERSON_LANGUAGE
HZ_PERSON_LANGUAGE stores information about the languages that a person
speaks, reads, or writes.
HZ_PERSON_INTEREST
HZ_PERSON_INTEREST stores information about a person’s personal
interests, such as hobbies or sports.
HZ_PERSON_PROFILES
HZ_PERSON_PROFILES stores detail information about people.
Contact Point
HZ_PARTIES
HZ_PARTIES stores
information about parties such as organizations, people, and groups, including
the identifying address information for the party.
HZ_PARTY_SITES
HZ_PARTY_SITES stores information about the relationship
between Parties and Locations. The same party can have multiple party sites.
Physical addresses are stored in HZ_LOCATIONS.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_ACCT_SITES_ALL stores information about
customer sites. One customer account can have multiple sites. The address is
maintained in HZ_LOCATIONS.
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNTS stores information about customer
relationships. If a party becomes a customer, information about the customer
account is stored in this table. You can establish multiple customer
relationships with a single party, so each party can have multiple customer
account records in this table.
HZ_CUST_CONTACT_POINTS
HZ_CUST_CONTACT_POINTS stores customer contact
point information.
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCOUNT_ROLES stores information about the
roles that parties perform in customer accounts.
HZ_CONTACT_POINTS
HZ_CONTACT_POINTS stores electronic methods of
communicating with entities such as parties and party sites. Each record in
this table represents a different means of contacting an entity.
HZ_CONTACT_RESTRICTIONS
HZ_CONTACT_RESTRICTIONS stores information about
restrictions on contacting parties. Each record references the type of contact
(such as email or fax), the reason contact should not be made, and start and end
dates of the restriction.
1 comment:
Thanks
Post a Comment