vw_aux_description

sql

SELECT tr.created_at AS transaction_date,
            tr.transaction_id AS transaction,
                CASE
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL THEN concat(m1.id, '-D')
                    ELSE tr.id::text
                END AS transaction_id,
            tr.transaction_type_id,
            tt.description,
                CASE
                    WHEN tt.description = 'WORKER ACCOUNT BALANCER'::text AND lower(m1.details) ~~ '%a tu cvu%'::text THEN 'EXTERNAL TRANSFER'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%a tu cvu%'::text THEN 'EXTERNAL TRANSFER'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%ajuste%'::text THEN 'WORKER ACCOUNT BALANCER'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%transferencia manual%'::text THEN 'TRANSFER TAP TO TAP'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%te transfi%'::text THEN 'TRANSFER TAP TO TAP'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%fue transfe%'::text THEN 'TRANSFER TAP TO TAP'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%acreditado desde la tarjeta%'::text AND m1.details ~~ '%bito%'::text THEN 'CASH IN DEBIT CARD'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%acreditado desde la tarjeta%'::text AND m1.details !~~ '%bito%'::text THEN 'CASH IN CREDIT CARD'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%transferiste a%'::text THEN 'TRANSFERS TO CBU'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%cash in a la wc%'::text THEN 'WC CASH IN'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%la cuenta wc%'::text AND lower(m1.details) ~~ '%fondeo%'::text THEN 'WC_FOUND'::text
                    WHEN m1.id IS NOT NULL AND tr.transaction_id IS NULL AND lower(m1.details) ~~ '%worker concilia%'::text THEN 'WORKER ACCOUNT BALANCER'::text
                    WHEN lower(m1.details) ~~ '%bonifica%'::text OR lower(m1.details) ~~ '%reintegro%'::text OR lower(m1.details) ~~ '%devoluc%'::text OR lower(m1.details) ~~ '%reembols%'::text THEN 'BONIF/REINTEGROS'::text
                    ELSE tt.description
                END AS description_ok,
                CASE
                    WHEN ( EXISTS ( SELECT tr_1.transaction_id
                       FROM ledger.transaction tr_1
                      WHERE tr.transaction_id=tr_1.transaction_id AND tr_1.transaction_type_id = 22)) AND (tr.transaction_type_id = 2 OR tr.transaction_type_id = 14) THEN 'CASH IN PAGO SERVICIOS'::text
                    WHEN (EXISTS( SELECT tr_1.transaction_id
                       FROM ledger.transaction tr_1
                      WHERE tr.transaction_id=tr_1.transaction_id AND tr_1.transaction_type_id = 21)) AND (tr.transaction_type_id = 2 OR tr.transaction_type_id = 14) THEN 'CASH IN RECARGA SERVICIOS (sin MIDE)'::text
                    WHEN (EXISTS( SELECT tr_1.transaction_id
                       FROM ledger.transaction tr_1
                      WHERE  tr.transaction_id=tr_1.transaction_id AND tr_1.transaction_type_id = 29)) AND (tr.transaction_type_id = 2 OR tr.transaction_type_id = 14) THEN 'CASH IN RECARGA MIDE'::text
                    WHEN (EXISTS ( SELECT tr_1.transaction_id
                       FROM ledger.transaction tr_1
                      WHERE tr.transaction_id=tr_1.transaction_id AND tr_1.transaction_type_id = 23)) AND (tr.transaction_type_id = 2 OR tr.transaction_type_id = 14) THEN 'CASH IN TRANSFER TO CBU'::text
                    WHEN (EXISTS( SELECT tr_1.transaction_id
                       FROM ledger.transaction tr_1
                      WHERE tr.transaction_id=tr_1.transaction_id AND tr_1.transaction_type_id = 5)) AND (tr.transaction_type_id = 2 OR tr.transaction_type_id = 14) THEN 'CASH IN TAP TO TAP'::text
                    WHEN (EXISTS ( SELECT tr_1.transaction_id
                       FROM ledger.transaction tr_1
                      WHERE tr.transaction_id=tr_1.transaction_id AND tr_1.transaction_type_id = 24)) AND (tr.transaction_type_id = 2 OR tr.transaction_type_id = 14) THEN 'CASH IN RECARGA SUBE'::text
                    WHEN tr.transaction_type_id = 2 OR tr.transaction_type_id = 14 THEN 'CASH IN NORMAL'::text
                    ELSE NULL::text
                END AS description_auxiliar
           FROM ledger.transaction tr
             LEFT JOIN ledger.transaction_type tt ON tr.transaction_type_id = tt.transaction_type_id
             FULL JOIN ledger.movement m1 ON tr.movement_id = m1.id
			;