XML Superbill Time Description Enhancements

Another issue that we came across with the XML Superbill was that the presense of a caret in the narrative did not function the same as in the old superbill. If you were not aware, if there is a caret "^" in the narrative, anything after the caret does not output on a normal superbill, and therefore doesn't show on the invoice. The XML Superbill code did not respect the caret, and includes the entire narrative. A quick change to the SQL in the stored procedure (dg_xml_timedesc) that delivers the narrative to the XML Superbill did the trick.

Old Code:

set @mysql =
 N'set @mystrmax =
   (select tdline, tddesc from ' + @timedesc + N' timedesc
   where tindex = @tindex
   and tddesc is not null
   order by tdline for xml raw)'

New Code:

set @mysql =
 N'set @mystrmax =
   (select tdline, LEFT(tddesc,CASE WHEN CHARINDEX(''^'',tddesc)=0 THEN LEN(tddesc) ELSE CHARINDEX(''^'',tddesc)-1 END) as tddesc
   from ' + @timedesc + N' timedesc
   where tindex = @tindex
   and tddesc is not null
   AND NOT EXISTS (SELECT * FROM timedesc caret WHERE timedesc.tindex=caret.tindex AND CHARINDEX(''^'',caret.tddesc)>0 AND timedesc.tdline>caret.tdline)
   order by tdline for xml raw)'

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.

Write-offs & Write-downs for Multi-Timekeepers

Here's a query that uses common table expressions to derive Accounts Receivable write-offs and Unbilled Time and Cost write-downs for multi-timekeepers and results in a single list of timekeepers with their volume break-outs. This is converted to FIRM currency, and is filtered for a single year and single title.

WITH AR AS (
SELECT
mtktk
,SUM(CASE WHEN ic.lcfco='F' THEN pmt.lamount/cdrate*mtkpercnt/100 ELSE 0 END) AS 'ARFees'
,SUM(CASE WHEN ic.lcfco='C' THEN pmt.lamount/cdrate*mtkpercnt/100 ELSE 0 END) AS 'ARCosts'
,SUM(CASE WHEN ic.lcfco NOT IN ('F','C') THEN pmt.lamount/cdrate*mtkpercnt/100 ELSE 0 END) AS 'AROther'
FROM ledger pmt
INNER JOIN ledcode pc ON pmt.llcode=pc.lccode
INNER JOIN ledger inv ON pmt.laptoin=inv.lindex
INNER JOIN ledcode ic ON inv.llcode=ic.lccode
INNER JOIN periodt ON pmt.lperiod=pe
INNER JOIN matter ON pmt.lmatter=mmatter
INNER JOIN currates ON mcurrency=curcode AND trtype='A' AND pebedt BETWEEN cddate1 AND cddate2
INNER JOIN mtkper ON mtkmatter=mmatter AND pebedt BETWEEN mtkdate1 AND mtkdate2 AND mtktype='SOURCE'
INNER JOIN timekeep ON mtktk=tkinit
WHERE tktitle='Member'
AND YEAR(pebedt)='2014'
AND pc.lcfco IS NULL
AND pc.lccollhs = 'N'
GROUP BY mtktk)
, WIP AS (
SELECT
mtktk
,SUM((mhdowkdb-mhdobidb)/cdrate*mtkpercnt/100) AS 'WIPFees'
,SUM(-mhwocdw*mtkpercnt/100) AS 'WIPCosts'
FROM
matths
INNER JOIN matter ON mhmatter=mmatter
INNER JOIN periodt ON mhper=pe
INNER JOIN currates ON mcurrency=curcode AND trtype='A' AND pebedt BETWEEN cddate1 AND cddate2
INNER JOIN mtkper ON mtkmatter=mmatter AND pebedt BETWEEN mtkdate1 AND mtkdate2 AND mtktype='SOURCE'
INNER JOIN timekeep ON mtktk=tkinit
WHERE tktitle='Member'
AND YEAR(pebedt)='2014'
AND mffee IS NULL
GROUP BY mtktk)
SELECT
tkinit AS 'TK #'
,tklast+', '+tkfirst AS 'Timekeeper'
,ISNULL(ARFees,0) AS 'AR Fees'
,ISNULL(ARCosts,0) AS 'AR Costs'
,ISNULL(AROther,0) AS 'AR Other'
,ISNULL(ARFees,0)+ISNULL(ARCosts,0)+ISNULL(AROther,0) AS 'AR Total'
,ISNULL(WIPFees,0) AS 'WIP Fees'
,ISNULL(WIPCosts,0) AS 'WIP Costs'
,ISNULL(WIPFees,0)+ISNULL(WIPCosts,0) AS 'WIP Total'
,ISNULL(ARFees,0) + ISNULL(ARCosts,0) + ISNULL(AROther,0) + ISNULL(WIPFees,0) + ISNULL(WIPCosts,0) AS 'Total'
FROM timekeep
LEFT OUTER JOIN AR ON tkinit=AR.mtktk
LEFT OUTER JOIN WIP ON tkinit=WIP.mtktk
WHERE tktmdate IS NULL
AND tktitle='Member'
ORDER BY [Timekeeper]