20 Jan 11 Record not found in WIP_PERIOD_BALANCE (metalink note ID 1080033.6)

Unable to close EAM Work Orders because Resource transactions are not being processed by the Cost Manager. Records are stuck in the wip_cost_txn_interface (WCTI) table with process_status = 1 (pending). Actual Cost Worker, CMCACW, ends with an error and the corresponding material transaction is erred in the mtl_material_transactions (MMT) table with costed_flag = E. The work order for the erred record in mtl_material_transactions is in Failed Close Status.

Problem Description
——————-
You cannot close the period due to errored transactions in the
MTL_MATERIAL_TRANSACTIONS (MMT) table. In the worker log, you may get a
generic message that reads:

APP-00001 Cannot find message name INV_NO_UPDATE

Solution Description
——————–
I. Normally, when there are records in MTL_MATERIAL_TRANSACTIONS with the
Costed_Flag = ‘E’ you would resubmit these records by doing the following:

a. Backup the rows to be updated.
b. Turn off the Cost Manager.
c. Run the SQL statement:

[code]UPDATE MTL_MATERIAL_TRANSACTIONS
SET costed_flag=’N’,
error_code = NULL,
error_explanation = NULL,
transaction_group_id=NULL
WHERE organization_id=<your org id> and
transaction_source_id=<your wip entity id> and
costed_flag IS NOT NULL; [/code]

II. When this does not work, it is usually due to one or more of the following
three conditions (see BUG:573272 for more information on this topic):

* Item costs are not defined for Frozen cost type in CST_ITEM_COSTS.
* WIP_PERIOD_BALANCES is missing rows for the acct_period_id.
* If a repetitive schedule, MTL_MATERIAL_TXN_ALLOCATIONS is missing rows
for the corresponding transaction_id from MTL_MATERIAL_TRANSACTIONS.

1. If you are running inltcp.opp 50.90 or lower, please apply patch for
BUG 559432 for version 50.91 (or INV patchset C or higher).

2. 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)

====================================================================
| 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 <APPS username/APPS password> @$BOM_TOP/sql/cm276916.sql |
====================================================================

[sql]
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);
[/sql]

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.

[sql]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);
————[/sql]

B. To fix missing rows in MTL_MATERIAL_TXN_ALLOCATIONS (MMTA):

a. Run the following select statement to see if you have missing
rows:

[sql]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=<wip_entity_id>
and mmt.organization_id=<your 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)[/sql]

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:

[sql]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=<wip_entity_id used above>
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)[/sql]

c. Set the schedule to Complete-Charges Allowed by running the
following:

[sql] update wip_repetitive_schedules
set date_closed=NULL, status_type=4
where repetitive_schedule_id=<repetitive_schedule_id above>
and organization_id = <your organization_id> [/sql]

d. Insert record into MMTA:

[sql]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,<repetitive_schedule_id above>,
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=<wip_entity_id?from w_p_b?>
and mmtt.organization_id=<your organization_id>)[/sql]

e. Set the schedule status back to Complete-No Charges Allowed:

[sql] update wip_repetitive_schedules
set date_closed=NULL,
status_type=5
where repetitive_schedule_id=<repetitive_schedule_id above>
and organization_id = <your organization_id>[/sql]

4. To fix the generic error message in the log file:

– Apply patch for bug 624269 (cmlwmx.ppc 50.23). 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.

5. To prevent entries for Repetitive Schedules in unopened periods:

– Apply patch for bug 776068 for non-character Rel 10.7.16.1,
WIPREMDF.fmb 61.47 and Rel 11, WIPREMDF.fmb 110.27(or WIP Patchset D)
– Apply patch for bug 876983 for character (WIPREMDF.inp 51.2)

6. One enhancement to keep an eye on…..bug 881684

Release 11i will have an enhancement to the View Material Transactions
form. This enhancement will allow the user to view material
transactions (MMT table) which are uncosted (mmt.costed_flag = N) or
erred out during cost processing (mmt.costed_flag = E). It will also
let the user resubmit those pending and erred transactions from the
same form.

Additional Search Words
———————–
period close errored

Etiketler: record not found, WIP_PERIOD_BALANCE

Kategori Oracle E-Business Suite

20 Jan 11 Record not found in WIP_PERIOD_BALANCE (metalink note ID 1080033.6)

Yorum Yaz