...
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 )) ||
...
,CHR(10),' '),CHR(13), ' '),CHR(9),' ') || ' '
AS AAIRNOTES
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 ) – the only fields required requested for this report
/* data fields described here :
...