WIP_PERIOD_BALANCE 中找不到記錄

如果當前GL、INV期間未開啟，工單無法開立；與將工單開立在上個期間，但是無法核發，從後臺更新Create date等日期到上個期間後，WO表中無等候數量，此時直接插入資料；工單過賬後，資源移動出現錯誤：WIP_PERIOD_BALANCE 中找不到記錄。

處理方法如下：

原因:        對重複式排程或工單而言, WIP_PERIOD_BALANCES 中沒有記錄.
作業:        請聯絡 Oracle 技術支援代表
這是因為Oracle在業務邏輯上定義為，當月的任務必須當月完成，

執行下面的語句，然後重新提交資源事務處理:

        INSERT INTO WIP_PERIOD_BALANCES
                (ACCT_PERIOD_ID, WIP_ENTITY_ID,
                REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
                LAST_UPDATED_BY, CREATION_DATE,
                CREATED_BY, LAST_UPDATE_LOGIN,
                ORGANIZATION_ID, CLASS_TYPE,
                TL_RESOURCE_IN, TL_OVERHEAD_IN,
                TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
                PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
                PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
                TL_MATERIAL_OUT, TL_MATERIAL_OVERHEAD_OUT, TL_RESOURCE_OUT,
                TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
                PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
                PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
                PL_OUTSIDE_PROCESSING_OUT,
                PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
                PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
                PL_OVERHEAD_VAR, TL_MATERIAL_VAR, TL_MATERIAL_OVERHEAD_VAR,
                TL_RESOURCE_VAR, TL_OUTSIDE_PROCESSING_VAR,
                TL_OVERHEAD_VAR)
        SELECT
                OAP.acct_period_id, WDJ.WIP_ENTITY_ID,
                NULL, SYSDATE,
                0, SYSDATE,
                0, 0,
                WDJ.ORGANIZATION_ID, WAC.CLASS_TYPE,
                0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
        FROM    WIP_ACCOUNTING_CLASSES WAC,
                ORG_ACCT_PERIODS OAP,
                WIP_DISCRETE_JOBS WDJ
        WHERE   WDJ.STATUS_TYPE IN (3, 4, 5, 6, 7, 14, 15)
        AND     WAC.CLASS_CODE = WDJ.CLASS_CODE
        AND     WDJ.ORGANIZATION_ID = WAC.ORGANIZATION_ID 
        AND     OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
        AND     OAP.OPEN_FLAG = 'Y'
        AND     OAP.PERIOD_CLOSE_DATE IS NULL
        AND     OAP.SCHEDULE_CLOSE_DATE >= NVL(WDJ.DATE_RELEASED,
                WDJ.CREATION_DATE)
        AND     WAC.CLASS_TYPE != 2
        AND     NOT EXISTS
                (
                SELECT 'X' FROM WIP_PERIOD_BALANCES WPB
                WHERE  WPB.REPETITIVE_SCHEDULE_ID IS NULL
                       AND   WPB.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
                       AND   WPB.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
                       AND   WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID);

**********
 

Record not Found in WIP_PERIOD_BALANCE [ID 730458.1]
 
 

--------------------------------------------------------------------------------

 
 
 修改時間 04-MAR-2009     型別 PROBLEM     狀態 MODERATED
 
 
       

In this Document
  Symptoms
  Cause
  Solution

--------------------------------------------------------------------------------

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review.
 

Applies to:

Oracle Work in Process - Version: 11.5.10 to 12.0
This problem can occur on any platform.
WIPTIRSC, WIP mass load

Symptoms

When using open interfaces and inserting a record into the WIP_COST_TXN_INTERFACE table, the Cost Manager runs the record errors. In the view resource pending transactions form. (WIPTIRSC) the error statement is:

"Record not found in WIP_PERIOD_BALANCE Cause: There is no record in WIP_PERIOD_BALANCES for the repetitive schedule or job".

 

 

Cause

1) If you are using open interfaces

Missing entries in WIP_PERIOD_BALANCES table can provocate the errors.

The incorrect use of the open interfaces (wrong parameters) can avoid a mass load to insert rows in the WIP_PERIOD_BALANCES table. Open Interfaces says explicitly the correct values that must be inserted for some fields when using the WIP_COST_TXN_INTERFACE table.

2) For unknown reason there're some missed rows

With the following script. you can detect if exists missed rows:

SELECT
WDJ.WIP_ENTITY_ID,
WE.WIP_ENTITY_NAME,
decode(we.entity_type,
1, 'Discrete Job',
2, 'Repetitive Schedule',
3, 'Closed Discrete Job',
4, 'Flow/Work Order-less',
5, 'Lot Based Job',
6, 'EAM Job',
we.entity_type) entity_type ,
WDJ.ORGANIZATION_ID,
OAP.acct_period_id,
WDJ.DATE_RELEASED,
WAC.CLASS_TYPE
FROM WIP_ACCOUNTING_CLASSES WAC,
ORG_ACCT_PERIODS OAP,
WIP_DISCRETE_JOBS WDJ,
WIP_ENTITIES WE
WHERE WDJ.STATUS_TYPE IN (3, 4, 5, 6, 7, 14, 15)
AND WAC.CLASS_CODE = WDJ.CLASS_CODE
AND WDJ.ORGANIZATION_ID = WAC.ORGANIZATION_ID
AND OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID
AND OAP.OPEN_FLAG = 'Y'
AND OAP.PERIOD_CLOSE_DATE IS NULL
AND OAP.SCHEDULE_CLOSE_DATE >= NVL(WDJ.DATE_RELEASED, WDJ.CREATION_DATE)
AND WAC.CLASS_TYPE != 2
AND NOT EXISTS
(
SELECT 'X' FROM WIP_PERIOD_BALANCES WPB
WHERE WPB.REPETITIVE_SCHEDULE_ID IS NULL
AND WPB.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND WPB.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID);

Solution

1) For cause # 1
When inserting data into the WIP_COST_TXN_INTERFACE table, set the PROCESS_PHASE field with value 1, which means "Move Validation". This is mandatory.

2) For cause # 2

Please log a SR with Oracle Support to get the datafix to add the missed rows

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/kevingao/archive/2010/04/02/5443925.aspx

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16860121/viewspace-664210/，如需轉載，請註明出處，否則將追究法律責任。