SELECT
TRIM(Y02.AIRCRAFT_MARK_ID) as MRK,
DECODE(Y01.YEAR_OF_MANUFCTURE_NBR,TO_NUMBER(TO_CHAR(sysdate,'YYYY')),'Y',' ')
as CYM,
DECODE(Y02.YEAR_OF_IMPORT_NBR ,TO_NUMBER(TO_CHAR(sysdate,'YYYY')),'Y',' ')
as CYI,
DECODE(M01.DATE_AAIR_RECEIPT_DTE , NULL, ' ','Y')
as REP,
DECODE(Y02.FLEET_ID , NULL, ' ','Y')
as FR,
TO_CHAR(Y01.YEAR_OF_MANUFCTURE_NBR) as YRMFG ,
RPAD(TO_CHAR(NVL(Y02.YEAR_OF_IMPORT_NBR,0)),4,'0') AS YRIMP ,
TO_CHAR(Y01.DATE_CURRENT_DTE ,'YYYY-MM-DD') || ' ' AS CAWIS_ENTRY,
TO_CHAR(Y02.DATE_C_OF_R_ISSUE_DTE ,'YYYY-MM-DD') || ' ' AS C_OF_R ,
TO_CHAR(Q01.DATE_C_OF_A_ISSUE_DTE ,'YYYY-MM-DD') || ' ' AS C_OF_A ,
TO_CHAR(Y53.DATE_START_DTE ,'YYYY-MM-DD') AS OWNER_SINCE,
SUBSTR(Y95.ENGINE_MANUFACTURER,1,1)
AS ACATGRY ,
Y02.AIRCRAFT_PURPOSE_CD
-- DECODE(Y02.AIRCRAFT_PURPOSE_CD,'1','1-PRIV','2','2-COMM','3','3-GOVT','9','9-MIL','4-OTHR')
AS PURPOSE,
REPLACE(Y02.AIRCRAFT_LICENSE_TYPE_CD ,'999',' ')
-- || ' - ' || REPLACE( SUBSTR(TR09.AIRCRAFT_LICENSE_TYPE_ETXT,1,20) ,'UNDEFINED',' ')
AS POR,
Q01.TYPE_OF_AUTHORITY_CD
--- || ' - ' || DATA_COMPRESSION(TR12.TYPE_OF_AUTHORITY_ETXT)
AS CFA,
Y93.FLIGHT_AUTHORITY || ' '
AS CC_AUTH,
TRIM(Y95.ENGINE_MODEL)
|| ' ' AS WEIGHT,
-----
'DUE-' ||
REPLACE( (Y02.DATE_AAIR_DUE_ALT_DTE ||
(SELECT Y59.DATE_AAIR_DUE_DTE FROM Y59_FLEET Y59 WHERE Y59.FLEET_ID = Y02.FLEET_ID)) ,'/','-') || ' '
AS DUE,
-----
Y50.PARTY_TYPE_CD || ' ' AS OWNER_TYPE,
-----
DATA_CHAR_REPLACE(replace(replace(replace(TRIM(
(TRIM(Y93.LEGAL_NAME ) || ' ' ||
TRIM(Y93.TRADE_NAME ) || ' ' ||
TRIM(Y93.FAMILY_NAME) || ' ' ||
TRIM(Y93.FIRST_NAME ) || ' ' ||
TRIM(Y93.INITIALS) ) ) ,
CHR(9),' '), CHR(10),' ') , CHR(13),' ') ) || ' '
as OWNER ,
---
TRIM(REPLACE(
Y02.FLEET_ID || ' ' || DECODE(Y02.AIRCRAFT_FRAME_CYCLES_NBR,1,'X',' ‘)
/* 'X’ INDICATES FLEET PROBLEM */
|| (SELECT ' - ' || SUBSTR(XX.NOTES_TXT,1,45)
FROM Y59_FLEET YY, Y50_PARTY XX
WHERE YY.FLEET_ID = Y02.FLEET_ID
AND XX.PARTY_ID = YY.PARTY_ID),' ',' ')) || ' '
as FLEET ,
---
REPLACE(
(DECODE(Y93.SALE_REPORTED,'Y','SR','') ||
DECODE(Y93.ACTIVE_FLAG, 'I','IA','')),'SRIA','SR') || ' ' AS SRIA,
TO_CHAR(Y02.OUT_OF_SERVICE_START_DTE,'YYYY-MM-DD') || ' ' AS OOS_START,
TO_CHAR(Y02.DATE_EXPECTED_RETURN_DTE,'YYYY-MM-DD') || ' ' AS OOS_END ,
(SELECT TR08.EXOS_ETXT FROM TR08_EXOS TR08
WHERE TR08.EXOS_CD = Y02.EXOS_CD) || ' ' AS OOS_REASON,
( SELECT LISTAGG ( '(' || TO_CHAR(X.ENTRY_DATE, 'YYYY-MM-DD') || ' THRU ' ||
TO_CHAR(X.LAST_UPDATE,'YYYY-MM-DD') || ')' , ' ' )
WITHIN GROUP (ORDER BY X.ENTRY_DATE DESC )
FROM SDRS_SERVICE_DIFFICULTY X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.ENTRY_DATE <> NVL(Y02.OUT_OF_SERVICE_START_DTE,SYSDATE)
AND X.CARRIER_CODE = 'EXOS'
GROUP BY X.AIRCRAFT_ID )
|| ' ' AS PREVIOUS_OUT_OF_SERVICE,
Y50.LANGUAGE_CD AS LANG,
DECODE(Y50.PREFER_NOTIFICATION_METHOD_CD,'1','EMAIL','2','FAX','MAIL') AS PREFNM,
TO_CHAR(M01.DATE_REPORT_ISSUE_DTE,'YYYY-MM-DD') || ' ' AS CURR_RPT_ISSD ,
REPLACE(
'(' || NVL(M01.YEAR_CALENDAR_NBR,0) || ') '
, '(0)',' ')
|| TO_CHAR(M01.DATE_AAIR_RECEIPT_DTE,'YYYY-MM-DD') || ' '
AS CURR_RPT_RECD ,
-----
REPLACE(
TO_CHAR(M01.LAST_MOD_TIMESTAMP_DTE,'YYYY-MM-DD') || ' (' ||
M01.LAST_MOD_BY_USER_ID
|| ')' ,'()', ' ') || ' '
AS AAIR_LAST_MOD ,
----------
'2021' AS EDM ,
----------
DECODE(
( SELECT COUNT(*) FROM AA003_NOTIFICATION_ATTEMPT AA003
WHERE AA003.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND AA003.PROCESS_YEAR_MONTH_NUM BETWEEN 202100 AND 202104 ),0,'NO','YES')
AS EDM_ATTEMPT ,
DECODE(
( SELECT MAX(AA003.NOTIFICATION_METHOD_CD) FROM AA003_NOTIFICATION_ATTEMPT AA003
where AA003.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND AA003.PROCESS_YEAR_MONTH_NUM BETWEEN 202100 AND 202104
and AA003.ATTEMPT_NUM = (select max(B.ATTEMPT_NUM) from AA003_NOTIFICATION_ATTEMPT B
WHERE B.PROCESS_YEAR_MONTH_NUM BETWEEN 202100 AND 202104
AND B.AIRCRAFT_ID = Y01.AIRCRAFT_ID)
AND AA003.NOTIFICATION_STATUS_CD IN (2,9)),'1','EMAIL','2','FAX','3','MAIL',' ' )
AS SENTBY ,
DECODE(
( SELECT COUNT(*) FROM AA003_NOTIFICATION_ATTEMPT AA003
where AA003.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND AA003.PROCESS_YEAR_MONTH_NUM BETWEEN 202100 AND 202104
AND AA003.NOTIFICATION_METHOD_CD = '1'
AND AA003.NOTIFICATION_STATUS_CD IN (2)),1,'SUCCESS','2','SUCCESS','3','SUCCESS',' ')
AS SENT_BY_EMAIL,
DECODE(
( select COUNT(*) from Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'FTP2021'
AND Z980.FROM_TXT = TRIM(Y02.AIRCRAFT_MARK_ID)),1,'YES',' ') AS SENT_BY_MAIL,
---
'202105' AS OVERDUE_EDM ,
DECODE(
( SELECT COUNT(*) FROM AA003_NOTIFICATION_ATTEMPT AA003
where AA003.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND AA003.PROCESS_YEAR_MONTH_NUM BETWEEN 202105 AND 202112 ),0,'NO','YES')
as OVD_EDM_ATTEMPT ,
DECODE(
( SELECT MAX(AA003.NOTIFICATION_METHOD_CD) FROM AA003_NOTIFICATION_ATTEMPT AA003
where AA003.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND AA003.PROCESS_YEAR_MONTH_NUM BETWEEN 202105 AND 202112
and AA003.ATTEMPT_NUM = (select max(B.ATTEMPT_NUM) from AA003_NOTIFICATION_ATTEMPT B
WHERE B.PROCESS_YEAR_MONTH_NUM = '202105'
AND B.AIRCRAFT_ID = Y01.AIRCRAFT_ID)
AND AA003.NOTIFICATION_STATUS_CD IN (2,9)),'1','EMAIL','2','FAX','3','MAIL',' ' )
AS OVD_SENTBY ,
DECODE(
( SELECT COUNT(*) FROM AA003_NOTIFICATION_ATTEMPT AA003
WHERE AA003.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND AA003.PROCESS_YEAR_MONTH_NUM BETWEEN 202105 AND 202112
AND AA003.NOTIFICATION_METHOD_CD = '1'
and AA003.NOTIFICATION_STATUS_CD in (2)),'1','SUCCESS','2','SUCCESS','3','SUCCESS',' ')
as OVD_SENT_EMAIL,
----------------------------------------------------------------------------------------------------
DECODE(Y93.SALE_REPORTED,'Y',NULL,
REPLACE(A92E.ADDRESS_PROPERTY_TXT,'AIRCRAFTREGHQ@TC.GC.CA', TRIM(Y93.OWNER_EMAIL) ) )
|| ' ' AS EMAIL ,
/* SUPRESS EMAILS FOR SALE REPORTED AIRCRAFT */
----------------------------------------------------------------------------------------------------
--- ALTERNATE, DELETED, SDR SYSTEM EMAILS
DECODE(Y93.SALE_REPORTED,'Y',NULL,
REPLACE ( -- REPLACE (
TRIM (
( SELECT LISTAGG ( REPLACE(TRIM(GET_USER_EMAIL_ADDRESS(M79.SUBMITTER_USER_ID)),
A92E.ADDRESS_PROPERTY_TXT,' ') , ' ' )
WITHIN GROUP (ORDER BY M79.SUBMITTER_SEQUENCE_NBR DESC )
FROM M79_SDR_SUBMITTER M79 , Y50_PARTY YY ,Y53_PARTY_LIST XX
WHERE XX.AIRCRAFT_PARTY_LIST_ID = Y01.AIRCRAFT_ID
AND XX.DATE_STOP_DTE IS NULL
AND YY.PARTY_ID = XX.PARTY_ID
AND YY.RUN_ID > 0
AND M79.SUBMITTER_ORGANIZATION_ID = YY.RUN_ID
AND GET_USER_EMAIL_ADDRESS(M79.SUBMITTER_USER_ID)
NOT IN (SELECT TRIM(TO_TXT) FROM Z980_TERM_CONVERSION
WHERE TERM_TYPE_CD = 'OLDEMAIL')
AND M79.SUBMITTER_SEQUENCE_NBR <= 3
GROUP BY M79.SUBMITTER_ORGANIZATION_ID)
|| ' ' ||
(SELECT REPLACE(Z980.TO_TXT,A92E.ADDRESS_PROPERTY_TXT,' ')
FROM Z980_TERM_CONVERSION Z980
WHERE Z980.TERM_TYPE_CD = 'OLDEMAIL'
AND Z980.FROM_TXT = Y93.CLIENT_ID ) )
, A92E.ADDRESS_PROPERTY_TXT,' ' ) )
-- , TRIM(Y93.OWNER_EMAIL), ' ' )
|| ' ' AS ALT_EMAILS,
----------------------------------------------------------------------------------------------------
DECODE(Y93.SALE_REPORTED,'Y',NULL,
REPLACE(A92F.ADDRESS_PROPERTY_TXT,'000-000-0000',' ') ) || ' ' AS FAX ,
----
DECODE(
Y93.MULTIPLE_OWNER_FLAG,'Y','YES','NO') || ' ' AS MULT_OWNERS,
Y01.COUNT_ENGINES_NBR || ' ' AS ENGINES,
( TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - NVL(Y01.YEAR_OF_MANUFCTURE_NBR,TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))) )
AS AIRCRAFT_AGE,
-----
Y95.ENGINE_SERIAL_NUMBER
|| ' ' AS TSN,
-----
(SELECT COUNT(*) FROM Y93_CCARCS_PREVIOUS_DAY X
WHERE X.SALE_REPORTED = 'N' AND X.CLIENT_ID = Y93.CLIENT_ID)
AS CLIENT_AIRCRAFT_KOUNT,
---
Y02.TC_REGION_CD || ' - ' || Y95.AIRCRAFT_MODEL || ' '
AS TC_REGN,
Y02.TCC_OFFICE_FILE_LOCATION_CD || ' - ' || Y95.AIRCRAFT_SERIAL_NUMBER || ' '
AS TCC,
---
(SELECT TR21.COUNTRY_ETXT FROM TR21_COUNTRY TR21
WHERE TR21.COUNTRY_CD = REPLACE(A90.COUNTRY_CD,'CF',' ') )
|| ' ' AS CNTY,
DATA_CHAR_REPLACE(NVL(
TRIM(TRIM(Y93.CITY) || ' ' || Y93.PROVINCE_OR_STATE),
(TRIM(A90.CITY_TXT) || ' ' || A90.PROVINCE_CD )
)) || ' '
AS CITYPROV,
----
GET_MANUFACTURER_NAME(Y04.MANUFACTURER_ID) || ' ' AS AIRCRAFT_MFG,
Y04.MODEL_ID || ' ' AS AIRCRAFT_MODEL,
Y04.SERIAL_ID || ' ' AS AIRCRAFT_SERIAL ,
----
TRIM(Y02.AIRCRAFT_MARK_ID) || ' - ' ||
'A' || Y01.AIRCRAFT_ID AS AIRCRAFT_ID,
'C' || TRIM(Y93.CLIENT_ID) AS CLIENT_ID,
'P' || Y50.PARTY_ID AS OWNER_PARTY_ID ,
'A' || Y90.ADDRESS_ID AS ADDRESS_ID ,
NVL(
DECODE( Y50.ACCESS_CODE, NULL ,
REPLACE( REPLACE(AAIR_ACCESS_CODE(Y50.CLIENT_ID, Y50.PARTY_ID , NULL ),
' TTTTT','') ,'999999', '') , '' )
|| DECODE(Y50.ACCESS_CODE,NULL,'','CHANGED'),'SR-IA') || ' '
AS ACCESS_CODE ,
-------
( SELECT LISTAGG ( X.AIRCRAFT_MARK_ID , ',' )
WITHIN GROUP (ORDER BY X.DATE_START_DTE DESC )
FROM Y02_AIRCRAFT_HISTORY X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.AIRCRAFT_MARK_ID <> Y02.AIRCRAFT_MARK_ID
AND X.DATE_START_DTE =
(SELECT MAX(Y.DATE_START_DTE) FROM Y02_AIRCRAFT_HISTORY Y
WHERE Y.AIRCRAFT_ID = X.AIRCRAFT_ID
AND Y.AIRCRAFT_MARK_ID = X.AIRCRAFT_MARK_ID)
GROUP BY X.AIRCRAFT_ID )
|| ' ' AS PREV_TAIL_MARKS,
-------
REPLACE( REPLACE ( REPLACE (
( SELECT LISTAGG ('(' ||
TO_CHAR(X.DATE_START_DTE,'YYYY-MM-DD') || ' THRU ' ||
TO_CHAR(X.DATE_STOP_DTE ,'YYYY-MM-DD') || ' - ' || GET_PARTY_INFO (X.PARTY_ID)
|| ')' ,' ' )
WITHIN GROUP (ORDER BY X.DATE_START_DTE DESC )
FROM Y53_PARTY_LIST X
WHERE X.AIRCRAFT_PARTY_LIST_ID = Y01.AIRCRAFT_ID
AND X.DATE_STOP_DTE IS NOT NULL
GROUP BY X.AIRCRAFT_PARTY_LIST_ID )
,CHR(10),' '),CHR(13), ' '),CHR(9),' ') || ' '
AS PREVIOUS_OWNERS,
-------
REPLACE(REPLACE(REPLACE(REPLACE(
( SELECT LISTAGG ( '(' || TO_CHAR(X.DATE_CREATED_DTE,'YYYY-MM-DD') || ':' ||
-- REPLACE(REPLACE(
REPLACE(
UPPER(TRIM(X.NOTIFICATION_TARGET_LBL))
,TRIM(Y93.OWNER_EMAIL),'EMAIL')
-- ,A92E.ADDRESS_PROPERTY_TXT,' ')
|| ')' , ' ' )
WITHIN GROUP (ORDER BY X.PROCESS_YEAR_MONTH_NUM DESC , X.ATTEMPT_NUM )
FROM AA003_NOTIFICATION_ATTEMPT X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.PROCESS_YEAR_MONTH_NUM >= TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 3 || '00'
GROUP BY X.AIRCRAFT_ID )
,' ',' '),CHR(9),''),CHR(10),''),CHR(13),'') || ' '
AS EDM_MESSAGES,
-------
(SELECT COUNT(*) FROM C07_AIRCRAFT_INSPECTION X WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' '
AS TOT_INSPECT ,
(SELECT TO_CHAR(X.DATE_INSPECT_DTE,'YYYY-MM-DD')
FROM C07_AIRCRAFT_INSPECTION X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.INSPECTION_TYPE_CD = 1
AND X.DATE_INSPECT_DTE = ( SELECT MAX(Y.DATE_INSPECT_DTE)
FROM C07_AIRCRAFT_INSPECTION Y WHERE Y.AIRCRAFT_ID = X.AIRCRAFT_ID AND Y.INSPECTION_TYPE_CD = 1 ) )
|| ' '
AS LAST_TC_INSPECT ,
----
( SELECT LISTAGG ('(' || TO_CHAR(X.DATE_INSPECT_DTE,'YYYY-MM-DD')
|| ' - ' || INSPECTOR_NAME_OTHER_NM || ' ' ||
X.INSPECTOR_AME_LICENSE_NUMBER_I || X.INSPECTOR_AMO_LICENSE_NUMBER_I || ') ',' ' )
WITHIN GROUP (ORDER BY X.DATE_INSPECT_DTE DESC )
FROM C07_AIRCRAFT_INSPECTION X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.INSPECTION_TYPE_CD = 2
GROUP BY X.AIRCRAFT_ID )
|| ' ' AS INSPECTIONS,
----
( SELECT LISTAGG ( '(DMG:' || TO_CHAR(X.ENTRY_DATE,'YYYY-MM-DD') || ' REP:' ||
TO_CHAR(X.LAST_UPDATE,'YYYY-MM-DD') || ')' , ' ' )
WITHIN GROUP (ORDER BY X.ENTRY_DATE DESC )
FROM SDRS_SERVICE_DIFFICULTY X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.CARRIER_CODE = 'DMGD'
GROUP BY X.AIRCRAFT_ID )
|| ' ' AS DAMAGED,
----
(SELECT COUNT(*) FROM M01_AIRCRAFT_AAIR_REPORT X WHERE X.DATE_AAIR_RECEIPT_DTE IS NULL
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' '
AS TOT_OVRD ,
----
( SELECT LISTAGG ('(' || X.YEAR_CALENDAR_NBR
|| '-' ||
DECODE(NVL(TO_CHAR(X.DATE_AAIR_RECEIPT_DTE,'YYYY-MM-DD'),'XX'),'XX','N','Y' )
|| ')',' ' )
WITHIN GROUP (ORDER BY X.YEAR_CALENDAR_NBR DESC)
FROM M01_AIRCRAFT_AAIR_REPORT X
WHERE X.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND X.YEAR_CALENDAR_NBR <= TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 2
-- AND X.YEAR_CALENDAR_NBR > TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 11
GROUP BY X.AIRCRAFT_ID )
|| ' ' AS AAIR_RECD_HISTORY ,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 1)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR01,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 2)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR02,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 3)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR03,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 4)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR04,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 5)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR05,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 6)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR06,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 7)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR07,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 8)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR08,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 9)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR09,
-------
(SELECT '(' || X.YEAR_CALENDAR_NBR || ') ' || X.HOURS_FLOWN_NBR
FROM Q02_AIRCRFT_LCNS_TYP_HR X
WHERE X.FLYING_HOURS_TYPE_CD = '2'
AND X.YEAR_CALENDAR_NBR = (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 10)
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' ' AS YR10,
-------
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 = Y01.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 = Y01.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 = Y01.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 = Y01.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 = Y01.AIRCRAFT_ID )
) )
,CHR(10),' '),CHR(13), ' '),CHR(9),' ') || ' '
AS AAIRNOTES ,
-------
TRIM(Y02.AIRCRAFT_MARK_ID) AS MRKK,
-------
REPLACE (
(SELECT
GET_MANUFACTURER_NAME(X.MANUFACTURER_ID) || ' , ' ||
X.MODEL_ID || ' (' || X.SERIAL_ID || ')'
FROM Y04_AIRCRAFT_CONFIGURATION X
WHERE X.PRODUCT_TYPE_CD = 'E' AND X.ORDINAL_POSITION_NBR = 1
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ),'()','') || ' '
AS ENG ,
-------
REPLACE (
(SELECT
GET_MANUFACTURER_NAME(X.MANUFACTURER_ID) || ' , ' ||
X.MODEL_ID || ' (' || X.SERIAL_ID || ')'
FROM Y04_AIRCRAFT_CONFIGURATION X
WHERE X.PRODUCT_TYPE_CD = 'P' AND X.ORDINAL_POSITION_NBR = 1
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ),'()','') || ' '
AS PROP ,
-------
(SELECT
GET_MANUFACTURER_NAME(X.MANUFACTURER_ID) || ' , ' || X.MODEL_ID
FROM Y04_AIRCRAFT_CONFIGURATION X
WHERE X.PRODUCT_TYPE_CD = 'F' AND X.ORDINAL_POSITION_NBR = 1
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' '
AS FLOATS ,
-------
(SELECT
GET_MANUFACTURER_NAME(X.MANUFACTURER_ID) || ' , ' || X.MODEL_ID
FROM Y04_AIRCRAFT_CONFIGURATION X
WHERE X.PRODUCT_TYPE_CD = 'S' AND X.ORDINAL_POSITION_NBR = 1
AND X.AIRCRAFT_ID = Y01.AIRCRAFT_ID ) || ' '
AS SKI ,
-------
TO_CHAR(SYSDATE,'YYYY-MM-DD') AS RUNDATE
FROM Y93_CCARCS_PREVIOUS_DAY Y93,
Y01_AIRCRAFT Y01,
Y02_AIRCRAFT_HISTORY Y02,
Q01_AIRCRFT_FLGHT_ATHTY Q01,
Y50_PARTY Y50,
Y53_PARTY_LIST Y53,
A90_ADDRESS A90,
A92_ADDRESS_PROPERTY A92E,
A92_ADDRESS_PROPERTY A92F,
Y90_PARTY_CONTACT Y90 ,
Y04_AIRCRAFT_CONFIGURATION Y04 ,
M01_AIRCRAFT_AAIR_REPORT M01 ,
Y95_SDRS_CCARCS_ENGINE Y95
WHERE Y01.AIRCRAFT_ID = Y93.AIRCRAFT_ID
and Y02.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND Y02.RUN_ID = 99
AND Y95.SDR_NUMBER = Y01.AIRCRAFT_ID
AND M01.AIRCRAFT_ID (+) = Y01.AIRCRAFT_ID
AND M01.YEAR_CALENDAR_NBR (+) = TO_NUMBER(TO_CHAR(SYSDATE,'YYYY')) - 1
AND Q01.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND Q01.ORDINAL_POSITION_NBR = 1
AND Q01.TYPE_OF_AUTHORITY_CD NOT IN ('H','I') -- ULTRALIGHTS DO NOT REPORT
AND Y53.AIRCRAFT_PARTY_LIST_ID = Y01.AIRCRAFT_ID
AND Y53.DATE_START_DTE =
(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)
AND Y50.PARTY_ID = Y53.PARTY_ID
AND Y90.PARTY_ID = Y53.PARTY_ID
AND A90.ADDRESS_ID = Y90.ADDRESS_ID
AND A92E.ADDRESS_ID = Y90.ADDRESS_ID
AND A92E.ADDRESS_PROPERTY_TYPE_CD = 3 -- EMAIL
AND A92E.ORDINAL_NBR = 1
AND A92F.ADDRESS_ID = Y90.ADDRESS_ID
AND A92F.ADDRESS_PROPERTY_TYPE_CD = 2 -- FAX
AND A92F.ORDINAL_NBR = 1
AND Y04.AIRCRAFT_ID = Y01.AIRCRAFT_ID
AND Y04.PRODUCT_TYPE_CD = 'A' ;
--- END
Sample output
0 Comments