STEP 9 Parsing the Data

Sept 2021 : NOTE - all required code for this process is found at :

simply copy and place into your preferred SQL tool

We now need to move each data element, from the SDR_text field, over to its proper M70 database column.

Layout :

Note: We also EXPORT TC SDR data down to the FAA - yearly by email. TC only processes about 4k reports per year, as opposed to the FAA receiving about 50k. (The FAA export procedure will be documented at a later date) The FAA processes incoming TC data slowly, which means reports sent to the FAA a year or two back, might suddenly appear in the "new" FAA data, today.

When data is exported from TC,

the SDR number has the century "20" replaced with ‘CA’

the SDR-text is prefixed with the hashtag '(CAN)'. However the FAA sometimes removes these characters (for reasons unknown).

To avoid report duplication in CAWIS, our first move is to delete any potential Transport Canada data from M70 as best we can. A second deletion attempt is made later.

*/

DELETE FROM M70_SDR_TRANSIT X WHERE X.SDR_TEXT LIKE '%(CAN)%' ;

COMMIT;

/*

Next, we set defaults on a few fields.

The function DATA_CHAR_REPLACE, Uppercases and removes special characters and diacritical marks from the data - We need a consistent North American character set in the SDR data to allow for successful character string scanning in text searches.

*/

UPDATE M70_SDR_TRANSIT SET

COUNTRY_CD = 'US', --- USA

HQ_STATUS_CODE = 'R', --- REVIEWED

SDR_TEXT = REPLACE(DATA_CHAR_REPLACE(SDR_TEXT) ,' |','|');

COMMIT;

/*

The final 25 characters of the data string, pertains to junk fields that we do not use, Microfilm-numbers, FAA-regional-office-numbers, etc. We remove this first

*/

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT, 1 , LENGTH(SDR_TEXT) - 25) ;

COMMIT;

/*

We now proceed thru the length of SDR-txt, moving each data element to its corresponding M70 column, also removing the BAR delimiters as we go along.

The layout of the FAA data is provided here:

https://av-info.faa.gov/data/SDRS/Filelayout.txt

If an exception is raised at any point, (ie: bad data into a Date field, or alphabetics into a numeric) there may have been

a layout change on the FAA end, or
it could be just 1 or 2 bad records

If it`s a consistent layout change, delete all data from M70, then reload from Data.txt and adjust the code down below to compensate for the new field offset.

If the issue is with only 1 or 2 records, try to repair them by hand by inserting extra Bar delimiters in DATA.TXT else they will reoccur from week to week and raise an exception each time.

SDR_NUMBER

The FAA used to provide sequential SDR_numbers in the format YYYYMMDDNNNNN, up until around 2007 when they decided to drop this field, in favor of submitter generated numbers. A blank placeholder remains in the leftmost column where this number used to be provided.

The FAA unique SDR-number identifier is now a non-sequential client generated SUBMITTER-CONTROL-NUMBER found further down this list. (example: UIEA2021032600722)

Note: Quite often, these numbers are prefixed with the carrier-code for that submitter

We now use this Submitter-control-number as the unique record identifier, but we assign traditional style SDR_numbers (YYYYMMDDNNNNN) later in the process.

*/

--- COLUMN 1 IS NULL - This is where the old format SDR_numbers used to be - REMOVE DELIMITER

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- RECORD REVIEWED OR ENTRY DATE

UPDATE M70_SDR_TRANSIT SET ENTRY_DATE = NVL(TO_DATE(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )),'YYYYMMDD'),SYSDATE) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- NULL FIELD

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- REGION - "US" IF NULL

UPDATE M70_SDR_TRANSIT SET REGION_CODE = TRIM(NVL(SUBSTR(SUBSTR(SDR_TEXT,1 ,ABS((INSTR(SDR_TEXT,'|') - 1 ))),1,2),'US')) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- REPEAT OF REVIEWED/ENTRY DATE IN 3 SEPARATE COLUMNS

--- FIELDS NOT USED

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- SUBMITTER CONTROL NUMBER

UPDATE M70_SDR_TRANSIT SET SUBMITTER_CONTROL_NUM = TRIM(SUBSTR(SDR_TEXT,1 ,ABS((INSTR(SDR_TEXT,'|') - 1 )))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

At this point, we have the FAA Submitter-control-number in its proper column. We can now verify against WSDRS (M72_SDR) and delete from the transit table (M70) any reports that we've previously uploaded, by submitter-control-number match

*/

--- REMOVE RECORDS WE ALREADY HAVE

SELECT COUNT(*) FROM M70_SDR_TRANSIT; -- check the “before” count

--- Ensure the Submitter control numbers are DATA-COMPRESSED (chars A-Z,0-9, only)

UPDATE M70_SDR_TRANSIT

SET SUBMITTER_CONTROL_NUM = DATA_COMPRESSION(SUBMITTER_CONTROL_NUM)

WHERE SUBMITTER_CONTROL_NUM <> DATA_COMPRESSION(SUBMITTER_CONTROL_NUM);

COMMIT;

--- DELETE from M70, any reports that we already have in Production WSDRS

DELETE FROM M70_SDR_TRANSIT M70

WHERE EXISTS

(SELECT 1 FROM M72_SDR M72

WHERE M72.COUNTRY_CD = 'US'

AND TRIM(M72.SUBMITTER_FILE_NUM) = M70.SUBMITTER_CONTROL_NUM ) ;

COMMIT;

--- CHECK FOR CANADIAN Reports, 1 more time in the Submitter control number and delete from m70 (sometimes the FAA removes the SDR-TEXT Hashtag from the text)

DELETE FROM M70_SDR_TRANSIT M70

WHERE M70.SUBMITTER_CONTROL_NUM LIKE 'CA0%'

OR M70.SUBMITTER_CONTROL_NUM LIKE 'CA1%'

OR M70.SUBMITTER_CONTROL_NUM LIKE 'CA2%' ;

COMMIT;

SELECT COUNT(*) FROM M70_SDR_TRANSIT; -- check the “after” count

/*

Note : if the Before count = the After count, we likely didnt grab enough data. Simply make a note to go backward a few more months the next time an import is performed.

We now proceed with moving the remainder of the data to its proper columns

*/

--- PRODUCT_CD

UPDATE M70_SDR_TRANSIT SET PRODUCT_CD = SUBSTR(NVL(SUBSTR(SDR_TEXT,1 ,ABS((INSTR(SDR_TEXT,'|') - 1 ))),'1'),1,1) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ; --- REMOVE PROCESSED DATA

COMMIT;

--- OCCURENCE DATE -- uses the ENTRY-DATE if null

UPDATE M70_SDR_TRANSIT SET OCCURENCE_DATE = NVL(TO_DATE(

DATA_COMPRESSION_3(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))),'YYYYMMDD'), ENTRY_DATE) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- General aviation or not code - FIELD NOT USED

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- JASC CODE --- 0000 if NULL

UPDATE M70_SDR_TRANSIT SET JASC_NBR_1 = TRIM(NVL(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))),'0000') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PART NUMBER -- UNKNOWN if null

UPDATE M70_SDR_TRANSIT SET PART_NUMBER = NVL(TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))),'UNKNOWN') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PART NAME -- UNKNOWN if null

UPDATE M70_SDR_TRANSIT SET PART_NAME = NVL(TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))),'UNKNOWN') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- COMPONENT Manufacturer

UPDATE M70_SDR_TRANSIT SET COMPONENT_MANUFACTURER = TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- COMPONENT MODEL

/*

The WSDRS Component model is ALWAYS "EQUIPMENT" - and it is not shown on WSDRS screen ( Equipment/component models on Y62_product_make_model ONLY allow for a model designated as EQUIPMENT under a "Q" product-type - our goal here is to only display the MANUFACTURER - this standard is consistent throughout CAWIS )

The FAA "Component model" column is later moved to component serial number if the component-serial is found null, else it does not get used.

*/

UPDATE M70_SDR_TRANSIT SET COMPONENT_MODEL = TRIM(DATA_COMPRESSION(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

The FAA contiunes to use the manufacturer and model standards they established in the 1970's

MANUFACTURER = 6 CHARS

MODEL = 12 CHARS

This data needs to be translated to CAWIS standards later

MANUFACTURER_ID = NUMBER

MANUFACTURER = 20 CHARS

MODEL_ID = 20 CHARS

using the Y72_FAA_MODEL_CONVERSION table, however the translation data found there is far from perfect.

We now move the AIRCRAFT/ENGINE/PROPELLER/COMPONENT manufacturer/model data to its intended fields

*/

--- AIRCRAFT MFG

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER = TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- group code (FIELD NOT USED) (Note: this data has NO RELATIONSHIP to the field GROUP_ID on Y62)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- AIRCRAFT MODEL

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) );

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- Aircraft code (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- AIRCRAFT REGION - used as a SUBSTITUTE WHERE default REGION = US is found

UPDATE M70_SDR_TRANSIT SET REGION_CODE = TRIM(NVL(SUBSTR(SUBSTR(SDR_TEXT,1 ,ABS((INSTR(SDR_TEXT,'|') - 1 ))),1,2),'US') ) WHERE REGION_CODE = 'US' ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

Note: there is usually far less Engine data and even less Propeller data , than Aircraft/Airframe in these imports. This is to be considered normal.

*/

--- ENGINE MFG

UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER = TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- group (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- ENGINE MODEL

UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- engine code (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- Engine region (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PROPELLER MFG

UPDATE M70_SDR_TRANSIT SET PROPELLER_MANUFACTURER = TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

-- PROPELLER GROUP (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PROPELLER MODEL

UPDATE M70_SDR_TRANSIT SET PROPELLER_MODEL = DATA_COMPRESSION(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PROPELLER REGION (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PART LOCATION -- set to "UNKNOWN" if null

UPDATE M70_SDR_TRANSIT SET PART_LOCATION = NVL(TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))),'UNKNOWN') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PART CONDITION -- set to "UNKNOWN" if null

UPDATE M70_SDR_TRANSIT SET PART_CONDITION = NVL(TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))),'UNKNOWN') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- Submitter-code, alert code - FIELDS NOT USED

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- CARRIER CODE (Owner/operator of the aircraft at the time of reporting)

UPDATE M70_SDR_TRANSIT SET CARRIER_CD = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- Action taken codes

--- ACTION TAKEN 1

UPDATE M70_SDR_TRANSIT SET ACTION_TAKEN_CD_1 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- ACTION TAKEN 2

UPDATE M70_SDR_TRANSIT SET ACTION_TAKEN_CD_2 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- ACTION TAKEN 3

UPDATE M70_SDR_TRANSIT SET ACTION_TAKEN_CD_3 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- ACTION TAKEN 4

UPDATE M70_SDR_TRANSIT SET ACTION_TAKEN_CD_4 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

Descriptive text of the Action taken codes - fields not used

*/

--- ACTION TAKEN TEXT 1 (aka Precautionary Procedure)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

--- ACTION TAKEN TEXT 2

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

--- ACTION TAKEN TEXT 3

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

--- ACTION TAKEN TEXT 4

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- Nature of Condition codes

--- NATURE CODE 1

UPDATE M70_SDR_TRANSIT SET NATURE_CD_1 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- NATURE CODE 2

UPDATE M70_SDR_TRANSIT SET NATURE_CD_2 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- NATURE CODE 3

UPDATE M70_SDR_TRANSIT SET NATURE_CD_3 = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

In this instance, since we're translating the Nature-codes to WSDRS standards later, as a precaution, we append the Nature code text descriptions onto the end of the SDR text in case there are differences.

*/

--- NATURE TEXT 1 - APPEND TO SDR TEXT

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SDR_TEXT || ' (' || TRIM( SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ) || ')' ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- NATURE TEXT 2

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SDR_TEXT || ' (' || TRIM( SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ) || ')' ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- NATURE TEXT 3

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SDR_TEXT || ' (' || TRIM( SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ) || ')' ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- WHEN DISCOVERED CODE (aka STAGE OF OPERATION)

UPDATE M70_SDR_TRANSIT SET WHEN_DISCOVERED_CODE = NVL(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )),'UK') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

Descriptive text of the WHEN DISCOVERED code - field not used

*/

--- WHEN DISCOVERED TEXT

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

--- regional office numbers, microfilm number - FIELDS NOT USED

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- OFFICE REGION CODE - SWAP INTO SDR REGION IF SDR REGION IS STILL DEFAULTED TO "US"

UPDATE M70_SDR_TRANSIT SET REGION_CODE = NVL(SUBSTR(SUBSTR(SDR_TEXT,1 ,ABS((INSTR(SDR_TEXT,'|') - 1 ))),1,2),'US') WHERE REGION_CODE = 'US' ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- FAA DISTRICT OFFICE NUMBER (FIELD NOT USED)

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

NOTE: All American aircraft Tail Marks are prefixed with an "N" as per the ICAO agreements

https://wwwapps.tc.gc.ca/Saf-Sec-Sur/2/AWD-CN/documents/NATIONALITYMARKS.htm

and 99.9% of the FAA data imported does pertain to US registered aircraft

https://registry.faa.gov/aircraftinquiry

So we begin by automatically prefixing all tail marks with an "N", which the FAA data does not provide. M70.AIRCRAFT_TAIL_MARK is 10 characters long, so we have plenty of room to work with. The MAX length of the incoming tail marks is 5 characters. We clean up any discrepancies afterward.

*/

--- AIRCRAFT TAIL MARK

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = 'N' || TRIM(SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = DATA_COMPRESSION(AIRCRAFT_MARK_ID) ;

COMMIT;

--- Null out any instances where no tail mark was provided

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = NULL WHERE AIRCRAFT_MARK_ID = 'N';

COMMIT;

--- REMOVE "N" IF THIS IS NOT A US REGISTRY

--- Edit check: character #2 in an FAA tail mark is always numeric. There are usually a few non-American tail marks in every batch

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = SUBSTR(AIRCRAFT_MARK_ID,2,10)

WHERE SUBSTR(AIRCRAFT_MARK_ID,2,1) NOT IN ('1','2','3','4','5','6','7','8','9') ;

COMMIT ;

--- PART TOTAL TIME (aka TIME-SINCE-NEW - TSN)

--- the function, Data-compression_3 strips any input value down to Numerics only

UPDATE M70_SDR_TRANSIT SET PART_TOTAL_TIME = DATA_COMPRESSION_3('0' || SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PART TIME (since last inspect)

UPDATE M70_SDR_TRANSIT SET PART_TIME = DATA_COMPRESSION_3('0' || SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 ))) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- COMPONENT SERIAL NUMBER

UPDATE M70_SDR_TRANSIT SET COMPONENT_SERIAL_NUMBER = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

UPDATE M70_SDR_TRANSIT SET COMPONENT_SERIAL_NUMBER = COMPONENT_MODEL WHERE TRIM(COMPONENT_SERIAL_NUMBER) IS NULL

AND TRIM(COMPONENT_MODEL) IS NOT NULL ;

UPDATE M70_SDR_TRANSIT SET COMPONENT_MODEL = 'EQUIPMENT'

WHERE TRIM(COMPONENT_MODEL) IS NULL

AND TRIM(COMPONENT_SERIAL_NUMBER) IS NOT NULL;

UPDATE M70_SDR_TRANSIT SET COMPONENT_MANUFACTURER = 'MISC EQUIPMENT' -- manufacturer_id = 1 default

WHERE TRIM(COMPONENT_MANUFACTURER) IS NULL

AND TRIM(COMPONENT_SERIAL_NUMBER) IS NOT NULL;

UPDATE M70_SDR_TRANSIT SET COMPONENT_MANUFACTURER = 'MISC EQUIPMENT'

WHERE TRIM(COMPONENT_MANUFACTURER) IS NULL

AND TRIM(COMPONENT_MODEL) IS NOT NULL;

COMMIT ;

--- Severity - field not used

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- AIRCRAFT SERIAL NUMBER

UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- ENGINE SERIAL NUMBER

UPDATE M70_SDR_TRANSIT SET ENGINE_SERIAL_NUMBER = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

--- PROPELLER SERIAL NUMBER

UPDATE M70_SDR_TRANSIT SET PROPELLER_SERIAL_NUMBER = SUBSTR(SDR_TEXT,1 ,(INSTR(SDR_TEXT,'|') - 1 )) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SUBSTR(SDR_TEXT,INSTR(SDR_TEXT,'|') + 1 ,4000 ) ;

COMMIT;

/*

BACKFILL AIRCRAFT-TAIL-MARK ON MODEL/SERIAL MATCH

If 2 records in this batch, have the same Aircraft-Model & Aircraft-Serial-Number, they are the same aircraft. If one of those records is missing a TAIL-MARK, fill it with the Tail-mark from the other, if possible

*/

UPDATE M70_SDR_TRANSIT Y SET

AIRCRAFT_MARK_ID = (SELECT AIRCRAFT_MARK_ID FROM M70_SDR_TRANSIT X WHERE X.SDR_NUMBER_NUM = (SELECT MAX(Z.SDR_NUMBER_NUM) FROM M70_SDR_TRANSIT Z WHERE TRIM(Z.AIRCRAFT_MARK_ID) IS NOT NULL AND Z.AIRCRAFT_MODEL = Y.AIRCRAFT_MODEL AND Z.AIRCRAFT_SERIAL_NUMBER = Y.AIRCRAFT_SERIAL_NUMBER ) )

WHERE TRIM(AIRCRAFT_MARK_ID) IS NULL

AND EXISTS (SELECT 1 FROM M70_SDR_TRANSIT X WHERE TRIM(X.AIRCRAFT_MARK_ID) IS NOT NULL

AND X.AIRCRAFT_MODEL = Y.AIRCRAFT_MODEL

AND X.AIRCRAFT_SERIAL_NUMBER = Y.AIRCRAFT_SERIAL_NUMBER ) ;

/*

In event that there are discrepancies in the Aircraft Manufacturer/Model translation, we append the FAA mfg/model as was provided, onto the end of the SDR-text to show what originally appeared in the data.

Quite often a tail mark lookup on the FAA website, and an SDR lookup thru their query facility

http://av-info.faa.gov/sdrx/Query.aspx

will show discrepancies in mfg/model designation on a given report. This issue is on the FAA end, they dont always keep their SDR model tables up to date, so they swap in the closest value on occasion. This really only gets raised as an issue on reports that pertain to CANADIAN type-approved data. (Bombardier, Bell Helicopter or Pratt & Whitney products etc...)

FAA SDR management is aware of the issue.

*/

--- APPEND FAA MAKE MODEL TO TEXT

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SDR_TEXT ||

' (' || TRIM(AIRCRAFT_MANUFACTURER) || ' ' || TRIM(AIRCRAFT_MODEL) || ') ' ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '( )',' ') ;

COMMIT;

/*

Another FAA issue - HTML code spillage. Their client data input facility allows many of the HTML tags seen below, to slip into their descriptive text, either by cutting and pasting from Word docs, and/or due to some software issue.

The data appears with these tags in them on their own website as well, so its confirmed to be not an issue with the facility that uploads to the FTP site.

FAA SDR management has been made aware of the issue.

So we try to clean it up.

At this point in time - CAWIS/WSDRS users CANNOT make updates to foreign imported data, but they have repeatedly expressed a desire to do so. (they would like to Repair incorrect models wrt Canadian products, or simply add comments into the Supplemental-text, occasionally) This is not a major issue and should be included in a future release.

Additionally, as per a security review back in 2018, CAWIS was deemed to be vulnerable to XSS Cross-scripting.

IM224044 - XSS Vulnerability CAWIS and TDG Schedule

Edits were put on nearly every text field in the system to raise an error if HTML code was seen in the text.

And so to prevent any troubles up the road, and to improve readability we perform the replacements seen below.

Note : these were the tags I found on a dozen or so records during a data review, a few years back. I've likely missed a few others.

*/

SET DEFINE OFF;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '(<P>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</P>)','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<P>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</P>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<SPAN>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</SPAN>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<BR>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</BR>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<BR />','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<BR/>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'BOGUS=`1`','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '/>','');

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '"','`');

COMMIT;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<TABLE WIDTH=' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'CLASS=`MCE-ITEM-TABLE' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'STYLE=`HEIGHT:' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'BORDER-COLLAPSE: COLLAPSE' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'BORDER=`0`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'CELLSPACING=`0`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'CELLPADDING=`0`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-LEFT=' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-MCE-SELECTED' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<TBODY>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<TR STYLE=`HEIGHT:' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<TD HEIGHT=' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DIR=`LTR`>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</TD>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</TR>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</TBODY>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</TABLE>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<DIV ID=`MCERESIZEHANDLENW`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-MCE-BOGUS=' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'CLASS=`MCE-RESIZEHANDLE`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'UNSELECTABLE=`TRUE`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'STYLE=`CURSOR:' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'NW-RESIZE' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'MARGIN:' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'PADDING:' , '' ) ;

COMMIT;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'LEFT: -2.5PX' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'TOP: 11.5PX,`>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</DIV>' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<DIV ID=`MCERESIZEHANDLENE`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-MCE-BOGUS=`ALL`' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-MCE-BOGUS' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-MCE' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'CLASS=`MCE-RES' , '' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '+AMP' , ' ' ) ;

COMMIT;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'DATA-MCE-BOGUS=' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'NBSP' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<BR ' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</SPAN>' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'Ï¿½' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '<STRONG>' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'Ï»¿' , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '</STRONG>' , ' ' ) ;

COMMIT;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '**' , '*' ) ; -- spacesavers

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '--' , '-' ) ; -- spacesavers

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, 'A€' , '-' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT, '€' , '-' ) ;

COMMIT;

--- CLEANUP SDR TEXT FOR INSTANCES OF DEFAULT NATURE CODE DESCRIPTIONS OR OTHER JUNK DATA

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT,'|',' ') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT,'()','') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT,'(OTHER)','') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT,'(NONE)','') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT,'(UNKNOWN)','') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE(SDR_TEXT,'(NOT REPORTED)','') ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE (SDR_TEXT , 'N°' , '#' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE (SDR_TEXT , '//' , '*' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE (SDR_TEXT , '\\' , '*' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE (SDR_TEXT , CHR(9) , ' ' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = TRIM(DATA_CHAR_REPLACE(SDR_TEXT)) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE (SDR_TEXT , 'A€“' , '*' ) ;

-- oddball characters, caused by WORD copy/paste

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = REPLACE (SDR_TEXT , '€' , '*' ) ;

UPDATE M70_SDR_TRANSIT SET SDR_TEXT = 'NO TEXT PROVIDED' WHERE TRIM(SDR_TEXT) IS NULL ;

COMMIT;

/*