VIEW: APPS.IGS_AD_IMP_PAR_MATCHES_V

Source

View Text - Preformatted

SELECT nm.rowid row_id ,nm.interface_id ,nm.party_site_id ,nm.addr_type ,nm.person_id_type ,nm.match_set_id ,nm.match_ind ,nm.person_id ,p.person_number person_number ,p.last_name ,p.first_name ,p.gender ,DECODE ( p.gender, NULL, NULL, (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE p.gender = LOOKUP_CODE AND LOOKUP_TYPE = 'HZ_GENDER' AND VIEW_APPLICATION_ID = 222 AND SECURITY_GROUP_ID = 0 AND LANGUAGE = USERENV('LANG') )) meaning ,p.birth_date ,pit.api_person_id ,s.declared_ethnicity ,DECODE ( S.DECLARED_ETHNICITY, NULL, NULL, (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE S.DECLARED_ETHNICITY = LOOKUP_CODE AND LOOKUP_TYPE = 'IGS_ETHNIC_ORIGIN' AND VIEW_APPLICATION_ID = 8405 AND SECURITY_GROUP_ID = 0 AND LANGUAGE = USERENV('LANG') )) ethnic_origin_meaning ,nm.created_by created_by ,nm.creation_date creation_date ,nm.last_updated_by last_updated_by ,nm.last_update_date last_update_date ,nm.last_update_login last_update_login ,nm.request_id request_id ,nm.program_application_id program_application_id ,nm.program_id program_id ,nm.program_update_date program_update_date ,nm.near_mtch_id near_mtch_id ,nm.action action FROM igs_ad_imp_near_mtch nm ,igs_pe_person_base_v p ,igs_pe_person_id_type_v pit ,hz_person_profiles s WHERE NM.PERSON_ID = P.PERSON_ID AND P.PERSON_ID = S.PARTY_ID (+) AND P.PERSON_ID = PIT.PE_PERSON_ID (+) AND SYSDATE BETWEEN S.EFFECTIVE_START_DATE AND NVL(S.EFFECTIVE_END_DATE,SYSDATE)

View Text - HTML Formatted

SELECT NM.ROWID ROW_ID
, NM.INTERFACE_ID
, NM.PARTY_SITE_ID
, NM.ADDR_TYPE
, NM.PERSON_ID_TYPE
, NM.MATCH_SET_ID
, NM.MATCH_IND
, NM.PERSON_ID
, P.PERSON_NUMBER PERSON_NUMBER
, P.LAST_NAME
, P.FIRST_NAME
, P.GENDER
, DECODE ( P.GENDER
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE P.GENDER = LOOKUP_CODE
AND LOOKUP_TYPE = 'HZ_GENDER'
AND VIEW_APPLICATION_ID = 222
AND SECURITY_GROUP_ID = 0
AND LANGUAGE = USERENV('LANG') )) MEANING
, P.BIRTH_DATE
, PIT.API_PERSON_ID
, S.DECLARED_ETHNICITY
, DECODE ( S.DECLARED_ETHNICITY
, NULL
, NULL
, (SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE S.DECLARED_ETHNICITY = LOOKUP_CODE
AND LOOKUP_TYPE = 'IGS_ETHNIC_ORIGIN'
AND VIEW_APPLICATION_ID = 8405
AND SECURITY_GROUP_ID = 0
AND LANGUAGE = USERENV('LANG') )) ETHNIC_ORIGIN_MEANING
, NM.CREATED_BY CREATED_BY
, NM.CREATION_DATE CREATION_DATE
, NM.LAST_UPDATED_BY LAST_UPDATED_BY
, NM.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NM.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, NM.REQUEST_ID REQUEST_ID
, NM.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, NM.PROGRAM_ID PROGRAM_ID
, NM.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, NM.NEAR_MTCH_ID NEAR_MTCH_ID
, NM.ACTION ACTION
FROM IGS_AD_IMP_NEAR_MTCH NM
, IGS_PE_PERSON_BASE_V P
, IGS_PE_PERSON_ID_TYPE_V PIT
, HZ_PERSON_PROFILES S
WHERE NM.PERSON_ID = P.PERSON_ID
AND P.PERSON_ID = S.PARTY_ID (+)
AND P.PERSON_ID = PIT.PE_PERSON_ID (+)
AND SYSDATE BETWEEN S.EFFECTIVE_START_DATE
AND NVL(S.EFFECTIVE_END_DATE
, SYSDATE)