Reports2017-11-18T12:27:27+00:00

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
By continuing to use the site, you agree to the use of cookies. Accept