Provide the result of an SQL as process to the user

Overview

• This guide shows you how to provide the resultset of an SQL to the user as Excel export by running a process.
• It also shows you how to add Translations Tabs in a running process.

Origin

This guide was developed in this issue: “Generic Process for SQL -> Excel Export” (metasfresh#4633).

In General

• The class name will be automatically set and made read-only: de.metas.impexp.excel.process.ExportToExcelProcess

  Note: In version 5.91 there is a bug which prevents the class from being set automatically.
  Workaround: update ad_process set classname='de.metas.impexp.excel.process.ExportToExcelProcess' where ad_process_id= <your ad_process id>

• Your SELECT statement, e.g.,

  select
  @C_Order_ID/-1@ as C_Order_ID
  , @AD_User_ID/-1@ as AD_User_ID
  , @C_BP_Group_ID/-1@ as C_BP_Group_ID
  , bp.*
  from C_BPartner bp
  

The context variables used in SQL will be resolved in the following order:

1. Process parameter, e.g., where c_order_id = @C_Order_ID/-1@.
2. Current selected record’s field, e.g., where <column> in ('@$WEBUI_ViewSelectedIds/0@').
3. Global context, e.g., @#AD_User_ID@.

Additional Information

1. AD_Column.DefaultValue and AD_Field.DefaultValue

• Note that there can be several windows with tabs that have fields pointing to the same column.
• When a default value is defined on AD_Column level, that value will have the default value no matter where it is used.
• When a default value is defined on AD_Field level, it means that it will be applied only in that field of that window.
• The AD_Field default value overrides the AD_Column one.

2. The DefaultValue SQL syntax

A default value can be:

• A constant like A or Y.
• A value from the global context, e.g., @#AD_Client_ID@, which is the AD_Client_ID you are logged in with. A global context value can always be identified by the leading #.

  Note: Even if there is another AD_Client_ID selected in the local context, when # is present, the global value will always be retrieved.

• A value from the local context, e.g., @AD_Client_ID@, which is the “closest” AD_Client_ID from the context.

  Example: If you are logged in with client X and open a window “A” which has client Y set, then the value “Y” will be taken.

• A more complex SQL, like @SQL=SELECT COALESCE(MAX(SeqNo),0) + 100 AS DefaultValue FROM C_PricingRule.

  Note: The statement starts with @SQL= but the @ does not have to be paired with another one at the end.

3. The WEBUI_ViewSelectedIds

• WEBUI_ViewSelectedIds is a value for the currently selected record’s field, e.g., where <column> in ('@$WEBUI_ViewSelectedIds/0@').

• Since the input of WEBUI_ViewSelectedIds is going to be a continuous varchar, you would need to split the input.

  Example
  ANY (regexp_split_to_array(p_i_candidate_ID, ',')::numeric[])

  In this example, p_i_candidate_ID is the inputted parameter.

Samples

How to create and bind processes

How to create and bind processes for selected records