Estimados,
buen día, he realizado el siguiente reporte el cual me sale todas las líneas de cada documento, lo que requiero realizar ahora es sumar las cantidades y el total de cada documento en una sola línea.
SELECT d0."DocNum",
d0."DocDate" as "FECHA",
d0."U_SYP_MDSD" as "SERIE DE DOCUMENTO" ,
d0."NumAtCard"as "N°GUÍA DE REMISIÓN",
d0."CardCode" as "CÓDIGO DE CLIENTE",
d0. "CardName"as "NOMBRE DE CLIENTE",
d0."Address2"as "DIRECCIÓN DE ENVIÓ",
CASE WHEN d0."U_SYP_MDMT" ='01' THEN 'VENTA' WHEN d0."U_SYP_MDMT" ='07' THEN 'OBSEQUIO'
WHEN d0."U_SYP_MDMT" ='91' THEN 'CANJE'
WHEN d0."U_SYP_MDMT" ='04' THEN 'CONSIGNA'
-- "U_SYP_MDMT" ='' THEN 'DEMO' Tranfer
--"U_SYP_MDMT" ='' THEN 'CESIÓN DE USO'
else 'OTROS'
END as "MOTIVO DEL TRASLADO",
d1."ItemCode" as "CÓDIGO PRODUCTO",
d1."Dscription" as "DESCRIPCIÓN",
-- "LOTE_O_SERIE_entregas"(d1."DocEntry", d1."LineNum") as "LOTE/SERIE",
"Quantity" as "CANT",
w."WhsName" as "ALMACÉN", -- : (ABBOTT, SOPHIA, DERMA, MARTIDER, ETC)
d1."OcrCode" as "Linea de Negocio"
,T1."SlpName" AS "Empleado de ventas"
,T1."U_LBF_RESPONSABLE" AS "Respresentante"
from DLN1 d1
INNER JOIN ODLN d0 on d0."DocEntry"=d1."DocEntry"
INNER JOIN OWHS w on w."WhsCode"= d1."WhsCode"
LEFT JOIN OSLP T1 ON d0."SlpCode" = T1."SlpCode"
WHERE d0."DocDate" BETWEEN [%3] AND [%4]
--YEAR(d0."DocDate")
AND "CANCELED"='N'
----------------------
UNION ALL
----------------------
select
d0."DocNum",
d0."DocDate" as "FECHA",
d0."U_SYP_MDSD" as "SERIE DE DOCUMENTO" ,
-- "NumAtCard"as "N°GUÍA DE REMISIÓN",
d0."U_SYP_MDTD"||'-'||d0."U_SYP_MDSD"||'-'||d0."U_SYP_MDCD" as "ND",
d0."CardCode" as "CÓDIGO DE CLIENTE",
d0."CardName"as "NOMBRE DE CLIENTE",
d0."Address"as "DIRECCIÓN DE ENVIÓ",
CASE WHEN d0."ToWhsCode" ='99026'
THEN 'CONSIGNACION' WHEN d0."ToWhsCode" ='99006'
THEN 'DEMO' -- "U_SYP_MDMT" ='' THEN 'DEMO' Tranfer
--"U_SYP_MDMT" ='' THEN 'CESIÓN DE USO'
else M."Name" -- 'OTROS'
END as "MOTIVO DEL TRASLADO",
d1."ItemCode" as "CÓDIGO PRODUCTO",
d1."Dscription" as "DESCRIPCIÓN",
-- "LOTE_O_SERIE_traslados"(d1."DocEntry", d1."LineNum") as "LOTE/SERIE",
d1."Quantity" as "CANT",
w."WhsName" as "ALMACÉN", -- : (ABBOTT, SOPHIA, DERMA, MARTIDER, ETC)
T1."FirmName" as "Linea de Negocio"
,T2."SlpName" AS "Empleado de ventas"
,T2."U_LBF_RESPONSABLE" AS "Respresentante"
from WTR1 d1
INNER JOIN OWTR d0 on d0."DocEntry"=d1."DocEntry"
INNER JOIN OWHS w on w."WhsCode"= d1."WhsCode"
INNER JOIN "@SYP_MTRASLADO" M ON M."Code"= d0."U_SYP_MDMT"
inner join OITM T0 ON d1."ItemCode"=T0."ItemCode"
inner join OMRC T1 ON T0."FirmCode"=T1."FirmCode"
LEFT JOIN OSLP T2 ON d0."SlpCode" = T2."SlpCode"
WHERE d0."DocDate" BETWEEN [%3] AND [%4]
AND "CANCELED"='N'
----------------
UNION ALL /* AGREGADO 21-05-21*/
----------------
SELECT d0."DocNum",
d0."DocDate" as "FECHA",
d0."U_SYP_MDSD" as "SERIE DE DOCUMENTO" ,
d0."NumAtCard"as "N°GUÍA DE REMISIÓN",
d0."CardCode" as "CÓDIGO DE CLIENTE",
d0. "CardName"as "NOMBRE DE CLIENTE",
d0."Address2"as "DIRECCIÓN DE ENVIÓ",
CASE WHEN d0."U_SYP_MDMT" ='01' THEN 'VENTA'
WHEN d0."U_SYP_MDMT" ='07' THEN 'OBSEQUIO'
WHEN d0."U_SYP_MDMT" ='91' THEN 'CANJE'
WHEN d0."U_SYP_MDMT" ='04' THEN 'CONSIGNA' -- "U_SYP_MDMT" ='' THEN 'DEMO' Tranfer --"U_SYP_MDMT" ='' THEN 'CESIÓN DE USO'
else 'OTROS' END as "MOTIVO DEL TRASLADO",
d1."ItemCode" as "CÓDIGO PRODUCTO",
d1."Dscription" as "DESCRIPCIÓN",
-- "LOTE_O_SERIE_entregas"(d1."DocEntry", d1."LineNum") as "LOTE/SERIE",
"Quantity" as "CANT",
w."WhsName" as "ALMACÉN", -- : (ABBOTT, SOPHIA, DERMA, MARTIDER, ETC)
d1."OcrCode" as "Linea de Negocio"
,T1."SlpName" AS "Empleado de ventas"
,T1."U_LBF_RESPONSABLE" AS "Respresentante"
from INV1 d1
INNER JOIN OINV d0 on d0."DocEntry"=d1."DocEntry"
INNER JOIN OWHS w on w."WhsCode"= d1."WhsCode"
LEFT JOIN OSLP T1 ON d0."SlpCode" = T1."SlpCode"
WHERE d0."DocDate" BETWEEN [%3] AND [%4] AND d0."U_SYP_MDTD"='03' AND d0."SlpCode"=3
--YEAR(d0."DocDate")
AND "CANCELED"='N'