---
title: "pk82029_titan stored procedure documentation"
summary: "Analysis of Oracle package/procedure including business rules and operations."
status: draft
owner: "SAIF"
last_reviewed: "2026-07-24"
source_schema: "`WCIS_DBA`"
source_name: "pk82029_titan"
source_type: "`Package`"
oracle_status: "found"
library: SAIF.Business
reference_type: package
---

# pk82029_titan

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `WCIS_DBA` |
| **Type** | `Package` |
| **Database** | Oracle |
| **Calling libraries** | `SAIF.Business` |
| **Calling classes** | `clsEmployeeLeave, clsEmployeeLeaveCollection` |

## Purpose

Employee-leave package that performs create, list, update, and delete operations for employee leave rows.

## Procedures / Methods

### Insert_employee_leave

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` row count (`SQL%ROWCOUNT`) representing the inserted employee-leave row. |
| **Called by** | `clsEmployeeLeave.BeforeInsert` |

**Description:** Inserts an employee-leave row and stamps create and update audit columns with `SYSDATE`.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `employee_leave_seq_num_in` | IN | EMPLOYEE_LEAVE.employee_leave_seq_num%TYPE | — | Employee leave sequence number. |
| `employee_emplid_in` | IN | EMPLOYEE_LEAVE.employee_emplid%TYPE | — | Employee EMPLID associated with the leave record. |
| `leave_type_code_in` | IN | EMPLOYEE_LEAVE.LEAVE_TYPE_CODE%TYPE | — | Leave type code to persist. |
| `system_type_code_in` | IN | EMPLOYEE_LEAVE.SYSTEM_TYPE_CODE%TYPE | — | System type code to persist. |
| `begin_date_in` | IN | EMPLOYEE_LEAVE.begin_date%TYPE | — | Beginning date of the leave interval. |
| `end_date_in` | IN | EMPLOYEE_LEAVE.end_date%TYPE | — | Ending date of the leave interval. |
| `CREATE_USER_in` | IN | EMPLOYEE_LEAVE.CREATE_USER%TYPE | — | User ID recorded on insert. |

### select_employee_leave

| Field | Value |
| --- | --- |
| **Returns** | A `REF CURSOR` over employee-leave rows joined to leave-type descriptions. |
| **Called by** | `clsEmployeeLeaveCollection.GetEmployeeLeave` |

**Description:** Returns employee-leave rows for the requested system type and start-date horizon.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `StartDate` | IN | DATE | — | Lower-bound date used to filter employee leave rows. |
| `SystemTypeCode` | IN | VARCHAR2 | — | System-type discriminator for employee leave rows. |
| `recordset` | OUT | ref_cursor | — | Result cursor returned to the caller. |

### update_employee_leave

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` row count (`SQL%ROWCOUNT`) representing the updated employee-leave row. |
| **Called by** | `clsEmployeeLeave.BeforeUpdate` |

**Description:** Updates the persisted leave row by sequence number and refreshes the audit columns.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `employee_leave_seq_num_in` | IN | EMPLOYEE_LEAVE.employee_leave_seq_num%TYPE | — | Employee leave sequence number. |
| `employee_emplid_in` | IN | EMPLOYEE_LEAVE.employee_emplid%TYPE | — | Employee EMPLID associated with the leave record. |
| `leave_type_code_in` | IN | EMPLOYEE_LEAVE.LEAVE_TYPE_CODE%TYPE | — | Leave type code to persist. |
| `system_type_code_in` | IN | EMPLOYEE_LEAVE.SYSTEM_TYPE_CODE%TYPE | — | System type code to persist. |
| `begin_date_in` | IN | EMPLOYEE_LEAVE.begin_date%TYPE | — | Beginning date of the leave interval. |
| `end_date_in` | IN | EMPLOYEE_LEAVE.end_date%TYPE | — | Ending date of the leave interval. |
| `UPDATE_USER_in` | IN | EMPLOYEE_LEAVE.UPDATE_USER%TYPE | — | User ID recorded on update. |

### delete_employee_leave

| Field | Value |
| --- | --- |
| **Returns** | A `NUMBER` row count (`SQL%ROWCOUNT`) representing the deleted employee-leave row. |
| **Called by** | `clsEmployeeLeave.BeforeDelete` |

**Description:** Deletes the employee-leave row identified by sequence number.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `employee_leave_SEQ_NUM_in` | IN | EMPLOYEE_LEAVE.employee_leave_SEQ_NUM%TYPE | — | Employee leave sequence number. |

## Business Rules

No additional database-level branching rules were identified in the reviewed `pk82029_titan` employee-leave routines beyond straightforward CRUD behavior.

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| N/A | The package performs direct employee-leave CRUD and audit stamping rather than conditional business-rule branching. | `select_employee_leave`, `Insert_employee_leave`, `update_employee_leave`, and `delete_employee_leave` run with the caller-supplied keys and values. | Return the requested result set or `SQL%ROWCOUNT`, while insert and update stamp `CREATE_DATE` / `UPDATE_DATE` with `SYSDATE`. |

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| OP-011 | Inserts a new `EMPLOYEE_LEAVE` row and stamps create/update audit columns with `SYSDATE`. | `clsEmployeeLeave.BeforeInsert` uses `pk82029_titan.Insert_employee_leave`. |
| OP-012 | Opens a result-set cursor over employee leave rows joined to leave-type descriptions for the requested system type and start date. | `clsEmployeeLeaveCollection.GetEmployeeLeave` uses `pk82029_titan.select_employee_leave`. |
| OP-013 | Updates an `EMPLOYEE_LEAVE` row by sequence number and refreshes audit columns. | `clsEmployeeLeave.BeforeUpdate` uses `pk82029_titan.update_employee_leave`. |
| OP-014 | Deletes an `EMPLOYEE_LEAVE` row by sequence number. | `clsEmployeeLeave.BeforeDelete` uses `pk82029_titan.delete_employee_leave`. |

> These operations are also listed in [operations-processes.md](../operations-processes.md).

## Source Code

<details>
<summary>PL/SQL package specification</summary>

```sql
Function Insert_employee_leave (
      employee_leave_seq_num_in   IN EMPLOYEE_LEAVE.employee_leave_seq_num%TYPE,
      employee_emplid_in          IN EMPLOYEE_LEAVE.employee_emplid%TYPE,
      leave_type_code_in          IN EMPLOYEE_LEAVE.LEAVE_TYPE_CODE%TYPE,
      system_type_code_in         IN EMPLOYEE_LEAVE.SYSTEM_TYPE_CODE%TYPE,
      begin_date_in               IN EMPLOYEE_LEAVE.begin_date%TYPE,
      end_date_in                 IN EMPLOYEE_LEAVE.end_date%TYPE,
      CREATE_USER_in              IN EMPLOYEE_LEAVE.CREATE_USER%TYPE)
      return number;

PROCEDURE select_employee_leave (
      StartDate           IN     DATE,
      SystemTypeCode       IN     VARCHAR2,
      recordset        OUT ref_cursor);

Function update_employee_leave (
      employee_leave_seq_num_in   IN EMPLOYEE_LEAVE.employee_leave_seq_num%TYPE,
      employee_emplid_in          IN EMPLOYEE_LEAVE.employee_emplid%TYPE,
      leave_type_code_in          IN EMPLOYEE_LEAVE.LEAVE_TYPE_CODE%TYPE,
      system_type_code_in         IN EMPLOYEE_LEAVE.SYSTEM_TYPE_CODE%TYPE,
      begin_date_in               IN EMPLOYEE_LEAVE.begin_date%TYPE,
      end_date_in                 IN EMPLOYEE_LEAVE.end_date%TYPE,
      UPDATE_USER_in              IN EMPLOYEE_LEAVE.UPDATE_USER%TYPE)
      return number;

Function delete_employee_leave (
      employee_leave_SEQ_NUM_in IN EMPLOYEE_LEAVE.employee_leave_SEQ_NUM%TYPE)
      return number;
```

</details>

<details>
<summary>PL/SQL package/procedure body</summary>

```sql
PROCEDURE select_employee_leave(
      StartDate      IN DATE,
      SystemTypeCode IN VARCHAR2,
      recordset OUT ref_cursor)
  IS
  BEGIN
    OPEN recordset FOR SELECT employee_leave_seq_num
  AS
    EmployeeLeaveSeqNum,
    employee_emplid
  AS
    EmployeeEmplid,
    el.LEAVE_TYPE_CODE
  AS
    LeaveTypeCode,
    lt.code_descr
  AS
    leave_descr,
    SYSTEM_TYPE_CODE
  AS
    SystemTypeCode,
    begin_date
  AS
    BeginDate,
    end_date
  AS
    EndDate,
    create_date
  AS
    CreateDate,
    create_user
  AS
    CreateUser,
    update_date
  AS
    UpdateDate,
    update_user
  AS
    UpdateUser FROM EMPLOYEE_LEAVE el,
    LEAVE_TYPE_CODE lt WHERE el.LEAVE_TYPE_CODE = lt.code AND el.end_date >= TRUNC (StartDate) AND el.SYSTEM_TYPE_CODE = SystemTypeCode ORDER BY begin_date,
    end_date;
  END select_employee_leave;

FUNCTION Insert_employee_leave(
      employee_leave_seq_num_in IN EMPLOYEE_LEAVE.employee_leave_seq_num%TYPE,
      employee_emplid_in        IN EMPLOYEE_LEAVE.employee_emplid%TYPE,
      leave_type_code_in        IN EMPLOYEE_LEAVE.LEAVE_TYPE_CODE%TYPE,
      system_type_code_in       IN EMPLOYEE_LEAVE.SYSTEM_TYPE_CODE%TYPE,
      begin_date_in             IN EMPLOYEE_LEAVE.begin_date%TYPE,
      end_date_in               IN EMPLOYEE_LEAVE.end_date%TYPE,
      CREATE_USER_in            IN EMPLOYEE_LEAVE.CREATE_USER%TYPE)
    RETURN NUMBER
  IS
  BEGIN
    INSERT
    INTO EMPLOYEE_LEAVE
      (
        employee_leave_seq_num,
        employee_emplid,
        LEAVE_TYPE_CODE,
        SYSTEM_TYPE_CODE,
        begin_date,
        end_date,
        CREATE_DATE,
        CREATE_USER,
        UPDATE_DATE,
        UPDATE_USER
      )
      VALUES
      (
        employee_leave_seq_num_in,
        employee_emplid_in,
        leave_type_code_in,
        system_type_code_in,
        begin_date_in,
        end_date_in,
        SYSDATE,
        CREATE_USER_in,
        SYSDATE,
        CREATE_USER_in
      );
    RETURN sql%rowcount;
  END Insert_employee_leave;

FUNCTION update_employee_leave
    (
      employee_leave_seq_num_in IN EMPLOYEE_LEAVE.employee_leave_seq_num%TYPE,
      employee_emplid_in        IN EMPLOYEE_LEAVE.employee_emplid%TYPE,
      leave_type_code_in        IN EMPLOYEE_LEAVE.LEAVE_TYPE_CODE%TYPE,
      system_type_code_in       IN EMPLOYEE_LEAVE.SYSTEM_TYPE_CODE%TYPE,
      begin_date_in             IN EMPLOYEE_LEAVE.begin_date%TYPE,
      end_date_in               IN EMPLOYEE_LEAVE.end_date%TYPE,
      UPDATE_USER_in            IN EMPLOYEE_LEAVE.UPDATE_USER%TYPE
    )
    RETURN NUMBER
  IS
  BEGIN
    UPDATE EMPLOYEE_LEAVE
    SET employee_leave_seq_num   = employee_leave_seq_num_in,
      employee_emplid            = employee_emplid_in,
      LEAVE_TYPE_CODE            = leave_type_code_in,
      SYSTEM_TYPE_CODE           = system_type_code_in,
      begin_date                 = begin_date_in,
      end_date                   = end_date_in,
      UPDATE_DATE                = SYSDATE,
      UPDATE_USER                = UPDATE_USER_in
    WHERE employee_leave_SEQ_NUM = employee_leave_SEQ_NUM_in;
    RETURN sql%rowcount;
  END update_employee_leave;

FUNCTION delete_employee_leave(
      employee_leave_SEQ_NUM_in IN EMPLOYEE_LEAVE.employee_leave_SEQ_NUM%TYPE)
    RETURN NUMBER
  IS
  BEGIN
    DELETE
    FROM EMPLOYEE_LEAVE
    WHERE employee_leave_SEQ_NUM = employee_leave_SEQ_NUM_in;
    RETURN sql%rowcount;
  END delete_employee_leave;
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- `Business\SAIF.Business\clsEmployeeLeave.vb`
- `Business\SAIF.Business\clsEmployeeLeaveCollection.vb`