WIP Discrete Job or EAM Work Order remain stuck in Pending Close Status (文档 ID 158674.1)

摘自：https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=184520598149910&id=158674.1&_adf.ctrl-state=rcpxur5y4_57

APPLIES TO:

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

SYMPTOMS

Cannot Close A WIP Discrete Job: Job status remains in Pending Close

ERROR:
APP-WIP-25191: x of x records failed.
You cannot change status of jobs. Either concurrent program exists to close these jobs or jobs did not have status of Pending Close
OR ERROR:
APP-WIP-25190 x of x records failed closing discrete jobs
OR ERROR:
APP-WIP-25190: You are not allowed to close jobs with these statuses

Steps to reproduce:

Case1:

Work in Process > Discrete Jobs > Close Discrete Jobs

Case2:

When trying to reprocess using Close Discrete jobs form (WIPDJMDF )
WIP > Discrete jobs> Close Discrete jobs > Tools > Close

Case3:

Close EAM Work Orders:

EAM > Close WO

Case4:
Log of concurrent request WICDCL - Close Discrete Jobs may show following message

delete Existing reservations
Exception has occurred

Case5:

Another situation is when the Work Orders get closed successfully but the status hasn't changed.

CAUSE

Unwanted records exist in WIP_DJ_CLOSE_TEMP
If, for whatever reason, the concurrent process is interrupted or terminated, records can get stranded in WIP_DJ_CLOSE_TEMP. Since all of the records in this table have status of 'Pending Close', no close routines have been called and committed.

SOLUTION

SQL Query to identify all Pending Close work orders (WIP and EAM):

SELECT WDJ.ORGANIZATION_ID,
       WDJ.WIP_ENTITY_ID,
       WE.WIP_ENTITY_NAME,
       WE.ENTITY_TYPE,
       WDJ.DATE_COMPLETED,
       WDJ.DATE_CLOSED,
       WDJ.DATE_RELEASED,
       WDJ.STATUS_TYPE WDJ_STATUS,
       WT.TRANSACTION_ID resource_transaction_id,
       WT.Transaction_Type resource_transaction_type,
       EWOD.user_defined_status_id wo_status,
       WDCT.status_type previous_status,
       WDCT.actual_close_date
FROM WIP_DISCRETE_JOBS      WDJ,
       WIP_ENTITIES           WE,
       wip_transactions       wt,
       eam_work_order_details ewod,
       WIP_DJ_CLOSE_TEMP      WDCT
WHERE WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
   and WDJ.WIP_ENTITY_ID = WDCT.WIP_ENTITY_ID(+)
   and wdj.organization_id = we.organization_id(+)
   and ewod.organization_id(+) = wdj.organization_id
   and ewod.wip_entity_id(+) = wdj.wip_entity_id
   and WDJ.WIP_ENTITY_ID = WT.WIP_ENTITY_ID(+)
   and wt.transaction_type(+) = 6
   and wt.organization_id(+) = wdj.organization_id
   and (WDJ.STATUS_TYPE = 14 OR EWOD.USER_DEFINED_STATUS_ID = 14);

DISCRETE JOBS

A. The following steps can be used via front end for WIP jobs:
Below R11.5.9
1.1 Navigate to Cost/Discrete Jobs/Close Discrete Jobs (Form).
1.2 Select the job in question and hit the [Open] button.
1.3 Go to Tools -> close -> select "Unclose". This puts the job back to the status it was in prior to attempting to close (i.e., Complete-No Charges Allowed) and clears out any records that may be erroneously stuck in the WIP_Discrete_Jobs_Close_Temp table that could prevent records from being processed.

Release 11.5.10.2 and R12
1. Check concurrent requests to ensure that no Close Job process is running.
2. Navigate Discrete > Close Discrete Jobs > Close Discrete Jobs(FORM).
3. Close the Find Discrete Jobs window.
4. On the Close Discrete Jobs Summary window:
a. Choose Query/Enter (F11).
b. Drag the horizontal scroll bar to the right to see the STATUS column.
c. Type 'Pending Close' (without quotes) in the STATUS column.
d. Choose Query/Run (ctrl + F11).
5. Choose Edit/Select All to select all pending close jobs from the summary window.
6. Choose Special/Check Requests to restore the status for all selected jobs.

Ideally, step A mentioned above should suffice to re-submit the jobs in Pending Close status and the rest of the steps are not required. If step A fails, for any reason then we need to run datafix to delete the records from the WIP_DJ_CLOSE_TEMP, change status of the job/WO to Failed Closed or Complete and then re-submit the jobs/WOs again manually for closure.

B. Datafix via back end:

In case you are not able to change the status from Pending Close the the previous status using the application , then backend intervention is required: This might happen because if, for whatever reason, the concurrent process is interrupted or terminated, records can get stuck in table WIP_DJ_CLOSE_TEMP. Since all of the records in this table have status of 'Pending Close', no close routines have been called and committed. Therefore, all you need to do is reset the job status from Pending Close to its previous status in WIP_DISCRETE_JOBS and delete the record from WIP_DJ_CLOSE_TEMP. You will then need to rerun the discrete job closure.

Please run the following data fix which will put the job in status Failed Closed. Failed Close status is allowing the status change in WIP:

1. Get the list of the jobs which are in pending close status:

2. Create backup tables:

create table xx_wdj_backup_001 as select * from wip_discrete_jobs where status_type = 14;

create table xx_wdct_backup_001 as select * from wip_dj_close_temp;

3. Datafix:

delete from wip_dj_close_temp where 1 = 1;

update wip_discrete_jobs wdj
set wdj.status_type = 15, -- failed close
last_updated_by = -999
where wdj.status_type = 14 -- pending close
and not exists (select 1 from wip_cost_txn_interface wcti
where wcti.wip_entity_id = wdj.wip_entity_id
and wcti.organization_id = wdj.organization_id
and wcti.transaction_type = 6 -- Job close variance
UNION ALL
select 1 from wip_transactions wt
where wt.wip_entity_id = wdj.wip_entity_id
and wt.organization_id = wdj.organization_id
and wt.transaction_type = 6);

commit;

4. Retry to close the job via Close Discrete Job form or SRS.

EAM WORK ORDERS

Same issue might happen for EAM Work orders.

DATAFIX for Release 11i

The datafix is similar to the WIP datafix, only that the WO should be changed to status Complete

1. Get the list of the jobs which are in pending close status:

2. Create backup tables:

create table xx_wdj_backup_001 as select * from wip_discrete_jobs where status_type = 14;

create table xx_wdct_backup_001 as select * from wip_dj_close_temp;

3. Datafix:

delete from wip_dj_close_temp where 1 = 1;

update wip_discrete_jobs wdj
set wdj.status_type = 4, -- complete
last_updated_by = -999
where wdj.status_type = 14 -- pending close
and not exists (select 1 from wip_cost_txn_interface wcti
where wcti.wip_entity_id = wdj.wip_entity_id
and wcti.organization_id = wdj.organization_id
and wcti.transaction_type = 6 -- Job close variance
UNION ALL
select 1 from wip_transactions wt
where wt.wip_entity_id = wdj.wip_entity_id
and wt.organization_id = wdj.organization_id
and wt.transaction_type = 6);

commit;

4. Retry to close the job via Close Work Orders form or Maintenance Super User > mass close

DATAFIX for Release 12.0 and 12.1

Since R12 onwards, EAM is using table EAM_WORK_ORDER_DETAILS to keep the WO user defined statuses, so we will need to fix the status in this table along with the one in wip_discrete_jobs:

1. Get the list of the jobs which are in pending close status:

2. Create backup tables:

create table xx_wdj_backup_001 as select * from wip_discrete_jobs where status_type = 14;

create table xx_wdct_backup_001 as select * from wip_dj_close_temp;

create table xx_ewod_backup_001 as select * from eam_work_order_details where wip_entity_id in (select wip_entity_id from wip_discrete_jobs where status_type = 14);

3. Datafix:

delete from wip_dj_close_temp where wip_entity_id in (select wip_entity_id from wip_discrete_jobs where status_type = 14 and wip_entity_id in (select wip_entity_id from wip_entities where entity_type in (6,7)));

The following will update the status to failed closed as the job close variance transactions are not created and the entity type is 6 (open eam work) and the close date is NULL

update wip_discrete_jobs wdj
set wdj.status_type = 15, -- failed close
wdj.last_updated_by = -999,
wdj.last_update_date = sysdate
where wdj.status_type = 14 -- pending close
and not exists (select 1 from wip_cost_txn_interface wcti
where wcti.wip_entity_id = wdj.wip_entity_id
and wcti.organization_id = wdj.organization_id
and wcti.transaction_type = 6 -- Job close variance
UNION ALL
select 1 from wip_transactions wt
where wt.wip_entity_id = wdj.wip_entity_id
and wt.organization_id = wdj.organization_id
and wt.transaction_type = 6)
and wdj.wip_entity_id in (select wip_entity_id from wip_entities where entity_type = 6)
and wdj.date_closed is NULL;

update eam_work_order_details
set user_defined_status_id = 15, -- failed close
last_updated_by = -999,
last_update_date = sysdate
where user_defined_status_id != 15 -- not failed close
and (wip_entity_id, organization_id) in
(select wip_entity_id, organization_id
from wip_discrete_jobs
where status_type = 15);

commit;

The following will update the status to Closed as the job close variance transactions exist and the close date is populated and the entity type is closed eam wo.

update wip_discrete_jobs wdj
   set wdj.status_type = 12,
         wdj.last_updated_by = -999,
         wdj.last_update_date = sysdate
where wdj.status_type = 14
   and wdj.wip_entity_id in
       (select wip_entity_id from wip_entities where entity_type = 7)
   and wdj.date_closed is not null
   and exists (select 1
          from wip_transactions wt
         where wt.wip_entity_id = wdj.wip_entity_id
           and wt.organization_id = wdj.organization_id
           and wt.transaction_type = 6)
   and not exists (select 1
          from wip_cost_txn_interface wcti
         where wcti.wip_entity_id = wdj.wip_entity_id
           and wcti.organization_id = wdj.organization_id
           and wcti.transaction_type = 6);

update eam_work_order_details ewod
   set ewod.user_defined_status_id = 12,
         ewod.last_updated_by = -999,
         ewod.last_update_date = sysdate
where ewod.user_defined_status_id != 12
   and (ewod.wip_entity_id, ewod.organization_id) in
       (select wdj.wip_entity_id, wdj.organization_id
          from wip_discrete_jobs wdj
         where wdj.organization_id = ewod.organization_id
           and wdj.status_type = 12);

commit;

For closed WOs that appears as still open (for WOs with entity_type = 7 and closed_date not NULL but status_type <> 12):

Please run the following DATAFIX in provided sequence in a TEST instance only (where you have reproducible cases):

create table xx_wdj_backup_01 as
select *
from wip_discrete_jobs wdj
where wdj.status_type != 12
   and wdj.wip_entity_id in
       (select wip_entity_id from wip_entities where entity_type = 7)
   and wdj.date_closed is not null
   and exists (select 1
          from wip_transactions wt
         where wt.wip_entity_id = wdj.wip_entity_id
           and wt.organization_id = wdj.organization_id
           and wt.transaction_type = 6)
   and not exists (select 1
          from wip_cost_txn_interface wcti
         where wcti.wip_entity_id = wdj.wip_entity_id
           and wcti.organization_id = wdj.organization_id
           and wcti.transaction_type = 6);

update wip_discrete_jobs wdj
   set wdj.status_type = 12
where wdj.status_type != 12
   and wdj.wip_entity_id in
       (select wip_entity_id from wip_entities where entity_type = 7)
   and wdj.date_closed is not null
   and exists (select 1
          from wip_transactions wt
         where wt.wip_entity_id = wdj.wip_entity_id
           and wt.organization_id = wdj.organization_id
           and wt.transaction_type = 6)
   and not exists (select 1
          from wip_cost_txn_interface wcti
         where wcti.wip_entity_id = wdj.wip_entity_id
           and wcti.organization_id = wdj.organization_id
           and wcti.transaction_type = 6);

commit;

create table xx_ewod_backup_01 as
select *
    from eam_work_order_details ewod
   where ewod.user_defined_status_id != 12
     and (ewod.wip_entity_id, ewod.organization_id) in
         (select wdj.wip_entity_id, wdj.organization_id
            from wip_discrete_jobs wdj
           where wdj.organization_id = ewod.organization_id
             and wdj.status_type = 12);

update eam_work_order_details ewod
   set ewod.user_defined_status_id = 12
where ewod.user_defined_status_id != 12
   and (ewod.wip_entity_id, ewod.organization_id) in
       (select wdj.wip_entity_id, wdj.organization_id
          from wip_discrete_jobs wdj
         where wdj.organization_id = ewod.organization_id
           and wdj.status_type = 12);

commit;