BUG - rows in the xero__general_ledger model that have a NULL value in the journal_line_id column about dbt_xero HOT 14 CLOSED

My understanding is that the 0 journal entry amounts are for audit trail purposes.

I would always expect a journal_line even for the 0 entries.

It might be that there are two entries 1 at 0 and 1 when it's updated. I'll have to look into this

from dbt_xero.

Hi @jazzy-r, thanks for opening this issue!

I would first like to confirm that this is not an error from the connector. Would you be able to confirm within your Xero application that for one of these journal_ids that have null for the journal_line_id, do in fact have no journal_line_id.

Our assumption when developing the package was that the journal_line_id would always be populated and be the true unique identifier for these journal records. t would be great to confirm that assumption with your live data.

from dbt_xero.

Hi @fivetran-joemarkiewicz no problem.

How would I go about verifying that there is a journal_line_id in the xero application?

from dbt_xero.

I believe if you go to the general_ledger_report within Xero and filter for one of the entries, you should be able to double click on the journal entry and see if there are journal lines that make up the entry.

from dbt_xero.

I've had a quick look and I can't actually find a way to link these rows with test failures to an entry in the general ledger.

It seems this is because many of the column values are in fact NULL.

from dbt_xero.

Interesting, my understanding is that the journal table would only contain the metadata of the journal record, and then journal_id would contain the monetary value of the entry. I would assume journal would not exist without a journal_line entry?

Would you be able to run the below query and see if you obtain any results? When I run, I see no results. If you do see results, I think we may want to open a support ticket to dig into this behavior in the connector.

with journal as (
    SELECT *
    FROM xero.journal

), journal_line as (
    select *
    from xero.journal_line
)
select 
    journal.journal_id,
    journal_line.journal_line_id,
    journal_line.gross_amount
from journal 
left join journal_line 
    on journal.journal_id = journal_line.journal_id
where journal_line.journal_line_id is null

from dbt_xero.

I ran the above query but changed the FROM clause to match the schema we use:

The tables were stg_xero__journal and stg_xero_journal_line.

It returned rows. Can you confirm that what I did was correct?

from dbt_xero.

@jazzy-r yeah those changes are fine and wouldn't alter the intention of the query.

I just confirmed with our PM of this connector that it is the intended behavior of Xero that a journal_line entry should be present for each journal entry. Would it be possible for you to confirm internally what these journal entries in fact are and if they should be tied to a journal_line entry? If so, this may be a bigger question for Xero as to why these journals are not matching to their respective journal_lines.

from dbt_xero.

@fivetran-joemarkiewicz I've been discussing this with the finance team. It seems it might be instances when a journal entry was created using 0 amounts and / or when this journal is updated again.

from dbt_xero.

Thinking through these cases 🤔

• What would be the business reason for 0 journal entry amounts?
• If the journal is updated again (I assume to have > 0 amounts) then I would expect a journal_line. Is that not the case?

from dbt_xero.

Hey @jazzy-r! I just wanted to check in if you had a chance to look into this further?

from dbt_xero.

Hi @fivetran-joemarkiewicz yes but I didn't get any further with it. Not sure what else can be done.

from dbt_xero.

Thanks for the update @jazzy-r.

I think what will probably be the next best step is to open a ticket with our Support Team as they will have a better idea about what next steps should be taken to look further into this on the Xero side. Unfortunately, I do not have much visibility in this area, but they have helped a lot of other folks with similar questions and I believe they can help us get to the bottom of this question!

from dbt_xero.

Hi @jazzy-r 👋

I will be closing this issue as I assume the thread has moved to the support channel. Please feel free to re-open this issue if you would like to discuss this further.

Thanks!

from dbt_xero.