Inprotech: Coding Tax for the BAS

Setting up tax codes in the system to be able to easily get auditable totals for the Australian tax office’s Business Activity Statement (BAS) it not that straight forward.

The system comes with a system used tax exempt category but if you use this invoices (both sales or purchases) that are marked as Exempt do not appear on the tax proof listings and hence you can’t get the totals of tax free sales and purchases that are required for the statement. Equally, using just one exempt tax code for GST means that deriving total export sales has to be a manual exercise. The same is true for capital expenditure purchases but as law firms typically don’t do much of this it is less of an issue.

The way to get around this is to set up two new tax codes, Exempt (Local) and Exempt (Overseas), both with a zero percentage taxation rate. The system provided exempt rate should be not be used. Unfortunately, it can’t be deleted so I would recommend renaming it to something obvious like “Do not use”.

Once this is done set up the WIP codes to be used by the system to be either GST or Exempt (Local), depending upon the nature of the charge. Typically Official Fees and Foreign Associates charges should be Exempt (Local) although there may be other charges, such as international couriers, that may also be exempt of tax.

Local clients should then have their tax code set to blank so that the tax coding will be picked up from the WIP items.

Foreign clients should be set to Exempt (Overseas). Billing will use this tax code to calculate the tax, overriding the tax code recorded WIP codes, and report the revenues under this category so total export sales can be derived.

Local suppliers need to have their tax code set to GST and the tax treatment should be inclusive or exclusive as applicable.

IP Australia is a special supplier however and needs to be set to Exempt (Local) and Tax Inclusive. The tax treatment of Tax Not Applicable, even though it seems obvious to use that one, should not be used as this means that any invoices will not be recorded on the Tax Proof Listing. The default expense account also needs to be set to an account that doesn’t require disbursement to WIP so that Lodgement/Fees Lists can now be entered without being forced to disbursements to be recorded against a case. I’ll go into processing lodgement or fees lists below.

Foreign Suppliers need to be set to Exempt (Overseas) and Tax Inclusive. Once more, the tax treatment of Tax Not Applicable should not be used. If this is used the suppliers invoices will be excluded from the tax report.

The next trick is then to ensure that all accounts payable transactions that need to be reported on the BAS are processed through the Purchase function in Accounts Payable, even if the GST component is exempt. If they are entered through the Manual Payment process as a one off payment or as a withdrawal through the Cash Book then they will not be considered for tax reporting as these to functions have no GST capability.

The biggest load of transactions here will be the payments for lodgements to IP Australia, ie. the official fees, whether the fees list is generated automatically from Inprotech or raised manually. These need to be entered as a purchase and then manually paid using the purchase option. One one thing to be aware of here is that IP Australia as a supplier will default to not disbursing invoices, therefore, if a sales order for fees or invitation to pay is to be processed and disbursed to a case then you will need to change the expense account to one that will force the disbursing to WIP. This means that the operator has to know to do this. Another option is to set up another supplier for IP Australia with a default disbursable account. Then the operator has to know to use this! Six of one, half a dozen of the other.

Other AP transactions that could be an issue here are financial services transactions that have a GST component and are automatically debited from the bank account, eg. lease payments, American Express card commissions, fees for foreign exchange payments. The temptation here is to take these up through the cash book but if you do that the GST component can’t be captured and processed (and you definitely want to capture these ones because this is GST that you are paying and can claim!). Raise them as a purchase invoice and pay them through manual payments to both get them on to the cash book for bank reconciliation and record them on the tax proof listing for BAS processing.

Equally it is not possible to register GST against general journals in the General Ledger so any transactions here that affect GST have to be put through the relevant sub ledgers.

The net result of the above is that the BAS worksheet can be created as follows:

G1 - report total of sales and tax from the Sales Tax Proof listing
G2 - total of sales for Exempt (Overseas) category from the Sales Tax Proof Listing
G3 - total of sales for Exempt (Local) category from the Sales Tax Proof Listing
G11 - report total of purchases and tax from the Purchases Tax Proof listing
G14 - total of purchases for Exempt (Overseas) and Exempt (Local) categories from the Purchases Tax Proof Listing

I haven’t discussed capital expenditure purchases as there usually aren’t very many of these so they have to be handled manually. Alternatively a separate tax code can be established for this and the reports will then total against this code.

The most important thing though is the set up of the client and supplier details. If this is not done properly then non-taxable items won’t appear on the reports. Not a complete show stopper in terms of the statement because the tax to be paid is always correct but the audit-ability is less complete.

I have a little Excel spreadsheet that mimics the worksheet and adds up the totals once you enter the above so if you want a copy drop me an e-mail. I don’t have much experience in this area in other jurisdictions, eg. VAT in Europe, but I am sure the principles are the same.

Merry Christmas

Best wishes for the festive season to all my readers out there, whoever you are!!

Looking forward to a bright and growing 2009.

All the best.

Stephen.

Inprotech: Searching for Cases in Multiple Countries

Another piece of potentially “hidden” functionality that I had reason to use recently was the ability to search for cases in multiple countries. This was added a few years back as a part of a sale for a firm who wanted to be able to report on cases for a client in a particular “region”, as defined by the marketing department eg. Asia Pacific, North America, South East Asia, South America.

The country pick list that is used from the case enquiry screen is different from the pick list used in other places in the system in that it allows the selection of multiple entries by using Ctl-click and Shift-click in the usual manner for multiple selections. Upon doing this the case enquiry will then return all the cases in countries selected.

The pick list itself also has “selection” criteria at the top of the dialog. It is possible to select all countries within a group or all those with a particular country attribute. I can’t remember what the attribute search was actually for. Hard to see, for example, a list of all cases in countries with single class applications being particularly relevant! Anyway, I am sure there was a reason for it, I just can’t remember it. These country selection criteria then limits the countries displayed in the body of the pick list so that the you can easily select the countries necessary.

You can also select multiple countries in the country entry field itself. Simply enter the desired country codes separated by a semi-colon, eg. US; CA, will return United States and Canada.

To be able to search on a region for marketing purposes, as mentioned above, it is then necessary to set up a new country group in the Country program that includes the countries defined to be in the region. From Cases, then use the Country pick list to select the multiple countries in the new group. You can then add extra search criteria for the client and maybe the property type. For example, all of a particular client’s trademarks in the South East Asian region. If you are using the Family field to define the clients brand name then this could further filter the list.

I am not sure how often this type of thing would be a requirement for many users but if it is there is a way to get the information out!!

Inprotech: More on Release 4

Having just been through another Release 4 upgrade with a client I thought I might pass on these experiences as well. The client, in this instance, went for the “rapid fire” rather than a more structured approach and sometimes the situation necessitates this. There is just a different risk management profile with these types of projects and you need to plan for more post implementation issues.

In any case, the client is live on the new release after a bumpy ride, especially at the end. I thought I might pass on some of the issues encountered as they were different from the previous project. The client experienced some minor template issues, but these were more related to the upgrade from SQL2000 to SQL2005 than the Inprotech upgrade. The two areas to go through are, firstly, the deployment process and, secondly, some issues that we found with the application.

The firm itself has 35 users, one of which is a Citrix server which supports another location with 15 users. The client PCs run Windows XP SP3 and they have now migrated to a brand new server which is running only SQL Server 2005 and Inprotech Version 4.0. The administrator logons on the Client PCs have a local profile on each PC and so differ slightly from machine to machine, this may explain some of the behaviour below.

In terms of the deploy the issues were:

1. Word needs to be installed on the server for the installationto work. CPA acknowledge this as an error but it isn’t going to be fixed until the release of Version 5.0.
2. For the client PCs the approach taken was to physically install the client component one each PC rather than use a silent install process. Partway through the installation it became apparent that running the setup.exe when logged on as an administrator actually went to the shared network folder which contained the dglib.dot, dgwiz.dot, etc and deleted the contents of this folder!! This only occurred on certain PCs. The server software installation had then to be re-run to retrieve these missing templates then save a copy to return to the folder every time a client installation deleted them.
3. For the .OCX files to register correctly on certain PCs it was necessary to actually launch Inprotech logged in as the administrator. This wasn’t clear from the documentation, and as a result it was necessary to re-log on to a number of PCs when users noted a problem on logging in.

In terms of the application, the new Reminders program has caused the most issues with, as far as we can see, pieces of functionality that have been taken away. The program is widely used around the firm and the issues have created some consternation and the need for new workaround processes.

1. The new colouring scheme doesn’t show satisfied reminders any more (or we can’t seem to find the way if it is possible). The firm doesn’t feel comfortable in just deleting all satisfied reminders in a blanket fashion. In the past some rules were set up to be satisfied automatically at due date and they would like to be able to manually review to ensure these rules have been corrected.
2. The details of where the where the due date has arisen from has been taken away from the message on the screen. This was information that was used in certain instances to better understand the situation with the due date but is now not available. You can get it by viewing the report but this just adds another step in the process.
3. The Countries button appears to be no longer available for reminders that are satisfied by updating the Designated Countries tab, eg a 30/31 month reminder on a PCT or a 12/18 month reminder on a Madrid case. The firm has rules to satisfy the reminders if all of the countries listed are updated to a particular level, eg to national phase filed. The Countries button used to appear on the second tab of those reminders and listed the countries which had not been updated to the appropriate level. We don’t seem to have access to this anymore but it may be that we just haven’t found the appropriate site option or whatever.

In all the jury is still out on the new program but they can’t go backwards.

The other application we have had an annoying problem with is the new Launch Pad. Occasionally, it gets itself confused remembering the screen position and when the user logs back in the Launch Pad can’t be seen, even though the task bar has an icon that shows it is running. What appears to be happening is that the X/Y coordinates of the remembered screen position get corrupted and the Launch Pad is position “off the screen” somewhere. The first 2 times this happened we resolved it by changing the screen resolution to the highest available and the program could then be seen again and moved back to the centre of the screen, so that it can be accessed once the resolution of the screen is changed back to normal.

On the third occasion the program was still nowhere to be seen no matter what we did and this might be related to users that use two monitors, I am not sure. In the end we tracked down the table that recorded the defaults (DEFAULTSETTINGS?) and deleted the row for the user. When they then logged in the defaults were reset and the Launch Pad is accessible again.
This is a highly intermittent bug and we haven’t been able to duplicate the circumstances that cause it and hence report it through. It is however very confusing to the user because they effectively can’t access the system at all. Fortunately the workaround fix is pretty quick once you know.

I have now been involved in, with and around three upgrade projects and all have them have been problematic so my advice would be thorough testing and preparation. However, I am also aware of another project that went completely smoothly so I guess you can be lucky!!!

Inprotech: Contact Addresses

Time goes so quickly. I had an intention of writing an article every one or two weeks as I have a long list of topics but before you know it a month goes by! I promise to do better.

My little article today is about contacts and names & addresses. The Inprotech database has a highly normalized set of data structures where the name is a separate data object from the address, which is separate from the telecommunication numbers. Each of them has their own set of attributes and they can be linked to each other with the goal of only having the information recorded in the database once, removing the potential for extra data entry and error.

The user interface tries to hide this complexity, providing structured input forms that attempt to lead the user to enter the information in a manner similar to how it is provided on paper and then the system builds the appropriate links and pointers. For example, an employee of a company is simply added as a name and the system defaults the address pointer to the company’s address pointer meaning that the address doesn’t need to be entered again. If the employee has a different address this is entered and the software breaks the link. A new address data item is created and linked to the employee. Same thing applies to phone numbers, faxes and the like, although, for direct telephone numbers, the choice is whether to override the number or use the Direct Line number type.

It is then possible to use the advanced functionality to link numbers and addresses to each other and to different organizations and contacts however my experience has been that users generally find this all very confusing and my recommendation is that, unless you are prepared to invest in training and good procedural documentation, avoid it completely.

Of course, with all these high ideals, the human factor kicks in with names added in a variety of different ways over the years simply because of the practicalities of day to day life. People come and go, some are trained, some are not, some understand, some don’t and some care and some don’t.

So the question that was raised: how can you tell if all contact names for an organization are linked to the same address. Here is my suggestion.

Use the Names program as the enquiry tool:

1. Enter the company name as the Associated Name.
2. Select the relationship “Employs” (this is the system relationship used for contacts).
3. Select the Reverse relationship check box (Employs becomes Employs By).
4. Remove the Has Code check box (contacts are unlikely to have a name code).
5. Check the Fast Correction check box in the tool bar. (Fast Correction was created to allow quick changes to a defined list of names without having to go backwards and forwards between screens).
6. Enter.

You will then be presented with a list of names and their associated addresses. Simply scroll through the list and if the address isn’t blue (and the same) then you have a duplicate address for the contact! To correct them you need to good into the name by double clicking and linking but, hey, at least you found them!

Inprotech: Prepaid Funds

Management of cash is a crucial component for any business but even more so for a patent attorney firm who:

• incur the liability for disbursements on behalf of their clients,
• can be dealing with fledging and start up organisations who often have their own survival issues
• deal with foreign associates who expect long trading terms and work outside of the firm’s jurisdiction!!

I am not going to even mention management of foreign currency risk, particularly in today’s volatile environment.

One way of managing the cash flow with local or direct clients is to obtain funds in advance from the customer, before the work is done. This could be in the form of obtaining a deposit before performing a broad range of activities or asking for a specific amount to cover a specific activity eg. payment of registration fees to obtain the certificate.

In the first instance the customer is required to provide cash in advance because a large amount of expenditure will be necessary, eg. a large multi-country protection of a brand, and the provision of cash is based upon the size of the project, the goodwill of the client and the commercial need to cover the outgoings. In the second instance, the attorney has more leverage, in that the payment is necessary to finalise the entire filing process.

Inprotech has one major way to handle these situations: prepayment processing. Another way for the second instance is billing in advance but I will talk about that in another article.

A prepayment is where the client pays funds in advance of receiving an actual invoice. The money is receipted into the business and recorded as a special form of unallocated cash, classified in the system as a prepayment.

These funds can then be marked for different types of use. For example, it may be only for patent cases or for trademark cases, for renewals work or non-renewals work, or for a specific case or set of cases. Alternatively, the sum of money can be just allocated to the client and therefore available for use against any piece of work for the client.

The definition of the funds as a prepayment as opposed to unallocated cash means that the money become available to be applied to the invoice during the billing process. This is done via the Apply Credits button that will become enabled when funds are available for a case being billed.

A list of prepayments will be shown that match the attributes of the case as defined against the prepayment above eg. if the prepayment has been allocated for patent work and the case is a trademark case the item will not be available to apply to the invoice.

Once the amount is selected (and the invoice is finalized) the cash will be accounted for correctly in the Accounts Receivable ledger, if you are using this, and, presuming the invoice format has been set up correctly, shown on the bill. Total Due less Prepayments Applied equals Total Now Payable. If the prepayment has been applied to the entire invoice then a zero bill will be created.

Various site options apply to the use of prepayments. There is one that provides for a warning to be shown if prepayments are to be applied during the billing and another that checks total WIP for a case against prepayments available and warns if the total exceeds the amount during the time and disbursement recording process. In this way prepaid work for the client can be monitored as the work is being performed rather than waiting until the billing process to find out that all prepaid funds have been expended.

A particularly interesting site option is AR for Prepayments (or something like that!). This allows prepayment functionality to be implemented where the firm doesn’t have a license for the Accounts Receivable module, although you obviously need a license for the Billing module. When this option is set to true a limited form of the Accounts Receivable program becomes available. All functionality, outside of recording receipt information for prepayment processing, is disabled.

The other areas that prepayment information is shown is on the WIP tab in the Cases program, where totals amounts for the case and debtor are shown as well as the Professional WorkBench against the client record and the case record. I think it can also be available for the client to see in the Client WorkBench but don’t quote me on that one. That’s a whole other set of decisions anyway.

The last area that needs consideration for prepaid funds is what I will call “proforma invoicing”. Occasionally, a client may require an actual invoice of some form before they will release funds. This could be because they believe that the tax regime requires an invoice or simply that their internal processes require a piece of paper.

In this instance you cannot raise an invoice (even one in advance) through the system because that will create revenue and debt that needs to be paid. What needs to be done is the creation of a proforma invoice through word processing. This is a piece of paper that looks like an invoice with the amounts and tax applicable (if necessary) but it not a financial document from the accounting systems standpoint.

If you are doing a lot of these it is probably makes sense to create a PassThru template for the sake of efficiency and consistency. I would also recommend some sort of numbering system and register to tracking the proforma invoices sent to clients. Probably easiest to do this manually although if you are database savvy it is relatively simple to set up some SQL to generate the next proforma invoice number (and create and monitor the register) via Docitems and PassThru.

Reflections on an Inprotech Release 4 Upgrade

I have recently been involved with delivering a project for a client that required moving the firm from release 3.3 of the system to the latest release 4 patch.

The firm has approximately 150 staff and uses virtually all of the client/server Inprotech modules as well as the Client WorkBench. The software is integrated with their document management system and there are a significant number of in house written reports.

As a result, the system touches on all users in the business, as well as clients. The project was therefore staged in a manner that tried to mitigate risk of disruption to the business from the change as much as possible. We also wanted to introduce the new versions of the software in a manner where the staff could take advantage of any useful new features and, as an adjunct, document the overall upgrade process so that moving to future releases would be easier.

The approach we took was as follows:

1. Analysis & Preparation. All release notes were reviewed and assessed with the business to determine which areas of new functionality could be utilized by the firm and what level of configuration was required.
2. Unit Testing. The software was installed in a test environment to allow configuration and testing of the new functionality identified in preparation for acceptance testing.
3. Integration Testing. The newly configured software was tested then with integration to Word, the document management system and the in house reports.
4. Acceptance Testing. Representatives from various areas of the business were co-opted (conscripted??) to test the software that they were affected by in a test lab environment with test cases that mimicked everyday usage of the software.
5. Deployment. Training materials showing the changes in the software between the different versions were constructed and training courses were delivered to reinforce the major changes. Actual deployment occurred over a weekend when the production database was upgraded and the new requisite client components for the user PCs were delivered via a silent install process that had previously be developed and tested.

In my view a formal approach is necessary for major upgrades where lots of people are affected but the same principles apply to smaller releases, just less effort is necessary and you could probably skip the acceptance test phase (or make it significantly smaller) and the deployment step will be easier.

For this project we generally achieved our goals with an excellent team effort however what are the things that others maybe should be aware of?

The first thing we found was that the effect of the new release on end users was fairly minor. This is because the big ticket items in the release: electronic data exchange, trust accounting, IDS, Canadian GST, Clerical/Professional WorkBench are only applicable if you do massive amounts of renewals, work in a particular jurisdiction or use the new web modules.

There are some interesting minor enhancements that we took advantage in the back office of but, to be honest, the most beneficial piece of the release to the user (outside of being on the current version for support) is the ability to maximize the screens and have the software, in the majority of cases, re-size itself to display more information (and remember that it has done it).

The re-design of the reminders screens is also nice for the project but that was available in a previous release so not necessarily a release 4 thing. As well the Launch Pad now docks and remembers where it has been docked between logons which adds to the overall usability a little bit.

Note however that the re-sizing only applies to screens that are of a tabular nature so it does not do anything for screens with fixed field locations such as the Instructor tab or some of the name tabs. Note also that it has only been applied to the main screens in the Cases & Names module. The other programs and the timesheet, billing and accounting modules are being done in a future release or releases as I understand it.

The second thing we found was that we had quite a few issues with the document generation Word DLL and had we three iterations of this crucial little piece of code delivered to us during the testing phase. Hopefully it is now stable in the current patch level.

The main issue was around the way Null values are handled. Instead of returning no value for Null the dglib.dll (the module that handles data requests from letters) was now returning a space. The client’s templates were checking for no value and colour highlighting these fields in the letter to indicate possible missing data. When a space was returned the check no longer highlighted the field.

The new dglib.dll is also less forgiving with poor coding. Whilst this is probably a good thing in some respects it does mean that some recoding work may be required for letter templates and testing of letters is critical to a successful upgrade. The following were typical of the issues found:

• You must always register a Docitem BEFORE it can be referenced in any VBA code. In the previous version all Docitems were registered by a separate DoItems call, usually at the end of the macro, but now they are registered dynamically so the order is more important.
• The registeritem must nominate the correct destination type and cannot be blank. In the previous version it would default to being a string returned to a bookmark if there were errors which sometimes allow the template to complete even though the code was wrong.

The nett result was that a significant number of the Word templates used were affected and as a result a good deal of work was necessary to make changes to the templates and testing them. As the generation of documents is probably one of the key deliverables to the system to the business we really had to make sure that this was right before we deployed.

Other things to look out for:

• The reminders program seems to handle security slightly differently (although we are not sure if it is a bug) so we had to do some extra configuration here,
• There is an annoying bug that has been introduced in the DocItems program that resets the flag for images and stored procedures even though you don’t change it.
• The client PC now requires to have some software to be loaded to it. (Previously the client had run the entire system from the server).
• We had to upgrade SQL server 2000 to SP4 with the latest hotfix to resolve some policing and billing issues.
• Some custom changes were needed to the upgrade scripts because the client used different codes to CPA (eg WO instead of PCT and different code for ‘Leave Open in Word’ delivery method ) .
• An extra cleanup script was required to remove some duplicated names attached to cases in CASENAME (mainly contacts but also other name types). We at first thought that this was something site specific but other clients have had the issue and CPA support have a script to assist if you know to ask for it (which you do now!).
• A fairly significant workbench issue in drilling into case details which were corrected by stored procedure patches, which presumably will be incorporated into the main release at some stage.

Lastly, a special thanks to guest editor Dave Palmer who revised and wrote some of the more technical items here!!

Inprotech: Multiple Invoice Formats

I passed on a quick little tip today that’s worth publishing overall I think.

Inprotech has functionality that allows multiple invoice formats to be set up. The idea is that individual clients can each have their own specific format and this format will be automatically assigned as the invoice is raised without the operator having to think about.

It might be because a different of invoice is required for different legal entity or office, a filing case has a different layout from an opposition case or simply one client wants more detail than the firm’s standard invoice.

This automation is achieved by recording attributes against the invoice format and a best fit algorithm is applied when the invoice is raised to pick the format that best meets the corresponding attributes of the debtor.

It all works very nicely but one little issue is when there are multiple clients that require the same invoice layout. The typical example is when you are dealing with multiple subsidiaries of a large organization. Maybe there are 10 or 15 different debtors in total but they all require to the group specific invoice format.

Using the system as it stands, you need to set up the billing template and then create the 15 bill formats in the table, one for each debtor. You get 5 or 6 clients like this and all of sudden you’ve got a maintenance nightmare on bill formats!

The reason you have to do this is that the debtor is seemingly the only client related attribute that you can apply against the invoice format. What you really need is the Client Group attribute or something like that.

The tip here is to use the client Language attribute as the grouping facility. Or course, this assumes you aren’t using it actually for the client’s language!!

Set up a Language in the pick list for say “ABC Company Invoice Format” and then attach this language against all of the name records for those organisations in the ABC Group. Then set up the ABC Invoice Format (once) with the necessary template and attach this Language attribute to the format. When the bill is raised for a debtor with this language the correct format will be selected. You don’t have set up a new format record, to use the same template, if a new group company comes along and you only have to maintain the record in one place when it changes.

I also like to code the Languages with an “AAA” at the start so that all of the invoice format languages are at the top of the picklist:

AAA – Australia Post Invoice Format
AAA – IBM Format
AAA – Proctor & Gamble Invoice Format

The alternative, or course, is to delete any languages not used from the list.

This approach is also useful if you want to have a different invoice format for local clients and international clients. Maybe you don’t want more space for descriptions for clients or the banking/remittance details are different. Simply set up a language type of International Invoice Format (or vice versa) and attach it to international clients.

Of course, you then need to make sure that this happens during the client take on process. Maybe using the name copy facility with a template record, but that’s definitely another story!!

Inprotech: Price List

I have been working on a couple of projects recently where we have set up the price list in fees & charges module and then incorporated it into the workflow to have the work in progress generated for billing based upon that list. As you will be aware there is no useful reporting in the system to help this process at all.


As a result we developed, over a period of time, an SQL statement that returns relevant data with one row per charge item so that it can be easily reviewed by the user. Here it is:

select F.CRITERIANO as "Id", C.DESCRIPTION as "Fee/Charge",
A.CASETYPEDESC as "Case Type", P.PROPERTYNAME as "Prop Type",
isnull(Y.INFORMALNAME,'') as "Cty", F.SERVWIPCODE as "S/C Code",
F.SERVBASEFEE as "S/C 1", F.SERVVARIABLEFEE as "S/C 2",
F.SERVICECURRENCY as "S/C Curr", N.NARRATIVECODE as "Narrative",
isnull(F.DISBWIPCODE,'') as "Disb Code",
F.DISBBASEFEE as "Disb Fee 1", F.DISBVARIABLEFEE as "Disb Fee 2",
isnull(F.DISBCURRENCY,'') as "Disb Curr", isnull(FT.FEENAME,'') as "Fee Type",
isnull(Q.SOURCE,'') as "Quantity"
from FEESCALCULATION F
left join CRITERIA C on C.CRITERIANO = F.CRITERIANO
left join COUNTRY Y on Y.COUNTRYCODE = C.COUNTRYCODE
left join PROPERTYTYPE P on P.PROPERTYTYPE = C.PROPERTYTYPE
left join CASETYPE A on A.CASETYPE = C.CASETYPE
left join NARRATIVE N on N.NARRATIVENO = F.SERVICENARRATIVE
left join QUANTITYSOURCE Q on Q.QUANTITYSOURCEID = F.PARAMETERSOURCE
left join FEETYPES FT on FT.FEETYPE = F.FEETYPE

The idea is then to copy it into Excel and sort it as appropriate and remove columns depending upon the type of information required to be reviewed.

Inprotech: Data Quality

Data quality is always an issue for patent attorney firms.

Many years ago we developed functionality to report upon potential data exceptions within the database. At a recent client meeting we were talking about auditing data and this report was raised, once more a good deal of people in the room were unaware of its existence.

It's called the Property Data Exceptions report and it is available from the Reports menu of the case summary window, the one that you get that lists all the cases selected and allows you to tick cases for reporting and processing. The menu item sort of sneaks in there on this window and you could be forgiven for not noticing it because it is not available on the Reports menu on the first selection window. Unless you look closely it is not so obvious that it is now available on the summary window.

The report was written for CPA renewal clients in attempt to be able to get the data as clean as possible before sending it electronically to CPA via the interfacing programs. As such, it has a bit of a renewals focus but there is some utility for the audit of data in general. It runs over the selection of ticked cases in the summary window and can be selected to run over all the cases or only those with a case type of Property.

Unfortunately there is not a lot of documentation about what the report does (in fact, absolutely none) so the following is what each of the options does to the best of my memory:

1. Agent Details - foreign cases where no agent is recorded against the case.
2. Application Details - the case has an application number but no application date or vice versa.
3. Registration Details - the case has a registration number but no registration date or vice versa.
4. Publication Details - the case has an publication number but no publication date or vice versa.
5. Priority Details - the case has a Basis that is claiming convention but has no earliest priority date.
6. Entity Size - the country requires definition of whether small or large entity parameters are to be used to calculate fees (eg. US and Canada) but this hasn't been recorded against the case. This is determined by an attribute recorded against the country via the Country program.
7. Number of Claims – the country requires the number of claims to be able to calculate the renewal fee, eg. Japan, The Philippines, but the number of claims hasn’t been recorded against the case. This is determined by an attribute recorded against the country via the Country program.
8. Number of Designs – the country requires the number of designs to be able to calculate the renewal fee, eg. Germany, but the number of designs hasn’t been recorded against the case. This is determined by an attribute recorded against the country via the Country program.
9. Date Sequence – makes sure that the dates recorded against the case meet the validation requirements as defined by the Dates Logic rules for the applicable criteria. This is the area where you might define, for example, that the Registration Date needs to be greater that the Application Date. This validation rule will be applied at the time of data entry however any previous loaded cases can be audited overall.
10. Official Number Format – applies any number formatting rules to official numbers entered and displays an error when the validation fails. The formats need to be set up against the number types for the country via the Country program.

Upon reflection, I can think of a number of extensions to the approach to add a lot more value but it is there right now and it is free to use!
One idea could be to use it if you are looking at giving a client access to their portfolio over the web via the Client WorkBench. Bring all their cases up in a query and run the report over it to see what exceptions are highlighted. This way you have an idea of the data quality that you are presenting to the client in a quick report and therefore assess how you want to handle the situation.
Another could be to run the report for cases in a takeover situation after they have been loaded.
Food for thought.

Inprotech: Printing Multiple Letters from the Case Enquiry

It’s interesting to remember sometimes why certain features were put in the software. Sometimes I don’t recall at all and often wonder if we were looking to solve a business problem or it just seemed a good and fun idea at the time!

It’s even more interesting when a user finds out about the functionality for the first time and tells me a really good business reason for the feature.

Case in point today is the ability to print multiple documents from the case enquiry summary window. This feature allows you to print the same letter for multiple cases. All the ones that are ticked in the summary window in fact. Go to the Forms menu and the WP Document window. Select the document from the picklist and click ok. One document will be created for every document in the summary list that is ticked.

I am pretty sure we added this because it was a good idea and easy to implement given that we were adding the “print a document from the case and name record” feature.

One of the people at my client found out about this today for the first time. She thinks it is brilliant because when a group of cases move from one attorney to another they use the bulk name change feature to change the staff responsibility but then have to print out, one by one, the new file cover sheet.

This is a PassThru letter that has information about the case, including the responsible staff member, that is attached to the front of the physical file. From now on, she will be able to print all of the cover sheets at the same time the name change occurs, with no manual intervention, saving heaps of her time!!!

Inprotech: Gross margins, cost rates and the Budget tab

One area I had a large amount of input into when I was at Maxim/CPASS was the addition of cost rates to the recording of work in progress and the addition of the Budget tab as an optional screen in the Cases program. The goal was to provide a first cut at facilities to assist with putting more financial rigour into the management of clients and their matters.

The traditional financial measurement of the value of client to the firm is nett billings (ie. after recovering disbursements). While this is important information my interest was in understanding what level of gross margin or profit that the client was delivering.

As an example, a large corporate client with a high level of billings may also require a lot of (non-chargeable) attention to meet their particular needs and demand a level of discounting, just because they can! The net result of this could be that the profit contribution from that client could be significantly lower then expected and the effort doing the work for that client may earn a higher return elsewhere. Equally, small local clients are can seen as lucrative by the person that has built a strong relationship with them but what is the actual profit position? If it is not adequate then should we be servicing them? Then, who are the clients that are contributing the most profit to our business because these are the ones that we want to protect and nurture.

The two important pieces of financial information to measure this are gross margin and gross margin %. Gross Margin is the raw profit contributed by the client. Let's say fees are $1,000 and the cost of delivery is $600 then the Gross Margin is $400. The Gross Margin % is $400 over $1,000 or 40%. While gross margin is a direct amount and therefore can be easily understood the Gross Margin % is a ratio that is a measure of return on investment and, in some ways, an assessment of risk with the customer.

Now I am not saying that total billings isn't useful information as the volume of work provided is an important factor in understanding the totality of a client relationship. Equally, dominating market share in a particular segment may require retaining some less profitable customers.

In my view, however, the key driver for marketing and customer service strategy should be client profitability. We should be investing our money in the areas of the business that have the greatest likelihood of return. Without assessing this, obviously alongside other relevant information, we don't ensure that we have fully understood our offering to the market place.

So how does the system help? Firstly, there is the ability to set up cost rates for work in progress items in a similar way as you can set up charge out rates for time. When this is done the cost of the work or expense is calculated and recorded on the WIP item along with the charge for the item. If the charge is written off or down the cost remains regardless of how it is billed to the customer.

For time, costs rates are an hourly rate, similar to charge out rates. Say, the cost rate for a particular staff classification is $100 per hour and the charge out rate applicable for the work is $400 per hour. When 30 minutes of time is recorded in the timesheet program, a cost of $50 and a charge of $200 will be written to the work history table allowing the calculation of the gross margin for the item at $150 ($200 minus $50).

For disbursements, cost rates are a percentage of the disbursement amount. For examples, associates invoices, courier charges and the like would be defined as having a cost of 100% of the disbursement (because that is the true direct cost). Recoverables such as photocopying and fax charges, on the other hard, may be defined as having a cost of 50% of the disbursement because there is no directly related invoice and the firm has decided that this is the notional cost that is to be assigned to these type of expenses.

Following on the above example, an associates invoice of $1,000 would be recorded with a cost of $1,000 and hence have no contribution to gross margin and a sundries charge of $20 would have an associated cost of $10 giving a $10 contribution to the gross margin.

The system also provides for 2 cost rates, surprisingly enough called Cost Rate 1 and Cost Rate 2. The intention here was to allow calculation of gross margin under two different scenarios. For example cost rate 1 could be used for the direct cost of the time, a rate directly related to the staff salary or a blended salary for a staff classification. Cost rate 2 could then be be used to add on an some percentage of on costs to cover any taxes, insurance or superannuation/pension that are directly related to employment. Alternatively this rate could include all of that plus some notional portion of overhead (accommodation, admin support) as per the firm's accounting policies.

A little thought needs to be put into this as the raw information will read differently depending upon the approach taken but in the end the key thing is the relativity between the figures for different clients. Therefore it is not something you want to change once you have started the process of recording information.

In terms of the standard system, the only place the information is aggregated is on the Budget tab for the case. This calculates the net fees and the total cost to provide a gross margin for the case. The next step was to provide reports aggregating costs & fees and calculating margins for clients overall but we never got around to doing it because we got so little feedback on what was provided. I suspect no user has ever implemented this aspect of the system.

The other work that was done at the same was the ability to record a budget (and a revised budget) against a case and there is a simplistic little tool that helps you do this eg. enter a staff member or staff class and a number of hours plus disbursements and it calculates the total budget. When the budget is created it also records budgeted costs, if set up, and budgeted gross margin are compared against actual for the case as well as showing budget used.

The tab also summarises billing for the case by fees, disbursements etc.. and provides a list of all bills raised for case. These can then be displayed our re-printed. From memory there is also a site option to allow a warning to be displayed if the fees for the case go over budget and we (naively) provided a web part that displays the actual versus budget on Client WorkBench. I suspect no one will ever implement that bit!!

Of course, what is really needed to make the feature useful is some reporting. The ability to measure profitability by client, type of client, industry segment, technology specialisation, etc... allow key information to be available for strategic decision making. While there are no reports provided with the system they could easily be constructed. The beauty of the database is that all the information is collected in an elegant, normalised and generally easily understood manner.

The key is unlocking this rich information source.

Inprotech: Correspondence Instructions

An interesting piece of functionality in the system is Correspondence Instructions. I have often wondered how widely used it is.

The idea is that you can record notes against a name record that pop up during the case take on process, billing and letter writing so that the operator sees them to knows any special instructions that have been provided about corresponding with the client. It might be all correspondence should be emailed to this address, invoice copies are required to be sent on yellow paper or they don't like staples in their letters!! The fields are available on the Client Details tab for the name record in the Names program.

There are 4 types of instructions, a default, one for cases, one for billing and one for PassThru (letters). If the same notes are to be displayed across all areas then just complete the default text. If a different set of instructions is required for billing then select the billing type and put the notes against this. This will override the default notes when the pop up occurs in the billing program.

The delivery of the notes is controlled by the Correspondence Instructions site option and this defines where pop ups are enabled.

The delivery of information to the user is via the "tip" functionality, the smaller yellow box that you see sometimes if help text has been set up against case tabs and detail entries. The instructions will pop up when a new case is added when the name is the instructor and when a bill is created when the name is the debtor.

With cases, my preference would be to display the instructions every time that you go into a case so that it is always in the operators face, but that's not how it works. I would also prefer it to be a bigger dialog right in the middle of the screen rather than the little yellow one on the left hand side. This way more instructions could be read without scrolling and you can't really miss them. The ability to display in Timesheet would also be useful.

Implementation of displaying the instructions in letters requires a bit more effort. Docitems need to be set up and templates changed. I am afraid I am not fully aware of the specifics here.

Lastly, this functionality was meant to supplement, not replace, the ability to automate instructions and processes with clients. Things like:

• copies of correspondence to a different address,
• billing in a different format for a client,
• different billing address,
• different statement address,
• numbers of renewal reminders to be received,
• auto renew,
• number of bill copies,
• billing currency, multiple cases on a bill or not,
• different letters, language of correspondence,

etc... are all things that can be recorded the system in other places so that the system can handle these without operator intervention.

Inprotech: AR Item Import

This little program is quite useful for loading up open items in the debtors ledger, most typically used when transferring information from an old system to Inprotech. It accepts an CSV file of items and loads them up as debit and credit journals.

First issue is that the layout of the import file isn't provided but if you ask the help desk they will give you the functional specification for the program which includes the file layout.

Next issue is that the program requires the NameNo of the client record in Inprotech, which is the internal unique identifier of the record in the Names database. The only place that this is visible is by going into the details of each name record and using the Systems Details menu item to visually read the number, clearly a time consuming task when you are looking to load items for hundreds, if not thousands, of customers.

The way to get around this is to use Excel:

1. Load the old system's account code for the customer against the name record using the account code field (or Search Key 2 as per a previous article).
2. Use SQL to extract a table that has the account code and the name number as the two columns.
3. In Excel, use the VLOOKUP function against the two columns (they will have to be sorted in account code sequence) to get the name number for the account code so that it can be used to import the item.

One word of warning on this program. For debit items, it converts the transaction date as the date of the transaction provided. For credit items, it seems to change that date to the date of the current period for some reason. This means that while the total balance of the load is correct the actual totals by aging period my not be and, if not, will have to be corrected manually to ensure that customer statements are consistent with the previous system. The problem has been reported to CPA but I wouldn't expect it to be corrected at any stage in the foreseeable future.

Inprotech: Keywords and Stop Words

I have often wondered how many clients make use of the Keyword functionality in the Cases program. It was a bit of carry through from the previous system we worked on and has probably been superseded by the ability to text search and to a certain extent clashes with the case attribute functionality.

The intention was to provide a facility to tag cases with any number of keywords that could be searched upon, hence the keyword field on the case enquiry screen.

To make it easier for entry, keywords are extracted from the title and attached to the case at the time the case is added. I am pretty sure that the there isn't a site control to stop this happening so they are always created and you probably have a big keyword table - could be wrong here though.

What you can also do is define certain keywords as Stop Words. Stop words are pieces of text that won't be created as keywords. For instance, "the", "and", "a", etc... are words that you don't want cluttering up your keyword list, presuming you care!

Stop words can be defined as Case Stop Words or Name Stop Words and this is where it is potentially interesting. If the keyword is a Name Stop Word then the system uses this information when automatically creating the default Search Key 1 when a name is added. The entered name is used but any Name Stop Words are removed.

For example, making "the" and "company" Name Stop Words will mean that the Search Key 1 for the name "The Proctor & Gamble Company" will default to "Proctor & Gamble". This facilitates makes easy searching for the end user and removes the need to change the search key manually.

Now, the catch. The only place that I am aware of where you can define a Stop Word is from the Keyword picklist but not the keyword picklist on the case enquiry screen. You have to go to the keyword picklist that is available from the Keywords tab in Cases, so you will need a program that has screen control to give you access to that tab.

Inprotech: Refunding Money to Clients

The Accounts Receivable module is a little clunky when it comes to giving money back to clients. A little clunky is a bit of an understatement, it doesn't actually handle it at all so there is a workaround. Luckily, giving money actually back to customers is not something that happens all that frequently (rather than leaving the credit on account).

Assume that the client's account is in credit. Maybe this is because they paid a deposit in the first instance and it wasn't all expended or you over estimated the associates charge (or the exchange rate moved!) or there was a refund of fees from the IP office because of overpayment of fees (might have to write another little story as to how to handle that as well!).

Whatever the reason, the client is in credit, you have sent them a statement so they know and they want their money back rather than keeping it on account for future work. Somehow you have to get the funds off the debtors ledger and back into their hands.

Here's what to do:

1. Set up a reason of Client Refund, applicable only to AR.
2. In your GL Interface mapping (you brave enough to go in there?) go to the Debtors Revenue accounting and set up a mapping for the Client Refund reason to go to Suspense in your General Ledger (I am assuming everyone has one of these accounts).
3. In Accounts Receivable raise a debit journal against the client account that matches the amount in credit. Use the new Client Refund reason. The mapping that you have just set up will cause the Suspense account to be creditted.
4. Apply the outstanding credit against the debit item just raised so that it is cleared off the client's debtor account.
5. You now need to get the money to the customer. Go to the Accounts Payable and if there is no tax applicable use Manual Payment function to raise the payment. Use the Account only facility. Enter Suspense account as the applicable expense account. The debit created here will nett off against the credit raised in AR.
6. If however there is some tax applicable as a part of the refund you will need to raise an invoice to sort the tax out. The expense account in this instance still must be Suspense so that the item raised in AR is cleared and Suspense can reconcile. You then pay the invoice through manual payment selecting the entered invoice as the one to be paid.

The issue, of course, is that you need a supplier to be able to raise the cheque and/or payment. One way to do this is to make the client a supplier as well so the payment process can be run. I'm not a big fan of this because it clutters up your supplier list with clients for transactions that probably only occur once or twice for the customer.
My preferred method is to have a Miscellaneous Supplier for one off payments rather then set the specific supplier up. This is not just refunds but any spurious invoice that comes along rather as it saves setting up the supplier each time.
This way you can just enter the details of the client and the refund into the description field and this can be printed on the remittance advice and by doing a manual payment you can type in the Drawer Name to print on a cheque, if that's how you are paying them. If its by EFT you have to raise that manually but at least you have a remittance advice!
Phew!! That was easy. Happy to take questions at this stage. Yes, you in the front row....

Inprotech: Finding Criteria

While I am thinking about it there was another time recently when a piece of, what I thought was, well known functionality was seen for the first time and commented upon how useful it was. This was a little gem under the Navigator menu in the Control program.

The normal way to find a criteria is to enter in as few attributes as possible, typically, case type, country, property type and maybe action, to get as short a list as possible to find the criteria that you are after. This will take three or 4 clicks and tabs and a hit of the Refresh button and then a short search visually to find the criteria you want.

Not too painful but you generally work on one criteria at a time and usually know the criteria number from the record that has created an issue. If this is the case, select the Navigator menu and the Find Criteria item, a dialog will be displayed. Type in the criteria number, eg. -6277, 788, 14, and hit enter: the criteria will be found immediately and displayed in the criteria table!!

Inprotech: Moving Columns

I was showing someone the Rates Maintenance program the other day, funnily enough to have a look at the charge out rates that had been set up!!

Of course looking at all the rates as an overview is typically difficult as there is no report. It is also not helped by the fact that the sequence of the columns displayed, while ordered like the fields on the screen, is not really consistent with how rates are typically set up eg. staff classification, staff member, local/foreign, etc...

To alleviate this I dragged the columns into the display sequence I wanted so that the relevant columns were at the start of the table rather than having to scroll horizontally to see them. I then moved the empty and hence meaningless columns to the right (and off the screen) so they didn't get in the way.

To my surprise the person watching said "I didn't know you could do that". It is in fact a standard piece of functionality for all tables displays in the system. You simply click on the column header and drag it to the position you want. You can also re-size the columns to the width required so that the display or the data is easier to read.

Unfortunately, the program doesn't remember these settings, which would be fantastic, but it is really easy to do and can provide value for the session you are working on in some instances.

I presume everyone knows about right clicking on tables so that an Excel CSV file can be created of the contents of the table (in the adjusted column sequence!) so that a report can be quickly created without having to resort to SQL.

Inprotech: The Case Pick List and Other Numbers

I have recently been involved with a client where a new partner has joined and brought along with them a swag of cases. The cases were loaded into Inprotech and what we did was set up a new number type called Previous Case Number and loaded into that the reference number from the previous system.

The advantage of doing this is that in the Timesheet and Billing programs (or any program where the case pick list is used to find and validate case numbers) the old reference number can be typed into the case picklist field and the case will be found, displaying the new IRN once found.

This is a nice way of easing in the change over of numbers as the case can be found with either number. This functionality works with all numbers registered against the case so if you have the application number or the registration number, but not the IRN, you can type this in and the case will be found.

The trick is that you have to type in the entire number though as it has to be an exact match. If it doesn't find an exact match it assumes you are searching by IRN and behaves accordingly.

The Fees List Program

The Fees List program is one of my favourite programs in Inprotech. It's goal is to produce a list of fees to be paid to the local IP office for a day. The items don't necessarily have to have an amount against them so a full lodgement list of documents can be produced. People who aren't using this program must be using Word or, more likely, Excel and while this is probably an acceptable "manual" workaround it does mean the they run the risk of typing errors on the applicants name, the official numbers and the reference details. Not to mention the extra work of re-typing information already in the system.

Older clients would have a licence for the module as a part of the Cases & Names module before the pricing was changed to make it a separately purchaseable module a few years back.

What I like about the program is that it's simple to enter data and use, delivers high value output easily and caters for most situations. When the Fees & Charges module is used to automatically create the entries it is exactly how integration should be used for productivity benefits.

It does have a few areas I'd like improved though and the ones that I think would be relatively easy to do are:

1. Allowing sorting of the list and unattached items tables by the different columns.
2. Printing the list in the selected sequence.
3. Adding the date/time the item was added as information against the fee items and allow sorting.
4. Allowing the Fees List name to be included on the report.
5. Displaying the Property Type on the bottom half of the screen in the Fees List picklist.
6. Remove blocking the attachment of unattached items against a list with a different country, warn only.
7. When printing the list:

• Default the Print Draft flag to be on.
• Remove the need for the Bank and Account fields to be mandatory to run the report or at least link it to the VerifyFeesListFunds site option
• Get rid of the meaningless error message after you run the report

The bigger enhancement would be for those users of the accounting modules. If the system could create a paid invoice in Accounts Payable on the Cash Book that would be fantastic.

Name Aliases & Account Codes

I am currently working on a project that requires upload of Accounts Receivable and Accounts Payable information from another account system into Inprotech's accounting modules. To do this we have to get the account number from old system and record it in the Inprotech account number field so that the SQL that is written for the data load can match against the correct name.

The account code field is actually stored in the Name Alias table on the Name Alias tab in names. This tab was created many years ago to record, I think, the British IPO Client Number so that it could be written to British IPO forms using PassThru. The idea of the table was to also allow other name cross reference codes to be recorded against the name and hence an extra tab for Name Aliases and the picklist of Name Alias types. The account code is a system type and when completed a field on the main name becomes enable at the top of the screen to allow direct entry of the account code.

Name Aliases can be searched using the name picklist advanced filter dialog to find the applicable name. Unfortunately, this can take a bit of effort for the user to find and hence is probably not that much value. A better option is to place the Account Code in Search Key 2 (remembering to turn on related site option). This way the user can type in the name, the Inprotech name code or the account code from the previous system and the name will be found. This is a good aid in the change over of systems allowing the operators to maintain a level of familarity with codes that they have been working with as they get used to the new system.

If you don't want to see Name Aliases in the Names program there is a site option that turns it off.