E-QR: PSQuery With Field Properties "Current Date" Throws The Error "Illegal usage of parenthesis in criteria! (30,12)" (Doc ID 2674002.1)

Last updated on JULY 27, 2022

Applies to:

Symptoms

When attempting to create a PS Query using the "Current Date" option in field properties, the following error occurs:

The issue can be reproduced at will with the following steps:

1. Login to PIA.
2. Navigate to Reporting Tools > Query > Query Manager.
3. Create a query.
4. Select the fields.
5. Under the Fields Tab, click Edit for one field.
6. In "Edit Fields Properties" window, select "Current Date" option, for example A.CHANGE_STATUS field in the below sample SQL.
7. Click "OK" button.
4. When saving the changes, the error will occur.

Sample SQL from Query Manager:


SELECT A.BUSINESS_UNIT, A.PO_ID, B.PO_STATUS, A.CHANGE_STATUS, TO_CHAR(B.ENTERED_DT,'YYYY-MM-DD'), TO_CHAR(B.APPROVAL_DT,'YYYY-MM-DD'), B.OPRID_APPROVED_BY, TO_CHAR(B.PO_DT,'YYYY-MM-DD'), B.BUYER_ID, B.VENDOR_ID, D.VNDR_NAME_SHRT_USR, A.AMT_ONLY_FLG, B.PO_REF, B.OPRID_MODIFIED_BY, TO_CHAR(CAST((B.LAST_DTTM_UPDATE) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'), A.UNIT_OF_MEASURE, TO_CHAR(B.PO_DT,'YYYY-MM-DD'), TO_CHAR(A.CUSTOM_DATE_B,'YYYY-MM-DD')
  FROM PS_PO_LINE_FS A, PS_PO_HDR_FS B, PS_PO_LINE_SHIP C, PS_VENDOR D
  WHERE ( A.BUSINESS_UNIT = B.BUSINESS_UNIT
  AND A.PO_ID = B.PO_ID
  AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
  AND B.PO_ID = C.PO_ID
  AND A.LINE_NBR = C.LINE_NBR
  AND A.AMT_ONLY_FLG = 'Y'
  AND B.PO_DT > TO_DATE('2013-01-01','YYYY-MM-DD')
  AND D.SETID = B.VENDOR_SETID
  AND D.VENDOR_ID = B.VENDOR_ID
  AND B.PO_STATUS NOT IN ('PX','X','C','DA')
  AND A.BUSINESS_UNIT = :2)
  ORDER BY 1, 2

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.