Store procedure Reporte de Ventas – Notas de Credito SAP HANA

este store procedure esta diseñado para ejecutarse con Cristal Reports ya que la versión de Hana la mejor manera de ejecutar reportes son con Sp de esta manera se crear el SP en Hana studio tan simple con CopyPaste en la segunda parte del Unión se encuentra la parte de notas de credito que los totales son multiplicados por *-1 para que a la hora de aplicar la Suma se resten correctamente este Sp lo que realiza es generar la información de manera detallada con los item de los artículos como por ejemplo Código ,Descripción, Cantidad, total, nombre de cliente, Grupo de Clientes y saber si Es Crédito o Contado.

esperamos que te funciones correctamente

CREATE PROCEDURE "SBO_SP_RPT_Detallado_Ventas" 
 
( 
IN FechaInicial DATE, 
IN FechaFinal DATE 
) 
 
LANGUAGE SQLSCRIPT 
AS  
BEGIN 
Select 
'FC' AS "Documento" 
 
,T0."ItemCode"  as “Codigo”
,CAST(T0."DocEntry" AS NVARCHAR(50)) AS  "DocEntry" 
,T4."DocDate" AS "Fecha" 
,T4."Series" AS "Series" 
,T4."DocNum" AS "NoDocumento" 
,T3."OcrCode" AS "CodSucursal" 
,T3."OcrName" AS "Sucursal" 
,T5."WhsName" AS "Bodega" 
,T2."ItmsGrpNam" AS "ItmsGrpNam"  
,T0."DocDate" AS "DocDate" 
,T0."Dscription" AS "Dscription"  
,T0."unitMsr" AS "unitMsr" 
,T0."Quantity" AS "Quantity" 
,T0."PriceAfVAT" AS "PriceAfVAT"  
,T0."DiscPrcnt" AS "DiscPrcnt" 
,T0."GTotal" AS "GTotal" 
,T0."GrssProfit" AS "GrssProfit" 
,T4."CardName"AS "NombreCliente" 
,T7."ListName"AS "Nombre_LP" 
,T8."OnHand" 
,(Select Sum (B."OnHand") 
From OITW B 
Where B."ItemCode"= T0."ItemCode") as "StockTotal" 
, Case when T4."GroupNum" = '1' THEN 'CONT.' 
  when T4."GroupNum" = '17'THEN 'CONT.' 
 else 'CRED.' end "TipoV" 
        
,T0."Price" 
 
from  
INV1 T0 --"_SBOV_Ventas_FC" 
INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode" 
INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod" 
INNER JOIN OOCR T3 ON T0."OcrCode" = T3."OcrCode" 
INNER JOIN OINV T4 ON T0."DocEntry" = T4."DocEntry" 
INNER JOIN OWHS T5 ON T0."WhsCode" = T5."WhsCode" 
INNER JOIN OITWT8 ON T0."WhsCode" = T8."WhsCode" AND T8."ItemCode" = T0."ItemCode" 
 INNER JOIN OCRD T6 ON T4."CardCode" = T6."CardCode"  
 INNER JOIN OPLN T7 ON T6."ListNum" = T7."ListNum" 
Where 
T4."CANCELED"='N' 
and T4."U_DoctoFiscal" = 'S' AND T4."DocDate" >= :FechaInicial and T4."DocDate" <= :FechaFinal 
---AND T3."OcrCode" IN (:Sucursal) 
--order by T0."DocEntry" 
 
 
UNION  -----la unión de la tabla de ventas y tabla de Notas de Credito 
 
Select 
'NC' AS "Documento" 
,T0."ItemCode" 
,CAST(T0."DocEntry" AS NVARCHAR(50)) AS  "DocEntry" 
,T4."DocDate" AS "DocDate" 
,T4."Series" AS "Series" 
,T4."DocNum" AS "DocNum" 
,T3."OcrCode" AS "CodSucursal" 
,T3."OcrName" AS "Sucursal" 
,T5."WhsName" AS "Bodega" 
,T2."ItmsGrpNam" AS "ItmsGrpNam"  
,T0."DocDate" AS "DocDate" 
,T0."Dscription" AS "Dscription"  
,T0."unitMsr" AS "unitMsr" 
,(T0."Quantity" *-1) AS "Quantity" 
,(T0."PriceAfVAT"*-1) AS "PriceAfVAT"  
,(T0."DiscPrcnt"*-1) AS "DiscPrcnt" 
,(T0."GTotal"*-1) AS "GTotal" 
,(T0."GrssProfit"*-1) AS "GrssProfit" 
,T4."CardName"AS "NombreCliente" 
,T7."ListName"AS "Nombre_LP" 
,T8."OnHand" 
,(Select Sum (B."OnHand") 
From OITW B 
Where B."ItemCode"= T0."ItemCode")as "StockTotal" 
,Case when T4."GroupNum" = '1' THEN 'CONT.' 
when T4."GroupNum" = '17'THEN 'CONT.' 
 else 'CRED.' end "TipoV" 
,T0."Price" 
from  
RIN1 T0 --"_SBOV_Ventas_FC" 
INNER JOIN OITM T1 ON T0."ItemCode" = T1."ItemCode" 
INNER JOIN OITB T2 ON T1."ItmsGrpCod" = T2."ItmsGrpCod" 
INNER JOIN OOCR T3 ON T0."OcrCode" = T3."OcrCode" 
INNER JOIN ORIN T4 ON T0."DocEntry" = T4."DocEntry" 
INNER JOIN OWHS T5 ON T0."WhsCode" = T5."WhsCode" 
INNER JOIN OITWT8 ON T0."WhsCode" = T8."WhsCode" AND T8."ItemCode" = T0."ItemCode" 
 INNER JOIN OCRD T6 ON T4."CardCode" = T6."CardCode"  
 INNER JOIN OPLN T7 ON T6."ListNum" = T7."ListNum" 
 
where  
T4."CANCELED" ='N' 
and T4."U_DoctoFiscal" = 'S' AND T4."DocDate" >= :FechaInicial and T4."DocDate" <= :FechaFinal 

order by T0."DocEntry" 
; 
 
END; 

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *