cancel
Showing results for 
Search instead for 
Did you mean: 

Query: Incoming Payments

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Henry,

Welcome you post on the forum.

Try:

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 t1.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

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

Thanks a lot for your prompt response; it worked like magic

Regards,

Henry

Answers (0)