create or replace
procedure ETL_FISCAL(P_DTINI IN DATE, P_DTFINA IN DATE)
IS
V_DTINI DATE;
V_DTFIN DATE;
BEGIN

   V_DTINI :=  TO_DATE(TO_CHAR(P_DTINI,'DD/MM/YYYY'),'DD/MM/YYYY');
   V_DTFIN :=  TO_DATE(TO_CHAR(P_DTFINA,'DD/MM/YYYY'),'DD/MM/YYYY');

   FOR C_DATA IN(SELECT TO_CHAR(DTMES,'YYYY') CDANOBI,
                        TO_CHAR(DTMES,'MM') CDMESBI,
                        DTMES INI_DT,
                        LAST_DAY(DTMES)FIM_DT
                   FROM DATA
                  WHERE DTDATA BETWEEN V_DTINI AND V_DTFIN
                  GROUP BY DTMES
                  ORDER BY DTMES) LOOP

		DELETE DM_FISCAL
		 WHERE DM_FISCAL.CDANOBI = C_DATA.CDANOBI
		   AND DM_FISCAL.CDMESBI = C_DATA.CDMESBI;
		COMMIT;

		INSERT INTO DM_FISCAL(CDANOBI,
		                      CDMESBI,
							  CDEMPRESA,
							  SGIMPOSTO,
                DSESFERA,
							  CDINSCESTA,
							  CDCST,
							  CDCFOP,
							  DSALIQUOTA,
							  VRCONTABIL,
							  VRIMPOSTO,
							  VRBASECALCIT,
							  VRISENTO,
							  DSNATUOPERAC)
	    SELECT CDANO,
               CDMES,
               CDEMPRESA,
               SGIMPOSTO,
               (CASE NVL(IDORGARRECAD,'F') WHEN 'F' THEN 'FEDERAL'
                                           WHEN 'E' THEN 'ESTADUAL'
                                           ELSE 'MUNICIPAL' END) IDORGARRECAD,
               NVL(CDINSCESTA,'N/INFORMADO') CDINSCESTA,
               NVL(CDSITUTRIBITNFE,'N/INFORMADO') CDSITUTRIBITNFE,
               DECODE(CDFISCOPERIT,NULL,'N/INFORMADO',(SUBSTR(CDFISCOPERIT,1,1)||'.'||SUBSTR(CDFISCOPERIT,2,3)))CFOP,
               NVL(DECODE(SUBSTR(LTRIM(TO_CHAR(ROUND(VRALIQITEM, 2), '9999999999999.99')), 1, 1), '.', '0'||LTRIM(TO_CHAR(ROUND(VRALIQITEM, 2), '9999999999999.99')), LTRIM(TO_CHAR(ROUND(VRALIQITEM, 2), '9999999999999.99'))),'N/INFORMADO') VRALIQITEM,
               NVL(VRCONTABIL,0.0) VRCONTABIL,
               NVL(VRITIMPOSTO,0.0) VRITIMPOSTO,
               NVL(VRBASECALCIT,0.0) VRBASECALCIT,
               NVL(VRISENTO,0.0) VRISENTO,
               DECODE(IDENTRSAIDOP,'E','Entrada',DECODE(IDENTRSAIDOP,'S', 'Saida','N/INFORMADO')) IDENTRSAIDOP
          FROM
             (SELECT TO_CHAR(NOTA.DTINIPERIODO,'YYYY') CDANO,
                     TO_CHAR(NOTA.DTINIPERIODO,'MM') CDMES,
                     NOTA.CDEMPRESA,
                     IP.SGIMPOSTO,
                     IP.IDORGARRECAD,
                     NOTA.CDINSCESTA,
                     NOTA.CDINSCMUNI,
                     IT.CDSITUTRIBITNFE,
                     IT.CDFISCOPERIT,
                     DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0.0)  AS VRALIQITEM,
                     SUM(ROUND(IT.VRCONTITIMP,2))                                AS VRCONTABIL,
                     SUM(ROUND(DECODE(IT.CDFISCALIT,'2',IT.VRCONTITIMP,0.0),2))  AS VRISENTO,
                     SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRIMPOSTOIT,0.0),2))  AS VRITIMPOSTO,
                     SUM(ROUND(DECODE(IT.CDFISCALIT,'1',IT.VRBASECALCIT,0.0),2)) AS VRBASECALCIT,
                     NOTA.INSCRESTAD,
                     NOTA.IDENTRSAIDOP
                FROM ITEMPROIMPOS IT, IMPOSTO IP,
                    (SELECT DISTINCT I.CDFILIAL,
                                     M.DTINIPERIODO,
                                     M.CDEMPRESA,
                                     I.NRLANCTONF,
                                     I.CDIMPOSTO,
                                     N.IDENTRSAIDOP,
                                     N.CDINSCESTA,
                                     M.CDINSCMUNI,
                                     N.CDINSCESTA AS INSCRESTAD
                                FROM NOTAFISCAL N, IMPOSTONF I, MAPAAPURAC M, IMPOSTONFE IM
                               WHERE M.CDEMPRESA    = N.CDEMPRESA
                                 AND ((M.NRSEQUMAPAPU = I.NRSEQUMAPAPU) OR (M.NRSEQUMAPAPU   = I.NRSEQMAPOIMP))
                                 AND TRUNC(M.DTINIPERIODO) >= C_DATA.INI_DT
                                 AND TRUNC(M.DTFIMPERIODO) <= C_DATA.FIM_DT
                                 AND N.CDFILIAL   = I.CDFILIAL
                                 AND N.NRLANCTONF = I.NRLANCTONF
                    ) NOTA
               WHERE IT.CDFILIAL = NOTA.CDFILIAL
                 AND IT.NRLANCTONF = NOTA.NRLANCTONF
                 AND IT.CDIMPOSTO  = NOTA.CDIMPOSTO
                 AND IT.CDIMPOSTO  = IP.CDIMPOSTO
               GROUP BY NOTA.DTINIPERIODO, NOTA.CDEMPRESA, IT.CDSITUTRIBITNFE, IT.CDFISCOPERIT, DECODE(IT.CDFISCALIT,'1',IT.VRALIQITEM,0.0), NOTA.CDINSCESTA,
			            NOTA.CDINSCMUNI, NOTA.INSCRESTAD, NOTA.IDENTRSAIDOP, IP.SGIMPOSTO, IP.IDORGARRECAD
            )
       ORDER BY CDANO, CDMES, CDEMPRESA, CDINSCESTA, CDSITUTRIBITNFE, CDFISCOPERIT;
	   COMMIT;
	  END LOOP;
END;
/
--BLOCO DE AUTO-COFIGURAÇÃO DE FATOS E DIMENSÕES
BEGIN
   --FATOS--
			CRIA_FATOS('DM_FISCAL', 'VRCONTABIL',    'Vr. Contábil',    'Valor Contábil',            'Mapa de Apuração');
			CRIA_FATOS('DM_FISCAL', 'VRIMPOSTO',     'Vr. Imposto',     'Valor do Imposto',          'Mapa de Apuração');
			CRIA_FATOS('DM_FISCAL', 'VRBASECALCIT',  'Vr. Base Calc.',  'Valor da Base de Cálculo',  'Mapa de Apuração');
			CRIA_FATOS('DM_FISCAL', 'VRISENTO',      'Vr. Isento',      'Valor de Isento',           'Mapa de Apuração');
			--DIMENSÕES
			CRIA_DIME('DM_FISCAL', 'CDEMPRESA',     'Empresa',        'EMPRESA', 'CDEMPRESA', 'NMEMPRESA', NULL);
			CRIA_DIME('DM_FISCAL', 'CDANOBI',       'Ano',             NULL,      NULL,        NULL,       NULL);
   CRIA_DIME('DM_FISCAL', 'CDMESBI',       'Mês',             NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'SGIMPOSTO',     'Imposto',         NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'DSESFERA',      'Esfera',          NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'CDINSCESTA',    'Insc. Estad.',    NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'CDCST',         'CST',             NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'CDCFOP',        'CFOP',            NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'DSALIQUOTA',    'Alíquota',        NULL,      NULL,        NULL,       NULL);
			CRIA_DIME('DM_FISCAL', 'DSNATUOPERAC',  'Operação',        NULL,      NULL,        NULL,       NULL);
END;
/
--INCLUSÃO NA AGENDA DO AGENDADOR DE EVENTOS
DECLARE
   V_CDSEQUENCIAL VARCHAR2(20) NULL;
BEGIN
    SELECT COUNT(*)
    INTO   V_CDSEQUENCIAL
    FROM   EVENTOAEV
    WHERE  NMOBJBDEVEN = 'ETL_FISCAL';

    IF V_CDSEQUENCIAL = 0 THEN     --> SÓ ADICIONA A PROCEDURE SE ELA NÃO EXISTIR AINDA NO CADASTRO
        BEGIN
            SELECT LPAD(NVL(MAX(LPAD(CDEVENTOAEV,10,'0'))+1,0),10,'0')
            INTO   V_CDSEQUENCIAL
            FROM   EVENTOAEV;

            INSERT INTO EVENTOAEV
            (CDEVENTOAEV, NMEVENTOAEV, IDTPEVENAEV, CDOPERINCL, DTINCLEVEN, NMOBJBDEVEN)
            VALUES
            (V_CDSEQUENCIAL,'Mapa Fiscal','STP',NULL,SYSDATE,'ETL_FISCAL');

            INSERT INTO PARAMEVENTO
            (CDEVENTOAEV,NMPARAMEVEN,IDDADPARAMEVEN,IDTIPPARAMEVEN,DSPARAMEVEN,IDPARAMNULL,VRTAMPARAM,NRSEQPARAMEVEN)
            VALUES
            (V_CDSEQUENCIAL,'P_DTINI','D','E','Data Inicial','N',0,'01');

            INSERT INTO PARAMEVENTO
            (CDEVENTOAEV,NMPARAMEVEN,IDDADPARAMEVEN,IDTIPPARAMEVEN,DSPARAMEVEN,IDPARAMNULL,VRTAMPARAM,NRSEQPARAMEVEN)
            VALUES
            (V_CDSEQUENCIAL,'P_DTFINA','D','E','Data Final','N',0,'02');

            COMMIT;
        EXCEPTION
           WHEN OTHERS THEN
              RAISE_APPLICATION_ERROR (-20001,'Falha no cadastro de eventos agendáveis.');
        END;
    END IF;
END;
/
-- VERSAO --
BEGIN ATUVEROBJ('ETL_FISCAL','5.65.xxx'); END;
/
