Purchase Analysis and Sales Analysis Report by Warehouse

[Former Member]

Hi Experts,

Good day!

I would like to know if there's a query designed to filter Sales Analysis/Purchase Analysis Reports by warehouse. In the selection criteria reports in SAP, there's no field for warehouse.

Thanks!

Ashley

[kothandaraman_nagarajan · Accepted solution]

Hi,

There is no standard report in SAP for sales and purchase based on ware house selection.

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi Nagarajan,

But is there aa query for this?

Thanks!

Ashley

[Former Member · Accepted solution]

Hi

Please refer the below query and add the warehouse field in it.

DECLARE

@StartDate DATETIME,

@EndDate DATETIME,

@FrmCard varchar(300),

@ToCard varchar(300),

@Dummy INTEGER

SELECT TOP 1 @Dummy = DocNum

FROM OINV T0

WHERE T0.DocDate >='[%2]' AND T0.DocDate <= '[%3]' AND

             T0.CardCode>='[%0]'  AND T0.CardCode <='[%1]'

SELECT

@FrmCard='[%0]',

@ToCard='[%1]',

@StartDate='[%2]' ,

@EndDate = '[%3]'

SELECT I0.CardCode,I0.CardName,I3.GroupName[Customer Group] ,I4.SlpName[Sales Employee] ,

  SUM(ISNULL(I0.Number,0))[Total Invoices],

  SUM(ISNULL(I0.[Total],0))[Total A/R Invoice],    

  SUM(ISNULL(I0.Profit,0))[Gross Profit],

  SUM(ISNULL(I0.Profit,0))*100/SUM(ISNULL(I0.[Total],0))[Gross Profit %]   

FROM(

   SELECT T0.CardCode,T0.CardName,T0.SlpCode, 

          COUNT(ISNULL(T0.DocEntry,0))[Number],SUM(ISNULL(T0.DocTotal,0)-(ISNULL(T0.VatSum,0)+ISNULL(T0.RoundDif,0)+ISNULL(TotalExpns,0) ))[Total],

  SUM(T0.GrosProfit)[Profit],(SUM(T0.GrosProfit)*100)/SUM(T0.DocTotal)[Profit%],

  SUM(DocTotal)[DocTotal],SUM(PaidSum)[PaidSum],SUM(DpmAmnt)[DPMAmt]

   FROM OINV T0

   WHERE T0.DocDate >=@StartDate  AND T0.DocDate <= @EndDate  AND T0.CardCode > =@FrmCard  AND T0.CardCode < =@ToCard

  GROUP BY T0.CardCode,T0.CardName,T0.SlpCode 

UNION ALL

  SELECT T0.CardCode,T0.CardName,T0.SlpCode,

  COUNT(ISNULL(T0.DocEntry,0))[Number],SUM(ISNULL(T0.DocTotal,0)-(ISNULL(T0.VatSum,0)+ISNULL(T0.RoundDif,0)+ISNULL(TotalExpns,0)))*-1[Total],

  SUM(T0.GrosProfit)*-1[Profit],(SUM(T0.GrosProfit)*100)/SUM(T0.DocTotal)*-1[Profit%],

  SUM(T0.DocTotal)*-1[DocTotal],SUM(T0.PaidSum)*-1[PaidSum],SUM(T0.DpmAmnt)*-1[DPMAmt]    

  FROM ORIN T0

  WHERE T0.DocDate >=@StartDate  AND T0.DocDate <= @EndDate  AND T0.CardCode > =@FrmCard  AND T0.CardCode < =@ToCard

  GROUP BY T0.CardCode,T0.CardName,T0.SlpCode 

)[I0] 

  JOIN OCRD I2 ON I0.CardCode =I2.CardCode

JOIN OCRG I3 ON I3.GroupCode =I2.GroupCode

JOIN OSLP I4 ON I0.SlpCode = I4.SlpCode

GROUP BY I0.CardCode,I0.CardName,I3.GroupName,I4.SlpName   

ORDER BY I0.CardCode

Regards,

Tausif

[kothandaraman_nagarajan · Accepted solution]

Try for purchase analsysis based on AP invoice:

SELECT T0.[CardCode], T0.[CardName], sum( T1.[LineTotal]) as Total, T1.[WhsCode] FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[WhsCode]  between [%0] and [%1] and  T0.[DocType] = 'i' and  T0.[DocDate] between [%2] and [%3] GROUP BY T0.[CardCode], T0.[CardName], T1.[WhsCode]

union all

SELECT T0.[CardCode], T0.[CardName], -sum( T1.[LineTotal]) as Total, T1.[WhsCode] FROM ORPC T0  INNER JOIN RPC1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[WhsCode]  between [%0] and [%1] and  T0.[DocType] = 'i' and  T0.[DocDate] between [%2] and [%3] GROUP BY T0.[CardCode], T0.[CardName], T1.[WhsCode]

[Former Member · Accepted solution]

Hi Nagarajan,

Yes. The query is working perfectly. Thanks for your help!

Ashley

[kothandaraman_nagarajan]

Did you check my query?

[former_member188586]

HI Leigh Ashley Loyola

please check bellow thread

Thanks&Regards

AndakondaRamudu

[Former Member]

Hi AndakondaRamudu,

Thanks for your reply but the thread is for Project only. I am looking for warehouse.

Thanks!

Ashley

[former_member188586]

HI

changed into Where House wise and try it, That time i tried with all replays No Hope ,

My Suggestion is try  with Separate Reports and try it