/* AIRCRAFT AAIR REPORT */
SELECT
...
.
...
.
...
.
...
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 AS PURPOSE,
REPLACE(Y02.AIRCRAFT_LICENSE_TYPE_CD ,'999',' ') 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 ,
REPLACE( TO_CHAR(M01.LAST_MOD_TIMESTAMP_DTE,'YYYY-MM-DD') || ' (' ||
M01.LAST_MOD_BY_USER_ID || ')' ,'()', ' ') || ' '
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 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 ,
...
'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 ,
...
/* EMAIL*/
DECODE(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 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 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 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')
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)
...
Go to attachment AAIRWEEKLY.TXT
--- END
Sample output
...
