...
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 ,
/* is the aircraft still on the current register ? */
TO_NUMBER(TO_CHAR(Y05.DATE_START_DTE,'YYYY') ) - 1 RY,
/* the date_start_dte on y05 indicates the reporting year - we always look for current year - 1 */
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') 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,
...
,CHR(10),' '),CHR(13), ' '),CHR(9),' ') || ' '
AS AAIRNOTES
/* most recent AAIR notes from client */
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 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
/* You need to specifiy the desired Year/Month */
AND Y02.AIRCRAFT_ID = Y05.AIRCRAFT_ID
AND Y02.RUN_ID = 99
...
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 ;
/* Current-reporting-year is ALWAYS (current-year - 1) */
...
Step 4 –
Export the data to a text file. Import that text file to the new spreadsheet, save a copy to the archive (where you got the original xlsx) and email this month's report to Eric Lanthier .
...