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.