SET DATEFORMAT YMD;
DROP TRIGGER IF EXISTS TR_ADM_INSERTARCXC
GO
CREATE TRIGGER TR_ADM_INSERTARCXC ON SAACXC WITH ENCRYPTION FOR INSERT AS
BEGIN
 SET NOCOUNT ON;
DECLARE
  @CODCLIE    varchar(15),
  @SALDO      decimal(28,4),
  @MONTO      decimal(28,4),
  @SaldoAnt   DECIMAL(28,4),
  @FECHAE     datetime,
  @NroUnico   INT,
  @TipoCxC    varchar(2),
  @FactorM    DECIMAL(28,4);
SELECT @CODCLIE=CODCLIE,
       @FechaE=FechaE,   @FactorM=Factor
  FROM INSERTED;
SET @SALDOANT=0;
SELECT TOP 1 @SALDOANT=ISNULL(SALDOACT,0)
  FROM SAACXC WITH (NOLOCK) 
 WHERE (CODCLIE=@CODCLIE) AND (FECHAE<@FechaE)
 ORDER BY FECHAE DESC;
UPDATE SAACXC 
   SET @SALDOANT=SALDOACT=@SALDOANT+
       ISNULL((case when SUBSTRING(tipocxc,1,1) in ('1','6','7') then 1
                    when SUBSTRING(tipocxc,1,1) in ('2') then
                         (case when tipocxc='20'  then 1
                               when tipocxc<>'25' then 1
                          else 0 end)
                    when SUBSTRING(tipocxc,1,1) in ('5') then
                         (case when tipocxc<>'50' then -1 else 0 end)
                    when SUBSTRING(tipocxc,1,1) in ('3','4','8') then -1
                    Else 0
               end),0)*MONTO
  FROM (SELECT CODCLIE,FECHAE,NROUNICO, ROW_NUMBER() OVER
               (ORDER BY CODCLIE, FECHAE) AS RowNum
          FROM SAACXC WITH (NOLOCK) 
         WHERE (FECHAE>=@FechaE) AND
               (CODCLIE=@CODCLIE)) V
 WHERE (SAACXC.NROUNICO=V.NROUNICO);
END;
GO
DROP TRIGGER IF EXISTS TR_ADM_INSERTARCXP
GO
CREATE TRIGGER TR_ADM_INSERTARCXP ON SAACXP WITH ENCRYPTION FOR INSERT AS
BEGIN
 SET NOCOUNT ON;
DECLARE
   @CODPROV    varchar(15),
   @MONTO      decimal(28,4),
   @saldo      decimal(28,4),
   @SaldoAnt   DECIMAL(28,4),
   @FECHAE     datetime,
   @NroUnico   INT,
   @FactorM    DECIMAL(28,4);
SELECT @CODPROV=CODPROV, @FechaE=FechaE, @FactorM=Factor
  FROM INSERTED;
SET @SALDOANT=0;
SELECT TOP 1 @SALDOANT=ISNULL(SALDOACT,0)
  FROM SAACXP WITH (NOLOCK) 
 WHERE (CODPROV=@CODPROV) AND (FECHAE<@FechaE)
 ORDER BY FECHAE DESC
UPDATE SAACXP 
   SET @saldoant=saldoact=@SALDOANT+
       ISNULL((CASE WHEN (CHARINDEX(TIPOCXP,'10.30.60.70')>0)
                          OR (TIPOCXP IN ('31','82')) then 1
                    WHEN ((CHARINDEX(SUBSTRING(TIPOCXP,1,1),'2.4')>0)
                          OR (TIPOCXP='81')) then -1
                    WHEN SUBSTRING(tipocxp,1,1) in ('5') then
                         (case when tipocxp<>'50' then -1 else 0 end)
                    WHEN SUBSTRING(tipocxp,1,1) in ('3') then
                         (case when tipocxp<>'35' then 1
                          else 0 end)
                    Else 0
               END),0)*MONTO
   FROM (SELECT NROUNICO, CODPROV,
                ROW_NUMBER() OVER (ORDER BY CODPROV, FECHAE) AS RowNum
           FROM SAACXP WITH (NOLOCK) 
          WHERE (FECHAE>=@FechaE) AND
                (CODPROV=@CODPROV)) V
  WHERE (SAACXP.NROUNICO=V.NROUNICO);
END;
GO
DROP TRIGGER IF EXISTS TR_ADM_INSERTDOCUMENTOVENTA
GO
CREATE TRIGGER TR_ADM_INSERTDOCUMENTOVENTA ON SAFACT
WITH ENCRYPTION FOR INSERT AS
BEGIN 
 SET NOCOUNT ON;
 DECLARE 
    @TipoFac  VARCHAR(2),
    @NumeroR  VARCHAR(20),
    @NumeroD  VARCHAR(20),
    @CodEsta  VARCHAR(10),
    @CodSUCU  VARCHAR(10),
    @CodCLIE  VARCHAR(15),
    @CODUBIC  VARCHAR(10),
    @PERIODO  VARCHAR(10),
    @CodUsua  VARCHAR(10),
    @MONTO    DECIMAL(28,4),
    @Saldo    decimal(28,4),
    @DESCONT  DECIMAL(28,4),
    @FLETES   DECIMAL(28,4),
    @MTOTAX   DECIMAL(28,4),
    @RETENIVA DECIMAL(28,4),
    @COSTO    DECIMAL(28,4),
    @CANCELI  DECIMAL(28,4),
    @CANCELA  DECIMAL(28,4),
    @CANCELP  DECIMAL(28,4),
    @DESCTOI  DECIMAL(28,4),
    @CONTADO  DECIMAL(28,4),
    @CREDITO  DECIMAL(28,4),
    @CANTPTOS DECIMAL(28,4),
    @FactorM  DECIMAL(28,4),
    @NROUNICO INT,
    @FROMTRAN INT,
    @FechaE   DateTime;
 SET DATEFORMAT YMD;
 SELECT @TipoFac=TIPOFAC, @NumeroD=NUMEROD, @CodEsta=CODESTA, 
        @CodUsua=CODUSUA, @CodSUCU=CODSUCU, @MONTO=SIGNO*MONTO,
        @DESCONT=SIGNO*(DESCTO1+DESCTO2), @FLETES=SIGNO*FLETES, 
        @MTOTAX=SIGNO*MTOTAX,@COSTO=SIGNO*(COSTOPRD+COSTOSRV), 
        @CONTADO=SIGNO*CONTADO, 
        @CREDITO=SIGNO*CREDITO, 
        @CODUBIC=CODUBIC, 
        @CANCELA=SIGNO*CANCELA, 
        @CANCELP=SIGNO*CANCELP, 
        @CANCELI=SIGNO*CANCELI, @DESCTOI=SIGNO*DESCTOP,@RETENIVA=SIGNO*RETENIVA,
        @CANTPTOS=SIGNO*VALORPTOS, @NumeroR=NUMEROR,@FACTORM=FACTOR, 
        @CODCLIE=CODCLIE, @FechaE=FECHAE, @FROMTRAN=FROMTRAN 
   FROM INSERTED;
 IF (@TipoFac In ('A','B')) BEGIN
    SET @PERIODO=REPLACE(SUBSTRING(CONVERT(VARCHAR,@FECHAE,120),1,7),'-','');
    UPDATE SACLIE 
       SET SALDO=SALDO+@CREDITO, PAGOSA=PAGOSA-@CANCELA,
           RETENIVA=RETENIVA+@RETENIVA,
           SALDOPTOS=SALDOPTOS+IIF(@CANCELP=0,@CANTPTOS,-@CANCELP),
           FECHAUV=@FECHAE, MontoUV=@MONTO, NumeroUV=@NUMEROD, 
           MontoMax=(CASE WHEN @MONTO>MontoMax THEN @MONTO ELSE MONTOMAX END), 
           MtoMaxCred=(CASE WHEN @CREDITO>MtoMaxCred THEN @CREDITO ELSE MTOMAXCRED END)
     WHERE CODCLIE=@CODCLIE;
    IF (@CANTPTOS>0) BEGIN
       IF NOT EXISTS(SELECT TOP 1 CODCLIE, PERIODO 
                       FROM SAPTOSCLIENTE WITH (NOLOCK)
                      WHERE CODCLIE=@CODCLIE AND PERIODO=@PERIODO)
          INSERT INTO SAPTOSCLIENTE (CODCLIE, PERIODO) 
                      VALUES (@CODCLIE,@PERIODO);
       UPDATE SAPTOSCLIENTE SET 
              FechaE=@FECHAE,
              NroFacts=NroFacts+IIF(@TIPOFAC='A',1,0),
              Credito=Credito+@Credito, 
              Contado=Contado+@Contado, 
              Mtoventas=Mtoventas+@MONTO,
              Descto=Descto+@DESCONT,
              ValorPtos=ValorPtos+@CANTPTOS
        WHERE CODCLIE=@CODCLIE AND PERIODO=@PERIODO;
    END;
 END;
 If (@TIPOFAC IN ('B','D')) BEGIN
    UPDATE SAFACT 
       SET NUMEROR=@NUMEROD
     WHERE (CODSUCU=@CODSUCU) AND
           (NUMEROD=@NumeroR) AND
           (TIPOFAC=IIF(@TIPOFAC='B','A','C'));
 END;
END;
GO
DROP TRIGGER IF EXISTS TR_ADM_INSERTDOCUMENTOCOMPRA
GO
CREATE TRIGGER TR_ADM_INSERTDOCUMENTOCOMPRA ON SACOMP
WITH ENCRYPTION FOR INSERT AS
BEGIN 
 SET NOCOUNT ON;
 DECLARE 
    @TipoCom  VARCHAR(2),
    @NumeroR  VARCHAR(20),
    @NumeroD  VARCHAR(20),
    @CodEsta  VARCHAR(10),
    @CodSUCU  VARCHAR(10),
    @CodProv  VARCHAR(15),
    @CODUBIC  VARCHAR(10),
    @PERIODO  VARCHAR(10),
    @CodUsua  VARCHAR(10),
    @MONTO    DECIMAL(28,4),
    @DESCONT  DECIMAL(28,4),
    @FLETES   DECIMAL(28,4),
    @MTOTAX   DECIMAL(28,4),
    @RETENIVA DECIMAL(28,4),
    @CANCELI  DECIMAL(28,4),
    @CANCELA  DECIMAL(28,4),
    @DESCTOI  DECIMAL(28,4),
    @CONTADO  DECIMAL(28,4),
    @CREDITO  DECIMAL(28,4),
    @FechaE   DateTime;
 SET DATEFORMAT YMD;
 SELECT @TipoCom=TipoCom, @NumeroD=NUMEROD, @CodEsta=CODESTA, 
        @CodUsua=CODUSUA, @CodSUCU=CODSUCU, @MONTO=SIGNO*MONTO,
        @DESCONT=SIGNO*(DESCTO1+DESCTO2), @FLETES=SIGNO*FLETES, 
        @MTOTAX=SIGNO*MTOTAX, 
        @CONTADO=SIGNO*CONTADO, 
        @CREDITO=SIGNO*CREDITO, 
        @CODUBIC=CODUBIC, @CANCELA=SIGNO*CANCELA,
        @CANCELI=SIGNO*CANCELI, @DESCTOI=SIGNO*DESCTOP,@RETENIVA=SIGNO*RETENIVA,
        @NumeroR=NUMERON, 
        @CodProv=CodProv, @FechaE=FECHAE 
   FROM INSERTED;
 IF (@TipoCom In ('H','I')) BEGIN
    UPDATE SAPROV 
       SET SALDO=SALDO+@CREDITO, PAGOSA=PAGOSA-@CANCELA,
           RETENIVA=RETENIVA+@RETENIVA, RETENISLR=RETENISLR+@CANCELI,
           FECHAUC=@FECHAE, MontoUC=@MONTO, NumeroUC=@NUMEROD, 
           MontoMax=(CASE WHEN @MONTO>MontoMax THEN @MONTO ELSE MONTOMAX END) 
     WHERE CodProv=@CodProv;
 END;
 If (@TipoCom IN ('I','K')) BEGIN
    UPDATE SACOMP 
       SET NUMERON=@NUMEROD
     WHERE (CODSUCU=@CODSUCU) And (NUMEROD=@NumeroR) AND (CODPROV=@CodProv) AND 
           (TipoCom=(CASE WHEN @TipoCom='I' THEN 'H' ELSE 'J' END));
 END;
END;
GO
