MatterInput

Billing To Milan uses the MatterInput.sql file to create and update workspaces based on data read from your billing or accounting system. In order for Billing To Milan to make sense of this data, you must edit the MatterInput.sql file so that the client and matter information is mapped to fields recognized by Billing To Milan. Once that has been completed, you can run Billing To Milan in matters mode, which performs the following actions.

1. The query contained in MatterInput.sql is executed on the database specified in your billing connection string.
2. Billing To Milan compares the result set with the data in your WorkSite library.
3. If a client matter number in your billing database does not exist in WorkSite, Billing To Milan adds a Create Workspace job to Milan's WorkspaceQueue table.
4. If a client matter number in your billing database matches that of an existing workspace, Billing To Milan compares the metadata obtained from the billing system with that of the workspace; if any discrepancies are found, Billing To Milan adds a Modify Workspace job to Milan's WorkspaceQueue table.
5. Milan's Provisioning Service processes the jobs placed in the queue and applies the changes to WorkSite.

To minimize the number of billing entries processed by Billing To Milan, include the following condition in the WHERE clause:

AND LAST_MODIFIED >= '{0}'

Replace LAST_MODIFIED with the name of a billing data field that represents when a client-matter record has been updated in the billing system.

Example Script

The following script maps the fields in an Elite database to the fields required by Billing To Milan and assigns folder templates based on the area of law.

SELECT top 500 left(m.mmatter,6) AS ClientID,
               Right(m.mmatter,4) AS MatterID,
               c.clname1 AS ClientName,
               m.mdesc1 AS MatterName,
               m.mstatus AS imProfileCustom6, --MatterStatus field
               m.mopendt AS imProfileCustom21, --OpenDate
               m.mclosedt AS imProfileCustom22, --CloseDate
               m.mloc AS imProfileCustom3, --Office
               m.mprac AS imProfileCustom3, --Practice
               LEFT(m.mdept,4) + RIGHT(m.mdept,2) AS imProfileCustom4, --AreaLaw
               NULL AS RespAtty,
               NULL AS HandlingAtty,
               usb.ufullname AS BillingAtty,
               uso.ufullname AS OrigAtty,
               uss.ufullname AS SupAtty,
               m.mdesc1 AS WorkspaceName,
               m.mdesc1 AS WorkspaceDescription CASE
                                                  WHEN m.mdept = '254' THEN 'template-100'
                                                  WHEN m.mdept = '325' THEN 'template-1'
                                                  ELSE 'template-5'
                                                END AS TemplateId,
               '30' AS DefaultSecurity --public default security
FROM matter m
LEFT JOIN client c ON c.clnum = left(m.mmatter,6)
LEFT JOIN uaccess uab ON m.mbillaty = uab.wvtkinit
LEFT JOIN uaccess uao ON m.morgaty = uao.wvtkinit
LEFT JOIN uaccess uas ON m.msupaty = uas.wvtkinit
LEFT JOIN usmaster usb ON uab.uname = usb.userid
LEFT JOIN usmaster uso ON uao.uname = uso.userid
LEFT JOIN usmaster uss ON uas.uname = uss.userid
WHERE ISNumeric(left(mmatter,6)) = 1 --and ISNumeric(right(mmatter,4)) = 1
AND CHARINDEX('-',reverse(mmatter)) = 5
  AND m.mmoddate >= '{0}'