Read this:

• KNOW WHAT IS SUPPLY CHAIN MANAGEMENT (SCM)? : An Overview
• SCM Subsystem

This article is an easy guide for Techno-functional consultant to understand SCM from Implementation as well as Oracle Application product Prospective.Lets start the topic with some of high points of earlier post .

• As mention in earlier post a Supply Chain is a network of retailers, distributors, transporters, storage facilities and suppliers that participate in the sale, delivery and production of a particular product.
  • Make a note a supply chain is product specific, not company specific
• Supply chain management (SCM) is a systematic approach to manage the entire flow of information, materials, and services from raw material suppliers through factories and warehouses to the end customer.
• Moreover , SCM involves the flows of material, information and finance in a network consisting of customers,suppliers, manufacturers, and distributors.
• You should be clear with Flow that SCM can manage :
  • The flow of actual materials, the top middle bars
    • From suppliers : flows of raw materials, intermediate products, finished goods
    • Reverse material flows : returns, repairs, servicing, recycling, disposal
  • and the information flows
    • From suppliers : manufacturing capacity, delivery schedules, promotions they have going
    • Reverse flows : sales, orders, inventory, quality, promotions
  • And finally, there are financial flows:
    • From suppliers: Credits, consignment, payment terms, invoice
    • Reverse Flows : payments, consignment
• Supply Chain Management is the management of the entire value-added chain, from the supplier to manufacturer right through to the retailer and the final customer.
• SCM has three primary goals: Reduce inventory, increase the transaction speed by exchanging data in real-time, and increase sales by implementing customer requirements more efficiently
• The need for SCM is because effective Supply Chain Mgt. is the next logical step towards increased profits and market share.
• Supply Chain Management (SCM) in line manager prospective is "let's-keep-things-moving-efficiently".

Read the rest of this entry »

It was request from one of reader to provide some information for new organization Hierarchy model between two release of 11i and R12.Therefore invite you to run through the following diagram as below with some basics.

Read the rest of this entry »

Read this

• Managing Cash , EBS and SWIFT

Whenever the Bank Intergation is required , the very first term would come in mind is SWIFT , a very similar way as EDI sort of messaging servics that Financial sectors are using .So let explore what is SWIFT and how many of these can be potenially targetted for Integration .

What is a S.W.I.F.T?

S.W.I.F.T. (or SWIFT) stands for Society for Worldwide Interbank Financial Telecommunication. It is a non-profit organization comprised of member financial institutions. It was established in 1973 by European bankers who needed a more efficient and secure system for inter bank communications and transfer of funds and securities. Until then, all inter bank communications were by telephone, telex, courier, or mail.

Swift Messages

SWIFT messages are preset and referred to by category numbers called MT numbers. Through this network (a.k.a. SWIFTnet) information can be exchanged using special crafted messages known as Message-Types (MT).

Read the rest of this entry »

MOAC allows users of a single responsibility to perform task in multiple operating units without having to switch responsibilities.Out of Box functionality of MOAC, there is something called "Multi-Org Preferences".

What is "Multi-Org Preferences"?

Multi-Org Preferences allows you to control the list of operating units to which you have access.

Lets say you have around 20 OU's divided into three major Region (North America, Europe and APAC). Lets say within APAC region you are responsible for all 7 OU's within Region.

Read the rest of this entry »

As we know in R12 , the existing Multiorg architecture includes a new feature Multiple Organizations Access Control (MOAC). Lot of queries and question were asked by my readers, therefore this post completely focus on some of technical Insight because of architectural change.The minimum you should suppose to know is that Access Control feature has backward compatible. Technically means that there are no code or procedural changes if MOAC is not implemented (i.e. The user is assigned one operating unit for a responsibility).

Read the rest of this entry »

Very common problem your end user normally reports "FRM-40654: Record has been updated by another user. Re-query to see change"
...

Cause: Another user has updated this record since you performed a query and has changed at least one field in the record. Your actions have not changed the record in memory.

Action: You can update or delete this record now only if another user has restored the field values back to the way they were when you performed the query. Otherwise, you must re-query to fetch and display the new record into the form before you can update or delete it.

This is very common issue after migration or with any external interfaces, If you have taken care by TRIM function , then there is nothing to bother, else your end user will report this, which need a proper investigation.

The reason and resolutions

This error is caused because of any of the following

1. FRM-40654 Caused by unnecessary foreign key fields
2. Trapping problem of some sort in Oracle Forms with triggers, execute_query
3. Record locked ie Another user changed the record and saved after you queried the record
4. Some of the columns have trailing spaces, and Forms by default truncates the trailing spaces and hence the database value and form value will not match and you get the error.
5. Date field stored in database is with time stamp but form showing only Date component and hence Form value and database value will not match and hence you get the error. (you need to remove the time component in DB or show date time in Form)

Bottom line is the value in Database and value in form are not matching, so you have to check for each column and see where is the difference. I know this is going to be painful checking out every field for changes, but there is no other options left.

What you have to do is to investigate the column first and then make a update with TRIM function as

update <table name>
set <column name> = trim(<column name>);

Resolutions Example

Example 1

User reported the problem at suppliers site,

• Taking which table hold the supplier data.
• Check a column value for a VARCHAR2 column. In this case VENDOR_SITE_CODE. or Address_line1
  SELECT '['||vendor_site_code||']'
  FROM po_vendor_sites_all
  WHERE vendor_id=<VENDOR ID>;
• If the column has trailing spaces the output will looks very similar to

[SITE ]

• Once you get the column , then use update script, with TRIM function, like
• UPDATE po_vendor_sites
  SET vendor_site_code = TRIM(vendor_site_code)
  WHERE vendor_id = <VENDOR ID>;

Example 2 : Your bank statement Reconcile program ending up with error

In similar way you have to check like

1. select '['||trx_text||']' from ce_statement_lines_interface;
2. select '['||trx_text||']' from ce_statement_lines;

The above mentioned scripts need to be run against all varchar2 columns, not just trx_text, in either ce_statement_lines or ce_statement_lines_interface.

If trailing spaces are found, run the following scripts to correct them:

update ce_statement_lines_interface
set trx_text=trim(trx_text,' ')

Change the column and table name to those with trailing spaces

Example 3:Invoice Through Auto invoice (External system)

Same way to find the column and apply by this kind of script
update ra_customer_trx_lines_all
set DESCRIPTION = trim(DESCRIPTION) ,
SALES_ORDER = trim(SALES_ORDER)
where customer_trx_id = <customer trx id>;

What if , not have "trailing space" Problem

Confirm with DBA , is table is having any lock or not . If there is no lock then you need to ask Oracle support .

Tips of Developing Conversion and Interface

Always use TRIM function in your insert portion of the program

Further Reading on metalink

• 246971.1 :FRM-40654 When Modifying Records
• 1003629.6 Troubleshooting FRM-40654

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