/****** Object:  StoredProcedure [dbo].[P_GERA_DUP_PAGAR_GRP_ECONOMICO]    Script Date: 02/06/2014 15:48:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_GERA_DUP_PAGAR_GRP_ECONOMICO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_GERA_DUP_PAGAR_GRP_ECONOMICO]
GO

/****** Object:  StoredProcedure [dbo].[P_PG_CRED_GERAR_DUPLICATA_GRP_ECONOMICO]    Script Date: 02/06/2014 15:48:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PG_CRED_GERAR_DUPLICATA_GRP_ECONOMICO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PG_CRED_GERAR_DUPLICATA_GRP_ECONOMICO]
GO
GO

--P_PG_CRED_GERAR_DUPLICATA_GRP_ECONOMICO 1,2013
CREATE PROCEDURE [dbo].[P_PG_CRED_GERAR_DUPLICATA_GRP_ECONOMICO](@CD_FILIAL AS INT, @ANO INT=0 )
AS

 
IF  OBJECT_ID('TEMPDB.DBO.#TBL_CRED') IS NOT NULL
BEGIN
	 DROP TABLE #TBL_CRED
END


DECLARE @NR_DP INT; 
DECLARE @CD_CTR INT;

---------------------------------------------------------------------------------------------------------------------
--	CARREGA A NUMERAÇÃO DE DUPLICATAS
---------------------------------------------------------------------------------------------------------------------

SET @NR_DP = ISNULL((SELECT 
						 MAX(NR_DP) NR_DP_ULT 
					FROM
						PG_CRED
					where
						ANO = @ANO
						AND CD_EMP=1
						AND CD_FILIAL =@CD_FILIAL),right(@ano * 1000000,len(@ano * 1000000)-2))

	
SET @CD_CTR = ISNULL((SELECT 
						 MAX(CD_PG_CRED) CD_PG_CRED 
					FROM
						PG_CRED
					where
						CD_EMP=1
						AND CD_FILIAL =@CD_FILIAL),0)
	
	
---------------------------------------------------------------------------------------------------------------------
--	CRIA A TABELA TEMPORARIA DE DUPLICATAS
---------------------------------------------------------------------------------------------------------------------
SELECT 
	E.CD_EMP,
	E.CD_FILIAL,
	ROW_NUMBER() OVER ( PARTITION BY E.CD_FILIAL ORDER BY E.CD_FILIAL) + @CD_CTR CD_PG_CRED,
	E.CD_FORN,
	E.CD_NF,
	E.DT_EMI_NF,
	E.NR_NF,
	RD.VLR_DP,
	RD.PARC,
	RD.DT_VENCTO,
	GETDATE() DT_CAD,
	ROW_NUMBER() OVER ( PARTITION BY E.CD_FILIAL ORDER BY E.CD_FILIAL) + @NR_DP NR_DP,
	0 COB,
	RD.SLD_DP,
	1 CD_BC,
	1 CD_USU,
	E.VLR_NF,
	@ANO ANO,
	RD.VLR_DESC,
	CONVERT(VARCHAR,E.NR_NF ) + '-' + CONVERT(VARCHAR(4),YEAR(E.DT_ENT)) +'-' + '0' + CONVERT(VARCHAR,RD.PARC) NR_TIT , --='1690747-2013-01'
	0 ORIG_LANC,
	E.DT_ENT DT_EMIS,
	7 CD_HIST,
	0 VLR_DESP_OUTR,
	0 VLR_DEVOLUCAO,
	0 VLR_JUROS,
	0 TP_DP  
	INTO #TBL_CRED
FROM 
	EST_NF_ENT E
	LEFT JOIN PG_CRED_EST_NF_ENT P ON 
	E.CD_EMP	= P.CD_EMP AND
	E.CD_NF		= P.CD_NF AND
	E.CD_FILIAL = P.CD_FILIAL  
	  
	INNER JOIN  EST_NF_ENT_EST_NF_SAI_TRANSF T ON 
	E.CD_EMP	= T.CD_EMP AND
	E.CD_NF		= T.CD_NF_ENT AND
	E.CD_FILIAL = T.CD_FIL_DEST 
	  
	INNER JOIN  EST_NF_SAI S ON 
	T.CD_EMP	  = S.CD_EMP AND
	T.CD_NF_SAI	  = S.CD_NF AND
	T.CD_FIL_ORIG = S.CD_FILIAL 
	
	INNER JOIN RC_DEB_EST_NF_SAI R ON 
	S.CD_EMP	= R.CD_EMP AND
	S.CD_NF		= R.CD_NF  AND
	S.CD_FILIAL = R.CD_FILIAL 
	
	INNER JOIN RC_DEB RD ON 
	R.CD_EMP	= RD.CD_EMP AND
	R.CD_FILIAL	= RD.CD_FILIAL  AND
	R.NR_DP		= RD.NR_DP AND
	R.ANO		= RD.ANO  
WHERE
	E.CD_EMP=1
	AND P.CD_EMP IS NULL
	AND E.CD_FILIAL =@CD_FILIAL
	AND YEAR(E.DT_ENT) =@ANO
	AND E.CD_FORN = 12497
 
---------------------------------------------------------------------------------------------------------------------
--	INSERE OS REGISTROS NA PG_CRED_DUP_NR_CTR
---------------------------------------------------------------------------------------------------------------------

INSERT INTO PG_CRED_DUP_NR_CTR (CD_EMP,CD_FILIAL, NR_DP,  ANO, TP_DOC)
SELECT DISTINCT
	CD_EMP, 
	CD_FILIAL, 
	NR_DP, 
	ANO,
	TP_DP 
FROM  #TBL_CRED

---------------------------------------------------------------------------------------------------------------------
--	INSERE OS REGISTROS NA PG_CRED
---------------------------------------------------------------------------------------------------------------------
INSERT INTO PG_CRED (CD_EMP,CD_FILIAL,CD_PG_CRED,CD_FORN,VLR_DP,PARC,DT_VENCTO,
	DT_CAD,NR_DP,COB,SLD_DP,CD_BC,CD_USU,VLR_NF,ANO,VLR_DESC,
	NR_TIT,ORIG_LANC,DT_EMIS,CD_HIST,VLR_DESP_OUTR,VLR_DEVOLUCAO,
	VLR_JUROS,TP_DP) 
SELECT	
	CD_EMP,
	CD_FILIAL,
	CD_PG_CRED,
	CD_FORN,
	VLR_DP,
	PARC,
	DT_VENCTO,
	DT_CAD,
	NR_DP,
	COB,
	SLD_DP,
	CD_BC,
	CD_USU,
	VLR_NF,
	ANO,
	VLR_DESC,
	NR_TIT,
	ORIG_LANC,
	DT_EMIS,
	CD_HIST,
	VLR_DESP_OUTR,
	VLR_DEVOLUCAO,
	VLR_JUROS,
	TP_DP
FROM  
	#TBL_CRED
 
---------------------------------------------------------------------------------------------------------------------
--	INSERE OS REGISTROS NA PG_CRED_EST_NF_ENT
---------------------------------------------------------------------------------------------------------------------
 
INSERT INTO PG_CRED_EST_NF_ENT (CD_EMP,CD_FILIAL,NR_DP,ANO,CD_NF)
SELECT DISTINCT
	CD_EMP,
	CD_FILIAL,
	NR_DP,
	ANO,
	CD_NF
FROM  
	#TBL_CRED 

---------------------------------------------------------------------------------------------------------------------
--	INSERE OS REGISTROS NA PG_CRED_NF
---------------------------------------------------------------------------------------------------------------------

INSERT INTO PG_CRED_NF (CD_EMP,	CD_FILIAL,NR_DP,ANO,NR_NF,DT_EMI_NF,VLR_NF)
SELECT DISTINCT
	CD_EMP,
	CD_FILIAL,
	NR_DP,
	ANO,
	NR_NF,
	DT_EMI_NF,
	VLR_NF
FROM  
	#TBL_CRED