ACA_AVG_WK_VW(SQL View) |
Index Back |
---|---|
ACA Weekly Average TotalsACA 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 |