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

ZCQ_IMP_PER_PHONE_VIEW

Details

  • Schema: FUSION

  • Object owner: ZCQ

  • Object type: VIEW

Columns

Name

PERSONINTERFACEIDENTIFIER

BATCHID

PERSONIDENTIFIER

REGISTRYIDENTIFIER

PERSONNAME

TAXREGISTRATIONNUMBER

PLACEOFBIRTH

DATEOFBIRTH

DATEOFDEATH

GENDER

DECLAREDETHNICITY

PERSONSECONDLASTNAME

LOCATIONIDENTIFIER

PARTYSITEID

HOUSETYPE

ADDRESSLINE1

ADDRESSLINE2

ADDRESSLINE3

ADDRESSLINE4

PHONETICADDRESSLINES

CITY

COUNTY

COUNTRYSUBENTITY

PROVINCE

COUNTRY

POSTALCODE

POSTALCODEEXT

PARTYIDENTIFIERID1

PERSONALIDENTIFICATION1

PARTYNAMEID

EMAILCONTACTPOINTID

EMAIL

PHONECONTACTPOINTID

PHONENUMBER

PHONEEXTENSION

PHONELINETYPE

CUSTOMATTRIBUTE1

CUSTOMATTRIBUTE2

CUSTOMATTRIBUTE3

CUSTOMATTRIBUTE4

CUSTOMATTRIBUTE5

CUSTOMATTRIBUTE6

CUSTOMATTRIBUTE7

CUSTOMATTRIBUTE8

CUSTOMATTRIBUTE9

CUSTOMATTRIBUTE10

Query

SQL_Statement

select

p.PARTY_T_ID PersonInterfaceIdentifier,p.BATCH_ID BatchID, p.PARTY_ID PersonIdentifier ,p.PARTY_NUMBER RegistryIdentifier,

p.person_title||' '||p.person_pre_name_adjunct||' '||p.person_first_name||' '||p.person_middle_name||

' '||adn.party_name||' '||p.person_last_name||' '||p.person_name_suffix PersonName,

p.JGZZ_FISCAL_CODE TaxRegistrationNumber,

p.PLACE_OF_BIRTH PlaceofBirth,p.DATE_OF_BIRTH DateofBirth,p.DATE_OF_DEATH DateofDeath,

p.GENDER Gender, p.DECLARED_ETHNICITY DeclaredEthnicity, p.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,

adi.PARTY_IDENTIFIER_ID PartyIdentifierID1,adi.PARTY_IDENTIFIER_VALUE PersonalIdentification1,

adn.party_name_id PartyNameID,

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,

p.ATTRIBUTE1 CustomAttribute1,p.ATTRIBUTE2 CustomAttribute2,p.ATTRIBUTE3 CustomAttribute3,

p.ATTRIBUTE4 CustomAttribute4, p.ATTRIBUTE5 CustomAttribute5,p.ATTRIBUTE6 CustomAttribute6,

p.ATTRIBUTE7 CustomAttribute7,p.ATTRIBUTE8 CustomAttribute8,p.ATTRIBUTE9 CustomAttribute9,

p.ATTRIBUTE10 CustomAttribute10

from HZ_IMP_PARTIES_T p, HZ_IMP_PARTYSITES_T ps, HZ_IMP_LOCATIONS_T loc,

HZ_IMP_CONTACTPTS_T ct1, HZ_IMP_CONTACTPTS_T ct2,

HZ_IMP_ADDTNLPARTYNAMES_T adn, HZ_IMP_ADDTNLPARTYIDS_T adi

where p.party_type = 'PERSON'

and p.party_id = ps.party_id(+)

and p.batch_id=ps.batch_id(+)

and ps.OVERALL_PRIMARY_flag(+)='Y'

and ps.location_id = loc.location_id(+)

and ps.batch_id= loc.batch_id(+)

and p.party_id = ct1.PARTY_ID(+)

and p.batch_id=ct1.batch_id(+)

and ct1.CONTACT_POINT_TYPE(+) = 'EMAIL'

and ct1.OVERALL_PRIMARY_FLAG(+) = 'Y'

and ct1.party_site_id(+) is null

and ct1.relationship_id(+) is null

and p.party_id = ct2.PARTY_ID

and p.batch_id=ct2.batch_id

and ct2.CONTACT_POINT_TYPE = 'PHONE'

and ct2.party_site_id is null

and ct2.relationship_id is null

and p.party_id = adn.party_id(+)

and p.batch_id=adn.batch_id(+)

and adn.party_name_type(+)='ALIAS'

and adn.preferred_flag(+)='Y'

and p.party_id = adi.party_id(+)

and p.batch_id=adi.batch_id(+)

and adi.party_identifier_type(+)='DRIVERS_LICENSE'