HZ_DATA_CLD_PERSON_ENRICH_V

Details

• Schema: FUSION

• Object owner: HZ

• Object type: VIEW

Columns

Name

PARTY_ID PARTY_NUMBER URL PARTY_NAME IDEN_ADDR_PARTY_SITE_ID IDEN_ADDR_LOCATION_ID COUNTRY ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 CITY POSTAL_CODE STATE PROVINCE STATUS COUNTY EMAIL_ADDRESS PRIMARY_PHONE_CONTACT_PT_ID PRIMARY_PHONE_PURPOSE PRIMARY_PHONE_LINE_TYPE PRIMARY_PHONE_COUNTRY_CODE PRIMARY_PHONE_AREA_CODE PRIMARY_PHONE_NUMBER PRIMARY_PHONE_EXTENSION PRIMARY_EMAIL_CONTACT_PT_ID PRIMARY_URL_CONTACT_PT_ID PERSON_PROFILE_ID LAST_UPDATED_BY CREATION_DATE LAST_UPDATE_LOGIN CREATED_BY LAST_UPDATE_DATE PERSON_NAME PERSON_PRE_NAME_ADJUNCT PERSON_FIRST_NAME PERSON_MIDDLE_NAME PERSON_LAST_NAME PERSON_NAME_SUFFIX PERSON_TITLE PERSON_ACADEMIC_TITLE PERSON_PREVIOUS_LAST_NAME PERSON_INITIALS JGZZ_FISCAL_CODE DATE_OF_BIRTH PLACE_OF_BIRTH DATE_OF_DEATH GENDER DECLARED_ETHNICITY MARITAL_STATUS MARITAL_STATUS_EFFECTIVE_DATE PERSONAL_INCOME HEAD_OF_HOUSEHOLD_FLAG HOUSEHOLD_INCOME HOUSEHOLD_SIZE RENT_OWN_IND LAST_KNOWN_GPS EFFECTIVE_START_DATE EFFECTIVE_END_DATE DECEASED_FLAG PERSON_LAST_NAME_PREFIX PERSON_SECOND_LAST_NAME PREF_FUNCTIONAL_CURRENCY COMMENTS UNIQUE_NAME_SUFFIX SUFFIX_OVERRIDDEN_FLAG SALUTATION CERTIFICATION_LEVEL CERT_REASON_CODE PREFERRED_CONTACT_METHOD PREFERRED_CONTACT_PERSON_ID PRIMARY_CUSTOMER_ID PRIMARY_CUST_RELATIONSHIP_ID SALES_BUYING_ROLE_CODE SALES_AFFINITY_CODE DEPARTMENT_CODE DEPARTMENT JOB_TITLE JOB_TITLE_CODE DO_NOT_CALL_FLAG DO_NOT_MAIL_FLAG DO_NOT_EMAIL_FLAG DO_NOT_CONTACT_FLAG LAST_CONTACT_DATE LAST_UPDATE_SOURCE_SYSTEM LAST_SOURCE_UPDATE_DATE LAST_ENRICHMENT_DATE DATA_CLOUD_STATUS DAAS_IDENTIFIER ORG_PARTY_ID ORG_PARTY_NUMBER ORG_PARTY_NAME ORG_IDEN_ADDR_PS_ID ORG_IDEN_ADDR_LOC_ID ORG_COUNTRY ORG_ADDRESS1 ORG_ADDRESS2 ORG_ADDRESS3 ORG_ADDRESS4 ORG_CITY ORG_POSTAL_CODE ORG_STATE ORG_PROVINCE ORG_COUNTY ORG_EMAIL_ADDRESS ORGANIZATION_PROFILE_ID ORGANIZATION_NAME ORG_DUNS_NUMBER ORG_DUNS_NUMBER_C ORG_ENQUIRY_DUNS ORG_PARENT_DUNS_NUM_C ORG_GLOBAL_ULT_DUNS_NUM_C ORG_DOMESTIC_ULT_DUNS_NUM_C ORG_LAST_ENRICHMENT_DATE ORG_DATA_CLOUD_STATUS ORG_DAAS_IDENTIFIER

Query

SQL_Statement

SELECT parties.PARTY_ID, parties.PARTY_NUMBER, parties.URL, parties.PARTY_NAME, parties.IDEN_ADDR_PARTY_SITE_ID, parties.IDEN_ADDR_LOCATION_ID, parties.COUNTRY, parties.ADDRESS1, parties.ADDRESS2, parties.ADDRESS3, parties.ADDRESS4, parties.CITY, parties.POSTAL_CODE, parties.STATE, parties.PROVINCE, parties.STATUS, parties.COUNTY, parties.EMAIL_ADDRESS, parties.PRIMARY_PHONE_CONTACT_PT_ID, parties.PRIMARY_PHONE_PURPOSE, parties.PRIMARY_PHONE_LINE_TYPE, parties.PRIMARY_PHONE_COUNTRY_CODE, parties.PRIMARY_PHONE_AREA_CODE, parties.PRIMARY_PHONE_NUMBER, parties.PRIMARY_PHONE_EXTENSION, parties.PRIMARY_EMAIL_CONTACT_PT_ID, parties.PRIMARY_URL_CONTACT_PT_ID, personProfiles.PERSON_PROFILE_ID, personProfiles.LAST_UPDATED_BY, personProfiles.CREATION_DATE, personProfiles.LAST_UPDATE_LOGIN, personProfiles.CREATED_BY, personProfiles.LAST_UPDATE_DATE, personProfiles.PERSON_NAME, personProfiles.PERSON_PRE_NAME_ADJUNCT, personProfiles.PERSON_FIRST_NAME, personProfiles.PERSON_MIDDLE_NAME, personProfiles.PERSON_LAST_NAME, personProfiles.PERSON_NAME_SUFFIX, personProfiles.PERSON_TITLE, personProfiles.PERSON_ACADEMIC_TITLE, personProfiles.PERSON_PREVIOUS_LAST_NAME, personProfiles.PERSON_INITIALS, personProfiles.JGZZ_FISCAL_CODE, personProfiles.DATE_OF_BIRTH, personProfiles.PLACE_OF_BIRTH, personProfiles.DATE_OF_DEATH, personProfiles.GENDER, personProfiles.DECLARED_ETHNICITY, personProfiles.MARITAL_STATUS, personProfiles.MARITAL_STATUS_EFFECTIVE_DATE, personProfiles.PERSONAL_INCOME, personProfiles.HEAD_OF_HOUSEHOLD_FLAG, personProfiles.HOUSEHOLD_INCOME, personProfiles.HOUSEHOLD_SIZE, personProfiles.RENT_OWN_IND, personProfiles.LAST_KNOWN_GPS, personProfiles.EFFECTIVE_START_DATE, personProfiles.EFFECTIVE_END_DATE, personProfiles.DECEASED_FLAG, personProfiles.PERSON_LAST_NAME_PREFIX, personProfiles.PERSON_SECOND_LAST_NAME, personProfiles.PREF_FUNCTIONAL_CURRENCY, personProfiles.COMMENTS, personProfiles.UNIQUE_NAME_SUFFIX, personProfiles.SUFFIX_OVERRIDDEN_FLAG, personProfiles.SALUTATION, personProfiles.CERTIFICATION_LEVEL, personProfiles.CERT_REASON_CODE, personProfiles.PREFERRED_CONTACT_METHOD, personProfiles.PREFERRED_CONTACT_PERSON_ID, personProfiles.PRIMARY_CUSTOMER_ID, personProfiles.PRIMARY_CUST_RELATIONSHIP_ID, personProfiles.SALES_BUYING_ROLE_CODE, personProfiles.SALES_AFFINITY_CODE, personProfiles.DEPARTMENT_CODE, personProfiles.DEPARTMENT, personProfiles.JOB_TITLE, personProfiles.JOB_TITLE_CODE, personProfiles.DO_NOT_CALL_FLAG, personProfiles.DO_NOT_MAIL_FLAG, personProfiles.DO_NOT_EMAIL_FLAG, personProfiles.DO_NOT_CONTACT_FLAG, personProfiles.LAST_CONTACT_DATE, personProfiles.LAST_UPDATE_SOURCE_SYSTEM, personProfiles.LAST_SOURCE_UPDATE_DATE, personProfiles.LAST_ENRICHMENT_DATE, personProfiles.DATA_CLOUD_STATUS DATA_CLOUD_STATUS, DECODE(SSM.ORIG_SYSTEM, 'DAAS', DECODE(INSTR(SSM.ORIG_SYSTEM_REFERENCE,'_'||PARTIES.PARTY_ID), 0, SSM.ORIG_SYSTEM_REFERENCE, RTRIM(RTRIM(SSM.ORIG_SYSTEM_REFERENCE, PARTIES.PARTY_ID), '_')), ssm.orig_system_reference) DAAS_IDENTIFIER, org.PARTY_ID ORG_PARTY_ID, org.PARTY_NUMBER ORG_PARTY_NUMBER, org.PARTY_NAME ORG_PARTY_NAME, org.IDEN_ADDR_PARTY_SITE_ID ORG_IDEN_ADDR_PS_ID, org.IDEN_ADDR_LOCATION_ID ORG_IDEN_ADDR_LOC_ID, org.COUNTRY ORG_COUNTRY, org.ADDRESS1 ORG_ADDRESS1, org.ADDRESS2 ORG_ADDRESS2, org.ADDRESS3 ORG_ADDRESS3, org.ADDRESS4 ORG_ADDRESS4, org.CITY ORG_CITY, org.POSTAL_CODE ORG_POSTAL_CODE, org.STATE ORG_STATE, org.PROVINCE ORG_PROVINCE, org.COUNTY ORG_COUNTY, org.EMAIL_ADDRESS ORG_EMAIL_ADDRESS, orgProfile.ORGANIZATION_PROFILE_ID, orgProfile.ORGANIZATION_NAME, orgProfile.DUNS_NUMBER ORG_DUNS_NUMBER, orgProfile.DUNS_NUMBER_C ORG_DUNS_NUMBER_C, orgProfile.ENQUIRY_DUNS ORG_ENQUIRY_DUNS, orgProfile.PARENT_DUNS_NUM_C ORG_PARENT_DUNS_NUM_C, orgProfile.GLOBAL_ULTIMATE_DUNS_NUM_C ORG_GLOBAL_ULT_DUNS_NUM_C, orgProfile.DOMESTIC_ULTIMATE_DUNS_NUM_C ORG_DOMESTIC_ULT_DUNS_NUM_C, orgProfile.LAST_ENRICHMENT_DATE ORG_LAST_ENRICHMENT_DATE, orgProfile.DATA_CLOUD_STATUS ORG_DATA_CLOUD_STATUS, DECODE(ORG_SSM.ORIG_SYSTEM, 'DAAS', DECODE(INSTR(org_ssm.ORIG_SYSTEM_REFERENCE,'_'||org.PARTY_ID), 0, org_ssm.ORIG_SYSTEM_REFERENCE, RTRIM(RTRIM(org_ssm.ORIG_SYSTEM_REFERENCE, org.PARTY_ID), '_')), org_ssm.orig_system_reference) ORG_DAAS_IDENTIFIER FROM hz_parties parties, hz_person_profiles personProfiles, hz_parties org, hz_organization_profiles orgProfile, hz_orig_sys_references ssm, hz_orig_sys_references org_ssm WHERE parties.party_id = personProfiles.party_id AND parties.party_type = 'PERSON' AND personProfiles.primary_customer_id = org.party_id AND org.party_type = 'ORGANIZATION' AND parties.status = 'A' AND ssm.owner_table_id (+) = parties.party_id AND ssm.owner_table_name (+) = 'HZ_PARTIES' AND ssm.orig_system (+) = 'DAAS' AND ssm.status(+) = 'A' AND TRUNC(SYSDATE) BETWEEN TRUNC(ssm.start_date_active(+)) AND TRUNC(ssm.end_date_active(+)) AND orgProfile.party_id = org.party_id AND org_ssm.owner_table_id (+) = personProfiles.primary_customer_id AND org_ssm.owner_table_name (+) = 'HZ_PARTIES' AND org_ssm.orig_system (+) = 'DAAS' AND org_ssm.status(+) = 'A' AND TRUNC(SYSDATE) BETWEEN TRUNC(org_ssm.start_date_active(+)) AND TRUNC(org_ssm.end_date_active(+))