/****** Object:  StoredProcedure [dbo].[P_EST_NF_ENT_EST_NF_SAI_TRANSF_VINCULA_XML]    Script Date: 06/21/2013 12:40:40 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_ENT_EST_NF_SAI_TRANSF_VINCULA_XML]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_ENT_EST_NF_SAI_TRANSF_VINCULA_XML]
GO
GO
GO
GO
--EXEC P_EST_NF_ENT_EST_NF_SAI_TRANSF_VINCULA_XML 1,2,'2016-09-01'
CREATE PROCEDURE P_EST_NF_ENT_EST_NF_SAI_TRANSF_VINCULA_XML @CD_EMP INT=1, @CD_FILIAL INT=0, @DT_PROC DATETIME=NULL
AS
BEGIN
INSERT INTO EST_NF_ENT_ARQ_DANFE
SELECT		
	0, 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP,
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST, 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT,
    EST_NF_SAI_ARQ_DANFE.ARQUIVO_DANFE,
	GETDATE()
FROM         
	EST_NF_ENT_EST_NF_SAI_TRANSF INNER JOIN EST_NF_SAI_ARQ_DANFE ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_SAI_ARQ_DANFE.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = EST_NF_SAI_ARQ_DANFE.CD_FILIAL AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = EST_NF_SAI_ARQ_DANFE.CD_NF
	INNER JOIN EST_NF_ENT ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_ENT.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST = EST_NF_ENT.CD_FILIAL AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT = EST_NF_ENT.CD_NF
	LEFT OUTER JOIN EST_NF_ENT_ARQ_DANFE ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_ENT_ARQ_DANFE.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST = EST_NF_ENT_ARQ_DANFE.CD_FILIAL AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT = EST_NF_ENT_ARQ_DANFE.CD_NF
WHERE
	EST_NF_ENT_ARQ_DANFE.CD_NF IS NULL
	AND EST_NF_ENT.CD_EMP = @CD_EMP
	AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
	AND EST_NF_ENT.DT_ENT >=@DT_PROC

--Pedencia de arquivo DANFE para validacao
INSERT INTO EST_NF_ENT_NFE_FORN_VALID
(CD_EMP, 
 CD_FILIAL, 
 CD_FORN, 
 NR_NF, 
 DT_EMI_NF, 
 NR_AUTORIZADOR, 
 NR_PROTOCOLO, 
 DIG_VALIDACAO, 
 DT_VALIDACAO, 
 VALIDACAO, 
 CD_USU, 
 DT_CAD, 
 ERR_VALIDACAO, 
 CANCELADA_SEFAZ)
SELECT		
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP,
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST, 
	PRC_FILIAL_PG_FORN.CD_FORN,
	EST_NF_SAI.NF_NF,
	EST_NF_SAI.DT_EMI_NF,
	EST_NF_SAI_NFE.NR_AUTORIZADOR,
	EST_NF_SAI_NFE.NR_PROTOCOLO,
	EST_NF_SAI_NFE.DG_VALIDADOR,
	EST_NF_SAI_NFE.DT_RECBTO,
	0,
	1,
	CONVERT(CHAR(10),GETDATE(),112),
	'',
	0  
FROM         
	EST_NF_ENT_EST_NF_SAI_TRANSF INNER JOIN EST_NF_SAI_NFE ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_SAI_NFE.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = EST_NF_SAI_NFE.CD_FILIAL AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = EST_NF_SAI_NFE.CD_NF
	INNER JOIN EST_NF_SAI ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_SAI.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = EST_NF_SAI.CD_FILIAL AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_SAI = EST_NF_SAI.CD_NF
	INNER JOIN PRC_FILIAL_PG_FORN ON
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = PRC_FILIAL_PG_FORN.CD_EMP AND
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_ORIG = PRC_FILIAL_PG_FORN.CD_FILIAL    
	INNER JOIN EST_NF_ENT ON 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP = EST_NF_ENT.CD_EMP AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST = EST_NF_ENT.CD_FILIAL AND 
    EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT = EST_NF_ENT.CD_NF
    LEFT OUTER JOIN EST_NF_ENT_NFE_FORN_VALID ON
    EST_NF_SAI.CD_EMP = EST_NF_ENT_NFE_FORN_VALID.CD_EMP AND
    PRC_FILIAL_PG_FORN.CD_FORN  = EST_NF_ENT_NFE_FORN_VALID.CD_FORN  AND
    EST_NF_SAI.NF_NF    = EST_NF_ENT_NFE_FORN_VALID.NR_NF AND
    EST_NF_SAI.DT_EMI_NF     = EST_NF_ENT_NFE_FORN_VALID.DT_EMI_NF 
WHERE
	EST_NF_ENT_NFE_FORN_VALID.NR_NF  IS NULL
	AND EST_NF_ENT.CD_EMP = @CD_EMP
	AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
	AND EST_NF_ENT.DT_ENT >=@DT_PROC

--Vinculando com a entrada
INSERT INTO EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID
SELECT 
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP,
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST,
	EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT,
	EST_NF_ENT_NFE_FORN_VALID.CD_CTR 
FROM         
	EST_NF_ENT_NFE_FORN_VALID INNER JOIN EST_NF_ENT ON 
	EST_NF_ENT_NFE_FORN_VALID.CD_EMP = EST_NF_ENT.CD_EMP AND 
    EST_NF_ENT_NFE_FORN_VALID.CD_FILIAL = EST_NF_ENT.CD_FILIAL AND 
    EST_NF_ENT_NFE_FORN_VALID.NR_NF = EST_NF_ENT.NR_NF AND 
    EST_NF_ENT_NFE_FORN_VALID.DT_EMI_NF = EST_NF_ENT.DT_EMI_NF
	INNER JOIN EST_NF_ENT_EST_NF_SAI_TRANSF ON 
	EST_NF_ENT.CD_EMP = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_EMP AND 
    EST_NF_ENT.CD_FILIAL = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_FIL_DEST  AND 
    EST_NF_ENT.CD_NF = EST_NF_ENT_EST_NF_SAI_TRANSF.CD_NF_ENT   
    LEFT OUTER JOIN EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID   ON
    EST_NF_ENT_NFE_FORN_VALID.CD_CTR = EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_CTR 
WHERE
	EST_NF_ENT_EST_NF_ENT_NFE_FORN_VALID.CD_CTR IS NULL
	AND EST_NF_ENT.CD_EMP = @CD_EMP
	AND EST_NF_ENT.CD_FILIAL = @CD_FILIAL
	AND EST_NF_ENT.DT_ENT >=@DT_PROC
END