View: dbo.Bank_Transaction_Account_List_View
View definition
CREATE VIEW dbo.Bank_Transaction_Account_List_View
AS
SELECT T1.bank_transaction_KEY
, gl_account_number_list = CASE
WHEN len(T1.btx_gl_account_number) > 0 AND len(T1.dist_gl_account_number_list) > 0
THEN T1.btx_gl_account_number + N', ' + T1.dist_gl_account_number_list
WHEN len(T1.btx_gl_account_number) > 0
THEN T1.btx_gl_account_number
ELSE T1.dist_gl_account_number_list
END
FROM (
SELECT bt.bank_transaction_KEY
, btx_gl_account_number = CASE
WHEN bt.transaction_status_KEY IN (9, 14, 15) -- Impound Memo, Impound Memo Deleted, Impound Memo Voided
THEN COALESCE(gla_impound.gl_account_number, gla_undistributed.gl_account_number)
ELSE COALESCE(gla_standard.gl_account_number, gla_undistributed.gl_account_number)
END
, dist_gl_account_number_list = replace(rtrim((
SELECT cast(gla.gl_account_number AS VARCHAR(max)) + N' '
FROM dbo.Bank_Transaction_Distribution AS dist
INNER JOIN dbo.GL_Account AS gla ON gla.gl_account_KEY = dist.gl_account_KEY
WHERE dist.bank_transaction_KEY = bt.bank_transaction_KEY AND dist.gl_account_KEY IS NOT NULL
AND NOT EXISTS (
SELECT 1
FROM dbo.Bank_Transaction_Distribution AS earlier_dist
WHERE dist.bank_transaction_KEY = earlier_dist.bank_transaction_KEY AND dist.gl_account_KEY = earlier_dist.gl_account_KEY AND dist.sequence_number > earlier_dist.sequence_number
-- We cannot elimintate duplicates on sequence number, and are living with that
)
ORDER BY dist.sequence_number
FOR XML path(N'')
)), N' ', N', ')
FROM dbo.Bank_Transaction AS bt
INNER JOIN dbo.Checkbook AS cbk ON bt.checkbook_KEY = cbk.checkbook_KEY
INNER JOIN dbo.Client_Undistributed_GL_Account AS undistributed ON undistributed.client_KEY = cbk.client_KEY
INNER JOIN dbo.GL_Account AS gla_undistributed ON gla_undistributed.gl_account_KEY = undistributed.gl_account_KEY
LEFT JOIN dbo.GL_Account AS gla_impound ON gla_impound.gl_account_KEY = cbk.impounding__gl_account_KEY
LEFT JOIN dbo.GL_Account AS gla_standard ON gla_standard.gl_account_KEY = cbk.gl_account_KEY
) AS T1