STEP 27 CARRIER CODES

Sept 2021 : NOTE - all required code for this process is found at :

simply copy and place into your preferred SQL tool

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 ;