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;