ACA_WEEKLY_TL(SQL View) |
Index Back |
|---|---|
ACA Weekly Total TLACA Weekly totals based on PS_TL_PAYABLE_TIME. |
| SELECT ED.EMPLID , ED.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(M.EFFDT),-1) 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)) ELSE D.THE_DATE END ACA_WEEK, CASE WHEN ( SELECT SUM(T.TL_QUANTITY) FROM PS_TL_PAYABLE_TIME T WHERE T.TRC IN ( SELECT W.TRC FROM PS_ACA_TRCPGM_TBL W WHERE W.WORKGROUP = ED.WORKGROUP AND W.EFFDT <= T.DUR) AND T.TRC NOT IN ( SELECT DISTINCT T.TRC FROM PS_TL_TRC_TBL T , PS_PAYGROUP_TBL P WHERE P.ERNCD_REG_HRS = T.TL_COPY_FROM AND P.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_PAYGROUP_TBL M WHERE M.COMPANY = P.COMPANY AND M.PAYGROUP = P.PAYGROUP AND M.EFFDT <= D.THE_DATE) AND P.EFF_STATUS = 'A' AND T.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_TL_TRC_TBL M WHERE M.TRC = T.TRC AND M.EFFDT <= D.THE_DATE) AND T.EFF_STATUS = 'A' AND J.EMPL_TYPE = 'S' AND P.COMPANY = J.COMPANY AND P.PAYGROUP = J.PAYGROUP) AND T.DUR 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(M.EFFDT),-1) 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)) ELSE D.THE_DATE END) AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD AND T.PAYABLE_STATUS IN ('AP','CL','DL','ES','NA','PD')) > 0 THEN ( SELECT SUM(T.TL_QUANTITY) FROM PS_TL_PAYABLE_TIME T WHERE T.TRC IN ( SELECT W.TRC FROM PS_ACA_TRCPGM_TBL W WHERE W.WORKGROUP = ED.WORKGROUP AND W.EFFDT <= T.DUR) AND T.TRC NOT IN ( SELECT DISTINCT T.TRC FROM PS_TL_TRC_TBL T , PS_PAYGROUP_TBL P WHERE P.ERNCD_REG_HRS = T.TL_COPY_FROM AND P.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_PAYGROUP_TBL M WHERE M.COMPANY = P.COMPANY AND M.PAYGROUP = P.PAYGROUP AND M.EFFDT <= D.THE_DATE) AND P.EFF_STATUS = 'A' AND T.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_TL_TRC_TBL M WHERE M.TRC = T.TRC AND M.EFFDT <= D.THE_DATE) AND T.EFF_STATUS = 'A' AND J.EMPL_TYPE = 'S' AND P.COMPANY = J.COMPANY AND P.PAYGROUP = J.PAYGROUP) AND T.DUR 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(M.EFFDT),-1) 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)) ELSE D.THE_DATE END) AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD AND T.PAYABLE_STATUS IN ('AP','CL','DL','ES','NA','PD')) ELSE 0.00 END ACA_WEEKLY_TOT, J.COMPANY, J.PAYGROUP, J.REG_REGION, J.BUSINESS_UNIT, J.DEPTID, J.JOBCODE, J.LOCATION, J.UNION_CD, J.FULL_PART_TIME, J.EMPL_TYPE, J.EMPL_STATUS, J.EMPL_CLASS, J.HR_STATUS, J.STD_HOURS FROM PS_TL_EMPL_DATA ED, PS_TL_DATES_TBL D, PS_TL_DATES_TBL B, PS_JOB J, PS_NAMES N, PS_ACA_PER_ELIG A, PS_ACA_CMPY_TBL AC WHERE J.COMPANY = AC.COMPANY AND J.EMPLID BETWEEN ' ' AND 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZ' AND J.REG_REGION = 'USA' 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 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 <= D.THE_DATE) 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(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(M.EFFDT),-1) 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)) 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 AC.COMPANY = J.COMPANY 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. |
| 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 | DESCR2 | Character(30) | VARCHAR2(30) NOT NULL | Descr2 |
| 14 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 15 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL | Location Code |
| 16 | UNION_CD | Character(3) | VARCHAR2(3) NOT NULL | Union Code |
| 17 | FULL_PART_TIME | Character(1) | VARCHAR2(1) NOT NULL |
Full/Part Time
D=On Demand F=Full-Time P=Part-Time |
| 18 | EMPL_TYPE | Character(1) | VARCHAR2(1) NOT NULL |
Employee Type
E=Exception Hourly H=Hourly N=Not Applicable S=Salaried |
| 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 | EMPL_CLASS | Character(3) | VARCHAR2(3) NOT NULL | Employee Classification |
| 21 | HR_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
HR Status
A=Active I=Inactive |
| 22 | STD_HOURS | Number(7,2) | DECIMAL(6,2) NOT NULL | Standard Hours |