IF  EXISTS( SELECT  * FROM  SYS.OBJECTS 
	WHERE  NAME LIKE 'P_EST_NF_ENT_DANFE_PROVISORIO_RESTORE' AND TYPE = 'P' )
BEGIN
	DROP PROCEDURE P_EST_NF_ENT_DANFE_PROVISORIO_RESTORE 
END
GO
--EXEC P_EST_NF_ENT_DANFE_PROVISORIO_RESTORE 
CREATE PROCEDURE P_EST_NF_ENT_DANFE_PROVISORIO_RESTORE 
WITH ENCRYPTION AS
SET NOCOUNT ON;
DECLARE @CD_CTR INT
/*
	Esta procedure roda dentro da P_EST_NF_ENT_DANFE_PROVISORIO_BLOQUEIO_PENDENTE_EXEC_ALL
*/
PRINT 'Tentativa de restauracao de DANFE (' + CONVERT(VARCHAR(8), GETDATE(), 114) + ')...';

--Criando vinculo com pedido de compras
INSERT INTO EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP
(CD_CTR,
CD_EMP,
CD_FILIAL,
CD_PED,
CD_USU,
DT_CAD,
CD_CTR_RESTORE)
SELECT
	D.CD_CTR,
	D.CD_EMP,
	D.CD_FILIAL,
	R.CD_PED,
	R.CD_USU,
	GETDATE(),
	R.CD_CTR_RESTORE 
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_PENDENTE  P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP_RESTORE R ON
	D.CD_EMP = R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.NR_NF = R.NR_NF AND
	D.DT_EMIS_NF = R.DT_EMIS_NF 
	LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP PED ON
	D.CD_CTR = PED.CD_CTR 
WHERE
	PED.CD_PED IS NULL

DELETE 
	R
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP P INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP_RESTORE R ON
	P.CD_CTR_RESTORE = R.CD_CTR_RESTORE
	
--Criando pre-entrada
INSERT INTO EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA
(CD_EMP,
CD_CTR_PRE_ENTRADA,
CD_CTR,
CD_CTR_RESTORE
)

SELECT
	D.CD_EMP,
	R.CD_CTR_PRE_ENTRADA,
	D.CD_CTR,
	R.CD_CTR_RESTORE
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_PENDENTE  P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA_RESTORE R ON
	D.CD_EMP = R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.NR_NF = R.NR_NF AND
	D.DT_EMIS_NF = R.DT_EMIS_NF 
	LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA PENT ON
	D.CD_CTR = PENT.CD_CTR 
WHERE
	PENT.CD_CTR IS NULL

DELETE 
	R
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT_PRE_ENTRADA PENT INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_PED_CP_RESTORE R ON
	PENT.CD_CTR_RESTORE = R.CD_CTR_RESTORE

	
--Criando cabecalho do checkin
INSERT INTO EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA
(CD_CTR,
CD_USU,
DT_CAD,
CHECKIN_DIVERGENCIA,
DT_TRANSACAO,
CD_CTR_RESTORE
)
SELECT
	D.CD_CTR,
	R.CD_USU,
	CONVERT(CHAR(10),GETDATE(),112),
	R.CHECKIN_DIVERGENCIA,
	GETDATE(),
	R.CD_CTR_RESTORE
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_PENDENTE  P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_RESTORE R ON
	D.CD_EMP = R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.NR_NF = R.NR_NF AND
	D.DT_EMIS_NF = R.DT_EMIS_NF 
	LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA CHK ON
	D.CD_CTR = CHK.CD_CTR 
WHERE
	CHK.CD_CTR IS NULL

DELETE 
	R
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA CHK INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_RESTORE R ON
	CHK.CD_CTR_RESTORE = R.CD_CTR_RESTORE	

--Criando checkin complemento
INSERT INTO EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_CPL
(CD_CTR,
CD_EMP,
CD_PROD,
QT_IT_CHECK,
CD_CTR_RESTORE)
SELECT
	D.CD_CTR,
	R.CD_EMP,
	R.CD_PROD,
	R.QT_IT_CHECK,
	R.CD_CTR_RESTORE
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_PENDENTE  P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_CPL_RESTORE R ON
	D.CD_EMP = R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.NR_NF = R.NR_NF AND
	D.DT_EMIS_NF = R.DT_EMIS_NF 
	LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_CPL CHKCPL ON
	D.CD_CTR = CHKCPL.CD_CTR 
WHERE
	CHKCPL.CD_CTR IS NULL

DELETE 
	R
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_CPL CHKCPL INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_CHECKIN_ENTRADA_CPL_RESTORE R ON
	CHKCPL.CD_CTR_RESTORE = R.CD_CTR_RESTORE	
	
--Criando ciencia da operacao
INSERT INTO EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO
(CD_CTR,
NR_PROTOCOLO,
DIG_VALIDACAO,
DATA_VALIDACAO,
DT_CAD,
CD_CTR_RESTORE)
SELECT
	D.CD_CTR,
	r.NR_PROTOCOLO,
	r.DIG_VALIDACAO,
	r.DATA_VALIDACAO,
	GETDATE(),
	R.CD_CTR_RESTORE	 
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_PENDENTE  P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO_RESTORE R ON
	D.CD_EMP = R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.NR_NF = R.NR_NF AND
	D.DT_EMIS_NF = R.DT_EMIS_NF 	
	LEFT OUTER JOIN EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO CIENCIA ON
	D.CD_CTR = CIENCIA.CD_CTR 
WHERE
	CIENCIA.CD_CTR IS NULL

DELETE 
	R
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO CIENCIA INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_CIENCIA_OPERACAO_RESTORE R ON
	CIENCIA.CD_CTR_RESTORE = R.CD_CTR_RESTORE		
	
--Criando liberacao de danfe
INSERT INTO EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO
(CD_CTR,
CD_USU,
DT_CAD,
TIPO_BLQ,
CD_PED_COMPRA,
DT_LIBERACAO,
CD_USU_LIB,
OBS_LIB,
DT_HR_CAD,
CD_CTR_RESTORE)
SELECT  
	D.CD_CTR,
	max (R.CD_USU),
	GETDATE(),
	max (R.TIPO_BLQ),
	max (R.CD_PED_COMPRA),
	max (R.DT_LIBERACAO),
	max (R.CD_USU_LIB),
	max (R.OBS_LIB),
	max (R.DT_HR_CAD),
	max  (R.CD_CTR_RESTORE)
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN  EST_NF_ENT_DANFE_PROVISORIO_PENDENTE  P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO_RESTORE R ON
	D.CD_EMP = R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.NR_NF = R.NR_NF AND
	D.DT_EMIS_NF = R.DT_EMIS_NF 	 
	LEFT OUTER  JOIN EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO LIB ON
	D.CD_CTR = LIB.CD_CTR
WHERE
	LIB.CD_CTR IS NULL 
group by
	D.CD_CTR

DELETE 
	R
FROM 
	EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO LIB INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_LIBERACAO_RESTORE R ON
	LIB.CD_CTR_RESTORE = R.CD_CTR_RESTORE		
		
--Voltando dados para o danfe provisorio
UPDATE
	D
SET
	D.CD_MOV_OP=R.CD_MOV_OP,
	D.CD_USU_LIBEROU_ENTRADA=R.CD_USU_LIBEROU_ENTRADA,
	D.FLAG_LIBERA_ENTRADA_SEM_PEDIDO=R.FLAG_LIBERA_ENTRADA_SEM_PEDIDO,
	D.FLAG_REPOSICAO_PBM=R.FLAG_REPOSICAO_PBM
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE P ON
	D.CD_CTR = P.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE_RESTORE R ON
	D.CD_EMP =R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.DT_EMIS_NF = R.DT_EMIS_NF AND
	D.NR_NF = R.NR_NF 
WHERE	
	D.CD_MOV_OP<>R.CD_MOV_OP
	OR D.CD_USU_LIBEROU_ENTRADA<>R.CD_USU_LIBEROU_ENTRADA
	OR D.FLAG_LIBERA_ENTRADA_SEM_PEDIDO<>R.FLAG_LIBERA_ENTRADA_SEM_PEDIDO
	OR D.FLAG_REPOSICAO_PBM<>R.FLAG_REPOSICAO_PBM

	
DELETE
	R
FROM
	EST_NF_ENT_DANFE_PROVISORIO D INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_EST_NF_ENT ENT ON
	D.CD_CTR = ENT.CD_CTR 
	INNER JOIN EST_NF_ENT_DANFE_PROVISORIO_PENDENTE_RESTORE R ON
	D.CD_EMP =R.CD_EMP AND
	D.CD_FILIAL = R.CD_FILIAL AND
	D.CD_FORN = R.CD_FORN AND
	D.DT_EMIS_NF = R.DT_EMIS_NF AND
	D.NR_NF = R.NR_NF 	


