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
(SELECT 'IN' DocType, DocNum, DocDate, CardCode, DocTotal - VatSum SalesAmount,
FROM OINV LEFT OUTER JOIN
--Deliveries, A/R Credit Memos
(SELECT BaseEntry, SUM(LineTotal)-SUM(Disc)AppliedTotal
(SELECT BaseEntry, LineTotal, DLN1.VatSum, LineTotal * ODLN.DiscPrcnt / 100 Disc
FROM ODLN INNER JOIN DLN1
ON ODLN.DocEntry = DLN1.DocEntry
WHERE BaseType = 13
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
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
(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
(SELECT 'CN' DocType, DocNum, DocDate, CardCode,
(DocTotal - VatSum)*-1 SalesAmount, (DocTotal - VatSum)*-1 AppliedTotal
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?