QUERY REPORT FOR ALLOCATION ACCOUNT (GOODS RECIEVED NOT INVOICED)

[Former Member]

Dear Experts,

I am using SBO for Hana, SAP 9.2 PL8.

I wish to generate a report that will show all open GRPO & Goods Return at a specific date and tie the amount therein to the balance in the allocation account.

I have come up with the following query:

SELECT T0."ObjType", T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T1."OpenCreQty" "Open Quantity",T1."Price", T1."OpenCreQty"*T1."Price" "Open GRPO Amount", T1."LineStatus" FROM OPDN T0 INNER JOIN PDN1 T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."DocDate" <= [%0] AND T0."DocStatus" = 'O' AND T1."LineStatus" = 'O' AND T1."AcctCode" = '1021101' UNION ALL SELECT T0."ObjType", T0."DocNum", T0."DocDate", T0."CardCode", T0."CardName", T1."OpenCreQty" "Open Quantity", T1."Price", T1."OpenCreQty"*T1."Price" "Open GRPO Amount", T1."LineStatus" FROM ORPD T0 INNER JOIN RPD1 T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."DocDate" <= [%0] AND T0."DocStatus" = 'O' AND T1."LineStatus" = 'O' AND T1."AcctCode" = '1021101'

The above query gives the correct report if ran for the current date. If ran for a past date, say 30.06.2017, it will exclude documents that were open then (30.06.2017) but which have since been closed. This will mismatch with the allocation account since the journal entry that closed the document was posted later (i.e in July 2017)

Kindly assist me amend the query.

Regards

George Njuguna