IF OBJECT_ID('F_INADIMPLENCIA_ANALITICO') IS NOT NULL
BEGIN
	DROP FUNCTION F_INADIMPLENCIA_ANALITICO
END
GO

CREATE	FUNCTION F_INADIMPLENCIA_ANALITICO(@DT_BASE DATETIME,@DT_INI DATETIME,@DT_FIN DATETIME)
RETURNS TABLE
AS
RETURN
SELECT		RC_DEB.CD_EMP, RC_DEB.CD_FILIAL,
			B.NM_BANDEIRA BANDEIRA,G.NM_GRP_ECON GRUPO_ECONOMICO,C.DS_CID CIDADE,C.UF,

			RC_CLI.CD_CLI,RC_CLI.NM_FANT,RC_CLI.CGC_CPF,RC_CLI.TEL
			,ISNULL(GLB_REG.CD_REG, 0) AS CD_REG
			,ISNULL(GLB_REG.DS_REG, 'NAO DEFINIDO') AS DS_REG
			,RC_DEB.CD_RC_DEB,RC_DEB.NR_DP,RC_DEB.PARC,RC_DEB.DT_VENCTO,RC_DEB.VLR_DP

			,RC_DEB.VLR_DP						AS VLR_FATURAS
			,SUM(ISNULL(RC_CRED.VL_DESC,0))		AS VLR_DESCONTOS
			,SUM(ISNULL(RC_CRED.VL_JUR,0))		AS VLR_JUROS

			,SUM(CASE WHEN RC_CRED.DT_PAG <= @DT_BASE THEN ISNULL(VL_PGTO,0) ELSE 0 END)	AS VLR_AMORTIZADO

			,CASE WHEN CLI_CONV.CD_CLI IS NULL THEN 0 ELSE 1 END AS IS_CLIENTE_CONVENIO
			,CASE WHEN CLI_CRED.CD_CLI IS NULL THEN 0 ELSE 1 END AS IS_CLIENTE_CREDIARIO
			,CASE WHEN CLI_CONV.CD_CLI IS NULL AND CLI_CRED.CD_CLI IS NULL THEN 1 ELSE 0 END AS IS_CLIENTE_OUTROS

FROM		RC_DEB 
LEFT		JOIN RC_CRED ON RC_DEB.CD_EMP = RC_CRED.CD_EMP 
AND			RC_DEB.CD_FILIAL = RC_CRED.CD_FILIAL 
AND			RC_DEB.CD_RC_DEB = RC_CRED.CD_RC_DEB

INNER		JOIN PRC_FILIAL F ON RC_DEB.CD_EMP = F.CD_EMP 
AND			RC_DEB.CD_FILIAL = F.CD_FILIAL 
INNER		JOIN GLB_CID C ON 
			F.CD_CID = C.CD_CID
LEFT		JOIN GLB_CID_GLB_MUNICIPIO CM ON 
			F.CD_CID = CM.CD_CID
LEFT		JOIN PRC_BANDEIRA_PRC_FILIAL BF ON
			F.CD_EMP	= BF.CD_EMP AND
			F.CD_FILIAL = BF.CD_FILIAL 
LEFT		JOIN PRC_BANDEIRA B ON 
			BF.CD_BANDEIRA = B.CD_BANDEIRA 
INNER		JOIN PRC_GRP_ECON_PRC_FILIAL FG ON
			F.CD_EMP	= FG.CD_EMP AND
			F.CD_FILIAL = FG.CD_FILIAL
INNER		JOIN PRC_GRP_ECON G ON 
			FG.CD_GRP_ECON	= G.CD_GRP_ECON

INNER		JOIN RC_CLI ON RC_DEB.CD_EMP = RC_CLI.CD_EMP 
AND			RC_DEB.CD_CLI = RC_CLI.CD_CLI

LEFT		JOIN RC_CLI_CONV_RC_CLI CLI_CONV ON CLI_CONV.CD_EMP = RC_CLI.CD_EMP
AND			CLI_CONV.CD_CLI = RC_CLI.CD_CLI

LEFT		JOIN RC_CLI_CREDIARIO CLI_CRED ON CLI_CRED.CD_EMP = RC_CLI.CD_EMP
AND			CLI_CRED.CD_CLI = RC_CLI.CD_CLI

LEFT		JOIN GLB_REG ON RC_CLI.CD_EMP = GLB_REG.CD_EMP AND RC_CLI.CD_REGI = GLB_REG.CD_REG

WHERE		RC_DEB.DT_VENCTO BETWEEN @DT_INI AND @DT_FIN
AND			RC_DEB.DT_VENCTO <= @DT_BASE
AND			RC_DEB.STS_DP != 3 

GROUP BY	
			RC_DEB.CD_EMP,RC_DEB.CD_FILIAL,
			B.NM_BANDEIRA,G.NM_GRP_ECON,C.DS_CID,C.UF,
			RC_CLI.CD_CLI,RC_CLI.NM_FANT,RC_CLI.CGC_CPF,RC_CLI.TEL,GLB_REG.CD_REG,GLB_REG.DS_REG,
			RC_DEB.CD_RC_DEB,RC_DEB.NR_DP,RC_DEB.PARC,RC_DEB.DT_VENCTO,RC_DEB.VLR_DP,
			CLI_CONV.CD_CLI,CLI_CRED.CD_CLI



GO

--SELECT * FROM F_INADIMPLENCIA_ANALITICO(GETDATE(),'2014-01-01',GETDATE()-1)