AR_Receipt_Final_Qry

/* Formatted on 14-08-2011 13:53:30 (QP5 v5.115.810.9015) */
SELECT /* PURPOSE: THE VIEW AR_CASH_RECEIPTS_V IS USED IN THE RECEIPTS GATEWAY AS THE BASETABLE FOR THE RECEIPTS FORM */
      HOU.NAME OPERATING_UNIT,
      --CR .ROWID ROW_ID,
         CR.CASH_RECEIPT_ID CASH_RECEIPT_ID,
         CRH_CURRENT.CASH_RECEIPT_HISTORY_ID CASH_RECEIPT_HISTORY_ID,
         CR.AMOUNT AMOUNT,
         CRH_CURRENT.ACCTD_AMOUNT FUNCTIONAL_AMOUNT,
         CRH_CURRENT.AMOUNT NET_AMOUNT,
         CR.CURRENCY_CODE CURRENCY_CODE,
         CR.RECEIPT_NUMBER RECEIPT_NUMBER,
         CR.RECEIPT_DATE RECEIPT_DATE,
         CR.ANTICIPATED_CLEARING_DATE ANTICIPATED_CLEARING_DATE,
         CR.ACTUAL_VALUE_DATE ACTUAL_VALUE_DATE,
         CR.TYPE TYPE,
         CR.STATUS RECEIPT_STATUS,
         ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('CHECK_STATUS', CR.STATUS)
            RECEIPT_STATUS_DSP,
         CR.COMMENTS COMMENTS,
         CR.MISC_PAYMENT_SOURCE MISC_PAYMENT_SOURCE,
         CR.EXCHANGE_RATE EXCHANGE_RATE,
         CR.EXCHANGE_DATE EXCHANGE_RATE_DATE,
         CR.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE,
         GL_DCT.USER_CONVERSION_TYPE EXCHANGE_RATE_TYPE_DSP,
         CR.DOC_SEQUENCE_ID DOC_SEQUENCE_ID,
         CR.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER,
         CR.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
         CR.CUSTOMER_RECEIPT_REFERENCE CUSTOMER_RECEIPT_REFERENCE,
         REC_METHOD.NAME PAYMENT_METHOD_DSP,
         REC_METHOD.PAYMENT_TYPE_CODE PAYMENT_TYPE_CODE,
         CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID,
         RC.NAME RECEIPT_CLASS_DSP,
         RC.BILL_OF_EXCHANGE_FLAG BILL_OF_EXCHANGE_FLAG,
         ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('RECEIPT_CREATION_METHOD',
                                                RC.CREATION_METHOD_CODE)
            CREATION_METHOD_DSP,
         RC.CREATION_METHOD_CODE CREATION_METHOD_CODE,
         CR.PAY_FROM_CUSTOMER CUSTOMER_ID,
         SUBSTRB (PARTY.PARTY_NAME, 1, 50) CUSTOMER_NAME,
         CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER,
         PARTY.JGZZ_FISCAL_CODE TAXPAYER_ID,
         SITE_USES.LOCATION LOCATION,
         CR.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID,
         CR.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID,
         CR.CUSTOMER_BANK_BRANCH_ID CUSTOMER_BANK_BRANCH_ID,
         DECODE (
            NVL (FND_PROFILE.VALUE ('AR_MASK_BANK_ACCOUNT_NUMBERS'), 'F'),
            'N',
            CUST_BANK.BANK_ACCOUNT_NUM,
            'F',
            RPAD (SUBSTR (CUST_BANK.BANK_ACCOUNT_NUM, 1, 4),
                  LENGTH (CUST_BANK.BANK_ACCOUNT_NUM),
                  '*'),
            'L',
            LPAD (SUBSTR (CUST_BANK.BANK_ACCOUNT_NUM, -4),
                  LENGTH (CUST_BANK.BANK_ACCOUNT_NUM),
                  '*')
         )
            CUSTOMER_BANK_ACCOUNT,
         CUST_BANK.BANK_ACCOUNT_NUM CUSTOMER_BANK_ACCOUNT_NUM,
         NVL (CUST_BANK_BRANCH.BANK_NAME, CUST_BANK_BRANCH2.BANK_NAME)
            CUSTOMER_BANK_NAME,
         NVL (CUST_BANK_BRANCH.BANK_BRANCH_NAME,
              CUST_BANK_BRANCH2.BANK_BRANCH_NAME)
            CUSTOMER_BANK_BRANCH,
         CRH_FIRST_POSTED.BATCH_ID BATCH_ID,
         DECODE (RC.CREATION_METHOD_CODE, 'BR', BAT_BR.NAME, BAT.NAME)
            BATCH_NAME        /* 20-APR-2000 J Rautiainen BR Implementation */
                      ,
         DIST_SET.DISTRIBUTION_SET_NAME DISTRIBUTION_SET,
         CR.DISTRIBUTION_SET_ID DISTRIBUTION_SET_ID,
         CR.DEPOSIT_DATE DEPOSIT_DATE,
         CR.REFERENCE_TYPE REFERENCE_TYPE,
         CR.VAT_TAX_ID VAT_TAX_ID,
         VAT.TAX_CODE TAX_CODE,
         CR.TAX_RATE TAX_RATE,
         VAT.TAX_ACCOUNT_ID,
         VAT.VALIDATE_FLAG ADHOC_FLAG,
         L_REF_TYPE.MEANING REFERENCE_TYPE_DSP,
         CR.REFERENCE_ID REFERENCE_ID,
         CR.REMITTANCE_BANK_ACCOUNT_ID REMITTANCE_BANK_ACCOUNT_ID,
         REMIT_BANK.BANK_ACCOUNT_NUM REMIT_BANK_ACCOUNT,
         REMIT_BANK_BRANCH.BANK_NAME REMIT_BANK_NAME,
         REMIT_BANK_BRANCH.BANK_BRANCH_ID REMITTANCE_BANK_BRANCH_ID,
         REMIT_BANK_BRANCH.BANK_BRANCH_NAME REMIT_BANK_BRANCH,
         REMIT_BANK.CURRENCY_CODE REMIT_BANK_CURRENCY,
         CRH_CURRENT.FACTOR_DISCOUNT_AMOUNT FACTOR_DISCOUNT_AMOUNT,
         PS.DUE_DATE MATURITY_DATE,
         PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID,
         ARPT_SQL_FUNC_UTIL.get_lookup_meaning ('RECEIPT_CREATION_STATUS',
                                                CRH_CURRENT.STATUS)
            STATE_DSP,
         CRH_CURRENT.STATUS STATE,
         CRH_CURRENT.GL_POSTED_DATE POSTED_DATE,
         REC_TRX.NAME ACTIVITY,
         REC_TRX.TAX_CODE_SOURCE TAX_CODE_SOURCE,
         CR.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID,
         CRH_CURRENT.GL_POSTED_DATE GL_POSTED_DATE,
         CRH_CURRENT.POSTING_CONTROL_ID POSTING_CONTROL_ID,
         CRH_FIRST_POSTED.GL_DATE GL_DATE,
         CR.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY,
         CR.REVERSAL_DATE REVERSAL_DATE,
         DECODE (CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.MEANING)
            REVERSAL_CATEGORY_DSP,
         CR.REVERSAL_CATEGORY REVERSAL_CATEGORY,
         DECODE (CR.REVERSAL_CATEGORY, NULL, NULL, L_REV_CAT.DESCRIPTION)
            CATEGORY_DESCRIPTION,
         CR.REVERSAL_COMMENTS REVERSAL_COMMENTS,
         DECODE (CR.REVERSAL_REASON_CODE, NULL, NULL, L_REV_REASON.MEANING)
            REVERSAL_REASON,
         CR.REVERSAL_REASON_CODE REVERSAL_REASON_CODE,
         DECODE (CR.REVERSAL_REASON_CODE,
                 NULL, NULL,
                 L_REV_REASON.DESCRIPTION)
            REVERSAL_REASON_DESCRIPTION,
         REM_BAT.NAME REMIT_BATCH,
         REM_BAT.BATCH_ID REMIT_BATCH_ID,
         NVL (CR.OVERRIDE_REMIT_ACCOUNT_FLAG, 'Y') OVERRIDE_REMIT_BANK,
         NVL (- (PS.AMOUNT_APPLIED), 0) APPLIED_AMOUNT,
         CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID,
         CR.PROGRAM_ID PROGRAM_ID,
         CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE,
         DECODE (
            NVL (CR.CONFIRMED_FLAG, 'Y'),
            'Y',
            DECODE (
               CR.REVERSAL_DATE,
               NULL,
               DECODE (
                  CRH_CURRENT.STATUS,
                  'REVERSED',
                  'N',
                  DECODE (
                     CRH_CURRENT.FACTOR_FLAG,
                     'Y',
                     DECODE (CRH_CURRENT.STATUS, 'RISK_ELIMINATED', 'N', 'Y'),
                     DECODE (CRH_CURRENT.STATUS, 'CLEARED', 'N', 'Y')
                  )
               ),
               'N'
            ),
            'N'
         )
            AT_RISK,
         REM_BAT.REMIT_METHOD_CODE REMITTANCE_METHOD,
         CR.ISSUER_NAME,
         CR.ISSUE_DATE,
         CR.ISSUER_BANK_BRANCH_ID,
         NOTES_BANK.BANK_NAME,
         NOTES_BANK.BANK_BRANCH_NAME,
         CRH_CURRENT.NOTE_STATUS,
         CRH_NOTE_STATUS.MEANING,
         CRH_NOTE_STATUS.DESCRIPTION,
         RC.NOTES_RECEIVABLE,
         CR.PAYMENT_SERVER_ORDER_NUM,
         CR.APPROVAL_CODE,
         CR.ADDRESS_VERIFICATION_CODE,
         PS.CONS_INV_ID,
         CR.POSTMARK_DATE POSTMARK_DATE
  FROM   AP_BANK_BRANCHES NOTES_BANK,
         AP_BANK_ACCOUNTS_ALL REMIT_BANK,
         AP_BANK_BRANCHES REMIT_BANK_BRANCH,
         AP_BANK_BRANCHES CUST_BANK_BRANCH,
         AP_BANK_BRANCHES CUST_BANK_BRANCH2,
         AP_BANK_ACCOUNTS_ALL CUST_BANK,
         AR_VAT_TAX VAT,
         HZ_CUST_ACCOUNTS CUST,
         HZ_PARTIES PARTY,
         AR_RECEIPT_METHODS REC_METHOD,
         AR_RECEIPT_CLASSES RC,
         HZ_CUST_SITE_USES_ALL SITE_USES,
         AR_LOOKUPS CRH_NOTE_STATUS,
         AR_LOOKUPS L_REV_CAT,
         AR_LOOKUPS L_REV_REASON,
         AR_LOOKUPS L_REF_TYPE,
         GL_DAILY_CONVERSION_TYPES GL_DCT,
         AR_CASH_RECEIPT_HISTORY_ALL CRH_REM,
         AR_BATCHES REM_BAT,
         AR_RECEIVABLES_TRX_ALL REC_TRX,
         AR_DISTRIBUTION_SETS_ALL DIST_SET,
         AR_PAYMENT_SCHEDULES_ALL PS,
         AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT,         /* CURRENT_RECORD */
         AR_BATCHES_ALL BAT,
         AR_BATCHES_ALL BAT_BR, /* 20-APR-2000 J Rautiainen BR Implementation */
         AR_CASH_RECEIPTS_ALL CR,
         AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED, /* FIRST_POSTED_RECORD */
         HR_OPERATING_UNITS HOU
 WHERE       CR.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+)
         AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
         AND CRH_NOTE_STATUS.LOOKUP_TYPE(+) = 'AR_NOTE_STATUS'
         AND CRH_NOTE_STATUS.LOOKUP_CODE(+) = CRH_CURRENT.NOTE_STATUS
         AND NOTES_BANK.BANK_BRANCH_ID(+) = CR.ISSUER_BANK_BRANCH_ID
         AND REMIT_BANK.BANK_ACCOUNT_ID(+) = CR.REMITTANCE_BANK_ACCOUNT_ID
         AND REMIT_BANK.BANK_BRANCH_ID = REMIT_BANK_BRANCH.BANK_BRANCH_ID(+)
         AND CUST_BANK.BANK_ACCOUNT_ID(+) = CR.CUSTOMER_BANK_ACCOUNT_ID
         AND CUST_BANK.BANK_BRANCH_ID = CUST_BANK_BRANCH.BANK_BRANCH_ID(+)
         AND CR.CUSTOMER_BANK_BRANCH_ID = CUST_BANK_BRANCH2.BANK_BRANCH_ID(+)
         AND VAT.VAT_TAX_ID(+) = CR.VAT_TAX_ID
         AND CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID
         AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
         AND CR.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID(+)
         AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID(+)
         AND CR.DISTRIBUTION_SET_ID = DIST_SET.DISTRIBUTION_SET_ID(+)
         AND L_REV_CAT.LOOKUP_TYPE(+) = 'REVERSAL_CATEGORY_TYPE'
         AND L_REV_CAT.LOOKUP_CODE(+) = CR.REVERSAL_CATEGORY
         AND L_REV_REASON.LOOKUP_TYPE(+) = 'CKAJST_REASON'
         AND L_REV_REASON.LOOKUP_CODE(+) = CR.REVERSAL_REASON_CODE
         AND L_REF_TYPE.LOOKUP_CODE(+) = CR.REFERENCE_TYPE
         AND L_REF_TYPE.LOOKUP_TYPE(+) = 'CB_REFERENCE_TYPE'
         AND GL_DCT.CONVERSION_TYPE(+) = CR.EXCHANGE_RATE_TYPE
         AND CRH_REM.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
         AND NOT EXISTS
               (SELECT   CASH_RECEIPT_HISTORY_ID
                  FROM   AR_CASH_RECEIPT_HISTORY_ALL CRH3
                 WHERE   CRH3.STATUS = 'REMITTED'
                         AND CRH3.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
                         AND CRH3.CASH_RECEIPT_HISTORY_ID <
                               CRH_REM.CASH_RECEIPT_HISTORY_ID)
         AND CRH_REM.STATUS(+) = 'REMITTED'
         AND CRH_REM.BATCH_ID = REM_BAT.BATCH_ID(+)
         AND REM_BAT.TYPE(+) = 'REMITTANCE'
         AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
         AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
         AND CRH_CURRENT.CURRENT_RECORD_FLAG = NVL ('Y', CR.RECEIPT_NUMBER)
         AND CRH_FIRST_POSTED.BATCH_ID = BAT.BATCH_ID(+)
         AND BAT.TYPE(+) = 'MANUAL'
         AND CRH_FIRST_POSTED.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
         AND CRH_FIRST_POSTED.FIRST_POSTED_RECORD_FLAG(+) = 'Y'
         AND CRH_FIRST_POSTED.BATCH_ID = BAT_BR.BATCH_ID(+)
         AND BAT_BR.TYPE(+) = 'BR'
         AND HOU.ORGANIZATION_ID=CR.ORG_ID
         --AND CR.CASH_RECEIPT_ID=581552
         AND CR.SET_OF_BOOKS_ID = 5
         AND TRUNC(CR.RECEIPT_DATE) BETWEEN '01-Apr-2011' AND '31-jul-2011'
         --and cr.STATUS not in ('REV', 'NSF')