View: dbo.Subsidiary_Client_Balance_View | |||
View definition | |||
-- SUMMARY -- This view gathers all the subsidiary client ledger entries and summarizes them by subsidary client, GL period and -- journal entry type for the master client. --drop view dbo.Subsidiary_Client_Balance_View CREATE VIEW dbo.Subsidiary_Client_Balance_View AS SELECT (_T013.debit__total_amount - _T013.credit__total_amount) AS balance_amount, _T013.balance_type_KEY, _T013.client_KEY, _T013.consolidating__client_KEY, _T013.credit__total_amount, _T013.debit__total_amount, _T013.end_date, _T013.gl_account_KEY, _T013.gl_period_number, _T013.journal_KEY, journal_entry_type_KEY = _T013.adjusted__journal_entry_type_KEY FROM (SELECT SUM(CASE WHEN ((_T012.gl_balance_effect_KEY = 1 /* Debit */)) THEN _T012.gl_transaction_amount ELSE 0 END) AS debit__total_amount, _T012.balance_type_KEY, _T012.client_KEY, _T012.consolidating__client_KEY, SUM(CASE WHEN ((_T012.gl_balance_effect_KEY = 2 /* Credit */)) THEN _T012.gl_transaction_amount ELSE 0 END) AS credit__total_amount, _T012.end_date, _T012.gl_account_KEY, _T012.gl_period_number, min(_T012.journal_KEY) as journal_KEY, _T012.adjusted__journal_entry_type_KEY FROM ( -- Gather all subsidiary client ledger entries for the defined period range and summarize them. SELECT _T001.balance_type_KEY , _T011.bank_transaction_KEY , _T006.subsidiary__client_KEY AS client_KEY , _T001.client_KEY AS consolidating__client_KEY , _T007.end_date , _T011.gl_account_KEY , _T011.gl_balance_effect_KEY , _T011.gl_balancing_entry_KEY , _T008.gl_period_number , _T011.gl_transaction_amount , _T011.gl_transaction_KEY , _T011.gl_transaction_source_KEY , _T011.journal_entry_KEY , _T010.journal_entry_type_KEY , case when _T010.journal_entry_type_KEY = 102 -- Beginning balance then 102 -- Beginning balances are grouped into a beginning balance journal entry in the master client. else 101 -- All other types get grouped together into an activity journal entry in the master client. end as adjusted__journal_entry_type_KEY , _T011.journal_KEY FROM dbo.Client_Consolidation_Option AS _T001 INNER JOIN dbo.Client_N_Subsidiary_Client AS _T006 ON _T001.client_KEY = _T006.consolidating__client_KEY INNER JOIN dbo.Client_AP_Setup AS caps on _T006.subsidiary__client_KEY = caps.client_KEY INNER JOIN dbo.Client_AR_Setup AS cars on _T006.subsidiary__client_KEY = cars.client_KEY INNER JOIN dbo.Balance_Definition AS _T009 ON _T006.subsidiary__client_KEY = _T009.client_KEY AND _T001.balance_type_KEY = _T009.balance_type_KEY INNER JOIN dbo.Balance_Definition_Journal_Entry_Type_Component AS _T010 ON _T009.balance_definition_KEY = _T010.balance_definition_KEY INNER JOIN dbo.Ledger_Entry AS _T011 ON _T010.journal_entry_type_KEY = _T011.journal_entry_type_KEY AND _T006.subsidiary__client_KEY = _T011.client_KEY INNER JOIN dbo.GL_Period AS _T008 ON _T011.gl_period_KEY = _T008.gl_period_KEY INNER JOIN dbo.GL_Fiscal_Year AS _T007 ON _T008.gl_fiscal_year_KEY = _T007.gl_fiscal_year_KEY where _T006.subsidiary_client_collection_type_KEY = 1 /* SelectedToConsolidate */ AND (_T011.transaction_status_KEY in (1, 9)) /* Live and Impound checks*/ and -- Keep only ledger entries for the target accounting basis - accrual vs. cash. ( -- Pick up those entries that are for *both* bases. _T011.gl_accounting_method_KEY is null -- Pick up AP entries that are for the selected AP accounting basis. or (_T011.gl_transaction_source_KEY in (4, 5) and _T011.gl_accounting_method_KEY = caps.ap__gl_accounting_method_KEY) -- Pick up AR entries that are for the selected AR accounting basis. or (_T011.gl_transaction_source_KEY in (6, 7, 8) and _T011.gl_accounting_method_KEY = cars.ar__gl_accounting_method_KEY) ) and _T011.gl_period_KEY in ( -- Include subsidiary client ledger entries only from those periods that are within the period range -- defined in the Master client's Client Consolidation Options. select _T008.gl_period_KEY from dbo.Client_Consolidation_Option AS _T001 INNER JOIN dbo.GL_Period AS _T002 ON _T001.consolidation_period_start__gl_period_KEY = _T002.gl_period_KEY INNER JOIN dbo.GL_Fiscal_Year AS _T003 ON _T002.gl_fiscal_year_KEY = _T003.gl_fiscal_year_KEY INNER JOIN dbo.GL_Period AS _T004 ON _T001.consolidation_period_end__gl_period_KEY = _T004.gl_period_KEY INNER JOIN dbo.GL_Fiscal_Year AS _T005 ON _T004.gl_fiscal_year_KEY = _T005.gl_fiscal_year_KEY INNER JOIN dbo.Client_N_Subsidiary_Client AS _T006 ON _T001.client_KEY = _T006.consolidating__client_KEY INNER JOIN dbo.GL_Fiscal_Year AS _T007 ON _T006.subsidiary__client_KEY = _T007.client_KEY INNER JOIN dbo.GL_Period AS _T008 ON _T007.gl_fiscal_year_KEY = _T008.gl_fiscal_year_KEY where _T006.subsidiary_client_collection_type_KEY = 1 /* SelectedToConsolidate */ AND ( _T007.end_date >= _T003.end_date AND _T007.end_date <= _T005.end_date AND (_T007.end_date <> _T003.end_date OR _T008.gl_period_number >= _T002.gl_period_number) AND (_T007.end_date <> _T005.end_date OR _T008.gl_period_number <= _T004.gl_period_number) ) ) ) AS _T012 GROUP BY _T012.consolidating__client_KEY, _T012.client_KEY, _T012.adjusted__journal_entry_type_KEY, _T012.gl_account_KEY, _T012.balance_type_KEY, _T012.end_date, _T012.gl_period_number) AS _T013; | |||