Skip to Content
0

Customer Statement in SAP B1

Jun 15, 2017 at 10:40 AM

141

avatar image
Former Member

Hi Experts,

I am creating a crystal report for customer statement.

Below is my query

select OCRD.cardcode 'Supplier Code',OCRD.cardname 'Name',sysdeb 'Debit GBP',syscred 'Credit GBP', (JDT1.BALDUEDEB - JDT1.BALDUECRED) as 'Balance Due',
case JDT1.transtype
when '-2' then 'Opening Balance'
when '13' then 'Invoice'
when '16' then 'Delivery Note'
when '24' then 'Incoming Payment'
when '203' then 'Incoming Payment'
else 'Other'
end 'Type',
JDT1.Ref1, OJDT.Memo,
fccurrency 'BP Currency',
CONVERT(VARCHAR(10), JDT1.refdate, 103)'Posting Date' ,
CONVERT(VARCHAR(10), JDT1.duedate, 103) 'Due Date',
CONVERT(VARCHAR(10), JDT1.taxdate, 103) 'Doc Date' ,

CASE
when DATEDIFF(dd,JDT1.taxdate,current_timestamp) < 31
then
case
when syscred <> 0 then syscred * - 1
else sysdeb
end
end "0-30 days",

case when (datediff(dd,JDT1.taxdate,current_timestamp) > 30
and datediff(dd,JDT1.taxdate,current_timestamp)< 61)
then
case
when syscred <> 0 then syscred * - 1
else sysdeb
end
end "31 to 60 days",

case when (datediff(dd,JDT1.taxdate,current_timestamp) > 60
and datediff(dd,JDT1.taxdate,current_timestamp)< 91)
then
case
when syscred <> 0 then syscred * - 1
else sysdeb
end
end "61 to 90 days",

CASE
when DATEDIFF(dd,JDT1.taxdate,current_timestamp) > 90
then
case
when syscred <> 0 then syscred * - 1
else sysdeb
end
end "90 + days"

from JDT1 inner join OCRD on JDT1.ShortName = OCRD.CardCode
inner join OJDT on JDT1.Transid = OJDT.TransId

where JDT1.shortname = OCRD.cardcode and OJDT.Transid = JDT1.TransId and cardtype = 'C' and intrnmatch = '0'
and JDT1.RefDate <= '2017-06-15 00:00:00.000'
and OCRD.CardCode = 'ABC0037'

ORDER BY OCRD.CARDCODE, JDT1.taxdate

I am not able to get the balance due hierarchy...! means same due amount is repeating for all the records as shown in the figure.

Please can anyone help me out for this?

cs01.jpg

cs01.jpg (160.4 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Rafaee Mustafa Jun 17, 2017 at 06:33 AM
0
Share
10 |10000 characters needed characters left characters exceeded
DIEGO LOTHER Jun 17, 2017 at 08:02 PM
0

Hi Jane,

If you use sql server 2012 or later, you can use this instruction:

Change:

(JDT1.BALDUEDEB - JDT1.BALDUECRED) as 'Balance Due',

By

SUM(JDT1.BALDUEDEB - JDT1.BALDUECRED) OVER (ORDER BY OCRD.CARDCODE, JDT1.taxdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Balance Due',

Hope it helps.

Kind Regards,

Diego Lother

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Diego,

Still the result is same.

Now my query is like,

select OCRD.cardcode 'Supplier Code',OCRD.cardname 'Name',sysdeb 'Debit USD',syscred 'Credit USD',
SUM(JDT1.BALDUEDEB - JDT1.BALDUECRED) OVER (ORDER BY OCRD.CARDCODE, JDT1.taxdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 'Balance Due',

case JDT1.transtype

when '-2' then 'Opening Balance'

when '13' then 'Invoice'

when '16' then 'Delivery Note'

when '24' then 'Incoming Payment'

when '203' then 'Incoming Payment'

else 'Other'

end 'Type',

JDT1.Ref1, OJDT.Memo,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), JDT1.refdate, 103)'Posting Date'

from JDT1,OCRD,OJDT where JDT1.shortname = OCRD.cardcode and OJDT.Transid = JDT1.TransId and cardtype = 'C' and intrnmatch = '0'
and JDT1.RefDate <= '2017-06-18 00:00:00.000'--{?JDT1.RefDate@}
and OCRD.CardCode = 'ABC0037'--'{?OCRD.CardCode@}'


ORDER BY OCRD.CARDCODE, JDT1.taxdate

I need running cumulative balance as shown in the figure below. And i want to add the opening balance to the query. how can i achieve this???

cs02.jpg

cs02.jpg (167.2 kB)
0