This project involved developing an automated interface between the UniClaim (UCP) system and the Policy Data Mart (PDM). This UCP to PDM interface allowed all policy information needed to establish a Business Owner Policy (BOP) loss on the UCP system to be added to UCP through an automated process. The project further developed the PDM to a single repository used to store all commercial and personal lines business. This centralized database provided any external source that needs to obtain complete details on a policy.
Responsibilities:
Responsible for requirements gathering, analysis, design and development of any enhancements in the application.
Created and maintained database objects like Tables, Views, Materialized views, and Synonyms to support business requirements.
Developed Stored Procedures, Functions, Triggers and Packages to meet the business requirements and update the existing objects based on change requests.
Utilized database links to accommodate cross-database transactions.
Extensively used PL/SQL Collections and Records, Partition methods for oracle tables and indexes.
Worked with Bulk Collect to improve the performance of fetching data.
Performed Oracle Performance tuning using SQL_TRACE and EXPLAIN PLAN.
Implemented table partitioning and sub-partitioning to improve performance and data management.
Handled errors using Named System Exceptions such as INVALID_NUMBER, NO_DATA_FOUND and PRAGMA EXCEPTION_INIT.
Created aggregate materialized views in Data Mart.
Designed and developed data loading processes using PL/SQL and UNIX Shell scripts.
Involved in developing ETL Process to load the data from different sources.
Used SQL*Loader and external table to successfully massive data migration.
Used AUTONOMOUS_TRANSACTION PRAGMA to log the status when error occurred.