web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Credit Hold Criteria F...
Microsoft Dynamics GP (Archived)

Credit Hold Criteria For SQL Query

(0) ShareShare
ReportReport
Posted on by Ron Wilson 6,010

I am trying to write a query to define customers who need to be placed on hold as well as taken off hold.  My criteria are as follows:

To Place On Hold

  1. Customer should not currently be on hold
  2. If customer is over their credit limit or past their terms place them on hold

To Remove From Hold

  1. Customer should be on hold
  2. If customer is not over credit limit AND customer is within his terms remove the hold

 

I have a query written for both of these but it seems like I am missing something.  Would anyone be willing to take a shot at it?  I think the placing customer on hold is the easiest, but the taking them off is where I feel I am having an issue.  This is the query I have written for placing customer on hold:

 

select

case

when d.custblnc>a.crlmtamt then 'Credit Limit'

when c.duedate < (getdate()+(b.duedtds+duegrper)) then 'Terms Exceeded'

when ((d.custblnc>a.crlmtamt) or ((c.duedate < (getdate()+(b.duedtds+duegrper))))) then 'Terms Exceeded/Credit Limit' end as 'Trigger',

a.custnmbr, a.custname, case when a.hold='0' then 'No' else 'Yes'end as 'On_Hold', a.pymtrmid, b. duedtds, a.duegrper, d.custblnc, a.crlmtamt, d.avdtplif, d.avgdtpyr, d.lastpydt, d.lsttrxdt, d.ttlslytd, d.ttlslltd, d.agperamt_1 as 'Current', d.agperamt_2 as '31_45', d.agperamt_3 as '46_60', d.agperamt_4 as '61-90', sum(d.agperamt_5 + d.agperamt_6 + d.agperamt_7) as '90+' from rm00101 a

left join sy03300 b

on a.pymtrmid=b.pymtrmid

join rm20101 c

on a.custnmbr=c.custnmbr

and c.duedate = ( Select max(i.duedate ) from rm20101 i where i.custnmbr=c.custnmbr and docnumbr like 'i%' ) --check and see if this is correct for docnumbr

join rm00103 d

on a.custnmbr= d.custnmbr

where ((c.duedate < (getdate()+(b.duedtds+duegrper))) or (d.custblnc>a.crlmtamt)) and a.hold='0' and a.pymtrmid<>'hard hold'

group by c.duedate, a.custnmbr, a.custname, a.hold, a.pymtrmid, b. duedtds, a.duegrper, d.custblnc, a.crlmtamt, d.avdtplif, d.avgdtpyr, d.lastpydt, d.lsttrxdt, d.ttlslytd, d.ttlslltd, d.agperamt_1, d.agperamt_2, d.agperamt_3, d.agperamt_4, d.agperamt_5, d.agperamt_6, d.agperamt_7

order by a.custnmbr

*This post is locked for comments

I have the same question (0)
  • Community Member Profile Picture
    Community Member on at

    What sort of issue are you having with the other script?

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at
    With the join of the rm20101 table it is only returning results where customers have unpaid invoices.  I could have a customer on credit hold that doesn't have any unpaid invoices and they would need to be taken off.  When I join the rm20101 table, it will not show any customers who do not have outstanding invoices.  Make sense?
  • Community Member Profile Picture
    Community Member on at

    Has 'Paid Transaction Removal' been run at some point? If so, I think you would need to union rm20101 with rm30101 in order for you to get all fully paid transactions.

    Also I see in your script that you are joining rm20101 with rm00101 and you have a comparison in the WHERE clause, a case statement and a group involving rm20101. What happens if you have a customer on hold but without any transaction in rm20101? I think the result will be a null value and so that customer will be skipped, and thus your result set will not have any customers that don't have any transactions at all.

    What you can do is join rm00101 with rm20101 first in a subquery and deal with the null values using isnull. Then join that subquery with the rest of the tables and do the comparison/grouping that you need to do outside of that subquery. That's one way to do it. The idea is to stop that null date value from seeping into the comparisons/grouping.

    Another way to do it is to take the whole query you have and union it with another query that returns customers that don't have any records in rm20101.

  • Ron Wilson Profile Picture
    Ron Wilson 6,010 on at

    I think Union fixes it!  Here is what I came up with (with some help from the experts at Experts-Exchange.com of course!!!):

    --This query selects all customers who have unpaid but not overdue invoices and unions that dataset with all customers who dont have any unpaid invioces

    --The hold will be removed from the result set if the following conditions are matched:

    /*Remove from credit hold if:

    Hold Status = 1

    and no invoice past duedate+grace period

    and not over credit limit

    and payment terms <> "hard hold"

    */

    select * from (

    --Customers To Remove From Hold With Unpaid But Not Overdue Invoices

    select min(c.duedate) as 'duedate',

    a.custnmbr,

    a.custname,

    a.hold,

    a.pymtrmid,

    a.duegrper,

    b.custblnc,

    a.crlmtamt,

    b.avdtplif,

    b.avgdtpyr,

    b.lastpydt,

    b.lsttrxdt,

    b.agperamt_1 as 'Current',

    b.agperamt_2 as '31-45',

    b.agperamt_3 as '46-60',

    b.agperamt_4 as '61-90',

    sum(b.agperamt_5 + b.agperamt_6 + b.agperamt_7) as '90+'

    from rm00101 a

    join rm00103 b on a.custnmbr=b.custnmbrleft join (select custnmbr, min(duedate) duedate from rm20101 group by custnmbr) c on a.custnmbr=c.custnmbr

     

    where (b.custblnc<a.crlmtamt) and

    (getdate()<=(c.duedate+a.duegrper)) and a.hold='1' and

    a.pymtrmid<>'hard hold'

     

    group by a.custnmbr,

    a.custname,

    a.hold,

    a.pymtrmid,

    a.duegrper,

    b.custblnc,

    a.crlmtamt,

    b.avdtplif,

    b.avgdtpyr,

    b.lastpydt,

    b.lsttrxdt,

    b.agperamt_1,

    b.agperamt_2,

    b.agperamt_3,

    b.agperamt_4

     

    UNION ALL

    --Customers To Remove From Hold With No Unpaid Invoices

    select null as duedate,

    a.custnmbr,

    a.custname,

    a.hold,

    a.pymtrmid,

    a.duegrper,

    b.custblnc,

    a.crlmtamt,

    b.avdtplif,

    b.avgdtpyr,

    b.lastpydt,

    b.lsttrxdt,

    b.agperamt_1 as 'Current',

    b.agperamt_2 as '31-45',

    b.agperamt_3 as '46-60',

    b.agperamt_4 as '61-90',

    sum(b.agperamt_5 + b.agperamt_6 + b.agperamt_7) as '90+'

    from rm00101 a

    inner join rm00103 b on a.custnmbr=b.custnmbr

    where (b.custblnc<a.crlmtamt) and

    not exists(select 1 from rm20101 where custnmbr = a.custnmbr) and

    a.hold='1' and a.pymtrmid<>'hard hold'

     

    group by a.custnmbr,

    a.custname,

    a.hold,

    a.pymtrmid,

    a.duegrper,

    b.custblnc,

    a.crlmtamt,

    b.avdtplif,

    b.avgdtpyr,

    b.lastpydt,

    b.lsttrxdt,

    b.agperamt_1,

    b.agperamt_2, b.agperamt_3,

    b.agperamt_4

    ) t1

    order by t1.custnmbr

     

  • Community Member Profile Picture
    Community Member on at

    Glad it worked for you.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans