View Definition:
SELECT F.FLDINT_ID,
F.FLDINT_DATE,
F.FLDINT_TYPE,
(SELECT FT.DESCRIPTION
FROM EJS_CODES FT
WHERE FT.CODE = F.FLDINT_TYPE
AND FT.CODE_TYPE = F.FLDINT_TYPE_TYPE)
AS FLDINT_TYPE_DESC,
F.AGENCY_CODE,
(SELECT AG.AGENCY_DESC
FROM AGENCY_CODES AG
WHERE AG.AGENCY_CODE = F.AGENCY_CODE)
AS AGENCY_DESC,
F.SUMMARY,
F.AGENCY_ONLY_YN,
F.SECURITY_LEVEL,
F.INTELL_ONLY_YN,
F.NOTE_ID,
(SELECT NARRATIVE FROM EJS_NOTES NTS WHERE NTS.NOTE_ID = F.NOTE_ID),
FA.ADDRESS_ID,
UPPER(EJS_FORMAT_ADDRESS(FA.ADDRESS_ID)),
A.STREET_NUMBER,
A.DIRCT_CD_DIRECTION_CODE,
A.STREET_NAME,
A.STREET_CD_STREET_TYPE_CODE,
(SELECT DESCRIPTION
FROM EJS_CODES
WHERE CODE = A.STREET_CD_STREET_TYPE_CODE
AND CODE_TYPE = A.STREET_CD_STREET_TYPE_COD_TYPE)
AS STREET_TYPE_DESC,
A.ADDR_SC_ADDRESS_SUBTYPE_CODE,
(SELECT DESCRIPTION
FROM EJS_CODES
WHERE CODE = A.ADDR_SC_ADDRESS_SUBTYPE_CODE
AND CODE_TYPE = A.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE)
AS ADDRESS_SUBTYPE_DESC,
A.SUB_NUMBER,
A.INTRSECT1_STREET_NUMBER,
A.INTRSECT1_DIR,
A.INTRSECT1_NAME,
FA.FI_ADDR_COMMENT,
CASE WHEN A.BEAT IS NOT NULL AND A.SUB_BEAT IS NOT NULL THEN A.BEAT||'-'||A.SUB_BEAT ELSE A.BEAT||A.SUB_BEAT END,
A.CITY,
A.STATE_CD_STATE_CODE,
(SELECT ST.DESCRIPTION
FROM EJS_CODES ST
WHERE ST.CODE = A.STATE_CD_STATE_CODE
AND ST.CODE_TYPE = A.STATE_CD_STATE_CODE_TYPE)
AS STATE_DESC,
A.ZIP5,
TO_CHAR (A.ZIP4, 'FM0000'),
A.LATITUDE,
A.LONGITUDE,
FA.FI_ADDR_COMMENT,
JOIN (
CURSOR (
SELECT O.TITLE
|| ' '
|| O.FNAME
|| ' '
|| O.LNAME
|| '-'
|| O.INTERNAL_ID
|| '/'
|| ORC.ROLE_DESC
FROM OFFICERS O, FLDINT_OFFICERS FO, OFFICER_ROLE_CODES ORC
WHERE F.FLDINT_ID = FO.FLDINT_ID
AND FO.OFFICER_ID = O.OFFICER_ID
AND FO.ROLE_CODE = ORC.ROLE_CODE),
' | '),
JOIN (
CURSOR (
SELECT DISTINCT GN.NAME ||' '|| DECODE(G.DESCRIPTION,NULL,NULL,' ('||G.DESCRIPTION||')' )
FROM GANGS G, GANG_NAMES GN, FLDINT_GANGS FG, EJS_CODES G
WHERE FG.GANG_ID = G.GANG_ID
AND G.GANG_ID = GN.GANG_ID
AND FG.FLDINT_ID = F.FLDINT_ID
AND G.CODE = G.GANG_TYPE_CODE AND G.CODE_TYPE = GANG_TYPE_CD_TYPE),
' | '),
JOIN (
CURSOR (
SELECT MN.FNAME||' '|| MN.MNAME ||' '|| MN.LNAME || DECODE(R.ROLE_DESC,NULL,NULL,' ('||R.ROLE_DESC||')')
FROM FLDINT_PEOPLE FP, PEOPLE PEOP, ROLE_CODES R, MASTER_NAMES MN
WHERE FP.PERSON_ID = PEOP.PERSON_ID
AND PEOP.PERSON_ID = MN.PER_PERSON_ID
AND MN.NAME_TYPE_CODE = 'P'
AND FP.ROLE_TYPE = R.ROLE_TYPE
AND FP.FLDINT_ID = F.FLDINT_ID),
' | '),
JOIN (
CURSOR (
SELECT BUSINESS_NAME || DECODE(BC.DESCRIPTION,NULL,NULL,' ('||BC.DESCRIPTION||')')
FROM FLDINT_BUSINESSES FB, BUSINESSES BUS, EJS_CODES BC
WHERE FB.BUSINESS_NUMBER = BUS.BUSINESS_NUMBER
AND BUS.BTC_TYPE_CODE = BC.CODE
AND BUS.BTC_TYPE_CODE_TYPE = BC.CODE_TYPE
AND FB.FLDINT_ID = F.FLDINT_ID),
' | '),
JOIN (
CURSOR (
SELECT EJS_GET_VEHICLE_DESC(FV.VEHICLE_ID)
FROM FLDINT_VEHICLES FV
WHERE FV.FLDINT_ID = F.FLDINT_ID),
' | ')
FROM FIELD_INTERVIEWS F
LEFT OUTER JOIN FLDINT_ADDRESSES FA ON F.FLDINT_ID = FA.FLDINT_ID
LEFT OUTER JOIN ADDRESSES A ON FA.ADDRESS_ID = A.ADDRESS_ID
