Untitled

WITH TRANSACCIONES AS (
SELECT DISTINCT /*+parallel */
FSE.ACCOUNT_NUMBER,
FSE.TRANS_REFERENCE,
CASE WHEN FFT.REF_NO IS NOT NULL AND FFT.L_FT_CHANNELS IS NULL THEN n'ARC'
	 		 WHEN FSE.TRANS_REFERENCE LIKE '%TT%' AND FFT.L_FT_CHANNELS IS NULL THEN n'CAJ'
	 		 WHEN FSE.TRANS_REFERENCE LIKE '%FT%' AND  FFT.REF_NO IS NULL THEN n'ARC'
	 		 WHEN FFT.L_FT_CHANNELS IS NULL THEN n'ARC'
	 		 ELSE 
	 		 FFT.L_FT_CHANNELS 
END CHANNEL,
CASE WHEN FSE.TRANSACTION_CODE NOT IN ('24','850','875','54') 
	 THEN TO_NUMBER(NVL(FSE.AMT_LCY,FSE.AMOUNT_LCY)) 
ELSE 0 END AS MONTO,
CASE WHEN FSE.TRANSACTION_CODE IN (97,96,401,46,52,4,35,163,534,305,128,283,282,121,545,127,284,600,957,213,277,217,224,39) AND TO_NUMBER(NVL(FSE.AMT_LCY,FSE.AMOUNT_LCY))>0
	 THEN  TO_NUMBER(NVL(FSE.AMT_LCY,FSE.AMOUNT_LCY))
ELSE 0 END AS MONTO_DEPOSITOS,
CASE WHEN FSE.TRANSACTION_CODE IN ('24','850','875','54') 
	 THEN TO_NUMBER(NVL(FSE.AMT_LCY,FSE.AMOUNT_LCY))
ELSE 0 END AS MONTO_NO_PARTICIPAN
FROM FBNK_STMT_ENTRY FSE  
INNER JOIN FBNK_STMT_ENTRY FSE2 ON FSE2.ACCOUNT_NUMBER = FSE.ACCOUNT_NUMBER AND FSE2.PRODUCT_CATEGORY IN ("+ @[User::PRODUCTOS] +")  
AND FSE2.PROCESSING_DATE= FSE.PROCESSING_DATE 
LEFT JOIN FBNK_FUNDS_TRANSFER$HIS FFT ON REPLACE(FFT.REF_NO,';1','') = REPLACE(FSE.TRANS_REFERENCE,'\\BNK','')
INNER JOIN FBNK_TRANSACTION ft ON ft.TRANSACTION_CODE = FSE.TRANSACTION_CODE
WHERE 
		FSE.ACCOUNT_NUMBER NOT IN('DOP1761700010017') AND
        FSE.PROCESSING_DATE >='2023-03-24' AND
FSE.TRANS_REFERENCE IS NOT NULL
)
SELECT 
ACCOUNT_NUMBER,
SUM(MONTO) BALANCE_FINAL,
SUM(MONTO_DEPOSITOS) MONTO_DEPOSITOS,
SUM(MONTO_NO_PARTICIPAN) MONTO_NO_PARTICIPAN
FROM TRANSACCIONES
GROUP BY ACCOUNT_NUMBER
ORDER BY ACCOUNT_NUMBER;