2 Replies Latest reply on Nov 5, 2019 10:04 AM by AlexZh

    FDMEE - Restrict prompt (query definition) based on POV Location in custom script


      Hello everyone,


      I'm trying to update the Target Budget option for Write-back data load rules (integration with EBS) using Jython Custom script.

      The reason is that end-users can't modify options for data load rules, but they should be able to update the EBS Budget target option in WB DLRs.


      Created the custom script, it works, this option is updated:


      #Get Parameters from update the table


      sLOCNAME = fdmAPI.getCustomScriptParameterValue("LOCNAME")

      sWBDLR = fdmAPI.getCustomScriptParameterValue("WBDLR")

      sWBBUD = fdmAPI.getCustomScriptParameterValue("WBBUD")


      #To update Budget option for WB rules


      sUpdateSql = "update aif_bal_rule_params set parameter_value = ?  where rule_id = ? and parameter_name = 'BUDGET'"

      params = [sWBBUD,sWBDLR]

      fdmAPI.executeDML(sUpdateSql, params, False)



      As well created 2 query definitions:

      1) The list of WB DLRs:

      select aif_balance_rules.rule_id as Value, aif_balance_rules.rule_name as Label, tPOVPartition.partitionkey as LocID

      from aif_balance_rules  inner join tPOVPartition ON aif_balance_rules.partitionkey = tPOVPartition.partitionkey

      where (aif_balance_rules .rule_name like '%WBR%')

      order by aif_balance_rules .rule_name


      2) The list of Budget options:



      As well I created script registration with these 2 queries and one parameter for POV Location.


      The question is how can I restrict the full list of Write-back rules based on POV Location?

      F.e. I choose Loc1 and when executing the custom script in query parameter (prompt) I need to see only appropriate rules of this location.


      I tried to use condition in query definition f.e. and ((tPOVPartition.partitionkey)=~POVLOC~), but ~POVLOC~ doesn't work properly when I'm choosing this prompt got the error: Missing IN or OUT parameter at index:: 1


      Probably does someone have any idea about that?



        • 1. Re: FDMEE - Restrict prompt (query definition) based on POV Location in custom script

          I think you are getting confused between Script Registration and Query Definition. You can use parameter placeholder strings (eg ~POVLOC~) in Query Definition. The parameters you create on Script Registration supply the replacement strings for the parameter placeholders. As far as I know you cannot use the parameter placeholders in the queries for Script Registration. I haven't found a way of intercepting the creation of the script parameters to do what you want.


          The only thing you could do is a check on the selected WBDLR once the parameters have been selected. If the user picked a WBDLR that is not valid for their location, raise an error and stop the script.

          • 2. Re: FDMEE - Restrict prompt (query definition) based on POV Location in custom script

            Thanks for your response. You are right.


            I have found the other way - to use the 'POV Rule' Parameter in Script Registration - it resolves my issue.


            The end-user can able to see only DLRs which related to users' locations.


            Unfortunately, we can't search in Query Definition and the user can able to see all Budgets (Target options for WB DLRs) and need to choose the correct option to their rule.