/****** Object:  UserDefinedFunction [dbo].[UFD_EST_NF_SAI_XML_GRAVACAO_VLR_SUBS]    Script Date: 04/29/2014 23:12:42 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UFD_EST_NF_SAI_XML_GRAVACAO_VLR_SUBS]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[UFD_EST_NF_SAI_XML_GRAVACAO_VLR_SUBS]
GO 
--SELECT * FROM dbo.UFD_EST_NF_SAI_XML_GRAVACAO_VLR_SUBS (999,2067314)
CREATE FUNCTION   [dbo].[UFD_EST_NF_SAI_XML_GRAVACAO_VLR_SUBS ](@CD_FILIAL INT,  @CD_NF INT, @CAB_IT INT=0)  

RETURNS @RS_TEMP TABLE(  CD_PROD INT,   
							CST  VARCHAR(5),
							VLR_BASE_ICMS MONEY,
							VLR_ICMS MONEY,

							VLR_BASE_SUBS MONEY,
							VLR_ICMS_SUBS MONEY,
							 
							VLR_TOT_PROD MONEY,
							VLR_NF MONEY)
AS  
 BEGIN
   
	DECLARE @Nfe  AS xml 
	SET @Nfe=(SELECT CONVERT(xml, ARQUIVO_DANFE)
	FROM 
		EST_NF_SAI_ARQ_DANFE 
	WHERE 
		CD_EMP= 1
		AND CD_FILIAL=@CD_FILIAL 
		AND CD_NF=@CD_NF)  

	IF @CAB_IT =0 --ITENS
	BEGIN
	;WITH XMLNAMESPACES(DEFAULT 'http://www.portalfiscal.inf.br/nfe') 
	INSERT INTO @RS_TEMP
	SELECT
		NFe.value('prod[1]/cProd [1]', 'int') AS CD_PROD
 
		, ISNULL(NFe.value('(imposto[1]/ICMS[1]/*/CST) [1]', 'varchar(5)'),0) AS CST

		, ISNULL(NFe.value('(imposto[1]/ICMS[1]/*/vBC) [1]', 'money'),0) AS VLR_BASE_ICMS
		, ISNULL(NFe.value('(imposto[1]/ICMS[1]/*/vICMS) [1]', 'money'),0) AS VLR_ICMS

		, ISNULL(NFe.value('(imposto[1]/ICMS[1]/*/vBCST) [1]', 'money'),0) AS VLR_BASE_ST
		, ISNULL(NFe.value('(imposto[1]/ICMS[1]/*/vICMSST) [1]', 'money'),0) AS VLR_ICMS_ST

		,0 AS VLR_TOT_PROD
		,0 AS VLR_NF


	FROM 
		@Nfe.nodes('//infNFe/det') AS NFes(NFe)  

	END
	ELSE
	BEGIN

	;WITH XMLNAMESPACES(DEFAULT 'http://www.portalfiscal.inf.br/nfe') 
	INSERT INTO @RS_TEMP
	
	SELECT
		0 AS CD_PROD		
		,'CAB' AS CST
		, NFe.value('(ICMSTot/vBC) [1]', 'money') AS VLR_BASE_ICMS
		, NFe.value('(ICMSTot/vICMS) [1]', 'money') AS VLR_ICMS
		, NFe.value('(ICMSTot/vBCST) [1]', 'money') AS VLR_BASE_SUBS
		, NFe.value('(ICMSTot/vST) [1]', 'money') AS VLR_ICMS_SUBS
		, NFe.value('(ICMSTot/vProd) [1]', 'money') AS VLR_TOT_PROD
		, NFe.value('(ICMSTot/vNF) [1]', 'money') AS VLR_NF

	FROM 
		@Nfe.nodes('//infNFe/total') AS NFes(NFe) 
	END

	RETURN;
END