on 03-11-2014 11:34 AM
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
Hi Jaheer,
Did your Issue resolved?
Please let me know if you need any assistance.
Thanks,
Raj.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.