Skip to Content

Writing a query

I am new to SAP sourcing and have a requirement to write a query with the below result fields. The Query must show all the RFXs’s. If the RFX has a related project, that project should be displayed as well and the same should be done if it has a related master agreement. The important part that I seem to struggle with is that the query brings duplicates once the RFX has a Related Project and is created from another RFX meaning it will also have a related RFX. Below is the query that I wrote and attached the picture of my result fields. Please assist

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_PRO_PROJECTS T1

LEFT OUTER JOIN <%SCHEMA%>.FCI_DOC_LINK T2 ON T2.PARENT_OBJECT_ID = T1.OBJECTID

RIGHT OUTER JOIN <%SCHEMA%>.FCI_RFX_DOC T3 ON T3.OBJECTID = T2.LINK_DOC_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_DOC_LINK T10 ON T10.PARENT_OBJECT_ID = T3.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_CONTRACT T11 ON T11.OBJECTID = T10.LINK_DOC_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>. FCI_CONGEN_CONTRACT_DOC T13 ON T13.PARENT_OBJECT_ID = T11.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>. FCI_DOC_CONTRACT_PHASE T14 ON T14.PARENT_OBJECT_ID = T13.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>. FCI_DOC_WORKFLOW_HISTORY T15 ON T15.PARENT_OBJECT_ID = T13.OBJECTID

LEFT OUTER JOIN <%EXT_TABLE(projects.projects)%> T5 ON T1.OBJECTID = T5.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T6 ON UPPER(T6.DISPLAY_NAME) = UPPER(T5.EXT_CLASSIFICA_NEW_OBJECT_NAME)

LEFT OUTER JOIN <%SCHEMA%>. FCI_PRO_CONFIG_PHASE_SUB T17 ON T1.CURR_CONFIG_PHASE_OBJECT_ID = T17.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>. FCI_DOC_WORKFLOW_HISTORY T18 ON T18.PARENT_OBJECT_ID = T1.OBJECTID

LEFT OUTER JOIN <%EXT_TABLE(rfx.RFXDoc)%> T8 ON UPPER(T6.DISPLAY_NAME) = UPPER(T8.EXT_CLASSIFICA_NEW_OBJECT_NAME)

LEFT OUTER JOIN <%EXT_TABLE(rfx.RFXDoc)%> T16 ON T3.OBJECTID = T16.PARENT_OBJECT_ID

WHERE (T3.IS_TEMPLATE = 0 AND T3.CONTEXTID=<%CONTEXT(rfx.RFXDoc)%> AND T3.INACTIVE = 0 )

AND (T13.CURR_CONFIG_PHASE_OBJECT_ID = T14.OBJECTID OR T13.CURR_CONFIG_PHASE_OBJECT_ID IS NULL)

AND (T14.DISPLAY_NAME = T15.PHASE_REF_OBJECT_NAME OR T14.DISPLAY_NAME IS NULL)

AND (T18.PHASE_REF_OBJECT_NAME = T17.DISPLAY_NAME OR T17.DISPLAY_NAME IS NULL)

AND (T2.LINK_DEFINITION_OBJECT_NAME = 'Related RFx' AND

T10.LINK_DEFINITION_OBJECT_NAME <> 'RFx Template' and T10.LINK_DEFINITION_OBJECT_NAME = 'Related Project' OR T2.LINK_DEFINITION_OBJECT_NAME IS NULL)

and (T10.LINK_DEFINITION_OBJECT_NAME <> 'Previous RFx' or T10.LINK_DEFINITION_OBJECT_NAME is null)

and (T10.LINK_DEFINITION_OBJECT_NAME <> 'Related Agreement' or T10.LINK_DEFINITION_OBJECT_NAME is null)

AND (T10.PARENT_DOC_NAME = T3.UNIQUE_DOC_NAME OR T10.PARENT_DOC_NAME IS NULL)

AND (DATE(T3.CREATED_AT) >= ? AND DATE(T3.CREATED_AT) <= ?)

Results_Fields.PNG (38.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 14, 2015 at 01:03 PM

    Hello Mashimbye,

    Please find below query which will pull all the RFxs along with the documents(projects, RFx, Auctions, MAs) attached in the Documents links section of the RFx

    Query String:

    SELECT DISTINCT

    <%RESULTS%>

    FROM

    <%SCHEMA%>.FCI_RFX_DOC T1

    LEFT OUTER JOIN <%SCHEMA%>.FCI_DOC_LINK T5

    ON T1.OBJECTID = T5.PARENT_OBJECT_ID

    WHERE T1.INACTIVE = 0

    AND T1.IS_TEMPLATE = 0

    AND T1.CONTEXTID=<%CONTEXT(rfx.RFXDoc)%>

    Result Field: Add a column in the result field with Database Column name as below

    FCI_GET_COMMA_LIST('SELECT T5.LINK_DOC_ID FROM <%SCHEMA%>.FCI_DOC_LINK T5 WHERE (T5.PARENT_CLASS_ID = 900 OR T5.PARENT_CLASS_ID = 1100 OR T5.PARENT_CLASS_ID = 1004 OR T5.PARENT_CLASS_ID = 816) AND T5.PARENT_OBJECT_ID='||T1.OBJECTID)

    This resolves your issue!

    Regards,

    Vignesh

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Vignesh

      All your answers are correct but my initial query does that and that is what I have to fix. But In short, My result should be in the format below: In different result fields.

      RFX1 | Doclink1 | Doclink2 | Doclink3|

      RFX2 | Doclink1 | Doclink2 | Doclink3|

      RFX3 | Doclink1 | Doclink2 | Doclink3|

      Thanks for your patience and assistance. Much appreciated

      Regards

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.