VIEW: APPS.IGS_DA_XML_PERSON_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'