SELECT V.VEHICLE_ID, V.VIN, V.YEAR, VM.VEHICLE_MAKE_DESC, VMO.VEHICLE_MODEL_DESC, VMS.VEHICLE_STYLE_DESC, VD.LIC_NUMBER, (SELECT DESCRIPTION FROM EJS_CODES ST WHERE ST.CODE = VD.LIC_STATE_CODE AND ST.CODE_TYPE = VD.LIC_STATE_CODE_TYPE), VD.LIC_YEAR, VD.LIC_MONTH, (SELECT DESCRIPTION FROM EJS_CODES CND WHERE CND.CODE = VD.COND_CODE AND CND.CODE_TYPE = VD.COND_CODE_TYPE), VD.COND_CODE, CASE WHEN VD.TOP_COLOR = VD.BOTTOM_COLOR THEN COLT.DESCRIPTION WHEN VD.BOTTOM_COLOR IS NOT NULL AND VD.TOP_COLOR IS NOT NULL THEN VD.TOP_COLOR ||'/'||VD.BOTTOM_COLOR WHEN VD.TOP_COLOR IS NULL THEN COLB.DESCRIPTION WHEN VD.BOTTOM_COLOR IS NULL THEN COLT.DESCRIPTION END AS VEH_COLOR, VD.ODOMETER_READING AS ODOMETER, VD.VALUE, VD.DATE_OF_INFO AS VEH_DOF, V.MCCS, VD.DAMAGE, VD.DAMAGE_CODE, (SELECT DESCRIPTION FROM EJS_CODES DAM WHERE DAM.CODE = VD.DAMAGE_CODE AND DAM.CODE_TYPE = VD.DAMAGE_CODE_TYPE), VD.MISC_ID, (SELECT GET_PERSON_NAME(PERSON_ID) FROM IMPOUND_PEOPLE OWNR, EJS_CODES EO WHERE EO.CODE = OWNR.ROLE_CODE AND EO.CODE_TYPE = OWNR.ROLE_CODE_TYPE AND OWNR.IMPOUND_ID = IMP.IMPOUND_ID AND OWNR.ROLE_CODE = 'O' AND ROWNUM = 1) AS OWNER_NAME, (SELECT INITCAP(EJS_ADDRESSES.FULL_STREET_SUMMARY( EJS_GET_LATEST_PERSON_ADDRESS(OWNR.PERSON_ID,NULL) ) ) FROM IMPOUND_PEOPLE OWNR, EJS_CODES EO WHERE EO.CODE = OWNR.ROLE_CODE AND EO.CODE_TYPE = OWNR.ROLE_CODE_TYPE AND OWNR.IMPOUND_ID = IMP.IMPOUND_ID AND OWNR.ROLE_CODE = 'O' AND ROWNUM = 1) AS OWNER_ADDR_STREET, (SELECT INITCAP(EJS_ADDRESSES.GET_CITY_NAME( EJS_GET_LATEST_PERSON_ADDRESS(OWNR.PERSON_ID,NULL) ) ) FROM IMPOUND_PEOPLE OWNR, EJS_CODES EO WHERE EO.CODE = OWNR.ROLE_CODE AND EO.CODE_TYPE = OWNR.ROLE_CODE_TYPE AND OWNR.IMPOUND_ID = IMP.IMPOUND_ID AND OWNR.ROLE_CODE = 'O' AND ROWNUM = 1) AS OWNER_ADDR_CITY, (SELECT EJS_ADDRESSES.GET_ADDR_STATE_CD( EJS_GET_LATEST_PERSON_ADDRESS(OWNR.PERSON_ID,NULL) ) FROM IMPOUND_PEOPLE OWNR, EJS_CODES EO WHERE EO.CODE = OWNR.ROLE_CODE AND EO.CODE_TYPE = OWNR.ROLE_CODE_TYPE AND OWNR.IMPOUND_ID = IMP.IMPOUND_ID AND OWNR.ROLE_CODE = 'O' AND ROWNUM = 1) AS OWNER_ADDR_STATE, (SELECT EJS_ADDRESSES.GET_ADDR_ZIP ( EJS_GET_LATEST_PERSON_ADDRESS(OWNR.PERSON_ID,NULL) ) FROM IMPOUND_PEOPLE OWNR, EJS_CODES EO WHERE EO.CODE = OWNR.ROLE_CODE AND EO.CODE_TYPE = OWNR.ROLE_CODE_TYPE AND OWNR.IMPOUND_ID = IMP.IMPOUND_ID AND OWNR.ROLE_CODE = 'O' AND ROWNUM = 1) AS OWNER_ADDR_ZIP5, (SELECT GET_PERSON_NAME(PERSON_ID) FROM IMPOUND_PEOPLE OWNR, EJS_CODES EO WHERE EO.CODE = OWNR.ROLE_CODE AND EO.CODE_TYPE = OWNR.ROLE_CODE_TYPE AND OWNR.IMPOUND_ID = IMP.IMPOUND_ID AND OWNR.ROLE_CODE = 'LP' AND ROWNUM = 1) AS LAST_POSSESS_NAME, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN TW.TOWING_CO_ID ELSE NULL END AS TOWING_CO_ID, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN INITCAP(TCC.TOWING_CO_NAME) ELSE INITCAP(TW.TOWING_CO) END AS TOWING_CO_NAME, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN TCC.TOWING_CO_STREET ELSE TW.TOWING_CO_STREET END AS TOWING_CO_STREET, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN TCC.TOWING_CO_CITY ELSE TW.TOWING_CITY END AS TOWING_CO_CITY, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN TCC.TOWING_CO_STATE ELSE TW.STATE_CD_STATE_CODE END AS TOWING_CO_STATE, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN TCC.TOWING_CO_ZIP ELSE TO_CHAR(TW.TOWING_CO_ZIP,'FM00000') END AS TOWING_CO_ZIP, CASE WHEN TW.TOWING_CO_ID IS NOT NULL THEN '('||TCC.PHONE_AREA || ') ' || TCC.PHONE_PREFIX || '-' || TCC.PHONE_SUFFIX ELSE '('||TW.PHONE_AREA || ') ' || TW.PHONE_PREFIX || '-' || TW.PHONE_SUFFIX END AS TOWING_CO_PHONE, TO_CHAR(IMP.DATE_OF_IMPOUND,'MM/DD/RRRR HH:MI PM') AS DATE_TOWED, TW.SLIP_NUMBER, INITCAP(TW.DRIVER), TW.TEXT, IMP.DATE_OF_INFO, IMP.LOCATION_OF_IMPOUND, IMP.CAD_NUMBER, (SELECT DESCRIPTION FROM EJS_CODES CNTY WHERE CNTY.CODE = IMP.COUNTY_CODE AND CNTY.CODE_TYPE = IMP.COUNTY_CODE_TYPE) AS IMP_COUNTY, IMP.CITY AS IMP_CITY, AC.AGENCY_DESC, IM.IMAGE, JOIN(CURSOR(SELECT EJS_OFFICER_PKG.GET_STD_OFFICER_DISP_FN(IOFF.OFFICER_ID) FROM IMPOUND_OFFICERS IOFF, EJS_CODES IOR WHERE IOR.CODE = IOFF.OFF_ROLE_CODE AND IOR.CODE_TYPE = IOFF.OFF_ROLE_CODE_TYPE --AND O.OFFICER_ID = IOFF.OFFICER_ID AND IOFF.IMPOUND_ID = IMP.IMPOUND_ID AND IOR.CODE = 'IMP' ),', ') AS IMPOUND_OFFICER, JOIN(CURSOR(SELECT HLD.DESCRIPTION||DECODE(RSN.DESCRIPTION, NULL, NULL, ' - '||RSN.DESCRIPTION) FROM IMPOUND_HOLDS IH, EJS_CODES HLD, EJS_CODES RSN WHERE IH.IMP_HOLD_CODE = HLD.CODE AND IH.IMP_HOLD_CODE_TYPE = HLD.CODE_TYPE AND IH.IMP_REAS_CODE = RSN.CODE (+) AND IH.IMP_REAS_CODE_TYPE = RSN.CODE_TYPE (+) AND IH.IMPOUND_ID = IMP.IMPOUND_ID AND RELEASE_DATE IS NULL ),', ') AS IMP_HOLDS, JOIN(CURSOR(SELECT RSN.DESCRIPTION FROM TOW_REASONS TR, EJS_CODES RSN WHERE TR.REASON_CODE = RSN.CODE AND TR.REASON_CODE_TYPE = RSN.CODE_TYPE AND TR.TOW_SEQ = IMP.TOW_SEQ ),', ') AS TOW_REASONS, JOIN(CURSOR(SELECT DECODE(TO_CHAR(QUANTITY), NULL, DESCRIPTION,'('||TO_CHAR(QUANTITY)||') ' || DESCRIPTION ) FROM IMPOUND_INVENTORY INV WHERE INV.IMPOUND_ID = IMP.IMPOUND_ID),', ') AS IMP_INVENTORY, IMP.IMPOUND_ID, IMP.INCIDENT_NUMBER, IMP.DATE_OF_RELEASE, IMP.DATE_RELEASE_SIGNED, IMP.IMP_DISP_CODE, (SELECT DESCRIPTION FROM EJS_CODES IDISP WHERE IDISP.CODE = IMP.IMP_DISP_CODE_TYPE AND IDISP.CODE_TYPE = IMP.COUNTY_CODE_TYPE) AS IMP_DISP_DESC FROM IMPOUND IMP INNER JOIN VEHICLES V ON IMP.VEHICLE_ID = V.VEHICLE_ID INNER JOIN VEHICLE_DESCRIPTIONS VD ON V.VEHICLE_ID = VD.VEHICLE_ID INNER JOIN AGENCY_CODES AC ON IMP.IMPOUNDING_AGENCY = AC.AGENCY_CODE LEFT OUTER JOIN IMAGES IM ON AC.IMAGE_ID = IM.IMAGE_ID LEFT OUTER JOIN VEHICLE_MAKES VM ON V.VMA_VEHICLE_MAKE_CODE = VM.VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_MODELS VMO ON V.VMO_VEHICLE_MODEL_CODE = VMO.VEHICLE_MODEL_CODE AND V.VMA_VEHICLE_MAKE_CODE = VMO.VMA_VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_STYLES VMS ON V.VTC_TYPE_CODE = VMS.VTC_TYPE_CODE AND V.VST_VEHICLE_STYLE_CODE = VMS.VEHICLE_STYLE_CODE LEFT OUTER JOIN TOWS TW ON IMP.VEHICLE_ID = TW.VEH_VEHICLE_ID AND IMP.TOW_SEQ = TW.TOW_SEQ LEFT OUTER JOIN TOW_COMPANY_CODES TCC ON TW.TOWING_CO_ID = TCC.TOWING_CO_ID LEFT OUTER JOIN EJS_CODES COLT ON VD.TOP_COLOR = COLT.CODE AND VD.TOP_COLOR_TYPE = COLT.CODE_TYPE LEFT OUTER JOIN EJS_CODES COLB ON VD.BOTTOM_COLOR = COLB.CODE AND VD.BOTTOM_COLOR_TYPE = COLB.CODE_TYPE LEFT OUTER JOIN EJS_CODES LTC ON VD.LIC_TYPE_CODE = LTC.CODE AND VD.LIC_TYPE_CODE_TYPE = LTC.CODE_TYPE LEFT OUTER JOIN EJS_CODES TST ON TW.STATE_CD_STATE_CODE = TST.CODE AND TW.STATE_CD_STATE_CODE_TYPE = TST.CODE_TYPE LEFT OUTER JOIN EJS_CODES TC ON TW.CITY_CD_CITY_CODE = TC.CODE AND TW.CITY_CD_CITY_CODE_TYPE = TC.CODE_TYPE WHERE (VD.DATE_OF_INFO = (SELECT MAX(DATE_OF_INFO) FROM VEHICLE_DESCRIPTIONS VD2 WHERE VD2.VEHICLE_ID = VD.VEHICLE_ID) OR VD.DATE_OF_INFO IS NULL)