Product: | ASF - Sales Online |
---|---|
Description: | View for Organization Create |
Implementation/DBA Data: | Not implemented in this database |
SELECT ORG.PARTY_ID
, ORG.PARTY_ID
, ORG.PARTY_TYPE
, ORG.PARTY_NAME
, ORG.PARTY_NUMBER
, ORG.TAX_REFERENCE
, ORG.STATUS
, ORG.SIC_CODE_TYPE
, ORG.CATEGORY_CODE
, ORG.URL
, PROFILE.DUNS_NUMBER
, PROFILE.CURR_FY_POTENTIAL_REVENUE
, PROFILE.LINE_OF_BUSINESS
, CNT_POINT_PHONE.PHONE_COUNTRY_CODE
, CNT_POINT_PHONE.PHONE_AREA_CODE
, CNT_POINT_PHONE.PHONE_NUMBER
, CNT_POINT_PHONE.PHONE_EXTENSION
, CNT_POINT_PHONE.PHONE_LINE_TYPE
, ORG.KNOWN_AS
, PROFILE.PREF_FUNCTIONAL_CURRENCY
, PROFILE.TOTAL_EMPLOYEES_TEXT
, ORG.YEAR_ESTABLISHED
, ORG.EMAIL_ADDRESS
, PROFILE.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.TOTAL_ORDERED_AMOUNT
, ORG.LAST_ORDERED_DATE
, CNT_POINT_PHONE.CONTACT_POINT_ID
, CNT_POINT_PHONE.LAST_UPDATE_DATE
, CNT_POINT_EMAIL.CONTACT_POINT_ID
, CNT_POINT_EMAIL.LAST_UPDATE_DATE
, CNT_POINT_WEB.CONTACT_POINT_ID
, CNT_POINT_WEB.LAST_UPDATE_DATE
FROM HZ_PARTIES ORG
, HZ_ORGANIZATION_PROFILES PROFILE
, HZ_CONTACT_POINTS CNT_POINT_PHONE
, HZ_CONTACT_POINTS CNT_POINT_EMAIL
, HZ_CONTACT_POINTS CNT_POINT_WEB
WHERE ORG.PARTY_TYPE = 'ORGANIZATION'
AND ORG.PARTY_ID = PROFILE.PARTY_ID
AND ORG.PARTY_ID = CNT_POINT_PHONE.OWNER_TABLE_ID
AND 'HZ_PARTIES' = CNT_POINT_PHONE.OWNER_TABLE_NAME
AND 'PHONE' = CNT_POINT_PHONE.CONTACT_POINT_TYPE
AND 'Y' = CNT_POINT_PHONE.PRIMARY_FLAG
AND ORG.PARTY_ID = CNT_POINT_EMAIL.OWNER_TABLE_ID
AND 'HZ_PARTIES' = CNT_POINT_EMAIL.OWNER_TABLE_NAME
AND 'EMAIL' = CNT_POINT_EMAIL.CONTACT_POINT_TYPE
AND 'Y' = CNT_POINT_EMAIL.PRIMARY_FLAG
AND ORG.PARTY_ID = CNT_POINT_WEB.OWNER_TABLE_ID
AND 'HZ_PARTIES' = CNT_POINT_WEB.OWNER_TABLE_NAME
AND 'WEB' = CNT_POINT_WEB.CONTACT_POINT_TYPE
AND 'Y' = CNT_POINT_WEB.PRIMARY_FLAG
AND PROFILE.EFFECTIVE_END_DATE IS NULL
AND ORG.STATUS IN ('A'
, 'I')
AND DECODE(CNT_POINT_PHONE.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1
AND DECODE(CNT_POINT_EMAIL.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1
AND DECODE(CNT_POINT_WEB.STATUS(+)
, 'A'
, 1
, 'I'
, 1
, NULL
, 1
, -99) = 1