Restar las notas credito a las facturas

[jtriana6591]

buenos días compañeros solicito su colaboración por favor es que en la empresa me solicitaron realizar una consulta en SAP B1 donde generara el total de ventas antes de IVA y con IVA incluido pero ahora me solicitaron que a esa consulta le quitara o les restara las que están en nota crédito o canceladas

gracias por su ayuda

[juliosura]

Hola @jtriana6591

Ví en un post anterior (Ventas antes de iva e iva incluido) que @meqs te ayudo con la consulta inicial, me ha basado en dicha consulta para apoyarte en la modificación que te han solicitado:

SELECT T2.[ItmsGrpNam] as 'GRUPO', sum(T0.[LineTotal]) as 'VENTA ANTES DE IVA', sum(T0.[VatSum]) as 'IVA', sum(T0.[LineTotal]+T0.[VatSum]) as 'VENTA IVA INCLUIDO' 
FROM INV1 T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod 
WHERE T0.LineStatus <> 'C'
GROUP BY  T2.[ItmsGrpNam]
UNION
SELECT T2.[ItmsGrpNam] as 'GRUPO', sum(T0.[LineTotal])*-1 as 'VENTA ANTES DE IVA', sum(T0.[VatSum])*-1 as 'IVA', sum(T0.[LineTotal]+T0.[VatSum])*-1 as 'VENTA IVA INCLUIDO' 
FROM RIN1 T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode 
INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod 
WHERE T0.LineStatus <> 'C'
GROUP BY  T2.[ItmsGrpNam]

Ahora aparece en otra fila y en negativo los valores correspondientes a las notas de crédito, al realizar la sumatoria obtendras es valor que buscas.

Saludos

[jtriana6591]

buenos dias resulta que mi consulta esta:

CREATE PROCEDURE "DISCORDOBAHANA".PAD01
(
IN TipoVentaIni nvarchar(50), IN TipoVentaFin nvarchar(50),
IN fechaini timestamp, IN fechafin timestamp, 
IN DistribIni nvarchar(50), IN DistrinbFin nvarchar(50),
IN GroupIni nvarchar(50), IN GroupFin nvarchar(50), out result "DISCORDOBAHANA".PA01
)

LANGUAGE SQLSCRIPT READS SQL DATA
AS
BEGIN

result=SELECT IFNULL(T0."ItmsGrpNam", T1."ItmsGrpNam") AS "GRUPO",

      
		TO_VARCHAR(TO_DECIMAL((IFNULL(T0."VantIVA", 0) - IFNULL(T1."VantIVA", 0)),20,2)) AS "VENTA ANTES DE IVA",
		TO_VARCHAR(TO_DECIMAL((IFNULL(T0.IVA, 0) - IFNULL(T1.IVA, 0)),20,2)) AS "IVA",
		TO_VARCHAR(TO_DECIMAL((IFNULL(T0."VIVAinclu", 0) - IFNULL(T1."VIVAinclu", 0)),20,2)) AS "VENTA IVA INCLUIDO"
		FROM (SELECT
		
		 T5."ItmsGrpNam", SUM(T1."LineTotal") AS "VantIVA", SUM(T1."VatSum") AS "IVA",
		SUM(T1."LineTotal" + T1."VatSum") AS "VIVAinclu"
		FROM OINV T0 INNER JOIN INV1 T1 ON T0."DocEntry" = T1."DocEntry"
		
		INNER JOIN OITM T4 ON T1."ItemCode" = T4."ItemCode"
		INNER JOIN OITB T5 ON T4."ItmsGrpCod" = T5."ItmsGrpCod"
		
		WHERE T0."U_TIPO_V" >= :TipoVentaIni AND T0."U_TIPO_V" <= :TipoVentaFin
		AND T0."DocDate" >= :fechaini AND T0."DocDate" <= :fechafin
	
		AND T1."OcrCode" >= :DistribIni AND T1."OcrCode" <= :DistrinbFin
		AND T5."ItmsGrpNam" >= :GroupIni AND T5."ItmsGrpNam" <= :GroupFin
		 
		GROUP BY T5."ItmsGrpNam") AS T0 
		
		LEFT OUTER JOIN (SELECT T5."ItmsGrpNam", SUM(T1."LineTotal") AS "VantIVA",
		SUM(T1."VatSum") AS "IVA", SUM(T1."LineTotal" + T1."VatSum") AS "VIVAinclu"
		FROM ORIN T0 INNER JOIN RIN1 T1 ON T0."DocEntry" = T1."DocEntry"
		INNER JOIN OITM T4 ON T1."ItemCode" = T4."ItemCode"
		INNER JOIN OITB T5 ON T4."ItmsGrpCod" = T5."ItmsGrpCod"
		WHERE T0."U_TIPO_V" >= :TipoVentaIni AND T0."U_TIPO_V" <= :TipoVentaFin
		AND T0."DocDate" >= :fechaini AND T0."DocDate" <= :fechafin
		AND T1."OcrCode" >= :DistribIni AND T1."OcrCode" <= :DistrinbFin
		AND T5."ItmsGrpNam" >= :GroupIni AND T5."ItmsGrpNam" <= :GroupFin
		GROUP BY T5."ItmsGrpNam") AS T1 ON T0."ItmsGrpNam" = T1."ItmsGrpNam"

	ORDER BY 1;

 end;

a esa consulta necesito restarle las facturas calnceladas osea cuando T0.“CANCELED” = ‘Y’ entonces SUM(-T1.“LineTotal”) pero no se como arregar esa linea
es una condicion