ACA_AVG_WK_VW2

(SQL View)
 Index Back

ACA Weekly Average Totals

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

SELECT J.FIRST_NAME , J.LAST_NAME , J.EMPLID , J.ACA_GRP_ID , J.ACA_WEEK , (SUM(J.ACA_WEEKLY_TOT) + (CASE WHEN ( SELECT SUM(T.ACA_WEEKLY_TOT) FROM PS_ACA_WEEKLY_TL T WHERE T.EMPLID = J.EMPLID AND T.ACA_GRP_ID = J.ACA_GRP_ID AND T.ACA_WEEK = J.ACA_WEEK) <> 0 THEN ( SELECT SUM(T.ACA_WEEKLY_TOT) FROM PS_ACA_WEEKLY_TL T WHERE T.EMPLID = J.EMPLID AND T.ACA_GRP_ID = J.ACA_GRP_ID AND T.ACA_WEEK = J.ACA_WEEK) ELSE 0.00 END)), J.ACA_PERIOD FROM PS_ACA_WEEKLY_JOB J GROUP BY J.FIRST_NAME, J.LAST_NAME, J.EMPLID, J.ACA_GRP_ID, J.ACA_WEEK, 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_WEEK Date(10) DATE ACA Year Month and Week.
6 ACA_WEEKLY_AVG Number(8,2) DECIMAL(7,2) NOT NULL ACA Weekly Average per month.
7 ACA_PERIOD Number(4,2) DECIMAL(3,2) NOT NULL ACA