CREATE OR REPLACE FUNCTION CONTCTBL_IMPOSTO (
   P_CDIMPOSTO    IN IMPOSTO.CDIMPOSTO%TYPE,
   P_IDTIPOCONT   IN VARCHAR2,
   P_IDENTRSAIDOP IN TIPOOPERAC.IDENTRSAIDOP%TYPE,
   P_CDTIPOOPER   IN TIPOOPERAC.CDTIPOOPER%TYPE,
   P_IDTPIJURNF   IN NOTAFISCAL.IDTPIJURNF%TYPE,
   P_VRALIQUOTA   IN NUMBER DEFAULT 0,
   P_BUSCAFECP    IN VARCHAR2 DEFAULT 'N')
   RETURN VARCHAR
AS
   V_CDCCTBLDESP     CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBLPASS     CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBLATIV     CONTCTBL.CDCONTCTBL%TYPE;

   V_CDCCTBDESPIF    CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBPASSIF    CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBATIVIF    CONTCTBL.CDCONTCTBL%TYPE;

   V_CDCCTBDESPTO    CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBPASSTO    CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBATIVTO    CONTCTBL.CDCONTCTBL%TYPE;

   V_IDIMPORECUP     IMPOSTO.IDIMPORECUP%TYPE;
   V_CDCONTCTBLIMP   CONTCTBL.CDCONTCTBL%TYPE;
   
   V_CDCCTBLDESPALIQ CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBLPASSALIQ CONTCTBL.CDCONTCTBL%TYPE;
   V_CDCCTBLATIVALIQ CONTCTBL.CDCONTCTBL%TYPE;
   
   V_DSRETURNAUX     VARCHAR2(20);

BEGIN
    BEGIN
      SELECT CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBLDESPFECP ELSE CDCCTBLDESP END AS CDCCTBLDESP, 
             CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBLPASSFECP ELSE CDCCTBLPASS END AS CDCCTBLPASS, 
             CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBLATIVFECP ELSE CDCCTBLATIV END AS CDCCTBLATIV,
             IDIMPORECUP
        INTO V_CDCCTBLDESP, 
             V_CDCCTBLPASS, 
             V_CDCCTBLATIV,
             V_IDIMPORECUP
        FROM IMPOSTO
       WHERE CDIMPOSTO = P_CDIMPOSTO;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_CDCCTBLDESP := NULL; 
             V_CDCCTBLPASS := NULL; 
             V_CDCCTBLATIV := NULL;
             V_IDIMPORECUP := NULL;
    END;

    BEGIN
      SELECT CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBDESPIFFECP ELSE CDCCTBDESPIF END AS CDCCTBDESPIF, 
             CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBPASSIFFECP ELSE CDCCTBPASSIF END AS CDCCTBPASSIF, 
             CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBATIVIFFECP ELSE CDCCTBATIVIF END AS CDCCTBATIVIF
        INTO V_CDCCTBDESPIF, 
             V_CDCCTBPASSIF,
             V_CDCCTBATIVIF
        FROM CCTBIMPFORN
       WHERE CDIMPOSTO = P_CDIMPOSTO
         AND IDTPIJURIMPO = P_IDTPIJURNF;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_CDCCTBDESPIF := NULL; 
             V_CDCCTBPASSIF := NULL; 
             V_CDCCTBATIVIF := NULL;
    END;

    BEGIN
      SELECT CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBDESPTOFECP ELSE CDCCTBDESPTO END AS CDCCTBDESPTO, 
             CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBPASSTOFECP ELSE CDCCTBPASSTO END AS CDCCTBPASSTO,
             CASE P_BUSCAFECP WHEN 'S' THEN CDCCTBATIVTOFECP ELSE CDCCTBATIVTO END AS CDCCTBATIVTO
        INTO V_CDCCTBDESPTO,
             V_CDCCTBPASSTO,
             V_CDCCTBATIVTO
        FROM CTBIMPTPOPER
       WHERE CDIMPOSTO = P_CDIMPOSTO
         AND IDENTRSAIDOP = P_IDENTRSAIDOP
         AND CDTIPOOPER = P_CDTIPOOPER;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_CDCCTBDESPTO := NULL; 
             V_CDCCTBPASSTO := NULL; 
             V_CDCCTBATIVTO := NULL;
    END;

   /*VERIFICA AS CONTAS CONTABEIS POR IMPOSTO E ALIQUOTA*/
    BEGIN
      SELECT CASE P_BUSCAFECP WHEN 'S' THEN I.CDCCTBLDESPFECP ELSE I.CDCCTBLDESP END AS CDCCTBLDESP, 
             CASE P_BUSCAFECP WHEN 'S' THEN I.CDCCTBLPASSFECP ELSE I.CDCCTBLPASS END AS CDCCTBLPASS, 
             CASE P_BUSCAFECP WHEN 'S' THEN I.CDCCTBLATIVFECP ELSE I.CDCCTBLATIV END AS CDCCTBLATIV
        INTO V_CDCCTBLDESPALIQ, 
             V_CDCCTBLPASSALIQ, 
             V_CDCCTBLATIVALIQ
        FROM IMPALIQCONTAB I, IMPOSTO IM
       WHERE I.CDIMPOSTO = IM.CDIMPOSTO
         AND I.CDIMPOSTO = P_CDIMPOSTO
         AND I.VRALIQUOTA = P_VRALIQUOTA;
   EXCEPTION
        WHEN NO_DATA_FOUND THEN
             V_CDCCTBLDESPALIQ := NULL;
             V_CDCCTBLPASSALIQ := NULL;
             V_CDCCTBLATIVALIQ := NULL;
    END;
   
	IF V_CDCCTBLATIVALIQ IS NOT NULL THEN
       V_CDCCTBLATIV :=  V_CDCCTBLATIVALIQ;
	ELSIF V_CDCCTBATIVTO IS NOT NULL THEN
       V_CDCCTBLATIV := V_CDCCTBATIVTO;
    ELSIF V_CDCCTBATIVIF IS NOT NULL THEN
       V_CDCCTBLATIV := V_CDCCTBATIVIF;
    END IF;

    IF V_CDCCTBLPASSALIQ IS NOT NULL THEN
       V_CDCCTBLPASS := V_CDCCTBLPASSALIQ;
	ELSIF V_CDCCTBPASSTO IS NOT NULL THEN
       V_CDCCTBLPASS := V_CDCCTBPASSTO;
    ELSIF V_CDCCTBPASSIF IS NOT NULL THEN
       V_CDCCTBLPASS := V_CDCCTBPASSIF;
    END IF;

    IF V_CDCCTBLDESPALIQ IS NOT NULL THEN
       V_CDCCTBLDESP := V_CDCCTBLDESPALIQ;
	ELSIF V_CDCCTBDESPTO IS NOT NULL THEN
       V_CDCCTBLDESP := V_CDCCTBDESPTO;
    ELSIF V_CDCCTBDESPIF IS NOT NULL THEN
       V_CDCCTBLDESP := V_CDCCTBDESPIF;
    END IF;

    IF V_IDIMPORECUP = 'S' THEN
       V_CDCONTCTBLIMP := V_CDCCTBLATIV;
    ELSE
       V_CDCONTCTBLIMP := V_CDCCTBLPASS;
    END IF;

    IF P_IDTIPOCONT = 'A' THEN
       V_DSRETURNAUX := V_CDCCTBLATIV;
    ELSIF P_IDTIPOCONT = 'P' THEN
       V_DSRETURNAUX := V_CDCCTBLPASS;
    ELSIF P_IDTIPOCONT = 'D' THEN
       V_DSRETURNAUX := V_CDCCTBLDESP;
    ELSIF P_IDTIPOCONT = 'E' THEN
       V_DSRETURNAUX := V_CDCONTCTBLIMP;
    END IF;

    RETURN(V_DSRETURNAUX);
END;
/