bkkjCWpt

1create or replace TRIGGER "ICT_SD_RECLAMACOES_ADD_ACTION" 
2BEFORE UPDATE
3ON ict_sd_reclamacoes 
4FOR EACH ROW
5DECLARE
6    vexecutions             NUMBER(10) := 0;
7    vsessionid              NUMBER(10) := 0;
8    vactionid               NUMBER(10) := NULL;
9    vstatusname             VARCHAR(100) := NULL;
10    voperationname          VARCHAR(100) := NULL;
11    vstatusodmok            VARCHAR(100) := NULL;
12    vactivateopscname       VARCHAR(100) := NULL;
13    vodmhistoricoregras     VARCHAR(1000) := NULL;
14    vodmhistoricooperacoes  VARCHAR(1000) := NULL;
15    vnameinfoacesso         VARCHAR(100) := NULL;
16    vnameodm                VARCHAR(100) := NULL;
17    vnamerecurrence         VARCHAR(100) := NULL;
18    vnamepbreturn           VARCHAR(100) := NULL;
19    voperacao               VARCHAR(100) := :OLD.operacao;
20
21BEGIN
22    -- SELECT de step Name ODM
23    SELECT VALUE INTO vnameodm FROM ict_sd_reclamacoes_params WHERE PARAMETER = 'OPERATION_ODM_NAME';
24    -- SELECT de status Name ODM OK
25    SELECT VALUE INTO vstatusodmok FROM ict_sd_reclamacoes_params WHERE PARAMETER = 'STATUS_ODM_OK';
26    -- SELECT de Ativação OPSC Name
27    SELECT VALUE INTO vactivateopscname FROM ict_sd_reclamacoes_params WHERE PARAMETER = 'OPERATION_MSE_ACTIVATE_NAME';
28    -- SELECT MAX execution_order And session_id IF Exists Then ++
29    SELECT MAX(execution_order) INTO vexecutions FROM ict_sd_reclamacoes_actions WHERE num_tt = :OLD.num_tt;
30    IF vexecutions IS NULL THEN
31        vexecutions := 0;
32    END IF;
33    SELECT MAX(session_id) INTO vsessionid FROM ict_sd_reclamacoes_actions WHERE num_tt = :OLD.num_tt;
34    IF vsessionid IS NULL THEN
35        vsessionid := 0;
36    END IF;
37
38    -- CHECK If is needed a New Row of Operation
39    IF (((:OLD.operacao <> :NEW.operacao) AND :NEW.operacao IS NOT NULL) OR (:NEW.operacao IS NOT NULL AND :OLD.operacao IS NULL)) THEN
40        -- SELECT de step Name Infoacesso
41        SELECT VALUE INTO vnameinfoacesso FROM ict_sd_reclamacoes_params WHERE PARAMETER = 'OPERATION_INFOACESSO_NAME';
42        -- SELECT de step Name Recurrence
43        SELECT VALUE INTO vnamerecurrence FROM ict_sd_reclamacoes_params WHERE PARAMETER = 'OPERATION_RECURRENCE_NAME';
44        -- SELECT de step Name PB Return
45        SELECT VALUE INTO vnamepbreturn FROM ict_sd_reclamacoes_params WHERE PARAMETER = 'OPERATION_PBRETURN_NAME';
46
47        -- CHECK If is Operation that Need New SESSION
48        IF :NEW.operacao IN (vnameinfoacesso, vnamerecurrence, vnamepbreturn) THEN
49            vsessionid := vsessionid + 1;
50        END IF;
51
52        -- Set New values
53        voperacao := :NEW.operacao;
54        vexecutions := vexecutions + 1;
55
56        -- Inser New Operation on Actions Table
57        INSERT INTO ict_sd_reclamacoes_actions (action, session_id, execution_order, start_time, retries, num_tt) VALUES (voperacao, vsessionid, vexecutions, current_timestamp, 0, :OLD.num_tt);
58    END IF;
59    IF voperacao IS NOT NULL THEN
60        -- Get Unique Row Id
61        SELECT
62            id INTO vactionid
63        FROM
64            ict_sd_reclamacoes_actions 
65        WHERE
66            num_tt = :OLD.num_tt
67            AND action = voperacao
68            AND session_id = vsessionid
69            AND execution_order = vexecutions;
70
71        -- Update das Last Changes
72        UPDATE ict_sd_reclamacoes_actions
73        SET
74            operacao_attributes = :NEW.operacao_attributes,
75            nota_trabalho = :NEW.nota_trabalho,
76            nota_operacao = :NEW.nota_operacao,
77            motivo_cancelamento = :NEW.motivo_cancelamento,
78            target_queue = :NEW.target_queue,
79            pb_queue = :NEW.pb_queue,
80            infoacesso_saida = :NEW.infoacesso_saida,
81            odm_saida = :NEW.odm_saida,
82            odm_entrada = :NEW.odm_entrada,
83            troubleshoot_saida = :NEW.troubleshoot_saida,
84            end_time = current_timestamp,
85            retries = :NEW.retry
86        WHERE
87            id = vactionid;
88
89        -- Insert Rules on Table If is ODM Operation
90        IF(voperacao = vnameodm AND :NEW.operacao IS NOT NULL AND :NEW.status = vstatusodmok) THEN            
91            -- Get Last ExecutionRules
92            SELECT
93                substr(:NEW.odm_historico_regras, instr(:NEW.odm_historico_regras, '/', -1) + 1 ),
94                substr(:NEW.odm_historico_operacoes, instr(:NEW.odm_historico_operacoes, '/', -1) + 1 )
95            INTO
96                vodmhistoricoregras,
97                vodmhistoricooperacoes
98            FROM dual;
99
100            --Transform in Multiples Returns for each Rule
101            FOR v_odm_rule in (
102                SELECT
103                    regexp_substr(vodmhistoricoregras,'[^;]+', 1, LEVEL) as name,
104                    regexp_substr(vodmhistoricooperacoes,'[^;]+', 1, LEVEL) as operation
105                FROM dual
106                CONNECT BY regexp_substr(vodmhistoricoregras, '[^;]+', 1, LEVEL) IS NOT NULL
107            )
108            LOOP
109               INSERT INTO ict_sd_reclamacoes_rules (action_id, regra, operacao) VALUES (vactionid, v_odm_rule.name, v_odm_rule.operation);
110            END LOOP;
111        END IF;
112    END IF;
113END;