View: dbo.Etl_Helper_Ledger_Entry_Payable_Transaction_Distribution_Cr_Dr_View
View definition
CREATE VIEW dbo.Etl_Helper_Ledger_Entry_Payable_Transaction_Distribution_Cr_Dr_View
AS
SELECT
-- Unique key
gltnpt.gl_transaction_KEY,
ledger_entry_type_KEY = 12, /*Payable Transaction Distribution Cr Dr*/
ptd.payable_transaction_distribution_KEY,
-- Ledger entry payload
v.client_KEY,
glt.journal_KEY,
glt.gl_period_KEY,
ptd.gl_account_KEY,
gl_accounting_method_KEY = 1, /*Accrual*/
gl_balance_effect_KEY =
CASE pt.payable_transaction_type_KEY
WHEN 204 /*Payable Liability*/ THEN 1 /*Debit*/
WHEN 205 /*Credit Memo*/ THEN 2 /*Credit*/
END,
gl_transaction_type_KEY = pt.payable_transaction_type_KEY,
transaction_status_KEY = 1, /*Live*/
gl_transaction_date = pt.transaction_date,
gl_transaction_amount = ptd.amount,
gl_transaction_description = v.vendor_name,
gl_transaction_detail_description = ptd.description,
gl_transaction_reference_number = pt.reference_number,
gl_transaction_reference_number_sortable = pt.reference_number_sortable,
-- Standard supplemental information
gl_transaction_source_expression_KEY = CAST(5 AS TINYINT), -- GLTransactionSourceExpression.PayableTransaction
gl_transaction_source_KEY = 4, /*PayableTransaction*/
is_activity_journal_entry = CAST(0 AS BIT),
journal_entry_type_KEY = 101, /*Journal entry*/
source_was_a_distribution = CAST(1 AS BIT),
ptd.sequence_number,
workpaper_reference = N'' COLLATE Latin1_General_CI_AS,
distribution_workpaper_reference = N'' COLLATE Latin1_General_CI_AS,
-- Custom supplemental information
pt.vendor_KEY,
pt.payable_transaction_KEY
FROM
dbo.Payable_Transaction AS pt
INNER JOIN
dbo.GL_Transaction_N_Payable_Transaction AS gltnpt
ON gltnpt.payable_transaction_KEY = pt.payable_transaction_KEY
INNER JOIN
dbo.GL_Transaction AS glt
ON glt.gl_transaction_KEY = gltnpt.gl_transaction_KEY
INNER JOIN
dbo.Vendor AS v
ON v.vendor_KEY = pt.vendor_KEY
INNER JOIN
dbo.Payable_Transaction_Distribution AS ptd
ON ptd.payable_transaction_KEY = pt.payable_transaction_KEY
WHERE
pt.payable_transaction_type_KEY IN (204 /* Payable Liability */,
205 /* Credit Memo */)
AND pt.transaction_status_KEY IN (10 /* Open */,
11 /* Paid */)
AND pt.payable_transaction_KEY NOT IN (SELECT payable_transaction_KEY FROM dbo.Vendor_AP_Beginning_Balance) -- Historical payables are not posted.
;