All contact & password data in all CAWIS test regions should be maintained as is outlined below to ensure no real-world clients are contacted during MDM or EDM testing.
These conditions need to be revisited after every DBA migration of data from Production to the DEV/ACC/PreACC/Training regions, or following any inadvertent execution of the CCARCS/CAWIS synchronization routines (which pulls Prod data) into those regions.
see : PBI-125292
BATCH JOBS
ENSURE that any automated triggers for Batch jobs (CCARCS-CAWIS sync, Project Notifications) are inactive in all regions except Production. If the synchronization routine is run in Non-Production region, it can do a great deal of damage to the test data.
DB-LINKS
Ensure all DBLinks are pointed to the correctly corresponding region after a test region refresh
DEV to DEV,
ACC to ACC etc…
REGION-SPECIFIC DATA BACKUPS
Backup the following data into TXT files and attach to the PBI before beginning the refresh (or clone)
TR47_CAWIS_GENERAL_PROPS needs to be backed up from the target region, then restored afterward
SELECT
'UPDATE TR47_CAWIS_GENERAL_PROPS SET PROPERTY_VALUE = ''' ||
PROPERTY_VALUE || ''' WHERE PROPERTY_NAME = ''' ||
PROPERTY_NAME || ''' ; ' AS UPD1
FROM TR47_CAWIS_GENERAL_PROPS ;
GENERIC SYSTEM ACCOUNT PASSWORDS
The system account passwords need to be backed up from Production and applied into the target region. A list of “system” accounts is found in the Z980 table with Term_type_cd = ‘SYSID’
SELECT A01.USER_ID,
'UPDATE A01_USER SET PASSWORD = ‘'' || REPLACE(A01.PASSWORD,'''','''''') ||
''' WHERE USER_ID = ‘'' || A01.USER_ID || '’’ ;' UPD1
FROM A01_USER A01
WHERE A01.USER_ID IN (SELECT FROM_TXT FROM Z980_TERM_CONVERSION
WHERE TERM_TYPE_CD = 'SYSID') ;
AFTER A TEST REGION DATA REFRESH, PERFORM THE FOLLOWING TASKS
PASSWORDS
1)Passwords on all Userids are to be set to “XXXXXX”
UPDATE A01_USER A01 SET PASSWORD = 'jV1Vz@<=lz'
WHERE A01.USER_ID NOT IN
(SELECT FROM_TXT FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = ‘SYSID’) ;
/* Apply the Production passwords for all SYSTEM Userids afterward */
2) Owner AAIR Access code/passwords are to be set to “XXXX1111”
UPDATE Y50_PARTY SET ACCESS_CODE = '37328B5E590E26C946B75A91E26B33B2'
WHERE CLIENT_ID > 0 ;
/* only the owners have client_ids on the Y50 table */
CONTACT DATA
4) All FAX numbers are to have their area codes replaced with 000-
SELECT * FROM A92_ADDRESS_PROPERTY
WHERE SUBSTR(ADDRESS_PROPERTY_TXT,1,3) <> '000' AND ADDRESS_PROPERTY_TYPE_CD = 2 ;
UPDATE A92_ADDRESS_PROPERTY
SET ADDRESS_PROPERTY_TXT = '000-' || SUBSTR(ADDRESS_PROPERTY_TXT,5,8)
WHERE SUBSTR(ADDRESS_PROPERTY_TXT,1,3) <> '000' AND ADDRESS_PROPERTY_TYPE_CD = 2 ;
5) All Email addresses are to be suffixed with "X"
SELECT * FROM A92_ADDRESS_PROPERTY
WHERE ADDRESS_PROPERTY_TXT LIKE '%@%' AND ADDRESS_PROPERTY_TXT NOT LIKE '%X' ;
UPDATE A92_ADDRESS_PROPERTY
SET ADDRESS_PROPERTY_TXT = UPPER(ADDRESS_PROPERTY_TXT) || 'X'
WHERE ADDRESS_PROPERTY_TXT LIKE '%@%' AND ADDRESS_PROPERTY_TXT NOT LIKE '%X' ;
/* ensure CAWIS admin’s email remains unmarked */
UPDATE A92_ADDRESS_PROPERTY
SET ADDRESS_PROPERTY_TXT = 'LAUREN.MCMAHON@TC.GC.CA'
WHERE ADDRESS_PROPERTY_TXT = 'LAUREN.MCMAHON@TC.GC.CAX' ;
6) ENSURE THAT CONTINUING AIRWORTHINESS ACCOUNTS REMAIN ACTIVE AND HAVE ADMIN ROLE CODES
The Userid’s for all members of Continuing Airworthiness are found on the Z980 table where Term_type_cd = ‘CAW’
UPDATE A01_USER SET ACTIVE_IND = 'Y'
WHERE ACTIVE_IND = 'N'
AND USER_ID IN (SELECT FROM_TXT FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'CAW');
UPDATE A103_USER_ROLES SET CAWIS_ROLE_CD = 'AU'
WHERE CAWIS_ROLE_CD = 'NS'
AND USER_ID IN (SELECT FROM_TXT FROM Z980_TERM_CONVERSION WHERE TERM_TYPE_CD = 'CAW');
7) Check PINS to ensure that the connection strings for that region are still valid.
Note : Its normal to find a small amount of records, with legitimate TC-email, or TC-employee personal emails, or TC cell phone numbers in the FAX fields at any given time. This is how data conditions are setup for MDM/EDM testing.
KM - 2022-08-10
Add Comment