Product: | AST - TeleSales |
---|---|
Description: | |
Implementation/DBA Data: | APPS.AST_ORG_PROFILES_V |
SELECT PARTY.ROWID
, PARTY.PARTY_ID
, PARTY.PARTY_NUMBER
, PARTY.PARTY_NAME
, PARTY.PARTY_TYPE
, PARTY.VALIDATED_FLAG
, PARTY.LAST_UPDATE_DATE
, PARTY.LAST_UPDATED_BY
, PARTY.LAST_UPDATE_LOGIN
, PARTY.CREATED_BY
, PARTY.CREATION_DATE
, ORG.ORGANIZATION_PROFILE_ID
, ORG.ORGANIZATION_NAME
, ORG.KNOWN_AS
, ORG.SIC_CODE
, ORG.SIC_CODE_TYPE
, LKUP6.MEANING
, ORG.ANALYSIS_FY
, ORG.FISCAL_YEAREND_MONTH
, LKUP2.MEANING
, ORG.EMPLOYEES_TOTAL
, ORG.CURR_FY_POTENTIAL_REVENUE
, ORG.NEXT_FY_POTENTIAL_REVENUE
, ORG.TAX_REFERENCE
, ORG.YEAR_ESTABLISHED
, ORG.INTERNAL_FLAG
, ORG.PUBLIC_PRIVATE_OWNERSHIP_FLAG
, ORG.DUNS_NUMBER
, ORG.DUNS_NUMBER_C
, ORG.JGZZ_FISCAL_CODE
, ORG.LINE_OF_BUSINESS
, ORG.DB_RATING
, PARTY.STATUS
, LKUP3.MEANING
, PARTY.CATEGORY_CODE
, LKUP.MEANING
, ORG.PREF_FUNCTIONAL_CURRENCY
, PARTY.TOTAL_NUM_OF_ORDERS
, PARTY.LAST_ORDERED_DATE
, PARTY.TOTAL_ORDERED_AMOUNT
, PARTY.ORIG_SYSTEM_REFERENCE
, ORG.LEGAL_STATUS
, LKUP4.MEANING
, ORG.BUSINESS_SCOPE
, ORG.CREDIT_SCORE
, ORG.REGISTRATION_TYPE
, LKUP5.MEANING
, ORG.ORGANIZATION_NAME_PHONETIC
, ORG.ATTRIBUTE_CATEGORY
, 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.CONTENT_SOURCE_TYPE
, PARTY.OBJECT_VERSION_NUMBER
, PARTY.CREATED_BY_MODULE
, PARTY.APPLICATION_ID
, PARTY.CERTIFICATION_LEVEL
, LKUP7.MEANING CERT_LEVEL_MEAN
FROM HZ_PARTIES PARTY
, HZ_ORGANIZATION_PROFILES ORG
, AR_LOOKUPS LKUP
, AR_LOOKUPS LKUP2
, AR_LOOKUPS LKUP3
, AR_LOOKUPS LKUP4
, AR_LOOKUPS LKUP5
, AR_LOOKUPS LKUP6
, AR_LOOKUPS LKUP7
WHERE PARTY.PARTY_ID = ORG.PARTY_ID(+)
AND PARTY.PARTY_TYPE = 'ORGANIZATION'
AND LKUP.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND LKUP.LOOKUP_CODE(+) = PARTY.CATEGORY_CODE
AND LKUP2.LOOKUP_TYPE(+) = 'MONTH'
AND LKUP2.LOOKUP_CODE(+) = ORG.FISCAL_YEAREND_MONTH
AND LKUP3.LOOKUP_TYPE(+) = 'REGISTRY_STATUS'
AND LKUP3.LOOKUP_CODE(+) = PARTY.STATUS
AND LKUP4.LOOKUP_TYPE(+) = 'LEGAL_STATUS'
AND LKUP4.LOOKUP_CODE(+) = ORG.LEGAL_STATUS
AND LKUP5.LOOKUP_TYPE(+) = 'REGISTRATION_TYPE'
AND LKUP5.LOOKUP_CODE(+) = ORG.REGISTRATION_TYPE
AND LKUP6.LOOKUP_TYPE(+) = 'SIC_CODE_TYPE'
AND LKUP6.LOOKUP_CODE(+) = ORG.SIC_CODE_TYPE
AND LKUP7.LOOKUP_TYPE(+) = 'HZ_PARTY_CERT_LEVEL'
AND LKUP7.LOOKUP_CODE(+) = PARTY.CERTIFICATION_LEVEL
AND PARTY.STATUS IN ('A'
, 'I')
AND ( ORG.EFFECTIVE_START_DATE IS NULL OR ( ORG.EFFECTIVE_START_DATE IS NOT NULL
AND SYSDATE BETWEEN ORG.EFFECTIVE_START_DATE AND NVL(ORG.EFFECTIVE_END_DATE
, SYSDATE) ) )