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.

Source Code¶

PL/SQL package specification

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;

PL/SQL package/procedure body

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;

Sources¶

Business\SAIF.Business\clsEmployeeLeave.vb
Business\SAIF.Business\clsEmployeeLeaveCollection.vb

pk82029_titan¶

Overview¶

Purpose¶

Procedures / Methods¶

Insert_employee_leave¶

Parameters¶

select_employee_leave¶

Parameters¶

update_employee_leave¶

Parameters¶

delete_employee_leave¶

Parameters¶

Business Rules¶

Operations¶

Source Code¶

Related Documentation¶

Sources¶