/*PROCEDURES que aliementam dados do Dashboard Corporativo tabela JOB_FINAN */

create or replace PROCEDURE JOB_FINAN_GIRAFFAS
AS
	V_NRSEQJOB  VARCHAR2(20);
	
	P_IDDASHBOARD VARCHAR2(1);
	/* VARIAVEL QUE VERIFICA QUAL DASHBOARD IRA SER CONSIDERADO PADRAO OU CORPORATIVO */
BEGIN


	/* VERIFICA SE O PARAMETRO ESTA PARA DASHBOARD PADRAO OU CORPORATIVO*/
	BEGIN
		P_IDDASHBOARD := 'P';
		SELECT IDDASHBOARD
		  INTO P_IDDASHBOARD
		  FROM PARAMGERAL;		 
		EXCEPTION
			WHEN NO_DATA_FOUND THEN
			P_IDDASHBOARD := 'P';
	END;

	IF (P_IDDASHBOARD = 'C') THEN


		-- Gera novo código - SEQJOBFINAN  
		NOVO_CODIGO_NT('SEQJOBFINAN', V_NRSEQJOB);
		V_NRSEQJOB := SUBSTR(V_NRSEQJOB,11,10);

		-- Exclui registros anteriores cadastrados
		DELETE FROM JOB_FINAN;

		/* 
		 * **** SALDO CONTA CORRENTE ****
		 * Busca saldo das contas correntes das empresas
		 */
		FOR C_SALDOC IN (   
			SELECT SUM(NVL(SALDO_CONTACORR(CDREDUCONT, TRUNC(SYSDATE)),0)) AS VRSALDO, C.CDEMPRESA, B.CDBANCO||' - '||B.NMBANCO AS DESCRICAO
			  FROM CONTCORR C, BANCO B
			 WHERE B.CDBANCO = C.CDBANCO
		  GROUP BY C.CDEMPRESA,  B.CDBANCO||' - '||B.NMBANCO
		)LOOP
			-- Cadastra saldo da contas corrente
			INSERT INTO JOB_FINAN
				(NRSEQJOB, CDEMPRESA, CDFILIAL, VRSALDOCCOR, IDINFJOB, DSBANCO)
			VALUES
				(TO_NUMBER(V_NRSEQJOB),C_SALDOC.CDEMPRESA, NULL, C_SALDOC.VRSALDO, 'S', C_SALDOC.DESCRICAO);
		END LOOP;

		/* 
		 * **** BOLETOS A PAGAR ****
		 * Busca valores a serem pagos e já vencidos na data atual, além dos vencimentos em 7, 15 e 30 dias.
		 */
		FOR C_C_PAG IN (
			SELECT SUM(DECODE(DTVENBOLPAG,TRUNC(SYSDATE), BP.VRFINALBPAG,0)) AS VRVENCDIA,
				   SUM(CASE WHEN ABS(TRUNC(SYSDATE) - DTVENBOLPAG) < 7  THEN BP.VRFINALBPAG ELSE 0 END) AS VRVENC7DIAS,
				   SUM(CASE WHEN ABS(TRUNC(SYSDATE) - DTVENBOLPAG) < 15 THEN BP.VRFINALBPAG ELSE 0 END) AS VRVENC15DIAS,
				   SUM(CASE WHEN ABS(TRUNC(SYSDATE) - DTVENBOLPAG) < 30 THEN BP.VRFINALBPAG ELSE 0 END) AS VRVENC30DIAS,
				   0 AS VRTOTALVENCIDO,
				   BP.CDFILIAL,
				   BP.CDEMPRESA
			  FROM BOLETPAGAR BP
			 WHERE BP.DTVENBOLPAG >= TRUNC(SYSDATE)
			   AND BP.IDSTATBPAG <> 'B'
			   AND EXISTS(SELECT 1 FROM ENDEFORN EN WHERE EN.CDFORNECED = BP.CDFORNECED AND IDTPENDEFORN = 'P')
			   AND EXISTS(SELECT 1 FROM CONTAPAGAR CR WHERE CR.CDFILIBPAG = BP.CDFILIAL AND CR.NRLANBOLPAG = BP.NRLANBOLPAG)
		  GROUP BY BP.CDFILIAL, BP.CDEMPRESA 
		  UNION 
			SELECT 0 AS VRVENCDIA,
				   0 AS VRVENC7DIAS,
				   0 AS VRVENC15DIAS,
				   0 AS VRVENC30DIAS,
				   SUM(BP.VRFINALBPAG),
				   BP.CDFILIAL,
				   BP.CDEMPRESA
			  FROM BOLETPAGAR BP
			 WHERE BP.DTVENBOLPAG < TRUNC(SYSDATE)
			   AND BP.IDSTATBPAG <> 'B'
			   AND EXISTS(SELECT 1 FROM ENDEFORN EN WHERE EN.CDFORNECED = BP.CDFORNECED AND IDTPENDEFORN = 'P')
			   AND EXISTS(SELECT 1 FROM CONTAPAGAR CR WHERE CR.CDFILIBPAG = BP.CDFILIAL AND CR.NRLANBOLPAG = BP.NRLANBOLPAG)
		  GROUP BY BP.CDFILIAL, BP.CDEMPRESA
		)LOOP
			-- Cadastra valores dos boletos a pagar
			INSERT INTO JOB_FINAN
				(NRSEQJOB, CDEMPRESA, CDFILIAL, VRVENCDIA, VRVENC7DIAS, VRVENC15DIAS, VRVENC30DIAS,  VRTOTALVENCIDO, IDINFJOB)
			VALUES
				(TO_NUMBER(V_NRSEQJOB),C_C_PAG.CDEMPRESA, C_C_PAG.CDFILIAL, C_C_PAG.VRVENCDIA,  C_C_PAG.VRVENC7DIAS, C_C_PAG.VRVENC15DIAS, C_C_PAG.VRVENC30DIAS,  C_C_PAG.VRTOTALVENCIDO, 'P');
		END LOOP;

		/* 
		 * **** BOLETOS A RECEBER ****
		 * Busca valores a serem recebidos e já vencidos na data atual, além dos vencimentos em 7, 15 e 30 dias.
		 */
		FOR C_C_REC IN (
			SELECT SUM(DECODE(trunc(DTVENBOLREC),TRUNC(SYSDATE), BR.VRFINALBREC,0)) AS VRVENCDIA,
				   SUM(CASE WHEN ABS(TRUNC(SYSDATE) - DTVENBOLREC) < 7  THEN BR.VRFINALBREC ELSE 0 END) AS VRVENC7DIAS,
				   SUM(CASE WHEN ABS(TRUNC(SYSDATE) - DTVENBOLREC) < 15 THEN BR.VRFINALBREC ELSE 0 END) AS VRVENC15DIAS,
				   SUM(CASE WHEN ABS(TRUNC(SYSDATE) - DTVENBOLREC) < 30 THEN BR.VRFINALBREC ELSE 0 END) AS VRVENC30DIAS,
				   0 AS VRTOTALVENCIDO,
				   BR.CDFILIAL,
				   BR.CDEMPRESA
			  FROM BOLETRECEBER BR
			 WHERE BR.DTVENBOLREC >= TRUNC(SYSDATE)
			   AND BR.IDSTATBREC <> 'B'
			   AND EXISTS(SELECT 1 FROM ENDECLIE EN WHERE EN.CDCLIENTE = BR.CDCLIENTE AND IDTPENDECLIE = 'P')
			   AND EXISTS(SELECT 1 FROM CONTARECEBER CR WHERE CR.CDFILIBOLREC = BR.CDFILIAL AND CR.NRLANBOLREC = BR.NRLANBOLREC)
		  GROUP BY BR.CDFILIAL, BR.CDEMPRESA
		  UNION
			SELECT 0 AS VRVENCDIA,
				   0 AS VRVENC7DIAS,
				   0 AS VRVENC15DIAS,
				   0 AS VRVENC30DIAS,
				   SUM(BR.VRFINALBREC),
				   BR.CDFILIAL,
				   BR.CDEMPRESA
			  FROM BOLETRECEBER BR
			  WHERE BR.DTVENBOLREC < TRUNC(SYSDATE)
				AND BR.IDSTATBREC <> 'B'
				AND EXISTS(SELECT 1 FROM ENDECLIE EN WHERE EN.CDCLIENTE = BR.CDCLIENTE AND IDTPENDECLIE = 'P')
			   AND EXISTS(SELECT 1 FROM CONTARECEBER CR WHERE CR.CDFILIBOLREC = BR.CDFILIAL AND CR.NRLANBOLREC = BR.NRLANBOLREC)
		  GROUP BY BR.CDFILIAL, BR.CDEMPRESA
		)LOOP
			-- Cadastra valores dos boletos a receber
			INSERT INTO JOB_FINAN
				(NRSEQJOB, CDEMPRESA, CDFILIAL, VRVENCDIA, VRVENC7DIAS, VRVENC15DIAS, VRVENC30DIAS,  VRTOTALVENCIDO, IDINFJOB)
			VALUES
				(TO_NUMBER(V_NRSEQJOB),C_C_REC.CDEMPRESA, C_C_REC.CDFILIAL, C_C_REC.VRVENCDIA,  C_C_REC.VRVENC7DIAS, C_C_REC.VRVENC15DIAS, C_C_REC.VRVENC30DIAS,  C_C_REC.VRTOTALVENCIDO, 'R');
		END LOOP;
	END IF;
END;