Personnel Data Tracking System

Introduction

The necessity to systematize procedures is needed by each unit in UPM, for efficient provision of services and for the release of complete, accurate information, data statistics and report.

Hence, automating the processes involved is viewed as a solution, especially in processing personnel information. The Human Resource Development Office(HRDO), mandated to handle personnel transactions and services, should be equipped with an electronic personnel data information system, for proper documentation and accurate reporting.

Objectives

To design an electronic database to complement the personal information needs of the University
To develop the Personnel Data Tracking System software using the evolutionary prototyping method
To deploy the necessary equipment needed during systems development and actual use of the system later

Task Force for the Personnel Data Tracking System

Due to the urgency of personnel data information processing, Chancellor Marita V.T. Reyes created a Task Force for the Personnel Data Tracking System, mandated to conduct a more thorough systems and requirements analysis as input in designing an effective PDTS. The task force is chaired by Dr. Emilie G. Flores with Prof. Ariel Betan of IMS as Co-Chair. The members are:

Vice Chancellor Arlene A. Samaniego, MD (OVCA)
Ms. Menchie Ramirez - Budget Office
Ms. Jeanaluz Parnacio - Project Coordinator (IMS)
Mr. Eric Pareja - Database System Administrator (IMS)
Ms. Blenda Atienza - Personnel Office
Ms. Virginia Lumabas - Personnel Office
Mr. Rolando Acantilado - Accounting Office
Aldee Paul Adique - Programmer (IMS)
Jose Enrique Bulatao - Programmer (IMS)
Rodwin Magdaraog - Programmer (IMS)

Current Status

January 2005

Coordinated with the PDTS Project Manager for the discussion and overview of the proposed system.
Received and familiarized the manual of the existing system.
Coordinated with the UP IMS System Administration in conjunction for the creation of a temporary data server.
Created the temporary server in batis.upm.edu.ph
Created the initial forms based from the existing Personal Data System (PDS).
Presented the initial forms to Hiring and Appointment section of Human Resource Development Office(HRDO).
Interviewed Hiring and Appointment for improvements and points of importance of the current system.
Obtained a copy of the existing database in MS Access format.
Redesigned and improved the existing database.
Extracted the MS Access database and converted it to SQL which is to be ported to PostgreSQL in the future.

February 2005

Isolated and pointed out discrepancies in the current database being used by the Human Resource Development Office(HRDO).
Completed and implemented an update module for the Appointments section of the Human Resource Development Office(HRDO) for the correction of entries with no employee number listed.
Interviewed the Leaves and Benefits section of the Human Resource Development Office(HRDO)
Created a Report Generating module for the Appointments Section to help in statistical reports.
Distributed account usernames for the appointments section employees to start in the update of data discrepancies.
Asked the UP IMS System Administrator to reassign the current server batis.upm.edu.ph/~pdts to pdts.upm.edu.ph for easier access.
Completed initial forms for the Leaves and Benefits Section.

Database Structure

create table personal(
	empno 		varchar(9) PRIMARY KEY,
	lname 		varchar(50),
	fname 		varchar(35),
	mname 		varchar(35),
	mdname 		varchar(35),
	sexcode 	int,
	civstatcode 	int,
	citizenshipcode int,
	bloodtypecode 	int,
	height 		varchar(5),
	weight 		varchar(7),
	religioncode 	int,
	bdate 		varchar(30),
	bplacecode 	int,
	permanentadd 	text,
	telno 		varchar(30),
	gsisno 		varchar(30),
	sssno 		varchar(30),
	tin 		varchar(30),
	philhealthno 	varchar(30),
	pagibigno 	varchar(30)
	);
	
create table sex(
	sexcode int PRIMARY KEY,
	sextext varchar(10)
);

create table civstat(
	civstatcode int PRIMARY KEY,
	civstattext varchar(15)
);

create table citizenship(
	citizenshipcode int PRIMARY KEY,
	citizenshiptext varchar(20)
);

create table bloodtype(
	bloodtypecode int PRIMARY KEY,
	bloodtypetext varchar(5)
);

create table religion(
	religioncode int PRIMARY KEY,
	religiontext varchar(30)
);

create table family(
	empno varchar(9) PRIMARY KEY,
	fathern varchar(50),
	foccup varchar(50),
	mothern varchar(50),
	moccup varchar(50),
	spousen varchar(50),
	soccup varchar(50),
	sadd varchar(50),
	sbplace varchar(50)
);

create table children(
	childrenid  serial PRIMARY KEY,
	empno varchar(9),
	childname varchar(30),
	childbdate varchar(30)
);

create table education(
	educationid  serial PRIMARY KEY,
	empno varchar(9),
	school varchar(30),
	degree varchar(30),
	date varchar(15),
	levelcode int
);

create table leveleducation(
	levelcode int PRIMARY KEY,
	leveltext varchar(20)
);

create table honors(
	honorid  serial PRIMARY KEY,
	empno varchar(9),
	levelcode int,
	honortext varchar(50)
);

create table grad_postgrad(
	grad_postgradid serial PRIMARY KEY,
	empno varchar(9),
	school varchar(30),
	degree varchar(30),
	date varchar(15),
	typecode int
);

create table grad_postgradhonors(
	honorid  serial PRIMARY KEY,
	empno varchar(9),
	honortext varchar(50)
);

create table typecode(
	typecode int PRIMARY KEY,
	typetext varchar(15)
);

create table exam(
	examid  serial PRIMARY KEY,
	empno varchar(9),
	exam varchar(30),
	date varchar(20),
	rating varchar(10),
	place varchar(30),
	examtypecode int
);

create table examtype(
	examtypecode int PRIMARY KEY,
	examtext varchar(15)
);

create table tss(
	tssid  serial PRIMARY KEY,
	empno varchar(9),
	tsscode int,
	name varchar(30),
	period varchar(30),
	sponsor varchar(30),
	speaker varchar(30),
	place varchar(30)
);

create table tsstype(
	tsscode int PRIMARY KEY,
	tsstext varchar(20)
);

create table employmenthistory(
	employmenthistoryid  serial PRIMARY KEY,
	empno varchar(9),
	company varchar(30),
	position varchar(30),
	lengthofservice int,
	dateresigned varchar(20),
	reasonforleaving text
);

create table skills(
	skillsid serial PRIMARY KEY,
	empno varchar(9),
	infotext text,
	skillscode int
);

create table skillscode(
	skillscode int PRIMARY KEY,
	skillstext varchar(20)
);


create table leaves(
	empno varchar(9) PRIMARY KEY,
	orig_appt varchar(20),
	vacation_leave double precision,
	sick_leave double precision,
	special_leave double precision,
	last_update_year integer,
	last_update_month integer,
	mandatory double precision
);

create table leave_history(
	historyid SERIAL,
	empno  varchar(9),
	month_update integer,
	year_update integer,
	leave_date_start date,
	leave_date_end date,
	num_of_days double precision,
	type_of_leave varchar(20)
);

create table leave_types(
	leaveid int PRIMARY KEY,
	leave_text varchar (50)
);

Contact Us

Ms. Jeanaluz Parnacio
Mr. Eric Pareja

Information Management Service Office
UNIVERSITY OF THE PHILIPPINES MANILA(The Health Sciences Center)
Information Technology Complex, 3/F Dietary Bldg. PGH Compound, Taft Avenue, Manila,
Tel/Fax No. 526-2265 l E-mail: IMS@mail.upm.edu.ph l Web: http://IMS.upm.edu.ph

the programmers: apladique@mail.upm.edu.ph

Additional Notes

-empty-

/PDTS/ - Personnel Data Tracking System