View: dbo.Bank_Transaction_Distribution_Detail_View
View definition
-- SUMMARY
-- This view produces information detailing bank transaction distributions, including for payroll checks (payroll
-- check payroll items, tax items and worker compensation items).
-- The "magic numbers" contained in the calculations of sequence_number
-- need to be kept in lockstep with those in
-- CS.Accounting.Business.Rules.GLTransactionExpressions as used by
-- method TransactionListReportDetail()
--
-- At least some subset of the ISNULL() invocationa are required to get
-- SQL Server to admit that the sequence_number column is of type INT NOT NULL.
-- Minor experimentation yielded that even the relatively simple item:
-- cti.sequence_number + 60000 did require the ISNULL() treatment. EMA had
-- initially hoped only the CASE statements would require that.
--
-- The goal of the prior paragraph is to make the Entity have a non-nullable
-- type on its SequenceNumber member.
CREATE VIEW dbo.Bank_Transaction_Distribution_Detail_View
AS
select
bank_transaction_KEY
, bank_transaction_distribution_KEY
, NULL as payroll_check_payroll_item_distribution_KEY
, NULL as payroll_check_tax_item_distribution_KEY
, NULL as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, gl_account_KEY
, NULL as gl_balance_effect_KEY
, description as gl_transaction_detail_description
, amount as bank_transaction_distribution_amount
, first__w2_1099_box_type_KEY
, second__w2_1099_box_type_KEY
, sequence_number
from
dbo.Bank_Transaction_Distribution_View
UNION ALL
select
pcpi.bank_transaction_KEY
, NULL as bank_transaction_distribution_KEY
, pcpid.payroll_check_payroll_item_distribution_KEY
, NULL as payroll_check_tax_item_distribution_KEY
, NULL as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, pcpid.expense__gl_account_KEY as gl_account_KEY
, 1 /* Debit */ as gl_balance_effect_KEY
, pcpi.description as gl_transaction_detail_description
, pcpid.amount AS gl_transaction_amount
, NULL as first__w2_1099_box_type_KEY
, NULL as second__w2_1099_box_type_KEY
, ISNULL(cpi.sequence_number + CASE
WHEN cpi.payroll_item_type_KEY = 1 /*Pay*/ THEN 1 /*payrollPayItemSequenceNumberStart*/
WHEN cpi.payroll_item_type_KEY = 3 /*Employer Contribution*/ THEN 30000 /*payrollEmployerContributionItemSequenceNumberStart*/
ELSE 0 /* given WHERE clause, should never happen */
END, 0) AS sequence_number
from
dbo.Payroll_Check_Payroll_Item as pcpi
inner join dbo.Client_Payroll_Item as cpi
ON pcpi.client_payroll_item_KEY = cpi.client_payroll_item_KEY
inner join dbo.Payroll_Check_Payroll_Item_Distribution as pcpid
on pcpi.payroll_check_payroll_item_KEY = pcpid.payroll_check_payroll_item_KEY
where
cpi.payroll_item_type_KEY in (1 /*Pay*/, 3 /*Employer Contribution*/)
and (pcpi.is_excluded_net_pay = 0 or cpi.payroll_item_type_KEY = 3 /* EmployerContribution */)
UNION ALL
select
pcpi.bank_transaction_KEY
, NULL as bank_transaction_distribution_KEY
, pcpid.payroll_check_payroll_item_distribution_KEY
, NULL as payroll_check_tax_item_distribution_KEY
, NULL as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, pcpid.liability__gl_account_KEY as gl_account_KEY
, 2 /* Credit */ as gl_balance_effect_KEY
, pcpi.description as gl_transaction_detail_description
, pcpid.amount AS gl_transaction_amount
, NULL as first__w2_1099_box_type_KEY
, NULL as second__w2_1099_box_type_KEY
, ISNULL(cpi.sequence_number + CASE
WHEN cpi.payroll_item_type_KEY = 2 /*Deduction*/ THEN 15000 /*payrollDeductionItemSequenceNumberStart*/
WHEN cpi.payroll_item_type_KEY = 3 /*Employer Contribution*/ THEN 30000 /*payrollEmployerContributionItemSequenceNumberStart*/
ELSE 0 /* given WHERE clause, should never happen */
END, 0) AS sequence_number
from
dbo.Payroll_Check_Payroll_Item as pcpi
inner join dbo.Client_Payroll_Item as cpi
ON pcpi.client_payroll_item_KEY = cpi.client_payroll_item_KEY
inner join dbo.Payroll_Check_Payroll_Item_Distribution as pcpid
on pcpi.payroll_check_payroll_item_KEY = pcpid.payroll_check_payroll_item_KEY
where
cpi.payroll_item_type_KEY in (2 /*Deduction*/, 3 /*Employer Contribution*/)
and (pcpi.is_excluded_net_pay = 0 or cpi.payroll_item_type_KEY = 3 /* EmployerContribution */)
UNION ALL
select
pcti.bank_transaction_KEY
, NULL as bank_transaction_distribution_KEY
, NULL as payroll_check_payroll_item_distribution_KEY
, pctid.payroll_check_tax_item_distribution_KEY
, NULL as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, pctid.expense__gl_account_KEY as gl_account_KEY
, 1 /* Debit */ as gl_balance_effect_KEY
, pcti.description as gl_transaction_detail_description
, pctid.amount as bank_transaction_distribution_amount
, NULL as first__w2_1099_box_type_KEY
, NULL as second__w2_1099_box_type_KEY
, ISNULL(cti.sequence_number + 60000 /*employerTaxItemSequenceNumberStart*/, 0) AS sequence_number
from
dbo.Payroll_Check_Tax_Item AS pcti
inner join dbo.Client_Tax_Item AS cti
on pcti.client_tax_item_KEY = cti.client_tax_item_KEY
inner join dbo.Payroll_Check_Tax_Item_Distribution AS pctid
on pcti.payroll_check_tax_item_KEY = pctid.payroll_check_tax_item_KEY
where
cti.client_tax_item_type_KEY = 2 /* Employer */
UNION ALL
select
pcti.bank_transaction_KEY
, NULL as bank_transaction_distribution_KEY
, NULL as payroll_check_payroll_item_distribution_KEY
, pctid.payroll_check_tax_item_distribution_KEY
, NULL as payroll_bank_transaction_worker_compensation_item_distribution_KEY
, pctid.liability__gl_account_KEY as gl_account_KEY
, 2 /* Credit */ as gl_balance_effect_KEY
, pcti.description as gl_transaction_detail_description
, pctid.amount as bank_transaction_distribution_amount
, NULL as first__w2_1099_box_type_KEY
, NULL as second__w2_1099_box_type_KEY
, ISNULL(cti.sequence_number + CASE
WHEN cti.client_tax_item_type_KEY = 2 /*Employer*/ THEN 60000 /*employerTaxItemSequenceNumberStart*/
WHEN cti.client_tax_item_type_KEY = 1 /* Employee */ THEN 45000 /*employeeTaxItemSequenceNumberStart*/
ELSE 0 /* given WHERE clause, should never happen */
END, 0) AS sequence_number
from
dbo.Payroll_Check_Tax_Item AS pcti
inner join dbo.Client_Tax_Item AS cti
on pcti.client_tax_item_KEY = cti.client_tax_item_KEY
inner join dbo.Payroll_Check_Tax_Item_Distribution AS pctid
on pcti.payroll_check_tax_item_KEY = pctid.payroll_check_tax_item_KEY
where
cti.client_tax_item_type_KEY in (2 /* Employer */, 1 /* Employee */)
UNION ALL
select
pbtwci.bank_transaction_KEY
, NULL as bank_transaction_distribution_KEY
, NULL as payroll_check_payroll_item_distribution_KEY
, NULL as payroll_check_tax_item_distribution_KEY
, pbtwcid.payroll_bank_transaction_worker_compensation_item_distribution_KEY
, pbtwcid.expense__gl_account_KEY AS gl_account_KEY
, 1 /* Debit */ AS gl_balance_effect_KEY
, left(wccc.description, 120) AS gl_transaction_detail_description
, pbtwcid.premium_amount AS bank_transaction_distribution_amount
, NULL AS first__w2_1099_box_type_KEY
, NULL AS second__w2_1099_box_type_KEY
, /*0 +*/ 75000 /*workersCompensationItemSequenceNumberStart*/ AS sequence_number
from
dbo.Payroll_Bank_Transaction_Worker_Compensation_Item AS pbtwci
inner join dbo.Payroll_Bank_Transaction_Worker_Compensation_Item_Distribution as pbtwcid
on pbtwci.payroll_bank_transaction_worker_compensation_item_KEY = pbtwcid.payroll_bank_transaction_worker_compensation_item_KEY
inner join dbo.Worker_Compensation as wc
on pbtwci.worker_compensation_KEY = wc.worker_compensation_KEY
inner join dbo.Worker_Compensation_Class_Code_Detail as wcccd
on wc.worker_compensation_class_code_detail_KEY = wcccd.worker_compensation_class_code_detail_KEY
inner join dbo.Worker_Compensation_Class_Code as wccc
on wcccd.worker_compensation_class_code_KEY = wccc.worker_compensation_class_code_KEY
UNION ALL
select
pbtwci.bank_transaction_KEY
, NULL as bank_transaction_distribution_KEY
, NULL as payroll_check_payroll_item_distribution_KEY
, NULL as payroll_check_tax_item_distribution_KEY
, pbtwcid.payroll_bank_transaction_worker_compensation_item_distribution_KEY
, pbtwcid.liability__gl_account_KEY AS gl_account_KEY
, 2 /* Credit */ AS gl_balance_effect_KEY
, left(wccc.description, 120) AS gl_transaction_detail_description
, pbtwcid.premium_amount AS bank_transaction_distribution_amount
, NULL AS first__w2_1099_box_type_KEY
, NULL AS second__w2_1099_box_type_KEY
, /*0 +*/ 75000 /*workersCompensationItemSequenceNumberStart*/ AS sequence_number
from
dbo.Payroll_Bank_Transaction_Worker_Compensation_Item as pbtwci
inner join dbo.Payroll_Bank_Transaction_Worker_Compensation_Item_Distribution as pbtwcid
on pbtwci.payroll_bank_transaction_worker_compensation_item_KEY = pbtwcid.payroll_bank_transaction_worker_compensation_item_KEY
inner join dbo.Worker_Compensation as wc
on pbtwci.worker_compensation_KEY = wc.worker_compensation_KEY
inner join dbo.Worker_Compensation_Class_Code_Detail as wcccd
on wc.worker_compensation_class_code_detail_KEY = wcccd.worker_compensation_class_code_detail_KEY
inner join dbo.Worker_Compensation_Class_Code as wccc
on wcccd.worker_compensation_class_code_KEY = wccc.worker_compensation_class_code_KEY
;