dbt_netsuite
dbt_netsuite copied to clipboard
[Feature] Add consolidated_exchange_rates_pass_through_columns pass through to NETSUITE2__TRANSACTION_DETAILS
Is there an existing feature request for this?
- [X] I have searched the existing issues
Describe the Feature
I have added
consolidated_exchange_rates_pass_through_columns:
- name: "fromsubsidiary"
alias: "trans_from_subsidiary_id"
- name: "tosubsidiary"
alias: "trans_to_subsidiary_id"
To my project.yaml but the data is not being passed through. Your team had a look and stated
"I have narrowed down the issue with fromsubsidiary not populating to the NETSUITE2__TRANSACTION_DETAILS not having the configuration set up to pass through that column from consolidated_exchange_rates. The next step would be to open a feature request in our netsuite repo so our team can look into adding that configuration in a future sprint!"
Describe alternatives you've considered
No response
Are you interested in contributing this feature?
- [ ] Yes.
- [x] Yes, but I will need assistance and will schedule time during your office hours for guidance.
- [ ] No.
Anything else?
No response
Thanks team for the call today. So from our discussion it looks like we need to just add a few fields like has been done for the to_subsidiary_id which are;
fromsubsidiaryid as from_subsidiary_id,
fromcurrency as from_currency_id,
tocurrency as to_currency_id
This would then allow us to join to a view such as
with rates as (select * from {{ source ("netsuite", "consolidatedexchangerate") }}),
periods as (select * from {{ source ("netsuite", "accountingperiod") }}),
final as (
select *
from rates
left join periods on rates.postingperiod= periods.id
)
select *
from final
where
(startdate<= CURRENT_DATE and enddate>= CURRENT_DATE)
--in our case we always want to Euro which is 1
and to_currency_id = 1
--in our case we are always concerned with our home subsidiary
and to_subsidiary_id = 1
order by from_currency_id
Then we join the NETSUITE2__TRANSACTION_DETAILS table to the view
left join rates on NETSUITE2__TRANSACTION_DETAILS.from_subsidiary_id = rates.from_subsidiary_id
and NETSUITE2__TRANSACTION_DETAILS.from_currency_id = rates.from_currency_id
Since we will now have the to_currency_id we can drop the hard coding as well and include that perhaps.
This allows a user to then create a report that shows outstanding debt at values in todays converted amount rather than amount at the time of the transaction
Thanks