Tax Reporting Ledger

Tax Reporting Ledger Overview

Important: Before you use this report it is very important that you apply the latest Tax Reporting Ledger (TRL) patches. Refer to Troubleshooting and Improving Performance and My Oracle Support Note 737320.1, Troubleshooting eBusiness Tax Reporting Issues in Release 12.

The Tax Reporting Ledger (TRL) provides a single reporting solution for the complex global tax requirements on sales and purchases and enables you to summarize tax information from Receivables, Payables, and General Ledger transaction. In Release 12 of Oracle Applications, taxable transactions are accounted for in the base products according to your tax configuration and tax rules setup in E-Business Tax. The Tax Reporting Ledger consists of the tax information recorded in each of these and related products.

TRL represents a reporting framework for E-Business Tax Release 12 reports for Receivables, Payables, and General Ledger transactions. Reports built around the TRL share a common set of code. When a TRL report is submitted, the main wrapper program is called. This wrapper code can take many forms including Java, PL*SQL procedures or traditional Oracle Reports (RDFs). Parameters are entered by the user. The wrapper code often derives additional parameters and then makes a call to invoke the TRL code. When the TRL code is called, parameters are passed to a set of stored procedures. These procedures extract data from the base table used by the product or products specified in the parameters. TRL code then populates this information into a standard set of reporting tables.

The tax extract copies the accounting information from each application and stores it in an interface table. You can use the available reporting tools, including RXi, Oracle Reports, or XML Publisher to specify which fields of the Tax Reporting Ledger to include and to print the report in a format that meets your needs.

The Tax Reporting Ledger supports the following reports:

Tax Data Extract

The E-Business Tax data extract draws tax information from each application and stores the data in an interface table. Output from the tax extract is designed to look as close to a simple tax report as possible.

The E-Business Tax data extract draws tax transaction data from these sources:

The tax extract copies the original data without performing complex calculation or derivations of taxable or tax amounts. Each record of the extract table includes both taxable and tax amounts and shows this information grouped by the transaction number and regime-to-rate codes of each transaction. See: Tax Reporting Ledger Column Descriptions for information about each of the Tax Reporting Ledger reporting columns.

Typically the wrapper code that invoked the TRL engine data extract will read the data from these reporting tables and generate output. This output also can take many forms. For the Financial Tax Register this is accomplished through the RXi solution. Other reports leverage Oracle Reports to produce XML output and then utilize XML publisher and a template (RTF) to format the output. Others still use Java or the text style output available in Oracle Reports.

It is critical for Tax Administrators or System Administrators to understand the TRL design in order to maintain the most current tax patches and thoroughly test all TRL reports as appropriate for an instance.

Tax Data Publishing

You can call the E-Business Tax data extract from any reporting tool available to you. In most jurisdictions, tax authorities require tax reports to include specific information and to present this information at different levels of summarization. E-Business Tax supports this requirement with tax journal reports, tax audit reports, and tax summary reports. E-Business Tax provides many cross-report parameters for organizing your tax data. See: Common Report Parameters for more information.

There are three publishing tools available for E-Business Tax reports:

Describing the Report Parameters

The following parameters have been organized into groups for descriptive purposes. These groups are not necessarily reflective of the order that they will be displayed when submitting the report.

Important: You should run this report from the forms user interface.

Responsibility: Tax Managers

Navigation: Requests > Other > Requests > Run > Financial Tax Register

Attribute Set

The Financial Tax Register is an RXi report that contains two predefined attribute sets.You can create additional custom attribute sets as needed by copying and editing them (see 'Customizing Output for This Report' below for guidance on creating your own attribute set). The seeded attribute sets are:

Output Format

RXi reports provide flexibility in selecting the format in which you wish to view your output. Four options exist:

Reporting Level, Reporting Context, Company Name and Currency

Important: If you are not using Multi-Org Access Control, the report ignores the Reporting Level and Reporting Context parameters.

When submitting this report, content can be filtered at multiple levels.

Reporting Level

Reporting Context: This LOV creates a list that is contextual to the reporting level selected, for example. a list of operating units appear if you select Operating Unit.

Note: The data displayed in this LOV is further restricted by your security settings and or operating unit restrictions defined by the System Administrator.

Company Name: This parameter is available only when the reporting context is set as Ledger and it is populated by a list of Legal Entities for a given Ledger.

Set of Books Currency: Currency LOV is restricted to the ledger currency. The ledger is identified by the Set of Books profile option, the ledger reporting context or the ledger that is defined for a Legal Entity or Operating Unit.

For more on the currency LOV refer to the value set ZX_TRL_LE_CA_CURRENCY.

Register Type

For examples on register types see Using Register Types.

Summary Level

Allows you to select the level of detail included in the report. Available options include:

Note: Please note that not all attributes specified in your attribute set appear at all summary levels. Distribution accounts for example are not rendered on the output when the report is run at the Transaction level nor are line-level attributes.

Transaction Parameters

Select the application to report on or select All to report on all applications.

Important: You must also select the Payables or Receivables parameters or no data will appear in your report output

GL Date Low/ High

Transaction Date Low/High

Tax Transaction Type Low Transaction Number: Enter a specific transaction number to report on. The list of values for this field depends on the Include options that you enable.

Currency Code Low/High Include Discounts

Transfer to GL: You can report on transactions transferred to General Ledger, transactions not transferred, or all transactions.

Accounting Status

Tax Type Parameters

Payables Parameters

Set to yes to see and No if you do not wish to see.

Note: The actual form does not designate these as Payables specific.

Receivables Parameters

You must to see on the report output.

Note: The actual form does not designate as Receivables specific

Matrix Report Flag

Matrix Reporting should be used to show a single line in the Tax Reporting Ledger for two or more tax amounts. Matrix Reporting can be used to support countries with multiple taxes (for example, Canada) or to provide a breakdown of input taxes, showing both recoverable and non-recoverable tax amounts on the same report line.

If Matrix Reporting is enabled, the Tax Reporting ledger groups either of the following into a single line:

Include Accounting Segments

Set to Yes if your attribute set includes accounting segments. Setting to No may help performance and is advised if you do not have accounting segments in your attribute set.

Using Register Types

The Tax Register and the RX-only: Financial Tax Register include a Register Type parameter. The registers are:

Example 1 - Interim Tax Register vs. Tax Register

This example shows the difference between the Interim Tax Register and the Tax Register. Interim tax details are only extracted for Receivables transactions.

Example Receivables Invoice: Inv-Test1
Debit Credit Debit Amount Credit Amount Description
Dr Receivable   1140.00    
  Cr Revenue @10%   600.00  
  Cr Tax @10   60.00 (Tax @10%)
  Cr Revenue @20   400.00  
  Cr Tax @20   80.00 (Interim Tax @20%)

This table shows the accounting entries created when you apply a partial payment of $570.00. In addition to reducing the open receivable, the cash application moves 50% of the deferred tax originally recorded on the invoice to a collected tax account.

Cash Receipt: CR-Test1. Applied to the Invoice with: CR-Appn-Test1
Debit Credit Debit Amount Credit Amount Description
Dr Cash   570.00   (Cash application of $570.00)
Dr Tax @20   40.00   (50% of the Deferred Tax)
  Cr Receivable   570.00 (Reduce AR by Cash Amount)
  Cr Tax @20   40.00 (Tax due to the Government)

The output from the two Tax Registers (in which both transactions are within the period range entered for the report) is shown below:

Interim Tax Register @10%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
No Records Found      
Interim Tax Register @20%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
Inv-Test1 Invoice 400.00 80.00
Cr-Test1 Cash App. (200.00) (40.00)
Tax Register @10%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
Inv-Test1 Invoice 600.00 60.00
Tax Register @20%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
CR-Test1 Cash App. 200.00 40.00

As these examples illustrate, the Interim Tax Register shows only those account postings for deferred tax, including the invoice and cash application. The Tax Register shows all standard tax transactions as well as all cash applications to a deferred tax invoice.

Note: Using Oracle Receivables you can apply, reverse and reapply cash. Each time you perform these tasks, the original document sequence name and number of the cash receipt identifies the application.

Example 2 - Output Taxes: Non-Recoverable Tax Register vs. Tax Register

This example shows the difference between the Non-Recoverable Tax Register and the Tax Register.

Invoice: Inv-Test2
Debit Credit Debit Amount Credit Amount Description
Dr Receivable @10%   1140.00    
  Cr Revenue @10   600.00  
  Cr Tax @10   60.00 (Tax at 10%)
  Cr Revenue @20   400.00  
  Cr Tax @20   80.00 (Tax at 20%)

An adjustment for (100.00) is made against the invoice, reducing the receivable to $1040.00. This adjustment is accounted for using the following Receivables Activity:

Receivables Activity Option Setting
GL Account Source Revenue on Invoice
Tax Code Source Invoice
Recoverable? Yes

This table shows the accounting entries created as a result of this adjustment:

Adjustment ADJ-1 for (100.00) to Inv-Test2
Debit Credit Debit Amount Credit Amount
Dr Revenue @10%   52.63  
Dr Revenue @20%   35.09  
Dr Tax @10%   5.26  
Dr Tax @20%   7.02  
  Cr Receivable   100.00

A second adjustment is made to invoice INV-Test2 for 200.00. This adjustment is accounted for using the following Receivables Activity:

Receivables Activity Option Setting
GL Account Source Revenue on Invoice
Tax Code Source Invoice
Recoverable No

This table shows the accounting entries created as a result of this adjustment:

Adjustment ADJ-2 for (200.00) to Inv-Test2
Debit Credit Debit Amount Credit Amount
Dr Revenue @10%   105.26  
Dr Revenue @20%   70.18  
Dr Tax (non-recoverable) @10%   10.52  
Dr Tax (non-recoverable) @20%   14.04  
  Cr Receivable   200.00

Using the accounting entries created in this example, the Tax Register and the Non-Recoverable Tax Register appear as shown below:

Tax Register @10%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
Inv-Test2 Invoice 600.00 60.00
ADJ-1 Adjustment (52.63) (5.26)
Tax Register @20%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
Inv-Test2 Invoice 400.00 80.00
ADJ-1 Adjustment (35.09) (7.02)
Non-Recoverable Tax Register @10%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
ADJ-2 Adjustment (105.26) (10.52)
Non-Recoverable Tax Register @20%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
ADJ-2 Adjustment (70.18) (14.04)

Example 3 - Oracle Payables Non-Recoverable Tax Register vs. Recoverable Tax Register

The following example shows how the Tax Registers appear when two Payables invoices have either fully recoverable or partial/non-recoverable taxes.

This example includes two Oracle Payables invoices: AP-Inv-Test3 and AP-Inv-Test4.

Payables Invoice: AP-Inv-Test3
Debit Credit Debit Amount Credit Amount Description
Dr Expense @10%   600.00   (Net amount $600.00 taxed @10%)
Dr Tax @10%   60.00   (Tax @10% fully recoverable)
Dr Expense @20%   400.00   (Net expense amount @20%)
Dr Expense @20%   32.00   (40% of 80 is non-recoverable)
Dr Tax @20%   48.00   (60% of 80 is recoverable)
  Cr Liability   1140.00  
Payables Invoice: AP-Inv-Test4
Debit Credit Debit Amount Credit Amount Description
Dr Expense @10%   600.00   (Net amount $600.00 taxed @10%)
Dr Expense @10%   60.00   (non-recoverable Tax @10%)
Dr Expense @20%   400.00   (Net amount @20% of $400)
Dr Expense @20%   80.00   (non-recoverable Tax @20%)
  Cr Liability   1140.00  

The Tax Registers for these invoices appear as shown below:

Input Tax Register @10%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
AP-Inv-Test3 Invoice 600.00 60.00 0.00
Input Tax Register @20%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
AP-Inv-Test3 Invoice 400.00 48.00 32.00
Non-Recoverable Input Tax Register @10%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
AP-Inv-Test4 Invoice 600.00 0.00 60.00
Non-Recoverable Input Tax Register @20%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
AP-Inv-Test3 Invoice 400.00 48.00 32.00
AP-Inv-Test4 Invoice   0.00 80.00

Example 4 - Prepayment Transactions, Tax is Exclusive

This example shows the content of the Tax Register when you create prepayments.

Prepayment Established: AP (Inv-Test10)
Debit Credit Debit Amount Credit Amount Description
Dr Prepaid Asset or Expense @10%   500.00   (Asset or expense is half paid)
Dr Tax @10%   50.00   (Tax on asset or expense fully paid)
  Cr Liability   550.00 (Prepaid with tax)
Pay the prepayment invoice: AP (Inv-Test10)
Debit Credit Debit Amount Credit Amount
Dr Liability   550.00  
  Cr Cash   550.00

The Tax Register for this prepayment appears as shown below:

Tax Register @10%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
Inv-Test10 Invoice (prepayment) 500.00 50.00 0.00

This is another example of a prepayment.

Invoice for a total including prepayment: AP (Inv-Test20)
Debit Credit Debit Amount Credit Amount Description
Dr Asset or Expense @10%   1000.00   (Invoiced amount)
Dr Tax @10%   50.00   (Tax on balance of invoiced amount)
  Cr Liability   550.00 (Liability for balance of invoiced amount)
  Cr Prepaid Asset or Expense   500.00 (Reverse prepaid asset or expense)

The Tax Register for this prepayment appears as shown below:

Tax Register @10%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
Inv-Test20 Invoice 500.00 50.00 0.00

Example 5 - Tax Calculation of Offset Taxes

This example shows how the Tax Register displays offset taxes in Oracle Payables.

Payables Invoice: AP-Inv-Test30
Debit Credit Debit Amount Credit Amount Description
Dr Expense @10%   1000.00   (Net amount $600.00 taxed @10%)
Dr Output Tax @10%   100.00   (Output Tax @10%)
  Cr Liability   1000.00  
  Cr Input Tax @10%   100.00 (Input Tax @10%)

The Tax Registers for this invoice appear as shown below:

Output Tax Register @10%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
Inv-Test30 Invoice 1000.00 100.00 0.00
Input Tax Register @10%
Transaction Number Transaction Class Taxable Amount Recoverable Tax Amount Non-Recoverable Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX1 ACCTD AMT TAX2 ACCTD AMT
Inv-Test30 Invoice 1000.00 100.00 0.00

Example 6 - Tax Calculation for GL Entries

This example shows how the Tax Register displays entries created in Oracle General Ledger.

General Ledger: AP-Inv-test60
Debit Credit Debit Amount Credit Amount Description
Dr Expense @10%   1000.00   (Net amount)
Dr Tax @10%   100.00   (Tax @10%)
  Cr Liability   1100.00  

The Tax Register appears as shown below:

Tax Register @10%
Transaction Number Transaction Class Taxable Amount Tax Amount
TRX NUMBER TRX CLASS MEANING TAXABLE ACCTD AMT TAX ACCTD AMT
Inv-Test60 General Ledger 1000.00 100.00

Tax Reporting Ledger Column Descriptions

The Tax Reporting Ledger lets you select your reporting columns from a single reporting view. The view ZX_REP_EXTRACT_V unites data from many sources and provides a single, consistent reference to the taxed transactions in your subledgers. Since this reporting view is large, the columns in the view have been grouped together for ease of use. The column groupings are shown below.

Each group of columns shows at which level the column is available (Summarization Level, Transaction Header Level, Transaction Line Level, or Transaction Distribution Level) and whether the column is available in Oracle Receivables (AR), Oracle Payables (AP), and Oracle General Ledger (GL).

Note: The tables below list the attribute names for the attribute set DEFAULT, but you can change them when copying or updating your own attribute sets using RXi.

Extract Columns

The Extract columns maintain system and Row Who information necessary for running the Tax Reporting Ledger. Each time the Tax Reporting Ledger is populated, the request ID of the concurrent program submission is used to identify the set of report lines that belong to that submission. The Extract columns are available at all summarization levels.

Attribute Name Column Name Description Application
Extract Request ID REQUEST_ID Concurrent request ID of the concurrent program or report that populates the Interface table. AR, AP, GL
Extract Created By CREATED_BY Concurrent request created by. AR, AP, GL
Extract Creation Date CREATED_DATE Concurrent request created date. AR, AP, GL
Extract Last Update By LAST_UPDATED_BY Concurrent request updated by. AR, AP, GL
Extract Last Update Date LAST_UPDATE_DATE Concurrent request update date. AR, AP, GL
Reporting Ledger ID LEDGER_ID Internal identifier for the ledger. AR, AP, GL
Reporting Chart of Accounts ID CHART_OF_ACCOUNTS_ID Internal identifier for the chart of accounts. AR, AP, GL
Detail Tax Line ID DETAIL_TAX_LINE_ID Internal unique identifier for this record. AR, AP, GL
Extract Source Ledger EXTRACT_SOURCE_LEDGER Identifies the source ledger for this ledger line. AR, AP, GL
Extract Report Line Number EXTRACT_REPORT_LINE_NUMBER A sequential line number for each record of the Tax Ledger. AR, AP, GL

Reporting Entity

Reporting Entity is included for legacy support only. New implementations should use the Legal Entity implementation for Who am I reporting. The Reporting Entity columns are available at all summarization levels.

Attribute Name Column Name Description Application
Reporting Entity ID REP_ENTITY_ID Reporting entity identifier AR, AP, GL
Reporting Entity Name REP_CONTEXT_ENTITY_NAME Name of the reporting entity AR, AP, GL

Reporting Context

These columns identify the reporting context, including name, level, and address. The Reporting Context columns are available at all summarization levels.

Attribute Name Column Name Description Application
Taxpayer ID TAXPAYER_ID First party taxpayer ID. AR, AP, GL
Reporting Context Level Code REP_CONTEXT_LVL_CODE Internal lookup code for the displayed field Reporting Context Level. AR, AP, GL
Reporting Context Level REP_CONTEXT_LVL_MNG The value of Operating Unit, Legal Entity or Ledger. The Reporting Context Level is taken from the submission parameters. AR, AP, GL
Reporting Context Name REP_CONTEXT_ENTITY_NAME The Reporting Context Name used in standard Cross Organization Reporting. The Reporting Context name is taken from the submission parameter Reporting Context Name, and refers to Operating Unit, Legal Entity or Ledger. AR, AP, GL

Legal Entity

The Legal Entity columns provide the name, tax registration number, and address information for the legal entity. The Legal Entity columns are available at all summarization levels.

Attribute Name Column Name Description Application
Legal Entity Tax Registration Number LEGAL_ENTITY_TAX_REG_NUMBER The tax registration number for the legal entity as setup using the Additional Organization information for the Legal Entity business purpose of the organization. AR, AP, GL
Legal Entity Location ID REP_CONTEXT_ENTITY_LOCATION_ID The internal ID used to identify the HR_LOCATIONS record for the address of the legal entity reporting this transaction. AR, AP, GL
Legal Entity City REP_CONTEXT_ENTITY_CITY The City field of the address of the legal entity reporting this transaction. AR, AP, GL
Legal Entity County REP_COMTEXT_ENTITY_COUNTY The County field of the address of the legal entity reporting this transaction. AR, AP, GL
Legal Entity State REP_CONTEXT_ENTITY_STATE The State field of the address of the legal entity reporting this transaction. AR, AP, GL
Legal Entity Province REP_CONTEXT_ENTITY_PROVINCE The Province field of the address of the legal entity reporting this transaction. AR, AP, GL
Legal Entity Address 1 REP_CONTEXT_ENTITY_ADDRESS1 Address Line 1 of the legal entity reporting this transaction. AR, AP, GL
Legal Entity Address 2 REP_CONTEXT_ENTITY_ADDRESS2 Address Line 2 of the legal entity reporting this transaction. AR, AP, GL
Legal Entity Address 3 REP_CONTEXT_ENTITY_ADDRESS3 Address Line 3 of the legal entity reporting this transaction. AR, AP, GL
Legal Entity Country REP_CONTEXT_ENTITY_COUNTRY The Country Code of the address for the legal entity reporting this transaction. AR, AP, GL
Legal Entity Postal Code REP_CONTEXT_ENTITY_POSTAL_CODE The Postal Code field of the address for the legal entity reporting this transaction. AR, AP, GL
Legal Entity Telephone Number REP_CONTEXT_ENTITY_TEL_NUMBER The Telephone number field of the address for the legal entity reporting this transaction. AR, AP, GL

Transaction Header Level

The Transaction Header Level columns are common across all sources and are available at all summarization levels.

Attribute Name Column Name Description Application
Trans ID TRX_ID The Internal ID used with TRX_CLASS_CODE and REPORTING_SOURCE_LEDGER to identify the original transaction. AR, AP, GL
Transaction Number TRX_NUMBER The transaction number as reported in the Tax Register. This is the Invoice Number, Credit Memo Number, or Prepayment Number, depending upon the source of the transaction. AR, AP, GL
Trans Description TRX_DESCRIPTION Description of the transaction. AR, AP, GL
Trans Document Sequence ID DOC_SEQ_ID The internal ID of the sequence name used in the taxed transaction. AR, AP, GL
Trans Document Sequence Name DOC_SEQ_NAME The name of the sequence used in the taxed transaction. AR, AP, GL
Trans Document Sequence Value DOC_SEQ_VALUE The value of the document sequence number field. AR, AP, GL
Transaction Date TRX_DATE The transaction date of the transaction. AR, AP, GL
Trans Class Code TRX_LINE_CLASS Internal lookup code for the transaction class used by developers to identify the original document. AR, AP, GL
Transaction Class TRX_CLASS_MNG The printed transaction class. This is Invoice, Credit Memo, Debit Memo, Prepayment, Earned Discount, or Unearned Discount. AR, AP, GL
Trans Type ID TRX_TYPE_ID The internal identifier used to control the Receivables transaction type of the original Invoice, Debit Memo or Credit Memo. AR, AP
Trans Type TRX_TYPE_MNG The Receivables transaction type assigned to the transaction. AR, AP
Trans Applied From Trans Header Table APPLIED_FROM_ENTITY_CODE Internal table name. AR, AP
Trans Applied From Trans Header ID APPLIED_FROM_TRX_ID Internal unique identifier. AR, AP
Trans Applied From Trans Class Code APPLIED_FROM_EVENT_CLASS_CODE The internal lookup code used to control the Applied From document class. AR, AP
Trans Applied From Trans Class TRX_APPLIED_FROM_CLASS_MEANING The displayed transaction class for the Applied From document. For an earned or unearned discount this would be the cash receipt number. AR, AP
Trans Applied From Trans Number APPLIED_FROM_TRX_NUMBER The cash receipt number for receipt applications, earned and unearned discounts. AR, AP
Trans Applied To Trans Header Table APPLIED_TO_ENTITY_CODE The internal table name used to store the Applied To document. AR, AP
Trans Applied To Trans Header ID APPLIED_TO_TRX_ID The internal ID used to identify the Applied To document name. AR, AP
Related Transaction RELATED_DOC_NUMBER The transaction number for related documents. AR, AP
Trans Applied To Trans Class Code APPLIED_TO_EVENT_CLASS_CODE The event class code of the Applied To document. AR, AP
Trans Applied To Trans Class TRX_APPLIED_TO_CLASS_MNG The internal lookup code used to identify the transaction class of the Applied To document. AR, AP
Adjusted Doc Entity Code ADJUSTED_DOC_ENTITY_CODE The adjusted document entity code. AR, AP
Adjusted Doc Event Class Code ADJUSTED_DOC_EVENT_CLASS_CODE The adjusted document event class code. AR, AP
Adjusted Doc Trx ID ADJUSTED_DOC_TRX_ID The adjusted document transaction identifier. AR, AP
Trans Type Description TRX_TYPE_DESCRIPTION The description of the Receivables transaction type assigned to this transaction. AR, AP
Trans User Desc Flex Category Trans User Desc Flex Attribute 1–10

  • TRX_HEADER_CATEGORY

  • TRX_HEADER_ATTRIBUTE1–10

  • User Descriptive flexfield – Attribute Category

  • User Descriptive flexfield – 1 through 10

 Available in AR, AP, GL for Attributes 1 – 10
Trans User Desc Flex Attribute11– 15 TRX_HEADER_ATTRIBUTE11–15 User Descriptive flexfield – 11 through 15. Available in AR, AP for Attributes 11 – 15
Trans Terms Date TRX_DUE_DATE The Payment Due date of the invoice/terms. AR, AP
Trans Shipping Date TRX_SHIPPING_DATE The Shipping or Receipt date identified at the invoice header. AR, AP
Trans Communicated Date TRX_COMMUNICATED_DATE The date printed/received of the transaction. AR, AP, GL
Trans Currency Code TRX_CURRENCY_CODE The entered currency code of the transaction header. AR, AP, GL
Trans Currency Conversion Type CURRENCY_CONVERSION_TYPE The currency conversion type of the transaction. AR, AP, GL
Trans Currency Conversion Date CURRENCY_CONVERSION_DATE The currency conversion date of the transaction. AR, AP, GL
Trans Currency Conversion Rate CURRENCY_CONVERSION_RATE The currency conversion rate of the transaction. AR, AP, GL
Trans Control Account Flexfield TRX_CONTROL_ACCOUNT_FLEXFIELD The accounting flexfield of the Receivables or Payables control account. In General Ledger, this is the accounting flexfield of the accounting line that offsets the tax line. AR, AP, GL
Company BALANCING_SEGMENT_VALUE The balancing segment or company value taken from the Transaction Control Account flexfield. This is usually the company code associated with the transaction balancing segment. AR, AP, GL
Trans Control Balancing Segment Desc TRX_CONTROL_ACCOUNT_BS_DESC The description associated with the Transaction Control Balancing Segment flexfield segment value. This is usually the company name associated with the transaction balancing segment. AR, AP, GL
Ledger Name LEDGER_NAME The ledger name. AR, AP, GL

Billing

The Billing columns provide trading partner information and the name and address for billing functions. These columns are available at all summarization levels.

Attribute Name Column Name Description Application
Billing Partner ID BILLING_TRADING_PARTNER_ID The internal Identifier of the trading partner (customer or supplier) that is used to bill or pay this transaction. AR, AP
Billing Partner Site ID BILLING_TP_SITE_ID The internal identifier of the trading partner site (customer site or supplier site) that is used to bill or pay this transaction. AR, AP
Billing Partner Number BILLING_TP_NUMBER The customer or supplier number. AR, AP, GL
Tax Registration Number BILLING_TP_TAX_REG_NUM The tax registration number of the trading partner that is either billing or paying this transaction. AR, AP, GL
Billing Partner Taxpayer ID BILLING_TP_TAXPAYER_ID The taxpayer ID of the billing or paying trading partner. AR, AP
Billing Partner Site Name BILLING_TP_SITE_NAME The name given to the billing or paying trading partner site. AR, AP
Billing Partner Site Alternate Name BILLING_TP_SITE_NAME_ALT The alternate name given to the billing or paying trading partner site.  
Billing Partner Site Tax Registration Number BILLING_TP_SITE_TAX_REG_NUM The tax registration number for the billing or paying trading partner. AR, AP
Billing Partner Income Tax Reporting Flag BILLING_TP_TAX_REPORTING_FLAG Identifies whether this site is used for Income Tax reporting. AR, AP
Billing Partner Name BILLING_TP_NAME Trading partner name. For Receivables, this is the Bill To customer name; for Payables this is the supplier name. AR, AP, GL
Billing Partner Alternate Name BILLING_TP_NAME_ALT Alternative or phonetic name of the trading partner. AR, AP
Billing Partner S.I.C. Code BILLING_TP_SIC_CODE SIC Code of the trading partner. AR, AP
Billing Partner Address ID BILLING_TP_ADDRESS_ID The internal ID of the customer address or supplier site. AR, AP
Billing Partner City BILLING_TP_CITY City field of the billing trading partner address. AR, AP
Billing Partner County BILLING_TP_COUNTY County field of the billing trading partner address. AR, AP
Billing Partner State BILLING_TP_STATE State field of the billing trading partner address. AR, AP
Billing Partner Province BILLING_TP_PROVINCE Province field of the billing trading partner address. AR, AP
Billing Partner Address 1 BILLING_TP_ADDRESS1 Address line 1 of the billing trading partner address. AR, AP
Billing Partner Address 2 BILLING_TP_ADDRESS2 Address line 2 of the billing trading partner address. AR, AP
Billing Partner Address 3 BILLING_TP_ADDRESS3 Address line 3 of the billing trading partner address. AR, AP
Billing Partner Address Alternate Lines BILLING_TP_ADDRESS_LINES_ALT The alternate address lines of the billing trading partner. AR, AP
Billing Partner Country BILLING_TP_COUNTRY Country field of the billing trading partner address. AR, AP
Billing Partner Postal Code BILLING_TP_POSTAL_CODE Postal Code field of the billing trading partner address. AR, AP

Shipping

The Shipping columns provide the name and address for the shipping function in Oracle Payables; however, no shipping information is recorded. The Shipping columns are available at all summarization levels.

Attribute Name Column Name Description Application
Shipping Partner ID SHIPPING_TRADING_PARTNER_ID The Ship To customer internal ID. Used only for Receivables transactions. AR, AP
Shipping Partner Site ID SHIPPING_TP_SITE_ID The Ship To customer site internal ID. Used only for Receivables transactions. AR, AP
Shipping Partner Number SHIPPING_TP_NUMBER The Ship To customer name. Used only for Receivables transactions. AR, AP
Shipping Partner Tax Registration Number SHIPPING_TP_TAX_REG_NUM The tax registration number of the Ship To customer. Used only for Receivables transactions. AR, AP
Shipping Partner Taxpayer ID SHIPPING_TP_TAXPAYER_ID The taxpayer ID of the Ship To customer. Used only for Receivables transactions. AR, AP
Shipping Partner Site Name SHIPPING_TP_SITE_NAME The site name of the shipping trading partner. AR, AP
Shipping Partner Site Alternate Name SHIPPING_TP_SITE_NAME_ALT The alternate name given to the shipping or paying trading partner site.  
Shipping Partner Site Tax Registration Number SHIPPING_TP_SITE_TAX_REG_NUM The tax registration number of the Ship To customer site. Used only for Receivables transactions. AR, AP
Shipping Partner Name SHIPPING_TP_NAME The Ship To customer site name. Used only for Receivables transactions. AR, AP
Shipping Partner Alternate Name SHIPPING_TP_NAME_ALT The alternative or phonetic name of the Ship To customer name. Used only for Receivables transactions. AR, AP
Shipping Partner S.I.C. Code SHIPPING_TP_SIC_CODE The SIC code of the Ship to customer. Used only for Receivables transactions. AR, AP
Shipping Partner Address ID SHIPPING_TP_ADDRESS_ID The internal identifier of the Ship to customer address. Used only for Receivables transactions. AR, AP
Shipping Partner City SHIPPING_TP_CITY The City field of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner County SHIPPING_TP_COUNTY The County field of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner State SHIPPING_TP_STATE The State field of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner Province SHIPPING_TP_PROVINCE The Province field of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner Address 1 SHIPPING_TP_ADDRESS1 Address line 1 of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner Address 2 SHIPPING_TP_ADDRESS2 Address line 2 of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner Address 3 SHIPPING_TP_ADDRESS3 Address line 3 of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner Address Alternate Lines SHIPPING_TP_ADDRESS_LINES_ALT The alternate address lines of the shipping trading partner. AR, AP
Shipping Partner Country SHIPPING_TP_COUNTRY The Country field of the Ship To customer address. Used only for Receivables transactions. AR, AP
Shipping Partner Postal Code SHIPPING_TP_POSTAL_CODE The Postal Code field of the Ship To customer address. Used only for Receivables transactions. AR, AP

Banking

Trading partner columns provide the name and address for banking functions and are available at all summarization levels.

Attribute Name Column Name Description Applications
Banking Partner Name BANKING_TP_NAME The bank name of the remittance bank account. Only populated for Miscellaneous Payments and Miscellaneous Receipts in Oracle Receivables. AR
Banking Partner Taxpayer ID BANKING_TP_TAXPAYER_ID The Taxpayer ID field of the remittance bank account. Only populated for Miscellaneous Payments and Miscellaneous Receipts in Oracle Receivables. AR

Receivables Transaction

These columns provide Oracle Receivables-specific header level transaction information and are available at all summarization levels.

Attribute Name Column Name Description Application
AR Trans Batch Source Name TRX_BATCH_SOURCE_NAME The Receivables batch source name used to create the original transaction. AR
AR Trans Batch Source ID TRX_BATCH_SOURCE_ID The internal identifier of the Receivables batch source name used to create the original transaction. AR
AR Trans S.I.C. Code TRX_SIC_CODE The SIC Code of the Receivables transaction header. AR
AR Trans FOB Point FOB_POINT The FOB (terms of delivery) of the Receivables transaction header. AR
AR Trans WayBill Number TRX_WAYBILL_NUMBER Waybill number of the Receivables transaction header. AR

Taxable Amounts

These columns provide the taxable amount for this tax ledger line and are available at all summarization levels.

Note: The taxable amount for the recoverable tax is held in Taxable1 and the taxable amount for the nonrecoverable tax is held in Taxable2. If the invoice had a second tax, the taxable amount for the recoverable tax is held in Taxable3 and the taxable amount for the nonrecoverable tax is held in Taxable4.

Attribute Name Column Name Description Application
Taxable Amount Entered Currency TAXABLE_AMT For Output Tax this is TAXABLE_ENTERED_CR – TAXABLE_ENTERED_DR.
For Input Tax this is TAXABLE_ENTERED_DR – TAXABLE_ENTERED_CR.		 AR, AP, GL
Taxable Amount TAXABLE_AMT_FUNCL_CURR For Output Tax this is TAXABLE_ACCOUNTED_CR – TAXABLE_ACCOUNTED_DR.
For Input Tax this is TAXABLE_ACCOUNTED_DR – TAXABLE_ACCOUNTED_CR.		 AR, AP, GL
Taxable Disc Amt TAXABLE_DISC_AMT Taxable discount amount in the transaction currency. AP
Taxable Disc Amt Funcl Curr TAXABLE_DISC_AMT_FUNCL_CURR Taxable discount amount in the ledger currency. AP
Taxable1 Amount Entered Currency TAXABLE1_AMT For Output Tax this is TAXABLE_ENTERED_CR – TAXABLE_ENTERED_DR.
For Input Tax this is: TAXABLE_ENTERED_DR – TAXABLE_ENTERED_CR.		 AR, AP
Taxable1 Amount Accounted TAXABLE1_AMT_FUNCL_CURR For Output Tax this is TAXABLE_ACCOUNTED_CR – TAXABLE_ACCOUNTED_DR.
For Input Tax this is TAXABLE_ACCOUNTED_DR – TAXABLE_ACCOUNTED_CR.		 AR, AP
Taxable2 Amount Entered Currency TAXABLE2_AMT For Output Tax this is TAXABLE_ENTERED_CR – TAXABLE_ENTERED_DR.
For Input Tax this is TAXABLE_ENTERED_DR – TAXABLE_ENTERED_CR.		 AR, AP
Taxable2 Amount Accounted TAXABLE2_AMT_FUNCL_CURR For Output Tax this is TAXABLE_ACCOUNTED_CR – TAXABLE_ACCOUNTED_DR.
For Input Tax Codes this is TAXABLE_ACCOUNTED_DR – TAXABLE_ACCOUNTED_CR.		 AR, AP
Taxable3 Amount Entered Currency TAXABLE3_AMT For Output Tax this is TAXABLE_ENTERED_CR – TAXABLE_ENTERED_DR.
For Input Tax this is TAXABLE_ENTERED_DR – TAXABLE_ENTERED_CR.		 AR, AP
Taxable3 Amount Accounted TAXABLE3_AMT_FUNCL_CURR For Output Tax codes this is TAXABLE_ACCOUNTED_CR – TAXABLE_ACCOUNTED_DR.
For Input Tax this is TAXABLE_ACCOUNTED_DR – TAXABLE_ACCOUNTED_CR.		 AR, AP
Taxable4 Amount Entered Currency TAXABLE4_AMT For Output Tax this is TAXABLE_ENTERED_CR – TAXABLE_ENTERED_DR.
For Input Tax this is TAXABLE_ENTERED_DR – TAXABLE_ENTERED_CR.		 AR, AP
Taxable4 Amount Accounted TAXABLE4_AMT_FUNCL_CURR For Output Tax this is TAXABLE_ACCOUNTED_CR – TAXABLE_ACCOUNTED_DR.
For Input Tax this is TAXABLE_ACCOUNTED_DR – TAXABLE_ACCOUNTED_CR.		 AR, AP

Taxable Line Fields

The Taxable Line fields provide transaction line-level information. These columns are available at the transaction line level.

Attribute Name Column Name Description Application
Taxable Line Number TRX_LINE_NUMBER The line number of the taxable line. This is only available for Line and Distribution level reporting. Otherwise this column is null. AR, AP, GL
Taxable Line Source Table TAXABLE_LINE_SOURCE_TABLE Identifies the Taxable Source table name. This column is only available for Line and Distribution reports. Requested by Latin America to allow extensible reporting of the GDF and other attributes of the taxable line. AR, AP
Taxable Line Source ID TAXABLE_ITEM_SOURCE_ID Identifies the row within the Taxable Source table. This column is only available for Line and Distribution level reports. AR, AP
Taxable Line Description TRX_LINE_DESCRIPTION The line description used to document the original taxable document line. This column is only available for Line and Distribution level reports. For Receivables invoices, the value comes from the Invoice Line table. For Payables invoices, the value comes from the Distribution Item description. AR, AP, GL
Taxable Inventory Item ID PRODUCT_ID The internal ID of the Inventory item used on this invoice line. For Receivables invoices, the value comes from the Invoice Line table. AR
Taxable Inventory Item Flexfield TAXABLE_LINE_ITEM_FLEXFIELD The description of the Inventory item used on this invoice line. For Receivables invoices, the value comes from the Invoice Line table. AR
Taxable Inventory Item Desc PRODUCT_DESCRIPTION The description of the Inventory item used on this invoice line. For Receivables invoices, the value comes from the Invoice Line table. Available in Line and Distribution level reports only. AR
Taxable Line Type Code TRX_LINE_TYPE The internal lookup code for the Receivables or Payables line type of the taxable document line. Available in Line and Distribution level reports only. AR, AP
Taxable Line Type TAXABLE_LINE_TYPE_MEANING The taxable line type of the Receivables or Payables line. Available in Line and Distribution level reports only. AR, AP
Trx Line Quantity TRX_LINE_QUANTITY Transaction line quantity. AR, AP
UOM Code UOM_CODE Unit of Measure code. AR, AP
Trx Quantity UOM Meaning TRX_QUANTITY_UOM_MNG Unit of Measure quantity meaning. AR, AP

Tax Amounts

The Tax Amounts columns provide the tax amount that is stored in the database. When the report is run at the transaction line level, the report sums multiple lines that have the same tax. These columns are available at all summarization levels.

Note: For partially recoverable tax in Oracle Payables, the recoverable tax is held in Tax1 and the nonrecoverable tax is held in Tax2. If the invoice had a second tax, the recoverable tax for the second tax is held in Tax3 and the nonrecoverable tax for the second tax is held in Tax4.

Attribute Name Column Name Description Application
Tax Amount Entered Currency TAX_AMT For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP, GL
Tax Amount TAX_AMT_FUNCL_CURR For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP, GL
Tax1 Amount Entered Currency TAX1_AMT For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax1 Amount Accounted TAX1_AMT_FUNCL_CURR For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax2 Amount Entered Currency TAX2_AMT For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax2 Amount Accounted TAX2_AMT_FUNCL_CURR For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax3 Amount Entered Currency TAX3_AMT For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax3 Amount Accounted TAX3_AMT_FUNCL_CURR For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax4 Amount Entered Currency TAX4_AMT_FUNCL_CURR For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP
Tax4 Amount Accounted TAX4_AMT For Output Tax this is TAX_ENTERED_CR – TAX_ENTERED_DR.
For Input Tax this is TAX_ENTERED_DR – TAX_ENTERED_CR.		 AR, AP

Tax Line

The Tax Line columns provide tax amounts, taxes and rates, and other attributes taken from the document line of type Tax. These columns are available at all summarization levels.

Attribute Name Column Name Description Application
Tax Line Number TAX_LINE_NUMBER The line number for multiple taxes on an invoice. The line number identifies that a given tax is part of a multiple tax. This is available in Transaction Line level and Distribution Line level reporting only. Otherwise, this column is null. AR, AP
Tax Line Tax Code ID TAX_RATE_ID The internal ID of either the Input or Output tax rate code used on this transaction. AR, AP, GL
Tax Line Tax Code TAX_RATE_CODE The tax rate code used on this transaction. AR, AP, GL
Tax Line Tax Code Rate TAX_RATE The tax rate setup for this tax rate code. This rate may not be the effective tax rate of the transaction line if limits or exceptions apply. AR, AP, GL
Tax Line Tax Code VAT Trans Type Code TAX_RATE_VAT_TRX_TYPE_CODE The VAT transaction type internal lookup code for this tax rate code. AR, AP, GL
Tax Line Tax Code VAT Trans Type TAX_RATE_VAT_TRX_TYPE_MNG The VAT transaction type displayed value for this tax rate code. AR, AP, GL
Tax Line Tax Code VAT Trans Type Desc TAX_RATE_VAT_TRX_TYPE_DESC The VAT transaction type description for this tax rate code.  
Tax Line Tax Code Register Type Code TAX_RATE_REGISTER_TYPE_CODE The internal lookup code for the tax register for this tax register line. AR, AP, GL
Tax Line Tax Code Register Type TAX_RATE_CODE_REG_TYPE_MEANING The displayed value for the tax register for this tax register line:

  • Interim Input Tax Register

  • Input Tax Register

  • Output Tax Register

  • Nonrecoverable Input Tax Register

  • Nonrecoverable Output Tax Register.

 AR, AP, GL
Tax Line Tax Code Type Code TAX_TYPE_CODE The internal lookup code of the tax type for this tax rate code, based on the extensible lookup AR: TAX_TYPE. Used to provide summary totals of taxes by tax regime. AR, AP, GL
Tax Line Tax Code Type TAX_TYPE_MNG The displayed value of the tax type for this tax rate code. Used to provide summary totals of taxes by tax regime. AR, AP, GL
Tax Code TAX_RATE_CODE_NAME The printed tax name for this tax rate code. AR, AP, GL
Tax Line Tax Code Description TAX_RATE_CODE_DESCRIPTION The description for this tax rate code. AR, AP, GL
Posted Flag POSTED_FLAG Posted flag. AP
AP Tax Line Tax Recovery Flag TAX_RECOVERABLE_FLAG Recoverable flag. AP
Reconciliation Flag RECONCILIATION_FLAG Reconciliation flag. AR
Tax1 Line Number TAX1_LINE_NUMBER The line number for multiple taxes on an invoice. Available in Transaction Line level and Distribution Line level reporting only. Otherwise this column is null. AR
Tax1 Line Tax Code ID TAX1_RATE_CODE_ID The internal ID of either the Input or Output tax rate code used on this transaction. AR, AP
Tax1 Line Tax Class Code TAX1_CODE_TAX_CLASS_CODE Either I (Input) or O (Output), used internally to identify the tax code class. This information is already available via the tax line tax code register type. AR, AP, GL
Tax1 Line Tax Class TAX1_CODE_TAX_CLASS_MEANING The displayed tax code class (Input or Output). AR, AP, GL
Tax1 Line Tax Code TAX1_RATE_CODE The tax rate code used on this transaction. AR, AP
Tax1 Line Tax Code Rate TAX1_RATE The tax rate setup for this tax rate code. This rate may not be the effective tax rate of the transaction line if limits or exceptions apply. AR, AP
Tax1 Line Effective Tax Rate TAX1_LINE_EFFECTIVE_TAX_RATE The effective tax rate for this transaction line. AR, AP
Tax1 Line Tax Code VAT Trans Type Code TAX1_RATE_VAT_TRX_TYPE_CODE The VAT transaction type internal lookup code for this tax rate code. AR, AP
Tax1 Line Tax Code VAT Trans Type TAX1_RATE_VAT_TRX_TYPE_MNG The VAT transaction type displayed value for this tax rate code. AR, AP
Tax1 Line Tax Code VAT Trans Type Desc TAX1_RATE_VAT_TRX_TYPE_DESC The VAT transaction type description for this tax rate code.  
Tax1 Line Tax Code Register Type Code TAX1_RATE_CODE_REG_TYPE_CODE The internal lookup code for the tax register for this tax register line. AR, AP
Tax1 Line Tax Code Register Type TAX1_RATE_CODE_REG_TYPE_MNG The displayed value for the tax register for this tax register line:

  • Interim Input Tax Register

  • Input Tax Register

  • Output Tax Register

  • Nonrecoverable Input Tax Register

  • Nonrecoverable Output Tax Register.

 AR, AP
Tax1 Line Tax Code Type Code TAX1_RATE_CODE_TYPE_CODE The internal lookup code of the tax type for this tax rate code, based on the extensible lookup AR: TAX_TYPE. Used to provide summary totals of taxes by tax regime. AR, AP
Tax1 Line Tax Code Type TAX1_RATE_CODE_TYPE_MNG The displayed value for the tax type for this tax rate code. Used to provide summary totals of taxes by tax regime. AR, AP
Tax1 Line Tax Code Name TAX1_RATE_CODE_NAME The printed tax name of this tax rate code. AR, AP
Tax1 Line Tax Code Description TAX1_RATE_CODE_DESCRIPTION The description of this tax rate code. AR, AP
Tax1 Line Recoverable Flag TAX1_RECOVERABLE_FLAG Recoverable flag. AP
Tax2 Line Number TAX2_LINE_NUMBER The line number for multiple taxes on an invoice. Available in Transaction Line level and Distribution Line level reporting only. Otherwise this column is null. AR
Tax2 Line Tax Code ID TAX2_RATE_CODE_ID The internal ID of either the Input or Output tax rate used on this transaction. AR, AP
Tax2 Line Tax Class Code TAX2_CODE_TAX_CLASS_CODE Either I (Input) or O (Output), used internally to identify the tax code class. This information is already available via the tax line tax code register type. AR, AP, GL
Tax2 Line Tax Class TAX2_CODE_TAX_CLASS_MEANING The displayed tax code class (Input or Output). AR, AP, GL
Tax2 Line Tax Code TAX2_RATE_CODE The tax rate code used on this transaction. AR, AP
Tax2 Line Tax Code Rate TAX2_RATE The tax rate setup for this tax rate code. This rate may not be the effective tax rate of the transaction line if limits or exceptions apply. AR, AP
Tax2 Line Effective Tax Rate TAX2_LINE_EFFECTIVE_TAX_RATE The effective tax rate for this transaction line. AR, AP
Tax2 Line Tax Code VAT Trans Type Code TAX2_RATE_VAT_TRX_TYPE_CODE The VAT transaction type internal lookup code for this tax rate code. AR, AP
Tax2 Line Tax Code VAT Trans Type TAX2_RATE_VAT_TRX_TYPE_MNG The VAT transaction type displayed value for this tax rate code. AR, AP
Tax2 Line Tax Code VAT Trans Type Desc TAX2_RATE_VAT_TRX_TYPE_DESC The VAT transaction type description for this tax rate code.  
Tax2 Line Tax Code Register Type Code TAX2_RATE_CODE_REG_TYPE_CODE The internal lookup code for the tax register for this tax register line. AR, AP
Tax2 Line Tax Code Register Type TAX_RATE_CODE_REG_TYPE_MNG The displayed value for the tax register for this tax register line:

  • Interim Input Tax Register

  • Input Tax Register

  • Output Tax Register

  • Nonrecoverable Input Tax Register

  • Nonrecoverable Output Tax Register.

 AR, AP
Tax2 Line Tax Code Type Code TAX2_RATE_CODE_TYPE_CODE The internal lookup code of the tax type for this tax rate code, based on the extensible lookup AR: TAX_TYPE. Used to provide summary totals of taxes by tax regime. AR, AP
Tax2 Line Tax Code Type TAX2_RATE_CODE_TYPE_MNG The displayed value for the tax type for this tax rate code. Used to provide summary totals of taxes by tax regime. AR, AP
Tax2 Line Tax Code Name TAX2_RATE_CODE_NAME The printed tax name for this tax rate code. AR, AP
Tax2 Line Tax Code Description TAX2_RATE_CODE_DESCRIPTION The description for this tax rate code. AR, AP
Tax2 Line Recoverable Flag TAX2_RECOVERABLE_FLAG Recoverable flag AP
Tax3 Line Tax Code ID TAX3_RATE_CODE_ID The internal ID of either the Input or Output tax rate code used on this transaction. AR, AP
Tax3 Line Tax Class Code TAX3_CODE_TAX_CLASS_CODE Either I (Input) or O (Output), used internally to identify the tax code class. This information is already available via the tax line tax code register type. AR, AP, GL
Tax3 Line Tax Class TAX3_CODE_TAX_CLASS_MEANING The displayed tax code class (Input or Output). AR, AP, GL
Tax3 Line Tax Code TAX3_RATE_CODE The tax rate code used on this transaction. AR, AP
Tax3 Line Tax Code Rate TAX3_RATE The tax rate setup for this tax rate code. This rate might not be the effective tax rate of the transaction line if limits or exceptions apply. AR, AP
Tax3 Line Effective Tax Rate TAX3_LINE_EFFECTIVE_TAX_RATE The effective tax rate for this transaction line. AR, AP
Tax3 Line Tax Code VAT Trans Type Code TAX3_RATE_VAT_TRX_TYPE_CODE The VAT transaction type internal lookup code for this tax rate code. AR, AP
Tax3 Line Tax Code VAT Trans Type TAX3_RATE_VAT_TRX_TYPE_MNG The VAT transaction type displayed value for this tax rate code. AR, AP
Tax3 Line Tax Code VAT Trans Type Desc TAX3_RATE_VAT_TRX_TYPE_DESC The VAT transaction type description for this tax rate code.  
Tax3 Line Tax Code Register Type Code TAX3_RATE_CODE_REG_TYPE_CODE The internal lookup code for the tax register for this tax register line. AR, AP
Tax3 Line Tax Code Register Type TAX3_RATE_CODE_REG_TYPE_MNG The displayed value for the tax register for this tax register line:

  • Interim Input Tax Register

  • Input Tax Register

  • Output Tax Register

  • Nonrecoverable Input Tax Register

  • Nonrecoverable Output Tax Register.

 AR, AP
Tax3 Line Tax Code Type Code TAX3_RATE_CODE_TYPE_CODE The internal lookup code of the tax type for this tax rate code, based on the extensible lookup AR: TAX_TYPE. Used to provide summary totals of taxes by tax regime. AR, AP
Tax3 Line Tax Code Type TAX3_RATE_CODE_TYPE_MNG The displayed value for the tax type for this tax rate code. Used to provide summary totals of taxes by tax regime. AR, AP
Tax3 Line Tax Code Name TAX3_RATE_CODE_NAME The printed tax name for this tax rate code. AR, AP
Tax3 Line Tax Code Description TAX3_RATE_CODE_DESCRIPTION The description for this tax rate code. AR, AP
Tax3 Line Recoverable Flag TAX3_RECOVERABLE_FLAG Recoverable flag. AP
Tax3 Line Number TAX3_LINE_NUMBER The line number for multiple taxes on an invoice. This is available in Transaction Line level and Distribution Line level reporting only. Otherwise this column is null. AR
Tax4 Line Tax Code ID TAX4_RATE_CODE_ID The internal ID of either the Input or Output tax rate code used on this transaction. AR, AP
Tax4 Line Tax Class Code TAX4_CODE_TAX_CLASS_CODE Either I (Input) or O (Output), used internally to identify the tax code class. This information is already available via the tax line tax code register type. AR, AP, GL
Tax4 Line Tax Class TAX4_CODE_TAX_CLASS_MEANING The displayed tax code class (Input or Output). AR, AP, GL
Tax4 Line Tax Code TAX4_RATE_CODE The tax rate code used on this transaction. AR, AP
Tax4 Line Tax Code Rate TAX4_RATE The tax rate setup for this tax rate code. This rate may not be the effective tax rate of the transaction line if limits or exceptions apply. AR, AP
Tax4 Line Effective Tax Rate TAX4_LINE_EFFECTIVE_TAX_RATE The effective tax rate for this transaction line. AR, AP
Tax4 Line Tax Code VAT Trans Type Code TAX4_RATE_VAT_TRX_TYPE_CODE The VAT transaction type internal lookup code for this tax rate code. AR, AP
Tax4 Line Tax Code VAT Trans Type TAX4_RATE_VAT_TRX_TYPE_MNG The VAT transaction type displayed value for this tax rate code. AR, AP
Tax4 Line Tax Code VAT Trans Type Desc TAX4_RATE_VAT_TRX_TYPE_DESC The VAT transaction type description for this tax rate code.  
Tax4 Line Tax Code Register Type Code TAX4_RATE_CODE_REG_TYPE_CODE The internal lookup code for the tax register for this tax register line. AR, AP
Tax4 Line Tax Code Register Type TAX4_RATE_CODE_REG_TYPE_MNG The displayed value for the tax register for this tax register line:

  • Interim Input Tax Register

  • Input Tax Register

  • Output Tax Register

  • Nonrecoverable Input Tax Register

  • Nonrecoverable Output Tax Register.

 AR, AP
Tax4 Line Tax Code Type Code TAX4_RATE_CODE_TYPE_CODE The internal lookup code of the tax type for this tax rate code, based on the extensible lookup AR: TAX_TYPE. Used to provide summary totals of taxes by tax regime. AR, AP
Tax4 Line Tax Code Type TAX4_RATE_CODE_TYPE_MNG The displayed value for the tax type for this tax rate code. Used to provide summary totals of taxes by tax regimes. AR, AP
Tax4 Line Tax Code Name TAX4_RATE_CODE_NAME The printed tax name of this tax rate code. AR, AP
Tax4 Line Tax Code Description TAX4_RATE_CODE_DESCRIPTION The description of this tax rate code. AR, AP
Tax4 Recoverable Flag TAX4_RECOVERABLE_FLAG Recoverable flag. AP
Tax4 Line Number TAX4_LINE_NUMBER The line number for multiple taxes on an invoice. This is available in Transaction Line level and Distribution Line level reporting only. Otherwise, this column is null. AR

Payables Tax Line

These columns provide Payables-specific attributes from the tax document line. These columns are only available at the distribution line level.

Attribute Name Column Name Description Application
AP Tax Line Tax Recovery Rate TAX_RECOVERY_RATE Records the tax recovery rate for the taxable amount on this tax reporting line. AP
AP Tax Line Merchant Document Number MERCHANT_PARTY_DOCUMENT_NUMBER The merchant document number recorded on the Payables expense report. AP
AP Tax Line Merchant Name MERCHANT_PARTY_NAME The merchant name recorded on the Payables expense report. AP
AP Tax Line Merchant Reference MERCHANT_PARTY_REFERENCE The merchant reference recorded on the Payables expense report. AP
AP Tax Line Merchant Tax Registration Num MERCHANT_PARTY_TAX_REG_NUMBER The merchant tax registration number recorded on the Payables expense report. AP
AP Tax Line Merchant Taxpayer ID MERCHANT_PARTY_TAXPAYER_ID The Taxpayer ID recorded on the Payables expense report. AP
AP Tax Line Country of Supply COUNTRY_OF_S UPPLY The first country of supply recorded on the Payables taxable distribution line. If this taxable amount records multiple values for country of supply, only the first value is shown. AP
Start Expense Date START_EXPENSE_DATE Start expense date. AP

Receivables Tax Line

These columns provide Receivables-specific attributes from the tax document line. These columns are only available at the transaction line and distribution levels.

Attribute Name Column Name Description Application
AR Tax Line Tax Exemption ID TAX_EXEMPTION_ID The Receivables tax exemption ID that was used in the calculation of the effective tax rate. AR
AR Tax Line Tax Exception ID TAX_EXCEPTION_ID The Receivables tax exception ID that was used in the calculation of the effective tax rate. AR
AR Tax Line Tax Exempt Number EXEMPT_CERTIFICATE_NUMBER The Receivables tax exemption number that was used in the calculation of the effective tax rate. AR
AR Tax Line Tax Exempt Reason Code EXEMPT_REASON_CODE The Receivables tax exemption reason lookup code used internally in the calculation of the effective tax rate. AR
AR Tax Line Tax Exempt Reason TAX_EXEMPT_REASON_MNG The displayed field for the tax exemption reason used in the calculation of the effective tax rate. AR
AR Tax Line Tax Exception Reason Code TAX_EXCEPTION_REASON_CODE The Receivables tax exception reason lookup code used internally in the calculation of the effective tax rate. AR
AR Tax Line Tax Exception Reason TAX_EXCEPTION_REASON_MNG The displayed field for the tax exception reason. AR

Transaction Line User Descriptive Flexfield

These columns provide the user descriptive flexfield values for the transaction line. These columns are available at the transaction line and distribution line levels in Oracle Receivables. These columns are available only at the distribution line level in Oracle Payables.

Attribute Name Column Name Description Application
Tax Line User Desc Flex Category Tax Line User Desc Flex Attribute 1–15 TAX_LINE_USER_CATEGORY TAX_LINE_USER_ATTRIBUTE1–15 Each of these values is included in the Group By criteria when adding taxable/tax lines. AR, AP, GL
Numeric Attributes NUMERIC1-16 Numeric attributes use to populate additional numeric data. AR, AP, GL
Attributes ATTRIBUTE1-18 Attribute columns use to populate additional character data. AR, AP, GL

Accounting

The Accounting columns provide accounting details for either taxable or tax accounting entries. These columns are available only at the transaction distribution level.

Attribute Name Column Name Description Application
Acct Event ID ACTG_EVENT_ID The internal ID of the accounting event. AR, AP
Acct Event Type Code ACTG_EVENT_TYPE_CODE The accounting event type lookup code. Examples include Invoice Creation, Invoice Adjustment, Payment Creation, and Payment Adjustment. AR, AP
Acct Event Type ACTG_EVENT_TYPE_MNG The displayed accounting event type. Examples include Invoice Creation, Invoice Adjustment, Payment Creation, and Payment Adjustment. AR, AP
Acct Event Number ACTG_EVENT_NUMBER The number of the accounting event within the document (for example: 1, 2). AR, AP
Acct Event Status Code ACTG_EVENT_STATUS_FLAG The lookup code for the status of the accounting event. Examples include Error and Accounted. The tax extract will only collect accounted taxed transactions. AR, AP
Acct Event Status ACTG_EVENT_STATUS_MNG For example: Error; Accounted. AR, AP
Acct Event Source Table ACTG_SOURCE_TABLE The table alias of the transaction table that is the source for this accounting event (for example, CR for AR_CASH_RECEIPTS). AR, AP
Acct Event Source ID ACTG_SOURCE_ID The ID of the transaction table that is the source for this accounting event (for example, 100002 for the value of INVOICE_ID). AR, AP
Acct Event Header ID ACTG_HEADER_ID Internal ID. AR, AP
Acct Event Category Code ACTG_CATEGORY_CODE The lookup code of the category of the accounting entry in the subledger. AR, AP
Acct Event Category ACTG_CATEGORY_MNG The displayed accounting event of the subledger accounting entry. AR, AP
Acct Period Name PERIOD_NAME The accounting period for the accounting entry. AR, AP
Acct Date ACCOUNTING_DATE The accounting date for the accounting entry (this value defaults from the accounting event). AR, AP
Acct GL Transfer Flag GL_TRANSFER_FLAG Indicates whether transfer to General Ledger has occurred. AR, AP
Acct GL Transfer Run ID GL_TRANSFER_RUN_ID This is a unique internal value assigned to a set of accounting entries that are transferred to General Ledger in one transfer request. If the entry has not been transferred, this column has a value of –1. AR, AP
Acct Header Description ACTG_HEADER_DESCRIPTION Accounting entry header description AR, AP
Acct Line Number ACTG_LINE_NUM Sequential number for the accounting line within the accounting entry (for example: 1, 2, 3, 4). AR, AP
Acct Line Type Code ACTG_LINE_TYPE_CODE The internal lookup code for the line type of the accounting line (for example: Liability, Charge, IPV, Exchange Rate Variance Gain, Exchange Rate Variance Loss, Tax, Freight, Cash, or Discount). AR, AP
Acct Line Type ACTG_LINE_TYPE_MNG The displayed line type of the accounting line (for example: Liability, Charge, IPV, Exchange Rate Variance Gain, Exchange Rate Variance Loss, Tax, Freight, Cash, or Discount). AR, AP
Acct Line Code Combination ID ACTG_LINE_CCID The code combination ID. AR, AP, GL
Acct Line Account Flexfield ACCOUNT_FLEXFIELD The Accounting Flexfield concatenated segments (for example: 01–100–1000–1000). AR, AP, GL
Acct Line Account Description ACTG_LINE_DESCRIPTION The Accounting Flexfield concatenated segment descriptions. AR, AP, GL
Account Description ACCOUNT_DESCRIPTION The Accounting Flexfield concatenated segment descriptions. AR, AP
Acct Line Statistical Amount ACTG_STAT_AMT If statistical amounts are enabled in your system, enter that amount in this column. This can be used for barrels of oil or any related numeric value.

  • AP Source: ra_distributions.stat_amount

  • GL Source: gl_lines.stat_amount

  • AR Source: null.

 AR, AP
Acct Line Error Code ACTG_ERROR_CODE Holds the error that occurred for the accounting line while creating the accounting entry. AR, AP
Acct Line GL Transfer Code GL_TRANSFER_CODE Holds the error that occurred for the accounting line while transferring to General Ledger. AR, AP
Acct Line Sub ledger Document Sequence ID ACTG_DOC_SEQUENCE_ID The internal ID of the document sequence number, if applicable. AR, AP
Acct Line Sub Ledger Document Sequence Name ACTG_DOC_SEQUENCE_NAME The displayed document sequence name, if applicable. AR, AP
Acct Line Sub Ledger Document Sequence Value ACTG_DOC_SEQUENCE_VALUE The value of the document sequence name. AR, AP
Acct Line Third Party ID ACTG_PARTY_ID Supplier ID or customer ID. AR, AP
Acct Line Third Party Site ID ACTG_PARTY_SITE_ID Supplier site ID or customer site ID. AR, AP
Trans Control Account Balancing Segment TRX_ARAP_BALANCING_SEGMENT The Balancing Segment value for each transaction class. This is the Liability account for AP Invoices, AP Prepayments, and AP Credit Memos; and the Receivables account for AR Invoices, credit/debit memos, discounts, and cash applications. For GL manual journals, this is the account of the opposite sign to the tax entry. This might be implemented as the Company field of any tax audit report. AR, AP
Trans Control Account Natural Account TRX_ARAP_NATURAL_ACCOUNT The Natural Account Segment value for each transaction class. This is the Liability account for AP Invoices, AP Prepayments, and AP Credit Memos; and the Receivables account for AR Invoices, credit/debit memos, discounts, and cash applications. For GL manual journals, this is the account of the opposite sign to the tax entry. This might be implemented as either the Receivables or Payables account of any tax audit report. AR, AP
Taxable Balancing Segment TRX_TAXABLE_BALANCING_SEGMENT The comma separated balancing segments of the accounting flexfields that were posted as part of the taxable accounting lines. AR, AP
Taxable Natural Account TRX_TAXABLE_NATURAL_ACCOUNT The comma separated natural account segment values of the accounting flexfields that were posted as part of the taxable lines. AR, AP
Tax Line Balancing Segment TRX_TAX_BALANCING_SEGMENT The comma separated balancing segment values of the accounting flexfields that were posted as part of the tax accounting lines. AR, AP
Tax Line Natural Account TRX_TAX_NATURAL_ACCOUNT The comma separated natural account segment values of the accounting flexfields that were posted as part of the tax accounting lines. AR, AP
Transaction Taxable Account TRX_TAXABLE_ACCOUNT Transaction taxable account. AR, AP
Transaction Taxable Account Desc TRX_TAXABLE_ACCOUNT_DESC Transaction taxable account description. AR, AP
Transaction Taxable Bal Seg description TRX_TAXABLE_BALSEG_DESC Transaction taxable balancing segment description. AR, AP
Transaction Taxable Natural Account Segment Description TRX_TAXABLE_NATACCT_SEG_DESC Transaction taxable natural account segment description. AR, AP

Miscellaneous

The Miscellaneous column is available at all summarization levels.

Attribute Name Column Name Description Application
Functional Currency FUNCTIONAL_CURRENCY_CODE The ledger currency code for this ledger. AR, AP, GL

Customizing Output for the Report

Creating a New Attribute Set

Customizing a report is a task that typically requires a developer or someone skilled in Oracle XML publisher. Because the Financial Tax Register uses the RXI engine, any user with the proper permissions can modify the fields displayed, add summary columns and otherwise adjust the output of this report. While RXi is not able to generate the attractive end-user reports that XML publisher can create, many financial analysts and tax managers prefer this format for the flexibility and ability to export data into spreadsheet (CSV) output. This is accomplished by creating a new Attribute Set to be used by the RXI engine for publishing the report output. This can be done following the instructions in the Oracle Financials RXi Reports Administration Tool User Guide. For a description of setting up a Financial Tax Register attribute set and to see an illustrated example of the process, refer to My Oracle Support Note 745943.1

Review the following information as you customize:

An abbreviated set of steps is listed below:

  1. Switch Responsibilities to Report eXchange Designer.

  2. Select Setup RXi.

  3. Pick the report RX-Only: Financial Tax Register Report.

    Note: Do not pick Financial Tax Register or RX-Only: Financial Tax Register as the report. The Financial Tax Register generates the RX-Only: Financial Tax Register Report. Picking the Financial Tax Register or RX-Only: Financial Tax Register results in the attribute sets from 11i being updated instead of the R12 attribute set being displayed.

  4. Copy the attribute set.

  5. Remove the columns that you do not wish to display (limit the columns to match the number available in your version of your spreadsheet solution if choosing CSV output).

  6. Select Tools > Security and add the responsibilities that should have access to the attribute set.

  7. Save.

    Note: Attribute sets may only be visible from the forms based request submission (not the web forms).

Formatting Currency and Number Cells

To display tax amount such as (XXX) instead of -XXX, you must define a format. Complete the following steps:

  1. From Report eXchange Designer responsibility.

  2. Double click on Setup Rxi.

  3. View the Attribute Set page.

  4. In the Report Name field enter RX-only: Financial Tax Register Report

  5. Navigate to Attribute Set field.

  6. Go to the attribute set which is used by the report.

  7. On right bottom side of screen click on Column Details.

  8. From the Menu Bar Folder the menu is disabled. Place the cursor on any of the fields, the folder is now enabled.

  9. Select Folder > Show Field.

  10. Select the Currency Column.

  11. Verify the Currency Column has been added.

  12. Navigate to any Amount related field, For example, Taxable_amt in the Currency Column select.

Limitations on Field Availability and Field Output

Fields not available for selection: Not all tax related attributes are in the available attributes. For example, one very significant field not available is Jurisdiction.

Fields not appearing that are in the attribute set: As noted earlier, not all field show up when the report is submitted. The fields displayed are dependent upon the level (distribution, transaction, line) from which the report is submitted. For example, in order to display accounting information the include_accounting_segments must be set to Yes and the Summary level must be equal to Transaction Distribution Level, otherwise, the accounting information does not to appear. Submitting at any other level will return no data for the accounting fields.

Troubleshooting and Improving Performance

Performing General Troubleshooting

This report works by pulling data from the underlying tables used across GL, AP and AR. The steps used to compile this data occur in the TRL source code. My Oracle Support Note 737320.1 (section 4) contains the very latest patches released for this code. The note also includes comprehensive troubleshooting steps for Release 12 Tax reports including the Financial Tax Register.

Check to see if the data missing or incorrect on your report is also wrong in the underlying view ZX_REP_EXTRACT_V - the view upon which attributes sets are defined. When submitting this report with FND:Debug set to Yes, data will be retained in the base tables summarized by this view. You can query this view to see if data exists If it does not you can then look to see if your parameters are properly set. If parameters are properly set then the problem is almost certainly in the TRL engine and database packages. If you find that the data appears correct in the view but invalid in the display, check to see if your attribute set has proper grouping and other settings. Also, test with one of the seeded attribute sets.

Common Issues Specific to the Financial Tax Register

Symptom: Fields are showing as blank on the report output

Cause: Check the level at which the report was run. When submitted at the Transaction level, line level details cannot be displayed. Similarly, when run at the transaction level, distribution level details are not shown.

Symptom: No Output appears when running the report for the Reporting Set of Books

Cause: Set the profile option GL Ledger Name at the responsibility level.

Improving Financial Tax Register Performance

If you have performance problems with this report, please check the following: