SBO - Approval Procedures at row level

[Former Member]

Does anyone know how to create an approval procedure that is triggered by a row field. For example if I want an approval procedure that verifies discount percentage in row level.

There must be a workaround, cause I know this is not a functionality in SBO.

Thanks,

Hernán Baudrit

[Former Member]

The way you could solve this is to create a UDF at Header level and link a formatted search which loops through the number of rows in the transaction and returns TRUE if for example the Discount % is greater than allowed.

The coding should be something as follows:

-

---

Declare @Counter as integer

Set @Couter = 0

Declare @TempTable TABLE (STAT varchar(50)) -- Temporary table holding results of loop

Declare @LoopFor as integer

Set @LoopFor = (select (*) from (select T1.DocEntry

from INV1 T0 join OINV T1 on T0.DocEntry = T1.DocEntry

where T0.DocNum = $[OINV.DocNum])

)

While @Counter < @LoopFor

BEGIN

Declare @Disc as integer

Set @Disc = ( select T1.DiscPrcnt

from INV1 T0 join OINV T1 on T0.DocEntry = T1.DocEntry

where T0.DocNum = $[OINV.DocNum]) and T1.LineNum = @Counter

)

IF @Disc > 20

BEGIN

INSERT @TempTable VALUES ('TRUE')

END ELSE INSERT @TempTable VALUES ('FALSE')

@Counter = @Counter + 1

END

SELECT Distinct 'TRUE' from @TempTable where STAT = 'TRUE'

-

---

Please note that the coding is not 100% accurate but should give you an idea of how to work around the problem.

Next you should write a simple validation query on the value of the UDF to check whether it is set to 'True'.

I hope this helps.

[former_member204969]

I think, the method could not work, because the row data is not stored into the INV1 table before adding the invoice.

May be you cold use a similar procedure in the transaction notification SP?

[Former Member]

In a project I have worked on, we created a header field containing a boolean; whenever one of the rows triggered the approval condition, the add-on set that header field to true right before saving, otherwise it reset it to false (or something to that extent).

[Former Member]

> In a project I have worked on, we created a header

> field containing a boolean; whenever one of the rows

> triggered the approval condition, the add-on set that

> header field to true right before saving, otherwise

> it reset it to false (or something to that extent).

Hi there Massimiliano

Thank you for your prompt response, the issue that we are experiencing is that the query only reads the first line of the Order for approval and cannot follow through the following lines. Hence if the order has a first line that requires approval it generates the approval procedure but if the approval is in the second or subsequent line which requires the approval because the first line was false it skips the approval procedure.

Regards

Ernest A Dumont

[Former Member]

Hi Ernest; Massimiliano;

It seems it has been fixed on version 2005A SP01. You can create an approval procedure based on line data, and no matter if it is not the first line. Try:

SELECT 'TRUE' FROM OITW T0 WHERE T0.ItemCode = $[38.1.0] AND T0.WhsCode = $[38.24.0] AND T0.OnHand < $[38.11.NUMBER]

[ronnie_scott]

I don't think this is working. It's still only showing the first row.

Does anyone know if we're able to define approval procedures based on row level queries now? We're on 2005 SP01 PL22.

[Former Member]

Does anyone know how to create an approval procedure

that is triggered by a row field. For example if I

want an approval procedure that verifies discount

percentage in row level.

There must be a workaround, cause I know this is not

a functionality in SBO.

Thanks,

Hernán Baudrit

Hi there Hernan

Did you manage to solve this issue as we are experiencing a similar request from a client and are experiencing difficulties.

Regards

Ernest A Dumont

NGO Consulting

ernest@ngo.co.za