AR Receipt Final Query

Query for  AR Receipts

PURPOSE: THE VIEW AR_CASH_RECEIPTS_V IS USED IN THE RECEIPTS GATEWAY AS THE BASETABLE FOR THE RECEIPTS FORM

/* Formatted on 08-12-2022 13:53:30 */ 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                       ,          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, /*  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 HOU.ORGANIZATION_ID= &org_id          --AND TRUNC(CR.RECEIPT_DATE) BETWEEN '01-Jan-2019' AND '02-jan-2019'          AND TRUNC(CRH_FIRST_POSTED.GL_DATE) BETWEEN '01-Jan-2019' AND '02-jan-2019'          --and cr.STATUS not in ('REV', 'NSF');