...
However, only approximately 7800 of 3739,000 registered aircraft are accounted for in this data link. In instances where NO OPRULE is provided for a specific tail-mark, the last known OPRULE remains in effect on that specific aircraft in CAWIS. In instances where an OPRULE is never provided, it stays at 999 – Unknown.
...
SELECT Y01.AIRCRAFT_ID AS AIRCRAFT_ID,
TRIM(Y02.AIRCRAFT_MARK_ID) || ' ' AS MARK,
TR26.YEAR_CALENDAR_NBR AS REPORTING_YEAR,
TO_CHAR(Y01.DATE_CURRENT_DTE,'YYYY-MM-DD') AS ENTRY_DATE,
DECODE(TRIM(Y93.MARK),NULL,'N','Y') || ' ' AS ACTIVE_IND,
REPLACE(
DECODE(Y93.SALE_REPORTED,'Y','SR',NULL) ||
DECODE(Y93.ACTIVE_FLAG ,'I','IA',NULL),
'SRIA','SR')
|| ' ' AS SRIA ,
Y01.YEAR_OF_MANUFCTURE_NBR || ' ' AS YEAR_MFG,
Y02.YEAR_OF_IMPORT_NBR || ' ' AS YEAR_IMPORT,
Q01.TYPE_OF_AUTHORITY_CD || ' ' AS FLIGHT_AUTHORITY,
Y02.AIRCRAFT_PURPOSE_CD || ' ' AS PURPOSE,
Y02.AIRCRAFT_LICENSE_TYPE_CD || ' ' AS OPERATING_RULE,
Y03.WEIGHT_NBR || ' ' AS MAX_TAKEOFF_LBS,
GET_MANUFACTURER_NAME(Y04.MANUFACTURER_ID) AS AIRCRAFT_MFG,
Y04.MODEL_ID AS AIRCRAFT_MODEL,
Y62.GROUP_ID || ' ' AS GROUP_ID,
Y04.SERIAL_ID AS SERIAL_NUMBER,
Y62.TATC_CD || ' ' AS TATC_CD,
Y50C.COUNTRY_CD || ' ' AS TCH_COUNTRY,
Y50.PARTY_TYPE_CD || ' ' AS OWNER_TYPE,
REPLACE( REPLACE ( REPLACE (
TRIM(Y50.NOTES_TXT),CHR(9),' '),CHR(10),' '),CHR(13),' ')
|| ' ' AS OWNER,
Y50.CLIENT_ID || ' ' AS CLIENT_ID,
Y02.TC_REGION_CD || ' ' AS REGION,
Y02.TCC_OFFICE_FILE_LOCATION_CD AS TC_CENTRE,
DECODE(M01.DATE_AAIR_RECEIPT_DTE,NULL,'N','Y') AS RECD_IND,
( SELECT ROUND(X.HOURS_FLOWN_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.FLYING_HOURS_TYPE_CD = 1
AND X.YEAR_CALENDAR_NBR = TR26.YEAR_CALENDAR_NBR ) || ' ' AS TIME_SINCE_NEW,
( SELECT ROUND(X.HOURS_FLOWN_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.FLYING_HOURS_TYPE_CD = 2
AND X.YEAR_CALENDAR_NBR = TR26.YEAR_CALENDAR_NBR ) || ' ' AS YEARLY_HOURS,
( SELECT ROUND(X.HOURS_FLOWN_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.FLYING_HOURS_TYPE_CD = 3
AND X.YEAR_CALENDAR_NBR = TR26.YEAR_CALENDAR_NBR ) || ' ' AS TRAINING_HOURS,
( SELECT ROUND(X.HOURS_FLOWN_NBR) FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.FLYING_HOURS_TYPE_CD = 4
AND X.YEAR_CALENDAR_NBR = TR26.YEAR_CALENDAR_NBR ) || ' ' AS OTHER_HOURS
FROM Y01_AIRCRAFT Y01 ,
Y02_AIRCRAFT_HISTORY Y02 ,
Y03_AIRCRFT_TKFF_WGHT Y03 ,
Y04_AIRCRAFT_CONFIGURATION Y04 ,
Y93_CCARCS_PREVIOUS_DAY Y93 ,
Y53_PARTY_LIST Y53 ,
Y50_PARTY Y50 ,
Q01_AIRCRFT_FLGHT_ATHTY Q01 ,
M01_AIRCRAFT_AAIR_REPORT M01 ,
TR26_REPORTING_YEAR TR26,
Y62_PRODUCT_MAKE_MODEL Y62 ,
Y60_MANUFACTURER Y60 ,
Y50_PARTY Y50C
WHERE Y02.AIRCRAFT_ID = Y01.AIRCRAFT_ID
and Y93.AIRCRAFT_ID (+) = Y01.AIRCRAFT_ID
--- JOIN Y01 MAIN, Y02 HISTORY, AND Y93 CCARCS SNAPSHOT
...
AND Y62.PRODUCT_TYPE_CD = 'A'
AND Y62.MANUFACTURER_ID = Y04.MANUFACTURER_ID
AND Y62.MODEL_ID = Y04.MODEL_ID
-- GET GROUP ID & TATC_CD FROM Y62 MODEL TABLE TO ALLOW FOR BROADER AIRCRAFT TYPE SUMMATIONS
AND TR26Y60.YEAR_CALENDAR_MANUFACTURER_ID = Y62.MANUFACTURER_ID
AND Y50C.PARTY_ID = Y60.PARTY_ID
--- GET TCH COUNTRY FROM Y50
AND TR26.YEAR_CALENDAR_NBR BETWEEN 2010 AND (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 1)
-- MINIMUM REPORTING YEAR IS 1994 (VERY INCOMPLETE)
-- MAXIMUM REPORTING YEAR IS CURRENT YEAR - 1 (SHOULD BE CONSIDERED INCOMPLETE UNTIL 31-DEC)
...
and Q01.AIRCRAFT_ID = Y01.AIRCRAFT_ID
and Q01.ORDINAL_POSITION_NBR = 1 ;
-- FLIGHT AUTHORITY -- STAYS SAME EVERY YEAR, NOT RECORDED HISTORICALLY
*/
DATA DESCRIPTIONS
AIRCRAFT_ID | UNIQUE NUMERIC IDENTIFIER FOR THIS AIRCRAFT ON THE CANADIAN REGISTER ALLOCATED BY CCARCS (Canadian Civil Aircraft Register) |
MARK | AIRCRAFT TAIL MARK DURING THIS REPORTING YEAR - BEGINS WITH 'F' OR 'G' ( 'I' is not included ultralights do not report flight hours ) An aircraft may change its tail mark many times over its lifetime |
REPORTING_YEAR | REPORTING YEAR - 2010 THRU CURRENT YEAR - 1. THIS REPORT ONLY INCLUDES AIRCRAFT FOR WHICH A REQUEST TO REPORT WAS ISSUED BY TC FOR REPORTING OF THAT CALENDAR YEAR - THE CURRENT "REPORTING YEAR" IS ALWAYS = CURRENT YEAR - 1 . DATA FOR THE CURRENT REPORTING YEAR IS NOT CONSIDERED COMPLETE UNTIL 31-DECEMBER OF THE FOLLOWING YEAR (aka THE COLLECTION YEAR) |
ENTRY_DATE | ENTRY-DATE - INDICATES THE FIRST APPEARANCE OF THIS AIRCRAFT IN CAWIS (A DEFAULT DATE OF 1994 WAS USED FOR MUCH OF THE LEGACY DATA TAKEN FROM THE OLD MAINFRAME APP, CAIS) |
ACTIVE_IND | ACTIVE INDICATOR - IS THIS AIRCRAFT ON THE CURRENT REGISTER ? |
SRIA | SALE REPORTED - OR - INVALID ADDRESS - THESE AIRCRAFT/OPERATORS ARE UNCONTACTABLE - THERE EXISTS AN INDICATION THAT AAIR WAS REQUESTED BUT NONE WAS RECEIVED. MANY SALE REPORTED AIRCRAFT HAVE BEEN DESTROYED, AND OWNERSHIP WAS TAKEN OVER BY INSURANCE COMPANIES, WHO DO NOT IDENTIFY THEMSELVES TO TC. MANY OTHER OF THESE AIRCRAFT APPEAR ON FOREIGN REGISTRIES. |
YEAR_MFG | YEAR OF MANUFACTURE - NOTE - AN AIRCRAFT LISTED HAVING BEEN CONSTRUCTED DURING THE CURRENT YEAR WILL NOT BE ISSUED A REQUEST FOR FLIGHT HOURS |
YEAR_IMPORT | YEAR OF IMPORT - NOTE - AN AIRCRAFT LISTED HAVING BEEN IMPORTED DURING THE CURRENT YEAR WILL NOT BE ISSUED A REQUEST FOR FLIGHT HOURS, DESPITE THE POSSIBLITY IT MAY HAVE BEEN ON THE REGISTER UNDER OTHER OWNERSHIP DURING THE PREVIOUS YEAR |
FLIGHT_AUTHORITY | FLIGHT_AUTHORITY |
PURPOSE | AIRCRAFT_PURPOSE DURING THE GIVEN REPORTING YEAR CODE DESCR |
OPERATING_RULE | PRIMARY OPERATING RULE DURING THE REPORTING YEAR (ONLY THE NUMERICALLY HIGHEST OP RULE IS SAVED TO THE DATABASE) CODE DESCR |
MAX_TAKEOFF_LBS | MAXIMUM TAKE OFF WEIGHT POUNDS |
AIRCRAFT_MFG | AIRCRAFT MANUFACTURER |
AIRCRAFT_MODEL | AIRCRAFT MODEL - AN 'X' SUFFIX INDICATES AN OWNER-MAINTAINED VERSION OF THIS TYPE OF MODEL |
GROUP_ID | AIRCRAFT GROUP CODE FOR BII TOTALLING PURPOSES |
SERIAL_NUMBER | AIRCRAFT SERIAL NUMBER |
TATC_CD | TYPE_APPROVAL - OR - TYPE_CERTIFICATE NUMBER FOR THIS MANUFACTURER AND MODEL |
TCH_COUNTRY | TYPE_CERTIFICATE_HOLDER COUNTRY CODE COUNTRY |
OWNER_TYPE | 1-INDIVIDUAL, 2-ORGANIZATION |
OWNER | LAST OWNER OF THE AIRCRAFT DURING THE REPORTING YEAR ( MANY MISSING VALUES ARE FROM CAIS/CAWIS CONVERSION AS HISTORICAL CCARCS OWNERSHIP DATA WAS NOT AVAILABLE ) |
CLIENT_ID | OWNER CLIENT_ID AS ASSIGN BY CCARCS |
REGION | TC_REGION CODE DESCR |
TC_CENTRE | TC_FILE_LOCATION CODE DESCR REGN PROV |
RECD_IND | WAS A REPORT RECEIVED FOR THIS REPORTING YEAR ? ( THERE IS 1 RECORD ON THIS TABLE FOR EVERY AIRCRAFT THAT WAS REQUIRED TO REPORT DURING THIS REPORTING YEAR - RESPONSE RATES WOULD BE CALCULATED AS [(TOTAL RECORDS FOR YEAR - SRIA - CURRENT YEAR BUILT ) / RECD=Y] |
TIME_SINCE_NEW | TIME SINCE NEW FOR THAT REPORTING YEAR ( NOT NECESSARILY CUMULATIVE, ENTRY ERRORS WERE MADE FROM YEAR TO YEAR) |
YEARLY_HOURS_FLOWN | TOTAL HOURS FLOWN BY THIS AIRCRAFT DURING THIS REPORTING YEAR |
TRAINING_HOURS | TRAINING HOURS DURING THIS REPORTING YEAR |
OTHER_HOURS | OTHER TYPE HOURS DURING THIS REPORTING YEAR |