Backend Service For Fetch Good and Error Record

Design Process flow
Create REST Provider End-point
Publish the Provider (REST URL) at UI

Usecase Overview

Backend Services to fetch data (GOOD Record, Error Record, pass percentage, File status, Error description, phase v3, phase EBMR) for File Queue with pagination support and Solution table.

1. Design Process flow

Process flow:

This process flow consists of three distinct flows, each with separate functionalities. It includes a backend query that interacts with the database to fetch all relevant information as requested by the user.

a. Default Flow: It fetches both errors and successful records, as illustrated in the screenshot below.

Process flow Activity:

Source Activity - The source comprises a database. It is necessary to create DbInfo and DbLayout to be invoked in the source activity

Go to edit query and to write tables joining query.

You must utilize the EDIT Query option to efficiently retrieve attributes from multiple tables and display relevant information on the user interface. Additionally, you implement where clauses conditions to filter the data as needed.

Query Details:

Table Name: DB/application_dataORDERBYFILE_QUEUE_IDDESCLIMIT25OFFSETstart/

SQL Query

SELECT ID,FILE_QUEUE_ID,APPLICATION_TYPE,RECORD_STATUS,ERROR_MESSAGE,WARNING_MESSAGE,SSN,EMPLOYEE_ID,EMPLOYEE_FIRST_NAME,EMPLOYEE_LAST_NAME,COVERAGE_ID,PRODUCT_TYPE_CODE,GROUP_POLICY_NUMBER,BENEFIT_PLAN_IDENTIFIER,CREATED_BY,CREATED_DATE,LAST_MODIFIED_BY,LAST_MODIFIED_DATE,BENEFIT_CLASS_IDENTIFIER,MASTER_AGREEMENT_NUMBER,EMPLOYEE_MIDDLE_NAME,EMPLOYEE_SUFFIX_CODE,EMPLOYEE_BIRTH_DATE,EMPLOYEE_GENDER_CODE,EMPLOYEE_MARITAL_STATUS_CODE,EMPLOYEE_HOME_PHONE,EMPLOYEE_EMAIL,EMPLOYEE_ALTERNATE_EMAIL,EMPLOYEE_MAILING_ADDRESS_FIRST_LINE_ADDRESS,EMPLOYEE_MAILING_ADDRESS_THIRD_LINE_ADDRESS,EMPLOYEE_MAILING_ADDRESS_CITY_NAME,EMPLOYEE_MAILING_ADDRESS_STATE_PROVINCE_CODE,EMPLOYEE_MAILING_ADDRESS_POSTAL_CODE,EMPLOYEE_MAILING_ADDRESS_COUNTRY_CODE,EMPLOYEE_IDENTIFIER,EMPLOYEE_TOBACCO_USECODE,EMPLOYEE_WORK_ADDRESS_STATE_PROVINCE_CODE,EMPLOYMENT_INFORMATION_EMPLOYMENT_STATUS_CODE,EMPLOYMENT_INFORMATION_EMPLOYMENT_TYPE_CODE,EMPLOYMENT_INFORMATION_ORIGINAL_HIRE_DATE,EMPLOYMENT_INFORMATION_MOST_RECENT_HIRE_DATE,EMPLOYMENT_INFORMATION_ADJUSTED_SERVICE_DATE,EMPLOYMENT_INFORMATION_TERMINATION_DATE,EMPLOYMENT_INFORMATION_JOB_TITLE_TEXT,EMPLOYMENT_INFORMATION_OCCUPATION_TEXT,EMPLOYMENT_INFORMATION_PAYROLL_FREQUENCY_QUANTITY,EMPLOYMENT_INFORMATION_EXEMPT_CODE,EMPLOYMENT_INFORMATION_UNION_INDICATOR,EMPLOYMENT_INFORMATION_WORK_LOCATION_TEXT,EMPLOYMENT_INFORMATION_WORK_STATE_CODE,COVERAGE_BENEFIT_SUBCLASS_IDENTIFIER,COVERAGE_COVERAGERIDER_RIDEROPTION_IDENTIFIER,COVERAGE_BILL_GROUP_IDENTIFIER,COVERAGE_BILL_SUB_GROUP_IDENTIFIER,COVERAGE_TIER_CODE,COVERAGE_EFFECTIVE_DATE,COVERAGE_TERMINATION_DATE,COVERAGE_BENEFIT_AMOUNT,DEPENDENT_TOBACCO_USECODE,DEPENDENT_FIRST_NAME,DEPENDENT_LAST_NAME,DEPENDENT_BIRTH_DATE,DEPENDENT_GENDER_CODE,DEPENDENT_RELATIONSHIP_TYPE_CODE,DEPENDENT_BENEFIT_AMOUNT,EVENT_DATE,EVENT_TYPE_REASON_CODE,EVENT_TYPE_CODE,EMPLOYEE_POSITION,POLICY_PRODUCTID_CLASS_POSITION FROM application_data

Where Clause

WHERE CREATED_DATE BETWEEN '$$start_date$$' AND DATE_ADD('$$end_date$$', INTERVAL 1 DAY) AND FILE_QUEUE_ID =$$fileQueueId$$ ORDER BY FILE_QUEUE_ID DESC

Target Layout -

Mapping - You must load the database schema and the target schema (JSON) to finalize the mapping process.

Context Target Using ContextTarget to store data in the flow which will be carried by the REST provider.

b. Process Flow

Process flow activity:

Source Activity - The source comprises a database. It is necessary to create DbInfo and DbLayout to be invoked in the source activity