View: dbo.Etl_Helper_Ledger_Entry_Ar_Payment_Unearned_Revenue_Cr_View | |||
View definition | |||
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Ar_Payment_Unearned_Revenue_Cr_View AS SELECT -- Unique key arp.gl_transaction_KEY, ledger_entry_type_KEY = 22 /* AR Payment Unearned Revenue Cr */, -- Ledger entry payload j.client_KEY, glt.journal_KEY, glt.gl_period_KEY, gl_account_KEY = arp.unearned_revenue__gl_account_KEY, gl_accounting_method_KEY = CASE WHEN arp.ar_payment_type_KEY = 553 /* Write off */ THEN 1 /* Accrual */ ELSE NULL /* Both Accrual & Cash Basis*/ END, gl_balance_effect_KEY = 2 /* Credit */, gl_transaction_type_KEY = arp.ar_payment_type_KEY, transaction_status_KEY = 1 /*Live*/, gl_transaction_date = arp.transaction_date, gl_transaction_amount = arp.amount - ISNULL(applied_at_payment_creation_amount, 0), gl_transaction_description = arp.customer_name, gl_transaction_detail_description = arp.customer_name, gl_transaction_reference_number = arp.reference_number, gl_transaction_reference_number_sortable = arp.reference_number_sortable, -- Standard supplemental information gl_transaction_source_expression_KEY = CAST(2 AS TINYINT), -- GLTransactionSourceExpression.ARPayment gl_transaction_source_KEY = 7 /*ARPayment*/, 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 = 0, workpaper_reference = N'' COLLATE Latin1_General_CI_AS, distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS, -- Custom supplemental information arp.customer_KEY FROM dbo.AR_Payment AS arp INNER JOIN dbo.GL_Transaction AS glt ON glt.gl_transaction_KEY = arp.gl_transaction_KEY INNER JOIN dbo.Journal AS j ON j.journal_KEY = glt.journal_KEY LEFT JOIN ( SELECT arpav.payment__gl_transaction_KEY, SUM(arpav.paid_amount) AS applied_at_payment_creation_amount FROM dbo.AR_Payment_Application_View AS arpav WHERE arpav.was_applied_at_payment_creation = 1 GROUP BY arpav.payment__gl_transaction_KEY ) AS arpa ON arp.gl_transaction_KEY = arpa.payment__gl_transaction_KEY WHERE arp.transaction_status_KEY = 1 -- Live AND arp.amount - ISNULL(applied_at_payment_creation_amount, 0) <> 0 -- Only create an unearned revenue entry if there is actually unearned revenue! ; | |||