, , ,

Advantages of using the XML Publisher Report

Advantages of using the XML Publisher Report

Choose Your Design Tool:
Xml Publisher Report formats can be designed using Microsoft Word or Adobe Acrobat – tools most users are already familiar with. There is no proprietary design studio component required, meaning no extra cost and no extra learning curve.
Data from Anywhere:
Xml Publisher can accept and format any well-formed Xml data, as well as generate the Data. It can therefore be integrated with any database. Xml Publisher also allows you to bring data In from Multiple Data Sources into a Single Output Document.
Deliver To Anywhere:
Deliver your content via Printer, E-Mail, Fax, Webdav, or publish your report to a portal. The Delivery Manager’s open architecture allows for easy implementation of custom delivery channels, as well.

Communicate With The World:
Report Layouts can be created for any combination of 185 languages and 244 territories. These Layouts are converted to Industry-Standard files for easy In-House translation or delivery to third party translation providers. Because each translation is a separate file, modifications can still be made to the Layout without impacting translations. Xml Publisher also offers Advanced Bidirectional and Font Handling Support.
Unsurpassed Performance:
Xml Publisher is based on the W3c Xsl-Fo Standard and it is the Fastest, Most Scalable Implementation in the world today. It can handle very large data inputs and generate output in less time, using very low levels of CPU time and memory.
Open Standards. Easy Integration:
Xml Publisher is built on Open Standard technologies. It is a J2ee application that can be deployed to any J2ee container. Data is handled as Xml and the Layout Templates created in the desktop applications are internally converted to Xsl-Fo, another W3c standard. Outputs generated by the application are also industry standards such as Pdf, Rtf and Html. The delivery protocols are Internet Printing Protocol (Ipp), Webdav, Ftp, As2.
/by
, , , ,

TDS Setup for suppliers in R12

This document explains the basic setup needed to setup TDS for suppliers in R12.

Setup Steps
1. Pan Number at the Legal Entity level
When Legal Entity is created, enter the Pan Number in the “Pan” field. This field is mandatory for India territory.

2. Attach Inventory Organization in Location
Create the inventory organization and attach it with the location. Only those locations with inventory organization attached will appear in the location field of organization additional information form.

3. TDS Lookups (Seeded values)
We need to make sure that the following seeded values are available in Asis/Pacific Localization lookups.
  1. JAI_AP_TDS_SECTION_TYPES
  2. JAI_TDS_SECTION
  3. JAI_TDS_VENDOR_TYPE
  • TDS Vendor type is used for categorizing vendors from TDS perspective.
  • TDS Vendor type and TDS section code determines TDS rate and surcharge based on TDS thresholds defined
4. Create Organization Additional Information and Tax calendar for the organization
For all inventory organizations, additional localization information needs to be maintained.
  1. We need to define two records here for each organization, one with location and another without location.
  2. Excise details are mandatory for manufacturing organization
  3. For trading organization, excise tab details should not be filled and trading information tab should be entered.
  4. Pan No, Tan No, Ward No are currently redundant and not used
  5. Only one tax calendar can be active at a time.



5. Create Regime Registration for TDS
Query the recode with Regime code as TDS. All the details except registration value in the registration tab will be defaulted. Enter appropriate registration value against the registration name. These details would get defaulted for the operating units and can be changed, if required.


6. Enter TDS Period

We need to enter the TDS period for calculating the TDS amount for the period. TAN number will be available in the LOV only if regime registration is completed for TDS.

7. Create TDS Authority as Supplier
We need to create a supplier with supplier type as “India TDS Authority”. Enter all the mandatory fields like payment method, payment terms etc.
Other suppliers are entered as usual.
8. Create a Tax definition
We need to create different tax codes for different section codes and tax rates. Enter the basic percentage in the Percentage field and enter additional surcharge details. System will automatically calculate the total percentage. If any lower TDS is involved for certain forms like Form C, enter the certificate name and original tax percent.

9. Create Threshold Setup
Threshold is defined for each section by TDS vendor type. Thresholds applicable to specific vendors are defined as exception setups and vendors are assigned manually to this setup.
There are two types of Thresholds
  1. Single – Applicable to a Single transaction
  2. Cumulative – In this case, TDS is applicable only when the cumulative purchases made from the supplier in one financial year exceeds specified limit i.e, Cumulative threshold amount.
Multiple thresholds with different tax rates may exist. For example, tax rate is 12 percent for the cumulative amount below 10L and tax rate of 12.24 percent for the cumulative amount above 10L.
Before cumulative threshold is reached, single invoice amount greater than the threshold limit for a single invoice attracts TDS at predefined rate
Threshold Amount = Total Invoice Amount – Total Invoice Cancelled Amount – Total Invoice Apply Amount + Total Invoice Unapply Amount


10. Create Supplier Additional Information

We need to add Pan Number, TAN number and TDS Details for each supplier. We should create two records for each supplier, one without supplier site and another with supplier site. Pan number should be confirmed at the supplier level and it can not be modified thereafter.
Enable invoice approval checkbox, if TDS invoice created for the supplier should be in approved status.
Enter the default TDS Section and/or Tax name, if TDS tax code should be automatically defaulted in the invoice for this supplier. Tax code defaulting logic is given below.
If default section and default tax name are not given in the supplier additional information and if tax name is not manually entered in the invoice, then TDS will not be calculated and no TDS invoice will be generated.


11. Review and Complete DFF and Profile Settings

TDS tax code is now captured in Global Descriptive Flexfield in the invoice distribution screen. We need to make sure that the DFF JG_AP_INVOICE_DISRIBUTIONS is enabled.
Also define the following profile option values at the responsibility level
Profile Options
Values
JG: Application
Payables
JG: Product
Asia Pacific Localization
JG: Territory
India
The country used against the Legal entity / Operating unit has to be India
Invoice Transaction
Let us now create an invoice for the “Google” supplier.
Invoice Amount – 90000
TDS Tax rate – 12% if the invoice amount is above 50001
Hence the TDS Invoice amount will be 90000 * 0.12 = 10800
Make sure that the taxation country is defaulted as ‘India’

As soon as the invoice distribution details are saved, the tax code ID is defaulted in DFF as shown below. The user can change the tax code, if required.
When the invoice is validated, system will run a concurrent program which will create TDS invoice (Credit Memo) for the supplier and another standard invoice for the TDS authority.

Click on ‘View Output’ for ‘Import TDS Invoices’ concurrent program to check for any errors. The report shows all the imported and error lines.
The invoice number format for the credit memo for the supplier and standard invoice for the TDS authority is given below
Supplier Credit Memo: (Original Invoice Number)-TDS-CM-(Serial Number)
Invoice for TDS Authority: (Original Invoice Number)-TDS-SI-(Serial Number)
References
  1. Oracle Financials for India – Implementation Guide
  2. Oracle Metalink Docs
/by
, , ,

When to enter a Value in to a Value Set ???

Everyone who has an experience of setting up a Set of Books would have come across the 3 C’s, Calendar, Currency and Chart of Accounts. For creating Chart of Accounts we will create segments and value sets for each of the segments to hold value. Then we will enter the values for each and every value set.

The normal order/steps involved in creating a Chart of Account is:
1) Create Value Sets First
2) Create Structure and assign the value sets created
3) Assign Flexfield Qualifiers
4) Compile the Flexfield
5) Enter Values in to Value sets
6) Define Segment Qualifiers

Why the above order becomes standard, who suggested it, does oracle recommend this order ?
All materials and books says that Value sets are the container of values, one can create a value set then enter values and then attach to the Structure.

A question got raised in my mind as to why one must enters the value in to the value set after the flexfield is compiled, why not we can enter values in to value set as a second step, that is, as soon as we create a Value set ?
Have asked few who were on the field, they said there is no difference , you can enter values as and when you please.

But later one fine day, have found the logic why it was done like this way.

The logic is….

Every one knows that every value in a value set will have a Segment Qualifier.
Standard segment Qualifiers for all the segments will be ” Allow Posting” and ” Allow Budgeting” and for Natural Account segment the additional qualifiers will be ” Account Type” , “Control Account” and “Reconciliation Flag”.

How the system identifies that whether a segment is an normal segment or a natural accounting segment ?
System will identify it only based on the Flexfield Qualifier attached to that segment. There is no setup that is related to the value set alone for this identification.

“Which means that a Value set will show those segment qualifiers only when that value set is assigned to a Segment which holds natural account flexfield qualifier”

When we try to enter a Value for a value set which is created for holding accounting segment values, without creating the structure, the segment qualifiers will show only “Allow Posting and Allow Budgeting ” qualifiers and not the others, since there is no way that system can assume that this value set is created for an Accounting Purpose.

So the accepted methodology is to enter the value in to value sets after it has got assigned to a Structure.

/by
, ,

GL Translation Process

What is Translation?
Foreign Currency translation is the process that restates functional currency account balances in another currency.
Translation converts balances from your functional currency to a foreign currency, you can translate both actual and budget balances. if you have average balance processing enabled the system can translate average balances as well.
Tranlation is frequently used to prepare financial reports for consolidation into global financial statements. It is also used in highly inflationary economies to produce reports in a more stable currency.

When do you run translation:
Translation should be run at the end of the period, after all transactions have been processed and reconciled.

How does the system transalte balances:
– Assets and liabilities  are translated by multiplying the YTD balance by the period end

How do I check that the translation has been carried out correctly?
Reconciliation of the Cumulative Translation Adjustment (CTA) Account:
1.Take the total of your P&L (Revenue and Expense) accounts and multiply this amount by the period average rate defined.
2.Take the total of assets and liabilities and multiply this amount by the period end rate.
3.Take the total of your retained earnings and use the historical amount or multiply by historical rate (whichever way you have defined it).
4.Add 1,2 and 3 together. This should equal the amount in your translation adjustment account.
5.Make sure no other entries have been made to the account. If there has been, they would have to be reversed in order to reconcile the amount.

Translation using Historical Amounts.?
In some situations, you may want to use Historical Amounts to translate certain accounts. However, when Historical Amounts are used in the very first period ever translated, this creates a large Rate Adjustment for the same amount which distorts the Cumulative Translation Adjustment account.
The translation code cannot distinguish between how much of the Historical Amount defined is attributable to the Beginning Balance and how much is attributable to rate fluctuation in the period, so the entire amount is thrown into the Rate Adjustment bucket.
For example, in the first period ever translated where there is no period activity for the account, when you use Historical Amounts, you may see:
Beginning Translated Balance = zero.
Rate Adjustment = the Historical Amount defined.
Ending Translated Balance = the Historical Amount defined.
By definition, when translating on YTD basis, the Historical Amount defined is equal to the YTD Translated Balance. You would like to know how to correct the situation so that you do not have a large Rate Adjustment in the first period translated.
The workaround is to “back into” an Historical Rate, based on the Historical Amount that you want to achieve for the period. You should use this Historical Rate in the first period ever translated, then in the subsequent months use the appropriate Historical Amount. This eliminates the large Rate Adjustment in the first period ever translated.
Does the Translation of Owner’s Equity Accounts comply with FASB 52?
The profile option GL: Owner’s Equity Translation Rule should be set PTD to comply with FASB 52.
Set to PTD:
Ending Translated balance = Beginning Translated Balance +
(Current month activity in Functional Currency * Current Month Historical Rate)
Set to YTD:
Translated Currency YTD = Functional Currency YTD * Rate
YTD translation of Owner’s Equity Accounts calculation does not take into account the historical rates that were in effect at the time of each transaction in the account.
How to change Translation from Historical to Period Rates?
1. Delete the historical rate from the Historical Rate Form.
(Note if you have a Prior Historical Rate – this will have to be deleted. You may have a prior historical rate if you deleted the historical rate and reran translation without purging the original translations using the original historical rate).
2. Define a period-end rate in the Period Rates form.
3. Purge translated balances to get rid of the original historical rate.
The amount used depends on whether the account to which the historical rate or amount applies is a revenue/expense, asset/liability, or owners’ equity account:
Revenue/Expense: The amount is treated as translated net activity for the period.
Asset/Liability: The amount becomes the YTD translated balance for the
account.
Owners’ Equity: If the profile option GL: Owners Equity Translation Rule is set
to PTD, the amount is treated as translated net activity for the period. If the profile
option is set to YTD, the amount becomes the YTD translated balance for the
owners’ equity account.
Restating Balances Previously Translated with the Year–to–Date Rule
Older versions of General Ledger always translated owners’ equity accounts using the
Year–to–Date rule. If you subsequently switch to the Period–to–Date rule, your owners’ equity accounts will be translated using this rule for new translations only. Previously translated owners’ equity balances will not change. If you wish, you can restate your previously translated owners’ equity balances.
To restate your previously translated owners’ equity balances using the Period–to–Date rule:
1. Purge the old translated balances for each period to be restated.
2. Change the GL: Owners Equity Translation Rule profile option to PTD.
3. For each period to be restated, use the Historical Rates window to delete the rates used to translate owners’ equity accounts, as follows:
— Retained Earnings: Delete any non–historical rates.
— Other Owners’ Equity accounts: Delete any period rates.
4. Run translation. Your owners’ equity balances will be translated using the PTD rule.
Note: If you change a period rate after you’ve already run translation, you mustretranslate your account balances for the period whose rate has changed.
Prior: General Ledger uses the most recently entered historical rate or amount for
your balance sheet accounts, and assigns it the rate type Prior. If you have average
balance processing enabled, General Ledger rolls this historical rate or amount
forward using the rate type Prior.
Period: If you have never defined a historical rate or amount for an owners’
equity account, General Ledger uses:
The period– average rate if the profile option GL: Owners Equity Translation Rule is set to PTD.
The period-end rate if the profile option GL: Owners Equity Translation Rule is set to YTD.
Calculated: This rate type is only used when the profile option GL: Owners
Equity Translation Rule is set to YTD. It is only applicable to the first period of
your fiscal year. If you have never defined a historical rate or amount for your
retained earnings account, General Ledger calculates a rate and assigns it the rate
type Calculated.
Profile related to Translation:
GL: Owners Equity Translation Rule
Specify the rule General Ledger follows to translate owners’ equity accounts when you
have not entered specific historical rates or amounts.
The following values are available:
PTD: The Period-to-Date rule is used to translate owners’ equity accounts. For
each period for which you translate owners’ equity accounts, the historical rate is
set to the period-average rate.
YTD: The Year-to-Date rule is used to translate owners’ equity accounts. For
each period for which you translate owners’ equity accounts, the historical rate is
set to the period-end rate.
The default value for this profile option is PTD.
GL Transaltion : Revenue/Expense Translation Rule:
Revenue and Expenses are translated according to the GL Translation: Revenue/Expense Translation Rule Profile setting (PTD or YTD). If this is not set then Revenue and Expenses are translated by multiplying the PTD balance by the Period Average Rate
Purge, Transaltion and Retranslation:
Retranslation again will be done on the basis of the status column in the gl_translation_statuses table. If the status is current, there will be nothing that will be translated.
Translation will run in 2 phases. First phase gets the out of date records Translation Status – C.Here it translates only those records, which are out of date due to some adjustments.if it is the account adjustment all the foreign currencies need to be translated again. If it is a rate adjustment only that currency needs to be translated Second phase gets the records for periods, which have never been translated.
As an Example, if the Functional Currency is USD and translation is done into GBP, INR and CAD. If there is a change in the accounts in the functional currency, then Translation needs to be run for all the currencies. On the other hand, if there is a change in period rates or Historical Rate/ Amount for any currency, it will be sufficient if Translation is run for that particular currency.
Purge means Translation status for the period will always be Never Translated for the periods for which it is run. It will re-state the values in the gl_translation_tracking table. It essentially is translating every record whether or not that is current or out of date as the foreign currencies will not be there. In a way it can be said to be direct phase 2 of translation.
Running translation for the first time
You cannot translate in the first period in your calendar.
The first Consolidation must be performed as YTD not PTD for two reasons:
1.PTD will not generate a correct Opening Balance figure.
2.The Consolidation Journal maybe unbalanced by the amount of the CTA adjustment if there are Accounts defined with a Historic Conversion Rate.

/by
, , ,

Auto Invoice

Auto Invoice??
Auto Invoice is a tool that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

What Module data can be integrated?

Oracle Order Management
Oracle Project Accounting
Oracle services

To make fully functional what else required?
Loader program
Validation program

Top 10 reasons for using Auto Invoice
1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice?

AutoInvoice is a tool consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR

Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.

Working of Auto invoice 
Validates data
Inserts records
Deletes interface data
Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.

•Deletes all rows where interface_status =‘P’

•Ra_interface_lines

•Ra_interface_distributions

•Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices.

A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table.

When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report.
Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works
Normally, Auto Invoice can be divided into three major phases

Pre-grouping: here the validates all of the line level data takes place
Grouping: groups lines based on the grouping rules and validates header level data
Transfer :validates information that exists in Receivables tables

What happen when AutoInvoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.

Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?

Select, insert and update and delete take place on certain tables once it is logged out.

Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert

– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling
Records that fail validation are called ‘Exceptions’

Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL
Errors can be corrected in the Exception Handling window
Once corrections are made, Auto invoice must be resubmitted
Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

/by