ACA_WEEKLY_JOB

(SQL View)
 Index Back

ACA Weekly Total JOB

ACA Weekly Total based on PS_JOB.

SELECT J.EMPLID , J.EMPL_RCD , A.ACA_GRP_ID , N.NAME , N.FIRST_NAME , N.LAST_NAME , CASE WHEN (D.THE_DATE >= ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EFFDT > J.EFFDT AND M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND %DateAdd(M.EFFDT ,-1) BETWEEN B.THE_DATE AND %DateAdd(D.THE_DATE ,-1))) THEN ( SELECT %DateAdd(MIN(M2.EFFDT) ,-1) FROM PS_JOB M2 WHERE M2.EFFDT > J.EFFDT AND M2.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M2.EMPLID = J.EMPLID AND M2.EMPL_RCD = J.EMPL_RCD AND %DateAdd(M2.EFFDT ,-1) BETWEEN B.THE_DATE AND %DateAdd(D.THE_DATE ,-1)) ELSE D.THE_DATE END ACA_WEEK, CASE WHEN J.EMPL_TYPE = 'S' THEN J.STD_HOURS * ( SELECT COUNT(* ) / 5 FROM PS_TL_DATES_TBL T WHERE D.WEEKOFYEAR = T.WEEKOFYEAR AND T.DAYOFWEEK IN (2, 3, 4, 5, 6) AND T.CALENDAR_YEAR = D.CALENDAR_YEAR AND T.THE_DATE BETWEEN (CASE WHEN J.EFFDT BETWEEN %DateAdd(B.THE_DATE ,+1) AND D.THE_DATE THEN J.EFFDT ELSE B.THE_DATE END) AND (CASE WHEN (D.THE_DATE >= ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EFFDT > J.EFFDT AND M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND %DateAdd(M.EFFDT ,-1) BETWEEN B.THE_DATE AND %DateAdd(D.THE_DATE ,-1))) THEN ( SELECT %DateAdd(MIN(M2.EFFDT),-1) FROM PS_JOB M2 WHERE M2.EFFDT > J.EFFDT AND M2.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M2.EMPLID = J.EMPLID AND M2.EMPL_RCD = J.EMPL_RCD AND %DateAdd(M2.EFFDT ,-1) BETWEEN B.THE_DATE AND %DateAdd(D.THE_DATE ,-1)) ELSE D.THE_DATE END)) ELSE 0.00 END HOURS_PER_WEEK , J.COMPANY, J.PAYGROUP, J.REG_REGION, J.BUSINESS_UNIT, J.DEPTID, J.JOBCODE, J.SETID_JOBCODE, J.LOCATION, J.SETID_LOCATION, J.UNION_CD, J.FULL_PART_TIME, J.EMPL_TYPE, J.EMPL_STATUS, J.EMPL_CLASS, J.HR_STATUS, J.STD_HOURS , CASE WHEN EM.ORIG_HIRE_DT BETWEEN B.THE_DATE AND D.THE_DATE THEN ( SELECT COUNT(* ) FROM PS_TL_DATES_TBL DE WHERE D.WEEKOFYEAR = DE.WEEKOFYEAR AND DE.DAYOFWEEK IN (2,3,4,5,6) AND DE.CALENDAR_YEAR = D.CALENDAR_YEAR AND DE.THE_DATE BETWEEN EM.ORIG_HIRE_DT AND D.THE_DATE) / ( SELECT COUNT(* ) FROM PS_TL_DATES_TBL T WHERE D.WEEKOFYEAR = T.WEEKOFYEAR AND T.DAYOFWEEK IN (2,3,4,5,6) AND T.CALENDAR_YEAR = D.CALENDAR_YEAR) WHEN EM.HIRE_DT BETWEEN B.THE_DATE AND D.THE_DATE THEN ( SELECT COUNT(* ) FROM PS_TL_DATES_TBL DE WHERE D.WEEKOFYEAR = DE.WEEKOFYEAR AND DE.DAYOFWEEK IN(2,3,4,5,6) AND DE.CALENDAR_YEAR = D.CALENDAR_YEAR AND DE.THE_DATE BETWEEN EM.HIRE_DT AND D.THE_DATE) / ( SELECT COUNT(* ) FROM PS_TL_DATES_TBL T WHERE D.WEEKOFYEAR = T.WEEKOFYEAR AND T.DAYOFWEEK IN (2,3,4,5,6) AND T.CALENDAR_YEAR = D.CALENDAR_YEAR) WHEN EM.REHIRE_DT BETWEEN B.THE_DATE AND D.THE_DATE THEN ( SELECT COUNT(* ) FROM PS_TL_DATES_TBL DE WHERE D.WEEKOFYEAR = DE.WEEKOFYEAR AND DE.DAYOFWEEK IN (2,3,4,5,6) AND DE.CALENDAR_YEAR = D.CALENDAR_YEAR AND DE.THE_DATE BETWEEN EM.REHIRE_DT AND D.THE_DATE) / ( SELECT COUNT(* ) FROM PS_TL_DATES_TBL T WHERE D.WEEKOFYEAR = T.WEEKOFYEAR AND T.DAYOFWEEK IN (2,3,4,5,6) AND T.CALENDAR_YEAR = D.CALENDAR_YEAR) ELSE 1 END ACA_PERIOD FROM PS_JOB J, PS_NAMES N, PS_ACA_PER_ELIG A, PS_ACA_CMPY_TBL AC, PS_EMPLOYEES EM , PS_TL_DATES_TBL B, PS_TL_DATES_TBL D WHERE J.COMPANY = AC.COMPANY AND J.EMPLID BETWEEN ' ' AND 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AND J.REG_REGION = 'USA' AND B.THE_DATE BETWEEN AC.EFFDT AND %CurrentDateIn AND D.THE_DATE BETWEEN AC.EFFDT AND %CurrentDateIn AND D.THE_DATE = ( SELECT MAX(M.THE_DATE) FROM PS_TL_DATES_TBL M WHERE M.WEEKOFYEAR = D.WEEKOFYEAR AND M.CALENDAR_YEAR = D.CALENDAR_YEAR) AND B.THE_DATE = ( SELECT MIN(M.THE_DATE) FROM PS_TL_DATES_TBL M WHERE M.WEEKOFYEAR = D.WEEKOFYEAR AND M.CALENDAR_YEAR = D.CALENDAR_YEAR) AND D.THE_DATE >= J.EFFDT AND D.THE_DATE <= %CurrentDateIn AND A.EMPLID = J.EMPLID AND A.ACA_GRP_ID = AC.ACA_GRP_ID AND J.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_JOB M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT <= (CASE WHEN (D.THE_DATE >= ( SELECT MIN(M3.EFFDT) FROM PS_JOB M3 WHERE M3.EFFDT > J.EFFDT AND M3.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M3.EMPLID = J.EMPLID AND M3.EMPL_RCD = J.EMPL_RCD AND %DateAdd(M3.EFFDT,-1) BETWEEN B.THE_DATE AND %DateAdd(D.THE_DATE,-1))) THEN ( SELECT %DateAdd(MIN(M2.EFFDT),-1) FROM PS_JOB M2 WHERE M2.EFFDT > J.EFFDT AND M2.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M2.EMPLID = J.EMPLID AND M2.EMPL_RCD = J.EMPL_RCD AND %DateAdd(M2.EFFDT,-1) BETWEEN B.THE_DATE AND %DateAdd(D.THE_DATE,-1)) ELSE D.THE_DATE END)) AND J.EFFSEQ = ( SELECT MAX(M.EFFSEQ) FROM PS_JOB M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT = J.EFFDT) AND J.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND N.EMPLID = J.EMPLID AND N.EFFDT = ( SELECT MAX(A_ED1.EFFDT) FROM PS_NAMES A_ED1 WHERE N.EMPLID = A_ED1.EMPLID AND N.NAME_TYPE = A_ED1.NAME_TYPE AND A_ED1.EFFDT <= D.THE_DATE) AND N.EFF_STATUS = 'A' AND N.NAME_TYPE = 'PRI' AND EM.EMPLID = J.EMPLID AND EM.EMPL_RCD = J.EMPL_RCD AND A.EFFDT = ( SELECT MAX(ES.EFFDT) FROM PS_ACA_PER_ELIG ES WHERE ES.EMPLID = A.EMPLID AND ES.ACA_GRP_ID = AC.ACA_GRP_ID AND ES.EFFDT <= D.THE_DATE) AND A.EFFSEQ = ( SELECT MAX(ES.EFFSEQ) FROM PS_ACA_PER_ELIG ES WHERE ES.EMPLID = A.EMPLID AND ES.ACA_GRP_ID = A.ACA_GRP_ID AND ES.EFFDT = A.EFFDT) AND AC.EFFDT = ( SELECT MAX(A_ED7.EFFDT) FROM PS_ACA_CMPY_TBL A_ED7 WHERE A_ED7.COMPANY = AC.COMPANY AND A_ED7.EFFDT <= D.THE_DATE)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr
3 ACA_GRP_ID Character(3) VARCHAR2(3) NOT NULL ACA Common ID.

Prompt Table: ACA_ID_TBL

4 NAME Character(50) VARCHAR2(50) NOT NULL Name
5 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
6 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
7 ACA_WEEK Date(10) DATE ACA Year Month and Week.
8 ACA_WEEKLY_TOT Number(10,2) DECIMAL(9,2) NOT NULL ACA
9 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
10 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
11 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
12 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
13 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
14 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
15 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
17 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
18 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code
19 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
20 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
21 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration
22 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification
23 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
24 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
25 ACA_PERIOD Number(4,2) DECIMAL(3,2) NOT NULL ACA