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),' ') = ' ' ;
/*
Add Comment