SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_ENCABEZADO_COMPROBANTES_SAT]
@FECHA_I nvarchar(50), @FECHA_F nvarchar(50), @ORIGEN nvarchar(MAX), @TDOCUMENTO nvarchar(MAX), @RFCRECEPTOR nvarchar(MAX), @RFCEMISOR nvarchar(MAX)
AS
BEGIN
DECLARE @FILTER NVARCHAR(200)
DECLARE @QUERY VARCHAR(MAX)
DECLARE @BOOK NVARCHAR(60)
DECLARE @GMAESTRA NVARCHAR(60)
SET @BOOK = (SELECT CVE_BLOQ FROM DBCONF_BLOQ WHERE DES_BLOQ LIKE 'GCOMPROBANTESATENC')








CREATE TABLE #NUM_DOC (
ID_NUM_DOC INT PRIMARY KEY
)
CREATE TABLE #DesDec (
ID_NUM_DOC INT PRIMARY KEY,
Concepto varchar(max)







)
CREATE TABLE #IMPUESTO (
NUM_DOC INT PRIMARY KEY,
ISR_R FLOAT,
IVA_R FLOAT,
IEPS_T FLOAT,
IVA_T FLOAT





)





-- EXEC(N'INSERT INTO #IMPUESTO (NUM_DOC,ISR_R, IVA_R,IEPS_T,IVA_T)
--SELECT IT.IdComprobante, Sum(CASE WHEN IR.IMPUESTO =''ISR''THEN IR.IMPORTE ELSE 0 END)AS ''ISR_R'',
--sum(CASE WHEN IR.IMPUESTO =''IVA''THEN IR.IMPORTE ELSE 0 END) AS ''IVA_R'',
--SUM(CASE WHEN IT.IMPUESTO =''IEPS''THEN IT.IMPORTE ELSE 0 END) AS ''IEPS_T'',
--SUM(CASE WHEN IT.IMPUESTO =''IVA''THEN IT.IMPORTE ELSE 0 END) AS ''IVA_T'' FROM gMaestraSAT M
--LEFT JOIN AUD_IMPUESTOSRETENCIONES IR ON IR.IDCOMPROBANTE = M.NUM_DOC
--LEFT JOIN AUD_IMPUESTOSTRASLADOS IT ON IT.IDCOMPROBANTE = M.NUM_DOC
--WHERE IT.IdComprobante IN(SELECT ID_NUM_DOC FROM #NUM_DOC)
--GROUP BY IT.IdComprobante ' )






IF (@ORIGEN <> NULL OR @ORIGEN <> '')







BEGIN
SET @FILTER = ''
IF (@TDOCUMENTO <> NULL OR @TDOCUMENTO <>'')
BEGIN
IF(@RFCEMISOR <> NULL OR @RFCEMISOR <>'')
BEGIN
SET @FILTER =' AND RFCEMISORSAT ='''+@RFCEMISOR+''''
END
IF(@RFCRECEPTOR <> NULL OR @RFCRECEPTOR <>'')
BEGIN
SET @FILTER =' AND RFCRECEPTORSAT ='''+@RFCRECEPTOR+''''
END
exec( N'INSERT INTO #NUM_DOC (ID_NUM_DOC ) SELECT NUM_DOC FROM GMAESTRASAT WHERE Num_doc > 0 and ORIGENSAT ='''+@ORIGEN +''' AND TipoComprobanteSAT ='''+@TDocumento +''' AND DATEADD(DD,0,DATEDIFF(DD,0,FechaFacturacionSAT)) BETWEEN DATEADD(DD,0,DATEDIFF(DD,0,'''+@FECHA_I+''')) AND DATEADD(DD,0,DATEDIFF(DD,0,'''+@FECHA_F+''')) ' +@FILTER )
exec(N'INSERT INTO #DesDec (ID_NUM_DOC,CONCEPTO ) SELECT en.IDGMAESTRASAT ,STUFF(( SELECT '' | ''+det.descripcion FROM dbdet'+@BOOK+' det WHERE det.num_doc = en.num_doc FOR XML PATH('''') ),1,1,'''')
FROM DbENC'+@BOOK+' en WHERE EN.IDGMAESTRASAT IN(SELECT ID_NUM_DOC FROM #NUM_DOC) ')
--/////////////////////////////////Columnas IEPS///////////////
select CONCAT('[',TASA ,']' ) AS TASAS INTO #TEST FROM AUD_IMPUESTOSTRASLADOS WHERE impuesto LIKE 'IEPS' AND IDCOMPROBANTE IN(SELECT ID_NUM_DOC FROM #NUM_DOC)
select ROUND(tasa,3) AS IMPORTE1, * INTO #IMPTRASLADO FROM AUD_IMPUESTOSTRASLADOS WHERE impuesto LIKE 'IEPS' AND IDCOMPROBANTE IN(SELECT ID_NUM_DOC FROM #NUM_DOC) --order by tasa desc
declare @total int
select @total = count(*) FROM #TEST
if(@total>0)
begin





DECLARE @PRUEBA NVARCHAR(MAX)
SET @PRUEBA = ''





SELECT @PRUEBA =@PRUEBA +T.TASAS+',' FROM( SELECT DISTINCT TASAS AS TASAS FROM #TEST ) AS T order by tasas asc
set @PRUEBA = left(@PRUEBA , len( @PRUEBA)-1)
--PRINT @PRUEBA





EXEC('select * INTO ##PROSENTAJES FROM #IMPTRASLADO R
pivot(
SUM(R.IMPORTE)
for IMPORTE1 in('+@PRUEBA+')





) as PVT;



')
DECLARE @NomColum NVARCHAR(MAX)
SET @NomColum = ''





SELECT @NomColum =@NomColum + 'MAX(ISNULL(P.'+T.TASAS +',0)) AS ''IEPS_'+T.TASAS +''' ,'
FROM( SELECT DISTINCT TASAS AS TASAS FROM #TEST ) AS T
--////////////////////////////////////////////////////////////
EXEC(N'INSERT INTO #IMPUESTO (NUM_DOC,ISR_R, IVA_R,IEPS_T,IVA_T)
SELECT IT.IdComprobante, Sum(CASE WHEN IR.IMPUESTO =''ISR''THEN IR.IMPORTE ELSE 0 END)AS ''ISR_R'',
sum(CASE WHEN IR.IMPUESTO =''IVA''THEN IR.IMPORTE ELSE 0 END) AS ''IVA_R'',
SUM(CASE WHEN IT.IMPUESTO =''IEPS''THEN IT.IMPORTE ELSE 0 END) AS ''IEPS_T'',
SUM(CASE WHEN IT.IMPUESTO =''IVA''THEN IT.IMPORTE ELSE 0 END) AS ''IVA_T'' FROM gMaestraSAT M
LEFT JOIN AUD_IMPUESTOSRETENCIONES IR ON IR.IDCOMPROBANTE = M.NUM_DOC
LEFT JOIN AUD_IMPUESTOSTRASLADOS IT ON IT.IDCOMPROBANTE = M.NUM_DOC
WHERE IT.IdComprobante IN(SELECT ID_NUM_DOC FROM #NUM_DOC)
GROUP BY IT.IdComprobante ' )





EXEC( N'SELECT
M.NUM_DOC AS ''ID'',
CASE WHEN ISNULL(M.CANCELADOSAT,0) = 0 THEN ''Vigente'' else ''Cancelado''end AS ''ESTATUS'' ,
ENC.TIPORELACIONSAT AS ''TIPO RELACION CFDI'',
ENC.CFDIRELACIONADOUUIDSAT AS '' CFDI RELACIONADO'',
M.TIPOCOMPROBANTESAT AS ''TIPO'',
M.UUIDSAT AS ''UUID'',
M.SERIESAT AS ''SERIE'',
M.FOLIOSAT AS ''FOLIIO'',
ENC.RECEPTORUSOCFDISAT AS ''USO CFDI'',
M.RFCEMISORSAT AS ''RFC EMISOR'',
M.RAZONSOCIALEMISORSAT AS ''RAZON SOCIAL EMISOR'',
ENC.EmisorCodigoPostalSAT AS ''Emisor Codigo Postal'',
M.RFCRECEPTORSAT AS ''RFC RECEPTOR'',
M.RAZONSOCIALRECEPTORSAT AS ''RAZON SOCIAL RECEPTOR'',
ENC.ReceptorCodigoPostalSAT as ''Receptor Codigo Postal'',
DD.CONCEPTO AS CONCEPTOS,
M.SUBTOTALSAT AS ''SUBTOTAL'',
sum( CASE WHEN DET.TRASLADOIMPUESTO=002 AND DET.TRASLADOTASAOCUOTA = 0 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IVA 0'',
sum(CASE WHEN DET.TRASLADOIMPUESTO=002 AND DET.TRASLADOTASAOCUOTA = 0.08 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IVA 8'',
sum(CASE WHEN DET.TRASLADOIMPUESTO=002 AND DET.TRASLADOTASAOCUOTA = 0.16 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IVA 16'',
SUM (CASE WHEN DET.TRASLADOIMPUESTO = 002 AND DET.TRASLADOTIPOFACTOR=''EXENTO'' THEN DET.TRASLADOBASE ELSE 0 END) AS ''IVA EXENTO'',
M.DESCUENTOSAT AS ''DESCUENTO'',
Sum(I.ISR_R)AS ''ISR RETENIDO'',
sum(I.IVA_R) AS ''IVA RETENIDO'',
max(I.IEPS_T) AS ''IEPS TRASLADADO'',
max(I.IVA_T) AS ''IVA TRASLADADO'',
SUM(CASE WHEN DET.TRASLADOIMPUESTO=003 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IEPS'' ,
M.IVASAT AS ''IVA'',
M.TOTALSAT AS ''TOTAL'',
M.MONEDASAT AS ''MONEDA'',
M.TIPOCAMBIOSAT AS '' TIPO CAMBIO'',
M.FORMADEPAGOSAT AS ''FORMA DE PAGO'',
M.METODODEPAGOSAT AS ''METODO DE PAGO'',
M.AGRUPACIONIDSAT AS ''AGRUPACION ID'',
M.ANIOSAT AS ''ANIO'',
M.MESSAT AS ''MES'',
M.CANCELADOSAT AS ''CANCELADO'',
M.CANCELADOCONTABILIZADOSAT AS ''CANCELADO CONTABILIZADO'',
M.CLAVENOMINASAT AS ''CLAVE NOMINA '',
M.PERIODICIDADPAGOSAT AS ''PERIODO DE PAGO'',
M.TIPONOMINASAT AS ''TIPO NOMINA'',
M.FECHAFACTURACIONSAT AS ''FECHA FACTURACION'',
M.FECHAGENERACIONPOLIZASAT AS ''FECHA GENERACION POLIZA'',
M.FECHAPAGOSAT AS ''FECHA PAGO'',
M.FECHATIMBRADOSAT AS ''FECHA TIMBRADO '',
M.IDPDFSAT AS ''ID PDF'',
M.INFORMACIONSAT AS ''INFORMACION'',
M.ORIGENSAT AS ''ORIGEN'',
M.ACUSECANCELADOSAT AS ''ACUSE CANCELADO'',
M.ISHPORCENTAJESAT AS ''ISH PORCENTAJE'',ISHIMPORTESAT AS ''ISH IMPORTE'',
M.VERSIONSAT AS ''VERSION'','+@NomColum+'
M.FECHAACTUALIZACIONSAT AS ''FECHA ACTUALIZACION''
FROM GMAESTRASAT M
LEFT JOIN DBENC'+@BOOK+' ENC ON M.NUM_DOC=ENC.IDGMAESTRASAT ---GCOMPROBANTESATENC
LEFT JOIN DBDET'+@BOOK+' DET ON ENC.NUM_DOC=DET.NUM_DOC ---
LEFT JOIN #DesDec DD ON DD.ID_NUM_DOC = M.NUM_DOC
LEFT JOIN #IMPUESTO I ON I.NUM_DOC = M.NUM_DOC
INNER JOIN #NUM_DOC ND on ND.ID_NUM_DOC=M.NUM_DOC
LEFT JOIN ##PROSENTAJES P ON P.IDCOMPROBANTE = M.NUM_DOC
WHERE LEN(M.UUIDSAT) = 36 AND M.NUM_DOC IN(SELECT * FROM #NUM_DOC)







group by
M.Num_doc,
M.AgrupacionIdSAT,
M.AnioSAT,
M.CanceladoSAT,
M.CanceladoContabilizadoSAT,
M.ClaveNominaSAT,
M.FechaFacturacionSAT,
M.FechaGeneracionPolizaSAT,
M.FechaPagoSAT,
M.FechaTimbradoSAT,
M.FolioSAT,
M.IdPDFSAT,
M.InformacionSAT,
M.IsValidSAT,
M.IvaSAT,
M.MesSAT,
M.MonedaSAT,
M.OrigenSAT,
M.PeriodicidadPagoSAT,
M.RazonSocialEmisorSAT,
M.RazonSocialReceptorSAT,
M.RFCEmisorSAT,
ENC.EmisorCodigoPostalSAT,
M.RFCReceptorSAT,
ENC.ReceptorCodigoPostalSAT,
DD.CONCEPTO,
M.SerieSAT,
M.SubtotalSAT,
M.TipoComprobanteSAT,
M.TipoCambioSAT,
M.TipoNominaSAT,
M.TotalSAT,
M.UUIDSAT,
M.AcuseCanceladoSAT,
M.DescuentoSAT,
M.ISHPorcentajeSAT,ISHImporteSAT,
M.VersionSAT,
M.FormaDePagoSAT,
M.MetodoDePagoSAT,
M.FechaIniSAT,
M.FechaActualizacionSAT,
ENC.TipoRelacionSAT,
ENC.CfdiRelacionadoUUIDSAT,
ENC.ReceptorUsoCFDISAT
ORDER BY M.Num_doc')
drop table ##PROSENTAJES
end
else
begin
EXEC(N'INSERT INTO #IMPUESTO (NUM_DOC,ISR_R, IVA_R,IEPS_T,IVA_T)
SELECT IT.IdComprobante, Sum(CASE WHEN IR.IMPUESTO =''ISR''THEN IR.IMPORTE ELSE 0 END)AS ''ISR_R'',
sum(CASE WHEN IR.IMPUESTO =''IVA''THEN IR.IMPORTE ELSE 0 END) AS ''IVA_R'',
SUM(CASE WHEN IT.IMPUESTO =''IEPS''THEN IT.IMPORTE ELSE 0 END) AS ''IEPS_T'',
SUM(CASE WHEN IT.IMPUESTO =''IVA''THEN IT.IMPORTE ELSE 0 END) AS ''IVA_T'' FROM gMaestraSAT M
LEFT JOIN AUD_IMPUESTOSRETENCIONES IR ON IR.IDCOMPROBANTE = M.NUM_DOC
LEFT JOIN AUD_IMPUESTOSTRASLADOS IT ON IT.IDCOMPROBANTE = M.NUM_DOC
WHERE IT.IdComprobante IN(SELECT ID_NUM_DOC FROM #NUM_DOC)
GROUP BY IT.IdComprobante ' )





EXEC( N'SELECT
M.NUM_DOC AS ''ID'',
CASE WHEN ISNULL(M.CANCELADOSAT,0) = 0 THEN ''Vigente'' else ''Cancelado''end AS ''ESTATUS'' ,
ENC.TIPORELACIONSAT AS ''TIPO RELACION CFDI'',
ENC.CFDIRELACIONADOUUIDSAT AS '' CFDI RELACIONADO'',
M.TIPOCOMPROBANTESAT AS ''TIPO'',
M.UUIDSAT AS ''UUID'',
M.SERIESAT AS ''SERIE'',
M.FOLIOSAT AS ''FOLIIO'',
ENC.RECEPTORUSOCFDISAT AS ''USO CFDI'',
M.RFCEMISORSAT AS ''RFC EMISOR'',
M.RAZONSOCIALEMISORSAT AS ''RAZON SOCIAL EMISOR'',
ENC.EmisorCodigoPostalSAT AS ''Emisor Codigo Postal'',
M.RFCRECEPTORSAT AS ''RFC RECEPTOR'',
M.RAZONSOCIALRECEPTORSAT AS ''RAZON SOCIAL RECEPTOR'',
ENC.ReceptorCodigoPostalSAT as ''Receptor Codigo Postal'',
DD.CONCEPTO AS CONCEPTOS,
M.SUBTOTALSAT AS ''SUBTOTAL'',
sum( CASE WHEN DET.TRASLADOIMPUESTO=002 AND DET.TRASLADOTASAOCUOTA = 0 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IVA 0'',
sum(CASE WHEN DET.TRASLADOIMPUESTO=002 AND DET.TRASLADOTASAOCUOTA = 0.08 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IVA 8'',
sum(CASE WHEN DET.TRASLADOIMPUESTO=002 AND DET.TRASLADOTASAOCUOTA = 0.16 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IVA 16'',
SUM (CASE WHEN DET.TRASLADOIMPUESTO = 002 AND DET.TRASLADOTIPOFACTOR=''EXENTO'' THEN DET.TRASLADOBASE ELSE 0 END) AS ''IVA EXENTO'',
M.DESCUENTOSAT AS ''DESCUENTO'',
Sum(I.ISR_R)AS ''ISR RETENIDO'',
sum(I.IVA_R) AS ''IVA RETENIDO'',
max(I.IEPS_T) AS ''IEPS TRASLADADO'',
max(I.IVA_T) AS ''IVA TRASLADADO'',
SUM(CASE WHEN DET.TRASLADOIMPUESTO=003 THEN DET.TRASLADOBASE ELSE 0 END) AS ''BASE IEPS'' ,
M.IVASAT AS ''IVA'',
M.TOTALSAT AS ''TOTAL'',
M.MONEDASAT AS ''MONEDA'',
M.TIPOCAMBIOSAT AS '' TIPO CAMBIO'',
M.FORMADEPAGOSAT AS ''FORMA DE PAGO'',
M.METODODEPAGOSAT AS ''METODO DE PAGO'',
M.AGRUPACIONIDSAT AS ''AGRUPACION ID'',
M.ANIOSAT AS ''ANIO'',
M.MESSAT AS ''MES'',
M.CANCELADOSAT AS ''CANCELADO'',
M.CANCELADOCONTABILIZADOSAT AS ''CANCELADO CONTABILIZADO'',
M.CLAVENOMINASAT AS ''CLAVE NOMINA '',
M.PERIODICIDADPAGOSAT AS ''PERIODO DE PAGO'',
M.TIPONOMINASAT AS ''TIPO NOMINA'',
M.FECHAFACTURACIONSAT AS ''FECHA FACTURACION'',
M.FECHAGENERACIONPOLIZASAT AS ''FECHA GENERACION POLIZA'',
M.FECHAPAGOSAT AS ''FECHA PAGO'',
M.FECHATIMBRADOSAT AS ''FECHA TIMBRADO '',
M.IDPDFSAT AS ''ID PDF'',
M.INFORMACIONSAT AS ''INFORMACION'',
M.ORIGENSAT AS ''ORIGEN'',
M.ACUSECANCELADOSAT AS ''ACUSE CANCELADO'',
M.ISHPORCENTAJESAT AS ''ISH PORCENTAJE'',ISHIMPORTESAT AS ''ISH IMPORTE'',
M.VERSIONSAT AS ''VERSION'',
M.FECHAACTUALIZACIONSAT AS ''FECHA ACTUALIZACION''
FROM GMAESTRASAT M
LEFT JOIN DBENC'+@BOOK+' ENC ON M.NUM_DOC=ENC.IDGMAESTRASAT ---GCOMPROBANTESATENC
LEFT JOIN DBDET'+@BOOK+' DET ON ENC.NUM_DOC=DET.NUM_DOC ---
LEFT JOIN #DesDec DD ON DD.ID_NUM_DOC = M.NUM_DOC
LEFT JOIN #IMPUESTO I ON I.NUM_DOC = M.NUM_DOC
INNER JOIN #NUM_DOC ND on ND.ID_NUM_DOC=M.NUM_DOC





WHERE LEN(M.UUIDSAT) = 36 AND M.NUM_DOC IN(SELECT * FROM #NUM_DOC)







group by
M.Num_doc,
M.AgrupacionIdSAT,
M.AnioSAT,
M.CanceladoSAT,
M.CanceladoContabilizadoSAT,
M.ClaveNominaSAT,
M.FechaFacturacionSAT,
M.FechaGeneracionPolizaSAT,
M.FechaPagoSAT,
M.FechaTimbradoSAT,
M.FolioSAT,
M.IdPDFSAT,
M.InformacionSAT,
M.IsValidSAT,
M.IvaSAT,
M.MesSAT,
M.MonedaSAT,
M.OrigenSAT,
M.PeriodicidadPagoSAT,
M.RazonSocialEmisorSAT,
M.RazonSocialReceptorSAT,
M.RFCEmisorSAT,
ENC.EmisorCodigoPostalSAT,
M.RFCReceptorSAT,
ENC.ReceptorCodigoPostalSAT,
DD.CONCEPTO,
M.SerieSAT,
M.SubtotalSAT,
M.TipoComprobanteSAT,
M.TipoCambioSAT,
M.TipoNominaSAT,
M.TotalSAT,
M.UUIDSAT,
M.AcuseCanceladoSAT,
M.DescuentoSAT,
M.ISHPorcentajeSAT,ISHImporteSAT,
M.VersionSAT,
M.FormaDePagoSAT,
M.MetodoDePagoSAT,
M.FechaIniSAT,
M.FechaActualizacionSAT,
ENC.TipoRelacionSAT,
ENC.CfdiRelacionadoUUIDSAT,
ENC.ReceptorUsoCFDISAT
ORDER BY M.Num_doc')
end
-----PRINT(@QUERY)
drop table #NUM_DOC
drop table #DesDec





drop table #TEST
drop table #IMPTRASLADO
END
END
END