create or replace PROCEDURE TESTA_VENCIMENTO(
   P_CDFORMPGTO   IN VARCHAR,
   P_CNPJ         IN VARCHAR2,
   P_IDFORNCLIE   IN VARCHAR2,
   P_DATABASE     DATE,
   P_CDFILIAL    VARCHAR2,
   P_IDCONSCALVENNF VARCHAR2,
   P_IDCONSPRAZO    VARCHAR2 DEFAULT 'S')
IS
V_DTVENCIMENTO DATE;
V_DATABASE DATE;
V_IDCONSDIAUTIL VARCHAR2(02);
V_IDTPPERIODO VARCHAR2(02);
V_NMRAZSCOCIAL  VARCHAR2(200);
V_CNPJ           VARCHAR2(50);
BEGIN
  DELETE FROM VALIDA_VENC;
  V_DATABASE := P_DATABASE;
  V_DTVENCIMENTO := V_DATABASE;
IF P_IDCONSPRAZO = 'S' THEN 
    FOR C_VENC IN (SELECT FP.CDFORMPGTO, FO.NRINSJURFORN AS CNPJ, FO.NMRAZSOCFORN AS RAZAOSOCIAL,
                          NVL(NRDIASPGTOFO, 0) AS NRDIASPGTO, NVL(VRPERCPGTOFO, 100) VRPERCPGTO, 'FORNECEDOR' AS IDFORNCLIE , 
                          FP.IDCONSDIAUTIL, FP.IDTPPERIODO
                      FROM PRAZOPGTFORN P, FORNECEDOR FO, FORMPGTO FP
                     WHERE P.CDFORNECED = P_CNPJ
                       AND 'F' = P_IDFORNCLIE
                       AND FO.CDFORNECED = P.CDFORNECED
                       AND FP.CDFORMPGTO = P_CDFORMPGTO
                     UNION
                    SELECT FP.CDFORMPGTO, CL.NRINSJURCLIE AS CNPJ, CL.NMRAZSOCCLIE AS RAZAOSOCIAL, NVL(NRDIASPGTOCL, 0) AS NRDIASPGTO, NVL(VRPERCPGTOCL, 100) VRPERCPGTO,
                          'CLIENTE' AS IDFORNCLIE, FP.IDCONSDIAUTIL, FP.IDTPPERIODO
                      FROM PRAZOPGTCLIE P, CLIENTE CL, FORMPGTO FP
                     WHERE P.CDCLIENTE = P_CNPJ
                       AND 'C' = P_IDFORNCLIE
                       AND CL.CDCLIENTE = P.CDCLIENTE
                       AND FP.CDFORMPGTO = P_CDFORMPGTO) LOOP
       
       V_DATABASE := V_DTVENCIMENTO+ C_VENC.NRDIASPGTO;
       IF C_VENC.IDTPPERIODO = 'M' THEN
          V_DTVENCIMENTO := UTIL_CRIAVENCIMENTO.DIAMES(P_CDFORMPGTO,
                                                       V_DATABASE);
       ELSIF C_VENC.IDTPPERIODO = 'S' THEN
          V_DTVENCIMENTO := UTIL_CRIAVENCIMENTO.DIASEMANA(P_CDFORMPGTO,
                                                          V_DATABASE);
       ELSE
          V_DTVENCIMENTO := UTIL_CRIAVENCIMENTO.DIASEMANAMES(P_CDFORMPGTO,
                                                             V_DATABASE);
       END IF;
       IF C_VENC.IDCONSDIAUTIL = 'S' THEN
       
          V_DTVENCIMENTO := CONSISTE_FERI_BANC(P_CDFILIAL, V_DTVENCIMENTO, P_IDCONSCALVENNF);
       END IF;
       INSERT INTO VALIDA_VENC
               (CDFORMPGTO, CNPJ, RAZAOSOCIAL, NRDIASPGTO, VRPERCPGTO, 
                IDTPPERIODO, IDFORNCLIE ,DTVENC, IDCONSDIAUTIL)
       VALUES
           (C_VENC.CDFORMPGTO, C_VENC.CNPJ, C_VENC.RAZAOSOCIAL, C_VENC.NRDIASPGTO, C_VENC.VRPERCPGTO, 
                C_VENC.IDTPPERIODO, C_VENC.IDFORNCLIE ,V_DTVENCIMENTO, C_VENC.IDCONSDIAUTIL);
       
    END LOOP;
ELSE
   SELECT IDCONSDIAUTIL, IDTPPERIODO
     INTO V_IDCONSDIAUTIL, V_IDTPPERIODO
     FROM FORMPGTO
    WHERE CDFORMPGTO = P_CDFORMPGTO;
    SELECT CNPJ, RAZAOSOC
      INTO V_CNPJ, V_NMRAZSCOCIAL
      FROM (SELECT NRINSJURFORN AS CNPJ, NMRAZSOCFORN AS RAZAOSOC
              FROM FORNECEDOR
             WHERE CDFORNECED = P_CNPJ
               AND 'F' = P_IDFORNCLIE
             UNION
            SELECT NRINSJURCLIE AS CNPJ, NMRAZSOCCLIE AS RAZAOSOC
              FROM CLIENTE
             WHERE CDCLIENTE = P_CNPJ
               AND 'C' = P_IDFORNCLIE);
    
       V_DATABASE := V_DTVENCIMENTO;
       IF V_IDTPPERIODO = 'M' THEN
          V_DTVENCIMENTO := UTIL_CRIAVENCIMENTO.DIAMES(P_CDFORMPGTO,
                                                       V_DATABASE);
       ELSIF V_IDTPPERIODO = 'S' THEN
          V_DTVENCIMENTO := UTIL_CRIAVENCIMENTO.DIASEMANA(P_CDFORMPGTO,
                                                          V_DATABASE);
       ELSE
          V_DTVENCIMENTO := UTIL_CRIAVENCIMENTO.DIASEMANAMES(P_CDFORMPGTO,
                                                             V_DATABASE);
       END IF;
       IF V_IDCONSDIAUTIL = 'S' THEN       
          V_DTVENCIMENTO := CONSISTE_FERI_BANC(P_CDFILIAL, V_DTVENCIMENTO, P_IDCONSCALVENNF);
       END IF;
       INSERT INTO VALIDA_VENC
               (CDFORMPGTO, CNPJ, RAZAOSOCIAL, NRDIASPGTO, VRPERCPGTO, 
                IDTPPERIODO, IDFORNCLIE ,DTVENC, IDCONSDIAUTIL)
       VALUES
           (P_CDFORMPGTO, V_CNPJ, V_NMRAZSCOCIAL, 0, 100, 
                V_IDTPPERIODO, DECODE(P_IDFORNCLIE,'F', 'FORNECEDOR','CLIENTE') ,V_DTVENCIMENTO, V_IDCONSDIAUTIL);
END IF;
COMMIT;
END;
/