VIEW: APPS.IGS_PE_DUP_MATCHES_PA_V

Source

View Text - Preformatted

SELECT P.PARTY_ID PERSON_ID, P.PARTY_NUMBER PERSON_NUMBER, 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, PP.GENDER SEX, PP.DATE_OF_BIRTH BIRTH_DT, PIT.API_PERSON_ID PREF_ALTERNATE_ID, PP.DECLARED_ETHNICITY ETHNIC_ORIGIN_ID, PSU.SITE_USE_TYPE ADDR_TYPE, HL.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 FROM HZ_PARTIES P, IGS_PE_PERSON_ID_TYPE_V PIT, HZ_PERSON_PROFILES PP, HZ_LOCATIONS HL, HZ_PARTY_SITES PS, HZ_PARTY_SITE_USES PSU WHERE P.PARTY_ID = PP.PARTY_ID AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE AND NVL(PP.EFFECTIVE_END_DATE, SYSDATE) AND P.PARTY_ID = PS.PARTY_ID (+) AND P.PARTY_ID = PIT.PE_PERSON_ID (+) AND HL.LOCATION_ID(+) =PS.LOCATION_ID AND PS.PARTY_SITE_ID =PSU.PARTY_SITE_ID (+)

View Text - HTML Formatted

SELECT P.PARTY_ID PERSON_ID
, P.PARTY_NUMBER PERSON_NUMBER
, 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
, PP.GENDER SEX
, PP.DATE_OF_BIRTH BIRTH_DT
, PIT.API_PERSON_ID PREF_ALTERNATE_ID
, PP.DECLARED_ETHNICITY ETHNIC_ORIGIN_ID
, PSU.SITE_USE_TYPE ADDR_TYPE
, HL.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
FROM HZ_PARTIES P
, IGS_PE_PERSON_ID_TYPE_V PIT
, HZ_PERSON_PROFILES PP
, HZ_LOCATIONS HL
, HZ_PARTY_SITES PS
, HZ_PARTY_SITE_USES PSU
WHERE P.PARTY_ID = PP.PARTY_ID
AND SYSDATE BETWEEN PP.EFFECTIVE_START_DATE
AND NVL(PP.EFFECTIVE_END_DATE
, SYSDATE)
AND P.PARTY_ID = PS.PARTY_ID (+)
AND P.PARTY_ID = PIT.PE_PERSON_ID (+)
AND HL.LOCATION_ID(+) =PS.LOCATION_ID
AND PS.PARTY_SITE_ID =PSU.PARTY_SITE_ID (+)