Defining Data Types

Data types assure that you enter valid values for prompts and substitution variables. You can add prompts to jobs, process flows, and reports. Data types used in prompts can include lists or SQL statements that allow lists of values to be selected.

If the program run by a job accepts parameters, you can create a prompt for each parameter in the program. When you create prompts and substitution variables, you define its characteristics. You create data types for the express purpose of defining prompts and substitution variables. Applications Manager ships with a number of predefined data types. However, you will probably want to create your own data types to meet the needs of your operation.

The Data Types window

Applications Manager user groups control access to data types. If you do not have access to them, see your Applications Manager administrator.

Procedure

To define a data type:

  1. From the Data Types Selector window, click New.

    2. Applications Manager opens the Data Types window shown above.

    For information on using selector windows, see Adding, Editing, and Deleting Applications Manager Objects.

  2. Complete the fields using the information in the table below.
    Field Description

    Name

    A 100-character description for the data type.

    Type

    General type-can be Character, Date, File, Number, Multi, List, or Password.

    For example, a "Divisions" data type would use character, a "Product Count" data type would use number.

    Multi, List, File, and Password data types are described in the subtopics that follow.

    Display Type

    Defines how prompts using this data type will be displayed on the Submit window. There are three options:

    • Text Field: A box in which the requestor may enter text.
    • List Box: A box including a down-arrow. When a requestor clicks on the down-arrow, a list of values appears. With List Boxes containing a list of values, text will auto-fill as the requestor types, based on the available values for the prompt.
    • Radio Buttons: Small circles that, when selected, have a smaller, filled-in circle inside them.

    When a data type is used as a multi select prompt that has List Box or Radio Buttons selected, and there are few enough choices to fit on the Submit window, the values will be displayed with check boxes.

    Min value

    Max value

    These fields define the values for the Minimum and Maximum fields for the prompts. For more information on these fields, see Setting Minimum and Maximum Values for Prompts.

    Max length

    Defines the maximum length of the data in characters. The value may not exceed 512 characters.

    Result Format

    Used with data types with SQL statements and Multi data types. Determines how the SQL results are formatted. Column numbers in { } are replaced by the value of that column. Other characters are included unchanged. If this field is empty. Applications Manager uses the default value of {1}, which means that it passes the value from the first column. The result format does not change the view when you click the Check SQL button.

    Date input format

    Date output format

    These two drop-down list boxes are selectable only when the value in the Type field is Date. They define the input format for a date entered in a prompt and the output format at the time the program is executed. They are used for date format verification and conversion.

    Referential checking

    This option only exists for legacy customers. Its functionality has been replaced by Multi data types with an intersection operation. For more information, see Using Multi Data Types.

    Upper Case

    When this option is selected, Applications Manager converts all values to upper case characters.

    Distinct

    When this option is selected, Applications Manager ensures that no duplicate values are returned. This is primarily for PeopleSoft where distinct is not allowed in their SQL statements.

  3. Depending on the value you select from the Type field, you may fill in the fields on either the SQL, Multi, List, or File tab.

    Because SQL data types are the most common, the fields on the SQL tab are described in the following table.

    Field Description

    Login

    The login you wish to run the SQL statement against. You can select:

    • Database login objects to execute the statement against a database.
    • Agent type logins to request information from an agent's application. Agent type logins are written in { } brackets. For example, you can select {PSE} and Applications Manager will run that SQL statement using the API connection it has to PeopleSoft. If two of the selected agent types are defined, a pop-up window will appear where you can select the agent to run against.

    This field becomes active when you enter a SQL statement below. If the login is set to 'No Selection', Applications Manager will use the Oracle database login used to connect to the Applications Manager database. If the login you wish to use is not included in the drop-down list, have your Applications Manager administrator check its definition.

    To evaluate dynamic substitution variables, data types, or reports in a database other than the Applications Manager database, the login object must be defined for JDBC use. For more information, see the Administration Guide.

    Use Requestor's Login

    Uses the login assigned to the user who requests an object that includes this data type. This option is useful if you want to evaluate a data type's SQL statement based on either a user's view of a database or on different databases depending upon the user. This check box is only active for data types where a login is specified. If a login is not selected in the requestor's user definition, the login set in the Login field on this window will be used.

    SQL Statement

    Contains a select statement which provides a list of values for a prompt. The SQL statement can be up to 2000 characters long. Each value can be no more than 100 characters long. The first value displays the prompt's value. The second value provides additional information about the first value.

    Although SQL statements can return values of various types, including numbers and dates, Applications Manager evaluates them all as characters. Therefore, you must return your values as text. To convert a number to text, use the to_char function. In the image above, the to_char(deptno) function converts the department number to text.

    The SQL allows the use of select replacement values to use with ad hoc requests. To insert replacement values, click the { } button.

    Check SQL

    Executes the SQL statement and displays the output in the SQL View window as shown below.

    4. When you check SQL, the results are displayed in the SQL View window.

  4. Optionally enter text on the ToolTip tab to display additional information about prompts when a user mouses over them on the Submit window.