[Home] [Help]
| Product: | ASF - Sales Online |
|---|---|
| Description: | View for Organization |
| Implementation/DBA Data: | Not implemented in this database |
SELECT ORG.PARTY_ID
, ORG.PARTY_ID
, ORG.PARTY_NAME
, ORG.ORGANIZATION_NAME_PHONETIC
, ORG.PARTY_NUMBER
, ORG.ORIG_SYSTEM_REFERENCE
, ORG.TAX_REFERENCE
, ORG.STATUS
, ORG.TOTAL_ORDERED_AMOUNT
, ORG.SIC_CODE
, ORG.SIC_CODE_TYPE
, ORG.CATEGORY_CODE
, AL2.MEANING
, ORG.URL
, OP.DUNS_NUMBER
, OP.CURR_FY_POTENTIAL_REVENUE
, OP.LINE_OF_BUSINESS
, ORG.ADDRESS1|| DECODE(ORG.ADDRESS2
, NULL
, ''
, ' '||ORG.ADDRESS2)|| DECODE(ORG.ADDRESS3
, NULL
, ''
, ' '||ORG.ADDRESS3)|| DECODE(ORG.ADDRESS4
, NULL
, ''
, ' '||ORG.ADDRESS4)|| '
, '||ORG.CITY||'
, '||ORG.STATE||'
, '||ORG.POSTAL_CODE||'
, '||ORG.COUNTRY
, ORG.ADDRESS1|| DECODE(ORG.ADDRESS2
, NULL
, ''
, ' '||ORG.ADDRESS2)|| DECODE(ORG.ADDRESS3
, NULL
, ''
, ' '||ORG.ADDRESS3)|| DECODE(ORG.ADDRESS4
, NULL
, ''
, ' '||ORG.ADDRESS4)
, ORG.STATE
, ORG.CITY
, ORG.PROVINCE
, ORG.COUNTY
, ORG.POSTAL_CODE
, ORG.COUNTRY
, CP.PHONE_COUNTRY_CODE || DECODE(CP.PHONE_AREA_CODE
, NULL
, ''
, ' '||CP.PHONE_AREA_CODE) || DECODE(CP.PHONE_NUMBER
, NULL
, ''
, ' '||CP.PHONE_NUMBER) || DECODE(CP.PHONE_EXTENSION
, NULL
, ''
, ' EXT. '||CP.PHONE_EXTENSION)
, CP.PHONE_COUNTRY_CODE
, CP.PHONE_AREA_CODE
, CP.PHONE_NUMBER
, CP.PHONE_EXTENSION
, CP.PHONE_LINE_TYPE
, ORG.KNOWN_AS
, OP.PREF_FUNCTIONAL_CURRENCY
, OP.EMPLOYEES_TOTAL
, ORG.YEAR_ESTABLISHED
, ORG.EMAIL_ADDRESS
, OP.INTERNAL_FLAG
, ORG.FISCAL_YEAREND_MONTH
, ORG.LAST_ORDERED_DATE
, ORG.CREATION_DATE
, ORG.LAST_UPDATE_DATE
, ORG.CREATED_BY
, ORG.LAST_UPDATED_BY
, ORG.ATTRIBUTE1
, ORG.ATTRIBUTE2
, ORG.ATTRIBUTE3
, ORG.ATTRIBUTE4
, ORG.ATTRIBUTE5
, ORG.ATTRIBUTE6
, ORG.ATTRIBUTE7
, ORG.ATTRIBUTE8
, ORG.ATTRIBUTE9
, ORG.ATTRIBUTE10
, ORG.ATTRIBUTE11
, ORG.ATTRIBUTE12
, ORG.ATTRIBUTE13
, ORG.ATTRIBUTE14
, ORG.ATTRIBUTE15
, ORG.ATTRIBUTE16
, ORG.ATTRIBUTE17
, ORG.ATTRIBUTE18
, ORG.ATTRIBUTE19
, ORG.ATTRIBUTE20
, ORG.ATTRIBUTE21
, ORG.ATTRIBUTE22
, ORG.ATTRIBUTE23
, ORG.ATTRIBUTE24
FROM HZ_PARTIES ORG
, HZ_ORGANIZATION_PROFILES OP
, HZ_CONTACT_POINTS CP
, AR_LOOKUPS AL2
WHERE ORG.PARTY_TYPE = 'ORGANIZATION'
AND ORG.PARTY_ID = OP.PARTY_ID (+)
AND ORG.PARTY_ID = CP.OWNER_TABLE_ID (+)
AND CP.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CP.CONTACT_POINT_TYPE (+) = 'PHONE'
AND CP.PRIMARY_FLAG (+) = 'Y'
AND AL2.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND ORG.CATEGORY_CODE = AL2.LOOKUP_CODE(+)
AND AL2.ENABLED_FLAG(+) = 'Y'
AND OP.EFFECTIVE_END_DATE IS NULL
AND ORG.STATUS IN ('A'
, 'I')
AND DECODE(CP.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1