Estimados les envió una consulta que he realizado, lo que hace es que me da una trazabilidad entera de las fechas de Orden de compra - Factura reserva - Entrada -precio de entrega y algunas fechas de cuando se movió a su determinado almacén.
Mi duda es que al momento de ejecutarla en query manager demora mucho. Talvez ustedes me puedas apoyar corrigiendo mi logica o algo en donde se pueda optimizar .Les pido encarecidamente que me den su feedback u otra version en la que podria correr mas rapido. De ante mano gracias.
CREATE PROCEDURE _seguimiento_reclamos
(@FechaInicio DATE, @FechaFin DATE)
AS
BEGIN
WITH Precio_entrega AS (
SELECT DISTINCT OI.DocNum, OI.DocDate, OI.AgentName, O1.*
FROM OIPF OI
LEFT JOIN IPF1 O1 ON O1.DocEntry = OI.DocEntry
),
Auditoria_stocks AS (
SELECT
InvntAct AS 'Cuenta_mayor',
Transtype AS 'Clase_Operacion',
BASE_REF AS 'Documento_SAP',
CreateDate AS 'Fecha_creacion',
DocDate AS 'Fecha_contabilizacion',
Comments AS 'Comentarios',
ItemCode AS 'Codigo_articulo',
Dscription AS 'Descripcion_articulo',
(SELECT Location FROM OLCT WHERE Code = CAST(OINM.Location AS INT)) AS 'Locacion',
Warehouse AS 'Almacen',
PrjCode AS 'Codigo_proyecto',
InQty AS 'Cantidad_entrada',
OutQty AS 'Cantidad_salida',
(SELECT SUM(InQty - OutQty)
FROM OINM AS O2
WHERE O2.ItemCode = OINM.ItemCode
AND O2.Warehouse = OINM.Warehouse
AND O2.DocDate <= OINM.DocDate
AND (O2.DocDate < OINM.DocDate
OR (O2.DocDate = OINM.DocDate AND O2.BASE_REF <= OINM.BASE_REF))
) AS 'Cantidad_acumulada',
CalcPrice AS 'Precio_calculado',
TransValue AS 'Valor Transaccion'
FROM OINM
WHERE Transvalue <> 0
)
SELECT DISTINCT
O.CardCode AS 'Codigo_proveedor',
O.CardName AS 'Nombre_proveedor',
O.DocNum AS 'Numero_SAP_orden',
O.DocDate AS 'Fecha_Orden',
PC1.DocNum AS 'Numero_SAP_factura',
P1.DocDate AS 'Fecha_Factura_reserva',
OP.DocNum AS 'Numero_SAP_entrada',
PN1.ItemCode AS 'Codigo_material',
PN1.Quantity AS 'Cantidad',
(SELECT TOP 1 OP1.DocDate
FROM OPDN OP1
INNER JOIN PDN1 PN1 ON OP1.DocEntry = PN1.DocEntry
WHERE PN1.BaseRef = PC1.DocNum
AND PN1.BaseEntry = P1.DocEntry
AND PN1.BaseLine = P1.LineNum
ORDER BY OP1.DocDate) AS 'Fecha_Contabilizacion_entrada',
(SELECT TOP 1 PE.DocNum
FROM Precio_entrega PE
WHERE PE.BaseType = '20' AND PE.BaseEntry = PN1.DocEntry
) AS 'Numero_Sap_precio_entrega',
(SELECT TOP 1 PE.DocDate
FROM Precio_entrega PE
WHERE PE.BaseType = '20' AND PE.BaseEntry = PN1.DocEntry
) AS 'Fecha_precio_entrega',
(SELECT TOP 1 VTA.Fecha_contabilizacion
FROM Auditoria_stocks VTA
WHERE VTA.Codigo_articulo = PN1.ItemCode
AND VTA.Fecha_contabilizacion >
(SELECT TOP 1 PE.DocDate
FROM Precio_entrega PE
WHERE PE.BaseType = '20' AND PE.BaseEntry = PN1.DocEntry)
ORDER BY VTA.Fecha_contabilizacion
) AS 'Fecha_ingreso_almacen_12',
(SELECT TOP 1 VTA.Cantidad_acumulada
FROM Auditoria_stocks VTA
WHERE VTA.Codigo_articulo = PN1.ItemCode
AND VTA.Fecha_contabilizacion >
(SELECT TOP 1 PE.DocDate
FROM Precio_entrega PE
WHERE PE.BaseType = '20' AND PE.BaseEntry = PN1.DocEntry)
ORDER BY VTA.Fecha_contabilizacion
) AS 'Stock_desp',
(SELECT SUM(O2.CostSum)
FROM OIPF OI
LEFT JOIN IPF2 O2 ON O2.DocEntry = OI.DocEntry
WHERE OI.DocNum =
(SELECT TOP 1 PE.DocNum
FROM Precio_entrega PE
WHERE PE.BaseType = '20' AND PE.BaseEntry = PN1.DocEntry)
GROUP BY OI.DocNum
) AS 'Costo_de_importacion'
FROM OPOR O
LEFT JOIN POR1 O1 ON O.DocEntry = O1.DocEntry
LEFT JOIN PCH1 P1 ON O.DocNum = P1.BaseRef
AND O1.DocEntry = P1.BaseEntry
AND O1.LineNum = P1.BaseLine
LEFT JOIN OPCH PC1 ON PC1.DocEntry = P1.DocEntry
LEFT JOIN PDN1 PN1 ON PN1.BaseRef = PC1.DocNum
AND PN1.BaseEntry = P1.DocEntry
AND PN1.BaseLine = P1.LineNum
LEFT JOIN OPDN OP ON OP.DocEntry = PN1.DocEntry
LEFT JOIN IPF1 DET_PRE ON DET_PRE.BaseEntry = PC1.DocNum
LEFT JOIN OIPF CAB_PRE ON CAB_PRE.DocEntry = DET_PRE.DocEntry
WHERE O.CANCELED != 'Y'
AND O.DocDate BETWEEN @FechaInicio AND @FechaFin
AND O.CardCode IN ('PE00001', 'PE00005', 'PE00040', 'P20601328209')
AND PC1.DocNum IN (
SELECT DISTINCT PC2.DocNum
FROM OPCH PC2
INNER JOIN PCH1 P2 ON PC2.DocEntry = P2.DocEntry
WHERE P2.BaseRef = O.DocNum
)
AND OP.DocNum IN (
SELECT DISTINCT OP1.DocNum
FROM OPDN OP1
INNER JOIN PDN1 PN1 ON OP1.DocEntry = PN1.DocEntry
WHERE PN1.BaseRef = PC1.DocNum
)
--ORDER BY O.DocNum, PC1.DocNum;
END