CE Transactions Available for Reconciliation - draft

Description
Categories: BI Publisher, Financials
Application: Cash Management
Source: Transactions Available for Reconciliation Report (XML) - Not Supported: Reserved For Future Use
Short Name: CEXAVTRX_XML
DB package: CE_CEXAVTRX_XMLP_PKG
SELECT 	CE_AVAILABLE_TRANSACTIONS_V.bank_account_name	bank_account_name,
	CE_AVAILABLE_TRANSACTIONS_V.bank_account_num	bank_account_num,
	bank_name				bank_name,
	bank_branch_name				bank_branch_name,
	DECODE(clearing_trx_type,'MISC','CASH',
	         'PAY', 'PAYROLL',
 	         'PAY_EFT', 'PAYROLL',
                           'XTR_LINE', DECODE(trx_type, 'PAYMENT', 'ROI-P',
			   'CASH', 'ROI-R'), 
                            'ROI_LINE', DECODE(trx_type, 'PAYMENT', 'ROI-P',
			   'CASH', 'ROI-R'), clearing_trx_type)	type,
	decode(clearing_trx_type, 'XTR_LINE', 'ROI_LINE', 
			        'PAY_EFT',   'PAY', clearing_trx_type)	break_type,
	DECODE( trx_type,
		'PAYMENT', type_meaning,
		'REFUND',type_meaning,
		'CASH', type_meaning,
		'MISC', :C_RECEIPT_TRANSLATION) 	type_dsp,
	DECODE(clearing_trx_type,
		'JE_LINE',batch_name,
		agent_name)			supplier_customer,
	trx_date					trx_date,
	maturity_date				maturity_date,
	round(bank_account_amount, :C_PRECISION)	bank_account_amount,
	trx_number				trx_number,
	DECODE(clearing_trx_type,
		'JE_LINE',type_meaning,
		payment_method)			payment_method,
                   CE_AVAILABLE_TRANSACTIONS_V.currency_code				currency_code,
	round(amount, :C_PRECISION)			amount,
	round(DECODE( trx_type,
		'PAYMENT', decode(status, 'VOIDED', bank_account_amount, -bank_account_amount),
		'REFUND', decode(status, 'VOIDED', bank_account_amount, -bank_account_amount),
		'CASH', decode(status, 'REVERSED', -bank_account_amount, bank_account_amount),
		'MISC', decode(status, 'REVERSED', -bank_account_amount, bank_account_amount))
                                     , :C_PRECISION) 	account_amount,
	DECODE( clearing_trx_type,
		'CASH', decode(status, 'REVERSED', 20, 10),
		'MISC', decode(status, 'REVERSED', 20, 10),
		'PAYMENT', decode(status, 'VOIDED', 40, 30),
		'PAY', decode(status, 'V', 45, 35),
		'PAY_EFT', decode(status, 'V', 45, 35),
		'REFUND', decode(status,'VOIDED',60,50),
		'JE_LINE',70,
		'XTR_LINE',80,
		'ROI_LINE',80 )			transaction_order,
	to_number(NULL)				batch_id,
	nvl(NULL,rpad(' ',240,' '))			batch_number,
	nvl(NULL,rpad(' ',240,' '))			remittance_number,
	trx_id,
	nvl(fnd_access_control_util.get_org_name(CE_AVAILABLE_TRANSACTIONS_V.org_id),
    fnd_access_control_util.get_org_name(CE_AVAILABLE_TRANSACTIONS_V.legal_entity_id))  ORG_NAME, 
		CE_CEXAVTRX_XMLP_PKG.c_account_amountformula(round(DECODE( trx_type , 'PAYMENT' , decode ( status , 'VOIDED' , bank_account_amount , - bank_account_amount ) , 'REFUND' , decode ( status , 'VOIDED' , bank_account_amount , - bank_account_amount ) , 'CASH' , decode ( status , 'REVERSED' , - bank_account_amount , bank_account_amount ) , 'MISC' , decode ( status , 'REVERSED' , - bank_account_amount , bank_account_amount ) ) , :C_PRECISION )) C_ACCOUNT_AMOUNT, 
	CE_CEXAVTRX_XMLP_PKG.c_amountformula(round ( amount , :C_PRECISION )) C_AMOUNT, 
	CE_CEXAVTRX_XMLP_PKG.c_bank_account_amountformula(
		DECODE ( clearing_trx_type , 'MISC' , 'CASH' , 'PAY' , 'PAYROLL' , 'PAY_EFT' , 'PAYROLL' , 'XTR_LINE' , DECODE ( trx_type , 'PAYMENT' , 'ROI-P' , 'CASH' , 'ROI-R' ) , 'ROI_LINE' , DECODE ( trx_type , 'PAYMENT' , 'ROI-P' , 'CASH' , 'ROI-R' ) , clearing_trx_type ), 
		round (DECODE(trx_type , 'PAYMENT' , decode ( status , 'VOIDED' , bank_account_amount , - bank_account_amount ) , 'REFUND' , decode ( status , 'VOIDED' , bank_account_amount , - bank_account_amount ) , 'CASH' , decode ( status , 'REVERSED' , - bank_account_amount , bank_account_amount ) , 'MISC' , decode ( status , 'REVERSED' , - bank_account_amount , bank_account_amount ) ) , :C_PRECISION ), 
		round ( bank_account_amount , :C_PRECISION )) C_BANK_ACCOUNT_AMOUNT,
	CE_CEXAVTRX_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
	CE_CEXAVTRX_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM  &P_source_view CE_AVAILABLE_TRANSACTIONS_V,
	ce_system_parameters ces
, ce_bank_accounts ba
WHERE	ba.bank_account_id = :P_Bank_Account 
AND  	
	status != DECODE(nvl(ces.show_void_payment_flag, 'N'), 'N',                                                         
                   decode(clearing_trx_type,'PAY', 'V', 'PAY_EFT', 'V', 'VOIDED'),
                   ' x') AND clearing_trx_type !='STATEMENT' AND
                   NVL(journal_category, 'Y') != DECODE(clearing_trx_type,'JE_LINE','Revaluation','X') AND
	trx_date >= ces.cashbook_begin_date
 and
	 CE_AVAILABLE_TRANSACTIONS_V.BANK_ACCOUNT_ID = BA.BANK_ACCOUNT_ID and
              BA.ACCOUNT_OWNER_ORG_ID = ces.LEGAL_ENTITY_ID (+)
 and (( :P_TYPE in ('PAYMENTS', 'RECEIPTS', 'AR_AND_AP','ALL') and 
       CE_AVAILABLE_TRANSACTIONS_V.ORG_ID=nvl(:P_ORG_ID, CE_AVAILABLE_TRANSACTIONS_V.ORG_ID) and
       :P_LEGAL_ENTITY_ID is null  and
       CE_AVAILABLE_TRANSACTIONS_V.clearing_trx_type in ( 'PAYMENT', 'REFUND', 'CASH', 'MISC') 
	) 
      or
        (CE_AVAILABLE_TRANSACTIONS_V.LEGAL_ENTITY_ID = nvl(:P_LEGAL_ENTITY_ID,
			 		CE_AVAILABLE_TRANSACTIONS_V.LEGAL_ENTITY_ID) and 
         :P_TYPE  in ( 'XTR_LINES', 'ALL') and 
	 CE_AVAILABLE_TRANSACTIONS_V.legal_entity_id = ces.legal_entity_id and
	 CE_AVAILABLE_TRANSACTIONS_V.legal_entity_id = nvl(:P_LEGAL_ENTITY_ID,
			 		CE_AVAILABLE_TRANSACTIONS_V.legal_entity_id) and
 	:P_ORG_ID is null and
	CE_AVAILABLE_TRANSACTIONS_V.clearing_trx_type = 'XTR_LINE'
	)  
       or 
         (:P_TYPE IN ( 'PAYROLLS', 'ALL')   and 
          CE_AVAILABLE_TRANSACTIONS_V.legal_entity_id is null  and 
          CE_AVAILABLE_TRANSACTIONS_V.ORG_ID=NVL(:P_BG_ORG_ID, CE_AVAILABLE_TRANSACTIONS_V.ORG_ID) AND
        :P_LEGAL_ENTITY_ID is null  and
        CE_AVAILABLE_TRANSACTIONS_V.clearing_trx_type in ( 'PAY', 'PAY_EFT') 
	)
       or 
         (:P_TYPE in ('ROI_LINES', 'JE_LINES','ALL') and 
          CE_AVAILABLE_TRANSACTIONS_V.org_id is null and 
          CE_AVAILABLE_TRANSACTIONS_V.legal_entity_id is null and 
         CE_AVAILABLE_TRANSACTIONS_V.clearing_trx_type in ( 'ROI_LINE', 'JE_LINE') 
	)
      )
UNION ALL
SELECT   distinct avtr.bank_account_name                                             	bank_account_name,
                 avtr.bank_account_num                                                      	bank_account_num,
                 avtr.bank_name                                                                   	bank_name,
                 avtr.bank_branch_name                                                      	bank_branch_name,
                 avtr.clearing_trx_type                                                         	type,
                 avtr.clearing_trx_type                                                         	break_type,
                 avtr.type_meaning                                                               	type_dsp,
                 DECODE( clearing_trx_type, 
                                'STATEMENT',statement_number,
                                agent_name)                                                         	supplier_customer,
                 avtr.trx_date                                                                        	trx_date,
                 avtr.maturity_date                                                               	maturity_date,
                 round(avtr.bank_account_amount, :C_PRECISION)           	bank_account_amount,
                 avtr.trx_number                                                                   	trx_number,
                 DECODE(clearing_trx_type, 
                                'STATEMENT',type_meaning,
                                 payment_method)                                               	payment_method,
                 avtr.currency_code                                                               	currency_code,
                 round(avtr.amount, :C_PRECISION)                                    	amount,
                 DECODE(trx_type,
                                'DEBIT', round(-avtr.bank_account_amount, :C_PRECISION),
                                round(avtr.bank_account_amount, :C_PRECISION))        account_amount,
                 DECODE(clearing_trx_type, 
                                    'STATEMENT',70)                                             	transaction_order,
                  to_number(NULL)                                                                	batch_id,
                  NULL                                                                                    	batch_number,
                  NULL                                                                                    	remittance_number,
	avtr.trx_id,
	null						  ORG_NAME, 
	CE_CEXAVTRX_XMLP_PKG.c_account_amountformula(DECODE(trx_type,  'DEBIT', round(-avtr.bank_account_amount, :C_PRECISION),	round(avtr.bank_account_amount, :C_PRECISION))) C_ACCOUNT_AMOUNT, 
	CE_CEXAVTRX_XMLP_PKG.c_amountformula(round(avtr.amount, :C_PRECISION)) C_AMOUNT, 
	CE_CEXAVTRX_XMLP_PKG.c_bank_account_amountformula(
		avtr.clearing_trx_type, 
		DECODE(trx_type,  'DEBIT', round(-avtr.bank_account_amount, :C_PRECISION), round(avtr.bank_account_amount, :C_PRECISION)), 
		round(avtr.bank_account_amount, :C_PRECISION)) C_BANK_ACCOUNT_AMOUNT,
	CE_CEXAVTRX_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
	CE_CEXAVTRX_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM       CE_260_TRANSACTIONS_V avtr,  CE_STATEMENT_HEADERS ch
WHERE     avtr.bank_account_id=:P_Bank_Account 
AND
                  :P_TYPE  in ('STATEMENTS'  , 'ALL')		AND                 
                  avtr.reference_id=ch.statement_header_id
UNION ALL
SELECT    avtr.bank_account_name                                                    	bank_account_name,
                 avtr.bank_account_num                                                      	bank_account_num,
                 avtr.bank_name                                                                   	bank_name,
                 avtr.bank_branch_name                                                      	bank_branch_name,
                 avtr.clearing_trx_type                                                         	type,
                 avtr.clearing_trx_type                                                         	break_type,
                 avtr.type_meaning                                                               	type_dsp,
                 DECODE( clearing_trx_type, 
                                'CASHFLOW',counterparty,
                                agent_name)                                                         	supplier_customer,
                 nvl(avtr.value_date,avtr.trx_date)                                                                        	trx_date,
                 avtr.maturity_date                                                               	maturity_date,
                 decode(trx_type,'PAYMENT',round(-avtr.bank_account_amount, :C_PRECISION),
	 round(avtr.bank_account_amount,:C_PRECISION))         	bank_account_amount,
                 avtr.trxn_subtype                                                                   	trx_number,
                 DECODE(clearing_trx_type, 
                                'CASHFLOW',l1.meaning,
                                 payment_method)                                               	payment_method,
                 avtr.currency_code                                                               	currency_code,
                 round(avtr.amount, :C_PRECISION)                                    	amount,
                 DECODE(trx_type,
                                'PAYMENT', round(-avtr.bank_account_amount, 2),
                                round(avtr.bank_account_amount, 2))        account_amount,
                 DECODE(clearing_trx_type, 
                                    'CASHFLOW',90)                                             	transaction_order,
                  to_number(NULL)                                                                	batch_id,
                  NULL                                                                                    	batch_number,
                  NULL                                                                                    	remittance_number,
	avtr.trx_id,
	fnd_access_control_util.get_org_name(avtr.legal_entity_id)  ORG_NAME, 
	CE_CEXAVTRX_XMLP_PKG.c_account_amountformula(DECODE(trx_type, 'PAYMENT', round(-avtr.bank_account_amount, 2), round(avtr.bank_account_amount, 2))) C_ACCOUNT_AMOUNT, 
	CE_CEXAVTRX_XMLP_PKG.c_amountformula(round(avtr.amount, :C_PRECISION)) C_AMOUNT, 
	CE_CEXAVTRX_XMLP_PKG.c_bank_account_amountformula(
		avtr.clearing_trx_type, 
		DECODE(trx_type, 'PAYMENT', round(-avtr.bank_account_amount, 2), round(avtr.bank_account_amount, 2)), 
		decode(trx_type,'PAYMENT',round(-avtr.bank_account_amount, :C_PRECISION), round(avtr.bank_account_amount,:C_PRECISION))) C_BANK_ACCOUNT_AMOUNT,
	CE_CEXAVTRX_XMLP_PKG.C_BATCH_AMOUNT_p C_BATCH_AMOUNT,
	CE_CEXAVTRX_XMLP_PKG.C_BATCH_BANK_ACCOUNT_AMOUNT_p C_BATCH_BANK_ACCOUNT_AMOUNT
FROM       CE_260_CF_TRANSACTIONS_V avtr,  CE_SYSTEM_PARAMETERS ch,
	CE_LOOKUPS l1
WHERE     avtr.bank_account_id=:P_Bank_Account	
AND
                  :P_TYPE in ( 'CASHFLOWS'  ,'ALL')                        			AND                 
                  avtr.legal_entity_id = ch.legal_entity_id
 AND
	 avtr.legal_entity_id = nvl(:P_LEGAL_ENTITY_ID, avtr.legal_entity_id ) AND
	 l1.lookup_type = 'CE_CASHFLOW_DIRECTION' AND
	l1.lookup_code = avtr.trx_type
&C_ORDER_BY
Parameter Name SQL text Validation
CE_OU_DUMMY
 
CE_BG_DUMMY
 
CE_LE_DUMMY
 
Order By
 
LOV Oracle
Bank Account Number
 
LOV Oracle
AP/AR Operating Unit
 
LOV Oracle
Payroll Business Group
 
LOV Oracle
Treasury Legal Entity
 
LOV Oracle
Transaction Type
 
LOV Oracle
Ask a question