VIEW: APPS.AR_RX_OTHER_APPLICATIONS_V

Source

View Text - Preformatted

SELECT GL.NAME SET_OF_BOOKS , ARP_PROCESS_WRITEOFF.BALANCING_SEGMENT(APP.CODE_COMBINATION_ID) COMPANY_SEGMENT , CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER , SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME , SU.LOCATION LOCATION_NAME , CR.RECEIPT_NUMBER RECEIPT_NUMBER , CR.RECEIPT_DATE RECEIPT_DATE , APP.APPLY_DATE APPLY_DATE , APP.GL_DATE GL_DATE , CR.CURRENCY_CODE CURRENCY_CODE , DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1,PS.TRX_NUMBER,NULL) APPLICATION_TYPE , CR.AMOUNT RECEIPT_AMOUNT , APP.AMOUNT_APPLIED AMOUNT_APPLIED , APP.ACCTD_AMOUNT_APPLIED_FROM AMOUNT_APPLIED_FROM , DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID), -1,ART.NAME,NULL) REC_ACTIVITY_NAME , APP.COMMENTS COMMENTS , RB.NAME BATCH_NAME , BS.NAME BATCH_SOURCE_NAME , CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE , CRH.ACCTD_AMOUNT ACCTD_AMOUNT , CR.POSTMARK_DATE , CR.DEPOSIT_DATE , RM.NAME RECEIPT_METHOD_NAME , BB.BANK_NAME BANK_NAME , BB.BANK_BRANCH_NAME BANK_BRANCH_NAME , CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER , CBA.CURRENCY_CODE BANK_ACCOUNT_CURRENCY , FA_RX_FLEX_PKG.GET_VALUE(101,'GL#',GL.CHART_OF_ACCOUNTS_ID, 'ALL',APP.CODE_COMBINATION_ID) ACCOUNTING_FLEX_FIELD ,arpt_sql_func_util.get_lookup_meaning(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID, -7, 'AR_PREPAYMENT_TYPE', 'APPLICATION_REF_TYPE'), APP.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE ,APP.APPLICATION_REF_NUM ,RBFIRST.NAME RECEIPT_BATCH_NAME FROM AR_RECEIVABLE_APPLICATIONS APP , AR_CASH_RECEIPTS CR , AR_CASH_RECEIPT_HISTORY CRH , AR_PAYMENT_SCHEDULES PS , HZ_CUST_ACCOUNTS CUST , HZ_PARTIES PARTY , AR_BATCH_SOURCES BS , AR_BATCHES RB , HZ_CUST_SITE_USES SU , AR_RECEIVABLES_TRX ART , GL_SETS_OF_BOOKS GL , GL_CODE_COMBINATIONS GLC , AR_RECEIPT_METHODS RM , CE_BANK_ACCOUNTS CBA , CE_BANK_ACCT_USES BA , CE_BANK_BRANCHES_V BB , AR_BATCHES RBFIRST , AR_CASH_RECEIPT_HISTORY CRHFIRST WHERE APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID AND APP.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND CRH.CURRENT_RECORD_FLAG = 'Y' AND APP.STATUS IN ('ACTIVITY','OTHER ACC') AND APP.AMOUNT_APPLIED <> 0 AND APP.DISPLAY = 'Y' AND BS.BATCH_SOURCE_ID (+) = RB.BATCH_SOURCE_ID AND RB.BATCH_ID (+) = CR.SELECTED_REMITTANCE_BATCH_ID AND RM.RECEIPT_METHOD_ID (+) = CR.RECEIPT_METHOD_ID AND CBA.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID AND CR.REMIT_BANK_ACCT_USE_ID = BA.BANK_ACCT_USE_ID(+) AND BB.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID AND CUST.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER AND CUST.PARTY_ID = PARTY.PARTY_ID(+) AND SU.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID AND ART.RECEIVABLES_TRX_ID(+) = APP.RECEIVABLES_TRX_ID AND GL.SET_OF_BOOKS_ID = CR.SET_OF_BOOKS_ID AND GLC.CODE_COMBINATION_ID(+) = APP.CODE_COMBINATION_ID AND CR.CASH_RECEIPT_ID = CRHFIRST.CASH_RECEIPT_ID AND CRHFIRST.FIRST_POSTED_RECORD_FLAG = 'Y' AND CRHFIRST.BATCH_ID = RBFIRST.BATCH_ID(+)

View Text - HTML Formatted

SELECT GL.NAME SET_OF_BOOKS
, ARP_PROCESS_WRITEOFF.BALANCING_SEGMENT(APP.CODE_COMBINATION_ID) COMPANY_SEGMENT
, CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION LOCATION_NAME
, CR.RECEIPT_NUMBER RECEIPT_NUMBER
, CR.RECEIPT_DATE RECEIPT_DATE
, APP.APPLY_DATE APPLY_DATE
, APP.GL_DATE GL_DATE
, CR.CURRENCY_CODE CURRENCY_CODE
, DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, PS.TRX_NUMBER
, NULL) APPLICATION_TYPE
, CR.AMOUNT RECEIPT_AMOUNT
, APP.AMOUNT_APPLIED AMOUNT_APPLIED
, APP.ACCTD_AMOUNT_APPLIED_FROM AMOUNT_APPLIED_FROM
, DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
, -1
, ART.NAME
, NULL) REC_ACTIVITY_NAME
, APP.COMMENTS COMMENTS
, RB.NAME BATCH_NAME
, BS.NAME BATCH_SOURCE_NAME
, CR.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, CRH.ACCTD_AMOUNT ACCTD_AMOUNT
, CR.POSTMARK_DATE
, CR.DEPOSIT_DATE
, RM.NAME RECEIPT_METHOD_NAME
, BB.BANK_NAME BANK_NAME
, BB.BANK_BRANCH_NAME BANK_BRANCH_NAME
, CBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER
, CBA.CURRENCY_CODE BANK_ACCOUNT_CURRENCY
, FA_RX_FLEX_PKG.GET_VALUE(101
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID
, 'ALL'
, APP.CODE_COMBINATION_ID) ACCOUNTING_FLEX_FIELD
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
, -7
, 'AR_PREPAYMENT_TYPE'
, 'APPLICATION_REF_TYPE')
, APP.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE
, APP.APPLICATION_REF_NUM
, RBFIRST.NAME RECEIPT_BATCH_NAME
FROM AR_RECEIVABLE_APPLICATIONS APP
, AR_CASH_RECEIPTS CR
, AR_CASH_RECEIPT_HISTORY CRH
, AR_PAYMENT_SCHEDULES PS
, HZ_CUST_ACCOUNTS CUST
, HZ_PARTIES PARTY
, AR_BATCH_SOURCES BS
, AR_BATCHES RB
, HZ_CUST_SITE_USES SU
, AR_RECEIVABLES_TRX ART
, GL_SETS_OF_BOOKS GL
, GL_CODE_COMBINATIONS GLC
, AR_RECEIPT_METHODS RM
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES BA
, CE_BANK_BRANCHES_V BB
, AR_BATCHES RBFIRST
, AR_CASH_RECEIPT_HISTORY CRHFIRST
WHERE APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
AND APP.CASH_RECEIPT_ID = CRH.CASH_RECEIPT_ID
AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND CRH.CURRENT_RECORD_FLAG = 'Y'
AND APP.STATUS IN ('ACTIVITY'
, 'OTHER ACC')
AND APP.AMOUNT_APPLIED <> 0
AND APP.DISPLAY = 'Y'
AND BS.BATCH_SOURCE_ID (+) = RB.BATCH_SOURCE_ID
AND RB.BATCH_ID (+) = CR.SELECTED_REMITTANCE_BATCH_ID
AND RM.RECEIPT_METHOD_ID (+) = CR.RECEIPT_METHOD_ID
AND CBA.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID
AND CR.REMIT_BANK_ACCT_USE_ID = BA.BANK_ACCT_USE_ID(+)
AND BB.BRANCH_PARTY_ID = CBA.BANK_BRANCH_ID
AND CUST.CUST_ACCOUNT_ID(+) = CR.PAY_FROM_CUSTOMER
AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
AND SU.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID
AND ART.RECEIVABLES_TRX_ID(+) = APP.RECEIVABLES_TRX_ID
AND GL.SET_OF_BOOKS_ID = CR.SET_OF_BOOKS_ID
AND GLC.CODE_COMBINATION_ID(+) = APP.CODE_COMBINATION_ID
AND CR.CASH_RECEIPT_ID = CRHFIRST.CASH_RECEIPT_ID
AND CRHFIRST.FIRST_POSTED_RECORD_FLAG = 'Y'
AND CRHFIRST.BATCH_ID = RBFIRST.BATCH_ID(+)