VIEW: APPS.AST_ORG_PROFILES_V

Source

View Text - Preformatted

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, org.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) ) )

View Text - HTML Formatted

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
, ORG.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) ) )