Oracle Sql

MIN ZHAN

214-***-**** • *********@*****.***

SUMMARY

Over seven years of experience in IT, with around five years of Oracle HRMS ( HR,

Payroll, OTL, OAB, and Compensation Workbench) implementation experience

across project cycles.

Experience with Oracle 11i HRMS Conversions and Interfaces, Data Loads using

APIs and Data Pump, Validations, API User Hooks, API Error Handling, fast

formula, and concurrent programs.

Experience in designing, developing, testing and supporting RDBMS based applications, which

involves programming with Oracle tools such as Forms 6i, Reports 6i, Discoverer 4i/10g,

Workflow Builder 2.6, XML Publisher 10.1, WEB ADI and PL/SQL.

Functional knowledge of configuring and setting up Oracle HRMS Applications.

Strong analytical, diagnostic and troubleshooting skills and ability to work as an individual and in

a dynamic, team-oriented environment.

SKILLS SUMMARY

Oracle Applications: Oracle HRMS 11i/10.x (HR/Payroll/OTL/ OAB/ CWB)

Operating Systems: Windows 98/2000/NT/XP, Linux, Sun Solaris

Languages: PL/SQL, Java, JSP, SQL, C, C++, PHP, HTML

Web Technologies : J2EE, Servlets, JSP, JDBC

Database: Oracle 10g/9i/8i, MS SQL 2000, MS-Access, MySQL

GUI Tools: Developer 2000, Reports 6i/9i, Discoverer 4i/10g, Crystal

Reports, JDeveloper

Utilities/Tools: SQL* Loader, UTL_FILE, Work Flow Builder, Toad, SQL

Navigator, Developer 2000, Dreamweaver Ultradev, Flash 4.0,

Rational Rose

Web/Application Servers: Apache Web server, Apache Tomcat, IIS

1

PROFESSIONAL EXPERIENCE

Client: ATCO Group of Companies, Edmonton, AB

Oracle HRMS Techno-Functional Consultant, 10/2009 –

Modules: HR, Payroll, OAB and OTL

Environment: Oracle Applications 11.5.10, TOAD, SQL*PLUS, PL/SQL, Reports6i,

Forms 6i, Discoverer 10g

Key Contributions:

• Worked as a techno-functional consultant responsible for fast formula development

of HRMS conversion and quality control of reports testing on HR, Payroll, OTL and

OAB.

Developed payroll fast formulas for pension calculation such as ASI DB Hourly EE, ASI DB

Hourly ER, ASI DB Salary EE, ASI DB Salary ER, ASI DC Hourly EE, ASI DC Hourly ER, ASI

DC Salary EE, ASI DC Salary ER, DBP PEN EE, DBP PEN ER, FC DC EE, FC DC ER, etc.

Developed PTO fast formulas for Vacation, Flex and Personal PTO plans such as Standard 37_5

Vacation, Standard 40 Vacation, PW 37_5 Vacation, PW 40 Vacation, Personal 3 Day 40,

Personal 2 Day 40, IT Personal 3 Day, ATCO 37_5 Flex, FC Moose Jaw 40800, YECL 40

Vacation, NUHR 40 Vacation, NUY 40 Vacation, etc.

Tested HR, Payroll, OAB and OTL reports such as Pension Membership Report, Retirement

Eligibility Report, Headcount Report, Vacation Payout Band Report, Vacancy FTE Count Report,

Monthly Absence Report, WCB Claim summary Report, STD Claim Summary Report,

Employee Vacation Report, etc.

Client: Jacobs Engineering, Pasadena, CA

Oracle HRMS Technical Consultant, 01/2008 – 07/2009

Modules: HR, OAB, and Compensation Workbench

Environment: Oracle Applications 11.5.10, TOAD, SQL*PLUS, PL/SQL, Reports6i,

Forms 6i, Workflow Builder 2.6

Key Contributions:

• Worked as a Technical consultant responsible for GLOBAL HRIS conversion and

daily support of Oracle Applications 11i (HR, OAB and Compensation Workbench).

• Prepared the technical specification documents (MD70) for conversion and

interfaces.

Prepared unit test documents and user procedures for interfaces.

• Developed the mapping program for conversion.

• Developed and involved in conversion programs such as, Assignment Criteria

Details/Assignment Details/ Assignment Budget /Visa Details in EIT/Timecard

Approver in SIT/ PTO plan and Old Balance/Termination Details/ Address Details/

Element entries/Salary Details/ Grade, to validate the data in line table, transfer data

from line table to staging table, validate data in staging table and insert into Oracle

Apps table using data pump and standard API.

• Developed the PL/SQL procedure for Global Job synchronization using

HR_JOB_API.CREATE_JOB and HR_JOB_API.UPDATE_JOB.

Developed the PL/SQL procedures for United Kingdom OAB outbound interfaces: AIG Travel

2

Insurance, BUPA, NUFFIELD Health Screening, CARE4, L&G (CIC and ALA), EQUITINI,

ALLIANZ, HSA (PMI + CASH) and NDP Dental Plan etc.

Developed outbound interfaces for Canada such as Export Staff PTO Data to JD Edwards, Export

Staff Override to JD Edwards, and Healthcare Spending account to GWL.

• Customized the custom.pll for United Kingdom rehire warning message, and Canada

Payroll File Number in Descriptive Flex Field etc.

• Developed workflows for Canada Service Percentage change notification,

Netherlands Holiday buy and sell FYI notification, and US PTO Reconciliation

notification etc.

• Developed new OAB fast formulas for Netherlands and Belgium, such as pension

rate calculation rule, postelection maximum coverage rule, and rate upper limit rule

etc.

• Created the trigger to catch data before delete and modified triggers .

• Resolved daily issues for the modules involved, such as customizing US OAB fast

formulas and PTO carryover fast formulas for US and Mexico, and creating scripts to

correct data.

Developed US OAB UHC FSA Annual outbound interface.

• Developed the PL/SQL procedures for Craft PTO Vacation Adjusted Service Date,

Craft vacation Date and Craft DOW PTO Plan enrollment, PTO carry over hours, US

PTO 100 percent PTO sell process, US PTO four times cash out process, US PTO

transfer process, Craft PTO Negative Balance report, and PTO Audit report etc.

• Developed the PL/SQL procedure for Business Partner Upload/Changes to process

business partner in the Additional Assignment Detail Descriptive Flex Field using

HR_ASSIGNMENT_API.UPDATE_EMP_ASG

• Customized the PL/SQL procedure BEN_CWB_CUSTOM_PERSON_PKG and

created fast formulas for compensation workbench

• Developed outbound and inbound interfaces for employee qualifications, reviews and

performance (external employment history, education history, candidate information,

performance history, etc.)

Client: Emergency Medical Services Corporation, Greenwood Village, CO

Oracle HRMS Technical Consultant, 11/2007 – 01/2008

Modules: Payroll

Environment: Oracle Applications 11.5.10, TOAD, SQL*PLUS, PL/SQL

Key Contributions:

• Worked as a technical consultant to make payroll balance adjustment.

• Developed the concurrent program to extract employees who have balance in

different GRE and store the data in the custom table.

Developed the script to fix the incorrect state tax issue.

• Developed the PL/SQL store procedure to extract all employees who have incorrect

adjust elements and calculate the exactly tax deduction amount affected .

Created custom tables and developed the PL/SQL store procedure using

PAY_BALANCE_ADJUSTMENT_API to create adjustment from the custom tables to fix the

incorrect element balance issue.

Developed the PL/SQL store procedure using PAY_BALANCE_ADJUSTMENT_API to move

balance for GRE changing.

3

Client: City of Chandler, Chandler, AZ

Oracle HRMS Techno-Functional Consultant, 04/2007 – 09/2007

Modules: Payroll and HR

Environment: Oracle Applications 11.5.10, TOAD, SQL*PLUS, PL/SQL, Reports6i,

Forms 6i

Key Contributions:

• Created elements, fast formulas, global values and new balances for the fiscal year

2007-08 Oracle Payroll Additions /Changes

Created Special Information Type such as Special Merit Pay, Vacation Time Payout and

Compensatory Time Payout in the Key flex field.

• Developed outbound interface to extract data from Oracle system to IntelliTime

system for processing timecard information.

Developed PL/SQL store procedures for accrual balance report.

• Developed AIM standard documents such as business requirement documents of

‘BR100_Payroll_City_of_Chandler’ and ‘BR100_HR_City_of_Chandler’, module

design documents of ‘MD.050 IntelliTime Outbound Interface Data Mapping’,

MD50 Functional Design Specification and MD70 Technical Design Specification

documents for the Reports.

• Trained technical staff with the topics such as Create Elements, Fast Formula, Batch

Element Entry, Absence Management and PTO Accrual Plan, Use API to Automate

Data Entry for Oracle Payroll, etc.

Resolved day to day technical and functional issues for the Payroll/HR such as incorrect

information element data, incorrect report name and incorrect total earning hours on the

paycheck, incorrect data in the vacancy report.

• Performance Tuning of PL/SQL procedure of payroll data procedure set which runs

every pay period to get all payroll information.

• Modified view balance custom form to fix incorrect hourly rate.

Client: City of Memphis, Memphis, TN

Oracle HRMS Techno-Functional Consultant, 07/2005 – 04/2007

Modules: OTL, Payroll, HR and OAB

Environment: Oracle Applications 11.5.9/ 11.5.10, TOAD, SQL*PLUS, PL/SQL,

Reports6i, Forms6i, 4i/10g

Key Contributions:

• Worked as a Techno-Functional consultant responsible for various tasks involved in

implementation and Support of Oracle Applications 11i (OTL, PAYROLL, HR and

OAB).

• Prepared the conversion documents for legacy layout format.

Created data control files and loaded legacy data into staging tables.

• Developed PTO and OTL conversion programs to validate the rows in staging tables

and insert into Oracle Apps table by using standard API such as HR_ENTRY_API,

HR_SIT_API, HXT_AAI, HXC_TIMEKEEPER_GROUP_API,

HXC_TK_GROUP_QUERY_API, and HXC_TK_GRP_QUERY_CRITERIA_API.

• Involved in Employee/Position/Assignment/Salary/ Payment Methods conversion.

• Performed customization, system testing and migration of Oracle Applications.

Resolved technical and functional daily issues for the modules involved.

4

Prepared the Technical Specification Documents for the Interfaces/Reports.

• Developed PL/SQL code and store procedures for various reports related to

HR/Payroll/OTL/OAB by using Oracle Reports 6i and Discoverer 4i/10g.

HR Reports: MPD Employee Information, MPD Position Information Report,

MPD Temporary Assignment Listing, Employee PTO Hours Report etc.

Payroll Reports: CMEM Payroll Exception Report, MPD Holiday Pay Report,

MPD Overtime Summary Report, Fire FLSA Report, MPD Weekly Rollcall List,

MPD Sick Occurrence List, and MPD Hazard Pay/Out of Class Pay report etc.

OTL Reports: OTL Time Entry Validation Report, Time Entries not transfer to BEE report,

Missing Time Summary/Detail report, Time Entries not transfer to Element Entries report.

OAB reports: Census for Life Insurance Renewal, COBRA Life Event Status Report, COBRA

Detected Life Event Report, etc.

• Customized the custom.pll for OTL standard form to resolve security issue

Developed PTO Bonus Carryover store procedure for PTO Year end.

• Customized PTO Bonus/Sick/Vacation/Personal Accrual fast formulas.

Developed OTL Time entry rules such as Regular Pay Vs TimeEntryWages to prevent

timekeepers from entering Time Entry Wages for Regular Employees.

Comp Time Balance 480 rule to prevent employees to earn CompTime more than 480

hours; HolidayPay Vs Temps rule to prevent timekeepers from entering Holiday Element

for Temps.

• Developed various functions for payroll fast formulas such as get_cwa_rate_code

function to use in union_dues_cwa_payroll_table fast formula.

• Defined new shift, rotation plans for 44 hours employees in OTL, developed payroll

function to calculate correct FLSA and modified payroll fast formula such as

REGULAR_PAY_FLAT_AMOUNT.

• Customized pension fast formulas for new pension plans.

EDUCATION

• Master of Science in Computer Information Systems

University Of Houston – Clear Lake, Houston, Texas

5

Contact this candidate