In this Document
APPLIES TO:
Oracle Inventory Management - Version 11.5.10.CU2 and later
Oracle Enterprise Asset Management - Version 11.5.10.2 and later
Oracle Cost Management - Version 11.5.10.2 and later
Oracle Work in Process - Version 11.5.10.2 and later
Information in this document applies to any platform.
CST_INVALID_WIP
CST_NO_BALANCE_ROW
Record not found in WIP_PERIOD_BALANCES
SYMPTOMS
This document provides the detailed instructions for fixing transactions that exist in the Material Transactions (MTL_MATERIAL_TRANSACTIONS.MMT) with error CST_INVALID_WIP. You cannot close the period due to error transactions in the MTL_MATERIAL_TRANSACTIONS (MMT) table. In the worker log, you may get a generic message that reads as follows:
APP-00001 Cannot find message name INV_NO_UPDATE or
CST_INVALID_WIP or
CST_NO_BALANCE_ROW Record not found in WIP_PERIOD_BALANCES or
The wip entity is either not defined or does not have a period balance entry or
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
This document will also show all the available information regarding the Root Cause of this data corruption.
Please use this document in the following order:
A. Identification Scripts: This will confirm if you are having this data corruption.
B. Verifying your file versions: This will allow you and Support to confirm if you are already using a known fixed version.
C. Root Cause Analysis. It shows all the relevant information about this issue Root Cause.
D. Datafix Instructions. It will provide steps for fixing the data corruption.
CAUSE
Data corruption where records are not found in the Work Order Period Balances (WIP_PERIOD_BALANCES) for the work order.
SOLUTION
A - Identification Scripts.
You might see the issue reviewing the following results:
1. Output of script collect_job_wip.sql for a problematic job (script is using job number as parameter). Refer to Note 1476250.1.
Or
The wipjob11i.sql output from Note 316142.1. Or run job.sql from Note 378348.1.
2. For eAM Jobs, you might review the following:
Enterprise Asset Management - Work Order Data Collection Script (Note 1448341.1)
Or
wipjob11i-EAM.sql
3. For Resource Transactions, the below query output display the errors
SELECT WCTI.PROCESS_STATUS, WTIE.ERROR_MESSAGE, WTIE.ERROR_COLUMN, WCTI.*
FROM WIP_COST_TXN_INTERFACE WCTI, WIP_TXN_INTERFACE_ERRORS WTIE
WHERE WCTI.TRANSACTION_ID = WTIE.TRANSACTION_ID(+);
4. For Material Transactions, the below query output displays the errors
select *
from mtl_material_transactions
where costed_flag = 'E'
and upper(error_code) like '%CST_INVALID_WIP%'
B - Verifying your File Versions.
Confirm your file version of INVTVPTX.fmb, INVTVPTB.pls, inctcm.ppc, wipmtivb.pls, wipfcomb.pls, wipflow.odf.
You can use the Application Check (appscheck) diagnostics to verify your file versions running the appscheck for inventory, work in process and costing. See Note 276207.1 - Diagnostics : Apps Check [Video].
C - Root Cause Analysis.
1. Release 12.x
There are no known root-cause patches.
2. Release 11.5.10.x
Release 11.5.10: Apply the following patches:
Patch 4209052 that gives INVTVPTX.fmb 115.99.115100.4 and INVTVPTB.pls 115.7.115100.2
Patch 4322200 that gives inctcm.ppc 115.71.115100.9
Patch 4771899 that gives WIP_MTI_PUB wipmtivb.pls 115.31.11510.16
Patch 5187652 that gives wipfcomb.pls 115.41.11510.3 and wipfscmb.pls 115.28.11510.6
Release 11.5.9: Apply Patch 5066396 that gives wipflow.odf 115.27.11510.2.
3. Release 10.7 :
Apply Patch 559432 that gives version inltcp.opp 50.91 (or INV patchset C or higher).
Apply Patch 624269 (cmlwmx.ppc 50.23) to fix the generic error message in the log file. It changes the error message from: APP-00001 Cannot find message name INV_NO_UPDATE to: No rows were updated in table WIP_PERIOD_BALANCES.
Apply Patch 776068 for non-character Rel 10.7.16.1, WIPREMDF.fmb 61.47 and Rel 11, WIPREMDF.fmb 110.27(or WIP Patchset D) -- To prevent entries for Repetitive Schedules in unopened periods. Apply Patch 876983 for character (WIPREMDF.inp 51.2)
D - Datafix.
Follow the steps below to fix the data in a test environment first, verify results, then migrate as appropriate:
1. Backup data.
Run the backup script. The datafix script does not backup data so do this on your own FIRST before running the datafix. Backup MMT records for the problem transactions. Here is an example to backup the MMT transactions:
CREATE table mtl_material_transactions_bak
AS
SELECT * from MTL_MATERIAL_TRANSACTIONS
WHERE organization_id = '&org_id_where_issue_exists'
and costed_flag in ('N','E');
2- Run the datafix script
To fix the issue, please proceed in a TEST Environment where this situation is replicated:
I. Try to reprocess
Normally, when there are records in MTL_MATERIAL_TRANSACTIONS with the Costed_Flag = 'E' you would resubmit these records by doing the following:
The following is the navigation path for resubmitting records through the applications:
Error transactions can be resubmitted through application using the following Navigation Path:
Cost Responsibility -> View Transactions -> Material Transactions query erred records -> From the menu, Tools, Select All, Submit All
If not, then:
a. Backup the rows to be updated.
b. Turn off the Cost Manager.
c. Run the SQL statement:
Use the following script to submit the records in MMT table:
update mtl_material_transactions MMT
set MMT.costed_flag = 'N',
MMT.error_code = NULL,
MMT.error_explanation = NULL,
MMT.transaction_group_id = NULL,
MMT.transaction_set_id = NULL
where MMT.organization_id = &organization_id
and MMT.costed_flag in ('N','E')
and NOT EXISTS ( SELECT 1 FROM MTL_TRANSACTION_ACCOUNTS MTA WHERE MMT.TRANSACTION_ID = MTA.TRANSACTION_ID);
commit;
d. Launch Cost Manager
Start the Cost Manager from Inventory responsibility or Manufacturing and Distribution manager Responsibility:
-Go Inventory>Setup>Transactions>Interface Managers
-select the Cost Manager record which should be Inactive now, set the Interval at 5 minutes (this is the recommended period)
-go to Menu>Tools>Launch Manager
-check if the Cost Manager gets activated and if it spawns the Cost Workers.
II. Check setup
When this does not work, it is usually due to one or more of the following three conditions (see BUG 573272, bug:573272 for more information on this topic):
1) Item costs are not defined for Frozen cost type in CST_ITEM_COSTS.
2) WIP_PERIOD_BALANCES is missing rows for the acct_period_id.
3) If a repetitive schedule, MTL_MATERIAL_TXN_ALLOCATIONS is missing rows for the corresponding transaction_id from MTL_MATERIAL_TRANSACTIONS.
Fixing missing rows in WIP_PERIOD_BALANCES for transactions that are the result of a DISCRETE JOB requires you to use the below cm276916.sql script. The rows are missing in WIP_PERIOD_BALANCES because status types 14 (Pending Close) and 15 (Failed Close) were not included while inserting rows for discrete jobs in this table.
To determine if you need to insert records into WIP_PERIOD_BALANCES, change the INSERT statement to a SELECT. If more than zero rows are returned, then re-run the script with INSERT and resubmit the records in MTL_MATERIAL_TRANSACTIONS.
NOTE: THIS DOES NOT APPLY TO CLOSED DISCRETE JOBS WDJ.STATUS_TYPE = 12)
III. Datafix
1. For fixing the data, identify the problematic jobs using following script in SQL:
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. Data fix
====================================================================
| Run the sql script cm276916.sql to create missing balances in |
| the wip_period_balances table. Run this script as APPS user: |
| For example, from UNIX shell, type: |
| sqlplus @$BOM_TOP/sql/cm276916.sql |
====================================================================
WHENEVER SQLERROR EXIT FAILURE;
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 trunc(OAP.SCHEDULE_CLOSE_DATE) >=
NVL(trunc(WDJ.DATE_RELEASED),trunc(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);
COMMIT;
EXIT;
OR for a specific Period, Job,Organization, Class. Can run the following as an Example:
The update script should be the following:
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)
VALUES
(156009,679074, NULL, SYSDATE, 0, SYSDATE, 0, 0, 2170, 6, 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);
ACCT_PERIOD_ID= 156009
WIP_ENTITY_ID= 679074
ORGANIZATION_ID= 2170
CLASS_TYPE= 6
3. For repetitive schedules, you may need to fix missing records in
WIP_PERIOD_BALANCES as well as in MTL_MATERIAL_TXN_ALLOCATIONS.
A. To fix missing rows in WIP_PERIOD_BALANCES as a result of a transaction that was due to a REPETITIVE SCHEDULE, you
will need to run the cm325424.sql script. It has been modified to include status_type=5 so that rows are inserted for closed schedules too.
------------------------------------------------------------------
| FILENAME |
| cm325424.sql |
| |
| DESCRIPTION |
| Bug fix 325424 |
| 1. Insert missing rows in wip_period_balances |
| |
| NOTES |
| Invoking Syntax: |
| sqlplus / @cm325424.sql |
| HISTORY |
| 28 NOV 95 Rina Banerjee Creation |
+================================================================+
WHENEVER SQLERROR EXIT FAILURE;
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, WRS.WIP_ENTITY_ID,
WRS.REPETITIVE_SCHEDULE_ID, SYSDATE,
0, SYSDATE,
0, 0,
WRS.ORGANIZATION_ID, 2,
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_REPETITIVE_SCHEDULES WRS,
ORG_ACCT_PERIODS OAP
WHERE WRS.STATUS_TYPE IN (3, 4, 5, 6)
AND WRS.ORGANIZATION_ID = OAP.ORGANIZATION_ID
AND OAP.OPEN_FLAG = 'Y'
AND OAP.PERIOD_CLOSE_DATE IS NULL
AND OAP.SCHEDULE_CLOSE_DATE >= NVL(WRS.DATE_RELEASED,
WRS.CREATION_DATE)
AND NOT EXISTS
(
SELECT 'X' FROM WIP_PERIOD_BALANCES WPB
WHERE WPB.REPETITIVE_SCHEDULE_ID IS NOT NULL
AND WPB.WIP_ENTITY_ID = WRS.WIP_ENTITY_ID
AND WPB.REPETITIVE_SCHEDULE_ID = WRS.REPETITIVE_SCHEDULE_ID
AND WPB.ORGANIZATION_ID = WRS.ORGANIZATION_ID
AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID);
COMMIT;
EXIT;
B. To fix missing rows in MTL_MATERIAL_TXN_ALLOCATIONS (MMTA):
a. Run the following select statement to see if you have missing rows:
select mmt.transaction_id id,mmt.transaction_quantity qty,
move_transaction_id moveid, transaction_source_id txnsource
from mtl_material_transactions mmt
where mmt.transaction_source_id=
and mmt.organization_id=
and not exists
(select 'x'
from mtl_material_txn_allocations mmta
where mmta.transaction_id=mmt.transaction_id
and mmta.organization_id=mmt.organization_id);
b. Run the following select statement to see what repetitive schedules do not have records in MMTA. When inserted, the missing rows from above will be allocated to the schedule(s) from below:
select repetitive_schedule_id,
(daily_production_rate * processing_work_days) schedqty
quantity_completed, status_type status
from wip_repetitive_schedules wrs
where wip_entity_id=
and not exists
(select 'x'
from mtl_material_txn_allocations mmta
where mmta.repetitive_schedule_id=wrs.repetitive_schedule_id
and mmta.organization_id=wrs.organization_id)
c. Set the schedule to Complete-Charges Allowed by running the following:
update wip_repetitive_schedules
set date_closed=NULL, status_type=4
where repetitive_schedule_id=
and organization_id =
d. Insert record into MMTA:
INSERT INTO MTL_MATERIAL_TXN_ALLOCATIONS
(TRANSACTION_ID,REPETITIVE_SCHEDULE_ID, ORGANIZATION_ID,
LAST_UPDATE_DATE,LAST_UPDATED_BY, CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN,REQUEST_ID,PROGRAM_APPLICATION_ID,
PROGRAM_ID, PROGRAM_UPDATE_DATE,PRIMARY_QUANTITY,
TRANSACTION_QUANTITY, TRANSACTION_DATE)
(select mmtt.material_allocation_temp_id,,
mmtt.organization_id, mmtt.last_update_date,
mmtt.last_updated_by,
mmtt.creation_date, mmtt.created_by, mmtt.last_update_login,
mmtt.REQUEST_ID, mmtt.PROGRAM_APPLICATION_ID,mmtt.PROGRAM_ID,
mmtt.program_update_date,mmtt.primary_quantity,
mmtt.transaction_quantity,
mmtt.transaction_date
from mtl_material_transactions_temp mmtt
where mmtt.transaction_source_id=
and mmtt.organization_id=)
e. Set the schedule status back to Complete-No Charges Allowed:
update wip_repetitive_schedules
set date_closed=NULL,
status_type=5
where repetitive_schedule_id=
and organization_id =
/
commit;
3- Check the data.
Check the data to ensure that your data has been corrected and you are not experiencing problems. You can rerun the script from the identification section.
REFERENCES
BUG:325557 - NEED SCRIPT TO FIX DATA FOR BUG 325424 - WIP_PERIOD_BALANCES
BUG:776068 - COMPLETED REPETITIVE SCHEDULES ARE NOT RELIEVING COSTS AT PERIOD CLOSE
BUG:559432 - COST MANAGER IS ALWAYS FAILING ANNOT PICKING UP COSTED MTL WITH COSTED FLAG='E
BUG:563253 - MATERIAL COST ERRORED OUT DUE TO MISSING
BUG:325424 - PERIOD OPENING DID NOT INSERT ROWS INTO WIP_PERIOD_BALANCES
NOTE:553138.1 - Material and Resource Transactions With Error Code CST_INVALID_WIP because of missing record in WIP_PERIOD_BALANCES
BUG:276916 - MISSING RECORDS IN WIP_PERIOD_BALANCES (SPORADIC)
BUG:876983 - REP SCHED COMPLETIONS ARE NOT RELIEVING COSTS AT PERIOD CLOSE
NOTE:2052420.1 - [Data Fix] WIP_COST_TXN_INTERFACE (WCTI)/MTL_MATERIAL_TRANSACTIONS (MMT) Tables with Errors Related to WIP_PERIOD_BALANCES Table
转到底部
