Implementation

Expected I/O: Input and Output

INPUT: File type where benefits under the same policy, spread across multiple lines.

Open

OUTPUT: The ultimate aim is to bring all benefits under same policy in a single line in Excel.

Open

Solution Design:

1. Create Process Flow

   1. Test_PF_Enrollment_Inbound

   2. Test_PF_Enrollment_Data_Validation

2. Create Template

3. Create Transaction

Solution:

1. Create Process Flow: Test_PF_Enrollment_Inbound:

This process flow converts the client format to a Standard Canonical Format and involves fetching the client file from an input FTP location, archiving the original file, creating a standard XML file, and handling file and entry creation and deletion.

Here's a description of each activity in the "Test_PF_Enrollment_Inbound" process flow:

• Add Start Event

  • Drag and drop the "Start Event" onto the canvas.

  • Configure the start event if necessary (e.g., set any initial variables or triggers).

• Add FTP Source Activity

  • Drag and drop the "FTP Source" activity.

  • Configure the FTP details (host, port, username, password, file path, etc.).

  • Set the output variable for the source file name.

• Add Unique ID Generation

  • Drag and drop a "Data Mapping" activity.

  • Configure it to generate a unique identifier for the incoming file.

• Add Input File Layout Verification

  • Drag and drop the "Schema Validation" or "Excel Schema" activity.

  • Configure it to validate the incoming file layout against a predefined schema.

• Add Standard Mapping

  • Drag and drop a "Data Mapping" activity.

  • Map the data from the source format to the standard format.

  • Converting the Standard XML 

• Add Stream Consumer

  • Drag and drop the "Stream Consumer" activity.

  • Configure the stream consumer settings.

• Add Layout Status Mapping

  • Drag and drop another "Data Mapping" activity.

  • Configure it to handle the layout status.

  • Sets Valid layout status to 'false' on error

• Add Entry File Layout Verification

  • Drag and drop the "JSON Schema" activity.

  • Configure it to validate the entry file’s layout.

• Add FileQueue Mapping

  • Drag and drop a "Data Mapping" activity.

  • Configure it to map data to the FileQueue table.

• Store Entry File Data to Database

  • Drag and drop the "Database Target" activity.

  • Configure it to insert entry file data into the database.

• Add Mail Target for Error Handling

  • Drag and drop the "Mail Target" activity.

  • Configure the email settings (recipient, subject, body, etc.).

  • Set it to trigger on errors. If the file has an error then send Mail

• Add LAN Target for File Storage

  • Drag and drop the "File Target" activity.

  • Configure it to store files in the LAN target location.

  • Storing Standard XML over Inprogress LAN location

• Add Entry File Layout

  • Verifies Entry File's layout 

• Add Trigger File LAN Target

  • Trigger file in LAN Target

• Add Entry File To Filequeue mapping

  • mapping for Inserting data in FileQueue Table

• Add Database Activities

  • Drag and drop the "Database Source" and "Database Target" activities as needed.

  • Configure them to retrieve and insert data as per the process requirements.

• Add Advance DataBase Source FileQueue Table

  • Database Source To Get Updated Data from the Database

• Add EntryFile To FileNameDetails Mapping

  • EntryFile To FileNameDetails Mapping

• Add ADT_Insert_FileNameDetails abd ADT_Insert_FileSummary

• Add Final Activities and End Event

  • Add any final data mappings, validations, or storage activities.

  • Drag and drop the "End Event" onto the canvas.

  • Configure the end event if needed.

• Validate and Save the Process Flow

  • Check for any errors or warnings in your process flow.

  • Validate the process flow to ensure all configurations are correct.

  • Save the process flow.

• Deploy the Process Flow

  • Once saved, deploy the process flow to make it active.

  • Monitor the execution of the process flow to ensure it runs as expected.

Summary of the Process Flow

The process flow begins with fetching the client file from an input FTP location, followed by archiving the original file, converting the file to a standard canonical format, deleting the original client file, and finally creating an entry file.

1. Fetch Client File:

   • Connect to the FTP server using an FTP client or script and download the file that matches the specified file pattern, ensuring successful transfer.

2. Archive Original File:

   • Create an archive directory if it doesn’t exist and copy the downloaded file to this archive folder.

3. Convert to Standard Canonical Format:

   • Define the logic to convert the client file into a standard XML format by parsing the client file and transforming the data into the required XML structure.

   • Create an in-progress directory if necessary and save the converted standard XML file in this directory.

4. Delete Original Client File:

   • Verify the successful creation of the standard XML file by checking its existence and correctness. Upon successful verification, delete the original client file from the input FTP location.

5. Create Entry File:

   • Ensure the existence of a directory for entry files. Generate a JSON entry file containing details such as the original file name, path, and the time the file was received, and save this JSON file in the specified location.

Process Flow Description: Test_PF_Enrollment_Data_Validation

This process flow is designed to validate the standard layout file, categorize records into good and error categories, and deliver all records to the output FTP location. It also handles data discrepancy reports and email notifications for failures.

Step-by-Step Instructions

• Add Start Event

  • Drag and drop the "Start Event" onto the canvas.

  • This event initiates the process.

• Parse Trigger File

  • Drag and drop the "Parse Trigger File" activity.

  • Configure it to parse the incoming trigger file.

• Extract Entry File (DM Extract EntryFile)

  • Drag and drop a "Data Mapping" (DM) activity.

  • Configure it to map values from the entry file to the context.

• Parse Entry File

  • Drag and drop the "Parse Entry File" activity.

  • Configure it to parse the entry file data.

• Validate File Layout (Read Standard XML)

  • Drag and drop the "Read Standard XML" activity.

  • Configure it to read the standard format XML and validate the file layout.

• Conditional Validation (Exclusive Gateway)

  • Drag and drop an "Exclusive Gateway" activity.

  • Configure the gateway to route based on whether the layout is valid or invalid.

• Invalid Layout Handling

  • DM Update FileQueue InvalidLayout

    • Drag and drop a "Data Mapping" activity.

    • Configure it to update the file queue with invalid layout status.

  • Put Context Variable

    • Drag and drop a "Put Context Variable" activity.

    • Configure it to update the context with error details.

• Validate Good and Bad Data (DM Data Validation)

  • Drag and drop a "Data Mapping" activity.

  • Configure it to validate the data and segregate into good and bad data.

• Standard Layout Excel

  • Drag and drop the "Standard Layout Excel" activity.

  • Configure it to apply the standard layout in Excel format.

• Stream Selector Service

  • Drag and drop the "Stream Selector" activity.

  • Configure it to select the stream based on the data.

• Encrypt Data (DS KeyManager Encrypt)

  • Drag and drop the "Data Security" activity.

  • Configure it to encrypt data using the Key Manager.

• Canonical Layout Excel for Error Records (LAN Target ErrorRecords)

  • Drag and drop the "LAN Target" activity.

  • Configure it to store error records in the canonical layout Excel format.

• Check for Good Records (Exclusive Gateway)

  • Drag and drop an "Exclusive Gateway" activity.

  • Configure the gateway to check if there are any good records.

• Handle Good Records

  • LAN Target GoodRecords

    • Drag and drop the "LAN Target" activity.

    • Configure it to store good records over LAN target.

  • Insert into Client Data Table (DM Insert into ClientDataTable)

    • Drag and drop a "Data Mapping" activity.

    • Configure it to map and insert data into the client data table.

  • Update File Summary (ADT Update FileSummary)

    • Drag and drop an "ADT" activity.

    • Configure it to update the file summary.

  • Insert Filename Details (ADT Insert FileNameDetails)

    • Drag and drop an "ADT" activity.

    • Configure it to insert filename details.

  • Update FileQueue (ADT Update FileQueue)

    • Drag and drop an "ADT" activity.

    • Configure it to update the file queue.

• Final Activities

  • Process the File (DM Processed File)

    • Drag and drop a "Data Mapping" activity.

    • Configure it to process the file and store the final output.

• End Event

  • Drag and drop the "End Event" onto the canvas.

  • This event signifies the completion of the process flow.

Summary of Process Flow

This summary outlines the steps and configurations necessary to set up a process flow in Adeptia Connect, handling file validation, categorization, delivery, reporting, and notifications efficiently.

1. Pick Up Canonical XML File

   • Event Trigger: Configure the FTP server to trigger the process when a new file is uploaded.

   • Fetch File: Connect to the FTP server and download the canonical XML file.

2. Validate Standard Layout File

   • Load XML File: Load the canonical XML file for validation.

   • Validate Data: Implement validation rules according to the standard layout and validate each record in the XML file.

   • Use Customs Plugin: Utilize the UNUM_CP_ExcelColumnName_Handler plugin to clean up column names by removing numbering.

3. Categorize Records

   • Good Records: Identify and separate records that pass validation.

   • Error Records: Identify and separate records that fail validation, specifying the reasons for failure.

   • Save Records: Save good and error records in separate files for further processing.

4. Deliver Records to Output FTP

   • Connect to Output FTP: Configure the output FTP location based on user-defined transaction settings.

   • Upload Files: Upload the good and error records files to the configured output FTP location.

5. Send Data Discrepancy Reports

   • Generate Report: Generate a discrepancy report detailing the errors found during validation.

   • Send Report: Send the report to the configured email addresses using an email client or script.

6. Send Email Notifications on Failures

   • Monitor Process: Monitor the process for any failures during validation or FTP operations.

   • Send Notification: Send an email notification detailing the failure.

7. Template Publishing

   • Publish Template: Publish the template as part of this process flow, ensuring it is accessible for future use.

8. Transaction Configuration

   • Configure Output FTP Location: Use the user-created transaction to configure the output FTP location and filename pattern for the output file.

   • Example Configuration: Configuration details could be stored in a configuration file or database.

Customs Plugin Details: Test_CP_ExcelColumnName_Handler

• This plugin removes the numbering from column names in the format ColumnName_Numbering.

• Usage Example:

  • Input: BenefitID_1, BenefitID_2

  • Output: BenefitID

2. Create Template

2.1 Create or define a new template within a project, requiring a name, description, and project selection before moving on to further configuration steps.

2.2 Choose the type and direction of the template you are defining. The selections made here will influence the subsequent steps and configurations for the template.

2.3 Choose Template Type:

• Select template type: Choose the type of template from the dropdown menu. Example: Custom.

• Select direction: Choose the direction from the dropdown menu. Example: Inbound.

• Click the Next button to proceed to the next step.

2.4 Define Process Flow

• Select Process Flow: TEST_PF_Enrollment_Inbound_Copy

• Guidelines: Ensure that you have followed the suggested guidelines to create a process flow for this template.

2.5 Select the activities to be configured in the template.

2.6 Add Template Parameters

2.7 Define In-progress Folder Location

2.8 Select Routing Type

2.9 Define Settings for data encryption at rest

2.10 Define the Source as defined in the below screenshot

2.11 Select the event that will trigger for data transfer

2.12 Select the target data format as JSON and create a JSON Layout.

2.13 Define Documentation URL, Save and exit

3. Create Transaction

3,1 Enter the Name and description of the transaction

3.2 Select Network and Partner

3.3 Define Transaction binding name, Partner Type, ID, Email Encryption and target file status.

3.4 Enter the FTP account information

3.5 Enter the FTP folder location and check the delete file on success

3.6 Enter the file name, Select Trigger type as POLLING and specify polling interval

In Other Settings define action on misfire event as ignore misfire and connector.

3.7 Select your source data format and layout

3.8 Select the Defined mapping, Save and Exit

Steps to Turn on the Transaction

After you have Saved and Exit the Transaction page. You will be asked to Turn on the transaction

 Viewing execution details

• Navigate to the Dashboard to access execution details. The Status column will indicate the current status of the transaction.

• Select the execution icon located under Action to delve into specific information about the transaction.

• To gain insights into each step's execution details, click on the information icon corresponding to that particular step.

• For additional transaction details, expand by clicking on the More arrow within the Action menu.

Summary

The solution design involves three main components: creating a process flow, creating a template, and creating a transaction.

Process Flow

A process flow called “Test_PF_Enrollment_Inbound” converts a client’s data to Standard Canonical format. This process involves pulling up the client file from an FTP server, making a backup of original file, converting it into the standard XML layout and controlling both new and old files. The steps that make up this process include connecting to the FTP server, downloading and archiving the file, establishing and executing the conversion logic, confirming successful conversion, and producing a JSON entry file containing detailed information about data files.

Similarly, another process flow called “Test_PF_Enrollment_Data_Validation” is aimed at verification of standardized layout file then categorizing them in to good records and bad ones are saved in an output FTP location. This method includes fetching XML document from FTP server; applying plugin to make column names standardized, sorting out records into different categories such as correct or incorrect ones, forwarding some files to output FTPs servers; creating reports for divergences as well as sending emails when system crashes.

Template Creation

The process of creating a template involves defining a new template with a specific name, description, and project selection. This also involves choosing the template type and direction, selecting “TEST_PF_Enrollment_Inbound_Copy” to establish the process flow, and then adding relevant activities. Other steps include introducing company user defined template parameters in addition to IT users and business. It is vital to define an in-progress folder location, routing type (context-based or content-based), data encryption settings at rest, specify data source, choose trigger event for data transfer, select JSON as target data format, create JSON layout and provide documentation URL before save & exit on ‘Template Creation’ screen.

Transaction Creation

Several steps must be followed in order to create a transaction. You must provide the transaction name and description, choose the network and partner, select the details of binding including partner type, ID, e-mail encryption and target file status. The next step will involve providing FTP account information, specifying the FTP folder location with the option of deleting the file after transfer is successful, setting up a source data format/layout, defining the misfire actions for events as well as establishing connectors. Additionally you will define a filename and set trigger type to polling using recommended settings of 5 minutes polling frequency and stable time of 10 seconds.

Additionally there are some actions that are done after firing or connector fails.

After these steps have been completed, you need to save them. Activation follows once this setup has been made. By checking on Dashboard where status column shows current phase of this transaction one can monitor how it runs through its execution period. For more detailed information regarding every step’s performance just click an appropriate icon (indicated by “i”). Besides that, additional transaction details can be accessed by extending the information from Action menu using More arrow.

Moreover,"Test_CP_ExcelColumnName_Handler", customs plugin plays a major role in making each column names similar eliminating numbers thereby streamlining for further use.