AOR analysis fails when I add representative or employee data

Sherie Ross

I'm trying to create an analysis using Workforce Management - Areas of Responsibility. I need to pull the AOR information as well as representative and employee information. I can get what I need from the AOR, but when I try to add representative or employee data, the report fails. I tried to create the analysis for the employee separately to see if I could get anything, and I was able to pull from just the Worker table. When I tried to add fields from Job, Position, Department, or Organization, the analysis failed.

Here is what I need (I removed fields that identify employee):

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
0 s_0,
"Workforce Management - Areas of Responsibility"."Area of Responsibility Setup"."Responsibility Name" s_1,
"Workforce Management - Areas of Responsibility"."Area of Responsibility Setup"."Responsibility Type" s_2,
"Workforce Management - Areas of Responsibility"."Department"."Department Name" s_3,
"Workforce Management - Areas of Responsibility"."Hierarchy Attributes"."Top Organization" s_4,
"Workforce Management - Areas of Responsibility"."Job"."Job Code" s_5,
"Workforce Management - Areas of Responsibility"."Job"."Job Name" s_6,
"Workforce Management - Areas of Responsibility"."Organization Hierarchy"."Level 01 Organization Name" s_7,
"Workforce Management - Areas of Responsibility"."Organization Hierarchy"."Level 02 Organization Name" s_8,
"Workforce Management - Areas of Responsibility"."Organization Hierarchy"."Level 03 Organization Name" s_9,
"Workforce Management - Areas of Responsibility"."Organization Hierarchy"."Level 04 Organization Name" s_10,
"Workforce Management - Areas of Responsibility"."Position"."Position Code" s_11,
"Workforce Management - Areas of Responsibility"."Position"."Position FTE" s_12,
"Workforce Management - Areas of Responsibility"."Position"."Position Name" s_13,
"Workforce Management - Areas of Responsibility"."Worker (Representative)"."Person Number" s_14,
"Workforce Management - Areas of Responsibility"."Worker"."Assignment Number" s_15,
"Workforce Management - Areas of Responsibility"."Worker"."HR Status" s_16,
"Workforce Management - Areas of Responsibility"."Worker"."Person Number" s_17,
"Workforce Management - Areas of Responsibility"."Worker"."Termination Date" s_18,
DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Area of Responsibility Setup"."Responsibility Type") s_19,
DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Job"."Job Name") s_20,
DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Position"."Position Name") s_21,
DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Worker"."HR Status") s_22
FROM "Workforce Management - Areas of Responsibility"
WHERE
((DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Area of Responsibility Setup"."Responsibility Type") = 'UT_NOTIF') AND (DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Worker"."HR Status") <> 'ACTIVE') AND ("Worker"."Termination Date" >= (CURRENT_DATE - 30)))
ORDER BY 5 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 15 ASC NULLS LAST, 18 ASC NULLS LAST, 16 ASC NULLS LAST, 17 ASC NULLS LAST, 19 ASC NULLS LAST, 12 ASC NULLS LAST, 14 ASC NULLS LAST, 13 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 7 ASC NULLS LAST, 11 ASC NULLS LAST, 10 ASC NULLS LAST, 9 ASC NULLS LAST, 8 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

This is from the analysis for just the employee information that works (before trying to add job or position):

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
0 s_0,
"Workforce Management - Areas of Responsibility"."Worker"."Assignment Number" s_1,
"Workforce Management - Areas of Responsibility"."Worker"."HR Status" s_2,
"Workforce Management - Areas of Responsibility"."Worker"."Person Number" s_3,
"Workforce Management - Areas of Responsibility"."Worker"."Termination Date" s_4,
DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Worker"."HR Status") s_5
FROM "Workforce Management - Areas of Responsibility"
WHERE
((DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Worker"."HR Status") <> 'ACTIVE') AND ("Worker"."Assignment Number" NOT LIKE 'P%') AND ("Worker"."Termination Date" >= (CURRENT_DATE - 30)))
ORDER BY 4 ASC NULLS LAST, 2 ASC NULLS LAST, 3 ASC NULLS LAST, 6 ASC NULLS LAST, 5 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

This results is this:

Then I try to add Position Code (but also data from job, department, and organization) where it did not work:

SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
0 s_0,
"Workforce Management - Areas of Responsibility"."Position"."Position Code" s_1,
"Workforce Management - Areas of Responsibility"."Worker"."Assignment Number" s_2,
"Workforce Management - Areas of Responsibility"."Worker"."HR Status" s_3,
"Workforce Management - Areas of Responsibility"."Worker"."Person Number" s_4,
"Workforce Management - Areas of Responsibility"."Worker"."Termination Date" s_5,
DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Worker"."HR Status") s_6
FROM "Workforce Management - Areas of Responsibility"
WHERE
((DESCRIPTOR_IDOF("Workforce Management - Areas of Responsibility"."Worker"."HR Status") <> 'ACTIVE') AND ("Worker"."Assignment Number" NOT LIKE 'P%') AND ("Worker"."Termination Date" >= (CURRENT_DATE - 30)))
ORDER BY 5 ASC NULLS LAST, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 7 ASC NULLS LAST, 6 ASC NULLS LAST, 2 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

The result is this: