developing customer portal with oracle apex - a case study

Developing Customer Portal with Oracle APEX: A Case Study

Chandra Movva

Synerz Technologies Inc.

& Ed Kloosterboer

EastGen Inc.

Presenter Bio

• Ed Kloosterboer • 32+ years exp. in Agri. industry

• United Breeders, Gecor & EastGen Inc.

• 16+ years exp. In IT • Subject Matter Expert • Field Systems Help Desk & System Admin. • IT Manager, EastGen Inc.

• Implemented Field Web, Field Mobile Handhelds, Member Login Access (MLA) a.k.a Customer Portal, Field Mobile Tablets

Presenter Bio

• Chandra Movva • Founder & President , Synerz Technologies Inc. • Board and Exec. Member, TOUG • 20+ Years of exp.

• Leadership • IM/IT Consultant • Developer & Architect

• Trainer • Oracle, SQL, PL/SQL, Oracle Reports, Discoverer and APEX

• Presenter • ODTUG APEXposed’13 • TOUG

• chandra.movva@synerz.com

About EastGen

• Agriculture Co-Operative with 8500 members/customers from Ontario to PEI

• Retail vertical

• Headquartered in Guelph, Ontario

• Products & Services • Semen products

• Farm, Agri. and IMV products

• Artificial Insemination & Training services

• For more info. check out http://www.eastgen.ca

About Synerz Tech.

• Founded in 2001 • Headquartered in Mississauga, Ontario, Canada • Oracle Partner since 2006 • Services

• Software Licensing – Consulting and Resale • Value Added Reseller (VAR) of Oracle, Microsoft, VMware, Symantec

etc.

• Database development, migration and integration • DW & BI Reporting • Enterprise web and mobile applications • Project Management • Training

• For more info. check out http://www.synerz.com

Agenda

• Overview

• Why Customer Portal?

• Solution Options

• Methodology

• Live Portal Demonstration

• Summary

• Q & A

Overview

EastGen Business & IT Environment

• Head Office System • Oracle Forms and Reports 10g

• Field System (Web based) • Oracle Designer 10g/PLSQL Cartridge

• Field System (Mobile)

• Progen System (.NET/SQL Server)

• PlanetPress – To generate and produce monthly statements

• Oracle 10g Release 2 – Dev, Test and Prod instances

• Oracle AS 10g Release 2 – Dev/Test, Prod instances

• Small IT team

Why Customer Portal

Business Case

• Driven by members/customers • Request from Board of Directors, delegates and are nothing but customers

• Business opportunity • Competitive edge as none of the competitors offer

• Showcase products • Ability to offer products and info online • Target advertising by customer segmentation • Facilitate customers to empower their staff by

providing access to portal • Ordering and request for dispatch of technicians

Key Requirements

• Simple to use • Reasonable response • Minimal or No latency • Easy to customize and maintain • Fast deployment • Avoid synchronization and/or replication • Avoid/Minimize perpetual software licensing and

support costs

Solution Options

Solution Options

Oracle APEX

.NET

PHP

J2EE

Evaluation Criteria • Application Type

• Query Information

• Application Size • Small, Medium, Large

• Based on functionality, # of pages

• # of users

• Budget • Capital • Maintenance

• Timeline • Security • End user customization • Ease of Maintenance with in-house skills

Chosen Solution & Why?

• Oracle APEX 3.2

• Leverage existing skills

• Familiar with SQL & PL/SQL

• Tight integration with Oracle DB

• RAD tool

• Declarative

• Easy to develop and deploy

• No additional software licensing costs

Methodology

Methodology

• Architecture • 3 tier – Oracle DB, Oracle AS(HTTP Server), Web Browser

• Review Licensing • Oracle 10g EE

• Concurrent users to Processor based • Downgrade to SE one

• Oracle AS 10g EE • Concurrent users to Processor based

• Estimate Performance and Sizing • Hardware - # of CPUs • # of users • Anticipated performance bottlenecks, if any

• Bench Marking • SDLC - Combination of Waterfall & Agile

• Agile – Phased approach with 3 phases/sprints • Waterfall – Each phase comprised of analysis, design, construction, test and deploy

Methodology Cont’d

• Phased Approach • Phase 1 –

• Core functionality – Security, Ordering products and services, Account activity and Monthly statements etc.

• Custom corporate look and feel • Pilot Implementation • Get feedback and refine • Go live

• Phase 2 • Mating Reports • Price List • Application Administration

• Phase 3 & beyond • Targeted Marketing, etc.

Live Demonstration

Landing Page

Signup Page

Home Page

Ordering/Dispatch Page

Account Activity Page

Summary

Benefits

• Account info. and reports online 24 X 7 • Reduced call volume to Head Office

• Alternative for ordering services/supplies 24 X 7 • Reduced call volume to dispatch call centre

• Facilitated Targeted marketing based on customer segmentation

• Self serve portal. • Update account information – including address, phone#,

email address and various preferences resulted in reduced call volume

• No overhead to handle username or password related help desk support

Present Status

• 520 customers signed up

• Upgraded DB to 11g

• Upgraded to APEX 4.2 in 2013

Future Plans

• New development of Forms and Reports with Oracle APEX

• Convert existing Oracle Forms/Reports based applications to Oracle APEX as required.

• Additional features/functionality will be added to Member/Customer Login Portal

info@synerz.com @SynerzTech https://www.facebook.com/SynerzTech http://www.synerz.com/apex

Thank You