DBA Data[Home] [Help]

VIEW: APPS.AR_OEX_RECEIPTS_VIEW

Source

View Text - Preformatted

SELECT /* PURPOSE: Used by exchange billing.*/ CR.ROWID ROW_ID , CR.ORG_ID org_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.TYPE TYPE , CR.STATUS RECEIPT_STATUS , L_CR_STATUS.MEANING RECEIPT_STATUS_DSP , CR.COMMENTS COMMENTS , CR.EXCHANGE_RATE EXCHANGE_RATE , CR.EXCHANGE_DATE EXCHANGE_RATE_DATE , 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 , CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID , RC.NAME RECEIPT_CLASS_DSP , RC_LOOKUP.MEANING 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 , 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 , 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 , BAT.NAME BATCH_NAME , CR.DEPOSIT_DATE DEPOSIT_DATE , 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 , L_CRH_STATUS.MEANING STATE_DSP , CRH_CURRENT.STATUS STATE , CRH_CURRENT.GL_POSTED_DATE POSTED_DATE , REC_TRX.NAME ACTIVITY , 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.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.CREATED_BY CREATED_BY , CR.CREATION_DATE CREATION_DATE , CR.LAST_UPDATED_BY LAST_UPDATED_BY , CR.LAST_UPDATE_DATE LAST_UPDATE_DATE , CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN , CR.REQUEST_ID REQUEST_ID , CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID , CR.PROGRAM_ID PROGRAM_ID , CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE , CR.ISSUER_NAME , CR.ISSUE_DATE , CR.ISSUER_BANK_BRANCH_ID , 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 , CUST.ORIG_SYSTEM_REFERENCE FROM AP_BANK_BRANCHES REMIT_BANK_BRANCH, AP_BANK_BRANCHES CUST_BANK_BRANCH, AP_BANK_BRANCHES CUST_BANK_BRANCH2, HZ_CUST_ACCOUNTS CUST, HZ_PARTIES PARTY, AR_RECEIPT_METHODS REC_METHOD, AR_RECEIPT_CLASSES RC, AR_LOOKUPS CRH_NOTE_STATUS, AR_LOOKUPS L_REV_CAT, AR_LOOKUPS L_REV_REASON, AR_LOOKUPS RC_LOOKUP, AR_LOOKUPS L_CRH_STATUS, AR_LOOKUPS L_CR_STATUS, AP_BANK_ACCOUNTS_all REMIT_BANK, AP_BANK_ACCOUNTS_all CUST_BANK, AR_CASH_RECEIPT_HISTORY_all CRH_REM, AR_BATCHES_all REM_BAT, AR_RECEIVABLES_TRX_all REC_TRX, AR_PAYMENT_SCHEDULES_all PS, AR_CASH_RECEIPT_HISTORY_all CRH_CURRENT, /* CURRENT_RECORD */ AR_BATCHES_all BAT, AR_CASH_RECEIPTS_all CR, AR_CASH_RECEIPT_HISTORY_all CRH_FIRST_POSTED /* FIRST_POSTED_RECORD */ 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 REMIT_BANK.BANK_ACCOUNT_ID (+)=CR.REMITTANCE_BANK_ACCOUNT_ID and REMIT_BANK.org_id (+)= cr.org_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.org_id (+)= cr.org_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 CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID AND RC_LOOKUP.LOOKUP_CODE = RC.CREATION_METHOD_CODE AND RC_LOOKUP.LOOKUP_TYPE = NVL('RECEIPT_CREATION_METHOD', CR.RECEIPT_NUMBER) AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID (+) and CR.org_id = REC_TRX.org_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_CRH_STATUS.LOOKUP_TYPE = NVL('RECEIPT_CREATION_STATUS',CR.RECEIPT_NUMBER) AND L_CRH_STATUS.LOOKUP_CODE = CRH_CURRENT.STATUS AND L_CR_STATUS.LOOKUP_TYPE = NVL('CHECK_STATUS',CR.RECEIPT_NUMBER) AND L_CR_STATUS.LOOKUP_CODE = CR.STATUS AND CRH_REM.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID and CRH_REM.org_id (+)= cr.org_id AND NOT EXISTS ( SELECT CASH_RECEIPT_HISTORY_ID FROM AR_CASH_RECEIPT_HISTORY_all CRH3 WHERE CRH3.STATUS = 'REMITTED' and crh3.org_id = CR.org_id 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.org_id (+)= CRH_REM.org_id AND REM_BAT.TYPE (+) = 'REMITTANCE' AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID AND PS.org_id(+) = CR.org_id AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND CRH_CURRENT.org_id = CR.org_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' UNION ALL SELECT /* THIS PART OF THE UNION FETCHES APPROVED RECEIPTS THAT ARE EXCLUDED FROM THE PREVIOUS SELECT, BECAUSE THEY DON't have a first_posted_record */ cr.ROWID ROW_ID, cr.org_id org_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.type type, cr.status receipt_status, l_cr_status.meaning receipt_status_dsp, cr.comments comments, cr.exchange_rate exchange_rate, cr.exchange_date exchange_rate_date, 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, cr.receipt_method_id receipt_method_id, rc.name receipt_class_dsp, rc_lookup.meaning 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, 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, LENGTH(CUST_BANK.BANK_ACCOUNT_NUM)-3, LENGTH(CUST_BANK.BANK_ACCOUNT_NUM)), LENGTH(CUST_BANK.BANK_ACCOUNT_NUM),'*')) CUSTOMER_BANK_ACCOUNT, 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, TO_NUMBER(NULL) batch_id, TO_CHAR(NULL) batch_name, cr.deposit_date deposit_date, 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, l_crh_status.meaning state_dsp, crh_current.status state, crh_current.gl_posted_date posted_date, rec_trx.name activity, cr.receivables_trx_id receivables_trx_id, crh_current.gl_posted_date gl_posted_date, crh_current.posting_control_id posting_control_id, DECODE(crh_current.status,'APPROVED',TO_DATE(NULL),'REVERSED', crh_current.gl_date, TO_DATE(NULL)) gl_date, 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.created_by created_by, cr.creation_date creation_date, cr.last_updated_by last_updated_by, cr.last_update_date last_update_date, cr.last_update_login last_update_login, cr.request_id request_id, cr.program_application_id program_application_id, cr.program_id program_id, cr.program_update_date program_update_date , CR.ISSUER_NAME , CR.ISSUE_DATE , CR.ISSUER_BANK_BRANCH_ID , 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 , CUST.ORIG_SYSTEM_REFERENCE FROM ap_bank_branches remit_bank_branch, ap_bank_branches cust_bank_branch, ap_bank_branches cust_bank_branch2, hz_cust_accounts cust, hz_parties party, ar_receipt_methods rec_method, ar_receipt_classes rc, AR_LOOKUPS CRH_NOTE_STATUS, ar_lookups l_rev_cat, ar_lookups l_rev_reason, ar_lookups rc_lookup, ar_lookups l_crh_status, ar_lookups l_cr_status, ap_bank_accounts_all remit_bank, ap_bank_accounts_all cust_bank, ar_cash_receipt_history_all crh_rem, ar_batches_all rem_bat, ar_receivables_trx_all rec_trx, ar_payment_schedules_all ps, ar_cash_receipt_history_all crh_current, /* current record */ ar_cash_receipts_all cr 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 remit_bank.bank_account_id (+)= cr.remittance_bank_account_id and REMIT_BANK.org_id (+)= cr.org_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.org_id (+)= cr.org_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 cr.receipt_method_id = rec_method.receipt_method_id AND rec_method.receipt_class_id = rc.receipt_class_id AND rc_lookup.lookup_code = rc.creation_method_code AND rc_lookup.lookup_type = 'RECEIPT_CREATION_METHOD' AND cr.receivables_trx_id = rec_trx.receivables_trx_id (+) and CR.org_id = REC_TRX.org_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_crh_status.lookup_type = 'RECEIPT_CREATION_STATUS' AND l_crh_status.lookup_code = crh_current.status AND l_cr_status.lookup_type = 'CHECK_STATUS' AND l_cr_status.lookup_code = cr.status AND crh_rem.cash_receipt_id (+) = cr.cash_receipt_id and CRH_REM.org_id (+)= cr.org_id AND not exists ( SELECT cash_receipt_history_id FROM ar_cash_receipt_history_all crh3 WHERE crh3.status = 'REMITTED' AND crh3.org_id = cr.org_id 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 crh_rem.org_id = REM_BAT.org_id (+) AND rem_bat.type (+) = 'REMITTANCE' AND ps.cash_receipt_id(+) = cr.cash_receipt_id AND PS.org_id(+) = CR.org_id AND crh_current.cash_receipt_id = cr.cash_receipt_id AND CRH_CURRENT.org_id = CR.org_id AND crh_current.current_record_flag = 'Y' AND (crh_current.status = 'APPROVED' or crh_current.status = 'REVERSED') AND NOT EXISTS ( SELECT cash_receipt_history_id FROM ar_cash_receipt_history_all crh_fp WHERE crh_fp.cash_receipt_id = crh_current.cash_receipt_id and crh_fp.org_id = crh_current.org_id AND crh_fp.first_posted_record_flag = 'Y')
View Text - HTML Formatted

SELECT /* PURPOSE: USED BY EXCHANGE BILLING.*/ CR.ROWID ROW_ID
, CR.ORG_ID ORG_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.TYPE TYPE
, CR.STATUS RECEIPT_STATUS
, L_CR_STATUS.MEANING RECEIPT_STATUS_DSP
, CR.COMMENTS COMMENTS
, CR.EXCHANGE_RATE EXCHANGE_RATE
, CR.EXCHANGE_DATE EXCHANGE_RATE_DATE
, 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
, CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, RC.NAME RECEIPT_CLASS_DSP
, RC_LOOKUP.MEANING 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
, 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
, 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
, BAT.NAME BATCH_NAME
, CR.DEPOSIT_DATE DEPOSIT_DATE
, 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
, L_CRH_STATUS.MEANING STATE_DSP
, CRH_CURRENT.STATUS STATE
, CRH_CURRENT.GL_POSTED_DATE POSTED_DATE
, REC_TRX.NAME ACTIVITY
, 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.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.CREATED_BY CREATED_BY
, CR.CREATION_DATE CREATION_DATE
, CR.LAST_UPDATED_BY LAST_UPDATED_BY
, CR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CR.REQUEST_ID REQUEST_ID
, CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CR.PROGRAM_ID PROGRAM_ID
, CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, CR.ISSUER_NAME
, CR.ISSUE_DATE
, CR.ISSUER_BANK_BRANCH_ID
, 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
, CUST.ORIG_SYSTEM_REFERENCE
FROM AP_BANK_BRANCHES REMIT_BANK_BRANCH
, AP_BANK_BRANCHES CUST_BANK_BRANCH
, AP_BANK_BRANCHES CUST_BANK_BRANCH2
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, AR_RECEIPT_METHODS REC_METHOD
, AR_RECEIPT_CLASSES RC
, AR_LOOKUPS CRH_NOTE_STATUS
, AR_LOOKUPS L_REV_CAT
, AR_LOOKUPS L_REV_REASON
, AR_LOOKUPS RC_LOOKUP
, AR_LOOKUPS L_CRH_STATUS
, AR_LOOKUPS L_CR_STATUS
, AP_BANK_ACCOUNTS_ALL REMIT_BANK
, AP_BANK_ACCOUNTS_ALL CUST_BANK
, AR_CASH_RECEIPT_HISTORY_ALL CRH_REM
, AR_BATCHES_ALL REM_BAT
, AR_RECEIVABLES_TRX_ALL REC_TRX
, AR_PAYMENT_SCHEDULES_ALL PS
, AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT
, /* CURRENT_RECORD */ AR_BATCHES_ALL BAT
, AR_CASH_RECEIPTS_ALL CR
, AR_CASH_RECEIPT_HISTORY_ALL CRH_FIRST_POSTED /* FIRST_POSTED_RECORD */
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 REMIT_BANK.BANK_ACCOUNT_ID (+)=CR.REMITTANCE_BANK_ACCOUNT_ID
AND REMIT_BANK.ORG_ID (+)= CR.ORG_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.ORG_ID (+)= CR.ORG_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 CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID
AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND RC_LOOKUP.LOOKUP_CODE = RC.CREATION_METHOD_CODE
AND RC_LOOKUP.LOOKUP_TYPE = NVL('RECEIPT_CREATION_METHOD'
, CR.RECEIPT_NUMBER)
AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID (+)
AND CR.ORG_ID = REC_TRX.ORG_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_CRH_STATUS.LOOKUP_TYPE = NVL('RECEIPT_CREATION_STATUS'
, CR.RECEIPT_NUMBER)
AND L_CRH_STATUS.LOOKUP_CODE = CRH_CURRENT.STATUS
AND L_CR_STATUS.LOOKUP_TYPE = NVL('CHECK_STATUS'
, CR.RECEIPT_NUMBER)
AND L_CR_STATUS.LOOKUP_CODE = CR.STATUS
AND CRH_REM.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND CRH_REM.ORG_ID (+)= CR.ORG_ID
AND NOT EXISTS ( SELECT CASH_RECEIPT_HISTORY_ID
FROM AR_CASH_RECEIPT_HISTORY_ALL CRH3
WHERE CRH3.STATUS = 'REMITTED'
AND CRH3.ORG_ID = CR.ORG_ID
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.ORG_ID (+)= CRH_REM.ORG_ID
AND REM_BAT.TYPE (+) = 'REMITTANCE'
AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND PS.ORG_ID(+) = CR.ORG_ID
AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_CURRENT.ORG_ID = CR.ORG_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' UNION ALL SELECT /* THIS PART OF THE UNION FETCHES APPROVED RECEIPTS THAT ARE EXCLUDED
FROM THE PREVIOUS SELECT
, BECAUSE THEY DON'T HAVE A FIRST_POSTED_RECORD */ CR.ROWID ROW_ID
, CR.ORG_ID ORG_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.TYPE TYPE
, CR.STATUS RECEIPT_STATUS
, L_CR_STATUS.MEANING RECEIPT_STATUS_DSP
, CR.COMMENTS COMMENTS
, CR.EXCHANGE_RATE EXCHANGE_RATE
, CR.EXCHANGE_DATE EXCHANGE_RATE_DATE
, 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
, CR.RECEIPT_METHOD_ID RECEIPT_METHOD_ID
, RC.NAME RECEIPT_CLASS_DSP
, RC_LOOKUP.MEANING 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
, 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
, LENGTH(CUST_BANK.BANK_ACCOUNT_NUM)-3
, LENGTH(CUST_BANK.BANK_ACCOUNT_NUM))
, LENGTH(CUST_BANK.BANK_ACCOUNT_NUM)
, '*')) CUSTOMER_BANK_ACCOUNT
, 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
, TO_NUMBER(NULL) BATCH_ID
, TO_CHAR(NULL) BATCH_NAME
, CR.DEPOSIT_DATE DEPOSIT_DATE
, 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
, L_CRH_STATUS.MEANING STATE_DSP
, CRH_CURRENT.STATUS STATE
, CRH_CURRENT.GL_POSTED_DATE POSTED_DATE
, REC_TRX.NAME ACTIVITY
, CR.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID
, CRH_CURRENT.GL_POSTED_DATE GL_POSTED_DATE
, CRH_CURRENT.POSTING_CONTROL_ID POSTING_CONTROL_ID
, DECODE(CRH_CURRENT.STATUS
, 'APPROVED'
, TO_DATE(NULL)
, 'REVERSED'
, CRH_CURRENT.GL_DATE
, TO_DATE(NULL)) GL_DATE
, 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.CREATED_BY CREATED_BY
, CR.CREATION_DATE CREATION_DATE
, CR.LAST_UPDATED_BY LAST_UPDATED_BY
, CR.LAST_UPDATE_DATE LAST_UPDATE_DATE
, CR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CR.REQUEST_ID REQUEST_ID
, CR.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, CR.PROGRAM_ID PROGRAM_ID
, CR.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, CR.ISSUER_NAME
, CR.ISSUE_DATE
, CR.ISSUER_BANK_BRANCH_ID
, 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
, CUST.ORIG_SYSTEM_REFERENCE
FROM AP_BANK_BRANCHES REMIT_BANK_BRANCH
, AP_BANK_BRANCHES CUST_BANK_BRANCH
, AP_BANK_BRANCHES CUST_BANK_BRANCH2
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, AR_RECEIPT_METHODS REC_METHOD
, AR_RECEIPT_CLASSES RC
, AR_LOOKUPS CRH_NOTE_STATUS
, AR_LOOKUPS L_REV_CAT
, AR_LOOKUPS L_REV_REASON
, AR_LOOKUPS RC_LOOKUP
, AR_LOOKUPS L_CRH_STATUS
, AR_LOOKUPS L_CR_STATUS
, AP_BANK_ACCOUNTS_ALL REMIT_BANK
, AP_BANK_ACCOUNTS_ALL CUST_BANK
, AR_CASH_RECEIPT_HISTORY_ALL CRH_REM
, AR_BATCHES_ALL REM_BAT
, AR_RECEIVABLES_TRX_ALL REC_TRX
, AR_PAYMENT_SCHEDULES_ALL PS
, AR_CASH_RECEIPT_HISTORY_ALL CRH_CURRENT
, /* CURRENT RECORD */ AR_CASH_RECEIPTS_ALL CR
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 REMIT_BANK.BANK_ACCOUNT_ID (+)= CR.REMITTANCE_BANK_ACCOUNT_ID
AND REMIT_BANK.ORG_ID (+)= CR.ORG_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.ORG_ID (+)= CR.ORG_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 CR.RECEIPT_METHOD_ID = REC_METHOD.RECEIPT_METHOD_ID
AND REC_METHOD.RECEIPT_CLASS_ID = RC.RECEIPT_CLASS_ID
AND RC_LOOKUP.LOOKUP_CODE = RC.CREATION_METHOD_CODE
AND RC_LOOKUP.LOOKUP_TYPE = 'RECEIPT_CREATION_METHOD'
AND CR.RECEIVABLES_TRX_ID = REC_TRX.RECEIVABLES_TRX_ID (+)
AND CR.ORG_ID = REC_TRX.ORG_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_CRH_STATUS.LOOKUP_TYPE = 'RECEIPT_CREATION_STATUS'
AND L_CRH_STATUS.LOOKUP_CODE = CRH_CURRENT.STATUS
AND L_CR_STATUS.LOOKUP_TYPE = 'CHECK_STATUS'
AND L_CR_STATUS.LOOKUP_CODE = CR.STATUS
AND CRH_REM.CASH_RECEIPT_ID (+) = CR.CASH_RECEIPT_ID
AND CRH_REM.ORG_ID (+)= CR.ORG_ID
AND NOT EXISTS ( SELECT CASH_RECEIPT_HISTORY_ID
FROM AR_CASH_RECEIPT_HISTORY_ALL CRH3
WHERE CRH3.STATUS = 'REMITTED'
AND CRH3.ORG_ID = CR.ORG_ID
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 CRH_REM.ORG_ID = REM_BAT.ORG_ID (+)
AND REM_BAT.TYPE (+) = 'REMITTANCE'
AND PS.CASH_RECEIPT_ID(+) = CR.CASH_RECEIPT_ID
AND PS.ORG_ID(+) = CR.ORG_ID
AND CRH_CURRENT.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND CRH_CURRENT.ORG_ID = CR.ORG_ID
AND CRH_CURRENT.CURRENT_RECORD_FLAG = 'Y'
AND (CRH_CURRENT.STATUS = 'APPROVED' OR CRH_CURRENT.STATUS = 'REVERSED')
AND NOT EXISTS ( SELECT CASH_RECEIPT_HISTORY_ID
FROM AR_CASH_RECEIPT_HISTORY_ALL CRH_FP
WHERE CRH_FP.CASH_RECEIPT_ID = CRH_CURRENT.CASH_RECEIPT_ID
AND CRH_FP.ORG_ID = CRH_CURRENT.ORG_ID
AND CRH_FP.FIRST_POSTED_RECORD_FLAG = 'Y')