1. Tables and Views for Financials
  2. AR_TOTAL_BALANCE_VIEW

AR_TOTAL_BALANCE_VIEW

Details

  • Schema: FUSION

  • Object owner: AR

  • Object type: VIEW

Columns

Name

CUST_ACCOUNT_ID

TOTAL_BALANCE

Query

SQL_Statement

SELECT cust_account_id, sum(amount) total_balance FROM (

SELECT

SUMM.CUST_ACCOUNT_ID cust_account_id,

GL.CONVERSION_RATE*(NVL(SUMM.OP_INVOICES_VALUE,0)+NVL(SUMM.OP_CREDIT_MEMOS_VALUE,0)+NVL(SUMM.OP_CHARGEBACK_VALUE,0)+NVL(SUMM.OP_DEBIT_MEMOS_VALUE,0)+NVL(SUMM.UNRESOLVED_CASH_VALUE,0)) Amount

FROM GL_DAILY_RATES GL, AR_TRX_BAL_SUMMARY SUMM, HZ_CUSTOMER_PROFILES_F PRO

WHERE GL.CONVERSION_DATE = Trunc(SYSDATE)

AND GL.CONVERSION_TYPE = PRO.EXCHANGE_RATE_TYPE

AND GL.FROM_CURRENCY = SUMM.CURRENCY

AND GL.TO_CURRENCY = PRO.CREDIT_CURRENCY_CODE

AND SUMM.CUST_ACCOUNT_ID = PRO.CUST_ACCOUNT_ID

AND TRUNC(SYSDATE) BETWEEN PRO.EFFECTIVE_START_DATE AND PRO.EFFECTIVE_END_DATE

UNION

SELECT

SUMM.CUST_ACCOUNT_ID cust_account_id,

NVL(SUMM.OP_INVOICES_VALUE,0)+NVL(SUMM.OP_CREDIT_MEMOS_VALUE,0)+NVL(SUMM.OP_CHARGEBACK_VALUE,0)+NVL(SUMM.OP_DEBIT_MEMOS_VALUE,0)+NVL(SUMM.UNRESOLVED_CASH_VALUE,0) AMOUNT

FROM AR_TRX_BAL_SUMMARY SUMM, HZ_CUSTOMER_PROFILES_F PRO

WHERE SUMM.CURRENCY = PRO.CREDIT_CURRENCY_CODE

AND SUMM.CUST_ACCOUNT_ID = PRO.CUST_ACCOUNT_ID

AND TRUNC(SYSDATE) BETWEEN PRO.EFFECTIVE_START_DATE AND PRO.EFFECTIVE_END_DATE

)

GROUP by cust_account_id