Aron's Tech Blog

***NOTE: wm_concat failed to function after the application of a patch in the 12c Oracle database. You can more or less use LISTAGG to replace this deprecated function for string aggregation.

We’ve all had those times when we want to export the user types or groups and return the selections on the same line as opposed to each type and group on a different line. Yes you could use csv-entity export but that’s never a sure thing.  You can use WM_CONCAT to pull the elements and stick them in the same column as comma separated value. This is a bit limited because you have include a grouping for every value not using WM_CONCAT.

Here is a functional demonstration:

Create an empty file named: UserExport-WM_CONCAT_sq.xml

Add this code to the file:

<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="SO1704" model="http://www.taleo.com/ws/so800/2009/01" projectedClass="User" locale="en" mode="CSV" csvheader="true" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:subQueries/>
	<quer:projections>
		<quer:projection>
            <quer:field path="UserAccount,Loginname"/>
        </quer:projection>
		<quer:projection>
            <quer:field path="FirstName"/>
        </quer:projection>
		<quer:projection>
            <quer:field path="LastName"/>
        </quer:projection>
		<quer:projection>
            <quer:field path="CorrespondenceEmail"/>
        </quer:projection>
		<quer:projection alias="UserTypeCode" projectedValueType="[C">
  <quer:customFunction name="wm_concat">
    <quer:field path="UserTypes,Code"/>
  </quer:customFunction>
</quer:projection>
	</quer:projections>
	<quer:projectionFilterings/>
	<quer:filterings/>
	<quer:sortings>
		<quer:sorting ascending="true">
			<quer:field path="LastName"/>
		</quer:sorting>
		<quer:sorting ascending="true">
			<quer:field path="FirstName"/>
		</quer:sorting>
	</quer:sortings>
	<quer:sortingFilterings/>
	<quer:groupings>
		<quer:grouping>
            <quer:field path="UserAccount,Loginname"/>
        </quer:grouping>
		<quer:grouping>
            <quer:field path="FirstName"/>
        </quer:grouping>
		<quer:grouping>
            <quer:field path="LastName"/>
        </quer:grouping>
		<quer:grouping>
            <quer:field path="CorrespondenceEmail"/>
        </quer:grouping>
	</quer:groupings>
	<quer:joinings/>
</quer:query>

We always know what fun it is to crawl way back into the past when we knew and were fluent with DOS, ah it certainly brings back nostalgia and I can usually do everything that I need to do but some of it I barely remembered then let alone know. But the internet provides and when setting up batch files for the touchpoints I just need a template so here is the header and import and output samples.

read more »

As we know there are a couple of keys that we need to consider when we’re creating or updating UDSElements, it turns out only one of these are needed for the delete operation.

Create a file named:  UDSElementDelete_ld.xml

Copy this source code to the file to make it work.

<?xml version="1.0" encoding="UTF-8"?>
<load:load productCode="RC1501" model="http://www.taleo.com/ws/tee800/2009/01" root="UDSElement" operation="delete" locale="en" header="true" delimiter="," quote="&quot;" xmlns:load="http://www.taleo.com/ws/integration/load">
	<load:columns>
		<load:column type="IDENTIFIER" header="Identifier" path=""/>
		<load:column parameter="1" type="DATA" path="Number" searchType="SEARCH" searchTarget="." localeType="NONE"/>
		<load:column parameter="1" type="DATA" path="UserDefinedSelection,Code" searchType="SEARCH" searchTarget=".." localeType="NONE"/>
	</load:columns>
	<load:processingInstructions/>
</load:load>

There is a URL you need to generate the service provider metadata in Taleo, it’s structured like this:

https://ZoneName/smartorg/sp/metadata.jss?target=EntityID

Where the EntityID is what’s in the “Entity ID or Issuer Identity” under Identity Provider Information in SSO Setup

I’m not sure why this gives me issues every time but I figured I’d put it here so I can find it next time I need it:

<quer:projection alias="CSWMotive" xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:query projectedClass="Application" alias="sqMotives" preventDuplicates="true" pagingsize="1">
		<quer:projections>
			<quer:projection alias="Motive">
				<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.CSWMotives,Mnemonic"/>
			</quer:projection>
		</quer:projections>
		<quer:filterings>
			<quer:filtering>
				<quer:equal>
					<quer:field path="Number"/>
					<quer:field ownerQuery="MainQuery" path="Number"/>
				</quer:equal>
			</quer:filtering>
			<quer:filtering>
				<quer:equal>
					<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Step,Mnemonic"/>
					<quer:field ownerQuery="MainQuery" path="CSWLatestStep,Mnemonic"/>
				</quer:equal>
			</quer:filtering>
			<quer:filtering>
				<quer:equal>
					<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
					<quer:field ownerQuery="MainQuery" path="CSWLatestStatus,Mnemonic"/>
				</quer:equal>
			</quer:filtering>
			<quer:filtering>
				<quer:equal>
					<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Reverted"/>
					<quer:boolean>false</quer:boolean>
				</quer:equal>
			</quer:filtering>
			<quer:filtering>
				<quer:equal>
					<quer:field path="Candidate,History,ApplicationTrackingCSWItem.Step,Mnemonic"/>
					<quer:string>step_mnemonic</quer:string>
				</quer:equal>
			</quer:filtering>
			<quer:filtering>
				<quer:equal>
					<quer:field path="Candidate,History,ApplicationTrackingCSWItem.Status,Mnemonic"/>
					<quer:string>status_mnemonic</quer:string>
				</quer:equal>
			</quer:filtering>
		</quer:filterings>
	</quer:query>
</quer:projection>

When building an export and the spec calls for returning the preferred phone, you can use this decode to avoid all the mouse clicks.

<quer:projection alias="PreferredPhone" xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:decode>
<quer:field path="Candidate,PreferredPhone,Number"/>
<quer:integer>1</quer:integer>
<quer:field path="Candidate,HomePhone"/>
<quer:integer>2</quer:integer>
<quer:field path="Candidate,WorkPhone"/>
<quer:integer>3</quer:integer>
<quer:field path="Candidate,MobilePhone"/>
<quer:integer>4</quer:integer>
<quer:field path="Candidate,PagerPhone"/>
<quer:integer>5</quer:integer>
<quer:field path="Candidate,Fax"/>
</quer:decode>
</quer:projection>

In case you were wondering, here’s how it’s done:

Use the Unicode Hex character code value for the horizontal tabulation character.

Type 	 in CSV Value Delimiter.