Prakash Dhandapani

SQL PL/SQL Developer / Python & SSIS Developer

PROFESSIONAL SUMMARY

11+ years of IT industry experience. Around 9+ years of experience as a SQL, PL/SQL Developer and worked extensively in Finance domains and banking systems in diverse business and technical environments.

Familiar with software development life cycle (SDLC) and working in Agile, Scrum, Waterfall models.

Expert in SQL (DDL, DML) coding, creating packages, stored procedures, functions, views, triggers using cursors, Dynamic queries, exception handling, cursors, inbuilt SQL functions and complex queries.

Hands on experience in Writing & Debugging Stored Procedures and Triggers, Troubleshooting Database issues.

Hands on experience in Creating tables, writing T-SQLs in SQL Server and creating SQL Server Integration Services (SSIS) packages to perform ETL process.

Extracted data from various sources like SQL Server, Oracle, CSV, Excel and Text file from Client servers, Transport to shared folders and load them into SQL Server database using SSIS tools/packages.

Experience in creating Data flow diagrams, Entity Relationship Diagrams (ERD), Data modelling techniques.

Experience in creating Tables, Indexes, Constraints and Sequences.

Experience with Tuning SQL Queries & Stored Procedures to improve performance.

Experience in data profiling, database designing, implementing normalization techniques.

Excellent technical and analytical skills with clear understanding of design goals of ER modeling for Online Transactional Processing (OLTP).

Expertise in Analysis, Design and Implementation of Applications using Oracle Relational Database Management Systems (RDMS).

Experience in client-server application development using Oracle 11g/12c and tools like: SQL *PLUS, Oracle SQL Developer.

Experience working on Oracle supplied packages like DBMS_OUTPUT, DBMS_UTILITY, DBMS_STATS, DBMS_METADATA, UTL_FILE and other error handling packages.

Experience in writing Unix shell scripts to execute batch processes, invoke online screens, stage codes to higher level target regions.

Hands on coding experience in Python programming language, by creating automation scripts and utilities.

Created PL/SQL Packages with inbuilt Procedures creating JSON outputs returning CLOB, for passing the data back to JAVA service layer coding and moving it down to the UI environment. Created JSONs using functions like: JSON_ARRAYAGG, JSON_OBJECT, JSON_OBJECT_T, JSON_ARRAY_T.

Worked for European, US and Canadian clients in developing/enhancing applications hosted on Solaris server running on Unix/ Windows OS.

Extensive experience in gathering and analyzing customer requirements, estimating effort and translating functional/non-functional requirements to system requirements and designs.

Currently pursuing Executive Post Graduate program on Artificial Intelligence and Machine learning from University of Texas, Austin.

TECHNICAL SKILLS

DBMS

Oracle 12c/11g, MS SQL Server 2014, DB2

Programming

SQL, PL/SQL, Unix shell scripts, COBOL, JCL, Python

Developer Tools

SQL Plus, SQL Developer, Microsoft SQL Server Management Studio (MSSMS), Pycharm, Jupyter Notebook, Winscp, Putty, Openstudio, Appworx(Batch scheduling tool), Visual Studio 2017, Notepad++, ALM

Version control tool

Starteam, Jenkins, Git, Confluence

ETL Tools

SQL Server Integration Services (SSIS)

Operating Systems

Windows(98/2000/XP/VISTA/7/10), Unix (Solaris), LINUX, Mainframe

Frameworks

Openframe architecture

Web Technologies

HTML, JSON, XML

Data Modeling

VISIO

PROFESSIONAL EXPERIENCE

Compunnel / Wells Fargo – Des Moines, IA / Danbury, CT Jul 2020 – Till date

Job title: Software Developer

Role: SQL, PLSQL, SSIS Developer

Project & Application overview

Data Analytics – Design and Develop ETL process and Datawarehouse (DWH) application for Extracting, Transforming, Staging and Loading data from different business portfolios into SQL Server Database, to support customer remediation process.

Languages: SQL, PL/SQL, Python, Unix. Tools: SQL Server Integration Services, SQL Server Management Studio, Oracle SQL Developer, Microsoft Visual Studio, Pycharm, Jupyter Notebook. DB: Oracle and SQL Server

Responsibilities

Developed SQL queries to get data from DataMart’s.

Created SSIS packages by adding Control Flow tasks like: Data Flow, SQL Execution, Bulk Insert, for loop, execute process, execute package, script involving components like: Source / Destination assistant, Data Conversion, Derived columns, etc.,

Created SSIS packages to extract data from various sources (Oracle, SAS, Teradata, SQL Server, FTP Server) in different formats (delimited, txt, csv), transform it to staging environment and load into target tables.

Involved in complete SSIS life cycle in creating SSIS packages, building, deploying and executing the packages.

Created jobs in SQL Management Studio to execute the packages / scripts created using SSIS tools.

Developed SQL queries and Store Procedures and provided Source to Target Mapping and Technical design documentation as per the requirement.

Created SSIS packages with Error handling, notifications and package configurations.

Troubleshooting and resolving data integration issues and maintaining data quality.

Providing Mapping documents from Source to Destination.

Involved in designing complex architecture to accept data from various sources / portfolios within the bank and stage the data into SQL Server stage tables, process, profile(data), apply transformation rules, filter, carry out edit checks and load into target SQL server tables.

Analyzed data from multiple portfolios and grouped them by functions, businesses, processes to use them in various stages of remediation.

Developed basic automation scripts and utilities using Python.

Used dynamic SQLs, cursors, array, for loop, inbuilt SQL functions while coding PL/SQLs.

TCS / BMO Transportation Finance, Danbury, CT Apr 2016 – Jul 2020

Job title: Technical Lead

Application Developer and Support

Roles: Oracle PL/SQL Developer

Project & Application overview

Portfolio Management System (PMS) is a core subledger transaction-based application inherited by BMO bank from GE as part of GE Capital’s sell off activity.

My contribution in this project was on following tasks: Enhance, Support, Lift, shift and stand up the application & database in BMO environment, Modernize batch processes.

Design enhancement projects, Build, test and implement in production.

As part of Lift and Shift project, all the applications supporting GE’s Transportation Finance were moved into BMO’s IT infrastructure. PMS being GE inhouse application was also moved into BMO’s environment. This movement needed sharing data from GE and loading them into BMO’s transportation finance IT infrastructure / Databases. The project it itself was a huge effort to meet a very stringent timelines and involved a lot of multi-dimensional roles played by team members.

Modernize batch jobs - Batch modernization is an IT driven project to enhance the batch processes and be rewritten in Oracle PLSQL Packages / Procedures.

Languages: SQL, PL/SQL(Package, Stored Procedure, Functions, Views, Types, Triggers), Python, Unix, COBOL, JCL. Tools: SQL Developer, Winscp, Putty, Starteam, Openstudio, Appworx. DB: Oracle

Responsibilities

Developed software applications using Oracle, created Utilities and Automation scripts using Unix.

Developed logical and physical database tables, performed data profiling, db design activities using normalization techniques.

Developed codes to extract data from different applications, transform data in different file formats to different applications in the ecosystem or warehouses and load data from other.

Used standard packages like DBMS_OUTPUT, DBMS_UTILITY, DBMS_STATS, DBMS_METADATA, UTL_FILE.

Created and updated UNIX shell scripts to execute newly created PLSQL (Packages, Procedures, Functions) and to move code to higher environments up until SIT, UAT and Production.

Created Entity Relational Diagrams representing new data structures for the enhancements.

Created new tables using 2NF and 3NF normalization technique, to accommodate new business' data and integrate with existing tables, supporting faster access, achieving minimal to zero performance issues.

Proven ability in Performance Tuning and Query Optimization.

Used dynamic SQLs, cursors, array, for/while loop, various SQL functions while coding PLSQLs.

Created PL/SQLs which created JSONS / CLOB outputs.

Analyzed, designed, implemented and converted batch jobs/programs into Oracle PL/SQL packages, procedures, Views, functions.

Created two instances of database for loading data created by as is batch processes and output of newly created PLSQLs.

Carried out data accuracy testing by comparing both instance of data (AS IS vs TO BE).

Involved in enhancement of application with new functionalities and fixing of User Acceptance Testing issues / problems.

Involved in table redesigning with implementation of constraints (Primary / Foreign key) to make Database Faster in response and easier to maintain.

Involved in creation of design documents for new requirements and coordinated with various portfolios within the IT ecosystem to integrate with other application within the bank system.

Supported application to run business as usual.

Resolved production defects.

Productionized code.

Involved in pre and post-production testing.

Coded Sort utility using Python programming language.

Client interaction and Requirement Gathering.

Lead onsite team and worked in onshore offshore model.

APPLICATION

Portfolio Management System (GE’s inhouse application)

Portfolio Management System (PMS) is a core subledger transaction-based application in Bank of Montreal Transportation Finance business’ IT echo system, dealing with lease and loan products. Lease and loan products were offered for wide range commercial goods and transportation equipment.

Portfolio Management System (PMS) is a core subledger transaction-based application in GE Capital business’ IT echo system, dealing with lease and loan products. Lease and loan products were offered for wide range commercial goods and transportation equipment.

PMS is an in-house application built in GE which supports following functions in itself: accounting, deal booking (back end), funding, Servicing, Termination, Collections, Litigation. The application is built using open framework platform offered by Tmax Soft running on Unix server.

TCS / GE Capital, Danbury, CT / Bangalore, India Apr 2011 – Mar 2016

Job title: Application Developer and Support

Roles: Oracle PL/SQL Developer and Production support

Languages: SQL, PL/SQL(Package, Stored Procedure, Functions, Views, Types, Triggers), Unix, COBOL, JCL. Tools: SQL Developer, Winscp, Putty, Starteam, Openstudio, Appworx. DB: Oracle

Project & Application overview:

Portfolio Management System (PMS) is a core subledger transaction-based application inherited by BMO bank from GE as part of GE Capital’s sell off activity.

My contribution in this project was on following projects:

Project Name: Tenant ID – Phase I & II

As part of Tenant ID phase II project changes were made to each processes to support divestiture. Project work included separation of processes based on portfolios.

Project Name: Last Calendar Day (LCD)

Last Calendar Day project is an IT driven enhancement to PMS application. It enabled month end batch processes to run exactly on the last day of the month instead of running on last business day of the month. Since PMS is a heavily batch dependent application, all month end batch processes running on last calendar day of the month had to be optimized. As part of this project pseudo codes created by Mainframe team was re-written in PLSQL/Unix scripts respectively.

Project Name: Loan Servicing Project (LSP)

Loan Servicing Project is an enhancement to application to handle new type loan product which was introduced to heavy truck. This product offering brought huge revenues to GE transportation finance business sector in GE.

Project Name: Accept All Assets (AAA)

Loan Servicing Project is an enhancement to application to handle new type loan product which was introduced to heavy truck. This product offering brought huge revenues to GE transportation finance business sector in GE.

Responsibilities

Integrated PL/SQL with Microfocus COBOL code, as part of a pilot project there was a requirement to call PL/SQL from a COBOL programs.

Converted monthly report generating batch process into PL/SQL procedures and functions.

Created UNIX shell scripts to execute new reporting program created as SQLs / PLSQLs.

Developed logical and physical database tables, performed data profiling, db design activities using normalization techniques.

Developed software applications using Oracle, created Utilities and Automation scripts in Unix.

Developed codes to extract data from different applications, transform data in different file formats to different applications in the ecosystem or warehouses and load data from other.

Analyzed, designed, implemented and converted batch jobs/programs into Oracle PL/SQL packages, procedures, Views, functions.

Created and updated UNIX shell scripts to execute newly created PLSQL (Packages, Procedures, Functions) and to move code to higher environments up until SIT, UAT and Production.

Used dynamic SQLs, cursors, array, for/while loop, various SQL functions while coding PLSQLs.

Involved in enhancement of application with new functionalities and fixing of User Acceptance Testing issues / problems.

Involved in creation of design documents for new requirements and coordinated with various portfolios within the IT ecosystem to integrate with other application within the Financial system.

Supported application to run business as usual.

Resolved production defects.

Productionized code.

Involved in pre and post-production testing.

Client interaction and Requirement Gathering.

TCS / ABN AMRO, Bangalore/Mumbai, India Oct 2008 – Mar 2011

Job title: Mainframe application support

Roles: Application support - offshore

Languages: IBM Mainframe, COBOL, JCL DB: DB2

Project & Application overview

CBS Domain is one of the core business units of ABN AMRO Bank as it deals with all the communication between the bank and its customers in any form. More than which it plays a pivotal role in communicating with all other applications in the Bank’s different applications. CBS runs on IBM Mainframe OS and applications performing the functionalities were coded in COBOL language and executed by JCLs.

Investment system deals with the development and maintenance of all investment products offered by ABNAMRO bank to their customers and shares data with the Input / Output processing system of the Bank. System runs on IBM Mainframe OS and applications performing the functionalities were coded in COBOL language and executed by JCLs.

Responsibilities

Creating technical Documents of programs impacted.

Support Functional Acceptance testing and Performance Testing performed by test vendor.

Analyzed and fixed defects

Monitored Batch run using batch scheduler tool

Designed, coded and involved in unit/system testing.

EDUCATION

Bachelor of Technology (Electrical and Electronics Engineering)

Amrita Institute of Technology (Amrita Vishwa Vidyapeetham), Coimbatore, TN, India

April 2004 - May 2008

Executive Post Graduate program on Artificial Intelligence and Machine learning

Currently in progress

CERTIFICATIONS / ACCOLADES / AWARDS

DB2-730 IBM DB2 9 Fundamentals

January 2011

TCS on the spot award

2008-2015

TCS Star team award

2008-2015

TCS Service & Commitment award

2011, 2013, 2015, 2018

TCS Technical Excellence award

2012

TCS Best team award

2013

Contact this candidate