For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.
For example, if this table contains a row with a from_currency of YEN, a to_currency of CND, a conversion_type of Spot, and a conversion_date of January 1, 1997, it will also contain a row with a from_currency of CND, a to_currency of YEN, a conversion_type of Spot, and a conversion_date of January 1, 1997.
In general, this row will contain a rate that is the inverse of the matching row. One should never insert directly into this table. They should instead insert into the DAILY_RATES_INTERFACE table. Data inserted into the GL_DAILY_RATES_INTERFACE table will be automatically copied into this table.
The posting program changes STATUS_VERIFIED to ’I’ when posting is in process and ’Y’ after posting is complete.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.
GL_BALANCES stores period activity for an account in the PERIOD_NET_DR and PERIOD_NET_CR columns. The table stores the period beginning balances in BEGIN_BALANCE_DR and BEGIN_BALANCE_CR.
An account’s year–to–date balance is calculated as BEGIN_BALANCE_DR – BEGIN_BALANCE_CR + PERIOD_NET_DR – PERIOD_NET_CR. Detail and summary foreign currency balances that are the result of posted foreign currency journal entries have TRANSLATED_FLAG set to ’R’, to indicate that the row is a candidate for revaluation.
For foreign currency rows, the begin balance and period net columns contain the foreign currency balance, while the begin balance and period net BEQ columns contain the converted functional currency balance. Detail foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to ’Y’ or ’N’. ’N’ indicates that the translation is out of date (i.e., the account needs to be re–translated). ’Y’ indicates that the translation is current.
Summary foreign currency balances that are the result of foreign currency translation have TRANSLATED_FLAG set to NULL. All summary account balances have TEMPLATE_ID not NULL. The columns that end in ADB are not used. Also, the REVALUATION_STATUS column is notused.
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.
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.
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.
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_ALLtable. 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 receiptis 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(Zero).
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.
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. Ifyou 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 alsocreated 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 theAR_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 theCUSTOMER_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.
To avoid the same invoice distribution being interfaced to both Oracle Projects and Oracle Assets, you must interface any project–related invoice distribution to Oracle Projects before you can interface it to Oracle Assets. If the project–related invoice distribution is charged to a capital project in Oracle Projects, Oracle Projects sets the ASSET_ADDITION_FLAG to P when the PA_ADDITION_FLAG is set to Y, Z or T. Oracle Assets only picks up invoice distributions with the ASSET_ADDITION_FLAG set to U and if project–related, with the PA_ADDITION_FLAG set to Y, Z, or T. PA_ADDITION_FLAG tracks the status of project–related supplier invoice distribution lines and expense report distribution lines.
For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project–related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Oracle Projects; see QuickCodes listing for all the errors. You must correct the rejection reason an try to retransfer the line. For supplier invoice adjustment lines interfaced from Oracle Projects to Oracle Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T. For expense report distributions interfaced from Oracle Projects to Oracle Payables via Invoice Import, this value is set to N. This row is never picked up by the Interface Supplier Invoices process based on the AP_INVOICES.INVOICE_TYPE_LOOKUP_CODE = EXPENSEREPORT. For expense report adjustment lines interfaced from Oracle Projects to Oracle Payables which net to zero with another line, thisvalue is set to T. Both lines are associated with the original invoice by the Oracle Projects Interface Expense Reports to AP process. Values for the ENCUMBERED_FLAG are as follows:
FA_DEPRN_PERIODS contains information about your depreciation periods. Oracle Assets uses this table to determine when each period in FA_CALENDARS was open for a depreciation book. PERIOD_OPEN_DATE and PERIOD_CLOSE_DATE are the dates when you opened and closed each book’s depreciation period. Each time you run the depreciation program, it closes the current period by setting PERIOD_CLOSE_DATE to the system date. It also opens the next period by inserting a new row into this table in which PERIOD_CLOSE_DATE is NULL and PERIOD_OPEN_DATE equals the PERIOD_CLOSE_DATE of the old row. CALENDAR_PERIOD_OPEN_DATE and CALENDAR_PERIOD_CLOSE_DATE correspond to your calendar as defined by the START_DATE and END_DATE columns in FA_CALENDAR_PERIODS.
FA_DEPRN_SUMMARY contains depreciation information for your assets. Each time you run the depreciation program, it inserts one row into thistable for each asset. PERIOD_COUNTER is the period for which you ran the depreciation program. DEPRN_AMOUNT is the depreciation expense for an asset in a depreciation period. It is the sum of DEPRN_AMOUNT in all the rows of FA_DEPRN_DETAIL for the asset and period. YTD_DEPRN is the accumulated depreciation of an asset for the current fiscal year as of the end of this period. DEPRN_RESERVE is the total accumulated depreciation for this asset. DEPRN_SOURCE_CODE tells you what program created the row BOOKS Created by the Depreciation Books form, Quick Additions form, or the post mass additions program when you enter a new asset. DEPRN Created by the depreciation program when you run depreciation. ADJUSTED_COST is the depreciable basis the depreciation program uses to calculate depreciation for an asset in a depreciation period. This value is the same as the asset’s recoverable cost, except for assets that use a diminishing value depreciation method, assets to which you have made an amortized adjustment, and assets you have revalued.
For assets that use a diminishing value method, the ADJUSTED_COST is the beginning of year net book value, which the depreciation program updates at the start of each fiscal year. When you perform an amortized adjustment on an asset or revalue it, the ADJUSTED_COST becomes the asset’s net book value at the time of the adjustment or revaluation. BONUS_RATE is the bonus rate that Oracle Assets adds to the adjusted rate to give you the flat rate for the fiscal year. The depreciation program uses this rate to calculate depreciation for an asset. This only applies to assets that use both a flat–rate depreciation method and bonus depreciation.
- ADDITION Depreciation program
- ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP ADDITION Depreciation program
- CIP ADJUSTMENT Expensed or Amortized Adjustment User Exit
- CIP RETIREMENT Gain/loss program
- DEPRECIATION Depreciation program (Retroactive transactions andexpensed depreciation adjustments)
- RETIREMENT Gain/loss program
- RECLASS Reclassification user exit
- TRANSFER Transfers form
- TAX Reserve Adjustments form
- REVALUATION Mass revaluation program
At any point in time, there is only one ”active” row in the table for an asset in any given depreciation book. Generally, Oracle Assets uses the active row, but if you run a report for a prior accounting period, Oracle Assets selects the row that was active during that period. You can identify the active row for anasset in a book because it is the only one whose DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_OUT are NULL. When Oracle Assets terminates a row, the DATE_INEFFECTIVE and TRANSACTION_HEADER_OUT are set to the DATE_EFFECTIVE and TRANSACTION_HEADER_IN of the new row, respectively. This means that you can easily identify rows affected by the same transaction because they have the same DATE_EFFECTIVE / DATE_INEFFECTIVE and TRANSACTION_HEADER_ID_IN / TRANSACTION_HEADER_ID_OUT pairs.
When Oracle Assets creates the new row, the value used for the TRANSACTION_HEADER_ID_IN column is the same as the TRANSACTION_HEADER_ID in the row inserted into FA_TRANSACTION_HEADERS, and the DATE_EFFECTIVE is the system date. When you retire an asset, Oracle Assets inserts a new row to reduce the COST by the amount retired. When you reinstate an asset, Oracle Assets inserts a new row to increase the COST by the COST_RETIRED in the corresponding row in FA_RETIREMENTS.RATE_ADJUSTMENT_FACTOR is originally 1. It is used to spread depreciation over the remaining life of an asset after an amortization or revaluation. If you perform a revaluation or an amortized adjustment, Oracle Assets resets the Rate Adjustment Factor to prorate the remaining recoverable net book value over the remaining life.
This fraction is calculated as [Recoverable Cost – what Depreciation Reserve would be]/Recoverable Cost. The depreciation program uses this value to adjust the depreciation rate for an asset.
Oracle Assets uses this information to create depreciation expense journal entries for your general ledger.The depreciation program inserts one row per distribution line for an asset each time you run depreciation.
For example, if you assign an asset to two different cost centers, the depreciation program inserts two rows in this table for the asset.
FND_ID_FLEXS stores registration information about key flexfields. Each row includes the four–character code that identifies the key flexfield, the title of the flexfield (by which a user identifies theflexfield), the name of the combinations table that contains the key flexfield columns, and the name of the structure defining (MultiFlex) column for the flexfield (SET_DEFINING_COLUMN_NAME). Each row also contains values that identify the application that owns the combination table and the application that owns the key flexfield, a table–type flag that specifies whether the combinations table is specificor generic (S or G), whether dynamic inserts are feasible for the flexfield(Y or N), whether the key flexfield can use ID type value sets, and the name of the unique ID column in the combinations table. You need one row for each key flexfield in each application. Oracle Application ObjectLibrary uses this information to generate a compiled key flexfield definition
FND_FLEX_VALUES stores valid values for key and descriptive flexfield segments. Oracle Application Object Library uses this table when users define values for independent or dependent type value sets. Oracle Application Object Library also uses this table when users define parent values for ranges of child values that exist in a validation table(Oracle Application Object Library stores the parent values in this table). Each row includes the value (FLEX_VALUE) and its hierarchy level if applicable as well as the identifier of the value set the value belongs to. If the value is a dependent value, PARENT_FLEX_VALUE_LOW contains the independent value this value depends upon. Oracle Application Object Library does not use the PARENT_FLEX_VALUE_HIGH column. If ENABLED_FLAG contains N, this value is currently invalid, regardless of the start and end dates.
If ENABLED_FLAG contains Y, the start and end dates indicate if this value is currently valid.
SUMMARY_FLAG indicates if this value is a parent value that has child values, and STRUCTURED_HIERARCHY_LEVEL contains the rollup group the parent value belongs to, if any (1 through 9). COMPILED_VALUE_ATTRIBUTES contains the compiled values of anysegment qualifiers assigned to this value. These values are in a special Oracle Application Object Library format, and you should never modify them.
VALUE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE50 are descriptive flexfield columns, where VALUE_CATEGORY is the context (structure defining) column.
These descriptive flexfield columns do not contain values unless you have defined the descriptive flexfield at your site. You need one row for each independent, dependent or parent value belonging to a value set.
Oracle Application Object Library uses this information to ensure that users enter valid values in flexfield segments