• Replies 3 replies
  • Subscribers 336 subscribers
  • Views 99 views
  • Users 0 members are here
Related

Output Element for On Account Funds

Ashley Elliott (Past Member)
$organization

** self-hosted organization**

Is is possible to create an output element that pulls on account funds? I have created them for attributes in the past, but we don't want this to be an attribute. My coworkers just want to create an output set that has this as a field.

We have custom constituent header that shows on account funds (created by my predecessor), and I bet my coworkers think this is a field somewhere, but I dug into it and found that works off of a local table function. I might be able to take some sections of code that, but it isn't going to help me with creating this output element. 

Does anyone have any advice?

Ashley Elliott

Database Administrator

St. Louis Symphony Orchestra

314-286-4198

ashleye@slso.org

Parents
  • $organization

    We use this for our flex header:

    select
    ISNULL((-1 * SUM(p.pmt_amt)), 0)
    from
    T_PAYMENT as p
    inner join (
    select value as pmt_method
    from dbo.LFT_CPSMA_SPLIT_BIGINT_LIST(ISNULL(DBO.FS_GET_DEFAULT_VALUE(null, 'CPSMA', 'Flex Header DEVO ONAC Types'), ''), ','))
    as pm on pm.pmt_method = p.pmt_method
    where
    p.customer_no = @customer_no
    group by
    p.customer_no

    It's using a list of specific payment methods from T_DEFAULTS, but you can also just filter on type=2 (On Account).

    Here's a more generic one:

    Select NULLIF((-1 * SUM(pmt_amt)),0) From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where a.customer_no = @customer_no and b.pmt_type = 2

Reply
  • $organization

    We use this for our flex header:

    select
    ISNULL((-1 * SUM(p.pmt_amt)), 0)
    from
    T_PAYMENT as p
    inner join (
    select value as pmt_method
    from dbo.LFT_CPSMA_SPLIT_BIGINT_LIST(ISNULL(DBO.FS_GET_DEFAULT_VALUE(null, 'CPSMA', 'Flex Header DEVO ONAC Types'), ''), ','))
    as pm on pm.pmt_method = p.pmt_method
    where
    p.customer_no = @customer_no
    group by
    p.customer_no

    It's using a list of specific payment methods from T_DEFAULTS, but you can also just filter on type=2 (On Account).

    Here's a more generic one:

    Select NULLIF((-1 * SUM(pmt_amt)),0) From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where a.customer_no = @customer_no and b.pmt_type = 2

Children
No Data