View: dbo.Journal_Entry_Account_List_Detail_View
View definition
create view dbo.Journal_Entry_Account_List_Detail_View
as
select
detail.journal_entry_KEY
, detail.gl_account_KEY
, detail.sequence_number
, gl_account_number = isnull(gla.gl_account_number, client_undistributed_gl_account_number)
from
(
-- Source: journal entry distributions
select
jed.journal_entry_KEY
, jed.gl_account_KEY
, client_undistributed_gl_account_number = case when je.is_activity_journal_entry = 1 then N'Unassigned' else cugla_gla.gl_account_number end
, sequence_number = case when jed.gl_account_KEY is null then -1 else jed.sequence_number end
from
dbo.Journal_Entry_Distribution as jed
inner join dbo.Journal_Entry as je on je.journal_entry_KEY = jed.journal_entry_KEY
inner join dbo.Client_Undistributed_GL_Account as cugla on cugla.client_KEY = je.client_KEY
inner join dbo.GL_Account as cugla_gla on cugla_gla.gl_account_KEY = cugla.gl_account_KEY
) as detail
left join dbo.GL_Account as gla
on gla.gl_account_KEY = detail.gl_account_KEY
group by
detail.journal_entry_KEY
, detail.gl_account_KEY
, detail.sequence_number
, detail.client_undistributed_gl_account_number
, gla.gl_account_number
-- order by
-- detail.journal_entry_KEY, detail.sequence_number