So sometimes you’re digging through a table and there isn’t an obvious primary key (normally Number). While most of the tables this happens on aren’t usually exported but in case you need to, you can query the table for the primary key by using this projection, then you can hopefully figure out what the key is named.
<quer:projection alias="Validkey" xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:key path=""/>
</quer:projection>
So for whatever reason I have a mental block distinguishing between these two when it comes to the Taleo API. So to finally write it down:
So net-change has given you a bunch of errors, oh well, that’s why we’re here. But it would be nice if net-change would give you the actual identifier instead of instead of a concatenated entity, operation and identifier with a pipe. Oh well, here’s how you pull it in SQL (access) so I don’t have to look up the three functions again.
SELECT SourceCandError.Identifier
,StrReverse(SourceCandError.Identifier) as Rev
,InStr(SourceCandError.Identifier, "|") as FirstPipeLoc
,InStr(StrReverse(SourceCandError.Identifier), "|") as LastPipeLoc
,Right(SourceCandError.Identifier, InStr(StrReverse(SourceCandError.Identifier), "|") -1) as Ident
,Right(SourceCandError.Identifier, InStr(SourceCandError.Identifier, "|")) as AfterPipe
,Mid(SourceCandError.Identifier, InStr(SourceCandError.Identifier, "|")+1, Len(SourceCandError.Identifier) - InStr(SourceCandError.Identifier, "|") - InStr(StrReverse(SourceCandError.Identifier), "|")) as Oper
,SourceCandError.Status
FROM SourceCandError;
If you’ve ever done time with TCC you’ll know that the bane of the out of the box integration possibilities is the employee net-change. This is because it’s a marriage of candidate and user and depending on both of those tables to be clean and ready for this is asking a lot.
I ran across a MOS article that actually made clear about the lookups for those two tables. It’s article Doc ID 1047682.1 and here is the PDF defining the candidate/user update columns in the employee net-change sample.
A long time ago I posted the code to be able to put test in a column in an export specification. This one in case you need to read the original.
There is an oddity with this in that if you alias “Comment” you will get a sax parsing error. The specific error is: “ORA-00936: missing expression”. I’m not sure why but you have to alias your comment as something other than “Comment”
8/20/2021 – Just found out an alias of Union will do the same thing.
So this is specifically for job templates but can be adapted to anything that needs to find out what languages are active for any given situation.
<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="RC1704" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="JobTemplate" 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="Number"/>
</quer:projection>
<quer:projection>
<quer:field path="JobCode"/>
</quer:projection>
<quer:projection>
<quer:field path="State,Description"/>
</quer:projection>
<quer:projection alias="Lang_fr">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fr"/>
</quer:projection>
<quer:projection alias="Lang_en">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="en"/>
</quer:projection>
<quer:projection alias="Lang_es">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="es"/>
</quer:projection>
<quer:projection alias="Lang_de">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="de"/>
</quer:projection>
<quer:projection alias="Lang_it">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="it"/>
</quer:projection>
<quer:projection alias="Lang_nl">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="nl"/>
</quer:projection>
<quer:projection alias="Lang_fr_FR">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fr-FR"/>
</quer:projection>
<quer:projection alias="Lang_en_GB">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="en-GB"/>
</quer:projection>
<quer:projection alias="Lang_zh_CN">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="zh-CN"/>
</quer:projection>
<quer:projection alias="Lang_ja">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ja"/>
</quer:projection>
<quer:projection alias="Lang_pt_BR">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pt-BR"/>
</quer:projection>
<quer:projection alias="Lang_sv">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sv"/>
</quer:projection>
<quer:projection alias="Lang_da">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="da"/>
</quer:projection>
<quer:projection alias="Lang_pl">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pl"/>
</quer:projection>
<quer:projection alias="Lang_ru">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ru"/>
</quer:projection>
<quer:projection alias="Lang_fi">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="fi"/>
</quer:projection>
<quer:projection alias="Lang_cs">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="cs"/>
</quer:projection>
<quer:projection alias="Lang_zh_TW">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="zh-TW"/>
</quer:projection>
<quer:projection alias="Lang_ko">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ko"/>
</quer:projection>
<quer:projection alias="Lang_th">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="th"/>
</quer:projection>
<quer:projection alias="Lang_ms">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ms"/>
</quer:projection>
<quer:projection alias="Lang_ca">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ca"/>
</quer:projection>
<quer:projection alias="Lang_tr">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="tr"/>
</quer:projection>
<quer:projection alias="Lang_sk">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sk"/>
</quer:projection>
<quer:projection alias="Lang_et">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="et"/>
</quer:projection>
<quer:projection alias="Lang_no">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="no"/>
</quer:projection>
<quer:projection alias="Lang_bg">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="bg"/>
</quer:projection>
<quer:projection alias="Lang_hr">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="hr"/>
</quer:projection>
<quer:projection alias="Lang_hu">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="hu"/>
</quer:projection>
<quer:projection alias="Lang_ro">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ro"/>
</quer:projection>
<quer:projection alias="Lang_el">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="el"/>
</quer:projection>
<quer:projection alias="Lang_sr">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sr"/>
</quer:projection>
<quer:projection alias="Lang_lt">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="lt"/>
</quer:projection>
<quer:projection alias="Lang_pt">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="pt"/>
</quer:projection>
<quer:projection alias="Lang_sl">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="sl"/>
</quer:projection>
<quer:projection alias="Lang_in">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="in"/>
</quer:projection>
<quer:projection alias="Lang_ar">
<quer:field path="JobInformation,DescriptionActive" localeFiltering="customLocales" locales="ar"/>
</quer:projection>
</quer:projections>
<quer:projectionFilterings/>
<quer:filterings>
<quer:filtering>
<quer:equal>
<quer:field path="State,Description"/>
<quer:string>Active</quer:string>
</quer:equal>
</quer:filtering>
</quer:filterings>
<quer:sortings>
<quer:sorting ascending="true">
<quer:field path="JobCode"/>
</quer:sorting>
</quer:sortings>
<quer:sortingFilterings/>
<quer:groupings/>
<quer:joinings/>
</quer:query>
As you know the automated process for producing exports relies heavily on the last run date. I’ve always been curious as to what happens when you pull the trigger on one that uses it without a lrd, turns out if there is no lrd file is gives you the last 24 hours and instantiates the lrd. I’ll sleep better tonight knowing this.
I can’t for the life of me keep track of this so here it is so I know where to look for it.
https://ZONE.taleo.net/smartorg/sp/metadata.jss?target={ENTITY-ID}
https://ZONE.taleo.net/careersection/sp/metadata.jss?target={ENTITY-ID}
I ran into an issue trying to get everyone within the last 24 hours that had achieved a certain step/status combination. The hitch was that they could have been moved out of the target step/status so we have to muck around in the history to get it. As it’s a date you need to make sure the projected value you’re comparing to is a date as well since the last 24 hours is always going to be date time. This did the trick. Remember to pay attention to the value types in sub queries like this.
<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:greaterThan>
<quer:query projectedClass="Application" alias="sqLocation" preventDuplicates="true" pagingsize="1">
<quer:projections>
<quer:projection alias="AnotherDate">
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
</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:string>RequiredStep</quer:string>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
<quer:string>RequiredStatus</quer:string>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
<quer:query projectedClass="Application" alias="sqMotives" preventDuplicates="true" pagingsize="1">
<quer:subQueries/>
<quer:projections>
<quer:projection>
<quer:maximum>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingHistoryItem.CreationDate"/>
</quer:maximum>
</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:string>RequiredStep</quer:string>
</quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="ProfileInformation,HistoryItems,ApplicationTrackingCSWItem.Status,Mnemonic"/>
<quer:string>RequiredStatus</quer:string>
</quer:equal>
</quer:filtering>
</quer:filterings>
</quer:query>
</quer:equal>
</quer:filtering>
</quer:filterings>
</quer:query>
<quer:castAsDate>
<quer:addDays>
<quer:date type="now"/>
<quer:integer>-1</quer:integer>
</quer:addDays>
</quer:castAsDate>
</quer:greaterThan>
</quer:filtering>
This is a common request I run across. You’ve got a selection list and you need to reset it back to ‘Not Specified’ for whatever reason.
Although I swear I’ve done this before, every time it comes up I’m scratching my head trying to figure out what I did the last time it came up.
As I was looking for help on another issue I ran across Doc ID 2478146.1 on MOS that spells out the ‘correct’ way to do it, leaving it here for my reference: