cancel
Showing results for 
Search instead for 
Did you mean: 

Query Error:

jaheer_hussain
Active Contributor
0 Kudos


Hi,

Below is the standard query.In this query I added one fllter filed 'Status'.Based on status(Draft,Executed,approved,Cancelled ..) I want to get my master agreement list. In the query I added this below line.<%AND_OP("T4.DISPLAY_NAME_ID",<%?(T4.DISPLAY_NAME_ID)%>)%>

Then when I try to execute the query  I am getting the following error.

SQL Exception: ORA-01722: invalid number , ANSI-92 SQL State: 42000, Vendor Error Code: 1722.

Can some body throw light on this issue,please?

SELECT <%RESULTS%>

FROM

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE

T4,

<%SCHEMA%>.FCI_CONTRACT T1

LEFT OUTER JOIN (SELECT

PARENT_OBJECT_ID, COUNT(*) AS AGR_COUNT FROM <%SCHEMA%>.FCI_AGREEMENT

GROUP BY PARENT_OBJECT_ID) T2 ON T1.OBJECTID = T2.PARENT_OBJECT_ID

LEFT OUTER

JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T3 ON T1.INT_CAT_OBJECT_ID =

T3.OBJECTID

LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract)%> T5 ON

T1.OBJECTID = T5.PARENT_OBJECT_ID

LEFT OUTER JOIN

<%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T6 ON T5.SPND_CAT_PROC_CONT_OBJECT_ID

= T6.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_CONTRACT T1 ON

T5.TOT_CONTRACT_VAL_IN_USD = T1.OBJECTID

LEFT OUTER JOIN

<%SCHEMA%>.FCI_CONTRACT T1 ON T5.COMP_CODE_OBJECT_ID =

T1.OBJECTID

<%DYN_JOIN%>

WHERE

T1.CONTEXTID

=<%CONTEXT(contracts.Contract)%>

AND T1.IS_TEMPLATE =0

AND

T1.STATUS_OBJECT_ID = T4.OBJECTID

AND T1.UNIQUE_DOC_NAME LIKE

'PMA%'

<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%>

<%AND_OP("UPPER(T1.VENDOR_OBJECT_NAME)",<%?(T1.VENDOR_OBJECT_NAME)%>)%>

<%AND_OP("T1.EXPIRATION_DATE_DATE",<%?(T1.EXPIRATION_DATE_DATE)%>)%>

<%AND_OP("T1.INT_CAT_OBJECT_ID",<%?(T1.INT_CAT_OBJECT_ID)%>)%>

<%AND_OP("UPPER(T1.DOC_OWNER_USER_OBJECT_NAME)",<%?(T1.DOC_OWNER_USER_OBJECT_NAME)%>)%>

<%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%AND_OP("T1.DOC_TYPE_OBJECT_ID",<%?(T1.DOC_TYPE_OBJECT_ID)%>)%>

<%AND_OP("T4.DISPLAY_NAME_ID",<%?(T4.DISPLAY_NAME_ID)%>)%>

<%DYN_FILTER%>

With Regards,

Jaheer

Accepted Solutions (1)

Accepted Solutions (1)

former_member207877
Active Participant
0 Kudos

Hi Jaheer,

Did your Issue resolved?

Please let me know if you need any assistance.

Thanks,

Raj.

jaheer_hussain
Active Contributor
0 Kudos

Hi  Raj,

yes.

Again I want to add purchasing group and purchasing organization in the result using FCI_COntract.

With Regards,

Jaheer

former_member207877
Active Participant
0 Kudos

Hi Jaheer,

T is  master Agreement Table FCI_CONTRACT

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_PORG T1
ON T1.OBJECTID= T.PURCHASING_ORG_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_PGROUP T2
ON T1.OBJECTID= T.PURCHASING_GRP_OBJECT_ID

For Purchasing Group, result field you can give T2.DISPLAY_NAME also you can get from MA Table as below

T.PURCHASING_GRP_OBJECT_NAME

For Purchasing Organization, result field you can give T1.DISPLAY_NAME also you can get from MA Table as T.PURCHASING_ORG_OBJECT_NAME

Hope this will help you.

Let me know if you need any assistance.

Thanks,

Raj.

Answers (0)