on 06-10-2014 5:32 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.