IF EXISTS (SELECT * FROM SYSOBJECTS O, SYSUSERS U
           WHERE O.NAME = 'GERA_LOTECONTABIL' AND
                 TYPE = 'P' 	         AND
	              U.UID = O.UID      AND
	              U.NAME = USER)
DROP PROCEDURE GERA_LOTECONTABIL
GO

CREATE PROCEDURE GERA_LOTECONTABIL(
  @P_CDEMPRESA   VARCHAR(2),
  @P_TIPOMOVI    VARCHAR,
  @P_CDFILIEMIT  VARCHAR(4),
  @P_CDFILIRECE  VARCHAR(4),
  @P_DTMOVI      DATETIME,
  @P_TIPOOPERAC  VARCHAR,
  @P_TPCREDDEB   VARCHAR,
  @P_CDLOTE      VARCHAR OUTPUT,
  @P_NRLANCLOTE   VARCHAR OUTPUT)
AS
BEGIN
  DECLARE @V_CDLOTE      VARCHAR(20);
  DECLARE @V_CDLOTE1     VARCHAR(20);
  DECLARE @V_SEQUENCIAL  VARCHAR(20);
  DECLARE @V_NRLANCLOTE  VARCHAR(06);
  DECLARE @V_EXISTELOTE  VARCHAR(20);
  DECLARE @V_DTINI       DATETIME;
  DECLARE @V_DTFIN       DATETIME;

  IF @P_TIPOMOVI = '00' BEGIN
     SELECT @V_CDLOTE = CDREFLOTECTBTO
       FROM TIPOOPERAC A
      WHERE (CDTIPOOPER = @P_TIPOOPERAC)
        AND (IDENTRSAIDOP = 'E');
  END
  ELSE IF @P_TIPOMOVI = '01' BEGIN 
          SELECT @V_CDLOTE = CDREFLOTECTBTO
            FROM TIPOOPERAC A
           WHERE (CDTIPOOPER   = @P_TIPOOPERAC)
             AND (IDENTRSAIDOP = 'S');
       END
       ELSE IF @P_TIPOMOVI = '02' BEGIN
               SELECT @V_CDLOTE = CDREFLOTECTBRET
                 FROM  PARAMESTOQUE;
            END
            ELSE IF @P_TIPOMOVI = '03' BEGIN 
                    SELECT @V_CDLOTE = CDREFLOTECTBPRO
                      FROM PARAMESTOQUE;
                 END
                  ELSE IF @P_TIPOMOVI = '04' BEGIN
                          SELECT @V_CDLOTE = CDREFLOTECTBSAI
                            FROM PARAMESTOQUE;
                       END
                       ELSE IF @P_TIPOMOVI = '05' BEGIN
                               SELECT @V_CDLOTE = CDREFLOTECTBIMP
                                 FROM  PARAMESTOQUE;
                            END
                            ELSE IF @P_TIPOMOVI = '06' BEGIN
                                    SELECT @V_CDLOTE = CDREFLOTECTBTP
                                      FROM TIPOCTPAGAR
                                     WHERE CDTPCTPAGAR = @P_TIPOOPERAC;
                                 END
                                 ELSE IF @P_TIPOMOVI = '07' BEGIN
                                         SELECT @V_CDLOTE = CDREFLOTECTBTR
                                           FROM TIPOCTRECEBER
                                          WHERE CDTPCTRECEB = @P_TIPOOPERAC;
                                      END
                                      ELSE IF @P_TIPOMOVI = '08' BEGIN
                                              IF @P_TPCREDDEB = 'C' BEGIN
                                                 SELECT @V_CDLOTE = CDREFLOTECTBTCB
                                                   FROM TPCREDBANC
                                                  WHERE CDTPCREDBANC = @P_TIPOOPERAC;
                                              END                       
                                              ELSE BEGIN
                                                 SELECT @V_CDLOTE = CDREFLOTECTBTDB
                                                   FROM TPDEBIBANC
                                                  WHERE CDTPDEBIBANC = @P_TIPOOPERAC;
                                              END;
                                           END
                                           ELSE IF @P_TIPOMOVI = '09' BEGIN 
                                                   SELECT @V_CDLOTE = CDREFLOTECTBL
                                                     FROM PARAMCTBL;
                                                END
                                                ELSE IF @P_TIPOMOVI = '10' BEGIN
                                                        SELECT @V_CDLOTE = CDREFLOTECTBTBP
                                                          FROM TIPOBAIXAPAG
                                                         WHERE CDTPBAIXAPAG = @P_TIPOOPERAC;
                                                     END
                                                     ELSE IF @P_TIPOMOVI = '11' BEGIN
                                                             SELECT @V_CDLOTE = CDREFLOTECTBTBR
                                                               FROM TIPOBAIXAREC
                                                              WHERE CDTPBAIXAREC = @P_TIPOOPERAC;
                                                          END; 

  
  IF @V_CDLOTE IS NULL BEGIN
     SELECT @V_CDLOTE = CDREFLOTECTBL 
       FROM PARAMCTBL;
  END;
  
  SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[EMP]', @P_CDEMPRESA);
  
  SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[FIEM]', @P_CDFILIEMIT);
  
  SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[FIRE]', @P_CDFILIRECE);
  
  SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[ANO]',SUBSTRING(CONVERT(VARCHAR, @P_DTMOVI, 103), 7, 4));
  
  SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[MES]',SUBSTRING(CONVERT(VARCHAR, @P_DTMOVI, 103), 4, 2));
  
  SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[DIA]',SUBSTRING(CONVERT(VARCHAR,@P_DTMOVI, 103),  1, 2));
  
  SELECT @V_CDLOTE1 = MAX(CDLOTE) 
    FROM CAPALOTE
   WHERE CDEMPRESA = @P_CDEMPRESA
     AND IDLOTE <> 'L'
     AND CDLOTE LIKE REPLACE(@V_CDLOTE,'[SEQ]','') + '%';

  IF @V_CDLOTE1 IS NULL BEGIN
     EXEC NOVO_CODIGO @V_CDLOTE, @V_SEQUENCIAL, 1;
     SET @V_CDLOTE = REPLACE(@V_CDLOTE,'[SEQ]',SUBSTRING(@V_SEQUENCIAL, 17, 4));
  END
  ELSE BEGIN
     SET @V_CDLOTE = @V_CDLOTE1;
  END;

  SELECT @V_NRLANCLOTE = MAX(NRLANCLOTE)
    FROM LANCLOTE
   WHERE CDEMPRESA = @P_CDEMPRESA
     AND CDLOTE = @V_CDLOTE;
                                                           
  SELECT @V_EXISTELOTE = CDLOTE
    FROM CAPALOTE
   WHERE CDEMPRESA = @P_CDEMPRESA
     AND CDLOTE = @V_CDLOTE;

  IF @P_TIPOMOVI IN ('06','07','08','09','10','11') BEGIN
     SELECT @V_DTINI = DTINICCTBL,
            @V_DTFIN = DTFINACCTBL
       FROM CONTOPECTBL
      WHERE CDEMPRESA = @P_CDEMPRESA;
  END
  ELSE IF @P_TIPOMOVI = '01' BEGIN
          SELECT @V_DTINI = DTINIINTCONTDFS, 
                 @V_DTFIN = DTFININTCONTDFS
            FROM CONTOPECTBL
           WHERE CDEMPRESA = @P_CDEMPRESA;
       END
       ELSE BEGIN
          SELECT @V_DTINI = DTINIINTCONTDFE, 
                 @V_DTFIN = DTFININTCONTDFE
            FROM CONTOPECTBL
           WHERE CDEMPRESA = @P_CDEMPRESA;
       END;

  IF @V_EXISTELOTE IS NULL BEGIN
    INSERT INTO CAPALOTE
      (CDEMPRESA, CDLOTE, DTINICLOTE, DTFINALOTE, IDTPMOVLOTE, IDLOTE)
    VALUES
      (@P_CDEMPRESA, @V_CDLOTE, @V_DTINI, @V_DTFIN, @P_TIPOMOVI, 'D');
  END;

  IF @V_NRLANCLOTE IS NULL BEGIN
     SET @V_NRLANCLOTE = '000001';
  END
  ELSE BEGIN
     SET @V_NRLANCLOTE = RIGHT(REPLICATE('0', 6) + CONVERT(VARCHAR(6),(CONVERT(INT, @V_NRLANCLOTE) + 1)), 6);
  END;

  SET @P_CDLOTE     = @V_CDLOTE;
  SET @P_NRLANCLOTE = @V_NRLANCLOTE;
END
GO