We need help with e - Sourcing 5.1

[Former Member]

We are currently running eSourcing 5.1 and our clients have asked us to enhance a basic query.  The query shows approval history.  They would like to add target contract value and estimated contract value to this query to depict the amount of the contract that was approved.  This is a very basic desire for the clients and it should be a very easy thing for us to provide.  We cloned the ZCFI ApprovalHistory query which shows elements from tables such as FCI_WORK_ITEM, FCI_WORK_PROCESS, and FCI_WORKITEM_DOC_VIEW .... we need to relate one of these tables to the contract document that the item represents so that we can show the target and estimated value fields.  We do not have a data model and we have been told that there is none to be had (this I find impossible to believe) .... therefore this becomes a terribly inefficient guessing game and to date we have been unable to find the right pathway to the data the users desire.  We do not know how these tables realte.  Can someone help?  Is there a data model?  How do we relate approvals to the documents they represent?  Thank you.

[Former Member · Accepted solution]

Hello Carmella,

Although I don't have a data model with me I think I might be able to help you here. I had faced a similar requirement in one of my earlier assignments.

Things might get confusing but I will give it a try nevertheless, all in good spirit

I think this is how it goes - Whenever a business document is pushed to workflow, a workflow process is created for this document. Also, this process then creates workitems and assigns it to different users (Workitems also refer the business document, workflow process and the step activity for which they are generated)

I think you should approach your problem at hand in the manner listed below:

Edit the existing query string. Introduce the FCI_CONGEN_CONTRACT_DOC table in the query (T1). This is the table that represents Contract Documents. Join it with FCI_WORK_PROCESS table (T2). The joining condition should be based on BIZ_DOC_CLASS_ID and BIZ_DOC_OBJECT_ID fields present in T2. All the other joins seem already present in the out-of-the-box query so your job is made simpler and you need to just make this one join.

After this is done you have a reference to the current contract document and you can access any field you want. Also, in order to access any extension fields you have to join the extension table as well ( I am assuming you are familiar with how it goes).

Again, I cant surely say if all the 'information' I gave above is correct but it had worked for me.

Good Luck

Devesh

[Former Member · Accepted solution]

Devesh,

This is FANTASTIC thank you SO MUCH!!!!! ..... how do you come to know these things?  With no data model how can you tell what fields belong to which table?  Look below, this is what I've written based on what you said.  Of course this does not work.  But how can I know what fields to join?  Can you help me revise this query with the correct fields?  We appreciate your response!

SELECT
<%RESULTS%> FROM <%SCHEMA%>.FCI_WORK_ITEM T1,
<%SCHEMA%>.FCI_WORK_PROCESS T2,
<%SCHEMA%>.FCI_WORKITEM_DOC_VIEW T3
<%SCHEMA%>.FCI_CONGEN_CONTRACT_DOC T4
LEFT OUTER JOIN
<%EXT_TABLE(doccommon.ContractDocument)%> T6 ON
T4.BIZ_DOC_OBJECT_ID  = T6.PARENT_OBJECT_ID 
WHERE
T2.BIZ_DOC_CLASS_ID = T4.BIZ_DOC_CLASS_ID AND
T2.BIZ_DOC_OBJECT_ID = T4.BIZ_DOC_OBJECT_ID AND
T1.CONTEXTID=<%CONTEXT(workflow.process)%> AND
T1.PARENT_OBJECT_ID = T2.OBJECTID AND
T3.CONTEXTID=<%CONTEXT(projects.projects)%> AND
T3.OBJECTID = (CASE WHEN T1.ROOT_PARENT_OBJECT_ID IS NULL THEN T1.BIZ_DOC_OBJECT_ID ELSE T1.ROOT_PARENT_OBJECT_ID END ) 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 )
<%ORDERBY%>

[Former Member · Accepted solution]

Hi Carmela,

Given the absence of a data-model, the best way to understand the back-end is if you have access to the database. If you can see the data present in the tables then life becomes much easier. You can see how two tables are linked by observing values in the many CLASS_ID columns.

If you don't have access to the database, which is a very valid scenario in many projects, you can still see the fields that a table has. For this you look at the 'Browse Full Schema' section under Setup -> System Setup -> Queries and Reports. Of course this will only give you field names/types in a particular table and not the data.

Now to your query, I think everything looks fine except that FCI_CONGEN_CONTRACT_DOC table doesn't have a field BIZ_DOC_OBJECT_ID. In your query try replacing T4.BIZ_DOC_OBJECT_ID with T4.OBJECTID and remove all occurrences T4.BIZ_DOC_CLASS_ID since this field doesnt exist too. These fields exist in the FCI_WORK_ITEM table. If you are sure that you will only be using Contract Documents in workflows then you should put this condition T2.BIZ_DOC_CLASS_ID = 2002 in the query as well.

Let us know how it goes.

Thanks

Devesh

[Former Member · Accepted solution]

Devesh,

This is helpful beyond words.  Not only does the query work now, but the existence of the schema information puts me leaps and bounds from where I was in terms of my ability to support the clients' needs.  They have also asked for "Target Price" and I do not see this on the Contract Extension table.  However it may be on another table and since you pointed out where this information is, I can see them all and experiment.  Our solution is hosted at SAP and we can not see the data.  This does hinder us a lot, and I've explained this to management.  However we can do a lot more now that we know what tables and fields exist.  Thank you SO much.