View: dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Tax_Item_Liability_Cr_View
View definition
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Payroll_Check_Tax_Item_Liability_Cr_View
AS
SELECT
-- Unique key
glt.gl_transaction_KEY,
ledger_entry_type_KEY = 8, /* Payroll Check Tax Item Liability Cr */
pctid.payroll_check_tax_item_distribution_KEY,
-- Ledger entry payload
tj.client_KEY,
glt.journal_KEY,
glt.gl_period_KEY,
gl_account_KEY = pctid.liability__gl_account_KEY,
gl_accounting_method_KEY = NULL,
gl_balance_effect_KEY = 2, /* Credit */
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(12 AS TINYINT), -- GLTransactionSourceExpression.PayrollCheckTaxItemLiability
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.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.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.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 IN (2 /* Employer */, 1 /* Employee */)
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)
;