/
REST Services for Data Retrieval like Good Record, Error Record, and file status

REST Services for Data Retrieval like Good Record, Error Record, and file status

Owned by Deepak Kumar (Deactivated)
Last updated: Jul 22, 2024
11 min read

Usecase

This use case outlines the implementation of REST services to retrieve detailed data about file processing statutes and error handling within a system. The services are designed to provide specific metrics and information crucial for monitoring and managing file queues efficiently.

Actors

  • User

  • Backend Server

  • Database (Solution DB)

  • UI (Web Form)

Preconditions

  • Data related to Good Records, Error Records, Pass Percentage, File Status, Error Description, Phase V3, and Phase EBMR are stored in the Solution DB.

  • RESTful API is set up on the backend server.

  • UI supports interaction with the REST services for data retrieval.

Postconditions

  • Users can retrieve the specified data directly from the UI.

  • The requested data is retrieved from the database and provided to the user with pagination support.

1. Design Process flow

Process flow:

This process flow comprises three distinct flows, each serving specific functionalities. It involves a backend query that communicates with the database to retrieve all pertinent information as per the user's request.

This flow contains the following functionality:

  • First flow – It enables the advanced search option like

  • Default flow - It shows the last 30 days' records like errors, status, warnings, and good records

  • Last flow – It enables a search option with an emp name or SSN

image-20240722-093913.png

Once the flow has been designed, it must be published for the REST provider.

image-20240621-060942.png

User Interface:

a. Default Flow: To fetch complete status

Process Overview

The Process flow is designed to view Application Data, allowing users to fetch complete statuses like good record records, error records, warnings, etc. from the User Interface (UI). This flow functions as a backend service for the REST provider API. When a user triggers the API, this flow is invoked to handle the request.

 

User Interface

Process Steps

  1. User Interface (UI) Request:

    • A user interacts with the UI and triggers a request to view application data.

    • The UI sends a request to the backend service via the REST API.

  2. API Endpoint Invocation:

    • The REST provider API receives the request and routes it to the appropriate endpoint.

    • The endpoint is configured to handle specific data queries and statuses (good records, error records, warnings, etc.).

  3. Backend Service Initialization:

    • The API endpoint invokes the backend service responsible for processing the request.

    • The backend service starts by validating the incoming request parameters.

  4. Data Retrieval:

    • The backend service queries the database or data storage to fetch the requested application data.

    • It retrieves data based on the requested criteria (e.g., all records, good records, error records, warnings).

  5. Data Processing:

    • The backend service processes the retrieved data to format it according to the requirements.

    • It filters and categorizes the data into different statuses (good records, error records, warnings).

  6. Response Preparation:

    • The processed data is then packaged into a structured response.

    • The response includes all the necessary details and statuses requested by the user.

  7. Sending the Response:

    • The backend service sends the prepared response back to the REST provider API.

    • The API then forwards the response to the UI.

  8. UI Displays Data:

    • The UI receives the response and parses the data.

    • It displays the data to the user, showing complete statuses like good records, error records, warnings, etc.

Process flow Activity:

  1. 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/

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 CREATED_DATE BETWEEN '$$start_date$$' AND DATE_ADD('$$end_date$$', INTERVAL 1 DAY) AND FILE_QUEUE_ID =$$fileQueueId$$ ORDER BY FILE_QUEUE_ID DESC

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 CREATED_DATE BETWEEN '$$start_date$$' AND DATE_ADD('$$end_date$$', INTERVAL 1 DAY) AND FILE_QUEUE_ID =$$fileQueueId$$ ORDER BY FILE_QUEUE_ID DESC

  1. Target Layout - The JSON schema for the source data (database) will be designed to be utilized in the RESTful API for the user interface. JSON is selected for its compatibility with RESTful APIs.

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

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

Only utilize the restResponse variable and ContentTarget parameter name to handle the data for output.

b. Advanced Search Flow - To Search Start Date and End Date

This process flow facilitates advanced search options for specific application data within the system. The advanced search functionality allows users to input Start Date and End Date as search parameters, which triggers interactions between the front-end and back-end systems. Subsequently, the filtered results are displayed to the user for review.

Process Steps

  1. User Interface Initialization:

    • The user accesses the search interface of the application.

  1. Input Parameters:

    • The user is presented with input fields for Start Date and End Date.

    • The user enters the desired Start Date and End Date to define the search parameters.

  2. Input Validation:

    • The system validates the input dates to ensure they are in the correct format and logical order (e.g., Start Date is not after End Date).

    • If the validation fails, the system prompts the user to correct the input.

  3. Search Trigger:

    • Once the input dates are validated, the user initiates the search by clicking a 'Search' button.

  4. Front-End Processing:

    • The front end captures the input parameters and prepares an API request to the back-end system.

    • The API request includes the Start Date and End Date as query parameters.

  5. Back-End Request Handling:

    • The back-end system receives the API request.

    • It processes the request by querying the database for records that fall within the specified date range.

  6. Data Filtering:

    • The back-end system applies the date filters to the application data.

    • The system retrieves the relevant records that match the search criteria.

  7. Response Construction:

    • The back-end system constructs a response containing the filtered data.

    • The response is sent back to the front-end system.

  8. Data Display:

    • The front-end system receives the response from the back-end.

    • It processes the response and displays the filtered results to the user in a user-friendly format.

Process flow activity:

  1. Gateway Condition: This condition enables the retrieval of a value by utilizing user-defined search criteria within the UI's advanced search feature. The code streamlines the process flow in alignment with the designated start_date.

Code Description:

Code

Description

Code

Description

String Query = context.get("start_date") == null ? "" : context.get("start_date");

if(Query!=null&&Query.length()>0)
{
return true;

}
else{
return false;
}

The code checks whether the start_date retrieved from the context is valid, meaning it is not null and not an empty string. If the start_date is valid, it returns true; otherwise, it returns false. This can be used to validate the presence of a start_date value before performing further actions.

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

 

Query Details:

Table Name: DB/application_dataORDERBYFILE_QUEUE_IDDESCLIMIT25OFFSETstart/

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

  1. Target Layout - The JSON schema for the source data (database) will be designed to be utilized in the RESTful API for the user interface. JSON is selected for its compatibility with RESTful APIs.

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

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

c. Process flow - To search By File Attribute.

This flow process enables search options by file attribute like FILE_QUEUE_ID, SSN, EMPLOYEE_LAST_NAME, GROUP_POLICY_NUMBER, COVERAGE_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_ID & RECORD_STATUS. 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.

  1. User Initiates Search:

  • User Interface: The user accesses a search interface on the front end. This interface provides input fields for each of the file attributes mentioned.

  • Input: The user enters search criteria in one or more fields (e.g., SSN, EMPLOYEE_LAST_NAME, etc.).

  1. Front End Validation:

  • Form Validation: The front end performs basic validation to ensure the inputs are in the correct format (e.g., SSN is numeric and of the correct length, EMPLOYEE_LAST_NAME is alphabetic, etc.).

    • Feedback: If any inputs are invalid, the user is prompted to correct them before proceeding.

  1. Search Request Submission:

  • Request Construction: The front end constructs a search request based on the input fields filled by the user. This request typically includes a payload with the search criteria.

    • API Call: The front end sends the search request to the back end via an API call.

  1. Backend Processing:

  • Request Reception: The back end receives the search request.

    • Validation and Sanitization: The back end further validates and sanitizes the input to ensure security and integrity.

    • Database Query: The back end constructs and executes a query against the database based on the search criteria

  1. Data Retrieval and Filtering:

  • Query Execution: The database executes the query and returns the results to the back end.

    • Filtering: The back end may apply additional filtering or business logic to the results if necessary.

  1. Front End Display:

  • Response Reception: The front end receives the response.

    • Result Rendering: The front end processes the response data and displays the filtered results to the user in a user-friendly format (e.g., a table or list).

    • Feedback: If no results are found, the front end provides appropriate feedback to the user (e.g., "No records found matching the search criteria").

Process flow activity:

 

  1. Gateway Activity: This condition retrieves a value based on the search criteria provided by the user interface, using the advanced search option. The code facilitates the flow to retrieve data according to the specified search query.

Code Description:

Gateway Code

Description

Gateway Code

Description

String Query = context.get("searchQuery") == null ? "" : context.get("searchQuery");

if(Query!=null&&Query.length()>0)
{
return true;

}
else{
return false;
}

The code is designed to determine whether the searchQuery retrieved from the context is valid, meaning it is not null and not an empty string. If searchQuery is valid, it returns true; otherwise, it returns false. This could be used to validate that a search query has been provided before proceeding with further operations.

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

 

Table Name: DB/application_dataORDERBYFILE_QUEUE_IDDESCLIMIT25OFFSETstart/

  1. Target Layout - The JSON schema for the source data (database) will be designed to be utilized in the RESTful API for the user interface. JSON is selected for its compatibility with RESTful APIs.

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

 

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

2. Create REST Provider End-point

In this particular use case, the process flow is shared by developing a REST provider that will eventually be integrated with the user interface.

Follow the steps below to create a REST Provider:

  1. Click Configure > WEB SERVICES > REST Provider.

  2. Click Create REST Provider. 

  3. In the Create REST Provider window, type the name and description of the new Web Service Provider service in the Name and Description fields respectively.

  4. Type API Version, for example, your API version can be 1

  1. Type the resource end path URL in the Resource End Path field. The URL can be static or dynamic. Static URL example:  /get/File 

  2. Select the process flow name to trigger with the web service from the Process Flow Name field. example: Pf_Attachment

    1. Set parameterName attribute of the context source should be restRequest

      1. fileQueueId-This process will generate a unique File ID for each file.

      2. fileType - Type of file downloading such as Input, Output, and Report files.

  1. Click Add Method and select GET as the method type.

 

Select the Response Schema (For example, we are opting for the schema that has been utilized in the process flow as the target schema)

  1. Save it.

3. Publish the Provider (REST URL) at UI

Once the above steps have been completed, it is necessary to link the REST provider URL with the user interface (UI) to present the data on the UI

 

Related content

REST Services for Downloading Input, Output, Dropped Record, Error, and Duplicate Record Files from Database
REST Services for Downloading Input, Output, Dropped Record, Error, and Duplicate Record Files from Database
Adeptia Implementation Service Knowledge Base
More like this
REST Service for File Search with Advanced Options and Pagination
REST Service for File Search with Advanced Options and Pagination
Adeptia Implementation Service Knowledge Base
More like this
SIC: Overview of Adeptia Rest Services Creation for GUI
SIC: Overview of Adeptia Rest Services Creation for GUI
Adeptia Implementation Service Knowledge Base
More like this
DIODE: Introduction to EDI X12 and EDIFACT
DIODE: Introduction to EDI X12 and EDIFACT
Adeptia Implementation Service Knowledge Base
Read with this
Frequently Ask Question
Frequently Ask Question
Adeptia Implementation Service Knowledge Base
More like this
Getting Started with REST Service Data Retrieval
Getting Started with REST Service Data Retrieval
Adeptia Implementation Service Knowledge Base
More like this