• Replies 13 replies
  • Subscribers 337 subscribers
  • Views 186 views
  • Users 0 members are here
Related

~Custom order confirmations & acknowledgements

Former Member
Former Member $organization

Surely someone out there can share their custom LP_ORDER_ACK? I'd be looking for the Infomaker form piece as well.

I'm looking to add seating information to the order acknowledgements the Box Office prints/emails.

Come on and help me out! Pullllllezzzzzzz!

Parents
  • $organization

    Parnell,

    It's been so long I'm a bit fuzzy about this, but I think I created a nested report in infomaker to give the detail. Here is the SQL code for that nested (sub) report. I hope this helps.

    Christian

     

    ALTER  proc [dbo].[lp_tech_order_ack_

    detail]

    @order_no int

    -- exec [dbo].[lp_tech_order_ack_detail] 132567

    -- grant exec on lp_tech_order_ack_detail to impusers


    as

    select     max(s.due_amt) as total_per_ticket,
        sum(s.due_amt) as total_per_line,
        s.price_type,
        y.description as price_type_desc,
        s.perf_no,
        s.zone_no,
        z.description,
        d.section,
        n.print_desc as section_print_desc,
        b.base_price,
        f.perf_dt,
        i.description as perf_desc,
        f.perf_code,
        count(*) as num_seats,
        s.order_no,
        max(case when d.smap_no = 4 then z.description
            when d.smap_no= 3 then o.custom_3
    else right(convert(varchar,f.perf_dt,100),7) end) as start_time

    from     t_sub_lineitem s
        /*JOIN (    select e.*
            from t_sli_detail e join t_pmap p on e.pmap_no = p.pmap_no
            where p.price_category = 1) e on s.sli_no = e.sli_no*/
           
        join t_seat d on s.seat_no = d.seat_no
        join tr_price_type y on s.price_type = y.id
        join t_zone z on  s.zone_no = z.zone_no
        join tr_section n on d.section = n.id
        join (    select c.zone_no,sum(price) as base_price,p.perf_no
            from     tx_perf_pmap p
                join t_pmap m on p.pmap_no = m.pmap_no
                join t_zmap z on m.zmap_no = z.zmap_no
                join t_zone e on z.zmap_no = e.zmap_no
                join t_subprice c on c.zmap_no = e.zmap_no and c.zone_no = e.zone_no and c.pmap_no = m.pmap_no

            where     p.base_ind = 'Y' /*and m.price_category=1*/

            group by c.zone_no,p.perf_no) b on s.perf_no = b.perf_no and s.zone_no = b.zone_no
        join t_perf f on s.perf_no = f.perf_no
        join t_inventory i on f.perf_no = i.inv_no
        join t_order o on s.order_no=o.order_no
    where     o.order_no=@order_no
    and     sli_status in (2,3,12)

    group by     s.due_amt,
            s.price_type,
            s.perf_no,
            s.zone_no,
            d.section,
            y.description,
            z.description,
            n.print_desc,
            b.base_price,
            f.perf_code,
            f.perf_dt,
            i.description,
            s.order_no

    /*
    select right(convert(varchar,perf_dt,100),7),perf_dt
    from t_perf
    */

    /*
    select o.order_no from tx_perf_seat s join t_seat d on s.seat_no = s.seat_no
    join t_order o on s.order_no = o.order_no
    where smap_no=4
    and o.mos=4

    select * from t_smap
    */
Reply
  • $organization

    Parnell,

    It's been so long I'm a bit fuzzy about this, but I think I created a nested report in infomaker to give the detail. Here is the SQL code for that nested (sub) report. I hope this helps.

    Christian

     

    ALTER  proc [dbo].[lp_tech_order_ack_

    detail]

    @order_no int

    -- exec [dbo].[lp_tech_order_ack_detail] 132567

    -- grant exec on lp_tech_order_ack_detail to impusers


    as

    select     max(s.due_amt) as total_per_ticket,
        sum(s.due_amt) as total_per_line,
        s.price_type,
        y.description as price_type_desc,
        s.perf_no,
        s.zone_no,
        z.description,
        d.section,
        n.print_desc as section_print_desc,
        b.base_price,
        f.perf_dt,
        i.description as perf_desc,
        f.perf_code,
        count(*) as num_seats,
        s.order_no,
        max(case when d.smap_no = 4 then z.description
            when d.smap_no= 3 then o.custom_3
    else right(convert(varchar,f.perf_dt,100),7) end) as start_time

    from     t_sub_lineitem s
        /*JOIN (    select e.*
            from t_sli_detail e join t_pmap p on e.pmap_no = p.pmap_no
            where p.price_category = 1) e on s.sli_no = e.sli_no*/
           
        join t_seat d on s.seat_no = d.seat_no
        join tr_price_type y on s.price_type = y.id
        join t_zone z on  s.zone_no = z.zone_no
        join tr_section n on d.section = n.id
        join (    select c.zone_no,sum(price) as base_price,p.perf_no
            from     tx_perf_pmap p
                join t_pmap m on p.pmap_no = m.pmap_no
                join t_zmap z on m.zmap_no = z.zmap_no
                join t_zone e on z.zmap_no = e.zmap_no
                join t_subprice c on c.zmap_no = e.zmap_no and c.zone_no = e.zone_no and c.pmap_no = m.pmap_no

            where     p.base_ind = 'Y' /*and m.price_category=1*/

            group by c.zone_no,p.perf_no) b on s.perf_no = b.perf_no and s.zone_no = b.zone_no
        join t_perf f on s.perf_no = f.perf_no
        join t_inventory i on f.perf_no = i.inv_no
        join t_order o on s.order_no=o.order_no
    where     o.order_no=@order_no
    and     sli_status in (2,3,12)

    group by     s.due_amt,
            s.price_type,
            s.perf_no,
            s.zone_no,
            d.section,
            y.description,
            z.description,
            n.print_desc,
            b.base_price,
            f.perf_code,
            f.perf_dt,
            i.description,
            s.order_no

    /*
    select right(convert(varchar,perf_dt,100),7),perf_dt
    from t_perf
    */

    /*
    select o.order_no from tx_perf_seat s join t_seat d on s.seat_no = s.seat_no
    join t_order o on s.order_no = o.order_no
    where smap_no=4
    and o.mos=4

    select * from t_smap
    */
Children
No Data