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