Skip to Content
avatar image
Former Member

Approval Query

Hi Experts,

I have the query below for approval:

SELECT DISTINCT 'TRUE'

FROM ODLN

INNER JOIN OCRD ON $[ODLN.CardCode]= OCRD.CardCode

WHERE ($[ODLN.DocTotal]+OCRD.Balance+(SELECT SUM(ORCT.DocTotal) FROM ORCT WHERE $[ODLN.CardCode]=ORCT.CardCode AND ORCT.DocDueDate>getDate() ))>OCRD.CreditLine OR (SELECT DISTINCT 'True' FROM ODLN

INNER JOIN OINV ON OINV.CardCode=$[ODLN.CardCode]

WHERE OINV.DocStatus='O' AND OINV.DocDueDate<getDate())='TRUE' OR ((SELECT DISTINCT 'True' FROM ODLN INNER JOIN OINV ON $[ODLN.CardCode]=OINV.CardCode where OINV.DocDueDate<getDate() and OINV.DocStatus='O') ='True' and (SELECT DISTINCT 'True' FROM ODLN INNER JOIN ORCT ON $[ODLN.CardCode]=ORCT.CardCode INNER JOIN RCT2 ON RCT2.DocNum=ORCT.DocEntry INNER JOIN OINV ON OINV.DocEntry=RCT2.baseAbs WHERE ORCT.DocDueDate>OINV.DocDueDate AND ORCT.Canceled='N')='True')

Its an approval at delivery. Its checking the following:

1. if Account balance + Delivery total + Postdated payments > Credit limit

2. if there are any open AR invoices that are past due date

3. if there are any open AR invoices that are past due date and if any payment duedate exceeds the invoice duedate

The approval does not trigger.

Kindly help me with this

Thanks & Regards

Aziz

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jun 02, 2015 at 12:36 PM

    Hi,

    Please check this thread:-

    Thanks.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jun 08, 2015 at 10:25 AM

    Hi,

    I managed to find out the issue,

    (SELECT SUM(ORCT.DocTotal) FROM ORCT WHERE $[ODLN.CardCode]=ORCT.CardCode AND ORCT.DocDueDate>getDate() )

    The command SUM() returns a null value, so used the Coalesce() function to to return 0 and now it works.

    Thanks

    Add comment
    10|10000 characters needed characters exceeded