VIEW: APPS.ARFV_CUSTOMERS

Source

View Text - Preformatted

SELECT /* UNIQUE ATTRIBUTES */ CUST_ACCT.CUST_ACCOUNT_ID , /* REGULAR ATTRIBUTES */ PARTY.PARTY_NAME , CUST_ACCT.ACCOUNT_NUMBER , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.ORGANIZATION_NAME_PHONETIC, NULL) , '_LA:CUST_ACCT.STATUS:AR_LOOKUPS:CODE_STATUS:MEANING' , '_LA:DECODE(party.TOTAL_NUM_OF_ORDERS,   0,   ''PROSPECT'',   1,   ''CUSTOMER'',   ''CUSTOMER''):AR_LOOKUPS:CUSTOMER_PROSPECT_CODE:MEANING' , CUST_ACCT.ORIG_SYSTEM_REFERENCE , '_LA:CUST_ACCT.CUSTOMER_TYPE:AR_LOOKUPS:CUSTOMER_TYPE:MEANING' , CUST_ACCT.CUSTOMER_CLASS_CODE , '_LA:PARTY.CATEGORY_CODE:AR_LOOKUPS:CUSTOMER_CATEGORY:MEANING' , CUST_ACCT.TAX_CODE , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.SIC_CODE, NULL) , PARTY.JGZZ_FISCAL_CODE , PARTY.TAX_REFERENCE , '_LA:CUST_ACCT.TAX_ROUNDING_RULE:AR_LOOKUPS:AR_TAX_ROUNDING_RULE:MEANING' , '_LA:CUST_ACCT.TAX_HEADER_LEVEL_FLAG:AR_LOOKUPS:YES/NO:MEANING' , '_LA:DECODE(PARTY.PARTY_TYPE,   ''ORGANIZATION'',   PARTY.GSA_INDICATOR_FLAG,   ''N''):AR_LOOKUPS:YES/NO:MEANING' , '_LA:CUST_ACCT.SHIP_PARTIAL:AR_LOOKUPS:YES/NO:MEANING' , CUST_ACCT.SHIP_VIA , '_LA:CUST_ACCT.FOB_POINT:AR_LOOKUPS:FOB:MEANING' , PL.NAME , '_LA:CUST_ACCT.FREIGHT_TERM:OE_LOOKUPS:FREIGHT_TERMS:MEANING' , '_LA:CUST_ACCT.SALES_CHANNEL_CODE:OE_LOOKUPS:SALES_CHANNEL:MEANING' , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.EMPLOYEES_TOTAL, TO_NUMBER(NULL)) , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.YEAR_ESTABLISHED, TO_NUMBER(NULL)) , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.FISCAL_YEAREND_MONTH, NULL) , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.ANALYSIS_FY, NULL) , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.CURR_FY_POTENTIAL_REVENUE, TO_NUMBER(NULL)) , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.NEXT_FY_POTENTIAL_REVENUE, TO_NUMBER(NULL)) , '_LA:THIRD_PARTY_FLAG:AR_LOOKUPS:YES/NO:MEANING' , '_LA:REFERENCE_USE_FLAG:AR_LOOKUPS:YES/NO:MEANING' , '_LA:COMPETITOR_FLAG:AR_LOOKUPS:YES/NO:MEANING' , DECODE(PARTY.PARTY_TYPE, 'ORGANIZATION', PARTY.MISSION_STATEMENT, NULL) , '_LA:CP.ACCOUNT_STATUS:AR_LOOKUPS:ACCOUNT_STATUS:MEANING' , '_LA:CP.CREDIT_RATING:AR_LOOKUPS:CREDIT_RATING:MEANING' , CP.PERCENT_COLLECTABLE , '_LA:CP.RISK_CODE:AR_LOOKUPS:RISK_CODE:MEANING' , '_LA:CP.CREDIT_CHECKING:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.CREDIT_HOLD:AR_LOOKUPS:YES/NO:MEANING' , TE.NAME , '_LA:CP.OVERRIDE_TERMS:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.DISCOUNT_TERMS:AR_LOOKUPS:YES/NO:MEANING' , CP.DISCOUNT_GRACE_DAYS , CP.PAYMENT_GRACE_DAYS , '_LA:CP.AUTO_REC_INCL_DISPUTED_FLAG:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.SEND_STATEMENTS:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.CREDIT_BALANCE_STATEMENTS:AR_LOOKUPS:YES/NO:MEANING' , SCY.NAME , '_LA:CP.DUNNING_LETTERS:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.CONS_INV_FLAG:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.CONS_INV_TYPE:AR_LOOKUPS:CONSINV_TYPE:MEANING' , '_LA:CP.INTEREST_CHARGES:AR_LOOKUPS:YES/NO:MEANING' , '_LA:CP.CHARGE_ON_FINANCE_CHARGE_FLAG:AR_LOOKUPS:YES/NO:MEANING' , CP.INTEREST_PERIOD_DAYS , '_LA:CP.TAX_PRINTING_OPTION:AR_LOOKUPS:TAX_PRINTING_OPTION:MEANING' , GR.NAME , CP.CLEARING_DAYS , AHI.HIERARCHY_NAME , '_LA:CP.LOCKBOX_MATCHING_OPTION:AR_LOOKUPS:ARLPLB_MATCHING_OPTION:MEANING' , AHIR.HIERARCHY_NAME , PARTY.URL , ORDE.NAME , SAL.NAME , NULL , /* DUNSU.LOCATION FK Not available, */ NULL , /* STASU.LOCATION FK Not available, */ ORTY.NAME , '_DF:AR:RA_CUSTOMERS_HZ:CUST_ACCT' , '_DF:AR:AR_CUSTOMER_PROFILES:CP' , '_DF:JG:JG_AR_CUSTOMER_PROFILES:CP' , /* IDS */ CUST_ACCT.WAREHOUSE_ID , CUST_ACCT.PRIMARY_SALESREP_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , CUST_ACCT.ORDER_TYPE_ID , /* WHO COLUMNS */ CUST_ACCT.LAST_UPDATE_DATE , CUST_ACCT.LAST_UPDATED_BY , CUST_ACCT.CREATION_DATE , CUST_ACCT.CREATED_BY FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT , HZ_CUSTOMER_PROFILES CP , SO_PRICE_LISTS PL , RA_TERMS TE , AR_STATEMENT_CYCLES SCY , RA_GROUPING_RULES GR , AR_AUTOCASH_HIERARCHIES AHI , AR_AUTOCASH_HIERARCHIES AHIR , HR_ALL_ORGANIZATION_UNITS orde , RA_SALESREPS SAL , SO_ORDER_TYPES ORTY WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID AND CP.SITE_USE_ID IS NULL AND CUST_ACCT.PRICE_LIST_ID = PL.PRICE_LIST_ID (+) AND CP.STANDARD_TERMS = TE.TERM_ID (+) AND CP.STATEMENT_CYCLE_ID = SCY.STATEMENT_CYCLE_ID (+) AND CP.GROUPING_RULE_ID = GR.GROUPING_RULE_ID (+) AND CP.AUTOCASH_HIERARCHY_ID = AHI.AUTOCASH_HIERARCHY_ID (+) AND CP.AUTOCASH_HIERARCHY_ID_FOR_ADR = AHIR.AUTOCASH_HIERARCHY_ID (+) AND CUST_ACCT.WAREHOUSE_ID = ORDE.ORGANIZATION_ID (+) AND CUST_ACCT.PRIMARY_SALESREP_ID = SAL.SALESREP_ID (+) AND CUST_ACCT.ORDER_TYPE_ID = ORTY.ORDER_TYPE_ID (+) WITH READ ONLY

View Text - HTML Formatted

SELECT /* UNIQUE ATTRIBUTES */ CUST_ACCT.CUST_ACCOUNT_ID
, /* REGULAR ATTRIBUTES */ PARTY.PARTY_NAME
, CUST_ACCT.ACCOUNT_NUMBER
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.ORGANIZATION_NAME_PHONETIC
, NULL)
, '_LA:CUST_ACCT.STATUS:AR_LOOKUPS:CODE_STATUS:MEANING'
, '_LA:DECODE(PARTY.TOTAL_NUM_OF_ORDERS
, 0
, ''PROSPECT''
, 1
, ''CUSTOMER''
, ''CUSTOMER''):AR_LOOKUPS:CUSTOMER_PROSPECT_CODE:MEANING'
, CUST_ACCT.ORIG_SYSTEM_REFERENCE
, '_LA:CUST_ACCT.CUSTOMER_TYPE:AR_LOOKUPS:CUSTOMER_TYPE:MEANING'
, CUST_ACCT.CUSTOMER_CLASS_CODE
, '_LA:PARTY.CATEGORY_CODE:AR_LOOKUPS:CUSTOMER_CATEGORY:MEANING'
, CUST_ACCT.TAX_CODE
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.SIC_CODE
, NULL)
, PARTY.JGZZ_FISCAL_CODE
, PARTY.TAX_REFERENCE
, '_LA:CUST_ACCT.TAX_ROUNDING_RULE:AR_LOOKUPS:AR_TAX_ROUNDING_RULE:MEANING'
, '_LA:CUST_ACCT.TAX_HEADER_LEVEL_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:DECODE(PARTY.PARTY_TYPE
, ''ORGANIZATION''
, PARTY.GSA_INDICATOR_FLAG
, ''N''):AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CUST_ACCT.SHIP_PARTIAL:AR_LOOKUPS:YES/NO:MEANING'
, CUST_ACCT.SHIP_VIA
, '_LA:CUST_ACCT.FOB_POINT:AR_LOOKUPS:FOB:MEANING'
, PL.NAME
, '_LA:CUST_ACCT.FREIGHT_TERM:OE_LOOKUPS:FREIGHT_TERMS:MEANING'
, '_LA:CUST_ACCT.SALES_CHANNEL_CODE:OE_LOOKUPS:SALES_CHANNEL:MEANING'
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.EMPLOYEES_TOTAL
, TO_NUMBER(NULL))
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.YEAR_ESTABLISHED
, TO_NUMBER(NULL))
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.FISCAL_YEAREND_MONTH
, NULL)
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.ANALYSIS_FY
, NULL)
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.CURR_FY_POTENTIAL_REVENUE
, TO_NUMBER(NULL))
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.NEXT_FY_POTENTIAL_REVENUE
, TO_NUMBER(NULL))
, '_LA:THIRD_PARTY_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:REFERENCE_USE_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:COMPETITOR_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, DECODE(PARTY.PARTY_TYPE
, 'ORGANIZATION'
, PARTY.MISSION_STATEMENT
, NULL)
, '_LA:CP.ACCOUNT_STATUS:AR_LOOKUPS:ACCOUNT_STATUS:MEANING'
, '_LA:CP.CREDIT_RATING:AR_LOOKUPS:CREDIT_RATING:MEANING'
, CP.PERCENT_COLLECTABLE
, '_LA:CP.RISK_CODE:AR_LOOKUPS:RISK_CODE:MEANING'
, '_LA:CP.CREDIT_CHECKING:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.CREDIT_HOLD:AR_LOOKUPS:YES/NO:MEANING'
, TE.NAME
, '_LA:CP.OVERRIDE_TERMS:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.DISCOUNT_TERMS:AR_LOOKUPS:YES/NO:MEANING'
, CP.DISCOUNT_GRACE_DAYS
, CP.PAYMENT_GRACE_DAYS
, '_LA:CP.AUTO_REC_INCL_DISPUTED_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.SEND_STATEMENTS:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.CREDIT_BALANCE_STATEMENTS:AR_LOOKUPS:YES/NO:MEANING'
, SCY.NAME
, '_LA:CP.DUNNING_LETTERS:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.CONS_INV_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.CONS_INV_TYPE:AR_LOOKUPS:CONSINV_TYPE:MEANING'
, '_LA:CP.INTEREST_CHARGES:AR_LOOKUPS:YES/NO:MEANING'
, '_LA:CP.CHARGE_ON_FINANCE_CHARGE_FLAG:AR_LOOKUPS:YES/NO:MEANING'
, CP.INTEREST_PERIOD_DAYS
, '_LA:CP.TAX_PRINTING_OPTION:AR_LOOKUPS:TAX_PRINTING_OPTION:MEANING'
, GR.NAME
, CP.CLEARING_DAYS
, AHI.HIERARCHY_NAME
, '_LA:CP.LOCKBOX_MATCHING_OPTION:AR_LOOKUPS:ARLPLB_MATCHING_OPTION:MEANING'
, AHIR.HIERARCHY_NAME
, PARTY.URL
, ORDE.NAME
, SAL.NAME
, NULL
, /* DUNSU.LOCATION FK NOT AVAILABLE
, */ NULL
, /* STASU.LOCATION FK NOT AVAILABLE
, */ ORTY.NAME
, '_DF:AR:RA_CUSTOMERS_HZ:CUST_ACCT'
, '_DF:AR:AR_CUSTOMER_PROFILES:CP'
, '_DF:JG:JG_AR_CUSTOMER_PROFILES:CP'
, /* IDS */ CUST_ACCT.WAREHOUSE_ID
, CUST_ACCT.PRIMARY_SALESREP_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, CUST_ACCT.ORDER_TYPE_ID
, /* WHO COLUMNS */ CUST_ACCT.LAST_UPDATE_DATE
, CUST_ACCT.LAST_UPDATED_BY
, CUST_ACCT.CREATION_DATE
, CUST_ACCT.CREATED_BY
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUSTOMER_PROFILES CP
, SO_PRICE_LISTS PL
, RA_TERMS TE
, AR_STATEMENT_CYCLES SCY
, RA_GROUPING_RULES GR
, AR_AUTOCASH_HIERARCHIES AHI
, AR_AUTOCASH_HIERARCHIES AHIR
, HR_ALL_ORGANIZATION_UNITS ORDE
, RA_SALESREPS SAL
, SO_ORDER_TYPES ORTY
WHERE CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.SITE_USE_ID IS NULL
AND CUST_ACCT.PRICE_LIST_ID = PL.PRICE_LIST_ID (+)
AND CP.STANDARD_TERMS = TE.TERM_ID (+)
AND CP.STATEMENT_CYCLE_ID = SCY.STATEMENT_CYCLE_ID (+)
AND CP.GROUPING_RULE_ID = GR.GROUPING_RULE_ID (+)
AND CP.AUTOCASH_HIERARCHY_ID = AHI.AUTOCASH_HIERARCHY_ID (+)
AND CP.AUTOCASH_HIERARCHY_ID_FOR_ADR = AHIR.AUTOCASH_HIERARCHY_ID (+)
AND CUST_ACCT.WAREHOUSE_ID = ORDE.ORGANIZATION_ID (+)
AND CUST_ACCT.PRIMARY_SALESREP_ID = SAL.SALESREP_ID (+)
AND CUST_ACCT.ORDER_TYPE_ID = ORTY.ORDER_TYPE_ID (+) WITH READ ONLY