We recently began using Design Gallery for our invoice output, replacing the aging Document Studio process. As part of this, we deployed the newish XML Superbill developed by Elite. We have found that it largely meets our needs, though a few pieces of information are missing that we needed to have. Thankfully, much of the XML Superbill is driven by stored procedures in SQL, which makes customizing the XML Superbill a fairly straight-forward process. While for some items, it's easy to write a pre-process in Design Gallery, this adds additional overhead to the SQL Server as additional queries are being executed against the database. If you can customize the stored procedure, and pull in the data you need in an existing query, it's much more advantageous to do so. However, make sure you document all your changes well so future updates to the XML Superbill do not leave you in a bind to recreate all your hard work. The first customization I'll show is to the timecard stored procedure: dg_xml_timecards.
Old Code: Starting around line 210, this sql string forms the backbone of the query to pull all the timecard details that get inserted into the XML output.
set @mysql =
N'set @mystrmax = (
select timecard.*, timekeep.tkinit, tkfirst, tklast, tkinitb, tkbfirst, tkblast, tktitle, tksort,
tkloc, location.ldesc,
temptaxdetail.tax, temptaxdetail.tccode, temptaxdetail.tcdesc, temptaxdetail.tcrate,
temprates.tkrt01 timerate1,
convert(decimal(16,2), temprates.tkrt01 * timecard.tbillhrs) as timevalue1,
temprates.tkrt02 timerate2,
convert(decimal(16,2), temprates.tkrt02 * timecard.tbillhrs) as timevalue2,
temptk.astd,
convert(decimal(16,2), temptk.astd * timecard.tbillhrs) as astdvalue,
title.tisort,
actcode.adesc1, actcode.adesc2, actcode.adesc3, actcode.adesc4,
actcode.adesc5, actcode.adesc6, actcode.adesc7, actcode.adesc8,
actcode.adesc9, actcode.adesc10, actcode.actname,
@task_code task_code, @task_description task_description,
@activity_code activity_code, @activity_description activity_description,
@cttitl1 cttitl1,
@cttitl2 cttitl2,
@cttitl3 cttitl3,
@cttitl4 cttitl4,
@cttitl5 cttitl5
from ' + @timecard + N' timecard
join ' + @temptaxdetail + N' temptaxdetail
on temptaxdetail.source = ''' + @timecard + N'''
and temptaxdetail.source_index = timecard.tindex
join ' + @temprates + N' temprates
on temprates.source = ''' + @timecard + N'''
and temprates.source_index = timecard.tindex
join timekeep on timecard.ttk = timekeep.tkinit
join location on timekeep.tkloc = location.locode
join title on timekeep.tktitle = title.tititle
join ' + @temptk + N' temptk
on temptk.ratenumber = 1 and timecard.ttk = temptk.tkinit and timecard.tmatter = temptk.mmatter
left join actcode on timecard.tcode = actcode.acode
where timecard.tindex = @tindex
for xml raw)'
New Code: We wanted to include the original value and hours for split timecards and a flag to indicate whether the timecard was a direct charge, or a split charge from a master matter. You can see we pull in all fields from the sptime table, and the CASE statement following that is the indicator flag. The tables are joined at the bottom in a LEFT OUTER JOIN.
set @mysql =
N'set @mystrmax = (
select timecard.*, timekeep.tkinit, tkfirst, tklast, tkinitb, tkbfirst, tkblast, tktitle, tksort,
tkloc, location.ldesc,
temptaxdetail.tax, temptaxdetail.tccode, temptaxdetail.tcdesc, temptaxdetail.tcrate,
temprates.tkrt01 timerate1,
convert(decimal(16,2), temprates.tkrt01 * timecard.tbillhrs) as timevalue1,
temprates.tkrt02 timerate2,
convert(decimal(16,2), temprates.tkrt02 * timecard.tbillhrs) as timevalue2,
temptk.astd,
convert(decimal(16,2), temptk.astd * timecard.tbillhrs) as astdvalue,
title.tisort,
actcode.adesc1, actcode.adesc2, actcode.adesc3, actcode.adesc4,
actcode.adesc5, actcode.adesc6, actcode.adesc7, actcode.adesc8,
actcode.adesc9, actcode.adesc10, actcode.actname,
@task_code task_code, @task_description task_description,
@activity_code activity_code, @activity_description activity_description,
@cttitl1 cttitl1,
@cttitl2 cttitl2,
@cttitl3 cttitl3,
@cttitl4 cttitl4,
@cttitl5 cttitl5,
sptime.*,
CASE WHEN sptime.sptindex IS NOT NULL THEN ''S'' ELSE ''D'' END AS split_direct
from ' + @timecard + N' timecard
join ' + @temptaxdetail + N' temptaxdetail
on temptaxdetail.source = ''' + @timecard + N'''
and temptaxdetail.source_index = timecard.tindex
join ' + @temprates + N' temprates
on temprates.source = ''' + @timecard + N'''
and temprates.source_index = timecard.tindex
join timekeep on timecard.ttk = timekeep.tkinit
join location on timekeep.tkloc = location.locode
join title on timekeep.tktitle = title.tititle
join ' + @temptk + N' temptk
on temptk.ratenumber = 1 and timecard.ttk = temptk.tkinit and timecard.tmatter = temptk.mmatter
left join actcode on timecard.tcode = actcode.acode
LEFT OUTER JOIN sptimes ON timecard.tindex = sptimes.sptsindex
LEFT OUTER JOIN sptime ON sptimes.sptindex = sptime.sptindex
where timecard.tindex = @tindex
for xml raw)'
Shortly thereafter is another query string that actually defines the XML output. You will need to add your new new nodes into this output string. Notice the new card-value type "split-original" with the new nodes inside, as well as the indicator flag "split-direct".
set @mystrmax = convert(nvarchar(max), @x.query('
for $row in row return
<root>
<timekeeper>
<initials>{data($row/@tkinit)}</initials>
<firstname>{data($row/@tkfirst)}</firstname>
<lastname>{data($row/@tklast)}</lastname>
<billing-initials>{data($row/@tkinitb)}</billing-initials>
<billing-lastname>{data($row/@tkblast)}</billing-lastname>
<billing-firstname>{data($row/@tkbfirst)}</billing-firstname>
{if (string-length(data($row/@tkblast)) gt 0) then
if (string-length(data($row/@tkbfirst)) gt 0) then
(<billingname>{concat(data($row/@tkbfirst)," ",data($row/@tkblast))}</billingname>,
<shortname>{concat(substring(data($row/@tkbfirst),1,1)," ",data($row/@tkblast))}</shortname>)
else
(<billingname>{data($row/@tkblast)}</billingname>,
<shortname>{data($row/@tkblast)}</shortname>)
else
if (string-length(data($row/@tkfirst)) gt 0) then
(<billingname>{concat(data($row/@tkfirst)," ",data($row/@tklast))}</billingname>,
<shortname>{concat(substring(data($row/@tkfirst),1,1)," ",data($row/@tklast))}</shortname>)
else
(<billingname>{data($row/@tklast)}</billingname>,
<shortname>{data($row/@tklast)}</shortname>)}
<title code="{data($row/@tktitle)}">{data($row/@tktitle)}</title>
<titlesort>{data($row/@tisort)}</titlesort>
<rank>{data($row/@tksort)}</rank>
<gender />
<location code="{data($row/@tkloc)}">{data($row/@ldesc)}</location>
</timekeeper>
<date type="timecard">{substring($row/@tworkdt,1,10)}</date>
<status>{data($row/@tstatus)}</status>
<print-flag>{data($row/@tprntfl)}</print-flag>
<fixed-fee-activity code="{data($row/@tcode)}">
<name>{data($row/@actname)}</name>
<line id="1">{data($row/@adesc1)}</line>
<line id="2">{data($row/@adesc2)}</line>
<line id="3">{data($row/@adesc3)}</line>
<line id="4">{data($row/@adesc4)}</line>
<line id="5">{data($row/@adesc5)}</line>
<line id="6">{data($row/@adesc6)}</line>
<line id="7">{data($row/@adesc7)}</line>
<line id="8">{data($row/@adesc8)}</line>
<line id="9">{data($row/@adesc9)}</line>
<line id="10">{data($row/@adesc10)}</line>
</fixed-fee-activity>
<card-values>
<card-value type="billed">
<rate>{data($row/@tbillrt)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@tbilldol)}</amount>
</card-value>
<card-value type="worked">
<rate>{data($row/@tworkrt)}</rate>
<hours>{data($row/@tworkhrs)}</hours>
<amount>{data($row/@tworkdol)}</amount>
</card-value>
<card-value type="standard">
<rate>{data($row/@astd)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@astdvalue)}</amount>
</card-value>
<card-value type="timerate1">
<rate>{data($row/@timerate1)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@timevalue1)}</amount>
</card-value>
<card-value type="timerate2">
<rate>{data($row/@timerate2)}</rate>
<hours>{data($row/@tbillhrs)}</hours>
<amount>{data($row/@timevalue2)}</amount>
</card-value>
<card-value type="split-original">
<rate>{data($row/@sptbrt)}</rate>
<hours>{data($row/@sptbhr)}</hours>
<amount>{data($row/@sptbdl)}</amount>
</card-value>
</card-values>
<split-direct>{data($row/@split_direct)}</split-direct>
<task code="{data($row/@task_code)}">{data($row/@task_description)}</task>
<activity code="{data($row/@activity_code)}">{data($row/@activity_description)}</activity>
<tax code="{data($row/@tccode)}" rate="{data($row/@tcrate)}">
<description>{data($row/@tcdesc)}</description>
<total type="tax">{data($row/@tax)}</total>
</tax>
<extension-fields>
<extension-field id="1" description="{data($row/@cttitl1)}">{data($row/@tudef1)}</extension-field>
<extension-field id="2" description="{data($row/@cttitl2)}">{data($row/@tudef2)}</extension-field>
<extension-field id="3" description="{data($row/@cttitl3)}">{data($row/@tudef3)}</extension-field>
<extension-field id="4" description="{data($row/@cttitl4)}">{data($row/@tudef4)}</extension-field>
<extension-field id="5" description="{data($row/@cttitl5)}">{data($row/@tudef5)}</extension-field>
</extension-fields>
</root>'))
And now you have successfully customized your timecard output in the XML superbill.
Posts
