7. Configure an Application and Trigger Actions

The goal is to send a CSV file by mail with all new employees of both divisions EAST and WEST as soon as a business day is finished. The file contains only BDate, ID, names, and division - no salary and birth date.

Currently only one table exists in PSA_EMP; however, the application ‘HR_READY’ is configured so that new tables in the same schema are included implicitly.

Note

Application names may only contain upper-case letters, digits, and underscores, and must start with a letter.

7.1. Configure the Application

 1execute script API_REDADM.ADD_APPLICATION(
 2     'HR_READY'                    -- p_app_name
 3    ,'HR data completed'           -- p_description
 4    ,false                         -- p_use_deps
 5);
 6
 7execute script API_REDADM.ADD_APP_DISCOVER_RULE(
 8     'HR_READY'                    -- p_app_name
 9     ,'PSA_EMP'                    -- p_schema_incl_pattern
10     ,null                         -- p_schema_excl_pattern
11     ,'.*'                         -- p_table_incl_pattern
12     ,NULL                         -- p_table_excl_pattern
13     ,'DIV_EAST'                   -- p_bdomain
14     ,'HR data from EAST'          -- p_rule_comment
15);
16
17execute script API_REDADM.ADD_APP_DISCOVER_RULE(
18     'HR_READY'                    -- p_app_name
19     ,'PSA_EMP'                    -- p_schema_incl_pattern
20     ,null                         -- p_schema_excl_pattern
21     ,'.*'                         -- p_table_incl_pattern
22     ,NULL                         -- p_table_excl_pattern
23     ,'DIV_WEST'                   -- p_bdomain
24     ,'HR data from WEST'          -- p_rule_comment
25);

Refresh the application components, so that they are available immediately:

1execute script API_REDADM.REFRESH_APP_COMPONENTS;

Check the result in REDADM.RED_APPLICATIONS and REDADM.RED_APP_COMPONENTS.

The status of the application and its components is shown in REDADM.RED_APP_STATUS and REDADM.RED_APP_OBJECT_STATUS. After the loads you will find something like this:

Table 7.1 RED_APP_STATUS

APP_NAME

BDATE

LOADED_STATUS

PRELIM_STATUS

FINAL_STATUS

HR_READY

2025-09-01

FINISHED

FINISHED

FINISHED

Table 7.2 RED_APP_OBJECT_STATUS

APP_NAME

OBJECT_SCHEMA

OBJECT_NAME

BDATE

OBJECT_STATUS

HR_READY

PSA_EMP

PERSON

2025-09-01

FINISHED

7.2. Configure the Mail Export

7.2.1. Create an Export View

First create an application mart (APB_HR) for the custom view EMP_NEW:

1execute script API_REDADM.CREATE_APB(
2    'APB_HR'    -- p_apb_name
3);

Note

This API call creates the schema APB_HR with all necessary privileges. No need for any further DBA-activity.

1create view APB_HR.EMP_NEW as
2    with base as (
3	    select bdate_from as bdate, empid, firstname, lastname, bdomain as division, count(1) over(partition by bsk) as cnt
4	      from psv_emp_ta.person
5	)
6	select bdate, empid, firstname, lastname, division from base where cnt = 1;

7.2.2. Define the Export

Create the export format for the CSV file and then the export itself.

The CSV file should

  • use the German number format

  • be encoded in UTF-8

  • use a semicolon as column separator

  • include a header line

  • use LF as row separator

 1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_EXP_FMT(
 2      'csv-default'                      -- p_fmt_name
 3     ,'CSV'                              -- p_fmt_type
 4     ,'YYYYMMDD'                         -- p_date_format
 5     ,'YYYYMMDD HH24MISS'                -- p_timestamp_format
 6     ,'UTF8'                             -- p_encoding
 7     ,true                               -- p_header
 8     ,null                               -- p_null_string
 9     ,'Y/N'                              -- p_boolean_string
10     ,'auto'                             -- p_delimit
11     ,';'                                -- p_column_separator
12     ,null                               -- p_row_separator
13     ,false                              -- p_bdate_first_col
14     ,',.'                               -- p_numeric_characters
15);
16
17execute script API_REDADM.ADD_APP_ACT_EXPORTS(
18      'emp-new'                          -- p_exp_name
19     ,'csv-default'                      -- p_fmt_name
20     ,'APB_HR'                           -- p_obj_schema
21     ,'EMP_NEW'                          -- p_obj_name
22     ,'@{bdate}_@{app_name}_export.csv'  -- p_filename
23     ,'bdate = @{bdate}'                 -- p_where
24     ,false                              -- p_skip_on_empty
25);

7.2.3. Add a Mail Action for the Export

 1execute script API_REDADM.ADD_APP_ACT_EXP_MAIL(
 2      'HR_READY'                -- p_app_name
 3     ,'mail-emp-new'            -- p_action_name
 4     ,'send CSV for new emps'   -- p_description
 5     ,'FINAL'                   -- p_finished_type
 6     ,'emp-new'                 -- p_exp_name
 7     ,'app_notify_final'        -- p_mail_template
 8     ,'<your mail>'             -- p_recipients
 9     ,'2025-09-01'              -- p_valid_from_bdate
10     ,true                      -- p_active
11     ,true                      -- p_run_once
12     ,0                         -- p_run_delay
13     ,-1                        -- p_bdate_retention
14     ,null                      -- p_cal_name
15     ,null                      -- p_cal_key
16     ,0                         -- p_bdate_retention_future
17);

Note

p_bdate_retention is set to -1 (unlimited), so that the action is also triggered for the business dates of the example data, which lie in the past. In production, a limited trigger window of a few days is recommended.

Check the configuration in REDADM.RED_APP_ACTIONS, REDADM.RED_APP_ACT_EXPORTS, and REDADM.RED_APP_ACT_EXP_FORMATS.

The status of the action is shown in REDADM.STA_APP_ACTION_STATUS, the details of each run in REDADM.STA_APP_ACTION_DETAIL.