CAWIS needs to provide a VIEW to BII (or any other requesting application) so that inquiries can be directed to one source which allows for a reckoning of all known aircraft, all flight hours for all available years and the associated anaylsis codes for any desired breakdowns.
Data notes : flight hours data has been collected historically since 1994 (though 1994 itself, is very incomplete)
The Operating Rule data (found on Y02) is terribly incomplete/inaccurate. Unfortunately this is the data element most frequently requested for breakdowns. Any conclusions drawn from this data should be considered highly suspect. (all requestors have been notified)
OPRULES
CODE DESC
---- --------------------
406 FLIGHT TRAINING UNIT
604 PRIVATE-OP PASSENGER
701 FOREIGN AIR OPS
702 AERIAL WORK
703 AIR TAXI OPS
704 COMMUTER OPS
705 AIRLINE OPS
999 UNDEFINED
OPRULE (aka, Operating Rule/License Type/CAR) is acquired from NAPA. Unfortunately, the data is NOT completely reliable as is presented. It’s simply 2 columns - Tail_mark, and Operating_rule - reviewed & kept up to date, each night in our CCARCS/CAWIS synchronization routines.
However, only approximately 7800 of 37,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.
So, an aircraft may operate over a given year, under multiple Operating rules, for differing periods of time, but NAPA only provides to CAWIS, the numerically “highest” operating rule found for that aircraft.
So if an aircraft flew under 705 & 704 in a given year - CAWIS would only record it as having flown under 705.
There exists a long standing request (from AVSTATS) that flight hours be broken down by operating rule when the operators submit their AAIR, though no action has ever been taken. (requires a change to the regulation, the printed form, the AAIR code etc)
The Public AAIR data view should look something like this :
/*
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,
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
WHERE Y02.AIRCRAFT_ID = Y01.AIRCRAFT_ID
and Y93.AIRCRAFT_ID (+) = Y01.AIRCRAFT_ID
--- JOIN Y01 MAIN, Y02 HISTORY, AND Y93 CCARCS SNAPSHOT
AND Y02.DATE_START_DTE =
NVL(
(SELECT MAX(X.DATE_START_DTE) FROM Y02_AIRCRAFT_HISTORY X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND TO_NUMBER(TO_CHAR(X.DATE_START_DTE,'YYYY')) <= TR26.YEAR_CALENDAR_NBR),
(SELECT MAX(X.DATE_START_DTE) FROM Y02_AIRCRAFT_HISTORY X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ))
-- WE WANT THE HISTORY RECORD THAT IS AS CLOSE AS POSSIBLE TO THE REPORTING YEAR IN QUESTION
AND Y53.AIRCRAFT_PARTY_LIST_ID = Y01.AIRCRAFT_ID
AND Y53.DATE_START_DTE =
NVL(
(SELECT MIN(X.DATE_START_DTE) FROM Y53_PARTY_LIST X
WHERE X.AIRCRAFT_PARTY_LIST_ID = Y01.AIRCRAFT_ID
AND X.PARTY_ID <> 9999999
AND X.DATE_START_DTE
BETWEEN TO_DATE(DECODE(X.PARTY_ID, 9999999,TR26.YEAR_CALENDAR_NBR - 1, TR26.YEAR_CALENDAR_NBR)
|| '0101','YYYYMMDD')
AND SYSDATE ), (SELECT MAX(X.DATE_START_DTE) FROM Y53_PARTY_LIST X
WHERE X.AIRCRAFT_PARTY_LIST_ID = Y01.AIRCRAFT_ID
AND X.PARTY_ID <> 9999999))
-- WE WANT THE OWNER OF THE AIRCRAFT DURING THAT REPORTING YEAR
and Y50.PARTY_ID = Y53.PARTY_ID
-- GET OWNER NAME AND TYPE
AND Y03.AIRCRAFT_ID = Y01.AIRCRAFT_ID
and Y03.WEIGHT_TYPE_CD = 3
-- GET MAX TAKEOFF WEIGHT
AND Y04.AIRCRAFT_ID = Y01.AIRCRAFT_ID
and Y04.PRODUCT_TYPE_CD = 'A'
-- GET AIRCRAFT MFG/MODEL/SERIAL CONFIGURATION
AND Y62.PRODUCT_TYPE_CD = 'A'
AND Y62.MANUFACTURER_ID = Y04.MANUFACTURER_ID
AND Y62.MODEL_ID = Y04.MODEL_ID
-- GET GROUP ID FROM Y62 MODEL TABLE TO ALLOW FOR BROADER AIRCRAFT TYPE SUMMATIONS
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 M01.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND M01.YEAR_CALENDAR_NBR = TR26.YEAR_CALENDAR_NBR
-- THIS GIVES US ALL AIRCRAFT ASKED TO REPORT DURING ANY GIVEN REPORTING YEAR
and Q01.AIRCRAFT_ID = Y01.AIRCRAFT_ID
and Q01.ORDINAL_POSITION_NBR = 1 ;
-- FLIGHT AUTHORITY -- STAYS SAME EVERY YEAR, NOT RECORDED HISTORICALLY
*/
Add Comment