View: dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Payroll_Item_Expense_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Payroll_Item_Expense_Dr_View AS SELECT -- Unique key glt.gl_transaction_KEY, ledger_entry_type_KEY = 5, /* Payroll Check Payroll Item Expense Dr */ pcpid.payroll_check_payroll_item_distribution_KEY, -- Ledger entry payload cpi.client_KEY, glt.journal_KEY, glt.gl_period_KEY, gl_account_KEY = pcpid.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 = pcpid.amount, gl_transaction_description = bt.description, gl_transaction_detail_description = pcpi.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(9 AS TINYINT), -- GLTransactionSourceExpression.PayrollCheckPayrollItemExpense 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), cpi.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, pcpi.bank_transaction_KEY 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.Bank_Transaction AS bt ON pcpi.bank_transaction_KEY = bt.bank_transaction_KEY INNER JOIN dbo.Client AS c ON cpi.client_KEY = c.client_KEY INNER JOIN dbo.GL_Transaction_N_Bank_Transaction AS nbt ON pcpi.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_Payroll_Item_Distribution AS pcpid ON pcpi.payroll_check_payroll_item_KEY = pcpid.payroll_check_payroll_item_KEY WHERE bt.bank_transaction_type_KEY = 5 AND cpi.payroll_item_type_KEY IN (1 /*Pay*/, 3 /*Employee [sic: S/B Employer] Contribution*/) AND ( pcpi.is_excluded_net_pay = 0 OR cpi.payroll_item_type_KEY = 3 /* EmployerContribution */ ) 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) ; | |||