cancel
Showing results for 
Search instead for 
Did you mean: 

A/P Cash Discounts Query

Former Member
0 Kudos

Hello Experts,

I am trying to query the list of all Open AP Invoices which are eligible for Cash Discounts.

Scenario : For Instance in OEC Computers,I have the following Open documents in SAP

DocNum CardCode PostingDate DueDate PaymentTerms

110 V10000 06/18/2010 07/19/2010 2P10Net30 ( Eligible for 2 % discount if paid within 10 days from posting date)

114 V20000 06/18/2010 08/09/2010 2P10Net45 ( Eligible for 2 % discount if paid within 10 days from posting date)

Let us say a user wants to run a check today and would like to know what documents are eligible for cash discounts based on duedate 07/02/10.

I am using the following query but does not seems to be working exactly:

select t0.DocNum,t0.CardCode,t0.CardName

,t0.DocTotal as Amount

,t0.DocDate as PostingDate

,t0.DocDueDate as DueDate

,t1.PymntGroup as PaymentTerms

,t2.Discount

,t1.ExtraDays

,t2.NumOfDays

from OPCH t0 inner join OCTG t1 on t0.GroupNum=t1.GroupNum

inner join CDC1 t2 on t1.DiscCode=t2.CdcCode

where T0.DocDate>= '[%0]' AND T0.DocDueDate<= '[%1]'+DATEADD(DAY,10,t0.DocDate) and t0.DocStatus='O'

Any help would be appreciated.

Thanks,

Praneeth

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Self Resolved

Former Member
0 Kudos

Hi Praneeth,

Try this:

from OPCH t0

inner join OCTG t1 on t0.GroupNum=t1.GroupNum

inner join CDC1 t2 on t1.DiscCode=t2.CdcCode

where t0.DocStatus='O' AND DateDiff(DD,T0.DocDate, GetDate())< T2.NumOfDays

Thanks,

Gordon

Former Member
0 Kudos

Hi Gordon,

This works fine , but the user needs to input Due Date. Based on the due date, the query should return the documents that are eligible for cash discounts.

For Instance :

Assume that there are two Open AP Invoice Documents 110,114 which has a posting date of 06/18/10.

There are eligible for cash discounts if paid with in 10 days from posting date i.e in this case 06/28/10.

Now the user wants to input the due date as 07/02/10 , because he would like all open AP Invoices that are eligible for cash discount with in this due date.

Since 06/28/10 falls with in the range of the user due date 07/02/10 the query should retrieve data.

Any advise would be appreciated.

Thanks,

Praneeth

Former Member
0 Kudos

Do you mean to restrict the result to only DueDate for that date?

Former Member
0 Kudos

Gordon,

Yes, we need to filter the result by due date. The user is intersted to know what Open AP Invoice documents are eligible for cash discount. Like i mentioned above if user enters due date as 07/02/10 the two documents 110,114 fall with in this range which are discount eligible. The query will return these two documents.

Regards,

Praneeth

Former Member
0 Kudos

Try this:

where t0.DocStatus='O' AND DateDiff(DD,T0.DocDate, GetDate())< T2.NumOfDays AND T0.DoCDueDate > '[%0\]'

Former Member
0 Kudos

Hi,

It's working fine, except that you have to change

  '%0' to [%0] 

Cheers

Edited by: Yeni Amin on Jun 24, 2010 5:26 AM

Former Member
0 Kudos

Hello Yeni,

When i copied directly from SQL to this message window it ignored [ ] .

Praneeth