Stored Procedure Name:  Allocations

The following article describes how to use the allocations SP to create a custom payment report

When payments are applied to bills/invoices, an allocation is created that tracks which timekeeper and billing code the payment was applied to.  The sum of all allocations for a particular payment distribution is equal to the amount of the payment distribution.  Multiple payment distributions can exist per payment.  This stored procedure returns values to create custom timekeeper and expense payment reports.

Please read the following article before attempting to create a custom report using this stored procedure:

Using SQL Stored Procedures to Create Custom Reports

Report Variables

The sections below contain the columns names, filter variables and special features of this report needed to create custom reports using this report stored procedure.

Stored Procs:

• cr_Billing_Filter_   = 'Allocations'
• cr_Billing_Data_    = 'Allocations'

Available Column Field Names:

• Payment Type (Payment or Credit) 
  • Description: Whether the allocation was for a actual payment, or application of a credit. Returns 'Payment' or 'Credit'
  • Field Name 'PaymentType'
  • Data type: 'Text'
• Fee or Expense
  
  • Description: Was the allocation applied to fees or Expenses) Returns value of 'Time' or 'Expense'
  • Field Name 'Type'
  • Data type: 'Text'
• The Accounting Account Credited
  
  • Description
    • If using accounting, will return account the allocation was credited to
  • Field Name 'AccountName'
  • Data type: 'Text'
• The Amount of the Allocation
  
  • Description: The amount of the payment allocation
  • Field Name 'Amount'
  • Data type: 'Decimal'
• Timekeeper (for Fees)
  • Description: This is the timekeeper for a fee allocation.  Normally empty for expenses but could contain the user that entered the expense.
  • Field Name 'UserName'
  • Data type: 'Text'
• Allocation Billing Code
  • Description: The billing / expense code associated with the allocation. A single payment distribution will subtotal allocations into both timekeepers and billing codes.
  • Field Name 'BillingCode'
  • Data type: 'Text'
• Date of Payment
  • Description: Date of top level payment.
  • Field Name 'DateReceived'
  • Data type: 'Date'
• Allocation Method 
  • Description: Indicates if when the payment distribution was created it was manually allocated rather than auto allocated.  Returns true if user edited allocations at time of  payment receipt or later.
  • Field Name 'IsManuallyAllocated'
  • Data type: 'Text'
• Matter Name
  • Description: Name of the matter/case payment distribution was applied to
  • Field Name 'BillableName'
  • Data type: 'Text'
• Client Name
  • Description: Name of the client (contact) the payment was applied to
  • Field Name 'ClientName'
  • Data type: 'Text'

Available Filters and Filter Settings:

• Payment Allocation Date Filter
  
  • Used in column 'DateReceived'
  • Filter Type = 'Date'
  • Sql Param Name =  'minDate|maxDate'
  • Filter Default = see filter default section for different options.
  • Filter Conditions = blank
  • Param in Data = unchecked
• Timekeeper Filter
  • Used in column 'UserName'
  • Filter Type = 'Lookup'
  • Sql Param Name =  'userid'
  • Filter Default = blank
  • Filter Conditions = '1'
  • Param in Data = unchecked
• Type of Payment (Payment or Applied Credit)
  • Used in column 'PaymentType'
  • Filter Type = 'Radio'
  • Sql Param Name =  'paymentTypeId'
  • Filter Default = Choose 0 or 1 based on Filter Condition below
  • Filter Conditions = '0:Payments|1:Applied Credits'
  • Param in Data = unchecked
• Fee or Expense
  • Used in column 'Type'
  • Filter Type = 'Radio'
  • Sql Param Name =  'typeid'
  • Filter Default = Choose 0 or 1 based on Filter Condition below
  • Filter Conditions = '1:Fees|2:Expenses Received'
  • Param in Data = unchecked
  • typeid (DEFAULTS to fees. 1 for fees, 2 for expenses)
    
    
• Matter
  • Used in column 'BillableName'
  • Filter Type = 'Lookup'
  • Sql Param Name =  'billableid'
  • Filter Default = blank
  • Filter Conditions = '1001'
  • Param in Data = unchecked
    
    
• Client
  • Used in column 'ClientName'
  • Filter Type = 'Lookup'
  • Sql Param Name =  'clientid'
  • Filter Default = blank
  • Filter Conditions = '3'
  • Param in Data = unchecked
    
    
• Billing Code
  • Used in column 'BillingCode'
  • Filter Type = 'Lookup'
  • Sql Param Name =  'billingCodeId'
  • Filter Default = blank
  • Filter Conditions = '31'
  • Param in Data = unchecked
  • Special Columns Values

Advanced Options

To specify the sort order of the report

To control how the report sorts, following the directions below

• Add a new Column called Sort Direction
  • Description: This column will instruct the report how to sort, but will not show in the report
  • Field Name 'SortDirection'
  • Data type: 'Text'
  • Filterable = checked
  • Filter Type = 'Hidden'
  • Sql Param Name =  'sortOrder'
  • Filter Default = 'datereceived;;billablename'
    • Specify column names in format above, in the order you wish the report to sort
  • Filter Conditions = blank
  • Param in Data = unchecked