View: dbo.Etl_Helper_Ledger_Entry_Ar_Transaction_Ar_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Ar_Transaction_Ar_Dr_View AS SELECT -- Unique key artv.gl_transaction_KEY, ledger_entry_type_KEY = 19 /* AR Transaction AR Dr */, -- Ledger entry payload cst.client_KEY, glt.journal_KEY, glt.gl_period_KEY, artv.gl_account_KEY, gl_accounting_method_KEY = 1 /* Accrual */, gl_balance_effect_KEY = 1 /* Debit */, gl_transaction_type_KEY = artv.ar_transaction_type_KEY, transaction_status_KEY = 1 /*Live*/, gl_transaction_date = artv.transaction_date, gl_transaction_amount = CASE WHEN artv.ar_transaction_type_KEY = 502 /* Credit Memo */ THEN -artv.amount ELSE artv.amount END, gl_transaction_description = cst.customer_name, gl_transaction_detail_description = cst.customer_name, gl_transaction_reference_number = artv.reference_number, gl_transaction_reference_number_sortable = artv.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(3 AS TINYINT), -- GLTransactionSourceExpression.ARTransaction gl_transaction_source_KEY = 6 /*ARTransaction*/, 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 artv.customer_KEY FROM dbo.AR_Transaction_View AS artv INNER JOIN dbo.GL_Transaction AS glt ON glt.gl_transaction_KEY = artv.gl_transaction_KEY INNER JOIN dbo.Customer AS cst ON cst.customer_KEY = artv.customer_KEY WHERE artv.transaction_status_KEY = 1 -- Live -- Exempt beginning balance (historical) AR transactions. AND artv.gl_transaction_KEY NOT IN (SELECT gl_transaction_KEY FROM dbo.Customer_Historical_AR_Transaction) ; | |||