TCC – ListAgg as replacement for WMCONCAT

Oracle had done an update and that broke the use of WMCONCAT returning one to many relations with a defined separator. For example, the one to many relation of job locations. While the requisition has one and only one primary location, other locations are one to many and can be literally dozens. So if I want the job number in column A, and the other locations concatenated together with tildes in column B, you can do that with the following code:

<quer:query productCode="RC1704" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="JobInformation" locale="en" mode="CSV" csvheader="true" pagingsize="100" largegraph="true" preventDuplicates="false" attributes="pageindex=1" xmlns:quer="http://www.taleo.com/ws/integration/query">
  <quer:subQueries/>
  <quer:projections>
    <quer:projection alias="JobInformation_Number">
      <quer:field path="Number"/>
    </quer:projection>
    <quer:projection alias="ListaggExample_LocNum">
      <quer:customFunction name="LISTAGG">
        <quer:field path="OtherLocations,Number"/>
        <quer:string>~</quer:string>
      </quer:customFunction>
    </quer:projection>
  </quer:projections>
  <quer:projectionFilterings/>
  <quer:filterings>
    <quer:filtering>
      <quer:isNotNull>
        <quer:field path="OtherLocations,Number"/>
      </quer:isNotNull>
    </quer:filtering>
    <quer:filtering>
      <quer:lrd>
        <quer:field path="LastModifiedDate"/>
      </quer:lrd>
    </quer:filtering>
  </quer:filterings>
  <quer:sortings>
    <quer:sorting ascending="false">
      <quer:field path="Number"/>
    </quer:sorting>
  </quer:sortings>
  <quer:sortingFilterings/>
  <quer:groupings>
    <quer:grouping>
      <quer:field path="Number"/>
    </quer:grouping>
  </quer:groupings>
  <quer:joinings/>
</quer:query>

TCC – Memory > CLI vs GUI

Normally this is a set and forget setting but with the upgrades that have taken place, it’s time to review where we set the memory allowances for the CLI (this should include all scheduled jobs) and then the GUI.

To adjust the memory for the GUI, adjust the TaleoConnectClient.ini and adjust the -Xmx512M as needed.

To adjust the memory for CLI jobs, you’ll need to adjust this in TaleoConnectClient.bat file, look for the line ‘%JAVA_HOME%\bin\java.exe’ and adjust the -Xmx265M value. There is a question as to if %JAVA_OPTS% over-rides this but to this point, we’ve noticed that if we adjust the -Xmx value up, it does fix Java Heap Space issues.


TCC – CLI Running Integrations in 22a.1

As we all know, the 22a.1 upgrade is happening because of the Log4J issue that’s been widely reported. As there was no attack vector it was okay but you have to upgrade it. But one of the changes reported in another post is related to the saxon*.jar, this is what renders the XML and by default we’ve lost the logging that used to be seen in the CLI for both Windows and Linux. But it turns out, if you take the log4j2_ui.xml that’s in the [22a.1 install]/log directory and name it to log4j2.xml and copy it into the [Touchpoints]/bin/(Windows or Unix)/core it will run as you remember it.

If you’re not using touchpoints, place this file beside the log4j.xml where ever it happens to reside and you should get the same effect.

Yes a log4j2.xml already exists but if you use that it logs in debug mode and as much fun as it is to watch computers converse in hex, it’s just a little TMI for me.


TCC 22a.1 – Issues With TCC_Touchpoints

So they’ve upgraded saxon8.jar to saxon10.jar (the XSLT and XQuery processor) but the framework (TCC_Touchpoints) that the majority of installs use have the reference to saxon8.jar hard coded in the init.bat/.sh files. Not sure that hard coded is the right word as you can change it but it’s another thing that would have been nice to know.

So the quick takeaway is that if you are using TCC_Touchpoints at all in your integrations, a change is going to have to be made to the touchpoints itself before it can work at all with TCC 22a.1.

The reference to saxon8.jar is found in the following files:

  • TCC_Touchpoints\bin\Unix\core\Init.sh (Linux: Lines 56 & 62)
  • TCC_Touchpoints\bin\Windows\core\Init.bat (Windows: Lines 117 & 131)
  • TCC_Touchpoints\bin\Windows\DailyLimits.bat (Windows: Lines 19 & 20)
  • TCC_Touchpoints\bin\Windows\tools\Parameterize_config.bat (Windows: Line 28)

While we haven’t seen any issues after this change is made. The expected new behavior is going to be the normal process runs but you won’t be able to see the individual steps that you are used to seeing. E.g. between the two pictures below, the top being with saxon10.jar and the bottom one being saxon8.jar.


TCC – Emailing Import Errors

This is for when you need to have the results file sent to you if there are errors in said results file. This can be a little tricky because you will not get a file if all elements were successful although you can probably do something about that with the trigger rule.
In any case this is the last custom step of the post process and assuming you have the mail host set properly in the config boards, you should be golden.

<cli:CustomStep>
	<cli:JavaClass>com.taleo.integration.client.customstep.mail.SendEmailPostStep</cli:JavaClass>
	<cli:Parameters>
		<cli:Parameter>
			<cli:Name>active</cli:Name>
			<cli:Value>true</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>SMTPHost</cli:Name>
			<cli:Value>[MAIL_HOST]</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>from</cli:Name>
			<cli:Value>[ALERTING_MAIL_FROM]</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>to</cli:Name>
			<cli:Value>[ALERTING_MAIL_ON_ERROR_TO]</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>subject</cli:Name>
			<cli:Value>Reporting - Subject/Object of Integration</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>messageTemplate</cli:Name>
			<cli:Value>$file.content()</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>sendAttachment</cli:Name>
			<cli:Value>true</cli:Value>
		</cli:Parameter>
		<cli:Parameter>
			<cli:Name>triggeringRule</cli:Name>
			<cli:Value>$logic.greaterThan($file.errorCount(),0)</cli:Value>
		</cli:Parameter>
	</cli:Parameters>
</cli:CustomStep>

TCC – Looping with LRD

So looping is great if you have a static set that you are pulling, think a big set of resumes. But what if you are using this as part of an ongoing and looping is needed with a LRD. Fear not, this is how you do it.

Pre-processing:
1st step – com.taleo.integration.client.customstep.paging.PagingPreStep
Arguments:
pagingSize
100000
pagingFilename
[CFGFOLDER]\pagefile.pgn

2nd step – com.taleo.integration.client.customstep.lrd.LRDPreStep
Arguments :
lrdFilename
LRD filename, this will be the name as created by the config in the standard LRD location

Post-processing (no arguments needed):
1st step – com.taleo.integration.client.customstep.count.ExportCountPostStep
2nd to last step – com.taleo.integration.client.customstep.paging.PagingPostStep
Last step – com.taleo.integration.client.customstep.lrd.LRDPostStep


TCC – Table Key

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>