PCM requires the creation of partners, applications and flows between them with the appropriate properties. This activity is basically performed through PCM UI. In case of heavy volume of partners, it is tedious to create multiple partners and flows through the UI. Also, the migration of the trading partner data and its setup is a tedious process during the movement from one environment to other, cloud as such. We developed an automation process which eases the job of migration and the data movement and also creating new set ups.
The shell script uses a file with SQL select statements. The SQL statements include fetching the TP and Workflow information pertaining to the TP Name which we pass as input to the shell script.
It uses the below tables
petpe_mailbox – This varies based on the requirements
The successful execution of the shell script provides us with the output which is the information of the TP and its Workflow.
Now, a Perl Script is created to create information for the new TPs.
The Perl script uses the output from the shell script and input TP list and creates the information for TP and workflows for all the TPs present in the input TP list.
The Perl script fetches the table details from the shell script output and compares with the input TP list and replaces the values with the values from the input TP list, thus creating new SQL statements with the input TP values. The same process is continued for all the tables and for all the data present in the base workflow details (output from shell script).
In case, of any modifications for any particular rules, the logic is written in the Perl script and is used when creating the SQL statements.
The SQL statements created are then stored into an output file. This file will have all the insert statements which are required for the TPs and Workflow creations in PCM.
The process can be used for migration as well as for new setup of data.
We need to have the database client in the environment for the script to connect to DB. This is required for updating the tables in case of new set up and getting/updating details in case of migration.
The below are the required command line database client for different database
Oracle – sqlplus
MSSQL – sqlcmd
DB2 – in built
In addition to this, we need to have the Perl installed.
New Set Up
We get the trading partner list, which contains the information to create the trading partner and the details to create the workflow and the rules.
The list looks like the above, which contains the Unique ID and Client Name which are used for the Trading Partner ID and the MB_Verify columns contains the information which is used in the QA Check rule, which is used to check condition based on the value. The MB location are provided in the sheet, which is used for the pickup and drop location for the setup. We could also see the filename column, which is used for the Rename rule to rename the filename. The Application ID and the Template No are used to link the partner with that application and the rules are used based on the Template No.
The above sheet can vary based on the customer. e.g.: If a customer uses map, he can give a column with the map names and we can use that to be put up in the Translation rule.
Once we get the above sheet, we can move on to execute the Perl script by providing the above trading partner input sheet and an empty file to get the output insert statements.
The Perl script takes the trading partner sheet and fetches the template no and based on the template no, the workflow details are taken from the output file and creates the rules and the properties are matched from the various columns provided in the trading partner sheet. This gets executed for all the rows in the trading partner sheet. It provides us with the output upload file which contains all the insert SQL statements.
It is then used for the bulk insert. This creates the trading partners and the workflow details for the same based on the base template.
We create an input file, which contains the trading partners which has to be migrated. The trading partner names are placed one below the other.
Note: In case of all the trading partners, we can use SQL query and fetch it.
Now, we have to execute the Shell Script by passing the input file created earlier as a parameter.
This creates a file which contains all the required details of the trading partner and its workflow. The script fetches the trading partner and workflow details and spools it into a file.
Next, Lets run the Perl script by passing the file created from shell script and another file to get the Perl script output as two parameters.
This collects the trading partner and workflow details from the first parameter and executes the logic present in the Perl script, which can be any changes which is required for the trading partner during migration etc and creates SQL statements for the new setup into the file given in the second parameter.
Note: The logic in the Perl script can be the changes such as email id e.g. the email in the old environment might be QA@test.com and after migration we need it to be email@example.com. And any other property changes required for the rules can also be programmed.
Once we get the output from the Perl Script, we can deploy it at a go. This provides the new environment with all the trading partner and its related work flow details.