TCC

TCC – Requisition Approved Date

Ran across a need to pull the approved date (latest) from a requisition export and it seems like something I could run across again so here it is:

<quer:projection alias="Recruiting_Start_Date" xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:query projectedclass="Requisition" alias="sqLocation" preventduplicates="true" pagingsize="1">
<quer:projections>
<quer:projection alias="ApproveDate" projectedvaluetype="string">
<quer:customfunction name="TO_CHAR">
<quer:field path="RequisitionEvent,EventDate">
<quer:string>yyyy-MM-dd</quer:string>
</quer:field></quer:customfunction>
</quer:projection>
</quer:projections>
<quer:filterings>
<quer:filtering>
<quer:equal>
<quer:field path="Number">
<quer:field ownerquery="MainQuery" path="Number">
</quer:field></quer:field></quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="RequisitionEvent,WorkActionNumber">
<quer:string>12</quer:string>
</quer:field></quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="RequisitionEvent,EventDate">
<quer:query projectedclass="Requisition" alias="sqMotives" preventduplicates="true" pagingsize="1">
<quer:subqueries>
<quer:projections>
<quer:projection>
<quer:maximum>
<quer:field path="RequisitionEvent,EventDate">
</quer:field></quer:maximum>
</quer:projection>
</quer:projections>
<quer:filterings>
<quer:filtering>
<quer:equal>
<quer:field path="Number">
<quer:field ownerquery="MainQuery" path="Number">
</quer:field></quer:field></quer:equal>
</quer:filtering>
<quer:filtering>
<quer:equal>
<quer:field path="RequisitionEvent,WorkActionNumber">
<quer:string>12</quer:string>
</quer:field></quer:equal>
</quer:filtering>
</quer:filterings>
</quer:subqueries></quer:query>
</quer:field></quer:equal>
</quer:filtering>
</quer:filterings>
</quer:query>
</quer:projection>
TCC

TCC – Current DateTime (NOW)

On occasion you need to export the current date/time for one reason or another. This is how you get it out of the export:

<quer:projection alias="CurrentDateTime" xmlns:quer="http://www.taleo.com/ws/integration/query">
	<quer:customFunction name="TO_CHAR">
		<quer:date type="now"/>
		<quer:string>yyyy-MM-dd</quer:string>
	</quer:customFunction>
</quer:projection>
[

TCC

TCC – Approximate Attachment Export Time

One of the big questions that’s asked of us in the computer field is how long is it going to take? I cringe when asked for dates in situations like this as the true answer is it will be completed when it’s completed. Computer programing is part (mostly) science but there is an art to it also. And to have the first without the last is to have something that works but doesn’t flow.

In any case my rough estimate on export of attachments out of the Oracle Taleo production zone is 74,848 attachments in a 24 hour period that in total are 10.26 GB in size. This is an extremely rough estimate and your mileage will certainly vary.

TCC

TCC – ORA-00936: missing expression

So it turns out if you try to alias any projection with “Number” it will expect something and you’ll get the SAX error, in the profiler you’ll get this:

<Code>ServerError</Code>
<Subcode>itk:ExportQueryExecutionError</Subcode>
<Reason>An error occurred during the query execution: ORA-00936: missing expression</Reason>

Not sure if that error is thrown for anything else but this was the fix to my issue.

TCC

TCC – List Of Escape Characters

Just a quick reminder if you’re converting UDF names retrieved from the custom dictionary, you need to code for the escapes:

_ = _5f

[space] = _20

/ = _2f

TCC

TCC – Expected Elements

So I found myself having to create an export for only UDF’s and had to code it for the type coming out of the custom dictionary so that if it was a string it would output just the field and if it was a date it would apply a TO_CHAR function along with a date specification. As I hang my head in shame I did it in Excel because I didn’t have time to write the Python version of it and inadvertently misspelled field.

To make a long story short the GUI threw an error saying this is the list of expected elements, I’m sure this is somewhere in an XSD but it’s now here for reference:

read more »

TCC

TCC – Time Offset

Sometimes you will notice some strange things dealing with time when using TCC and the LRD (last run date). You’ll find that even your export is scheduled for every 10 minutes you’re not getting the people that you just switched to the conditions of the filters. This is especially infuriating when you’re just bringing something to fruition and just testing.

The reason for this is that TCC by default is using [CURRENT_TIME] – 5 minutes for the LRD.

You can set this by adding a line to the *_sq.xml file advanced options with a value lrd.filtering.minute.adjustment.zone with the value being time in minutes.

This is documented on the MOS under Doc ID 1590569.1

Uncategorized

Search Parameter in Widows and Outlook

I have the need to figure out by week what was modified in my file system.
Fortunately there’s a string to put in the search box that will give you just that:

modified:7/2/2018 .. 7/31/2018

How about if you need to screen all emails from one week in time in Outlook.
Fortunately there’s a string to put in the search box that will give you just that:

received:7/2/2018 .. 7/31/2018

I’m sure that this format can be used in many other cases (e.g sent items) so it’s good to keep in mind.

Blogs/Blogging

Excel – VLOOKUP

Okay if you read this blog you’ll know I’m more of a DB geek than an Excel user but I’m getting tired of every time that I want to do a quick sub-out of data having to load not one but two files into the DB and then write the query to join the two and get what I need. It’s a great way to go but awfully time consuming if it’s not going to be built into a SSIS pack or something of the sort.
So that brings us to the old standard Excel. Actually Excel is awfully handy on the export side of things but using it to import data from a DB or even flat file.
In this case we’ve got two sheets representing two tables with a common key.

  • =VLOOKUP(F2,States!A$2:B$38,2)
  • The $ keeps the range in the middle from moving when you populate down.
  • In this case this is done in G2 where F2 is being looked up.
  • The range is on a second sheet from A2 to B38 where items in A relate to F and the B value will be returned for G.
  • Remember to have the columns in the same order to pull this off.
MSSQL

SQL – Got People Data?

So how many time did you think to yourself, wouldn’t it be nice to have a repository of names an addresses so I can load for testing? If you’re normal you’ve never asked yourself that. If you’re a computer geek it crosses your mind from time to time. Microsoft has generously given us the Adventure Works database and this is perfect for just such repository. You can find the DB on Microsoft’s site and here is a bit of SQL to get you started.

SELECT top 1000
	   replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as Ident
	  --,p2.rowguid
      --,isnull(p2.[Title], '') as Title
      ,isnull(p2.[FirstName], '') as FirstName
      ,isnull(p2.[MiddleName], '') as MiddleName
      ,isnull(p2.[LastName], '') as LastName
      --,isnull(p2.[Suffix], '') as Suffix
	  ,replace(pa.AddressLine1, ',', '-') as AddressLine1
	  ,isnull(pa.AddressLine2, '') as AddressLine2
	  ,pa.City
	  --,sp.StateProvinceCode as State
	  --,sp.CountryRegionCode as Country
	  ,pa.PostalCode
	  ,replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as EmailAddress
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 1), '') as CellPhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 2), '') as HomePhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 3), '') as WorkPhone
	  ,Lower(LEFT(p2.FirstName, 1) + p2.LastName) as Username
	  ,'taleo123' as Pswd
  FROM [Person].[Person] p2
  join Person.BusinessEntityAddress ba on (p2.BusinessEntityID = ba.BusinessEntityID)
  join Person.Address pa on (ba.AddressID = pa.AddressID)
  join Person.StateProvince sp on (sp.StateProvinceID = pa.StateProvinceID)
  join Person.EmailAddress pe on (p2.BusinessEntityID = pe.BusinessEntityID)
  join Person.PersonPhone pp on (p2.BusinessEntityID = pp.BusinessEntityID)
  where sp.CountryRegionCode = 'US'
order by LastName, FirstName