Monthly Archives: April 2010

Error 1004 from Oracle ADI (Application Desktop Integration)

After installing Oracle ADI (Application Desktop Integration),  we met the error 1004 during generating journal template.

In fact, it is not the error message from ADI, but Excel. You need to go to Tools > Options > Security, click the macro button, then check both two check-boxes.

Security Rule in Payables Accounting Process

Sometimes when you try to create accounting for an AP invoice or AP payment, an error message said that you can’t do that and suggests you to run Payables Accounting Process. But after running the suggested request, the accounting entries are still not generated.

Why? The security rule setup.

When creating accounting, the system may generate some entries like bank charges, realized/unrealized gain and loss, etc. These accounting entries may not able to be entered or seen by your responsibility against your organization’s security rule/cross validation rule setup. So if you create accounting or run Payables accounting process in a responsibility without required authorities, above error message will be shown.

How to handle it? We have several options to selection. You can select anyone against your situation.

1. Wait for a night and to see it tomorrow morning. Generally, Payables Accounting Process will be scheduled to run at night with a top authority in every organization.

2. Find a authorized responsibility and switch to it and run the process.

3. Contact system administrator or finance control admin to release the security rule and then run the process.

Significance of Inventory Organization field in Financial Options

Each of your Purchasing operating units can be associated with one inventory organization. When you associate your Purchasing operating unit with an inventory organization, items you define in this organization become available in Purchasing.

You can do this by selecting an appropriate Inventory Organization in Financial Options screen (Oracle Purchasing > Setup > Organizations > Financial Options > ‘Supplier-Purchasing’ alternate region > ‘Inventory Organization’ field).

It is important to note that the lov for this field doesn’t only show inventory organizations classified as an Item Master but also all other Inventory Organizations, provided the organization uses the same set of books as your Purchasing operating unit.

In cases where an inventory organization NOT classified as an Item Master is selected, all the items defined in the Item Master organization associated to this Inventory Organization, and ASSIGNED to this Inventory Organization, form the set of items available to this setup of Oracle Purchasing. As a result none of the items, which are not specifically assigned to this Inventory Organization (though defined in its Item Master) can be accessed by this setup
of Oracle Purchasing.

PO Submission Validation/Check

We all know there’s a button call “Approve…” in the right bottom of the PO ENTRY form. When you click it, what happens?

We all experienced that an error message said like “shipment line is not equal to po line, etc.”, so it looks Oracle Purchasing executes some validation/check process when you click “Approve” button.

Yes, you’re right. The package PO_DOCUMENT_CHECKS_PVT handle this validation/check. The procedure po_submission_check under the package is the main procedure. It calls some other procedures (also within this package) to do validation progress by progress against the document type, for example, for standard PO, PO_DOCUMENT_CHECKS_PVT.po_submission_check calls PO_CHECK procedure to do the checks.

The structure of PO_CHECK procedure looks like below

“l_progress := ‘001’;

l_progress := ‘002’;

l_progress := ‘003’;

l_progress := ‘004’;

So if you want to add some more validations restriction, you can add some new segment code like

“l_progress := ‘XXX_CUS01’;

It looks very flexible.

For some more information/application about this design, please see the post later.

Preference for Purchase Order and Requisition

To facilitate PO/requisition entry, Oracle introduce “Preference”  functionality to users. Before entering PO or requisition, you can specify perference, then a lot of information in PO line, PO shipment line, PO distribution line can be auto default to your new added lines.

To use Preference,

  • Open the PO entery window and directly go to Menu > Tools > Preference;
  • Enter the Inventory Organization, Tax Code, etc.

Note that Preference is just helpful for new added PO lines, but not for existing lines, for example, your copied PO document.

Receipt, Return and Correct

It is ok to use “return” to correct “receipt” and use “receipt” to correct “return“. For example,  you received 20 units and then found you just received 19 units, it is ok to return 1 unit to correct your mistake.

But is it reasonable?  No, because it corrupts your receiving and returning trail.  In above example, you actually received 19 unit, but in the system it seems that you received 20 unit and then found one unit damaged and then returned it.

So we recommend you to use correct to handle that.  Go to Purchasing > Receiving > Correction or Inventory > Transactions > Receiving > Corrections, find out the mistake PO/RMA and then enter the correct quantity.

Please take care that,  against your organization’s receiving option setup, you may select Receipt Routing as “Direct Delivery”, then you directly receive and deliver your goods by one one saving action. But when you correct it, you must do two steps for it, one is from your on-hand inventory to receiving inventory, the other is from your receiving inventory to your supplier.

Return an Approved Requisition

Occassionally, the buyers can’t help to buy some items for requestor, although the requisition has been approved by managers’ hierarchy.  To return an approved requisition, buyer can do it in “AutoCreate” window.

1. Find out the requisition line, which can’t be fulfilled, in “AutoCreate” window;
2. Go to “Tools” menu and select “Return Requisition“;
3. Enter the reasons to return the approved requisition, e.g. “The item has been expired”, etc.;
4. Click “OK” button.

Then the requisition requestor will receive a notification about this and he/she can modify the requisition and submit for approval again.

How to Correct (Reverse) the Zero-Cost Misc. Issue?

Reference:  When the Item Average Cost Down to Zero…

If you are unfortunately create a misc. issue with a big unit cost and make the item cost to zero and some cost variance balance, how to correct it? Please see below article, it may help you on this. (If you meet any more other situation, feel free to discuss with me!)  Please supposed that

Original Status Before Making Mistake

Item Cost: 5
Item Quantity: 10


Now you are required to make a misc. issue for quantity 1, unit cost is 5. But you make a mistake, you misc. issue for quantity 1 and unit cost 60, the accounting entry is
Dr. Account 60 (60*1)
Cr. Cost Variance 10 (60-50)
Cr. Inventory 50 (5*10)


Now we found the mistake and want to correct it.

1. Firstly, check if there is any cost update transactions occurred during these days, e.g. sales order issue, etc.

2. If there’s no sales order issue, the accounting entry variance should be

  Wrong Accounting Entry Correct Accounting Entry Variance
Dr. Account  60
Cr. Cost Variance 10
Cr. Inventry  50
Dr. Account 5
Cr. Inventory 5
Account 60 5 -55
Cost Variance -10 0 10
Inventory -50 -5 45

3. If there is sales order issue, the accounting entry variance should be

  Wrong Accounting Entry Correct Accounting Entry Variance
Dr. Account  60
Cr. Cost Variance 10
Cr. Inventry  50Dr. COGS  0
Cr. Inventory 0
Dr. Account 5
Cr. Inventory 5Dr. COGS  5
Cr. Inventory 5
Account 60 5 -55
Cost Variance -10 0 10
Inventory -50 -10 40
COGS 0 5 5


If there’s no sales order issue,

  • Use Update Average Cost functionality to update average cost 45, meanwhile select the same account during misc. issue. (Dr. Inventory 45; Cr. Account 45). Note that “Don’t” use GL journal to do it, GL journal can’t update item cost.
  • Create a GL journal to debit Cost Variance 10 and credit Account 10.

If there’s any sales order issue,

  • Use Update Average Cost functionality to update average cost 40, meanwhile select the same account during misc. issue. (Dr. Inventory 40; Cr. Account 40). Note that “Don’t” use GL journal to do it, GL journal can’t update item cost.
  • Create GL journal to Dr. Cost Variance 10, Dr. COGS 5 and Cr. Account 15.

Below is the Update Average Cost window in Oracle, you can try to find it from Cost > Item Cost > Average Cost Update like  below

Above solution is just used for the zero-cost misc. issue. If you just make a mistake for a little amount (it means “Cost Variance” account is not shown in the accounting entry), then that’s not complicate so much. You just need to go to update average cost to correct the variance easily.

By the way, the accounting entry can be gotten from Inventory > Material Transactions. Just go there, find out your transactions by transaction type, date, item, etc., then go to menu Tools > View accounting.

So long! And see you!


When the Item Average Cost Down to Zero…

Generally, the item cost (supposed you are using weighted average cost method) will not down to zero, but sometimes it is inevitable, e.g. your colleagues make a misc. issue with a big amount unit cost by mistake. If you are unfortunately meeting that, how about the accounting entry will be? Please see my below example, it may help you to understand it better.

Now, supposed that the item cost is 5, the quantity of this item is 10.

If you create a misc. issue with the quantity of 1 and cost of 20, then the accounting entry will be
Dr. Account 20
Cr. Inventory 20
The new updated average cost will be (50-20)/9

If you create a misc. issue with the quantity of 1 and cost of 60 (>5*10), then the accounting entry will be
Dr. Account 60
Cr. Cost Variance 10
Cr. Inventory 50
The new update average cost will be 0.

*Note that the average cost will not be under 0 forever.

If you continue to create a misc. receipt with the quantity of 1 and cost of 60, the accounting entry will NOT reverse the last one, it should be like
Dr. Inventory 60
Cr. Account 60
The new update average cost will be 60/10=6, this is just because the last cost is zero and last cost variance will not affect on this misc. receipt.

In all above accounting code,

Inventory is from your item setup (or others depending on your setup);
Account is from the accounting code you entered during misc. transactions;
Cost Variance is from your organization parameter setup like below.

TroubleShooting Installation Oracle Jinitiator (ISSET_SE, IMSCUI.DLL)

This is the first time I met problem during installing oajinit.exe (Some modern client setup don’t need to install oajinit, but nature JRE). Generally, it is automatically installed when you log into Oracle professional forms at first time. But this time, I met the problem like below whatever I choose to install it automatically or manually.

ISSET_SE makes access error on IMSCUI.DLL address 0x44763f90? What’s this? I can’t click any button whatever Closing, Cancel, Ignore.

Trying for some times with failure, I searched the metalink again. It says it is an authorization issue and need administrator authorization. I changed to the local admin account, then it is ok – no errors.

If you need any more details, go to metalink 166219.1 Trouble Shooting on installation Jinitiator on Win2000.

Please also note that my OS is Windows XP (WinXP), but I met the problem again.

By the way, yesterday, when I’m free, I also search this error on Google. Somebody says that it is because of Microsoft Pinyin 2010. It is indeed occurred after I installing the Microsoft Pinyin 2010. I also ever doubt about it and un-install it.

Customer Item

Sometimes the names are called differently by you and your customers. To record it, control it and print it on external documents, you may need to define the customer item and map it to your own item number.  The definition/setup path is Inventory > Items>Customer Items. The customer item can be set up at three different level.

“None” Level –  The top level. You can use this customer item for any customer’s order, sometime it is like an item’s alternative name.

“Customer” Level – You can only use this customer item number for this customer’s order. You need to specify the customer when you create customer item.

“Customer Address” Level – You can only use this customer item number for the orders which under the specified customer address.

Above three level setup could be able to fulfill your requirement to control the item utility. But if you hope to show different names against different situation on your external documents, you may need to set up/customize your own report.

Can’t Delete/Inactive a Customer Item

When you try to inactive a customer item, an error message show you “INV_CI_XREF_ACTIVE_EXISTS“, even though you have inactived any cross reference items on this customer item.

Why? And it seems  not reasonable.

But it is an intended functionality. Consulting with Oracle, it is explained like

When you go to re-activate the Customer Item Cross Reference the Internal Item does not display so users cannot re-activate the item.
i) Users made the Cross Reference inactive first. However the user is able to see the cross reference as inactive in cross reference screen .
ii) Then Customer Item is made inactive. Once the Customer Item is made inactive, then this item is no longer used to show any cross reference within the system. The view is designed correctly to not display any Cross Reference for inactive Customer Items.
iii) In case users want to see the cross references again, then users have to first make the Customer Item active from Customer Items screen. Now users will be able to see the Cross Reference, whether active or inactive, for this customer item in cross reference screen .
So this is not a bug but functionality.

If you have an Oracle metalink account, go to document 435287.1.

Language of Oracle Developer Suite 10g

If you’re using the Chinese version of an operating system (windows), you must install your Oracle Developer Suite in Chinese with below language and region setup. Otherwise, the program menu can’t be generated during installation.

After installation, if you would like to work in English environment, please change the “NLS_LANG” item in the register file with the path of “Root>HKEY_LOCAL_MACHINE>Software>Key_DeveSuiteHome_1” (Supposed that your home name is DeveSuiteHome_1) to “AMERICAN_AMERICA.WE8MSWIN1252