Ayuda SAP

Restar las notas credito a las facturas

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

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

1 me gusta

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

Este tema se cerró automáticamente 91 días después del último post. No se permiten nuevas respuestas.