Skip to Content
Author's profile photo DIEGO LOTHER

Approval template at the line level on marketing documents

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


View Diego Lother's profile on LinkedIn

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Chia-Wei Hsu
      Chia-Wei Hsu

      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

      Author's profile photo DIEGO LOTHER
      DIEGO LOTHER
      Blog Post Author

      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

      Author's profile photo Former Member
      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

      Author's profile photo DIEGO LOTHER
      DIEGO LOTHER
      Blog Post Author

      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

      Author's profile photo Jesus Gutierrez
      Jesus Gutierrez

      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.

       

       

      Author's profile photo Adewale Kayode Adegbola
      Adewale Kayode 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.

      Author's profile photo Marian Matejovic
      Marian Matejovic

      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

      Author's profile photo Marian Matejovic
      Marian Matejovic

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

      Thanks, Marian