/*
-- ============================================================
--   Table: SERIALSSCC
-- ============================================================
create table SERIALSSCC 
(
   CDDIGEXTENSAO VARCHAR2(01) NOT NULL,
   CDSEQUENCIAL  VARCHAR2(07) NOT NULL,
   CDSSCC        VARCHAR2(18) NOT NULL,
   CDFILIAL      VARCHAR2(04) NULL,
   NRORDPROD     VARCHAR2(20) NULL,
   NRSUBORDPROD  VARCHAR2(01) NULL,
   CDPRODUTO     VARCHAR2(10) NULL,
   IDTIPOEMB     VARCHAR2(01) NULL,
   constraint PK_SERIALSSCC primary key (CDSSCC)
)
/

ALTER TABLE SERIALSSCC ADD NRPALET VARCHAR2(10);

ALTER TABLE SERIALSSCC ADD VRPESOBRUT FLOAT;

ALTER TABLE SERIALSSCC ADD NRLOTEPALET VARCHAR2(250);

ALTER TABLE SERIALSSCC ADD CDCENTRALPALET VARCHAR2(03);

create table LOTEPALET
(
   CDFILIAL      VARCHAR2(04) NOT NULL,
   CDPRODUTO     VARCHAR2(10) NOT NULL,
   CDSSCC        VARCHAR2(18) NOT NULL,
   NRPALET       VARCHAR2(10) NOT NULL
   NRLOTEESTQ    VARCHAR2(20) NULL,
   NRSUBLOTE     VARCHAR2(04) NULL,
   constraint PK_LOTEPALET primary key (CDFILIAL, CDPRODUTO, CDSSCC, NRPALET)
)
/

SERIALSSCC(CDDIGEXTENSAO, CDSEQUENCIAL, CDSSCC, CDFILIAL, NRORDPROD, NRSUBORDPROD, CDPRODUTO)
*/
create or replace PROCEDURE INSERE_SSCC (P_CDFILIAL     IN SERIALSSCC.CDFILIAL%TYPE,
                                         P_NRORDPROD    IN SERIALSSCC.NRORDPROD%TYPE,
                                         P_NRSUBORDPROD IN SERIALSSCC.NRSUBORDPROD%TYPE,
                                         P_CDPRODUTO    IN SERIALSSCC.CDPRODUTO%TYPE,
                                         P_IDTIPOEMB    IN SERIALSSCC.IDTIPOEMB%TYPE,
                                         P_CDMENSERRO   OUT VARCHAR2)
IS
   V_DIGEXTENSAO  VARCHAR2(01);
   V_SEQUENCIAL   INTEGER;
   V_CDSEQUENCIAL VARCHAR2(07);
   V_PREVISOGS1   VARCHAR2(09);
   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_CDSEQUENCIALPALET := NULL;
   SELECT NVL(MAX(CDDIGEXTENSAO),0)
     INTO V_DIGEXTENSAO
     FROM SERIALSSCC;

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

   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;

   IF V_PREVISOGS1 IS NULL THEN
      P_CDMENSERRO := 'DUNVAZIO';
   END IF;
   IF P_IDTIPOEMB = 'P' THEN
      IF (V_CAIXAPALET IS NULL) OR (V_CAIXAPALET = 0) THEN
         P_CDMENSERRO := 'PALCAIXA';
      END IF;
   END IF;
   
   IF P_CDMENSERRO = 'OK' THEN
      SELECT MAX(ORD.QTAPRODPREOP)
        INTO V_QTAPRODPREOP
        FROM ORDPRODUCAO ORD
       WHERE ORD.CDFILIAL     = P_CDFILIAL
         AND ORD.NRORDPROD    = P_NRORDPROD
         AND ORD.NRSUBORDPROD =  P_NRSUBORDPROD
         AND ORD.CDPRODUTO    = P_CDPRODUTO;

      SELECT COUNT(1)
        INTO V_COUNTSSCC
        FROM SERIALSSCC
       WHERE CDFILIAL = P_CDFILIAL
         AND NRORDPROD = P_NRORDPROD
         AND NRSUBORDPROD = P_NRSUBORDPROD
         AND CDPRODUTO = P_CDPRODUTO
         AND IDTIPOEMB = P_IDTIPOEMB;

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

      IF P_IDTIPOEMB = 'P' THEN
         SELECT MAX(ORD.QTAPRODPREOP)
           INTO V_QTAPRODPREOP
           FROM ORDPRODUCAO ORD
          WHERE ORD.CDFILIAL     = P_CDFILIAL
            AND ORD.NRORDPROD    = P_NRORDPROD
            AND ORD.NRSUBORDPROD =  P_NRSUBORDPROD
            AND ORD.CDPRODUTO    = P_CDPRODUTO;

         V_QTAPRODPREOP := CEIL(V_QTAPRODPREOP/V_CAIXAPALET);
         SELECT COUNT(1)
           INTO V_COUNTSSCC
           FROM SERIALSSCC
          WHERE CDFILIAL = P_CDFILIAL
            AND NRORDPROD = P_NRORDPROD
            AND NRSUBORDPROD = P_NRSUBORDPROD
            AND CDPRODUTO = P_CDPRODUTO
            AND IDTIPOEMB = P_IDTIPOEMB;

         IF V_QTAPRODPREOP >= V_COUNTSSCC THEN
            V_QTAPRODPREOP := V_QTAPRODPREOP - V_COUNTSSCC;
         END IF;
           
        SELECT LPAD(RTRIM(TO_CHAR(TO_NUMBER(MAX(nvl(CDCENTRALPALET, 0))) + 1)),3,'0')
           INTO V_CDCENTRALPALET
           FROM SERIALSSCC;

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

      END IF;
      FOR  V_INILOOP  IN   1 .. V_QTAPRODPREOP  LOOP
         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;

         V_NRPALET := '';
         IF P_IDTIPOEMB = 'P' THEN
            IF V_SEQUENCIALPALET = 9999999 THEN
               V_CDCENTRALPALET := V_CDCENTRALPALET + 1;
               V_SEQUENCIALPALET := 0;
            END IF;

            V_CDSEQUENCIALPALET := LPAD(RTRIM(TO_CHAR(TO_NUMBER(V_SEQUENCIALPALET) + 1)),7,'0');
            V_SEQUENCIALPALET   := V_SEQUENCIALPALET+1;
            V_NRPALET           := V_CDCENTRALPALET||V_CDSEQUENCIALPALET;

         END IF;
         INSERT INTO SERIALSSCC(CDDIGEXTENSAO, CDSEQUENCIAL, CDSSCC, CDFILIAL, NRORDPROD, NRSUBORDPROD, CDPRODUTO, IDTIPOEMB, NRPALET, VRPESOBRUT,CDCENTRALPALET)
         VALUES(V_DIGEXTENSAO, V_CDSEQUENCIAL, V_CDSSCC,P_CDFILIAL, P_NRORDPROD, P_NRSUBORDPROD, P_CDPRODUTO, P_IDTIPOEMB, V_NRPALET, V_VRPESOBRUT,V_CDCENTRALPALET);
      END LOOP; /* FOR  V_INILOOP*/
      IF V_CDSEQUENCIAL IS NOT NULL THEN
         SELECT COUNT(CDCONTADOR)
           INTO V_COUNT
           FROM NOVOCODIGO
          WHERE CDCONTADOR = 'SSCC';
         IF (V_COUNT > 0) THEN
            UPDATE NOVOCODIGO
               SET NRSEQUENCIAL = LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_CDSEQUENCIAL)) )),20,'0')
             WHERE CDCONTADOR = 'SSCC';
         ELSE
            INSERT INTO NOVOCODIGO(CDCONTADOR, NRSEQUENCIAL)
            VALUES('SSCC', LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_CDSEQUENCIAL)) )),20,'0'));
         END IF;
      END IF;
      IF P_IDTIPOEMB = 'P' THEN
         IF V_CDSEQUENCIALPALET IS NOT NULL THEN
            SELECT COUNT(CDCONTADOR)
              INTO V_COUNTPALET
              FROM NOVOCODIGO
             WHERE CDCONTADOR = 'PALET';
            IF (V_COUNTPALET > 0) THEN
               UPDATE NOVOCODIGO
                  SET NRSEQUENCIAL = LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_CDSEQUENCIALPALET)) )),20,'0')
                WHERE CDCONTADOR = 'PALET';
            ELSE
               INSERT INTO NOVOCODIGO(CDCONTADOR, NRSEQUENCIAL)
               VALUES('PALET', LPAD(RTRIM(TO_CHAR(TO_NUMBER(RTRIM(V_CDSEQUENCIALPALET)) )),20,'0'));
            END IF;
         END IF;
      END IF;
   END IF;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('INSERE_SSCC','6.45.009'); END;
/
