R12: FAQ and Bug Fixes on Financial Tax Register Report

1. What is the Financial Tax Register Report?

The Financial Tax Register Report is a flexible cross-product tax register report released as part of the E-Business Tax transaction based tax reporting solution in R12.

This report allows users to report on both Output (sales transactions) and Input (purchase transactions) tax for specified parameters. (See 'Parameters' below for more details on controlling content).
IMPORTANT: This report can render nearly all information required by customers for their tax reporting but only if the proper parameters are entered. Be sure you read and understand the impact of tax parameters when using this report. Also see 'Enhancements' for a few scenarios not covered in this reporting solution.

The output created for this report is presented to users through the RXI tool. RXI allows users to select the fields they wish to see, add summary columns, totals and breaks. Additionally users can select the output format (HTML, CSV, Text, etc) when running these reports. In most cases this data is used by customers for data analysis as RXI (unlike most other reports which use XML Publisher) does not have the formatting capabilities to embed graphics (company logo) and/or otherwise customize the presentation. See the section below on 'Customizing output for this report' for more details on RXI configuration options.

Data selected for viewing in this report uses the Tax Reporting Ledger or 'TRL' engine to gather data. This same engine is used across a wide number of other R12 E-Business Tax reports. See 'How the Tax Reporting Ledger (TRL) Works' below for more details.

The financial tax register report was created to provide a comprehensive and flexible framework that customers can use to analyze and report on their transaction based tax activities in Release 12.

2. What Transactions (Modules) will show up on the Report Output?

Transactions from the following modules can appear on the Financial Tax Register if the proper parameters are supplied:

Oracle Receivables
Oracle Payables
Oracle General Ledger

3. What are the different Attribute sets available in Financial Tax Register Report?

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:

Default: The Default attribute set contains all of the available attributes from the extract view. You can use this attribute set to create new attribute sets. There is no predefined layout. Note that running the report at this level with an output format of "CSV" will typically not work well because the standard Excel workbook does not have enough columns. Choose HTML if you wish to see all fields.
TaxReg: The TAXREG attribute set can be used for Oracle Payables, Oracle Receivables, and Oracle General Ledger, or all products. The report contains the attributes most commonly included in Tax Registers. It does not include discount amounts for Oracle Receivables and Oracle General Ledger.

4. R12 How To Change Financial Tax Register Report RXZXPFTR Format ?

Following are the steps to update/modify the attribute set/columns of the report. Please try this in TEST instance and re-test the issue.

1) Switch Responsibilities to Report eXchange Designer
2) select "Setup RXi"
3) Pick the report RX-Only: Financial Tax Register Report
Do not pick "Financial Tax Register" or "RX-Only: Financial Tax Register" as the report. The Financial Tax Register spawns the RX-Only: Financial Tax Register Report. Picking the Financial Tax Register or RX-Only: Financial Tax Register" will result in the attribute sets from 11i being updated instead of the R12 attribute set being displayed
4) Click on 'Column Details' button
5) Add/Modify the columns or column names that you wish to display (limit the columns to match the number available in your version of Excel)
6) Save and Re-test the issue by running the report again.

5. When using the default attribute set, output of financial tax register truncates a number of the columns when publishing to excel. How to avoid this and display only the columns that are required for reporting?

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. An abbreviated set of steps are listed below:

1) Switch Responsibilities to Report exchange Designer
2) Select "Setup RXi"
3) Pick the report RX-Only: Financial Tax Register Report
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 Excel)
6) Select "Tools > Security" and add the responsibilities that should have access to the attribute set.
7) Save

6. Report Parameters And What They Do

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

 

a. 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:

• Default: The Default attribute set contains all of the available attributes from the extract view.  You can use this attribute set to create new attribute sets. There is no predefined layout.  Note that running the report at this level with an output format of "CSV" will typically not work well because the standard Excel workbook does not have enough columns.  Choose HTML if you wish to see all fields.
• TaxReg: The TAXREG attribute set can be used for Oracle Payables, Oracle Receivables, and Oracle General Ledger, or all products. The report contains the attributes most commonly included in Tax Registers.  It does not include discount amounts for Oracle Receivables and Oracle General Ledger.

Please note that the reporting level selected will change which attributes are displayed. This means that distribution level attributes will not be displayed if the report is submitted at the transaction level.  Similarly some transaction level details are shown as null (blank) if the report is submitted the distribution level.

    b. Output Format

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

• CSV: Also known as comma separated this output format is typically used when importing data into a spreadsheet such as Microsoft Excel.  Data will be truncated by Excel if the number of columns exceed the capacity of your spreadsheet.  Refer to the section below on customizing output to modify the fields extracted.
• HTML: This option produces output in an HTML table and is best viewed through a browser.  This is a good choice when exporting the "Default" attribute set as the table has no limitations on the number of columns.
• TAB: A good alternative to CSV in cases where your content may include a comma (Ex: description or text fields). 
• TEXT: basic text output.  This output format can be difficult to view as it may result in large white spaces being produced on the page.

    c. Reporting Level, Reporting Context, Company Name & Currency

         Note: 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

• Ledger:  The most broad setting, this method selects all tax data for transactions within a ledger.
• Legal Entity
• Operating Unit

Manual tax journals are created a ledger_id will be stamped on the manual tax journal. The R12 gl extract selects data for the ledger regardless of the OU or LE parameters selected from above. 

Reporting Context:  This LOV will render a list that is contextual to the reporting level selected above.  (Ex: A list of operating units will be displayed if you select Operating Unit). 

Please note that the data displayed in this LOV is further restricted by your security settings and/or operating unit restrictions defined in System Administrator.

Company Name:  This parameter is available only when the reporting context is set as "Ledger" and 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 (if selected above) 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

    d. Register Type

• All:  All transactions
• Interim Tax Register: Limits the output to Receivables transactions that are posted to the interim tax account.  A tax is posted to the interim account when the settlement option for the tax rate is set to "Deferred" and the receivables invoice has not yet been paid.  This feature was brought forward from 11i to R12 for Receivables however no comparable solution exists for Payables interim tax reporting.  A workaround exists in R12 to use the Account Analysis report provided by SLA for AP interim reporting.  ER 8649885 EXTEND THE INTERIM TAX REGISTER OPTION TO PAYABLES INVOICES was logged to request that this be expanded.
• Non-Recoverable Tax: Displays taxes sourced in both Payables and Receivables where the tax is not recoverable. 
• Tax Register: Displays recoverable taxes, excludes any tax that is deferred (posted to the Interim tax account)

  i. Receivables Parameters

Standard Yes/No Option.  You MUST set to yes if you wish to see on the report output.  Note that the actual form does not designate as Receivables Specific

Include Invoices
Include Applications
Include Adjustments
Include Miscellaneous Receipts
Include Bills Receivables
AR Exemption Status: If you are reporting on Receivables transactions, select a tax
exemption status to report on.

j. 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:

• Up to Four Output Taxes
• Up to Two Input Taxes, each with a split between Recoverable and Non-Recoverable amounts.

    k. 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 these in your attribute set.

    e. Summary Level

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

• Transaction Distribution Level
• Transaction Level
• Transaction Line Level

Please note that not all attributes specified in your attribute set will be displayed 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.

    f. Transaction Parameters

Product

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

• ALL
• Oracle General Ledger
• Oracle Payables
• Oracle Receivables

Note that you MUST also select the Payables or Receivables parameters or no data will be displayed 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.

NOTE for GL Transactions:

Journal line details will only appear if the JE had the "Document Identifier" and "Document Date" entered in the journal lines window.

While entering journal lines the "Tax Information" popup window will appear. User has to enter"Document Identifier" and "Document Date". These details will appear as Trx Number and Trx Date in the report output.

Currency Code Low/High

Include Discounts

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

• ALL
• Not Transferred (Requires that the report be run at the distribution level)
• Transferred (Requires that the report be run at the distribution level)

Accounting Status

• Accounted (Requires that the report be run at the distribution level)
• Both Accounted and Unaccounted
• Unaccounted (Requires that the report be run at the distribution level)

    g. Tax Type Parameters

Tax Type Low/High:  The range of tax types to include in the report. If you are reporting on a specific tax regime or tax, then the report uses the tax types belonging to this tax regime or tax only.
Tax Regime Code: Select a tax regime to report on, or leave blank to report on all applicable tax regimes
Tax: Select a tax to report on, or leave blank to report on all applicable taxes.
Tax Jurisdiction: Select a tax jurisdiction to report on, or leave blank to report on all applicable tax regimes.
Tax Status Code: Select a tax status to report on, or leave blank to report on all applicable tax regimes.
Tax Code Low/High: The range of applicable tax rate codes to include in the report

    h. Payables Parameters

Standard Yes/No option.  Set to yes if you wish to see and No if you do not. 
Note that the actual form does not designate these as Payables specific.

Include Standard Invoices
Include Debit Memo's
Include Prepayments
Include Mixed Invoices
Include Expense Reports