/****** Object:  StoredProcedure [dbo].[P_EST_NF_SAI_CPL]    Script Date: 02/11/2014 18:34:05 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[P_EST_NF_SAI_CPL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[P_EST_NF_SAI_CPL]
GO
GO 

GO
--EXEC P_EST_NF_SAI_CPL 1,999,2188616, '2015-09-30',6
CREATE  PROCEDURE [dbo].[P_EST_NF_SAI_CPL] (@CD_EMP INT, 
										   @CD_FILIAL INT, 
										   @CD_NF_PROC INT,
										   @DT_EMI_NF DATE, 
										   @CD_NF_SERIE INT)
AS
begin
SET NOCOUNT ON  
DECLARE @CD_NF INT
DECLARE @NR_NF INT
IF NOT EXISTS(SELECT
				CD_NF
			  FROM
				EST_NF_SAI_EST_NF_SAI_CPL
			  WHERE
				CD_EMP =@CD_EMP
				AND CD_FILIAL=@CD_FILIAL
				AND CD_NF_REF=@CD_NF_PROC)
begin


	begin try
		--SET @CD_NF_SERIE=1171
		begin transaction;
		
			SET @CD_NF=(SELECT
						ISNULL(MAX(CD_NF),0) + 1
						FROM
							EST_NF_SAI
						WHERE
							CD_EMP =@CD_EMP
							AND CD_FILIAL = @CD_FILIAL)
			SET @NR_NF=(SELECT
						ISNULL(MAX(NR_NF),0) + 1
						FROM
							EF_NF_CTR_NR
						WHERE
							CD_EMP =@CD_EMP
							AND CD_FILIAL = @CD_FILIAL
							AND CD_NF_SERIE=@CD_NF_SERIE)
					
			/******************************************************************************************************************/

			Print 'Gerando a numeracao da nota fical ' + convert(varchar(8), @NR_NF);

			INSERT INTO EF_NF_CTR_NR (CD_EMP,CD_FILIAL,NR_NF,CD_NF_SERIE,NR_SELO_SAIDA,DT_CAD)
			SELECT
				@CD_EMP AS CD_EMP,
				@CD_FILIAL AS CD_FILIAL,
				@NR_NF AS NR_NF,
				@CD_NF_SERIE AS CD_NF_SERIE,
				0 AS NR_SELO_SAIDA,
				GETDATE() AS DT_CAD

			/******************************************************************************************************************/
			Print 'Gerando o cabecalho da nota fiscal '

			INSERT INTO EST_NF_SAI (CD_EMP,CD_FILIAL,CD_NF,CD_PED,CD_CLI,DT_EMI_NF,NF_NF,CD_NF_SERIE,VLR_NF,VLR_ICMS,VLR_FRETE,VLR_OUTROS
								,VLR_BASE_ICMS,ALIQ_ICMS,VLR_TOT_PROD,CD_NT_MOV,VLR_DESC,VLR_BASE_ICMS_SUBS,VLR_ICMS_SUBS,VLR_IPI,DT_CAD
								,DEV,CD_VEND,CD_COND_PGTO,COB,STS_NF,TP_NF,OBS1,OBS2,OBS3,OBS4,OBS5,OBS6,OBS7,OBS8,OBS9,OBS10,OBS11,CD_TRANSP,QT_VOL,ESP_VOL,MC_VOL,NR_VOL,PESO_VOL
								,PESO_LIQ_VOL,TP_FRETE,TP_DEV_NF,DT_CANCEL,VLR_ISENTAS,VLR_OUTRAS,CD_NF_ENT,CD_USU,VLR_PROD_ICMS_SUBS,DT_ULT_ALT,LANC_EFETU,NM_COMPRADOR,VLR_BASE_ICMS_IT,NF_PROD_SERV,HR_EMI_NF,VLR_DESC_CORPO
								,INF_DESCONTO,DS_TRANSP,PLACA_VEIC,PLACA_UF,CGC_CPF_TRANSP,ENDE_TRANSP,DS_CID_TRANSP,UF_TRANSP,INSC_EST_TRANSP,VLR_ISSQN,VLR_SERVICO,PERC_ISSQN,VLR_SUFRAMA,PEND_ENTRADA,NR_SELO_SAIDA
								,NR_FORMULARIO,CD_MT_CANCEL,CD_MOT_EMIS_NF,QT_CX_VOL,FLAG_NF_PROD_TOTAL_BRUTO,FLAG_IGNORAR_CONTROL,VLR_BASE_ICMS_RET,VLR_ICMS_RET,NF_MANUAL,BAIRRO_DESTINATARIO,BAIRRO_EMITENTE,CEP_DESTINATARIO
								,CEP_EMITENTE,CIDADE_DESTINATARIO,CIDADE_EMITENTE,CNPJ_DESTINATARIO,CNPJ_EMITENTE,END_DESTINATARIO,END_EMITENTE,INSC_ESTADUAL_DESTINATARIO,INSC_ESTADUAL_EMITENTE,NT_OPERACAO,RZ_DESTINATARIO
								,RZ_EMITENTE,RZ_TRANSPORTADOR,UF_DESTINATARIO,UF_EMITENTE,APLICATIVO,VERSAO_APLICATIVO,CD_CID_DESTINATARIO,CD_NT_OP_TRANSF_ENTRADA,FLAG_NOTA_INTEGRACAO,XPED_NOTA_FISCAL
								,NFE_REJEITADA,XML_NOTA,CD_CID_EMITENTE,CD_CID_TRANSP,EMAIL_TRANSP,NOTA_FISCAL_LIVRE,VLR_ICMS_DESONERADO,DT_HR_CAD)
				SELECT
					s.CD_EMP
					,s.CD_FILIAL
					,@CD_NF CD_NF
					,0 CD_PED
					,s.CD_CLI
					,@DT_EMI_NF
					,@NR_NF NF_NF
					,@CD_NF_SERIE CD_NF_SERIE
					,SUM(ROUND(s.VLR_IT + ISNULL(S.VLR_ICMS_ST, T.VLR_ICMS_SUBS),2))  VLR_NF
					,SUM(ROUND(ISNULL(s.VLR_ICMS,t.VLR_ICMS),2))  VLR_ICMS

					,0 VLR_FRETE
					,0 VLR_OUTROS
					,SUM(ROUND(ISNULL(S.VLR_BASE_ICMS,T.VLR_BASE_ICMS),2)) VLR_BASE_ICMS
					,0 ALIQ_ICMS
					,SUM(ROUND(s.VLR_IT,2)) VLR_TOT_PROD
					,s.CD_NT_MOV
					,0 VLR_DESC
					,SUM(ROUND(ISNULL(S.VLR_BASE_ST, T.VLR_BASE_SUBS),2))  VLR_BASE_ICMS_SUBS
					,SUM(ROUND(ISNULL(S.VLR_ICMS_ST, T.VLR_ICMS_SUBS),2)) VLR_ICMS_SUBS
					,0 VLR_IPI
					,@DT_EMI_NF DT_CAD
					,0 DEV
					,1 CD_VEND
					,1 CD_COND_PGTO
					,0 COB
					,0 STS_NF
					,0 TP_NF
					,'' OBS1
					,'NOTA FISCAL ORIG. NR.: ' + CONVERT(VARCHAR(16) , s.nr_nf)  OBS2
					,'' OBS3
					,'' OBS4
					,'' OBS5
					,'' OBS6
					,'' OBS7
					,'' OBS8
					,'' OBS9
					,'' OBS10
					,'' OBS11
					,0 CD_TRANSP
					,0 QT_VOL
					,'' ESP_VOL
					,'' MC_VOL
					,0 NR_VOL
					,0 PESO_VOL
					,0 PESO_LIQ_VOL
					,0 TP_FRETE
					,0 TP_DEV_NF
					,NULL DT_CANCEL
					,0 VLR_ISENTAS
					,0 VLR_OUTRAS
					,0 CD_NF_ENT
					,1 CD_USU
					,0 VLR_PROD_ICMS_SUBS
					,NULL DT_ULT_ALT
					,0 LANC_EFETU
					,'' NM_COMPRADOR
					,0 VLR_BASE_ICMS_IT
					,0 NF_PROD_SERV
					,CONVERT(CHAR(8),GETDATE(),114) HR_EMI_NF
					,0 VLR_DESC_CORPO
					,'' INF_DESCONTO
					,'' DS_TRANSP
					,'' PLACA_VEIC
					,'' PLACA_UF
					,'' CGC_CPF_TRANSP
					,'' ENDE_TRANSP
					,'' DS_CID_TRANSP
					,'' UF_TRANSP
					,'' INSC_EST_TRANSP
					,0 VLR_ISSQN
					,0 VLR_SERVICO
					,0 PERC_ISSQN
					,0 VLR_SUFRAMA
					,1 PEND_ENTRADA
					,'' NR_SELO_SAIDA
					,0 NR_FORMULARIO
					,0 CD_MT_CANCEL
					,0 CD_MOT_EMIS_NF
					,0 QT_CX_VOL
					,1 FLAG_NF_PROD_TOTAL_BRUTO
					,0 FLAG_IGNORAR_CONTROL
					,0 VLR_BASE_ICMS_RET
					,0 VLR_ICMS_RET
					,1 NF_MANUAL
					,'' BAIRRO_DESTINATARIO			
					,'' BAIRRO_EMITENTE				
					,'' CEP_DESTINATARIO			
					,'' CEP_EMITENTE				
					,'' CIDADE_DESTINATARIO			
					,'' CIDADE_EMITENTE				
					,'' CNPJ_DESTINATARIO			
					,'' CNPJ_EMITENTE				
					,'' END_DESTINATARIO			
					,'' END_EMITENTE				
					,'' INSC_ESTADUAL_DESTINATARIO	
					,'' INSC_ESTADUAL_EMITENTE		
					,'' NT_OPERACAO					
					,'' RZ_DESTINATARIO				
					,'' RZ_EMITENTE					
					,'' RZ_TRANSPORTADOR			
					,'' UF_DESTINATARIO				
					,'' UF_EMITENTE					
					,'MANUAL' APLICATIVO
					,'' VERSAO_APLICATIVO 
					,0 CD_CID_DESTINATARIO
					,0 CD_NT_OP_TRANSF_ENTRADA
					,0 FLAG_NOTA_INTEGRACAO
					,'' XPED_NOTA_FISCAL
					,0 NFE_REJEITADA
					,NULL XML_NOTA
					,0 CD_CID_EMITENTE
					,0 CD_CID_TRANSP
					,'' EMAIL_TRANSP
					,1 NOTA_FISCAL_LIVRE
					,0 VLR_ICMS_DESONERADO
					,GETDATE () DT_HR_CAD
				FROM
					EST_NF_SAI_CPL_EMISSAO s 
					LEFT OUTER JOIN EST_NF_SAI_EST_NF_SAI_CPL cpl ON
					s.CD_EMP = cpl.CD_EMP AND
					s.CD_FILIAL = cpl.CD_FILIAL AND
					s.CD_NF = cpl.CD_NF_REF

					--Nota Referencia
					LEFT OUTER JOIN EST_NF_SAI r ON
					s.CD_EMP	= r.CD_EMP AND
					s.CD_FILIAL = r.CD_FILIAL AND
					s.CD_NF		= r.CD_NF

					join GLB_MOV_OP O ON 
					S.CD_NT_MOV =O.CD_MOV_OP

					CROSS APPLY [dbo].[UFD_EST_PROD_TRIB](  s.CD_EMP,		--@CD_EMP
															s.CD_FILIAL,	--@CD_FILIAL
															s.CD_NT_MOV,	--@CD_MOV_OP
															s.CD_CLI,		--@CD_DEST_ORIG
															s.CD_PROD,		--@CD_PROD
															0,				--@GERA_DARE
															O.TP_MOV,		--@TP_MOV
															O.TP_MOV_OP_TRANSA, --@TP_MOV_OP_TRANSA
															1,				--@QT_PROD
															s.VLR_IT,		--@VLR_IT
															0,				--@PERC_DESC_IT
															0,				--@PERC_IPI
															0,				--@VLR_FRETE
															0,				--@VLR_OUTRAS
															0,				--@TIPO_OPERACAO
															0) t				--@ALIQ_ICMS_ENTRADA	


				WHERE
					s.CD_EMP =@CD_EMP
					AND s.CD_FILIAL = @CD_FILIAL
					AND s.CD_NF = @CD_NF_PROC
					AND cpl.CD_NF IS NULL
				GROUP BY
					s.CD_EMP,
					s.CD_FILIAL,
					s.CD_CLI,
					s.nr_nf,
					S.CD_NT_MOV;

			if not exists (SELECT *FROM  EST_NF_SAI 
								WHERE CD_EMP =@CD_EMP
									AND CD_FILIAL = @CD_FILIAL
									AND CD_NF = @CD_NF)
			Begin
			  Print 'Cabecalho da nota nao foi gerado Ref. ' +convert(varchar(9),@CD_NF_PROC)
			  return
			end
		
		
			UPDATE S
			SET	
				CD_CLI =R.CD_CLI,
				BAIRRO_DESTINATARIO			=ISNULL(r.BAIRRO_DESTINATARIO			,''),
				BAIRRO_EMITENTE				=ISNULL(r.BAIRRO_EMITENTE				,''),
				CEP_DESTINATARIO			=ISNULL(r.CEP_DESTINATARIO				,''),
				CEP_EMITENTE				=ISNULL(r.CEP_EMITENTE					,''),
				CIDADE_DESTINATARIO			=ISNULL(r.CIDADE_DESTINATARIO			,''),
				CIDADE_EMITENTE				=ISNULL(r.CIDADE_EMITENTE				,''),
				CNPJ_DESTINATARIO			=ISNULL(r.CNPJ_DESTINATARIO				,''),
				CNPJ_EMITENTE				=ISNULL(r.CNPJ_EMITENTE					,''),
				END_DESTINATARIO			=ISNULL(r.END_DESTINATARIO				,''),
				END_EMITENTE				=ISNULL(r.END_EMITENTE					,''),
				INSC_ESTADUAL_DESTINATARIO	=ISNULL(r.INSC_ESTADUAL_DESTINATARIO	,''),
				INSC_ESTADUAL_EMITENTE		=ISNULL(r.INSC_ESTADUAL_EMITENTE		,''),
				NT_OPERACAO					=ISNULL(r.NT_OPERACAO					,''),
				RZ_DESTINATARIO				=ISNULL(r.RZ_DESTINATARIO				,''),
				RZ_EMITENTE					=ISNULL(r.RZ_EMITENTE					,''),
				RZ_TRANSPORTADOR			=ISNULL(r.RZ_TRANSPORTADOR				,''),
				UF_DESTINATARIO				=ISNULL(r.UF_DESTINATARIO				,''),
				UF_EMITENTE					=ISNULL(r.UF_EMITENTE					,'')
						
			FROM
				EST_NF_SAI s
				--Nota Referencia
				JOIN EST_NF_SAI r ON
				s.CD_EMP	= r.CD_EMP AND
				s.CD_FILIAL = r.CD_FILIAL 
			where
				s.cd_emp=@cd_emp 
				and s.CD_FILIAL=@cd_filial
				AND s.CD_NF = @CD_NF 
				and r.CD_NF =@CD_NF_PROC;

		
			 print 'Gerando o vinculo do cliente com a nota fiscal'

			INSERT INTO EST_NF_SAI_RC_CLI (CD_EMP,CD_FILIAL,CD_NF,CD_CLI)
			SELECT
				TOP 1 s.CD_EMP,
				s.CD_FILIAL,
				@CD_NF AS CD_NF,
				s.CD_CLI 
			FROM
				EST_NF_SAI_CPL_EMISSAO s 
				LEFT OUTER JOIN EST_NF_SAI_EST_NF_SAI_CPL cpl ON
				s.CD_EMP 	= cpl.CD_EMP AND
				s.CD_FILIAL = cpl.CD_FILIAL AND
				s.CD_NF 	= cpl.CD_NF_REF
			WHERE
				s.CD_EMP =@CD_EMP
				AND s.CD_FILIAL =@CD_FILIAL
				AND s.CD_NF = @CD_NF_PROC
				AND cpl.CD_NF IS NULL;

		
	/******************************************************************************************************************/
			 Print 'Gerando nota fiscal de referencia';

			INSERT INTO EST_NF_SAI_DEVOLUCAO_NFE_CHAVE_REFERENCIA (CD_EMP,CD_FILIAL,CD_NF,NR_AUTORIZADOR,NR_ECF,CD_CX,MODELO,CD_UF,ANO_MES_EMI,CNPJ,SERIE,NR_NF)
			SELECT
				TOP 1 s.CD_EMP,
				s.CD_FILIAL,
				@CD_NF AS CD_NF,
				nfe.NR_AUTORIZADOR,
				0 AS NR_ECF,
				0 AS CD_CX,
				'' MODELO,
				0 AS CD_UF,
				0 AS ANO_MES_EMI,
				'' AS CNPJ,
				0 AS SERIE,
				0 AS NR_NF
			FROM
				EST_NF_SAI_CPL_EMISSAO s INNER JOIN EST_NF_SAI_NFE nfe ON
				s.CD_EMP = nfe.CD_EMP AND
				s.CD_FILIAL = nfe.CD_FILIAL AND
				s.CD_NF = nfe.CD_NF 
				LEFT OUTER JOIN EST_NF_SAI_EST_NF_SAI_CPL cpl ON
				s.CD_EMP = cpl.CD_EMP AND
				s.CD_FILIAL = cpl.CD_FILIAL AND
				s.CD_NF = cpl.CD_NF_REF
			WHERE
				s.CD_EMP =@CD_EMP
				AND s.CD_FILIAL =@CD_FILIAL
				AND s.CD_NF = @CD_NF_PROC
				AND cpl.CD_NF IS NULL

		
	/******************************************************************************************************************/
	--Gerando os itens da nota fiscal

			Print 'Itens da nota fiscal ';

			INSERT INTO EST_NF_SAI_IT (CD_EMP,CD_FILIAL,CD_NF,CD_PROD,DS_PROD,VLR_IT,QT_IT,VLR_ICMS_IT,ALIQ_ICMS,CD_TRIB,UN,VLR_CUSTO,VLR_BASE_SUBS,VLR_ICMS_SUBS,QT_EST_ANT
											,CD_NT_OP,VLR_BASE_ICMS_IT,VLR_PROD_PED,VLR_TOT_IT,VLR_SUFRAMA,VLR_ISENTO,VLR_OUTROS,PERC_DESC_IT,VLR_DESC_IT,PERC_DESC_CTR,PERC_COMI,PERC_COMI_CTR,CD_IT,PIS_COFINS
											,VLR_PMC,PERC_REDU_BASE_SUBS,PERC_REDU_BASE_ICMS,VLR_PROD_FRETE,LISTA_PROD,VLR_BASE_PIS,VLR_PIS,PERC_ALIQ_PIS,VLR_BASE_COFINS,VLR_COFINS,PERC_ALIQ_COFINS,VLR_BASE_ICMS_RET,VLR_ICMS_RET
											,VLR_OUTROS_DESPESAS,CD_SIT_OPERA_SIMPLES_NACIO,MODALI_BASE_ICMS_SUBST,MODALI_BASE_ICMS,VLR_CRED_BASE_SN,VLR_CRED_ICMS_SN,PIS_COFINS_RECALCULADO,CST_PIS,CST_COFINS,DT_EMI_NF,PERC_IVA,TP_PROD,VLR_CUSTO_MD
											,ALIQ_ICMS_INTERNO,TIPO_TRIBUTACAO_PIS_COFINS,FLAG_EXCECAO_PIS_COFINS,SIGLA_EXCECAO_PIS_COFINS,XPED_IT,N_ITEM_PED,FLAG_PROD_CESTA_BASICA,VLR_CUSTO_GERENCIAL_TRANSF,QTDE_PROD_DEVOLUCAO
											,PERC_IPI,VLR_RESSARCIMENTO_ICMS,IND_TOTAL,VLR_CUSTO_ACERTO,VLR_ICMS_DESONERADO,ICMS_DESONERADO_MOTIVO,PERC_ICMS_DESTINO_DIFAL,PERC_ICMS_ORIGEM_DIFAL,GERAR_DIFAL,FECOP_RECALCULADO
											,PERC_FECOP,VLR_BASE_ICMS_OP,VLR_ICMS_OP,PERC_ICMS_OP,PERC_DIFERIMENTO,VLR_ICMS_DIFERIMENTO,VLR_COMISSAO_FABRIC,DS_PERC_COMIS,DS_VLR_COMIS,PERC_ICMS_LUCRO_REAL
											,VLR_ICMS_LUCRO_REAL,VLR_BASE_ICMS_LUCRO_REAL)
			SELECT
				s.CD_EMP
				,s.CD_FILIAL
				,@CD_NF
				,s.CD_PROD
				,LEFT(DS_PROD,50) DS_PROD
				,s.VLR_IT
				, 0 QT--ISNULL(S.QT_IT, 0) QT_IT
				,0 -- ROUND(ISNULL(S.VLR_ICMS, T.VLR_ICMS),2) VLR_ICMS_IT
				,0 --ISNULL(S.PERC_ICMS, t.PERC_ICMS) ALIQ_ICMS
				,ISNULL(S.CD_TRIB_FC, T.CD_TRIB)
				,'UN' UN
				,s.VLR_IT VLR_CUSTO
				,ROUND(ISNULL(S.VLR_BASE_ST, T.VLR_BASE_SUBS),2) VLR_BASE_SUBS
				,ROUND(ISNULL(S.VLR_ICMS_ST, T.VLR_ICMS_SUBS),2)  VLR_ICMS_SUBS
				,0 QT_EST_ANT
				, CD_NT_OP
				,ROUND(ISNULL(S.VLR_BASE_ICMS, T.VLR_BASE_ICMS),2) VLR_BASE_ICMS_IT
				,s.VLR_IT VLR_PROD_PED
				,s.VLR_IT  VLR_TOT_IT
				,0 VLR_SUFRAMA
				,0 VLR_ISENTO
				,0 VLR_OUTROS
				,0 PERC_DESC_IT
				,0 VLR_DESC_IT
				,0 PERC_DESC_CTR
				,0 PERC_COMI
				,0 PERC_COMI_CTR
				,ROW_NUMBER() OVER (PARTITION BY s.CD_EMP, 
												s.CD_FILIAL
												ORDER BY S.CD_EMP, 
												S.CD_PROD)  CD_IT
				,0 PIS_COFINS
				,0 VLR_PMC
				--,ISNULL(S.RED_BC_ST ,T.PERC_RED_SUBS_TRIB) PERC_REDU_BASE_SUBS
				--,ISNULL(S.RED_BC_ICMS ,T.PERC_RED) PERC_REDU_BASE_ICMS
				,T.PERC_RED_SUBS_TRIB PERC_REDU_BASE_SUBS
				,T.PERC_RED PERC_REDU_BASE_ICMS
			
				,0 VLR_PROD_FRETE
				,0 LISTA_PROD
				, ROUND(VLR_BASE_PIS,2) VLR_BASE_PIS
				, ROUND(ISNULL(S.VLR_PIS, T.VLR_PIS),2) VLR_PIS
				, PERC_PIS PERC_ALIQ_PIS
				, ROUND(VLR_BASE_COFINS,2) VLR_BASE_COFINS
				, ROUND(ISNULL(S.VLR_COFINS, T.VLR_COFINS),2) VLR_COFINS
				, PERC_COFINS PERC_ALIQ_COFINS
				,0 VLR_BASE_ICMS_RET
				,0 VLR_ICMS_RET
				,0 VLR_OUTROS_DESPESAS
				,0 CD_SIT_OPERA_SIMPLES_NACIO
				,0 MODALI_BASE_ICMS_SUBST
				,0 MODALI_BASE_ICMS
				,0 VLR_CRED_BASE_SN
				,0 VLR_CRED_ICMS_SN
				,1 PIS_COFINS_RECALCULADO
				,CST_PIS
				,CST_COFINS
				,@DT_EMI_NF
				,ISNULL(s.IVA, T.VLR_PAUTA_IVA) PERC_IVA
				,0 TP_PROD
				,s.VLR_IT VLR_CUSTO_MD
				,S.PERC_ICMS ALIQ_ICMS_INTERNO
				,0 TIPO_TRIBUTACAO_PIS_COFINS
				,0 FLAG_EXCECAO_PIS_COFINS
				,0 SIGLA_EXCECAO_PIS_COFINS
				,0 XPED_IT
				,0 N_ITEM_PED
				,0 FLAG_PROD_CESTA_BASICA
				,0 VLR_CUSTO_GERENCIAL_TRANSF
				,0 QTDE_PROD_DEVOLUCAO
				,0 PERC_IPI
				,0 VLR_RESSARCIMENTO_ICMS
				,0 IND_TOTAL
				,0 VLR_CUSTO_ACERTO
				,0 VLR_ICMS_DESONERADO
				,0 ICMS_DESONERADO_MOTIVO
				,0 PERC_ICMS_DESTINO_DIFAL
				,0 PERC_ICMS_ORIGEM_DIFAL
				,0 GERAR_DIFAL
				,0 FECOP_RECALCULADO
				,0 PERC_FECOP
				,0 VLR_BASE_ICMS_OP
				,0 VLR_ICMS_OP
				,0 PERC_ICMS_OP
				,0 PERC_DIFERIMENTO
				,0 VLR_ICMS_DIFERIMENTO
				,0 VLR_COMISSAO_FABRIC
				,'' DS_PERC_COMIS
				,'' DS_VLR_COMIS 
				,0 PERC_ICMS_LUCRO_REAL
				,0 VLR_ICMS_LUCRO_REAL
				,0 VLR_BASE_ICMS_LUCRO_REAL
			FROM
				EST_NF_SAI_CPL_EMISSAO s INNER JOIN EST_PROD p ON
				s.CD_EMP = p.CD_EMP AND
				s.CD_PROD =p.CD_PROD
				LEFT OUTER JOIN EST_NF_SAI_EST_NF_SAI_CPL cpl ON
				s.CD_EMP = cpl.CD_EMP AND
				s.CD_FILIAL = cpl.CD_FILIAL AND
				s.CD_NF = cpl.CD_NF_REF
				CROSS APPLY [dbo].[UFD_EST_PROD_TRIB](  s.CD_EMP,
														s.CD_FILIAL,
														s.CD_NT_MOV,
														s.CD_CLI,
														s.CD_PROD,
														0,
														0,
														0,
														1,
														s.VLR_IT,
														0,
														0,
														0,
														0,
														0,				--@TIPO_OPERACAO
														0)	T			--@ALIQ_ICMS_ENTRADA														
			WHERE
				s.CD_EMP =@CD_EMP
				AND s.CD_FILIAL =@CD_FILIAL
				AND s.CD_NF = @CD_NF_PROC
				and cpl.CD_NF is null
		   Print convert(varchar, @@rowcount) + ' Produtos inseridos..'
		
	/******************************************************************************************************************/

	--Gerando o vinculo da nota fiscal de origem com a nota complementar
			INSERT INTO EST_NF_SAI_EST_NF_SAI_CPL (CD_EMP,CD_FILIAL,CD_NF,CD_NF_REF)
			SELECT
				TOP 1 s.CD_EMP,
				s.CD_FILIAL,
				@CD_NF AS CD_NF,
				s.CD_NF
			FROM
				EST_NF_SAI_CPL_EMISSAO s LEFT OUTER JOIN EST_NF_SAI_EST_NF_SAI_CPL cpl ON
				s.CD_EMP = cpl.CD_EMP AND
				s.CD_FILIAL = cpl.CD_FILIAL AND
				s.CD_NF = cpl.CD_NF_REF
			WHERE
				s.CD_EMP =@CD_EMP
				AND s.CD_FILIAL =@CD_FILIAL
				AND s.CD_NF = @CD_NF_PROC
				AND cpl.CD_NF IS NULL
		
		commit transaction;

		Print 'Emitida a nota Nr ' + convert(varchar(20),@NR_NF) + ' Filial ' + convert(varchar(20),@CD_FILIAL)

		
	end try 
	begin catch
		
		rollback transaction;
		
		declare @mensagemErro varchar(500)
		set @mensagemErro ='Falha na geracao da NF ' + ERROR_MESSAGE() 
		RAISERROR (@mensagemErro, 16,0);

		RETURN -2;	
	end catch

/******************************************************************************************************************/
end
else
	begin
		print 'Nota fical complementar já emitida anteriormente..'
	end
end
/*
update
est_nf_sai_IT
set 
vlr_icms_it=round(vlr_icms_it,2),
vlr_base_subs=round(vlr_base_subs,2),
vlr_icms_subs=round(vlr_icms_subs,2),
vlr_base_icms_it=round(vlr_base_icms_it,2)
where
cd_emp =1
and cd_filial = 999
and cd_nf =2212343
select
*
,VLR_TOT_PROD + VLR_ICMS_SUBS
from
est_nf_sai
where
cd_emp =1
and cd_filial = 999
and cd_nf =2212381
select
sum(vlr_icms_it),
sum(vlr_base_subs),
sum(vlr_icms_subs),
sum(vlr_base_icms_it)
from
est_nf_sai_IT
where
cd_emp =1
and cd_filial = 999
and cd_nf =2212381



update d
set
	cd_cli= c.cd_cli
FROM
     EST_NF_SAI_CPL_EMISSAO s
	JOIN EST_NF_SAI_EST_NF_SAI_CPL cpl ON s.CD_EMP = cpl.CD_EMP
		AND s.CD_FILIAL = cpl.CD_FILIAL
		AND s.CD_NF = cpl.CD_NF_REF

		join prc_filial_rc_cli c on 
		s.cd_cli =c.cd_filial
		join EST_NF_SAI_RC_CLI d on 
		d.CD_EMP	= cpl.cd_emp and
		d.CD_FILIAL	= cpl.cd_filial and
		d.CD_NF		= cpl.cd_nf
		and d.cd_cli <> c.cd_cli
WHERE s.dt_emissao_nova = '2020-01-31'
      AND s.cd_emp = 1
      AND s.CD_FILIAL = 90;
	  

*/





GO




