/****** Object:  StoredProcedure [dbo].[P_PG_CRED_HISTORICO_FILIAL]    Script Date: 03/26/2014 10:46:14 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PG_CRED_HISTORICO_FILIAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PG_CRED_HISTORICO_FILIAL]
GO
/****** Object:  StoredProcedure [dbo].[P_PG_CRED_HISTORICO_FILIAL]    Script Date: 17/02/2016 16:55:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec P_PG_CRED_HISTORICO_FILIAL 1,2,'2016-01-02'
CREATE PROCEDURE [dbo].[P_PG_CRED_HISTORICO_FILIAL] @CD_EMP INT=1, @CD_FILIAL INT, @DT_PROC DATETIME=NULL WITH ENCRYPTION AS 
SET NOCOUNT ON
DECLARE @CD_CTR INT
DECLARE @DT_ATUAL DATETIME 
SET @DT_ATUAL=GETDATE()

IF @DT_PROC IS NULL 
begin
	SET @DT_PROC = CONVERT(CHAR(10),GETDATE(),112)
end

CREATE TABLE #RSPAGAR (CD_EMP INT, CD_FILIAL INT, CD_PG_CRED INT, VLR_DP MONEY)
CREATE TABLE #RSPAGARRECEBIMENTO (CD_EMP INT, CD_FILIAL INT, CD_PG_CRED INT, VLR_PGTO MONEY)
--Gerando contas a pagar da filial
INSERT INTO #RSPAGAR
SELECT
	CD_EMP,
	CD_FILIAL,
	CD_PG_CRED,
	ROUND(VLR_DP,2)
FROM
	PG_CRED
WHERE
	CD_EMP =@CD_EMP
	AND CD_FILIAL = @CD_FILIAL
	AND DT_EMIS<=@DT_PROC
	AND TP_DP <> 5
	AND STS_DP <> 3
	
--Gerando recebimentos do contas a pagar
INSERT INTO #RSPAGARRECEBIMENTO
SELECT
	R.CD_EMP,
	R.CD_FILIAL,
	R.CD_PG_CRED,
	SUM(ROUND(R.VL_PGTO,2) - CASE WHEN R.CD_DP_LANC =1 THEN P.VLR_DESP_OUTR  ELSE 0 END) AS VLR_PGTO
FROM
	PG_DEB R INNER JOIN PG_CRED P ON
	R.CD_EMP = P.CD_EMP AND
	R.CD_FILIAL = P.CD_FILIAL AND
	R.CD_PG_CRED = P.CD_PG_CRED
WHERE
	R.CD_EMP =@CD_EMP
	AND R.CD_FILIAL =@CD_FILIAL
	AND R.DT_PAG <=@DT_PROC
GROUP BY
	R.CD_EMP,
	R.CD_FILIAL,
	R.CD_PG_CRED

DELETE
	P
FROM
	#RSPAGAR P LEFT OUTER JOIN #RSPAGARRECEBIMENTO R ON
	P.CD_EMP = R.CD_EMP AND
	P.CD_FILIAL = R.CD_FILIAL AND
	P.CD_PG_CRED=R.CD_PG_CRED
WHERE
	(VLR_DP-ISNULL(VLR_PGTO,0)) = 0

SET @CD_CTR=ISNULL((SELECT 
	MAX(CD_CTR)
FROM
	PG_CRED_HISTORICO
WHERE
	DT_PROC=@DT_PROC),0)

IF @CD_CTR > 0
begin
	DELETE 
		FROM PG_CRED_HISTORICO_CPL
	WHERE
		CD_EMP =@CD_EMP
		AND CD_FILIAL=@CD_FILIAL
		AND CD_CTR=@CD_CTR

	PRINT 'Lote atual.: ' + CONVERT(VARCHAR, @CD_CTR)
end

IF @CD_CTR=0
begin
	INSERT INTO PG_CRED_HISTORICO (DT_PROC,DT_CAD)
	SELECT
		@DT_PROC,
		@DT_ATUAL

	SET @CD_CTR = IDENT_CURRENT( 'PG_CRED_HISTORICO' )
	PRINT 'Lote novo.: ' + CONVERT(VARCHAR, @CD_CTR)
end

INSERT INTO PG_CRED_HISTORICO_CPL (
	CD_CTR,
	CD_EMP,
	CD_FILIAL,
	CD_PG_CRED,
	CD_FORN,
	VLR_DP,
	DT_EMIS,
	DT_VENCTO,
	SLD_DP,
	STS_DP,
	COB,
	NR_BOLETA,
	CD_BARRAS_BOLETA,
	ORIG_LANC,
	CD_HIST,
	HIST,
	NR_DOC,
	CD_BC,
	DT_CAD,
	CD_USU,
	CHQ_LANC,
	V_NF,
	TP_DP,
	VLR_DESC,
	VLR_JUROS,
	NR_TIT,
	VLR_DESP_OUTR,
	VLR_DEVOLUCAO,
	ST_CHK,
	NR_DP,
	ANO,
	PARC)
SELECT
	@CD_CTR,      
	a.CD_EMP, 
	a.CD_FILIAL, 
	a.CD_PG_CRED,	 
	a.CD_FORN, 
	a.VLR_DP, 
	a.DT_EMIS, 
	a.DT_VENCTO, 
	a.SLD_DP, 
	0 AS STS_DP, 
	a.COB, 
	a.NR_BOLETA, 
    a.CD_BARRAS_BOLETA, 
    a.ORIG_LANC, 
    a.CD_HIST, 
    a.HIST, 
    a.NR_DOC, 
    a.CD_BC, 
    a.DT_CAD, 
    a.CD_USU, 
    a.CHQ_LANC, 
    a.V_NF, 
    a.TP_DP, 
    a.VLR_DESC, 
    a.VLR_JUROS,
	SUBSTRING(a.NR_TIT,1,50) as NR_TIT, 
    a.VLR_DESP_OUTR, 
    a.VLR_DEVOLUCAO, 
    isnull(a.ST_CHK,0),
    a.NR_DP,
	a.ANO,
	a.PARC
FROM         
	PG_CRED a 
	inner join pg_forn f on 
	a.cd_emp = f.cd_emp and
	a.cd_forn = f.cd_forn
	INNER JOIN #RSPAGAR p ON 
	a.CD_EMP = p.CD_EMP AND
	a.CD_FILIAL = p.CD_FILIAL AND
	a.CD_PG_CRED = p.CD_PG_CRED

DROP TABLE #RSPAGAR
DROP TABLE #RSPAGARRECEBIMENTO
