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

Question

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

RVohra · Answer

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

Riti Malhotra · Answer

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

Nathan CCC · Answer

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)