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