...
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
...
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 )) ||
...
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 ) – only fields required for report
/* data fields described here :
*/
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 ---
...
enter desired extract month
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 month's report to Eric Lanthier
REQUIREMENTS FOR TASK
...