on 06-24-2010 2:20 AM
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
Self Resolved
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.