View: dbo.Etl_Helper_Ledger_Entry_Payable_Transaction_Ap_Cr_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Payable_Transaction_Ap_Cr_Dr_View AS SELECT -- Unique key gltnpt.gl_transaction_KEY, ledger_entry_type_KEY = 11, /*Payable Transaction AP Cr Dr*/ -- Ledger entry payload v.client_KEY, glt.journal_KEY, glt.gl_period_KEY, gl_account_KEY = pt.payable__gl_account_KEY, gl_accounting_method_KEY = 1, /*Accrual*/ gl_balance_effect_KEY = CASE pt.payable_transaction_type_KEY WHEN 204 /*Payable Liability*/ THEN 2 /*Credit*/ WHEN 205 /*Credit Memo*/ THEN 1 /*Debit*/ END, gl_transaction_type_KEY = pt.payable_transaction_type_KEY, transaction_status_KEY = 1, /*Live*/ gl_transaction_date = pt.transaction_date, gl_transaction_amount = pt.amount, gl_transaction_description = v.vendor_name, gl_transaction_detail_description = v.vendor_name, gl_transaction_reference_number = pt.reference_number, gl_transaction_reference_number_sortable = pt.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(5 AS TINYINT), -- GLTransactionSourceExpression.PayableTransaction gl_transaction_source_KEY = 4, /*PayableTransaction*/ is_activity_journal_entry = CAST(0 AS BIT), journal_entry_type_KEY = 101, /*Journal entry*/ source_was_a_distribution = CAST(0 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 pt.vendor_KEY, pt.payable_transaction_KEY FROM dbo.Payable_Transaction AS pt INNER JOIN dbo.GL_Transaction_N_Payable_Transaction AS gltnpt ON gltnpt.payable_transaction_KEY = pt.payable_transaction_KEY INNER JOIN dbo.GL_Transaction AS glt ON glt.gl_transaction_KEY = gltnpt.gl_transaction_KEY INNER JOIN dbo.Vendor AS v ON v.vendor_KEY = pt.vendor_KEY WHERE pt.payable_transaction_type_KEY IN (204 /* Payable Liability */, 205 /* Credit Memo */) AND pt.transaction_status_KEY IN (10 /* Open */, 11 /* Paid */) AND pt.payable_transaction_KEY NOT IN (SELECT payable_transaction_KEY FROM dbo.Vendor_AP_Beginning_Balance) -- Historical payables are not posted. ; | |||