Explanation of “JP-250DB 10” Depreciation Method in Oracle Formula Setup

image

Why Oracle design the formula like this? It looks far different than the requirement description.(See extracted requirement in below passage)

For formula itself, it can be explained easily. 

Guarantee depreciation amount can be calculated as “Cost * Guarantee Rate”

Depreciation Rate before switching can be calculated as “Begin NBV*Original Rate”

Depreciation Rate after switching can be calculated as “NBV when switching*Revised Rate”

The formula can be explained easily as “When depreciation amount less than guarantee amount, we will switch the rate from original rate to revised rate.” And due to “Dual Rate Evaluation” depreciation basis rule, the depreciation basis is changed from NBV of each begin of fiscal year to the NBV of the asset as at the date of the switch from Original rate to the Revised rate. It means after switching rate to revised rate, it is straight line depreciation on the base of the NBV of the asset as at the date of the switch from Original rate to the Revised rate.

The formula is explained, but the formula looks far away from requirement description! Why? And how the figure of revised rate and guarantee rate get from? Is it given by government? NO! It is introduced to design formula. The requirement has not any place mentioned these kind of rate.

Requirement

The Old Declining Balance Method is calculated by multiplying the book value as of the beginning of the fiscal year by a predetermined depreciation rate. The New Declining Balance Method will be calculated by multiplying the book value as of the beginning of the fiscal year by the depreciation rate, which is 2.5 times the depreciation rate under the straight line method. If the amount calculated using the New Declining Balance Method is less than the “amount calculated by dividing the book value as of the beginning of the fiscal year by the remaining years (useful life less the elapsed year)”, then the calculation method will be changed from the declining balance method to the straight line method when calculating the depreciation limit.

It is decided on a fiscal year basis whether the “amount calculated by multiplying the book value as of the beginning of the fiscal year by the depreciation rate which is 2.5 times the depreciation rate under the straight line method” is lower than the “amount calculated by dividing the book value as of the beginning of the fiscal year by the remaining years”. A change of method from the declining balance method to the straight line method in the middle of a fiscal year is not permitted.

Let’s have a look at how revised rate and guarantee rate is gotten.

From requirement description,

Assuming asset cost = a, depreciation year = b, original rate=x, so, there’s a relationship b=1/x*2.5

So, by original rate declining balance deprecation in the first years,

Year

Depreciation Value

NBV

1

xa

(1-x)a

2

x(1-x)a

(1-x)2a

3

X(1-x)2a

(1-x)3a

n

X(1-x)n-1a

(1-x)na

Against the requirement, If the amount calculated using the New Declining Balance Method is less than the “amount calculated by dividing the book value as of the beginning of the fiscal year by the remaining years (useful life less the elapsed year)”, then the calculation method will be changed from the declining balance method to the straight line method when calculating the depreciation limit.,

We can get a inequation as below, assuming n is the switching year

x(1-x)n-1a<(1-x)n-1/(b-(n-1))a                     (b=1/x*2.5)

On the base of total deprecation year, we can get the switch year.

For example, b=10, x=0.25 in above screendump,

Finally, we can get n>7 from above inequation. So, at the eighth year, we need to switch from the declining balance method to the straight line method when calculating the depreciation limit.

On the base of n=8, we can calculate so-called guarantee rate to help design the formula.

Guarantee Rate = right of inequation = (1-x)n-1/(b-(n-1)) = 0.757/3 = 0.0445!

Revised Rate = 1/(b-(n-1))=1/3=0.33

 

As similar as above you can calculate the guarantee rate and revised rate for depreciation year 5, 10, 20 or others.

image

For this case, switching year n can be calculated as 14 by inequation.

Guarantee Rate = 0.875^13/7=0..252

Revised Rate = 1/7=0.143

Can you understand it now? Is it interesting? And who designed this formula?Smile

2007 Japan Tax Reforms–Depreciation Method Changes

1. Outline on Revision of the Depreciation Method

(1) With regard to depreciable assets acquired on or after April 1, 2007, the concept of residual value will be abolished such that after the useful life has expired, depreciable assets can be depreciated until their book value is JPY 1.

(2) An accelerated depreciation rule, the so called “250% declining balance method” is introduced (for details, please refer to 2.(2) below).

(3) With regard to depreciable assets acquired on or before March 31, 2007, the residual value of 5% can be depreciable using a straight line method over 5 years starting from the next fiscal year following the fiscal year in which the available limit for depreciation applied to the assets (being 95% of the acquisition cost) is depreciated (“5 Years Average Depreciation”).

(4) The valuation method is maintained with regard to depreciable assets for fixed asset tax purposes.

2. Calculation Method and Example

The calculation method of the depreciation limit for corporation tax purposes has been amended as described below by the 2007 tax reform. We set out the calculation method under the 2007 tax reform and also the depreciation limit and residual book value which have been computed under the straight line method prior to the tax reform (“Old Straight Line Method”), the declining balance method prior to tax reform (“Old Declining Balance Method”), the straight line method after the tax reform (“New Straight Line Method”) and the declining balance method after the tax reform (“New Declining Balance Method”).

(1) Straight Line Method

The Old Straight Line Method is calculated by taking the acquisition price of an asset less its residual value (10% of the acquisition price) and multiplied by the depreciation rate. The New Straight Line Method will be calculated by multiplying the acquisition price by the depreciation rate, now that the residual value has been abolished.

(2) Declining Balance Method

The Old Declining Balance Method is calculated by multiplying the book value as of the beginning of the fiscal year by a predetermined depreciation rate. The New Declining Balance Method will be calculated by multiplying the book value as of the beginning of the fiscal year by the depreciation rate, which is 2.5 times the depreciation rate under the straight line method. If the amount calculated using the New Declining Balance Method is less than the “amount calculated by dividing the book value as of the beginning of the fiscal year by the remaining years (useful life less the elapsed year)”, then the calculation method will be changed from the declining balance method to the straight line method when calculating the depreciation limit.

It is decided on a fiscal year basis whether the “amount calculated by multiplying the book value as of the beginning of the fiscal year by the depreciation rate which is 2.5 times the depreciation rate under the straight line method” is lower than the “amount calculated by dividing the book value as of the beginning of the fiscal year by the remaining years”. A change of method from the declining balance method to the straight line method in the middle of a fiscal year is not permitted.

(3) Example on computation of depreciation limit and residual book value (assuming the acquisition price is JPY 1,000 and useful life is 10 years)

image

3. Timing of Application of 5 Years Average Depreciation on the Existing Assets

For depreciable assets acquired on or before March 31, 2007, the residual value of 5% will be depreciable over 5 years starting from the fiscal year after the fiscal year in which the available limit for depreciation (95% of the acquisition cost) is depreciated. The decision whether the available limit has been depreciated will be made on a fiscal year basis. Therefore, 5 Years average depreciation will apply from the next fiscal year when the available limit is depreciated.

 

Mapping to above requirement changes, we developed the Japanese 250% Declining Balance Depreciation Method and Japanese Straight Line Extension Depreciation Method and their corresponding matched functions like what-if analysis etc. in Oracle E-Business Suite and will migrate to Fusion Application.

How to debug accounting issues in EBS and Fusion Application?

Oracle ERP applications, EBS and Fusion application, use subledger accounting architecture to create accounting for transactions and accounting events.

When you create accounting online from AP invoice, AR invoice, payment, receipt, etc., you may meet some problems that the accounting entries(subledger journals) are not able to created. It suggests you run create accounting concurrent program and see detailed errors. Generally, this is because some validations are not passed or some cross-application problems. For example, AP prepayment application for multiple times, etc.

At this moment, you should run the program and read the output file and log file to locate the problem. And you may need to validate the AAD (Application accounting definition) for your application and resolve the problems indicate in output file. Generally the problem can be resolved.

To validate ADD, you need to go to subledger accounting setup->subledger accounting setup->accounting methods builder->method and definitions->application accounting definition to validate your ADD by event class or run a concurrent program called “Validate Application Accounting Definitions” and select ledger and application in parameter.

To resolve other errors indicate in create accounting output file. It is case by case. For example, below error message

“The subledger journal entry does not balance by balancing segment and there is no Intracompany Balancing Rule defined for the ledger Vision India Operations, source Payables and journal category Purchase Invoices. The ledger option Intracompany Balancing is enabled, but no balancing rules are defined for the ledger. Please update the journal entry setups for the application accounting definition so that the journal entry balances by balancing segment, or define a cross-entity balancing rule for the ledger.”

It means the accounting entries generated for the transaction are not balanced. It means, in AP transaction, Dr. side accounting is from distribution line and Cr. Side accounting is from supplier liability account setup. They have different company segment (balance segment), so they are not balanced. You must need to change either distribution balance segment or supplier liability balance segment to make them consistent.

Oracle Taiwan Localizations

Oracle Financials provides standard localization features for Taiwan region,which includes

One function:

  1. Government Uniform Invoice Number Generation (GUI)
    The feature works together with Oracle e-Business Suite to support Taiwanese three kinds of invoict types and its special invoice number generation. The invoice number is predefined by tax authority for the company bi-monthly which has a prefix and changed every two months. 
    The feature uses standard transaction source to fulfill the requirement. User must set up new transaction source when they get the new pre-defined invoice number from tax authority every two months. Which is not very user-friendly.

Nine Reports:

  1. Taiwanese Input VAT Report
    List input VAT entries from Payables. The report can be submit at different levels, legal entity, operating unit, ledger, etc. as well as below reports.
  2. Taiwanese Payables Sales/Purchase Return and Discount Certificate
    This is the purchase return or discount certificate assigned to supplier for evidence of accounitng and audit purpose.
  3. Taiwanese Purchase Return and Discount Report
    List purchase return and discount with tax entry information.
  4. Taiwanese Output VAT Report
    List input VAT entries from Receivables.
  5. Taiwanese Receivables Government Uniform Invoice Report
    This is an internal report to list all GUI invoice in receivable.
  6. Taiwanese Receivables Zero-Rate Tax Report
    List the tax exemption entries informaiton.
  7. Taiwanese Sales Return and Discount Report
    List sales return and discount with tax entry information.
  8. Taiwanese EDI Government Uniform Invoice
    List all GUI invoice from both Payables and Receivables in tax authority required format. The EDI file will upload to tax authority statutorly. 
  9. Taiwanese Pro Forma 401 Report
    This report provides required information to help customer submit statutory 401 Form. 401 form should be tax return report. It is like below
    Taiwan401

 

Difference between jobs and positions

What’s the job and position?

Oracle HRMS provides flexible work structures to represent the ongoing responsibilities and functions that an organization must carry out in order to meet its goals. Jobs and positions are placeholders in your enterprise model representing roles, which enable you to distinguish between tasks and the individuals who perform those tasks. Oracle HRMS uses jobs to represent the duties people perform and the required skills, for example:

  • Professor

  • Developer

  • Accountant

Positions represent a specific instance of a job, such as:

  • Assistant Professor of Sociology

  • Senior Software Developer

  • Payroll Accountant

A clear conceptual model of your enterprise helps you to optimize your workforce. Jobs and positions are key components of that model. The flexibility of jobs and positions enables you to model your enterprise accurately. Use jobs or positions (in combination with organizations, assignments, grades, salaries, and other HR structures) to manage your workforce in a manner consistent with the way you do business.

How to select job or position control for your enterprise?

As you implement your enterprise model, one of the earliest decisions you face is whether to use jobs, positions, or a combination of both. You can use Oracle HRMS to define required skills and valid grades for either one. Enterprises fall into one of three general categories:

  • Rule-based

  • Project-based

  • Hybrid

If your organization is a rule-based enterprise, you regulate employment, roles, and compensation according to strict policies and procedures. Fixed roles tend to endure over time, surviving multiple incumbents. You manage roles rather than individuals. Examples include government, higher education, and health care. Rule-based industries, where roles continue to exist after individuals leave, typically model the enterprise using positions.

If your organization is a project-based enterprise, such as a construction or software company, you require the flexibility to assign people to new projects or organizations on a regular basis. You manage people and their skill sets, rather than fixed roles. This requires the flexibility to match competencies to tasks quickly and easily. Project-based organizations, where roles end when individuals complete a project, typically model the enterprise using jobs.

If your organization is a hybrid enterprise, you assign some individuals to fixed roles, and others to multiple projects. This is typical of large manufacturing or corporate enterprises. Hybrid enterprises such as these model the enterprise using both jobs and positions.

Notes_Rxi_Reports

Refer from http://sureshappstech.blogspot.jp/2013/11/notesrxireports.html

In this Document
Purpose
Questions and Answers
What is an RX report ?
What makes RX reports a Variable Format Report ?
What is a RXi Attribute Set ?
Where to get documentation about RXi Reports ?
How to Locate Oracle Financials RXi Administration Tool User Guide in Metalink ?
What patches need to be installed to use the RXi Reports ?
For what is used the Responsability ‘Report eXchange Designer’ ?
How to add a RXi report to an existing responsability ?
How To Find The Source Code Of An RXi Report ?
How is the RXi report output generated ?
How the RX Reports works in the Applications Release 11.0.3 ?
How to control the content and the layout of the RXi Reports ?
How to purge interface data from the RXi temporary table ?
How to gather / collect debug information from an erroring RXi Report ?
How to know the package body responsible for extracting data for a specific RX report ?
How to activate the ‘Debug’ parameter for the RXi reports ?
How the activate the SUBMIT_DEBUG and/or the PUBLISH_DEBUG flag(s) ?
How to find out the version of a package ?
Most of RXi reports have a parameter called ‘Drill Down’ – what it is used for ?
Running the RXi: Tax Reconciliation By Taxable Account (JGRXTXBL, RXJGTXBL) output shows a ‘No data found’ message – Why ?
The RXi: Asset Details Extract (JGRXFAAX, RXJGFAAX) does not produce any output – Why ?
The RXi: Fixed Assets Register Report (RXJGFARP) output shows several lines for a same asset number and so, the report total is wrong; How to solve ?
How to get a trace file from a RXi report ?
Some existing Notes reporting RXi problems ?


Applies to:

Financials Common Country – Version: 11.5.1 to 11.5.10.2 – Release: 11.5 to 11.5
Information in this document applies to any platform.
EXECUTABLE:JGRXPIR – RXi: Payables Invoice Register
EXECUTABLE:JGRXPIRD – RXi: Payables Invoice Register by Detail
EXECUTABLE:JGRXTXBL – Publish Tax Reconciliation by Taxable Account
EXECUTABLE:RXJGFAAX – RXi: Asset Details Extract
EXECUTABLE:RXJGFAMX – RXi: Fixed Asset Register Extract
ConcurrentProgram:RXJGFARP – RXi: Fixed Assets Register Report
ConcurrentProgram:RXJGFARX – RXi: Retirement Details Extract
ConcurrentProgram:RXJGPIR – RX-only: Payables Invoice Register
ConcurrentProgram:RXJGPIRD – RX-only: Payables Invoice Register by Detail
ConcurrentProgram:RXJGRTPB – General Ledger Reconciled Transactions Report
EXECUTABLE:RXJGRTXX – RX-only: General Ledger Reconciled Transactions Report
ConcurrentProgram:RXJGTXBL – RX-only: Tax Reconciliation by Taxable Account
ConcurrentProgram:RXJGUTPB – General Ledger Unreconciled Transactions Report
EXECUTABLE:RXJGUTXX – RX-only: General Ledger Unreconciled Transactions ReportConcurrentProgram:FARXAC – RXi: Asset Cost Balance Report
ConcurrentProgram:FARXAD – RXi: Accum Deprn Balance Report
ConcurrentProgram:FARXADD – RXi: Additions by Date-Placed-in-Service Report
ConcurrentProgram:FARXADP – RXi: Additions by Period Report
ConcurrentProgram:FARXADR – RXi: Additions By Responsibility Report
EXECUTABLE:FARXAL – RXi: Asset Listing by Period
EXECUTABLE:FARXBK – RXi: Fixed Assets Book
ConcurrentProgram:FARXCAJ – RXi: Cost Adjustments Report
ConcurrentProgram:FARXCAP – RXi: Capitalizations Report
ConcurrentProgram:FARXCC – RXi: CIP Cost Balance Report
ConcurrentProgram:FARXCCR – RXi: Cost Clearing Reconciliation Report
ConcurrentProgram:FARXCHGP – RXi: Mass Change Preview Report
ConcurrentProgram:FARXCHGR – RXi: Mass Change Review Report
ConcurrentProgram:FARXGA – RXi: Group Asset Report
ConcurrentProgram:FARXHWD – RXi: Hypothetical What-If Depreciation Analysis
ConcurrentProgram:FARXMAD – RXi: Mass Additions Report
ConcurrentProgram:FARXMT – RXi: Asset Maintenance Report
EXECUTABLE:FARXPBSH – Publish RX Reports
ConcurrentProgram:FARXPIC – RXi: Physical Inventory Comparison Report
ConcurrentProgram:FARXPIM – RXi: Physical Inventory Missing Assets Report
REPORT:FARXPRG.RDF – Purge RX Interface Data
ConcurrentProgram:FARXRCL – RXi: Reclass Report
ConcurrentProgram:FARXRCLP – RXi: Mass Reclassification Preview Report
ConcurrentProgram:FARXRCLR – RXi: Mass Reclassification Review Report
ConcurrentProgram:FARXRET – RXi: Retirements Report
ConcurrentProgram:FARXRL – RXi: Reserve Ledger Report
ConcurrentProgram:FARXRR – RXi: Reval Reserve Balance Report
ConcurrentProgram:FARXTFR – RXi: Transfers Report
ConcurrentProgram:FARXWD – RXi: What-If Depreciation Analysis
ConcurrentProgram:RXJGFARX – RXi: Retirement Details Extract
EXECUTABLE:RXFAAC – RX-only: Asset Cost Balance Report
EXECUTABLE:RXFAAD – RX-only: Accum Deprn Balance Report
EXECUTABLE:RXFAADD – RX-only: Additions by Date-Placed-in-Service Report
EXECUTABLE:RXFAADP – RX-only: Additions by Period Report
EXECUTABLE:RXFAADR – RX-only: Additions by Responsibility Report
ConcurrentProgram:RXFAAL – RX-only: Asset Listing by Period
ConcurrentProgram:RXFABK – RX-only: Fixed Assets Book
EXECUTABLE:RXFACAJ – RX-only: Cost Adjustments Report
EXECUTABLE:RXFACAP – RX-only: Capitalizations Report
EXECUTABLE:RXFACC – RX-only: CIP Cost Balance Report
EXECUTABLE:RXFACCR – RX-only: Cost Clearing Reconciliation Report
ConcurrentProgram:RXFADPTX – Japanese Depreciable Assets Tax Reports
EXECUTABLE:RXFAGA – RX: Group Asset Report
EXECUTABLE:RXFAHWD – RX-only: Hypothetical What-If Depreciation Analysis
EXECUTABLE:RXFAMAD – RX-only: Mass Additions Report
EXECUTABLE:RXFAMCHP – RX: Mass Change Preview Report
EXECUTABLE:RXFAMCHR – RX: Mass Change Review Report
EXECUTABLE:RXFAMT – RX-only: Asset Maintenance Report
EXECUTABLE:RXFAPIC – RX-only: Physical Inventory Comparison Report
EXECUTABLE:RXFAPIM – RX-only: Physical Inventory Missing Assets Report
EXECUTABLE:RXFAPTAX – RX-only: Property Tax Report
EXECUTABLE:RXFARCL – RX-only: Reclass Report
EXECUTABLE:RXFARCLP – RX-only: Mass Reclassification Preview Report
EXECUTABLE:RXFARCLR – RX-only: Mass Reclassification Review Report
EXECUTABLE:RXFARET – RX-only: Retirements Report
EXECUTABLE:RXFARL – RX-only: Reserve Ledger Report
EXECUTABLE:RXFARR – RX-only: Reval Reserve Balance Report
EXECUTABLE:RXFATFR – RX-only: Transfers Report
EXECUTABLE:RXFAWD – RX-only: What-If Depreciation Analysis
EXECUTABLE:APRXPYAC – Publish Actual Payment
EXECUTABLE:APRXPYRG – Publish Payment Register
EXECUTABLE:APRXWHTX – AP Withholding Tax Extract
ConcurrentProgram:FAPRXTAX – RXi: Property Tax Report
ConcurrentProgram:RXAPPYAC – RX-only: Actual Payment
ConcurrentProgram:RXAPWHTP – AP Withholding Tax Report
ConcurrentProgram:RXAPWHTX – AP Withholding Tax Extract
ConcurrentProgram:RXARACRC – RX-only: Actual Receipt
EXECUTABLE:ARRXACRC – Publish Actual Receipt
ConcurrentProgram:RXARADJR – RX-only: Adjustment Register
ConcurrentProgram:ARRXARPB – Adjustment Register
ConcurrentProgram:RXARARRG – RX-only: Applied Receipts Register
EXECUTABLE:ARRXARRG – Applied Receipts Register
ConcurrentProgram:RXARBRBS – RX-only: Bills Receivable by Status Report
EXECUTABLE:ARRXBRBS – Bills Receivable by Status Report
EXECUTABLE:ARRXBRSS – Bills Receivable Summary Report
ConcurrentProgram:RXARBRSS – RX-only: Bills Receivable Summary Report
ConcurrentProgram:RXARINVC – RX-only: Transaction Check
EXECUTABLE:ARRXINVC – Publish Transaction Check
ConcurrentProgram:RXARINVR – RX-only: Transaction Register
EXECUTABLE:ARRXINVR – Transaction Register
EXECUTABLE:RXARMTRG – RX-only: Miscellaneous Receipts Register
ConcurrentProgram:ARRXMTRG – Miscellaneous Receipts Register
ConcurrentProgram:RXARPFTR – Financial Tax Register
EXECUTABLE:ARRXRCRG – Receipt Register
ConcurrentProgram:RXARPTEX – RX-only: Financial Tax Register
ConcurrentProgram:RXARRCFR – RX-only: Receipt Forecast
EXECUTABLE:ARRXRCFR – Publish Receipt Forecast
ConcurrentProgram:RXARSLRG – RX-only: Sales Register
EXECUTABLE:ARRXSLRG – Publish Sales Register

Purpose

This FAQ document intend to provide technical and functional informations about all the existing:

  • Financials Common Country module RXi reports (JGRX / RXJG)
  • Assets module RXi reports (FARX / RAXFA)
  • Payables RXi reports (APRX / RXAP)
  • Receivables RXi reports (ARRX / RXAR)

This FAQ will also contains information to help to debug and investigate the RXi Reports problems that Users may face handling it.

Questions and Answers

What is an RX report ?

  • The RX reports are not designed with Oracle Reports;
  • There is no RDF file for the RX Report;
  • The RX report is generated by the database packages.

The RXi was developed by FA development team. In spite of that, the JG module (or the report respective module) must has status ‘ installed’ so that the JG RX reports works. The same rule is valid for AP RX, AR RX or FA RX  reports.
You may run the following statement in a SQL*plus session (user = APPS) to determine the present status of each of your Modules:

select t.application_name
, t.application_id
, i.patch_level
, decode(i.status,’I’,’Fully Installed’,
‘N’,’Not Installed’,’S’,’Shared’,’Undetermined’) status
from fnd_product_installations i
, fnd_application_vl t
where i.application_id = t.application_id
order by t.application_id;

What makes RX reports a Variable Format Report ?

The RX report is generated by the database packages.
The database packages prepare data inside the interim tables called Interface Tables.
At the end this data can be presented in text, html, CSV (Comma-Separated Values), and tab-delimited formats. User can define which columns should be shown at the final output of the RX report. This output type is usually possible to select via report parameters “Output Format”  OR  if there isn’t any, via System Administrator; Concurrent Program Define; Query it and you will see a field called “Output Format”; just changed it as you wish.

What is a RXi Attribute Set ?

User can define which columns should be shown at the final output of the RX report.
The set of columns is called Attribute Set
Attribute Set is a subset of all columns available for the report.

Where to get documentation about RXi Reports ?

The functional documentation about the RXi Reports is spread all over several User Guides. This will be better explained in one of the below topics but mainly it’s because there are RXI Reports that belongs to JG, AR, AP, GL and FA modules.
The technical documentation about RXi Reports can be found in Oracle Financials RXi Reports Administration Tool User Guide Release 11i. This manual is available via Metalink as part of the Release 11i Related Documentation.
You may also visit and study more about RXi , accessing RXi Home Page
There are also AR RXi  Brown bags
AR Transfers Of Information Docs (TOI)

How to Locate Oracle Financials RXi Administration Tool User Guide in Metalink ?

Login to Metalink;
Click on E-Bussiness 11i;
Click on Documentation;
Scroll down to Financials, and choose General Ledger;
Click on Oracle Financial Applications Related Documentation Release 11i;
Click on Oracle Financials RXi Reports Administration Tool User Guide Release 11i.

What patches need to be installed to use the RXi Reports ?

The RXi Reports will be automatically installed when installing Applications embeded within their Modules (AP, GL, AR, JG and FA). So this means that the objects, forms and reports for this functionality are already available.
However, when there isn’t already a country-specific functionality enabled, then you need to license/activate the JG module first through the License Manager in Oracle Applications Manager.
With the following SQL, it can be checked whether your Modules were already been licensed:

SELECT tl.application_name, t.application_id, i.patch_level
, decode(i.status,’I’,’Licensed’, ‘N’,’Not Licensed’,’S’,’Shared’,’Undetermined’) status
FROM fnd_product_installations i, fnd_application t, fnd_application_tl tl
WHERE i.application_id = t.application_id
AND tl.application_id = t.application_id
AND i.application_id = 7003
AND tl.language = ‘US’;

Note:
At various times throughout the life cycle of an Oracle Applications release, you may decide to license additional products, or begin using additional country-specific functionalities (localized products) or languages. If you do, you can record the status of newly active products and components by registering them in your system. Registration ensures that utilities (like AutoPatch) recognize them as active during patching or other maintenance processes.
You register active products, country-specific functionalities, or languages with the OAM License Manager.
Please check the Oracle Applications Maintenance Procedures manual, page 1-13 Direct URL: http://download-uk.oracle.com/docs/cd/B16981_01/current/acrobat/11iadproc.pdf
and the Oracle Applications Maintenance Utilities Direct URL: http://download-uk.oracle.com/docs/cd/B16981_01/current/acrobat/11iadutil.pdf

For what is used the Responsability ‘Report eXchange Designer’ ?

This responsibility allows:
1. Choose the columns to include in the report;
2. Order and group selected data;
3. Format selected columns;
4. Edit report and column headings;
5. Manage page size and other printing controls.

How to add a RXi report to an existing responsability ?

1. Assign RXi report to that responsability request group;
2. Assign request group to the responsibility;
3. Assign the Publish RX Reports concurrent program to the responsibility;
4. Above tasks can be performed as the System Administrator responsability level.

How To Find The Source Code Of An RXi Report ?

RXi reports build the queries dinamycaly during execution time.
RXi reports are made up of one or more PL/SQL files that should be located under the product top of each module. Usually the latest version is under:
$_TOP/patch/115/sql
The RXi engine makes the call to the different PL/SQL files as needed.
Making changes to the PL/SQL files is not supported and requires a lot of maintenance everytime a new patch is applied.
The easiest way to modify RXi reports is through the RXi Reports Administration tool.
Information on this tool can be found in the Oracle Financials RXi Reports Administration Tool User Guide Release 11i.

How is the RXi report output generated ?

Everytime we launch a RXi report, two requests are launched:

  • an RXi…. request – the parent request
  • an RX-Only…. request – the child request

The RXi… request call the child request immediately. The RX-Only… is responsible for retrieving (extracting) data for the RX report.
It calls the database package. It populates the Interface Table according to the parameters specifed at the moment of running. At that stage there is no difference which Attribute Set will be used for presenting the report. In Interface Table all availabe columns are populated.
Then the RXi… request is responsible for publishing the data according to the chosen Attribute Set and the output Format; based on the data in the interface table.
The RXi…combines retrieving and publishing request together in one step; it includes the Publish RX Reports program parameters and the parameters specific to given RX report. The RXi…. comes up for release 11i only.

How the RX Reports works in the Applications Release 11.0.3 ?

In Release 11.0.3 there was not RXi… request. The report called is directly the RX-Only… and then, when it fisnish, if we want to publish the data we have to use ADI – it is the only way of publishing RX report in release 11.0.3
The RX report were very limited in release 11.0.3…

  • Possibility of using Themes (kind of template for Excel or Web with some color, size, font, logos, background pics);
  • Possibility of defining new Attribute Sets;
  • No page dimension – output is treated as one big single page (lines never wrap; page break never occur);
  • No Report Heading;
  • No Summary at all – assumption that you can do any summaries in Excel.

In Release 11i , the RXi…combines retrieving and publishing request together in one step; it includes the Publish RX Reports program parameters and the parameters specific to given RX report.

How to control the content and the layout of the RXi Reports ?

The Oracle Financials RXi Reports Administration Tool lets you design the content and layout of your RXi reports. With the Oracle Financials RXi Reports Administration Tool, you can print the same report using different layouts, and you can control the data items included in your report. RXi lets you tailor your reports to meet statutory and other reporting requirements, and to create reports for your own internal use.
The Oracle Financials RXi Reports Administration Tool gives you great flexibility in managing your reporting requirements. Use the Oracle Financials  RXi Reports Administration Tool to:

  • Choose the columns to include in the report
  • Format selected columns
  • Order and group selected data
  • Edit report and column headings
  • Manage page size and other printing controls
  • Generate reports in text, html, CSV (Comma-Separated Values), 
  • and tab-delimited formats

All formatting and column information is defined and maintained in attribute sets. Each RXi report comes with one or more attribute sets. Use the Attribute Set window to view default attribute sets that come standard with RXi. You can create user-defined attribute sets based on default attribute sets with the
format and data that your company needs. You can further modify your user-defined attribute sets to meet additional company requirements. Then assign the attribute set to a report when you print the report using the Standard Request Submission forms.

How to purge interface data from the RXi temporary table ?

Using the Purge RX Interface Data program – it is available only for release 11i
Available under Report eXchange Designer responsibility (request group: RX Reports – Oracle Assets)
Deletes records from Interface Table older than specified number of days for one or for all reports.
Another secure way to erase everything (!) from that interface table that also works for Release 11i or 11.0.3 is truncating that table, using the SQL*plus command:
    truncate table ;
    commit;

How to gather / collect debug information from an erroring RXi Report ?

Note that the ‘FA:Print Debug’ profile has little influence on the debug messages in logs of RX extract requests.

            This Collection script shows the setup of Attribute Sets for given report.
            It is available only for release 11i

  • Determine whether the problem is with Extracting program (RX-only) or Publishing Engine
  • Verify with RXi and ADI 
  • Data in Interface table is correct or not ?
  • Some RXi reports only works via TRL engine code – check if it’s name is listed inside the Note 292163.1 – 11.5: Recommended Patches for VAT Reporting in EMEA and Asia/Pacific; if it is listed then ALL those patches MUST be APPLIED to avoid known bugs and data corruptions problems;
  • Activate the ‘Debug’ parameter;
  • Activate the ‘Request Type’ parameter to SUBMIT_DEBUG and/or the PUBLISH_DEBUG flag(s);
  • There is possibility to define a new ‘Debug’ argument as a last one (using System Administrator responsibility); Put own debug messages in package body responsible for extracting data for RX report. The following procedures are available:
    • fa_rx_util_pkg.enable_debug;
    • rx_util_pkg.debug(‘….’);

How to know the package body responsible for extracting data for a specific RX report ?

  1. System Administrator: Concurrent -> Program -> Define
  2. Query for your RX-Only…  report request;
  3. Take note of the ‘Executable Name’
  4. System Administrator: Concurrent -> Program -> Executable
  5. Query for ‘Short Name’ =  executable name you find out  in the earlier step
  6. Take note of the executable file name – thats your package body name !

How to activate the ‘Debug’ parameter for the RXi reports ?

All RXi reports that have a parameter field called ‘debug’ may be debugged using this method.
(Navigate: System Administrator: Concurrent -> Program -> Define; Query for your RXi report and press on Parameters button)
If your RXi report does not have a ‘Debug’ parameter, try activating the next debug method below.

  1. Go the sysadmin responsibility
  2. Navigate through System Administrator: Concurrent – Program – Define  to the concurrent
  3. program define window
  4. Press the F11 button or choose in the menu View – Query by example – Enter
  5. In the Short Name field you enter your  RX-Only… request short name
  6. 1.5. Press the ctrl+F11 buttons or choose in the menu View – Query by example – Run
  7. Click on the button Parameters
  8. Scroll trough the parameters and at the end you will find first, parameter ‘DEBUG’
  9. Change the Default value from N to Y and enable the check box
  10. Display for this parameter at bottom of the screen.
  11. Next parameter to search is ‘SQL TRACE’ (if there is one called as that; if not, skip this step)
  12. Check that the Default value is N

How the activate the SUBMIT_DEBUG and/or the PUBLISH_DEBUG flag(s) ?

Not all RXi reports has a parameter field ‘Debug’ so, this other method. Usually, if one is not possible, the other debug method it is.
This ‘debug’ is activated for the RXi child request always.
When getting this ‘debug’ for Request Sets, remember that you have to repeat all these steps for all launched RX-Only (child) requests !
As example, I picked  RXi: Physical Inventory Comparison Report  to show you how to activate this debug method.
1. Go to the System Administrator Responsibility.
– Navigate to: Concurrent/Program/Define
– Query Short Name = RXFAPIC (RX-Only: Physical Inventory Comparison Report)
– Click on the Parameters button
2. Check the Request Type parameter.
If the default value of the Request Type parameter = SUBMIT
– then change it to SUBMIT_DEBUG
or if it is PUBLISH
– then change it to PUBLISH_DEBUG
(this change is done in the ‘Default Value’ field )
3. Save the changes
4. Rerun the report and upload the log files of both the parent and the child request.
(The RXi: Physical Inventory Comparison Report is one example where the ‘Debug’ debug option  is not possible but it is possible this debug method !)

How to find out the version of a package ?

If you know the name of the package, just run in a SQL*plus session (user APPS):

select text from all_source where name = upper(‘JG_RX_IR_PKG’) and line=2;

otherwise just run the following two instructions and they will return all the relevant report and TRL code versions:
=============
Report code level
=============
SELECT TEXT
FROM ALL_SOURCE
WHERE (UPPER(NAME) LIKE ‘JG_RX_%’
OR UPPER(NAME) LIKE ‘AR_RX_%’
OR UPPER(NAME) LIKE ‘AP_RX_%’
OR UPPER(NAME) LIKE ‘FA_RX_%’
OR UPPER(NAME) LIKE ‘JGRX_%’
OR UPPER(NAME) LIKE ‘ARRX_%’
OR UPPER(NAME) LIKE ‘FARX_%’
OR UPPER(NAME) LIKE ‘APRX_%’)
AND LINE=2;
=============
   TRL code level
=============
SELECT TEXT FROM ALL_SOURCE
WHERE NAME IN (‘ARP_TAX_EXTRACT’, ‘AR_TAX_EXTRACT’,’AR_TAX_POPULATE’,
      ‘AP_TAX_EXTRACT’,’AP_TAX_POPULATE’,’GL_TAX_EXTRACT’,’GL_TAX_POPULATE’)
AND line=2;

Most of RXi reports have a parameter called ‘Drill Down’ – what it is used for ?

Choosing YES or choosing NO in the Drill Down parameter has no effect on the output. Data presentation is not different when we choose YES or NO value.
It is working as designed for R11i. This parameter utility will be implemented in a future version of Oracle Applications.

Running the RXi: Tax Reconciliation By Taxable Account (JGRXTXBL, RXJGTXBL) output shows a ‘No data found’ message – Why ?

The Tax Reconciliation by Taxable Account report to report on taxable transactions in Payables, Receivables, and General Ledger in a single report.
This report works via TRL engine. This means that if the TRL code is not up to date, the report will surely run into errors or known bug in the output.
If it shows a ‘No data found’, start by checking:

  1. Check the current status of the JG module – mentioned in one of the above topics;
  2. Check the versions of report and the Tax Reporting Ledger (TRL) code – be sure that all the patches inside the critical Note.292163.1 are applied;
  3. Clarify if this problem is happening running for AP, AR and/or GL transactions ?
  4. Check if the involved report table JG_ZZ_COVER_TAXABLE_EXTRACT  has the expected data inside it, using :   select * from JG_ZZ_COVER_TAXABLE_EXTRACT;

The RXi: Asset Details Extract (JGRXFAAX, RXJGFAAX) does not produce any output – Why ?

The report was submitted as standalone instead of being called via the RXi: Fixed Assets Register Report.
The RXi: Fixed Asset Register Extract concurrent program should not be run on its own, as it is expected
to get spawned only when the “RXi: Fixed Assets Register Report” is run.
This report is one of the “sub-reports” of the RXi: Fixed Assets Register Report (the other sub-report is the RXi: Retirement Details Extract) and it should not be run in separate.
If we want to run it in separate – and after executed it successfully – we must run the ‘Publish RX Report’ request, so we can ‘publish’ it’s extracted data.

The RXi: Fixed Assets Register Report (RXJGFARP) output shows several lines for a same asset number and so, the report total is wrong; How to solve ?

RXi report is working as expected – It is a setup issue.
Duplicate entries are shown because grouping is not taking place at the asset number level.
This is because all the columns in the last level are considered to be leaf level columns and grouping does not take place on them. This is the design of RXi reports.
Change the setup accordingly, to move description field to the leaf level, which will show the description twice but the totals would be correct. Steps to do this:
1. Copy ‘Italy’ attribute set and create a new set;
2. Remove all the leaf level columns, which the customer does not seem to want;
3. Remove ‘Description’ field from the displayed columns list and add it again;
(note: This should now get included at the leaf level. Not on the same level as asset number)
4. Apply ‘Sum’ and ‘Count’ in column-level details as per requirements;
5. Save this attribute set;
6. Now run the report using this new attribute set for the Asset Details Extract section.

How to get a trace file from a RXi report ?

As we already mentioned in a earlier topic in this note, a RX report  is not a   ‘.RDF’  file ; I mean, it is not generated by Oracle Reports but by DB packages.
So, RX reports are PL/SQL and so a database trace might not exactly help. Instead, get a database trace with binds and a tkprof’ed version of it .
Note 296559.1 (more generic) and note 160124.1 (How to Set SQL Trace on with 10046 Event Trace which Provides the Bind Variables) explain how and where you have to do this.
Note 170223.1 also is useful and helps you to understand the process.

Some existing Notes reporting RXi problems ?

<<456572 .1=””>> RXi: Fixed Assets Register Report (RXJGFARP) Shows Twice The Same Asset
NOTE: 274884.1 Rxi Reports End With The Error :ORA-20001: Please Choose Columns For Display To Generate This Report
NOTE: 364913.1 All Rxi Report with width less than 180 chars shows ###### in Report Date Printed
NOTE: 400166.1 Cannot Modify The Rxi: Fixed Assets Register Report (RXJGFARP) Output
NOTE: 375340.1 Arabic Reads In Incorrect Direction In Various RXi Reports
NOTE: 277832.1 RXI Only Reports Do Not Utilize Free Space In RX Temp Tables
NOTE: 314079.1 RXJGFAMX module: RXi: Fixed Asset Register Extract Errors with PLS-00306 Wrong Number Or Types Of Arguments In Call To ‘GET_FORMAT’ ORA-06550
NOTE: 246798.1 Unable To Make Changes To The Rxi Report Using Report Exchange Designer Responsibility.
NOTE: 357678.1 All Rxi Reports Finishes Successfully but doesnot produce any Output file
NOTE: 421707.1 Rxi: CIP Cost balance report Does Not Include Capitalized Asset During Report Period
NOTE: 214399.1 RXi Reports Print Parameters On Every Page
NOTE: 266911.1 How to Print Rxi Reports Giving No Data Found
NOTE: 352981.1 RXi Report Mis Alignment for Japan Output Character Set – but is ok for US Output
Unpublished NOTE: 361015.1 RXi: Asset Cost Balance Report Shows ‘No Data Found ‘ After Code-Fix Instructions for Bug 4860955
NOTE: 374551.1 Rxi Asset Listing By Period RXFAAL is Missing Assets With DPIS Equal to FA_CATEGORY_BOOK_DEFAULTS.START_DPIS
NOTE: 402274.1 RXJGTXBL: Not All Adjustments Are Showing On The Report
NOTE: 423270.1 DPIS displayed As ##### Instead Of Actual Dates In Rxi: Reserve Ledger Report
NOTE: 136297.1 GETTING *** NO DATA FOUND *** IN THE PUBLISHED RXI LEASE REPORTS
NOTE: 178514.1 Transaction Register does not have Consolidated Billing Number as a Column
NOTE: 200037.1 RXi Lease Options Showing Unclear Messages – RXPNCOLS
NOTE: 266863.1 ARXOTHER Other Receipt Applications Report Unable to See Report In LOV Under Setup RXi
NOTE: 285187.1 Number signs (#) Printed Instead of Date Time Stamp on RXI Report
NOTE: 397210.1 RXJGFAAX (Rxi: Asset Details Extract) Fails With ORA-06502
NOTE: 418114.1 RXi Financial Tax Report Not Correctly Populating Tax1 And Tax2 Codes
NOTE: 158416.1 RXi Report Printing Blank Page After Each Valid Print Page 0
Unpublished NOTE: 314057.1 RXJGPIRD Errors With Ora-06502 For Some Months
NOTE: 265057.1 Do Oracle Assets standard reports show the Member assets information?
NOTE: 274050.1 Report Beginning Balance And Ending Balance For Reserve Reports shows ********
NOTE: 141295.1 ARRXINVR-Transaction Register errors:ORA-06550 PLS-00201
NOTE: 214399.1 RXi Reports Print Parameters On Every Page
NOTE: 371041.1 Inflation Accounting – Projection Fails With FRM-41830 List of Values Contains No Entries
NOTE: 247667.1 RXi Adjustment Register Prints Entire Parameter List On Each Page
NOTE: 398596.1 RXFAAL – RX-only: Asset Listing by Period fails with Value Too Large For Column: FA_ASSET_LISTING_REP_ITF.SERIAL_NUMBER
NOTE: 416364.1 RXAROTHR Other Receipt Application Report Error Ora-06512 Null Index Table Key Value Apps.Fa_rx_publish
NOTE: 401487.1 Rxi Asset Listing By Period Report Truncates the Serial Number

Use subscription of the First Party Legal Entity

In configuration owner setup for operating unit, there’s a checkbox “Use subscription of the First Party Legal Entity”. It this checkbox is maked, most of setup fields will be disabled for operating unit configuration owner. This is used to block the conflict setup between operating unit and legal entity.

If the checkbox is marked, legal entity id will be extracted from transaction and use the corresponding tax setup in legal entity level to determine the taxes on transaction.

If the checkbox is not marked, operating unit id (org_id) will be extracted from transaction and use the corresponding tax setup (configuration owner) in operating unit level to determin the taxes on transaction.

Ledger, OU and Legal Entity (1)

User can select legal entities in AP invoice and AR transaction through “Customer Tax Payer ID” and “Legal Entity” field. The list of value of these two fields looks from the view xle_le_ou_ledger_v through operating unit, in which the relationship between ledger, OU and legal entity are described. The three entities are associated by leger configuration (ledger&LE) and organization definition (OU&ledger).

—————————————————————————————-

CREATE OR REPLACE VIEW APPS.XLE_LE_OU_LEDGER_V AS
SELECT O2.ORGANIZATION_ID OPERATING_UNIT_ID,
       lg.LEDGER_ID ,
       lg.NAME LEDGER_NAME ,
       lg.SHORT_NAME LEDGER_SHORT_NAME ,
       cfgDet.OBJECT_ID LEGAL_ENTITY_ID ,
       xlep.name legal_entity_name,
       xlep.legal_entity_identifier,
       xlep.activity_code,
       xlep.sub_activity_code,
       xlep.type_of_company,
       xlep.effective_from le_effective_from,
       xlep.effective_to le_effective_to,
       reg.registration_number,
       hrl.address_line_1,
       hrl.address_line_2,
       hrl.address_line_3,
       hrl.region_1,
       hrl.region_2,
       hrl.region_3,
       hrl.town_or_city,
       hrl.postal_code,
       hrl.country
       FROM GL_LEDGERS primaryLg ,
       GL_LEDGERS lg ,
       GL_LEDGER_RELATIONSHIPS rs ,
       GL_LEDGER_CONFIGURATIONS cfg ,
       GL_LEDGER_CONFIG_DETAILS cfgDet,
       XLE_ENTITY_PROFILES xlep,
       XLE_REGISTRATIONS reg,
       HR_LOCATIONS_ALL hrl,
       HR_ORGANIZATION_INFORMATION O2,
       HR_ORGANIZATION_INFORMATION O3
       WHERE rs.application_id = 101
         AND ( ( rs.target_ledger_category_code = ‘SECONDARY’ AND rs.relationship_type_code <> ‘NONE’ )
           OR ( rs.target_ledger_category_code = ‘PRIMARY’ AND rs.relationship_type_code = ‘NONE’ )
            OR ( rs.target_ledger_category_code = ‘ALC’ AND rs.relationship_type_code IN ( ‘JOURNAL’, ‘SUBLEDGER’) ) )
         AND lg.ledger_id = rs.target_ledger_id
         AND lg.ledger_category_code = rs.target_ledger_category_code
         AND primaryLg.ledger_id = rs.primary_ledger_id
         AND primaryLg.ledger_category_code = ‘PRIMARY’
         AND cfg.configuration_id = primaryLg.configuration_id
         AND cfgDet.configuration_id ( + ) = cfg.configuration_id
         AND cfgDet.object_type_code ( + ) = ‘LEGAL_ENTITY’
    
AND cfgDet.object_id = xlep.legal_entity_id
     AND xlep.legal_entity_id = reg.source_id
     AND reg.source_table = ‘XLE_ENTITY_PROFILES’
     AND reg.identifying_flag = ‘Y’
     AND reg.location_id = hrl.location_id
     AND O3.ORG_INFORMATION3 = to_char(lg.LEDGER_ID)
     AND O3.ORGANIZATION_ID = O2.ORGANIZATION_ID
         AND O2.ORG_INFORMATION_CONTEXT = ‘CLASS’
         AND O3.ORG_INFORMATION_CONTEXT = ‘Operating Unit Information’
         AND O2.ORG_INFORMATION1 = ‘OPERATING_UNIT’
         AND O2.ORG_INFORMATION2 = ‘Y’
    AND xlep.transacting_entity_flag = ‘Y’;

Legal Entity (legal_entity_id) in AP invoice header

Oracle default the legal entity (legal_entity_id) based on the balancing segment value of the liability account in AP invoice. The balance segment value assigned to legal entity and optionally operating unit in legal entity configuration pages.

Oracle allow user to enter other legal entity value in “customer taxpayer ID” in AP invioce header to change legal entity party and determine tax.

The legal entity (legal_entity_id) is used to determine tax. In E-Business Tax, the legal entity first party is get from the source product like AP, AR, etc.

Two Issues to be Considered When Implementing Multi Period Accounting (MPA)

1. Seems that drilldown functionality doesn’t work very well in GL journal entries from MPA. When clicking “Line Drilldown” button in GL journal entry form, below page pop up without SLA journal information.

image

2. MPA only generates the multi journal entries for defined periods in the ledger’s accounting calendar setup. If the periods are not defined enough, the remaining expense/revenue/recovery tax will be allocated to the last defined calendar. This is the standard functionality of MPA following the generic EBS accounting policy.

Preaccounting and Postaccounting Hook

“Create Accounting” program creates SLA journal entries for events. As we ever mentioned in the post before, we can add some logic in preaccounting hook or postaccounting hook to do something before or after accounting.  If you want to do something in preaccounting or postaccounting, you must know which events will/have be processed in the accounting program. How to get these event or transaction id?

Generally, you can get it from “p_report_request_id”. The request id will be updated to xla_events table and the request id will be shipped to hooks as a parameter. So you can get the event or transaction by below code in accounting hook.

  SELECT……
  FROM   xla.xla_events xee,
                xla.xla_transaction_entities  xte,
                xla.xla_ae_headers xah,
                xla.xla_ae_lines xal
  WHERE  xee.application_id = XXX
  AND       xee.event_id = xte.event_id
  AND       xee.event_id=xah.event_id
  AND       xah.ae_header_id=xal.ae_header_id
  AND       ……         –any other condition you want to add
  AND    XEE.request_id = p_report_request_id;

Have a Look at DB Policy from xla_transaction_entities

Today, I need to get an AP inovice id from an SLA event_id. From xla_events table, I got the entity_id for the event. And then I directly query xla_transaction_entities table with entity_id, but nothing can be gotten.

It is very strange. Under the same schema apps, why there is this entity_id in xla_events table, but not exists in xla_transaction_entities table? xla_events and xla_transaction_entities are both the synonym in apps schema from xla schema!!! They’re the views!!!

In R12, oracle introduce a new concept – DB Policy, which will be assign to table and table’s synonym. It is used to apply MOAC security to table and synonym. (Maybe my express is not very suitable here. I’m not a technical person. I can only do some simple queries微笑

Let’s have a look at below two sql query.

image

No DB Policy assigned to XLA.XLA_TRANSACTION_ENTITIES

image

Several DB policies are assigned to XLA_TRANSACTION_ENTITIES synonym in apps schema. That’s why I can’t query out my event entity from apps schema in normaly way.

Why can we get the event from xla_events? Let’s have a look at xla_events’s db policy.

image

There’s no db policy on xla_event synonym in apps.

OK. Finally, after understanding the mechanism,  I can get my event entity by adding xla. pre-fix now.

image

Multi Period Accounting (MPA) 4 – Comparison & Extension

Comparison:

MPA in SLA is too limited. It can only recogonize the prepaid expense or defered revenue evenly. And the recogonition schedule is also very limited. So, the revenue recogonition functionality can’t use this SLA feature to fulfill their requirement. You can see “Accounting Rule” setup is more flexible than MPA setup.

MPA in SLA are generating multiple period journal entries under one event (m:1), while revenue recogonition are generating journal entries under multiple events (1:1).

MPA in SLA complete the journal entries by “Complete Multi Period Journal Entries” program, while revenue recogonition complete the journal entries by “Create Accounting”.

Personally I still prefer the way of Receivables’s revenue recogonition.

 

Extension:

1. In FAH (Financial Accounting Hub) context, as you can create your own event model, you can select the way of Receivables’s revenue recogonition to create multiple event for multiple period and assigne event source to these event. The event source can be calculated freely by program code, so the shedule is free and amount (proporate percentage) is also can be calculated by flexible configuration.

2. In SLA implementation context, you can not create your own event model, you can only use the existing event, e.g. AP invoice validation, but you want to get a flexible recogonition schedule and recogonition percentage. You have two way to try to do:

  • Use create accounting hook. There are seversal kind of create accounting hook. Here, we can use “Preaccounting” and “Postaccounting”hook. Before accounting, we can change the “MultiPeriod Start Date” and “MultiPeriod End Date”to a proper value to get the same installment number of your flexible recogonition schedule, so the create accounting will generate this number of recogonition installments. After accounting, in postaccounting hook, you can programmly change the GL date and line amount against your flexible recogonition schedule setup, then optionally change back the “MultiPeriod Start Date” and “MultiPeriod End Date”. For other process, it is as the same as standard functionality.
  • In above solution, we can make it more easier. We don’t need to change “MultiPeriod Start Date” and “MultiPeriod End Date”and change it back. As these two attributes are finally go into event sources. We can change the event sources directly by revising the event source “view”. In the view, we directly make it to get the same installment number of flexible recogonition schedule. In postaccounting hook, you can programmly change the GL date and line amount against your flexible recogonition schedule setup. By this way, user is more transparent.
  • As the multi period accounting entries are generated during “Create Accounting”, copy the create accounting program and find out the part of multi period journal entry generation, change the schedule and amount calculation logic. Don’t touch the status control part, just change the GL date and amount calculation.

Multi Period Accounting (MPA) 3 – SLA MPA Feature

From Oracle R12.0, subledger accounting (SLA) is introduced to Oracle E-Business Suite, which is a event-based accounting generation engine. From R12.1, MPA feature is introduced to SLA architecture. It enables users to create accounting for a single accounting event for more than one GL period.  Taking an example for prepaid expense, when AP invoice validated, an accounting event “Invoice Validation” will be created, by create accounting, the multiple period accounting journals will created for this event. As one SLA accounting journal can have only one GL date, so the relationship between event and SLA journal is 1:m.

The multiple period accounting process is depicted as below diagram.

image

  1. Setup
  1. Define Accounting Derivation Rule (ADR): This is used to get the journal entry account for recogonition entries. (Dr. Expense, Cr. Prepaid expense)
  2. Define Journal Entry Type (JLT):
    • Accural journal entry type
    • Recogonition journal entry type
  3. From journal entry type perspective, the total accounting entries should be like this
    Dr. Accrual journal entry type  (first period)
         Cr. Liability  (first period)
    Dr. Recogonition journal entry type  (first period)
         Cr. Accrual journal entry type (first period)
    Dr. Recogonition journal entry type (second period)
         Cr. Accrual journal entry type (second period)
    …….
  4. Define Journal Line Definition (JLD) and assign Accrual journal entry type to JLD and set up accrual jlt’s ADR;
  5. Attach Recogonition journal entry type to Accrual journal entry type and set up multi period accounting options and recogonition JLT’s ADR;
  6. Define Application Accounting Definition(AAD) and Subledger Accounting Method (SLAM)
  7. Assign SLAM to ledger
  • Create an AP invoice. In AP invoice line assign multiple accounting options.
    image
    The three options in red circle will be mapping to accounting attribute in SLA to create multiple accounitng. The two options in blue circle doesn’t make sense currently.
  • Name Data Type Journal Entry Level Accounting Method Builder Components Accounting Attribute Assignment Rules Optional or Mandatory Validation Rules
    Multiperiod Option Alphanumeric Line Event class Should be assigned if another multiperiod accounting attribute is assigned Optional Should be Y for yes or N for no
    Multiperiod Start Date Date Line Event class Should be assigned if another multiperiod accounting attribute is assigned Mandatory if multiperiod option is populated  
    Multiperiod End Date Date Line Event class Should be assigned if another multiperiod accounting attribute is assigned Mandatory if multiperiod period type is not populated  

    After invoice created and validated, the AP invoice distribution has still only two lines. The recogonition accounting entries will not be inserted to AP invoice.
    image

  • After AP invoice validation, the AP invoice validation accounting event will be generated.
    image
  • After AP invoice accounting, the event’s status becomes “Final Accounted”, even though not all of the journal entries under the event are finally accounted.
  • After AP invoice accounting, the multiple journal entries are generated for multiple periods.
    image
    For opened AP period, the status is Final;
    For unopened AP period, the status is Incompleted;

    Note that it depends on AP period status.
    If the accounting calendar has not been defined for the period, the journal entry will not be generated, the remaining balance will be recogonized in the last defined period.
    Another very important point is the relationship between the first accrual journal entry and repeated recogonition journal entries. In xla_ae_headers, accrual journal entry is the parent journal entry of recogonition journal entries.
    Please also understand that all of journal entries are generated by “Create Accounting”. It is very important for us to extent the MPA functionality.
  • When next AP period is opened, the user can run the concurrent program “Complete Multi Period Journal Entries” to make the journal entry status from “Incompleted” to “Final”. Please note that it also depends on AP period status.
    The program has outout to tell you how many and what MPA journal entries are handled.
    Below is the a section of complete MPA journal entry program code. It shows how MPA journal entries are controled. (Package Name: XLA_MULTIPERIOD_ACCOUNTING_PKG)
    image
    After the concurrent program is run, the journal entry will be finally processed and can be transferred to GL.
  • Multi Period Accounting (MPA) 2 – Revenue Recognition

    From Oracle official concept, revenue recognition is renue recognition. It belongs to AR. Multi Period Accounting is the concept of SLA. But in fact, they are the similar thing. One of  initial purpose of MPA is for revenue recognition, but it is not flexible enough for revenue recognition. So revenue recognition is still handled in Receivables itself.

    Let’s have a look at revenue recognition first then compare it with multi period accounting feature in SLA.

    1. Create AR transaction and assign Invoice Rule in AR transaction header. There are two options for Invoice Rule:

    • In Advance: Receiving money at first before revenue recogonition
    • In Arrears: Receiving money at last after revenue recogonition

    2. Assign accounting rules in AR transaction line. Accounting Rule means the revenue recogonition schedule. The revenue recognition program/API calcualte the recogonition schedule against accounting rule. Only if invoice rule is assigned in header, accounting rule can be

    image

    3. Once AR transaction is assigned with invoice rule and accounting rule, the original revenue account distribution will be changed to defered revenue account. It can be set up in AutoAccounting functionality. And receivable account distribution will be changed agasint invoice rule. If “In Arrears” is selected, the recevable account will be defaulted against the autoaccouting setup for Unbilled Receivable.

    4. Complete the AR invoice.

    5. Create Accounting with Final option.

    6. Revenue Recognition: You have two options to recogonize revenue.

    • by concurrent program “Revenue Recognition Master Program”
    • by Revenue Accounting form. In the form, you can manage revenue and sales credits more flexible than concurrent program. You can schedule or unschedule the revenue recogonition and you can also partailly recogonize the revenue.

    image

    7. Whatever you select to recogonize the revenue by concurrent program or form, the functionality will create series of AR transaction distributions with multiple period. And for each period, it create accounting event with type of “Invocie Update”. So by revenue recogonition, multiple events will be created. It’s one of the difference between MPA and Revenue Recogonition.

    image

    Please note that, for normal transaction entry, you can’t key in unopened GL date. But for program inserting, it can.

    8. Create Accounting againt for the new created events and distribution lines. The SLA journal entries are created for all of the periods even though the period is not opened.

    • For the opened GL period and future entry statsu GL period, the journal entry status is “Final”and can be transfered to GL.
    • For un-opened GL period, the journal entry status is “Invalid”and event status is unprocessed.
    • It is very strange that it depends on GL period but not Receivable period.
    EVENT_ID APPLICATION_ID EVENT_TYPE_CODE EVENT_DATE ENTITY_ID EVENT_STATUS_CODE PROCESS_STATUS_CODE
    3362490 222 INV_UPDATE 23-2月 -13 3295913 U I
    3362489 222 INV_CREATE 23-1月 -13 3295913 P P

     

    AE_HEADER_ID APPLICATION_ID LEDGER_ID ENTITY_ID EVENT_ID EVENT_TYPE_CODE ACCOUNTING_DATE GL_TRANSFER_STATUS_CODE GL_TRANSFER_DATE JE_CATEGORY_NAME ACCOUNTING_ENTRY_STATUS_CODE ACCOUNTING_ENTRY_TYPE_CODE AMB_CONTEXT_CODE PRODUCT_RULE_TYPE_CODE
    5003040 222 1 3295913 3362489 INV_CREATE 23-1月 -13 N   Sales Invoices F STANDARD DEFAULT S
    5002952 222 1 3295913 3362490 INV_UPDATE 23-2月 -13 N   Sales Invoices R STANDARD DEFAULT S

     

    image

     

    image

     

    image

     

    image

    9. When you open the next period and run create accounting again, the journal entry status will changed from Invalid to Final and transferred to GL module.

    By summery, in revenue recogonition solution, Oracle insert the transaction ditribution line with multiple periods, create events for each period and create journal entries for each period. For unopened period, Oracle make its journal entries as “Invalid” until the period opened.