Create a Solution DB and Tables
Design Process flow
Create REST Provider End-point
Publish the Provider (REST URL) at UI
1
...
Overview
In this use case, data is stored in the Solution DB. The data retrieval involves writing a joining query to fetch the necessary information from the backend. The query will be based on the attributes required for the UI, such as File Phase, Error Description, Total Record Count, Unique Record Count, Duplicate Record Count, and File Status.
Database Structure
The tables in the Solution DB should be structured to efficiently support these queries. Here is an example schema:
...
Creating a Solution Database using the following query:
...
| title | file_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;
...
| title | error_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;
...
| title | application_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;
...
| title | phase |
|---|
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;
...
| title | solution_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;
...
| title | transaction_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;
...
. Design Process flow
Overview
When the file queue page is accessed, the backend server performs specific steps to ensure that all relevant file queue data is accurately retrieved and displayed on the front end. There are three primary flows, each with a distinct purpose. Once designed, these flows need to be published for the REST provider.
...
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.
...
3. Create REST Provider End-point
Overview
A REST (Representational State Transfer) Provider is a service that offers resources and functionalities over HTTP(S) using standard REST principles. It enables clients to interact with the service through predefined APIs, facilitating the integration and communication between different systems.
...