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 | |||