Oracle Transactional Business Intelligence

Home› Oracle Analytics› Forums› More Solutions› Oracle Transactional Business Intelligence

Categories

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

Received Response
21
Views
2
Comments
Wendy G
Wendy G ✭
in Oracle Transactional Business Intelligence

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

image.png

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

Otherwise, what table contains Job Application submission details?

Thank you.

Tagged:

Answers

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle ✭✭✭✭
    edited July 26

    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.

  • Nathan CCC
    Nathan CCC ✭✭✭✭✭

    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
    image.png