Fetching line items for a contract

[Former Member]

Hi Experts,

I have written a query to fetch all the line items for a particular contract. However, the query results are fetching all the deleted line items in the FCI_CONTRACT_LINEITEMS table also. There is no 'INACTIVE' column in this table which I can use to fetch only the active rows.

In the UI however, the line items tab shows only the active line items.

I am not able to find out the particular column which can be used to get only the active line items in the contracts. Below is the sample query we are using:

SELECT <%RESULTS%> FROM
<%SCHEMA%>.FCI_CONTRACT T1,
<%SCHEMA%>.FCI_CONTRACT_LINEITEM T3
WHERE T1.INACTIVE = 0
AND T1.OBJECTID = T3.PARENT_OBJECT_ID
AND T1.UNIQUE_DOC_NAME = 'OC-5556-SO'

Can you please help ?

Regards

Moumita

[vikram_shanmugasundaram · Accepted solution]

Hi Moumita,

When the line item is deleted from the UI,  it will be deleted from FCI_CONTRACT_LINEITEM table as well.  It's unclear to me how you could still see the deleted line items in your query. What version of Sourcing are you on?

Regards,

Vikram

[Former Member · Accepted solution]

Hi Vikram,

Thanks for the response. Yes, you are correct. We found out that the issue was on our side in the query. The line items get deleted correctly from FCI_CONTRACT_LINEITEM table.

Reason for the issue was:

When we create MSA, entry goes into contract table and contract_lineitems table.

When we create SOW, entry goes into agreement table and contract_lineitems table.

Both contract and agreement tables have OBJECTID as the unique identifier, and the mapping for this is present in contract_lineitems table with the name PARENT_OBJECT_ID.

So, to get the line items for MSA or SOW, we used clause contract.OBJECTID = contract_lineitems.PARENT_OBJECT_ID in our query.

But in some cases both contract and agreement tables have same OBJECTID (my guess is that this is populated using 2 different sequence numbers, so there are chances of same number getting populated for OBJECTID of both the tables).

In our case, MSA1 and SOW1 had the same object id. So when we try to get the corresponding line items for MSA1 using the OBJECTID mapping, it fetches the line items for SOW1 as well.

Fix:

We included another clause, PARENT_CLASS_ID =1004 to get only the line items for MSA) and PARENT_CLASS_ID =1003 to get only the line items for SOW.

Regards

Moumita