/* AIRCRAFT AAIR REPORT */
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 ,
...
REPLACE(
(DECODE(Y93.SALE_REPORTED,'Y','SR','') ||
DECODE(Y93.ACTIVE_FLAG, 'I','IA','')),'SRIA','SR') || ' ' AS SRIA,
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')
...
SUBSTR(Y95.ENGINE_MANUFACTURER,1,1)
AS ACATGRY ,
|| ' ' 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
...
AS PURPOSE,
REPLACE(Y02.AIRCRAFT_LICENSE_
...
TYPE_CD ,'
...
999','
...
'
...
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,
...
) AS POR,
Q01.TYPE_OF_AUTHORITY_CD 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 ,
/* A FEW OWNERS HAVE CARRIAGE RETURNS IN THEIR NAMES */
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 ,
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 ,
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' ) || ' ' 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 ,
(SELECT TR08.EXOS_ETXT FROM TR08_EXOS TR08
WHERE TR08.EXOS_CD = Y02.EXOS_CD) || ' ' AS OOS_REASON,
...
REPLACE( TO_CHAR(M01.LAST_MOD_TIMESTAMP_DTE,'YYYY-MM-DD') || '
...
(' ||
...
M01.LAST_
...
MOD_BY_USER_ID || ')' ,'()',
...
' ')
...
WITHIN GROUP (ORDER BY X.ENTRY_DATE DESC )
FROM SDRS_SERVICE_DIFFICULTY X
...
|| ' '
AS AAIR_LAST_MOD ,
TO_CHAR(SYSDATE,'YYYY') AS EDM ,
DECODE(
( SELECT COUNT(*) FROM AA003_NOTIFICATION_ATTEMPT AA003
WHERE AA003.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,
...
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 ,
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
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,
...
/* 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
...
Y93.SALE_REPORTED,'Y',NULL, REPLACE(A92E.ADDRESS_PROPERTY_TXT,'AIRCRAFTREGHQ@TC.GC.CA', TRIM(Y93.OWNER_EMAIL) ) ) || ' ' AS EMAIL ,
/* ALTERNATE, DELETED, SDR SYSTEM EMAILS */
DECODE(Y93.SALE_REPORTED,'Y',NULL,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 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,
----------------------------------------------------------------------------------------------------
...
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)
|| ' ' ||
(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,' ' ) ) || ' ' 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,
...
---
...
...
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,
REPLACE(REPLACE(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)
...
)
...
...
|| ' '
...
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')) -
...
2 || '00'
...
...
)
...
...
,'
...
',' '),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
...
|| ' '
AS LAST_TC_INSPECT ,
----
...
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
...
)
...
|| ' '
...
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'
...
)
...
|| ' '
...
...
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
...
)
...
|| ' '
...
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
' ' 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
(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')
...
...
AND Y53.AIRCRAFT_PARTY_LIST_
...
ID = Y01.AIRCRAFT_ID
...
AND Y53.DATE_START_DTE =
...
NVL(
(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)
...
...
,Y53.DATE_START_DTE)
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
...
