This CE Miscellaneous Transactions query fetches cash receipt and transaction details, to help customers review line wise statement details for transactions.
#sqlquery
SELECT
--SQL4Fusion (An Orbit Analytics Project)
--This query retrieves Customer Bank Information and Miscellaneous transaction details
-- Bank Info
CBA.BANK_ACCOUNT_NAME ,
CBA.BANK_ACCOUNT_NUM ACCOUNT_NUMBER,
CBA.ACCOUNT_CLASSIFICATION,
CBA.SHORT_ACCOUNT_NAME ACCOUNT_SHORT_NAME,
CBA.ACCOUNT_HOLDER_NAME ACCOUNT_HOLDER,
-- Statement info
CSH.STATEMENT_NUMBER,
CSH.STATEMENT_DATE,
CSH.STATEMENT_TYPE,
CSL.LINE_NUMBER,
CSL.TRX_TYPE TRANSACTION_TYPE,
CSL.CHECK_NUMBER,
-- GL Infor
GCC.SEGMENT1 AS COMPANY,
GCC.SEGMENT2 AS DEPARTMENT,
GCC.SEGMENT3 AS ACCOUNT,
GCC.SEGMENT4 AS PRODUCT,
GCC.SEGMENT5 AS FUTURE,
-- Currency code
CSH.CURRENCY_CODE CURRENCY,
-- Amount info
CSL.AMOUNT STATEMENT_LINE_AMOUNT,
CSL.TRX_AMOUNT TRANSACTION_AMOUNT
FROM
CE_STATEMENT_LINES CSL,
CE_STATEMENT_HEADERS CSH,
CE_BANK_ACCOUNTS CBA,
CE_EXTERNAL_TRANSACTIONS CET,
GL_CODE_COMBINATIONS GCC,
HZ_PARTIES HP
WHERE 1 = 1
AND CSL.STATEMENT_HEADER_ID = CSH.STATEMENT_HEADER_ID
AND CSL.TRX_TYPE = 'MSC'
AND CBA.BANK_ACCOUNT_ID = CSH.BANK_ACCOUNT_ID
AND CET.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID
AND GCC.CODE_COMBINATION_ID = CET.ASSET_CCID
AND HP.PARTY_ID = CBA.ACCOUNT_OWNER_PARTY_ID