Hola Buen día!!
Me gustaría obtener ayuda estoy realizando un reporte donde debo contemplar las Facturas Deudor y la Entregas realizadas. me gustaría saber si esto en algún punto es posible cuadrarlo con informe de perdidas y ganancias ya que la suma de estos documentos menos las notas de crédito… el valor me sale 230,314.54 arriba de los ingresos que tiene el informe antes mencionado. en primer instancia me gustaría saber si es posible hacer que este valor pegue con el informe de perdida y ganancia de ser así como puedo lograrlo. dejo mi query para que den su opinion!! de ante mano muchas gracias por su ayuda.
SELECT T0."DocNum", T0."DocDate",T0."CardCode", T0."CardName",SUM(T1."Quantity"* T2."SWeight1" ) ,
SUM(T1."LineTotal"),T0."U_Tipo_Entrega",
EXTRACT (YEAR FROM T0."DocDate") as "Año",
EXTRACT (MONTH FROM T0."DocDate") as "Mes",
Case
when T0."U_Tipo_Entrega"= 1 then 'Enviar a Domicilio'
when T0."U_Tipo_Entrega"= 2 then 'Enviar a Domicilio Foraneo'
when T0."U_Tipo_Entrega"=3 then 'Pago Contra Entrega'
when T0."U_Tipo_Entrega"=4 then 'Recogera en Bodega'
when IFNULL(T0."U_Tipo_Entrega",0)=0 then 'Otros'
End as "Tipo de Entrega",
(
CASE T0."CANCELED"
WHEN 'C' THEN (SUM(T1."LineTotal")*(-1)) --cancelación de una factura
ELSE (SUM(T1."LineTotal"))
END
) as "Total Venta"
FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN "OACT" T4 ON T4."AcctCode" = T1."AcctCode"
WHERE T0."DocDate" between {?Fecha1} and {?Fecha2} and T0."U_Tipo_Entrega" ='4'
AND T4."GroupMask" IN ( '4')
AND T1."TaxOnly"!='Y'
AND IFNULL(T1."ItemCode", '') <> '2' and
T0."DocType"<>'S'
GROUP BY T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate", EXTRACT (YEAR FROM T0."DocDate") ,
EXTRACT (MONTH FROM T0."DocDate") ,T0."U_Tipo_Entrega",T0."CANCELED"
UNION ALL
SELECT T0."DocNum", T0."DocDate",T0."CardCode", T0."CardName",SUM(T1."Quantity"* T2."SWeight1" )*-1,
SUM(T1."LineTotal")*-1,T0."U_Tipo_Entrega" ,
EXTRACT (YEAR FROM T0."DocDate") as "Año",
EXTRACT (MONTH FROM T0."DocDate") as "Mes",
Case
when T0."U_Tipo_Entrega"= 1 then 'Enviar a Domicilio'
when T0."U_Tipo_Entrega"= 2 then 'Enviar a Domicilio Foraneo'
when T0."U_Tipo_Entrega"=3 then 'Pago Contra Entrega'
when T0."U_Tipo_Entrega"=4 then 'Recogera en Bodega'
when IFNULL(T0."U_Tipo_Entrega",0)=0 then 'Otros'
End as "Tipo de Entrega",
(
CASE T0."CANCELED"
WHEN 'C' THEN (SUM(T1."LineTotal")) --cancelación de una factura
ELSE (SUM(T1."LineTotal")*(-1))
END
) as "Total Venta"
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN "OACT" T4 ON T4."AcctCode" = T1."AcctCode"
WHERE T0."DocDate" between {?Fecha1} and {?Fecha2} and T0."U_Tipo_Entrega" ='4'
AND T4."GroupMask" IN ( '4')
AND T1."TaxOnly"!='Y'
AND IFNULL(T1."ItemCode", '') <> '2'and
T0."DocType"<>'S'
GROUP BY T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate",
EXTRACT (YEAR FROM T0."DocDate") ,
EXTRACT (MONTH FROM T0."DocDate") ,T0."U_Tipo_Entrega",T0."CANCELED"
UNION ALL
SELECT T0."DocNum", T0."DocDate",T0."CardCode", T0."CardName",SUM(T1."Quantity"* T2."SWeight1" ) ,
SUM(T1."LineTotal"),T0."U_Tipo_Entrega" ,
EXTRACT (YEAR FROM T0."DocDate") as "Año",
EXTRACT (MONTH FROM T0."DocDate") as "Mes",
Case
when T0."U_Tipo_Entrega"= 1 then 'Enviar a Domicilio'
when T0."U_Tipo_Entrega"= 2 then 'Enviar a Domicilio Foraneo'
when T0."U_Tipo_Entrega"=3 then 'Pago Contra Entrega'
when T0."U_Tipo_Entrega"=4 then 'Recogera en Bodega'
when IFNULL(T0."U_Tipo_Entrega",0)=0 then 'Otros'
End as "Tipo de Entrega",
(
CASE T0."CANCELED"
WHEN 'C' THEN (SUM(T1."LineTotal")*(-1)) --cancelación de una factura
ELSE (SUM(T1."LineTotal"))
END
) as "Total Venta"
FROM ODLN T0 INNER JOIN DLN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN "OACT" T4 ON T4."AcctCode" = T1."AcctCode"
WHERE T0."DocDate" between{?Fecha1} and {?Fecha2}
AND T4."GroupMask" IN ( '4')
AND T1."TaxOnly"!='Y'
AND IFNULL(T1."ItemCode", '') <> '2'and
T0."DocType"<>'S'
GROUP BY T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate",
EXTRACT (YEAR FROM T0."DocDate") ,
EXTRACT (MONTH FROM T0."DocDate"),T0."U_Tipo_Entrega",T0."CANCELED"
UNION ALL
SELECT T0."DocNum", T0."DocDate",T0."CardCode", T0."CardName",SUM(T1."Quantity"* T2."SWeight1" )*-1 ,
SUM(T1."LineTotal")*-1,T0."U_Tipo_Entrega" ,
EXTRACT (YEAR FROM T0."DocDate") as "Año",
EXTRACT (MONTH FROM T0."DocDate") as "Mes",
Case
when T0."U_Tipo_Entrega"= 1 then 'Enviar a Domicilio'
when T0."U_Tipo_Entrega"= 2 then 'Enviar a Domicilio Foraneo'
when T0."U_Tipo_Entrega"=3 then 'Pago Contra Entrega'
when T0."U_Tipo_Entrega"=4 then 'Recogera en Bodega'
when IFNULL(T0."U_Tipo_Entrega",0)=0 then 'Otros'
End as "Tipo de Entrega",
(
CASE T0."CANCELED"
WHEN 'C' THEN (SUM(T1."LineTotal")) --cancelación de una factura
ELSE (SUM(T1."LineTotal")*(-1))
END
) as "Total Venta"
FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITM T2 ON T1."ItemCode" = T2."ItemCode"
INNER JOIN "OACT" T4 ON T4."AcctCode" = T1."AcctCode"
WHERE T0."DocDate" between{?Fecha1} and{?Fecha2}
AND T4."GroupMask" IN ( '4')
AND T1."TaxOnly"!='Y'
AND IFNULL(T1."ItemCode", '') <> '2'and
T0."DocType"<>'S'
GROUP BY T0."DocNum", T0."CardCode", T0."CardName", T0."DocDate",
EXTRACT (YEAR FROM T0."DocDate") ,
EXTRACT (MONTH FROM T0."DocDate") ,T0."U_Tipo_Entrega",T0."CANCELED"