Best Practice | Oracle ADF BC 'q' Parameter

Introduction

When querying data, finders can be a useful tool. However, finders are pre-built tools created by developers, and while they offer excellent user-friendliness, they can have limitations. Oracle ADF BC framework offers an alternative called query 'q' parameter. The 'q' parameter can be used standalone or by combining with a finder to deliver maximum value.

When to use

Let's consider the "pyemployee" endpoint. This endpoint provides access to finders that query data based "auditDate", "compCode," "EmpTermDate," "EmpType," "EmpNo," "EmpPrn," and "EmpStatus." However, there other queryable fields you can use to search for data beyond those available in the finders.

For example, the HR Manager may want to search for employees in FLSA non-exempt who are single. The existing finders do not offer queriable fields like marital status nor the FLSA type. However, EmpMaritalStatus and EmpFlsaType are two queryable fields in the employee endpoint. In this case, the below 'q' parameter can be utilized. This query will return active hourly employees with marital status single who are in the weekly payroll and exempt from overtime.

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?finder=PyEmployeeFinder;EmpPrnVar=WK,EmpStatusVar=A,EmpTermDate=,EmpTypeVar=H&q=EmpMaritalStatus='S' and EmpFlsaType='N'

Version 1

Supported Operators

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpBillingRate>20.00

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpAnnualSalary<2004

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpChargeOutRate>=38.87 

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpTravelDistance<=5000

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpYearWorkingDays<300 and EmpYearWorkingDays>100

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpYearWorkingHours>1800 or EmpYearWorkingHours<=40

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpNo=1004

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpFirstName like 'Andrew%

Version 2

Version 2 of the 'q' parameter expands on the filtering options under Version 1, with optional operators syntaxes: and , or , like , not like , between, along with other combined parameters.

Version 2 is accessible by adding "REST-Framework-Version" header to the API request.

CMiC will support defaulting the Version 2 header on Patch 20. Version 1 will continue to be supported by hard coding the REST-Framework-Version header request value to 1.

Supported Operators

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee/hcm-rest-api/rest/1/pyemployee?q=EmpReportsToName='Larson Andrew'

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpHireDate='2012-01-01' and EmpStateCode='PA'

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpReportsToName like 'Larson Andrew'

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpBillingRate<25.00 or EmpChargeOutRate<33.00

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpIuCreateDatetime between '2022-03-18' and '2022-03-22'

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpHireDate!='2012-01-01'

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpReportsToName like 'Larson Andrew' and EmpHomeDeptCode=133

https://{add CMiC domain URL}/{add CMiC server}/hcm-rest-api/rest/1/pyemployee?q=EmpCompCode in ('001', '002')

Notable change from Version 1 to 2

Further information can be found by visiting Oracle ADF BC Framework Query Syntax.