Material and Resource Transactions With Error Code CST_INVALID_WIP because of missing record in WIP_PERIOD_BALANCES (文档 ID 553138.1)

转到底部

In this Document

Symptoms

Cause

Solution

References

APPLIES TO:

Oracle Work in Process - Version 11.5.9 and later
Oracle Enterprise Asset Management - Version 11.5.9 and later
Oracle Cost Management - Version 11.5.9 and later
Information in this document applies to any platform.

SYMPTOMS

This note provides solution for following situations:

A. Material Transactions

There are transactions in MTL_MATERIAL_TRANSACTIONS related to DISCRETE JOB which have following error :

ERROR CODE: CST_INVALID_WIP
ERROR_EXPLANATION: The wip entity is either not defined or does not have a period balance entry.

The issue can be reproduced at will with the following steps:

1. WIP > Material Transactions > View Material Transactions > Select Status=Error

or

2. Inventory > Transactions > Material Transactions > Select Status=Error

B. Resource Transactions

There are Resource Transactions stuck in pending in WIP_COST_TXN_INTERFACE with following error explanation:

Record not found in WIP_PERIOD_BALANCE
Cause: There is no record in WIP_PERIOD_BALANCES for the repetitive schedule or job.
Action: Contact Oracle Support Representative

Other possible error message:

The wip entity is either not defined or does not have a period balance entry

This same error can also occur for Resource transactions where in MTL_MATERIAL_TRANSACTIONS the transaction_source_id is a negative number. See Note 1344384.1 for details.

Steps:

1. WIP > Resource Transactions > Pending Resource Transactions

CAUSE

The cause of the issue is missing records in WIP_PERIOD_BALANCES
The root cause resides in the way jobs/WOs are being released : Many reported cases were because of custom code releasing the jobs/WO without populating data in wip_period_balances.

But there were also some bugs:

Release 11.5.9:

Patch:3949012 MATERIAL ISSUE TRANSACTIONS ARE CREATED FOR CLOSED PERIOD FROM EAM
This will bring fixed version: EAMMATTB.pls 115.13.1159.8

Patch:5557229 - CANNOT CHARGE A WO ON LAST DAY OF INV ACCOUNTING PERIOD
This will bring fixed version: wiprstxb.pls 115.13.1159.7

Release 11.5.10:

Patch:5378577 - EAM TRANSACTIONS IN PREVIOUS MONTHS ARE POSTED IN CURRENT MONTH
This will bring fixed version: wiprstxb.pls 115.23.11510.8

Patch:5573584 - RELEASE WO FROM CANCELLED STATUS HAVE NO RECORD IN WPB TABLE
This will bring fixed version: EAMVWOSB.pls 115.38.11510.8

Release 12.0.x:

Patch.8344919:R12.INV.A - CSTPNTXN:PERIOD CLOSE PENDING TRANSACTIONS REPORT OUTPUT IS IN XML LANG. FORMAT
This will bring fixed version: INVTTGPM.fmb 120.19.12000000.7

Patch: 7516448:R12.EAM.A - 1OFF:7454536:RUP4:12.0.4:CANNOT CHARGE LABOR TO WO ON LAST DAY OF INV ACCOUNTING
This will bring fixed version: wiprstxb.pls 120.13.12000000.3

Patch:7582553:R12.EAM.A - ERROR WARNING NOT PROVIDED WHEN RESOURCE TRANSACTIONS MADE FOR CLOSED INV PERIOD
This will bring fixed version: ChargeTimeAMImpl.java 120.6.12000000.3

Patch:8647138:R12.EAM.A - FWDPORT:5573584:12.0.3:12.0.3:RELEASE WO FROM CANCELLED STATUS HAVE NO RECORD IN
This will bring fixed version: EAMVWOSS.pls 120.3.12000000.3

Release 12.1.x:

Patch 13605602:R12.INV.B - 1OFF:12410248:12.1.3:12.1.3:MISSING RECORD IN WIP_PERIOD_BALANCE EVEN AFTER APPL.... see more in Document.1416033.1

This will bring fixed versions:

INVTTGPM.fmb 120.21.12010000.10

INVMOCLB.pls 120.6.12010000.8

Patch:18101499:R12.WIP.B WIP_PERIOD_BALANCES NOT CREATED FROM CANCELLED TO COMPLETE NO CHARGES ............. see more in Document.1664618.1
This patch solves the case when WIP Discrete job is changed from Unreleased to Cancelled and then to Complete-No charges
This will bring ne version of Discrete Job form:
WIPDJMDF.fmb 120.80.12010000.82

SOLUTION

To implement the solution, please execute the following steps:

For the root cause please make sure you have applied appropriate patches from previous section (as per your current EBS release)

1. For fixing the data,identify the problematic jobs using following script in SQLPlus:

SET LINES 180
SET PAGES 65

spool wip_missing_wpb.lst

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 ,
decode (WDJ.STATUS_TYPE,
                         1,'Unreleased',
                         3, 'Released',
                         4, 'Complete',
                         5, 'Complete NoCharge',
                         6, 'On Hold',
                         7, 'Cancelled',
                         8, 'Pend Bill Load',
                         9, 'Failed Bill Load',
                         10, 'Pend Rtg Load',
                         11, 'Failed Rtg Load',
                         12, 'Closed',
                         13, 'Pending- Mass Loaded',
                         14, 'Pending Close',
                         15, 'Failed Close',
                         WDJ.STATUS_TYPE) status_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 >= TRUNC(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);

spool off

2. Create backup of WIP_PERIOD_BALANCES table

3. Stop Cost Manager

System Administrator Responsibility>Requests>query for specific 'Cost Manager' and increase the Number of Days to view from 7 to 1000>Find>Cancel request

4. Apply the script to insert missing records in WIP_PERIOD_BALANCES table :

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 >= TRUNC(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);

5. Run again select from step 1 which should return no records now. If so, then issue a commit.

6. Check the output and see also if DATE_RELEASED is populated . if not, script wip_wdj_date_released_upd.sql should be also used (after taking necessary backup as per next step)

6.1 create backup for problematic records in wip_discrete_jobs

create table XX_WDJ_BACKUP_FC_02
as select * from wip_discrete_jobs
where date_released is null and status_type in (3, 4, 5, 6, 7, 14, 15);

6.2 populate date_released for problematic jobs

update wip_discrete_jobs wdj
   set wdj.date_released = (select min(oap.period_start_date)
                              from org_acct_periods oap
                             where (oap.acct_period_id, oap.organization_id) in
                                   (select wpb.acct_period_id,
                                           wpb.organization_id
                                      from wip_period_balances wpb
                                     where wpb.wip_entity_id =
                                           wdj.wip_entity_id))
where wdj.date_released is null
   and WDJ.STATUS_TYPE IN (3, 4, 5, 6, 7, 14, 15);

7. After committing the transaction, resubmit the erred material and resource transactions

Fr materials: Inventory > Transactions > Material Transactions > query for error transactions > resubmit

For resources: WIP > Resource Transactions > Pending Resource Transactions

8. Launch the Cost Manager:

Inventory > Setup > Transactions > Interface Managers

9. Check if Cost Worker is spawned and transactions are processed.

Note: also data fix cm276916.sql in Note 1080033.6 can be used for resolving this kind of errors but data fix in Note 1080033.6 will not work for jobs released in the last day of the month because in table ORG_ACCT_PERIODS the field SCHEDULE_CLOSE_DATE has no time stamp but at the same time in table WIP_DISCRETE_JOBS both fields DATE_RELEASED and CREATION_DATE have time stamp and it happens that jobs released in the last day of the months will only be found by the script if we use

OAP.SCHEDULE_CLOSE_DATE >= NVL(WDJ.DATE_RELEASED,
WDJ.CREATION_DATE)

instead of

OAP.SCHEDULE_CLOSE_DATE >= TRUNC(NVL(WDJ.DATE_RELEASED, WDJ.CREATION_DATE))

For Additional information, please review:
Note 865438.1 How to resolve Costing Errors & Problems

REFERENCES

NOTE:1080033.6 - Errored Transactions in MTL_MATERIAL_TRANSACTIONS INV_NO_UPDATE / CST_INVALID_WIP
BUG:6709562 - DATAFIX: WIP TRANSACTIONS STUCK WITH COSTED_FLAG ERROR CST_INVALID_WIP
NOTE:1416033.1 - Root Cause Fix: Missing Record In WIP_PERIOD_BALANCES and CST_INVALID_WIP Errors - Most Current or Recent Root Cause Fix
BUG:6709562 - DATAFIX: WIP TRANSACTIONS STUCK WITH COSTED_FLAG ERROR CST_INVALID_WIP
NOTE:1344384.1 - Uncosted Material Transactions Error Out With Error Code: CST_INVALID_WIP having Negative TRANSACTION_SOURCE_ID in MMT

CST_INVALID_WIP error

APPLIES TO:

SYMPTOMS

CAUSE

SOLUTION

REFERENCES