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.

I saw this while I was digging for some other information and it made me wonder if it will work with exporting issues we see with descriptions and qualifications.

Will have that answer soon…

Goal

When executing the sub-query result of a CLOB or BLOB projection, TCC returns the signature of the underlying object (e.g.: oracle.sql.BLOB@334693be), rather than the actual content.

To get the actual content the projections must use a data type cast, but the lob cast in Oracle has a 4000 characters limit, which is insufficient for many attached files.

Solution

In the complex projection, set the projectedValueType to “[B”, example as following

<quer:projection alias=”BLOB” projectedValueType=”[B” xmlns:quer=”http://www.taleo.com/ws/integration/query”>

See the attached sample for a complete example.

References

NOTE:1047850.1 – SD6210 – Exported Attached Files
NOTE:1047912.1 – SD6243 – Base64 String Decoding
BUG:18642011 – EXPORT FILECONTENT IN SUBQUERIES PROJECTIONS

One thing that’s really important to remember about TCC is that you have to synchronize the custom fields for any new installation or anytime a UDF has been created in the front end. The feature and product integration packs only have the dictionaries for the standard fields. The custom dictionary is located in the [TCC Installation Directory]\customdictionaries directory.

It is also important to point out that these dictionaries are set by product version.  I.E. if your script is on 13a, you will need to synchonize with 13a, if you synchronized with 15a in TCC you still wouldn’t see the custom fields.

Read More

User Types and Groups are collections in Taleo. If you need to clear them you can drag the parent entity over to the columns of an import and then you can see the reset the collection is available and checked.  This is a Boolean for the import file.

Read More