Approval Query needed for AR Reserve Invoice

[Former Member]

Need to create an approval for delivery doc- If AR reserve invoice not paid need to triger an approval. how can I cutomise the query to look at base document.

SELECT DISTINCT 'TRUE' FROM OINV T0 WHERE T0.[isIns] = 'Y' AND T0.[DocStatus] <> 'C'

Thanks and Regards

[JesperB1 · Accepted solution]

Hi Anoop,

This one should do it I believe. I have attachde the query as not everything gets copied in this forum. The query below will not work.

 SELECT distinct 'true' FROM DLN1 T0 WHERE T0.[BaseType] = 13 and  $[$38.45.1] in (SELECT docentry  FROM OINV T0 WHERE isins = 'Y' and paidtodate <> doctotal) 

There are a few limitations with approvals in B1 that are based on values on the linelevel like this one.

The query can not see past the first line of the document. So it will only check the basedoc of the first item in the Delivery (it is possible to add an item on the first line after copying the Reserve Invoice details into the delivery).

Apart from that, the query only checks if the payment have been fully done "(paidtodate <> doctotal)", if it has been partially paid it will still trigger the approval. If partial payment is acceptable you can change that to "paidtodate <> 0".

Hope it helps,

Jesper

[Former Member]

i need help with the opposite of this.

i need to lookup UnDelivered AR Reserve Invoices, preferably where the selection criteria is = BpCode and ItemCode.

[Former Member]

Please post your question by a new thread. Only thread owner should ask question by forum rule.

Thanks,

Gordon

[Former Member]

Hi Jesper,

Have received your mail, that query works thanks very much for your help.

Regards,

Edited by: Anoop Jacob on May 19, 2009 12:57 PM

[Former Member]

Hi Jasper,

Its anoop.jacob txtgroup.com,

Regards,

[JesperB1]

Hi Anoop,

Sent the query by email.

Hope it arrived.

Jesper

[Former Member]

I have the same problem to download the file if it is attached to the title of the message. It might be an authorization issue.

[Former Member]

Hi Gordon,

Yes, I have noticed that unfortunetly that file is empty. Looking at its showing .57mb but once you dowload content missing.

Regards,

[JesperB1]

Hi Anoop,

If you give me your mail address I can mail it to you.

It is not on your business card.

Jesper

[Former Member]

You may not read the posting carefully. The query below will not work. That is refer to the query you are trying to use now! You should download the query on top of the message and try again.

Thanks,

Gordon

[Former Member]

Hi Jesper,

I have tried the query in diffrent data base -- its doesnt trigger approval the query you have mentioned as below:

SELECT distinct 'true' FROM DLN1 T0 WHERE T0.[BaseType] = 13 and $[$38.45.1] in (SELECT docentry FROM OINV T0 WHERE isins = 'Y' and paidtodate doctotal)

not realy geting where it went wrong..?

All approvals in the sytems seems working alright, this doesnt trigger anything.

Regards,

[Former Member]

Hi Jesper,

Have tried with this query, unfortunetly ist trigerr any approval, more precisely when I use the u2018Docstatusu2019 or u2018PaidtoDateu2019 fields as trigger when the u2018isInsu2019 field isu2019yesu2019 (document is a reserve invoice).

The docstatus changes to u2018Cu2019 once the invoice is paid.

Therefore, need to set the approval to trigger if the docstatus of the base document is not u2018cu2019.

Regards,

[JesperB1]

Hi Anoop,

I tried it and it worked good.

Meaning, it will only trigger when the Invoice has not been paid and not for every single Delivery that is based on a Reserved Invoice once there is one paid Reserved Invoice in the system.

Are you talking about a formatted search or an Approval Procedure as defined under:

Administration -> Approval Procedures?

Did you get it working?

Let us know.

Jesper