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:
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 = ‘0000004341’;
2. Check for data in PS_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 = '0000004341');
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:
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 = ‘0000004342’;
2. Check for data in PS_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 = '0000004342');
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 2nd 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.
No comments:
Post a Comment