JA India - Cash/Bank Book - draft

Description
Categories: BI Publisher
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
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
Chart of Accounts
 
Number
Account Number
 
Org ID
 
Number
To Date
 
Date
Start Date
 
Date
Book Type
 
LOV Oracle
Account Number
 
LOV Oracle
Bank Name
 
LOV Oracle
Ask a question