Oracle Transactional Business Intelligence

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

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

How can i do Join in this two suject areas Human Capital Mgmt – Transaction X Document of Records

Received Response
44
Views
5
Comments
AlexSantos
AlexSantos Rank 1 - Community Starter
in Oracle Transactional Business Intelligence

We need the report to documents of records and datas about approvals in Console Transaction. We are using this subjects areas:

Human Capital Management – Transaction Administration Real Time

Workforce Management - Documents of Record Real Time

We cant identify the common attribute to join this subject areas

Regards

Tagged:

Answers

  • RichardChan
    RichardChan Rank 6 - Analytics & AI Lead

    If there are no common attributes then there will be an issue in joining. Can you double check as it seems you see a relationship between these subject areas

  • MandeepGupta
    MandeepGupta Rank 8 - Analytics & AI Strategist

    Hi @AlexSantos ,

    I think it should be possible using object_id from transaction analysis to dor id from DOR Subject areas. There are many examples for different objects on customer connect. Check below posts:

    Assignment Approvals — Cloud Customer Connect (oracle.com)

    Goal Plan Approvals — Cloud Customer Connect (oracle.com)

    Join subject areas Human Capital Management and Recruiting — Cloud Customer Connect (oracle.com)

    OTBI anlaysis using "Human Capital Management - Transaction Administration Real Time" & " Workforce Management - Absence Real Time" and Report shows all the Approver details (Doc ID 2717457.1)

    Thanks.

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics & AI Coach
    edited Nov 22, 2024 10:25AM

    Hi,

    Assume the common dimension is the unique identifier of a person (or alternative business group id concatenated with upper person number) so join the subject areas using that common thing

    select all a.*, b.* from
    
(select all 0 as s_0, "presentationtable"."presentationcolumn" as person_id, … 
    
from "Subject Area A") a //A is some subject areas with person in it
    
left outer join 
    
(select all 0 as s_0, "Worker"."Person ID" as person_id #, … other cols
    
from "Workforce Management - Documents of Record Real Time") b 
    
on (a.person_id = b.person_id)
  • Riti Malhotra
    Riti Malhotra Rank 3 - Community Apprentice

    were you able to join the two subject areas? If so, Can you share how you did it. I am trying retrieve the details of the document of records that went through the transaction approval process and were rejected

  • RVohra
    RVohra Rank 7 - Analytics & AI Coach

    Here is the one example showing join between

    "Workforce Management - Worker Assignment Event Real Time" and "Human Capital Management - Transaction Administration Real Time"

    SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT Person_Number saw_0, Name saw_1, Last_Name saw_2, First_Name saw_3, Middle_name saw_4, System_Person_Type saw_5, Person_Type saw_6, Enterprise_Hire_Date saw_7, Effective_Date saw_8, Term_date saw_9, Last_Day_Worked saw_10, Last_Update_Date saw_11, Manager_Person_Number saw_12, Manager_Name saw_13, Action_Name saw_14, Action_reason saw_15, Contractor_Conversion saw_16, Job_Code saw_17, Job_Title saw_18, Department_Name saw_19, Department_Manager saw_20, Business_Unit_Name saw_21, Division saw_22, Location_Name saw_23, Approval_Status saw_24, Submitted_Date saw_25, Completion_Date saw_26, Assignment_ID saw_27, Object_ID saw_28 FROM (
    SELECT A.s_23 Person_Number, A.s_20 Name, A.s_18 Last_Name, A.s_17 First_Name, A.s_19 Middle_Name, A.s_25 System_Person_Type, A.s_24 Person_Type,
    A.s_21 Enterprise_Hire_Date, A.s_4 Effective_Date, A.s_31 Term_Date, A.s_1 Last_Day_Worked, A.s_3 Last_Update_Date,A.s_6 Manager_Person_Number, A.s_9 Manager_Name, A.s_11 Action_Name, A.s_10 Action_Reason, A.s_26 Contractor_Conversion,
    A.s_12 Job_Code, A.s_13 Job_Title, A.s_8 Department_Name, A.s_9 Department_Manager, A.s_7 Business_Unit_Name, A.s_15 Division, A.s_14 Location_Name,
    B.s_2 Approval_Status, B.s_10 Submitted_Date, B.s_3 Completion_Date, A.s_2 Assignment_ID, b.s_4 Object_ID
    FROM
    (
    SELECT
    "Workforce Management - Work Relationship Real Time"."Work Relationship Details"."Last Working Date" s_1,
    "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Id" s_2,
    "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Assignment Last Update Date" s_3,
    "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Effective Start Date" s_4,
    "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Manager Name" s_5,
    "Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Manager Person Number" s_6,
    "Workforce Management - Worker Assignment Event Real Time"."Business Unit"."Business Unit Name" s_7,
    "Workforce Management - Worker Assignment Event Real Time"."Department"."Department Name" s_8,
    "Workforce Management - Worker Assignment Event Real Time"."Department"."Supervisor Full Name" s_9,
    "Workforce Management - Worker Assignment Event Real Time"."HR Action Reason"."Action Reason" s_10,
    "Workforce Management - Worker Assignment Event Real Time"."HR Action"."Action Name" s_11,
    "Workforce Management - Worker Assignment Event Real Time"."Job"."Job Code" s_12,
    "Workforce Management - Worker Assignment Event Real Time"."Job"."Job Name" s_13,
    "Workforce Management - Worker Assignment Event Real Time"."Location"."Worker Location Name" s_14,
    "Workforce Management - Worker Assignment Event Real Time"."Organization Hierarchy"."Level 01 Organization Name" s_15,
    "Workforce Management - Worker Assignment Event Real Time"."Organization Hierarchy"."Tree Version Status" s_16,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee First Name" s_17,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Last Name" s_18,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Middle Name" s_19,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Employee Name" s_20,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Enterprise Hire Date" s_21,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Person ID" s_22,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Number" s_23,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Type" s_24,
    "Workforce Management - Worker Assignment Event Real Time"."Worker"."System Person Type" s_25,
    CASE WHEN "Workforce Management - Worker Assignment Event Real Time"."HR Action Reason"."Action Reason" ='Contractor Conversion' THEN 'Yes' ELSE ' ' END s_26,
    DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Business Unit"."Business Unit Name") s_27,
    DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Job"."Job Name") s_28,
    DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Type") s_29,
    DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."System Person Type") s_30,
    TIMESTAMPADD(SQL_TSI_DAY,-1,"Workforce Management - Worker Assignment Event Real Time"."Assignment Event Details"."Effective Start Date") s_31,
    "Workforce Management - Work Relationship Real Time"."Work Relationship"."# Of Years Service" s_32,
    "Workforce Management - Worker Assignment Event Real Time"."Assignment Event"."Assignment Event Count" s_33
    FROM "Workforce Management - Worker Assignment Event Real Time"
    WHERE
    (("HR Action"."Action Name" IN ('Involuntary Termination', 'Termination')) AND (DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Event Real Time"."Worker"."Person Type") <> '300000000133066.00') AND ("Assignment Event Details"."Effective Start Date" > date '2021-01-01'))
    )A
    LEFT OUTER JOIN
    (
    SELECT
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Approval State" s_1,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Approval Status" s_2,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Completion Date" s_3,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Object ID" s_4,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Process Category" s_5,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Process Name" s_6,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Subject ID" s_7,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Submitted by User Name" s_8,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Submitted by User" s_9,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Submitted Date" s_10,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Transaction ID" s_11,
    "Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Transaction Status" s_12,
    DESCRIPTOR_IDOF("Human Capital Management - Transaction Administration Real Time"."Transaction Details"."Transaction Status") s_13,
    "Human Capital Management - Transaction Administration Real Time"."Facts - Transactions and Issues"."Count of Transactions" s_14
    FROM "Human Capital Management - Transaction Administration Real Time"
    WHERE
    (("Transaction Details"."Process Category" = 'Termination') AND ("Transaction Details"."Approval State" = 'COMPLETE'))
    )B
    ON A.s_2 = B.s_4
    )TERMINATION_INFO ORDER BY saw_0, saw_1, saw_2, saw_3, saw_4, saw_5, saw_6, saw_7, saw_8, saw_9, saw_10, saw_11, saw_12, saw_13, saw_14, saw_15, saw_16, saw_17, saw_18, saw_19, saw_20, saw_21, saw_22, saw_23, saw_24, saw_25, saw_26, saw_27, saw_28