Status Field in query

[jaheer_hussain]

Hi,

In the my query filter field 'staus',this field should have as dropdown values like  as Draft/Approved/Executed/Expiry/Cancelled.

I configured this field as below.I am not getting these vales Draft/Approved/Executed/Expiry/Cancelled.Can some body guide me,please? I need select data type as "object reference" ?????.

With Regards,

Jaheer

[Former Member · Accepted solution]

Hi Jaheer,

You need to configure the filter to a dropdown to a value list value and there should be one available for Master Agreement Status.

Regards,

Parankush

[jaheer_hussain · Accepted solution]

Dear Prankush,

Can you please give some information for how to this configuration?

With Regards,

Jaheer

[jaheer_hussain · Accepted solution]

I got it.Thank you. I have given points.

With Regards,

Jaheer

[former_member207877]

Hi Jaheer,

Please find the screenshot of configuration:

I hope it would help.

Thanks,

Raj

[jaheer_hussain]

Hi Raj,

Thank you. I got this already.

How ever how to add this field in my query?

I  added coding like below but I am getting error .

SQL Exception: ORA-01722: invalid number , ANSI-92 SQL State: 42000, Vendor

Error Code: 1722. ORA-01722: invalid number.

Can you please guide me?

With Regards,

Jaheer

[former_member207877]

Hi Jaheer,

Please have a look in to the query which I have written.

SELECT <%RESULTS%>

FROM <%SCHEMA%>.FCI_CONTRACT T2

LEFT OUTER JOIN <%SCHEMA%>.FCI_CONGEN_CONTRACT_DOC T3

ON T2.OBJECTID = T3.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T6

ON T2.STATUS_OBJECT_ID= T6.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_TYPE T5

ON T6.PARENT_OBJECT_ID= T5.OBJECTID

WHERE T2.INACTIVE=0 AND T2.STATUS_OBJECT_ID=T6.OBJECTID AND

T6.OBJECTID=?

Group By (T2.OBJECTID,T2.UNIQUE_DOC_NAME,T2.STATUS_OBJECT_NAME)

In the results field please find the screenshot:

Please let me know if you still have problem.

Thanks,

Raj

[jaheer_hussain]

Hi  Raj,

I added coding like this,but still I am getting error.

Note:please note I am working a function lead.

SQL Exception: ORA-00904:
"T4"."DISPLAY_OBJECT_NAMET_ID": invalid identifier , ANSI-92 SQL State: 42000,
Vendor Error Code: 904. ORA-00904: "T4"."DISPLAY_OBJECT_NAMET_ID": invalid
identifier

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

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE

T4

ON T4.DISPLAY_OBJECT_NAMET_ID= T4.DISPLAY_OBJECT_NAMET_ID

[former_member207877]

Hi Jaheer,

The name which you entered is wrong.

Please find the screen shot.

Thanks,

Raj.

[jaheer_hussain]

Hi Raj,

I have filter as T4.display_name_id and result field as t4.display_name_id,that is why i entered like this.

Can you change my coding and show me,please?

Regards,

Jaheer

[former_member207877]

Hi Jaheer,

You can use the code which I have sent to you.

All the fields are standard.

Thanks,

Raj

[jaheer_hussain]

Thank you Raj

With Regards,

Jaheer

[jaheer_hussain]

Hello Raj,

Thank you very much it is working fine.

I need some more help from your end.

I want to add some fields like company code,Agreement manager,Expiry date in the results(output).

With Regards,

Jaheer

[former_member207877]

HI Jaheer,

For the above mentioned fields,  T is Master Agreement Table

Agreement manager T.DOC_OWNER_USER_OBJECT_NAME

Company Code   For company code you have to add extension table(company) and then you should get the result field from that table by linking with Master Agreement table.

Expiration Date T.EXPIRATION_DATE_DATE

Please let me know if you need any further information.

Thanks,

Raj.