Versions Compared

Version Old Version 15 New Version 16
Changes made by

Deepak Kumar (Deactivated)

Deepak Kumar (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

  1. Design Create a Solution DB and Tables

  2. Design Process flow

  3. Create REST Provider End-point

  4. Publish the Provider (REST URL) at UI

...

The tables in the Solution DB should be structured to efficiently support these queries. Here is an example schema:

...

  1. Creating a Solution Database using the following query:

...

titlefile_queue

...

be structured to efficiently support these queries. Here is an example schema:

...

  1. Creating a Solution Database using the following query:

Expand
titlefile_name_details

CREATE TABLE `file_name_details` (

  `ID` int NOT NULL AUTO_INCREMENT,

  `FILE_QUEUE_ID` int NOT NULL,

  `LOCATION_TYPE` varchar(50) NOT NULL,

  `FILE_TYPE` varchar(50) NOT NULL,

  `FILE_PATH` varchar(500) NOT NULL,

  `FILE_NAME` varchar(400) DEFAULT NULL,

  `CREATED_BY` varchar(50) NOT NULL,

  `CREATED_DATE` datetime NOT NULL,

  `LAST_MODIFIED_BY` varchar(50) NOT NULL,

  `LAST_MODIFIED_DATE` datetime NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE KEY `UC_FILE_ATT` (`FILE_QUEUE_ID`,`FILE_NAME`,`FILE_TYPE`,`LOCATION_TYPE`),

  CONSTRAINT `file_name_details_ibfk_1` FOREIGN KEY (`FILE_QUEUE_ID`) REFERENCES `file_queue` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=7676 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand
titleerror_code

CREATE TABLE `error_code` (

  `ID` int NOT NULL AUTO_INCREMENT,

  `ERROR_CODE` varchar(10) NOT NULL,

  `ERROR_MESSAGE` varchar(255) NOT NULL,

  `PHASE_ID` varchar(10) NOT NULL,

  PRIMARY KEY (`ID`),

  UNIQUE KEY `UC_ERROR_CD` (`ERROR_CODE`),

  KEY `PHASE_ID` (`PHASE_ID`),

  CONSTRAINT `error_code_ibfk_1` FOREIGN KEY (`PHASE_ID`) REFERENCES `phase` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=104 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand
titleapplication_data

CREATE TABLE `application_data` (

  `ID` int NOT NULL AUTO_INCREMENT,

  `FILE_QUEUE_ID` int NOT NULL,

  `APPLICATION_TYPE` varchar(50) DEFAULT NULL,

  `RECORD_STATUS` varchar(50) DEFAULT NULL,

  `ERROR_MESSAGE` varchar(2000) DEFAULT NULL,

  `WARNING_MESSAGE` varchar(2000) DEFAULT NULL,

  `SSN` varchar(20) DEFAULT NULL,

  `EMPLOYEE_ID` varchar(20) DEFAULT NULL,

  `EMPLOYEE_FIRST_NAME` varchar(200) DEFAULT NULL,

  `EMPLOYEE_LAST_NAME` varchar(200) DEFAULT NULL,

  `COVERAGE_ID` varchar(50) DEFAULT NULL,

  `PRODUCT_TYPE_CODE` varchar(50) DEFAULT NULL,

  `GROUP_POLICY_NUMBER` varchar(50) DEFAULT NULL,

  `BENEFIT_PLAN_IDENTIFIER` varchar(50) DEFAULT NULL,

  `CREATED_BY` varchar(50) NOT NULL,

  `CREATED_DATE` datetime NOT NULL,

  `LAST_MODIFIED_BY` varchar(50) NOT NULL,

  `LAST_MODIFIED_DATE` datetime NOT NULL,

  `BENEFIT_CLASS_IDENTIFIER` varchar(50) DEFAULT NULL,

  `MASTER_AGREEMENT_NUMBER` varchar(50) DEFAULT NULL,

  `EMPLOYEE_MIDDLE_NAME` varchar(50) DEFAULT NULL,

  `EMPLOYEE_SUFFIX_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_BIRTH_DATE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_GENDER_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_MARITAL_STATUS_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_HOME_PHONE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_EMAIL` varchar(50) DEFAULT NULL,

  `EMPLOYEE_ALTERNATE_EMAIL` varchar(50) DEFAULT NULL,

  `EMPLOYEE_MAILING_ADDRESS_FIRST_LINE_ADDRESS` varchar(100) DEFAULT NULL,

  `EMPLOYEE_MAILING_ADDRESS_THIRD_LINE_ADDRESS` varchar(100) DEFAULT NULL,

  `EMPLOYEE_MAILING_ADDRESS_CITY_NAME` varchar(50) DEFAULT NULL,

  `EMPLOYEE_MAILING_ADDRESS_STATE_PROVINCE_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_MAILING_ADDRESS_POSTAL_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_MAILING_ADDRESS_COUNTRY_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_IDENTIFIER` varchar(50) DEFAULT NULL,

  `EMPLOYEE_TOBACCO_USECODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_WORK_ADDRESS_STATE_PROVINCE_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_EMPLOYMENT_STATUS_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_EMPLOYMENT_TYPE_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_ORIGINAL_HIRE_DATE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_MOST_RECENT_HIRE_DATE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_ADJUSTED_SERVICE_DATE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_TERMINATION_DATE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_JOB_TITLE_TEXT` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_OCCUPATION_TEXT` varchar(400) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_PAYROLL_FREQUENCY_QUANTITY` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_EXEMPT_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_UNION_INDICATOR` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_WORK_LOCATION_TEXT` varchar(50) DEFAULT NULL,

  `EMPLOYMENT_INFORMATION_WORK_STATE_CODE` varchar(50) DEFAULT NULL,

  `COVERAGE_BENEFIT_SUBCLASS_IDENTIFIER` varchar(50) DEFAULT NULL,

  `COVERAGE_COVERAGERIDER_RIDEROPTION_IDENTIFIER` varchar(50) DEFAULT NULL,

  `COVERAGE_BILL_GROUP_IDENTIFIER` varchar(50) DEFAULT NULL,

  `COVERAGE_BILL_SUB_GROUP_IDENTIFIER` varchar(50) DEFAULT NULL,

  `COVERAGE_TIER_CODE` varchar(50) DEFAULT NULL,

  `COVERAGE_EFFECTIVE_DATE` varchar(50) DEFAULT NULL,

  `COVERAGE_TERMINATION_DATE` varchar(50) DEFAULT NULL,

  `COVERAGE_BENEFIT_AMOUNT` varchar(50) DEFAULT NULL,

  `DEPENDENT_TOBACCO_USECODE` varchar(50) DEFAULT NULL,

  `DEPENDENT_FIRST_NAME` varchar(50) DEFAULT NULL,

  `DEPENDENT_LAST_NAME` varchar(50) DEFAULT NULL,

  `DEPENDENT_BIRTH_DATE` varchar(50) DEFAULT NULL,

  `DEPENDENT_GENDER_CODE` varchar(50) DEFAULT NULL,

  `DEPENDENT_RELATIONSHIP_TYPE_CODE` varchar(50) DEFAULT NULL,

  `DEPENDENT_BENEFIT_AMOUNT` varchar(50) DEFAULT NULL,

  `EVENT_DATE` varchar(50) DEFAULT NULL,

  `EVENT_TYPE_REASON_CODE` varchar(50) DEFAULT NULL,

  `EVENT_TYPE_CODE` varchar(50) DEFAULT NULL,

  `EMPLOYEE_POSITION` varchar(50) DEFAULT NULL,

  `POLICY_PRODUCTID_CLASS_POSITION` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ID`),

  KEY `idx_file_queue_id` (`FILE_QUEUE_ID`),

  CONSTRAINT `fk_grade_id` FOREIGN KEY (`FILE_QUEUE_ID`) REFERENCES `file_queue` (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=426019 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand
titlephase

CREATE TABLE `phase` (

  `ID` varchar(10) NOT NULL,

  `PHASE_NAME` varchar(50) NOT NULL,

  `CREATED_BY` varchar(50) NOT NULL,

  `CREATED_DATE` datetime NOT NULL,

  `LAST_MODIFIED_BY` varchar(50) NOT NULL,

  `LAST_MODIFIED_DATE` datetime NOT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand
titlesolution_configuration

CREATE TABLE `solution_configuration` (

  `ID` int NOT NULL AUTO_INCREMENT,

  `CATEGORY` varchar(50) NOT NULL,

  `PROPERTY_NAME` varchar(50) NOT NULL,

  `PROPERTY_DEFAULT` varchar(50) DEFAULT NULL,

  `PROPERTY_VALUE` varchar(50) DEFAULT NULL,

  `PROPERTY_ORDER` int DEFAULT NULL,

  `ACTIVE_FLAG` varchar(10) NOT NULL,

  `CREATED_BY` varchar(50) NOT NULL,

  `CREATED_DATE` datetime NOT NULL,

  `LAST_MODIFIED_BY` varchar(50) NOT NULL,

  `LAST_MODIFIED_DATE` datetime NOT NULL,

  `TRANSACTION_NAME` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Expand
titletransaction_run_history

CREATE TABLE `transaction_run_history` (

  `ID` bigint NOT NULL AUTO_INCREMENT,

  `transactionId` varchar(100) NOT NULL,

  `transactionType` varchar(500) DEFAULT NULL,

  `transactionName` varchar(500) DEFAULT NULL,

  `partner` varchar(400) DEFAULT NULL,

  `inputFile` varchar(500) DEFAULT NULL,

  `inputFilePath` varchar(500) DEFAULT NULL,

  `sourceSystemOutputFile` varchar(500) DEFAULT NULL,

  `sourceSystemOutputFilePath` varchar(500) DEFAULT NULL,

  `adeptiaOutputFile` varchar(500) DEFAULT NULL,

  `adeptiaOutputFilePath` varchar(500) DEFAULT NULL,

  `deltaReportFile` varchar(500) DEFAULT NULL,

  `deltaReportFilePath` varchar(500) DEFAULT NULL,

  `status` varchar(100) DEFAULT NULL,

  `executedBy` varchar(500) DEFAULT NULL,

  `executionDate` varchar(50) DEFAULT NULL,

  `submittedBy` varchar(100) DEFAULT NULL,

  `matchPercent` varchar(50) DEFAULT NULL,

  `processInstanceID` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1705672808620 DEFAULT CHARSET=latin1;

...

  1. Context Target -The target will be ContextTarget to store data in the flow for the REST provider.

...

b.

...

Search Flow: Search by Filename & Transaction Id

This flow process is for searching specific file information within the system. It enables how the user initiates the search, the interactions between the front end and back end, and the final display of the filtered results to the user.

...

  • Action: The front end receives the response from the backend.

  • Display: The filtered search results are displayed to the user clearly and organized.

  • User Interface: The results can be displayed in a list, table, or any other suitable format, allowing the user to browse and analyze the information easily.

c.

...

Advanced Process Flow -

...

Filtering Records by (Start Date & End Date)

...

Overview

This Process flow is used for advanced search in which a user can filter records based on their start date and end date using the Advanced Search functionality. This includes the interactions between the frontend and backend systems to accomplish this task.

...