Generally, Oracle 11i fulfills multi-org feature by the views in apps schema; while Oracle R12 fulfills the feature by Virtual Private Database feature.
In 11i, the base table are created by product schema, e.g. oe_order_headers_all in ont db schema. In apps schema, there’s a view called oe_order_headers, which has a where clause like “org_id = SUBSTRB (USERENV (‘CLIENT_INFO’), 1, 10)” . So every user accessed application has his own userenv, then every user can access his own org’s data.
In R12, the base table are also created by product schema, e.g. oe_order_haders_all in ont schema. In apps schema, there’s a Synonym oe_order_headers with VPD feature to fulfill the multi-org feature.
In 11i, we set MO: Operating Unit profile on the responsibility level, then all of user logged from this responsibility has this OU(org_id) client info, so this user can only access this operating unit data from this responsibility.
In R12, we set up a security profile, which can include several operating units, then we assign this security profile to the profile option “MO: Security Profile” on the responsibility level. The user logged from this responsibility can access all of OUs’ data in the security profile.
The following steps are required to add new operating units to your organizational structure:
1.1 Revise the Organization Structure.
1.2 Define Sets of Books (optional).
1.3 Define Locations.
1.4. Define Organizations and Relationships.
2.1 Define Responsibilities.
2.2 Set Profile Options for Each Responsibility linked to the new Operating Unit.
1.1. HR: Business Group
1.2 HR: Security Option
1.3: HR: User Type (FOR accessing HRMS functions)
1.4 HR: Cross Business Group
2.1. GL: Set of Books(11i)
2.1 GL:%Ledger% (R12)
2.3 GL: Data Access Set. This profile option to control the ledgers that can be used by Oracle General Ledger.
3.1. MO: Operating Unit
3.2. MO: Security Profile (R12)
3.3. MO: Default Operating Unit
4.1 Tax: Allow Override of Tax Code
4.2 Tax: Invoice Freight as Revenue
4.3 Tax: Inventory Item for Freight
5.1 Sequential Numbering
5.2 INV: Intercompany Currency Conversion
2.3 Run the Replicate Seed Data concurrent program.
3.1 Define Inventory Organization Security.
3.2 Define Intercompany Relations (optional).
3.3 Implement the Application Products.
4.1 Secure Balancing Segment Values (optional).
4.2 Run the Setup Validation Report (recommended).
4.3 Implement Document Sequencing (optional).
4.4 Set the top reporting level (optional).
4.5 Set up conflict domains (optional).
One user reported she can’t return on PO receipt to vendor and meet the error code APP-PO-14094 like below:
When meeting this message, we always check the status of PO/PO line/PO shipment lines. If the status is cancel or closed, the user may can’t do RTV. But for this case, the status of PO/PO line/PO shipment line are all “Open”.
Then I think maybe the goods has been sold out. In Oracle, if you want to do RTV, you must make sure there are enough goods in the original inventory organization and subinventory when you received them. But for this case, there’re enough goods over there.
At last, I checked “Transaction Status Summary” window and found there’s a pending transactions over there. This is maybe because something happens when the user try to do RTV(return to supplier) and it makes transaction pending and failed. I deleted this transaction from this form by “Delete” button. Then the user can do RTV again and no error message “APP-PO-14094” shows again.
In the real environment, for the physical inventory (stock take) every time, you may follow the below process:
- Plan your stock take and select a big date to freeze your stocks, generally it is the weekend or before the public holiday;
- Notice the related department not to make any transactions to change the stock status from freezing time till to another notice;
- Freeze your stock on the freezing time;
- List/export the quantity of the stocks in your books/system;
- Generate and print tags for your stocks against above list;
- Count and record the quantity for every kind of stocks in you warehouse and attach the tags to the real stocks in your warehouse;
- If there’s any stocks without printed tags, generate the new one, attach it to the real stocks, and record it in your list
- Notice the related department it is ok to do any transactions;
- Compare the quantity from warehouse with the one from your books/systems
- Analyze the reason of the variance and submit the variance adjustment for approval
- Against the result of step 9, adjust the quantity and cost amount in your books/systems to the real one;
- Post the adjustment journals to the general ledger and affect the financial statement
Mapping above process to Oracle standard physical inventory (stock take) process:
- Mapping to above step 1, define a physical inventory from your IC module – go to Counting > Physical Inventory >Physical Inventories, select the organizations and click “New”button. In below screen dump, enter the the stock take name, whether to need approval for adjustment (generally select “always”), tolerance and the subinventories to be counted and click the check option for “Dynamic Tag”.
- Mapping to above step 3, click the “Snapshot” button on above screen dump;
- Mapping to above step 5, click the “Tags” on above screen dump, then a new screen dump jumps out. In this screen dump, enter the first tag number and the increments, then click generate. After generation program completes, run and print the standard report “Physical Inventory Tags” or your customization tag printing program.
- Mapping to the 6th and 7th steps, enter or import the counting result to the counting windows:
- Mapping to above steps 11, approve or reject the variance in the “Approve Adjustment” window
- At last, mapping for the step 12, launch the adjustment journal from the “Tool” menu. During this step, you need to enter the adjustment expense account
- For other steps, you can manage them out side the ERP system
Except above the whole physical inventory/stock take process, you can also use the “Purge” function to delete your old stock take history or generated tags. To perform purge, just select the “Perform purge” under the “Launch adjustments” in “Tools” menu.
Some days ago, a user reported that some quantity of the item is not available but he/she can’t find out the reservation records. Why? After investigating, there’s a pending transaction in the system while the related SO has been closed and all of the move orders for this SO are closed too. To remove the pending transaction and restore the availability of the lost goods, we find out below script from metalink. In below script, Oracle help you on below situation:
- Move order released from SO, Move order in Pre-approved status; Move order has not transacted
- Move order has been closed, but pending transaction records are still existing
- Pending transaction has a move order line id, but you can not find it out in move order line table
- Pending transaction has a reservation _id, you can not find out it in mtl_reservations
- Source ID are different below move order and delivery details
- Source ID are different between pending transaction and move order line
Below is the script and my comments:
You can delete an item, if there’s no any transactions related to it in your system.
1. Log into IC module and change the organization to the item master organization, e.g. 000.
2. Go to item>delete an item and create a delete group.
3. Enter a group name, e.g. “20090101” and select “All organizations” (To delete the item thoroughly, please select “All organizations”. To delete the item only on your current organization but remain it in other organizations, you can select “Current organizations”.)
4. Enter the item to be deleted.
5. Click “Check Group” button, then a concurrent request will be run and return you some Errors if there is. Generally, if you have assigned item to multiple org.,there’s always an error said “the item is still existing in the child org.”. Just ignore it. For other errors, fix them.
6. Click “Delete Group” to delete them.
You can’t delete an inactive locator and can’t create a duplicate locator either even though they are under different sub-inventory. If your user create a locator under a wrong sub-inventory and then inactive it, he/she still can’t create a new one with the same locator name under the correct sub-inventory.
I don’t know why Oracle inactive the “Delete” button in the Stock Locator window, but Oracle provide a public API to delete the locator called inv_loc_wms_pub.delete_locator. If if there are no items attached to the locator and no onhand quantity for the locator, you can use it to delete it.
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.
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
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
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!
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.
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.
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.