CT_CORE_TAS_ATT_ACCRUALS

Purpose of Query

Returns an employee’s leave accrual earnings along with his or her time and attendance entries recorded in TAS.

Folder

HR

Tables

Top Level

A.CTAS_ATT – TAS Attendance

B.CTAS_EMP_MSTR – TAS Employee Master

Subquery - C.CTAS_EMP_MSTR – TAS Employee Master

Note: A JOIN is an SQL command that is used to relate two or more data tables (Records in PeopleSoft) based on the use of related (key) fields from one table to the next. Once joined, data can be retrieved without repeating all of the data in every table.

Union

D.CTAS_ERN – TAS Earn

E.CTAS_EMP_MSTR – TAS Employee Master

Subquery - F.CTAS_EMP_MSTR – TAS Employee Master

Note: A Union query combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union. The two queries must have the same number of columns and compatible data types to unite.

Prompts

EMPLID – Employee ID

Sample output:

Please note, due to page size limitations, sample results may be incomplete.

Criteria

Top Level

A.EMPLID – EmplID = B.EMPLID – EmplID

A.EMPLID – EmplID = :1

B.CREATE_DATE – Creation Date = Sub query

Sub query

MAX(C.CREATE_DATE – Creation Date)

B.EMPLID – EmplID = C.EMPLID – EmplID

Fields Top Level

Col / Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / A.EMPLID - EmplID / Char11 / ID
2 / B.LAST_NAME – Last Name / Char30 / Last Name
3 / B.FIRST_NAME – First Name / Char30 / First Name
4 / A.CREATION_DATE – Date Created / Date / 1 / Created
5 / A.CODE – CODE / Char4 / CODE
6 / A.CT_ATT_HOURS – Attendance Hours / SNm7.4 / Att Hrs
7 / A.CT_STD_HRS_PER_DAY – Standard Hours Per Day / Num6.2 / Hrs/Day
8 / A.CT_DAYS_OR_HOURS – Days or Hours / Char1 / Days/Hours
9 / A.TYPE - Type / Char25 / Type
10 / 0 / SNm7.4 / Accrual Balance
11 / A.AGENCY_ID – Agency ID / Char20 / Agency ID
12 / A.CT_VAX_DEPT - VAX Department Description / Char100 / VAX Department Descr
13 / A.CT_VAX_SUB_DEPT - VAX Sub-Department Description / Char100 / VAX Sub-Department Descr
14 / A.CT_VAX_AGENCY_ID - VAX Agency ID / Char10 / VAX Agcy ID
15 / A.DIRLOCATION - Directory Network Location / Char254 / Dir Location
16 / D.DEPTID – Department / Char10 / CORE DeptID

Criteria

Union

D.EMPLID – EmplID = E.EMPLID – EmplID

D.EMPLID – EmplID = :1

D.CT_TYPE_LEAVE not in list (‘CE’, ‘HE’)

E.CREATE_DATE – Creation Date = Sub query

Sub query

MAX(F.CREATE_DATE – Creation Date)

E.EMPLID – EmplID = F.EMPLID – EmplID

Fields Union

Col / Fieldname / Format / Ord / XLAT / Agg / Heading Text
1 / D.EMPLID - EmplID / Char11 / ID
2 / E.LAST_NAME – Last Name / Char30 / Last Name
3 / E.FIRST_NAME – First Name / Char30 / First Name
4 / D.CT_EARN_DATE - Earn Jul Date / Date / 1 / Date
5 / D.CT_TYPE_LEAVE – Type Leave / Char3 / Type Leave
6 / D.CT_EARNED_HRS – Earned Hours / SNm7.4 / Earned Hours
7 / D.CT_STD_HRS_PER_DAY – Standard Hours Per Day / Num6.2 / Hrs/Day
8 / D.CT_DAYS_OR_HOURS – Days or Hours / Char1 / Days/Hours
9 / ‘Accrual Earn/Adj Row’ / Char25 / Type
10 / D.CT_AVAIL_HRS – Available Hours / SNm7.4 / Hours
11 / ‘ ‘ / Char20 / Agency ID
12 / D.CT_VAX_DEPT - VAX Department Description / Char100 / VAX Department Descr
13 / D.CT_VAX_SUB_DEPT - VAX Sub-Department Description / Char100 / VAX Sub-Department Descr
14 / D.CT_VAX_AGENCY_ID - VAX Agency ID / Char10 / VAX Agcy ID
15 / D.DIRLOCATION - Directory Network Location / Char254 / Dir Location
16 / D.DEPTID – Department / Char10 / CORE DeptID

Note: On the Top Level column 10 and on the Union level columns 9 and 11 are expressions. On the Top Level column 11 and on the Union level column 11 are balancing literals, placeholder fields. On the Union level, column 9 alerts users to those rows of data that reflect the leave accrual earning rows.