Transport Canada
Continuing Airworthiness Web Information System (CAWIS)
Monthly Annual Airworthiness Information Report (AAIR)
Data Extract Procedure
Kurt Martin - 2021-08-03
######################
Overview
A data extract provided to the Aircraft Certification publications section on or about the first business day of each month.
Step 1
go to
\\ncrfs345\AARD\AARD Public\CAWIS-REPORTS\AAIR-REPORTS\02-MONTHLY-REVISIONS
Step 2
Take a copy of last month’s report , AAIR-202106-JUNE.xlsx, for example to your C:\ drive
Rename to AAIR-202107-JULY.xlsx (note, we’re always extracting data for the previous month just ended)
Purge the data from the spreadsheet
Step 3
Execute this code
SELECT Y02.AIRCRAFT_MARK_ID MRK ,
Y02.TC_REGION_CD || ' - ' || TR02.TC_REGION_ETXT || ' '
REGION ,
DECODE(
(SELECT COUNT(Y93.AIRCRAFT_ID) FROM Y93_CCARCS_PREVIOUS_DAY Y93
WHERE Y93.AIRCRAFT_ID = Y02.AIRCRAFT_ID)
,1,'Y','N') REGD ,
TO_NUMBER(TO_CHAR(Y05.DATE_START_DTE,'YYYY') ) - 1 RY,
TO_CHAR(M01.DATE_AAIR_RECEIPT_DTE,'YYYY-MM-DD') AAIR_RECD,
GET_PARTY_INFO(Y50.PARTY_ID) OWNER,
(SELECT GET_MANUFACTURER_NAME(Y04.MANUFACTURER_ID) || ' ' ||
Y04.MODEL_ID || ' (' || Y04.SERIAL_ID || ')'
FROM Y04_AIRCRAFT_CONFIGURATION Y04
WHERE Y04.AIRCRAFT_ID = Y05.AIRCRAFT_ID
AND Y04.PRODUCT_TYPE_CD = 'A')
AS AIRCRAFT_INFO,
Y05.FIELD_ID FID,
REPLACE(REPLACE(REPLACE(REPLACE(
Z983.FIELD_TITLE_ETXT,'PROPELLER','PROP'),'AIRCRAFT ',''),'DATE',''),' ID','')
FIELDNAME,
Y05.VALUE_OLD_TXT || ' ' OLD_DATA,
UPPER(Y05.VALUE_NEW_TXT) || ' ' NEW_DATA,
Y05.LAST_MOD_BY_USER_ID || ' ' LMODBY ,
TO_CHAR(Y05.DATE_START_DTE,'YYYY-MM-DD') || ' ' LMOD ,
TRIM(Y02.AIRCRAFT_MARK_ID) || ' - ' ||
REPLACE(REPLACE(REPLACE(
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' ||
'(' || SUBSTR(TRIM(GET_PARTY_INFO(X.RUN_ID)),1,40) || ') ' ||
SUBSTR(TRIM(X.TC_AAIR_NOTES_TXT),1,1600)
FROM M01_AIRCRAFT_AAIR_REPORT X
WHERE X.AIRCRAFT_ID = Y02.AIRCRAFT_ID
AND X.YEAR_CALENDAR_NBR =
(SELECT MAX(Y.YEAR_CALENDAR_NBR) FROM M01_AIRCRAFT_AAIR_REPORT Y
WHERE TRIM(Y.TC_AAIR_NOTES_TXT) IS NOT NULL
AND Y.AIRCRAFT_ID = Y02.AIRCRAFT_ID )) ||
(SELECT ' * (' || X.YEAR_CALENDAR_NBR || ') ' ||
'(' || SUBSTR(TRIM(GET_PARTY_INFO(X.RUN_ID)),1,40) || ') ' ||
SUBSTR(TRIM(X.TC_AAIR_NOTES_TXT),1,400)
FROM M01_AIRCRAFT_AAIR_REPORT X
WHERE X.AIRCRAFT_ID = Y02.AIRCRAFT_ID
AND X.YEAR_CALENDAR_NBR =
(SELECT MAX(Y.YEAR_CALENDAR_NBR) FROM M01_AIRCRAFT_AAIR_REPORT Y
WHERE TRIM(Y.TC_AAIR_NOTES_TXT) IS NOT NULL
AND Y.AIRCRAFT_ID = Y02.AIRCRAFT_ID
AND Y.YEAR_CALENDAR_NBR <
(SELECT MAX(Z.YEAR_CALENDAR_NBR) FROM M01_AIRCRAFT_AAIR_REPORT Z
WHERE TRIM(Z.TC_AAIR_NOTES_TXT) IS NOT NULL
AND Z.AIRCRAFT_ID = Y02.AIRCRAFT_ID )
) )
,CHR(10),' '),CHR(13), ' '),CHR(9),' ') || ' '
AS AAIRNOTES
FROM Y05_AIRCRAFT_AAIR_UPDATES Y05 ,
Z983_ADHOC_FIELDS Z983 ,
Y02_AIRCRAFT_HISTORY Y02 ,
TR02_REGION TR02 ,
M01_AIRCRAFT_AAIR_REPORT M01 ,
Y53_PARTY_LIST Y53 ,
Y50_PARTY Y50
WHERE Z983.FIELD_ID = Y05.FIELD_ID
---AND Z983.FIELD_ID IN (16,19,230,90,106,122,138,239,98,114,130,146 )
AND Y05.AIRCRAFT_ID NOT IN (SELECT Q01.AIRCRAFT_ID
FROM Q01_AIRCRFT_FLGHT_ATHTY Q01
WHERE Q01.TYPE_OF_AUTHORITY_CD IN ('H','I','F'))
AND TO_NUMBER(TO_CHAR(Y05.DATE_START_DTE,'YYYYMM') ) = 202107 --- JUN-2021
AND Y02.AIRCRAFT_ID = Y05.AIRCRAFT_ID
AND Y02.RUN_ID = 99
AND TR02.TC_REGION_CD = Y02.TC_REGION_CD
AND Y53.AIRCRAFT_PARTY_LIST_ID = Y05.AIRCRAFT_ID
AND Y53.DATE_START_DTE = (SELECT MAX(Y53B.DATE_START_DTE) FROM Y53_PARTY_LIST Y53B
WHERE Y53B.AIRCRAFT_PARTY_LIST_ID = Y05.AIRCRAFT_ID
AND Y53B.PARTY_ID <> 9999999 )
AND Y50.PARTY_ID = Y53.PARTY_ID
AND M01.AIRCRAFT_ID (+) = Y05.AIRCRAFT_ID
AND M01.YEAR_CALENDAR_NBR (+) = TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 1 ;
Step 4 –
Import to the new spreadsheet, save a copy to the archive (where you got the original xlsx) and email this months report to Eric Lanthier
REQUIREMENTS FOR TASK
1 TOAD or SQL Developer installed on the submitter’s machine ( with connect strings to CAWIS PROD )
2 access to the the server location \\ncrfs345\AARD\AARD Public\CAWIS-REPORTS
Add Comment