alter table TSLRPRM_TARIF_AN add SUMA_SM NUMBER;

CREATE OR REPLACE VIEW VSLRPRM_TARIF_AN
(AN, SUMA_P, SUMA_S, SUMA_M, SUMA_H, 
 SUMA_1, SUMA_SM, LIMIT_BAZA_SOCFOND, AT1, AT2, AT3, 
 AP1, AP2, AP3, CLCAP1, CLCAP2, 
 CLCAP3)
AS 
SELECT AN,
  NVL(SUMA_P,0),NVL(SUMA_S,0),NVL(SUMA_M,0),NVL(SUMA_H,0),
  NVL(SUMA_1,0),NVL(SUMA_SM,0), NVL(LIMIT_BAZA_SOCFOND,0),
  NVL(AT1,0),NVL(AT2,0),NVL(AT3,0),NVL(AP1,0),NVL(AP2,0),NVL(AP3,0),
  NVL(AP1,0)*100,NVL(AP2,0)*100,NVL(AP3,0)*100
FROM TSLRPRM_TARIF_AN;

-----------------
DROP VIEW VMDB_SALPONTAJ;

CREATE OR REPLACE VIEW VMDB_SALPONTAJ
(NRDOC, DATASTART, DATAEND, INTTIP, SECTIA, 
 REGIM, boolflag1, boolflag2, DATAMANUAL, CLCSECTIAT, CLCREGIMT)
AS 
SELECT A.NRDOC_ID NRDOC,A.DATASTART,A.DATAEND,A.INTTIP,A.CONTA.DEP SECTIA,A.CONTA.SC1 REGIM,a.boolflag1,a.boolflag2,
(SELECT DATAMANUAL FROM TMDB_DOCS WHERE COD=A.NRDOC_ID)DATAMANUAL,
(SELECT DENUMIREA__1 FROM VMS_UNIVERS WHERE COD=A.CONTA.DEP)CLCSECTIAT,
(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='R' AND COD=18 AND COD1=A.CONTA.SC1)CLCREGIMT
FROM TMDB_VZFORMS3 A;

CREATE OR REPLACE TRIGGER TR_VMDB_SALPONTAJ  
INSTEAD OF INSERT OR UPDATE ON VMDB_SALPONTAJ
FOR EACH ROW
BEGIN
 IF INSERTING THEN
  INSERT INTO TMDB_VZFORMS3(NRDOC_ID,DATASTART,DATAEND,INTTIP,CONTA,boolflag1,boolflag2)
  VALUES(:NEW.NRDOC,:NEW.DATASTART,:NEW.DATAEND,:NEW.INTTIP,
    UN$CM(' ','','','',:NEW.SECTIA,:NEW.REGIM,:NEW.NRDOC,'','','',UN$SUMA('','','','')),:new.boolflag1,:new.boolflag2);
 ELSIF UPDATING THEN
  UPDATE TMDB_VZFORMS3 SET
    DATASTART=:NEW.DATASTART,DATAEND=:NEW.DATAEND,INTTIP=:NEW.INTTIP,
    CONTA=UN$CM(' ','','','',:NEW.SECTIA,:NEW.REGIM,:NEW.NRDOC,'','','',UN$SUMA('','','','')),
    boolflag1=:new.boolflag1,
    boolflag2=:new.boolflag2
    WHERE NRDOC_ID=:OLD.NRDOC;
 END IF;
END;
/

alter table tms_org add TARA NUMBER (10);

CREATE OR REPLACE FORCE VIEW VMS_ORG
(COD, GR1, GR11, ORAS, TARA, GR12, 
 ADRESS, BANK, ACCOUNT, ACCOUNT1, ACCOUNT2, 
 ACCOUNT3, ACCOUNTV1, ACCOUNTV2, ACCOUNTV3, ACCOUNTV4, 
 ACCOUNTV5, CVALACC1, CVALACC2, CVALACC3, CVALACC4, 
 CVALACC5, MFO, CONTACT, DATE1, TELEFON, 
 TELPRIM, ADM_CENTRU, DIRECTOR, PERSDECONTR, TELEFONPERSDECONTR, 
 FAX, COD_RAION_S_25, CATEGORIA_S_26, TIPCLIENT_S_27, TIPCLIENTSOL_S_28, 
 BANK_S_30, OKPO, OKHN, SPEC1, SPEC2, 
 DISTANTAKM, CONTCCOST, CONTSP1, CONTSP2, CONTPENTRUUZURA, 
 CODFISCAL, SPECF1, SPECF2, CONTSP3, DT, 
 CT, CONT1PENTRUUZURA, MP_TIPFURNIZ_S_51, MP_AGRONOM_UNIV, MP_PERSINCR_UNIV, 
 MINJUST_REGNUM, MINJUST_REGDATA, OI_TIPSECT_S_79, CHELTMBP_CONT, CHELTMBP_CONT1, 
 CHELTMBP_SC, CHELTMBP_DEP, CHELTMBP_SC1, ADDR_FACT, ACTIONER_SA_1, 
 TIP_IMP_VENIT_SRC, CLCORAST, CLCTARAT, CLCCATEGORIA_S_26T, CLCTIPCLIENT_S_27T, CLCTIPCLIENTSOL_S_28T, 
 CLCBANK_S_30T, CLCCOD_RAION_S_25, CLCCOD_RAION_S_25T, CLCMP_TIPFURNIZ_S_51T, CLCMP_AGRONOM_UNIVT, 
 CLCMP_PERSINCR_UNIVT, CLCOI_TIPSECT_S_79T, CLC_COUNTRY_S_11, CLC_COUNTRY_S_11T, CLC_ACTIONER_SA_1T, 
 CLCTIP_IMP_VENIT_SRC, CLCTIP_IMP_VENIT_SRCT)
AS 
SELECT v."COD",v."GR1",v."GR11",v."ORAS",v."TARA",v."GR12",v."ADRESS",v."BANK",v."ACCOUNT",v."ACCOUNT1",v."ACCOUNT2",v."ACCOUNT3",v."ACCOUNTV1",v."ACCOUNTV2",v."ACCOUNTV3",v."ACCOUNTV4",v."ACCOUNTV5",v."CVALACC1",v."CVALACC2",v."CVALACC3",v."CVALACC4",v."CVALACC5",v."MFO",v."CONTACT",v."DATE1",v."TELEFON",v."TELPRIM",v."ADM_CENTRU",v."DIRECTOR",v."PERSDECONTR",v."TELEFONPERSDECONTR",v."FAX",v."COD_RAION_S_25",v."CATEGORIA_S_26",v."TIPCLIENT_S_27",v."TIPCLIENTSOL_S_28",v."BANK_S_30",v."OKPO",v."OKHN",v."SPEC1",v."SPEC2",v."DISTANTAKM",v."CONTCCOST",v."CONTSP1",v."CONTSP2",v."CONTPENTRUUZURA",v."CODFISCAL",v."SPECF1",v."SPECF2",v."CONTSP3",v."DT",v."CT",v."CONT1PENTRUUZURA",v."MP_TIPFURNIZ_S_51",v."MP_AGRONOM_UNIV",v."MP_PERSINCR_UNIV",v."MINJUST_REGNUM",v."MINJUST_REGDATA",v."OI_TIPSECT_S_79",v."CHELTMBP_CONT",v."CHELTMBP_CONT1",v."CHELTMBP_SC",v."CHELTMBP_DEP",v."CHELTMBP_SC1",v."ADDR_FACT",v."ACTIONER_SA_1",v."TIP_IMP_VENIT_SRC"
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=12 AND COD1=v.Oras) AS clcOrasT
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=11 AND COD1=v.tara) AS clcTaraT
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=26 AND COD1=v.CATEGORIA_S_26) AS clcCATEGORIA_S_26t
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=27 AND COD1=v.TipClient_S_27) AS clcTipClient_S_27t
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=28 AND COD1=v.TipClientSol_S_28) AS clcTipClientSol_S_28t
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=30 AND COD1=v.Bank_S_30) AS clcBank_S_30t
,(SELECT NUMBER2 FROM VMS_SYSS WHERE TIP='S' AND COD=12 AND COD1=v.Oras) AS clcCod_Raion_S_25
,(SELECT (SELECT denumirea FROM VMS_SYSS S2 WHERE TIP='S' AND COD=25 AND COD1=S.number2)
  FROM VMS_SYSS S WHERE TIP='S' AND COD=12 AND COD1=v.oras) AS clcCod_Raion_S_25t
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=51 AND COD1=v.MP_TIPFURNIZ_S_51) AS clcMP_TIPFURNIZ_S_51t
,(SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=v.MP_AGRONOM_univ) AS clcMP_AGRONOM_univt
,(SELECT DENUMIREA FROM VMS_UNIVERS WHERE COD=v.MP_persincr_univ) AS clcMP_persincr_univt
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='S' AND COD=79 AND COD1=v.OI_tipsect_S_79) AS clcOI_tipsect_S_79t
,(SELECT NUMBER1 FROM VMS_SYSS WHERE TIP='S' AND COD=12 AND COD1=v.Oras) AS clc_country_S_11
,(SELECT (SELECT denumirea FROM VMS_SYSS  WHERE TIP='S' AND COD=11 AND COD1=S.number1)
  FROM VMS_SYSS s WHERE TIP='S' AND COD=12 AND COD1=v.oras) AS clc_country_S_11T
,(SELECT DENUMIREA FROM VMS_SYSS WHERE TIP='SA' AND COD=1 AND COD1=v.ACTIONER_SA_1) AS clc_ACTIONER_SA_1t
,(SELECT VALUE FROM VMS_CONST_ACT WHERE TIP='TIP_IMP_VENIT_SRC' AND tip_imp_venit_src=ID) AS clctip_imp_venit_src
,(SELECT denumirea FROM VMS_CONST_ACT WHERE TIP='TIP_IMP_VENIT_SRC' AND tip_imp_venit_src=ID) AS clctip_imp_venit_srcT
FROM TMS_ORG v;

