Approval template at the line level on marketing documents

[former_member185682]

Hi all,

Today I will write a little about approval template, I went through a situation recently with this resource and I would like to share my experience.

Recently on the SAP B1 SDK space, appeared a thread asking help to do a query for approval template on the line level of the document. After read a little about this subject, I saw in many other threads in the SAP B1 spaces, comments talking that is not possible use information of the line level to build approval templates.

At the company that I work I know that are done approval templates on line level, then, I went check this resource and I got those considerations:

Is it possible to use approval templates at the line level?

Yes, it is.

Is it possible to do a query for approval template using data from header and line together?

Yes. it is.

A real sample: Approval template for sales order.

e.g. Line level

At this query, I don't want that SAP allow sales order with any line with discount greater than 20 %

SELECT CASE WHEN $[$38.15.NUMBER] > 20 THEN 'TRUE' ELSE 'FALSE' END

e.g. Header level

At this query, I don't want that SAP allow sales order with header discount greater than 20%.

SELECT CASE WHEN $[$24.0.NUMBER] > 20 THEN 'TRUE' ELSE 'FALSE' END

e.g. Header and line level

At this query, I don't want that SAP allow sales order with any line discount greater than 20% for the customer C00001

SELECT CASE WHEN $[$38.15.NUMBER] > 20 AND $[$4.0.0] = 'C00001'  THEN 'TRUE' ELSE 'FALSE' END

Is it possible group values from the lines in query, from approval templates?

No. The query will be executed line by line, and if any of them returns 'TRUE', the approval procedure will be started. Because of this execution, line by line is not possible use resources like SUM(valor), for example.

Obs.: After some tests, I saw that the sintax $[TABLE.FIELD] doesn't work correctly on approval template, so, in the scenarios that I mentioned, our queries should use always the sintax $[$ITEM.COL].

The information shared here, were tested on SAP B1 9.0 PL 11  and 9.1 PL 12.

That's all folks, hope it helps.

Leave your feedback about this subject and share your experience about it.

---

Best regards,

Diego Lother

[former_member227145]

Hi Diego,

Thanks for this great sharing.

I tested it and wonder why your query works

SELECT CASE WHEN $[$38.15.NUMBER] > 20 THEN 'TRUE' ELSE 'FALSE' END

but below one fail?

SELECT CASE WHEN $[$38.15.0] > 20 THEN 'TRUE' ELSE 'FALSE' END

 

Best regards,

Alex Hsu

[Former Member]

Diego,

I will double check your solution, but I am almost 100% sure that I already tried that before and the QUERY was executed only for the first row of the document.

Best regards,

Damian

[former_member185682]

Hi Chia-Wei Hsu,

 

This happens because in the first case I cast my variable as number, so, the comparision is number (variable) with number(20).

On the second query you cast your variable to a string. The sintax "0" represent a cast as string. So, the comparision is string(variable) with number (20). Then fails.

Hope it helps.

Kind Regards,

Diego Lother

[former_member185682]

Hi Damian,

Maybe in older versions of SAP Business One. I tested on SAP B1 9.0 PL 11  and 9.1 PL 12 and works fine. Approval procedure execute line by line. If something not work for you let me know.

Kind Regards,

Diego Lother

[wale_adegbola]

I need a Query that will allow Invoices with PO not to go for Approvals and at the same time Invoices that has no PO to go for Approvals on the same Database.

[Former Member]

Hi Diego,

 

I have Sap B1 9.2 PL 09, and I tried to run a query at line level, to ask approval on purchase order based on the values of the field QryGroup21 and if the Total(LC).

But the query only fire the approval procedure on the first line. What do I have to do to make the query run on every row?

This is my query

SELECT CASE WHEN T0."QryGroup21" = 'N' AND $[$38.21.NUMBER] > 10000 THEN 'TRUE' ELSE 'FALSE' END FROM OITM T0 WHERE T0."ItemCode" = $[$38.1.0]

TIA

Jesus.

 

 

[former_member640383]

Hi Diego,

kindly is it possible in SAP B1 v9.3 to setting up of LINE authorization base on Department? For instance all Purchase Orders in the company are prepared/and submitted for approval by Purchasing department only. Let's assume PO with 2 lines where the 1st line includes 10 toners for Finance dpt, which has to be approved by Head of Finance and the 2nd one 30 toners for Operation, which has to be approved by Head of Operation.

In case that Order is above 1500 USD additional CEO approval is requested (this is simple via new approval template)

 

Thanks, Marian

[former_member640383]

Following your examples I finally found out how to make my line approval setup ...:-)

Thanks, Marian

[wale_adegbola]

Good day,

I need a query for an approval template for an invoice with the condition that the owner on the Invoice will be the one to approve the invoice even though there are more than one authorizer on the approval stages.

[PauloF]

Hi,

I've been around this subject, and today I found that the approval based on lines works on SAP B1 10 SQL, but not on HANA version.

In SQL 10FP2202 - Working fine

SELECT CASE WHEN $[$38.1.0] = 'XXX' THEN 'TRUE' ELSE 'FALSE' END

In HANA 10FP2202 - not working

SELECT CASE WHEN $[$38.1.0] = 'XXX'
THEN 'TRUE'
ELSE 'FALSE' END
FROM DUMMY;

Anybody whit the same ?

Regards