View: dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Tax_Item_Expense_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Tax_Item_Expense_Dr_View AS SELECT -- Unique key glt.gl_transaction_KEY, ledger_entry_type_KEY = 7, /* Payroll Check Tax Item Expense Dr */ pctid.payroll_check_tax_item_distribution_KEY, -- Ledger entry payload tj.client_KEY, glt.journal_KEY, glt.gl_period_KEY, gl_account_KEY = pctid.expense__gl_account_KEY, gl_accounting_method_KEY = NULL, gl_balance_effect_KEY = 1, /* Debit */ gl_transaction_type_KEY = bt.bank_transaction_type_KEY, bt.transaction_status_KEY, gl_transaction_date = bt.transaction_date, gl_transaction_amount = pctid.amount, gl_transaction_description = bt.description, gl_transaction_detail_description = pcti.description, gl_transaction_reference_number = bt.reference_number, gl_transaction_reference_number_sortable = bt.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(11 AS TINYINT), -- GLTransactionSourceExpression.PayrollCheckTaxItemExpense gl_transaction_source_KEY = 2, -- GLTransactionSource.BankTransaction is_activity_journal_entry = CAST(0 AS BIT), journal_entry_type_KEY = 101, /* Regular */ source_was_a_distribution = CAST(1 AS BIT), cti.sequence_number, workpaper_reference = N'' COLLATE Latin1_General_CI_AS, distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS, -- Custom supplemental information bt.checkbook_KEY, pcti.bank_transaction_KEY 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.Bank_Transaction AS bt ON pcti.bank_transaction_KEY = bt.bank_transaction_KEY INNER JOIN dbo.Tax_Jurisdiction AS tj ON cti.tax_jurisdiction_KEY = tj.tax_jurisdiction_KEY INNER JOIN dbo.Client AS c ON tj.client_KEY = c.client_KEY INNER JOIN dbo.GL_Transaction_N_Bank_Transaction AS nbt ON pcti.bank_transaction_KEY = nbt.bank_transaction_KEY INNER JOIN dbo.GL_Transaction AS glt ON nbt.gl_transaction_KEY = glt.gl_transaction_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 bt.bank_transaction_type_KEY = 5 AND cti.client_tax_item_type_KEY = 2 /* Employer */ AND bt.transaction_status_KEY IN (1 /* Live */, 5 /* Unprinted */, 3 /* Voided */, 9 /* ImpoundMemo */, 14 /*ImpoundMemoDeleted*/, 15 /*ImpoundMemoVoided*/) AND ( bt.data_origin_KEY != 2 -- Payroll Check that came from a CSA client with the TBS OR c.csa_client_had_tbs_or_gl_module_marked = 0 -- or GL module marked do not affect accounting. ) AND glt.gl_period_KEY NOT IN (SELECT gl_period_KEY FROM dbo.Non_Posting_Payroll_Transaction_GL_Period) ; | |||