Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Conversion Estimated value from RFx Header to CPO dashboard

In Esourcing , we have developed a dashboard called as CPO Dashborad. In Dashboard there is one output for SPEND.As per current setup , system is considering only those Rfx with current phase as AWARD and calculates total spend as Estimated Value in Rfx Header.

Client Requirement is that they want this estimated value from RFx header to reflect in CPO dashboard in EURO's.We have query to reflect amount from estimated value to CPO dashboard but the problem is estimated value is stored in document specific currency i.e. sometimes amount will be in Rs,USD,EUR,etc. But the client want that estimated amount from RFx header to be displayed in EURO within dashboard. Hence I wanted to know is there any feasible way /solution to met the above requirements.

Actually I am having knowledge of Java and mySQL. But doesn't have any experience on ESourcing issues.I have been assigned this task since the query is in mysql.

Could someone please help me to solve this problem. Please find the queries for Estimated value below:

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

INNER JOIN

<%EXT_TABLE(rfx.RFXDoc)%> T3

ON T3.PARENT_OBJECT_ID = T1.OBJECTID

INNER JOIN

FCI_UPP_USER_ACCOUNT T2

ON

T1.DOC_OWNER_USER_OBJECT_ID = T2.OBJECTID

WHERE

T1.INACTIVE = 0 AND

T1.IS_TEMPLATE = 0 AND

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

T2.REGION IS NOT NULL AND

EXTRACT( YEAR FROM T1.CREATED_AT) >= EXTRACT(YEAR FROM sysdate) AND

T1.CURRENT_PHASE = 6

AND T2.REGION IN ('Europe','North America','North Asia','South Asia','South East Asia')

group by T2.REGION

<%ORDERBY%>

SELECT SUM(T10.ESTIMATED_PRICE) FROM <%SCHEMA%>.FCI_RFX_DOC T10

INNER JOIN

<%EXT_TABLE(rfx.RFXDoc)%> T30

ON T30.PARENT_OBJECT_ID = T10.OBJECTID

INNER JOIN

FCI_UPP_USER_ACCOUNT T20

ON T10.DOC_OWNER_USER_OBJECT_ID = T20.OBJECTID

WHERE

T10.INACTIVE = 0 AND

T10.IS_TEMPLATE = 0 AND

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

T20.REGION IS NOT NULL AND

EXTRACT( YEAR FROM T10.CREATED_AT) >= EXTRACT(YEAR FROM sysdate) AND

T10.CURRENT_PHASE = 6 AND T20.REGION = T2.REGION

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question