View Definition:
SELECT DISTINCT
ewar.warrant_id,
ewar.wsc_code,
(SELECT W.DESCRIPTION FROM EJS_CODES W WHERE W.CODE = ewar.wsc_code AND W.CODE_TYPE = ewar.wsc_code_type),
ewar.plaintiff,
ewar.date_issued,
ewar.bond_amount,
ewar.bnd_typ_cd_bond_type "BOND_TYPE",
(SELECT DESCRIPTION FROM BOND_TYPE_CODES WHERE BOND_TYPE = ewar.bnd_typ_cd_bond_type),
ewar.sys_id,
ewar.issuing_agency,
ac_iss.agency_desc "ISSUING_AGENCY_DESC",
ewar.judge,
ewar.agency_code,
ac.agency_desc,
ewar.state_cd_state_code "STATE_CODE",
JOIN (
CURSOR (
SELECT DISTINCT
REPLACE (wr.reference_type, '_', ' ')
|| ': '
|| wr.reference_id
FROM warrant_references wr
WHERE WR.WARRANT_ID = EWAR.WARRANT_ID),
', '),
ewar.mn_per_person_id person_id,
ewar.mn_mn_id,
TRIM (mn.lname || ', ' || mn.fname || ' ' || mn.mname)
"PERSON_NAME_LFM",
TRIM (
mn.fname
|| ' '
|| DECODE (mn.mname,
NULL, mn.lname,
mn.mname || ' ' || mn.lname))
"PERSON_NAME_FML",
mn.dob,
mn.ssn,
mp.race_code,
(SELECT R.DESCRIPTION FROM EJS_CODES R WHERE R.CODE = mp.race_code AND R.CODE_TYPE = MP.RACE_CODE_TYPE),
mp.sex_code,
(SELECT S.DESCRIPTION FROM EJS_CODES S WHERE S.CODE = mp.race_code AND S.CODE_TYPE = MP.RACE_CODE_TYPE),
ejs_get_latest_person_phys (ewar.mn_per_person_id,
ewar.date_issued)
"PERSON_PHYS_ID",
ejs_get_latest_person_dl (ewar.mn_per_person_id,
ewar.state_cd_state_code)
"PERSON_DL",
JOIN (CURSOR (SELECT vw.offense_code || ' - ' || vw.offense_desc
FROM warrant_offense_charge_vw vw
WHERE vw.warrant_id = EWAR.WARRANT_ID),
', '),
ewar.STATE_ENTRY_DATE,
ewar.STATE_REMOVAL_DATE,
ewar.REMOVED,
WSO.SERVED_DATE
FROM e_warrants ewar,
master_names mn,
people p,
master_people mp,
agency_codes ac,
agency_codes ac_iss,
warrant_serving_officers wso
WHERE ewar.mn_mn_id = mn.mn_id
AND ewar.mn_per_person_id = mn.per_person_id
AND ewar.mn_per_person_id = p.person_id
AND p.master_person_id = mp.master_person_id
AND ac_iss.agency_code = ewar.issuing_agency
AND ac.agency_code = ewar.agency_code
AND ewar.warrant_id = wso.warrant_id (+)
