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