create or replace PROCEDURE INSERE_SSCCNF (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
                                          P_NRLANCTONF  IN NOTAFISCAL.NRLANCTONF%TYPE,
                                          P_CDMENSERRO  OUT VARCHAR2)
IS
   V_DIGEXTENSAO  VARCHAR2(01);
   V_SEQUENCIAL   INTEGER;
   V_CDSEQUENCIAL VARCHAR2(07);
   V_PREVISOGS1   VARCHAR2(100);
   V_QTAPRODPREOP FLOAT;
   V_INILOOP      INTEGER;
   V_PREFIXOSSCC  VARCHAR2(17);
   V_SOMA         FLOAT;
   V_INDICE       INTEGER;
   V_VALOR        FLOAT;
   V_DIGITO       INTEGER;
   V_CDSSCC       VARCHAR2(20);
   V_COUNT        INTEGER;
   V_CAIXAPALET   FLOAT;
   V_CDCENTRALPALET VARCHAR2(03);
   V_SEQUENCIALPALET   INTEGER;
   V_CDSEQUENCIALPALET VARCHAR2(07);
   V_NRPALET      VARCHAR2(10);
   V_VRPESOBRUT   FLOAT;
   V_COUNTPALET   INTEGER;
   V_COUNTSSCC    INTEGER;
BEGIN
   P_CDMENSERRO   := 'OK';
   V_CDSEQUENCIAL := NULL; 
   V_INDICE       := 1;

   SELECT NVL(MAX(CDDIGEXTENSAO),0)
     INTO V_DIGEXTENSAO
     FROM SERIALSSCCNF;

   SELECT TO_NUMBER(NVL(MAX(NRSEQUENCIAL),0))
     INTO V_SEQUENCIAL
     FROM NOVOCODIGO
    WHERE CDCONTADOR = 'SSCCNF';

   IF V_SEQUENCIAL = 9999999 THEN
      V_DIGEXTENSAO := V_DIGEXTENSAO + 1;
      V_SEQUENCIAL := 0;
   END IF;

   /*SELECT SUBSTR(MAX(I.CDDUNPROD),2, 9), MAX(I.NRPALLET), (MAX(PR.VRPESOBRUTPR)*MAX(I.NRPALLET))
     INTO V_PREVISOGS1, V_CAIXAPALET, V_VRPESOBRUT
     FROM INFADICPROD I, PRODUTO PR
    WHERE I.CDPRODUTO = P_CDPRODUTO
      AND I.CDPRODUTO = PR.CDPRODUTO;*/
      

    SELECT NVL(CDPREFIXOGS1,'SEM_PREVISOGS1')
       INTO V_PREVISOGS1
       FROM NOTAFISCAL N, EMPRESA E
      WHERE CDFILIAL    = P_CDFILIAL 
        AND NRLANCTONF  = P_NRLANCTONF
        AND N.CDEMPRESA = E.CDEMPRESA;

   
   IF V_PREVISOGS1 <> 'SEM_PREVISOGS1' THEN
       IF (LENGTH(V_PREVISOGS1)> 9) THEN     
         P_CDMENSERRO := 'PREFIXOGS1_GRANDE';   
       END IF;
   ELSE 
     P_CDMENSERRO :='SEM_PREFIXOGS1'; 
   END IF;

   IF P_CDMENSERRO = 'OK' THEN
   
      SELECT COUNT(1)
        INTO V_COUNTSSCC
        FROM SERIALSSCCNF
       WHERE CDFILIAL     = P_CDFILIAL
         AND NRLANCTONF   = P_NRLANCTONF;

      IF V_QTAPRODPREOP >= V_COUNTSSCC THEN
         V_QTAPRODPREOP := V_QTAPRODPREOP - V_COUNTSSCC;
      END IF;

     
      V_CDSEQUENCIAL := LPAD(RTRIM(TO_CHAR(TO_NUMBER(V_SEQUENCIAL) + 1)),7,'0');
      V_SEQUENCIAL   := V_SEQUENCIAL+1;
      V_PREFIXOSSCC  := V_DIGEXTENSAO||V_PREVISOGS1||V_CDSEQUENCIAL;
      V_SOMA := 0;
     
      FOR V_INDICE IN 1 .. 17 LOOP
        V_VALOR := TO_NUMBER(SUBSTR(V_PREFIXOSSCC, V_INDICE, 1));
        IF (MOD(V_INDICE,2) <> 0) THEN
           V_VALOR := V_VALOR * 3;
        END IF;
        V_SOMA := V_SOMA + V_VALOR;
      END LOOP;
      V_DIGITO := 10 - MOD(V_SOMA,10);
     
      IF V_DIGITO = 10 THEN
        V_DIGITO := 0;
      END IF;
      
      V_CDSSCC := V_PREFIXOSSCC||V_DIGITO;

      INSERT INTO SERIALSSCCNF(CDFILIAL, NRLANCTONF, CDSSCC, CDDIGEXTENSAO, CDSEQUENCIAL)
                       VALUES(P_CDFILIAL,P_NRLANCTONF,V_CDSSCC,V_DIGEXTENSAO,V_CDSEQUENCIAL);


      IF V_CDSEQUENCIAL IS NOT NULL THEN
         SELECT COUNT(CDCONTADOR)
           INTO V_COUNT
           FROM NOVOCODIGO
          WHERE CDCONTADOR = 'SSCCNF';
         IF (V_COUNT > 0) THEN
            UPDATE NOVOCODIGO
               SET NRSEQUENCIAL = LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_CDSEQUENCIAL)) )),20,'0')
             WHERE CDCONTADOR = 'SSCCNF';
         ELSE
            INSERT INTO NOVOCODIGO(CDCONTADOR, NRSEQUENCIAL)
            VALUES('SSCCNF', LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_CDSEQUENCIAL)) )),20,'0'));
         END IF;
      END IF;
   END IF;
END;
/