Error with Standard Costing cst_match_date_period

In this Document Symptoms Cause Solution References Applies to: Oracle Inventory Management - Version 11.5.10.CU2 to 12.1.3 [Release 11.5.10 to 12.1] Oracle Cost Management - Version 11.5.10.1 to 12.0.4 [Release 11.5 to 12] Information in this document applies to any platform. Checked for relevance 19-JUN-2010 Symptoms When attempting to cost Sales Order Pick and Internal Order Pick the following error occurs: ERROR Error Code =CST_MATCH_DATE_PERIOD Error Explanation = The transaction date is not within the accounting period specified. The issue can be reproduced at will with the following steps: 1. Oracle cost Management  2. Inventory  3. Transactions  4. View Transactions  5. select costed_flag option error Cause The cause of the issue is invalid / incorrect data in MTL_MATERIAL_TRANSACTIONS, the acct_period_id is getting stamped with the accounting is of the previous period.  Solution To identify the issue, please obtain the following scripts output: 1)  SELECT mmt.transaction_id txn_id, mmt.transaction_date txn_date, mmt.acct_period_id txn_period_id, o.acct_period_id org_period_id, o.period_start_date st_date, o.schedule_close_date cl_date FROM mtl_material_transactions mmt, org_acct_periods o WHERE mmt.organization_id = o.organization_id AND TRUNC(o.schedule_close_date) >= TRUNC(mmt.transaction_date) AND TRUNC(o.period_start_date) <= TRUNC(mmt.transaction_date) and mmt.transaction_date is not NULL and mmt.acct_period_id <> o.acct_period_id ; 2) select *     from org_acct_periods      where organization_id = '&org_id_where_issue_exists'; -- To implement the solution, please execute the following steps: 1) Make a backup of MTL_MATERIAL_TRANSACTIONS before running the update script.  2) Shut down the cost manager before the update.  3) Run the following update to correct the acct_period_id: Update mtl_material_transactions  set acct_period_id = <insert correct acct period id>  ,   costed_flag = 'N'  ,   transaction_group_id = NULL  ,   transaction_set_id = NULL  ,   error_code = NULL  ,   error_explanation = NULL  where costed_flag = 'E'  and acct_period_id =  <current incorrect value>  and error_code = 'CST_MATCH_DATE_PERIOD' and organization_id = '<organization_id for the affected records>' Note: The transaction_id could also be used as the basis for the update, but in this case the number of records made that difficult and uniqueness could be achieved with the four where clauses parameters above. 3) Restart the Cost Manager.

週期性成本分配 計算錯誤 CSTPALPC.create_dist_entry : 40

1. 從主機端直接抓 LOG 檔 (日誌太大時, 無法從系統直接打開)

   A. 找路徑

select logfile_name from fnd_concurrent_requests

where request_id = 2668178

然後用 ftp 直接抓下來, 這是純文字檔

2. 從 log 檔查到

CSTPAPBR.create_inv_ae_lines: 0: begin << transaction_id: 8498336

CSTPAPBR.create_inv_ae_lines: 16: l_curr_rec.pri_currency = TWD; l_curr_rec.alt_currency =

CSTPAPBR.create_inv_ae_lines: 16: l_curr_rec.currency_conv_rate = -1

Pcu_cost_txn <<

Category: 1213 has no accounts defined

30005未定義科目給分類、成本型態、成本群組及合法實體.

CSTPALPC.dyn_proc_call : Error Calling Package

30005未定義科目給分類、成本型態、成本群組及合法實體.: CSTPALPC.create_dist_entry : 40

Create_Acct_Entry >>>

CSTPDPPC.dyn_proc_call : Error Calling Package

30005未定義科目給分類、成本型態、成本群組及合法實體.: CSTPALPC.create_dist_entry : 40

3. 回到異動檔查詢 TRANSACTION_ID=8498336 的紀錄

SELECT * FROM mtl_material_transactions WHERE TRANSACTION_ID=8498336

查出資料如下

Inventory_item_id = 136589  但是 organization_id = 82 , 這是 組織 ID

用以下 SQL 查出料號

SELECT segment1 FROM MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID=136589 and organization_id=82

4. 然後到料號補入成本分類資料 (組織要選對)。

附註:

(1) 用來檢查庫存有異動但是沒有成本分類

select MMT.TRANSACTION_ID,msi.segment1,mic.category_concat_segs ,mic.organization_id

from  mtl_material_transactions MMT,

mtl_system_items msi,

MTL_ITEM_CATEGORIES_V MIC

where mic.CATEGORY_ID = 1213  ---T B D 

and mic.category_set_id = 1100000042

and mic.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID

AND MMT.organization_id = MSI.ORGANIZATION_ID

AND MIC.organization_id = MSI.ORGANIZATION_ID

and MMT.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID

(2) 用來檢查基本資料成本分類是TBD

select msi.segment1,mic.category_concat_segs ,mic.organization_id

from mtl_system_items msi,

MTL_ITEM_CATEGORIES_V MIC

where mic.CATEGORY_ID = 1213  ---T B D 

and mic.category_set_id = 1100000042

and mic.organization_id = 82 ---  組織代號

and mic.ORGANIZATION_ID = msi.ORGANIZATION_ID

and mic.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID

(3) 檢查完全未設成本分類的料號

SELECT msi.inventory_item_id,msi.segment1,

msi.organization_id

FROM mtl_system_items_b msi

WHERE msi.organization_id = 86 ---  組織代號

AND MSI.INVENTORY_ITEM_ID IN (SELECT DISTINCT inventory_item_id

FROM mtl_material_transactions

WHERE organization_id =86) ---  組織代號

AND NOT EXISTS(

SELECT category_id

FROM mtl_item_categories mic

WHERE mic.inventory_item_id = msi.inventory_item_id

AND mic.organization_id = msi.organization_id

AND mic.category_set_id = 1100000042);

檢查有沒有料號未設成本分類 (SQL)

偶而會有料號未設成本分類, 造成會計在算成本時出現錯誤, 可以用以下 SQL 來檢查:

SELECT msi.inventory_item_id,msi.segment1,msi.organization_id
FROM mtl_system_items_b msi
WHERE msi.organization_id = 86
AND MSI.INVENTORY_ITEM_ID IN (SELECT DISTINCT inventory_item_id
FROM mtl_material_transactions
WHERE organization_id =86)

AND NOT EXISTS(
SELECT category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mic.category_set_id = 1100000042);

查看LOV中的查詢語句

    一直有實施顧問詢問我XXForm的XX LOV是什麼邏輯,取數SQL是什麼來著,以前比較笨,打開Form去看,運氣好點,碰到LOV和Record Group都是固定的就一下找到了,運氣不好有可能LOV是動態綁定到ITEM上,而LOV的RG是動態生成,想找個查詢的SQL,可能要吐血,下面介紹一個最簡單的方法,方便快捷而準確:

1.打開個性化定義介面(如果沒有許可權,到系統配置檔設置中,查看是否是"隱藏診斷功能表"被設置成了是),所以定義一個個性化,action如下圖所示:

消息文本中的SQL語句是:"=(SELECT t.sid FROM v$mystat t where rownum = 1)",點擊驗證,獲得當前SESSION 對應的sid,記錄下來,例子中是102;

2.回到需要查詢LOV介面隨意輸入資料,觸發LOV的查詢.

3.打開PL/SQL Dev,查詢上一條和當前SQL的SQLADDRESS：

SELECT s.prev_sql_addr,s.sql_address FROM v$session s WHERE s.sid = 102;

(有可能查詢的間隔比較短,當前執行的SQL語句在sql_address 欄位中)

4.根據SQLaddress查詢對應的SQL語句

SELECT sql_text FROM v$sqltext_with_newlines t WHERE t.address = '0000000390E496F0' ORDER BY t.piece;

  出處http://blog.csdn.net/shishun123/article/details/6874824

Oralce Form 開發: 特殊的預設值 $$variables$$

特殊的預設值 $$variables$$

Oracle Forms 提供了六個特殊的系統變數，均為提供日期和時間的資訊的變數，他們是：

·                  $$DATE$$

·                  $$TIME$$

·                  $$DATETIME$$

·                  $$DBDATE$$

·                  $$DBTIME$$

·                  $$DBDATETIME$$

其中，

$$DATE$$, $$TIME$$, $$DATETIME$$三個系統變數可以用來獲取用戶用戶端作業系統上當前的日期和時間。

$$DBDATE$$, $$DBTIME$$, $$DBDATETIME$$三個系統變數可以用來獲取資料庫系統所在的伺服器上的當前日期和時間。

使用方法

一般說來，這六個系統變數可以用來填充資料塊(BLOCK)上的某個資料項目(ITEM)的以下三個屬性

·                  Initial Value(初始值) - 這個最常用

·                  Highest Allowed Value(允許的最大值) 

·                  Lowest Allowed Value(允許的最小值)

注意事項

1.                                 當目標資料源是非ORACLE資料庫時，避免使用$$DBDATE$$, $$DBTIME$$, $$DBDATETIME$$. 建議在WHEN-CREATE-RECORD觸發器中利用資料源所指定的方式通過SQL語句直接取當前時間。

2.                                 不要忘記$$DATE$$系列變數取的是用戶端作業系統上的當前時間，$$DBDATE$$系列變數則是獲取目標資料庫中的當前時間。例如你通過遠端登錄到一個伺服器系統，該伺服器所在區域的時區和你所在的時區不同的時候，二者取到的值可能是截然不同的。

    資料來源: http://www.cnblogs.com/objectorl/archive/2010/11/09/Oracle-Forms-System-Variables.html