GL - AR - Receipts

SELECT  acra.amount, NVL(gjl.ENTERED_DR, 0) , NVL(GJL.ACCOUNTED_DR,0) , NVL(gjl.ENTERED_CR, 0) ,  CASE
         WHEN acra.amount IS NOT NULL THEN
          acra.amount
         ELSE
          NVL(gjl.ENTERED_DR, 0) - NVL(gjl.ENTERED_CR, 0)
       END as Line_Total,
       GJH.DESCRIPTION,
      

       gjl.DESCRIPTION as Description,
       gjl.EFFECTIVE_DATE as Effective_Date,
       acra.amount as Invoice_amount,
       acra.receipt_date as Invoice_Date,
       acra.receipt_number as trx_number,
       gp.PERIOD_NAME as Period_Name,
       gp.PERIOD_NUM as Period_Num,
       gp.PERIOD_YEAR as Period_Year,
       gcc.SEGMENT2 as Division,
       gcc.SEGMENT3 as Department,
       gcc.SEGMENT4 as Account,
       gcc.SEGMENT5 as FTA,
       ard.source_id,
       NVL(hzp.party_name, acra.misc_payment_source) as Customer_Name,
       'Receipts(CRH)' as source
  FROM ar.ar_cash_receipt_history_all acr,
       ar.ar_Cash_Receipts_all        acra,
       ar.ar_distributions_all        ard,
       ar.hz_cust_accounts            hca,
       ar.hz_parties                  hzp,
       GL.GL_CODE_COMBINATIONS        gcc,
       GL.GL_IMPORT_REFERENCES        gir,
       GL.GL_JE_HEADERS               gjh,
       GL.GL_JE_LINES                 gjl,
       GL.GL_PERIODS                  gp,
       XLA.XLA_AE_LINES               xal,
       XLA.XLA_DISTRIBUTION_LINKS     xdl
WHERE (ard.source_id = acr.cash_receipt_history_id)
   and (acr.cash_receipt_id = acra.cash_receipt_id)
   and (acra.pay_from_customer = hca.cust_account_id(+))
   and (hca.party_id = hzp.party_id(+))
   and (gjh.je_header_id = gjl.je_header_id)
   and (gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID)
   and (gjl.JE_HEADER_ID = gir.JE_HEADER_ID AND
       gjl.JE_LINE_NUM = gir.JE_LINE_NUM)
   and (gjl.PERIOD_NAME = gp.PERIOD_NAME)
   and (gir.GL_SL_LINK_ID = xal.GL_SL_LINK_ID)
   and (xdl.source_distribution_id_num_1 = ard.line_id)
   and (xdl.event_id = acr.event_id)
   and (xal.code_combination_id = acr.account_code_combination_id)
   and (xal.AE_HEADER_ID = xdl.AE_HEADER_ID AND
       xal.AE_LINE_NUM = xdl.AE_LINE_NUM)
      -- and (xdl.accounting_line_code IN ('MFAR_MISC_RCT_CASH','RCT_APP_MFAR_TRX_REC'))--('CASH', 'MISC_CASH')) --('RCT_CASH'))--
      -- and (xal.accounting_class_code IN ('RECEIVABLE'))
   and (gjh.je_source = 'Receivables')
      -- and (gjh.JE_CATEGORY <> '
--   AND (gp.PERIOD_SET_NAME = 'PACE_CAL')
   AND (gjh.CURRENCY_CODE = 'USD')
   AND (gjh.period_name = 'Aug-2013'
   
   AND (gjh.STATUS = 'P')
   AND (gjh.ACTUAL_FLAG = 'A')