Normal 0 false false false MicrosoftInternetExplorer4
Hi all;
I’m trying to create a ticket receipt that breaks out our GST and eventually our HST to show the break down on the receipt. Our price types consist of 4 price maps (components) – the ticket price, the GST of the ticket price, the facility fee and the GST of the facility fee. What I need to do is create a receipt design in ticket design that calculates both the GST of the ticket price and the GST of the facility fee in a separate column and the ticket price and facility fee also in a separate column.
How do I show the calculations as such? Is this possible???
Thanks in advance for your help!
Hi Kareen,
Good day.
I have the same task as you.
it is a bit complicated.
an invoice normally is t_order level thing.
but gst is a t_sli_detail level thing.
if you also want to show the performance details it is t_sub_lineitem level thing.
also t_order table can have fee, contribution, t_sli_detail level can have fee too.
What a mess.
I think it cannot be finished in one report only or it must have some sub reports.
because it has different datasets.
one for t_order table (total paid and due),
one for t_sub_lineitem(performances info),
one for t_sli_detail table (pick up pmap_no go to t_pmap table pickup gl account and gst / or pickup price_category go tr_price_category pick up gst)
but at here another problem pops up,
if you want to use something like acknowledgement printing from order printing screen, then use mail merging. What kind of dataset you can get to handle mail merging? There are some logical issues here.
So I am thinking maybe it should have three screens or at least two reports to achieve the mail merging.
so the structure should be like that:
Part 1: return one line
passing in an order_no, then go to the t_order table pick up total fee and ticket amt(paid and due)
then go to the t_sli_detail pick up fee and gst and ticket number count.
then return as one line record as a summary.
total fee(from t_order +t_sli_detail),
total gst(from t_sli_detail),
total amt( paid and due from t_order)
total ticket count ( from t_sli_detail )
also customer_no (from t_order table).
Part 2: return several lines
from order_no go to t_sub_lineitem table, pick up perf_no then go to t_perf table pick up performance info, also go to t_sli_detail table get total ticket amt and total ticket count.
I think these are all info returned in their natural way.
Next thing is how to organise the display and mail merging.
What do you think?
have fun
Ben
Wow - thanks Ben. For sure it looks complicated...but I'm willing to take a stab!
Thanks again...
Have you finished your receipt design?
After I finished my invoice, I think the whole process is all about denormalization.
it should be regarded like that: you pass in an order_no, then reurn everything about the order one by one.
So it is not about returning a 10 records table, it is about returning a one record with 20+ columns table.
so what is the differences?
normally we design stored procedures first then design reports.
but this report has the invoice template first, we know what we want first,
then go back the database to dig them out.
Hi Ben - we're still working on the receipt design as we are hoping to have this rolled out by July 1st. I'll let you know how it goes but because of how we have built our tickets vs how we built our fees, we're finding it difficult to write a proper invoice report. (we break out the GST in our tickets, but in our fees, we have our Finance Dept break it out manually). It's definitely a challenge.
I think you can use functions and some local tables to count fees.
When you pass in a order_no, you can call a function to return fees, in the function you can pick up values from a local table. In the local table, you can setup rules from Finance Dept.
also you can add one more level to a function, pass a parameter to decide which value will be returned.
create function dbo.LF_myfunction
@season int,
@prod_season_no int,
@order_no int,
@returnType int
as
return money
begin
declare @myreturn money
declare @myPaidAmt money
if @returnType =1
--- based on season, prod_season_no, pick up finance rules from LT_tables.
@myreturn =@myPaidAmt
end
return @myreturn