Parse Excel File

Many a times, we need to import data from excel into PRPC data structures. There are quite a few ways to do so, here I am describing an OOTB internal activity to import data from Excel (XSLX) into PRPC clipboard.

As a pre requisite we need a template excel file uploaded as Rule-File-Binary.  Later this template file is used to interpret the uploaded spread sheets values and map into appropriate clipboard structures. Always have the first row as heading, and second row should have following syntax

{.PageListName().PropertyName input}

Next write an activity, to get the file location of the uploaded excel file (use FileUpload control to upload the excel sheet), and then call OOTB internal activity named MSOParseExcelFile. This activity takes two mandatory properties, first is the location and name of the uploaded excel file, second is the name of the template file.

With this MSOParseExcelFile would map the contents of the excel file in to appropriate clip board structures.

Here is a scenario illustrating the same

Scenario: Upload Employee Details (Name, ID, Manager, Age) from an excel file

Step1: Create Template File and upload it is Rule-File-Binary

Step 2: Write an activity to call MSOParseExcelFile

Param Supplied:

FSFFileName : Name and Location of the uploaded excel file.

TemplateRFB: Name of the uploaded binary file, it should follow <Directory name>!<Rule Name>!<extension>

Step 3: Run the activity to check results

Hope the above article is covers most of the required stuff. Please leave a comment with your practical experiences on how useful this article was and also any thing needs further explanations. 

Feedback's are welcomed in the form of comments.

Vamshi

Sub Case Vs SubFlow

Often during a design discussion, we hear questions / discussions like, why do we need a sub case for this task, cant this be done by a sub flow? Whatever a sub case can do, sub flow can do the same, let’s not complicate by having sub cases. Contrary, we also hear, design a sub case, we can extend it later if required. All of these questions / discussions are valid, but not directed or supported. Here is a small article, describing when to use which Design Paradigm, which would help us make above design discussions directed and supportive with data points

Given the capabilities for each, there are many ways to compare pros and cons of each. However, the scenario or requirements should be detrimental  of the design paradigm.

- Sub Flows, in this discussion represent Sub Process / Spin Offs / Split for Each / Split for Join

- “Task” has to be read either as Sub Flow or Sub case depending on the context

Dependency Management: Is the a requirement to Start off the “Task”, when certain conditions are met. Example: StartLoanEvaluation “Task” needs to be performed only after Customer submitting required Proofs for Source of Income.

Sub Flows: Not Achievable.

Sub cases: Achievable.  

Data Propagation:  

·         Is the requirement to not show /save unwanted data to the “Task” or to show / save as much data as required to perform the “Task”.  Example: To perform an Assessment of a loan, Assement officer should know about Loan Details / Income and expenditure details, but not required to know about applicant’s personal details.

Sub Flows: Not Achievable.

Sub cases: Achievable.  Using Data Propagation, we can select the data properties to be copied into sub cases.

·         Is the requirement not to have stale data while performing the “Task”

Sub Flows: Achievable.

Sub cases: Not Achievable.  We need to write custom code to fetch data from parent to child case.

Specialization:

·         Is the requirement to Specialize flow rules

Sub Flows: Achievable.

Sub cases: Not Achievable.  

·         Is the requirement to Specialize Class rules

Sub Flows: Not Achievable.

Sub cases: Achievable.  

Storage:

·         Is the requirement to store the details of the “Task” in separate table. Example: Store all Underwritten claims to a different table

Sub Flows: Not Achievable.

Sub cases: Achievable.

Security:

·         Is the requirement to have different secured strategies for the “Task”.

Sub Flows: Not Achievable.

Sub cases: Achievable.

Reporting:

·         Is the requirement to have fine grain report for the “Task”

Sub Flows: Not Achievable.

Sub cases: Achievable.

Policy Override:

·         Is the requirement to suspend only the “Task”, when certain conditions are met

Sub Flows: Not Achievable.

Sub cases: Achievable.

·         Is the requirement to suspend not only  the “Task” but also the origination flow, when certain conditions are met

Sub Flows: Achievable.

Sub cases: Not Achievable.

If we can have each box ticked off and then a simple math would help us decide which one should our design have, Sub Case or Sub Flows.

Hope the above article is covers most of the required stuff. Please leave a comment with your practical experiences on how useful this article was and also any thing needs further explanations. 

Feedback's are welcomed in the form of comments.

Vamshi

Connectors - Exception Handling

Integrating with external systems can introduce additional problems. Network errors involving firewalls, incorrect authentication or credentials or system failures with respect the messages being passed are just some examples.  To ensure effective results, we need to have a graceful exception handling approach in place.

Let’s discuss on what are the different exception or errors we expect, how to handle these in various scenarios, finally let’s do a sample implementation and exception handling.

For quick identification and fixing of the errors we need understand different types of errors which can occur while integrating with third party applications. Broadly we can classify them as

·         Transient

·         Permanent

Transient errors typically don’t last very long and correct themselves over time. For example, consider a situation where the remote host of the application or system the connector is accessing is restarted or temporarily unavailable. 

Permanent errors can also occur. These errors are typically due to a configuration error or an error in the remote application logic. For example, consider a situation in which an invalid SOAP request is sent to a SOAP service. In this case, the error persists until the SOAP request message is fixed.

To understand how to handle these exceptions, we need to first understand how connector rules are configured to execute. A connector rule could be called from

·         Flow (Integrator) / Activity

·         Data Pages

For Flow/Activity rules, we can use OOTB ConnectionProblem Flow, which would retry transient errors for 5 times before placing the assignment into IncompleteConnections Work Basket. In case of Permanent errors, another sub flow FlowProblems is initiated, from where an administrator can either Resume the flow from current step, else restart the flow from where error occurred. The ConnectionProblem Flow, can be extended to customize work baskets, SLA rules or other requirements as demanded by the business case.

**Point to remember, do not have transition step configured in the activity rule for the step where we can making Connect-XXX calls. If we have transition step, ConnectionProblem flow configured in connect rule form is not initiated by Pega.

Sample Implementation:
Consider the following Flow

Sample Flow with Integrator

Upon User1, completion of Assignment 1, an Integrator is triggered followed by another assignment, which is routed to a work basket. Now configure connector rule to include ConnectionProblem flow in the Error Handling section of the rule form.

At run time, if any exception occurs while executing the Connect rule, ConnectionProblem flow is initiated

Now Admin or Platform Support team can monitor the work basket IncompleteConnections  and fix the connection issues and restart the flow execution from here. 

In addition, Platform Support team can also fetch a report to quickly identify how many cases are impacted with similar issue. To do so, Pega --> Process and Rules --> Tools-->My work --> Work By WorkBasket

In case of DataPages, we need utilize POST LOAD PROCESSING activity, to verify .pyStatusMessage property for the exception and take appropriate remediation steps. 

As part of remediation steps, we can initiate the same ConnectionProblem Flow and also provide appropriate User Friendly Error Message to the end user.

**ConnectionProblem flow is not initiated by Pega even if it is configured in Connect Rule form.

In summary this design pattern prescribes, exception handling for any errors occurred while fetching data from external systems, should be notified, corrected/fixed, and re executed.

Hope the above article is covers most of the required stuff. Please leave a comment with your practical experiences on how useful this article was and also any thing needs further explanations. 

Feedback's are welcomed in the form of comments.

Vamshi