JA India - Cash/Bank Book Report- Not Supported: Reserved For Future Use

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