Oracle ERP R12. create locator using API (INV_LOC_WMS_PUB.CREATE_LOCATOR) - 建立Locator的API

若有需要大量新增Locator 則其有二種方式 :

1. Data Load

2. 呼叫 INV_LOC_WMS_PUB.CREATE_LOCATOR 該API
用法 :
FND_GLOBAL.APPS_INITIALIZE(1114,
                                                           51007,
                                                           401);
FND_MSG_PUB.Initialize;

INV_LOC_WMS_PUB.CREATE_LOCATOR(X_RETURN_STATUS => :X_RETURN_STATUS,
                                 X_MSG_COUNT => :X_MSG_COUNT,
                                 X_MSG_DATA => :X_MSG_DATA,
                                 X_INVENTORY_LOCATION_ID => :X_INVENTORY_LOCATION_ID,
                                 X_LOCATOR_EXISTS => :X_LOCATOR_EXISTS,
                                 P_ORGANIZATION_ID => :P_ORGANIZATION_ID,
                                 P_ORGANIZATION_CODE => :P_ORGANIZATION_CODE,
                                 P_CONCATENATED_SEGMENTS => 儲位,
                                 P_DESCRIPTION => 儲位描述,
                                 P_INVENTORY_LOCATION_TYPE => 3,
                                 P_PICKING_ORDER => :P_PICKING_ORDER,
                                 P_LOCATION_MAXIMUM_UNITS => :P_LOCATION_MAXIMUM_UNITS,
                                 P_SUBINVENTORY_CODE => 倉別,
                                 P_LOCATION_WEIGHT_UOM_CODE => :P_LOCATION_WEIGHT_UOM_CODE,
                                 P_MAX_WEIGHT => :P_MAX_WEIGHT,
                                 P_VOLUME_UOM_CODE => :P_VOLUME_UOM_CODE,
                                 P_MAX_CUBIC_AREA => :P_MAX_CUBIC_AREA,
                                 P_X_COORDINATE => :P_X_COORDINATE,
                                 P_Y_COORDINATE => :P_Y_COORDINATE,
                                 P_Z_COORDINATE => :P_Z_COORDINATE,
                                 P_PHYSICAL_LOCATION_ID => :P_PHYSICAL_LOCATION_ID,
                                 P_PICK_UOM_CODE => :P_PICK_UOM_CODE,
                                 P_DIMENSION_UOM_CODE => :P_DIMENSION_UOM_CODE,
                                 P_LENGTH => :P_LENGTH,
                                 P_WIDTH => :P_WIDTH,
                                 P_HEIGHT => :P_HEIGHT,
                                 P_STATUS_ID =>1,
                                 P_DROPPING_ORDER => :P_DROPPING_ORDER,
                                 P_ATTRIBUTE_CATEGORY => :P_ATTRIBUTE_CATEGORY,
                                 P_ATTRIBUTE1 => :P_ATTRIBUTE1,
                                 P_ATTRIBUTE2 => :P_ATTRIBUTE2,
                                 P_ATTRIBUTE3 => :P_ATTRIBUTE3,
                                 P_ATTRIBUTE4 => :P_ATTRIBUTE4,
                                 P_ATTRIBUTE5 => :P_ATTRIBUTE5,
                                 P_ATTRIBUTE6 => :P_ATTRIBUTE6,
                                 P_ATTRIBUTE7 => :P_ATTRIBUTE7,
                                 P_ATTRIBUTE8 => :P_ATTRIBUTE8,
                                 P_ATTRIBUTE9 => :P_ATTRIBUTE9,
                                 P_ATTRIBUTE10 => :P_ATTRIBUTE10,
                                 P_ATTRIBUTE11 => :P_ATTRIBUTE11,
                                 P_ATTRIBUTE12 => :P_ATTRIBUTE12,
                                 P_ATTRIBUTE13 => :P_ATTRIBUTE13,
                                 P_ATTRIBUTE14 => :P_ATTRIBUTE14,
                                 P_ATTRIBUTE15 => :P_ATTRIBUTE15,
                                 P_ALIAS => :P_ALIAS);

Oracle ERP R12. Unassign or delete large number of items .

There is an option on the Tools menu of the Delete Item form that opens the Item Search screen.
There you can search on a number of criteria, including status, and the search results are populated in your Delete Group.
You can review the list, and delete unwanted entries (or add new ones, if necessary).

Then run the Delete process.

Reference from Metalink : ID 1347893.1

Oracle ERP R12. CST_INVALID_WIP (The wip entity is either not defined or does not have a period balance entry.) - Discrete Job

Cause :

Records not found in WIP_PERIOD_BALANCES

(該工單於WIP_PERIOD_BALANCES裡，少了該交易期間的資料)

Solution :

I. Normally, when there are records in MTL_MATERIAL_TRANSACTIONS with the　Costed_Flag = 'E' you would resubmit these records by doing the following :

  （一般而言，若MMT裡有發現COSTED_FLAG=E時，正常異常排除的步驟如下）

  a. Backup the rows to be updated.　（將資料備份下來）

  b. Turn off the Cost Manager.　（先暫停Cost Manager，以避免干擾接下來要執行的步驟）

  c. Run the SQL statement:　（執行以下SQL）

     UPDATE MTL_MATERIAL_TRANSACTIONS

        SET costed_flag          = 'N',

            error_code           = NULL,

            error_explanation    = NULL,

            transaction_group_id = NULL

      WHERE organization_id =

        and transaction_source_id =

        and costed_flag IS NOT NULL;

II. When this does not work, it is usually due to one or more of the following three conditions.

    (若異常狀況仍存在，則可以是以下原因造成) 

   * Item costs are not defined for Frozen cost type in CST_ITEM_COSTS.

　　（CST_ITEM_COSTS沒有該品號的Frozen cost）

     ==> 請補建Item Cost 並Copy cost至Forezen cost

     

   * WIP_PERIOD_BALANCES is missing rows for the acct_period_id.

　　 (該工單於WIP_PERIOD_BALANCES缺少該期間的餘額資料)

     ==> 請於WIP_PERIOD_BALANCES補該筆工單該Period的資料

     

    NOTE:  THIS DOES NOT APPLY TO CLOSED DISCRETE JOBS WDJ.STATUS_TYPE = 12

    (注意 : 該動作不適用於Closed的工單)

Oracle ERP R12. - Resubmitting Pending Material Transactions

若有些交易卡在MTL_TRANSACTION_INTERFACE,並在修正完錯誤資料欄位,想透過Update interface的方式來重新執行,其Update 語法如下 :


UPDATE MTL_TRANSACTIONS_INTERFACE A
   SET A.PROCESS_FLAG = 1,
       A.LOCK_FLAG = 2,
       A.TRANSACTION_MODE = 3,
       A.ERROR_CODE = NULL
 WHERE A.PROCESS_FLAG = 3 --ERROR


Ps. 但一般仍建議由Oracle Standard 的Transaction Interface功能(INVTVPTX)來Resubmit

參考文件 : Oracle - Resolving Period Close Pending Transaction

Oracle ERP R12. - Resubmitting MTL_MATERIAL_TRANSACTIONS's Uncosted transactions

Resubmitting Uncosted Transactions

若MTL_MATERIAL_TRANSACTIONS有些交易執行Cost Manager失敗,導致該交易的成本未收集到,並影响關帳,其重新執行的Update 語法如下:


UPDATE MTL_MATERIAL_TRANSACTIONS A
   SET A.COSTED_FLAG = 'N',
       A.TRANSACTION_GROUP_ID = NULL
 WHERE A.COSTED_FLAG IS NOT NULL


參考文件 : Oracle - Resolving Period Close Pending Transaction

Oracle ERP R12 - Resubmitting MTL_MATERIAL_TRANSACTIONS_TEMP

Resubmitting Unporcessed Material Transactions :

Unporcessed Material Transactions指MTL_MATERIAL_TRANSACTIONS_TEMP內有未處理完成或失敗的交易.其重新執行的Update 語法如下 :


UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP A
   SET A.PROCESS_FLAG = 'Y',
       A.LOCK_FLAG = 'N',
       A.TRANSACTION_MODE = 3,
       A.ERROR_CODE = NULL
 WHERE A.TRANSACTION_HEADER_ID = <要重新執行的TRANSACTION_HEADER_ID>



參考文件 : Oracle - Resolving Period Close Pending Transaction

Oracle ERP R12. Material Transaction Control use period control(INV: Transaction Date Validation).

雖然目前Oracle ERP 有 Peroid control的功能,但是每到月初,因上期的帳尚未結完,故無法將Inventroy的period予以關結(Ps : Inventory period一經關結是不能再打開的)
故Oracle在Profile上有定義以下几種選項:
1. Allow date in any open period : 只要該period是open的,其material transaction可以做在該period
2. Do not allow date in past period : material transaction只能做在本期Open period. (但可以是本期Open period內過去的任何一天,只要是Open period,且Oracle似乎不可以輸入未來的日期)
3. Do not allow past date : material transaction只做在今天以後的所有Open period. (但Oracle似乎也不予許輸入未來的交易,故該選項其實就只能輸入現在的日期,及其必需為Open period)
4. Provide warning when date in past period : 當material transaction為過去的Open period,則只顯示警告,但不予卡關!

Oracle ERP R12. WIP Material Transactions 發料異常

最近因為user一直反應有庫存,但是工單發料時,系統卻顯示庫存不足,但是查了一下庫存,其available和pending的資料,卻都很正常,所以跟user模擬一下操作方式後,發現這可能是Oracle ERP R12的bug.以下是操作步驟

步驟 :
1. WIP Material Transactions
1.1. Input Job (:WIPTXMAT.WIP_ENTITY_NAME)
1.2. Input Subinventory (:WIPTXMAT.SUBINVENTORY)
1.3. Click Open (:CONTROL.OPEN_BUTTON)
1.3.1. Input Locator (:MTL_TRX_LINE.LOCATOR) - <Option , if locator control>
1.3.2. Input Quantity (:MTL_TRX_LINE.TRANSACTION_QUANTITY)
1.3.3. Click Lot/Serial (:CONTROL.LOT_SERIAL_BUTTON)
1.3.3.1. Input Lot Number (:LOT_ENTRY.LOT_NUMBER)
1.3.3.1.1. See the lov's Quantity is enough .
1.3.3.1.2. See the Quantity Available (:LOT_QUANTITY.QAT)
                 ==> LOV's Quantity <>:LOT_QUANTITY.QAT !
                       (例如:LOV是1000,但是在Quantity Available上卻帶出其它lot的數量)
1.3.3.2. Input Quantity (:LOT_ENTRY.TRANSACTION_QUANTITY) - the same lov's Quantity
1.3.3.3. Message show Negative balances is not allowed.
如上步驟,因為1.3.3.1.2.中其Quantity Available (:LOT_QUANTITY.QAT )因為疑似帶錯,所以可能就會誤判可用庫存不足以發料.

故初步分析其大部分只會發生在當可用庫存批號多於一筆時,因為只有多筆才會帶錯資料.

結論 : 若Oracle尚未Release patch前,則只要步驟1.2.不要指定Subinventory即可,改至明細資料內Subinventory (:MTL_TRX_LINE.SUBINVENTORY_CODE)輸入,就可以避開止一Bug.