View Definition:
SELECT
W.WARRANT_ID,
W.WARRANT_NUMBER,
W.NIC_NUMBER,
EC.DESCRIPTION AS STATUS_DESC,
WSC_CODE As STATUS_CODE,
TC.DESCRIPTION AS TYPE_DESC,
TC.CODE AS TYPE_CODE,
IAC.AGENCY_DESC AS ISSUING_AGENCY_NAME,
IAC.AGENCY_CODE AS ISSUING_AGENCY_CODE,
W.ISSUING_COURT_LOC_ID,
W.ISSUING_COURT_NAME,
W.BOND_AMOUNT,
BTC.DESCRIPTION AS BOND_TYPE_DESC,
BTC.BOND_TYPE AS BOND_TYPE_CODE,
W.AGENCY_CODE as USER_AGENCY,
W.CHARGING_AGENCY_CODE,
W.CHARGING_AGENCY_NAME,
W.DATE_ISSUED,
W.RECEIVED_DATE,
W.STATE_ENTRY_DATE,
W.STATE_REMOVAL_DATE,
W.STATE_CD_STATE_CODE,
W.SYS_ID,
MN.FNAME,
MN.LNAME,
PE.MASTER_PERSON_ID as MASTER_PERSON_ID,
TO_CHAR(MN.DOB,'MM/DD/RRRR') as DOB_STR,
MN.DOB as DOB,
W.JUDGE,
W.PLAINTIFF,
EJS_FORMAT_ADDRESS (ADDR.ADDRESS_ID) AS ADDRESS,
ADDR.ADDRESS_ID,
ADDR.STREET_NUMBER,
ADDR.DIRCT_CD_DIRECTION_CODE as STREET_DIRECTION_CODE,
ADDR.STREET_NAME,
ADDR.STREET_CD_STREET_TYPE_CODE as STREET_TYPE_CODE,
ADDR.DIRECT_SUFFIX as STREET_DIRECTION_SUFFIX_CODE,
ADDR.ADDR_SC_ADDRESS_SUBTYPE_CODE as STREET_SUB_TYPE_CODE,
ADDR.SUB_NUMBER as STREET_SUB_NUMBER,
ADDR.CITY,
ADDR.STATE_CD_STATE_CODE AS ADDR_STATE_CODE,
ADDR.ZIP5,
ADDR.SUB_BEAT,
ADDR.INTRSECT1_NAME as INTERSECT_STREET_NAME,
ADDR.INTRSECT1_DIR as INTERSECT_STREET_DIR_CODE,
ADDR.INTRSECT1_STREET_TYPE_CODE AS INTERSECT_STREET_TYPE_CODE,
ADDR.INTRSECT1_DIR_SUFFIX as INTERSECT_DIR_SUFFIX_CODE,
ADDR.GEOCODED_FLAG,
W.EXPIRED_DATE,
W.REVIEW_DATE,
(SELECT LISTAGG(INC_REPORT_NUMBER, ',') WITHIN GROUP (ORDER BY INC_REPORT_NUMBER)
FROM INCIDENT_WARRANTS IW
LEFT OUTER JOIN INCIDENTS I ON IW.INC_INCIDENT_ID = I.INCIDENT_ID
WHERE IW.WARRANT_ID = W.WARRANT_ID) AS INC_REPORT_NUMBER,
WSO.SERVED_DATE
FROM E_WARRANTS W
INNER JOIN PEOPLE PE ON W.MN_PER_PERSON_ID = PE.PERSON_ID
INNER JOIN MASTER_NAMES MN ON W.MN_MN_ID = MN.MN_ID
INNER JOIN AGENCY_CODES AC ON W.AGENCY_CODE = AC.AGENCY_CODE
INNER JOIN AGENCY_CODES IAC ON W.ISSUING_AGENCY = IAC.AGENCY_CODE
LEFT OUTER JOIN WARRANT_SERVICE_ADDRESSES WSA ON W.WARRANT_ID = WSA.WARRANT_ID
LEFT OUTER JOIN ADDRESSES ADDR ON WSA.ADDRESS_ID = ADDR.ADDRESS_ID
LEFT OUTER JOIN BOND_TYPE_CODES BTC ON W.BND_TYP_CD_BOND_TYPE = BTC.BOND_TYPE
LEFT OUTER JOIN EJS_CODES EC ON EC.CODE_TYPE = 'WARRANT_STATUS_CODES' AND EC.CODE = W.WSC_CODE
LEFT OUTER JOIN EJS_CODES TC ON W.WARRANT_TYPE_CODE_TYPE = TC.CODE_TYPE AND W.WARRANT_TYPE = TC.CODE
LEFT OUTER JOIN WARRANT_SERVING_OFFICERS WSO ON W.WARRANT_ID = WSO.WARRANT_ID
LEFT OUTER JOIN OFFICERS OFF ON OFF.OFFICER_ID = WSO.OFFICER_ID
LEFT OUTER JOIN WARRANT_SERVING_OFFICERS WSO ON W.WARRANT_ID = WSO.WARRANT_ID
