Query with two filter prompts(phase and document)

[gangadurga_kadimicherla]

Hi All,

I am new to sap sourcing.

I am trying to customize a query for (Sourcing Report for all supplier registrations showing the cycle times for each
approval phase of the process)which I have added two filter prompts(phase and document name).

When I try to execute the query, I don't get the required output for the phase filter prompt.

Also phase is a value list and when I select the phase from the drop down, the report doesn't give any value.

Please find the query as mentioned.

SELECT <%RESULTS%>

FROM <%SCHEMA%>.FCI_WORK_ITEM

T1

INNER JOIN <%SCHEMA%>.FCI_WORK_PROCESS T2 ON T1.PARENT_OBJECT_ID =

T2.OBJECTID INNER JOIN (

SELECT

OBJECTID,

10002203 AS

CLASSID,

CREATED_BY_USER_OBJECT_ID,

CREATED_BY_USER_CLASS_ID,

CREATED_BY_USER_OBJECT_NAME,

CREATED_AT,

EDIT_AT_DATETIME, MODIFIED_AT

FROM

<%SCHEMA%>.ODP_ODP_VEN_MODIFICATION_WF

WHERE

CONTEXTID=<%CONTEXT(odp.vendormgmt.wf_vendor_modification)%>

UNION

ALL

SELECT

OBJECTID,

10002213 AS

CLASSID,

CREATED_BY_USER_OBJECT_ID,

CREATED_BY_USER_CLASS_ID,

CREATED_BY_USER_OBJECT_NAME,

CREATED_AT,

EDIT_AT_DATETIME, MODIFIED_AT

FROM

<%SCHEMA%>.ODP_ODP_VEN_REGISTRATION_WF

WHERE

CONTEXTID=<%CONTEXT(odp.vendormgmt.wf_vendor_registration)%>

)

T3 ON

T3.OBJECTID = <%COALESCE%>(T1.ROOT_PARENT_OBJECT_ID,

T1.BIZ_DOC_OBJECT_ID) AND

T3.CLASSID = CASE WHEN

T1.ROOT_PARENT_OBJECT_ID IS NULL THEN T1.BIZ_DOC_CLASS_ID ELSE

T1.ROOT_PARENT_CLASS_ID END

WHERE

T1.CONTEXTID=<%CONTEXT(workflow.process)%> AND

(T2.PHASE_SUB_REF_OBJECT_NAME like ?OR T2.BIZ_DOC_OBJECT_NAME like

?)

<%ORDERBY%>

Please suggest how can I change the query accordingly.

Also , please suggest how can add the result fields for calculating the duration(created at - Action date).

Please do the needful.

Thanks and Regards

Ganga

[gangadurga_kadimicherla]

Hi Vignesh,

How can I do that (mark as correct).

Thanks and Regards

Ganga Durga

[Former Member]

Under every reply you can find that option with start icon (which will be green in color).

[gangadurga_kadimicherla]

Hi Vignesh,

Thanks a lot!! This solved the problem.Now the query works.fine.

But, I have given the Enum Type Name as Workflow.Approval Status

Is there any value that I can hide the unwanted values in the dropdown.

And I also want to add a value in the drop down so that it will display all workflow status in the query.

Please suggest.

Thanks and Regards

Ganga Durga

[Former Member]

Hi Ganga,

The values in the Enumeration are standard. They can neither be hidden nor you can add some value in the Enumeration.

Also, If the above suggestion worked please mark this as correct so that it would help the community!!

Regards,

Vignesh

[gangadurga_kadimicherla]

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         Hello Vignesh,

Thank you!!

I could find that only the supplier registration modifications are included in the query given by you, but we need to include the supplier registrations as well.

I tried modifying the query and it worked.

But, now the challenge is to include the workflow status(T1.APPROVAL_STAUS) in the filter prompt.

T1 table is FCI_WORK_ITEM.

T.APPROVAL_STAUS is an integer data type in the table.

I defined the T.APPROVAL_STAUS as string In Result fields and in the filter prompt.

But the query works for other filter prompts except workflow status.

I have included the filter prompts as mentioned below:

T1.CONTEXTID=<%CONTEXT(workflow.process)%> AND

UPPER(T2.BIZ_DOC_OBJECT_NAME) like ? AND

UPPER(T2.PHASE_SUB_REF_OBJECT_NAME) like ? AND

UPPER(T1.APPROVAL_STATUS) like ? AND

UPPER(T1.PERFORMER_OBJECT_NAME) like ?

<%ORDERBY%>

Please suggest on this.

Thanks and Regards

Ganga Durga

[Former Member]

Hello,

The column T1.APPROVAL_STATUS is of Enumeration type. PFB the values in Status field:

In query, the condition should be T1.APPROVAL_STATUS = ? and in filters tab, the data type should be selected as enumeration. Follow the below screenshot.

This will solve your problem!!!

Regards,

Vignesh

[Former Member]

Hi Ganga,

Use below snippet instead of the existing one in your query

T2.PHASE_SUB_REF_OBJECT_ID = ? OR T2.BIZ_DOC_OBJECT_ID=? (I am assuming that the biz_doc_object_id is object reference).

Hope this helps!!

Regards,

Vignesh

[gangadurga_kadimicherla]

Hi Vignesh,

I tried using snippet given by you.

I get following error:

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

Error Code: 1722. ORA-01722: invalid number

Please suggest.

Thanks and Regards

Ganga

[Former Member]

Hello,

Can you give the name of the standard query which you have duplicated or customized.

And also let me know the purpose of adding those 2 filters.

Regards,

Vignesh

[gangadurga_kadimicherla]

Hi,

The customized report is Suppliers Approval History.

The purpose of adding those filter prompts is that can filter out based on phase and document as the report is for all supplier registrations showing the cycle times for each approval phase of the process.

Regards

Ganga

[Former Member]

Hi Ganga,

I feel that you are doing some error in filter prompt tab.

Check whether you are giving correct field type.

1. Document: Type should be object ref. and default value should be null:(class id of that particular document):null

2. Phase: Type should be value list and in adjacent field, you need to select the correct value list from the picker.

Let me know if you still face this issue.

Regards,

Vignesh

[gangadurga_kadimicherla]

Hi Vignesh,

I defined the phase filter prompt as said by you only.

Please find the screenshot

The other filter prompt I defined is display name.

Also let me know how to find the class id of that particular document in default value.

Please suggest.

Thanks and Regards

Ganga Durga

[Former Member]

Hello,

Refer the modified query below:

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_WORK_ITEM T1
LEFT OUTER JOIN <%SCHEMA%>.FCI_WORK_PROCESS T2 ON T1.PARENT_OBJECT_ID = T2.OBJECTID
LEFT OUTER JOIN  <%SCHEMA%>.ODP_ODP_VEN_MODIFICATION_WF T3
ON (T3.OBJECTID =T1.ROOT_PARENT_OBJECT_ID or T3.OBJECTID =T1.BIZ_DOC_OBJECT_ID)
<%DYN_JOIN%>
WHERE T1.INACTIVE=0
<%AND_OP("T2.PHASE_SUB_REF_OBJECT_ID",<%?(T2.PHASE_SUB_REF_OBJECT_NAME)%>)%>
<%AND_OP("T2.BIZ_DOC_OBJECT_NAME",<%?(Name)%>)%>

<%DYN_FILTER%>
<%ORDERBY%>

This will solve your issue!!

Regards,

Vignesh