How to get currency code values in transactional tables

[Sneha Raina]

Hello all, 

In the Posted Transaction Review portlet and the ppa_wip_values table we have currency code values. 

Our projects are a mix between AUD (Australia) and NZD ( New Zealand) and we want to get the currency code value in our data warehouse tables. 

We checked FIN_TRANSACTION, INV_TEAM, RES_RESOURCE etc but none contain currency_code.

Is there a way to access currency code  corresponding to every transaction in the DWH tables?

Thanks, 
Sneha Raina

[Sean jOE]

When utilizing the AQuA API to create a data source export:

In the request's convertToCurrencies box, enter the currencies into which you wish the sums to be converted. Any number of currencies can be specified. Use ISO currency codes to specify the currencies, and use a comma to separate each one.
In the data source export, select the Amount field of the base object.
Otherwise, an error will be given if the type field is not set to "zoqlexport." The type "zoql" does not support this feature.
Make sure you're running API version 78 or higher. You can achieve this by using the apiVersion field to identify the API version. If the apiVersion option is left blank, the most recent API version is used by default.

The following SOAP API request specifies that the data source export contain conversion data for the British Pound Sterling (GBP) and Japanese Yen (JPY).

view sourceprint

1

...

2	<ns1:create xmlns:ns1="http://api.zuora.com/">

3	<ns1:zObjects xsi:type="ns2:Export" xmlns:ns2="http://object.api.zuora.com/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

4	<ns2:Format>csv</ns2:Format>

5	<ns2:Query>select Amount, CreatedDate from Invoice</ns2:Query>

6	<ns2:ConvertToCurrencies>GBP,JPY</ns2:ConvertToCurrencies>

7	</ns1:zObjects>

8	</ns1:create>

We wish you GOOD LUCK!

[Sneha Raina]

Thank you Sean. Would check this out

[Paul Schofield]

In the data warehouse, table dwh_fin_transactions has entity and entity_key columns. Use these to link to the dwh_fin_entity table where the billing, home and reporting currencies are stored. This assumes that the AUD and NZD transactions come from two different entities of course.

[Sneha Raina]

Thanks Paul. This actually seems to work. I was able to find AUD and NZD in dwh_fin_entitiy table.

[Liz Williamson]

I checked for this as well and could not find those specifically associated for the transactions.  The Home currency is the one that shows.
To go along with Paul's reply, there are times the resource has a different currency in the Rate Matrix.  I don't see those anywhere in the DWH either.

------------------------------
Liz Williamson
Principal Support Engineer - Clarity
Broadcom
------------------------------

[Paul Schofield]

Hello again,

If the currency really isn't in the DWH anywhere, then another way is to link back to the original transactions' values in the transactional database via the database link between the databases.

DWH_FIN_TRANSACTIONS has a CLARITY_TRANSACTION_KEY column. This can be linked back to PPA_WIP and hence to PPA_WIP_VALUES:

SELECT pwv.currency_code, pwv.actualcost, pwv.currency_type, dft.*
FROM dwh_fin_transaction dft
JOIN ppa_wip_values@CLARITY pwv on pwv.transno = dft.clarity_transaction_key



Where CLARITY is the name of the database link (I use Oracle, the syntax in SQL Server is a bit different). Just pull up the columns you want from both databases. In the DWH I suspect the monetary amounts will have been boiled down to the default currency code of the entity, but this way you can get back to the original data.