View: dbo.Vendor_Activity_Transaction_View
View definition
-- drop view dbo.Vendor_Activity_Transaction_View
create view dbo.Vendor_Activity_Transaction_View
as
(
select
txn.vendor_KEY
, glt.journal_KEY
, glt.gl_period_KEY
, txn.gl_transaction_KEY
, txn.bank_transaction_KEY
, txn.payable_transaction_KEY
, txn.gl_transaction_type_KEY
, txn.transaction_status_KEY
, txn.transaction_date
, txn.reference_number
, txn.description
, txn.amount as gross_amount
, txn.memo
-- Payable-specific information
, txn.discount_allowed_amount
, txn.not_eligible_for_discount_allowed_amount
, txn.discount_expiration_date
, txn.due_date
, txn.payable__gl_account_KEY
, txn.purchase_order_number
, txn.payment_term_KEY
from
(
-- Bring in all the bank transactions associated with a vendor.
select
vbt.vendor_KEY
, gltnbt.gl_transaction_KEY
, bt.bank_transaction_KEY
, cast(null as int) as payable_transaction_KEY
, bt.bank_transaction_type_KEY as gl_transaction_type_KEY
, bt.transaction_status_KEY
, bt.transaction_date
, bt.reference_number
, bt.description
, bt.amount
, bt.memo
-- Payable-specific information
, cast(null as decimal(17,2)) as discount_allowed_amount
, cast(null as decimal(17,2)) as not_eligible_for_discount_allowed_amount
, cast(null as smalldatetime) as discount_expiration_date
, cast(null as smalldatetime) as due_date
, cast(null as int) as payable__gl_account_KEY
, cast(null as nvarchar(80)) collate Latin1_General_CI_AS as purchase_order_number
, cast(null as int) as payment_term_KEY
from
dbo.Vendor_Bank_Transaction as vbt
inner join dbo.Bank_Transaction as bt
on vbt.bank_transaction_KEY = bt.bank_transaction_KEY
left join dbo.GL_Transaction_N_Bank_Transaction as gltnbt
on bt.bank_transaction_KEY = gltnbt.bank_transaction_KEY
UNION
-- Bring in all liabilities.
select
pt.vendor_KEY
, gltnpt.gl_transaction_KEY
, cast(null as int) as bank_transaction_KEY
, pt.payable_transaction_KEY
, pt.payable_transaction_type_KEY as gl_transaction_type_KEY
, pt.transaction_status_KEY
, pt.transaction_date
, isnull(case when (pt.payable_transaction_type_KEY IN (201, 202, 203) and pt.description is not null)
-- 201: PayrollLiability, 202: BillingLiability, 203: PayrollTaxAdjustmentLiability
then left(pt.description, 16)
else pt.reference_number
end, '')
as reference_number
, pt.description
, pt.amount
, pt.memo
-- Payable-specific information
, pt.discount_allowed_amount
, pt.not_eligible_for_discount_allowed_amount
, pt.discount_expiration_date
, pt.due_date
, pt.payable__gl_account_KEY
, pt.purchase_order_number
, pt.payment_term_KEY
from
dbo.Payable_Transaction as pt
left join dbo.GL_Transaction_N_Payable_Transaction as gltnpt
on pt.payable_transaction_KEY = gltnpt.payable_transaction_KEY
) as txn
left join dbo.GL_Transaction as glt
on txn.gl_transaction_KEY = glt.gl_transaction_KEY
)