Category TCC

This complex filter ensures that you are pulling the most recent offer information with the new hire.

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
 <quer:equal>
 <quer:field path="Offers"/>
 <quer:query projectedClass="Offer" alias="My_Offer_Subquery">
 <quer:projections>
 <quer:projection alias="MaxOffer">
 <quer:maximum>
 <quer:field path="Offers,Number"/>
 </quer:maximum>
 </quer:projection>
 </quer:projections>
 <quer:filterings>
 <quer:filtering>
 <quer:equal>
 <quer:field path="Application,Offers"/>
 <quer:field ownerQuery="NewHire" path="Offers,Number"/>
 </quer:equal>
 </quer:filtering>
 </quer:filterings>
 </quer:query>
 </quer:equal>
</quer:filtering>

Retrieved off of 12C, other versions not tested.

<?xml version="1.0" encoding="UTF-8"?>
<quer:query productCode="PRO1001" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="JobTemplate" locale="en" mode="CSV" csvheader="true" csvdelimiter="|" csvquote="&quot;" largegraph="true" preventDuplicates="false" xmlns:quer="http://www.taleo.com/ws/integration/query"><quer:subQueries/><quer:projections><quer:projection>
 <quer:field path="EffectiveFrom"/>
 </quer:projection><quer:projection>
 <quer:field path="EffectiveTill"/>
 </quer:projection><quer:projection>
 <quer:field path="JobCode"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,AdditionalInformation"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,CreationDate"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DepartmentString"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DescriptionActive"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DescriptionBaseLocale"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DescriptionExternal"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DescriptionExternalHTML"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DescriptionInternal"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,DescriptionInternalHTML"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,EmployeeStatus,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,EmployeeStatus,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,EmploymentEquityEstablishment,Description"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,ExternalBonusAmount"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,ExternalBonusTracking"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,ExternalQualification"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,ExternalQualificationHTML"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,HighQuartileSalary"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,HireType,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,HireType,Description"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,HireType,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,InternalBonusAmount"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,InternalBonusTracking"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,InternalQualification"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,InternalQualificationHTML"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobBoardDescription"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobField,Code"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobField,Name"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobGrade"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobLevel,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobLevel,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobSchedule,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobSchedule,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobShift,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobShift,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobType,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,JobType,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,LastModifiedDate"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,LowQuartileSalary"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,MidPointSalary"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OfferParameter,MaximumSalary"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OfferParameter,PayFrequencyBasis,Description"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OfferParameter,PayFrequencyBasis,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OfferParameter,Salary"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OfferParameter,SignOnBonus"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,Organization,Name"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OtherCosts"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OvertimeStatus,Description"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,OvertimeStatus,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,PrimaryLocation,Code"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,PrimaryLocation,Name"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,RelocationCosts"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,SourcingBudget"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,StudyLevel,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,StudyLevel,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,Title"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,TravelCosts"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,WillingnessToTravel,Active"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,WillingnessToTravel,Number"/>
 </quer:projection><quer:projection>
 <quer:field path="JobInformation,WillingnessToTravel,Value"/>
 </quer:projection><quer:projection>
 <quer:field path="State,Description"/>
 </quer:projection><quer:projection>
 <quer:field path="State,Number"/>
 </quer:projection></quer:projections><quer:projectionFilterings/><quer:filterings><quer:filtering>
 <quer:equal>
 <quer:field path="JobCode"/>
 <quer:string>INSERT JOB CODE HERE</quer:string>
 </quer:equal>
 </quer:filtering></quer:filterings><quer:sortings/><quer:sortingFilterings/><quer:groupings/><quer:joinings/></quer:query>

It would seem that you can progress further into an entity than indicated by the entity structure listed in TCC.  This is some really great news.  The issue is that I needed to return the Agency when the hire type was going to be contingent.  But when digging down the application entity, you’re halted at Candidate,TalentUser,Profile,RecruitmentSource as seen here:

It seems that if you know the path to a related entity (including child entities) you can dig in and show that information, for example:
Candidate,TalentUser,Profile,RecruitmentSource,ParentSource,Description will actually return the agency name.

I killed myself for hours on this, still never got it but it was created for me by a smarter mind:

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:or>
<quer:equal>
<quer:field path="ProfileInformation,Experiences,DisplaySequence"/>
<quer:query projectedClass="Application" alias="expSubQuery">
<quer:projections>
<quer:projection>
<quer:maximum>
<quer:field path="ProfileInformation,Experiences,DisplaySequence"/>
</quer:maximum>
</quer:projection>
</quer:projections>
<quer:filterings>
<quer:filtering>
<quer:equal>
<quer:field ownerQuery="expSubQuery" path="Number"/>
<quer:field ownerQuery="mainQuery" path="Number"/>
</quer:equal>
</quer:filtering>
</quer:filterings>
</quer:query>
</quer:equal>
<quer:isNull>
<quer:field path="ProfileInformation,Experiences,DisplaySequence"/>
</quer:isNull>
</quer:or>
</quer:filtering>

So we’ve established that case can be done by criterion or value.  Criterion has been done previously in the blog, here is how to do it by value:

<quer:projection alias="testCase" xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:switchByValue>
<quer:baseValue>
<quer:field path="Requisition,JobInformation,HireType,Description"/>
</quer:baseValue>
<quer:cases>
<quer:case>
<quer:string>Contingent</quer:string>
<quer:string>This is temp work</quer:string>
</quer:case>
</quer:cases>
<quer:defaultValue>
<quer:string> </quer:string>
</quer:defaultValue>
</quer:switchByValue>
</quer:projection>

This is a less than or equal to X to return work experiences.

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
 <quer:or>
 <quer:lessThanOrEqual>
 <quer:field path="ProfileInformation,Experiences,DisplaySequence"/>
 <quer:integer>1</quer:integer>
 </quer:lessThanOrEqual>
 <quer:isNull>
 <quer:query projectedClass="Experience" alias="My_Exp_SubQuery">
 <quer:projections>
 <quer:projection alias="My_Exp_Alias">
 <quer:field path="DisplaySequence"/>
 </quer:projection>
 </quer:projections>
 <quer:filterings>
 <quer:filtering>
 <quer:equal>
 <quer:field path="Number"/>
 <quer:field ownerQuery="NewHire" path="ProfileInformation,Experiences"/>
 </quer:equal>
 </quer:filtering>
 </quer:filterings>
 </quer:query>
 </quer:isNull>
 </quer:or>
</quer:filtering>

This is a query that returns the top X elements in the study class

<quer:filtering xmlns:quer="http://www.taleo.com/ws/integration/query">
 <quer:or>
 <quer:lessThanOrEqual>
 <quer:field path="ProfileInformation,Studies,DisplaySequence"/>
 <quer:integer>1</quer:integer>
 </quer:lessThanOrEqual>
 <quer:isNull>
 <quer:query projectedClass="Study" alias="My_Study_SubQuery">
 <quer:projections>
 <quer:projection alias="My_Study_Alias">
 <quer:field path="DisplaySequence"/>
 </quer:projection>
 </quer:projections>
 <quer:filterings>
 <quer:filtering>
 <quer:equal>
 <quer:field path="Number"/>
 <quer:field ownerQuery="NewHire" path="ProfileInformation,Studies"/>
 </quer:equal>
 </quer:filtering>
 </quer:filterings>
 </quer:query>
 </quer:isNull>
 </quer:or>
</quer:filtering>

Had a field that either needed to be an employee number or 777777 to signify that it’s not a rehire.  I had to use a case statement based on criterion, so I set the default value to 777777 and if it’s isNotNull then it’s the number, I was stoked when it finally worked.

<quer:projection alias="EmployeeID" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:switchByCriterion>
    <quer:cases>
      <quer:case>
        <quer:isNotNull>
          <quer:field path="Offers,Employee_20ID"/>
        </quer:isNotNull>
        <quer:field path="Offers,Employee_20ID"/>
      </quer:case>
    </quer:cases>
    <quer:defaultValue>
      <quer:string>999999</quer:string>
    </quer:defaultValue>
  </quer:switchByCriterion>
</quer:projection>

Here is an example of how to do the substring function in TCC:

</pre>
<quer:projection alias="JobCode">
 <quer:substring>
 <quer:field path="JobCode"/>
 <quer:integer>0</quer:integer>
 <quer:integer>20</quer:integer>
 </quer:substring>
 </quer:projection>
<pre>

Writing SQL is kind of hard if your filtering is limited to and under the where clause.  Here’s an example of how to filter using OR, in this case we had 14 items:)

<quer:filterings>
 <quer:filtering>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712187</quer:string>
 </quer:containsIgnoreCase>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712188</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712186</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712865</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712183</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712386</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712063</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712063</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712084</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712089</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712185</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712604</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712657</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 <quer:containsIgnoreCase>
 <quer:field path="JobCode"/>
 <quer:string>712839</quer:string>
 </quer:containsIgnoreCase>
 </quer:or>
 </quer:filtering>
</quer:filterings>