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));

 

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' `