View: dbo.AP_Payable_Distribution_View
View definition
CREATE VIEW dbo.AP_Payable_Distribution_View
AS
select
pt.payable_transaction_KEY
, pt.discount_expiration_date
, pt.is_discount_forced
, ptd.payable_transaction_distribution_KEY
, ptd.gl_account_KEY
, ptd.description
, ptd.amount
, ptd.discount_allowed_amount
, ptd.sequence_number
, ptd.is_dedicated_discount_distribution
, ptd.first__w2_1099_box_type_KEY
, ptd.second__w2_1099_box_type_KEY
, ptd.note
, sum(isnull(ppapd.distributed__paid_amount,0)) as paid_amount
, sum(isnull(ppapd.distributed__discount_taken_amount,0)) as discount_taken_amount
, (ptd.discount_allowed_amount - sum(isnull(ppapd.distributed__discount_taken_amount,0))) as discount_unused_amount
, (ptd.amount
- sum(isnull(ppapd.distributed__paid_amount,0))
- sum(isnull(ppapd.distributed__discount_taken_amount,0))) as gross_open_balance_amount
, W2BT.w2_1099_type_KEY as w2_1099_type_KEY
from
dbo.Payable_Transaction as pt
inner join dbo.Payable_Transaction_Distribution_View as ptd
on pt.payable_transaction_KEY = ptd.payable_transaction_KEY
left join dbo.Payable_Payment_Application_Payable_Distribution as ppapd
on ptd.payable_transaction_distribution_KEY = ppapd.payable_transaction_distribution_KEY
left join dbo.W2_1099_Box_Type as W2BT
on ptd.first__w2_1099_box_type_KEY = W2BT.w2_1099_box_type_KEY
where
pt.payable_transaction_type_KEY = 204 -- PayableLiability
group by
pt.payable_transaction_KEY
, pt.discount_expiration_date
, pt.is_discount_forced
, ptd.payable_transaction_distribution_KEY
, ptd.gl_account_KEY
, ptd.description
, ptd.amount
, ptd.discount_allowed_amount
, ptd.sequence_number
, ptd.is_dedicated_discount_distribution
, ptd.first__w2_1099_box_type_KEY
, ptd.second__w2_1099_box_type_KEY
, ptd.note
, W2BT.w2_1099_type_KEY
;