Approval Template based on overdue invoices for a Region

[Former Member]

Hi,

We have set an approval template for all overdue invoices which should go to the repsective sales managers for approval.

We have setup two approval templates one for Central region and one for NES region.

The template involves running of the following query and going to the respective manager for approval, however this does not work.

When we create a seperate location wise approval template it works:

Please check and let me know the error if any:

SELECT DISTINCT 'TRUE' FROM OINV T0 and OCRD T1 WHERE T1.CardCode =

$[$4.0.0] and T1.U_AT_PC = 'NES' and T0.DocDueDate <= Getdate() AND T0.DocStatus !='C' AND $[ORDR.CARDCODE] = T0.CARDCODE

Regards,

[Former Member]

Hi Vishal,

Please clarify:

1. When we create a separate location wise approval template it works. If it works that way, is there any problems to use it?

2. Why do you need the last condition *AND [ORDR\].CARDCODE = T0.CARDCODE?

Thanks,

Gordon

[Former Member]

Hi Gordon,

Thanks for your reply!

Actually, we had created the following query as per our requirement for all overdue invoices:

SELECT DISTINCT 'TRUE' FROM OINV T0 WHERE T0.DocDueDate <= Getdate() AND T0.DocStatus !='C' AND $[ORDR.CARDCODE] = T0.CARDCODE

( here the customer name in the order should be equal to the Invoice Table Customer code )

and the location wise query:

SELECT 'TRUE' FROM OCRD T0 where T0.CardCode =

$[$4.0.0] and T0.U_AT_PC = 'NES'

When i tried using this query with the Overdue invoice query the same order had to be approved by the manager twice, which is not acceptable.

The problem in using the location wise approval template with the query, was that the "Confirm Credit Line Deviation" prompts up every time we try to enter a Sales Order.

Whereas we wanted an alert to be prompted to the sales manager once any sales order is entered however if the SO is above the credit limit the sales manager should know about it, which can be achieved if we use the overdue invoice query correctly.

I tried clubbing both the above queries together,but it doesn't work.

Is there a way where you could use one query with an IF clause saying that if the UDF field is U_AT_PC= "NES" then go to NES sales manager if U_AT_PC="Central" then go to Central sales manager.

Please revert

Regards

[Former Member]

In your case, I suggest you create two approval stages and two approval temples with two different queries for each region. In this way, problem will become simpler.

[Former Member]

Hi gordon,

I tried doing it, but i find the location wise query doesn't work then...

All SO prompts for both sales manager's approval.

Anything I need to change in the query?

Regards,

[Former Member]

Try sonething like this one:

SELECT DISTINCT 'TRUE'  FROM $[ORDR] T1 
WHERE T1.CardCode = $[$4.0.0] and T1.U_AT_PC = 'NES' 
AND T1.CARDCODE IN SELECT T0.CARDCODE FROM OINV T0 
WHERE T0.CARDCODE =T1.CARDCODE 
AND T0.DocDueDate <= Getdate() AND T0.DocStatus !='C'

It is difficult for me to verify it. Please understand the logic in the query and come out your own

[Former Member]

Hi gordon,

Thanks for your reply!

If I run this query even though the customer is based in NES, both the queries are prompted for approval.

As per our requirement there should be only one approval for any So with or without credit approval.

Please comment.

Regards,