1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41
| CREATE OR REPLACE VIEW
"VW_SNAG_FT_AC"
("SNAG_NUMBER",
"ATA") AS ( SELECT
-- SNAG_NUMBER
SN_SNAG_ISSUE.SNAG_NUMBER as SNAG_NUMBER,
-- ATA_CHAPTER
(SELECT ATA_CHAPTER.ATA_CHAPTER
FROM SN_SNAG_ISSUE, ATA_CHAPTER
WHERE SN_SNAG_ISSUE.ATA_CHAPTER_LAST_ID=ATA_CHAPTER.ID
AND SN_SNAG_ISSUE.SNAG_NUMBER =SNAG_NUMBER) as ATA_CHAPTER
FROM
SN_SNAG_ISSUE,
FT_FLIGHT,
AC_AIRCRAFT,
AC_TYPE,
AC_PROGRAM,
ATA_CHAPTER
WHERE
-- Miltary programs are not selected
SN_SNAG_ISSUE.FT_FLIGHT_ID=FT_FLIGHT.ID
AND FT_FLIGHT.AC_AIRCRAFT_ID=AC_AIRCRAFT.ID
AND AC_AIRCRAFT.AC_TYPE_ID=AC_TYPE.ID
AND AC_TYPE.AC_PROGRAM_ID=AC_PROGRAM.ID
AND AC_PROGRAM.AC_PROGRAM != 'M'
-- Snags where last Status for department 'EVT' is 'DELETED' are nor selected
AND
(SELECT SN_SNAG_STATUS.SNAG_STATUS_TITLE
FROM SN_SNAG_ISSUE_STATUS, SN_SNAG_STATUS, SN_SNAG_ISSUE, SN_DEPARTMENT
WHERE SN_SNAG_ISSUE_STATUS.SN_SNAG_STATUS_ID=SN_SNAG_STATUS.ID
AND SN_SNAG_ISSUE_STATUS.SN_SNAG_ISSUE_ID=SN_SNAG_ISSUE.ID
AND SN_SNAG_ISSUE_STATUS.SN_DEPARTMENT_ID=SN_DEPARTMENT.ID
AND SN_DEPARTMENT.DEPARTMENT='EVT'
AND SN_SNAG_ISSUE_STATUS.SNAG_STATUS_DATE = (SELECT max(SN_SNAG_ISSUE_STATUS.SNAG_STATUS_DATE)
FROM SN_SNAG_ISSUE_STATUS, SN_SNAG_STATUS, SN_SNAG_ISSUE, SN_DEPARTMENT
WHERE SN_SNAG_ISSUE_STATUS.SN_SNAG_STATUS_ID=SN_SNAG_STATUS.ID
AND SN_SNAG_ISSUE_STATUS.SN_SNAG_ISSUE_ID=SN_SNAG_ISSUE.ID
AND SN_SNAG_ISSUE_STATUS.SN_DEPARTMENT_ID=SN_DEPARTMENT.ID
AND SN_DEPARTMENT.DEPARTMENT='EVT')) != 'DELETED'
); |
Partager