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' ENDe.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' ENDe.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' ENDIs 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
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
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
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
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
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.
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.
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
Following your examples I finally found out how to make my line approval setup ...:-)
Thanks, Marian
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.
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