ZCQ_PER_NAMES_ADDRESS_VIEW
Details
-
Schema: FUSION
-
Object owner: ZCQ
-
Object type: VIEW
Columns
| Name |
|---|
|
PERSONIDENTIFIER REGISTRYIDENTIFIER LANGUAGENAME PERSONPROFILEID PERSONNAME TAXREGISTRATIONNUMBER PLACEOFBIRTH DATEOFBIRTH DATEOFDEATH GENDER DECLAREDETHNICITY PERSONSECONDLASTNAME LOCATIONIDENTIFIER PARTYSITEID HOUSETYPE ADDRESSLINE1 ADDRESSLINE2 ADDRESSLINE3 ADDRESSLINE4 PHONETICADDRESSLINES CITY COUNTY COUNTRYSUBENTITY PROVINCE COUNTRY POSTALCODE POSTALCODEEXT ADDRESS PARTYIDENTIFIERID1 PERSONALIDENTIFICATION1 PARTYIDENTIFIERID2 PERSONALIDENTIFICATION2 PARTYIDENTIFIERID3 PERSONALIDENTIFICATION3 EMAILCONTACTPOINTID PHONECONTACTPOINTID PHONENUMBER PHONEEXTENSION PHONELINETYPE CUSTOMATTRIBUTE1 CUSTOMATTRIBUTE2 CUSTOMATTRIBUTE3 CUSTOMATTRIBUTE4 CUSTOMATTRIBUTE5 CUSTOMATTRIBUTE6 CUSTOMATTRIBUTE7 CUSTOMATTRIBUTE8 CUSTOMATTRIBUTE9 CUSTOMATTRIBUTE10 PARTYNAMEID |
Query
| SQL_Statement |
|---|
|
SELECT p.PARTY_ID PersonIdentifier , p.PARTY_NUMBER RegistryIdentifier, p.LANGUAGE_NAME LanguageName, pr.PERSON_PROFILE_ID PersonProfileID, pr.person_title ||' ' ||pr.person_pre_name_adjunct ||' ' ||pr.person_first_name ||' ' ||pr.person_middle_name || ' ' ||p.preferred_name ||' ' ||pr.person_last_name ||' ' ||pr.person_name_suffix PersonName, pr.JGZZ_FISCAL_CODE TaxRegistrationNumber, pr.PLACE_OF_BIRTH PlaceofBirth, pr.DATE_OF_BIRTH DateofBirth, pr.DATE_OF_DEATH DateofDeath, pr.GENDER Gender, pr.DECLARED_ETHNICITY DeclaredEthnicity, pr.PERSON_SECOND_LAST_NAME PersonSecondLastName, loc.LOCATION_ID LocationIdentifier, ps.PARTY_SITE_ID PartySiteID, loc.HOUSE_TYPE HouseType, loc.ADDRESS1 AddressLine1, loc.ADDRESS2 AddressLine2, loc.ADDRESS3 AddressLine3, loc.ADDRESS4 AddressLine4, loc.ADDRESS_LINES_PHONETIC PhoneticAddressLines, loc.CITY City, loc.COUNTY County, loc.STATE CountrySubEntity, loc.Province Province, loc.COUNTRY Country, loc.POSTAL_CODE PostalCode, loc.POSTAL_PLUS4_CODE PostalCodeExt, (loc.Address1 ||' ' ||loc.Address2 ||' ' ||loc.Address3 ||' ' ||loc.Address4) Address, adi1.PARTY_IDENTIFIER_ID PartyIdentifierID1, adi1.PARTY_IDENTIFIER_VALUE PersonalIdentification1, adi2.PARTY_IDENTIFIER_ID PartyIdentifierID2, adi2.PARTY_IDENTIFIER_VALUE PersonalIdentification2, adi3.PARTY_IDENTIFIER_ID PartyIdentifierID3, adi3.PARTY_IDENTIFIER_VALUE PersonalIdentification3, ct1.CONTACT_POINT_ID EmailContactPointID, SUBSTR(ct1.EMAIL_ADDRESS,0,300) Email, ct2.CONTACT_POINT_ID PhoneContactPointID, ct2.RAW_PHONE_NUMBER PhoneNumber, ct2.PHONE_EXTENSION PhoneExtension, ct2.PHONE_LINE_TYPE PhoneLineType, pr.ATTRIBUTE1 CustomAttribute1, pr.ATTRIBUTE2 CustomAttribute2, pr.ATTRIBUTE3 CustomAttribute3, pr.ATTRIBUTE4 CustomAttribute4, pr.ATTRIBUTE5 CustomAttribute5, pr.ATTRIBUTE6 CustomAttribute6, pr.ATTRIBUTE7 CustomAttribute7, pr.ATTRIBUTE8 CustomAttribute8, pr.ATTRIBUTE9 CustomAttribute9, pr.ATTRIBUTE10 CustomAttribute10, NULL PartyNameID FROM hz_parties p, hz_person_profiles pr, hz_party_sites ps, hz_locations loc, hz_contact_points ct1, hz_contact_points ct2, hz_addtnl_party_ids adi1, hz_addtnl_party_ids adi2, hz_addtnl_party_ids adi3 WHERE p.party_type = 'PERSON' AND p.party_id = pr.PARTY_ID AND p.status = 'A' AND TRUNC(sysdate) BETWEEN pr.effective_start_date AND pr.effective_end_date AND (pr.ACTUAL_CONTENT_SOURCE = 'SST' AND pr.EFFECTIVE_LATEST_CHANGE = 'Y' ) AND p.party_id = ps.party_id AND ps.status = 'A' AND (NVL(ps.START_DATE_ACTIVE,sysdate) <=sysdate AND NVL(ps.END_DATE_ACTIVE,sysdate+1) >=sysdate) AND ps.location_id = loc.location_id AND p.party_id = ct1.OWNER_TABLE_ID(+) AND ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct1.CONTACT_POINT_TYPE(+) = 'EMAIL' AND ct1.STATUS(+) = 'A' AND ct1.OVERALL_PRIMARY_FLAG(+) = 'Y' AND (NVL(ct1.START_DATE(+),sysdate) <=sysdate AND NVL(ct1.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = ct2.OWNER_TABLE_ID(+) AND ct2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct2.CONTACT_POINT_TYPE(+) = 'PHONE' AND ct2.STATUS(+) = 'A' AND ct2.OVERALL_PRIMARY_FLAG(+) = 'Y' AND (NVL(ct2.START_DATE(+),sysdate) <=sysdate AND NVL(ct2.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = adi1.party_id(+) AND adi1.status_flag(+) ='A' AND adi1.party_identifier_type(+) ='DUMMY1' AND (NVL(adi1.issue_date(+),sysdate) <=sysdate AND NVL(adi1.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi2.party_id(+) AND adi2.status_flag(+) ='A' AND adi2.party_identifier_type(+) ='DUMMY2' AND (NVL(adi2.issue_date(+),sysdate) <=sysdate AND NVL(adi2.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi3.party_id(+) AND adi3.status_flag(+) ='A' AND adi3.party_identifier_type(+) ='DUMMY3' AND (NVL(adi3.issue_date(+),sysdate) <=sysdate AND NVL(adi3.expiration_date(+),sysdate+1)>=sysdate) UNION ALL SELECT p.PARTY_ID PersonIdentifier , p.PARTY_NUMBER RegistryIdentifier, p.LANGUAGE_NAME LanguageName, pr.PERSON_PROFILE_ID PersonProfileID, pn.party_name PersonName, pr.JGZZ_FISCAL_CODE TaxRegistrationNumber, pr.PLACE_OF_BIRTH PlaceofBirth, pr.DATE_OF_BIRTH DateofBirth, pr.DATE_OF_DEATH DateofDeath, pr.GENDER Gender, pr.DECLARED_ETHNICITY DeclaredEthnicity, pr.PERSON_SECOND_LAST_NAME PersonSecondLastName, loc.LOCATION_ID LocationIdentifier, ps.PARTY_SITE_ID PartySiteID, loc.HOUSE_TYPE HouseType, loc.ADDRESS1 AddressLine1, loc.ADDRESS2 AddressLine2, loc.ADDRESS3 AddressLine3, loc.ADDRESS4 AddressLine4, loc.ADDRESS_LINES_PHONETIC PhoneticAddressLines, loc.CITY City, loc.COUNTY County, loc.STATE CountrySubEntity, loc.Province Province, loc.COUNTRY Country, loc.POSTAL_CODE PostalCode, loc.POSTAL_PLUS4_CODE PostalCodeExt, (loc.Address1 ||' ' ||loc.Address2 ||' ' ||loc.Address3 ||' ' ||loc.Address4) Address, adi1.PARTY_IDENTIFIER_ID PartyIdentifierID1, adi1.PARTY_IDENTIFIER_VALUE PersonalIdentification1, adi2.PARTY_IDENTIFIER_ID PartyIdentifierID2, adi2.PARTY_IDENTIFIER_VALUE PersonalIdentification2, adi3.PARTY_IDENTIFIER_ID PartyIdentifierID3, adi3.PARTY_IDENTIFIER_VALUE PersonalIdentification3, ct1.CONTACT_POINT_ID EmailContactPointID, SUBSTR(ct1.EMAIL_ADDRESS,0,300) Email, ct2.CONTACT_POINT_ID PhoneContactPointID, ct2.RAW_PHONE_NUMBER PhoneNumber, ct2.PHONE_EXTENSION PhoneExtension, ct2.PHONE_LINE_TYPE PhoneLineType, pr.ATTRIBUTE1 CustomAttribute1, pr.ATTRIBUTE2 CustomAttribute2, pr.ATTRIBUTE3 CustomAttribute3, pr.ATTRIBUTE4 CustomAttribute4, pr.ATTRIBUTE5 CustomAttribute5, pr.ATTRIBUTE6 CustomAttribute6, pr.ATTRIBUTE7 CustomAttribute7, pr.ATTRIBUTE8 CustomAttribute8, pr.ATTRIBUTE9 CustomAttribute9, pr.ATTRIBUTE10 CustomAttribute10, pn.party_name_id PartyNameId FROM hz_parties p, hz_person_profiles pr, hz_party_sites ps, hz_locations loc, hz_contact_points ct1, hz_contact_points ct2, hz_addtnl_party_ids adi1, hz_addtnl_party_ids adi2, hz_addtnl_party_ids adi3, hz_addtnl_party_names pn WHERE p.party_type = 'PERSON' AND p.party_id = pr.PARTY_ID AND p.status = 'A' AND TRUNC(sysdate) BETWEEN pr.effective_start_date AND pr.effective_end_date AND (pr.ACTUAL_CONTENT_SOURCE = 'SST' AND pr.EFFECTIVE_LATEST_CHANGE = 'Y' ) AND p.party_id = pn.party_id AND pn.status_flag = 'A' AND p.party_id = ps.party_id AND ps.status = 'A' AND (NVL(ps.START_DATE_ACTIVE,sysdate) <=sysdate AND NVL(ps.END_DATE_ACTIVE,sysdate+1) >=sysdate) AND ps.location_id = loc.location_id AND p.party_id = ct1.OWNER_TABLE_ID(+) AND ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct1.CONTACT_POINT_TYPE(+) = 'EMAIL' AND ct1.STATUS(+) = 'A' AND ct1.OVERALL_PRIMARY_FLAG(+) = 'Y' AND (NVL(ct1.START_DATE(+),sysdate) <=sysdate AND NVL(ct1.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = ct2.OWNER_TABLE_ID(+) AND ct2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND ct2.CONTACT_POINT_TYPE(+) = 'PHONE' AND ct2.STATUS(+) = 'A' AND ct2.OVERALL_PRIMARY_FLAG(+) = 'Y' AND (NVL(ct2.START_DATE(+),sysdate) <=sysdate AND NVL(ct2.END_DATE(+),sysdate+1) >=sysdate) AND p.party_id = adi1.party_id(+) AND adi1.status_flag(+) ='A' AND adi1.party_identifier_type(+) ='DUMMY1' AND (NVL(adi1.issue_date(+),sysdate) <=sysdate AND NVL(adi1.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi2.party_id(+) AND adi2.status_flag(+) ='A' AND adi2.party_identifier_type(+) ='DUMMY2' AND (NVL(adi2.issue_date(+),sysdate) <=sysdate AND NVL(adi2.expiration_date(+),sysdate+1)>=sysdate) AND p.party_id = adi3.party_id(+) AND adi3.status_flag(+) ='A' AND adi3.party_identifier_type(+) ='DUMMY3' AND (NVL(adi3.issue_date(+),sysdate) <=sysdate AND NVL(adi3.expiration_date(+),sysdate+1)>=sysdate) |