Ar_Receipt_Dist_Qry

/* Formatted on 14-08-2011 21:48:44 (QP5 v5.115.810.9015) */
SELECT     /* PURPOSE: THE VIEW AR_MISC_CASH_DISTRIBUTIONS_V IS USED IN THE */
         /* MISC CASH DISTRIBUTIONS WINDOW OF THE RECEIPT GATEWAY */
DISTINCT
HOU.NAME OPERATING_UNIT,
MCD.MISC_CASH_DISTRIBUTION_ID MISC_CASH_DISTRIBUTION_ID,
         MCD.CASH_RECEIPT_ID CASH_RECEIPT_ID,
         CR.RECEIPT_NUMBER,
         MCD.CODE_COMBINATION_ID CODE_COMBINATION_ID,
         KFV.CONCATENATED_SEGMENTS ACCT_DESC,
         MCD.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
         MCD.PERCENT PERCENT,
         DECODE (SIGN (MCD.AMOUNT),
                 1,
                 SIGN (MCD.AMOUNT) * DIST.AMOUNT_CR,
                 -1,
                 SIGN (MCD.AMOUNT) * DIST.AMOUNT_DR,
                 0,
                 SIGN (MCD.AMOUNT) * DIST.AMOUNT_CR)
            AMOUNT,
         DECODE (SIGN (MCD.ACCTD_AMOUNT),
                 1,
                 SIGN (MCD.ACCTD_AMOUNT) * DIST.ACCTD_AMOUNT_CR,
                 -1,
                 SIGN (MCD.ACCTD_AMOUNT) * DIST.ACCTD_AMOUNT_DR,
                 0,
                 SIGN (MCD.ACCTD_AMOUNT) * DIST.ACCTD_AMOUNT_CR)
            ACCTD_AMOUNT,
         CR.CURRENCY_CODE CURRENCY_CODE,
         CR.EXCHANGE_RATE EXCHANGE_RATE,
         MCD.COMMENTS COMMENTS,
         MCD.GL_POSTED_DATE GL_POSTED_DATE,
         MCD.GL_DATE GL_DATE,
         MCD.APPLY_DATE APPLY_DATE,
         MCD.POSTING_CONTROL_ID POSTING_CONTROL_ID,
         MCD.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
         MCD.REVERSAL_GL_DATE
  FROM   AR_MISC_CASH_DISTRIBUTIONS_ALL MCD,
         AR_CASH_RECEIPTS_ALL CR,
         AR_DISTRIBUTIONS_ALL DIST,
         AR_SYSTEM_PARAMETERS_ALL ASP,
         GL_CODE_COMBINATIONS_KFV KFV,
         HR_OPERATING_UNITS HOU
 WHERE       CR.CASH_RECEIPT_ID = MCD.CASH_RECEIPT_ID
         AND MCD.MISC_CASH_DISTRIBUTION_ID = DIST.SOURCE_ID
         AND KFV.CODE_COMBINATION_ID = MCD.CODE_COMBINATION_ID
         AND CR.ORG_ID=HOU.ORGANIZATION_ID
         AND (DIST.SOURCE_TYPE = 'MISCCASH')
         AND MCD.REVERSAl_GL_DATE IS NULL
         AND ASP.ACCOUNTING_METHOD = 'ACCRUAL'
--         and cr.STATUS not in ('REV','NSF')
         AND TRUNC (CR.RECEIPT_DATE) BETWEEN '01-Apr-2011' AND '31-Jul-2011'
         AND CR.SET_OF_BOOKS_ID = 5        
UNION ALL
SELECT  DISTINCT 
HOU.NAME OPERATING_UNIT,
MCD.MISC_CASH_DISTRIBUTION_ID MISC_CASH_DISTRIBUTION_ID,
         MCD.CASH_RECEIPT_ID CASH_RECEIPT_ID,
         CR.RECEIPT_NUMBER,
         MCD.CODE_COMBINATION_ID CODE_COMBINATION_ID,
         KFV.CONCATENATED_SEGMENTS ACCT_DESC,
         MCD.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
         MCD.PERCENT PERCENT,
         MCD.AMOUNT,
         MCD.ACCTD_AMOUNT,
         CR.CURRENCY_CODE CURRENCY_CODE,
         CR.EXCHANGE_RATE EXCHANGE_RATE,
         MCD.COMMENTS COMMENTS,
         MCD.GL_POSTED_DATE GL_POSTED_DATE,
         MCD.GL_DATE GL_DATE,
         MCD.APPLY_DATE APPLY_DATE,
         MCD.POSTING_CONTROL_ID POSTING_CONTROL_ID,
         MCD.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
         MCD.REVERSAL_GL_DATE
  FROM   AR_MISC_CASH_DISTRIBUTIONS_ALL MCD,
         AR_CASH_RECEIPTS_ALL CR,
         ar_system_parameters_ALL asp,
         GL_CODE_COMBINATIONS_KFV KFV,
         HR_OPERATING_UNITS HOU
 WHERE       CR.CASH_RECEIPT_ID = MCD.CASH_RECEIPT_ID
         AND KFV.CODE_COMBINATION_ID = MCD.CODE_COMBINATION_ID
         AND CR.ORG_ID=HOU.ORGANIZATION_ID
         AND TRUNC (CR.RECEIPT_DATE) BETWEEN '01-Apr-2011' AND '31-Jul-2011'
         AND cr.SET_OF_BOOKS_ID = 5
         --and cr.STATUS not in ('REV', 'NSF')
         AND asp.accounting_method = 'MISC';