SELECT A.ARREST_ID ,A.PERSON_ID ,EJS_PERSON_PKG.GET_PERSON_SUMMARY(PERSON_ID) ,A.ARREST_DATE ,A.TOWING_CO_ID ,A.ARR_COMMENT ,A.TOWING_CO_AREA ,A.TOWING_CO_CITY ,A.TOWING_CO_NAME ,A.TOWING_CO_PREFIX ,A.TOWING_CO_STREET ,A.TOWING_CO_SUFFIX ,A.TOWING_CO_ZIP ,A.TOWING_CO_STATE_CODE ,A.VEH_VEHICLE_ID ,A.AGENCY_CODE ,(SELECT AG.AGENCY_DESC FROM AGENCY_CODES AG WHERE AG.AGENCY_CODE = A.AGENCY_CODE) ,A.FINGERPRINT_CODE ,A.FINGERPRINT_CODE_TYPE ,A.ARREST_NUM ,A.ARREST_DSP_CODE ,(SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE = ARREST_DSP_CODE AND EC.CODE_TYPE = ARREST_DSP_CODE_TYPE) ,A.ARREST_DSP_COMMENT ,A.ARREST_DSP_CNTY_CODE ,(SELECT EC1.DESCRIPTION FROM EJS_CODES EC1 WHERE EC1.CODE_TYPE = A.ARREST_DSP_CNTY_CODE_TYPE AND EC1.CODE = A.ARREST_DSP_CNTY_CODE) ,A.ARREST_DSP_JAIL_CODE ,(SELECT EC2.DESCRIPTION FROM EJS_CODES EC2 WHERE EC2.CODE_TYPE = A.ARREST_DSP_JAIL_CODE_TYPE AND EC2.CODE = A.ARREST_DSP_JAIL_CODE) ,A.BOND_TYPE ,(SELECT BTC.DESCRIPTION FROM BOND_TYPE_CODES BTC WHERE BTC.BOND_TYPE = A.BOND_TYPE) ,A.BOND_AMOUNT ,A.ARREST_CNTY_CODE ,(SELECT EC2.DESCRIPTION FROM EJS_CODES EC2 WHERE EC2.CODE_TYPE = A.ARREST_CNTY_CODE_TYPE AND EC2.CODE = A.ARREST_CNTY_CODE) ,A.INC_REPORT_NUMBER ,A.JAIL_DATE ,A.IMPOUND_ID ,A.VEHICLE_SEIZED_FLAG ,A.VEHICLE_STORED_FLAG ,A.CURRENCY_SEIZED_FLAG ,A.CURRENCY_TOTAL ,A.DISCHARGE_DATE ,A.STATUS_CODE ,(SELECT S.DESCRIPTION FROM EJS_CODES S WHERE S.CODE_TYPE = A.STATUS_CODE_TYPE AND S.CODE = A.STATUS_CODE) ,A.CASE_NUMBER ,A.ADDR_ADDRESS_ID ,EJS_FORMAT_ADDRESS(A.ADDR_ADDRESS_ID) ,ADDR.STREET_NUMBER ,ADDR.DIRCT_CD_DIRECTION_CODE ,ADDR.STREET_NAME ,ADDR.STREET_CD_STREET_TYPE_CODE ,(SELECT DESCRIPTION FROM EJS_CODES WHERE CODE_TYPE = ADDR.STREET_CD_STREET_TYPE_COD_TYPE AND CODE = ADDR.STREET_CD_STREET_TYPE_CODE) AS STREET_TYPE_DESC ,ADDR.ADDR_SC_ADDRESS_SUBTYPE_CODE ,(SELECT DESCRIPTION FROM EJS_CODES WHERE CODE_TYPE = ADDR.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE AND CODE = ADDR.ADDR_SC_ADDRESS_SUBTYPE_CODE) AS ADDRESS_SUBTYPE_DESC ,ADDR.SUB_NUMBER ,ADDR.INTRSECT1_STREET_NUMBER ,ADDR.INTRSECT1_DIR ,ADDR.INTRSECT1_NAME ,CASE WHEN ADDR.BEAT IS NOT NULL AND ADDR.SUB_BEAT IS NOT NULL THEN ADDR.BEAT||'-'||ADDR.SUB_BEAT ELSE ADDR.BEAT||ADDR.SUB_BEAT END ,ADDR.CITY ,ADDR.STATE_CD_STATE_CODE ,(SELECT ST.DESCRIPTION FROM EJS_CODES ST WHERE ST.CODE_TYPE = ADDR.STATE_CD_STATE_CODE_TYPE AND ST.CODE = ADDR.STATE_CD_STATE_CODE) AS STATE_DESC -- ,TO_CHAR(ADDR.ZIP5, 'FM00000') ,ADDR.ZIP5 ,TO_CHAR(ADDR.ZIP4, 'FM0000') ,ADDR.LATITUDE ,ADDR.LONGITUDE ,AC.AC_ID as ARREST_CHARGE_ID ,AC.CHARGE_CODE ,CG.CHA_DESC as CHARGE_DESC ,CG.STATUTE ,AC.OFFENSE_COUNT ,TO_CHAR(AC.OFFENSE_DATE,'MM/DD/RRRR') as OFFENSE_DATE ,AC.CSAU_CODE ,CC.CSAU_DESC ,CG.DV_FLAG ,CASE WHEN OFF.FNAME IS NOT NULL AND OFF.LNAME IS NOT NULL THEN DECODE(OFF.TITLE,NULL,NULL,OFF.TITLE||' ')||OFF.FNAME||' '||OFF.LNAME||' - '||OFF.INTERNAL_ID ELSE NULL END --,AO.ROLE_CODE --,ORC.ROLE_DESC FROM ARRESTS A INNER JOIN ARREST_CHARGES AC ON A.ARREST_ID = AC.ARREST_ID INNER JOIN CSAU_CODES CC ON AC.CSAU_CODE = CC.CSAU_CODE INNER JOIN CHARGE_CODES CG ON AC.CHARGE_CODE = CG.CODE LEFT OUTER JOIN ADDRESSES ADDR ON A.ADDR_ADDRESS_ID = ADDR.ADDRESS_ID LEFT OUTER JOIN ARREST_OFFICERS AO ON A.ARREST_ID = AO.ARREST_ID AND AO.ROLE_CODE = 'X' --LEFT OUTER JOIN OFFICER_ROLE_CODES ORC ON AO.ROLE_CODE = ORC.ROLE_CODE LEFT OUTER JOIN OFFICERS OFF ON AO.OFFICER_ID = OFF.OFFICER_ID