JA India - Cash/Bank Book Report- Not Supported: Reserved For Future Use
Description
Categories: BI Publisher
Imported from BI Publisher
Description: India - Cash/Bank Book Report
Application: Asia/Pacific Localizations
Source: India - Cash/Bank Book Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINBBR_XML
DB package: JA_JAINBBR_XMLP_PKG
Description: India - Cash/Bank Book Report
Application: Asia/Pacific Localizations
Source: India - Cash/Bank Book Report (XML) - Not Supported: Reserved For Future Use
Short Name: JAINBBR_XML
DB package: JA_JAINBBR_XMLP_PKG
Run
JA India - Cash/Bank Book Report- Not Supported: Reserved For Future Use and other Oracle EBS reports with Blitz Report™ on our demo environment
select aip.accounting_date GL_DATE, 'P' TYPE, api.doc_sequence_value VOUCHER_NO, api.creation_date VOUCHER_DATE, to_char(apc.check_number) CHECK_NUMBER, TO_CHAR(apc.check_date, 'DD-MON-YYYY') CHECK_DATE, apc.vendor_name VENDOR_NAME, api.accts_pay_code_combination_id ACCOUNT_CODE, NVL(api.description,api.invoice_num) DESCRIPTION, TO_CHAR( decode( sign( SUM(aip.amount)), -1, SUM(aip.amount*NVL(aip.exchange_rate,1))*-1, SUM(0) ), 'fm999999999990.00') RECEIPTS, TO_CHAR( decode( sign( SUM(aip.amount)), 1, SUM(aip.amount*NVL(aip.exchange_rate,1)), SUM(0) ), 'fm999999999990.00') PAYMENTS, to_number(SUM(aip.amount*NVL(aip.exchange_rate,1))) PAYMENTS1, aba.bank_account_name BANK_ACCOUNT_NAME, aba.bank_account_num BANK_ACCOUNT_NUM, JA_JAINBBR_XMLP_PKG.cf_closing_balanceformula(:CF_closing_balance, :CF_open_balance, TO_CHAR ( decode ( sign ( SUM ( aip.amount ) ) , - 1 , SUM ( aip.amount * NVL ( aip.exchange_rate , 1 ) ) * - 1 , SUM ( 0 ) ) , 'fm999999999990.00' ), TO_CHAR ( decode ( sign ( SUM ( aip.amount ) ) , 1 , SUM ( aip.amount * NVL ( aip.exchange_rate , 1 ) ) , SUM ( 0 ) ) , 'fm999999999990.00' )) CF_closing_balance, JA_JAINBBR_XMLP_PKG.cf_balanceformula(TO_CHAR ( decode ( sign ( SUM ( aip.amount ) ) , - 1 , SUM ( aip.amount * NVL ( aip.exchange_rate , 1 ) ) * - 1 , SUM ( 0 ) ) , 'fm999999999990.00' ), TO_CHAR ( decode ( sign ( SUM ( aip.amount ) ) , 1 , SUM ( aip.amount * NVL ( aip.exchange_rate , 1 ) ) , SUM ( 0 ) ) , 'fm999999999990.00' )) CF_balance, JA_JAINBBR_XMLP_PKG.cf_account_codeformula(api.accts_pay_code_combination_id) CF_account, JA_JAINBBR_XMLP_PKG.cf_acct_descformula(api.accts_pay_code_combination_id) CF_ACCT_DESC FROM ap_invoice_payments_all aip, ap_invoices_all api, ap_checks_all apc, ap_bank_accounts_all aba WHERE api.invoice_id = aip.invoice_id AND aip.check_id = apc.check_id AND apc.bank_account_id = aba.bank_account_id AND apc.status_lookup_code IN ('NEGOTIABLE', 'CLEARED','VOIDED', 'RECONCILED UNACCOUNTED', 'RECONCILED', 'CLEARED BUT UNACCOUNTED' ) AND aba.bank_account_id = :p_bank_account_id AND aip.INVOICE_PAYMENT_TYPE is null AND aip.accounting_date BETWEEN NVL(:p_start_date,aip.accounting_date) AND NVL(:p_end_date,aip.accounting_date) AND (api.org_id IS NULL OR api.org_id = :p_org_id) GROUP BY aip.accounting_date, api.doc_sequence_value, api.creation_date, apc.check_number, apc.check_date, apc.vendor_name, api.accts_pay_code_combination_id, nvl(api.description,api.invoice_num) , aba.bank_account_name, aba.bank_account_num UNION ALL SELECT apid.accounting_date GL_DATE, 'I' TYPE, api.doc_sequence_value VOUCHER_NO, api.creation_date VOUCHER_DATE, api.invoice_num CHECK_NUMBER, TO_CHAR(api.invoice_date, 'DD-MON-YYYY') CHECK_DATE, POV.vendor_name VENDOR_NAME, api.accts_pay_code_combination_id ACCOUNT_CODE, nvl(api.description,api.invoice_num) DESCRIPTION, to_CHAR(SUM(api.invoice_amount*NVL(api.exchange_rate,1)),'fm999999999990.00') RECEIPTS, TO_CHAR(SUM(0), 'fm999999999990.00') PAYMENTS, to_number(SUM(0)) PAYMENTS1, aba.bank_account_name BANK_ACCOUNT_NAME, aba.bank_account_num BANK_ACCOUNT_NUM, JA_JAINBBR_XMLP_PKG.cf_closing_balanceformula(:CF_closing_balance, :CF_open_balance, to_CHAR(SUM(api.invoice_amount*NVL(api.exchange_rate,1)),'fm999999999990.00'),TO_CHAR(SUM(0), 'fm999999999990.00') ) CF_closing_balance, JA_JAINBBR_XMLP_PKG.cf_balanceformula(to_CHAR(SUM(api.invoice_amount*NVL(api.exchange_rate,1)),'fm999999999990.00'), TO_CHAR(SUM(0), 'fm999999999990.00')) CF_balance, JA_JAINBBR_XMLP_PKG.cf_account_codeformula(api.accts_pay_code_combination_id ) CF_account, JA_JAINBBR_XMLP_PKG.cf_acct_descformula(api.accts_pay_code_combination_id) CF_ACCT_DESC FROM ap_invoice_distributions_all apid, ap_invoice_lines_all apla, ap_invoices_all api, po_vendors POV, ap_bank_accounts_all aba WHERE api.invoice_id=apid.invoice_id AND apla.invoice_id = apid.invoice_id AND apla.line_number = apid.invoice_line_number AND api.vendor_id = pov.vendor_id AND aba.bank_account_id = :p_bank_account_id AND aba.asset_code_combination_id=apid.dist_code_combination_id AND apid.match_status_flag = 'A' AND apid.accounting_date BETWEEN NVL(:p_start_date,apid.accounting_date) AND NVL(:p_end_date,apid.accounting_date) AND (api.org_id IS NULL OR api.org_id = :p_org_id) GROUP BY apid.accounting_date, api.doc_sequence_value, api.creation_date, api.invoice_num, api.invoice_date, pov.vendor_name, api.accts_pay_code_combination_id, nvl(api.description, api.invoice_num ), aba.bank_account_name, aba.bank_account_num UNION ALL SELECT acrh.gl_date GL_DATE, DECODE(acrh.status, 'REVERSED','REV','R') TYPE, acr.doc_sequence_value VOUCHER_NO, acr.creation_date VOUCHER_DATE, NULL CHECK_NUMBER, NULL CHECK_DATE, SUBSTR(hp.PARTY_NAME,1,50) VENDOR_NAME, acrh.account_code_combination_id ACCOUNT_CODE, nvl(acr.comments,acr.receipt_number) DESCRIPTION, TO_CHAR( SUM( DECODE(acrh.status, 'CLEARED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)), 'REMITTED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)), 'CONFIRMED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)))) , 'fm999999999990.00') RECEIPTS, TO_CHAR( SUM( DECODE(acrh.status, 'REVERSED',(acrh.amount*NVL(acrh.exchange_rate,1)))), 'fm999999999990.00') PAYMENTS, to_number(to_char(sum(0))) PAYMENTS1, aba.bank_account_name BANK_ACCOUNT_NAME, aba.bank_account_num BANK_ACCOUNT_NUM, JA_JAINBBR_XMLP_PKG.cf_closing_balanceformula(:CF_closing_balance, :CF_open_balance,TO_CHAR(SUM(DECODE(acrh.status, 'CLEARED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)),'REMITTED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)),'CONFIRMED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)))) , 'fm999999999990.00') , TO_CHAR(SUM(DECODE(acrh.status, 'REVERSED',(acrh.amount*NVL(acrh.exchange_rate,1)))), 'fm999999999990.00')) CF_closing_balance, JA_JAINBBR_XMLP_PKG.cf_balanceformula(TO_CHAR(SUM(DECODE(acrh.status, 'CLEARED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)),'REMITTED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)),'CONFIRMED',(NVL(acrh.amount,0)*NVL(acrh.exchange_rate,1)))) , 'fm999999999990.00'),TO_CHAR(SUM(DECODE(acrh.status, 'REVERSED',(acrh.amount*NVL(acrh.exchange_rate,1)))), 'fm999999999990.00') ) CF_balance, JA_JAINBBR_XMLP_PKG.cf_account_codeformula(acrh.account_code_combination_id) CF_account, JA_JAINBBR_XMLP_PKG.cf_acct_descformula(acrh.account_code_combination_id) CF_ACCT_DESC FROM ar_cash_receipt_history_all acrh, ar_cash_receipts_all acr, hz_parties hp, hz_cust_accounts hca, ap_bank_accounts_all aba WHERE acrh.cash_receipt_id = acr.cash_receipt_id AND acr.remittance_bank_account_id = aba.bank_account_id AND hca.party_id = hp.party_id(+) AND acr.pay_from_customer = hca.cust_account_id(+) AND acrh.status IN ('CLEARED', 'REMITTED', 'CONFIRMED','REVERSED') AND aba.bank_account_id = :p_bank_account_id AND acrh.gl_date BETWEEN :p_start_date AND :p_end_date AND (acr.org_id IS NULL OR acr.org_id = :p_org_id) AND ( ( acr.reversal_date IS NOT NULL AND acrh.STATUS = 'REVERSED' ) OR ( acrh.CASH_RECEIPT_HISTORY_ID IN (SELECT MIN(INCRH.CASH_RECEIPT_HISTORY_ID) FROM AR_CASH_RECEIPT_HISTORY_ALL INCRH WHERE INCRH.CASH_RECEIPT_ID = acr.CASH_RECEIPT_ID AND INCRH.STATUS <> 'REVERSED' ) ) ) GROUP BY acrh.gl_date, acrh.status, acr.doc_sequence_value, acr.creation_date, SUBSTR(hp.PARTY_NAME,1,50), acrh.account_code_combination_id, nvl(acr.comments , acr.receipt_number), aba.bank_account_name, aba.bank_account_num UNION ALL SELECT glh.default_effective_date GL_DATE, 'G' TYPE, glh.doc_sequence_value VOUCHER_NO, glh.creation_date VOUCHER_DATE, NULL CHECK_NUMBER, NULL CHECK_DATE, NULL VENDOR_NAME, gll.code_combination_id ACCOUNT_CODE, gll.description DESCRIPTION, DECODE(SUM(accounted_dr), NULL, NULL, TO_CHAR(SUM(accounted_dr), 'fm999999999990.00')) RECEIPTS, DECODE(SUM(accounted_cr), NULL, NULL, TO_CHAR(SUM(accounted_cr), 'fm999999999990.00')) PAYMENTS, to_number(DECODE(SUM(accounted_cr), NULL, NULL, SUM(accounted_cr))) PAYMENTS1, aba.bank_account_name BANK_ACCOUNT_NAME, aba.bank_account_num BANK_ACCOUNT_NUM, JA_JAINBBR_XMLP_PKG.cf_closing_balanceformula(:CF_closing_balance, :CF_open_balance,DECODE(SUM(accounted_dr), NULL, NULL, TO_CHAR(SUM(accounted_dr), 'fm999999999990.00')) , DECODE(SUM(accounted_cr), NULL, NULL, TO_CHAR(SUM(accounted_cr), 'fm999999999990.00'))) CF_closing_balance, JA_JAINBBR_XMLP_PKG.cf_balanceformula(DECODE(SUM(accounted_dr), NULL, NULL, TO_CHAR(SUM(accounted_dr), 'fm999999999990.00')), DECODE(SUM(accounted_cr), NULL, NULL, TO_CHAR(SUM(accounted_cr), 'fm999999999990.00'))) CF_balance, JA_JAINBBR_XMLP_PKG.cf_account_codeformula(gll.code_combination_id ) CF_account, JA_JAINBBR_XMLP_PKG.cf_acct_descformula(gll.code_combination_id) CF_ACCT_DESC FROM gl_je_headers glh, gl_je_lines gll, ap_bank_accounts_all aba WHERE glh.je_header_id = gll.je_header_id AND glh.ledger_id = gll.ledger_id AND gll.ledger_id = :lp_set_of_books_id AND aba.asset_code_combination_id = gll.code_combination_id AND aba.bank_account_id = :p_bank_account_id AND glh.je_source NOT IN ('Payables', 'Receivables') AND glh.default_effective_date BETWEEN :p_start_date AND :p_end_date AND (aba.org_id IS NULL OR aba.org_id = :p_org_id) GROUP BY glh.default_effective_date, gll.code_combination_id, gll.description, aba.bank_account_name, aba.bank_account_num, glh.doc_sequence_value, glh.creation_date ORDER BY 1 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Operating Unit |
|
LOV | |
| Bank Name |
|
LOV Oracle | |
| Account Number |
|
LOV Oracle | |
| Book Type |
|
LOV Oracle | |
| Start Date |
|
Date | |
| To Date |
|
Date |
