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