PeopleSoft Technical: PeopleSoft AWE errors - Transaction and Setup Issues during AWE Transaction

PeopleSoft AWE Open Data Conversion Errors for Requisition

Introduction

For doing data conversion from PeopleSoft 8.9 Instance to PeopleSoft 9.1 Instance as part of upgrade, few points should be considered with respect to AWE data migration in 9.1 instances for Requisition.

Main Setup and Transaction tables in 8.9 for Workflow

S.NO	TABLE NAME	TABLE DESCRIPTION
1	PS_SAC_AW_IDS	Counters for Approval Workflow ID's
2	PS_SAC_AW_PRCS	This table holds the Approval Process definition properties
3	PS_SAC_AW_STEP	Approval Step
4	PS_SAC_AW_PATH	Approval Path Definition
5	PS_SAC_AW_STAGE	Approval Stage
6	PS_PV_REQ_AW	Approval cross reference for ePro Requisitions. Contains the transaction keys and the approval process keys.
7	PS_SAC_AW_STEPINST	Instance of approval step
8	PS_SAC_AW_USERINST	Step instance assigner to user

Main Setup and Transaction tables in 9.1 for Workflow

S.NO	TABLE NAME	TABLE DESCRIPTION
1	PS_EOAW_IDS	Counters for Approval Workflow ID's. The counter values should be the max values of the EOAWTHREAD_ID present in the instance
2	PS_EOAW_PRCS	This table holds the Approval Process definition properties. Gets populated when we manually configure Approval Process Setup
3	PS_EOAW_STEP	Approval Step. Gets populated when we manually configure Approval Process Setup
4	PS_EOAW_PATH	Approval Path Definition. Gets populated when we manually configure Approval Process Setup
5	PS_EOAW_STAGE	Approval Stage
6	PS_PV_REQ_AW	Approval cross reference for ePro Requisitions. Contains the transaction keys and the approval process keys.
7	PS_EOAW_STEPINST	Instance of approval step
8	PS_EOAW_USERINST	Step instance assigner to user
9	PS_EOAW_WL	Worklist used for all Approvals
10	PS_PO_AW	Approval cross reference for purchase orders. Contains the transaction keys and the approval process keys.

Mapping between 8.9 and 9.1 Workflow Tables

8.9 Table	9.1 Table
PS_SAC_AW_IDS	PS_EOAW_IDS
PS_SAC_AW_PRCS	PS_EOAW_PRCS
PS_SAC_AW_STEP	PS_EOAW_STEP
PS_SAC_AW_PATH	PS_EOAW_PATH
PS_SAC_AW_STAGE	PS_EOAW_STAGE
PS_PV_REQ_AW	PS_PV_REQ_AW
PS_SAC_AW_STEPINST	PS_EOAW_STEPINST
PS_SAC_AW_USERINST	PS_EOAW_USERINST

EOAW_STEPINST (After migrating data from 8.9 to 9.1 following few points needs to be considered)

a) The EFFDT in the EOAW_STEPINST should be updated with the Max EFFDT from EOAW_PRCS ,for the Process which is Active.

b) The EOAWPATH_ID should be checked properly, so that the value should match the EOAWOATH_ID value in the EOAW_PATH.

c) The EOAWSTAGE_NBR should be checked properly, so that the value should match the EOAWOATH_ID value in the EOAW_STAGE.

Before proceeding to resolve errors, we need to check the data in the main setup tables in 9.1 instances for Requisition

Note: The main SETUP tables will act as base for all the transactions moved from 8.9 to 9.1 for requisition, and the open data needs to be updated accordingly. For all the errors mentioned below the SETUP data remains the same.

Online Navigation: Main Menu → eProcurement → Administer Procurement → Maintain Workflow → Approval Process Setup

Screenshot:

When we enter the details in the Approval Process Setup, data gets stored in the below mentioned tables:

EOAW_PRCS

EOAW_STAGE

EOAW_PATH

EOAW_STEP

EOAWCRTA

EOAWCRTA_REC

EOAWCRTA_VAL

These tables act as base for all the open data transaction moved from 8.9 to 9.1

Data in main SETUP tables in PeopleSoft 9.1 instance

1. PS_EOAW_PRCS

SQL:

SELECT EOAWPRCS_ID, EOAWDEFN_ID, EFFDT, EFF_STATUS, EOAWADMIN_ROLENAME, EOAWACT_LINE_LEVEL FROM PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = 'US001' AND EFF_STATUS =‘A’;

2. PS_EOAW_STEP

SQL:

SELECT EOAWPRCS_ID , EOAWDEFN_ID , EFFDT, EOAWSTAGE_NBR , EOAWPATH_ID , EOAWSTEP_NBR , SEQ_NBR , EOAWAPPROVER_LIST FROM PS_EOAW_STEP WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = 'US001' AND EFFDT = ( SELECT MAX(EFFDT) FROM PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = 'US001' AND EFF_STATUS ='A' ) ;

3. PS_EOAW_PATH

SQL:

SELECT EOAWPRCS_ID , EOAWDEFN_ID , EFFDT, EOAWSTAGE_NBR , EOAWPATH_ID , EOAWSTEP_SOURCE , EOAWNUMBER_HOURS, EOAWNUMBER_DAYS FROM PS_EOAW_PATH WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = 'US001' AND EFFDT = ( SELECT MAX(EFFDT) FROM PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = 'US001' AND EFF_STATUS ='A' ) ;

4. PS_EOAW_STAGE

SQL:

SELECT * FROM PS_EOAW_STAGE WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = ‘US001’ AND EFFDT = (SELECT MAX (EFFDT) FROM PS_EOAW_PRCS WHERE EOAWPRCS_ID = 'Requisition' AND EOAWDEFN_ID = 'US001' AND EFF_STATUS =‘A’);

The remaining three table details are not shown, as those are transaction tables. And data will be entered whenever we create a transaction. And the data in these tables will merely depends on the data in the main four SETUP tables, mentioned above.

Below are the lists of five most frequently occurring errors in requisition, when doing an Upgrade from PeopleSoft 8.9 to PeopleSoft 9.1.

Error 1: PeopleCode Error

Navigation: Main menu → eProcurement → Manage Requisition

Error Description: While clicking on View Approval link in Manage Requisition Page, PeopleCode error comes.

Resolution of Error 1

1. Check the entry for this requisition in PV_REQ_AW

SQL:

SELECT EOAWTHREAD_ID, EOAWPRCS_ID, EOAWDEFN_ID, EOAWREQUESTOR_ID, RECNAME, EOAWTHREAD_STATUS, EOAWPARENT_THREAD, BUSINESS_UNIT, REQ_ID, LINE_NBR FROM PS_PV_REQ_AW WHERE BUSINESS_UNIT = 'US001' AND REQ_ID = ‘0000004340’;

2. Check data in EOAW_STEPINST for the thread ID fetched from above SQL

SQL:

SELECT EOAWTHREAD_ID, EOAWSTEP_INSTANCE, EOAWPRCS_ID, EOAWDEFN_ID, EFFDT, EOAWPATH_ID, EOAWSTAGE_NBR, EOAWSTEP_NBR, EOAWSTEP_STATUS FROM PS_EOAW_STEPINST WHERE EOAWTHREAD_ID IN (SELECT EOAWTHREAD_ID FROM PS_PV_REQ_AW WHERE BUSINESS_UNIT = 'US001' AND REQ_ID = '0000004340');

Reason: The EFFDT in EOAW_STEPINST for this requisition, needs to be updated by the maximum EFFDT from EOAW_PRCS which is active based on the EOAWPRCS_ID & EOAWDEFN_ID.

EOAWPRCS_ID is ‘Requisition’ and EOAWDEFN_ID is the Business Unit i.e. ‘US001’.In this case the value of EFFDT in PS_EOAW_STEPINST should be 01/01/1950 (Please refer page number 9 , the table EOAW_PRCS contains the details of the EFFDT. The details of EFFDT can also be found from Online Setup page at Page number 8).

SQL to update EFFDT in EOAW_STEPINST table by maximum EFFDT from EOAW_PRCS which will be active, based on the EOAWPRCS_ID & EOAWDEFN_ID

Update PS_EOAW_STEPINST B

SET B.EFFDT = (

Select max (A.effdt)

From PS_EOAW_PRCS A

Where A.EOAWPRCS_ID = 'Requisition'

And A.EOAWDEFN_ID = B.EOAWDEFN_ID

AND A.EFF_STATUS = 'A'

AND A.EOAWPRCS_ID=B.EOAWPRCS_ID

)

WHERE EXISTS (

SELECT 'X'

From PS_EOAW_PRCS A1

Where A1.EOAWPRCS_ID = 'Requisition'

And A1.EOAWDEFN_ID = B.EOAWDEFN_ID

AND A1.EFF_STATUS = 'A'

AND A1.EOAWPRCS_ID=B.EOAWPRCS_ID

)

Error 2: Approval Map not Visible

Navigation: Main menu → eProcurement → Manage Requisition

Error Description: While clicking on View Approval link in Manage Requisition Page, it shows ‘No Approval Required’ even when the Requisition is in pending status.

Resolution of Error 2

1. Check the entry for this requisition in PS_PV_REQ_AW

SQL:

2. Check for data in PS_EOAW_STEPINST for the thread ID fetched from above SQL

SQL:

Reason: The EOAWSTAGE_NBR is incorrect in EOAW_STEPINST for this requisition. The EOAWSTAGE_NBR should be the same as EOAW_STAGE based on the EFFDT from the EOAW_PRCS which is active based on the EOAWPRCS_ID & EOAWDEFN_ID.

EOAWPRCS_ID is ‘Requisition’ and EOAWDEFN_ID is the Business Unit i.e. ‘US001’

In this case the EOAWSTAGE_NBR should be 1,2,3,4 depending on the SETUP in 9.1 ( Please refer page number 9 , the table EOAW_STAGE contains the details of the EOAWSTAGE_NBR. The details of Stage Number (marked as red) can be found on Online Setup Page , refer page 8 for Online Setup page ).

Error 3: PeopleCode Error

Navigation: Main menu → eProcurement → Manage Requisition

Error Description: While clicking on View Approval link in Manage Requisition Page, it gives PeopleCode error, same as in Case 1 above.

Resolution of Error 3

1. Check the entry for this requisition in PS_PV_REQ_AW

SQL:

2. Check for data in PS_EOAW_STEPINST for the thread ID fetched from above SQL

SQL:

Reason: The data for EFFDT, EOAWPATH_ID, EOAWSTAGE_NBR , for the requisition mentioned above is incorrect. So the data needs to be updated for this Requisition in EOAW_STEPINST table.

The EFFDT in EOAW_STEPINST for this requisition needs to be updated by the maximum EFFDT from the EOAW_PRCS which is active based on the EOAWPRCS_ID & EOAWDEFN_ID. In this case the value of EFFDT in EOAW_STEPINST should be 01/01/1950 (Please refer page number 9 , the table EOAW_PRCS contains the details of the EFFDT. The details of EFFDT (marked as red) can also be found from Online Setup page at Page number 8)

The EOAWSTAGE_NBR for this requisition needs to be updated to the Value of EOAWSTAGE_NBR in the EOAW_STAGE Table based on the maximum EFFDT from EOAW_PRCS which is active based on the EOAWPRCS_ID & EOAWDEFN_ID. In this case EOAWSTAGE_NBR will be 1, 2, 3... According to the SETUP in 9.1 (Please refer page number 9 , the table EOAW_STAGE contains the details of the EOAWSTAGE_NBR. The details of Stage Number (marked as red) can be found on Online Setup Page , refer page 8 for Online Setup page)

The EOAWPATH_ID for this requisition needs to be updated to the Value of EOAWPATH_ID in the EOAW_PATH table based on the EOAWSTAGE_NBR ,based on the maximum EFFDT from EOAW_PRCS which is active based on the EOAWPRCS_ID & EOAWDEFN_ID (Please refer page number 9 , the table EOAW_PATH contains the details of EOAWPATH_ID (marked as red) ).

SQL to update EOAWPATH_ID in EOAW_STEPINST table by the value of EOAWPATH_ID present in the table EOAW_PATH based on EOAWPRCS_ID & EOAWDEFN_ID.

UPDATE PS_EOAW_STEPINST SET EOAWPATH_ID = ‘1’ WHERE EOAWPRCS_ID = ’Requisition’ AND EOAWDEFN_ID = ’US001’ ;

Error 4: Worklist Error

Navigation: Main menu → eProcurement → Manage Requisition

Error Description: While clicking on View Approval link in Manage Requisition Page, we are able to see approval Map, Login as approver to approve requisition and click on approval link.

Resolution of Error 4

For Worklist error we need to check the data from the following tables:

1. PS_EOAW_WL on the basis of EOAWTHREAD_ID

SQL:

SELECT BUSPROCNAME, ACTIVITYNAME, EVENTNAME, WORKLISTNAME, INSTANCEID, TRANSACTIONID, EOAWPRCS_ID, EOAWTHREAD_ID, EOAWDEFN_ID, EFFDT FROM PS_EOAW_WL WHERE EOAWTHREAD_ID IN (SELECT EOAWTHREAD_ID FROM PS_PV_REQ_AW WHERE BUSINESS_UNIT = 'US01' AND REQ_ID = '0000004344');

Reason: For the same EOAWTHREAD_ID there are 3 entries in EOAW_WL, of which one of the Event entry is Error whose EFFDT is 01/01/2011. This needs to be deleted as this is a part of corrupt data present in 9.1 as part of data migration from other instances or the EOAWTHREAD_ID Counter value needs to be updated in EOAW_IDS table.

Error 5: Data Integrity Error

Navigation: Main menu → eProcurement → Create Requisition

Error Description: Create Requisition and click on Save and Preview approvals, able to see Approval Map, click on Submit, PeopleCode error Occurs.

Resolution of Error 5

Check the Auto numbering sequence for the Business Unit.

Navigation:

Main menu → Set Up Financials/Supply Chain → Business Unit Related → Purchasing → Purchasing Definition

The Number (Last requisition Number) displayed above should be the last REQ_ID used for the BUSINESS UNIT. If this is fine then check 2^nd point.

Check the Counter Values in PS_EOAW_IDS

The Counter value present in EOAW_IDS table must be the maximum value of the thread IDs present in the transaction records. Transaction record is the cross reference record mentioned in Transaction Registry.

Transaction record for Requisition in 9.1 – PV_REQ_AW

Transaction record for Purchase Order in 9.1 – PO_AW

The counter value gets incremented whenever we create transactions.

In case of Open data migration from 8.9 to 9.1 instances , update the Counter value in EOAW_IDS with the corresponding maximum value of threads. The main Counter name is mentioned below.

The main COUNTERNAME related to Requisition, Purchase Order & Sourcing in EOAW_IDS table are:

PV_REQ_AW ( Counter name for Thread ID in Requisition)

PO_AW (Counter name for Thread ID in PO)

AUC_EVENT_APPR (Counter Name for Thread ID in Sourcing)

STEPINST_ID (Counter Name for Stepinst ID)

USERINST_ID (Counter Name for Userinst ID)

For Requisition, the counter value PV_REQ_AW must be the maximum value of EOAWTHREAD_ID present in the transaction table PS_PV_REQ_AW (transaction table for Requisition). In the similar manner the Counter value needs to be updated for requisition, Purchase Order, Sourcing etc.

Below update script will update the counter values for these counter name mentioned above.

/* Update the Counter with corresponding values */

UPDATE PS_EOAW_IDS SET EOAWCOUNTER = (SELECT MAX (EOAWTHREAD_ID) FROM PS_AUC_EVENT_APPR) WHERE EOAWCOUNTERNAME = 'AUC_EVENT_APPR’;

UPDATE PS_EOAW_IDS SET EOAWCOUNTER = (SELECT MAX (EOAWTHREAD_ID) FROM PS_PV_REQ_AW ) WHERE EOAWCOUNTERNAME = 'PV_REQ_AW';

UPDATE PS_EOAW_IDS SET EOAWCOUNTER = (SELECT MAX (EOAWSTEP_INSTANCE) FROM PS_EOAW_STEPINST) WHERE EOAWCOUNTERNAME = 'STEPINST_ID';

UPDATE PS_EOAW_IDS SET EOAWCOUNTER = (SELECT MAX (EOAWUSTEP_INST_ID) FROM PS_EOAW_USERINST ) WHERE EOAWCOUNTERNAME = 'USERINST_ID';

UPDATE PS_EOAW_IDS SET EOAWCOUNTER = (SELECT MAX (EOAWTHREAD_ID) FROM PS_PO_AW ) WHERE EOAWCOUNTERNAME = 'PO_AW’;

Optimistic lock Error is also related with this.

For resolving optimistic lock issue, check the counter values in EOAW_IDS table and if required , update the counter value as mentioned above.

PeopleSoft AWE errors - Transaction and Setup Issues during AWE Transaction

1 comment: