Skip to Content
0
May 27, 2014 at 05:25 AM

Sales Analysis Report query

1520 Views

Hi,

I'm trying to make a query that mimics the Sales Analysis Report by Customer (detailed) in SAP B1. Here's my query below.

SELECT DocType, DocNum, DocDate, CardCode, SalesAmount, AppliedAmount

FROM

(

(SELECT 'IN' DocType, DocNum, DocDate, CardCode, DocTotal - VatSum SalesAmount,

ISNULL(AppliedTotal,0)+ISNULL(RCTTotal,0)+ISNULL(VPMTotal,0)AppliedAmount

FROM OINV LEFT OUTER JOIN

--Deliveries, A/R Credit Memos

(SELECT BaseEntry, SUM(LineTotal)-SUM(Disc)AppliedTotal

FROM

(SELECT BaseEntry, LineTotal, DLN1.VatSum, LineTotal * ODLN.DiscPrcnt / 100 Disc

FROM ODLN INNER JOIN DLN1

ON ODLN.DocEntry = DLN1.DocEntry

WHERE BaseType = 13

UNION

SELECT BaseEntry, LineTotal, RIN1.VatSum, LineTotal * ORIN.DiscPrcnt / 100 Disc

FROM ORIN INNER JOIN RIN1

ON ORIN.DocEntry = RIN1.DocEntry

WHERE BaseType = 13)O

GROUP BY BaseEntry)DNCM

--Incoming Payments

ON OINV.DocEntry=DNCM.BaseEntry LEFT OUTER JOIN

(SELECT RCT2.DocEntry, SUM(SumApplied - vatApplied) AS RCTTotal

FROM ORCT INNER JOIN RCT2

ON ORCT.DocNum = RCT2.DocNum

WHERE Canceled <> 'Y' AND InvType = 13 --AND RCT2.DocEntry = 202883

GROUP BY RCT2.DocEntry)RCT

ON OINV.DocEntry=RCT.DocEntry LEFT OUTER JOIN

--Outgoing Payments

(SELECT VPM2.DocEntry, SUM(SumApplied - vatApplied) AS VPMTotal

FROM OVPM INNER JOIN VPM2

ON OVPM.DocNum = VPM2.DocNum

WHERE Canceled <> 'Y' AND InvType = 13

GROUP BY VPM2.DocEntry)VCH

ON OINV.DocEntry=VCH.DocEntry)

UNION

--Credit Note

(SELECT 'CN' DocType, DocNum, DocDate, CardCode,

(DocTotal - VatSum)*-1 SalesAmount, (DocTotal - VatSum)*-1 AppliedTotal

FROM ORIN)

) Z

WHERE CardCode = '006-968-065-000'

ORDER BY DocDate, DocNum

I'm pretty much getting the Invoices and Credit Memos from the OINV and ORIN tables. For the Invoice's Applied Amount, I'm reconciling them with Deliveries, Credit Memos, Incoming Payments and Outgoing Payment to get the Applied Amount column. My problem is when the invoice has a manual reconciliation that cause my query above to be wrong in those situations.

Is there another way of getting the output that I want or can I get the information I need from another table or view? How can I get the correct Applied Amount that is being displayed on the Invoice form of SAP B1?