Approval procedure query with UDF at header level

[fcpeomaior]

Hi,

I have added an UDF to marketing documents header to trigger an approval procedure validation computed via AddOn.

Since the approval procedure must be activated in 4 AR documents, I would like to convert this query that is document specific:

• SELECT 'TRUE' FROM DUMMY WHERE $[ORDR.U_LineValidation] <> ''

To a generic one, without the reference to the document table, thus using instead the reference to the system form field ID:

• SELECT 'TRUE' FROM DUMMY WHERE $[$U_LineValidation.0.0] <> ''

However, the second query ALWAYS returns error in SAP...

Does any body know if UDF field is supported in SAP queries? Is there any alternative to this query that avoids the usage of table reference?

This query would allow me to use it as a generic query validation in my approval process for all 4 documents: otherwise I have to create 4 approval processes (1 by each document type) to use a query table specific.

Thanks

mdias

[mgregur]

Hi,

You can use table and field names, or formID/field to select data from active form. A query which would select all BP details based on e.g. CardCode form any marketing document (GRPO, SO, PO, Invoices etc) would be:

SELECT * FROM OCRD WHERE "CardCode" = $[$4.0]

As you can see, only two "numbers" are mentioned. This is because it is a header field. So, CardCode fieldID is 4, and 0 gives back the format in which to read the data (0 = "read as-is"). You use three variables when reading from a matrix on the document, eg. details of an Item from an order line:

SELECT * FROM OITM WHERE "ItemCode" = $[$38.1.0]

So, the matrix has a fieldID 38, and number 1 gives it the instruction to read from the first column (each field in the matrix has a fixed columnID, it's not visual order).

Hope this helps clear it out.

BR,

Matija

[fcpeomaior]

Hi Matija,

I understand how B1 queries work: my question is specific for SAP approval procedures with UDF using field ids.

Do you know if its possible to write a query with field ID for a UDF, instead of table/field?

Example: this query works fine in a SAP approval procedure for Sales Order AR document:

• SELECT 'TRUE' FROM DUMMY WHERE $[ORDR.U_LineValidation.0.0] <> ''~

However, I would like to have an equivalent query but using the field ID, to be used in ALL AR documents:

• SELECT 'TRUE' FROM DUMMY WHERE $[$U_LineValidation.0.0] <> '

The previous query returns error in SAP so I believe its not supported: do you have any workaround?

Otherwise I have to create a specific approval process for each AR document that I want to validate.

Regards

[mgregur]

Hi,

Yes, it is possible, I don't know if it is the same in SQL as I'm using HANA, but UDF field name must not be in quotes to work with FMS.

BR,

Matija