7. Configure an Application And Trigger Acions

The goal is to send out a CSV file via mail containing all new employees of both divisions EAST and WEST as soon as a business day has been finished. The data should contain only BDate, names, divison and department - no salary and birthdate.

Currently only one table exists in PSA_EMP, however, we configure the application ‘HR-ready’ in the way that ne tables in the same schema will be included implicitly.

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);

Check the following REDADM objects to see the result of the above confguration:

  • RED_APPLICATIONS

  • RED_APP_COMPONENTS_V (RED_APP_COMPONENTS will show the components after the next refresh)

To check the current status of the application and its components check in REDADM

  • RED_APP_STATUS

  • RED_APP_OBJECT_STATUS

In theses status views you will probably find something like this:

7.2. Configure the Mail Export

7.2.1. Create an Export View

First of all we need an application mart (APB_HR) where we can place our 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 German numeric format

  • be encoded in UTF-8

  • user semicolon as column delimiter

  • 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);

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_apb'          -- 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     ,999                       -- p_run_delay
13     ,3                         -- p_bdate_retention
14);

Note

The p_run_delay in this example is set to 999 day which means that the action will be triggered for BDates also in the past. As an alternative you may change all the dates in the example data to actual dates - then a value of 0 or at least some days would be sufficient.

Check your configuration using the following tables and views in REDADM:

  • RED_APP_ACT_EXPORTS

  • RED_APP_ACT_EXP_FORMATS

The action status is recorded in

  • RED_APP_ACT_STATUS

Finally refresh the repository metadata:

1execute script API_REDADM.REFRESH_APP_COMPONENTS;