We have a requirement that, if the product owner is disabled the approval for that product should go to product owner's manager. I have copied the pre-defined approval procedure(OA-Product Owner) and created a new one, in which I am customising the query for approver selection and adding SQL CASE statement which verifies if the product owner is active or not and returns UID_Person of the respective person. This query is executed successfully in object browser when hardcoded the value pwo.UID_personwantsorg, however when I add this query in new custom approval procedure I am getting an error as below:
Object (OC - Product owner) could not be saved!
Error flushing changes to database.
Database error 50000: re-throw in Procedure QBM_PSQLCheckExecutable, Line 26
Database error 50000: detected in (SRV=SPRW026, DB=OneIM) Procedure QBM_PSQLCheckExecutable, Line 23
Database error 50000: SyntaxError in Element <Key><T>PWODecisionRuleRulerDetect</T><P>CCC-EB0B893892917D478602193B3DD7DD14</P></Key>
8155 0 detected in (SRV=SPRW026, DB=OneIM) no procedure, no Code, Line 16
Database error 8155: No column name was specified for column 1 of 'x'.
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.
Below is the query which I am adding to custom product owner procedure.
WHEN p.IsTemporaryDeactivated=0 THEN pia.UID_Person
ELSE p.UID_PersonHead END, dbo.QER_FGIPWORulerOrigin(pia.XObjectkey) as UID_PWORulerOrigin
from personwantsorg pwo
join basetree pro on pwo.UID_Org = pro.UID_Org
join Basetree pr on pr.UID_Org = pwo.UID_ITShopOrgFinal -- the product node
join accproduct a on pr.UID_AccProduct = a.UID_AccProduct
join personinbasetree pia on pia.UID_Org = a.UID_OrgRuler
join Person p on p.UID_Person = pia.UID_Person
and pia.XOrigin > 0
where pwo.UID_personwantsorg = @UID_personwantsorg
Appreciate your help on this.