CE Transaction Codes (Oracle Fusion SQL)

Enterprise Resource Planning (ERP) Oracle Cash Management (CE-Fusion-SQL's)
1

This CE Transaction Codes query displays transaction codes with associated bank details, helping users to monitor and manage financial transaction types effectively.

#sqlquery

SELECT  
--SQL4Fusion (An Orbit Analytics Project) 
--This query displays transaction codes with related bank info
  -- Bank Information
  CBBV.BANK_BRANCH_NAME,
  CBBV.BANK_NAME,
  CBBV.BANK_INSTITUTION_TYPE,
  CBAV.BANK_ACCOUNT_NAME,
  CBAV.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUMBER,
  CBAV.CURRENCY_CODE BANK_ACCOUNT_CURRENCY_CODE,
  CL.MEANING TRANSACTION_CODE_DESCRIPTION,
  --Transaction Information
  CTC.TRX_TYPE TRANSACTION_TYPE,
  CTC.TRX_CODE TRANSACTION_CODE,
  CTC.DESCRIPTION TRANSACTION_DESCRIPTION
FROM
  CE_TRANSACTION_CODES 	CTC,
  CE_BANK_ACCOUNTS_CE_V CBAV,
  CE_BANK_BRANCHES_V 	CBBV,
  CE_LOOKUPS 			CL
WHERE
  1 = 1
AND CBBV.BRANCH_PARTY_ID 	= CBAV.BANK_BRANCH_ID
AND CL.LOOKUP_CODE 			= CTC.TRX_TYPE  
AND EXISTS (SELECT 1 FROM CE_STATEMENT_HEADERS CSH,  
						  CE_STATEMENT_LINES CSL 
					WHERE 1=1
					AND CSH.BANK_ACCOUNT_ID		= CBAV.BANK_ACCOUNT_ID
					AND CTC.TRANSACTION_CODE_ID = CSL.TRX_CODE_ID
					AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID)
ORDER BY CBAV.BANK_ACCOUNT_NUM 
	  ,  CTC.TRX_CODE