SELECT OC.OFFENSE_CODE AS OFFENSE_CODE, OC.OFFENSE_DESC AS OFFENSE_DESC_SHORT, OC.OFFENSE_DETAILS AS OFFENSE_DESC_LONG, CC.CODE AS CHARGE_CODE, CC.CHA_DESC AS CHARGE_DESC, ACC.ARREST_CHARGE_CODE AS ARREST_CHARGE_CODE, ACC.ARREST_CHA_DESC AS ARREST_CHARGE_DESC, JOIN ( CURSOR ( SELECT NOF.NIBRS_CODE FROM NIBRS_OFFENSES NOF WHERE NOF.OFFENSE_CODE = OC.OFFENSE_CODE ), ',' ) AS PARENT_NIBRS_CODE, OC.IBR_REPORTING_CODE AS IBR_REPORTING_CODE, CC.STATUTE AS STATUTE, CC.CHARGE_CLASS_CODE AS CLASS_CODE, CC.CHARGE_CATEGORY_CODE AS CATEGORY_CODE, CC.INADATE AS CHARGE_EXPIRE_DATE, '' AS NOTES, '' AS ACTION, '' AS DEACTIVATE_OFFENSE_CODE, '' AS DEACTIVATE_CHARGE_CODE, '' AS DEACTIVATE_ARREST_CODE, CC.EFFDATE AS CHARGE_EFFECTIVE_DATE, OC.ACTIVE_FLAG AS ACTIVE_FLAG FROM OFFENSE_CODES OC LEFT OUTER JOIN CHARGE_CODES CC ON CC.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE AND (CC.INADATE > SYSDATE OR CC.INADATE IS NULL) LEFT OUTER JOIN ARREST_CHARGE_CODES ACC ON ACC.OFFENSE_CODE = OC.OFFENSE_CODE --AND ACC.ACTIVE_FLAG = 'Y' ORDER BY OC.ACTIVE_FLAG DESC