Skip to Content
0
Former Member
Oct 25, 2011 at 04:54 PM

Query: Incoming Payments

39 Views

Hi all,

I came up with the query below to capture details of Incoming payments as well as give out the names of the users who posted them into SAP. I linked OUSR with OINV hence it gives out the users' names only where the payment is linked to an Invoice. I would like the user name to appear even for payments on account. Kindly assist on the best way of achieving this.

SELECT

T1.DocNum Doc,

T4.[U_NAME] name,

T1.CardCode code,

T1.CardName Customer,

T1.TrsfrSum Transfer,

T1.CashSum Cash,

T1.CheckSum Chck,

T1.CreditSum Credit,

sum(T1.TrsfrSum + T1.CashSum + T1.CheckSum + T1.CreditSum) Total

from ORCT T1

left JOIN RCT1 T2 ON T1.DocNum = T2.DocNum

left outer join oinv t3 on t1.DocEntry=T3.ReceiptNum

left outer join ousr t4 on t3.usersign=t4.userid

WHERE (T1.DocDate>='[%0]' And T1.DocDate<='[%1]')

group by

T1.DocNum,

T1.CardCode,

T1.CardName,

T1.TrsfrSum,

T1.CashSum,

T1.CheckSum,

T1.CreditSum,

T4.[U_NAME]

Thank you in advance.

Rgds,

Henry