LhuPWsty

1-- drop contraint preventing insert of WEB channels.
2ALTER TABLE sy_e2e.D_VCCFE_IVGROUPS_SBL DROP CONSTRAINT D_VCCFE_IVGROUPS_SBL_C02;
3
4CREATE OR REPLACE TRIGGER D_VCCFE_IVGROUPS_SBL_INST
5BEFORE INSERT OR UPDATE
6ON D_VCCFE_IVGROUPS_SBL
7REFERENCING NEW AS NEW OLD AS OLD
8FOR EACH ROW
9DECLARE
10    vista_id                    INTEGER;
11    cnt                         INTEGER;
12    SERVICE  CONSTANT VARCHAR2(32) := 'VCC_FE_SBL';     
13   BEGIN
14        SELECT ID INTO vista_id FROM G_VISTAS WHERE NAME = 'Transactions';
15        CASE
16            WHEN INSERTING THEN
17                :NEW.modified := SYSDATE;
18                    -- let's have a look if we have 2nd level instance already created 
19                    SELECT COUNT(*) INTO cnt FROM G_INSTANCE WHERE servicename = SERVICE AND transactionname = :NEW.channel_ivname AND instance_level = 2 AND vistaid = vista_id;
20                    
21                    IF cnt = 0 THEN  -- we have no 2nd level instance created, so let's create one 
22                        INSERT INTO G_INSTANCE (instance_id,instance_level,servicename,transactionname,typename,subtypename,vistaid,iv_tag)
23                        VALUES (g_instance_seq.NEXTVAL,2,SERVICE,:NEW.channel_ivname,NULL,NULL,vista_id,g_instance_seq.CURRVAL);
24                    END IF;
25                                                                                
26                    -- let's have a look if we have 3rd level instance already created 
27                    SELECT COUNT(*) INTO cnt FROM G_INSTANCE WHERE servicename = SERVICE AND transactionname = :NEW.channel_ivname AND typename = :NEW.group_ivname AND instance_level = 3 AND vistaid = vista_id;
28                    
29                    IF cnt = 0 THEN  -- we have no 3rd level instance created, so let's create one 
30                        INSERT INTO G_INSTANCE (instance_id,instance_level,servicename,transactionname,typename,subtypename,vistaid,iv_tag)
31                        VALUES (g_instance_seq.NEXTVAL,3,SERVICE,:NEW.channel_ivname,:NEW.group_ivname,NULL,vista_id,g_instance_seq.CURRVAL);
32                    END IF;                                          
33            WHEN UPDATING THEN
34                :NEW.modified := SYSDATE;
35                IF :OLD.channel_ivname <> :NEW.channel_ivname OR :OLD.group_ivname <> :NEW.group_ivname THEN  -- udpate of channel_ivname may invoke new 2nd level instance creation 
36                     -- let's have a look if we have 2nd level instance already created for potential channel_ivnames (JSON, IVR, USSD)
37                    SELECT COUNT(*) INTO cnt FROM G_INSTANCE WHERE servicename = SERVICE AND transactionname = :NEW.channel_ivname AND instance_level = 2 AND vistaid = vista_id;
38                    IF cnt = 0 THEN  -- we have no 2nd level instance created, so let's create one 
39                        INSERT INTO G_INSTANCE (instance_id,instance_level,servicename,transactionname,typename,subtypename,vistaid,iv_tag)
40                        VALUES (g_instance_seq.NEXTVAL,2,SERVICE,:NEW.channel_ivname,NULL,NULL,vista_id,g_instance_seq.CURRVAL);
41                    END IF;
42                    
43                    -- let's have a look if we have already instance with the same typename created
44                    SELECT COUNT(*) INTO cnt FROM G_INSTANCE WHERE servicename = SERVICE AND transactionname = :NEW.channel_ivname AND typename = :NEW.group_ivname AND instance_level = 3 AND vistaid = vista_id;
45                    IF cnt = 0 THEN     -- no instance with the same name exists -> ok we may create instance with a new typename                 
46                        INSERT INTO G_INSTANCE (instance_id,instance_level,servicename,transactionname,typename,subtypename,vistaid,iv_tag)
47                        VALUES (g_instance_seq.NEXTVAL,3,SERVICE,:NEW.channel_ivname,:NEW.group_ivname,NULL,vista_id,g_instance_seq.CURRVAL);                        
48                    END IF;                                                                                            
49                END IF;                
50
51        END CASE;
52
53    EXCEPTION
54        WHEN NO_DATA_FOUND THEN
55            G_P_Log.LOG('vista not found in G_VISTAS for service ' ||SERVICE|| ', cannot insert new instances',G_P_Log.E,'create instance',SERVICE);
56            RAISE;
57        WHEN OTHERS THEN
58            G_P_Log.LOG('failed to modify instances:'||SQLERRM,G_P_Log.E,'create/drop instance',SERVICE);
59            RAISE;
60END;
61/