ACA_MONTH(SQL View) |
Index Back |
|---|---|
ACA Monthly Totals ViewView of Views ACA_MONTH_PNA and ACA_MONTH_TL includes a source code |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.ACA_GRP_ID , A.NAME , A.FIRST_NAME , A.LAST_NAME , A.ACA_MONTH , A.COMPANY , A.BUSINESS_UNIT ,A.SETID_DEPT ,A.DEPTID ,A.EMPL_TYPE ,A.FULL_PART_TIME ,A.UNION_CD ,A.SETID_JOBCODE ,A.JOBCODE ,A.PAYGROUP ,A.EMPL_STATUS ,A.SETID_LOCATION ,A.LOCATION ,A.STATE ,A.ACA_FREQ ,A.BN_ACA_ELIG_IND , A.ACA_MONTHLY_TOT + B.ACA_MONTHLY_TOT ACA_MONTHLY_TOT , ( SELECT SUM(WJ.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_JOB WJ WHERE WJ.EMPLID = A.EMPLID AND WJ.ACA_GRP_ID = A.ACA_GRP_ID AND WJ.COMPANY = A.COMPANY AND WJ.ACA_MONTH = A.ACA_MONTH) + ( SELECT SUM(T.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_TL T WHERE T.EMPLID = A.EMPLID AND T.ACA_GRP_ID = A.ACA_GRP_ID AND T.COMPANY = A.COMPANY AND T.ACA_MONTH = A.ACA_MONTH) ACA_MONTHLY_TOT_2 , A.ACA_PERIOD FROM PS_ACA_MONTHLY_JOB A , PS_ACA_MONTHLY_TL B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.ACA_GRP_ID = B.ACA_GRP_ID AND A.ACA_MONTH = B.ACA_MONTH AND A.COMPANY = B.COMPANY |
| # | 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 | COMPANY |
Character(3) | VARCHAR2(3) NOT NULL | Company |
| 9 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | SETID_DEPT | Character(5) | VARCHAR2(5) NOT NULL | Department Set ID |
| 11 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 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 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 18 | 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 |
| 19 | SETID_LOCATION | Character(5) | VARCHAR2(5) NOT NULL | Location Set ID |
| 20 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 21 | STATE | Character(6) | VARCHAR2(6) NOT NULL | State |
| 22 | ACA_FREQ | Character(1) | VARCHAR2(1) NOT NULL |
ACA average service hours frequency.
M=Monthly W=Weekly |
| 23 | 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 |
| 24 | ACA_MONTHLY_AVG | Number(8,2) | DECIMAL(7,2) NOT NULL | ACA Monthly Total. |
| 25 | ACA_MONTHLY_TOT | Number(8,2) | DECIMAL(7,2) NOT NULL | ACA Monthly Total. |
| 26 | ACA_PERIOD | Number(4,2) | DECIMAL(3,2) NOT NULL | ACA |