Two File Processing

When the system receives two files, it will update the procedures for checking and validating the data. In addition, they will implement a new process to connect to Sunlife’s system, allowing them to acquire a unique identifier (comparable to a tracking number). Moreover, they will merge individual reports into a unified document and improve the comprehensibility of error reports.

Trading Partner Flow

A file is placed in a FTP location. The process flow retrieves this file and checks for the presence of a corresponding client file in the same location.
If the client file is found, the process creates a JSON trigger file in a specified location and relocates the two source files to the same location as the trigger file.
If the client file is not found, it will insert the data in the database and end the flow.

For more details please refer to this Process Flow: SL_PF_InboundFile_TradingPartner_v2

Client Flow

A file is placed in a FTP location. The process flow retrieves this file and checks for the presence of a corresponding trading partner file in the same location.
If the trading partner file is found, the process creates a JSON trigger file in a specified location and also relocates the two source files to the same location as the trigger file.
If the trading partner file is not found, it will insert the data in the database and end the flow.

For more details please refer to this Process Flow: SL_PF_InboundFile_Client_v2

Merge Flow

For more details please refer to this Process Flow: SL_PF_Outbound_MergeFlow_v2

Once a trigger file is created in the specified location, the merge process flow is activated.
The process first selects the trading partner file.
The data from the trading partner file is converted into a standard file format.
Validation rules are applied to both the Client and Trading_Partner files.
Two data streams are generated from the validation process: one for valid records and another for erroneous records.
Validations are performed at the record level for error records, focusing on errors and warning fields.
Validations are conducted at both the record and field levels for valid records.
Two output files are generated—one containing valid records and another containing erroneous records. The details of these files are also stored in the database.
The process then selects the client file.
The data from the client file is converted into a standard file format.
Validation rules are applied to the standardized file format.
Two data streams are generated from the validation process: one for valid records and another for erroneous records.
Validations are performed at the record level for error records, focusing on errors and warning fields.
Validations are conducted at both the record and field levels for valid records.
Two output files are generated—one containing valid records and another containing erroneous records. The details of these files are also stored in the database.
Following this, a mapping process checks the pass percentage for valid records. This percentage must exceed the threshold defined in the transaction parameters.
If the percentage check is successful, both valid record files are forwarded to the merge mapping process.
The merge mapping generates three streams: a merged file, orphaned trading partner records, and orphaned client records.
A subsequent merge mapping consolidates orphaned records into a single file, including a descriptive file name.
An email notification is sent containing the erroneous records, the merged file, and the orphaned file.
Finally, all files are placed in the designated target location.

Solution Design:

Source File_Trigger.txt from FTP Location. With this source, the flow would be triggered and also further in the flow second source is also attached which picks the file from the trading partner file location.

Map values from File Trigger to Context.

Locates Trading_Partner File on the FTP Server.

Converting the Trending Partner file data to a standard file format. After it we move with Validation mapping.

Once the values are mapped to the context, we validate good and bad data in the Trading_Partner File via Mapping. Maps Trading_Partner file data to a standard file

In this validation mapping, we are generating Good records & Bad records along with errors & warnings for the Trading_Partner File.

In the Error Layout, we are generating Errors and Warnings as well.

Error Conditions

Error Conditions

IF CONDITION{$var_fetch_key_identifier = 'Employee SSN' and EMPLOYEE_SSN = ''} VALUE=["Key Identifier is Employee SSN and Employee SSN is Missing|"]

IF CONDITION{$var_fetch_key_identifier = 'Employee ID' and EMPLOYEE_ID = ''} VALUE=["Key Identifier is Employee ID and Employee ID is Missing|"]

IF CONDITION{($var_fetch_key_identifier = 'Employee SSN' and EMPLOYEE_SSN != '' ) and (saxonJavaMapperUtilityClass:replaceAll(EMPLOYEE_SSN, $varRegexSSN ,'VALID' ) != 'VALID')} VALUE=['Invalid Employee SSN|']

IF CONDITION{($var_fetch_key_identifier = 'Employee ID' and EMPLOYEE_ID!= '') and (saxonJavaMapperUtilityClass:replaceAll(EMPLOYEE_ID, $varRegexCheckEmpID,'VALID') != 'VALID')} VALUE=[ "Invalid Employee ID|"]

IF CONDITION{EMPLOYEE_EARNING != '' and not(matches(EMPLOYEE_EARNING,$varRegexEmployeeEarning))} VALUE=["Invalid Employee Earning|"]

IF CONDITION{EARNINGS_FREQUENCY != '' and $var_EmployeeFrequencyVM = 'InvalidEarningsFrequency'} VALUE=["Invalid Earnings Frequency|"]

IF CONDITION{EARNINGS_EFFECTIVE_DATE !='' and not(matches(EARNINGS_EFFECTIVE_DATE, $var_DateFormat_REGEX))} VALUE=['Invalid Earning Effective date format|']

IF CONDITION{STD_WEEKLY_EARNINGS !='' and not(matches(STD_WEEKLY_EARNINGS ,$varRegexMonthlyWeeklyEarning))} VALUE=['Invalid STD Weekly Earnings|']

IF CONDITION{LTD_MONTHLY_EARNINGS!='' and not(matches(LTD_MONTHLY_EARNINGS, $varRegexMonthlyWeeklyEarning))} VALUE=['Invalid LTD Weekly Earnings|']

IF CONDITION{STD_ELIGIBILITY_DATE!='' and not(matches(STD_ELIGIBILITY_DATE, $var_DateFormat_REGEX) )} VALUE=['Invalid STD Eligibility Date format|']

IF CONDITION{STD_ELECTION !='' and $Var_STD_ElectionVM = 'InvalidBenefitElectionType'} VALUE=["Invalid STD Election|"]

IF CONDITION{STD_BENEFIT_AMOUNT != '' and not(matches(STD_BENEFIT_AMOUNT, $varRegexBenefitAmount))} VALUE=['Invalid STD Benefit Amount|']

IF CONDITION{STD_CLASS != '' and not(matches(STD_CLASS,$varRegexClass))} VALUE=["Invalid STD Class|"]

IF CONDITION{STD_TERMINATION_DATE!= '' and not(matches(STD_TERMINATION_DATE, $var_DateFormat_REGEX))} VALUE=['Invalid STD Termination Date format|']

IF CONDITION{STATUTORY_DISABILITY_TYPE !='' and $Var_StatutoryDisabilityTypeVM ='InvalidStatutoryDisabilityType'} VALUE=["Invalid Statutory Disability Type|"]

IF CONDITION{LTD_ELIGIBILITY_DATE!='' and not(matches(LTD_ELIGIBILITY_DATE, $var_DateFormat_REGEX))} VALUE=['Invalid LTD Eligibility Date format|']

IF CONDITION{LTD_ELECTION !='' and $Var_LTD_ElectionVM = 'InvalidBenefitElectionType'} VALUE=["Invalid LTD Election|"]

IF CONDITION{LTD_BENEFIT_AMOUNT != '' and not(matches(LTD_BENEFIT_AMOUNT, $varRegexBenefitAmount) )} VALUE=['Invalid LTD Benefit Amount|']

IF CONDITION{LTD_CLASS != '' and not(matches(LTD_CLASS,$varRegexClass))} VALUE=["Invalid LTD Class|"]

IF CONDITION{LTD_TERMINATION_DATE != '' and not(matches(LTD_TERMINATION_DATE, $var_DateFormat_REGEX))} VALUE=['Invalid LTD Termination Date format|']

IF CONDITION{SUPERVISOR_EMPLOYEE_ID != '' and not(matches(SUPERVISOR_EMPLOYEE_ID ,$varRegexSupervisorEID))}VALUE=["Invalid Supervisor Employee ID|"]

IF CONDITION{SUPERVISOR_FIRST_NAME != '' and not(matches(SUPERVISOR_FIRST_NAME ,$varRegexFirstName))}VALUE=["Invalid Supervisor First Name|"]

IF CONDITION{SUPERVISOR_LAST_NAME != '' and not(matches(SUPERVISOR_LAST_NAME,$varRegexLastName))} VALUE=["Invalid Supervisor Last Name|"]

IF CONDITION{OCCUPATION_QUALIFIER1 != '' and not(matches(OCCUPATION_QUALIFIER1,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier1|"]

IF CONDITION{OCCUPATION_QUALIFIER2 != '' and not(matches(OCCUPATION_QUALIFIER2,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier2|"]

IF CONDITION{OCCUPATION_QUALIFIER3 != '' and not(matches(OCCUPATION_QUALIFIER3,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier3|"]

IF CONDITION{OCCUPATION_QUALIFIER4 != '' and not(matches(OCCUPATION_QUALIFIER4,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier4|"]

IF CONDITION{OCCUPATION_QUALIFIER5 != '' and not(matches(OCCUPATION_QUALIFIER5,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier5|"]

IF CONDITION{OCCUPATION_QUALIFIER6 != '' and not(matches(OCCUPATION_QUALIFIER6,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier6|"]

IF CONDITION{OCCUPATION_QUALIFIER7 != '' and not(matches(OCCUPATION_QUALIFIER7,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier7|"]

IF CONDITION{OCCUPATION_QUALIFIER8 != '' and not(matches(OCCUPATION_QUALIFIER8,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier8|"]

IF CONDITION{OCCUPATION_QUALIFIER9 != '' and not(matches(OCCUPATION_QUALIFIER9,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier9|"]

IF CONDITION{OCCUPATION_QUALIFIER10 != '' and not(matches(OCCUPATION_QUALIFIER10,$varRegexOccupQual))} VALUE=["Invalid Occupation Qualifier10|"]

IF CONDITION{SPOUSE_DATE_OF_CHANGE!='' and not(matches(SPOUSE_DATE_OF_CHANGE, $var_DateFormat_REGEX))} VALUE=['Invalid Spouse Date of Change format|']

IF CONDITION{SPOUSE_FIRST_NAME != '' and not(matches(SPOUSE_FIRST_NAME ,$varRegexFirstName)) }VALUE=["Invalid Spouse First Name|"]

IF CONDITION{SPOUSE_LAST_NAME != '' and not(matches(SPOUSE_LAST_NAME,$varRegexLastName))}VALUE=["Invalid Spouse Last Name|" ]

IF CONDITION{SPOUSE_DATE_OF_BIRTH != '' and not(matches(SPOUSE_DATE_OF_BIRTH, $var_DateFormat_REGEX))} VALUE=['Invalid Spouse Date of Birth format|']

IF CONDITION{SPOUSE_SSN != '' and not(matches(SPOUSE_SSN,$VarRegexSpouseSSN))} VALUE=["Spouse SSN is Invalid|"]

IF CONDITION{SPOUSE_EMPLOYEE_ID != '' and (not(matches(SPOUSE_EMPLOYEE_ID,$VarRegexSpouseEMPID)))} VALUE=["Spouse Emp ID is Invalid|"]

IF CONDITION{MARITAL_STATUS != '' and $var_MaritialStatus = 'InvalidMaritalStatus'} VALUE=["Invalid Maritial Status|"]

IF CONDITION{SPOUSE_REASON_OF_CHANGE != '' and $Var_SpouseROC = 'InvalidSpouseROC'} VALUE=["Invalid Spouse Reason of Change|"]

IF CONDITION{EMPLOYMENT_CLASS != '' and $Var_EmploymentClass = 'InvalidEmploymentClass'} VALUE=["Invalid Employment Class|"]

IF CONDITION{EMPLOYMENT_STRENGTH != '' and $Var_EmploymentStrengthsVM = 'InvalidEmployeeStrength'} VALUE=["Invalid Employee Strength|"]

Warning Conditions

Warning Conditions

IF CONDITION{generate-id(.) != generate-id(key('First_Name_Key', concat(EMPLOYEE_FIRST_NAME, '|',EMPLOYEE_LAST_NAME)))[1]} VALUE=["Duplicate First Name and last Name | " ]
IF CONDITION{EMPLOYEE_TITLE !='' and $Var_EmployeeTitleVM = 'InvalidTitle'} VALUE=["Invalid Employee Title|" ]
IF CONDITION{EMPLOYEE_FIRST_NAME = '' } VALUE=["Missing Employee First Name|" ]
IF CONDITION{EMPLOYEE_FIRST_NAME!='' and not(matches(EMPLOYEE_FIRST_NAME ,$varRegexFirstName))}VALUE=["Invalid Employee First Name|" ]
IF CONDITION{EMPLOYEE_MIDDLE_NAME = ''}VALUE=["Missing Employee Middle Name|" ]
IF CONDITION{EMPLOYEE_MIDDLE_NAME!= ''and not(matches(EMPLOYEE_MIDDLE_NAME,$varRegexMiddleName))}VALUE=["Invalid Employee Middle Name|" ]
IF CONDITION{EMPLOYEE_LAST_NAME = '' } VALUE=["Missing Employee Last Name|" ]
IF CONDITION{ EMPLOYEE_LAST_NAME != '' and not(matches(EMPLOYEE_LAST_NAME,$varRegexLastName))}VALUE=["Invalid Employee Last Name|" ]
IF CONDITION{ EMPLOYEE_GENDER = ''} VALUE=[ 'Missing Employee Gender|']
IF CONDITION{ EMPLOYEE_GENDER!='' and $Var_GenderCodesVM = 'InvalidGender'} VALUE=["Invalid Employee Gender|" ]
WHEN CONDITION{EMPLOYEE_DATE_OF_BIRTH= ''} VALUE=['Missing Employee Date of Birth|'] OTHERWISE VALUE=[IF CONDITION{EMPLOYEE_DATE_OF_BIRTH !='' and not(matches(EMPLOYEE_DATE_OF_BIRTH, $var_DateFormat_REGEX))} VALUE=['Invalid Employee Date Of Birth format|' ]]
IF CONDITION{ EMPLOYEE_HOME_ADDRESS_LINE_1 = '' }VALUE=["Missing Employee Home Address Line 1|" ]
IF CONDITION{EMPLOYEE_HOME_ADDRESS_LINE_1 != '' and not(matches(EMPLOYEE_HOME_ADDRESS_LINE_1,$varRegexAddress1))}VALUE=["Employee Home Address Line 1 has invalid Characters or length|"]
IF CONDITION{ EMPLOYEE_HOME_ADDRESS_LINE_2 = '' }VALUE=["Missing Employee Home Address Line 2|" ]
IF CONDITION{EMPLOYEE_HOME_ADDRESS_LINE_2 != '' and not(matches(EMPLOYEE_HOME_ADDRESS_LINE_2,$varRegexAddress2))}VALUE=["Employee Home Address Line 2 has invalid Characters or length|" ]
IF CONDITION{ EMPLOYEE_CITY = ''} VALUE=["Missing Employee Home City|" ]
IF CONDITION{EMPLOYEE_CITY != '' and not(matches( EMPLOYEE_CITY, $varRegexHomeCity))} VALUE=[ "Invalid Employee Home City|"]
IF CONDITION{EMPLOYEE_STATE = '' } VALUE=[ "Missing Employee Home State|"]
IF CONDITION{ EMPLOYEE_STATE != '' and $Var_EmployeeHomeStateVM = 'NonState' } VALUE=["Invalid Employee Home State|"]
IF CONDITION{EMPLOYEE_HOME_ZIP_CODE = '' } VALUE=["Missing Employee Zip Code|" ]
IF CONDITION{EMPLOYEE_HOME_ZIP_CODE != '' and not(matches(EMPLOYEE_HOME_ZIP_CODE, $varRegexZipCode)) }
VALUE=["Invalid Employee Zip Code|"]
IF CONDITION{EMPLOYEE_COUNTY = '' } VALUE=["Missing Employee County|" ]
IF CONDITION{EMPLOYEE_COUNTY !='' and not(matches(EMPLOYEE_COUNTY,$VarCounty))} VALUE=["Invalid Employee County|" ]
IF CONDITION{EMPLOYEE_COUNTRY = ''} VALUE=[ "Missing Employee Country|"]
IF CONDITION{EMPLOYEE_COUNTRY !='' and $Var_EmployeeCountryCodesVM = 'InvalidCountry' } VALUE=[ "Invalid Employee Country|"]
IF CONDITION{ (EMPLOYEE_HOME_PHONE_NUMBER !='') and (not(matches(EMPLOYEE_HOME_PHONE_NUMBER,$varNumberMobilePhone )))} VALUE=["Invalid Employee Home Phone number|" ]
IF CONDITION{ (EMPLOYEE_MOBILE_PHONE_NUMBER !='') and (not(matches(EMPLOYEE_MOBILE_PHONE_NUMBER,$varNumberMobilePhone )))}VALUE=["Invalid Employee Mobile Phone number|" ]
IF CONDITION{PRIMARY_PHONE_TYPE !='' and $Var_PhoneTypeVM = 'InvalidPhoneType'} VALUE=["Invalid Phone Type|" ]
IF CONDITION{ (EMPLOYEE_EMAIL_ADDRESS != '' ) and (not(matches(EMPLOYEE_EMAIL_ADDRESS, $varRegexEmail))) }
VALUE=["Invalid Employee Email Address|"]
IF CONDITION{ (EMPLOYEE_WORK_EMAIL_ADDRESS != '' ) and (not(matches(EMPLOYEE_WORK_EMAIL_ADDRESS, $varRegexEmail))) }VALUE=["Invalid Employee Work Email Address|"]
IF CONDITION{EMPLOYEE_WORK_STATE !='' and $Var_EmployeeWorkStateVM = 'InvalidWorkState' } VALUE=[ "Invalid Employee Work State| "]
IF CONDITION{ EMPLOYEE_ORIGINAL_DATE_OF_HIRE != '' and not(matches(EMPLOYEE_ORIGINAL_DATE_OF_HIRE,$var_DateFormat_REGEX))} VALUE=["Invalid Original Date Of Hire| " ]
IF CONDITION{ EMPLOYEE_MOST_RECENT_DATE_OF_HIRE != '' and
not(matches(EMPLOYEE_MOST_RECENT_DATE_OF_HIRE,$var_DateFormat_REGEX))} VALUE=["Invalid Most recent date of hire|" ]
IF CONDITION{EMPLOYEE_ADJUSTED_DATE_OF_HIRE != '' and
not(matches(EMPLOYEE_ADJUSTED_DATE_OF_HIRE,$var_DateFormat_REGEX))} VALUE=["Invalid Employee Adjusted date of hire|" ]
IF CONDITION{EMPLOYEE_JOB_TITLE = '' } VALUE=[ "Missing Employee Job Title|"]
IF CONDITION{EMPLOYEE_JOB_TITLE != '' and not(matches(EMPLOYEE_JOB_TITLE,$varRegexEmployeeJobTitle)) } VALUE=[ "Invalid Employee Job Title|"]
IF CONDITION{EMPLOYMENT_STATUS = '' } VALUE=[ "Missing Employment Status|"]
IF CONDITION{ EMPLOYMENT_STATUS != '' and $Var_EmploymentStatusVM = 'InvalidEmploymentStatus' }
VALUE=[ "Invalid Employment Status|"]
IF CONDITION{ FULL_TIME_PART_TIME_STATUS != '' and $Var_FullTimePartTimeVM = 'InvalidStatus' }
VALUE=[ "Invalid full/part time status| "]
IF CONDITION{EXEMPT_STATUS !='' and $Var_ExemptionTypeVM = 'InvalidExemptionType'} VALUE=["Invalid Exempt Status|" ]
IF CONDITION{COMPENSATION_METHOD !='' and $Var_CompensationMethodVM = 'InvalidCompensationMethod'} VALUE=["Invalid Compensation Method|" ]
IF CONDITION{UNION_TYPE !='' and $Var_UnionTypeVM = 'InvalidUnionType'} VALUE=["Invalid Union Type|" ]
IF CONDITION{UNION_NAME !='' and not(matches(UNION_NAME,$VarRegexUnionName)) } VALUE=[ "Invalid Union Name|"]
IF CONDITION{SCHEDULED_HOURS_PER_WEEK != '' and not(matches(SCHEDULED_HOURS_PER_WEEK,$varRegexScheduledHrs)) } VALUE=[ "Invalid Scheduled Hours Per Week|"]
IF CONDITION{SCHEDULED_DAYS_PER_WEEK != '' and not(matches(SCHEDULED_DAYS_PER_WEEK,$varRegexScheduledDays)) } VALUE=[ "Invalid Scheduled Days Per Week|"]
IF CONDITION{EMPLOYMENT_TYPE != '' and $Var_EmploymentTypeVM = 'InvalidEmploymentType'} VALUE=["Invalid Employment Type|" ]
IF CONDITION{WORK_SITE_NAME != '' and not(matches(WORK_SITE_NAME,$varRegexWorkSite)) } VALUE=[ "Invalid Work Site|"]
IF CONDITION{ORGANIZATION_UNIT != '' and not(matches(ORGANIZATION_UNIT,$varRegexOrganizationUnit)) } VALUE=[ "Invalid Organization Unit|"]
IF CONDITION{ (($VarWorkSiteCount > 1) and (ORGANIZATION_UNIT = ''))} VALUE=[ "No Organization Unit|"]
IF CONDITION{BUSINESS_UNIT != '' and not(matches(BUSINESS_UNIT,$varRegexBusinessUnit)) } VALUE=[ "Invalid Business Unit|"]
IF CONDITION{DIVISION != '' and not(matches(DIVISION,$varRegexBusinessUnit)) } VALUE=[ "Invalid Division|"]
IF CONDITION{GROUP_TYPE != '' and not(matches(GROUP_TYPE,$varRegexGroupType)) } VALUE=[ "Invalid Group Type|"]
IF CONDITION{EMPLOYEE_EARNING = ''} VALUE=["Missing Employee Earning|" ]
IF CONDITION{EMPLOYEE_EARNING != '' and EARNINGS_FREQUENCY = '' } VALUE=["Missing Earning Frequency|" ]
IF CONDITION{EMPLOYEE_EARNING != '' and EARNINGS_EFFECTIVE_DATE = '' } VALUE=["Missing Earnings Effective Date|" ]
IF CONDITION{STD_WEEKLY_EARNINGS != '' and not(matches(STD_WEEKLY_EARNINGS, $varRegexSTDWeeklyEarnings))} VALUE=["Invalid STD Weekly Earnings|"]
IF CONDITION{LTD_MONTHLY_EARNINGS != '' and not(matches(LTD_MONTHLY_EARNINGS, $VarRegexLTDMonthlyEarnings) )} VALUE=['Invalid LTD Monthly Earnings|' ]
IF CONDITION{((EMPLOYMENT_STATUS = 'TE') or (EMPLOYMENT_STATUS = 'RT')) and (TERMINATION_DATE = '')} VALUE=["Missing Term date based on Employment Status|" ]
WHEN CONDITION{TERMINATION_DATE = ''} VALUE=[TERMINATION_DATE] OTHERWISE VALUE=[IF CONDITION{TERMINATION_DATE !='' and not(matches(TERMINATION_DATE, $var_DateFormat_REGEX))} VALUE=['Invalid Termination date format|']]
IF CONDITION{STD_ELIGIBILITY_DATE = '' }VALUE=["Missing STD Eligibility Date|" ]
IF CONDITION{STD_ELIGIBILITY_DATE != '' and STD_BENEFIT_AMOUNT = ''} VALUE=["Missing STD Benefit Amount|" ]
IF CONDITION{LTD_ELIGIBILITY_DATE = '' } VALUE=["Missing LTD Eligibility Date|" ]
IF CONDITION{LTD_ELIGIBILITY_DATE != '' and LTD_BENEFIT_AMOUNT = ''} VALUE=["Missing LTD Benefit Amount|" ]
IF CONDITION{POLICY_NUMBER != '' and not(matches(POLICY_NUMBER,$VarRegexPolicyNumber)) } VALUE=[ "Invalid Policy Number|"]
IF CONDITION{KEY_EMPLOYEE_INDICATOR != '' and not(matches(KEY_EMPLOYEE_INDICATOR,$varRegexKeyEmployeeIndicator)) } VALUE=[ "Invalid Key Employee Indicator|"]
IF CONDITION{WORK_AT_HOME_INDICATOR != '' and not(matches(WORK_AT_HOME_INDICATOR,$varRegexKeyEmployeeIndicator)) } VALUE=[ "Invalid Work at home Indicator|"]
IF CONDITION{Field_50_IN_75_RULE_INDICATOR != '' and not(matches(Field_50_IN_75_RULE_INDICATOR,$varRegexKeyEmployeeIndicator)) } VALUE=[ "Invalid 50 in 75 Rule Indicator|"]
IF CONDITION{HOURS_WORKED_PREVIOUS_12_MONTHS != '' and not(matches(HOURS_WORKED_PREVIOUS_12_MONTHS,$varRegexHoursWorked)) } VALUE=[ "Invalid Hours Worked Previous 12 Months|"]
IF CONDITION{EMPLOYER_CONTACT_1 != '' and not(matches(EMPLOYER_CONTACT_1,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 1|"]
IF CONDITION{EMPLOYER_CONTACT_2 != '' and not(matches(EMPLOYER_CONTACT_2,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 2|"]
IF CONDITION{EMPLOYER_CONTACT_3 != '' and not(matches(EMPLOYER_CONTACT_3,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 3|"]
IF CONDITION{EMPLOYER_CONTACT_4 != '' and not(matches(EMPLOYER_CONTACT_4,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 4|"]
IF CONDITION{EMPLOYER_CONTACT_5 != '' and not(matches(EMPLOYER_CONTACT_5,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 5|"]
IF CONDITION{EMPLOYER_CONTACT_6 != '' and not(matches(EMPLOYER_CONTACT_6,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 6|"]
IF CONDITION{EMPLOYER_CONTACT_7 != '' and not(matches(EMPLOYER_CONTACT_7,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 7|"]
IF CONDITION{EMPLOYER_CONTACT_8 != '' and not(matches(EMPLOYER_CONTACT_8,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 8|"]
IF CONDITION{EMPLOYER_CONTACT_9 != '' and not(matches(EMPLOYER_CONTACT_9,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 9|"]
IF CONDITION{EMPLOYER_CONTACT_10 != '' and not(matches(EMPLOYER_CONTACT_10,$varRegexEmployerContact)) } VALUE=[ "Invalid Employer Contact 10|"]
IF CONDITION{REPORTING_FIELD1 != '' and not(matches(REPORTING_FIELD1,$varRegexReportingField))} VALUE=[ "Invalid Reporting Field 1|"]
IF CONDITION{REPORTING_FIELD2 != '' and not(matches(REPORTING_FIELD2,$varRegexReportingField))} VALUE=[ "Invalid Reporting Field 2|"]
IF CONDITION{REPORTING_FIELD3 != '' and not(matches(REPORTING_FIELD3,$varRegexReportingField))} VALUE=[ "Invalid Reporting Field 3|"]
IF CONDITION{REPORTING_FIELD4 != '' and not(matches(REPORTING_FIELD4,$varRegexReportingField))} VALUE=[ "Invalid Reporting Field 4|"]
IF CONDITION{SUPERVISOR_EMPLOYEE_ID = ''} VALUE = ['No Supervisor ID provided|']
IF CONDITION{SUPERVISOR_EMPLOYEE_ID !='' and (SUPERVISOR_FIRST_NAME = '' or SUPERVISOR_LAST_NAME = '' )} VALUE=[ "Supervisor without First Name and Last Name| "]
IF CONDITION{((EMPLOYMENT_STATUS = 'IN') or (EMPLOYMENT_STATUS = 'OL') or (EMPLOYMENT_STATUS = 'AE') or (EMPLOYMENT_STATUS = 'SU')) and (TERMINATION_DATE != '') } VALUE=["Term date present in non Term/Retired employment status |" ]
IF CONDITION{ contains(SCHEDULED_DAYS_PER_WEEK ,'.' )} VALUE=['Partial work days|']
IF CONDITION{$varOriginalDate<0 } VALUE=['Original DOH > Most Recent DOH|']
IF CONDITION{(preceding ::Employee_SSN = EMPLOYEE_SSN) } VALUE=[ "Duplicate SSN|" ]
IF CONDITION{(preceding ::Employee_ID = EMPLOYEE_ID) } VALUE=[ "Duplicate Employee ID|" ]

Locates Client File on the FTP Server.

In this validation mapping, we are generating Good records & Bad records along with errors & warnings for the Client File. In the errors & warnings, we get the details of the Bad Records i.e what is wrong in the Bad Records.

In the Error Layout, we are generating Errors and Warnings as well.

Error Condition

Error Condition

IF CONDITION{$var_fetch_key_identifier = 'Employee SSN' and Employee_SSN = ''} VALUE=["Key Identifier is Employee SSN And Employee SSN is Missing|"]
IF CONDITION{ $var_fetch_key_identifier = 'Employee ID' and Employee_ID = ''} VALUE=["Key Identifier is Employee ID And Employee ID is Missing|"]

IF CONDITION{($var_fetch_key_identifier = 'Employee SSN' and Employee_SSN != '' ) and (saxonJavaMapperUtilityClass:replaceAll(Employee_SSN, $varRegexSSN ,'VALID' ) != 'VALID')} VALUE=['Invalid Employee SSN|']

IF CONDITION{($var_fetch_key_identifier = 'Employee ID' and Employee_ID != '') and (saxonJavaMapperUtilityClass:replaceAll(Employee_ID , $varRegexCheckEmpID,'VALID') != 'VALID')} VALUE=[ "Invalid Employee ID|"]

IF CONDITION{Employee_First_Name = ''} VALUE=["Missing Employee First Name|"]
IF CONDITION{(Employee_First_Name != '') and not(matches(Employee_First_Name ,$varRegexFirstName))}VALUE=["Invalid Employee First name|"]

IF CONDITION{ Employee_Middle_Name !='' and not(matches(Employee_Middle_Name , $varRegexMiddleName)) }
VALUE=["Invalid Employee Middle name|"]

IF CONDITION{Employee_Last_Name = ''} VALUE=["Missing Employee Last Name|" ]
IF CONDITION{(Employee_Last_Name != '') and not(matches(Employee_Last_Name , $varRegexLastName))}VALUE=["Invalid Employee Last Name|"]

IF CONDITION{ Employee_Gender= ''} VALUE=["Missing Gender|" ]
IF CONDITION{ Employee_Gender!= '' and $Var_GenderCodesVM = 'InvalidGender'} VALUE=["Invalid Employee Gender|"]

IF CONDITION{ Employee_Date_of_Birth= ''} VALUE=["Missing Employee Date of Birth|" ]
IF CONDITION{( Employee_Date_of_Birth != '' and not(matches(Employee_Date_of_Birth, $var_DateFormat_REGEX))) }
VALUE=[ "Invalid Employee Date Of Birth format|"]

IF CONDITION{Employee_Home_Address_Line_1 = ''} VALUE=["Missing Home Address Line 1|"]
IF CONDITION{(Employee_Home_Address_Line_1 !='' ) and (not(matches(Employee_Home_Address_Line_1,$varRegexAddress1)))} VALUE=["Invalid Home Address line 1|"]

IF CONDITION{(Employee_Home_Address_Line_2 != '') and (not(matches(Employee_Home_Address_Line_2,$varRegexAddress2)))} VALUE=["Invalid Home Address line 2|"]

IF CONDITION{Employee_Home_Address_Line_1 = '' and Employee_Home_Address_Line_2 != ''}VALUE=["Home Address line 2 is present but missing Home Address Line 1|"]

IF CONDITION{Employee_Home_City = ''} VALUE=["Missing Employee Home City|"]
IF CONDITION{Employee_Home_City != '' and not(matches( Employee_Home_City, $varRegexHomeCity))} VALUE=["Invalid Employee Home City|"]

IF CONDITION{Employee_Home_State = '' } VALUE=[ "Missing Employee's Home State|"]
IF CONDITION{ Employee_Home_State !='' and $Var_EmployeeHomeStateVM = 'NonState' } VALUE=[ "Invalid Employee Home State|"]

IF CONDITION{Employee_Home_Zip_Code = ''} VALUE=["Missing Zip Code|"]
IF CONDITION{Employee_Home_Zip_Code != ''and not(matches(Employee_Home_Zip_Code, $varRegexZipCode))} VALUE=["Invalid Zip Code|"]

IF CONDITION{Employee_Work_Email_Address != '' and not(matches(Employee_Work_Email_Address, $varRegexEmail))} VALUE=["Invalid Work Email Address|"]

IF CONDITION{Employee_Work_State != '' and $Var_EmployeeWorkStateVM = 'InvalidWorkState' }VALUE=[ "Invalid Employee Work State|"]

IF CONDITION{Employee_Original_Date_of_Hire !='' and not(matches(Employee_Original_Date_of_Hire,$var_DateFormat_REGEX))} VALUE=["Invalid Employee Original Date of Hire|"]
IF CONDITION{Employee_Original_Date_of_Hire = ''} VALUE=["Missing Employee Original Date of Hire|"]

IF CONDITION{Employee_Most_Recent_Date_of_Hire !='' and not(matches(Employee_Most_Recent_Date_of_Hire,$var_DateFormat_REGEX))} VALUE=["Invalid Employee Most Recent Date of Hire|"]

IF CONDITION{Employee_Job_Title != '' and not(matches(Employee_Job_Title, $varRegexEmployeeJobTitle))} VALUE=["Invalid Employee Job Title|"]

IF CONDITION{Termination_Date !='' and not(matches(Termination_Date,$var_DateFormat_REGEX))} VALUE=["Invalid Termination date|"]

IF CONDITION{Employment_Status= ''} VALUE=[ "Missing Employment Status|"]
IF CONDITION{Employment_Status != '' and $Var_EmploymentStatusVM = 'InvalidEmploymentStatus'} VALUE=["Invalid Employment Status|"]

IF CONDITION{ Full_Time_Part_Time_Status= ''} VALUE=["Missing full/part time status|"]
IF CONDITION{ Full_Time_Part_Time_Status!= '' and $Var_FullTimePartTimeVM = 'InvalidStatus' } VALUE=["Invalid full/part time status|"]

IF CONDITION{Exempt_Status != '' and $Var_ExemptStatus = 'InvalidExemptionType'} VALUE=["Invalid Exempt Status|"]

IF CONDITION{Compensation_Method != '' and $var_CompensationMethodVM = 'InvalidCompensationMethod'} VALUE=["Invalid Compensation Method|"]

IF CONDITION{Union_Type != '' and $var_UnionTypeVM = 'InvalidUnionType'} VALUE=["Invalid Union Type|"]

IF CONDITION{ Union_Name != '' and not(matches(Union_Name,$varRegexUnionName)) } VALUE=["Invalid Union Name|"]

IF CONDITION{Scheduled_Hours_per_Week = ''} VALUE=["Missing Scheduled Hours|"]
IF CONDITION{Scheduled_Hours_per_Week != '' and not(matches(Scheduled_Hours_per_Week,$varRegexScheduledHours))} VALUE=["Invalid Scheduled Hours|"]

IF CONDITION{Scheduled_Days_per_Week != '' and not(matches(Scheduled_Days_per_Week, $varRegexScheduledDays))} VALUE=["Invalid Scheduled Days Per Week|"]

IF CONDITION{Work_Site_Name != '' and not(matches(Work_Site_Name, $varRegexWorkSite))} VALUE=["Invalid Work Site Name|"]

IF CONDITION{ Organization_Unit != '' and not(matches(Organization_Unit, $varRegexOrganizationUnit ))} VALUE=["Invalid Organization Unit|"]

IF CONDITION{Key_Employee_Indicator != '' and not(matches(Key_Employee_Indicator,$varRegexKeyEmployeeIndicator ))} VALUE=["Invalid Key Employee Indicator|"]

IF CONDITION{Field_50_In_75_Rule_Indicator != '' and not(matches(Field_50_In_75_Rule_Indicator, $varRegexField50In75RuleIndicator ))} VALUE=["Invalid 50 In 75 Rule Indicator|"]

IF CONDITION{Hours_Worked_Previous_12_Months != '' and not(matches(Hours_Worked_Previous_12_Months, $varRegexHoursWorkedPrevious12Months))} VALUE=["Invalid Hours Worked Previous 12 Months|"]

IF CONDITION{Occupation_Qualifier1 != '' and not(matches(Occupation_Qualifier1,$varRegexOccupationQualifier1))} VALUE=["Invalid Occupation Qualifier1|"]

IF CONDITION{Employer_Contact_1 != '' and not(matches(Employer_Contact_1, $varRegexEmployerContact1))} VALUE=["Invalid Employer Contact1|"]

IF CONDITION{Employer_Contact_2 != '' and not(matches(Employer_Contact_2, $varRegexEmployerContact2))} VALUE=["Invalid Employer Contact2|"]

IF CONDITION{Reporting_Field1 != '' and not(matches(Reporting_Field1, $varRegexReportingField1))} VALUE=["Invalid Reporting Field1|"]

IF CONDITION{Reporting_Field2 != '' and not(matches(Reporting_Field2, $varRegexReportingField2))} VALUE=["Invalid Reporting Field2|"]

IF CONDITION{Primary_Med_Prem_Pre_Tax != '' and not(matches(Primary_Med_Prem_Pre_Tax, $varRegexPrimaryMedPremPreTax ))} VALUE=["Invalid Primary Med Prem Pre Tax|"]

IF CONDITION{Primary_Life_Ins_Prem_Post_Tax != '' and not(matches(Primary_Life_Ins_Prem_Post_Tax, $varRegexPrimaryLifeInsPremPostTax))} VALUE=["Invalid Primary Life Ins Prem Post Tax|"]

IF CONDITION{Spouse_Life_Ins_Prem_Post_Tax != '' and not(matches(Spouse_Life_Ins_Prem_Post_Tax, $varRegexSpouseLifeInsPremPostTax ))} VALUE=["Invalid Spouse Life Ins Prem Post Tax|"]

IF CONDITION{Dep_Life_Ins_Prem_Post_Tax!= '' and not(matches(Dep_Life_Ins_Prem_Post_Tax, $varRegexDepLifeInsPremPostTax))} VALUE=["Invalid Dep Life Ins Prem Post Tax|"]

IF CONDITION{STD_Prem_Pre_Tax!= '' and not(matches(STD_Prem_Pre_Tax, $varRegexSTDPremPreTax))} VALUE=["Invalid STD Prem Pre Tax|"]

IF CONDITION{LTD_Prem_Pre_Tax != '' and not(matches(LTD_Prem_Pre_Tax, $varRegexLTDPremPreTax))} VALUE=["Invalid LTD Prem Pre Tax|"]

IF CONDITION{Primary_Dental_Prem_Pre_Tax != '' and not(matches(Primary_Dental_Prem_Pre_Tax, $varRegexPrimaryDentalPremPreTax ))} VALUE=["Primary Dental Prem Pre Tax|"]

IF CONDITION{Primary_Critical_Illness_Prem_Post_Tax != '' and not(matches(Primary_Critical_Illness_Prem_Post_Tax, $varRegexPrimaryCriticalIllnessPremPostTax))} VALUE=["Invalid Primary Critical Illness Prem Post Tax|"]

IF CONDITION{Misc_1_Prem_Post_Tax__Identity_Protection_ != '' and not(matches(Misc_1_Prem_Post_Tax__Identity_Protection_, $varRegexMisc1PremPostTaxIdentityProtection))} VALUE=["Invalid Misc 1 Prem Post Tax Identity Protection|"]

IF CONDITION{Misc_2_Prem_Post_Tax__Legal_Services_ != '' and not(matches(Misc_2_Prem_Post_Tax__Legal_Services_,$varRegexMisc2PremPostTaxLegalServices))} VALUE=["Invalid Misc 2 Prem Post Tax Legal Services|"]

IF CONDITION{Primary_Vision_Prem_Pre_Tax != '' and not(matches(Primary_Vision_Prem_Pre_Tax, $varRegexPrimaryVisionPremPreTax ))} VALUE=["Invalid Primary Vision Prem Pre Tax|"]

IF CONDITION{Personal_Accident_Ins_Prem_Post_Tax != '' and not(matches(Personal_Accident_Ins_Prem_Post_Tax, $varRegexPersonalAccidentInsPremPostTax ))} VALUE=["Invalid Personal Accident Ins Prem Post Tax|"]

IF CONDITION{ Hospital_Indemnity_Prem_Post_Tax != '' and not(matches(Hospital_Indemnity_Prem_Post_Tax, $varRegexHospitalIndemnityPremPostTax ))} VALUE=["Invalid Hospital Indemnity Prem Post Tax|"]

Warning Conditions

Warning Conditions

IF CONDITION{($var_fetch_key_identifier = 'Employee SSN' and Employee_ID != '' ) and (saxonJavaMapperUtilityClass:replaceAll(Employee_ID , $varRegexCheckEmpID,'VALID') != 'VALID')} VALUE=[ "Key Identifier is Employee SSN and Employee ID is Invalid|"]

IF CONDITION{($var_fetch_key_identifier = 'Employee ID' and Employee_SSN != '') and (saxonJavaMapperUtilityClass:replaceAll(Employee_SSN, $varRegexSSN ,'VALID' ) != 'VALID')} VALUE=[ "Key Identifier is Employee ID and Employee SSN is Invalid|"]

IF CONDITION{Employee_Work_Email_Address = ''} VALUE=["Missing Work Email Address|" ]

IF CONDITION{Employee_Work_State = '' } VALUE=["Missing Work State of employee|"]

IF CONDITION{$varOriginalDate<0 } VALUE=['Original DOH > Most Recent DOH|']

IF CONDITION{Employee_Job_Title = '' } VALUE=["Missing Employee Job Title|"]

IF CONDITION{((Employment_Status = 'TE') or (Employment_Status = 'RT')) and (Termination_Date = '')} VALUE=["Missing Termination date based on Employment Status |" ]

IF CONDITION{Key_Employee_Indicator = ''} VALUE=["Missing Key Employee Indicator|" ]

IF CONDITION{Field_50_In_75_Rule_Indicator = ''} VALUE=["Missing 50 in 75 Rule Indicator|" ]

IF CONDITION{Hours_Worked_Previous_12_Months = ''} VALUE=["Missing Hours Worked Prev 12 Months|" ]

IF CONDITION{generate-id(.) != generate-id(key('First_Name_Key', concat(Employee_First_Name, '|',Employee_Last_Name)))[1]} VALUE=["Duplicate First Name and last Name | " ]
IF CONDITION{(preceding ::Employee_SSN = Employee_SSN) } VALUE=[ "Duplicate SSN|" ]
IF CONDITION{(preceding ::Employee_ID = Employee_ID) } VALUE=[ "Duplicate Employee ID|" ]

After the validation, Good Records are directly to Pass Percentage Mapping, and Bad Records are inserted into the Database.

Pass Percentage Mapping:

Pass Percentage Condtion

Pass Percentage Condtion
`WHEN CONDITION{ ($varTradingPartnerPassPercent >= $varConfiguredMergePassPercent and $varClientPassPercent >=$varConfiguredMergePassPercent) } VALUE=[ 'true'] OTHERWISE VALUE=['false']`

The Condition states: It checks whether the passing percentages for the trading partner and the client are greater than or equal to a configured minimum passing percentage. Here's a breakdown in simpler terms:

Trading Partner Pass Percentage ($varTradingPartnerPassPercent): This is the percentage of files or data from the trading partner that passed validation.
Client Pass Percentage ($varClientPassPercent): This is the percentage of files or data from the client that passed validation.
Configured Merge Pass Percentage ($varConfiguredMergePassPercent): This is the minimum passing percentage required for the merge process to proceed.
This condition checks if the trading partner and the client have passed enough files/data to meet a required threshold. If they have, the result is 'true'; if not, it's 'false'.

Once the Pass Percentage Conditions are met the flow moves forward with the merging of both the Trading Partner File & Client File data. Also, Orphan records are generated for both the Trading Partner File & Client File. For Example: Suppose If “SSN=1” on the source side it will check if “SSN=1” records exist in Trading Partner Records or Client Records. If the records exist in the Trading Partner file only then it will be an orphan record for the Trading Partner.

Orphan Records will be for only mandatory records not for all.

In the Merge Mapping at the destination side at the root level variables are defined.

Name: varSuccessRecordCount
Value: get-context( 'MergedRecordCount', '0')
Description: This condition is checking to see how many records have been merged. If there are no merged records found, it assumes the count is zero.
Name: varOrphanedClientRecordCount
Value: get-context( 'OrphanedClientRecordCount', '0')
Description: This condition checks how many orphaned records there are for a client. If no orphaned records are found, it assumes the count is zero.
Name: varOrphanedTradingPartnerRecordCount
Value: get-context( 'OrphanedTradingPartnerRecordCount', '0')
Description: This condition checks for how many orphaned records there are for a trading partner. If none are found, it assumes there are zero orphaned records.

After merged records are updated they are stored at the FTP location & Also an Email Notification will be shared.

If the Pass Percentage Conditions are not met it moves ahead with another flow which sends the Email Notification to the configured email ID stating to re-process the files.

Once the Good Records are merged and stored at an FTP Location, the Client doesn’t want two separate files for Orphan Records. They require merged records with File Descriptions.

On the destination side at the root level, two child records are created Record & Record1

Once the Orphan Records are merged we pass them through the layout and keep it on the FTP Location.

In the Flow, all the records are generated Merged Records & Orphan Records are processed and updated in the Database. Also, Cleanups are initiated which performs cleanup for the Trading Partner File & Client File from the In-progress Folder.

Once all the processes are performed at the end final Email Notification is shared.