Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Answered

Period Sensitive Aged AP Crystal Error SL2015

Posted on by Microsoft Employee

Hi Everyone,

I just upgraded to SL 2015 from 2011, however my period sensitive aged AP report keeps generating an error in Crystal:

Failed to retrieve data from the database

database vendor code: 8625

I get this for the detail, summary and our customized detail report

Anyone encountered this?

  • Marisol Caamaño Profile Picture
    Marisol Caamaño 90 on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    I'm having this same error after migrating from 2011 to 2015,  I altered this view and report is still showing old PP that have balance 0  but report is showing its original balance which affects all totals and everything is desbalanced.

    I really don't know what else to do because this is afecting all my companies and now its a big issue. Do you know if there is available any other fix (either views, sp or reports)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    Thank you Elaine

    Will this script work also in version SL 2011, in this version we also have the "EP" payment type?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

     

    Here is the SQL script for 03.68 attached to bug 18769  for version 2015

     

    alter view vr_03681_docs as

    SELECT r.ri_id, rc.cpnyname,

           d.acct APAcct, d.sub APSub,

           d.cpnyid, d.vendid, d.doctype,

           d.refnbr, d.docdate, d.perpost, d.S4Future11,

           d.terms, d.perclosed, d.status,

           d.CuryId, d.InvcNbr, d.DiscDate, d.MasterDocNbr,

           d.User1 docUser1,d.User2 docUser2,

           d.User3 docUser3,d.User4 docUser4,

           d.User5 docUser5,d.User6 docUser6,

           d.User7 docUser7,d.User8 docUser8,

           r.reportdate, d.Paydate,

           d.InvcDate,

           jadjamt = ISNULL(j.adjamt,0),

           jcuryadjdamt = ISNULL(j.curyadjdamt,0),

           CuryOrigDocAmt = CASE WHEN  d.doctype in ('AD','PP')

                                  THEN -d.CuryOrigDocAmt

                                  ELSE  d.CuryOrigDocAmt

                              END,

           CurrBalance = CASE WHEN d.doctype = 'PP'

                              THEN -d.curyorigdocamt + isnull(jpp.curyadjdamt,

                                                    CASE WHEN j.ADJDRefNbr IS NOT NULL

                                                         THEN CASE WHEN(SELECT top 1 VENDID

                                                                          FROM APADJUST vc

                                                                         WHERE vc.ADJDREFNBR = j.ADJDRefNbr

                                                                         and vc.ADJGDocType = 'VC'

                                                                         and vc.AdjdDocType = 'PP'

                                                                          AND vc.VENDID = d.vendid

                                                                          AND vc.perappl <= r.begpernbr)IS NULL

                                                                    THEN 0

                                                                    ELSE d.curyorigdocamt

                                                                     END

                                                         ELSE d.curyorigdocamt END)

                                                 ELSE

                                       CASE WHEN  d.doctype in( 'AD')

                                       THEN -convert(dec(28,3),d.CuryOrigDocAmt)

                                                   WHEN d.doctype in ('CK','HC','EP')

                                                      THEN convert(dec(28,3),(d.CuryDocBal))

                                       ELSE  convert(dec(28,3),d.CuryOrigDocAmt)                                                  

                          END - (CASE WHEN d.doctype IN ('CK','HC','EP')

                                         THEN ISNULL(c.curyadjgamt,0)

                                         ELSE ISNULL(j.curyadjdamt,0)

                                     END)

                                        END,

           DueDate = CASE WHEN  duedate = CONVERT(smalldatetime,'')

                                   THEN CASE d.docdate

                                                 WHEN CONVERT(smalldatetime,'')

                                     THEN GETDATE()

                                     ELSE d.docdate

                                  END

                                  ELSE  d.duedate

                       END,

           OrigDocAmt = CASE WHEN  d.doctype in ( 'AD', 'PP')

                                      THEN -d.OrigDocAmt

                                      ELSE  d.OrigDocAmt

                          END,

           Balance =   CASE WHEN d.doctype = 'PP'

                            THEN -d.origdocamt + isnull(jpp.adjamt + jpp.curyrgolamt,

                                                    CASE WHEN j.ADJDRefNbr IS NOT NULL

                                                         THEN CASE WHEN(SELECT top 1 VENDID

                                                                          FROM APADJUST vc

                                                                         WHERE vc.ADJDREFNBR = j.ADJDRefNbr

                                                                         and vc.ADJGDocType = 'VC'

                                                                         and vc.AdjdDocType = 'PP'

                                                                          AND vc.VENDID = d.vendid

                                                                          AND vc.perappl <= r.begpernbr)IS NULL

                                          THEN 0

                                                                    ELSE d.OrigDocAmt

                                                                     END

                                                         ELSE d.origdocamt END)

                                                 ELSE

                                    CASE WHEN  d.doctype in ( 'AD')

                            THEN -convert(dec(28,3),d.OrigDocAmt)

                                       WHEN d.doctype in ('CK','HC','EP')

                                                 THEN convert(dec(28,3),(d.DocBal))

                            ELSE convert(dec(28,3),d.OrigDocAmt)

                       END - (CASE WHEN d.doctype IN ('CK','HC','EP')

                                      THEN ISNULL(c.adjamt,0)

                                      ELSE ISNULL(j.adjamt,0)                                                           

                                 END)

                                     END

     

             FROM rptruntime r INNER LOOP JOIN RptCompany rc ON r.ri_id = rc.ri_id

                        JOIN ApDoc d ON d.cpnyid = rc.cpnyid

                                          AND d.perpost <= r.endpernbr

                                          AND

                                                 (d.perclosed > r.begpernbr or perclosed = '' OR

                                                                               (d.doctype IN ('PP','CK','HC','EP')))

                        LEFT  OUTER JOIN vr_03681_adjvo j ON d.vendid = j.vendid

                                                                                                             AND d.refnbr = j.adjdRefnbr

                                                                                                             AND d.doctype = j.adjdDoctype

                                                                                                             AND r.ri_id = j.ri_id

                                      LEFT OUTER JOIN vr_apreport_adjpp jpp ON d.vendid = jpp.vendid

                                                                                                                 AND d.refnbr = jpp.adjdRefnbr

                                                                                                                 AND d.doctype = jpp.adjdDoctype

                                                                                                                 AND r.ri_id = jpp.ri_id

                                      LEFT  OUTER JOIN vr_03681_adjchk c ON d.vendid = c.vendid

                                                                                                             AND d.refnbr = c.adjgRefnbr

                                                                                                             AND d.acct = c.adjgacct

                                                                                                             AND d.sub = c.adjgsub

                                                                                                             AND r.ri_id =c.ri_id

                                           

     WHERE (doctype IN ('VO','AD','AC','PP')

       AND d.status <> 'V' OR doctype IN ('HC','EP','CK')

       AND (d.perclosed > r.begpernbr or perclosed = '' or ISNULL(c.curyadjgamt,0) <> 0))

       AND d.rlsed = 1

     

    Elaine

  • Laura Profile Picture
    Laura 450 on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    I reviewed the altered view and compared it to the existing view in our Dynamics SL 2015 CU1 installation.  The differences I see is that the sections for CurrBalance and Balance for doctype = 'PP' had 'TOP 1' added to the select.

    I did not want to alter the entire view since the fix above left out doctypes of 'EP' in multiple places.  I assumed it was because these are newer doctypes than when the fix was created.

    Adding the TOP 1 to the select allowed the report to run without error.

    But now the Period Sensitive Aged AP included prepayments for old transactions that had a zero balance.  Even though they do not affect the balance for a vendor, they should not be on the report if they are zero.

    Can you advise if there are any updates on this? Also, I was unable to locate the knowledge base 974298 that was referred to.

    Thanks,

    Laura Vogel

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    While I wish  all bugs are fixed in the next version, many are not.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    Thanks Elaine, this resolved the issue.

    I'm surprised this wasn't addressed with the new version. I believe we made this update to 2011 but assumed it would have been corrected in 2015.

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    I wonder if  you lost a modified proc that had to do with bug 18769 when a prepayment is paid, the check voided and a new check issues.  See KB 974298.

    The modified script for ver 7 worked for ver8.  I haven't tested it in ver 9.  Here is that modified script.

    -- modifed for bug 18769

    alter view vr_03681_docs as

    SELECT r.ri_id, rc.cpnyname,

           d.acct APAcct, d.sub APSub,

           d.cpnyid, d.vendid, d.doctype,

           d.refnbr, d.docdate, d.perpost, d.S4Future11,

           d.terms, d.perclosed, d.status,

         d.CuryId, d.InvcNbr, d.DiscDate, d.MasterDocNbr,

           d.User1 docUser1,d.User2 docUser2,

           d.User3 docUser3,d.User4 docUser4,

           d.User5 docUser5,d.User6 docUser6,

           d.User7 docUser7,d.User8 docUser8,

           r.reportdate, d.Paydate,

           d.InvcDate,

           jadjamt = ISNULL(j.adjamt,0),

           jcuryadjdamt = ISNULL(j.curyadjdamt,0),

           CuryOrigDocAmt = CASE WHEN d.doctype in ('AD','PP')

                                  THEN -d.CuryOrigDocAmt

                                 ELSE d.CuryOrigDocAmt

                             END,

           CurrBalance = CASE WHEN d.doctype = 'PP'

                             THEN -d.curyorigdocamt + isnull(jpp.curyadjdamt,

                                                   CASE WHEN j.ADJDRefNbr IS NOT NULL

                                                         THEN CASE WHEN(SELECT TOP 1 VENDID

                                                                         FROM APADJUST vc

                                                                         WHERE vc.ADJDREFNBR = j.ADJDRefNbr

                                                                         and vc.ADJGDocType = 'VC'

                                                                         and vc.AdjdDocType = 'PP'

                                                                          AND vc.VENDID = d.vendid

                                                                         AND vc.perappl <= r.begpernbr)IS NULL

                                                                   THEN 0

                                                                    ELSE d.curyorigdocamt

                                                                     END

                                                         ELSE d.curyorigdocamt END)

                     ELSE

                     CASE WHEN d.doctype in( 'AD')

                                THEN -convert(dec(28,3),d.CuryOrigDocAmt)

                      WHEN d.doctype in ('CK','HC')

                           THEN convert(dec(28,3),(d.CuryDocBal))

                                ELSE convert(dec(28,3),d.CuryOrigDocAmt)                   

                         END - (CASE WHEN d.doctype IN ('CK','HC')

                                         THEN ISNULL(c.curyadjgamt,0)

                                         ELSE ISNULL(j.curyadjdamt,0)

                                     END)

                     END,

           DueDate = CASE WHEN duedate = CONVERT(smalldatetime,'')

                            THEN CASE d.docdate

                                 WHEN CONVERT(smalldatetime,'')

                                     THEN GETDATE()

                                     ELSE d.docdate

                                 END

                            ELSE d.duedate

                       END,

           OrigDocAmt = CASE WHEN d.doctype in ( 'AD', 'PP')

                              THEN -d.OrigDocAmt

                              ELSE d.OrigDocAmt

                        END,

           Balance =   CASE WHEN d.doctype = 'PP'

                           THEN -d.origdocamt + isnull(jpp.adjamt + jpp.curyrgolamt,

                                                   CASE WHEN j.ADJDRefNbr IS NOT NULL

                                                        THEN CASE WHEN(SELECT TOP 1 VENDID

                                                                         FROM APADJUST vc

                                                                         WHERE vc.ADJDREFNBR = j.ADJDRefNbr

                                                                         and vc.ADJGDocType = 'VC'

                                                                         and vc.AdjdDocType = 'PP'

                                                                        AND vc.VENDID = d.vendid

                                                                         AND vc.perappl <= r.begpernbr)IS NULL

                               THEN 0

                                                                   ELSE d.OrigDocAmt

                                                                     END

                                                         ELSE d.origdocamt END)

                      ELSE

                CASE WHEN d.doctype in ( 'AD')

                           THEN -convert(dec(28,3),d.OrigDocAmt)

                     WHEN d.doctype in ('CK','HC')

                      THEN convert(dec(28,3),(d.DocBal))

                           ELSE convert(dec(28,3),d.OrigDocAmt)

                       END - (CASE WHEN d.doctype IN ('CK','HC')

                                     THEN ISNULL(c.adjamt,0)

                                     ELSE ISNULL(j.adjamt,0)                       

                                 END)

                   END

             FROM rptruntime r INNER LOOP JOIN RptCompany rc ON r.ri_id = rc.ri_id

                       JOIN ApDoc d ON d.cpnyid = rc.cpnyid

                                         AND d.perpost <= r.endpernbr

                                         AND

                      (d.perclosed > r.begpernbr or perclosed = '' OR

                                  (d.doctype IN ('PP','CK','HC')))

                       LEFT OUTER JOIN vr_03681_adjvo j ON d.vendid = j.vendid

                                              AND d.refnbr = j.adjdRefnbr

                                              AND d.doctype = j.adjdDoctype

                                              AND r.ri_id = j.ri_id

                   LEFT OUTER JOIN vr_apreport_adjpp jpp ON d.vendid = jpp.vendid

                                                 AND d.refnbr = jpp.adjdRefnbr

                                                 AND d.doctype = jpp.adjdDoctype

                                                 AND r.ri_id = jpp.ri_id

                   LEFT OUTER JOIN vr_03681_adjchk c ON d.vendid = c.vendid

                                              AND d.refnbr = c.adjgRefnbr

                                              AND d.acct = c.adjgacct

                                              AND d.sub = c.adjgsub

                                              AND r.ri_id =c.ri_id

                                          

    WHERE (doctype IN ('VO','AD','AC','PP')

       AND d.status <> 'V' OR doctype IN ('HC','CK')

       AND (d.perclosed > r.begpernbr or perclosed = '' or ISNULL(c.curyadjgamt,0) <> 0))

       AND d.rlsed = 1

    The bug has not been fixed.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    With no template loaded it doesn't generate at all. With some of our templates it runs.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    Are you using a template when you run it?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Period Sensitive Aged AP Crystal Error SL2015

    stock reports and our customized one gives the same message

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,459 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,783 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans