Generación de formato impresión en Crystal lento

[former_member429163]

Buenas tardes Estimados;

Quisiera su Apoyo con lo siguiente, ACTUALMENTE tengo un Formato de impresión para factura el cual se usa con Crystal, sin embargo me preocupa Que la Generación de DICHO formato me tarda aproximadamente entre 3 A 5 minutos, en La Consulta SQL Lo que sé Logra es mandar a llamar toda la información relacionada con la factura, Asi Como también si el Código del Producto Tiene Lote y Fecha de vencimiento.

Adjunto el archivo de la consulta en SQL ,

DECLARE @DocEntry AS INT; SET @DocEntry ={?DocKey@} ;WITH ItemSeries AS ( (SELECT DLN1.ItemCode, OSRI.IntrSerial AS "SerieLote", OSRI.ExpDate, 1 AS TN FROM DLN1 INNER JOIN SRI1 ON SRI1.BaseType = DLN1.ObjType AND SRI1.BaseEntry = DLN1.DocEntry INNER JOIN OSRI ON OSRI.SysSerial = SRI1.SysSerial WHERE DLN1.DocEntry IN (SELECT BaseEntry FROM INV1 WHERE INV1.DocEntry = @DocEntry) AND (OSRI.ItemCode = DLN1.ItemCode)

UNION SELECT DLN1.ItemCode, IBT1.BatchNum AS "SerieLote", OBTN.ExpDate , 1 AS TN FROM IBT1 INNER JOIN DLN1 ON IBT1.ItemCode = DLN1.ItemCode AND IBT1.BaseEntry = DLN1.DocEntry INNER JOIN OBTN ON OBTN.DistNumber = BatchNum WHERE IBT1.BaseType = DLN1.ObjType AND DLN1.DocEntry IN (SELECT BaseEntry FROM INV1 WHERE DocEntry = @DocEntry)) UNION SELECT INV1.ItemCode, OSRI.IntrSerial AS "SerieLote", OSRI.ExpDate, 1 AS TN FROM INV1 INNER JOIN SRI1 ON SRI1.BaseType = INV1.ObjType AND SRI1.BaseEntry = INV1.DocEntry INNER JOIN OSRI ON OSRI.SysSerial = SRI1.SysSerial WHERE INV1.DocEntry = @DocEntry AND (OSRI.ItemCode = INV1.ItemCode) UNION SELECT INV1.ItemCode, IBT1.BatchNum AS "SerieLote", OBTN.ExpDate , 1 AS TN FROM IBT1 INNER JOIN INV1 ON IBT1.ItemCode = INV1.ItemCode AND IBT1.BaseEntry = INV1.DocEntry INNER JOIN OBTN ON OBTN.DistNumber = BatchNum WHERE IBT1.BaseType = INV1.ObjType AND INV1.DocEntry = @DocEntry ) SELECT -----Información Factura Encabezado BaseType , T0.[DocEntry] , T0.U_POPA_NUM_FACTURA 'Numeración DEI' , T3.[U_POPA_FechaE] 'Fecha Emisión DEI' , T0.[U_POPA_CAI] 'CAI DEI' , SUBSTRING (T0.U_POPA_CLC,9,3) 'Fondo' , T0.[DocNum] 'Numero de Documento' , T0.[DocCur] 'Moneda' , T0.[DocDate] 'Fecha Contabilización' , T0.[DocDueDate] 'Fecha Vencimiento' , T0.[CardCode] 'Codigo Cliente' , T0.[CardName] 'Nombre Cliente' , T8.[PymntGroup] 'Condición de Pago' , Case when (T8.GroupNum = 19 OR T8.GroupNum = 43 OR T8.GroupNum = 10 OR T8.GroupNum = 47) then 'Contado' else 'Crédito / Financiamiento' end as Facturacion , T2.[AddID] 'RTN' , ISNULL(T0.U_RTN_Cliente,'') 'RTN Cliente 2' , T2.[VatIdUnCmp] 'Identidad' , ISNULL(T0.U_Identidad_Cliente,'') 'Identidad Cliente 2' , T0.Address 'Dirección Cliente' , ISNULL(T2.[E_Mail],'') 'Correo Cliente' , T9.SlpName AS 'Vendedor' , (SUBSTRING(U_POPA_NUM_FACTURA, 1,10) + '-'+RIGHT('0000000' + CAST(U_POPA_DESDE AS VARCHAR), 8)) Rango_Autorizado_Desde , (SUBSTRING(U_POPA_NUM_FACTURA, 1,10) + '-'+RIGHT('0000000' + CAST(U_POPA_Hasta AS VARCHAR), 8)) Rango_Autorizado_Hasta

-----Información Factura Detalle , T12.Address 'Dirección Sucursal' , T0.VatSum AS ' IMPUESTO LPS' , T0.VatSumFC AS 'IMPUESTO USD' , (T1.[LineVat]/Quantity) AS LineVat , T1.[ItemCode] 'Codigo Producto' , T1.[Dscription] 'Descripción Producto' , T1.[PriceBefDi] AS 'Precio Nomral' , T1.PRICE AS 'Precio con Descuento' , (T1.[LineTotal]/Quantity) AS 'Total de linea LPS' , (T1.[TotalFrgn]/Quantity) AS 'Total de linea USD' , T1.[TaxCode] 'Indicador Impuesto' , T0.DOCTOTAL AS 'Total documento en LPS' , T0.Printed AS Impreso , T0.DocTotalFC AS 'Total documento en USD' , ISNULL(T0.[VatSum],0) 'Impuesto en LPS' , ISNULL(T0.[VatSumFC],0) 'Impuesto en USD' , (T1.Quantity / CASE WHEN (SELECT COUNT(*) FROM ItemSeries WHERE ItemSeries.ItemCode = T1.ItemCode) = 0 THEN 1 ELSE (SELECT COUNT(*) FROM ItemSeries WHERE ItemSeries.ItemCode = T1.ItemCode) END) AS 'Cantidad' , T1.[DiscPrcnt] '% Descuento' , CASE WHEN T7.VATLiable <> 'Y' THEN T1.LineTotal ELSE 0 END AS Exento , CASE WHEN T7.VATLiable = 'Y' THEN T1.LineTotal ELSE 0 END AS Grabado , ( SELECT ISNULL(SUM(e.[TotalFrgn]),0) FROM INV1 e INNER JOIN OITM I ON e.ItemCode = I.ItemCode WHERE I.VATLiable = 'N' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Venta ExentasUSD' , ( SELECT ISNULL(SUM(e.[PriceBefDi]*e.[Quantity]),0) FROM OITM c inner join INV1 e on c.ItemCode = e.ItemCode WHERE c.VATLiable = 'N' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Venta Exentas' , ( SELECT ISNULL(SUM(e.[TotalFrgn]),0) FROM INV1 e INNER JOIN OITM I ON e.ItemCode = I.ItemCode WHERE I.VATLiable = 'Y' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Venta GravadasUSD' , ( SELECT ISNULL(SUM(e.[PriceBefDi]*e.[Quantity]),0) FROM OITM c inner join INV1 e on c.ItemCode = e.ItemCode WHERE c.VATLiable = 'Y' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Venta Gravadas' , ( SELECT ISNULL(SUM(((e.[DiscPrcnt]/100) * (e.PriceBefDi * Quantity))),0) FROM INV1 e INNER JOIN OITM I ON e.ItemCode = I.ItemCode WHERE I.VATLiable = 'N' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Decuento EXEUSD' , ( SELECT ISNULL(SUM(((e.[DiscPrcnt]/100) * (e.PriceBefDi * Quantity))),0) FROM INV1 e INNER JOIN OITM I ON e.ItemCode = I.ItemCode WHERE I.VATLiable = 'N' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Decuento EXE' , ( SELECT ISNULL(SUM(((e.[DiscPrcnt]/100) *(e.PriceBefDi * Quantity))),0) FROM INV1 e INNER JOIN OITM I ON e.ItemCode = I.ItemCode WHERE I.VATLiable = 'Y' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Decuento ISVUSD' , ( SELECT ISNULL(SUM(((e.[DiscPrcnt]/100) * (e.[PriceBefDi])* e.[Quantity])),0) FROM INV1 e INNER JOIN OITM I ON e.ItemCode = I.ItemCode WHERE I.VATLiable = 'Y' AND e.[DocEntry] = T0.[DocEntry] ) AS 'Decuento ISV' ,T1.[WhsCode] ,T1.[unitMsr] ,T7.VATLiable ,T0.[DiscSum] , T0.[NumAtCard] , COALESCE(ItemSeries.SerieLote, '') AS "Número de serie/lote" , COALESCE(CAST(ItemSeries.ExpDate AS NVARCHAR(11)), 'N/A') AS "Fecha de vencimiento" , T0.Printed FROM OINV T0 INNER JOIN INV1 T1 ON T1.[DocEntry] = T0.[DocEntry] INNER JOIN OCRD T2 ON T2.[CardCode] = T0.[CardCode] INNER JOIN [@DEI_REGIMEN] T3 ON T0.[U_POPA_CAI] = T3.[U_POPA_CAI] INNER JOIN "@DEI_DOCUMENTO" DD ON DD.Code = T3.Code INNER JOIN OITM T7 ON T1.[ItemCode] = T7.[ItemCode] INNER JOIN OCTG T8 ON T8.GroupNum = T0.GroupNum INNER JOIN OUSR AS T10 ON T10.USERID = T0.UserSign INNER JOIN OUDG AS T12 ON T10.DfltsGroup = T12.Code INNER JOIN OUBR AS T11 ON T11.Code = T10.Branch INNER JOIN OSLP T9 ON T9.SlpCode = T0.SlpCode LEFT OUTER JOIN ItemSeries ON T1.ItemCode = ItemSeries.ItemCode , OADM T4 , ADM1 T5

WHERE T0.[DocEntry] ={?DocKey@} AND U_POPA_Sucursal = SUBSTRING(U_POPA_NUM_FACTURA, 1,3) AND U_POPA_Caja = SUBSTRING(U_POPA_NUM_FACTURA, 5,3) AND U_POPA_DocNum = SUBSTRING(U_POPA_NUM_FACTURA, 9,2) AND T3.U_POPA_CAI = T0.[U_POPA_CAI]