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 DEV/ACC/PreACC/Training regions,
or following any execution of the CCARCS/CAWIS synchronization routines (which pulls Prod data) in any of those regions.
see : PBI-125292
BATCH JOBS
ENSURE that any automated triggers for Batch jobs (CCARCS-CAWIS sync, Project Notifications) are DEACTIVATED in the Development and Acceptance regions. If the synchronization routine is run in this region it can do a great deal of damage.
REGION-SPECIFIC DATA
TR47_CAWIS_GENERAL_PROPS needs to be backed up before any data refresh and 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 ;
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’) ;
/* ALWAYS skip the system Id’s, ‘CLIENT’, ‘ccarcsupd’ etc. . . */
2) Owner AAIR Access code/passwords are to be set to “XXXX1111”
UPDATE Y50_PARTY SET ACCESS_CODE = '37328B5E590E26C946B75A91E26B33B2'
WHERE CLIENT_ID IS NOT NULL ;
/*only the owners have client_ids */
CONTACT DATA
3) 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 ;
4) 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 = 'SYLVIE.BLAIS@TC.GC.CA'
WHERE ADDRESS_PROPERTY_TXT = 'SYLVIE.BLAIS@TC.GC.CAX' ;
5) ENSURE THAT CONTINUING AIRWORTHINESS ACCOUNTS REMAIN ACTIVE
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');
COMMIT;
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 - 2021-08-25
Add Comment