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