1. Tables and Views for Sales and Fusion Service
  2. ZCQ_SYNC_PER_PRIMARY_VIEW

ZCQ_SYNC_PER_PRIMARY_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

ADDRESS

CITY

COUNTY

COUNTRYSUBENTITY

PROVINCE

COUNTRY

POSTALCODE

POSTALCODEEXT

PARTYIDENTIFIERID1

PERSONALIDENTIFICATION1

PARTYIDENTIFIERID2

PERSONALIDENTIFICATION2

PARTYIDENTIFIERID3

PERSONALIDENTIFICATION3

EMAILCONTACTPOINTID

EMAIL

PHONECONTACTPOINTID

PHONENUMBER

PHONEEXTENSION

PHONELINETYPE

CUSTOMATTRIBUTE1

CUSTOMATTRIBUTE2

CUSTOMATTRIBUTE3

CUSTOMATTRIBUTE4

CUSTOMATTRIBUTE5

CUSTOMATTRIBUTE6

CUSTOMATTRIBUTE7

CUSTOMATTRIBUTE8

CUSTOMATTRIBUTE9

CUSTOMATTRIBUTE10

PARTYDATE

PERSONPROFILEDATE

PARTYSITEDATE

LOCATIONDATE

CONTACTPOINT1DATE

CONTACTPOINT2DATE

ADI1DATE

ADI2DATE

ADI3DATE

OPERATION

PARTYSTATUS

PARTYSITESTATUS

CONTACTPOINT1STATUS

CONTACTPOINT2STATUS

ADI1STATUS

ADI2STATUS

ADI3STATUS

PERSONPROFILESTATUS

LOCATIONSTATUS

IDT_NAME

ENTITY_TYPE_CODE

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.ADDRESS1||' '||loc.ADDRESS2||' '||loc.ADDRESS3||' '||loc.ADDRESS4) Address,

loc.CITY City,loc.COUNTY County,loc.STATE CountrySubEntity,loc.Province Province,

loc.COUNTRY Country,loc.POSTAL_CODE PostalCode,

loc.POSTAL_PLUS4_CODE PostalCodeExt,

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,

p.LAST_UPDATE_DATE PartyDate,pr.LAST_UPDATE_DATE PersonProfileDate,ps.LAST_UPDATE_DATE PartySiteDate,

loc.LAST_UPDATE_DATE LocationDate,

ct1.LAST_UPDATE_DATE ContactPoint1Date,

ct2.LAST_UPDATE_DATE ContactPoint2Date,

adi1.LAST_UPDATE_DATE Adi1Date,

adi2.LAST_UPDATE_DATE Adi2Date,

adi3.LAST_UPDATE_DATE Adi3Date,

decode(nvl(p.status,'A'),'A','ADD','DELETE') Operation,

p.status PartyStatus,

ps.status PartySiteStatus,

ct1.STATUS ContactPoint1Status,

ct2.STATUS ContactPoint2Status,

adi1.status_flag Adi1Status,

adi2.status_flag Adi2Status,

adi3.status_flag Adi3Status,

pr.status PersonProfileStatus, loc.status_flag LocationStatus ,

sync.idt_name,

sync.entity_type_code

from zcq_sync_ids sync, 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 sync.entity_id = p.party_id

and sync.entity_type_code = 'PERSON'

and p.party_id = pr.PARTY_ID

and p.party_type = 'PERSON'

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.overall_primary_flag(+)='Y'

and ps.location_id = loc.location_id(+)

and ps.status(+)='A'

and (nvl(ps.START_DATE_ACTIVE(+),sysdate)<=sysdate AND nvl(ps.END_DATE_ACTIVE(+),sysdate+1)>=sysdate)

and p.party_id = ct1.OWNER_TABLE_ID(+)

and ct1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'

and ct1.CONTACT_POINT_TYPE(+) = 'EMAIL'

and ct1.OVERALL_PRIMARY_FLAG(+) = 'Y'

and ct1.status(+) = 'A'

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.OVERALL_PRIMARY_FLAG(+) = 'Y'

and ct2.status(+)='A'

and (nvl(ct2.START_DATE(+),sysdate)<=sysdate AND nvl(ct2.END_DATE(+),sysdate+1)>=sysdate)

and p.party_id = adi1.party_id(+)

and adi1.party_identifier_type(+)='DUMMY1'

and adi1.status_flag(+)='A'

and (nvl(adi1.issue_date(+),sysdate)<=sysdate AND nvl(adi1.expiration_date(+),sysdate+1)>=sysdate)

and p.party_id = adi2.party_id(+)

and adi2.party_identifier_type(+)='DUMMY2'

and adi2.status_flag(+)='A'

and (nvl(adi2.issue_date(+),sysdate)<=sysdate AND nvl(adi2.expiration_date(+),sysdate+1)>=sysdate)

and p.party_id = adi3.party_id(+)

and adi3.party_identifier_type(+)='DUMMY3'

and adi3.status_flag(+)='A'

and (nvl(adi3.issue_date(+),sysdate)<=sysdate AND nvl(adi3.expiration_date(+),sysdate+1)>=sysdate)