--@C:\Web_409\Srv\Src\Cli\SAP\SQL\0002_SAPTRGO_USR_FORMA_PAGAMENTO.sql

CREATE OR REPLACE PROCEDURE USR_FORMA_PAGAMENTO
AS
   V_IDTPPERIODO  FORMPGTO.IDTPPERIODO%TYPE;
   V_CDFORMPGTO   NOVOCODIGO.NRSEQUENCIAL%TYPE;
   V_NRSEQUENCIAL NOVOCODIGO.NRSEQUENCIAL%TYPE;
   V_CDCONTADOR   NOVOCODIGO.CDCONTADOR%TYPE;
   V_CDAUXILIAR   FORMPGTO.CDFORMPGTO%TYPE;

BEGIN
   V_CDCONTADOR := 'FORMPGTO';

   BEGIN
      SELECT NRSEQUENCIAL
      INTO V_NRSEQUENCIAL
      FROM NOVOCODIGO
      WHERE CDCONTADOR = V_CDCONTADOR;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         NULL;
   END;

   SELECT MAX(CDFORMPGTO)
   INTO V_CDFORMPGTO
   FROM FORMPGTO;

   IF (V_NRSEQUENCIAL IS NULL) OR (SUBSTR(V_CDFORMPGTO,19,2) < V_CDFORMPGTO) THEN
      IF V_NRSEQUENCIAL IS NULL THEN
         INSERT INTO NOVOCODIGO
         (CDCONTADOR, NRSEQUENCIAL)
         VALUES
         (V_CDCONTADOR, LPAD(V_CDFORMPGTO,20,'0'));

      ELSE
         UPDATE NOVOCODIGO
         SET NRSEQUENCIAL = LPAD(V_CDFORMPGTO,20,'0')
         WHERE CDCONTADOR = V_CDCONTADOR;
      END IF;
   END IF;

   FOR CP IN (
   SELECT MAX(CODCPG) AS CODCPG,
          MAX(DESCPG) AS DESCPG,
          NUMPAR, DIASEMI, PCEMISSAO,
          DECODE(CP_PRAZO,'DDE','****','DDD','****','MS','FM',CP_PRAZO) AS CP_PRAZO
   FROM USR_TCONDPAGTO
   WHERE PRIMEIRA = 'S'
     AND DIASEMI <> 0
     AND CP_PRAZO <> 'TD'
     AND NOT EXISTS (SELECT CDFORMPGTO
                     FROM FORMPGTO F
                     WHERE F.USR_CODCPG = CODCPG)
   GROUP BY NUMPAR, DIASEMI, PCEMISSAO, DECODE(CP_PRAZO,'DDE','****','DDD','****','MS','FM',CP_PRAZO))
   LOOP

      IF (CP.CP_PRAZO = 'FS') OR (CP.DESCPG LIKE '%FORA%SEMANA%') THEN
         V_IDTPPERIODO := 'S';
      ELSE
         V_IDTPPERIODO := 'M';
      END IF;

      IF (SUBSTR(V_CDFORMPGTO,2,1) <= '9' AND SUBSTR(V_CDFORMPGTO,2,1) >= '0') AND (V_CDFORMPGTO <> '99') THEN
         NOVO_CODIGO('FORMPGTO',V_CDFORMPGTO,1);
         V_CDFORMPGTO := SUBSTR(V_CDFORMPGTO,19,2);
      ELSE
         IF V_CDFORMPGTO = '99' THEN
            V_CDFORMPGTO := '0@';

            UPDATE NOVOCODIGO
            SET NRSEQUENCIAL = LPAD(V_CDFORMPGTO,20,'0')
            WHERE CDCONTADOR = V_CDCONTADOR;
         END IF;

         NOVO_CODIGO_ALFA('FORMPGTO',V_CDFORMPGTO,1);
         V_CDFORMPGTO := SUBSTR(V_CDFORMPGTO,19,2);

         SELECT MAX(CDFORMPGTO)
         INTO V_CDAUXILIAR
         FROM FORMPGTO
         WHERE CDFORMPGTO = V_CDFORMPGTO;

         WHILE V_CDAUXILIAR IS NOT NULL LOOP
            NOVO_CODIGO_ALFA('FORMPGTO',V_CDFORMPGTO,1);
            V_CDFORMPGTO := SUBSTR(V_CDFORMPGTO,19,2);

            SELECT MAX(CDFORMPGTO)
            INTO V_CDAUXILIAR
            FROM FORMPGTO
            WHERE CDFORMPGTO = V_CDFORMPGTO;
         END LOOP;
      END IF;

      INSERT INTO FORMPGTO
      (CDFORMPGTO, NMFORMPGTO, IDTIPODTPGTO, IDTPPERIODO, USR_CODCPG)
      VALUES
      (V_CDFORMPGTO, SUBSTR(CP.DESCPG,1,30), 'ENIN', V_IDTPPERIODO, CP.CODCPG);


      IF (CP.CP_PRAZO = 'FS') OR (CP.DESCPG LIKE '%FORA%SEMANA%') OR (CP.DESCPG LIKE '%FS%') THEN
         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '01', '07',
          '02', '1', '0');
      ELSIF (CP.CP_PRAZO = 'FM') OR (CP.DESCPG LIKE '%FORA%MES%') OR (CP.DESCPG LIKE '%FM%') THEN
         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '01', '31',
          '01', '1', '0');
      ELSIF (CP.CP_PRAZO = 'FD') OR (CP.DESCPG LIKE '%FORA%DEZENA%')  OR (CP.DESCPG LIKE '%FD%') THEN
         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '01', '10',
          '11', '0', '0');

         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '11', '20',
          '21', '0', '0');

         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '21', '31',
          '01', '1', '0');
      ELSIF (CP.CP_PRAZO = 'FQ') OR (CP.DESCPG LIKE '%FORA%QUINZENA%')  OR (CP.DESCPG LIKE '%FQ%') THEN
         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '01', '15',
          '16', '0', '0');

         INSERT INTO DIAPGTO
         (CDFORMPGTO, NRDIAINIPGTO, NRDIAFINPGTO,
          NRDIAPGTOVAR, NRMESFORMPGT, NRNUMMESPGTO)
         VALUES
         (V_CDFORMPGTO, '16', '31',
          '01', '1', '0');
      END IF;
   END LOOP;
END;
/


-- VERSAO --
BEGIN ATUVEROBJ('USR_FORMA_PAGAMENTO','5.01.003'); END;
/

