Custom Approval Procedure for Product Owner gives error when SQL CASE statement is used

Hi There,

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:

ERROR:

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.

select distinct

CASE

  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.

Parents
  • Hey Kiran,

    without further checking of the SQL it works for me if you write:

      "ELSE p.UID_PersonHead END as UID_PersonHead,  dbo.QER_FGIPWORulerOrigin(pia.XObjectkey) as UID_PWORulerOrigin"

    Best Regards,

    Thorsten

  • Hi Thorsten,

    After saving the custom approval procedure, I was assigning the procedure to IT SHOP requests approval workflow which displayed an error on compliance check as mentioned below.

    This approval policy is not valid: Error - The step 'CCC-BDECE4BB8427BD469836D65E5EF9602A - CCC-793C80EE0804B144BE19AEFABD131960 - Application Owner or Product Owner' must be attached when compliance check exits with error.

    I am not able to find the step mentioned in the error, can you please advice.

  • Hey Kiran,

    I can't really help with that one because i can't recreate it. I am guessing, that the UID you posted is the UID of your customized PWODecisionRule so maybe there is an issue with the relation inside the workflow.

    Sorry that i can't be of more help, but there are people more capable than me here Slight smile  But in any case, could you provide a screenshot of your workflow?

    Best regards,

    Thorsten

Reply
  • Hey Kiran,

    I can't really help with that one because i can't recreate it. I am guessing, that the UID you posted is the UID of your customized PWODecisionRule so maybe there is an issue with the relation inside the workflow.

    Sorry that i can't be of more help, but there are people more capable than me here Slight smile  But in any case, could you provide a screenshot of your workflow?

    Best regards,

    Thorsten

Children