STEP 15 AIRCRAFT SERIAL NUMBER

OPTIONAL - if we have a tail mark, and airframe data, BUT no aircraft serial number retrieve it from the FAA register, or null out that tail mark. Serial numbers are important to help us associate the reports to previously Canadian-registered aircraft by establishing a possible Aircraft_id. (unique numeric identifier for a specific aircraft, provided by CCARCS)

*/

--- ACQUIRE MISSING AIRCRAFT SERIAL BY TAIL-MARK/MODEL MATCH

--- TO ANOTHER REPORT IN THIS BATCH ??

SELECT

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = ''' || M70B.AIRCRAFT_SERIAL_NUMBER ||

''' WHERE SDR_NUMBER_NUM = ''' || M70.SDR_NUMBER_NUM || ''' ; ' AS UPD1

FROM   M70_SDR_TRANSIT M70,

       M70_SDR_TRANSIT M70B

WHERE  M70.AIRCRAFT_SERIAL_NUMBER IS NULL

AND    M70.AIRCRAFT_MARK_ID       IS NOT NULL

AND    M70B.AIRCRAFT_MARK_ID       = M70.AIRCRAFT_MARK_ID

AND    M70B.AIRCRAFT_MODEL         = M70.AIRCRAFT_MODEL

AND    M70B.AIRCRAFT_SERIAL_NUMBER IS NOT NULL;

--- RETRIEVE AIRCRAFT-SERIAL FROM FAA WEB SITE ??

SELECT SDR_NUMBER_NUM,

       AIRCRAFT_MARK_ID AS MRK,

       AIRCRAFT_MANUFACTURER,

       AIRCRAFT_MODEL,

CHR(13) ||

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_SERIAL_NUMBER = ''XXXX'' WHERE AIRCRAFT_MARK_ID = ''' ||

AIRCRAFT_MARK_ID || ''';' as UPD1 ,

'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MARK_ID = NULL WHERE SDR_NUMBER_NUM = ''' ||

SDR_NUMBER_NUM || ''';' AS UPD2

FROM   M70_SDR_TRANSIT

WHERE  NVL(TRIM(AIRCRAFT_MARK_ID      ),' ') <> ' '

AND    NVL(TRIM(AIRCRAFT_MANUFACTURER ),' ') <> ' '

AND    NVL(TRIM(AIRCRAFT_SERIAL_NUMBER),' ')  = ' '  ;

/*