Perguntas Query Fluxo de Caixa

USE [DBP11]
GO
/****** Object:  StoredProcedure [dbo].[SP_MONTA_FLUXO]    Script Date: 12/16/2014 10:23:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_MONTA_FLUXO]
		AS
		BEGIN
			
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ZZFLFIN]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ZZFLFIN]		
						 
CREATE TABLE ZZFLFIN
( 
FILIAL  VARCHAR(4),
ANO VARCHAR(4),
MES VARCHAR(2),
DIA VARCHAR(2),
C_D VARCHAR(15),
TIPO VARCHAR(4),
NATUREZA  VARCHAR(200),
TITULO VARCHAR(260),
NF VARCHAR(400),
PEDIDO VARCHAR(200),
VALOR_CREDITO NUMERIC(15,2),
VALOR_DEBITO NUMERIC(15,2)
)

INSERT INTO ZZFLFIN
SELECT E1_FILIAL as Filial,
	   substring(E1_VENCTO,1,4) as Ano,
	   substring(E1_VENCTO,5,2) as Mes,
	   substring(E1_VENCTO,7,2) as Dia,
	   'Receber' AS C_D,
	   dbo.TRIM(E1_TIPO) AS Tipo,
	   ISNULL(dbo.TRIM(ED_CODIGO)+'-'+dbo.TRIM(ED_DESCRIC),'') AS Natureza,
	   dbo.TRIM(E1_PREFIXO)+'-'+dbo.TRIM(E1_NUM)+' Parc.: '+
	   CAST(CASE CAST(E1_PARCELA AS INTEGER)
		WHEN 0 THEN 1
		ELSE + CAST(E1_PARCELA AS INTEGER)
		END AS VARCHAR)+ '/'+
		CAST(ISNULL((SELECT COUNT(E1_NUM) 
			FROM SE1010 SE1TOT 
				WHERE SE1TOT.D_E_L_E_T_<>'*' 
						AND SE1TOT.E1_FILIAL=SE1.E1_FILIAL 
						AND SE1TOT.E1_TIPO=SE1.E1_TIPO
						AND SE1TOT.E1_PREFIXO=SE1.E1_PREFIXO
						AND SE1TOT.E1_NUM=SE1.E1_NUM
						AND SE1TOT.E1_CLIENTE=SE1.E1_CLIENTE
						AND SE1TOT.E1_LOJA=SE1.E1_LOJA),1) AS VARCHAR)+' - '+dbo.TRIM(E1_NOMCLI) +' de '+
							dbo.dateConv(E1_EMISSAO,1)
						 as Titulo,
						 ISNULL(dbo.TRIM(D2_COD) + ' - ' +dbo.TRIM(B1_DESC)+ ' - Qt: '+CAST(D2_QUANT AS VARCHAR)+ ' - Total:'+CAST(D2_VALBRUT AS VARCHAR),'') AS NF,
						 ISNULL(dbo.TRIM(C6_NUM)+ ' - '+dbo.TRIM(C6_ITEM),'') as Pedido,
						 ROUND(CASE E1_MOEDA
							WHEN 1 THEN
								ISNULL((D2_VALBRUT*((E1_VALOR*100)/(SELECT SUM(D2_VALBRUT) FROM SD2010 D2C WHERE 
								D2C.D2_DOC=SE1.E1_NUM AND D2C.D2_SERIE=SE1.E1_PREFIXO 
								AND D2C.D_E_L_E_T_<>'*' AND D2C.D2_CLIENTE=SE1.E1_CLIENTE 
								AND D2C.D2_LOJA=SE1.E1_LOJA))/100),E1_VALOR) 
							WHEN 2 THEN
								ISNULL((D2_VALBRUT*((E1_VALOR*100)/(SELECT SUM(D2_VALBRUT) FROM SD2010 D2C WHERE 
								D2C.D2_DOC=SE1.E1_NUM AND D2C.D2_SERIE=SE1.E1_PREFIXO 
								AND D2C.D_E_L_E_T_<>'*' AND D2C.D2_CLIENTE=SE1.E1_CLIENTE 
								AND D2C.D2_LOJA=SE1.E1_LOJA))/100),E1_VALOR) * ISNULL((SELECT M2_MOEDA2 FROM SM2010 WHERE E1_EMISSAO=SM2010.M2_DATA AND SM2010.D_E_L_E_T_<>'*'),1)
							WHEN 3 THEN
								ISNULL((D2_VALBRUT*((E1_VALOR*100)/(SELECT SUM(D2_VALBRUT) FROM SD2010 D2C WHERE 
								D2C.D2_DOC=SE1.E1_NUM AND D2C.D2_SERIE=SE1.E1_PREFIXO 
								AND D2C.D_E_L_E_T_<>'*' AND D2C.D2_CLIENTE=SE1.E1_CLIENTE 
								AND D2C.D2_LOJA=SE1.E1_LOJA))/100),E1_VALOR) * ISNULL((SELECT M2_MOEDA3 FROM SM2010 WHERE E1_EMISSAO=SM2010.M2_DATA AND SM2010.D_E_L_E_T_<>'*'),1)						 
							END,2) AS Valor_Credito,
							0 AS Valor_Debito
 FROM SE1010 SE1 
	LEFT JOIN SED010 SED ON SED.ED_CODIGO=SE1.E1_NATUREZ AND SED.D_E_L_E_T_<>'*'
	LEFT JOIN SD2010 SD2 ON SD2.D2_DOC=SE1.E1_NUM AND SD2.D2_SERIE=SE1.E1_PREFIXO AND SD2.D_E_L_E_T_<>'*' AND SD2.D2_CLIENTE=SE1.E1_CLIENTE AND SD2.D2_LOJA=SE1.E1_LOJA
	LEFT JOIN SB1010 SB1 ON SD2.D2_COD=SB1.B1_COD AND SB1.D_E_L_E_T_<>'*'
	LEFT JOIN SC6010 SC6 ON SC6.C6_NUM=SD2.D2_PEDIDO AND SC6.C6_ITEM=SD2.D2_ITEMPV AND SC6.D_E_L_E_T_<>'*'
	 WHERE SE1.D_E_L_E_T_<>'*' 
	 ORDER BY E1_VENCTO,E1_NUM,D2_COD
	 
	 
INSERT INTO ZZFLFIN	 
SELECT dbo.TRIM(E2_FILIAL) AS Filial,
  	   substring(E2_VENCTO,1,4) as Ano,
	   substring(E2_VENCTO,5,2) as Mes,
	   substring(E2_VENCTO,7,2) as Dia,
	   'Pagar' as C_D,
	   dbo.TRIM(E2_TIPO) AS Tipo,
	   ISNULL(dbo.TRIM(ED_CODIGO)+'-'+dbo.TRIM(ED_DESCRIC),'') AS Natureza,
	    dbo.TRIM(E2_PREFIXO)+'-'+dbo.TRIM(E2_NUM)+' Parc.: '+
	   CAST(CASE CAST(E2_PARCELA AS INTEGER)
		WHEN 0 THEN 1
		ELSE + CAST(E2_PARCELA AS INTEGER)
		END AS VARCHAR)+ '/'+
		CAST(ISNULL((SELECT COUNT(E2_NUM) 
			FROM SE2010 SE2TOT 
				WHERE SE2TOT.D_E_L_E_T_<>'*' 
						AND SE2TOT.E2_FILIAL=SE2.E2_FILIAL 
						AND SE2TOT.E2_TIPO=SE2.E2_TIPO
						AND SE2TOT.E2_PREFIXO=SE2.E2_PREFIXO
						AND SE2TOT.E2_NUM=SE2.E2_NUM
						AND SE2TOT.E2_FORNECE=SE2.E2_FORNECE
						AND SE2TOT.E2_LOJA=SE2.E2_LOJA),1) AS VARCHAR)+' - '+dbo.TRIM(E2_NOMFOR) +' de '+
							dbo.dateConv(E2_EMISSAO,1)
						 as Titulo,
		ISNULL(dbo.TRIM(D1_COD) + ' - ' +dbo.TRIM(D1_ITNOME)+ ' - Qt: '+CAST(D1_QUANT AS VARCHAR)+ ' - Total: '+CAST(D1_TOTAL AS VARCHAR),'') AS NF,						 
		ISNULL(dbo.TRIM(D1_PEDIDO)+'-'+dbo.TRIM(D1_ITEMPC) + ' - Qt: '+CAST(C7_QUANT AS VARCHAR)+ ' - Total: '+CAST(C7_TOTAL AS VARCHAR),'') AS Pedido,
		0 as Valor_Credito,
		ROUND(CASE E2_MOEDA
					WHEN 1 THEN
							ISNULL((D1_TOTAL*((E2_VALOR*100)/(SELECT SUM(D1_TOTAL) FROM SD1010 D1C WHERE 
								D1C.D1_DOC=SE2.E2_NUM AND D1C.D1_SERIE=SE2.E2_PREFIXO 
								AND D1C.D_E_L_E_T_<>'*' AND D1C.D1_FORNECE=SE2.E2_FORNECE 
								AND D1C.D1_LOJA=SE2.E2_LOJA))/100),E2_VALOR) 
							WHEN 2 THEN
								ISNULL((D1_TOTAL*((E2_VALOR*100)/(SELECT SUM(D1_TOTAL) FROM SD1010 D1C WHERE 
								D1C.D1_DOC=SE2.E2_NUM AND D1C.D1_SERIE=SE2.E2_PREFIXO 
								AND D1C.D_E_L_E_T_<>'*' AND D1C.D1_FORNECE=SE2.E2_FORNECE 
								AND D1C.D1_LOJA=SE2.E2_LOJA))/100),E2_VALOR)  * ISNULL((SELECT M2_MOEDA2 FROM SM2010 WHERE E2_EMISSAO=SM2010.M2_DATA AND SM2010.D_E_L_E_T_<>'*'),1)
							WHEN 3 THEN
								ISNULL((D1_TOTAL*((E2_VALOR*100)/(SELECT SUM(D1_TOTAL) FROM SD1010 D1C WHERE 
								D1C.D1_DOC=SE2.E2_NUM AND D1C.D1_SERIE=SE2.E2_PREFIXO 
								AND D1C.D_E_L_E_T_<>'*' AND D1C.D1_FORNECE=SE2.E2_FORNECE 
								AND D1C.D1_LOJA=SE2.E2_LOJA))/100),E2_VALOR)  * ISNULL((SELECT M2_MOEDA3 FROM SM2010 WHERE E2_EMISSAO=SM2010.M2_DATA AND SM2010.D_E_L_E_T_<>'*'),1)						 
							END,2) AS Valor_Debito
	FROM SE2010 SE2 
		LEFT JOIN SED010 SED ON SED.ED_CODIGO=SE2.E2_NATUREZ AND SED.D_E_L_E_T_<>'*'
		LEFT JOIN SD1010 SD1 ON SD1.D1_DOC=SE2.E2_NUM AND SD1.D1_SERIE=SE2.E2_PREFIXO AND SD1.D_E_L_E_T_<>'*' AND SD1.D1_FORNECE=SE2.E2_FORNECE AND SD1.D1_LOJA=SE2.E2_LOJA
		LEFT JOIN SC7010 SC7 ON SC7.D_E_L_E_T_<>'*' AND D1_PEDIDO=C7_NUM AND D1_ITEMPC=C7_ITEM
WHERE SE2.D_E_L_E_T_<>'*'--	AND E2_VENCTO='20140625' 
ORDER BY E2_VENCTO,E2_NUM,D1_COD






SELECT * FROM ZZFLFIN ORDER BY ANO,MES,DIA


		
		
		END