Customer Ledger Basic Query

SELECT TRX_NUMBER,
       TRX_DATE,
       GL_DATE,
       CUSTOMER_NAME,
       NAME,
       INVOICE_CURRENCY_CODE,
       CASE
         WHEN t.type in ('INV', 'DM') THEN
          AMOUNT
       END DR,
       CASE
         WHEN T.TYPE = 'CM' THEN
          AMOUNT * -1
       END CR,
       B.SEGMENT1,
       B.SEGMENT2,
       B.SEGMENT3,
       B.SEGMENT4,
       B.SEGMENT5,
       B.SEGMENT6,
       B.SEGMENT7,
       B.SEGMENT8
  FROM AR.RA_CUST_TRX_LINE_GL_DIST_ALL D,
       GL.GL_CODE_COMBINATIONS         B,
       AR.RA_CUSTOMER_TRX_ALL          C,
       APPS.AR_CUSTOMERS               M,
       AR.RA_CUST_TRX_TYPES_ALL        T,
       AR.RA_CUSTOMER_TRX_LINES_ALL    L
 WHERE D.CODE_COMBINATION_ID = b.CODE_COMBINATION_ID
   AND c.CUSTOMER_TRX_ID = d.CUSTOMER_TRX_ID
   AND c.org_id = '83'
   AND M.CUSTOMER_ID = c.BILL_TO_CUSTOMER_ID
   AND T.CUST_TRX_TYPE_ID = c.CUST_TRX_TYPE_ID
   AND L.CUSTOMER_TRX_LINE_ID(+) = D.CUSTOMER_TRX_LINE_ID
   AND GL_POSTED_DATE IS NOT NULL
   and ACCOUNT_CLASS = 'REC'
   and ACCOUNTING_AFFECT_FLAG = 'Y'
union all
SELECT RECEIPT_NUMBER,
       RECEIPT_DATE,
       ACRH.GL_DATE,
       CUSTOMER_NAME,
       'RECEIPT',
       ACR.CURRENCY_CODE,
       AMOUNT_CR,
       AMOUNT_DR,
       GCC.SEGMENT1,
       GCC.SEGMENT2,
       GCC.SEGMENT3,
       GCC.SEGMENT4,
       GCC.SEGMENT5,
       GCC.SEGMENT6,
       GCC.SEGMENT7,
       GCC.SEGMent8
  FROM AR.AR_CASH_RECEIPTS_ALL        ACR,
       AR.AR_CASH_RECEIPT_HISTORY_ALL ACRH,
       AR.AR_DISTRIBUTIONS_ALL        ADR,
       APPS.AR_CUSTOMERS              ARC,
       APPS.GL_CODE_COMBINATIONS_KFV  GCC
 where ACR.CASH_RECEIPT_ID = ACRH.CASH_RECEIPT_ID
   AND ARC.CUSTOMER_ID = ACR.PAY_FROM_CUSTOMER
   AND GCC.CODE_COMBINATION_ID = ADR.CODE_COMBINATION_ID
   AND ADR.SOURCE_TYPE IN ('REMITTANCE', 'CASH')
   AND acr.org_id = 83
   AND ADR.SOURCE_ID = ACRH.CASH_RECEIPT_HISTORY_ID