週期性成本分配 計算錯誤 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