Reporting on FCI_ONT_LINE_ITEM_RESP with Master Agreement

[former_member632117]

Hi ,

I'm trying to build a custom query with the output details of Master Agreement Line items and it's specifications. I have the query as below so far...

Can some one help me to bridge a link between table - FCI_CONTRACT_LINEITEM and the Line item specification data created within MA-> Line items? Is the data would be found in FCI_DCOM_ITEM_SPEC ?
SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_CONTRACT T2 LEFT JOIN <%SCHEMA%>.FCI_CONTRACT_LINEITEM LI ON T2.OBJECTID=LI.PARENT_OBJECT_ID LEFT JOIN <%SCHEMA%>.FCI_MAS_VENDOR VEN ON T2.VENDOR_OBJECT_ID=VEN.OBJECTID

<.....need link to a specific line item specification values in MA- Line items..>

WHERE

T2.INACTIVE = 0 AND

T2.VENDOR_OBJECT_ID=VEN.OBJECTID AND
T2.UNIQUE_DOC_NAME LIKE ? AND
T2.IS_TEMPLATE = 0 AND
T2.CONTEXTID=<%CONTEXT(contracts.Contract)%>

<%ORDERBY%>

Or is there any standard query which uses this data? We are under e-souring CLM 11.0. Thanks, Thomas

[former_member13619 · Accepted solution]

Did you already look into the reference guide? There you find all the information and depending on what you want to show, fields or collections you have to link more tables to it.

You can go into the master agreement and click on the RG (reference guide) so you will be immediately at the right spot or you open the RG from somewhere else and do a find for the ID 1004.

Go to the master agreement (ID = 1004), go to the schema view and then choose the line item (bottom of the list) or service line items as they have different tables. All the technical data is there to build up your query (which tables, fieldnames, etc...). It could be that you have to link more tables to it (especially if it are collections you want to query).