Copy all variables from one task to another

[Michael A. Lowry]

Last week I developed a couple of small solutions:

• an SQL query to list the variables of a task
• a bit of AE scripting to generate script variables based on these SQL query results

By combining these two things, it is possible to copy all variables from one task in an AE system to another.

 

 

List all variables of a task (UC0.GET_VARIABLE_VALUES.VARA_SEC_SQLI)

This (Oracle) SQL query lists the names and values of the script/object/prompt set variables of a task.

with

bindparms as
(select ? as BP_Run_ID from Dual),

task_values_archived as
(
select AH_Client as Client, AH_OType as Obj_Type, AH_IDnr as Run_ID,
AH_Name as Obj_Name, AH_Alias as Task_Alias,
AV_VName as Var_Name, AV_Value as Var_Value
from AH join AV on AH_IDnr = AV_AH_Idnr
),

task_values_active as
(
select EH_Client as Client, EH_OType as Obj_Type, EH_AH_IDnr as Run_ID,
EH_Name as Obj_Name, EH_Alias as Task_Alias,
EV_VName as Var_Name, EV_Value as Var_Value
from EH join EV on EH_AH_IDnr = EV_AH_Idnr
),

workflow_task_values_archived as
(select AH_Client as Client, AH_OType as Obj_Type, AH_IDnr as Run_ID,
AH_Name as Obj_Name, AH_Alias as Task_Alias,
AJPPV_VName as Var_Name, AJPPV_Value as Var_Value
from AH join AJPPV on AH_Idnr = AJPPV_AH_Idnr
),

workflow_task_values_active as
(select EH_Client as Client, EH_OType as Obj_Type, EH_AH_IDnr as Run_ID,
EH_Name as Obj_Name, EH_Alias as Task_Alias,
EJPPV_VName as Var_Name, EJPPV_Value as Var_Value
from EH join EJPPV on EH_AH_Idnr = EJPPV_AH_Idnr
),

task_values as
(select * from workflow_task_values_archived where Run_ID = (select BP_Run_ID from bindparms)
union all
select * from workflow_task_values_active where Run_ID = (select BP_Run_ID from bindparms)
union all
select * from task_values_archived where Run_ID = (select BP_Run_ID from bindparms)
union all
select * from task_values_active where Run_ID = (select BP_Run_ID from bindparms)
)

select SUBSTR(Var_Name,2), Var_Value from task_values

 

This query cuts off the first character of each variable name, because GENERATE_SCRIPT_VARS expects variable names without the leading ampersand (&). Just save this query to an SEC_SQLI VARA object called UC0.GET_VARIABLE_VALUES.VARA_SEC_SQLI, and define one bind parameter:

 

 

#	Parameter
1	&Task_RunID#

 

Generate script variables from SQL query results (UC0.COPY_VARS_FROM_OTHER_TASK.JOBI)

This bit of AE scripting evaluates the SQL query stored in the SEC_SQLI VARA object, and assembles the results into a format that can be handled by the GENERATE_SCRIPT_VARS script function.

:SET &Counter# = 0
:SET &GetVarValuesHnd# = PREP_PROCESS_VAR(UC0.GET_VARIABLE_VALUES.VARA_SEC_SQLI)
:PROCESS &GetVarValuesHnd#
: SET &Counter# = &Counter# + 1
: IF &Counter# = 1
: SET &TempDS# = CREATE_PROCESS(NEW)
: ENDIF
: SET &Var_Name# = GET_PROCESS_LINE(&GetVarValuesHnd#,2)
: SET &Var_Value# = GET_PROCESS_LINE(&GetVarValuesHnd#,3)
: SET &PPL_RC# = PUT_PROCESS_LINE(&TempDS#, "&Var_Name#|&Var_Value#", "|")
:ENDPROCESS
:IF &Counter# > 0
: SET &SP_RC# = SAVE_PROCESS(&TempDS#)
: SET &GSV_RC# = GENERATE_SCRIPT_VARS(&TempDS#,,UPDATE)
: SET &GSV_RC# = FORMAT(&GSV_RC#)
: PRINT "Return code from GENERATE_SCRIPT_VARS: &GSV_RC#"
:ENDIF
:CLOSE_PROCESS &GetVarValuesHnd#




To use the JOBI, just set the value of &Task_RunID# to the run ID of the task whose variables you want to copy, and then include the JOBI using the :INCLUDE statement.

:SET &Task_RunID# = 1234567
:INC UC0.COPY_VARS_FROM_OTHER_TASK.JOBI

 

In the above example, 1234567 is the run ID of the task whose variables are copied to the current task. Because this approach is based on SQL, it could conceivably be extended to pass information between tasks in completely different AE systems. (This would entail replacing the SEC_SQLI VARA object with an SQL job.)

 

Note: because this solution relies on GENERATE_SCRIPT_VARS, it requires AE v12.1 or later.

 

Enjoy!

[Michael A. Lowry]

@Jason McClellan Broken links.​