Untitled
unknown
plain_text
6 months ago
1.7 kB
3
Indexable
Never
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;