Charge Query Specifications Panel

Performing Query Functions › Charge Query Panels › Charge Query Specifications Panel

Charge Query Specifications Panel

The Charge Query Specifications panel allows you to formulate new queries or change/execute existing ones. You specify the query selection criteria and the manner in which you want results displayed.

The following sample Charge Query Specifications panel shows the default values that appear when you select the panel for the first time for a new query. CA PMA Chargeback displays the name of the query and its description as entered in the Charge Query Primary panel, or as retrieved from a saved definition on the first line after the panel action bar.

USERID CA PMA Chargeback MM/DD/YY HH:MM:SS __ Acctdefs Cbdefs Query Data Period Options Exit Help +-- CBQQRYS ------------- Charge Query Specifications ----------------------- | Panel Exit Help | --------------------------------------------------------------------------- | Type information for Query CPUCOST Desc CPU COST FOR PER.1-3 Table TAB | | Summarize N Break N Bud/DBCR N D. B. Version : PROD PROD A.| Brk Srt E. F. | ORD Type . . . . N Rec Count B. > SUM | Charge Element . N Units . . > SUM | Qualifier . . . N Rate . . . > AVG | Period Number . thru B. N Charge . . C. > SUM | DIVISION . . . . N | DEPARTMENT . . . N | | | | F2=Exec F5=Save F6=Delete F11=Refresh +---------------------------------------------------------------------------- Command ===> ____________________________________________________________ F1=Help F3=Exit F4=Prompt F9=Command F10=Action Bar F12=Cancel PA1=Susp

The Version field displays the CA PMA Chargeback version in use. Only the last four characters of the version field are available for entry. The default is the version specified on the Primary Panel's Option function. All other fields are available for entry.

Once you specify the query requirements, CA PMA Chargeback displays results on one of three different types of Charge Query Results panels:

Summary
Summary-with-Breaks
Detail

See Charge Query Specifications Processing Options through Charge Query Specifications Processing Options for more information about the Charge Query Results panels.

The following is a description of the Charge Query Specifications panel.

Access

The Charge Query Specifications panel appears when one of these events occur:

You create a new query on the Charge Query Primary panel shown in Charge Query Primary Panel.
You make a selection on the Charge Query List or Expanded List panel.

You return from any of the Charge Query Results panels.

                           -----------------
                           | Expanded List |
                           |>Enter         |
    ----------------     ----------------- |
    | Charge Query |     | Charge Query  | |
    |   Primary    |     |      List     |_|
    |>F2           |     |>Enter         |
    ----------------     -----------------
            |                    |
            ----------------------
                      |
                      v
              -----------------
              |  Charge Query |
              | Specifications|
              |     Panel     |
              -----------------
                     ▲
                     |
   ----------------- |
   |  Charge Query |_|
   | Results Panels|
   |>F3            |
   -----------------

Input:

The following Charge Query Specification panel fields are available for input. These input fields refer to the sample panel in Charge Query Specifications Panel.

Note: The following discusses input fields by the function they perform and not in the order the cursor moves.

A.

Display Options

Summarize A one-character field that works with the Break field to determine the format of results. Valid entries for this field are N(no) and Y(yes). The default value is N and appears when you first select the panel.

When you set the Summarize option to N, results display in detail form (see Charge Query Specifications Processing Options). When you set the Summarize option to Y, results display in summary form, or in summary-with-breaks form (see Charge Query Specifications Processing Options through Charge Query Specifications Processing Options).

Break A one-character field that works with the Summarize field to determine result format. Valid entries for this field are N and Y. The default value is N and appears when you first select the panel.

When you set the Break field to Y, the Brk column becomes active (see D.)

Note: At least one field in the Brk column must be set to Y when you select the Break option. Otherwise, CA PMA Chargeback returns an error message.

If you do not set the Break field to Y, CA PMA Chargeback ignores any value entered in the Brk column.

The Break field cannot be set to Y without setting the Summarize field to Y. The application returns an error message if you attempt this action.

Bud/DBCR A one-character flag that allows CA PMA Chargeback to display budget, debit, and credit, as well as charge information based on any criteria you specify (see page Charge Query Specifications Processing Options). Valid entries for this field are Y and N. The default value is N and appears when you first select the panel.

Srt CA PMA Chargeback displays results in the order specified by the selected fields. To select a field, enter a Y in the Srt column next to the field. (Sort priority is from top to bottom in the Srt column.)

B.

Selection Criteria

Note: You can use the SQL wildcard character % alone or with any alphanumeric character string as selection criteria for these fields. If a field is left blank, then all values for the field are accepted.

Version The last four characters of this field are available for data entry. Specify the version of CA PMA Chargeback you want to query.

ORD Type The three-character name of the output record identifier used as selection criteria. CA PMA Chargeback processes only those records with the ORD Type you specify.

Charge Element The name (up to 16 alphanumeric characters) of the Charge Element used for selection criteria. Only those records with the Charge Element you specify display.

Qualifier The name (up to 16 alphanumeric characters) of the conditional test applied to the charge unit, used for selection criteria. CA PMA Chargeback selects only those records to which the qualifier applies.

Period Number The start of the range of accounting periods or the accounting period from which CA PMA Chargeback selects records.

thru The end of the range of accounting periods from which CA PMA Chargeback selects records.

Entering data in these fields in the following manner provides you with the following results:

Period Number	thru	Result
___	___	List of all records that meet the rest of the criteria specified
001	006	List of all records in periods 001 thru 006 that meet the criteria specified
001	___	List of all records in period 001 that meet the criteria specified
___	006	Invalid; you will get an error message

Table The table from which CA PMA Chargeback retrieves records. Valid entries are TAB for the CCCTAB table, or MOD for the CCCMOD table.

Note: When you request information from the MOD table, the Bud/DBCR option is invalid.

The following entry fields indicate the accounting structures you want to query.

Note: CA PMA Chargeback displays only defined accounting structures. The examples displayed in this chapter show an accounting structure with two levels. The accounting structures you define can be different.

Division Department Enter the name of a defined accounting level whose charge, budget, debit or credit information you want to query.

C.

Selection Criteria/Display Options

The Rec Count, Units, Rate, and Charge fields allow you to specify:

Any query criteria
The valid SQL summary options (Sum, Min, Max, Avg) performed on result records

When specifying query selection criteria, enter only numeric values in these fields. You must specify a comparison operator (<, >, =) in the Op column (see E in Charge Query Specifications Panel).

To select all records, do not specify query criteria within the Rec Count, Units, Rate, or Charge fields. Then the Op column is invalid.
If you specify query criteria but do not specify an operator in the Op column, CA PMA Chargeback uses the default > for comparisons.

If you request a summary, or a summary-with-breaks report, CA PMA Chargeback activates the Sum column (see F in Charge Query Specifications Panel). This allows you to specify an SQL summary option to be performed on result records.

Rec Count

Units

Rate

Charge Enter a numeric value and an arithmetic operator, and/or change the default SQL function if Summarize is Y. The SQL functions are by default SUM, SUM, AVG, SUM.

The following table gives examples of how selection criteria specified within the Rec Count, Units, Rate and Charge fields work. The table also shows how specifying valid SQL functions in the Sum column for these fields affects results.

Fields	Criteria	Op	SQL Functions	Results
Rec Count	2	>	AVG	CA PMA Chargeback averages the values within the Rec Count field for those records where Rec Count > 2.
Units	50	=	SUM	CA PMA Chargeback sums the values within the Units field for those records where Units = 50.
Rate	.5	<	MIN	CA PMA Chargeback computes the minimum rate of those records where Rate < .5.
Charge	200	>	MAX	CA PMA Chargeback computes the maximum charge of those records where Charge > 200.
Charge			SUM	CA PMA Chargeback sums all charge values for the selected records.

Actions

F2=Exec: Executes the query for the requested selections from the panel.
F5=Save: Saves all query information on the panel.
F6=Delete: Lets you delete data from the saved query table.
F11=Refresh: Resets the screen to the original entry state for existing queries.