error 1
0
AND (
:p_badge IS NULL
OR :p_badge = ''
OR zre.EXTN_ATTRIBUTE_CHAR002 IN (
SELECT TRIM(REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_INC_TYPE IS NULL
OR :P_INC_TYPE = ''
OR zre.EXTN_ATTRIBUTE_CHAR022 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAY_PERIOD1_0,:P_PAY_PERIOD1_1 IS NULL
OR :P_PAY_PERIOD2_0,:P_PAY_PERIOD2_1 = ''
OR zre.EXTN_ATTRIBUTE_NUMBER001 IN (
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(:P_PAY_PERIOD3_0,:P_PAY_PERIOD3_1,'[^,]+',1,LEVEL)))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAY_PERIOD4_0,:P_PAY_PERIOD4_1,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_MPA_STAT IS NULL
OR :P_MPA_STAT = ''
OR zre.EXTN_ATTRIBUTE_CHAR029 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAYBATCH IS NULL
OR :P_PAYBATCH = ''
OR zre.EXTN_ATTRIBUTE_CHAR011 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL) IS NOT NULL
)
)
UNION ALL
SELECT
record_number || '-' || 'MPA' AS RecordNumber,
record_number || '-' || 'MPA' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
EXTN_ATTRIBUTE_CHAR029 AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
EXTN_ATTRIBUTE_CHAR013 AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
NVL(EXTN_ATTRIBUTE_NUMBER023, 0) AS AmountInLocalCurrency_c,
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c,
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id
FROM fun_all_business_units_v
WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
EXTN_ATTRIBUTE_CHAR023 AS ReasonCodeLOV_c,
EXTN_ATTRIBUTE_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER023, 0) <> 0
AND (
:p_badge IS NULL
OR :p_badge = ''
OR zre.EXTN_ATTRIBUTE_CHAR002 IN (
SELECT TRIM(REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_INC_TYPE IS NULL
OR :P_INC_TYPE = ''
OR zre.EXTN_ATTRIBUTE_CHAR022 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAY_PERIOD5_0,:P_PAY_PERIOD5_1 IS NULL
OR :P_PAY_PERIOD6_0,:P_PAY_PERIOD6_1 = ''
OR zre.EXTN_ATTRIBUTE_NUMBER001 IN (
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(:P_PAY_PERIOD7_0,:P_PAY_PERIOD7_1,'[^,]+',1,LEVEL)))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAY_PERIOD8_0,:P_PAY_PERIOD8_1,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_MPA_STAT IS NULL
OR :P_MPA_STAT = ''
OR zre.EXTN_ATTRIBUTE_CHAR029 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAYBATCH IS NULL
OR :P_PAYBATCH = ''
OR zre.EXTN_ATTRIBUTE_CHAR011 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL) IS NOT NULL
)
)
SQL Query Timeout: 500
oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
https://docs.oracle.com/error-help/db/ora-00920/
at oracle.xdo.dataengine.diagnostic.Xplan.execute(Xplan.java:292)
at oracle.xdo.dataengine.diagnostic.Xplan.processSQLDataSource(Xplan.java:245)
at oracle.xdo.dataengine.diagnostic.Xplan.generate(Xplan.java:117)
at oracle.xdo.dataengine.XMLPGEN.processMergedDataSet(XMLPGEN.java:2061)
at oracle.xdo.dataengine.XMLPGEN.processMergedDataSet(XMLPGEN.java:3936)
at oracle.xdo.dataengine.DataProcessor.processData(DataProcessor.java:386)
at oracle.xdo.servlet.dataengine.DataProcessorImpl.processData(DataProcessorImpl.java:311)
at oracle.xdo.servlet.dataengine.DataProcessorImpl.render(DataProcessorImpl.java:688)
at oracle.xdo.servlet.ReportModelContextImpl.getReportXMLData(ReportModelContextImpl.java:448)
at oracle.xdo.servlet.CoreProcessor.getReportXMLData(CoreProcessor.java:2324)
at oracle.xdo.servlet.CoreProcessor.process(CoreProcessor.java:634)
at oracle.xdo.servlet.CoreProcessor.processRequest(CoreProcessor.java:299)
at oracle.xdo.servlet.CoreProcessor.generateDocument(CoreProcessor.java:171)
at oracle.xdo.servlet.ReportImpl.renderBodyHTTP(ReportImpl.java:1719)
at oracle.xdo.servlet.ReportImpl.renderReportBodyHTTP(ReportImpl.java:806)
at oracle.xdo.servlet.ReportImpl.renderReportBodyHTTP(ReportImpl.java:252)
at oracle.xdo.servlet.resources.ReportItemServiceImpl$ReportItemRunner.call(ReportItemServiceImpl.java:117)
at oracle.xdo.servlet.resources.ReportItemServiceImpl$ReportItemRunner.call(ReportItemServiceImpl.java:82)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:842)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
https://docs.oracle.com/error-help/db/ora-00920/
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:715)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:615)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1372)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:972)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:237)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:524)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:298)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1510)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:2020)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1633)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3973)
at oracle.jdbc.driver.OraclePreparedStatement.doExecute(OraclePreparedStatement.java:4447)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4433)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1032)
at weblogic.jdbc.wrapper.PreparedStatement.execute(PreparedStatement.java:119)
at oracle.xdo.dataengine.diagnostic.Xplan.execute(Xplan.java:272)
... 21 more
Caused by: Error : 920, Position : 1749, SQL = EXPLAIN PLAN SET STATEMENT_ID = 'dm_plan_newquery_260219_071810' FOR
SELECT
record_number || '-' || 'MC' AS RecordNumber,
record_number || '-' || 'MC' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
'Auto Approved' AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
'N' AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
EXTN_ATTRIBUTE_NUMBER037 AS AmountInLocalCurrency_c,
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c,
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id
FROM fun_all_business_units_v
WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
'MAV_DIR_MPA_CODE_1012' AS ReasonCodeLOV_c,
EXTN_ATTRIBUTE_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER037, 0) <> 0
AND (
:p_badge IS NULL
OR :p_badge = ''
OR zre.EXTN_ATTRIBUTE_CHAR002 IN (
SELECT TRIM(REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_INC_TYPE IS NULL
OR :P_INC_TYPE = ''
OR zre.EXTN_ATTRIBUTE_CHAR022 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAY_PERIOD1_0,:P_PAY_PERIOD1_1 IS NULL
OR :P_PAY_PERIOD2_0,:P_PAY_PERIOD2_1 = ''
OR zre.EXTN_ATTRIBUTE_NUMBER001 IN (
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(:P_PAY_PERIOD3_0,:P_PAY_PERIOD3_1,'[^,]+',1,LEVEL)))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAY_PERIOD4_0,:P_PAY_PERIOD4_1,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_MPA_STAT IS NULL
OR :P_MPA_STAT = ''
OR zre.EXTN_ATTRIBUTE_CHAR029 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAYBATCH IS NULL
OR :P_PAYBATCH = ''
OR zre.EXTN_ATTRIBUTE_CHAR011 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL) IS NOT NULL
)
)
UNION ALL
SELECT
record_number || '-' || 'MPA' AS RecordNumber,
record_number || '-' || 'MPA' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
EXTN_ATTRIBUTE_CHAR029 AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
EXTN_ATTRIBUTE_CHAR013 AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
NVL(EXTN_ATTRIBUTE_NUMBER023, 0) AS AmountInLocalCurrency_c,
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c,
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id
FROM fun_all_business_units_v
WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
EXTN_ATTRIBUTE_CHAR023 AS ReasonCodeLOV_c,
EXTN_ATTRIBUTE_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER023, 0) <> 0
AND (
:p_badge IS NULL
OR :p_badge = ''
OR zre.EXTN_ATTRIBUTE_CHAR002 IN (
SELECT TRIM(REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_INC_TYPE IS NULL
OR :P_INC_TYPE = ''
OR zre.EXTN_ATTRIBUTE_CHAR022 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAY_PERIOD5_0,:P_PAY_PERIOD5_1 IS NULL
OR :P_PAY_PERIOD6_0,:P_PAY_PERIOD6_1 = ''
OR zre.EXTN_ATTRIBUTE_NUMBER001 IN (
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(:P_PAY_PERIOD7_0,:P_PAY_PERIOD7_1,'[^,]+',1,LEVEL)))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAY_PERIOD8_0,:P_PAY_PERIOD8_1,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_MPA_STAT IS NULL
OR :P_MPA_STAT = ''
OR zre.EXTN_ATTRIBUTE_CHAR029 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAYBATCH IS NULL
OR :P_PAYBATCH = ''
OR zre.EXTN_ATTRIBUTE_CHAR011 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL) IS NOT NULL
)
), Original SQL = EXPLAIN PLAN SET STATEMENT_ID = 'dm_plan_newquery_260219_071810' FOR
SELECT
record_number || '-' || 'MC' AS RecordNumber,
record_number || '-' || 'MC' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
'Auto Approved' AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
'N' AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
EXTN_ATTRIBUTE_NUMBER037 AS AmountInLocalCurrency_c,
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c,
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id
FROM fun_all_business_units_v
WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
'MAV_DIR_MPA_CODE_1012' AS ReasonCodeLOV_c,
EXTN_ATTRIBUTE_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER037, 0) <> 0
AND (
:p_badge IS NULL
OR :p_badge = ''
OR zre.EXTN_ATTRIBUTE_CHAR002 IN (
SELECT TRIM(REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_INC_TYPE IS NULL
OR :P_INC_TYPE = ''
OR zre.EXTN_ATTRIBUTE_CHAR022 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAY_PERIOD1_0,:P_PAY_PERIOD1_1 IS NULL
OR :P_PAY_PERIOD2_0,:P_PAY_PERIOD2_1 = ''
OR zre.EXTN_ATTRIBUTE_NUMBER001 IN (
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(:P_PAY_PERIOD3_0,:P_PAY_PERIOD3_1,'[^,]+',1,LEVEL)))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAY_PERIOD4_0,:P_PAY_PERIOD4_1,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_MPA_STAT IS NULL
OR :P_MPA_STAT = ''
OR zre.EXTN_ATTRIBUTE_CHAR029 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAYBATCH IS NULL
OR :P_PAYBATCH = ''
OR zre.EXTN_ATTRIBUTE_CHAR011 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL) IS NOT NULL
)
)
UNION ALL
SELECT
record_number || '-' || 'MPA' AS RecordNumber,
record_number || '-' || 'MPA' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
EXTN_ATTRIBUTE_CHAR029 AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
EXTN_ATTRIBUTE_CHAR013 AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
NVL(EXTN_ATTRIBUTE_NUMBER023, 0) AS AmountInLocalCurrency_c,
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c,
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id
FROM fun_all_business_units_v
WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
EXTN_ATTRIBUTE_CHAR023 AS ReasonCodeLOV_c,
EXTN_ATTRIBUTE_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER023, 0) <> 0
AND (
:p_badge IS NULL
OR :p_badge = ''
OR zre.EXTN_ATTRIBUTE_CHAR002 IN (
SELECT TRIM(REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:p_badge,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_INC_TYPE IS NULL
OR :P_INC_TYPE = ''
OR zre.EXTN_ATTRIBUTE_CHAR022 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_INC_TYPE,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAY_PERIOD5_0,:P_PAY_PERIOD5_1 IS NULL
OR :P_PAY_PERIOD6_0,:P_PAY_PERIOD6_1 = ''
OR zre.EXTN_ATTRIBUTE_NUMBER001 IN (
SELECT TO_NUMBER(TRIM(REGEXP_SUBSTR(:P_PAY_PERIOD7_0,:P_PAY_PERIOD7_1,'[^,]+',1,LEVEL)))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAY_PERIOD8_0,:P_PAY_PERIOD8_1,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_MPA_STAT IS NULL
OR :P_MPA_STAT = ''
OR zre.EXTN_ATTRIBUTE_CHAR029 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_MPA_STAT,'[^,]+',1,LEVEL) IS NOT NULL
)
)
AND (
:P_PAYBATCH IS NULL
OR :P_PAYBATCH = ''
OR zre.EXTN_ATTRIBUTE_CHAR011 IN (
SELECT TRIM(REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL))
FROM dual
CONNECT BY REGEXP_SUBSTR(:P_PAYBATCH,'[^,]+',1,LEVEL) IS NOT NULL
)
), Error Message = ORA-00920: invalid relational operator
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:723)
... 36 more
]]>
]]>
Comments
Post a Comment