View: dbo.AP_Payment_Distribution_View
View definition
CREATE VIEW dbo.AP_Payment_Distribution_View
AS
select
glt.gl_transaction_KEY
, bt.bank_transaction_KEY
, btd.bank_transaction_distribution_KEY
, pt.payable_transaction_KEY
, ptd.payable_transaction_distribution_KEY
, isnull(btd.gl_account_KEY, ptd.gl_account_KEY) as gl_account_KEY
, isnull(btd.description, ptd.description) as description
, isnull(btd.amount, ptd.amount) as amount
, isnull(btd.sequence_number, ptd.sequence_number) as sequence_number
, isnull(btd.first__w2_1099_box_type_KEY, ptd.first__w2_1099_box_type_KEY) as first__w2_1099_box_type_KEY
, isnull(btd.second__w2_1099_box_type_KEY, ptd.second__w2_1099_box_type_KEY) as second__w2_1099_box_type_KEY
, sum(coalesce(bt_ppapd.distributed__paid_amount,cm_ppapd.distributed__paid_amount,0)) as used_payment_amount
, (isnull(btd.amount, ptd.amount)
- sum(coalesce(bt_ppapd.distributed__paid_amount,cm_ppapd.distributed__paid_amount,0))) as unused_payment_amount
from
dbo.GL_Transaction as glt
left join dbo.GL_Transaction_N_Bank_Transaction as gltnbt
on glt.gl_transaction_KEY = gltnbt.gl_transaction_KEY
left join dbo.Bank_Transaction as bt
on gltnbt.bank_transaction_KEY = bt.bank_transaction_KEY
left join dbo.Bank_Transaction_Distribution as btd
on bt.bank_transaction_KEY = btd.bank_transaction_KEY
left join dbo.Payable_Payment_Application_Payment_Distribution as bt_ppapd
on btd.bank_transaction_distribution_KEY = bt_ppapd.bank_transaction_distribution_KEY
left join dbo.GL_Transaction_N_Payable_Transaction as gltnpt
on glt.gl_transaction_KEY = gltnpt.gl_transaction_KEY
left join dbo.Payable_Transaction as pt
on gltnpt.payable_transaction_KEY = pt.payable_transaction_KEY
left join dbo.Payable_Transaction_Distribution as ptd
on pt.payable_transaction_KEY = ptd.payable_transaction_KEY
left join dbo.Payable_Payment_Application_Payment_Distribution as cm_ppapd
on ptd.payable_transaction_distribution_KEY = cm_ppapd.payable_transaction_distribution_KEY
where
(bt.bank_transaction_KEY is not null and bt.bank_transaction_type_KEY in (10,11)) -- AP Check, AP Payment
or
(pt.payable_transaction_KEY is not null and pt.payable_transaction_type_KEY = 205) -- Credit Memo
group by
glt.gl_transaction_KEY
, bt.bank_transaction_KEY
, btd.bank_transaction_distribution_KEY
, pt.payable_transaction_KEY
, ptd.payable_transaction_distribution_KEY
, btd.gl_account_KEY, ptd.gl_account_KEY
, btd.description, ptd.description
, btd.amount, ptd.amount
, btd.sequence_number, ptd.sequence_number
, btd.first__w2_1099_box_type_KEY, ptd.first__w2_1099_box_type_KEY
, btd.second__w2_1099_box_type_KEY, ptd.second__w2_1099_box_type_KEY
;