Sr. Oracle PL/SQL - ETL(Informatica Developer)

PROFESSIONAL SUMMARY:

Information Technology professional experienced in various project management methodologies (including Waterfall, Agile and Scrum) to achieve corporate objectives, streamline business processes and increase bottom-line results.

Having 9.0 years of experience in various domains (comprising Retail, Banking, Mortgage, Telecommunication, and Manufacturing) to successfully design, develop, deploy and support software applications/processes.

Worked in all aspects of software development life cycle i.e. requirement analysis, design, development, testing, deployment and maintenance.

Experienced in analyzing functional requirements, interacting with clients/users and preparing relevant technical design documentation.

Proven experience in design, development of relational database applications, data migration processes and ETL processes.

Well-versed in creating oracle stored procedures, functions, packages, triggers, materialized views, and other database objects using sql, pl/sql.

Experienced in troubleshooting and Performance tuning ETL/Transaction processes using explain plan, by creating indexes, sql trace/tkprof, and hints.

Experienced in creating Informatica workflows, designing mappings, monitoring workflow executions as per the business needs.

Experienced in creating unix shell scripts to automate the business processes and scheduling using Control-M.

Created business intelligence reports using crystal reports and oracle reports 6i as per the requirements and automated the execution as per the specified interval (daily, weekly, or monthly).

Experienced in unit testing, user acceptance testing and creating test cases and resolving the issues by coordinating with the business analysts and end users.

Good team player, quick learner, target oriented individual with excellent communication and interpersonal skills, also possess the ability to work independently.

TECHNICAL SKILLS:

Operating Systems

AIX 5.3, Windows 7, HP-UX 11i/10, Solaris 10/8/, RHEL 5.0

Programming Languages

SQL, PL/SQL, Unix Shell Scripting

Databases

Oracle 11g/10g, SQL Server 2008 R2

Development Tools

TOAD 10.6.1,SQL Navigator 5.0, SQL Developer.

Reporting Tools

Business Objects, Oracle Reports 6.0.

ETL Tools

Informatica 9.1/9.6.1

ERP Software

Oracle E-Business Suite 11.5.10.

Productivity Tools

Microsoft Office 2010(Word, Excel, Power Point), Ultra Edit, Notepad++

PROFESSIONAL EXPERIENCE:

Client: Carters, Atlanta, GA Sep`13- Till Date

Department: Finance and HR Systems

Sr. Oracle PL/SQL – ETL(Informatica) Developer

Responsibilities:

Designed, Developed and automated the process of loading the currency conversion rates from external source (OANDA) on a daily basis and transmitted to the downstream Applications.

Designed, Developed interfaces to post the ADP General Ledger, E-commerce General Ledger transactions into the finance application (INFOR) automatically for every financial billing cycle and created html report to display the invalid GL Accounts as per the business validation criteria.

Designed, developed and implemented the reconciliation process for the point of sales (POS) Credit Card/Cash transactions, adjustments using Recon NET and automated the process using PL/SQL, Unix Shell Script and Control-M.

Designed and implemented the bank reconciliation process to compare the cheques cleared by the bank with the accounts payable team using oracle pl/sql and automated the process using shell script and scheduled them using Control-M job.

Performance tuned the General Ledger Data Processing using PL/SQL and reduced the Processing time from 11 hours to 3 hours by range partitioning the base tables. Used bulk collect and FORALL programming features to process the data with effective performance and fixed the bottle necks for the existing processes by creating appropriate indexes.

Modified Informatica Workflows by changing the base queries, re-mapped the Source Fields to the New Fields, deleted the unwanted Fields from the Mappings, Modified the Server Connections, and tested the workflow as part of Upgrade of the Sales Audit Application (APTOS).

Developed Informatica mappings, enabling the ETL process for large volumes of data into target tables

Identified the data discrepancies in source systems and significantly reduced annual commodity costs.

Used Informatica designer for designing mappings and mapplets to extract data from Oracle sources.

Used Various Transformations like Expression, Filter, Router, Joiner, Look Up, and Update strategy, Source Qualifier in many mappings.

Invoked Unix scrips from Informatica command task for achieving file manipulations.

Extensively worked on scheduling and DWH batch processing of workflows through Control-M.

Created Business Objects Reports and modified existing ones as per the business requirements and automated them to execute as per the specified interval (Daily, Weekly, and monthly).

Created Adhoc Excel Reports (Related to Accounts Payable and Accounts Receivables) using Oracle SQL and PL/SQL as per the Business Requirements.

Created Change Requests and got approvals from the Managers, Directors by performing the Unit Testing and User Acceptance Testing (UAT) for all the applications, documented the Test Case Scenarios and assigned the tasks to the concerned groups to get the code deployed in production.

Interacted with the Cross Functional teams such as the Migration team, Integration team, Business Analysts, DBA Team, Unix Admin Team, IT Security Team, to resolve miscellaneous tasks like FTP,SFTP set up(with external clients), setting up business account for rendering services of external clients, creating database links to extract data from external servers etc.

Developed Migration Scripts to migrate the data from Legacy Finance Applications (INFOR, COMPUTRON) into SAP.

Provided extensive production support by monitoring, managing, communicating the critical issues to the appropriate user group and fixing them with approval from the business after testing.

Environment: Infor, Computron, Jesta, Recon NET, AIX 6.3, Oracle 11g/10g, TOAD 10.3, SQL Server 2008 R2, Informatica 9.1/9.6.1, Control-M, Crystal Reports, SAP.

Client: Wells Fargo, Charlotte, NC Nov`12 – May`13

Department: Finance IT

Sr. Oracle PL/SQL –ETL(Datastage) Developer

Description:

General ledger maintenance application is used to process requests and approval for adding, closing and updating Accounting Units, with the Exception of moving them in the hierarchy. GLM is also used to open, change and close accounts, account hierarchy nodes, in addition to maintaining combinations of existing company/account/subaccount segments.

Responsibilities:

Participated in the walk through the existing application code to understand the business process flow.

Created/documented test cases for the existing application to understand the business process, data flow and captured all the results into a table.

Converted the functional requirements into the technical requirements and created a technical design document.

Created stored procedures to extract the data from source table, transformed as per the business logic and loaded into the target table.

Created stored functions to perform calculations on the raw data and accessed through procedures while processing the data as per the business logic.

Created Packages comprising of stored procedures and functions as per business requirements to process the data requests from the front end (Java).

Performance tuned sql queries using explain plan, indexes, and tweaked the order of the filter conditions to reduce the execution time.

Created reports using analytical functions like RANK, LEAD/LAG, AVG, SUM, MAX and MIN as per the business requirements.

Unit tested the code in the development environment and deployed them in the UAT Environments.

Environment: UNIX, Oracle 11g, PL/SQL, TOAD 10.0, Autosys, Datastage, SVN, Share-Point.

Client: JP Morgan Chase, Lewisville, TX May`11 - Oct`12

Sr. Oracle PL/SQL – ETL(Informatica) Developer

Description:

Doc-data/Doc-Engine is part of the default IT group dealing with mortgage defaults, loan modifications and government incentives to avoid any foreclosures. The primary role/responsibility of doc-engine was to generate the documents/letters pertaining to loan modification, trial packages, final packages, government approval packages and denial packages by satisfying the business rules of chase.

Responsibilities:

Involved in re-architecture of the existing data model to drive the existing application at the meta data level.

Created the ER Diagram by reverse engineering of the database objects using toad data modeler.

Analyzed the functional requirements and prepared the technical design document for various change requests.

Translated business processes into Informatica mappings for building data marts.

Developed code to extract, transform, and load (ETL) data from inbound flat files and various databases into various outbound files involving complex business logic using Informatica Designer/Power Centre.

Used Informatica Power Center for conversion of the data, transforming, reconciling the different data types and delivering that data to the downstream system decreasing the risk and also minimizing the errors associated.

Created stored procedures, functions and packages as per the business requirements and used them to populate the data into the master table and generated XML out of it which was the source file for downstream application.

Used bulk collect and FORALL programming features to process the data with effective performance.

Created row level triggers to auto-number the data while inserting, to capture the historical data into archive tables during any insert/update operations on the tables.

Created complex views and performance tuned them which were the sources to the downstream applications.

Used analytical functions extensively like XML ELEMENT, STRAGG, and RANK to generate adhoc reports as per the business requirements.

Created shell scripts to automate the process of generating the XML's after successful verification by the business analysts.

Created Implementation plans to deploy the code in database in UAT and production environments.

Coordinated with QA and UAT teams to test the modifications made to the existing application, and resolved the defects rose in HPQC tool.

Provided production support to resolve issues and address the unique needs of the business users.

Environment: Unix, Oracle 11g, PL/SQL, HPQC 10.0, TOAD 10.0, Informatica 9.1, SVN, SharePoint.

Client: AT&T, Atlanta, GA Nov`10-April`11

Sr. Oracle Developer/Release Coordinator

Description:

Session Based Pricing (SBP) Application supports customers in creating a new profile or use an existing profile to purchase online browsing sessions. Customers use their AT&T member id and password to log into the application and view/update their profile and order data plans as per their requirements.

Responsibilities:

Application monitoring using introscope to resolve the alerts and correlating it to the impact of business and customers.

Created and deployed the SQL Scripts in production as per the adhoc business requirements.

Created an automated process to export the database objects from one schema to the other to in building a production like test environment.

Provided on-call support and created Trouble Tickets (AOTS TT’s) for Production issues of the application and coordinated with teams which were being impacted by the issue to resolve it.

Created change requests (AOTS CR’s) as per business requirements and coordinated with the teams for successful deployment of the Release.

Triaged calls to find out the root cause of the issue and generated the reports of impact to the applications to the higher management.

Was responsible for coordinating on-site/offshore for Release Deployments and Knowledge Transfer.

Environment: Solaris, UNIX, Oracle 10g, Introscope, AOTS, SharePoint, TOAD, SQL, PL/SQL, Web logic.

Client: Verizon Telecom, Tampa, FL Feb`10 – Oct`10

Mid-Level Oracle PL/SQL Developer

Description:

Product Data Catalogue (PCAT) is a part of the ordering flow system where the product data is maintained after it is designed by the product line managers in the VPF(Verizon Product Factory, A GUI based Tool) and data will passed over to the related down streams systems for marketing the products as per their validity. The corresponding billing data will also be passed over to the respective Billing systems.

Responsibilities:

Participated in code walk through of the existing applications and review sessions with development team.

Involved in the design calls to understand the new business requirements.

Created E-R diagram by reverse engineering the existing table structures, enhanced it with new tables to support the business requirements of the application.

Developed ETL process using stored procedures, functions and packages to migrate the data from SQL Server to Oracle Database.

Performance tuned sql queries and pl/sql code for the better performance using explain plans and by creating the indexes.

Used export, import and oracle data pump to replicate the schemas for creating test environment.

Involved in testing the custom applications, created test plan, documented the test cases and coordinated with the business and the development teams in resolving the issues.

Provided extensive production support for applications and resolved the bug fixes by coordinating with the business users.

Environment: Solaris, Oracle 10g, SQL Server 2005, TOAD, SQL Navigator, Clear case, Erwin.

Client: Elavon Inc, Atlanta, GA May`09 – Jan`10

Mid-Level Oracle PL/SQL Developer

Description:

Elavon delivers reliable and secure payment solutions to help businesses succeed in an increasingly complex global marketplace by serving as a single source for payment processing worldwide. Visa authorization fees is about the service charge levied by the VISA association to process the merchant transactions based on the volume and type of transactions per billing cycle.

Responsibilities:

Participated in code walk through and review sessions with development team.

Involved in scoping requirements, preparing technical specification documents by interacting with business analysts and end-users.

Created source to target mappings to load the data from data files into dimension tables applying business logic.

Extensively used oracle external tables to read data from various file formats like EBCDIC, .CSV, flat files and load it into database tables.

Developed stored procedures, functions, packages and triggers using PL/SQL to populate the data into various application tables.

Used collections with bulk collect for accessing complex data resulted from joining of large number of tables to extract data from data warehouse.

Used exception handling extensively for the ease of debugging and displaying the error messages.

Developed adhoc reports using cognos to display the number of transactions of a given merchant on weekly, monthly and quarterly basis.

Used oracle data pump to export some tables from one schema to the other for replicating the data in order to test the application.

Environment: Unix, Oracle 11g, Data stage, Cognos, SQL Navigator, TOAD, Clear case, Ultra Edit.

Client: Toppan Photo Masks, Round Rock, TX Apr`08 – Oct`08

Jr. Oracle EBS Developer

Description:

E-Mask View is a web based interface that provides quick, reliable and easy to use pattern viewing and job deck viewing of photo masks remotely. The interface is designed to provide customers with the ability to view job layouts prior to manufacturing without having to invest in specialized viewing software or training. It also automates the customer order, provides a reusable and a flexible way for customers to enhance the order processing, order specification validity, streamlining of orders and also lowers the risk of human errors within the organization.

Responsibilities:

Involved in gathering business requirements, analysis and defining technical requirements.

Created technical design documentation as per the functional requirements.

Created stored procedures, packages, and functions to transform the source data files and loaded into the target tables.

Created unix shell scripts to automate the business processes and scheduled them using cron tab.

Performance tuned the processes using explain plan, creating indexes, using sql trace and tkprof.

Developed an interface to transmit data on a daily basis from the mainstream application to downstream applications.

Developed complex matrix reports, html reports, adhoc excel reports as per the business requirements.

Generated various statistical reports using oracle reports builder based on the migrated data for the review of the management.

Developed group, tabular and form reports, menus, and object libraries using oracle reports developer.

Designed user interface forms using oracle forms builder to provide user with a column selection criteria to generate reports.

Environment: Solaris,Oracle10g, Java, Oracle Forms/Reports,Oracle EBS 11.5.10, TOAD 9.0.

Client: Verizon Business, Clinton, MS Mar`07- Feb`08

Jr. Oracle PL/SQL Developer

Description:

Four way match is a web based application that provides the functionality to obtain, track and validate data regarding circuit counts across revenue billing, LEC billing(cost),provisioning and order entry systems which is critical to cost management. It provides automated, accurate reporting on circuit based costs and revenue as well as determines the relation between them. The tool furnishes production level reports that can be acted upon to see the impact of non-revenue generating circuits that still have LEC costs.

Responsibilities:

Involved in translating functional requirements into detailed design requirements.

Involved in the upgrading of the existing logical and physical data model and created various relational database tables, index organized tables and partitioned tables.

Configured the java file extraction process to migrate the data from different databases.

Created and modified materialized views by adding new fields to enhance the existing business reports as per the requirements.

Created SQL*Loader scripts, external tables to load the date from source files into database tables.

Developed pl/sql packages, stored procedures and functions to populate the data in the database tables, generated revenue stream audit reports in excel sheets.

Created test plan, test scripts, documented the test scenarios, and coordinated with the business users on resolution.

Performance tuned sql queries and pl/sql blocks for the maximum efficiency and fast response using explain plan, using indexes and hints.

Environment: Solaris 10, Oracle10g, Java, PL/SQL, SQL Navigator 5.0, PVCS, Erwin 4.1

EDUCATION:

Masters in Engineering, Muenster, Germany (Oct`2003-Nov`2005).

Contact this candidate