report assistant for microsoft dynamics sl cash manager...

Report Assistant for Microsoft Dynamics

® SL

Cash Manager Module

Last Revision: October 2012

(c)2012 Microsoft Corporation. All rights reserved. This document is provided "as-is." Information and

views expressed in this document, including URL and other Internet Web site references, may change

without notice. You bear the risk of using it.

Some examples are for illustration only and are fictitious. No real association is intended or inferred.

This document does not provide you with any legal rights to any intellectual property in any Microsoft

product. You may copy and use this document for your internal, reference purposes.

Trademark

Microsoft, ActiveX, Excel, FRx, FrontPage, MapPoint, Outlook, SharePoint, Visual Basic, Visual Studio,

Windows, Windows and Windows Server are either registered trademarks or trademarks of Microsoft

Corporation, FRx Software Corporation, or their affiliates in the United States and/or other countries. FRx

Software Corporation is a subsidiary of Microsoft Corporation. Business Objects and the Business

Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, and other Business Objects products

and services mentioned herein as well as their respective logos are trademarks or registered trademarks of

Business Objects Software Ltd. Business Objects is an SAP company.

The names of actual companies and products mentioned herein may be trademarks or registered marks -

in the United States and/or other countries - of their respective owners.

The example companies, organizations, products, domain names, e-mail addresses, logos, people, places,

and events depicted herein are fictitious. No association with any real company, organization, product,

domain name, e-mail address, logo, person, place, or event is intended or should be inferred.

Warranty disclaimer Microsoft Corporation disclaims any warranty regarding the sample code

contained in this documentation, including the warranties of merchantability and fitness for a particular

purpose.

Limitation of liability The content of this manual is furnished for informational use only, is subject to

change without notice, and should not be construed as a commitment by Microsoft Corporation.

Microsoft Corporation assumes no responsibility or liability for any errors or inaccuracies that may

appear in this manual. Neither Microsoft Corporation nor anyone else who has been involved in the

creation, production or delivery of this documentation shall be liable for any indirect, incidental, special,

exemplary or consequential damages, including but not limited to any loss of anticipated profit or

benefits, resulting from the use of this documentation or sample code.

License agreement Use of this product is covered by a license agreement provided with the software

product. If you have any questions, please call the Microsoft Business Solutions Customer Assistance

Department at 800-456-0025 (in the U.S. or Canada) or +1-701-281-6500.

Table of Contents

How to use this Manual 1

Sort and Select statements 5

Overview 6 Select Statements 7 Sort Statements 18 Possible Values Chart 23

Data Field Illustrations 25

Overview 26

Cash Account Transactions – Screen 20.010.00 27

Cash Account Transfers – Screen 20.020.00 28 Daily Cash Balances – Screen 20.200.00 29 Cash Account Balances Sorted By Period – Screen 20.200.01 30

Cash Account Balances Sorted By Date – Screen 20.200.04 31 Bank Reconciliation – Screen 20.210.00 32

Reconcile Deposits – Screen 20.210.03 33 Reconcile Checks – Screen 20.210.01 34 Cash Account Maintenance – Screen 20.250.00 35

Entry Type Maintenance – Screen 20.260.00 36 Recurring Item Maintenance – Screen 20.270.00 37

Cash Flow Maintenance – Screen 20.280.00 38

Clear Check/Deposit Data Import – Screen 20.420.00 39

Clear Checks/Deposits – Screen 20.440.00 40 CA Setup – Screen 20.950.00 41

Clear Check/Deposit Import Setup – Screen 20.970.00 42

Report Information 43

Overview 44 Check Register – Screen 20.600.00 46 Daily Cash Balance – Screen 20.610.00 47 Check/Deposit Reconciliation – Screen 20.620.00 48 Unmatched Item List – Screen 20.625.00 49

Bank Reconciliation – Screen 20.630.00 50 Float Analysis – Screen 20.640.00 51

Cash Flow Projection – Screen 20.650.00 52 PTD Transaction List – Screen 20.660.00 53 PTD Distribution List – Screen 20.670.00 54 Cash Account List – Screen 20.680.00 55 Entry Type List – Screen 20.690.00 56

Recurring Item List – Screen 20.700.00 57 Cash Flow Items List – Screen 20.710.00 58 Cash Transactions History – Screen 20.720.00 59

Cash Manager Batch Register – Screen 20.800.00 60

CA Edit – Screen 20.810.00 61

H O W T O U S E T H I S M A N U A L

Report Assistant – Cash Manager

2 Page

Flexible reporting is an important part of a company’s ability to effectively

operate their business. This manual is intended to assist you with flexible

reporting in the Dynamics SL Cash Manager module. This manual is useful

during creation or modification of reports in the Crystal Report writer. The

manual is divided into three parts:

Sort and Select Statements – The first section describes the differences between

Sort statements and Select statements. This manual also describes each of the

parameters that can be used in a sort or select statement and gives examples of

how to use each parameter. This section is a good reference for deciding what

Operator to use, and whether to use a Select statement or a Sort statement.

At the end of this section, a Possible Values Chart is included. The chart

displays values stored in Dynamics SL for some of the more common fields in

the Cash Manager module. For example, document type values, batch status

values, and document status values. When performing Sort and Select statements,

you may not be sure of the values for some of these types of fields. The section

displays some common fields with possible values.

Data Field Illustrations – The second section contains a picture of data entry

screens and maintenance screens for the Cash Manager module. There is a

reference for each field in the screen noting the table and field where the data is

stored in the Dynamics SL data files, as noted through the Customization

Manager module. For example, when entries are made in the Cash Account

Transactions (20.010.00) screen, the values entered in the Account field are

stored in the Catran table, in a field named acct. The illustration in the second

section of this guide with the Cash Account Transactions screen includes a

reference to the Account field, noting the value for the field is stored in

Catran.acct.

When printing a report, determining the field to use in a Sort or Select statement

is not always easy. You may know the field where the data is entered in a screen,

but you are unsure of the field and table where the data is stored. With the

information in this section, you can quickly determine the entry needed in the

Sort or Select Field.

How To Use This Manual

Page 3

Report Information – The third section contains pertinent information about

each report in the Cash Manager module. The following information is contained

in the section:

Master table – When using Sort and Select statements for printing

reports, knowing the master table name is important. If the field

chosen for the Sort and Select statement is from the master table, the

result can be faster report processing and more accurate results.

Sort order – If a report is sorted differently than designed, report

totals and subtotals may be inaccurate. Knowing the fields the report

currently uses for the sort order may help you produce accurate

reports when using sort statements.

Report name as stored in Crystal – If you need to access the report

for modification or copying, the report name is important. Many

reports have multiple formats available, and determining the correct

report to retrieve is more difficult.

Sort/select field examples – A table is included with Sort and Select

fields for many of the more commonly used report filters for each

report.

S O R T A N D S E L E C T S TAT E M E N T S


6 Page

Overview

Each of Dynamics SL’s report screens includes a Sort tab and a Select tab that

allows you to specify parameters used to limit the information included on

reports. This section is intended to assist you with using these Sort and Select

options.

What is the difference between a Sort statement and a Select statement? Select

statements limit the records to be included in a report. For example, if you enter a

Select statement to include only one Customer ID in a report, only records

meeting the specified criteria is included in the report. If no records are found to

match the Select parameters entered, the report prints, but no data displays.

Sort statements modify the sort order of the report. The report contains all of the

data that is in the original report, but the Sort statement changes the order of the

records when the records are printed.

You can create multiple Sort and Select statements in one report.

The Sort and Select tabs are available for all reports. However, the available

fields for Sort and Select statements vary based on the tables associated with

the report.

When creating Sort and Select statements, be aware that the results might not

always be what you expect. Reports are written with specific grouping criteria

and sort orders. When other fields are entered for Sort or Select parameters, the

report may not print or total properly. Section 3 of this manual details the master

table and default sort order for each report.

Sort and Select Statements

Page 7

Select Statements

As noted earlier, a Select statement is used to limit the records to be included in a

report. A Select statement is similar to a filter. The Select tab is used to specify

fields, operators, and values that control the records to be included in the report.

The Select tab contains multiple rows, with four fields per row. These four fields

include: Field, Operator, Value and Boolean. These fields are discussed below,

along with examples.

FIGURE 1 – SELECT TAB


8 Page

Field

The Field field is used to specify the table and field name to be used to filter the

records in the report. In the Field area, you can use Dynamics SL’s F3 inquiry

function to view a list of available tables and fields for the report. Sections 2 and

3 of this guide can be used as a reference for these field names.

NOTE: The list of available fields may include fields that are not used in the

report. If you use a field in the Select statement that is not used in the report,

the report may be blank. See Section 3 for examples of fields commonly used

for Sort and Select statements for each report.

Occasionally, you may need to add a field to the Field list to use in a Select

statement. Adding the ROI SELECT function to the report through Crystal

Reports can be used to add the field. Refer to Technical Bulletin 2426 for

information on this process.

NOTE: ROI stands for Report Options Interpreter.


Page 9

Operator

The Operator field controls the type of filter to apply to the records when the

system is gathering data for the report.


Begins With

The Begins With Operator is useful when you need a report for a group of

records with a common factor. For example, if the income account numbers

begin with three, the following statement is used to print a report for cash

transactions recorded to income accounts.

Field Operator Value Boolean Vr_20600catran.acct Begins with 3 And

Between

Use Between to include records on the report between a beginning and ending

range. In the Value field, separate the first and second values with a comma. In

the following example, the report includes data for cash transactions with account

numbers 3030 through 3095, inclusive.

Field Operator Value Boolean Vr_20600catran.acct

Vr_20600catran.acct

Between

Between

3030,3095

3030 and 3095

And

And

NOTE: When using between, the report includes records matching the first

and second values specified. For example, accounts 3030 and 3095 are

included in the report.


10 Page

Contains

The Contains Operator is used to select records that have a specified value in a

field. In the following example, the statement used displays records for

transactions that have Refund in the description.

Field Operator Value Boolean Vr_20600catran.trandesc Contains Refund And

Equal

Use Equal for an exact match. In the following example, only records for entry

type ‘MD’ are included in the report.

Field Operator Value Boolean Vr_20600catran.entryid Equal MD And

Greater than

Greater than is used to include records larger (higher) than the value specified. In

the following example, transactions with amounts greater than $2999.00 are

included in the report.

Field Operator Value Boolean Vr_20600catran.tranamt Greater than 2999 And

Greater than or equal to

Greater than or equal to is used to include records containing the value specified,

as well as records greater than the value. In the following example, all

transactions for amounts greater than or equal to $3000.00 are included in the

report.

Field Operator Value Boolean

Vr_20600catran.tranamt Greater than or equal

to

3000 And

In

The In Operator works similarly to the Equal Operator, but allows you to specify

multiple items in the Value field. In the example below, records for subaccounts

1015, 2015, and 2020 are included in the report. If there are no records for one

of the values specified, the record does not print. However, records for the other

two values print on the report.

Field Operator Value Boolean Vr_20600catran.sub In 1015,2015,2020 And


Page 11

Is NULL

Using the Is NULL Operator includes records on the report where the selected

field is empty. In the example below, the report includes transactions where no

entry was made in the reference number field. When using Is NULL, the Value

field must be left blank.

NOTE: The Is NULL operator is not applicable with this version of Dynamics

SL.

Field Operator Value Boolean Vr_20600catran.refnbr Is NULL And

Is not NULL

Is not NULL works the opposite of the Is NULL Operator. Is not NULL is used

to include items on a report where a specified field is populated. In the example

below, the report prints only transactions that have a value in the reference

number field.

NOTE: The Is not NULL operator is not applicable with this version

of Dynamics SL.

Field Operator Value Boolean Vr_20600catran.refnbr Is not NULL And

Less than

The Less than Operator is the opposite of the Greater than Operator. The Less

than Operator is used to select records where a specified field is less than the

amount in the Value field. In the example below, the report includes records

with transaction amounts less than $3000.00.

Field Operator Value Boolean Vr_20600catran.tranamt Less than 3000 And

Less than or equal to

The Less than or equal to operator works the same as the Less than Operator. The

Less than or equal to Operator also includes records with a value equal to the

amount in the Value field. In the example below, the report includes records with

transaction amounts of $1,999.00 or less.

Field Operator Value Boolean Vr_20600catran.tranamt Less than or equal to 1999 And


12 Page

Not between

The Not between Operator is used to print a report for values outside a particular

range. For example, you need a report that includes all accounts except 1015,

1020 and 1025.

Field Operator Value Boolean Vr_20600catran.acct Not between 1015, 1025 And

Vr_20600catran.acct Not between 1015 and 1025 And

NOTE: When using Not Between, the report does not include records

matching the beginning and ending values specified. For example, accounts

1015 and 1025.

Not contains

The Not contains Operator is used to exclude data from a report with broad

specifications. For example, you want to exclude all transactions that do not have

Refund in the transaction description field. Note that any transaction with Refund

in the transaction description is excluded.

Field Operator Value Boolean Vr_20600catran.trandesc Not contains Refund And

Not equal

The Not equal Operator is used to exclude specific data from a report. For

example, the report excludes all batches with a status of H (Hold).

Field Operator Value Boolean Batch.status Not equal H And

Not in

The Not in Operator is similar to the Not equal Operator, but allows you to

specify multiple items in the Value field. In the example below, the report

excludes batches with status H and B (Hold and Balanced).

Field Operator Value Boolean Batch.status Not in H, B And

Batch.status Not in H and B And


Page 13

Value

The Value field is used to specify data that Dynamics SL uses to compare with

when selecting records to be included or excluded in the report. The previous

section includes many examples for the Value field. However, there are

additional functions available.

NOTE: The Value field is limited to 70 characters.


NOTE: The Possible Values section on Page 23 contains helpful information

about values for True and False fields, Active and Inactive fields, and

checkboxes.

Wildcards

Dynamics SL supports the use of wildcards in the Value field of select

statements. In the following example, the report includes information for all

subaccounts with AA as the sixth and seventh characters. For example,

subaccounts 01-100-AA-00-00-1, 02-223-AA-00-00-1, and 03-000-AA-00-00-1.

Field Operator Value Boolean Vr_20600catran.sub Equal ?????AA????? And


14 Page

A question mark (?) is used when one character needs to be masked. When a

variable number of characters or multiple characters need to be masked, an

asterisk (*) is used. By changing the value in the previous example to the

following example, Dynamics SL returns all subaccounts with segment AA

included anywhere in the subaccount.

Field Operator Value Boolean Vr_20600catran.sub Equal *50* And

Date Values

When using date values, you need to include the forward slashes (/) in the date.

Quotes are not necessary. The month and day may be typed with or without

leading zeroes, and the year may be typed with two or four characters.

Field Operator Value Boolean Vr_20600catran.trandate Less than 3/3/06 And

Vr_20600catran.trandate Greater than 07/08/2006 And

Dashes and Other Special Characters

Many fields in Dynamics SL may contain dashes or other special characters that

require special consideration when specifying values for Sort and Select

statements. Sometimes the values must be specified with the special characters.

The governing factor is whether the special characters must be typed during

regular data entry. For example, there are subaccounts with multiple segments

and the segments may be separated with dashes or other characters. During data

entry, the system fills the separator for you. When making an entry for

subaccount 05-25, you only need to type 0525 in the data entry screen. In this

case, if you want to use subaccount 05-25 as a Value for a Select statement, type

0525 in the Value field.

In other instances, the special characters may be required. For example,

Inventory IDs can be set up with only one segment, and then dashes can be used

in the ID field when creating Inventory IDs. In this case, since the dash is not a

separator between segments, the dash must be typed during data entry and when

used in a Select statement.

For items such as phone numbers, zip codes, and social security numbers, the

system supplies the special characters during data entry. Therefore, the characters

do not have to be typed during data entry or when used in a Select statement.

In general, the way to test that special characters need to be specified is to open a

screen where the item is typed. If you do not have to type the special characters

during data entry, then you do not specify the characters in the Select statement.


Page 15

Fiscal Periods

Special care must be taken when using fiscal periods in Select statements. In

many of the report screens, there are fields used to specify the fiscal periods to be

included in the report. The fiscal period(s) specified in the report screen are used

to select the data based on the Period to Post field associated with documents or

transactions

In the Select tab, parameters can be specified to select data based on the Period

Entered, Period Closed, and Period to Post. If the report screen includes fields for

restricting the periods to be reported and you specify a select statement for Period

to Post with a different Period to Post, the report is blank because the data cannot

meet both criteria.

The PTD Transaction List (20.660.00) report screen includes fields to specify

the beginning and ending fiscal periods for the report. If you type 01-2006 in

both fields, the report includes only transactions posted to 01-2006. If you then

specify the following select statement in the Select tab, the report is blank:

Field Operator Value Boolean Vr_20600catran.perpost Equal 200602 And

Although fiscal periods are displayed in screens and reports as month then year,

the values are stored in the data files as year, then month. Therefore, fiscal

periods must be typed in Select statements as shown in the previous example.


16 Page

Boolean

Often, you may need to specify more than one Select statement in order to print

the needed information on the report. The Boolean value defines how multiple

Select statements are used.


NOTE: The And Boolean on the first line is inactive. The first Boolean is used

to join this select criteria to the existing select criteria of the report defined in

the Crystal Report Writer.


Page 17

And – When And is used for the Boolean value, each record to be included in the

report must match the values for both of the Select statements. In the following

example, the resulting report includes records that have account numbers

beginning with 3 and subaccount equal to 105035.


Vr_20600catran.sub Equal 105035 And

Or – When Or is used for the Boolean value, records are included in the report if

the records meet the specifications of one of the Select statements. In the

example below, records are included in the report if the account equals 7200 or

the amount is greater than $3,000.00.

Field Operator Value Boolean Vr_20600catran.acct Equal 7200 And

Vr_20600catran.tranamt Greater than 3000 Or

Boolean values can be used to create multiple criteria select statements. This

feature allows you to narrow report results as needed. For example, if you need a

report showing transactions for income accounts, subaccount 105075, and

transaction dates of 12/15/06 or 12/31/06, use the following example to

accomplish this report.


Vr_20600catran.sub Equals 105035 And

Vr_20600catran.trandate In 12/15/06, 12/31/2006 And


18 Page

Sort Statements

The Sort tab contains multiple rows, with five fields per row. When creating Sort

statements, the following five fields are used: Field, Sort Type, Sort Ascending,

Page Break, and Total Break.

FIGURE 5 – DEFAULT SORT TAB FOR BATCH NUMBER FORMAT OF PTD TRANSACTION

LIST

Field

The sort Field is used to specify the table and field name to use for the sort. For

example, the PTD Transaction List (20.660.00) report is designed to sort by

bankcpnyid, module, batnbr, trandate, refnbr, and linenbr.

FIGURE 6 – PTD TRANSACTION LIST WITH DEFAULT SORT CRITERIA


Page 19

If you want to sort the report by acct instead of batnbr, you must specify the

following information in the Sort statement.

FIGURE 7 – SORT ON ACCT FIELD INSTEAD OF BATNBR.

FIGURE 8 – PTD TRANSACTION LIST WITH SORT CRITERIA ON ACCT INSTEAD OF BATNBR

NOTE: When in the Field area, you can use Dynamics SL’s F3 inquiry

function to view a list of available tables, views, and fields associated with the

report. Sections 2 and 3 of this guide can also be used as a reference for field

names.

The Sort Type field describes the type of field identified in Field field. Group

Field indicates that the field is a report group that allows page and total breaks.

Sort Field indicates that the report data is sorted by the values in the field, within

existing groups. Groups are always processed first, followed by Sort fields.


20 Page

Sort Ascending

The Sort Ascending field is used to specify whether the values are sorted in

ascending or descending order. To sort a report in ascending order, check the

Sort Ascending field. To sort a report in descending order, uncheck the Sort

Ascending field.

Page Break

Checking the Page Break field causes the report to begin a new page when the

value in the Sort Field changes. The Page Break option is useful if you want to

print transactions associated with an account, one per page.

For example, you want to print the PTD Transaction List (20.660.00) report for

each department manager. The following Sort criteria prints all of the account

information for the first account, performs a page break, and prints the account

information for the second account.

FIGURE 9 – PAGE BREAK ON THE ACCT FIELD

FIGURE 10 - PTD TRANSACTION LIST WITH PAGE BREAK BY TRANSACTION ACCOUNT


Page 21

Total Break

The Total Break field is used to change the total grouping of a report. There are

two important factors to keep in mind when creating Total Breaks. First, the

report must already have total rows included in the report. If the report is not

written with a total row, the following message displays when you try to process

a report with a Total Break:

The second factor to keep in mind is that you need to specify a sort order for the

field or the Total Break does not calculate. Usually you specify the field for the

Total Break as the first sort order.

For example, the PTD Transaction List (20.660.00) report sorts by batch

number and prints a total when the batch number changes.

FIGURE 11 – PTD TRANSACTION LIST WITH DEFAULT TOTAL BREAK


22 Page

FIGURE 12 – PTD TRANSACTION LIST WITH TOTAL BREAK ON THE ACCT FIELD

CAUTION: The Total Break does not always work properly. Therefore,

review the report carefully if you have selected a Total Break.

Up and Down Buttons

The Up and Down buttons located on the right side of the Sort Tab allows you to

change the hierarchy of the sort commands. Clicking the Up button moves the

sort command higher in the list. Clicking the Down button moves the sort

command lower in the sort order list.

Reset Button

Click the Reset button to restore the default grouping and sorting criteria from

the report. This feature allows you to make changes and, if you do not like the

changes, to reset the original values.

Apply Button

Click the Apply button to implement the changes you have made for this specific

report generation.


Page 23

Possible Values Chart

The Value that is stored in a field might not always be obvious. The following

chart is intended to assist with values that are stored in some of the Cash

Manager fields. The Value/Format field is the value you specify in the Value

field of a Select statement.

Field Type Value/Format

Batch Status:

On Hold H

Balanced B

Released – Not posted to the GL U

Posted to the GL P

Released – Does not effect the GL C

Partially Released S

Voided V

Deleted D

Journal Type:

General Ledger GL

Accounts Payable AP

Accounts Receivable AR

Cash Manager CA

Payroll PR

Currency Manager CM

Entry Type or Transaction Type:

AP Check CK

Interest Earned IN

Miscellaneous Disbursement MD

Miscellaneous Receipt MR

AR Deposits PA

Service Charges SC

Transfers In TI

Transfers Out TO

Offsetting Entry ZZ

Reconciliation Status:

Cleared C

Outstanding O

Date fields 12/05/2006 or 12/5/06

Period fields such as Period to Post

and Period Entered.

200612

Fiscal Year fields 2006


24 Page

Dynamics SL stores several fields as True/False fields using the values of 1 for

True and 0 for False.

When a value is a True or False, such as the Released field,

Dynamics SL stores True as a 1 and False as a 0.

If a field has a value of Yes or No, Dynamics SL stores Yes with a

value of 1 and No with a value of 0.

If the field is a checkbox, the value is stored as a 1 if the field is

checked, and the value is stored as 0 if the field is not checked.

D ATA F I E L D I L L U S T R AT I O N S


26 Page

Overview

The information in this section contains a screen illustration of the data entry

screens and most maintenance screens in the Cash Manager module. The

information in this section is very useful when performing Sort and Select

statements, writing reports in Crystal Reports, using SQL statements to verify

information, or using ODBC.

Each screen displays with a callout box indicating the table and field name where

the data is stored as noted in the Fieldname field on the Property Window of the

Customization Manager module. Some data is stored in multiple tables and

fields. Additional tables and fields may be noted in the callout box. However,

there may be additional tables or fields where the data is stored.

NOTE: Some fields in data entry and maintenance screens are calculated or

temporary fields specifically for the screen. These fields, designated by the

text Calculated or Temporary, are not stored in the database. Since these

fields are calculated or temporary, these fields are not in Sort and Select

inquiry lists, in Crystal Reports tables, or field lists.

Cash Account Transactions – Screen 20.010.00

Batch.batnbr

Catran.batnbr

Batch.perpost

Catran.perpost

Catran.trandesc

Catran.noteid

Batch.status Batch.curyctrltot

Batch.ctrltot

Batch.curydrtot

Batch.drtot

Catran.rcnclstatus

Catran.cleardate

Batch.battype

Temporary

Batch.curycrtot

Batch.crtot

Catran.payeeid

Catran.pc_flag Catran.qty

Catran.refnbr

Calculated

Calculated

Calculated

Catran.payeeid

Catran.labor_class_cd

Catran.sub

Catran.taskid

Catran.projectid

Catran.acct

Catran.entryid

Catran.cpnyid

Catran.trandate

Temporary

Catran.employeeid


28 Page

Cash Account Transfers – Screen 20.020.00

Catran.bankcpnyid

Catran.bankacct

Catran.trsftocpnyid

Catran.trsftobankacct

Catran.trsftobanksub

Catran.refnbr

Catran.trandate

Catran.curytranamt

Catran.tranamt

Catran.banksub

Catran.trandesc

Batch.batnbr

Catran.batnbr

Batch.perpost

Catran.perpost

Batch.status Calculated

Batch.curyctrltot

Batch.ctrltot

Batch.curyctrtot

Batch.ctrtot

Batch.noteid

Batch.curyctrtot

Batch.ctrtot

Daily Cash Balances – Screen 20.200.00

Calculated Calculated

Cashacct.cashacctname

Cashacct.acctnbr

Cashacct.curyid

Calculated

Cashacct.banksub

Cashacct.bankacct

Cashacct.cpnyid

Calculated


30 Page

Cash Account Balances Sorted By Period – Screen 20.200.01

Cashsumd.curyreceipts

Cashsumd.receipts

Calculated

Cashsumd.pernbr


Cashsumd.trandate

Cashsumd.curydisbursements

Cashsumd.disbursements

Cash Account Balances Sorted By Date – Screen 20.200.04

Cashsumd.curydisbursements

Cashsumd.disbursements

Calculated


Cashsumd.curyreceipts

Cashsumd.receipts

Cashsumd.trandate

Cashsumd.pernbr


32 Page

Bank Reconciliation – Screen 20.210.00

Cashacct.cpnyid

Cashacct.bankacct

Bankrec.curdepintransit

Bankrec.depintransit

Cashacct.banksub

Bankrec.reconcileflag

Bankrec.stmtdate

Bankrec.recondate

Cashacct.curystmtbal

Bankrec.stmtbal

Calculated

Calculated

Bankrec.curyoutstandingchk

Bankrec.outstandingchk Calculated

Reconcile Deposits – Screen 20.210.03

Recondep.refnbr

Recondep.batnbr

Recondep.cleared

Recondep.clearamt

Recondep.curytranamt

Recondep.payeeid

Recondep.payeename

Recondep.module

Recondep.entryid

Recondep.cleardate

Recondep.trandate


34 Page

Reconcile Checks – Screen 20.210.01

Reconchk.cleared

Reconchk.clearamt

Reconchk.refnbr

Reconchk.curytranamt

Reconchk.cleardate

Reconchk.trandate

Reconchk.payeeid

Reconchk.payeename

Reconchk.module

Reconchk.entryid

Reconchk.status

Cash Account Maintenance – Screen 20.250.00

Cashacct.bankacct

Cashacct.cpnyid

Cashacct.banksub

Calculated


Cashacct.acctnbr

Cashacct.transitnbr Cashacct.addrid

Addr.name Bankrec.recondate

Calculated

Cashacct.active

Cashacct.acceptglupdates


36 Page

Entry Type Maintenance – Screen 20.260.00

Entrytyp.entryid

Entrytyp.descr

Entrytyp.dfltacct

Entrytyp.dfltsub

Entrytyp.rcptdisbflg

Entrytyp.active

Entrytyp.updarmod

Entrytyp.inclavgdays

Recurring Item Maintenance – Screen 20.270.00

Carecur.nbrcycle

Calculated

Carecur.curydocamt

Carecur.docamt

Catran.drcr

Catran.curytranamt

Catran.tranamt

Catran.trandesc

Catran.refnbr

Catran.sub

Catran.acct

Catran.entryid

Catran.cpnyid

Carecur.cycle

Carecur.frqofgen

Carecur.nextgendate

Carecur.banksub

Carecur.bankacct

Carecur.recurid

Carecur.cpnyid


38 Page

Cash Flow Maintenance – Screen 20.280.00

Cashflow.curyantrcpt

Cashflow.antrcpt

Cashflow.active

Cashflow.curyantdisb

Cashflow.antdisb

Cashflow.banksub

Cashflow.bankacct

Cashflow.cpnyid

Cashflow.descr

Cashflow.rcptdisbdate Cashflow.casenbr

Clear Check/Deposit Data Import – Screen 20.420.00

Cabtset.checkdepositfield

Cabtset.assumedecimalnumber

Cabtset.checkindicator

Cabtset.banksub

Cabtset.bankacct

Cabtset.cpnyid

Cabtset.cleardatefield

Cabtset.startingline

Cabtset.aborterrors

Cabtset.fielddelimiter

Cabtset.assumedecimalplaces

Cabtset.itemamountfield

Cabtset.itemnumberfield

Cabtset.depositindicator

Calculated

Cabtset.setupid


40 Page

Clear Checks/Deposits – Screen 20.440.00

Calculated

Banktran.checkdepositflag

Banktran.itemnumber

Banktran.itemamount

Banktran.cleardate

Banktran.importref

Calculated

Cashacct.banksub

Cashacct.bankacct

Cashacct.cpnyid

CA Setup – Screen 20.950.00

Casetup.pernbr

Casetup.lastbatnbr

Casetup.accepttransdate

Casetup.perretbal

Casetup.nbravgdat

Casetup.perrettran

Casetup.arholdingacct

Casetup.arholdingsub

Casetup.dfltrcnclamt

Casetup.showglinfo

Casetup.postgldetail

Temporary

Casetup.glpostopt

Casetup.showlastbankrecs


42 Page

Clear Check/Deposit Import Setup – Screen 20.970.00

Cabtset.SetUpID

Cabtset.cpnyid

Cabtset.bankacct

Cabtset.banksub

Cabtset.checkdepositfield

Cabtset.checkindicator

Cabtset.depositindicator

Cabtset.itemnumberfield

Cabtset.itemamountfield

Cabtset.assumedecimalplaces

Cabtset.assumedecimalnumber

Cabtset.cleardatefield

Cabtset.startingline

Cabtset.aborterrors

Cabtset.fielddelimiter

R E P O R T I N F O R M AT I O N


44 Page

Overview

The information in this section lists each report in the Cash Manager module,

along with the following information:

Report Formats and Report Names

This section lists the name of the report format from the Report Format field of

the report screen. Next to the Report Format is the name of the Report specified

in Crystal Reports. This information is also stored in the Rptcontrol table that is

part of the system database.

Master Table

Often times when you are looking at the inquiry list in the Sort or Select field of

a report, you may see the same field in more than one table. For example, the

account number field (acct) is in several tables. If you are specifying a Sort or

Select statement based on a field that is contained in multiple tables, the report

may process faster if you use the field from the master table. This section gives

you the master table or view that is used in the report.

NOTE: A View is a virtual table that has contents that are defined by a SQL

statement. A View is similar to a table with columns and rows of data, but

does not exist in the database as a stored set of data values. All of the reports

that are written with a View, rather than a master table, begin with the letter

V. For purposes of Sort and Select statements, a View is just like any other

table in a report and can be accessed in Sort and Select statements.

Default Sort Fields

This section lists the sort fields, in the same order as the fields are in the report. If

different fields are selected when sorting a report, the totals on the report may no

longer be accurate. When running a report with a Sort statement, use this section

of the manual to see the fields the report currently uses as sort fields to help you

ensure the sort field is a good choice.

Common Sort Examples

At the bottom of each report, there is a list of common Sort and Select fields that

you might use in the report, along with the possible values for the field listed.

Report Information

Page 45

Date Range Entry Screen

The Cash Manager module has provided an additional screen that when printing

certain reports called the Date Range Entry (20.630.01) screen. This screen

allows you to select a date range and/or the bank account used when the

report prints.

FIGURE 7 – DATE RANGE ENTRY SCREEN USED IN CASH MANAGER REPORTS.


46 Page

Check Register – Screen 20.600.00

Report Formats Report Name Master Table/View Default Sort Fields

Default only 20600 Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Wrkcadetail.refnbr

1 This field is a temporary field calculated for this report and is not stored in the

database. Therefore, the field is not available for Sort and Select statements in the

Field list.

The following table shows fields typically used for Sort and Select parameters for

this report:

Sort and Select Item Field Name Format / Possible Values

Reference Number Wrkcadetail.refnbr Valid check numbers

Payee ID Wrkcadetail.payeeid Valid Vendor ID

Transaction Date Wrkcadetail.trandate Valid date

Report Information

Page 47

Daily Cash Balance – Screen 20.610.00


Summary 20610s Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Wrkcabalances.trandate

Wrkcadetail.period

Detail 20610d Wrkcabalances Wrkcabalances.cpnyid

Acctsub1


Wrkcadetail.perpost

Wrkcadetail.module

Wrkcadetail.refnbr



Field list.


48 Page

Check/Deposit Reconciliation – Screen 20.620.00

Report Formats Report Name Master Table Default Sort Fields

Default only 20620 Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Acctsubdate1

Wrkcadetail.rcptdisbflg

Wrkcadetail.batnbr

Wrkcadetail.refnbr



Field list.


this report:


Check Number Wrkcadetail.refnbr Valid reference numbers

Reconciliation Status Wrkcadetail.cleared Cleared = 1, Outstanding = 0

Cleared Date Wrkcadetail.cleardate Valid date

Module Wrkcadetal.module GL, AP, AR, CA

Report Information

Page 49

Unmatched Item List – Screen 20.625.00

Report Formats Report Name Master

Table/View

Default Sort Fields

Default only 20625 Banktran Rptcompany.cpnyid

Banktran.bankacct

Banktran.banksub

Banktran.checkdepositflag

Banktran.itemnumber


this report:


Item Clear Date Banktran.cleardate Valid date

Check or Deposit Flag Banktran.checkdepositflag Deposits = D, Checks = C,

Unknown = U

Item number (check or deposit

number)

Banktran.itemnumber Valid item number


50 Page

Bank Reconciliation – Screen 20.630.00


Summary 20630s Wrkcabalances Wrkcabalances.cpnyID

Acctsub1


Acctsubtrandate1

Detail 20630d Wrkcabalances Wrkcabalances.cpnyID

Acctsub1


Wrkcadetail.rcptdisbflg

Wrkcadetail.refnbr



Field list.

Report Information

Page 51

Float Analysis – Screen 20.640.00


Bank Summary 20640bs Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Bank Detail 20640bd Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Wrkcadetail.trandate

Payee Summary 20640p Wrkcadetail Wrkcadetail.cpnyid

Wrkcadetail.payeeid

Wrkcadetail.trandesc

Bank Summary, MC 20640bsm Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Bank Detail, MC 20640bdm Wrkcabalances Wrkcabalances.cpnyid

Acctsub1

Wrkcadetail.trandate

Payee Summary, MC 20640pm Wrkcadetail Wrkcadetail.cpnyid

Wrkcadetail.payeeid

Wrkcadetail.trandesc



Field list.


this report:


Vendor ID Wrkcadetail.payeeID Valid Vendor ID

Bank account Wrkcabalances.bankacct Valid bank account

Bank subaccount Wrkcabalances.banksub Valid bank subaccount

Transaction date Wrkcadetail.trandate Valid date


52 Page

Cash Flow Projection – Screen 20.650.00


Table/View

Default Sort Fields

Summary by Account 20650 Wrkcaforecast Wrkcaforecast.cpnyid

Wrkcaforecast.module

Wrkcaforecast.bankacct

Summary by Customer Wrkcaforecast.cpnyid



Wrkcaforecast.banksub

Wrkcaforecast.payeeid

Wrkcaforecast.entryid

Wrkcaforecast.refnbr

Detail by Customer Wrkcaforecast.cpnyid



Wrkcaforecast.banksub

Wrkcaforecast.payeeid

Wrkcaforecast.entryid

Wrkcaforecast.refnbr

This report displays the Cash Flow Projection Report (20.650.00) screen that

allows you to specify criteria that control the records to be printed.

Report Information

Page 53

PTD Transaction List – Screen 20.660.00


Batch Number 20660b Vr_20600catran Vr_20600catran.bankcpnyid

Vr_20600catran.module

Vr_20600catran.batnbr

Vr_20600catran.trandate

Vr_20600catran.refnbr

Vr_20600catran.linenbr

Tran Type 20660t Vr_20600catran Vr_20600catran.bankcpnyid


Vr_20600catran.entryid




Batch Number, MC 20660bmc Vr_20600catran Vr_20600catran.bankcpnyid



Vr_20600catran.trandate



Tran Type, MC 20660tmc Vr_20600catran Vr_20600catran.bankcpnyid


Vr_20600catran.entryid





this report:


Transaction Date Vr_20600catran.trandate Valid date

Batch Number Vr_20600catran.batnbr Valid Batch Number

Transaction Account Vr_20600catran.acct Valid account

Transaction Subaccount Vr_20600catran.sub Valid subaccount

Entry Type/Transaction Type Vr_20600catran.entryid Valid Entry Type ID

Payee Vr_20600catran.payeeid Valid Vendor ID


54 Page

PTD Distribution List – Screen 20.670.00


Standard 20670 Catran Catran.bankcpnyid

Catran.perpost

Acctsub1

Catran.batnbr

Catran.module

Catran.linenbr

Multi-Currency 20670mc Catran Catran.bankcpnyid

Catran.perpost

Acctsub1

Catran.batnbr

Catran.module

Catran.linenbr



Field list.


this report:


Transaction date Catran.trandate Valid date

Batch number Catran.batnbr Valid batch number

Transaction account Catran.acct Valid account number

Transaction subaccount Catran.sub Valid subaccount

Entry type/ Transaction type Catran.entryid Valid entry type

Released Catran.rlsd True = 1, False = 0

Payee Catran.payeeid Valid Payee ID

Report Information

Page 55

Cash Account List – Screen 20.680.00


Detail 20680d Cashacct Cashacct.cpnyid

Acctsub1

Summary 20680s Cashacct Cashacct.cpnyid

Acctsub1



Field list.


this report:


Cash account Cashacct.bankacct Valid account

Cash subaccount Cashacct.banksub Valid subaccount


56 Page

Entry Type List – Screen 20.690.00


Default only 20690 Entrytyp Entrytyp.entryid


this report:


Entry type Entrytyp.entryid Valid Entry Type ID

Update Accounts Receivable Entrytyp.updatear True = 1, False = 0

Active Entry Type Entrytyp.active True = 1, False = 0

Report Information

Page 57

Recurring Item List – Screen 20.700.00


Table/View

Default Sort Fields

Standard 20700 Carecur Carecur.cpnyid

Carecur.recurid

Multi-Currency 20700mc Carecur Carecur.cpnyid

Carecur.recurid


this report:


Entry type Catran.entryid Valid Entry Type ID

Recurring Item Bank Account Carecur.bankacct Valid cash account

Recurring Item Bank Subaccount Carecur.banksub Valid cash subaccount


58 Page

Cash Flow Items List – Screen 20.710.00


Standard 20710 Cashflow Cashflow.casenbr

Cashflow.recptdisbdate

Multi-Currency 20710mc Cashflow Cashflow.casenbr

Cashflow.recptdisbdate


this report:


Cash Flow Transaction Date Cashflow.rcptdisbdate Valid date

Case Number Cashflow.casenbr Valid case number

Recurring Item Bank Account Cashflow.bankacct Valid cash account

Recurring Item Bank Subaccount Cashflow.banksub Valid cash subaccount

Report Information

Page 59

Cash Transactions History – Screen 20.720.00


Standard 20720 Wrkcadetail Wrkcadetail.cpnyid

Acctsub1

Wrkcadetail.refnbr

Multi-Currency 20720mc Wrkcadetail Wrkcadetail.cpnyid

Acctsub1

Wrkcadetail.refnbr



Field list.


this report:


Transaction Date Wrkcadetail.trandate Valid date

Reference Number Wrkcadetail.refnbr Valid reference numbers

Payee ID Wrkcadetail.payeeid Valid Vendor ID

Debits or Credits Wrkcadetail.drcr Debit = D, Credit = C

Transaction Bank Account Wrkcadetail.bankacct Valid cash account

Transaction Bank Subaccount Wrkcadetail.banksub Valid cash subaccount


60 Page

Cash Manager Batch Register – Screen 20.800.00


Detail 20800d Batch Batch.cpnyid

Batch.batnbr

Acctsub1

Summary 20800s Batch Batch.cpnyid

Batch.batnbr

Detail – MC 20800dmc Batch Batch.cpnyid

Batch.batnbr

Acctsub1

Summary – MC 20800smc Batch Batch.cpnyid

Batch.batnbr



Field list.


this report:


Batch Number Batch.batnbr Valid Batch Number

Batch Status Batch.status U, P, B, H, S

Released Batch Batch.rlsd True = 1, False = 0

Report Information

Page 61

CA Edit – Screen 20.810.00


Standard 20810 Batch Batch.cpnyid

Batch.batnbr

Catran.linenbr

Multi-Currency 20810mc Batch Batch.cpnyid

Batch.batnbr

Catran.linenbr


this report:


Batch Number Batch.batnbr Valid Batch Number

Batch Status Batch.status U, P, B, H, S

Released Batch Batch.rlsd True = 1, False = 0

report assistant for microsoft dynamics sl cash manager...

Documents