STEP 14 AIRCRAFT MANUFACTURER/MODEL

OPTIONAL - if we do NOT have an AIRCRAFT MANUFACTURER/MODEL, but we DO have a tail mark, go to the FAA registry website and acquire the airframe info.

This query normally returns only 1 or 2 rows.

If the aircraft is amateur built, ignore.

If its a Canadian TCH airframe, its more important

Alternately, if the registry page shows an aircraft that left the US before the SDR occurence date, its a good idea to null out the tail mark

*/

SELECT SDR_NUMBER_NUM,

       AIRCRAFT_MARK_ID AS XXX,

       AIRCRAFT_MANUFACTURER,

       AIRCRAFT_MODEL,

CHR(13) || 'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER_ID = ?, ' ||

'AIRCRAFT_MODEL = ''XXXX'',AIRCRAFT_SERIAL_NUMBER = ''XXXX'' WHERE SDR_NUMBER_NUM = ''' ||

SDR_NUMBER_NUM || ''';' UPD1,  --- update with aircraft info

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

SDR_NUMBER_NUM || ''';' UPD2   --- or . . null out aircraft tail mark

FROM   M70_SDR_TRANSIT

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

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