ACA_AVG_MTH_VW2

(SQL View)
 Index Back

ACA Monthly Average Totals

ACA Monthly Average Totals for caluclating the threshold for employees for Monthly period. This view fetches employees only when Monthly work period is chosen on the run control page.

SELECT J.FIRST_NAME , J.LAST_NAME , J.EMPLID , J.ACA_GRP_ID , J.ACA_MONTH , (SUM(J.ACA_MONTHLY_TOT) + (CASE WHEN ( SELECT SUM(T.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_TL T WHERE T.EMPLID = J.EMPLID AND T.ACA_GRP_ID = J.ACA_GRP_ID AND T.ACA_MONTH = J.ACA_MONTH) <> 0 THEN ( SELECT SUM(T.ACA_MONTHLY_TOT) FROM PS_ACA_MONTHLY_TL T WHERE T.EMPLID = J.EMPLID AND T.ACA_GRP_ID = J.ACA_GRP_ID AND T.ACA_MONTH = J.ACA_MONTH) ELSE 0.00 END)), J.ACA_PERIOD FROM PS_ACA_MONTHLY_JOB J GROUP BY J.FIRST_NAME, J.LAST_NAME, J.EMPLID, J.ACA_GRP_ID, J.ACA_MONTH, J.ACA_PERIOD

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
2 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 ACA_GRP_ID Character(3) VARCHAR2(3) NOT NULL ACA Common ID.
5 ACA_MONTH Date(10) DATE ACA Year and Month.
6 ACA_MONTHLY_AVG Number(8,2) DECIMAL(7,2) NOT NULL ACA Monthly Total.
7 ACA_PERIOD Number(4,2) DECIMAL(3,2) NOT NULL ACA