1. Tables and Views
  2. PSC_CE_OFFICER_DISTRICT_V

PSC_CE_OFFICER_DISTRICT_V

Details

  • Schema: FUSION

  • Object owner: PSC_CE

  • Object type: VIEW

Columns

Name

PERSON_ID

DISTRICT_TYPE

DISTRICT_ID

DISTRICT_DESCRIPTION

DISTRICT_TYPE_DESCRIPTION

Query

SQL_Statement

SELECT A.PERSON_ID, DL.DISTRICT_TYPE DISTRICT_TYPE, A.JOB_FUNC_SECTN_VALUE DISTRICT_ID, DL.DESCRIPTION DISTRICT_DESCRIPTION, TL.DESCRIPTION DISTRICT_TYPE_DESCRIPTION

FROM PSC_COM_JOBSECTN_PROF_GRP_V A, PSC_COM_DISTRICT_VL DL, PSC_COM_DISTRICT_TYPE_VL TL

WHERE A.JOB_FUNC_ID='ORA_CODE_ENFORCEMENT_OFFICER'

AND A.JOB_FUNC_SEC_ID='ORA_PSC_CE_DISTRICTS'

AND DL.DISTRICT_ID=A.JOB_FUNC_SECTN_VALUE

AND TL.DISTRICT_TYPE = DL.DISTRICT_TYPE

AND TL.DISTRICT_CATEGORY='ORA_CE'

UNION

SELECT A.PERSON_ID, A.JOB_FUNC_SECTN_VALUE DISTRICT_TYPE, DL.DISTRICT_ID DISTRICT_ID, DL.DESCRIPTION DISTRICT_DESCRIPTION, TL.DESCRIPTION DISTRICT_TYPE_DESCRIPTION

FROM PSC_COM_JOBSECTN_PROF_GRP_V A, PSC_COM_DISTRICT_VL DL, PSC_COM_DISTRICT_TYPE_VL TL

WHERE A.JOB_FUNC_ID='ORA_CODE_ENFORCEMENT_OFFICER'

AND A.JOB_FUNC_SEC_ID='ORA_PSC_CE_DISTRICT_TYPES'

AND DL.DISTRICT_TYPE=A.JOB_FUNC_SECTN_VALUE

AND TL.DISTRICT_TYPE = DL.DISTRICT_TYPE

AND TL.DISTRICT_CATEGORY='ORA_CE'

UNION

SELECT DISTINCT A.PERSON_ID, DL.DISTRICT_TYPE DISTRICT_TYPE, DL.DISTRICT_ID DISTRICT_ID, DL.DESCRIPTION DISTRICT_DESCRIPTION, TL.DESCRIPTION DISTRICT_TYPE_DESCRIPTION

FROM PSC_COM_JOBSECTN_PROF_GRP_V A, PSC_COM_DISTRICT_VL DL, PSC_COM_DISTRICT_TYPE_VL TL

WHERE A.JOB_FUNC_ID='ORA_CODE_ENFORCEMENT_OFFICER'

AND A.JOB_FUNC_SEC_ID IN ('ORA_PSC_CE_DISTRICT_TYPES','ORA_PSC_CE_DISTRICTS')

AND TRIM(A.JOB_FUNC_SECTN_VALUE) IS NULL

AND TL.DISTRICT_TYPE = DL.DISTRICT_TYPE

AND TL.DISTRICT_CATEGORY='ORA_CE'