/****** Object:  StoredProcedure [dbo].[P_PG_CRED_LIB_PGTO_VINCULO_DANFE]    Script Date: 02/11/2014 18:34:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_PG_CRED_LIB_PGTO_VINCULO_DANFE]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_PG_CRED_LIB_PGTO_VINCULO_DANFE]
GO
--EXEC P_PG_CRED_LIB_PGTO_VINCULO_DANFE 1
CREATE PROCEDURE P_PG_CRED_LIB_PGTO_VINCULO_DANFE @CD_EMP INT=1
AS
SET NOCOUNT ON  
DECLARE @CD_LIB_PGTO INT
DECLARE @CD_FUN_LIB INT

SET @CD_FUN_LIB=ISNULL((SELECT
							TOP 1 CD_FUN
						FROM
							FP_FUNC_ALC_PG
						ORDER BY
							VLR_ALC_PG DESC),0)

CREATE TABLE #RsDupl (CD_EMP INT,
					  CD_FILIAL INT,
					  CD_PG_CRED INT)

IF @CD_FUN_LIB>0
begin
INSERT INTO #RsDupl
SELECT
	DISTINCT
	PG_CRED.CD_EMP,
	PG_CRED.CD_FILIAL,
	PG_CRED.CD_PG_CRED
FROM
	PG_CRED INNER JOIN PG_CRED_EST_NF_ENT ON
	PG_CRED.CD_EMP = PG_CRED_EST_NF_ENT.CD_EMP AND
	PG_CRED.CD_FILIAL = PG_CRED_EST_NF_ENT.CD_FILIAL AND
	PG_CRED.NR_DP = PG_CRED_EST_NF_ENT.NR_DP AND
	PG_CRED.ANO = PG_CRED_EST_NF_ENT.ANO
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ON
	PG_CRED_EST_NF_ENT.CD_EMP = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_EMP AND
	PG_CRED_EST_NF_ENT.CD_FILIAL = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_FILIAL AND
	PG_CRED_EST_NF_ENT.CD_NF = EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT.CD_NF 
	LEFT OUTER JOIN PG_CRED_LIB_PGTO_PG_CRED ON
	PG_CRED.CD_EMP = PG_CRED_LIB_PGTO_PG_CRED.CD_EMP AND
	PG_CRED.CD_FILIAL = PG_CRED_LIB_PGTO_PG_CRED.CD_FILIAL AND
	PG_CRED.CD_PG_CRED = PG_CRED_LIB_PGTO_PG_CRED.CD_PG_CRED
WHERE
	PG_CRED.DT_VENCTO <=GETDATE()+10
	AND  PG_CRED_LIB_PGTO_PG_CRED.CD_PG_CRED IS NULL
	AND PG_CRED.STS_DP IN (0,2)
UNION
SELECT
	DISTINCT
	PG_CRED.CD_EMP,
	PG_CRED.CD_FILIAL,
	PG_CRED.CD_PG_CRED
FROM
	PG_CRED INNER JOIN PG_CRED_CONC_DDA ON
	PG_CRED.CD_EMP = PG_CRED_CONC_DDA.CD_EMP AND
	PG_CRED.CD_FILIAL = PG_CRED_CONC_DDA.CD_FILIAL AND
	PG_CRED.CD_PG_CRED = PG_CRED_CONC_DDA.CD_PG_CRED 
	LEFT OUTER JOIN PG_CRED_LIB_PGTO_PG_CRED ON
	PG_CRED.CD_EMP = PG_CRED_LIB_PGTO_PG_CRED.CD_EMP AND
	PG_CRED.CD_FILIAL = PG_CRED_LIB_PGTO_PG_CRED.CD_FILIAL AND
	PG_CRED.CD_PG_CRED = PG_CRED_LIB_PGTO_PG_CRED.CD_PG_CRED
WHERE
	PG_CRED.DT_VENCTO <=GETDATE()+10
	AND  PG_CRED_LIB_PGTO_PG_CRED.CD_PG_CRED IS NULL
	AND PG_CRED.STS_DP IN (0,2)


IF  EXISTS (SELECT
			 TOP 1 CD_EMP
		   FROM
			#RsDupl V)
begin
	SET @CD_LIB_PGTO=ISNULL((SELECT
								MAX(CD_LIB_PGTO)
							 FROM
								PG_CRED_LIB_PGTO
							 WHERE
								CD_EMP=@CD_EMP),0) +1

	INSERT INTO PG_CRED_LIB_PGTO
	(CD_EMP,
	CD_LIB_PGTO,
	DT_LIB_PGTO,
	HR_LIB_PGTO,
	CD_FUN,
	VLR_ALC_PG,
	CD_USU,
	DT_CAD,
	DT_ULT_ALT,
	HIST_LIB_PGTO)
	SELECT
		@CD_EMP,
		@CD_LIB_PGTO,
		CONVERT(CHAR(10),GETDATE(),112),
		GETDATE(),
		@CD_FUN_LIB,
		0,
		1,
		GETDATE(),
		NULL,
		'LIBERACAO AUTOMATICA PELO SISTEMA, VINCULO COM DANFE OU CONCILIACAO POR DDA'

	INSERT INTO PG_CRED_LIB_PGTO_PG_CRED
	(CD_EMP,
	CD_LIB_PGTO,
	CD_FILIAL,
	CD_PG_CRED)
	SELECT
		D.CD_EMP,
		@CD_LIB_PGTO,
		D.CD_FILIAL,
		D.CD_PG_CRED
	FROM
		#RsDupl D
	END
end
DROP TABLE #RsDupl;