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?
*This post is locked for comments
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)
Thank you Elaine
Will this script work also in version SL 2011, in this version we also have the "EP" payment type?
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
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
While I wish all bugs are fixed in the next version, many are not.
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.
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.
With no template loaded it doesn't generate at all. With some of our templates it runs.
Are you using a template when you run it?
stock reports and our customized one gives the same message
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (