View: dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Worker_Compensation_Expense_Dr_View
View definition
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Worker_Compensation_Expense_Dr_View
AS
SELECT
-- Unique key
glt.gl_transaction_KEY,
ledger_entry_type_KEY = 9, /* Payroll Check Worker Compensation Expense Dr */
pbtwcid.payroll_bank_transaction_worker_compensation_item_distribution_KEY,
-- Ledger entry payload
wc.client_KEY,
glt.journal_KEY,
glt.gl_period_KEY,
gl_account_KEY = pbtwcid.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 = pbtwcid.premium_amount,
gl_transaction_description = bt.description,
gl_transaction_detail_description = LEFT(wccc.description, 120),
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(13 AS TINYINT), -- GLTransactionSourceExpression.PayrollCheckWorkerCompensationExpense
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),
sequence_number = 0,
workpaper_reference = N'' COLLATE Latin1_General_CI_AS,
distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS,
-- Custom supplemental information
bt.checkbook_KEY,
pbtwci.bank_transaction_KEY
FROM
dbo.Payroll_Bank_Transaction_Worker_Compensation_Item AS pbtwci
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
INNER JOIN
dbo.Bank_Transaction AS bt
ON pbtwci.bank_transaction_KEY = bt.bank_transaction_KEY
INNER JOIN
dbo.Client AS c
ON wc.client_KEY = c.client_KEY
INNER JOIN
dbo.GL_Transaction_N_Bank_Transaction AS nbt
ON pbtwci.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_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
WHERE
bt.bank_transaction_type_KEY = 5
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)
;