[FEATURE] stg_netsuite2_transactions.status does not provide "display" status

[ghost]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

The current package setup returns 'A', 'B', 'C', etc as transactions status (differently from NS1). NS2 provides a dedicated function to calculate the display status: https://timdietrich.me/blog/netsuite-suiteql-transaction-statuses/

Relevant error log or model output

No response

Expected behavior

The "display" status should be 'Fully Billed', 'Open', etc

dbt Project configurations

basic config

Package versions

0.7.1

What database are you using dbt with?

snowflake

dbt Version

1.3.2

Additional Context

Solution 1: package should foresee the join with stg_netsuite2__transactionstatus on status and type

FROM {{ ref('stg_netsuite2__transactions') }} AS transactions
INNER JOIN {{ ref('stg_transaction_status') }} AS status_map
    ON transactions.status = status_map.id
        AND transactions.transaction_type = status_map.trantype

Solution 2: adding the conversion in the _tmp model

Solution 3: a macro to get the value from the transaction status and type:

{% macro get_status_display_value(tr_status, tr_type) %}
    SELECT name
    FROM {{ source('netsuite2', 'transactionstatus') }}
    WHERE id = {{ tr_status }}
    AND trantype = {{ tr_type }}
{% endmacro %}

Are you willing to open a PR to help address this issue?

• [X] Yes.
• [ ] Yes, but I will need assistance and will schedule time during our office hours for guidance
• [ ] No.

[fivetran-joemarkiewicz]

Hi @ropalloni thanks for opening this issue and for adding valuable context in order to understand the status mapping within Netsuite2. I am going to adjust the bug report to instead me positioned as a feature request since this seems to fit more in line with adding additional functionality rather than fixing something that is broken.

That being said, I think this would be a great feature to add to the package and provide users with more insight into their Netsuite2 transaction data. The only thing to call out is that in my own local data I do not see the transactionstatus source table. If this is implemented, we will want to take into account that not all users may have this source table at the start. Additionally, we typically do not prefer performing joins in the staging models. I feel the join you suggested would be best served as an intermediate model in the dbt_netsuite package. However, in this package we would need to still add the transaction status staging model to then be brought downstream.

I noticed you are open to creating a PR. If you want to contribute this feature to the package, I would be happy to review and help to integrate it into this package and the downstream dbt_netsuite package. Let me know! In the meantime, I will explore adding the transactionstatus table to my local data and I will continue to explore this.

[JustinSmethers]

I'm working on a PR for this now. I'd be happy to help integrate the transactionstatus table into the downstream dbt_netsuite package as well. In case you weren't able to get the transactionstatus source table, I found that you need to have the "Set Up Company" permission in NetSuite. Seems weird, but that's NetSuite 🤷