Untitled

mail@pastecode.io avatar
unknown
sql
a year ago
2.0 kB
2
Indexable
Never
"WITH TRANSACCIONES_CUENTAS_AHORROS AS (
SELECT /*+parallel */
		FSE.ACCOUNT_NUMBER,
	 	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'
	 		 ELSE 
	 		 FFT.L_FT_CHANNELS
	 	END CHANNEL,
	    TO_DATE(FSE.PROCESSING_DATE,'YYYY-MM-DD') AS VALUE_DATE,
	    FSE.TRANS_REFERENCE,
	    FFT.REF_NO,
		Abs(NVL(FSE.AMT_LCY,FSE.AMOUNT_LCY)) AS BALANCE_TRANSACCIONES,
		FT.STMT_NARR,
		FT.TRANSACTION_CODE 
		FROM FBNK_STMT_ENTRY FSE 
		LEFT JOIN DW_STA.FBNK_FUNDS_TRANSFER$HIS FFT ON REPLACE(FFT.REF_NO,';1','') = REPLACE(FSE.TRANS_REFERENCE,'\\BNK','')												
		INNER JOIN DW_STA.FBNK_TRANSACTION FT ON FT.TRANSACTION_CODE = FSE.TRANSACTION_CODE
		WHERE 
	     	(
		    (FFT.DEBIT_CUSTOMER <> FFT.CREDIT_CUSTOMER AND 
		    FT.TRANSACTION_CODE NOT IN (104, 40, 48)) OR 
		    FSE.ACCOUNT_NUMBER = FSE.ACCOUNT_NUMBER 
	    	) AND 
		FSE.PROCESSING_DATE >='" + (DT_STR,4,1252)DATEPART( "yyyy" , @[User::FECHA_INICIO_TRANSACCIONES]  )  +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , @[User::FECHA_INICIO_TRANSACCIONES]  ), 2)  +RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[User::FECHA_INICIO_TRANSACCIONES]  ), 2) +"'  AND
        FSE.PROCESSING_DATE <='" + (DT_STR,4,1252)DATEPART( "yyyy" , @[User::FECHA_FIN_TRANSACCIONES]   )  +RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" ,   @[User::FECHA_FIN_TRANSACCIONES]  ), 2)  +RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , @[User::FECHA_FIN_TRANSACCIONES]   ), 2) +"' AND
        FSE.ACCOUNT_NUMBER NOT IN('DOP1761700010017') AND
		Abs(NVL(FSE.AMT_LCY,FSE.AMOUNT_LCY)) >="+(DT_WSTR,15)@[User::INCREMENTO_TARJETA]+" AND
	    FT.TRANSACTION_CODE IN ("+ @[User::TIPO_TRANSACCIONES_CANALES] +")
)
SELECT * 
FROM TRANSACCIONES_CUENTAS_AHORROS
WHERE CHANNEL IN ("+ @[User::PRODUCTOS] +")
ORDER BY ACCOUNT_NUMBER"