STEP 13 INCOMPLETE MANUFACTURER/MODEL DATA
- kurt.martin
Sept 2021 : NOTE - all required code for this process is found at :
simply copy and place into your preferred SQL tool
In order to later insert records into the M73_SDR_PRODUCT_MAKE_MODEL table, the minimum requirement is :
PRODUCT_TYPE_CD - (A-AIRCRAFT, E-ENGINE, P-PROPELLER, Q-EQUIPMENT)
MANUFACTURER_ID
MODEL_ID
Unfortunately, the FAA frequently posts incomplete configuration data.
Examples :
Records having a Manufacturer, but no model or
Records having a model, but no manufacturer or
Records having serial-number, but no manufacturer/model
Auditing of the SDR data directly on the FAA website, shows that the data is missing on their end as well, so it`s not a data transfer error. FAA SDR management has been informed of the issue.
In this step, we determine how many records have this problem. Its normally only less that 5 records per batch.
For airframe and engine data issues, the FAA registry will give us the needed info, if the SDR has a tail mark, or a serial number to work with.
Also, if a model is provided but NOT the manufacturer, we can easily determine the Manufacturer by looking up the model on the tables at
CAWIS MAIN MENU - CODE TABLES - MAKE-MODEL - QUERY
INSTANCES OF MODEL WITH NO MANUFACTURER
EITHER DETERMINE THE MFG FROM THE FAA WEBSITE by tail mark/serial number lookup
OR –
BY LOOKING AT THE MODEL, then the CAWIS make model tables
OR -
NULL OUT THE MODEL AND APPEND IT TO SDR_TEXT
*/
--- AIRCRAFT
SELECT SDR_NUMBER_NUM, AIRCRAFT_MARK_ID, AIRCRAFT_MANUFACTURER,
AIRCRAFT_MODEL, AIRCRAFT_SERIAL_NUMBER,
' UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER = ''XXX''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD1 , --- MFG CAN BE DETERMINED
' UPDATE M70_SDR_TRANSIT SET SDR_TEXT_TXT = SDR_TEXT_TXT || ( AC-MODEL: ''' ||
AIRCRAFT_MODEL || ' - NO MFG PROVIDED ) '' ; ' ||
' UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = NULL' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD2 --- MFG CANNOT BE DETERMINED
FROM M70_SDR_TRANSIT
WHERE TRIM(AIRCRAFT_MANUFACTURER) IS NULL
AND TRIM(AIRCRAFT_MODEL) IS NOT NULL ;
--- ENGINE
SELECT SDR_NUMBER_NUM, AIRCRAFT_MARK_ID, ENGINE_MANUFACTURER,
ENGINE_MODEL, ENGINE_SERIAL_NUMBER,
' UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER = ''XXX''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD1 , --- MFG CAN BE DETERMINED
' UPDATE M70_SDR_TRANSIT SET SDR_TEXT_TXT = SDR_TEXT_TXT || ( ENG-MODEL: ''' ||
ENGINE_MODEL || ' - NO MFG PROVIDED ) '' ; ' ||
' UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = NULL' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD2 --- MFG CANNOT BE DETERMINED
FROM M70_SDR_TRANSIT
WHERE TRIM(ENGINE_MANUFACTURER) IS NULL
AND TRIM(ENGINE_MODEL) IS NOT NULL ;
--- PROPELLER
SELECT SDR_NUMBER_NUM, AIRCRAFT_MARK_ID, PROPELLER_MANUFACTURER,
PROPELLER_MODEL, PROPELLER_SERIAL_NUMBER,
' UPDATE M70_SDR_TRANSIT SET PROPELLER_MANUFACTURER = ''XXX''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD1 , --- MFG CAN BE DETERMINED
' UPDATE M70_SDR_TRANSIT SET SDR_TEXT_TXT = SDR_TEXT_TXT || ( PROP-MODEL: ''' ||
PROPELLER_MODEL || ' - NO MFG PROVIDED ) '' ; ' ||
' UPDATE M70_SDR_TRANSIT SET PROPELLER_MODEL = NULL' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD2 --- MFG CANNOT BE DETERMINED
FROM M70_SDR_TRANSIT
WHERE TRIM(PROPELLER_MANUFACTURER) IS NULL
AND TRIM(PROPELLER_MODEL) IS NOT NULL ;
--- EQUIPMENT/COMPONENT
--- "MISC EQUIPMENT" (manufacturer_id = 1) IS ALWAYS THE DEFAULT MANUFACTURER NAME
UPDATE M70_SDR_TRANSIT
SET COMPONENT_MANUFACTURER = 'MISC EQUIPMENT'
WHERE TRIM(COMPONENT_MANUFACTURER) IS NULL
AND TRIM(COMPONENT_MODEL) IS NOT NULL ;
COMMIT;
----
/*
INSTANCES OF MANUFACTURER WITH NO MODEL
EITHER DETERMINE THE MODEL FROM THE FAA WEBSITE by tail mark/serial number lookup
OR
NULL OUT THE MODEL AND APPEND IT TO SDR_TEXT
Note: when entering a new Engine-model, never include the word "SERIES"
*/
--- AIRCRAFT
SELECT SDR_NUMBER_NUM, AIRCRAFT_MARK_ID, AIRCRAFT_MANUFACTURER,
AIRCRAFT_MODEL, AIRCRAFT_SERIAL_NUMBER,
'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MODEL = ''XXX''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD1 , --- MDL CAN BE DETERMINED
' UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SDR_TEXT || '' ( AC-MFG: ' ||
AIRCRAFT_MANUFACTURER || ' - NO MODEL PROVIDED) ''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' ||
'UPDATE M70_SDR_TRANSIT SET AIRCRAFT_MANUFACTURER = NULL' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD2 --- MDL CANNOT BE DETERMINED
FROM M70_SDR_TRANSIT
WHERE TRIM(AIRCRAFT_MANUFACTURER) IS NOT NULL
AND TRIM(AIRCRAFT_MODEL) IS NULL ;
--- ENGINE
SELECT SDR_NUMBER_NUM, AIRCRAFT_MARK_ID, AIRCRAFT_MODEL, ENGINE_MANUFACTURER,
ENGINE_MODEL, ENGINE_SERIAL_NUMBER,
' UPDATE M70_SDR_TRANSIT SET ENGINE_MODEL = ''XXX''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD1 , --- MDL CAN BE DETERMINED
' UPDATE M70_SDR_TRANSIT SET SDR_TEXT = SDR_TEXT || '' ( ENG-MFG: ' ||
ENGINE_MANUFACTURER || ' - NO MODEL PROVIDED ) ''' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' ||
' UPDATE M70_SDR_TRANSIT SET ENGINE_MANUFACTURER = NULL' ||
' WHERE SDR_NUMBER_NUM = ''' || SDR_NUMBER_NUM || ''';' UPD2 --- MDL CANNOT BE DETERMINED
FROM M70_SDR_TRANSIT
WHERE TRIM(ENGINE_MANUFACTURER) IS NOT NULL
AND TRIM(ENGINE_MODEL) IS NULL ;
/*
--- PROPELLER - propeller data cannot be acquired from the FAA registry and we cannot allocate a model simply by looking at the manufacturer. Here we simply use the default model "PROPELLER"
ALL manufacturers on the Y62 table that have propeller models, also have this default model, "PROPELLER". This is auto-maintained.
*/
UPDATE M70_SDR_TRANSIT
SET PROPELLER_MODEL = 'PROPELLER'
WHERE TRIM(PROPELLER_MANUFACTURER) IS NOT NULL
AND TRIM(PROPELLER_MODEL) IS NULL ;
COMMIT;
--- COMPONENT -- "EQUIPMENT" IS ALWAYS THE DEFAULT MODEL
UPDATE M70_SDR_TRANSIT
SET COMPONENT_MODEL = 'EQUIPMENT'
WHERE TRIM(COMPONENT_MANUFACTURER) IS NOT NULL
AND TRIM(COMPONENT_MODEL) IS NULL ;
COMMIT;
--- RECORD HAS SERIAL NUMBER DATA, BUT NO MFG/MODEL DATA - WE SIMPLY NULL THESE
UPDATE M70_SDR_TRANSIT
SET AIRCRAFT_SERIAL_NUMBER = NULL
WHERE TRIM(AIRCRAFT_SERIAL_NUMBER) IS NOT NULL
AND TRIM(AIRCRAFT_MODEL) IS NULL ;
UPDATE M70_SDR_TRANSIT
SET ENGINE_SERIAL_NUMBER = NULL
WHERE TRIM(ENGINE_SERIAL_NUMBER) IS NOT NULL
AND TRIM(ENGINE_MODEL) IS NULL ;
UPDATE M70_SDR_TRANSIT
SET PROPELLER_SERIAL_NUMBER = NULL
WHERE TRIM(PROPELLER_SERIAL_NUMBER) IS NOT NULL
AND TRIM(PROPELLER_MODEL) IS NULL ;
COMMIT ;
/*