ACA_MONTH

(SQL View)
 Index Back

ACA Monthly Totals View

View 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.ACA_MONTHLY_TOT + CASE WHEN( SELECT T.ACA_MONTHLY_TOT FROM PS_ACA_MONTHLY_TL T WHERE T.EMPLID = A.EMPLID AND T.EMPL_RCD = A.EMPL_RCD AND T.ACA_GRP_ID = A.ACA_GRP_ID AND T.ACA_MONTH = A.ACA_MONTH AND T.COMPANY = A.COMPANY)<> 0 THEN ( SELECT T.ACA_MONTHLY_TOT FROM PS_ACA_MONTHLY_TL T WHERE T.EMPLID = A.EMPLID AND T.EMPL_RCD = A.EMPL_RCD AND T.ACA_GRP_ID = A.ACA_GRP_ID AND T.ACA_MONTH = A.ACA_MONTH AND T.COMPANY = A.COMPANY) ELSE 0.00 END ACA_MONTHLY_AVG, ( SELECT SUM(B.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_JOB B WHERE B.EMPLID = A.EMPLID AND B.ACA_GRP_ID = A.ACA_GRP_ID AND B.COMPANY = A.COMPANY AND B.ACA_MONTH = A.ACA_MONTH) + CASE WHEN( SELECT SUM(T2.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_TL T2 WHERE T2.EMPLID = A.EMPLID AND T2.ACA_GRP_ID = A.ACA_GRP_ID AND T2.COMPANY = A.COMPANY AND T2.ACA_MONTH = A.ACA_MONTH) <> 0 THEN ( SELECT SUM(T2.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_TL T2 WHERE T2.EMPLID = A.EMPLID AND T2.ACA_GRP_ID = A.ACA_GRP_ID AND T2.COMPANY = A.COMPANY AND T2.ACA_MONTH = A.ACA_MONTH) ELSE 0.00 END ACA_MONTHLY_TOT_2, A.ACA_PERIOD FROM PS_ACA_MONTHLY_JOB A

# 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.

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 ACA_MONTHLY_AVG Number(8,2) DECIMAL(7,2) NOT NULL ACA Monthly Total.
10 ACA_MONTHLY_TOT Number(8,2) DECIMAL(7,2) NOT NULL ACA Monthly Total.
11 ACA_PERIOD Number(4,2) DECIMAL(3,2) NOT NULL ACA