[Feature] Add consolidated_exchange_rates_pass_through_columns pass through to NETSUITE2__TRANSACTION_DETAILS

[dubdesigndesk]

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

[dubdesigndesk]

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