ACA_AVG_WK_VW

(SQL View)
Index Back

ACA Weekly Average Totals

ACA Weekly Average Totals for caluclating the threshold for employees for Monthly period. This view fetches employees only when Weekly work period is chosen on the run control page.

SELECT D.EMPLID , D.EMPL_RCD , E.ACA_GRP_ID , P.FIRST_NAME , P.LAST_NAME , E.BN_ACA_ELIG_IND , D.COMPANY , D.BUSINESS_UNIT , D.SETID_DEPT , D.DEPTID , DE.DESCR , D.EMPL_TYPE , D.FULL_PART_TIME , D.UNION_CD , D.SETID_JOBCODE , D.JOBCODE , J.DESCR , D.PAYGROUP , D.EMPL_STATUS , D.SETID_LOCATION , D.LOCATION , L.DESCR , E.STATE , S.DESCR , K.ACA_WEEKLY_DESC , %DecDiv( SUM(E.ACA_WEEKLY_AVG) , SUM(E.ACA_PERIOD) ) WEEKLY_AVG , %DecDiv( SUM(E.ACA_WEEKLY_TOT) , SUM(E.ACA_PERIOD) ) WEEKLY_SUM FROM PS_JOB D , PS_NAMES P , PS_ACA_WEEK E , PS_DEPT_TBL DE , PS_ACA_INST K , PS_JOBCODE_TBL J , PS_ACA_RUNCTL B , PS_LOCATION_TBL L LEFT OUTER JOIN PS_STATE_TBL S ON L.COUNTRY = S.COUNTRY AND L.STATE = S.STATE WHERE B.ACA_FREQ = 'W' AND E.ACA_WEEK BETWEEN B.FROM_DT AND B.THRU_DT AND E.ACA_FREQ = B.ACA_FREQ AND E.BN_ACA_ELIG_IND IN ('I', 'E', 'U') AND D.EMPLID = E.EMPLID AND D.EMPL_RCD = E.EMPL_RCD AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = D.EMPLID AND EMPL_RCD = D.EMPL_RCD AND EFFDT <= B.THRU_DT) AND D.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = D.EMPLID AND EMPL_RCD = D.EMPL_RCD AND EFFDT = D.EFFDT) AND P.EMPLID = D.EMPLID AND P.NAME_TYPE ='PRI' AND P.EFFDT = ( SELECT MAX(EFFDT) FROM PS_NAMES WHERE EMPLID = P.EMPLID AND NAME_TYPE = P.NAME_TYPE AND EFFDT <= B.THRU_DT) AND DE.SETID = E.SETID_DEPT AND DE.DEPTID = E.DEPTID AND DE.EFFDT = ( SELECT MAX(EFFDT) FROM PS_DEPT_TBL WHERE SETID = DE.SETID AND DEPTID = DE.DEPTID AND EFFDT <= B.THRU_DT ) AND J.SETID = E.SETID_JOBCODE AND J.JOBCODE = E.JOBCODE AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL WHERE SETID = J.SETID AND JOBCODE = J.JOBCODE AND EFFDT <= B.THRU_DT ) AND L.SETID = E.SETID_LOCATION AND L.LOCATION = E.LOCATION AND L.EFFDT = ( SELECT MAX(EFFDT) FROM PS_LOCATION_TBL WHERE SETID = L.SETID AND LOCATION = L.LOCATION AND EFFDT <= B.THRU_DT ) GROUP BY D.EMPLID , D.EMPL_RCD , E.ACA_GRP_ID , P.FIRST_NAME , P.LAST_NAME , E.BN_ACA_ELIG_IND , D.COMPANY , D.BUSINESS_UNIT , D.SETID_DEPT , D.DEPTID , DE.DESCR , D.EMPL_TYPE , D.FULL_PART_TIME , D.UNION_CD , D.SETID_JOBCODE , D.JOBCODE , J.DESCR , D.PAYGROUP , D.EMPL_STATUS , D.SETID_LOCATION , D.LOCATION , L.DESCR , E.STATE , S.DESCR , K.ACA_WEEKLY_DESC HAVING SUM(E.ACA_PERIOD) > 0 AND %DecDiv( SUM(E.ACA_WEEKLY_TOT) , SUM(E.ACA_PERIOD) ) BETWEEN MAX(K.ACA_WEEKLY_L) AND MAX(K.ACA_WEEKLY_U)

# 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.
4 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
5 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
6 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
7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
8 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
9 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
11 DEPTNAME Character(30) VARCHAR2(30) NOT NULL Department Name
12 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
E=Exception Hourly
H=Hourly
N=Not Applicable
S=Salaried
13 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
D=On Demand
F=Full-Time
P=Part-Time
14 UNION_CD Character(3) VARCHAR2(3) NOT NULL Union Code
15 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID
16 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
17 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title
18 PAYGROUP Character(3) VARCHAR2(3) NOT NULL Pay Group
19 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
20 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
21 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
22 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location Description
23 STATE Character(6) VARCHAR2(6) NOT NULL State
24 STATE_DESCR Character(30) VARCHAR2(30) NOT NULL State Description
25 ACA_WEEKLY_DESC Character(30) VARCHAR2(30) NOT NULL ACA Weekly description field for Weekly period specifying as label on the ACA Threshold report pivot grid. For example, Medium 22-28 Hours.
26 ACA_WEEKLY_AVG Number(8,2) DECIMAL(7,2) NOT NULL ACA Weekly Average per month.
27 ACA_WEEKLY_TOT Number(10,2) DECIMAL(9,2) NOT NULL ACA