View: IGS_DA_XML_PERSON_V

View Text

SELECT DISTINCT NVL((SELECT API.PERSON_ID_TYPE
FROM IGS_PE_ALT_PERS_ID API
WHERE HP.PARTY_ID = API.PE_PERSON_ID
AND API.PERSON_ID_TYPE = DS.DEFAULT_STUDENT_ID_TYPE
AND (API.END_DT >= SYSDATE OR API.END_DT IS NULL))
, 'OSS')
, NVL((SELECT API.API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID API
WHERE HP.PARTY_ID = API.PE_PERSON_ID
AND API.PERSON_ID_TYPE = DS.DEFAULT_STUDENT_ID_TYPE
AND (API.END_DT >= SYSDATE OR API.END_DT IS NULL))
, HP.PARTY_NUMBER)
, HP.PARTY_TYPE
, HP.PERSON_PRE_NAME_ADJUNCT
, HP.PERSON_FIRST_NAME
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_LAST_NAME
, HP.PERSON_NAME_SUFFIX
, HPSU.SITE_USE_TYPE
, HPL.ADDRESS1
, HPL.ADDRESS2
, HPL.ADDRESS3
, HPL.ADDRESS4
, HPL.CITY
, (HPL.STATE || HP.PROVINCE)
, HPL.POSTAL_CODE
, HPL.COUNTRY
, DR.BATCH_ID
, HP.PARTY_ID
FROM HZ_PARTIES HP
, HZ_LOCATIONS HPL
, HZ_PARTY_SITE_USES HPSU
, HZ_PARTY_SITES HPS
, IGS_DA_SETUP DS
, IGS_DA_RQST DR
, IGS_DA_REQ_STDNTS DRS
, IGS_PE_HZ_PTY_SITES HZPTYS
WHERE DS.S_CONTROL_NUM = 1
AND HP.PARTY_ID = HPS.PARTY_ID
AND HPS.LOCATION_ID = HPL.LOCATION_ID
AND HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = NVL( (SELECT DISTINCT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.BATCH_ID = DR.BATCH_ID
AND DRF.FEATURE_CODE = 'PAO')
, DS.DEFAULT_ADDRESS_TYPE)
AND DRS.BATCH_ID = DR.BATCH_ID
AND DRS.PERSON_ID = HP.PARTY_ID
AND HZPTYS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND SYSDATE BETWEEN NVL (HZPTYS.START_DATE
, SYSDATE)
AND NVL (HZPTYS.END_DATE
, SYSDATE)
AND NVL (HPS.IDENTIFYING_ADDRESS_FLAG
, 'N') = 'Y' UNION ALL SELECT DISTINCT NVL((SELECT API.PERSON_ID_TYPE
FROM IGS_PE_ALT_PERS_ID API
WHERE HP.PARTY_ID = API.PE_PERSON_ID
AND API.PERSON_ID_TYPE = DS.DEFAULT_STUDENT_ID_TYPE
AND (API.END_DT >= SYSDATE OR API.END_DT IS NULL))
, 'OSS')
, NVL((SELECT API.API_PERSON_ID
FROM IGS_PE_ALT_PERS_ID API
WHERE HP.PARTY_ID = API.PE_PERSON_ID
AND API.PERSON_ID_TYPE = DS.DEFAULT_STUDENT_ID_TYPE
AND (API.END_DT >= SYSDATE OR API.END_DT IS NULL))
, HP.PARTY_NUMBER)
, HP.PARTY_TYPE
, HP.PERSON_PRE_NAME_ADJUNCT
, HP.PERSON_FIRST_NAME
, HP.PERSON_MIDDLE_NAME
, HP.PERSON_LAST_NAME
, HP.PERSON_NAME_SUFFIX
, NVL(DRF.FEATURE_VALUE
, DS.DEFAULT_ADDRESS_TYPE)
, 'ADDRESS1 DOES NOT EXIST'
, 'ADDRESS2 DOES NOT EXIST'
, 'ADDRESS3 DOES NOT EXIST'
, 'ADDRESS4 DOES NOT EXIST'
, 'CITY DOES NOT EXIST'
, 'STATE OR PROVINCE DOES NOT EXIST'
, 'POSTAL CODE DOES NOT EXIST'
, 'COUNTRY VALUE DOES NOT EXIST'
, DR.BATCH_ID
, HP.PARTY_ID
FROM HZ_PARTIES HP
, IGS_DA_SETUP DS
, IGS_DA_RQST DR
, IGS_DA_REQ_STDNTS DRS
, IGS_DA_REQ_FTRS DRF
WHERE DS.S_CONTROL_NUM = 1
AND NOT EXISTS (SELECT 'X'
FROM HZ_LOCATIONS HPL
, HZ_PARTY_SITE_USES HPSU
, HZ_PARTY_SITES HPS
, IGS_PE_HZ_PTY_SITES HZPTYS
WHERE HP.PARTY_ID = HPS.PARTY_ID
AND HPS.LOCATION_ID = HPL.LOCATION_ID
AND HPS.PARTY_SITE_ID = HPSU.PARTY_SITE_ID
AND HPSU.SITE_USE_TYPE = NVL( (SELECT DISTINCT DRF.FEATURE_VALUE
FROM IGS_DA_REQ_FTRS DRF
WHERE DRF.BATCH_ID = DR.BATCH_ID
AND DRF.FEATURE_CODE = 'PAO')
, DS.DEFAULT_ADDRESS_TYPE)
AND HZPTYS.PARTY_SITE_ID = HPS.PARTY_SITE_ID
AND SYSDATE BETWEEN NVL (HZPTYS.START_DATE
, SYSDATE)
AND NVL (HZPTYS.END_DATE
, SYSDATE)
AND NVL (HPS.IDENTIFYING_ADDRESS_FLAG
, 'N') = 'Y' )
AND DRS.BATCH_ID = DR.BATCH_ID
AND DRS.PERSON_ID = HP.PARTY_ID
AND DR.BATCH_ID = DRF.BATCH_ID(+)
AND DRF.FEATURE_CODE(+) = 'PAO'

Columns