ACA_WEEKLY_TL

(SQL View)
 Index Back

ACA Weekly Total TL

ACA Weekly totals based on PS_TL_PAYABLE_TIME.

SELECT DISTINCT J.EMPLID , J.EMPL_RCD , A.ACA_GRP_ID , N.NAME , N.FIRST_NAME , N.LAST_NAME , BE.END_DT 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 = ( SELECT MAX(EFFDT) FROM PS_ACA_TRCPGM_TBL WHERE WORKGROUP = W.WORKGROUP AND 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 <= BE.END_DT) 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 <= BE.END_DT) 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 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 ) AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD AND T.PAYABLE_STATUS IN ('AP', 'CL', 'DL', 'ES', 'NA', 'PD', 'TP')) > 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 = ( SELECT MAX(EFFDT) FROM PS_ACA_TRCPGM_TBL WHERE WORKGROUP = W.WORKGROUP AND 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 <= BE.END_DT) 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 <= BE.END_DT) 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 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 ) AND T.EMPLID = J.EMPLID AND T.EMPL_RCD = J.EMPL_RCD AND T.PAYABLE_STATUS IN ('AP', 'CL', 'DL', 'ES', 'NA', 'PD', 'TP')) 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 , A.ACA_FREQ, A.BN_ACA_ELIG_IND 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_WK_TBL BE 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'

# 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 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
23 ACA_FREQ Character(1) VARCHAR2(1) NOT NULL ACA average service hours frequency.
M=Monthly
W=Weekly
24 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