Top of GSFF Index Table of Contents Feedback

GENW1210 - Interface Extract Queries

Overview

Interface Extract Queries are institution-defined queries that are used to extract data (in XML format) from Callista SMS via the 'Generate SMS Interface XML File' (GENJ1200) job and the 'interfaceExtract' web service. Using the queries, institutions can customise the extracted data to meet their individual requirements and the requirements of external systems.

The interfaceExtract web service can be called from custom developments or other interfacing systems. The 'Generate SMS Interface XML File' (GENJ1200) job can be used to extract the data to a file for manual uploads into external applications.

The following Query Parameter attributes can be defined for institution-defined queries:
(See Field Descriptions, below, for more information.)

Parameter attributes enable validation of parameters when they are passed into the interfaceExtract web service or GENJ1200 job.

Each parameter has optional allowable values, which form a set list that will be used for validations, thereby ensuring that the value passed in is one from the defined list. If allowable values are not defined for the query parameter, then the value is not validated.

Existing queries provided by Callista are view-only. They can only be modified by the application owner.

It is recommended that queries are created and tested in a third-party developer tool, then copied and pasted into GENW1210 once the query syntax has been validated.

Queries can be constructed to get any data out of Callista. It is recommended that the queries use Oracle-provided functions to correctly format the XML. (For a list of functions and examples, see the 'Interface Extract Queries' technical information for the latest Release.)

An Interface Extract Query can be run via the GENJ1200 job or interfaceExtract web service, to return details of the Web Service Access Log for a nominated web service. It returns matching Web Service Access Log records along with the parameter details and data passed in and out of the web service for the specified web method.

In addition, a set of SMS-LMS system queries are provided in Callista to extract data for different services in a Learning Management System (LMS). These system queries cannot be updated or deleted, or executed without modification. However they can be copied to a new record where they can be modified to match an institution's requirements.

SMS-LMS example queries (which comply with the Learning Information Services (LIS) 2.0 standards) that are pre-loaded onto this page are:

S_WS_A_LOG System Extract Query:

An optional Callista System Interface Extract Query, S_WS_A_LOG, can be used to retrieve log entries for a nominated web service; specifically Request (i.e. parameters that were passed into the web service) and Response (i.e. parameters that were retrieved by the web service) messages from the S_WS_ACCESS_LOG table.
Institutions can enter search criteria in the query's parameters to return the web service Request and Response message details in XML format. 
Results can be restricted by web service name, web service method, start and end time-stamp (i.e. date range), http return status, application username, application user Person ID, end-user username, and end-user username Person ID.
As per other System Extract Queries, the S_WS_A_LOG query can be executed via the interfaceExtract web service or the Generate SMS Interface XML File (GENJ1200 ) job. This query cannot be modified.

For further information about the creation of interface extract queries, refer to the 'Interface Extract Queries' technical information for the latest Callista Release.

Return to Top

Navigation

The GENW1210 page is accessed directly from the Callista menu. It includes a 'Create' and 'Edit' page.

genw1210 navigation

Upon accessing the GENW1210 page, a list of Interface Query records is displayed. (For more information, see the Working with Lists overview Help page.)

Return to Top

Field Descriptions

An asterisk (*) indicates that the field is mandatory.

Field: Description:
*Interface Extract Code

Unique code for the interface extract query. Cannot be modified once the record has been saved.

This value is used to identify the Interface Extract Query in GENJ1200.

Table value:
INTERFACE_EXTRACT_QUERY.INTERFACE_EXTRACT_CD
*Description

Description of the interface extract query. Can be updated after creation.

This value is used to identify the Interface Extract Query in GENJ1200.

Table value:
INTERFACE_EXTRACT_QUERY.DESCRIPTION
XML Schema URL

This value represents the SCHEMAURL used by DBMS_XMLSCHEMA.REGISTERSCHEMA and must be specified for the graduationDocumentsSoap web service FindDocuments method when used with queries that return XML documents. For more information refer to Tribal Callista Technical Documentation for the graduationDocumentsSoap web service in the Callista Product Centre. This field may be left blank if a value is not required.

Table value:
INTERFACE_EXTRACT_QUERY.XML_SCHEMA_URL
System check box

This check box can only be set by the application owner (for example, Callista).

For records created in this page, the value will always be 'No'.

This check box is set to 'Yes' only if the query is a 'system' interface extract query, delivered by Callista.
System queries are example queries that can be used as a basis for working queries that can be selected in GENJ1200. They may also be queries set up specifically to return details of the Web Service Access Log for a nominated web service, via the GENJ1200 job or interfaceExtract web service.

Table value:
INTERFACE_EXTRACT_QUERY.SYSTEM_IND
Closed check box

Select this check box to close the interface extract query.

If a query is closed it is no longer available for selection in GENJ1200.

Table value:
INTERFACE_EXTRACT_QUERY.CLOSED_IND
Query

Enter the text for the interface extract query, which is stored as a CLOB.

No validations are performed on syntax in this field.

Table value:
INTERFACE_EXTRACT_QUERY.INTERFACE_EXTRACT_QUERY

Query Parameters

Click the New icon in the Query Parameter block to display the New Interface Extract Query Parameter pop-up page.

The parameters defined in the Query field and the parameters defined in the Query Parameters block, must match.

Descriptions of each field displayed in the pop-up page are provided below.

Field: Description:
*Parameter Name

Enter the name of the parameter.

Table value:
INTERFACE_EXTRACT_QUERY_PARM.QUERY_PARAMETER_NAME
*Data Type

Select a value from the drop-down selector. Values are:

  • STRING
  • DATE
  • NUMBER
  • BOOLEAN
  • FUNCTION

DATE and NUMBER Data Types have the option of defining a Format Mask.

Table value:
INTERFACE_EXTRACT_QUERY_PARM.S_QUERY_PARAMETER_DATA_TYPE
Format Mask

This field is only displayed if you selected a Data Type of NUMBER or DATE in the previous field.

The field is mandatory if the Data Type is DATE. The field is optional if the Data Type is Number.

Enter the required Format Mask. For example, for a Data Type of DATE, the Format Mask must be a valid Oracle DateTime Format Model, such as: dd/mm/yyyy.

Table value:
INTERFACE_EXTRACT_QUERY_PARM.FORMAT_MASK
Allowable Values pop-up

Accessed via the Create & Specify Allowable Values button.
Allowable Values

Click 'Add' to display the Free Text field in which you can add an allowable value for the selected Data Type. For example for a Data Type of DATE, you could enter a date such as, 01/01/2014.

Table value:
INTERFACE_EXTRACT_QUERY_PARM.ALLOWABLE_VALUE

Return to Top

To Create an Interface Extract Query

  1. Open GENW1210. The Interface Extract Queries List page is displayed.

    A:
  2. Click the Interface Extract Code of the required Query record to open it in Edit mode.
  3. Copy the contents of the Query text box and then close this record. (The contents will be pasted into a new Query, see step 8, below.)
  4. Using a Third-Party developer tool, modify the query as required and then test using a Third Party developer tool.
    Note: Third Party developer tools can be used to write the query, execute the query to obtain the XML output, and to validate the XML output (for example, against the IMS LIS 2.0 Specification).

    B:
  5. Click the New icon on the Interface Extract Queries List page, to open the New Interface Extract Query page.
  6. Record values for the Code and Description. These will be used to identify the Interface Extract Query in GENJ1200.
    For field information, see Field Descriptions above.
  7. The System indicator value will be set to 'No' and cannot be updated.
  8. Paste the text (copied and modified at step 3, above) into the Query text box.
  9. Click Create & Close button to create the Query and exit the page. Alternatively, to create Query parameters for the new Query, proceed to the next step, below:

    C:
  10. In the Query Parameters block, click the New icon. (Note: At this stage, the Query has not yet been saved.)
  11. Enter a name for the parameter in the Parameter Name field. For example, :P_PERSON_ID:
  12. In the Data Type field, select a value from the drop-down Selector. For example, STRING.
  13. If the Data Type is NUMBER or DATE enter a value in the Format Mask field.
  14. To add Allowable Values, click the Create & Specify Allowable Values button which creates the Query and the new Query Parameter, and opens the Allowable Values pop-up page.
    Alternatively, click the Create & Close button to create the Query and Query Parameter, and exit the page without adding Allowable Values.
  15. Add multiple parameters, as required.
  16. Click the Save & Close button to exit the page.

For more information about creating custom queries, refer to the 'Interface Extract Queries' technical information for the latest Callista Release.

To Edit an Interface Extract Query

  1. In the Interface Extract Queries List page, click on the Interface Extract Code of the required record. The record will display in full on the Edit page. (For field information, see Field Descriptions above.)
  2. Change the Description if required.
    Note: The System indicator for an Interface Extract Query record cannot be changed in this page.
  3. Select the Closed indicator if you wish to prevent this Interface Extract Query from being available for selection in GENJ1200.
  4. Make changes as required to the Query text, or paste in query text that has been created/updated elsewhere.
  5. Edit existing Query Parameter records and/or add Allowable Values; or create new Query Parameters, as required.
  6. Click the Save & Close button.

To Delete an Interface Extract Query

  1. In the Interface Extract Queries List page, select the Interface Extract Code of the required record and click the Delete icon. Alternatively, click the code to open the Query record in full in Edit mode.
    Note: System Interface Extract Query records cannot be deleted. These are records with a System indicator set to 'Yes'.
  2. Click the Delete icon located at the top of the page.
  3. Click OK when asked to confirm the deletion.
Return to Top

Related Pages

Return to Top

Last modified on 3 October, 2017 11:03 AM

History Information

Release Version Project Change to Document
19.0.0.3, 19.1.0.3 & 20.0.0.2 2298 - Digitary Extensions Added information for the XML Schema URL field.
17.1 2010 - API Improvements (Security) Added new information re: the System Interface Extract Query, S_WS_A_LOG.
17.0 1945 - Interface Extract 2 Updated page.
16.0.0.2 1935 - Interface Extract Updated and edited page.
15.0.0.2 & 15.1 1738 - SMS-LMS Interface New Help page