Puerto Rico
Department of Education
PHASE 6-SIE Employees_SIE_notin_STAFF
Auditing 11-78-2 Scripts
Auditing 11-78-2 Scripts
Project Auditing 11-78-2 Scripts
Version Information
Version number 1.0.0
Draft/Final as of 15/March/2013
Printed on 20 March 2013
Author Ricardo Cortés Huertas
Owner Vicente Graulau Rosario
Last Update By Ricardo Cortés Huertas
Document Information
Document sourceThis document is maintained as an online document. Contact the author for the latest version. Revision history
Version Number Date Summary of Changes Revision Marks
1.0 15/Mar/2013 First version YES
Reviews
This document has been reviewed internally by the following people.
Name Date of
Review
Vicente Graulau Rosario/PRDE 15/Mar/2013
Approvals
This document has been approved by the following people. The signed approval forms are filed in the Project Control Book.
Name
Function Date of
Approval Signature
Distribution
This document has been distributed to:
Name Function
Vicente Graulau Rosario PRDE SIE Administrator Ricardo Cortés Huertas Intellutions Consultant http://edusharepoint/sie Sharepoint site
Table of Contents Page 1 Introduction ... 3 1.1 Objectives ... 4 2 Scripts Section ... 4 2.1 SCRIPT Script_Same_Name_Different_SSN_SIE_VS_STAFF_step1 ... 4
Auditing 11-78-2 Scripts
1 Introduction
One of the purposes of the Audting 11-78-2 Scripts Project is to provide scripts for the generation of reports based in the active employees in the SIE database but not in the STAFF Human Resources database.
1.1 Objectives
The objective of this document is to provide scripts documentation to be used in order to generates reports based in the active employees in the SIE database but not in the STAFF Human Resources database because the social security number does not match or they do not appears active in the Human Resources database. The instructions to be followed are also included. This solution is based on an auditing 11-78-2 done against the SIE production database called PMAXIO.
2 Scripts Section
The following sections describe the scripts used in order to generate reports based on the active employees in the SIE database but not in the STAFF Human Resources.
2.1 SCRIPT Script_Same_Name_Different_SSN_SIE_VS_STAFF_step1
-- Prepared by: Ricardo Cortés -- on: March 13, 2013
-- script: PHASE6 Script_Same_Name_Different_SSN_SIE_VS_STAFF_step1
---- Script to select active employees (staff_ids)in the SIE systems
with different social security numbers but same names in the STAFF Human -- Resources
-- database.
----
-- This script needs to be executed under prod schema --
/* Formatted on 2013/03/12 12:03 (Formatter Plus v4.8.7) */
SELECT h.description AS "Region", i.description AS "Distrito",
g.ext_school_number AS "Num.de Escuela",
g.school_name AS "Nom. de Esc.", a.staff_id AS "Numero Interno SIE", a.ext_staff_number AS "Numero Empleado",
d.sec_user_id AS "Numero de usuario", a.last_name AS "Apellido", a.first_name AS "Nombre", x."first_name" AS "Nombre en RH",
a.middle_name AS "Inicial", x."middle_initial" AS "Inicial en RH", x."paternal_last_name"
|| ' '
a.date_of_birth AS "Fecha de Nacimiento", c.sec_role_id AS "Rol ID", e.role_name AS "Nom.Role", d.login_name AS "Nom. Usuario",
l.last_login_time AS "Fecha ultimo login",
l.inactive_base_date AS "Fecha base para inactivacion", d.status AS "Status",
d.session_access_counter AS "Intentos fallidos",
d.create_dt AS "Fecha de creacion"
--FROM sy_staff a, sec_group b, sec_user_group_role c, sec_user d, sec_role e, sec_group_xref f, sy_school g, sy_district_zone h, sy_campus i, sec_user_xref k, sec_user_access_log l, prde_human_resources.vw_active_employees x
WHERE b.sec_group_id = c.sec_group_id(+)
AND d.status = 1
AND k.party_type_cd = 1
AND d.sec_user_id = k.sec_user_id(+) AND c.sec_user_id = d.sec_user_id(+) AND d.sec_user_id = l.sec_user_id(+) AND b.sec_group_id = f.sec_group_id AND f.school_id = g.school_id(+) AND d.party_id = a.staff_id
AND g.district_zone_id = h.district_zone_id(+) AND g.campus_id = i.campus_id(+)
AND ( a.ssn IS NULL OR (a.ssn != x."ssn" OR (a.ssn IS NULL AND a.ssn = x."ssn")) ) AND ( TRANSLATE (UPPER (a.first_name), 'aeiouáéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ', 'AEIOUAEIOUAEIOUAOAEIOOAEIOUCAEIOUAEIOUAOAEIOUAEIOUC' ) = x."first_name" AND TRANSLATE (UPPER (a.last_name), 'aeiouáéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ', 'AEIOUAEIOUAEIOUAOAEIOOAEIOUCAEIOUAEIOUAOAEIOUAEIOUC' ) = (x."paternal_last_name" || ' ' || x."maternal_last_name" ) AND TRANSLATE (UPPER (a.middle_name), 'aeiouáéíóúàèìòùãõâêîôôäëïöüçÁÉÍÓÚÀÈÌÒÙÃÕÂÊÎÔÛÄËÏÖÜÇ', 'AEIOUAEIOUAEIOUAOAEIOOAEIOUCAEIOUAEIOUAOAEIOUAEIOUC' ) = (x."middle_initial")
OR (a.middle_name IS NULL AND (a.middle_name = x."middle_initial") )
)
AND a.is_active_ind = 1
AND x."school_code" = g.ext_school_number
