[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | IGS_PE_MATCHES_PPA_V is used for the duplicate information about persons. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT P.PARTY_ID PERSON_ID
, P.PERSON_LAST_NAME SURNAME
, NULL SURNAME_5_CHAR
, P.PERSON_FIRST_NAME GIVEN_NAMES
, SUBSTR(P.PERSON_FIRST_NAME
, 1
, 1) GIVEN_NAME_1_CHAR
, S.GENDER SEX
, S.DATE_OF_BIRTH BIRTH_DT
, PIT2.API_PERSON_ID PREF_ALTERNATE_ID
, API.API_PERSON_ID
, API.PERSON_ID_TYPE
, NULL
, S.DECLARED_ETHNICITY ETHNIC_ORIGIN_ID
, PSU.SITE_USE_TYPE ADDR_TYPE
, PS.LOCATION_ID
, REPLACE(HL.ADDRESS1
, ' '
, '')||REPLACE(HL.ADDRESS2
, ' '
, '')||REPLACE(HL.ADDRESS3
, ' '
, '') ||REPLACE(HL.ADDRESS4
, ' '
, '') CONCT_ADDRESS_LINES
, REPLACE(HL.ADDRESS1
, ' '
, '') ADDR_LINE_1
, REPLACE(HL.ADDRESS2
, ' '
, '') ADDR_LINE_2
, REPLACE(HL.ADDRESS3
, ' '
, '') ADDR_LINE_3
, REPLACE(HL.ADDRESS4
, ' '
, '') ADDR_LINE_4
, HL.CITY
, HL.STATE
, HL.PROVINCE
, HL.COUNTY
, HL.COUNTRY
, HL.POSTAL_CODE POSTCODE
, PS.PARTY_SITE_ID
, P.STATUS
, API.START_DT API_START_DATE
, API.END_DT API_END_DATE
FROM HZ_PARTIES P
, IGS_PE_ALT_PERS_ID API
, HZ_PERSON_PROFILES S
, IGS_PE_PERSON_ID_TYPE_V PIT2
, HZ_LOCATIONS HL
, HZ_PARTY_SITE_USES PSU
, HZ_PARTY_SITES PS
WHERE P.PARTY_ID = API.PE_PERSON_ID (+)
AND P.PARTY_ID = S.PARTY_ID
AND SYSDATE BETWEEN S.EFFECTIVE_START_DATE
AND NVL(S.EFFECTIVE_END_DATE
, SYSDATE)
AND P.PARTY_ID = PIT2.PE_PERSON_ID (+)
AND HL.LOCATION_ID(+) = PS.LOCATION_ID
AND PS.PARTY_SITE_ID = PSU.PARTY_SITE_ID (+)
AND P.PARTY_ID = PS.PARTY_ID (+)