STEP 30 SDR TEXT

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

simply copy and place into your preferred SQL tool

/*

APPEND THE YEAR OF MANUFACTURER (YOM) ONTO THE TEXT OF REPORTS WHERE WE'VE IDENTIFIED THE AIRCRAFT BY CAWIS_AIRCRAFT_ID (ie this aircraft was previously registered in Canada)

APPEND THE FAA SUBMITTER-file-number (aka SUBMITTER-CONTROL-NUMBER) onto the text, so the public can see the original FAA number if they choose to verify the report against the FAA SDR query site (the public cannot see the Submitter file number field on the web unless we put it into the text )

*/

--- APPEND YOM WHERE AVAILABLE

UPDATE M70_SDR_TRANSIT X

SET SDR_TEXT = SDR_TEXT || ' (AC-YRMFG :' || ' ' ||

(SELECT Y.YEAR_OF_MANUFCTURE_NBR FROM Y01_AIRCRAFT Y WHERE Y.AIRCRAFT_ID = X.AIRCRAFT_ID)

|| ')'

WHERE SDR_TEXT NOT LIKE '%(AC-YRMFG :%'

AND NVL(AIRCRAFT_ID,0) > 0 ;

COMMIT;

--- APPEND FAA CONTROL NUMBER ONTO SDR_TEXT

UPDATE M70_SDR_TRANSIT

SET SDR_TEXT = SDR_TEXT || ' (FAA-NO:' || ' ' || SUBMITTER_CONTROL_NUM || ')'

WHERE SDR_TEXT NOT LIKE '%(FAA-NO:%' ;

commit;

Sample : The end of the SDR_text will look something like this