Purpose

To create an SMS interface XML file, that can be imported into external systems.

SubSystem

General

This job allows an administrator to select an Interface Extract Query (as recorded in GENW1210) as the output directory for data returned by the job; and to add one or more parameters to the selected query, if required.

The query run by this job (GENJ1200) extracts data from Callista SMS and generates a set of XML files which can then be imported into external systems.

This job enables institutions to use customised data extraction to match their individual requirements and the requirements of external systems. Custom queries are created via the Interface Extract Queries page (GENW1210). This job or the interfaceExtract web service can then be used to run the queries and extract the required data.

Queries can be formatted to produce data in a customised format as defined in the query, or in the Learning Information System (LIS) 2.0 format (as described below).

System Queries can also be executed via this job. (Although SMS-LMS system template queries remain unexecutable.)

The query returns a CLOB of data containing XML which is written to the output file.

This job is accessed from the main menu. It can be run in Immediate or Schedule mode.

For detailed information about the creation of queries for this job and the interfaceExtract web service, refer to the Callista Product Centre - wiki.callista.com.au/display/CPC.

Generate SMS Interface XML File block:

Parameters** Tab:

• Interface Extract Code (LOV) - (mandatory)
• Output Directory (LOV) - (mandatory)
  

Interface Extract Query Parameters:

Parameters 1 and 2:

• Name
• Value

Parameters 3-5 Tab:

Interface Extract Query Parameters:

• Name
• Value

Parameters 6-8 Tab:

Interface Extract Query Parameters:

• Name
• Value

Parameters 9-10 Tab:

Interface Extract Query Parameters:

• Name
• Value

Rules/Notes:

Queries are run by a read-only user: 'READ_ONLY_USERNAME', with password 'READ_ONLY_PASSWORD'. These details are defined in Maintain Job Control (JBSF4600) and this user should already exist, as it is used by other jobs.

The Interface Extract Code parameter uses a List of Values (LOV) to select an Interface Extract Query Code that has been recorded in GENW1210. This may be a System Query, if required.

The 'Output Directory' is used to specify the output directory of the XML file that is generated by the job. The specified Output Directory must exist as a record defined in the system (in the JBS_DIRECTORY table).

Query Parameters:

Queries can have parameters defined in GENW1210 before running this job. Parameters are placeholders for values. They allow records to be dynamically restricted to a subset of data.

The Query Parameter fields are optional. However, if the query (as defined in GENW1210 and selected from the 'Interface Extract Code' parameter for this job), includes parameters, then they are required by this job.

Each parameter has two components (as listed below). All two components are required for each parameter.

• Name - This is the name of the parameter used in the query. It must be defined as a parameter for the query in GENW1210 and must be defined in uppercase with a colon on both sides of the name to facilitate finding and replacing the Name with values (provided in the Value parameter). For example, ':P_PERSON_ID:', which is defined in the query as pe.person_id = :P_PERSON_ID:
  Note: Parameter Names defined in the query are replaced by the parameter Value passed in.
• Value - This is the actual value that will be substituted into the query for each instance of the parameter Name. The parameter Value must match the Format Mask if specified for this parameter in GENW1210. For example, if the parameter is defined as NUMBER in GENW1210, then the parameter Value must be numeric value in the format specified by the Format Mask in GENW1210.
  If the parameter is defined as DATE in GENW1210, then the parameter Value must be valid date in the format specified by the Format Mask in GENW1210, for example, 'DD/MM/YYYY' or 'DD/MM/YYYY HH24:MI:SS' or 'yyyy-MM-dd'.
  If Allowable Values are defined in GENW1210, then they must match the specified Allowable Values.
  

For example:
pe.person_id = :P_PERSON_ID: can be used to pass in a Person ID.
The parameter Name will be P_PERSON_ID. The parameter Value is a numerical value that is the Person ID value.

A parameter Name is substituted with a value during execution of the query via this job or via the interfaceExtract web service.

The query returns a CLOB of data containing XML which is written to the output file.

Output files:

Output XML files are stored on the server. The directory for the location of these files is selected from the List of Values (LOV) for the 'Output Directory' parameter. (Note: LOV values are defined in the JBS_DIRECTORY table.)

The name of the output file is: p_sms_interface_cd.<job request ID>.xml, where <job request ID> is the request ID generated by the job scheduler.

The same process is also used by the interfaceExtract web service when it is run by the 'request' method.

For more information, see the latest 'td_interface_extract' Technical document.

Using Parameters:

Parameter Name:

Use an uppercase name and append a colon to each end to indicate the start and end of a parameter Name, for example, :P_BIRTH_DT:
This enables the parameter Name to be correctly substituted with the parameter Value (obtained from the Query parameter definitions in GENW1210).

SMS to LMS:

Queries can be formatted (in GENW1210) to produce data (XML files) in the 'LIS 2.0' format which complies with LIS v2.0 standards. 'LIS 2.0' consists of various services including:

• Person Management Service (PMS) - Person records.
• Group Management Service (GMS) - Organisational Structure data. The selection of organisational structure data considers configuration options for Cross-Listings, Organisational Units, Organisational Unit Structure and Non-Unit Sites.
• Course Management Service (CMS) - Course Management Service consists of course and unit data. The selection of course and unit data will consider configuration options for Cross-Listings, Organisational Units, Organisational Structure and Non-Unit Sites.
• Membership Management Service (MMS) - Enrolment records.

Queries are required to extract data from Callista SMS for each of the services, listed above.

Important: SMS-LMS System Queries are unexecutable. They are intended to be used as 'template' queries only. However, to execute; copy the system query into a new query, modify the query, then execute.

For further information about the SMS-LMS interface, see SMS-LMS Interface.

Rules/Notes:

During processing:

• Only Non Unit Site records with Uploaded to LMS = N are processed and for each record processed, the 'Loaded to LMS' indicator is changed to Y in CRSW0200.
• Only Cross-Listing site records with Uploaded to LMS =N are processed and for each record processed, the 'Loaded to LMS' indicator is changed to Y in SCCLMAIN.
• If any Cross-Listing or Non Unit Site records are locked, the processed records will be rolled back and processing will cease.
• Exception details and error messages are written to the Job Scheduler Run Log.