HZ_DATA_CLD_ORG_ENRICH_V

Details

• Schema: FUSION

• Object owner: HZ

• Object type: VIEW

Columns

Name

PARTY_ID PARTY_NUMBER URL PARTY_NAME SIC_CODE IDEN_ADDR_PARTY_SITE_ID IDEN_ADDR_LOCATION_ID COUNTRY ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 CITY POSTAL_CODE STATE PROVINCE COUNTY SIC_CODE_TYPE 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 ORGANIZATION_PROFILE_ID CEO_NAME CEO_TITLE LEGAL_STATUS CONTROL_YR EMPLOYEES_TOTAL OOB_IND LINE_OF_BUSINESS LABOR_SURPLUS_IND MINORITY_OWNED_IND MINORITY_OWNED_TYPE WOMAN_OWNED_IND DISADV_8A_IND SMALL_BUS_IND DB_RATING TOTAL_PAYMENTS LAST_UPDATE_DATE LAST_UPDATED_BY CREATION_DATE CREATED_BY LAST_UPDATE_LOGIN ORGANIZATION_NAME DUNS_NUMBER ENQUIRY_DUNS PRINCIPAL_NAME PRINCIPAL_TITLE HQ_BRANCH_IND BRANCH_FLAG CONG_DIST_CODE RENT_OWN_IND GSA_INDICATOR_FLAG JGZZ_FISCAL_CODE ANALYSIS_FY FISCAL_YEAREND_MONTH CURR_FY_POTENTIAL_REVENUE NEXT_FY_POTENTIAL_REVENUE YEAR_ESTABLISHED MISSION_STATEMENT ORGANIZATION_TYPE BUSINESS_SCOPE CORPORATION_CLASS LOCAL_BUS_IDEN_TYPE LOCAL_BUS_IDENTIFIER PREF_FUNCTIONAL_CURRENCY REGISTRATION_TYPE TOTAL_EMPLOYEES_TEXT TOTAL_EMPLOYEES_IND TOTAL_EMP_EST_IND TOTAL_EMP_MIN_IND PARENT_SUB_IND INCORP_YEAR EFFECTIVE_START_DATE EFFECTIVE_END_DATE PUBLIC_PRIVATE_OWNERSHIP_FLAG LOCAL_ACTIVITY_CODE_TYPE LOCAL_ACTIVITY_CODE EMP_AT_PRIMARY_ADR EMP_AT_PRIMARY_ADR_TEXT EMP_AT_PRIMARY_ADR_EST_IND EMP_AT_PRIMARY_ADR_MIN_IND DISPLAYED_DUNS_PARTY_ID BUSINESS_REPORT DUNS_NUMBER_C BANK_OR_BRANCH_NUMBER BANK_CODE BRANCH_CODE DO_NOT_CONFUSE_WITH ACTUAL_CONTENT_SOURCE HOME_COUNTRY TRADING_PARTNER_IDENTIFIER COMMENTS STOCK_SYMBOL GROWTH_STRATEGY_DESC ORGANIZATION_SIZE UNIQUE_NAME_ALIAS UNIQUE_NAME_SUFFIX SUFFIX_OVERRIDDEN_FLAG PARENT_DUNS_NUM_C GLOBAL_ULTIMATE_DUNS_NUM_C DOMESTIC_ULTIMATE_DUNS_NUM_C CERTIFICATION_LEVEL CERT_REASON_CODE PREFERRED_CONTACT_METHOD PREFERRED_CONTACT_PERSON_ID LAST_ENRICHMENT_DATE DATA_CLOUD_STATUS DAAS_IDENTIFIER

Query

SQL_Statement

SELECT parties.PARTY_ID, parties.PARTY_NUMBER, parties.URL , parties.PARTY_NAME, parties.SIC_CODE, 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.COUNTY, parties.SIC_CODE_TYPE, 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, orgProfiles.ORGANIZATION_PROFILE_ID, orgProfiles.CEO_NAME, orgProfiles.CEO_TITLE, orgProfiles.LEGAL_STATUS, orgProfiles.CONTROL_YR, orgProfiles.EMPLOYEES_TOTAL, orgProfiles.OOB_IND, orgProfiles.LINE_OF_BUSINESS, orgProfiles.LABOR_SURPLUS_IND, orgProfiles.MINORITY_OWNED_IND, orgProfiles.MINORITY_OWNED_TYPE, orgProfiles.WOMAN_OWNED_IND, orgProfiles.DISADV_8A_IND, orgProfiles.SMALL_BUS_IND, orgProfiles.DB_RATING, orgProfiles.TOTAL_PAYMENTS, orgProfiles.LAST_UPDATE_DATE, orgProfiles.LAST_UPDATED_BY, orgProfiles.CREATION_DATE, orgProfiles.CREATED_BY, orgProfiles.LAST_UPDATE_LOGIN, orgProfiles.ORGANIZATION_NAME, orgProfiles.DUNS_NUMBER, orgProfiles.ENQUIRY_DUNS, orgProfiles.PRINCIPAL_NAME, orgProfiles.PRINCIPAL_TITLE, orgProfiles.HQ_BRANCH_IND, orgProfiles.BRANCH_FLAG, orgProfiles.CONG_DIST_CODE, orgProfiles.RENT_OWN_IND, orgProfiles.GSA_INDICATOR_FLAG, orgProfiles.JGZZ_FISCAL_CODE, orgProfiles.ANALYSIS_FY, orgProfiles.FISCAL_YEAREND_MONTH, orgProfiles.CURR_FY_POTENTIAL_REVENUE, orgProfiles.NEXT_FY_POTENTIAL_REVENUE, orgProfiles.YEAR_ESTABLISHED, orgProfiles.MISSION_STATEMENT, orgProfiles.ORGANIZATION_TYPE, orgProfiles.BUSINESS_SCOPE, orgProfiles.CORPORATION_CLASS, orgProfiles.LOCAL_BUS_IDEN_TYPE, orgProfiles.LOCAL_BUS_IDENTIFIER, orgProfiles.PREF_FUNCTIONAL_CURRENCY, orgProfiles.REGISTRATION_TYPE, orgProfiles.TOTAL_EMPLOYEES_TEXT, orgProfiles.TOTAL_EMPLOYEES_IND, orgProfiles.TOTAL_EMP_EST_IND, orgProfiles.TOTAL_EMP_MIN_IND, orgProfiles.PARENT_SUB_IND, orgProfiles.INCORP_YEAR, orgProfiles.EFFECTIVE_START_DATE, orgProfiles.EFFECTIVE_END_DATE, orgProfiles.PUBLIC_PRIVATE_OWNERSHIP_FLAG, orgProfiles.LOCAL_ACTIVITY_CODE_TYPE, orgProfiles.LOCAL_ACTIVITY_CODE, orgProfiles.EMP_AT_PRIMARY_ADR, orgProfiles.EMP_AT_PRIMARY_ADR_TEXT, orgProfiles.EMP_AT_PRIMARY_ADR_EST_IND, orgProfiles.EMP_AT_PRIMARY_ADR_MIN_IND, orgProfiles.DISPLAYED_DUNS_PARTY_ID, orgProfiles.BUSINESS_REPORT, orgProfiles.DUNS_NUMBER_C, orgProfiles.BANK_OR_BRANCH_NUMBER, orgProfiles.BANK_CODE, orgProfiles.BRANCH_CODE, orgProfiles.DO_NOT_CONFUSE_WITH, orgProfiles.ACTUAL_CONTENT_SOURCE, orgProfiles.HOME_COUNTRY, orgProfiles.TRADING_PARTNER_IDENTIFIER, orgProfiles.COMMENTS, orgProfiles.STOCK_SYMBOL, orgProfiles.GROWTH_STRATEGY_DESC, orgProfiles.ORGANIZATION_SIZE, orgProfiles.UNIQUE_NAME_ALIAS, orgProfiles.UNIQUE_NAME_SUFFIX, orgProfiles.SUFFIX_OVERRIDDEN_FLAG, orgProfiles.PARENT_DUNS_NUM_C, orgProfiles.GLOBAL_ULTIMATE_DUNS_NUM_C, orgProfiles.DOMESTIC_ULTIMATE_DUNS_NUM_C, orgProfiles.CERTIFICATION_LEVEL, orgProfiles.CERT_REASON_CODE, orgProfiles.PREFERRED_CONTACT_METHOD, orgProfiles.PREFERRED_CONTACT_PERSON_ID, orgProfiles.LAST_ENRICHMENT_DATE, orgProfiles.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 FROM hz_parties parties, hz_organization_profiles orgProfiles, hz_orig_sys_references ssm WHERE parties.party_id = orgProfiles.party_id AND parties.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(+))