Query For Absence Custom OTBI Query (Workforce Management - Absence Real Time) , OTBI Reporting Using Two Subject Areas

 SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT DISTINCT 

   Abs_name_type saw_0,

   Abs_entry_created_by saw_1,

   Abs_last_updated_by saw_2,

   Abs_End_Date saw_3,

   Abs_Start_Date saw_4,

   Abs_Entry_Identifier saw_5,

   Dep_Name saw_6,

   Grade_Name saw_7,

   sector_2 saw_8,

   Job_Name saw_9,

   Legal_employer_name saw_10,

   Postion_name saw_11,

   Emp_name saw_12,

   Hire_date saw_13,

   Emp_num saw_14,

   Abs_status saw_15,

   Approval_status saw_16,

   Abs_UOM saw_17,

   BU_Name saw_18,

   Leave_Duration saw_19

 FROM (select 

   "Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name" Abs_name_type,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Created By" Abs_entry_created_by,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Last Updated By" Abs_last_updated_by,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status" Abs_status,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status" Approval_status,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date End" Abs_End_Date,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date Start" Abs_Start_Date,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Per Absence Entry Identifier" Abs_Entry_Identifier,

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM" Abs_UOM,

   "Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name" BU_Name,

   "Workforce Management - Absence Real Time"."Department"."Department Name" Dep_Name,

   "Workforce Management - Absence Real Time"."Grade"."Grade Name" Grade_Name,

   "Workforce Management - Absence Real Time"."Job"."Job Name" Job_Name,

   "Workforce Management - Absence Real Time"."Legal Employer"."Name" Legal_employer_name,

   "Workforce Management - Absence Real Time"."Position"."Position Name" Postion_name,

   "Workforce Management - Absence Real Time"."Worker"."Employee Name" Emp_name,

   "Workforce Management - Absence Real Time"."Worker"."Enterprise Hire Date" Hire_date,

   "Workforce Management - Absence Real Time"."Worker"."Person Number" Emp_num,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status") Abs_status_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status") Approval_status_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM") Abs_UOM_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name")BU_Name_des ,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Job"."Job Name") Job_Name_des,

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Position"."Position Name") Postion_name_des,

   "Workforce Management - Absence Real Time"."Assignment Absences"."Leave Duration" Leave_Duration,

   REPORT_SUM("Workforce Management - Absence Real Time"."Assignment Absences"."Leave Duration" 

   BY "Workforce Management - Absence Real Time"."Worker"."Person Number",

   "Workforce Management - Absence Real Time"."Worker"."Enterprise Hire Date",

   "Workforce Management - Absence Real Time"."Worker"."Employee Name",

   "Workforce Management - Absence Real Time"."Department"."Department Name",

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name"),

   "Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date Start",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Date End",

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM"),

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status"),

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status"),

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Created By",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Last Updated By",

   "Workforce Management - Absence Real Time"."Assignment Absences Details"."Per Absence Entry Identifier",

   DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Position"."Position Name"),"Workforce Management - Absence Real Time"."Grade"."Grade Name",DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Job"."Job Name")) Duration

FROM "Workforce Management - Absence Real Time"

WHERE

((DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Worker"."HR Status") NOT IN ('*)nqgtn(*', 'INACTIVE')))) TABLE_A INNER JOIN (

SELECT

   "Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_SECTOR_" sector_2,

   "Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" person_number2

FROM "Workforce Management - Worker Assignment Real Time"

WHERE

(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") IN (1, 2))

) TABLE_B ON TABLE_A.Emp_num = TABLE_B.person_number2

 WHERE 

(Emp_num IN ('XYZ'))

 ORDER BY saw_14, saw_4 DESC