SQL for getting the Talent Profile details in Oracle HCM Cloud environment

Sample SQL Query (You can modify as required):

-----------------------------------

SELECT 
--HPV.CREATED_BY
--,HPV.CREATION_DATE
--,HPV.LAST_UPDATED_BY
--,HPV.LAST_UPDATE_DATE

 HPIM.CREATED_BY
,HPIM.CREATION_DATE
,HPIM.LAST_UPDATED_BY
,HPIM.LAST_UPDATE_DATE

,(SELECT DISTINCT PAPF.PERSON_NUMBER FROM PER_ALL_PEOPLE_F PAPF
   WHERE PAPF.PERSON_ID = HPV.PERSON_ID)

,HPV.PERSON_ID
,HPV.OWNER_PERSON_ID
,HPV.PROFILE_ID
,HPV.DESCRIPTION
,HPV.PROFILE_TYPE_ID
,HPV.PROFILE_CODE
,HPV.PROFILE_STATUS_CODE
,HPV.PROFILE_USAGE_CODE

--,HPIM.PROFILE_ID
,HPIM.PROFILE_ITEM_ID
,HPIM.PARENT_PROFILE_ITEM_ID
,HPIM.CONTENT_TYPE_ID
,HPIM.CONTENT_ITEM_ID
,HPIM.DATE_FROM
,HPIM.DATE_TO
,HPIM.ITEM_TEXT240_1
,HPIM.ITEM_TEXT30_1
,HPIM.ITEM_TEXT30_7
,HPIM.ITEM_TEXT30_9
,HPIM.ITEM_TEXT30_10
,HPIM.ITEM_TEXT30_11
,HPIM.ITEM_TEXT30_12
,HPIM.ITEM_DATE_1
,HPIM.ITEM_DATE_2
,HPIM.ITEM_DATE_3
,HPIM.ITEM_DATE_4

,--HCTV.CONTENT_TYPE_ID
,HCTV.CONTENT_TYPE_NAME
,HCTV.CONTENT_DESCRIPTION
,HCTV.CONTEXT_NAME
,HCTV.FREE_FORM_FLAG

FROM HRT_PROFILES_VL HPV
,HRT_PROFILE_ITEMS_MSV HPIM
,HRT_CONTENT_TYPES_VL HCTV

WHERE HPV.PROFILE_ID = HPIM.PROFILE_ID
AND HPIM.CONTENT_TYPE_ID = HCTV.CONTENT_TYPE_ID
ORDER BY HPV.PERSON_ID,HPIM.DATE_FROM,HPIM.DATE_TO

Some of the useful Table List in Oracle HCM Cloud environment

Table Name	Description
Table Name : PER_PERSONS Schema : FUSION Object owner : PER Object type : TABLE Tablespace : APPS_TS_TX_DATA Primary Key Name : PER_PERSONS_PK Primary Key Column1 : PERSON_ID Primary Key Column2 : Primary Key Column3 : Primary Key Column4 : Primary Key Column5 :	This table is new for Fusion, and is created to assist BC4J architecture by providing a non-date tracked parent table to act as the parent for all tables in the Person Model, even PER_ALL_PEOPLE_F itself
Table Name : PER_ALL_PEOPLE_F Schema : FUSION Object owner : PER Object type : TABLE Tablespace : APPS_TS_TX_DATA Primary Key Name : PER_PEOPLE_F_PK Primary Key Column1 : PERSON_ID Primary Key Column2 : EFFECTIVE_START_DATE Primary Key Column3 : EFFECTIVE_END_DATE Primary Key Column4 : Primary Key Column5 :	This table will store core personal data that is not expected to vary by Legislation Code, or has need of a global value that is independent of the Legislation Code context. Data is stored with date effectivity, but need not functionally change over time. Values that are static over time will be duplicated to each new date effective instance of a row with the same person_id.
Table Name : PER_ALL_ASSIGNMENTS_M Schema : FUSION Object owner : PER Object type : TABLE Tablespace : FUSION_TS_TX_DATA Primary Key Name : PER_ALL_ASSIGNMENTS_M_PK Primary Key Column1 : ASSIGNMENT_ID Primary Key Column2 : EFFECTIVE_START_DATE Primary Key Column3 : EFFECTIVE_END_DATE Primary Key Column4 : EFFECTIVE_LATEST_CHANGE Primary Key Column5 : EFFECTIVE_SEQUENCE	This stores two levels of the 3-Tier Model: Employment/Placement Terms (Level 2) and Assignments (Level 3). The assignment type is used to differentiate between these two levels as well as it continues to differentiate among employee, contingent worker, applicants, and benefits assignments. This is date-tracked and allows multiple changes in a day.
Table Name : PER_PERIODS_OF_SERVICE Schema : FUSION Object owner : PER Object type : TABLE Tablespace : TRANSACTION_TABLES Primary Key Name : PER_PERIODS_OF_SERVICE_PK Primary Key Column1 : PERIOD_OF_SERVICE_ID Primary Key Column2 : Primary Key Column3 : Primary Key Column4 : Primary Key Column5 :	This table stores all information related to the person?s Work Relationships. An Work Relationship denotes a formal relationship between a person and a legal employer. Therefore, this table will hold information for employees and contingent workers. Current design allows for a person to have multiple active ?periods of service? as long as they are for different legal entities. This is not Effective Dated.
Table Name : PER_ASSIGNMENT_EXTRA_INFO_M Schema : FUSION Object owner : PER Object type : TABLE Tablespace : FUSION_TS_TX_DATA Primary Key Name : PER_ASSIGN_EXTRA_INFO_M_PK Primary Key Column1 : ASSIGNMENT_EXTRA_INFO_ID Primary Key Column2 : EFFECTIVE_START_DATE Primary Key Column3 : EFFECTIVE_END_DATE Primary Key Column4 : EFFECTIVE_LATEST_CHANGE Primary Key Column5 : EFFECTIVE_SEQUENCE	This stores extensible data (Customer, Localization or Verticalization) for Assignments or Sets of Employment/Placement Terms. Changes are related to making this table "Effective Dated".
Table Name : PER_ASSIGNMENT_SUPERVISORS_F Schema : FUSION Object owner : PER Object type : TABLE Tablespace : FUSION_TS_TX_DATA Primary Key Name : PER_ASSIGNMENT_SUP_F_PK Primary Key Column1 : ASSIGNMENT_SUPERVISOR_ID Primary Key Column2 : EFFECTIVE_START_DATE Primary Key Column3 : EFFECTIVE_END_DATE Primary Key Column4 : Primary Key Column5 :	This stores the supervisors associated to a particular assignment. This supports different relationships established between a person and a particular supervisor. One person could have one functional manager (line manager who has absolute control over the person) and a ???project manager??? (project leader who controls a project and organizes resources/people).
Table Name : PER_ASSIGN_WORK_MEASURES_F Schema : FUSION Object owner : PER Object type : TABLE Tablespace : FUSION_TS_TX_DATA Primary Key Name : PER_ASSIGN_WORK_MEASURES_F_PK Primary Key Column1 : ASSIGN_WORK_MEASURE_ID Primary Key Column2 : EFFECTIVE_START_DATE Primary Key Column3 : EFFECTIVE_END_DATE Primary Key Column4 : Primary Key Column5 :	This table holds information about the HR budget values for an assignment. An Assignment may be budgeted using headcount, FTE (full time equivalent), points or any other user defined units. Oracle HRMS sums the budget values held in this table for employee assignment when calculating actual headcount figures. This table has been renamed for Fusion. It was formerly known as PER_ASSIGNMENT_BUDGET_VALUES_F.

How to automate value sets values load using Web Service in Oracle HCM Cloud environment

How to automate value sets values load using ESS job process :
--------------------------------------------------------------------------------------------------------------------------

Step 1 : Create a value set value load file (ABC_Value_Set_Value_Load.txt) as below :
--------------------------------------------------------------------------------------------------------------------------

ValueSerCode|Value|TranslatedValue|Description|EnabledFlag|StartDateActive|EndDateActive|SortOrder
ABC|9999 9999 9999 9999||Test ABC1|N|1901-01-01|4712-12-31|1
ABC|9999 9999 9999 9998||Test ABC2|N|1901-01-01|4712-12-31|2
ABC|9999 9999 9999 9997||Test ABC3|N|1901-01-01|4712-12-31|3


Step 2 : Convert the values of the input file (ABC_Value_Set_Value_Load.txt) into Base64 encoding.
--------------------------------------------------------------------------------------------------------------------------

(For testing purpose I have used "https://www.base64encode.org/"). Once the conversion is done, the Base64 value will be looking like below.

VmFsdWVTZXJDb2RlfFZhbHVlfFRyYW5zbGF0ZWRWYWx1ZXxEZXNjcmlwdGlvbnxFbmFibGVkRmxhZ3xTdGFydERhdGVBY3RpdmV8RW5kRGF0ZUFjdGl2ZXxTb3J0T3JkZXIKQUJDfDk5OTkgOTk5OSA5OTk5IDk5OTl8fFRl
c3QgQUJDMXxOfDE5MDEtMDEtMDF8NDcxMi0xMi0zMXwxCkFCQ3w5OTk5IDk5OTkgOTk5OSA5OTk4fHxUZXN0IEFCQzJ8TnwxOTAxLTAxLTAxfDQ3MTItMTItMzF8MgpBQkN8OTk5OSA5OTk5IDk5OTkgOTk5N3x8VGVzdCBB
QkMzfE58MTkwMS0wMS0wMXw0NzEyLTEyLTMxfDM=


Step 3 : Upload the file details in UCM. We get the Document Id in response. This will also upload the file into UCM.
--------------------------------------------------------------------------------------------------------------------------

Web Service details below :

WSDL Url : https://************************.com/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL
Action    : POST
Method Name : uploadFileToUcm
Description : The method uploads a file to the UCM server based on the document specified.

Request Payload :

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
  <ns1:uploadFileToUcm xmlns:ns1="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
<ns1:document xmlns:ns2="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/">
<ns2:Content>VmFsdWVTZXJDb2RlfFZhbHVlfFRyYW5zbGF0ZWRWYWx1ZXxEZXNjcmlwdGlvbnxFbmFibGVkRmxhZ3xTdGFydERhdGVBY3RpdmV8RW5kRGF0ZUFjdGl2ZXxTb3J0T3JkZXIKQUJDfDk5OTkgOTk5OSA5OTk5IDk5OTl8fFRlc3QgQUJDMXxOfDE5MDEtMDEtMDF8NDcxMi0xMi0zMXwxCkFCQ3w5OTk5IDk5OTkgOTk5OSA5OTk4fHxUZXN0IEFCQzJ8TnwxOTAxLTAxLTAxfDQ3MTItMTItMzF8MgpBQkN8OTk5OSA5OTk5IDk5OTkgOTk5N3x8VGVzdCBBQkMzfE58MTkwMS0wMS0wMXw0NzEyLTEyLTMxfDM=</ns2:Content>
<ns2:FileName>ABC_Value_Set_Value_Load.txt</ns2:FileName>
<ns2:ContentType>txt</ns2:ContentType>
<ns2:DocumentTitle>ABC_Value_Set_Value_Load.txt</ns2:DocumentTitle>
<ns2:DocumentAuthor>Sourav</ns2:DocumentAuthor>
<ns2:DocumentSecurityGroup>FAFusionImportExport</ns2:DocumentSecurityGroup>
  <ns2:DocumentAccount>hcm$/dataloader$/import$</ns2:DocumentAccount>
  </ns1:document>
  </ns1:uploadFileToUcm>
 </soap:Body>
</soap:Envelope>


--------------------------------------------------------------------------------------------------------------------------
*** Note : Please navigate to "https://************************.com/cs" in UCM and check for the file whether the upload is successful or not for testing.
--------------------------------------------------------------------------------------------------------------------------


Step 4 : Submit the ESS Job Request. We get the Request Id in response.
--------------------------------------------------------------------------------------------------------------------------

Web Service details below :

WSDL Url : https://************************.com/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL
Action    : POST
Method Name : submitESSJobRequest
Description : Submits an ESS job request for the specified job definition

Request Payload :
<soap:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
<soapenv:Header/>
<soapenv:Body>
<typ:submitESSJobRequest>
<typ:jobPackageName>/oracle/apps/ess/fnd/applcore</typ:jobPackageName>
<typ:jobDefinitionName>FndValueSetUploadServiceJob</typ:jobDefinitionName>
<typ:paramList>ABC_Value_Set_Value_Load.txt</typ:paramList>
<typ:paramList>hcm/dataloader/import</typ:paramList>
</typ:submitESSJobRequest>
</soapenv:Body>
</soap:Envelope>


Step 5 : Get the status of the ESS Job submit. We get the Job status in response. Pass the request id here what was received in web service response from step 4.
--------------------------------------------------------------------------------------------------------------------------

Web Service details below :

WSDL Url : https://************************.com/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL
Action    : POST
Method Name : getEssJobStatus
Description : Obtains the execution status of the submitted ESS job.

Request Payload :

<soap:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
<soapenv:Header/>
<soapenv:Body>
<typ:getEssJobStatus>
<typ:requestId>181036</typ:requestId>
</typ:getEssJobStatus>
</soapenv:Body>
</soap:Envelope>


Step 6 : Downloads the ESS job output and the logs as a zip file : We get the log file details in response (in binary format)
--------------------------------------------------------------------------------------------------------------------------

Web Service details below :

WSDL Url : https://************************.com/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL
Action    : POST
Method Name : downloadESSJobExecutionDetails
Description : Downloads the ESS job output and the logs as a zip file.

Request Payload :

<soap:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
<soapenv:Header/>
<soapenv:Body>
<typ:downloadESSJobExecutionDetails>
<typ:requestId>181036</typ:requestId>
</typ:downloadESSJobExecutionDetails>
</soapenv:Body>
</soap:Envelope>


--------------------------------------------------------------------------------------------------------------------------
*** Note : To get the successful insert,update and error details (feedback), the log file should be read. Log file will be having the details of summary.
--------------------------------------------------------------------------------------------------------------------------


Step 7 (Optional): To get the details of the input file below web service can be used. Pass the Document Id what was received from the response of web service in step 3.
--------------------------------------------------------------------------------------------------------------------------

Web Service details below :

WSDL Url : https://************************.com/publicFinancialCommonErpIntegration/ErpIntegrationService?WSDL
Action    : POST
Method Name : getDocumentForDocumentId
Description : Downloads the job output file generated by the importBulkData operation.

Request Payload :

<soap:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://xmlns.oracle.com/apps/financials/commonModules/shared/model/erpIntegrationService/types/">
<soapenv:Header/>
<soapenv:Body>
<typ:getDocumentForDocumentId>
<typ:DocumentId>132324</typ:DocumentId>
</typ:getDocumentForDocumentId>
</soapenv:Body>
</soap:Envelope>

Some of the useful Set Instructions which are used in Oracle HCM Cloud environment

The below table lists the SET instructions and their default values (from Oracle website 20B).
Url : https://docs.oracle.com/en/cloud/saas/human-resources/20b/faihm/data-file-instructions-and-delivery.html

Navigation of Configuring HCM Data Loader options :

Go to Setup and Maintenance --> Tasks --> Configure HCM Data Loader

Instruction	Default Value
SET PURGE_FUTURE_CHANGES Y|N	Y
SET DISABLE_POST_PROCESS_TASKS <process>	Not applicable
SET INVOKE_POST_PROCESS Y|N	Y
SET FILE_DELIMITER <delimiter>	Vertical bar (|)
SET FILE_ESCAPE <escape character>	Backslash (\)
SET FILE_NEWLINE <newline character>	n
SET ENABLE_AUDIT_DATA Y|N	N
SET PURGE_AUDIT_DATA Y|N	N
SET CALCULATE_FTE Y|N	N
SET CREATE_DEFAULT_WORKING_HOUR_PATTERN Y|N	N

How to get User last login information from Oracle HCM Cloud environment

There are two ways to get the User's last login information from Oracle HCM Cloud environment.

1> From FND_SESSIONS table
2> From ASE_USER_LOGIN_INFO table

We can directly query FND_SESSIONS table and it always stores data.

ASE_USER_LOGIN_INFO table needs to be updated. We have to run a ESS Job named "Import User Login History" to get ASE_USER_LOGIN_INFO table to be populated.

Sample SQL Query (You can modify as required):

-----------------------------------

Procedure 1 :

SELECT DISTINCT
   USER_NAME
  ,CREATED_BY
  ,CREATION_DATE
  ,LAST_UPDATED_BY
  ,LAST_UPDATE_DATE
  ,LAST_UPDATE_LOGIN
  ,FIRST_CONNECT
  ,LAST_CONNECT
  ,TERRITORY
FROM FND_SESSIONS
WHERE LAST_UPDATE_DATE > (SYSDATE-30)

Procedure 2 :

a> First navigate to Home --> Tools --> Scheduled Processes

b> Schedule the process "Import User Login History"

c> Then run the below query in OTBI

SELECT DISTINCT
   PU.USERNAME
FROM ASE_USER_LOGIN_INFO AULI, PER_USERS PU
WHERE AULI.USER_GUID = PU.USER_GUID

AND   AULI.LAST_LOGIN_DATE > (SYSDATE-30)