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.

    Oracle Standard Payment Process in EBS 11i

    My drafted Oracle standard Payment Process in EBS 11i –

    Welcome to comment.

    Change AR Invoice/Credit Memo/Debit Memo status from Complete to Incomplete

    Once you complete an AR invoice/credit memo/debit memo by clicking the “Complete” button, you can’t change it back to “Incomplete” (the “Incomplete” button is greyed out) if any below situation exists:

    • The transaction has been transferred to GL after completing
    • The transaction has been printed after completing
    • The transaction has any activity, e.g. payment, application, adjustment, etc.

    Error Message during ADI installation

    We met an error message during ADI installation.

    adicom30.ins(509):OS_ERROR while spwawning
    C:\windows\system32\spawner.exe
    C:\windows\system32\AUWERG32.exe /S /32

    We just comment the 509th line in the file of adicom30.ins, then it is passed. Or you can download the  attached file, rename the file name to “adicom30.ins” and replace the one in your installation package if you also met this problem and have not other solution to resolve it.

    New added account can’t be shown in the FSG report

    One user called me that the new added account 1xxxxx can’t be shown in the balance sheet (FSG report) after she added it in the row set definition properly.

    Generally it is because of security rule, but this time after checking in the system, the security rule has been release.

    So why can’t the account be shown in Balance Sheet on this situation? I did some test and found below:
    • If the account has balance of “0”, it will be shown as “0” in the report;
    • If the account had never been used before the period of the runninig report, the whole line of this account won’t be shown in the report.

    It may be because of FSG’s logic – extracting the required accounting entries firstly, then organizing the report.

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

    Some New Features in Oracle R12

    1. Security Control:
    • MOAC (Mitiple Organization Access Control): You can access multiple OU in one responsibility. You can enter transaction and run reports for mulitple organization in one responsibility;
    • Data Access Set: You can access mulitple ledger or ledger set in one GL responsibility.
    • Access Set: To control access level for the specific template in a specific responsibility, e.g. FSG report template, distribution template, allocation template, etc.
    2. Ledger’s 4C, Secondary Ledger and Reporting currency:
    • Oracle adds “Accounting Method” as the fourth C on the ledger designation, e.g. China Standard Accrual, HK Principle, etc. Secondary Ledger and reporting currency can be attached with primary ledger. Every single transaction in the sub ledger can be transferred to two ledgers. Above feature maybe helpful for consolidation. Supposed that N1 has a primary ledger with CNY and China accounting rule and a secondary ledger with HKD and HK accounting rule, when transaction transferred to GL, accounting entries are generated in both ledgers against the different rule. It may ease and speed up the consolidation.
    3. E-Business Tax and standard “金税” (Gold Tax) Adapter:
    • Centralized tax management and report may help finance team calculate and trail the tax. Oracle standard “金税” looks more powerful than the current solution on the control and trace functionality, but it seems not support customer item.
    4. Centralized Bank and bank account management:
    • Bank is centralized and bank account is owed by Legal Entity. It likes much more with real business.
    5. AGIS (Advanced Global Intercompany System) (740297.1)
    • AGIS can help us to balance and reconcile the intercompany transactions, especially for G0. In R12, we can create intercompany invoice in AR manully, then proceed other steps. But not sure if intercompany invoices can be imported from sales order.
    6. Oracle standard AP/AR Netting solution instead of dummy bank (740297.1)
    7. Refund in AR
    • Currently, when you need to pay back to customer, you need to do DM in AR and do invoice and payment in AP. From R12, there’s a botton “Refund”, which is directly link to AP payment.
    8. COGS/Revenue Matching Report by Oracle standard functionality

    9. AP Invoice Line
    • We only have AP invoice distribution line before. From R12, between invoice header level and invoice distribution line level, we have an AP invoice line.
    10. The new product – Oracle Payment
    • For the payment method of cash, manual check, manual wire and clearing, it is still done in Oracle payment form (APXPAWKB).
    • For electronic and check (auto) payment method, Oracle obsolete payment batch concept and introduce a new concept – pay run. For pay run, Oracle move the payment batch form to Payment Manager Windows which developed by OAF.
    • In Payment Manager, you can complete all of the payment works for electronic payment. You need to create one or more payment process request template in which you can predefine some criteria for invoice selection. When you want to pay, you need to submit Payment Process Request to select invoice to pay against your criteria. After modifying your invoice selection, you can build payment, create payment instruction and transmit/print your instruction.
    11. Buyer Workbench
    • In PO module, there’s a new set of OAF windows called buyer workbench. Using it, we can create and inquiry PO in the new http page and manage the PO which is created by herself.