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