VIEW: APPS.AMS_ORG_DETAILS_V

Source

View Text - Preformatted

SELECT HP.PARTY_ID ,HP.PARTY_NUMBER ,HP.PARTY_NAME ,HP.PARTY_TYPE ,HP.STATUS ,hps.PARTY_SITE_ID ,hps.PARTY_SITE_NAME ,hps.REGION ,hps.MAILSTOP ,hl.LANGUAGE LANGUAGE ,hps.IDENTIFYING_ADDRESS_FLAG ,HP.ORIG_SYSTEM_REFERENCE ,HP.CREATION_DATE ,HP.CREATED_BY ,HP.LAST_UPDATE_DATE ,HP.LAST_UPDATED_BY ,HP.LAST_UPDATE_LOGIN ,HP.PROGRAM_UPDATE_DATE ,HP.WH_UPDATE_DATE ,HP.REQUEST_ID ,HP.PROGRAM_ID ,HP.PROGRAM_APPLICATION_ID ,HP.SALUTATION ,HP.KNOWN_AS ,HP.ADDRESS1 ,HP.ADDRESS2 ,HP.ADDRESS3 ,HP.ADDRESS4 ,HP.CITY ,HP.STATE ,HP.POSTAL_CODE ,HP.PROVINCE ,HP.COUNTY ,HP.COUNTRY ,'' ,HP.TAX_NAME ,HP.TAX_REFERENCE ,HP.CUSTOMER_KEY ,HP.GROUP_TYPE ,HP.VALIDATED_FLAG ,HP.JGZZ_FISCAL_CODE ,HP.DUNS_NUMBER ,HP.TOTAL_NUM_OF_ORDERS ,HP.TOTAL_ORDERED_AMOUNT ,HP.LAST_ORDERED_DATE ,HP.DO_NOT_MAIL_FLAG ,HP.REFERENCE_USE_FLAG ,HP.GSA_INDICATOR_FLAG ,HP.LANGUAGE_NAME ,HP.URL ,HOP.ORGANIZATION_PROFILE_ID ,HOP.ORGANIZATION_NAME ,HOP.CONTENT_SOURCE_TYPE ,HOP.EFFECTIVE_START_DATE ,HOP.EFFECTIVE_END_DATE ,HOP.CEO_NAME ,HOP.CEO_TITLE ,HOP.LEGAL_STATUS ,HOP.CONTROL_YR ,HOP.EMPLOYEES_TOTAL ,HOP.HQ_BRANCH_IND ,HOP.BRANCH_FLAG ,HOP.OOB_IND ,HOP.LINE_OF_BUSINESS ,HOP.CONG_DIST_CODE ,HOP.SIC_CODE ,HOP.SIC_CODE_TYPE ,HOP.IMPORT_IND ,HOP.EXPORT_IND ,HOP.MINORITY_OWNED_IND ,HOP.MINORITY_OWNED_TYPE ,HOP.WOMAN_OWNED_IND ,HOP.DISADV_8A_IND ,HOP.SMALL_BUS_IND ,HOP.RENT_OWN_IND ,HOP.FAILURE_SCORE ,HOP.FAILURE_SCORE_NATL_PERCENTILE ,HOP.FAILURE_SCORE_OVERRIDE_CODE ,HOP.FAILURE_SCORE_COMMENTARY ,HOP.GLOBAL_FAILURE_SCORE ,HOP.DB_RATING ,HOP.CREDIT_SCORE ,HOP.PAYDEX_SCORE ,HOP.PAYDEX_THREE_MONTHS_AGO ,HOP.PAYDEX_NORM ,HOP.PRIMARY_CONTACT_ID ,HOP.BEST_TIME_CONTACT_BEGIN ,HOP.BEST_TIME_CONTACT_END ,HOP.ANALYSIS_FY ,HOP.FISCAL_YEAREND_MONTH ,HOP.CURR_FY_POTENTIAL_REVENUE ,HOP.NEXT_FY_POTENTIAL_REVENUE ,HOP.YEAR_ESTABLISHED ,HOP.ORGANIZATION_TYPE ,HOP.CORPORATION_CLASS ,HOP.LOCAL_BUS_IDEN_TYPE ,HOP.LOCAL_BUS_IDENTIFIER ,HOP.PREF_FUNCTIONAL_CURRENCY ,HOP.REGISTRATION_TYPE ,HOP.TOTAL_EMPLOYEES_TEXT ,HOP.TOTAL_EMPLOYEES_IND ,HOP.TOTAL_EMP_EST_IND ,HOP.TOTAL_EMP_MIN_IND ,HOP.PARENT_SUB_IND ,HOP.INCORP_YEAR ,HOP.PUBLIC_PRIVATE_OWNERSHIP_FLAG ,HOP.EMP_AT_PRIMARY_ADR ,HOP.EMP_AT_PRIMARY_ADR_TEXT ,HOP.EMP_AT_PRIMARY_ADR_EST_IND ,HOP.EMP_AT_PRIMARY_ADR_MIN_IND ,HOP.INTERNAL_FLAG ,HOP.HIGH_CREDIT ,HOP.AVG_HIGH_CREDIT ,HOP.TOTAL_PAYMENTS ,hop.CONTENT_SOURCE_NUMBER ,hop.BUSINESS_SCOPE ,hop.ENQUIRY_DUNS ,hop.LABOR_SURPLUS_IND ,hop.PRINCIPAL_NAME ,hop.PRINCIPAL_TITLE ,hop.DEBARMENT_IND ,hop.DEBARMENTS_COUNT ,hop.DEBARMENTS_DATE ,hop.LOCAL_ACTIVITY_CODE_TYPE ,hop.LOCAL_ACTIVITY_CODE ,hop.CREDIT_SCORE_CLASS ,hop.CREDIT_SCORE_NATL_PERCENTILE ,hop.CREDIT_SCORE_INCD_DEFAULT ,hop.CREDIT_SCORE_AGE ,hop.CREDIT_SCORE_DATE ,hop.FAILURE_SCORE_CLASS ,hop.FAILURE_SCORE_INCD_DEFAULT ,hop.FAILURE_SCORE_AGE ,hop.FAILURE_SCORE_DATE ,hop.MAXIMUM_CREDIT_RECOMMENDATION ,hop.MAXIMUM_CREDIT_CURRENCY_CODE ,hop.DISPLAYED_DUNS_PARTY_ID ,hop.FAILURE_SCORE_NATNL_PERCENTILE ,hop.BUSINESS_REPORT ,HOP.ATTRIBUTE_CATEGORY PROFILE_ATTRIBUTE_CATEGORY ,HOP.ATTRIBUTE1 PROFILE_ATTRIBUTE1 ,HOP.ATTRIBUTE2 PROFILE_ATTRIBUTE2 ,HOP.ATTRIBUTE3 PROFILE_ATTRIBUTE3 ,HOP.ATTRIBUTE4 PROFILE_ATTRIBUTE4 ,HOP.ATTRIBUTE5 PROFILE_ATTRIBUTE5 ,HOP.ATTRIBUTE6 PROFILE_ATTRIBUTE6 ,HOP.ATTRIBUTE7 PROFILE_ATTRIBUTE7 ,HOP.ATTRIBUTE8 PROFILE_ATTRIBUTE8 ,HOP.ATTRIBUTE9 PROFILE_ATTRIBUTE9 ,HOP.ATTRIBUTE10 PROFILE_ATTRIBUTE10 ,HOP.ATTRIBUTE11 PROFILE_ATTRIBUTE11 ,HOP.ATTRIBUTE12 PROFILE_ATTRIBUTE12 ,HOP.ATTRIBUTE13 PROFILE_ATTRIBUTE13 ,HOP.ATTRIBUTE14 PROFILE_ATTRIBUTE14 ,HOP.ATTRIBUTE15 PROFILE_ATTRIBUTE15 ,HOP.ATTRIBUTE16 PROFILE_ATTRIBUTE16 ,HOP.ATTRIBUTE17 PROFILE_ATTRIBUTE17 ,HOP.ATTRIBUTE18 PROFILE_ATTRIBUTE18 ,HOP.ATTRIBUTE19 PROFILE_ATTRIBUTE19 ,HOP.ATTRIBUTE20 PROFILE_ATTRIBUTE20 ,HP.ATTRIBUTE_CATEGORY PARTY_ATTRIBUTE_CATEGORY ,HP.ATTRIBUTE1 PARTY_ATTRIBUTE1 ,HP.ATTRIBUTE2 PARTY_ATTRIBUTE2 ,HP.ATTRIBUTE3 PARTY_ATTRIBUTE3 ,HP.ATTRIBUTE4 PARTY_ATTRIBUTE4 ,HP.ATTRIBUTE5 PARTY_ATTRIBUTE5 ,HP.ATTRIBUTE6 PARTY_ATTRIBUTE6 ,HP.ATTRIBUTE7 PARTY_ATTRIBUTE7 ,HP.ATTRIBUTE8 PARTY_ATTRIBUTE8 ,HP.ATTRIBUTE9 PARTY_ATTRIBUTE9 ,HP.ATTRIBUTE10 PARTY_ATTRIBUTE10 ,HP.ATTRIBUTE11 PARTY_ATTRIBUTE11 ,HP.ATTRIBUTE12 PARTY_ATTRIBUTE12 ,HP.ATTRIBUTE13 PARTY_ATTRIBUTE13 ,HP.ATTRIBUTE14 PARTY_ATTRIBUTE14 ,HP.ATTRIBUTE15 PARTY_ATTRIBUTE15 ,HP.ATTRIBUTE16 PARTY_ATTRIBUTE16 ,HP.ATTRIBUTE17 PARTY_ATTRIBUTE17 ,HP.ATTRIBUTE18 PARTY_ATTRIBUTE18 ,HP.ATTRIBUTE19 PARTY_ATTRIBUTE19 ,HP.ATTRIBUTE20 PARTY_ATTRIBUTE20 ,HP.ATTRIBUTE21 PARTY_ATTRIBUTE21 ,HP.ATTRIBUTE22 PARTY_ATTRIBUTE22 ,HP.ATTRIBUTE23 PARTY_ATTRIBUTE23 ,HP.ATTRIBUTE24 PARTY_ATTRIBUTE24 ,hp.created_by CREATED_BY_NAME ,hp.last_update_login LAST_UPDATE_LOGIN_NAME ,hp.last_updated_by LAST_UPDATED_BY_NAME, hp.category_code ,hcp.phone_country_code ,hcp.phone_area_code ,hcp.phone_number ,hcp.phone_extension ,hp.competitor_flag ,hp.third_party_flag FROM HZ_PARTIES HP, HZ_ORGANIZATION_PROFILES HOP, HZ_PARTY_SITES hps, hz_contact_points hcp, HZ_LOCATIONS hl WHERE HP.PARTY_TYPE = 'ORGANIZATION' AND HP.STATUS = 'A' AND HP.PARTY_ID = HOP.PARTY_ID AND HOP.effective_end_date is NULL AND HOP.ACTUAL_CONTENT_SOURCE = 'SST' AND hps.PARTY_ID(+) = hp.PARTY_ID AND hps.IDENTIFYING_ADDRESS_FLAG(+) = 'Y' AND hps.LOCATION_ID = HL.LOCATION_ID(+) and hcp.contact_point_type(+) = 'PHONE' and hcp.owner_table_name(+) = 'HZ_PARTIES' and hcp.owner_table_id (+) = hp.party_id and hcp.status (+) = 'A' and hcp.primary_flag (+) = 'Y'

View Text - HTML Formatted

SELECT HP.PARTY_ID
, HP.PARTY_NUMBER
, HP.PARTY_NAME
, HP.PARTY_TYPE
, HP.STATUS
, HPS.PARTY_SITE_ID
, HPS.PARTY_SITE_NAME
, HPS.REGION
, HPS.MAILSTOP
, HL.LANGUAGE LANGUAGE
, HPS.IDENTIFYING_ADDRESS_FLAG
, HP.ORIG_SYSTEM_REFERENCE
, HP.CREATION_DATE
, HP.CREATED_BY
, HP.LAST_UPDATE_DATE
, HP.LAST_UPDATED_BY
, HP.LAST_UPDATE_LOGIN
, HP.PROGRAM_UPDATE_DATE
, HP.WH_UPDATE_DATE
, HP.REQUEST_ID
, HP.PROGRAM_ID
, HP.PROGRAM_APPLICATION_ID
, HP.SALUTATION
, HP.KNOWN_AS
, HP.ADDRESS1
, HP.ADDRESS2
, HP.ADDRESS3
, HP.ADDRESS4
, HP.CITY
, HP.STATE
, HP.POSTAL_CODE
, HP.PROVINCE
, HP.COUNTY
, HP.COUNTRY
, ''
, HP.TAX_NAME
, HP.TAX_REFERENCE
, HP.CUSTOMER_KEY
, HP.GROUP_TYPE
, HP.VALIDATED_FLAG
, HP.JGZZ_FISCAL_CODE
, HP.DUNS_NUMBER
, HP.TOTAL_NUM_OF_ORDERS
, HP.TOTAL_ORDERED_AMOUNT
, HP.LAST_ORDERED_DATE
, HP.DO_NOT_MAIL_FLAG
, HP.REFERENCE_USE_FLAG
, HP.GSA_INDICATOR_FLAG
, HP.LANGUAGE_NAME
, HP.URL
, HOP.ORGANIZATION_PROFILE_ID
, HOP.ORGANIZATION_NAME
, HOP.CONTENT_SOURCE_TYPE
, HOP.EFFECTIVE_START_DATE
, HOP.EFFECTIVE_END_DATE
, HOP.CEO_NAME
, HOP.CEO_TITLE
, HOP.LEGAL_STATUS
, HOP.CONTROL_YR
, HOP.EMPLOYEES_TOTAL
, HOP.HQ_BRANCH_IND
, HOP.BRANCH_FLAG
, HOP.OOB_IND
, HOP.LINE_OF_BUSINESS
, HOP.CONG_DIST_CODE
, HOP.SIC_CODE
, HOP.SIC_CODE_TYPE
, HOP.IMPORT_IND
, HOP.EXPORT_IND
, HOP.MINORITY_OWNED_IND
, HOP.MINORITY_OWNED_TYPE
, HOP.WOMAN_OWNED_IND
, HOP.DISADV_8A_IND
, HOP.SMALL_BUS_IND
, HOP.RENT_OWN_IND
, HOP.FAILURE_SCORE
, HOP.FAILURE_SCORE_NATL_PERCENTILE
, HOP.FAILURE_SCORE_OVERRIDE_CODE
, HOP.FAILURE_SCORE_COMMENTARY
, HOP.GLOBAL_FAILURE_SCORE
, HOP.DB_RATING
, HOP.CREDIT_SCORE
, HOP.PAYDEX_SCORE
, HOP.PAYDEX_THREE_MONTHS_AGO
, HOP.PAYDEX_NORM
, HOP.PRIMARY_CONTACT_ID
, HOP.BEST_TIME_CONTACT_BEGIN
, HOP.BEST_TIME_CONTACT_END
, HOP.ANALYSIS_FY
, HOP.FISCAL_YEAREND_MONTH
, HOP.CURR_FY_POTENTIAL_REVENUE
, HOP.NEXT_FY_POTENTIAL_REVENUE
, HOP.YEAR_ESTABLISHED
, HOP.ORGANIZATION_TYPE
, HOP.CORPORATION_CLASS
, HOP.LOCAL_BUS_IDEN_TYPE
, HOP.LOCAL_BUS_IDENTIFIER
, HOP.PREF_FUNCTIONAL_CURRENCY
, HOP.REGISTRATION_TYPE
, HOP.TOTAL_EMPLOYEES_TEXT
, HOP.TOTAL_EMPLOYEES_IND
, HOP.TOTAL_EMP_EST_IND
, HOP.TOTAL_EMP_MIN_IND
, HOP.PARENT_SUB_IND
, HOP.INCORP_YEAR
, HOP.PUBLIC_PRIVATE_OWNERSHIP_FLAG
, HOP.EMP_AT_PRIMARY_ADR
, HOP.EMP_AT_PRIMARY_ADR_TEXT
, HOP.EMP_AT_PRIMARY_ADR_EST_IND
, HOP.EMP_AT_PRIMARY_ADR_MIN_IND
, HOP.INTERNAL_FLAG
, HOP.HIGH_CREDIT
, HOP.AVG_HIGH_CREDIT
, HOP.TOTAL_PAYMENTS
, HOP.CONTENT_SOURCE_NUMBER
, HOP.BUSINESS_SCOPE
, HOP.ENQUIRY_DUNS
, HOP.LABOR_SURPLUS_IND
, HOP.PRINCIPAL_NAME
, HOP.PRINCIPAL_TITLE
, HOP.DEBARMENT_IND
, HOP.DEBARMENTS_COUNT
, HOP.DEBARMENTS_DATE
, HOP.LOCAL_ACTIVITY_CODE_TYPE
, HOP.LOCAL_ACTIVITY_CODE
, HOP.CREDIT_SCORE_CLASS
, HOP.CREDIT_SCORE_NATL_PERCENTILE
, HOP.CREDIT_SCORE_INCD_DEFAULT
, HOP.CREDIT_SCORE_AGE
, HOP.CREDIT_SCORE_DATE
, HOP.FAILURE_SCORE_CLASS
, HOP.FAILURE_SCORE_INCD_DEFAULT
, HOP.FAILURE_SCORE_AGE
, HOP.FAILURE_SCORE_DATE
, HOP.MAXIMUM_CREDIT_RECOMMENDATION
, HOP.MAXIMUM_CREDIT_CURRENCY_CODE
, HOP.DISPLAYED_DUNS_PARTY_ID
, HOP.FAILURE_SCORE_NATNL_PERCENTILE
, HOP.BUSINESS_REPORT
, HOP.ATTRIBUTE_CATEGORY PROFILE_ATTRIBUTE_CATEGORY
, HOP.ATTRIBUTE1 PROFILE_ATTRIBUTE1
, HOP.ATTRIBUTE2 PROFILE_ATTRIBUTE2
, HOP.ATTRIBUTE3 PROFILE_ATTRIBUTE3
, HOP.ATTRIBUTE4 PROFILE_ATTRIBUTE4
, HOP.ATTRIBUTE5 PROFILE_ATTRIBUTE5
, HOP.ATTRIBUTE6 PROFILE_ATTRIBUTE6
, HOP.ATTRIBUTE7 PROFILE_ATTRIBUTE7
, HOP.ATTRIBUTE8 PROFILE_ATTRIBUTE8
, HOP.ATTRIBUTE9 PROFILE_ATTRIBUTE9
, HOP.ATTRIBUTE10 PROFILE_ATTRIBUTE10
, HOP.ATTRIBUTE11 PROFILE_ATTRIBUTE11
, HOP.ATTRIBUTE12 PROFILE_ATTRIBUTE12
, HOP.ATTRIBUTE13 PROFILE_ATTRIBUTE13
, HOP.ATTRIBUTE14 PROFILE_ATTRIBUTE14
, HOP.ATTRIBUTE15 PROFILE_ATTRIBUTE15
, HOP.ATTRIBUTE16 PROFILE_ATTRIBUTE16
, HOP.ATTRIBUTE17 PROFILE_ATTRIBUTE17
, HOP.ATTRIBUTE18 PROFILE_ATTRIBUTE18
, HOP.ATTRIBUTE19 PROFILE_ATTRIBUTE19
, HOP.ATTRIBUTE20 PROFILE_ATTRIBUTE20
, HP.ATTRIBUTE_CATEGORY PARTY_ATTRIBUTE_CATEGORY
, HP.ATTRIBUTE1 PARTY_ATTRIBUTE1
, HP.ATTRIBUTE2 PARTY_ATTRIBUTE2
, HP.ATTRIBUTE3 PARTY_ATTRIBUTE3
, HP.ATTRIBUTE4 PARTY_ATTRIBUTE4
, HP.ATTRIBUTE5 PARTY_ATTRIBUTE5
, HP.ATTRIBUTE6 PARTY_ATTRIBUTE6
, HP.ATTRIBUTE7 PARTY_ATTRIBUTE7
, HP.ATTRIBUTE8 PARTY_ATTRIBUTE8
, HP.ATTRIBUTE9 PARTY_ATTRIBUTE9
, HP.ATTRIBUTE10 PARTY_ATTRIBUTE10
, HP.ATTRIBUTE11 PARTY_ATTRIBUTE11
, HP.ATTRIBUTE12 PARTY_ATTRIBUTE12
, HP.ATTRIBUTE13 PARTY_ATTRIBUTE13
, HP.ATTRIBUTE14 PARTY_ATTRIBUTE14
, HP.ATTRIBUTE15 PARTY_ATTRIBUTE15
, HP.ATTRIBUTE16 PARTY_ATTRIBUTE16
, HP.ATTRIBUTE17 PARTY_ATTRIBUTE17
, HP.ATTRIBUTE18 PARTY_ATTRIBUTE18
, HP.ATTRIBUTE19 PARTY_ATTRIBUTE19
, HP.ATTRIBUTE20 PARTY_ATTRIBUTE20
, HP.ATTRIBUTE21 PARTY_ATTRIBUTE21
, HP.ATTRIBUTE22 PARTY_ATTRIBUTE22
, HP.ATTRIBUTE23 PARTY_ATTRIBUTE23
, HP.ATTRIBUTE24 PARTY_ATTRIBUTE24
, HP.CREATED_BY CREATED_BY_NAME
, HP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN_NAME
, HP.LAST_UPDATED_BY LAST_UPDATED_BY_NAME
, HP.CATEGORY_CODE
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, HP.COMPETITOR_FLAG
, HP.THIRD_PARTY_FLAG
FROM HZ_PARTIES HP
, HZ_ORGANIZATION_PROFILES HOP
, HZ_PARTY_SITES HPS
, HZ_CONTACT_POINTS HCP
, HZ_LOCATIONS HL
WHERE HP.PARTY_TYPE = 'ORGANIZATION'
AND HP.STATUS = 'A'
AND HP.PARTY_ID = HOP.PARTY_ID
AND HOP.EFFECTIVE_END_DATE IS NULL
AND HOP.ACTUAL_CONTENT_SOURCE = 'SST'
AND HPS.PARTY_ID(+) = HP.PARTY_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND HPS.LOCATION_ID = HL.LOCATION_ID(+)
AND HCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND HCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP.OWNER_TABLE_ID (+) = HP.PARTY_ID
AND HCP.STATUS (+) = 'A'
AND HCP.PRIMARY_FLAG (+) = 'Y'