ACA_MONTHLY_JOB

(SQL View)
 Index Back

ACA Monthly Total JOB

ACA Monthly Total based on PS_JOB information.

SELECT DISTINCT J.EMPLID , J.EMPL_RCD , A.ACA_GRP_ID , N.NAME , N.FIRST_NAME , N.LAST_NAME , BE.END_DT ACA_MONTH , CASE WHEN J.EMPL_TYPE = 'S' THEN J.STD_HOURS * ( SELECT %DecDiv( COUNT(*) , 5 ) FROM PS_TL_DATES_TBL T WHERE T.DAYOFWEEK IN (2, 3, 4, 5, 6) AND T.THE_DATE BETWEEN ( CASE WHEN BE.BEGIN_DT < ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.ACTION IN ('HIR','REH','ADL','ADD') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) THEN ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.ACTION IN ('HIR','REH','ADL','ADD') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) ELSE BE.BEGIN_DT END ) AND ( CASE WHEN BE.END_DT >= ( SELECT %DateAdd(MIN(M.EFFDT),-1) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('T', 'D', 'R') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) THEN ( SELECT %DateAdd(MIN(M.EFFDT),-1) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('T', 'D', 'R') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) ELSE BE.END_DT END ) ) ELSE 0.00 END ACA_MONTHLY_TOT , J.COMPANY , J.PAYGROUP , J.REG_REGION , J.BUSINESS_UNIT , J.SETID_DEPT , J.DEPTID , J.SETID_JOBCODE , J.JOBCODE , J.SETID_LOCATION , J.LOCATION , LT.STATE , J.UNION_CD , J.FULL_PART_TIME , J.EMPL_TYPE , J.EMPL_STATUS , J.EMPL_CLASS , J.HR_STATUS , J.STD_HOURS , A.ACA_FREQ, A.BN_ACA_ELIG_IND , CASE WHEN %DecDiv( ( SELECT COUNT(*) FROM PS_TL_DATES_TBL DE WHERE DE.DAYOFWEEK IN (2, 3, 4, 5, 6) AND DE.THE_DATE BETWEEN (CASE WHEN BE.BEGIN_DT < ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.ACTION IN ('HIR','REH','ADL','ADD') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) THEN ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.ACTION IN ('HIR','REH','ADL','ADD') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) ELSE BE.BEGIN_DT END ) AND (CASE WHEN BE.END_DT >= ( SELECT %DateAdd(MIN(M.EFFDT),-1) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('T', 'D', 'R') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) THEN ( SELECT %DateAdd(MIN(M.EFFDT),-1) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('T', 'D', 'R') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) ELSE BE.END_DT END ) ) , (SELECT COUNT(*) FROM PS_TL_DATES_TBL T WHERE T.DAYOFWEEK IN (2, 3, 4, 5, 6) AND T.THE_DATE BETWEEN BE.BEGIN_DT AND BE.END_DT ) ) > 0.01 THEN %DecDiv( ( SELECT COUNT(*) FROM PS_TL_DATES_TBL DE WHERE DE.DAYOFWEEK IN (2, 3, 4, 5, 6) AND DE.THE_DATE BETWEEN (CASE WHEN BE.BEGIN_DT < ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.ACTION IN ('HIR','REH','ADL','ADD') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) THEN ( SELECT MIN(M.EFFDT) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('A', 'P', 'Q', 'U') AND M.ACTION IN ('HIR','REH','ADL','ADD') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) ELSE BE.BEGIN_DT END ) AND (CASE WHEN BE.END_DT >= ( SELECT %DateAdd(MIN(M.EFFDT),-1) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('T', 'D', 'R') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) THEN ( SELECT %DateAdd(MIN(M.EFFDT),-1) FROM PS_JOB M WHERE M.EMPL_STATUS IN ('T', 'D', 'R') AND M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT BETWEEN BE.BEGIN_DT AND BE.END_DT ) ELSE BE.END_DT END ) ) , (SELECT COUNT(*) FROM PS_TL_DATES_TBL T WHERE T.DAYOFWEEK IN (2, 3, 4, 5, 6) AND T.THE_DATE BETWEEN BE.BEGIN_DT AND BE.END_DT ) ) ELSE 0.01 END ACA_PERIOD FROM PS_TL_EMPL_DATA ED, PS_JOB J , PS_NAMES N , PS_ACA_PER_ELIG A , PS_ACA_CMPY_TBL AC , PS_ACA_BGN_MTH_TBL BE , PS_LOCATION_TBL LT WHERE J.COMPANY = AC.COMPANY AND J.REG_REGION = 'USA' AND J.EFFDT <= BE.END_DT AND A.EMPLID = J.EMPLID AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_ACA_PER_ELIG A2 WHERE A2.EMPLID = A.EMPLID AND A2.ACA_GRP_ID = A.ACA_GRP_ID AND A2.EFFDT <= BE.END_DT) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_ACA_PER_ELIG A2 WHERE A2.EMPLID = A.EMPLID AND A2.ACA_GRP_ID = A.ACA_GRP_ID AND A2.EFFDT = A.EFFDT) AND ED.EMPLID = J.EMPLID AND ED.EMPL_RCD = J.EMPL_RCD AND ED.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_TL_EMPL_DATA M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT <= BE.END_DT) AND A.ACA_GRP_ID = AC.ACA_GRP_ID 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 <= BE.END_DT) 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 <= BE.END_DT ) AND J.EMPL_STATUS IN ('A', 'P', 'Q', 'U') 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) ) OR ( 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 BETWEEN BE.BEGIN_DT AND BE.END_DT ) AND J.EMPL_STATUS IN ('D', 'T', 'R') 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 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 <= %CurrentDateIn) AND N.EFF_STATUS = 'A' AND N.NAME_TYPE = 'PRI' AND J.SETID_LOCATION = LT.SETID AND J.LOCATION = LT.LOCATION AND LT.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_LOCATION_TBL M WHERE M.SETID = LT.SETID AND M.LOCATION = LT.LOCATION AND M.EFFDT <= J.EFFDT) AND LT.EFF_STATUS = 'A'

# 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 Record
3 ACA_GRP_ID Character(3) VARCHAR2(3) NOT NULL ACA Coomon 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_MONTH Date(10) DATE ACA Year and Month.
8 ACA_MONTHLY_TOT Number(8,2) DECIMAL(7,2) NOT NULL ACA Monthly Total.
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 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
15 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
16 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
17 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
18 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
19 STATE Character(6) VARCHAR2(6) NOT NULL State
20 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code
21 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
22 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
23 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
24 EMPL_CLASS Character(3) VARCHAR2(3) NOT NULL Employee Classification
25 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive
26 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
27 ACA_FREQ Character(1) VARCHAR2(1) NOT NULL ACA average service hours frequency.
M=Monthly
W=Weekly
28 BN_ACA_ELIG_IND Character(1) VARCHAR2(1) NOT NULL ACA (Affordable Care Act) Eligibility Indicator
A=Always Eligible
E=Eligible
I=Ineligible
U=To be determined
X=Excluded
29 ACA_PERIOD Number(4,2) DECIMAL(3,2) NOT NULL ACA