Skip to Content
0
Jun 10, 2014 at 04:32 AM

Sales Analysis with AR Downpayment Invoice

30 Views

Hi all

Please help if you know how to rectify this customised stored procedure for Sales Analysis Crystal Report.

It does not reflect the Sales amount for AR Invoice that is linked to a fully paid Downpayment Invoice.

[SBODemoSG]




ANSI_NULLS ON



QUOTED_IDENTIFIER ON



PROC [dbo].[GPbySP]


datetime, @DateTo datetime, @SlpFrom nvarchar(20), @SlpTo nvarchar(20)



'IN' as DocType, T0.DocNum, T0.DocDate, T1.CardName, T0.DocCur, T0.DocRate,


WHEN T0.DocCur=(SELECT TOP 1 T10.MainCurncy FROM OADM T10) THEN T0.DocTotal ELSE T0.DocTotalFC END as 'DocAmt',


.DocTotal-T0.VatSum as 'DocTotalBefTax', T0.VatSum, T0.DocTotal,


.ItemCode, T3.ItemName, T2.Quantity, T2.LineTotal, T2.GrossBuyPr as 'ItemCost', T2.GrssProfit, T5.SlpName, T5.SlpCode,


.GrosProfit


OINV T0


JOIN OCRD T1 ON T0.CardCode=T1.CardCode


JOIN INV1 T2 ON T0.DocEntry=T2.DocEntry


JOIN OITM T3 ON T2.ItemCode=T3.ItemCode


JOIN OITB T4 ON T3.ItmsGrpCod=T4.ItmsGrpCod


JOIN OSLP T5 ON T0.SlpCode=T5.SlpCode


T0.DocDate BETWEEN @DateFrom AND @DateTo AND T5.SlpName BETWEEN @SlpFrom AND @SlpTo


ALL


'CN' as DocType, T0.DocNum, T0.DocDate, T1.CardName, T0.DocCur, T0.DocRate,


WHEN T0.DocCur=(SELECT TOP 1 T10.MainCurncy FROM OADM T10) THEN T0.DocTotal*-1 ELSE T0.DocTotalFC*-1 END as 'DocAmt',


T0.DocTotal-T0.VatSum)*-1 as 'DocTotalBefTax', T0.VatSum*-1, T0.DocTotal*-1,


.ItemCode, T3.ItemName, T2.Quantity*-1, T2.LineTotal*-1, T2.GrossBuyPr*-1 as 'ItemCost', T2.GrssProfit*-1, T5.SlpName, T5.SlpCode,


.GrosProfit*-1


ORIN T0


JOIN OCRD T1 ON T0.CardCode=T1.CardCode


JOIN RIN1 T2 ON T0.DocEntry=T2.DocEntry


JOIN OITM T3 ON T2.ItemCode=T3.ItemCode


JOIN OITB T4 ON T3.ItmsGrpCod=T4.ItmsGrpCod


JOIN OSLP T5 ON T0.SlpCode=T5.SlpCode


T0.DocDate BETWEEN @DateFrom AND @DateTo AND T5.SlpName BETWEEN @SlpFrom AND @SlpTo