SELECT PH.ID, PH.AGENCY_CODE, (SELECT AC.AGENCY_DESC FROM AGENCY_CODES AC WHERE AC.AGENCY_CODE = PH.AGENCY_CODE) AS AGENCY_DESC, PH.COMMENTS, PH.RECOVERED_BY, PH.RECOVERED_DATE, PH.DISPOSED_FLAG, PH.DISPOSITION_PERSON_ID, PH.DISPOSITION_DATE, PH.DISPOSITION_COMMENTS, MN.FNAME AS DISPOSITION_PERSON_FIRST_NAME, MN.LNAME AS DISPOSITION_PERSON_LAST_NAME, CASE WHEN MN.MN_ID IS NOT NULL THEN MN.FNAME || ' ' || MN.LNAME ELSE PH.DISPOSITION_PERSON_TEXT END AS DISPOSITION_PERSON_NAME, CASE WHEN PH.DISPOSITION_EMPLOYEE_ID IS NOT NULL THEN GET_OFF_EMP_NAME(PH.DISPOSITION_EMPLOYEE_ID , 'E') WHEN PH.DISPOSITION_OFFICER_ID IS NOT NULL THEN GET_OFF_EMP_NAME(PH.DISPOSITION_OFFICER_ID, 'O') END AS DISPOSITION_RELEASED_BY, DRC.CODE AS DISPOSITION_REASON_CODE, DRC.DESCRIPTION AS DISPOSITION_REASON_DESC, PHC.COMMENTS as CUSTODY_COMMENTS, PHC.START_DATE, PHC.LOCATION_ID, (SELECT DESCRIPTION FROM PROPERTY_HOLD_LOCATIONS PHL WHERE PHL.ID = PHC.LOCATION_ID) AS LOCATION_DESCRIPTION, PHC.OFFICER_ID, CASE WHEN PH.DISPOSED_FLAG = 'Y' THEN 'Disposed' WHEN PHC.EMPLOYEE_ID IS NOT NULL THEN GET_OFF_EMP_NAME(PHC.EMPLOYEE_ID, 'E') WHEN PHC.OFFICER_ID IS NOT NULL THEN GET_OFF_EMP_NAME(PHC.OFFICER_ID, 'O') WHEN PHC.LOCATION_ID IS NOT NULL THEN (SELECT DESCRIPTION FROM PROPERTY_HOLD_LOCATIONS PHL WHERE PHL.ID = PHC.LOCATION_ID) END AS CURRENT_CUSTODY_DESC, CASE WHEN PHC.EMPLOYEE_ID IS NOT NULL THEN EJS_ENCRYPTION.DECRYPT_AES256(E.ENC_FNAME) WHEN PHC.OFFICER_ID IS NOT NULL THEN O.FNAME ELSE NULL END AS CURRENT_CUSTODY_FIRST_NAME, CASE WHEN PHC.EMPLOYEE_ID IS NOT NULL THEN EJS_ENCRYPTION.DECRYPT_AES256(E.ENC_LNAME) WHEN PHC.OFFICER_ID IS NOT NULL THEN O.LNAME ELSE NULL END AS CURRENT_CUSTODY_LAST_NAME, PH.PROPERTY_ID, P.SERIAL_NUMBER, P.DESCRIPTION, P.DOCUMENT, P.PROPERTY_TYPE_CODE, (SELECT PROPERTY_TYPE_DESC FROM PROPERTY_TYPE_CODES PTC WHERE PTC.PROPERTY_TYPE = P.PROPERTY_TYPE_CODE) AS PROPERTY_TYPE_DESC, P.MISC, P.MAKE, P.MODEL, P.PRIMARY_COLOR AS PRIMARY_COLOR_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.PRIMARY_COLOR_TYPE AND P.PRIMARY_COLOR = EC.CODE) AS PRIMARY_COLOR_DESC, P.SECONDARY_COLOR AS SECONDARY_COLOR_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.SECONDARY_COLOR_TYPE AND P.SECONDARY_COLOR = EC.CODE) AS SECONDARY_COLOR_DESC , P.QUANTITY, P.DRUG_TYPE AS DRUG_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.DRUG_TYPE_TYPE AND P.DRUG_TYPE = EC.CODE) AS DRUG_TYPE_DESC, P.TYPE_OF_QUANTITY as MEASURE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.TYPE_OF_QUANTITY_TYPE AND P.TYPE_OF_QUANTITY = EC.CODE) AS MEASURE_DESC, P.BANK, P.DOCUMENT_NUMBER, P.DOCUMENT_DATE, P.ACCOUNT_NAME, P.ACCOUNT_NUMBER, P.PAYABLE_TO, P.AMOUNT, P.ENDORSEE, P.CURRENCY_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.CURRENCY_CODE_TYPE AND P.CURRENCY_CODE = EC.CODE) AS CURRENCY_DESC, P.GUN_TYP_CODE AS GUN_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.GUN_TYP_CODE_TYPE AND P.GUN_TYP_CODE = EC.CODE) AS GUN_TYPE_DESC, P.GUN_ACT_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.GUN_ACT_CODE_TYPE AND P.GUN_ACT_CODE = EC.CODE) AS GUN_ACTION_DESC, P.CALGAG_CODE AS GUN_CALIBER_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = P.CALGAG_CODE_TYPE AND P.CALGAG_CODE = EC.CODE) AS GUN_CALIBER_DESC, P.BARREL_LENGTH, PH.CREATOR_DATE FROM PROPERTY_HOLDS PH INNER JOIN PROPERTIES P ON PH.PROPERTY_ID = P.PROPERTY_ID LEFT OUTER JOIN PROPERTY_HOLD_CUSTODIES PHC ON PH.ID = PHC.PROPERTY_HOLD_ID LEFT OUTER JOIN OFFICERS O ON PHC.OFFICER_ID = O.OFFICER_ID LEFT OUTER JOIN EMPLOYEES E ON PHC.EMPLOYEE_ID = E.EJS_EMP_ID LEFT OUTER JOIN EJS_CODES DRC ON PH.DISPOSITION_REASON_CODE_TYPE = DRC.CODE_TYPE AND PH.DISPOSITION_REASON_CODE = DRC.CODE LEFT OUTER JOIN (SELECT P.PERSON_ID, MN.FNAME, MN.LNAME, MN.MN_ID FROM PEOPLE P INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID INNER JOIN PEOPLE P2 ON MP.MASTER_PERSON_ID = P2.MASTER_PERSON_ID INNER JOIN MASTER_NAMES MN ON P2.PERSON_ID = MN.PER_PERSON_ID AND MN.NAME_TYPE_CODE = 'P') MN ON MN.PERSON_ID = PH.DISPOSITION_PERSON_ID WHERE PHC.ID = (SELECT MAX(PHC2.ID) FROM PROPERTY_HOLD_CUSTODIES PHC2 WHERE PHC2.PROPERTY_HOLD_ID = PH.ID)