on 10-14-2015 1:46 PM
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) <= ?)
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Reddy
Thanks for the response. The Oracle function 'FCI_GET_COMMA_LIST' gives me the below error when I add it to the result field.
SQL Exception: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FCI_GET_COMMA_LIST;FUNCTION, DRIVER=4.14.137, ANSI-92 SQL State: 42884, Vendor Error Code: -440. [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;FCI_GET_COMMA_LIST|FUNCTION, DRIVER=4.14.137, ANSI-92 SQL State: 56098, Vendor Error Code: -727. [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;FCI_GET_COMMA_LIST|FUNCTION, DRIVER=4.14.137, ANSI-92 SQL State: 56098, Vendor Error Code: -727.
Hello Mashimbye,
The same query is working fine for me but I am not sure why it is not working for you.
As a work around, please delete the column test from Results field tab and add one new column with Database Column name as T5.LINK_DOC_ID. This will pull all the RFxs along with its document links in different lines.
You will get the preview in below format:
RFX1 doclink1
RFX1 doclink2
RFX1 doclink3
RFX1 doclink4
Regards,
Vignesh
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
User | Count |
---|---|
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.