cancel
Showing results for 
Search instead for 
Did you mean: 

Sales Analysis with AR Downpayment Invoice

kedalenechong
Participant
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try to add below portion in above query with union:

'DP' 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


ODPI 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

Thanks & Regards,

Nagarajan