Talent Summary Dashboard

Manager Dashboard

Pivot Grid Overview

AWE Tables and Migration script

AWE 9.1 Application Tables:

-- Approval Process Definitions

set output EOAW_Systbls_export.dat;
set log EOAW_Systbls_export.log;

-- Export Transaction Registry
Export EOAW_ADMMON_CFG;
Export EOAW_MONDIS_DTL;
Export EOAW_MONDIS_HDR;
Export EOAW_USRMON_CFG;
Export EOAW_USRMON_LNG;
Export EOAW_TXN;
Export EOAW_TXN_CFG;
Export EOAW_TXN_COMP;
Export EOAW_TXN_LBL;
Export EOAW_TXN_LNG;
Export EOAW_TXN_LVL;


--Export Transaction Notifications 
Export EOAW_NOTIFY;
Export EOAW_NOTIFYDEF;
Export EOAW_NOT_HDR;
Export EOAW_NOT_USER;
Export EOAW_NOT_USRDEF;


-- Export User Lists
Export EOAWUSER_LIST;
Export EOAWUSER_LNG;

-- Export Process Definitions
Export EOAW_IDS where EOAWCOUNTERNAME = 'STAGE_ID';
Export EOAW_PATH;
Export EOAW_PATH_LNG;
Export EOAW_PRCS;
Export EOAW_PRCS_LNG;
Export EOAW_STAGE;
Export EOAW_STEP;
Export EOAW_STEP_LNG;
Export EOAW_STG_LNG;
Export EOAW_TIMEOUT;
Export EOAW_TIMEOUTDEF;

Export EOAW_AUTH;
Export EOAW_AUTH_DTL;
Export EOAWCRTA;
Export EOAWCRTA_LNG;
Export EOAWCRTA_REC;
Export EOAWCRTA_RECLNG;
Export EOAWCRTA_VAL;

-- Export NEM (Notification and Escalations)
Export EOAWNEM;
Export EOAWNEM_EMAIL;
Export EOAWNEM_EVENTS;

-- Export EMC (Email Collaboration)
Export EOAWMCLTLN_LNG;
Export EOAWEMC_LYT_HDR;
Export EOAWEMC_LYT_LIN;
Export EOAWEMC_MSGHDR;
Export EOAWFIELD_LIST;
Export EOAWRMINPT_HDR;
Export EOAWRMINPT_LIN;
Export EOAWXLAT_SYMBOL;

-- Export RunTime 
rem Because the user's Cross Reference Tables need to be exported as well, this should not be done by AWE;
rem Leaving them in for now in case we change this position. 
rem Export EOAW_STEPINST;
rem Export EOAW_USERINST;
rem Export EOAW_WL;

AWE 9.0 Application Tables:

-- Approval Process Definitions

set output PTAFAW_Systbls_export.dat;
set log PTAFAW_Systbls_export.log;

-- Export Transaction Registry
Export PTAFAW_ADMMON_CFG;
Export PTAFAW_MONDIS_DTL;
Export PTAFAW_MONDIS_HDR;
Export PTAFAW_USRMON_CFG;
Export PTAFAW_USRMON_LNG;
Export PTAFAW_TXN;
Export PTAFAW_TXN_CFG;
Export PTAFAW_TXN_COMP;
Export PTAFAW_TXN_LBL;
Export PTAFAW_TXN_LNG;
Export PTAFAW_TXN_LVL;


--Export Transaction Notifications 
Export PTAFAW_NOTIFY;
Export PTAFAW_NOTIFYDEF;
Export PTAFAW_NOT_HDR;
Export PTAFAW_NOT_USER;
Export PTAFAW_NOT_USRDEF;


-- Export User Lists
Export PTAFUSER_LIST;
Export PTAFUSER_LNG;

-- Export Process Definitions
Export PTAFAW_IDS where PTAFAWCOUNTERNAME = 'STAGE_ID';
Export PTAFAW_PATH;
Export PTAFAW_PATH_LNG;
Export PTAFAW_PRCS;
Export PTAFAW_PRCS_LNG;
Export PTAFAW_STAGE;
Export PTAFAW_STEP;
Export PTAFAW_STEP_LNG;
Export PTAFAW_STG_LNG;
Export PTAFAW_TIMEOUT;
Export PTAFAW_TIMEOUTDEF;

Export PTAFAW_AUTH;
Export PTAFAW_AUTH_DTL;
Export PTAFCRTA;
Export PTAFCRTA_LNG;
Export PTAFCRTA_REC;
Export PTAFCRTA_RECLNG;
Export PTAFCRTA_VAL;

-- Export NEM (Notification and Escalations)
Export PTAFAWNEM;
Export PTAFAWNEM_EMAIL;
Export PTAFAWNEM_EVENTS;

-- Export EMC (Email Collaboration)
Export PTAFMCLTLN_LNG;
Export PTAFEMC_LYT_HDR;
Export PTAFEMC_LYT_LIN;
Export PTAFEMC_MSGHDR;
Export PTAFFIELD_LIST;
Export PTAFRMINPT_HDR;
Export PTAFRMINPT_LIN;
Export PTAFXLAT_SYMBOL;

-- Export RunTime 
rem Because the user's Cross Reference Tables need to be exported as well, this should not be done by AWE;
rem Leaving them in for now in case we change this position. 
rem Export PTAFAW_STEPINST;
rem Export PTAFAW_USERINST;
rem Export PTAFAW_WL;

Approval Tables 8.9:

-- Approval Process Definitions
-- Export script for work orders

set output c:\temp\ApprovalsUserData.dat;
set log c:\temp\ApprovalsUserData.log;

EXPORT SAC_USER_LIST;


EXPORT WL_TEMPLATE_GEN;
EXPORT WL_TEMPL_GEN_TK;
EXPORT WL_TEMPL_GEN_RS;

rem Exporting Transaction Registry Information;
EXPORT SAC_AW_TXN;
EXPORT SAC_AW_TXN_COMP;
EXPORT SAC_AW_TXN_LNG;
EXPORT SAC_AW_TXN_LVL;
EXPORT SAC_AW_TXN_LBL;
EXPORT SAC_AW_NOTIFY;
EXPORT SAC_AW_TXN_CFG;
EXPORT SAC_AW_NOT_USER;

REM Exporting Process Definitions;
EXPORT SAC_AW_PRCS;
EXPORT SAC_AW_PRCS_LNG;
EXPORT SAC_AW_STAGE;
EXPORT SAC_AW_PATH;
EXPORT SAC_AW_PATH_LNG
EXPORT SAC_AW_STEP;
EXPORT SAC_AW_STEP_LNG;

REM Exporting Authorized Approver settings;
EXPORT SAC_AW_AUTH;
EXPORT SAC_AW_AUTH_DTL;

REM Exporting Criteria Details;
EXPORT SAC_CRTA;
EXPORT SAC_CRTA_LNG
EXPORT SAC_CRTA_REC;
EXPORT SAC_CRTA_RECLNG;
EXPORT SAC_CRTA_VAL;

Sample Script: 

EXPORT PS_PTAFAW_TXN where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_TXN_LBL where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_TXN_LVL where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_NOTIFY where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_NOT_USER where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFAW_TXN_CFG where PTAFPRCS_ID = 'ABC' ;

EXPORT PS_PTAFUSER_LIST;

EXPORT PS_N_WF_TRANS where N_TRAN_CATEGORY = 'ABC';

EXPORT PS_N_WF_TRAN_CAT where N_TRAN_CATEGORY = 'ABC';

EXPORT PS_PTAFAW_PATH  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_PATH B where B.PTAFPRCS_ID = PS_PTAFAW_PATH.PTAFPRCS_ID and B.EFFDT <= SYSDATE );

EXPORT PS_PTAFAW_PRCS  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_PRCS B where B.PTAFPRCS_ID = PS_PTAFAW_PRCS.PTAFPRCS_ID and B.EFFDT <= SYSDATE);

EXPORT PS_PTAFAW_STAGE  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STAGE B where B.PTAFPRCS_ID = PS_PTAFAW_STAGE.PTAFPRCS_ID and B.EFFDT <= SYSDATE);

EXPORT PS_PTAFAW_STEP  where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = PS_PTAFAW_STEP.PTAFPRCS_ID and B.EFFDT <= SYSDATE);

EXPORT PS_PTAFCRTA  where PTAFCRTA_ID = (select MAX(PTAFCRTA_ID) from PS_PTAFAW_STEP A where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = A.PTAFPRCS_ID and B.EFFDT <= SYSDATE));

EXPORT PS_PTAFCRTA_REC where PTAFCRTA_ID = (select MAX(PTAFCRTA_ID) from PS_PTAFAW_STEP A where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = A.PTAFPRCS_ID and B.EFFDT <= SYSDATE));

EXPORT PS_PTAFCRTA_VAL where PTAFCRTA_ID = (select MAX(PTAFCRTA_ID) from PS_PTAFAW_STEP A where PTAFPRCS_ID = 'ABC' and EFFDT = (select max(EFFDT) from PS_PTAFAW_STEP B where B.PTAFPRCS_ID = A.PTAFPRCS_ID and B.EFFDT <= SYSDATE));

 

Change Access Password

The symbolic ID is the account used by PeopleSoft to link users (operators) to the system account which is stored as ACCESSID in the PSACCESSPRFL record and encrypted.
Syntax to change or update password is:
CHANGE_ACCESS_PASSWORD [SymbolicID] [Password]
Alternative Method:
If user(“SYSADM” for Oracle and “SA” for MSS) is altered by the below query,

ALTER USER username IDENTIFIED BY password

then below scripts need to execute.

UPDATE PSSTATUS SET OWNERID = 'SYSADM';
UPDATE PSOPRDEFN SET SYMBOLICID = 'SYSADM1', OPERPSWD = OPRID, ENCRYPTED = 0;
UPDATE PSACCESSPRFL SET ACCESSID = 'SYSADM', SYMBOLICID = 'SYSADM1', ACCESSPSWD = 'SYSADM', VERSION = 0, ENCRYPTED = 0;
GRANT SELECT ON PSSTATUS TO people;
GRANT SELECT ON PSOPRDEFN TO people;
GRANT SELECT ON PSACCESSPRFL TO people;

Now login to Data-Mover in bootstrap mode and execute the below script.

 
REM - ENCRYPT PASSWORD
/
SET LOG d:\dbsetup_log\encrypt.log;
ENCRYPT_PASSWORD *;
/

Note: Make sure Connect-ID (people) account is not locked and correctly entered to configuration manager.

Summary of the requested processes by process status

Query to get summary of the requested process.

select

    RQST.RUNSTATUS,

    RQST.PRCSTYPE,

    (

        select XLAT.XLATLONGNAME

        from PSXLATITEM XLAT

        where XLAT.EFFDT = (

            select max(XLAT_ED.EFFDT)

            from PSXLATITEM XLAT_ED

            where XLAT_ED.FIELDNAME = XLAT.FIELDNAME

            and XLAT_ED.FIELDVALUE = XLAT.FIELDVALUE

        ) and XLAT.FIELDNAME = 'RUNSTATUS'

        and XLAT.FIELDVALUE = RQST.RUNSTATUS

    ) as RUNSTATUS_XLAT,

    count(RQST.PRCSINSTANCE) as TOTAL_PROCESSES,

    min(RUNDTTM) as FIRST_OCCURRED,

    max(RUNDTTM) as LAST_OCCURRED

from PSPRCSRQST RQST

group by RQST.RUNSTATUS, RQST.PRCSTYPE

order by RUNSTATUS_XLAT, RQST.PRCSTYPE

How To Delete an AWE Approval Process ID, Definition and Related Data

Below scripts helps to delete AWE related data.

Each Delete statement should be specific and include Key values.  This Example uses PrcsName & DefnName, which should be replaced with correct values, as well as the specific Effective Date (EFFDT) be used.

 

The Step Table;
DELETE FROM PS_EOAW_STEP
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT = '05-SEP-11'

The first of three Criteria Tables;
DELETE FROM PS_EOAWCRTA
WHERE EOAWCRTA_ID LIKE '%PrcsNameDefnName2011-09-05%'

The second of three Criteria Tables.
DELETE FROM PS_EOAWCRTA_REC
WHERE EOAWCRTA_ID LIKE '%PrcsNameDefnName2011-09-05%'

The thrid of three Criteria Tables.
DELETE FROM PS_EOAWCRTA_VAL
WHERE EOAWCRTA_ID LIKE '%PrcsNameDefnName2011-09-05%'

The Table used to hold Timeout information for Notification and Escalation processing;
DELETE FROM PS_EOAW_TIMEOUT
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11'

The Path Table;
DELETE FROM PS_EOAW_PATH
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11'

The Stage Table;
DELETE FROM PS_EOAW_STAGE
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11'

And finally the AWE Process table;
DELETE FROM PS_EOAW_PRCS
WHERE EOAWPRCS_ID = 'ThePrcsName' AND EOAWDEFN_ID = 'TheDefnName' AND EFFDT='05-SEP-11' `

Image URL from PeopleCode

Below code helps  to get Image URL.

%Response.GetImageURL(Image.PT_PIXEL);

Component Toolbar Actions

Below table contains component action button and associated Action ID.

Action ID	Button Name
#ICSave	Save | OK
#ICCancel	Cancel | Return
#ICList	Return to Search
#ICNextInList	Next in List
#ICPrevInList	Previous in List
#ICSendNotify	Notify
#ICRefresh	Refresh
#ICNext	Next Page in Component
#ICPrev	Previous Page in Component
#ICSpellCheck	Spell Check
#ICAdd	Add
#ICUpdate	Update/Display
#ICUpdateAll	Include History
#ICCorrection	Correct History
#ICExpertEntry	Expert Entry