Automatic exports

From Onlinehelp
Jump to navigation Jump to search

Using automatic exports, you can extract all kinds of content from the system in any combination you like. All automatic exports are prepared in XML or CSV format. The technology used for this is completely different from the one used with export templates. It must be set up by experts, and SQL SELECT queries are used instead of variables. This is the same technology as is used for diagrams or Excel report templates, for example. You request a URL, and the system responds with an XML. You can use the GET or POST methods described below for these requests.

HTTP request methods: GET and POST

GET and POST requests have the following properties, advantages, and disadvantages.

GET methods

With the GET method, the parameters are submitted through the URL.

GET queries:
  • can be cached
  • stay in the browser history
  • can be marked with bookmarks
  • have length restrictions
  • should only be used to fetch data
POST method

With the POST method, unlike the GET method, the parameters are not sent through the URL, but rather through a form/script. which is configured on the customer side.

POST queries:
  • are not cached
  • do not stay in the browser history
  • cannot be bookmarked
  • have no length restrictions
Example form for a POST request: Click on “Expand” to see an example form...
<html>
<head>
</head>
<body>
<form action="https://recruitingapp-90063140.umantis.com/Exports/TestExport/85" method="post">
submit 
<input type="hidden" name="Key" value="yourkey">
<input type="hidden" name="PlattformID" value="98060">
<input type="hidden" name="LangID" value="1">
<input type="hidden" name="CustomerID" value="90063140">
<input type="hidden" name="Lang" value="de">
<input type="hidden" name="OrganisationsID" value="">
<input type="submit" value="Submit">
</form> 
</body>
</html>

Description of input fields

  • Name: Appears in the list of “Automatic exports”.
  • Technical description: Explains any control parameters and the content that is fetched.
  • Parameters 1 – n: Up to 20 parameters can be submitted, which are then replaced in the SQL. This allows the calling tool to set the scope of the query dynamically. For example, you can submit an offset, a limit or a specific status that the SQL should search for. The design possibilities here are virtually unlimited.
  • Test variables: used to test the export during setup. Enter the parameters however you want them to be replaced in the subsequent test run. (e.g. Offset=5, Limit=5)
  • SQL statement: This part requires in-depth knowledge of the underlying data structure. The specified parameters are shown here as placeholders (e.g. SELECT ... FROM ... WHERE ... Limit [Limit] Offset [Offset])
  • XSLT file: The result returned as the output of the SQL query has a specific XML structure. The XSLT then serves to reformat the standard XML output according to your own preferences, insert any additional tags that are needed, or to generate sums and the like and include them in the delivered XML.
The XSLT can also transform the exported data into a CSV (text file) output format. In this case, the “XMLExport” variable must be replaced by “CSVExport” in the request URL.
  • Authentication key: each export is subject to authentication. Only if the caller provides the correct key can the report/export be retrieved. It works exactly like a password when logging in. (Please use https:// for requests)
  • Request: serves simply to help you to remember the request link more easily. It always has a similar structure:
    https: //employeeapp-[YourCustomerID].umantis.com/XMLExport/[YourExportID]?Param1=[YourParam1]&Param2=[YourParam2]&Key=[YourAuthenticationKey]
    or
    https: //recruitingapp-[YourCustomerID].umantis.com/XMLExport/[YourExportID]?Param1=[YourParam1]&Param2=[YourParam2]&Key=[YourAuthenticationKey] ... Obviously, you must replace variables like [YourParam1] with real values in the actual call.
For CSV exports, the variable “XMLExport” should be replaced by “CSVExport” in the URL.


Transfer data to Applicant Management or Employee Management
  • URL of the target system
Complete URL of the target system, e.g. https://recruitingapp-12345678.umantis.com or https://employeeapp-12345678.umantis.com
  • Import type in target system
Please select an import type that matches the target system (URL of the target system). Please make sure that the format generated by the export corresponds exactly to the requirements of the selected import. (see Imports)
  • Example link to trigger the import
https://recruitingapp-[CustomerID].umantis.com/?CustomFunction=AutoExportToSystem&ExportID=89&Param1=[ UrlID1 ]
URL to trigger the export for immediate import into the target system. Any parameters required to control the SQL must also be specified in this call. Please do not attach the key, otherwise it could become known in the link target. It is added automatically. CustomFunction=AutoExportToSystem is essential to enable the function.

Create new automatic export

In the following, you will find information on creating an automatic export, based on the example of an XML export for the job board (Applicant Management).

Create new automatic export (job board XML export) — Click on “Expand” to expand

Navigate to the automatic exports (/Exports/AutoExportTemplates) and click on “Create new automatic export”. Then enter the following data to create an export template for the job board.

Name

Enter a name, e.g. “Job board XML export”

Technical description
Click on “Expand” to show the technical description.

Fields:

  • StellenID
  • Jobtitel
  • TitleTextblock1
  • Textblock1
  • TitleTextblock2
  • Textblock2
  • TitleTextblock3
  • Textblock3
  • TitleTextblock4
  • Textblock4
  • TitleTextblock5
  • Textblock5
  • TitleTextblock6
  • Textblock6
  • TitleTextblock7
  • Textblock7
  • TitleTextblock8
  • Textblock8
  • PlaceOfWork, MultiSelect
  • Departement, MultiSelect
  • EntryLevel, MultiSelect
  • EmploymentType, MultiSelect
  • ContractType, MultiSelect
  • OnlineSince Publication date
  • ApplyURL
  • Publication

Parameters:

  • CustomerID
  • SprachID
  • Language
Parameter list
  • Parameter 1: CustomerID
  • Parameter 2: SprachID
  • Parameter 3: Language
Test variables
  • Customer ID = [YourID], SprachID = 1, Language = ger
Data processing
Click on “Expand” to show the SQL statement. 
 SELECT
"Pos"."Pos+id" AS "StellenID",
"Pos++ExtPub"."JobTitle" AS "Jobtitel",
"ExtPubCommSet1"."Comments" AS "TitelTextblock1",
"ExtPubCommSet2"."Comments" AS "Textblock1",
"ExtPubCommSet3"."Comments" AS "TitelTextblock2",
"ExtPubCommSet4"."Comments" AS "Textblock2",
"ExtPubCommSet5"."Comments" AS "TitelTextblock3",
"ExtPubCommSet6"."Comments" AS "Textblock3",
"ExtPubCommSet7"."Comments" AS "TitelTextblock4",
"ExtPubCommSet8"."Comments" AS "Textblock4",
"ExtPubCommSet9"."Comments" AS "TitelTextblock5",
"ExtPubCommSet10"."Comments" AS "Textblock5",
"ExtPubCommSet11"."Comments" AS "TitelTextblock6",
"ExtPubCommSet12"."Comments" AS "Textblock6",
"ExtPubCommSet15"."Comments" AS "TitelTextblock7",
"ExtPubCommSet16"."Comments" AS "Textblock7",
"ExtPubCommSet17"."Comments" AS "TitelTextblock8",
"ExtPubCommSet18"."Comments" AS "Textblock8",
(SELECT
	STRING_AGG("Dr++DrE++ML"."Text", ' | ')
	FROM "Pos" AS "InnerPos"
	JOIN "Pos++SklS" ON
	"Pos++SklS"."Pos+id" = "InnerPos"."Pos+id" AND
	"Pos++SklS"."Pos++SklS+id" = 1004
	JOIN "Dr++DrE++ML" ON 
	('|' || text("Pos++SklS"."Skill") || '|') like ('%|' || text("Dr++DrE++ML"."Dr++DrE+id") || '|%') AND
	"Dr++DrE++ML"."Dr+id" = 1004 AND 
	"Dr++DrE++ML"."Dr++DrE++ML+id" = "Pos++ExtPub"."Pos++ExtPub+id"
	WHERE 
	"InnerPos"."Pos+id" = "Pos"."Pos+id"
) AS "PlaceOfWork",
(SELECT 
	STRING_AGG("Dr++DrE++ML"."Text", ' | ')
	FROM "Pos" AS "InnerPos"
	JOIN "Dr++DrE++ML" ON 
	('|' || text("InnerPos"."DescFct+Function") || '|') like ('%|' || text("Dr++DrE++ML"."Dr++DrE+id") || '|%') AND
	"Dr++DrE++ML"."Dr+id" = 10 AND 
	"Dr++DrE++ML"."Dr++DrE++ML+id" = "Pos++ExtPub"."Pos++ExtPub+id"
	WHERE 
	"InnerPos"."Pos+id" = "Pos"."Pos+id"
) AS "Departement",
(SELECT 
	STRING_AGG("Dr++DrE++ML"."Text", ' | ')
	FROM "Pos++PplRMSL"	
	LEFT JOIN "Dr++DrE++ML" ON 
	('|' || text("Pos++PplRMSL"."Pos++PplRMSL+id") || '|') like ('%|' || text("Dr++DrE++ML"."Dr++DrE+id") || '|%') AND
	"Dr++DrE++ML"."Dr+id" = 15 AND 
	"Dr++DrE++ML"."Dr++DrE++ML+id" = "Pos++ExtPub"."Pos++ExtPub+id"
	WHERE 
	"Pos++PplRMSL"."Pos+id" = "Pos"."Pos+id"
) AS "EntryLevel",
(SELECT 
	STRING_AGG("Dr++DrE++ML"."Text", ' | ')
	FROM "Pos" AS "InnerPos"
	JOIN "Dr++DrE++ML" ON 
	('|' || text("InnerPos"."DescEmp+EmploymentType") || '|') like ('%|' || text("Dr++DrE++ML"."Dr++DrE+id") || '|%') AND
	"Dr++DrE++ML"."Dr+id" = 13 AND 
	"Dr++DrE++ML"."Dr++DrE++ML+id" = "Pos++ExtPub"."Pos++ExtPub+id"
	WHERE 
	"InnerPos"."Pos+id" = "Pos"."Pos+id"
)AS "EmploymentType",
(SELECT 
	STRING_AGG("Dr++DrE++ML"."Text", ' | ')
	FROM "Pos" AS "InnerPos"
	JOIN "Dr++DrE++ML" ON 
	('|' || text("InnerPos"."DescEmp+ContractType") || '|') like ('%|' || text("Dr++DrE++ML"."Dr++DrE+id") || '|%') AND
	"Dr++DrE++ML"."Dr+id" = 27 AND 
	"Dr++DrE++ML"."Dr++DrE++ML+id" = "Pos++ExtPub"."Pos++ExtPub+id"
	WHERE 
	"InnerPos"."Pos+id" = "Pos"."Pos+id"
) AS "ContractType",
"HRExpert"."PName+GivenName" AS "HRExpert_Surname",
"HRExpert"."PName+FamilyName" AS "HRExpert_Name",
"HRExpert"."Biz+PhoneDirect" AS "HRExpert_Phone",
"Com"."Com+id" AS "BranchID",
"Com"."EntityIdentifier" AS "Branch_Name",
to_char(to_date("Pos++ExtPub"."LastPubDate",'YYYYMMDDHH24MISS'),'DD.MM.YYYY') AS "OnlineSince",
'https://recruitingapp-' || '[CustomerID]' || '.umantis.com/Vacancies/' || "Pos"."Pos+id"::text || '/Application/CheckLogin/' || 
'[SprachID]' || '?lang=' || '[Sprache]' AS "ApplyURL",
'https://recruitingapp-' || '[CustomerID]' || '.umantis.com/Vacancies/' || "Pos"."Pos+id"::text || '/Description/' || 
'[SprachID]' || '?lang=' || '[Sprache]' AS "PublicationURL"

FROM "Pos"

-- Language
JOIN "Pos++ExtPub" ON
"Pos++ExtPub"."Pos+id" = "Pos"."Pos+id" AND
"Pos++ExtPub"."Pos++ExtPub+id" = [SprachID]

-- Text blocks
LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet1" ON
"ExtPubCommSet1"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet1"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet1"."Pos++ExtPub++CommSet+id" = 1

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet2" ON
"ExtPubCommSet2"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet2"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet2"."Pos++ExtPub++CommSet+id" = 2

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet3" ON
"ExtPubCommSet3"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet3"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet3"."Pos++ExtPub++CommSet+id" = 3 

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet4" ON
"ExtPubCommSet4"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet4"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet4"."Pos++ExtPub++CommSet+id" = 4

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet5" ON
"ExtPubCommSet5"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet5"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet5"."Pos++ExtPub++CommSet+id" = 5

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet6" ON
"ExtPubCommSet6"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet6"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet6"."Pos++ExtPub++CommSet+id" = 6

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet7" ON
"ExtPubCommSet7"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet7"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet7"."Pos++ExtPub++CommSet+id" = 7

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet8" ON
"ExtPubCommSet8"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet8"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet8"."Pos++ExtPub++CommSet+id" = 8

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet9" ON
"ExtPubCommSet9"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet9"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet9"."Pos++ExtPub++CommSet+id" = 9

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet10" ON
"ExtPubCommSet10"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet10"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet10"."Pos++ExtPub++CommSet+id" = 10

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet11" ON
"ExtPubCommSet11"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet11"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet11"."Pos++ExtPub++CommSet+id" = 11 

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet12" ON
"ExtPubCommSet12"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet12"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet12"."Pos++ExtPub++CommSet+id" = 12 

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet15" ON
"ExtPubCommSet15"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet15"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet15"."Pos++ExtPub++CommSet+id" = 15

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet16" ON
"ExtPubCommSet16"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet16"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet16"."Pos++ExtPub++CommSet+id" = 16

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet17" ON
"ExtPubCommSet17"."Pos+id" = "Pos"."Pos+id" AND
"ExtPubCommSet17"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet17"."Pos++ExtPub++CommSet+id" = 17

LEFT OUTER JOIN "Pos++ExtPub++CommSet" AS "ExtPubCommSet18" ON
"ExtPubCommSet18"."Pos+id"= "Pos"."Pos+id" AND
"ExtPubCommSet18"."Pos++ExtPub+id" = "Pos++ExtPub"."Pos++ExtPub+id" AND
"ExtPubCommSet18"."Pos++ExtPub++CommSet+id" = 18 

-- HRExpert
LEFT JOIN "Pers" AS "HRExpert" ON
"HRExpert"."Pers+id" = "Pos"."EntResp+PtrCustodianID"

-- Branch office
LEFT JOIN "Com" ON

"Com"."Com+id" = "Pos"."ComS+PtrLastConnectedEntityID"

 WHERE
"Pos"."EntitySubtype" = 'PositionType_Vacancy' AND
"Pos++ExtPub"."Websites" IS NOT NULL
XSLT file

Upload the XSLT file: Default-XML-Stellen-Feed_V1.xslt

Authentication key

Create a specific authentication key.

Request

https://recruitingapp-[YourID].umantis.com/XMLExport/[NewID]?Key=[YourAuthenticationKey]&param1=[param1]&param_n=[param_n]

Document exports

The application export can be extended: Make the following changes to the (new or existing) export template to enable a document export:

  1. Add the following parameter: get_documents_zip
  2. Add the following section to the SQL statement and modify it if necessary:
[DocumentStart_1 ("Pers"."Pers+id")::text || "Pers"."PhotoF+Name" AS "PictureName1",
(SELECT "Pers++PhotoF"."SpecialName" from "Pers++PhotoF"
where "Pers"."Pers+id"="Pers++PhotoF"."Pers+id" and 3 = "Pers++PhotoF"."Pers++PhotoF+id" ) as "PictureTarget1"
DocumentEnd_1]

If the configuration is done correctly, the URL extension &get_documents_zip=true will return a ZIP file.
The following is an example URL: https://recruitingapp-[YourID].umantis.com/XMLExport/[NewID]?Key=[YourAuthenticationKey]&get_documents_zip=true

Please note. that the following characters in the title: ( \/:*?"<>|) will be converted to _ as they are not allowed according to file naming convention.

Actions

  • Test export: This is where your test variables are used. The link in the actions automatically puts together the test call so that you will receive the report in the response.
  • Show SQL result: Here you can test the first step of the configuration, the SQL. The test parameters are needed here as well.
  • Delete: deletes the entire configuration for the export.

Note: If an SQL error occurs during your export (this also applies to diagrams and Excel report templates), you must first correct the SQL and save it again, since the saved error prevents it from being executed again. Diagrams are excluded from this behavior – they are not locked after an error.

Related topics

Retrieved from "https://en.onlinehelp.umantis.com/index.php?title=Automatic_exports&oldid=8800"