Backend Service For Fetch Good and Error Record

Description:

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.

Process flow:

a. Default 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

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 -