ACA_WEEK

(SQL View)
 Index Back

ACA Weekly Totals View

View of Views ACA_WEEKLY_JOB and ACA_WEEKLY_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_WEEK , A.COMPANY , A.ACA_WEEKLY_TOT + CASE WHEN ( SELECT T.ACA_WEEKLY_TOT FROM PS_ACA_WEEKLY_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_WEEK = A.ACA_WEEK AND T.COMPANY = A.COMPANY) <> 0 THEN ( SELECT T.ACA_WEEKLY_TOT FROM PS_ACA_WEEKLY_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_WEEK = A.ACA_WEEK AND T.COMPANY = A.COMPANY) ELSE 0.00 END ACA_WEEKLY_TOT, ( SELECT SUM(B.ACA_WEEKLY_TOT) FROM PS_ACA_WEEKLY_JOB B WHERE B.EMPLID = A.EMPLID AND B.ACA_GRP_ID = A.ACA_GRP_ID AND B.COMPANY = A.COMPANY AND B.ACA_WEEK = A.ACA_WEEK) + CASE WHEN ( SELECT SUM(B2.ACA_WEEKLY_TOT) FROM PS_ACA_WEEKLY_TL B2 WHERE B2.EMPLID = A.EMPLID AND B2.ACA_GRP_ID = A.ACA_GRP_ID AND B2.ACA_WEEK = A.ACA_WEEK) <> 0 THEN ( SELECT SUM(B3.ACA_WEEKLY_TOT) FROM PS_ACA_WEEKLY_TL B3 WHERE B3.EMPLID = A.EMPLID AND B3.ACA_GRP_ID = A.ACA_GRP_ID AND B3.ACA_WEEK = A.ACA_WEEK) ELSE 0.00 END ACA_WEEKLY_TOT_2, A.ACA_PERIOD FROM PS_ACA_WEEKLY_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_WEEK Date(10) DATE ACA Year Month and Week.
8 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
9 ACA_WEEKLY_AVG Number(8,2) DECIMAL(7,2) NOT NULL ACA Weekly Average per month.
10 ACA_WEEKLY_TOT Number(10,2) DECIMAL(9,2) NOT NULL ACA
11 ACA_PERIOD Number(4,2) DECIMAL(3,2) NOT NULL ACA