ACA_MONTHLY_TL

(SQL View)
 Index Back

ACA Monthly Total TL

ACA Monthly Total from 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7))) 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7)) ELSE D.THE_DATE END ACA_MONTH , 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 B.THE_DATE < J.EFFDT AND %Substring(%CAST(%DATEOUT(B.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEOUT(J.EFFDT),DATE,CHARACTER),1,7) 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7)= %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7))) 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7)= %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7)) 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 B.THE_DATE < J.EFFDT AND %Substring(%CAST(%DATEOUT(B.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEOUT(J.EFFDT),DATE,CHARACTER),1,7) 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7)= %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7))) 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7)= %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7)) 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_MONTHLY_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 B.THE_DATE BETWEEN AC.EFFDT AND %CurrentDateIn AND D.THE_DATE BETWEEN AC.EFFDT AND %CurrentDateIn AND D.THE_DATE <= %CurrentDateIn AND D.THE_DATE = ( SELECT MAX(M.THE_DATE) FROM PS_TL_DATES_TBL M WHERE M.MONTHOFYEAR = D.MONTHOFYEAR AND M.CALENDAR_YEAR = D.CALENDAR_YEAR) AND B.THE_DATE = ( SELECT MIN(M.THE_DATE) FROM PS_TL_DATES_TBL M WHERE M.MONTHOFYEAR = D.MONTHOFYEAR AND M.CALENDAR_YEAR = D.CALENDAR_YEAR) AND D.THE_DATE >= J.EFFDT AND J.EMPLID = A.EMPLID AND A.ACA_GRP_ID = AC.ACA_GRP_ID AND ED.EMPLID = J.EMPLID AND ED.EMPL_RCD = J.EMPL_RCD 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 B.THE_DATE < J.EFFDT AND %Substring(%CAST(%DATEOUT(B.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEOUT(J.EFFDT),DATE,CHARACTER),1,7) THEN J.EFFDT 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7))) 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 %Substring(%CAST(%DATEOUT(D.THE_DATE),DATE,CHARACTER),1,7) = %Substring(%CAST(%DATEADD(M.EFFDT,-1),DATE,CHARACTER),1,7)) 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 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 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 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.COMPANY = J.COMPANY 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_MONTH Date(10) DATE ACA Year and Month.
8 ACA_MONTHLY_TOT Number(8,2) DECIMAL(7,2) NOT NULL ACA Monthly Total.
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 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
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