Differences between HR:Security Profile & MO:Security Profile

HR:Security Profile and MO:Security Profile are used for MOAC (Multi-Org-Access-Control).
HR:Security Profile is used to restrict the data in Human Resources according to the Business Group or whatever criteria you define in this security profile. Thus, for HR, it uses this secuity profile in its data exposure to the user.
MO:Security Profile acts the same but is used for Financials and Manufacturing applications. It restricts the access (site level or can be set at responsibility level) to certain operating unit (or whatever criteria defined in the security profile).
If there is no value at MO:Security Profile, then Financials and Manufacturing use the HR:Security Profile option for their data exposure to users.

Multi-Org features in Oracle EBS 11i and R12

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.

Adding a New Operating Unit

From:  http://www.oracleug.com

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).

Can’t return to vendor with error code APP-PO-14094

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.

Physical Inventory (Stock Take) Process

In the real environment, for the physical inventory (stock take) every time, you may follow the below process:

  1. Plan your stock take and select a big date to freeze your stocks, generally it is the weekend or before the public holiday;
  2. Notice the related department not to make any transactions to change the stock status from freezing time till to another notice;
  3. Freeze your stock on the freezing time;
  4. List/export the quantity of the stocks in your books/system;
  5. Generate and print tags for your stocks against above list;
  6. Count and record the quantity for every kind of stocks in you warehouse and attach the tags to the real stocks in your warehouse;
  7. If there’s any stocks without printed tags, generate the new one, attach it to the real stocks, and record it in your list
  8. Notice the related department it is ok to do any transactions;
  9. Compare the quantity from warehouse with the one from your books/systems
  10. Analyze the reason of the variance and submit the variance adjustment for approval
  11. Against the result of step 9, adjust the quantity and cost amount in your books/systems to the real one;
  12. 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.

    Remove Corrupt(Orphan) Pending Transaction/Move Order

    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:

    REM $Header: INVCLRMO.sql 115.5 2002/03/09 16:23:25 kadavi noship $
    REM
    REM (c) Copyright Oracle Corporation 2001
    REM All Rights Reserved
    REM
    REM HISTORY
    REM This is script to clear Open move order lines which
    REM are not linked to Delivery details ,clean Orphan suggestions
    REM in Mtl_material_transactions_temp and remove reservations
    REM link to Mtl_material_transactions_temp if reservations are
    REM not existing.
    REM
    REM Also this script creates and drop temp tables so Ct
    REM need to manually run this script.
    REM dbdrv: sql ~PROD ~PATH ~FILE none none none sqlplus_single phase=dat \
    REM dbdrv: checkfile:~PROD:~PATH:~FILE
    WHENEVER SQLERROR CONTINUE ROLLBACK; –when an error occurred during the sql executing, rollback
    prompt dropping tables -showing “droping tables”
    DROP TABLE mtl_mmtt_backup; –clean/drop the previous backup table
    DROP TABLE mtl_mtrl_backup;
    DROP TABLE mtl_msnt_backup;
    DROP TABLE mtl_mtlt_backup;
    prompt create table for MMTT backup –start to create backup tables
    CREATE TABLE mtl_mmtt_backup AS
    (SELECT mmtt.*
    FROM mtl_material_transactions_temp mmtt,
    mtl_txn_request_lines mtrl,
    mtl_txn_request_headers mtrh
    WHERE mmtt.move_order_line_id IS NOT NULL
    AND mmtt.move_order_line_id    = mtrl.line_id
    AND mtrl.line_status           = 7 –preapproved
    AND mtrl.header_id             = mtrh.header_id
    AND mtrh.move_order_type       = 3 –pick-wave
    AND NOT EXISTS
    (SELECT ‘Y’
    FROM wsh_delivery_details
    WHERE move_order_line_id = mtrl.line_id
    AND released_status      = ‘S’ –released to warehouse
    )
    )
    /
    –select to backup the lines whose linked move order line status is pre-approved, the move order is
    –generated from realeasing SO, meantime there’s no line in the delivery details or there is
    –but it is has been released to warehouse
    prompt select allocation records for closed move order
    INSERT
    INTO mtl_mmtt_backup
    (SELECT mmtt.*
    FROM mtl_material_transactions_temp mmtt,
    mtl_txn_request_lines mtrl
    WHERE mmtt.move_order_line_id = mtrl.line_id
    AND mtrl.line_status          = 5
    )
    / –select to backup the lines whose move order line status is closed
    prompt select allocation records with missing move order
    INSERT
    INTO mtl_mmtt_backup
    (SELECT mmtt.*
    FROM mtl_material_transactions_temp mmtt
    WHERE move_order_line_id IS NOT NULL
    AND NOT EXISTS
    (SELECT mtrl.line_id
    FROM mtl_txn_request_lines mtrl
    WHERE mtrl.line_id = mmtt.move_order_line_id
    )
    )
    / –select to backup the lines who has a move order link, but corresponding move order is not existing now.
    prompt create backup table for move order lines
    CREATE TABLE mtl_mtrl_backup AS
    (SELECT mtrl.*
    FROM mtl_txn_request_lines mtrl,
    mtl_txn_request_headers mtrh
    WHERE mtrl.line_status   = 7
    AND mtrl.header_id       = mtrh.header_id
    AND mtrh.move_order_type = 3
    AND NOT EXISTS
    (SELECT ‘Y’
    FROM wsh_delivery_details
    WHERE move_order_line_id = mtrl.line_id
    AND released_status      = ‘S’
    )
    )
    /
    –select to backup the move order lines whose status is pre-approved, the move order is
    –generated from realeasing SO, meantime there’s no line in the delivery details or there is
    –but it is has been released to warehouse
    prompt create backup table for serial number allocations
    CREATE TABLE mtl_msnt_backup AS
    (SELECT msnt.*
    FROM mtl_serial_numbers_temp msnt
    WHERE msnt.transaction_temp_id IN
    (SELECT transaction_temp_id FROM mtl_mmtt_backup
    )
    )
    /
    –backup all of the series records under the backuped  mtl_material_transactions_temp
    prompt create backup table for lot number allocations
    CREATE TABLE mtl_mtlt_backup AS
    (SELECT mtlt.*
    FROM mtl_transaction_lots_temp mtlt
    WHERE mtlt.transaction_temp_id IN
    (SELECT transaction_temp_Id FROM mtl_mmtt_backup
    )
    )
    /
    –backup all of the lots records under the backuped mtl_material_transactions_temp
    prompt select serial number allocations for lot controlled items
    INSERT
    INTO mtl_msnt_backup
    (SELECT msnt.*
    FROM mtl_serial_numbers_temp msnt
    WHERE msnt.transaction_temp_id IN
    (SELECT serial_transaction_temp_id FROM mtl_mtlt_backup
    )
    )
    /
    –backup all of the serials records for the lot and serial control items
    prompt delete serial number allocations
    DELETE
    FROM mtl_serial_numbers_temp
    WHERE transaction_temp_id IN
    (SELECT transaction_temp_id FROM mtl_msnt_backup
    ) /
    –delete all of the serials records which has been backuped
    prompt delete lot number allocations
    DELETE
    FROM mtl_transaction_lots_temp
    WHERE transaction_temp_id IN
    (SELECT transaction_temp_id FROM mtl_mtlt_backup
    ) /
    –delete all of the lot records which has been backuped
    prompt delete allocations
    DELETE
    FROM mtl_material_transactions_temp
    WHERE transaction_temp_id IN
    (SELECT transaction_temp_id FROM mtl_mmtt_backup
    ) /
    –delete all of the allocations (records in mmtt table)which has been backuped
    prompt close move order lines
    UPDATE mtl_txn_request_lines
    SET quantity   = NVL(quantity_detailed, 0) ,
    line_status  = 5
    WHERE line_id IN
    (SELECT line_id FROM mtl_mtrl_backup
    ) /
    –close all of the move order which has been backuped in mtl_mtrl_backup
    prompt update transaction source on the move order line
    UPDATE mtl_txn_request_lines mtrl
    SET mtrl.txn_source_line_id =
    (SELECT DISTINCT(source_line_id)
    FROM wsh_delivery_details
    WHERE move_order_line_id = mtrl.line_id
    AND released_status      = ‘S’
    )
    WHERE mtrl.line_status = 7
    AND EXISTS
    (SELECT delivery_detail_id
    FROM wsh_delivery_details wdd
    WHERE move_order_line_Id = mtrl.line_Id
    AND wdd.source_line_id  <> mtrl.txn_source_line_id
    AND wdd.source_line_id   > 0
    AND wdd.released_status  = ‘S’
    ) /
    –The move order has linked with delivery details and delivery details has been released to warehouse
    –In the delivery details, there’s a source line id but the source line id is different with the one in move order
    –And the move order is in the status of pre-approved.
    –If above is true, change the source line id in move order line to the one in delivery details
    prompt update transaction source on the allocation
    UPDATE mtl_material_transactions_temp mmtt
    SET mmtt.trx_source_line_id =
    (SELECT txn_source_line_id
    FROM mtl_txn_request_lines
    WHERE line_id = mmtt.move_order_line_id
    )
    WHERE mmtt.transaction_type_id IN (52, 53)
    AND mmtt.move_order_line_id    IS NOT NULL
    AND EXISTS
    (SELECT line_id
    FROM mtl_txn_request_lines
    WHERE line_status       = 7
    AND line_id             = mmtt.move_order_line_id
    AND txn_source_line_id <> mmtt.trx_source_line_id
    ) /
    –pending transaction is from internal order pick and sales order pick
    –pending transaction is linked with move order, move order is in Pre-approved
    –source line id in pending transaction is different with the one in move order
    –If above is true, change the source line id in pending transaction to the one in move order
    prompt update allocations for missing reservations
    UPDATE mtl_material_transactions_temp mmtt
    SET reservation_id         = NULL
    WHERE mmtt.reservation_id IS NOT NULL
    AND NOT EXISTS
    (SELECT mr.reservation_id
    FROM mtl_reservations mr
    WHERE reservation_id = mmtt.reservation_id –there’s a reservation id in pending transaction,
    ) / COMMIT / EXIT; –but there’s no in reservation record; If above is true, change it to null.
    /

    Delete an Item

    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.

    Delete Stock Locator

    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.

    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.

    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

    Mistake

    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)

    Analysis

    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

    Solution

    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.

    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.