Non mandatory - the Carrier code is the Owner/Operator of the aircraft listed in the SDR at that point in time. We check for integrity and possibly Add new CARRIER codes to TR91, if necessary.
The latest FAA CARRIER CODE list is here
http://av-info.faa.gov/data/AirOperators/fix/airopera.txt
Though the FAA does not always keep this list up to date (ie: you’ll find carrier codes in the data that are not on this list – FAA SDR management is aware of the issue)
Sample (columns 1-4 = CARRIER_CD, columns 5-55 = OWNER/OPERATOR/CARRIER NAME
If we encounter a CARRIER code we dont have, we can possibly grab it from this list, and add it into CAWIS - manually updating the CARRIER code table
CAWIS MAIN MENU -> CODES -> SDR CODES -> CARRIER
If the new carrier code is not on the FAA list, we normally just null it out.
We do not upload the entirety of the FAA carrier code list to TR91_carrier as they tend to pollute their data with Private-Individual operator names, and repetitive companies that have never been involved with an SDR.
*/
--- If null, can we possibly acquire a CARRIER CODE for this aircraft from another report in this batch?
SELECT
' UPDATE M70_SDR_TRANSIT SET ' ||
' CARRIER_CD = ''' || M70B.CARRIER_CD ||
''' WHERE SDR_NUMBER_NUM = ''' || M70.SDR_NUMBER_NUM ||
''' AND NVL(CARRIER_CD,'' '') = '' '';' AS UPD1
FROM M70_SDR_TRANSIT M70,
M70_SDR_TRANSIT M70B
WHERE NVL(M70.CARRIER_CD,' ') = ' '
AND M70B.AIRCRAFT_MARK_ID = M70.AIRCRAFT_MARK_ID
AND NVL(M70B.CARRIER_CD,' ') <> ' ' ;
--- Lookup or null out Bad carrier code
SELECT DISTINCT(M70.CARRIER_CD) , M70.AIRCRAFT_MARK_ID,
CHR(13) || ' UPDATE M70_SDR_TRANSIT SET CARRIER_CD = null WHERE CARRIER_CD = '''
|| M70.CARRIER_CD || ''';' UPD1
FROM M70_SDR_TRANSIT M70
WHERE TRIM(M70.CARRIER_CD) NOT IN (SELECT TR91.CARRIER_CD FROM TR91_CARRIER TR91)
AND M70.CARRIER_CD IS NOT NULL ;
0 Comments