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