Buena tarde compañeros,
Diseñe una consulta, con la cual me trae toda la trazabilidad de un pedido, pasando por la orden de venta hasta llegar a la factura, lo cual me permite validar si el pedido salio completo 'o parcial, pero tengo un pequeño error, lo cual no logro encontrar.
Para el caso del codigo 3045060160 salen 654 pero al validar en SAP se facturaron 4, no entiendo el porque me esta mostrando ese valor.
Comparto consulta:
SELECT
-- PEDIDOS
T1."CardCode",
T1."CardName",
T9."SlpName",
T1."DocNum" AS "Pedido",
T1."Comments" AS "Pedido Cliente",
T1."DocDate" AS "FechaPedido",
T1."DocDueDate" AS "FechaEntregaEsperada",
T2."ItemCode",
T2."Dscription",
(SELECT SUM(TB."Quantity") FROM "RDR1" TB WHERE TB."DocEntry" = T1."DocEntry" AND TB."ItemCode" = T2."ItemCode") AS "CantidadPedida",
T2."unitMsr" AS "Embalaje",
(SELECT SUM(TC."Quantity" * TC."NumPerMsr") FROM "RDR1" TC WHERE TC."DocEntry" = T1."DocEntry" AND TC."ItemCode" = T2."ItemCode") AS "Total unidades",
(SELECT SUM(TA."LineTotal") FROM "RDR1" TA WHERE TA."DocEntry" = T1."DocEntry" AND TA."ItemCode" = T2."ItemCode") AS "SubTotalPedido",
-- FACTURAS
MAX(IFNULL(T6."DocNum",T8."DocNum")) AS "Factura",
MAX(IFNULL(T6."DocDate",T8."DocDate")) AS "FechaFactura",
(SELECT IFNULL(SUM(T5."Quantity"),0) FROM "INV1" T5 WHERE T4."DocEntry" = T5."BaseEntry" AND T3."ItemCode" = T5."ItemCode") AS "CantidadFactura",
(SELECT IFNULL(SUM(T7."Quantity"),0) FROM "INV1" T7 WHERE T1."DocEntry" = T7."BaseEntry" AND T2."ItemCode" = T7."ItemCode") AS "CantidadFactura1",
(SELECT SUM(TB."OpenQty") FROM "RDR1" TB WHERE TB."DocEntry" = T1."DocEntry" AND TB."ItemCode" = T2."ItemCode") AS "Cantidad Pendiente por Facturar",
STRING_AGG(IFNULL(T5."unitMsr",T7."unitMsr"), ', ') AS "Unidad medida",
(SELECT IFNULL(SUM(T5."LineTotal"),0) FROM "INV1" T5 WHERE T4."DocEntry" = T5."BaseEntry" AND T3."ItemCode" = T5."ItemCode") AS "SubTotalFactura",
(SELECT IFNULL(SUM(T7."LineTotal"),0) FROM "INV1" T7 WHERE T1."DocEntry" = T7."BaseEntry" AND T2."ItemCode" = T7."ItemCode") AS "SubTotalFactura1",
CASE
WHEN MAX(IFNULL(T6."DocDate",T8."DocDate")) <= T1."DocDueDate" AND SUM(IFNULL(T5."LineTotal",T7."LineTotal")) >= (SELECT SUM(TA."LineTotal") FROM "RDR1" TA WHERE TA."DocEntry" = T1."DocEntry" AND TA."ItemCode" = T2."ItemCode") THEN 'PERFECTO'
WHEN SUM(IFNULL(T5."LineTotal",T7."LineTotal")) >= (SELECT SUM(TA."LineTotal") FROM "RDR1" TA WHERE TA."DocEntry" = T1."DocEntry" AND TA."ItemCode" = T2."ItemCode") THEN 'PRODUCTO PERFECTO'
WHEN T1."DocStatus" = 'C' AND SUM(IFNULL(IFNULL(T5."LineTotal",T7."LineTotal"),0))= 0 THEN 'PEDIDO CERRADO'
ELSE 'IMPERFECTO'
END AS "Estado",
T10."Name" AS "Causal cierre pedido"
-- PEDIDOS
FROM
"ORDR" T1
INNER JOIN
"RDR1" T2
ON T1."DocEntry" = T2."DocEntry"
-- ENTREGAS
LEFT JOIN
"DLN1" T3
ON T1."DocEntry" = T3."BaseEntry"
AND T3."BaseType" = '17'
AND T2."ItemCode" = T3."ItemCode"
LEFT JOIN
"ODLN" T4
ON T3."DocEntry" = T4."DocEntry"
-- FACTURAS DE ENTREGAS
LEFT JOIN
"INV1" T5
ON T4."DocEntry" = T5."BaseEntry"
AND T5."BaseType" = '15'
AND T3."ItemCode" = T5."ItemCode"
LEFT JOIN
"OINV" T6
ON T5."DocEntry" = T6."DocEntry"
-- FACTURAS DE PEDIDOS
LEFT JOIN
"INV1" T7
ON T1."DocEntry" = T7."BaseEntry"
AND T7."BaseType" = '17'
AND T2."ItemCode" = T7."ItemCode"
LEFT JOIN
"OINV" T8
ON T7."DocEntry" = T8."DocEntry"
-- VENDEDORES
LEFT JOIN
"OSLP" T9
ON T1."SlpCode" = T9."SlpCode"
LEFT JOIN
"@VIC_CAUSALES_CIERRE" T10
ON T1."U_VIC_Causal_Cierre" = T10."Code"
WHERE
T1."DocDate" BETWEEN '2023-04-01' AND '2023-04-30'
AND T1."DocNum" IN ('196708')
GROUP BY
T1."DocStatus",
T10."Name",
T1."DocEntry",
T4."DocEntry",
T3."ItemCode",
T1."DocNum",
T1."Comments",
T1."CardCode",
T1."CardName",
T9."SlpName",
T1."DocDate",
T1."DocDueDate",
T2."ItemCode",
T2."unitMsr",
T2."Dscription"
les agradezco cualquier asesoría
Saludos