On this last day of 2007, I would like to take a moment to wish everyone a happy, healthy, blessed and peaceful New Year! Hope your 2008 is a great one!

Happy New Year 2008!

Affectionately yours,

Sanjit Anand

Someone ask offline for ACH, as term appears in my last post, so thought to have a briefing...here to go

What is ACH?
ACH stands for Automated Clearing House.

The rules for ACH transactions are prepared by the National Automated Clearing House Association (NACHA).

Normally it refers to a national network used by banks and other financial institutions to move transactions (i.e., checks) between financial institutions.

In other sense moving money electronically to and from checking and savings accounts. Typical example would be a check by phone or taking recurring payments directly from a checking/saving account.

• ACH is a “loosely” defined standard for electronic fund transactions.

• Why use ACH payment processing?

• More secured transaction than hardcopy check
• Convenience of electronic banking. No visits to bank
• Increases good-will with employees and suppliers
• Immediate, tangible savings on every ACH transaction vs. hardcopy check!!!

The Difference with ACH & Credit Card

The MAJOR difference between ACH and credit card processing is that a credit card transaction “captures”
the merchant’s funds from the consumer and essentially guarantees payment. An ACH transaction is a request to transfer funds. The transaction may reject for several reasons with the most common being NSF (non sufficient funds) or a closed account. The funds are not guaranteed.

To debit a consumer's checking or savings account you need their permission to do so. Failure to produce proof of the authorization either by voice recording (one-time payments) or an ACH Authorization form can have consequences.

Where it is popular?

ACH Transaction in US iteslf sharing more than 40% payment pie. This was provided in recent releases Federal Reserve Payment Study.

Suggested Reading

• The Evolution of the ACH

Today world credit cards have become part in the our daily life. Not only have credit cards and different electronic payment options,changed the way that consumers manage their personal finances, these have also enabled companies to reduce the risk associated with receivables and improve their cash flow, and reduce the product delivery timeline.Specially in some sectors where there is direct interaction with Consumer , it is important to manage and do the efficient credit processing and recording hundreds of credit card/ACH remittances in Receivable area.

During in implementation, many times it requires the ability to more efficiently manage receipts for credit card or ACH transactions, therefore this post will brief you on the seeded functionlity offered within EBS.

The Importance?

Oracle Receivables integrates with any feeder system, such as Oracle Order Management, to automatically record and process customer payments made before the delivery of goods or services , such of business Scenario that have company companies who is selling there product through internet channel, which is very similar as buying a Dell laptop from internet and paid most of time credit card transactions or Internet banking. Oracle in there EBS environment refers to these transactions as prepayments.

How ?

Oracle receivables creates prepayments as receipts before the related invoices are created. Later, a business event from your feeder system triggers the invoicing process in Receivables, and an AutoInvoice postprocess matches the prepaid receipts to their related invoices.

When you determine that a prepayment is required, you record the payment information in your feeder system, and the information is passed to Oracle Receivables.

What is expected in the setup side is , in Oracle Order Management, if an order is to be evaluated for prepayment, the underline order must utilize both a Payment Term (Collect Credit Card Prepayment check box) at the order level that enables prepayments and a Payment Type of Credit Card, payment terms for an order line are ignored. At the time of order booking, Order Management will determine if the order is Prepaid and make a call to the Oracle Receivables Receipts API for receipt creation.

The Oracle Receivables Receipts API creates a prepayment receipt in Receivables, and processes the receipt using the payment information provided.

Receivables immediately applies all prepayment receipts against the Prepayment application type, and records accounting according to a special prepayment receivables activity. Receivables reserves these receipts for subsequent reapplication to the invoice or invoices that are eventually generated for the order.

When the order is later sent to Receivables for invoicing, AutoInvoice creates an invoice that is marked as prepaid . Additionally, AutoInvoice initiates a postprocess matching program to identify any open prepaid invoices and search for matching prepayment receipts.

Prepayment matching occurs when a prepayment receipt is created from a feeder system such as Order Management. When the subsequent order is shipped and an invoice is created in AR, the AR Automatic Receipts program automatically creates an application of the prepayment against the Transaction. This ONLY occurs if the Prepayment and Transaction stem from the SAME ORDER. If the order is different then a manual application is required to apply the prepayment.

The other functionality for AR Prepayments is , you can also be modified or cancelled for the orders processed. You can:

• Increase the order amount
• Decrease the order amount: For credit card prepayments, you can refund the original credit card for a full or partial refund.
• Change an order without changing the order amount: If an order change does not result in a price change, then Receivables does nothing.
• Cancel an order: For credit card prepayments, Receivables refunds the original credit card using standard credit card refund functionality.For prepayments made with other payment types, Receivables places the refund amount on account.
• Reallocate prepaid funds towards an overdue invoice: You can unapply a prepayment receipt and manually reapply the amount to another invoice. When you later import the order into Receivables using AutoInvoice, Receivables considers the prepaid invoice that was associated with the receipt to be unpaid and treats it as a typical overdue invoice.

What's the difference between Deposits and pre-payments?

In reality , it looks very similar kind of process...

A prepayment is payment in advance of the delivery of goods or services.As discussed above , receivables creates prepayments as receipts before the related invoices are created. Later, a business event from your feeder system triggers the invoicing process in Receivables, and an AutoInvoice postprocess matches the prepaid receipts to their related invoices.

On the other hand Deposit a payment/receipt made against a Transaction or Invoice in advance.

How does a Receipt applied to a Credit Card Prepayment that is created and captured in Oracle Order Management get applied to the Invoice once it has been interfaced via AutoInvoice?

The matching of a Credit Card Prepayment Receipt and the associated Invoice is completed when the Invoice is interfaced into Oracle Receivables via AutoInvoice. The AutoInvoice process will look for all Invoice marked as 'Prepaid'. Once it finds one, it will then unapply the Prepayment from the Receipt that was created via the API AR_PREPAYMENTS_PUB.create_prepayment, and apply the Invoice.

Glossary of terms used in payments

• ACH Automated Clearing House
• BOC Back Office Conversions
• COGS Costs of Goods Sold
• DTC Depository Transfer Check
• EFT Electronic Funds Transfer
• FAS Financial Accounting Standards
• GDR Global Depository Receipt
• HPR Highly Protected Risk
• IRD Image Replacement Document
• JIT Just-in-Time Inventory
• KFC Kansas City Financial Center
• L/C Letter of Credit
• MICR Magnetic Ink Character Recognition
• NACHA National Automated Clearing House Association
• OBSA Off-Balance Sheet Arrangements
• POP Point-of-Purchase
• RCK Re-Presented Check Entries
• STP Straight Through Processing
• TEL Telephone Initiated Entry
• UCC Uniform Commercial Code
• VAT Value Added Tax
• WIP Work-in-Progress
• XBRL eXtensible Business Reporting Language
• YTM Yield-to-Maturity
• ZBA Zero Balance Accounts

Aggregator for monitoring Oracle related weblogs and news.

This page is under construction as of 2007/12/29. Thank you for your patience.

Steven Chan's Oracle E-Business Suite Technology

Is your company is in process of enabling credit card functionality within Oracle Internet Expense?

If answer is yes…
It means, once the credit card functionality is enabled in i-Expense, then its allows users to select open credit card transactions and include them in their expense report. The only pre-requisite for your organization would make an agreement with the credit card company to import an electronic file of credit card transactions that summarizes employees' expenses for a period. Once the electronic file is imported and validated, employees would be able to see their credit card transactions and then select them for expensing on their expense report.

AMEX (American Express) Corporate card is most popular one normally companies providing there employee who have requirement to frequent travel or other similar requirement.

American Express Corporate Service does provide these services to there corporate customer:

1. KR/KP-1205 Cardmember Listing Report
2. KR-1600 Corporate Express Cash Billed Data
3. KR/KP-1300 Monthly (Cyclic) Aging Analysis
4. KR/KP-1301 Interim Aging Analysis
5. KR/KP-1100 Industry Summary
6. KR-1022 Monthly (Cyclic) Billed Reconciliation Data
7. KR-1025 Daily Unbilled Reconciliation Data
8. KR-1072 Line Item Detail
9. KR-1075 Line Item Detail-Daily Unbilled

Once your company is decided, to go for AMEX Corporate Card, then Finnace IT/ERP team tries to find out the way to do integration within Oracle.From the above mentions list Oracle does only provide the direct integration for Items #7, which is KR-1025 Daily unbilled Reconciliation Data.

Item #8, 9 are treated as level 3 data, and important to know these kinds of data is not currently supported in Oracle.

Level 3 transaction data refers to the detailed transactions that constitute a single transaction line on the credit card statement. For example, a single hotel transaction can contain the detailed transactions of room charge, meals, and room service.

Not only Amexm there are few more for which Oracle does support credit card transactions data file formats in EBS:

• American Express KR-1025 format (file name is Card Daily Data Feed)
• Diner's Club Standard Data File format (file name is TRANS.DAT)
• Master Card Common Data Format, version 2.0
• Master Card Common Data Format, version 3.0
• Visa VCF4 format
• US Bank Visa format
• Bank of America Visa TS2 format

What is offered as a feature in EBS Suite:

1. Import Credit Card Transactions :As discussed above, if employees in your company use corporate credit cards, you can set up Internet Expenses to enable users to automatically import credit card charges from the card issuer into an expense report. Employees can also categorize each charge as either Business or Personal and, depending on your implementation, place items in dispute.
2. Flexible Credit Card Payment and Reimbursement Setup: Internet Expenses supports several different scenarios for paying the credit card company and reimbursing employees for corporate credit card charges. You can set up your system to indicate how payment must be remitted to the card issuer. Your choices are:

Next will share some setup activity to enable this functionality.

There was a requirement to provide a particular BU user to access to other BU PO's for approval. Normally such kinds of requirement always happen in today's complex business model . To implement this requirement a best we can do it is to use of new profile option which was introduced in Oracle 11i called HR: Cross Business Group , which need to switched on (i.e. set to "Yes") can be used .This new profile option makes it possible for Oracle Application users to view and modify certain specific areas of data across all business groups.

From 11.5.9 onward in HRMS there are two Security Models as:

• Standard HRMS Security
• Security Groups

The first one is Standard HRMS security which normally requires defining a security profile, and defining a responsibility for use by application users, whereas security groups means whereby you can reuse a responsibility and assign it to different security profiles in different business groups if required.

Typically Multi-national Companies would be benefited from security as they normally have concept of service centres using multiple business groups and security profiles.

The good and bad in new Security Group Model

The Standard Security Model on Oracle HRMS forces a responsibility to be tied to only one business group/security profile. This means that when new business groups are added a brand new set of responsibilities must be set up for the business group, even if the new set of responsibilities is identical in every respect to existing responsibilities assigned to another business group.

The new security group model can cut down dramatically on the number of responsibilities required as it allows responsibilities to be reused by many different business groups.

Here are the key points of how the new security group functionality works.

• Every time a business group is created a new security group of the same name is also created.
• Security profiles are defined the same way they are now. There is no change in this functionality.
• Form Assign Security Profile is activated under the new security model. This form allows a user to be linked to a security profile, responsibility, security group (business group) combination.
• Profile option HR: Business Group is no longer set manually for HRMS responsibilities. This profile option will be set dynamically when a user selects a responsibility/security group combination at logon.
• Profile option HR: Security Profile is no longer set manually for HRMS responsibilities. This profile option will be set dynamically when a user selects a responsibility/security group combination at logon.
• The \Security\User Define Screen in the System Administrator responsibility is no longer user to assign HRMS responsibilities to users as this is now done in the new Assign Security Profile screen.

Please note, that although it is possible in the new security group model to access many business groups through the same responsibility, because of the way users are now assigned to responsibility/security group combinations, an HRMS user can only access the data in one business group at any one time.This can be best understood in next section.

Overview of Standard Security Model versus new Security Group Model

Lets try to understand by a simple diagram below, what is here is 3 BU defined in global instance representing three BU X-Singapore, X- Australia and third one X-UK. The simple diagram below shows the difference in responsibility set-ups associated with each model.

In the standard security model when a user logs on or decides to change responsibility the list of responsibility names they have access to are presented to them to select from. In the security group model when a user logs on or decides to change responsibility the list of responsibility names and the associated security group (i.e. business group) assigned to the user are presented to the user to select from. This difference can be best described as:

3 steps away to switching to the new security model

The steps required to switch to the new security model are as follows;

• Profile option HR: Cross Business Group should be set to "Yes".
• Profile option Enable Security Groups must be set to "Service Bureau"
• Concurrent request Enable Multiple Security Groups must be run.

Not to Forget

Once the new security model is switched on, it cannot be switched off.

Suggested Reading

• Enhancements in Oracle HRMS Security in R11.5: Note:202478.1
• How Does the Cross Business Group Profile Option Impact the Application :Note:224822.1
• Understanding and Using HRMS Security in Oracle HRMS :Note:394083.1

Other related post in Security

• Know the “Security by Book” in Fixed Asset
• Understanding Security in : Oracle Financials and Manufacturing
• MOAC : From Multi-Org….To Multi-Access

This refers to foreign currency transactions that are immediately converted at the time of entry to the functional currency of the Set of Books in which the Transaction takes place.

Conversion uses a daily rate that is either supplied by the user or pulled from the Daily Rates Table at the time the transaction is entered.

The Rate Type is simply a label to identify the kind of rate used in a transaction.

In EBS suite, “User” rate Type is reserved, whereas other rate type like "Corporate" and "Spot" used for the seeded data in GL currency table.

When such a transaction is posted, a separate balance is kept in the GL_BALANCES table of all accounts entered in a foreign currency and their equivalent balance in the functional currency.

When you see the information in GL_BALANCES table, which keeps a cumulative balance for the foreign and the functional balance in an account; the rate information is missing. The rate information and the transaction-by-transaction detail is kept in the GL_JE_LINES table. GL_BALANCES only keeps the total amount entered for a particular account in a particular currency and the total functional amount for those foreign transactions. This allows for the segregation of portions of an account balance by the different currencies used in each transaction.

Finally lets compare all three

This table has compares the three Foreign Currency concepts:

Related Posts in series for “Dealing with Foreign Currency”

• Dealing with Foreign Currency : “Revaluation”
• Dealing with Foreign Currency :”Translation”
• Dealing with Foreign Currency : "Conversion"

Similar to Revaluation, there is another term called 'Translation' , a feature that linked with Foreign Currency transactions in General Ledger.

Translation

Translation is used to translate an entire set of books or balances for a company from the functional currency to a foreign currency.

This feature can translate both actual and budget balances. If the system have enabled average balance processing then the system can translate average balances as well.

Translation is frequently used to prepare financial reports for consolidation into global financial statements.

Translation uses periodic rates and, optionally, historical rates in compliance with FASB52.

FASB stands for Financial Accounting Standards Board which is a group within the Accounting field that issues bulletins on how to account for various financial events.

How does the system translate balances?

As per Metalink Note 1061166.6, FASB52 states that when translating a Trial Balance from one currency to another, the following conventions should be used:

How does the system translate balances?

Assets and liabilities are translated by multiplying the YTD balance by the Period End Rate.

YTD (translated currency) = Rate X YTD (functional currency)

Whereas,revenue and Expense balances are translated using the PTD balance for each period and the corresponding Period Average rate for each period; therefore, translation must be performed for the first period of the fiscal year forward to the period for which translation is required. Rates must also exist in the Period Rates table back to the first period of the fiscal year in which the translation is being performed.

PTD (translated currency) = Rate X PTD (functional currency)

In the Stock and Ownership Equity accounts, historical rates are generally used. but there are certain other special cases requiring the use of Historical rates.

Point that should be noted is EBS GL allows the use of an amount to be used as the translated balance for the account specified rather than calculating the amount using the Historical Rates. This feature allows the translated balance to be calculated outside of the application in lieu of setting up and maintaining the Historical Rates. Historical Rate usage is set up by specifying a range of accounts to use Historical Rate translation. This set-up overrides the above rules for using the Period End and Period Average rates.

Cumulative Translation Adjustment Account

Since the Balance Sheet and the Profit and Loss accounts are being translated using different rates, the translated Trial Balance is no longer in balance. The amount required to bring the foreign Trial Balance back in balance is called the Cumulative Translation Adjustment or CTA. This account is specified in the Set of Books set-up screen. The accounts and the amounts in them are created and populated dynamically when the Translation process get completed successfully.

You should note that CTA is typically a Balance Sheet account, the account type is determined when the account value is defined for the account

Some of the underline report for Translation :

• Historical Rates Execution Report : This is used to review the historical rates, mounts or weighted-average rates you assigned to individual accounts or ranges of accounts.
• Translated Trial Balance Report :This is for reviewing account balances and period activity after running translation.
  

..What happen in Oracle when Translation Run?

Translation is very table-space intensive. When this run its roughly doubling one period of data held in the GL_BALANCES table.

Suggested Reading at Metalink

• How Does the Translation Process Calculate the Translated Amounts? Note 188530.1
• DOES ORACLE APPS COMPLY WITH FASB 52? Note:1061166.6

Related Posts in series for "Dealing with Foreign Currency"

1. Dealing with Foreign Currency : "Revaluation"
2. Dealing with Foreign Currency :"Translation"

Lets take some thoughts on Oracle GL Foreign Currency exposure.As we know three key terminology most wildly used in GL that pertain to foreign currency. They are Conversion, Revaluation, and Translation.Lets start with Revaluation:

Revaluation - when ,what & why?

Revaluation is used if, and only if, you have foreign currency transactions (i.e.Conversion of foreign currency transactions). Revaluation uses the Period Rates Table. The Revaluation Rate is simply 1/Period End Rate.

The Revaluation Process:

1.)Finds accounts within the range of accounts specified that have all or a portion of their balance derived from foreign currency transactions;

2.)takes the foreign currency portion of the account balance and revalues it using the Revaluation Rate from the Period Rates Table;

3.)figures the difference between the current cumulative functional balance of these foreign transactions and the revalued functional currency balance calculated using the Revaluation Rate;

4.) creates an unposted journal batch to adjust the account balance to the new revalued balance calculated using the Revaluation Rate. The offsetting account is an Unrealized Gain/Loss account specified when running the Revaluation process.

Don’t be confused by the fact that the Revaluation process revalues transactions entered using Daily Rates with rates from the Period Rate table; the rate it is using is simply the "Daily Rate" on the last day of the month stored in the Period Rate Table.

The purposes of Revaluation is to "true-up" liability or asset accounts that may be materially understated or overstated at month-end using an exchange rate at month- end. This understatement or overstatement is caused by an unacceptable fluctuation in the exchange rate between the time the transaction was entered into and the period of interest for reporting, usually at a month-end. Revaluation is only necessary while the obligation remains unsettled (example ..the invoice is still unpaid or the receivable uncollected). The Realized Gain/Loss will be recorded at the time the obligation is settled.

Take a note revaluation can be done on any account, but typically,this is done for balance sheet accounts, whose balance is made up of open
transactions (ie. Accounts Payable, Accounts Receivable).

Revaluation is typically done for reporting purposes only; therefore, the journal entries produced as a result should be reversed in the following period.

Although Revaluation is intended to be used when transacting in currencies because of fluctuating Forex rate in the unstable economies, more and more company who is operating in Multi national environment , normally using this functionality by creating Journal Entries to reconcile their foreign subsidiary intercompany account.

The idea being that they are getting translated balances from their subsidiaries that do not balance to their inter company due to using different rates throughout the month to record inter company transactions.

Revaluation is used to revalue all these transaction at the same rate the foreign subsidiary used to translate their intercompany balance.

How to specifying PTD or YTD Revaluation

We can use the setting in the profile option 'GL: Income Statement Accounts Revaluation Rule'.

The following values are available:

PTD: Only PTD balances will be revalued for income statement accounts.

When you select PTD, the Revaluation program only revalues the PTD balances of your income statement accounts but continues to revalue YTD balances for balance sheet accounts.

YTD: Only YTD balances will be revalued for income statement accounts.
When you select YTD, then the revaluation program behaves as it did before, revaluing YTD balances for both your income statement and
balance sheet accounts.

Formula Used By Revaluation Calculation

YTD:

REVALUATION ACCOUNT AMOUNT= ((begin_balance_dr + period_net_dr - begin_balance_cr -period_net_cr) * revaluation_rate)
LESS
(begin_balance_dr_beq + period_net_dr_beq - begin_balance_cr_beq - period_net_cr_beq)

PTD

REVALUATION ACCOUNT AMOUNT = ((period_net_dr - period_net_cr) * revaluation_rate))
LESS
(period_net_dr_beq - period_net_cr_beq)

Will explore some more in foreign currency functionality area , keep watching...Happy weekend