Skip to Content
avatar image
Former Member

Customer Statement in SAP B1

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 17, 2017 at 06:33 AM
    Add comment
    10|10000 characters needed characters exceeded

  • Jun 17, 2017 at 08:02 PM

    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

    Add comment
    10|10000 characters needed 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)