Configuring BPM FlexFields

MyST can be used to execute SQL files at deploy-time. This approach can be useful for updating BPM Flexfields at deploy-time.

To use this approach, you first need to create a Maven project in your SCM which will be used to store the SQL file which contains the BPM Flexfields for deployment. This project can be very simple and just contain the following files:

• pom.xml

• flexfields.sql

Samples of these are below:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <groupId>com.mycompany.bpm</groupId>
    <artifactId>flexfields</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging> 

    <properties>
        <myst.component.type>sql</myst.component.type>
        <sql.execute>(EMBEDDED)/flexfields.sql</sql.execute>
        <sql.data-source-username>${core.product[rcu].param[db-user-prefix]}_SOAINFRA</sql.data-source-username>
        <sql.data-source-password>${core.product[rcu].param[db-password]}</sql.data-source-password>
        <sql.data-source-url>${core.product[rcu].param[db-url]}</sql.data-source-url> 
        <sql.fail-on-error>true</sql.fail-on-error>
        <sql.client>sqlplus</sql.client>
    </properties>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>   
                <configuration>
                    <classesDirectory>.</classesDirectory>
                    <includes>
                        <include>**.sql</include>
                    </includes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

create or replace
procedure UPS_WFATTRIBUTELABELMAP(input_id varchar2, input_taskattribute varchar2, input_labelname varchar2, input_active varchar2, 
input_workflowtype varchar2, input_datatype varchar2, input_description varchar2, input_formatstyle varchar2)
as
begin
    merge into WFATTRIBUTELABELMAP m using dual on (m.id = input_id)
    when matched then update set labelname = input_labelname, taskattribute = input_taskattribute, active = input_active,
    workflowtype = input_workflowtype, description = input_description, formatstyle = input_formatstyle, datatype = input_datatype, updateddate = sysdate
    when not matched then insert (id,labelname,taskattribute,active,workflowtype,datatype,description,formatstyle,createddate,updateddate) 
    values (input_id,input_labelname,input_taskattribute,input_active,input_workflowtype,input_datatype,input_description,input_formatstyle,sysdate,sysdate);
end UPS_WFATTRIBUTELABELMAP;
/

call UPS_WFATTRIBUTELABELMAP('20a89d4b-b599-4818-8653-bea561b4ea59','ProtectedTextAttribute11','employeeEmail','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('9541296a-0dbc-4e91-bab8-ea39cc0d209e','ProtectedTextAttribute12','customerSegmentation','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('538c093e-2cd1-415e-9af7-a851a0f541ba','ProtectedTextAttribute1','processGroupInstanceId','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('8eb27cab-bf4f-4b93-b2a4-b46849303269','ProtectedNumberAttribute1','bankCode','T','BPEL_WF_10.1.3','Number','','');
call UPS_WFATTRIBUTELABELMAP('5ff677ae-1628-491f-b000-fc86f06cd8cd','ProtectedTextAttribute10','bankLocCode','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('778634b9-f311-419d-8804-65d73737a2d5','ProtectedTextAttribute13','processType','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('4eccf6cc-f1cc-4cb7-bb54-e6d3437c603a','ProtectedTextAttribute4','requesterId','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('6d70f873-2ae2-4aa3-9169-e9ed4cbffeea','ProtectedTextAttribute17','customerName','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('732787de-c922-4c6f-8671-b76f4144f6ea','ProtectedTextAttribute2','businessObjectId','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('20519bed-69b1-4ae4-bc60-0988c1cb7887','ProtectedTextAttribute19','bankLocationName','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('598153ce-6780-457a-bca4-be3a893d8ec7','ProtectedTextAttribute20','bankName','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('71804f42-1944-44bd-8ff6-eda1cb88a97a','ProtectedTextAttribute18','advisorEmail','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('af3f7381-6f31-463e-a6a0-c730640f28e5','ProtectedTextAttribute19','channel','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('7a1c8b35-b047-4770-a801-4c713c586bcd','ProtectedTextAttribute5','currentChannel','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('5d5f17d6-97a8-4eaa-8af8-b4d6fc1f5cd0','ProtectedTextAttribute20','dossiernummer','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('3157f3f4-6d68-4540-93e1-304ce27c3470','ProtectedTextAttribute9','functionalVersion','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('5832cfaa-59d9-4325-a100-36de41ec6f71','ProtectedTextAttribute19','klantnaam','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('5561447f-ed40-466f-a67a-be3560982999','ProtectedTextAttribute5','phase','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('39f95bf1-9175-4113-a3f6-6f41ec3d12ef','ProtectedTextAttribute7','processInstanceId','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('68ee5b8b-9849-4ed6-bf64-2a20bac9cd65','ProtectedTextAttribute14','processMajorVersion','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('c4ccc310-6a7f-49e9-aeab-e1512477ddba','ProtectedTextAttribute18','product','T','BPEL_WF_10.1.3','Text','','');
call UPS_WFATTRIBUTELABELMAP('1610224c-00fa-42c1-9a08-d0a14a52534a','ProtectedNumberAttribute2','retryCounter','T','BPEL_WF_10.1.3','Number','','');

Once you are building the project using Jenkins and registering the build with MyST Studio, you can include the artifact in an Application Blueprint and deploy that using MyST Studio. The registered artifact in MyST Studio will look similar to the following:

For the above to work, you will also need SQLPlus installed on the target machine. This can be installed on AWS with the following steps, executed as root:

wget http://192.168.146.88/software/oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm

wget http://192.168.146.88/software/oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm
rpm -i oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm -i oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm
printf 'export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH\nexport PATH=/usr/lib/oracle/12.1/client64/bin:$PATH' > /etc/profile.d/sqlplus.sh
chmod +x /etc/profile.d/sqlplus.sh

You will also need to reference the SQL Plus client in MyST. This can be done by adding the following Global Variable in the Platform Blueprint:

core.product[sqlplus].home=/usr/lib/oracle/12.1/client64
action.deploy.post=sql