View: AR_CUSTOMERS_V

View Text

SELECT CUST.ROWID ROW_ID
, CUST.CUST_ACCOUNT_ID CUSTOMER_ID
, CUST_PARTY.PARTY_NAME CUSTOMER_NAME
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, CUST_PARTY.PARTY_ID
, CUST_PARTY.PARTY_NUMBER
, CUST_PARTY.PARTY_TYPE
, CUST_PARTY.CUSTOMER_KEY CUSTOMER_KEY
, CUST.STATUS STATUS
, CUST.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, 'CUSTOMER' CUSTOMER_PROSPECT_CODE
, CUST_PARTY.CATEGORY_CODE CUSTOMER_CATEGORY_CODE
, L_CAT.MEANING CUSTOMER_CATEGORY_MEANING
, CUST.CUSTOMER_CLASS_CODE CUSTOMER_CLASS_CODE
, L_CLASS.MEANING CUSTOMER_CLASS_MEANING
, CUST.CUSTOMER_TYPE CUSTOMER_TYPE
, L_TYPE.MEANING CUSTOMER_TYPE_MEANING
, CUST.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID
, REP.NAME PRIMARY_SALESREP_NAME
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.SIC_CODE
, NULL) SIC_CODE
, CUST_PARTY.TAX_REFERENCE TAX_REFERENCE
, CUST.TAX_CODE TAX_CODE
, CUST.FOB_POINT FOB_POINT
, L_FOB.MEANING FOB_POINT_MEANING
, CUST.SHIP_VIA SHIP_VIA
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.GSA_INDICATOR_FLAG
, 'N') GSA_INDICATOR
, CUST.SHIP_PARTIAL SHIP_PARTIAL
, CUST_PARTY.JGZZ_FISCAL_CODE TAXPAYER_ID
, CUST.PRICE_LIST_ID PRICE_LIST_ID
, PL.NAME PRICE_LIST_NAME
, CUST.FREIGHT_TERM FREIGHT_TERM
, L_FT.MEANING FREIGHT_TERM_MEANING
, CUST.ORDER_TYPE_ID ORDER_TYPE_ID
, OT.NAME ORDER_TYPE_NAME
, CUST.SALES_CHANNEL_CODE SALES_CHANNEL_CODE
, L_SC.MEANING SALES_CHANNEL_MEANING
, CUST.WAREHOUSE_ID WAREHOUSE_ID
, ORG.NAME WAREHOUSE_NAME
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.MISSION_STATEMENT
, NULL) MISSION_STATEMENT
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.EMPLOYEES_TOTAL
, TO_NUMBER(NULL)) NUM_OF_EMPLOYEES
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.CURR_FY_POTENTIAL_REVENUE
, TO_NUMBER(NULL)) POTENTIAL_REVENUE_CURR_FY
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.NEXT_FY_POTENTIAL_REVENUE
, TO_NUMBER(NULL)) POTENTIAL_REVENUE_NEXT_FY
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.FISCAL_YEAREND_MONTH
, NULL) FISCAL_YEAREND_MONTH
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.YEAR_ESTABLISHED
, TO_NUMBER(NULL)) YEAR_ESTABLISHED
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.ANALYSIS_FY
, NULL) ANALYSIS_FY
, CUST_PARTY.COMPETITOR_FLAG COMPETITOR_FLAG
, CUST_PARTY.REFERENCE_USE_FLAG REFERENCE_USE_FLAG
, CUST_PARTY.THIRD_PARTY_FLAG THIRD_PARTY_FLAG
, CUST.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CUST.ATTRIBUTE1 ATTRIBUTE1
, CUST.ATTRIBUTE2 ATTRIBUTE2
, CUST.ATTRIBUTE3 ATTRIBUTE3
, CUST.ATTRIBUTE4 ATTRIBUTE4
, CUST.ATTRIBUTE5 ATTRIBUTE5
, CUST.ATTRIBUTE6 ATTRIBUTE6
, CUST.ATTRIBUTE7 ATTRIBUTE7
, CUST.ATTRIBUTE8 ATTRIBUTE8
, CUST.ATTRIBUTE9 ATTRIBUTE9
, CUST.ATTRIBUTE10 ATTRIBUTE10
, CUST.ATTRIBUTE11 ATTRIBUTE11
, CUST.ATTRIBUTE12 ATTRIBUTE12
, CUST.ATTRIBUTE13 ATTRIBUTE13
, CUST.ATTRIBUTE14 ATTRIBUTE14
, CUST.ATTRIBUTE15 ATTRIBUTE15
, CUST.LAST_UPDATED_BY LAST_UPDATED_BY
, CUST.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CUST.OBJECT_VERSION_NUMBER OBJECT_VERSION
, CUST.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CUST.CREATED_BY CREATED_BY
, CUST.CREATION_DATE CREATION_DATE
, CPC.PROFILE_CLASS_ID CUSTOMER_PROFILE_CLASS_ID
, CPC.NAME PROFILE_CLASS_NAME
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.ORGANIZATION_NAME_PHONETIC
, NULL) CUSTOMER_NAME_PHONETIC
, CUST.TAX_HEADER_LEVEL_FLAG TAX_HEADER_LEVEL_FLAG
, CUST.TAX_ROUNDING_RULE TAX_ROUNDING_RULE
, CUST.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, CUST.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, CUST.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, CUST.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, CUST.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, CUST.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, CUST.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, CUST.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, CUST.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, CUST.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, CUST.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, CUST.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, CUST.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, CUST.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, CUST.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, CUST.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, CUST.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, CUST.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, CUST.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, CUST.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, CUST.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, NULL ACCESS_TEMPLATE_ENTITY_CODE
, NULL ORIG_SYSTEM
, CUST.COTERMINATE_DAY_MONTH
, CUST.WH_UPDATE_DATE
, NULL LANGUAGE
, NULL TRANSLATED_CUSTOMER_NAME
, CUST_PARTY.PERSON_PRE_NAME_ADJUNCT
, CUST_PARTY.PERSON_FIRST_NAME
, CUST_PARTY.PERSON_MIDDLE_NAME
, CUST_PARTY.PERSON_LAST_NAME
, CUST_PARTY.PERSON_NAME_SUFFIX PERSON_SUFFIX
, DECODE(CUST_PARTY.PARTY_TYPE
, 'PERSON'
, PER_PROF.PERSON_NAME_PHONETIC
, NULL) PERSON_NAME_PHONETIC
, DECODE(CUST_PARTY.PARTY_TYPE
, 'PERSON'
, CUST_PARTY.PERSON_FIRST_NAME_PHONETIC
, NULL) PERSON_FIRST_NAME_PHONETIC
, DECODE(CUST_PARTY.PARTY_TYPE
, 'PERSON'
, CUST_PARTY.PERSON_LAST_NAME_PHONETIC
, NULL) PERSON_LAST_NAME_PHONETIC
, CUST.SHIP_SETS_INCLUDE_LINES_FLAG
, CUST.ARRIVALSETS_INCLUDE_LINES_FLAG
, CUST.SCHED_DATE_PUSH_FLAG
, CUST.OVER_SHIPMENT_TOLERANCE
, CUST.UNDER_SHIPMENT_TOLERANCE
, CUST.OVER_RETURN_TOLERANCE
, CUST.UNDER_RETURN_TOLERANCE
, CUST.ITEM_CROSS_REF_PREF
, CUST.DATE_TYPE_PREFERENCE
, CUST.DATES_NEGATIVE_TOLERANCE
, CUST.DATES_POSITIVE_TOLERANCE
, CUST.INVOICE_QUANTITY_RULE
, CUST.ATTRIBUTE16
, CUST.ATTRIBUTE17
, CUST.ATTRIBUTE18
, CUST.ATTRIBUTE19
, CUST.ATTRIBUTE20
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.DUNS_NUMBER
, NULL) DUNS_NUMBER
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.DUNS_NUMBER_C
, NULL) DUNS_NUMBER_C
, CONT_RES.LAST_UPDATE_DATE
, CONT_POINT.LAST_UPDATE_DATE
, CONT_POINT.OBJECT_VERSION_NUMBER
, ORG_PROF.LAST_UPDATE_DATE
, ORG_PROF.OBJECT_VERSION_NUMBER
, PER_PROF.LAST_UPDATE_DATE
, PER_PROF.OBJECT_VERSION_NUMBER
, PARTY.LAST_UPDATE_DATE
, PARTY.OBJECT_VERSION_NUMBER
, ORG_PROF.ORGANIZATION_PROFILE_ID
, PER_PROF.PERSON_PROFILE_ID
, DECODE(CUST_PARTY.PARTY_TYPE
, 'ORGANIZATION'
, CUST_PARTY.SIC_CODE_TYPE
, NULL) SIC_CODE_TYPE
, CUST_ACCT.ACCOUNT_NAME
FROM AR_LOOKUPS L_CAT
, AR_LOOKUPS L_CLASS
, AR_LOOKUPS L_TYPE
, RA_SALESREPS REP
, AR_LOOKUPS L_FOB
, SO_PRICE_LISTS PL
, SO_LOOKUPS L_FT
, SO_ORDER_TYPES OT
, SO_LOOKUPS L_SC
, HR_ALL_ORGANIZATION_UNITS ORG
, HZ_CUST_PROFILE_CLASSES CPC
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES CUST_PARTY
, HZ_CONTACT_RESTRICTIONS CONT_RES
, HZ_CONTACT_POINTS CONT_POINT
, HZ_ORGANIZATION_PROFILES ORG_PROF
, HZ_PERSON_PROFILES PER_PROF
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
WHERE CUST_PARTY.CATEGORY_CODE = L_CAT.LOOKUP_CODE(+)
AND L_CAT.LOOKUP_TYPE(+) = 'CUSTOMER_CATEGORY'
AND CUST.CUSTOMER_CLASS_CODE = L_CLASS.LOOKUP_CODE(+)
AND L_CLASS.LOOKUP_TYPE(+) = 'CUSTOMER CLASS'
AND CUST.CUSTOMER_TYPE = L_TYPE.LOOKUP_CODE(+)
AND L_TYPE.LOOKUP_TYPE(+) = 'CUSTOMER_TYPE'
AND CUST.PRIMARY_SALESREP_ID = REP.SALESREP_ID(+)
AND CUST.FOB_POINT = L_FOB.LOOKUP_CODE(+)
AND L_FOB.LOOKUP_TYPE(+) = 'FOB'
AND CUST.PRICE_LIST_ID = PL.PRICE_LIST_ID(+)
AND CUST.FREIGHT_TERM = L_FT.LOOKUP_CODE(+)
AND L_FT.LOOKUP_TYPE(+) = 'FREIGHT_TERMS'
AND CUST.ORDER_TYPE_ID = OT.ORDER_TYPE_ID(+)
AND CUST.SALES_CHANNEL_CODE = L_SC.LOOKUP_CODE(+)
AND L_SC.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND CUST.WAREHOUSE_ID = ORG.ORGANIZATION_ID(+)
AND CP.CUST_ACCOUNT_ID = CUST.CUST_ACCOUNT_ID
AND CUST.PARTY_ID = CUST_PARTY.PARTY_ID
AND CP.SITE_USE_ID IS NULL
AND CP.PROFILE_CLASS_ID = CPC.PROFILE_CLASS_ID(+)
AND CUST_ACCT.CUST_ACCOUNT_ID = CUST.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND PARTY.PARTY_ID = ORG_PROF.PARTY_ID(+)
AND PARTY.PARTY_ID = PER_PROF.PARTY_ID(+)
AND PARTY.PARTY_ID = CONT_POINT.OWNER_TABLE_ID(+)
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'WEB'
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND PARTY.PARTY_ID = CONT_RES.SUBJECT_ID(+)
AND CONT_RES.SUBJECT_TABLE(+) = 'HZ_PARTIES'
AND ORG_PROF.CONTENT_SOURCE_TYPE(+) = 'USER_ENTERED'
AND PER_PROF.CONTENT_SOURCE_TYPE(+) = 'USER_ENTERED'
AND ORG_PROF.EFFECTIVE_END_DATE IS NULL
AND PER_PROF.EFFECTIVE_END_DATE IS NULL

Columns