View Definition:
SELECT DISTINCT
DV.VEHICLE_ID
AS VEHICLE_ID,
DV.AGENCY_CODE
AS AGENCY_CODE,
(SELECT AC.AGENCY_DESC
FROM AGENCY_CODES AC
WHERE AC.AGENCY_CODE = DV.AGENCY_CODE)
AS AGENCY_DESC,
(SELECT PARENT_AGENCY_CODE
FROM AGENCY_PARENT_VW APV
WHERE APV.AGENCY_CODE = DV.AGENCY_CODE
UNION ALL
SELECT DV.AGENCY_CODE
FROM DUAL
WHERE NOT EXISTS
(SELECT *
FROM AGENCY_PARENT_VW APV
WHERE APV.AGENCY_CODE = DV.AGENCY_CODE))
AS PARENT_AGENCY_CODE,
(SELECT AC2.AGENCY_DESC
FROM AGENCY_CODES AC2
WHERE AC2.AGENCY_CODE =
(SELECT PARENT_AGENCY_CODE
FROM AGENCY_PARENT_VW APV
WHERE APV.AGENCY_CODE = DV.AGENCY_CODE
UNION ALL
SELECT DV.AGENCY_CODE
FROM DUAL
WHERE NOT EXISTS
(SELECT *
FROM AGENCY_PARENT_VW APV
WHERE APV.AGENCY_CODE = DV.AGENCY_CODE)))
AS PARENT_AGENCY_DESC,
DV.UNIT_NUMBER
AS UNIT_NUMBER,
DV.VEH_CATEGORY_CODE
AS VEH_CATEGORY_CODE,
DV.STATUS_CODE
AS STATUS_CODE,
DV.LICENSE_NUMBER
AS LICENSE_NUMBER,
DV.VIN
AS VIN,
DV.YEAR
AS YEAR,
MAKES.DESCRIPTION
AS VEHICLE_MAKE,
MODELS.DESCRIPTION
AS VEHICLE_MODEL,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE = DV.STATUS_CODE_TYPE
AND EC.CODE = DV.STATUS_CODE)
AS STATUS_DESC,
DV.ASSIGNMENT_CODE
AS ASSIGNED_LOC_CODE,
(SELECT EC.DESCRIPTION
FROM EJS_CODES EC
WHERE EC.CODE_TYPE = DV.ASSIGNMENT_CODE_TYPE
AND EC.CODE = DV.ASSIGNMENT_CODE)
AS ASSIGNED_LOC_DESC,
JOIN (
CURSOR (
SELECT MAX (DVAS1.ASSIGNMENT_ID)
FROM DV_ASSIGNMENTS DVAS1
WHERE DVAS1.VEHICLE_ID = DV.VEHICLE_ID
AND ( DVAS1.ASSIGN_END_DATE IS NULL
OR DVAS1.ASSIGN_END_DATE > SYSDATE)
UNION ALL
SELECT ASSIGNMENT_ID
FROM ( SELECT DVAS2.VEHICLE_ID,
MAX (DVAS2.ASSIGNMENT_ID) AS ASSIGNMENT_ID
FROM DV_ASSIGNMENTS DVAS2
JOIN DEPARTMENT_VEHICLES DV1
ON DVAS2.VEHICLE_ID = DV1.VEHICLE_ID
WHERE (SELECT COUNT (*)
FROM DV_ASSIGNMENTS DVAS3
WHERE DVAS3.VEHICLE_ID = DV1.VEHICLE_ID
AND ( DVAS3.ASSIGN_END_DATE IS NULL
OR DVAS3.ASSIGN_END_DATE > SYSDATE)) = 0
GROUP BY DVAS2.VEHICLE_ID)
WHERE VEHICLE_ID = DV.VEHICLE_ID),
', ')
AS VEHASSID,
JOIN (
CURSOR (
SELECT DVAS4.ASSIGN_END_DATE
FROM DV_ASSIGNMENTS DVAS4
JOIN DEPARTMENT_VEHICLES DV2
ON DVAS4.VEHICLE_ID = DV2.VEHICLE_ID
WHERE DVAS4.VEHICLE_ID = DV.VEHICLE_ID
AND ( DVAS4.ASSIGN_END_DATE IS NULL
OR DVAS4.ASSIGN_END_DATE > SYSDATE)
UNION ALL
SELECT ASSIGN_END_DATE
FROM ( SELECT DVAS5.VEHICLE_ID,
MAX (DVAS5.ASSIGN_END_DATE) AS ASSIGN_END_DATE
FROM DV_ASSIGNMENTS DVAS5
JOIN DEPARTMENT_VEHICLES DV3
ON DVAS5.VEHICLE_ID = DV3.VEHICLE_ID
WHERE (SELECT COUNT (*)
FROM DV_ASSIGNMENTS DVAS6
WHERE DVAS6.VEHICLE_ID = DV3.VEHICLE_ID
AND ( DVAS6.ASSIGN_END_DATE IS NULL
OR DVAS6.ASSIGN_END_DATE > SYSDATE)) = 0
GROUP BY DVAS5.VEHICLE_ID)
WHERE VEHICLE_ID = DV.VEHICLE_ID),
', ')
AS VEHASSEND,
JOIN (
CURSOR (
SELECT END_MILEAGE
FROM (SELECT DVAS7.VEHICLE_ID,
DVAS7.END_MILEAGE AS END_MILEAGE
FROM DV_ASSIGNMENTS DVAS7
JOIN DEPARTMENT_VEHICLES DV4
ON DVAS7.VEHICLE_ID = DV4.VEHICLE_ID
WHERE DVAS7.ASSIGNMENT_ID =
( SELECT MAX (DVAS8.ASSIGNMENT_ID)
FROM DV_ASSIGNMENTS DVAS8
WHERE DVAS8.VEHICLE_ID = DV4.VEHICLE_ID
GROUP BY DVAS8.VEHICLE_ID))
WHERE VEHICLE_ID = DV.VEHICLE_ID),
', ')
AS END_MILEAGE,
JOIN (
CURSOR (
SELECT CASE
WHEN OFFS.OFFICER_ID IS NOT NULL
THEN
OFFS.LNAME
WHEN UOFFS.OFFICER_ID IS NOT NULL
THEN
UOFFS.LNAME
WHEN EMPS.EJS_EMP_ID IS NOT NULL
THEN
EJS_ENCRYPTION.DECRYPT_AES256 (EMPS.ENC_LNAME)
WHEN UEMPS.EJS_EMP_ID IS NOT NULL
THEN
EJS_ENCRYPTION.DECRYPT_AES256 (UEMPS.ENC_LNAME)
END
FROM DV_ASSIGNMENTS DVAS8
INNER JOIN DV_OFFICERS DVOFFS
ON DVAS8.ASSIGNMENT_ID = DVOFFS.ASSIGNMENT_ID
LEFT OUTER JOIN OFFICERS OFFS
ON OFFS.OFFICER_ID = DVOFFS.OFFICER_ID
LEFT OUTER JOIN EMPLOYEES EMPS
ON DVOFFS.EMPLOYEE_ID = EMPS.EJS_EMP_ID
LEFT OUTER JOIN OFFICERS UOFFS
ON UOFFS.USER_ID = DVOFFS.LOGIN_ID
LEFT OUTER JOIN EMPLOYEES UEMPS
ON UEMPS.LOGIN_ID = DVOFFS.LOGIN_ID
WHERE DVAS8.VEHICLE_ID = DV.VEHICLE_ID
AND ( DVOFFS.ASSIGN_END_DATE IS NULL
OR DVOFFS.ASSIGN_END_DATE > SYSDATE)
UNION ALL
SELECT 'VEHICLE'
FROM DUAL
WHERE NOT EXISTS
(SELECT *
FROM DV_ASSIGNMENTS DVAS9
WHERE DVAS9.VEHICLE_ID = DV.VEHICLE_ID
AND DVAS9.ASSIGN_END_DATE IS NULL)),
', ')
AS LNAME,
JOIN (
CURSOR (
SELECT CASE
WHEN OFFS.OFFICER_ID IS NOT NULL
THEN
OFFS.FNAME
WHEN UOFFS.OFFICER_ID IS NOT NULL
THEN
UOFFS.FNAME
WHEN EMPS.EJS_EMP_ID IS NOT NULL
THEN
EJS_ENCRYPTION.DECRYPT_AES256 (EMPS.ENC_FNAME)
WHEN UEMPS.EJS_EMP_ID IS NOT NULL
THEN
EJS_ENCRYPTION.DECRYPT_AES256 (UEMPS.ENC_FNAME)
END
FROM DV_ASSIGNMENTS DVAS10
INNER JOIN DV_OFFICERS DVOFFS
ON DVAS10.ASSIGNMENT_ID = DVOFFS.ASSIGNMENT_ID
LEFT OUTER JOIN OFFICERS OFFS
ON OFFS.OFFICER_ID = DVOFFS.OFFICER_ID
LEFT OUTER JOIN EMPLOYEES EMPS
ON DVOFFS.EMPLOYEE_ID = EMPS.EJS_EMP_ID
LEFT OUTER JOIN OFFICERS UOFFS
ON UOFFS.USER_ID = DVOFFS.LOGIN_ID
LEFT OUTER JOIN EMPLOYEES UEMPS
ON UEMPS.LOGIN_ID = DVOFFS.LOGIN_ID
WHERE DVAS10.VEHICLE_ID = DV.VEHICLE_ID
AND ( DVOFFS.ASSIGN_END_DATE IS NULL
OR DVOFFS.ASSIGN_END_DATE > SYSDATE)
UNION ALL
SELECT 'UNASSIGNED'
FROM DUAL
WHERE NOT EXISTS
(SELECT *
FROM DV_ASSIGNMENTS DVAS11
WHERE DVAS11.VEHICLE_ID = DV.VEHICLE_ID
AND DVAS11.ASSIGN_END_DATE IS NULL)),
', ')
AS FNAME,
JOIN (
CURSOR (
SELECT CASE
WHEN OFFS.OFFICER_ID IS NOT NULL
THEN
OFFS.CAD_EMP_ID
WHEN UOFFS.OFFICER_ID IS NOT NULL
THEN
UOFFS.CAD_EMP_ID
WHEN EMPS.EJS_EMP_ID IS NOT NULL
THEN
EMPS.EMPLOYEE_ID
WHEN UEMPS.EJS_EMP_ID IS NOT NULL
THEN
UEMPS.EMPLOYEE_ID
END
FROM DV_ASSIGNMENTS DVAS12
INNER JOIN DV_OFFICERS DVOFFS
ON DVAS12.ASSIGNMENT_ID = DVOFFS.ASSIGNMENT_ID
LEFT OUTER JOIN OFFICERS OFFS
ON OFFS.OFFICER_ID = DVOFFS.OFFICER_ID
LEFT OUTER JOIN EMPLOYEES EMPS
ON DVOFFS.EMPLOYEE_ID = EMPS.EJS_EMP_ID
LEFT OUTER JOIN OFFICERS UOFFS
ON UOFFS.USER_ID = DVOFFS.LOGIN_ID
LEFT OUTER JOIN EMPLOYEES UEMPS
ON UEMPS.LOGIN_ID = DVOFFS.LOGIN_ID
WHERE DVAS12.VEHICLE_ID = DV.VEHICLE_ID
AND ( DVOFFS.ASSIGN_END_DATE IS NULL
OR DVOFFS.ASSIGN_END_DATE > SYSDATE)),
', ')
AS CAD_OR_EMP_ID,
JOIN (
CURSOR (
SELECT CASE
WHEN OFFS.OFFICER_ID IS NOT NULL
AND OFFS.TITLE IS NOT NULL
THEN
OFFS.TITLE
WHEN UOFFS.OFFICER_ID IS NOT NULL
AND UOFFS.TITLE IS NOT NULL
THEN
UOFFS.TITLE
WHEN EMPS.EJS_EMP_ID IS NOT NULL
AND EMPS.RANK IS NOT NULL
THEN
(SELECT R.RANK_TITLE_DESC
FROM EMP_RANK_TITLE_CODES R
WHERE R.CODE = EMPS.RANK)
WHEN UEMPS.EJS_EMP_ID IS NOT NULL
AND UEMPS.RANK IS NOT NULL
THEN
(SELECT R.RANK_TITLE_DESC
FROM EMP_RANK_TITLE_CODES R
WHERE R.CODE = UEMPS.RANK)
WHEN EMPS.EJS_EMP_ID IS NOT NULL
AND EMPS.EMPLOYEE_LEVEL_CODE IS NOT NULL
THEN
EMPS.EMPLOYEE_LEVEL_CODE
WHEN UEMPS.EJS_EMP_ID IS NOT NULL
AND UEMPS.EJS_EMP_ID IS NOT NULL
THEN
UEMPS.EMPLOYEE_LEVEL_CODE
END
FROM DV_ASSIGNMENTS DVAS13
INNER JOIN DV_OFFICERS DVOFFS
ON DVAS13.ASSIGNMENT_ID = DVOFFS.ASSIGNMENT_ID
LEFT OUTER JOIN OFFICERS OFFS
ON OFFS.OFFICER_ID = DVOFFS.OFFICER_ID
LEFT OUTER JOIN EMPLOYEES EMPS
ON DVOFFS.EMPLOYEE_ID = EMPS.EJS_EMP_ID
LEFT OUTER JOIN OFFICERS UOFFS
ON UOFFS.USER_ID = DVOFFS.LOGIN_ID
LEFT OUTER JOIN EMPLOYEES UEMPS
ON UEMPS.LOGIN_ID = DVOFFS.LOGIN_ID
WHERE DVAS13.VEHICLE_ID = DV.VEHICLE_ID
AND ( DVOFFS.ASSIGN_END_DATE IS NULL
OR DVOFFS.ASSIGN_END_DATE > SYSDATE)),
', ')
AS TITLE,
JOIN (
CURSOR (
SELECT DVFO.OFF_DUTY_MILES
FROM DV_FUEL_OIL DVFO
INNER JOIN DEPARTMENT_VEHICLES DV5
ON DVFO.VEHICLE_ID = DV5.VEHICLE_ID
LEFT OUTER JOIN DV_ASSIGNMENTS DVAS14
ON DVFO.VEHICLE_ID = DVAS14.VEHICLE_ID
WHERE DVFO.VEHICLE_ID = DV.VEHICLE_ID
AND DVFO.FUEL_OIL_ID =
(SELECT MAX (FO2.FUEL_OIL_ID)
FROM DV_FUEL_OIL FO2
WHERE FO2.VEHICLE_ID = DVFO.VEHICLE_ID)
AND DVAS14.ASSIGNMENT_ID =
(SELECT MAX (DVAS15.ASSIGNMENT_ID)
FROM DV_ASSIGNMENTS DVAS15
WHERE DVAS15.VEHICLE_ID = DVFO.VEHICLE_ID)
),
', ')
AS OFF_DUTY_MILES,
JOIN (
CURSOR (
SELECT DVFO1.SERVICE_INTERVAL_CODE
FROM DV_FUEL_OIL DVFO1
INNER JOIN DEPARTMENT_VEHICLES DV6
ON DVFO1.VEHICLE_ID = DV6.VEHICLE_ID
LEFT OUTER JOIN DV_ASSIGNMENTS DVASSG
ON DVFO1.VEHICLE_ID = DVASSG.VEHICLE_ID
WHERE DVFO1.VEHICLE_ID = DV.VEHICLE_ID
AND DVFO1.FUEL_OIL_ID =
(SELECT MAX (FO3.FUEL_OIL_ID)
FROM DV_FUEL_OIL FO3
WHERE FO3.VEHICLE_ID = DVFO1.VEHICLE_ID)
AND DVASSG.ASSIGNMENT_ID =
(SELECT MAX (DVA3.ASSIGNMENT_ID)
FROM DV_ASSIGNMENTS DVA3
WHERE DVA3.VEHICLE_ID = DV.VEHICLE_ID)
),
', ')
AS SERVICE_INTERVAL_CODE,
JOIN (
CURSOR (
SELECT DVFO2.DATE_TIME AS DATE_TIME
FROM DV_FUEL_OIL DVFO2
INNER JOIN DEPARTMENT_VEHICLES DV6
ON DVFO2.VEHICLE_ID = DV6.VEHICLE_ID
LEFT OUTER JOIN DV_ASSIGNMENTS DVASSG1
ON DVFO2.VEHICLE_ID = DVASSG1.VEHICLE_ID
WHERE DVFO2.VEHICLE_ID = DV.VEHICLE_ID
AND DVFO2.FUEL_OIL_ID =
(SELECT MAX (FO4.FUEL_OIL_ID)
FROM DV_FUEL_OIL FO4
WHERE FO4.VEHICLE_ID = DVFO2.VEHICLE_ID)
AND DVASSG1.ASSIGNMENT_ID =
(SELECT MAX (DVA4.ASSIGNMENT_ID)
FROM DV_ASSIGNMENTS DVA4
WHERE DVA4.VEHICLE_ID = DV.VEHICLE_ID)
),
', ')
AS DATE_TIME,
TO_CHAR (DV.PURCHASE_DATE, 'MM/DD/RRRR')
AS PURCHASE_DATE,
DV.PURCHASE_DATE
AS PURCHASE_DATE_SORT,
EJS_FLEET_MGMT_SEARCH_PK.GET_MAX_VEHICLE_ODOMETER (DV.VEHICLE_ID)
AS CURRENT_MILEAGE,
DV.CREATOR_ID
AS CREATOR_ID,
TO_CHAR (DV.CREATOR_DATE, 'MM/DD/RRRR HH24:MM:SS')
AS CREATOR_DATE,
DV.UPDATOR_ID
AS UPDATOR_ID,
TO_CHAR (DV.UPDATOR_DATE, 'MM/DD/RRRR HH24:MM:SS')
AS UPDATOR_DATE
FROM DEPARTMENT_VEHICLES DV
LEFT OUTER JOIN ITEM_MAKE_CODES MAKES ON MAKES.MAKE_ID = DV.MAKE_ID
LEFT OUTER JOIN ITEM_MODEL_CODES MODELS
ON MODELS.MODEL_ID = DV.MODEL_ID
INNER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = DV.AGENCY_CODE
WHERE 1 = 1
