XML Superbill Timecard Changes

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.