View: dbo.Customer_Activity_Transaction_View
View definition
-- drop view dbo.Customer_Activity_Transaction_View
create view dbo.Customer_Activity_Transaction_View
as
select
txn.customer_KEY
, glt.journal_KEY
, glt.gl_period_KEY
, txn.gl_transaction_KEY -- Unique key for the view.
, txn.gl_transaction_type_KEY
, txn.transaction_status_KEY
, txn.transaction_date
, txn.reference_number
, txn.reference_number_sortable
, txn.amount
-- ARTransaction-specific information
, txn.memo
, txn.payment_discount_amount
, txn.payment_discount_expiration_date
, txn.due_date
, txn.ar__gl_account_KEY
, txn.purchase_order_number
, txn.purchase_order_number_sortable
, txn.payment_term_KEY
-- ARPayment-specific information
, txn.original_customer_name
, txn.ar_deposit__bank_transaction_KEY
, txn.unearned_revenue__gl_account_KEY
, txn.payment__gl_account_KEY
from
dbo.GL_Transaction as glt
inner join
(
select
customer_KEY
, gl_transaction_KEY
, ar_transaction_type_KEY as gl_transaction_type_KEY
, transaction_status_KEY
, transaction_date
, reference_number
, reference_number_sortable
, amount
-- ARTransaction-specific information
, message AS memo
, payment_discount_amount
, payment_discount_expiration_date
, due_date
, gl_account_KEY as ar__gl_account_KEY
, purchase_order_number
, purchase_order_number_sortable
, payment_term_KEY
-- ARPayment-specific information
, cast(null as nvarchar(50)) collate Latin1_General_CI_AS as original_customer_name
, null as ar_deposit__bank_transaction_KEY
, null as unearned_revenue__gl_account_KEY
, null as payment__gl_account_KEY
from
dbo.AR_Transaction_View
UNION ALL
select
customer_KEY
, gl_transaction_KEY
, ar_payment_type_KEY as gl_transaction_type_KEY
, transaction_status_KEY
, transaction_date
, reference_number
, reference_number_sortable
, amount
-- ARTransaction-specific information
, cast(null as nvarchar(240)) collate Latin1_General_CI_AS as memo
, cast(null as decimal(17,2)) as payment_discount_amount
, cast(null as smalldatetime) as payment_discount_expiration_date
, cast(null as smalldatetime) as due_date
, null as ar__gl_account_KEY
, cast(null as nvarchar(16)) collate Latin1_General_CI_AS as purchase_order_number
, cast(null as nvarchar(80)) collate Latin1_General_CI_AS as purchase_order_number_sortable
, null as payment_term_KEY
-- ARPayment-specific information
, customer_name as original_customer_name
, ar_deposit__bank_transaction_KEY
, unearned_revenue__gl_account_KEY
, undeposited_payment__gl_account_KEY as payment__gl_account_KEY
from
dbo.AR_Payment
) as txn
on glt.gl_transaction_KEY = txn.gl_transaction_KEY
;