Can you convert field [Recruiting-Recruiting Real Time] from Date to Date & Time in OTBI?

Question

We would like the time associated with the job application in the field 'Job Application - Electronic Signature'

Can I convert this field to date & time instead? If so, how?

Otherwise, what table contains Job Application submission details?

Thank you.

Nathan CCC · Answer

Hi Wendy,

Unfortunately if in the data model the designer of this subject area has made this column a data type "day" date YYYY-MM-DD in the metadata  repository database (RPD) then there is not a lot you can do to get the time bit back. The time of day bit has already been truncated off at design time so you cant get it back at runtime.

You can if you want as suggested edit column properties data format to add a custom format YYYY-MM-DD"T"hh24:MI:SS but it will not add any value for your users because the time part will always be midnight 00:00:00. Is the the problem you have. You do not know what time of day it was signed?

As you can see from your session log "View log" on page /analytics/saw.dll?IssueRawSQL

SELECT
 ALL 0 s_0,
 "Job Application - Electronic Signature"."Signature Date" as s

FROM "Recruiting - Recruiting Real Time"

ORDER BY 2 ASC NULLS LAST

FETCH FIRST 7ROWS ONLY

"under the hood" in the physical SQL to the application database tables and views the good news is that the data source column is in fact being stored as a data type datetime timestamp in the transaction database - but looks like the rpd has converted this to a day date for your subject area presentation column?

WITH 
SAWITH0 AS (select  /*+ inline */  T3065830.C433458987 as c1
from (

SELECT V51746895.SIGNATURE_DATE AS C433458987

,         V51746895.ESIGNATURE_ID AS PKA_EsignatureId0

FROM IRC_ESIGNATURES V51746895

) T3065830)
select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
     D1.c1 as c2
from SAWITH0 D1 order by c2 ) D1 where rownum <= 7desc

desc

https://docs.oracle.com/en/cloud/saas/human-resources/24c/oedmh/ircesignatures-3912.html

SIGNATURE_DATE	TIMESTAMP			Yes	Date time when eSignature was used.

Look it was stored in the transaction database with time of day all along

SELECT ALL to_char(t.SIGNATURE_DATE,upper('yyyy-mm-dd"t"hh24:mi:ss')) as s FROM IRC_ESIGNATURES t

Rajasekhar Bandaru-Oracle · Answer

Hi Wendy

Please review the HCM OTBI subject area lineage mapping details

HCM

https://community.oracle.com/customerconnect/discussion/40249/hcm-otbi-subject-areas-to-database-lineage-mapping-spreadsheet/p1

Filter the data based on the subject area name and identify the Database objects.

Job Application - Electronic Signature---database table(IRC_ESIGNATURES),database column(SIGNATURE_DATE)

To view OTBI column output data in date & time format. Follow the below steps

select the column in OTBI criteria tab "Job Application - Electronic Signature"."Signature Date"-->Click on Column Properties-->Click on Data format tab-->select the check box "Override Default Data Format"-->in the date format choose option "custom"-->in custom date format enter the text dd-mm-yyyy hh:mm:ss or any other format as per your business requirement and click ok and save the analysis and verify the results.