Open Items List Query.

[Former Member]

Hi all,

Could you please advise if itu2019s possible to run a query in SAP based on the Open Items List in the reporting area that can detail the following;

Both AR Invoices and Credit Notes and show additional fields, taken from the AR invoice u2013 Account Manager, Item Group and Project Code?

The Project Code and Item Group can be taken from the Item Line however the Account Manager field is located below the item line area on the contents tab.

Would someone have a query which might enable us to do this or at least give us a directing to go in ?

Thank you,

MB

[Former Member]

Hi Jesper,

Just to let you know that it is 1 line per document.

Thank you,

MB

[JesperB1]

Hi Matthew,

You can try this one.

It will check if the document is open on document level. The query is written so that it will give a result for every line in the document so you might have a problem if there are many line per document. For the account manager I used Sales person, I am not sure if that is what you mean.

select 
'AR Invoice'+' '+(convert(varchar (10), t0.docnum)) as 'Document', 
(select t7.slpname from oslp t7 where t7.slpcode = t0.slpcode) as 'Account Manager', 
(select itmsgrpnam from oitm t5 inner join oitb t6 on t5.itmsgrpcod = t6.itmsgrpcod 
where t5.itemcode = t1.itemcode) as 'Item Group',
t1.project 
from 
oinv t0 inner join inv1 t1 on t0.docentry = t1.docentry 
where t0.docstatus = 'o'

union 

select 
'AR Credit'+' '+(convert(varchar (10), t0.docnum)) as 'Document', 
(select t7.slpname from oslp t7 where t7.slpcode = t0.slpcode) as 'Account Manager', 
(select itmsgrpnam from oitm t5 inner join oitb t6 on t5.itmsgrpcod = t6.itmsgrpcod 
where t5.itemcode = t1.itemcode) as 'Item Group',
t1.project 
from 
orin t0 inner join rin1 t1 on t0.docentry = t1.docentry 
where t0.docstatus = 'o'

Let us know if it works out.

Jesper

[JesperB1]

Hi Matthew,

Do you want 1 line per document or one line per row in each document. In case there are many different project codes and item groups in the lines of one document this will quite a difference.

Let us know.

Jesper