View: AMS_ORG_DETAILS_V

Product:	AMS - Marketing
Description:	This view list the details of an Organization
Implementation/DBA Data:	APPS.AMS_ORG_DETAILS_V

View Text

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'

Columns