The Z980_Term_conversion table serves a multitude of purposes.
Its primary reason is to enforce MANUFACTURER spellings throughout CAWIS and prevent duplication thru auto-correction.
By defining various TERM_TYPE_Codes (ttc), we use this table for further data verification and/or purification.
A full list of term types and descriptions can be had at : TR32_TERM_TYPE
TTC TERM_TYPE_ETXT
---------- --------------------
1 AIRCRAFT
2 ENGINE
3 PROPELLER
4 SKI/FLOAT
ACTIVEUSER ACTIVE USER
ADBKP AD MAKE/MODEL BACKUP
BADEMAIL BADEMAIL
BADFILE BAD AD FILE
CARSXREF CC/CW MFG/MODEL
CAW CAW USERS
CAWROLES CAW ROLES
CNREG COUNTRY OF REGISTRY
DATFIX DATA MODS IN SYNC
DATFIX2 DATFIX2
EASA EASA MEMBER
FAASTD STANDARDIZE FAA MFG
FLEET FLEET
FTP2019 AAIRFTP2019
FTP2020 AAIRFTP2020
FTP2021 AAIRFTP2021
NULLEMAIL NULL OWNER EMAILS
OLDEMAIL OLD CLIENT EMAIL
REGION CAWISREGION
SDBAD INVALID SDR DATA
SDCONV CONVERT FAA SDR NO
SDRACCT SDR IGNORE LIST
SYSID SYSTEM ACCOUNTS
TCORG TC SDR ORG
USERCHANGE USERCHANGE
USERROLES USERROLES
XEMAIL BACKUP EMAIL DATA
XFAX BACKUP FAX DATA
XPHONE BACKUP PHONE DATA
YRMON YEAR-MONTH
So Term_type_cd = SDBAD is a list of BAD/UNWANTED/UNNECESSARY data entries identified thru many years of data review (examples, NA, N/A, NOT FOUND, NONE) that we use to clear out trash values from various Text fields as seen below. The word, "UNKNOWN" is an agreed upon (with TC SDRS management) default value placed into NULL serial numbers, and part data fields because the CAWIS/WSDRS page demands these data elements as mandatory fields. We fill them to prevent this error (even though - again - at this point TC users cannot go into edit mode on a non-Canadian SDR)
*/
UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = NULL
WHERE AIRCRAFT_MARK_ID = CARRIER_CD ; --- common FAA data entry error
UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = NULL
WHERE AIRCRAFT_MARK_ID IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = 'UNKNOWN'
WHERE AIRCRAFT_SERIAL_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET ENGINE_SERIAL_NUMBER = 'UNKNOWN'
WHERE ENGINE_SERIAL_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET PROPELLER_SERIAL_NUMBER = 'UNKNOWN'
WHERE PROPELLER_SERIAL_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET COMPONENT_SERIAL_NUMBER = 'UNKNOWN'
WHERE COMPONENT_SERIAL_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET COMPONENT_MANUFACTURER = 'MISC EQUIPMENT'
WHERE COMPONENT_MANUFACTURER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET COMPONENT_NAME = NULL
WHERE COMPONENT_NAME IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET PART_NAME = 'UNKNOWN'
WHERE PART_NAME IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET PART_NUMBER = 'UNKNOWN'
WHERE PART_NUMBER IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET PART_CONDITION = 'UNKNOWN'
WHERE PART_CONDITION IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
UPDATE M70_SDR_TRANSIT SET PART_LOCATION = NULL
WHERE PART_LOCATION IN (SELECT Z980.FROM_TXT FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'SDBAD') ;
-- DOUBLE CHECK THAT THESE FIELDS ARE DATA-COMPRESSED or TRIMMED
UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER = DATA_COMPRESSION(AIRCRAFT_MANUFACTURER) ;
UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = DATA_COMPRESSION(AIRCRAFT_MODEL) ;
UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = DATA_COMPRESSION(AIRCRAFT_SERIAL_NUMBER) ;
UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER = DATA_COMPRESSION(ENGINE_MANUFACTURER) ;
UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = DATA_COMPRESSION(ENGINE_MODEL) ;
UPDATE M70_SDR_TRANSIT SET ENGINE_SERIAL_NUMBER = DATA_COMPRESSION(ENGINE_SERIAL_NUMBER) ;
UPDATE M70_SDR_TRANSIT SET PROPELLER_MANUFACTURER = DATA_COMPRESSION(PROPELLER_MANUFACTURER) ;
UPDATE M70_SDR_TRANSIT SET PROPELLER_MODEL = DATA_COMPRESSION(PROPELLER_MODEL);
UPDATE M70_SDR_TRANSIT SET PROPELLER_SERIAL_NUMBER = DATA_COMPRESSION(PROPELLER_SERIAL_NUMBER) ;
UPDATE M70_SDR_TRANSIT SET COMPONENT_MANUFACTURER = DATA_COMPRESSION(COMPONENT_MANUFACTURER) ;
UPDATE M70_SDR_TRANSIT SET COMPONENT_MODEL = DATA_COMPRESSION(COMPONENT_MODEL) ;
UPDATE M70_SDR_TRANSIT SET COMPONENT_SERIAL_NUMBER = DATA_COMPRESSION(COMPONENT_SERIAL_NUMBER) ;
UPDATE M70_SDR_TRANSIT SET PART_NUMBER = DATA_COMPRESSION(PART_NUMBER) ;
COMMIT;
UPDATE M70_SDR_TRANSIT SET PART_NAME = TRIM(PART_NAME) ;
UPDATE M70_SDR_TRANSIT SET PART_CONDITION = TRIM(PART_CONDITION) ;
UPDATE M70_SDR_TRANSIT SET PART_LOCATION = TRIM(PART_LOCATION) ;
UPDATE M70_SDR_TRANSIT SET PART_NAME = 'UNKNOWN' WHERE TRIM(PART_NAME) IS NULL ;
UPDATE M70_SDR_TRANSIT SET PART_CONDITION = 'UNKNOWN' WHERE TRIM(PART_CONDITION) IS NULL ;
UPDATE M70_SDR_TRANSIT SET PART_LOCATION = 'UNKNOWN' WHERE TRIM(PART_LOCATION) IS NULL ;
UPDATE M70_SDR_TRANSIT SET PART_NUMBER = 'UNKNOWN' WHERE TRIM(PART_NUMBER) IS NULL ;
COMMIT;
--- ENSURE THE DATE FIELDS ARE POPULATED
UPDATE M70_SDR_TRANSIT SET ENTRY_DATE = SYSDATE WHERE ENTRY_DATE IS NULL ;
UPDATE M70_SDR_TRANSIT SET LAST_UPDATE = ENTRY_DATE WHERE LAST_UPDATE IS NULL ;
UPDATE M70_SDR_TRANSIT SET OCCURENCE_DATE = ENTRY_DATE WHERE OCCURENCE_DATE IS NULL ;
COMMIT;
0 Comments