Query to check if payment receipt

[Former Member]

Hello All,

One of my client has a requirement, he needs to check every week if payment is received for all invoices having "Cash Against Delveiry" payment term.

This payment terms is fixed at at customer master data.

[Former Member · Accepted solution]

Hi Hari,

Kindly use this query

select v.DocNum ,v.DocDate ,v.CardCode ,v.CardName ,v.DocTotal ,v.PaidToDate 'Amount received'

,CASE when v.DocTotal = v.PaidToDate then 'Fully received' when v.PaidToDate>0 then 'Partially Received' else 'Not Received' end Status

from OINV v inner join OCTG g on v.GroupNum =g.GroupNum

where g.PymntGroup ='Cash Against Delveiry' and v.CANCELED='N'  and v.DocDate>=[%0] and v.DocDate<=[%1]

Regards,

BHARATHIRAJA

[Former Member · Accepted solution]

Hello Bhararthiraja,

It is working fine, I'm getting currency used in document. Here my BP currency is different from system & local currency.

Is it possible to get the amount in particular currency? I would provide the exchagne rate for them.

Thankfully

Hari

[Former Member · Accepted solution]

Hi Hari,

Check this query,

select v.DocNum ,v.DocDate ,v.CardCode ,v.CardName ,v.DocCur 'Currency',v.DocRate 'Exchange rate',v.DocTotal,v.DocTotalFC 'Foreign Currency Total'  ,v.PaidToDate 'Amount received'

,CASE when v.DocTotal = v.PaidToDate then 'Fully received' when v.PaidToDate>0 then 'Partially Received' else 'Not Received' end Status

from OINV v inner join OCTG g on v.GroupNum =g.GroupNum

where g.PymntGroup ='Cash Against Delveiry' and v.CANCELED='N' and v.DocDate>=[%0] and v.DocDate<=[%1]

Regards,

BHARATHIRAJA

[Former Member · Accepted solution]

Thank you Bharathiraja