ALTER TABLE NOTAFISCAL
ADD USR_DTEXPOINTG DATE
/

CREATE OR REPLACE TRIGGER ATUALIZA_USR_DTEXPOINTG
BEFORE UPDATE ON NOTAFISCAL FOR EACH ROW
BEGIN
   IF :OLD.USR_DTEXPOINTG IS NOT NULL THEN
      :NEW.USR_DTEXPOINTG := NULL;
   END IF;
END;
/

CREATE OR REPLACE FUNCTION USR_DATAVENCIMENTO
   (P_NRPARCELA  IN INTEGER,
    P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE)
RETURN DATE
AS
   V_CONTADOR   INTEGER;
   V_DTAUXILIAR DATE;
BEGIN
   V_CONTADOR := 1;

   FOR C_CTPAGAR IN (SELECT DTATUAVENPAG    
                     FROM CONTAPAGAR
                     WHERE CDFILIAL   = P_CDFILIAL AND   
                           NRLANCTONF = P_NRLANCTONF
                     ORDER BY QTDIASVENPAG) LOOP

      IF V_CONTADOR = P_NRPARCELA THEN 
         V_CONTADOR   := V_CONTADOR + 1;
         V_DTAUXILIAR := C_CTPAGAR.DTATUAVENPAG;
      END IF;
   END LOOP;
   RETURN(V_DTAUXILIAR);
END;
/

CREATE OR REPLACE FUNCTION USR_DSDADOSADIC
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE)
RETURN VARCHAR
AS
   V_AUXILIAR VARCHAR2(2000);
BEGIN
   V_AUXILIAR := 'X';
   FOR C_DADOSADIC IN (SELECT DSDADOADIC
                       FROM DADOSADIC
                       WHERE CDFILIAL   = P_CDFILIAL AND
                             NRLANCTONF = P_NRLANCTONF
                       ORDER BY NRDADOADIC) LOOP
      IF V_AUXILIAR = 'X' THEN
         V_AUXILIAR := C_DADOSADIC.DSDADOADIC;
      ELSE
         V_AUXILIAR := CHR(10) || V_AUXILIAR || C_DADOSADIC.DSDADOADIC;
      END IF;
   END LOOP;
   RETURN(V_AUXILIAR);
END;
/

CREATE OR REPLACE FUNCTION USR_CONTCTBL_PROD
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_CDPRODUTO  IN PRODUTO.CDPRODUTO%TYPE,
    P_CDARVPROD  IN PRODUTO.CDARVPROD%TYPE)
RETURN VARCHAR
AS
   V_IDENTRSAIDOP VARCHAR2(01);
   V_CDTIPOOPER   VARCHAR2(02);
   V_CDFILIAL     VARCHAR2(04);
   V_CDCONTCTBL   VARCHAR2(20);
   V_CDARVPROD    VARCHAR2(20);
BEGIN
   SELECT MAX(IDENTRSAIDOP), MAX(CDTIPOOPER), 
          MAX(DECODE(IDENTRSAIDOP, 'E', CDFILIRECE, CDFILIEMIT))
   INTO V_IDENTRSAIDOP, V_CDTIPOOPER, V_CDFILIAL
   FROM NOTAFISCAL
   WHERE CDFILIAL   = P_CDFILIAL AND 
         NRLANCTONF = P_NRLANCTONF;

   SELECT MAX(CDCONTPROD) 
   INTO V_CDCONTCTBL
   FROM TIPOOPERAC 
   WHERE IDENTRSAIDOP = V_IDENTRSAIDOP AND 
         CDTIPOOPER   = V_CDTIPOOPER;

   IF V_CDCONTCTBL IS NULL THEN 
      SELECT MAX(DECODE(V_IDENTRSAIDOP, 'E', CDCTBLENTRPF, CDCTBLSAIDPF)) 
      INTO V_CDCONTCTBL
      FROM PRODFILI
      WHERE CDFILIAL  = V_CDFILIAL AND 
            CDPRODUTO = P_CDPRODUTO;

      IF V_CDCONTCTBL IS NULL THEN 	
         SELECT MAX(CDARVPROD)
         INTO V_CDARVPROD
         FROM PRODUTO
         WHERE P_CDARVPROD LIKE CDARVPROD || '%' AND 
               DECODE(V_IDENTRSAIDOP, 'E', CDCTBLENTRPR, CDCTBLSAIDPR) IS NOT NULL;

         SELECT MAX(DECODE(V_IDENTRSAIDOP, 'E', CDCTBLENTRPR, CDCTBLSAIDPR))
         INTO V_CDCONTCTBL
         FROM PRODUTO 
         WHERE CDARVPROD = V_CDARVPROD;
      END IF;
   END IF;
   RETURN(V_CDCONTCTBL);
END;
/

CREATE OR REPLACE FUNCTION USR_CONTCTBL_IMPOSTO
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_CDIMPOSTO  IN IMPOSTO.CDIMPOSTO%TYPE)
RETURN VARCHAR
AS
   V_IDENTRSAIDOP VARCHAR2(01);
   V_CDTIPOOPER   VARCHAR2(02);
   V_CDCONTCTBL   VARCHAR2(20);
BEGIN
   SELECT MAX(IDENTRSAIDOP), MAX(CDTIPOOPER)
   INTO V_IDENTRSAIDOP, V_CDTIPOOPER
   FROM NOTAFISCAL
   WHERE CDFILIAL   = P_CDFILIAL AND 
         NRLANCTONF = P_NRLANCTONF;

   SELECT MAX(DECODE(V_IDENTRSAIDOP, 'E', CDCCTBATIVTO, CDCCTBPASSTO))
   INTO V_CDCONTCTBL
   FROM CTBIMPTPOPER 
   WHERE IDENTRSAIDOP = V_IDENTRSAIDOP AND 
         CDTIPOOPER   = V_CDTIPOOPER   AND
         CDIMPOSTO    = P_CDIMPOSTO;

   IF V_CDCONTCTBL IS NULL THEN 
      SELECT MAX(DECODE(V_IDENTRSAIDOP, 'E', CDCCTBLATIV, CDCCTBLPASS))
      INTO V_CDCONTCTBL
      FROM IMPOSTO 
      WHERE CDIMPOSTO    = P_CDIMPOSTO;
   END IF;
   RETURN(V_CDCONTCTBL);
END;
/

CREATE OR REPLACE FUNCTION USR_CONTCTBL_DOCU
   (P_CDFILIAL   IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF IN NOTAFISCAL.NRLANCTONF%TYPE)
RETURN VARCHAR
AS
   V_IDENTRSAIDOP VARCHAR2(01);
   V_CDTIPOOPER   VARCHAR2(02);
   V_CDFILIAL     VARCHAR2(04);
   V_CDEMPRESA    VARCHAR2(02);
   V_CDFORNCLIE   VARCHAR2(21);
   V_CDCONTCTBL   VARCHAR2(20);
BEGIN
   SELECT MAX(IDENTRSAIDOP), MAX(CDTIPOOPER), 
          MAX(DECODE(IDENTRSAIDOP, 'E', CDFORNECED, CDCLIENTE)),
          MAX(DECODE(IDENTRSAIDOP, 'E', CDFILIRECE, CDFILIEMIT)),
          MAX(CDEMPRESA)
   INTO V_IDENTRSAIDOP, V_CDTIPOOPER, V_CDFORNCLIE, V_CDFILIAL, V_CDEMPRESA
   FROM NOTAFISCAL
   WHERE CDFILIAL   = P_CDFILIAL AND 
         NRLANCTONF = P_NRLANCTONF;

   IF V_IDENTRSAIDOP = 'E' THEN
      SELECT MAX(CDCONTCTBL)
      INTO V_CDCONTCTBL
      FROM FORNECEDOR
      WHERE CDFORNECED = V_CDFORNCLIE;
   ELSE
      SELECT MAX(CDCONTCTBL)
      INTO V_CDCONTCTBL
      FROM CLIENTE
      WHERE CDCLIENTE = V_CDFORNCLIE;
   END IF;

   IF V_CDCONTCTBL IS NULL THEN 
      SELECT MAX(CDCONTFORCLI) 
      INTO V_CDCONTCTBL
      FROM TIPOOPERAC 
      WHERE IDENTRSAIDOP = V_IDENTRSAIDOP AND 
            CDTIPOOPER   = V_CDTIPOOPER;

      IF V_CDCONTCTBL IS NULL THEN 
         SELECT MAX(DECODE(V_IDENTRSAIDOP, 'E', CDCONFORNFI, CDCONCLIEFI))
         INTO V_CDCONTCTBL
         FROM PARAMFILIAL
         WHERE CDFILIAL = V_CDFILIAL;

         IF V_CDCONTCTBL IS NULL THEN 
            SELECT MAX(DECODE(V_IDENTRSAIDOP, 'E', CDCONFORNEM, CDCONCLIEEM))
            INTO V_CDCONTCTBL
            FROM PARAMEMPRESA
            WHERE CDEMPRESA = V_CDEMPRESA;
         END IF;
      END IF;
   END IF;

   RETURN(V_CDCONTCTBL);
END;
/

CREATE OR REPLACE FUNCTION USR_CONCEPTO
   (P_IDTIPOCONC  IN NOTAFISCAL.IDSTATUS%TYPE,
    P_CDFILIAL    IN NOTAFISCAL.CDFILIAL%TYPE,
    P_NRLANCTONF  IN NOTAFISCAL.NRLANCTONF%TYPE,
    P_NRSEQITPROD IN ITEMPRODUTO.NRSEQITPROD%TYPE,
    P_CDIMPOSTO   IN IMPOSTO.CDIMPOSTO%TYPE,
    P_VRALIQNF    IN IMPOSTONF.VRALIQNF%TYPE DEFAULT NULL)
RETURN VARCHAR
AS
   V_AUXILIAR   VARCHAR2(10);
   V_CDOFICIMPO VARCHAR2(10);
BEGIN
   IF P_IDTIPOCONC = 'A' THEN
      SELECT MAX(CDIMPOSTO)
      INTO V_AUXILIAR
      FROM ITEMPROIMPOS
      WHERE CDFILIAL    = P_CDFILIAL    AND
            NRLANCTONF  = P_NRLANCTONF  AND
            NRSEQITPROD = P_NRSEQITPROD AND
            VRIMPOSTOIT > 0;
      IF V_AUXILIAR IS NULL THEN
         V_AUXILIAR := 'NOGRAV';
      ELSE
         V_AUXILIAR := 'GRAV';
      END IF;
      RETURN(V_AUXILIAR);
   ELSIF P_IDTIPOCONC = 'I' THEN
      SELECT MAX(CDOFICIMPO)
      INTO V_CDOFICIMPO
      FROM IMPOSTO
      WHERE CDIMPOSTO = P_CDIMPOSTO;
      V_CDOFICIMPO := REPLACE(V_CDOFICIMPO, '@', SUBSTR(LTRIM(REPLACE(TO_CHAR(P_VRALIQNF,'99.999'),'.','')),1,2));
      RETURN(V_CDOFICIMPO||V_AUXILIAR);
   END IF;
END;
/
