Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified…
Below SQL query can be used to find part-timers in HCM. Any worker where FTE is less than 1 is normally considered as part-timer:
Recently, I faced a scenario for a customer where after go-live, where there was an issue found with absence plan configuration. The absence plan was incorrectly set up. The Balance Frequency Source wasn’t setup to “Repeating Period”, so the accrual was calculated incorrectly. In order to fix this, a new absence plan was created and…
There are times when we just need a quick query to count number of positions created in HCM against a department. Below is a sample SQL which can be handy in such situations:
Below sample queries can be used to extract count of workers (Active/ Inactive) in HCM: Query 1: Query 2: Find count workers not having basic assignment details:
On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative. Below SQL can be used to identify such persons:
Below SQL can be used to extract list counties configured for GB in HCM:
There are different requirements where one wants to get number of active/inactive work relationships in a legal entity. Below SQL query can be run to get these counts:
Below query can be used to extract the document record from content server.
Oracle HCM allows special characters to be used in fields like Person Names/ Department Names/ Jobs etc. But in reporting, there may be a requirement to change the special characters (like ý, ě, ž, ů etc) to normal English characters. CONVERT function can be used for such requirements. Output:
Below query can be used to pull actions and configured actions for these reasons along with action type:
Once Jobs data is loaded/created, you can use below SQL to extract the setup data for Jobs:
Below query can be used to extract checklist configuration details:
There is no easy way in OTBI to find list of jobs which don’t have a model profile associated with them. This can be achieved easily using BIP query. Sample Query:
There are multiple primary flags for a worker in assignment table. Namely – Primary Assignment Flag, Primary Work Terms flag and Primary flag. Below query can be used to check these flags:
While performing SIT/UAT, sometimes it becomes mandatory to disable (bypass) all configured approval rules except for a few. One has to go in manually in Transaction console and check the status for each task. An easy way to run the following SQL query and check the tasks for which approvals are yet not Bypassed:
