View: dbo.Etl_Helper_Ledger_Entry_Ar_Transaction_Item_Cr_Dr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Ar_Transaction_Item_Cr_Dr_View AS SELECT -- Unique key art.gl_transaction_KEY, ledger_entry_type_KEY = 20 /* AR Transaction Item Cr Dr */, artigla.ar_transaction_item_gl_account_KEY, -- Ledger entry payload cst.client_KEY, glt.journal_KEY, glt.gl_period_KEY, artigla.gl_account_KEY, gl_accounting_method_KEY = 1 /* Accrual */, gl_balance_effect_KEY = CASE WHEN artigla.ar_transaction_item_gl_account_type_KEY = 2 /* Cost of Goods Sold */ THEN 1 /* Debit */ ELSE 2 /* Credit */ END, gl_transaction_type_KEY = art.ar_transaction_type_KEY, transaction_status_KEY = 1 /*Live*/, gl_transaction_date = art.transaction_date, gl_transaction_amount = CASE WHEN artigla.ar_transaction_item_gl_account_type_KEY = 4 /* Additional Tax */ THEN CASE WHEN art.ar_transaction_type_KEY = 502 /* Credit Memo */ THEN -arti.additional_tax_amount ELSE arti.additional_tax_amount END WHEN artigla.ar_transaction_item_gl_account_type_KEY IN (2 /* Cost of Goods Sold */, 3 /* Inventory */) THEN CASE WHEN art.ar_transaction_type_KEY = 502 /* Credit Memo */ THEN -arti.cost_amount ELSE arti.cost_amount END ELSE CASE WHEN art.ar_transaction_type_KEY = 502 /* Credit Memo */ THEN -arti.amount ELSE arti.amount END END, gl_transaction_description = cst.customer_name, gl_transaction_detail_description = cst.customer_name, gl_transaction_reference_number = art.reference_number, gl_transaction_reference_number_sortable = art.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(1 AS BIT), sequence_number = arti.sequence_number, workpaper_reference = N'' COLLATE Latin1_General_CI_AS, distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS, -- Custom supplemental information art.customer_KEY, arti.ar_transaction_item_KEY FROM dbo.AR_Transaction AS art INNER JOIN dbo.GL_Transaction AS glt ON glt.gl_transaction_KEY = art.gl_transaction_KEY INNER JOIN dbo.Customer AS cst ON cst.customer_KEY = art.customer_KEY INNER JOIN dbo.AR_Transaction_Item AS arti ON art.gl_transaction_KEY = arti.gl_transaction_KEY INNER JOIN dbo.AR_Transaction_Item_GL_Account AS artigla ON arti.ar_transaction_item_KEY = artigla.ar_transaction_item_KEY WHERE art.transaction_status_KEY = 1 -- Live -- Exempt beginning balance (historical) AR transactions. AND art.gl_transaction_KEY NOT IN (SELECT gl_transaction_KEY FROM dbo.Customer_Historical_AR_Transaction) ; | |||