/****** Object:  StoredProcedure [dbo].[P_PG_CRED_GERAR_DUPLICATA]    Script Date: 02/06/2014 15:09:29 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PG_CRED_GERAR_DUPLICATA_EST_NF_ENT_DANFE_PROVISORIO]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PG_CRED_GERAR_DUPLICATA_EST_NF_ENT_DANFE_PROVISORIO]
GO
GO
GO
--P_PG_CRED_GERAR_DUPLICATA_EST_NF_ENT_DANFE_PROVISORIO 3,2013
CREATE PROCEDURE [dbo].[P_PG_CRED_GERAR_DUPLICATA_EST_NF_ENT_DANFE_PROVISORIO](@CD_FILIAL AS INT, @ANO AS 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,
	DUP.VLR_TITULO AS VLR_DP,
	CASE WHEN CD_CTR_TITULO=0 THEN 1 ELSE CD_CTR_TITULO END  AS PARC,
	DUP.DT_VENCTO,
	GETDATE() DT_CAD,
	ROW_NUMBER() OVER ( PARTITION BY E.CD_FILIAL ORDER BY E.CD_FILIAL) + @NR_DP NR_DP,
	0 COB,
	DUP.VLR_TITULO AS SLD_DP,
	1 CD_BC,
	1 CD_USU,
	E.VLR_NF,
	@ANO ANO,
	DUP.VLR_DESC,
	CONVERT(VARCHAR,E.NR_NF ) + '-' + CONVERT(VARCHAR(4),YEAR(E.DT_ENT)) +'-' + '0' + CONVERT(VARCHAR,CASE WHEN CD_CTR_TITULO=0 THEN 1 ELSE CD_CTR_TITULO END) NR_TIT ,
	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_DANFE_PROVISORIO_EST_NF_ENT ON
	E.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP AND
	E.CD_FILIAL = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL AND
	E.CD_NF = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_DUPLICATA DUP ON
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_CTR = DUP.CD_CTR 	
WHERE
	E.CD_EMP=1
	AND P.CD_EMP IS NULL
	AND E.CD_FILIAL =@CD_FILIAL
	AND YEAR(E.DT_ENT) =  @ANO
---------------------------------------------------------------------------------------------------------------------
--	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