Conversion Estimated value from RFx Header to CPO dashboard

[Former Member]

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

[Former Member]

Hi Dhiraj,

I understood that you want the EURO to be displayed in your dashboard.

So if you are fetching the spend value in your query, you can apply the EURO conversion logic in the result field of "Spend" itself so that it gives you the value in EURO

For Ex:

If your spend value contains 100 INR, in the result field you can apply the logic to get in EURO..

0.01403 * spend value

Hope this will help!!

Thanks,

Vennela

[Former Member]

Hi Vennela,

You have got what I want but the problem is that the spend value  will not be always in INR. It can be in INR,EUR,USD,etc depending on the type of document or country. That is the reason I am thinking about creating a new custom field with currency conversion logic.

However, I already have a custom field which calculate value in Euro. But I am not able to find out the script code which is written for this field in script definition. Is there any way by which I can locate the code for this custom field.

Anyway, thanks for your contribution and help. Please suggest me if something can be done here.

Thank You,

Dhiraj More