on 06-26-2015 7:24 PM
Hi
Can any share me the Aging Query in SAP Business one.
Gopi
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Gopinath...
Have you checked the SAP standard one..
Business Partners --> Business Partners Reports -->Aging --> Customer Receivables Aging
If you need for customized one you can try this...
select T1.cardcode 'Bp Code',T1.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount', (sysdeb - syscred) as 'Balance Due',
case T0.transtype
when '13' then 'INV'
when '14' then 'AR CN'
when '24' then 'INCOMING'
else 'Other'
end 'Type',
Ref1,
fccurrency 'BP Currency',
CONVERT(VARCHAR(10), refdate, 103) 'Posting Date',
CONVERT(VARCHAR(10), duedate, 103) 'Due Date',
CONVERT(VARCHAR(10), taxdate, 103) 'Doc Date' ,
CASE
when (DATEDIFF(dd,refdate,current_timestamp))+1 < 31
then
case
when syscred <> 0 then -syscred
else sysdeb
end
end "0-30 days",
case when ((datediff(dd,refdate,current_timestamp))+1 > 30
and (datediff(dd,refdate,current_timestamp))+1< 61)
then
case
when syscred <> 0 then -syscred
else sysdeb
end
end "31 to 60 days",
case when ((datediff(dd,refdate,current_timestamp))+1 > 60
and (datediff(dd,refdate,current_timestamp))+1< 91)
then
case
when syscred <> 0 then -syscred
else sysdeb
end
end "61 to 90 days",
CASE
when (DATEDIFF(dd,refdate,current_timestamp))+1 > 90
then
case
when syscred= 0 then sysdeb
when sysdeb= 0 then -syscred
end
end "90 + days"
from dbo.JDT1 T0
INNER JOIN dbo.OCRD T1 ON T0.shortname = T1.cardcode and T1.cardtype = 'c'
where T0.intrnmatch = '0' and T0.BALDUEDEB != T0.BALDUECRED
ORDER BY T1.CARDCODE, T0.taxdate
Hope Helpful
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, Gopinath.
Do you need a query to get aging (customer and/or vendor) or do you need some way to get for that information from SAP Business One?
SAP B1 has 2 Aging reports:
If you really need a query, there's some posts in this forum about this theme. I recommend you do a search this forum.
Regards,
JC.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
98 | |
11 | |
11 | |
6 | |
6 | |
4 | |
4 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.