View: dbo.Etl_Helper_Ledger_Entry_Ar_Payment_Application_Distribution_Cr_Dr_View
View definition
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Ar_Payment_Application_Distribution_Cr_Dr_View
AS
SELECT
-- Unique key
arpa.gl_transaction_KEY,
ledger_entry_type_KEY = 26 /* AR Payment Application Distribution Cr Dr */,
arpad.ar_payment_application_distribution_KEY,
artigla.ar_transaction_item_gl_account_KEY,
-- PayloadLedger entry payload
j.client_KEY,
glt.journal_KEY,
glt.gl_period_KEY,
artigla.gl_account_KEY,
gl_accounting_method_KEY = 2 /* Cash Basis */,
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 = 570 /*AR payment application*/,
1 /*Live*/ AS transaction_status_KEY,
gl_transaction_date = arpa.application_date,
gl_transaction_amount = arpad.distributed__paid_amount + distributed__discount_honored_amount,
gl_transaction_description = arpa.description,
gl_transaction_detail_description = arpa.description,
gl_transaction_reference_number = arpa.reference_number,
gl_transaction_reference_number_sortable = arpa.reference_number_sortable,
-- Standard supplemental information
gl_transaction_source_expression_KEY = CAST(1 AS TINYINT), -- GLTransactionSourceExpression.ARPaymentApplication
gl_transaction_source_KEY = 8 /*ARPaymentApplication*/,
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
art.customer_KEY,
artigla.ar_transaction_item_KEY
FROM
dbo.AR_Payment_Application_View AS arpa
LEFT JOIN
dbo.AR_Payment AS arp
ON arpa.payment__gl_transaction_KEY = arp.gl_transaction_KEY
INNER JOIN
dbo.GL_Transaction AS glt
ON arpa.gl_transaction_KEY = glt.gl_transaction_KEY
INNER JOIN
dbo.Journal AS j
ON j.journal_KEY = glt.journal_KEY
INNER JOIN
dbo.AR_Payment_Application_Distribution AS arpad
ON arpa.gl_transaction_KEY = arpad.payment_application__gl_transaction_KEY
INNER JOIN
dbo.AR_Transaction_Item_GL_Account AS artigla
ON arpad.ar_transaction_item_gl_account_KEY = artigla.ar_transaction_item_gl_account_KEY
INNER JOIN
dbo.AR_Transaction_Item AS arti
ON artigla.ar_transaction_item_KEY = arti.ar_transaction_item_KEY
INNER JOIN
dbo.AR_Transaction AS art
ON arti.gl_transaction_KEY = art.gl_transaction_KEY
WHERE arp.ar_payment_type_KEY IN (550 /* Check */,
551 /* Cash */,
552 /* Credit Card */) -- IN() implicitly provides NOT NULL
;