Discoverer Portlets for Sharing 11i OM

1
Discoverer Portlets for Sharing 11i OM
Shipping Data with Customers

• NorCal OAUG 2007 Training Day Event
• January 17, 2007
• Eric Guether
• Opnext, Inc.
• eguether_at_opnext.com

2
Learning Objectives

• Understand why Opnext implemented a Portal with
  Discoverer portlets
• Find out some things that Discoverer portlets can
  and cannot do
• Learn how to create a shipment tracking link to
  an external website in a Discoverer portlet or
  even in a Discoverer workbook!
• Receive an overview of Discoverer portlet key
  setups

3
Todays Speaker Eric Guether

• IT Director at Opnext in Eatontown, NJ
• Manages Opnexts Oracle EBS 11i system
• Provides user support for some 11i modules,
  Oracle Alerts and Discoverer 9i Desktop
• Member of OAUG and NJOAUG
• Began career as an accountant and then CPA

4
Who Is Opnext?

• Lasers!
• Designer maker of active fiber optic components
• Spin-off of 2 Hitachi businesses
• Headquarters in Eatontown, NJ
• Manufacturing in Yokohama Komoro, Japan
• U.S. Sales based in Fremont, CA
• Global sales to customers such as Lucent,
  Alcatel, Cisco, Siemens, Kodak, NEC, and Hitachi
• Running multiorg, single global instance of EBS
  11i
• 11.5.10.2 (CU2) American English with Japanese
  NLS
• Financials, OM, Inventory, Purchasing, Cost
  Mgmt., Discrete MFG Master Scheduling/MRP

5
Examples of Opnext Products
Laser Diode Modules
Transmitters
Receivers
Transceivers
10GbE XENPAK Modules
6
Introduction

• Oracle Portal
• Provides hubs of information from EBS 11i and
  other systems
• Used by many organizations
• Discoverer
• Provides users with queries against EBS 11i data
• Used by many organizations
• Discoverer Portlets
• Objects within Oracle Portal to cache and display
  data directly from Discoverer workbooks
• Rarely used?

7
Business Case Initial User Request

• Grant Discoverer Desktop access to external
  customers sales reps
• Allow external parties to run real-time
  Discoverer queries on open sales orders and
  recent shipments
• Arose from frustration Internal staff was
  burdened with weekly filtering exporting of
  Discoverer data to e-mail to customers

8
Business Case IT Dept. Response

• Denied the initial request
• External users would need a Discoverer client
  installation and VPN access to Opnexts network
• Support burden for Opnexts IT staff
• Security concerns about customer access to other
  EBS 11i data
• Proposed an alternative solution using Oracle
  Portal
• Originally Portal 9i
• Subsequently upgraded to Portal 10gR2 (v 10.1.2.1)

9
Business Case Proposed Solution

• Public website built from Oracle Portal 9i
• No client installation or VPN access required
• Unique Portal ID and password for each external
  user
• Use Discoverer portlets to provide OM data from
  Discoverer 9i worksheets
• Portlet data would be cached
• User sees data immediately on Portal page
• No waiting for the Discoverer query to run
• Cached data refreshed hourly or daily
• Date/time of cached data displayed on Portal page

10
Encrypted User Access from Internet
Certificate Authority (CA) validation
Verisign
Certificate Authority (CA) validation
SSL-128 bit Encryption
Internet Client Desktop or Laptop
To Portal
Firewall
Secure policy / NAT translation
Encryption Key
11
Original Application Server (AS) Architecture
12
Portal Login Page
13
Portal Page 1 Open Sales Orders
14
Open Sales Orders Worksheet

• Open sales order lines for the customer (not yet
  ship confirmed)
• Columns include
• Sales Order and Line
• Customer PO
• Product and Customer Item
• Quantity and Price
• Queried EBS 11i tables or views include
• ONT.OE_ORDER_HEADERS_ALL
• ONT.OE_ORDER_LINES_ALL
• APPS.MTL_CUSTOMER_ITEM_XREFS_V

15
Portal Page 2 Current Month Shipments
16
Current Month Shipments Worksheet

• Fulfilled shipments with ship confirm date in the
  current month
• Columns include
• Sales Order and Line
• Customer PO
• Product and Customer Item
• Quantity and Price
• Ship Method Code and Waybill
• Receivables Invoice (NULL until AutoInvoice
  runs)
• Queried EBS 11i tables include
• WSH.WSH_NEW_DELIVERIES
• WSH.WSH_DELIVERY_DETAILS
• AR.RA_CUSTOMER_TRX_ALL

17
Portal Page 3 Prior Month Shipments
18
Overview of Key Portlet Creation Steps

• Prerequisites
• Create new Portal ID for external user
• Create a new Portal page for the new Portal ID
• Assign new page as default for new Portal ID
• Create a user-defined connection explained
  later
• Highlight region on new Portal page
• Add Discoverer portlet (undefined worksheet)
• Select user-defined connection
• Select workbook worksheet from EBS 11i
  databases Discoverer 9i End User Layer (EUL)
• Select Discoverer portlet refresh options

19
Highlight Region on Portal Page
20
Add Discoverer Portlet

• Creates an undefined portlet (no worksheet
  selected)

21
Select User-Defined Connection
22
Select Worksheet from EBS Database EUL

• Lists all workbooks to which Portal ID has access
  via the selected user-defined connection

23
Edit Portlet Defaults
24
Select Portlet Refresh Options
25
Issue Export from Portal Page to Excel

• Portal offered no way to export the result set
  table
• Browsers Save As functionality was inadequate
• Highlighting the table and copying into Excel
  produced weird results
• Critical enough to kill the project if not
  resolved

26
Resolution Discoverer Viewer

• Discoverer Viewer has Export Data functionality
• Can export its result set table to many file
  formats, including to Excel XLS format
• Each Discoverer portlet has an optional link to
  Discoverer Viewer
• Link appears in lower left bottom of portlet
• Link in Portal 10gR2 Analyze
• Link in Portal 9i View Worksheet
• Opens a Viewer page and runs a real-time query of
  the Discoverer worksheet used by the portlet

27
Analyze Link on Portal Page . . .
28
. . . Opens Viewer Reruns Sheet Query
29
Export Action in Viewer . . .
30
. . . Gets the Result Set into Excel
31
Issue Restricting User Access in Viewer

• Risk 1 Could the user remove a worksheets
  conditions in Viewer to see records of other
  customers?
• Opnext used worksheet conditions to filter by
  customer
• No Risk Viewer users cannot modify or remove
  worksheet conditions
• Risk 2 Would the security allow the portal
  user in Viewer to open other database workbooks?
• Portal 9i users who accessed Viewer 9i via the
  portlets View Worksheet link could open any
  workbook in Viewer to which their connection had
  access (not just the worksheet used by the
  portlet)
• Opnexts implemented security relationship
  limited the user to only the workbook used by
  his/her 3 portlets

32
Solution Security Relationships
EBS 11i ID
Responsibility
Workbook
Portal ID
PORTAL_USER
US Portal for Demo
PORTAL DEMO
DEMO
US Portal for Customer1
PORTAL CUSTOMER1
CUSTOMER1
US Portal for Customer2
PORTAL CUSTOMER2
CUSTOMER2
Assign Responsibility
Grant Workbook Access
Key to Solution
User-Defined Connection linking Portal ID to
Responsibility
33
Create One EBS Account for All Portal IDs

• An EBS user account, PORTAL_USER, created only
  once not once per Portal ID

34
Create New EBS 11i Responsibility

• One unique responsibility for each Portal ID
• Responsibility only has access to Preferences
  menu

35
Assign New Responsibility

• Assigned to shared EBS user account PORTAL_USER
• Used to grant access to Discoverer workbook

36
Grant Workbook Access to Responsibility

• Granted within Discoverer Desktop (EUL in EBS 11i
  database)
• Restricts other Portal users from viewing
  workbook through Discoverer Viewer

37
Create User-Defined Connection
PORTAL_USER is the EBS 11i user name (EBS ID)
38
Create User-Defined Connection (cont.)
US Portal for DEMO is the EBS responsibility
39
Issue Shipment Tracking Hyperlink

• On Current Month and Prior Month Shipments
  portlets, change the FedEx, UPS, or DHL tracking
  number in the Waybill column to a hyperlink
  to the shipping carriers website
• Not part of initial requirements
• Requested during user testing
• Dilemma How to add a Tracking Link URL column to
  the cached result set of a Discoverer portlet?

40
Resolution FILE Item Type in Workbook

• New item Tracking Link created in Discoverer
  Admin and added to Discoverer Desktop workbook
• Content type of new item set to FILE default
  
• Data type set to Varchar
• Formula
• Uses Ship Method Code in DECODE to determine
  external web address
• Concatenates tracking from Waybill column

41
FILE Item Type in Discoverer Admin
42
Tracking Link Item Formula

• DECODE(Ship Method Code,
• 'UPS','http//wwwapps.ups.com/WebTracking/proce
  ssInputRequest?HTMLVersion5.0sort_bystatustrac
  knums_displayed5TypeOfInquiryNumberTInquiryNum
  ber1'"Waybill"'AgreeToTermsAndConditionsyes
  track.x42track.y11',
• 'FEDEX','http//www.fedex.com/Tracking?trackn
  umbers'"Waybill"'',
• 'DHL','http//track.dhl-usa.com/TrackByNbr.asp?S
  hipmentNumber'"Waybill"'',
• NULL)

43
Desktop Workbook with Tracking Link
44
Portlet with Tracking Link

• Click triangle (1st click)

45
Portlet with Tracking Link (continued)

• Mouse over URL and click (2nd click)

46
Portlet with Tracking Link (continued)

• Tracking website pops up in new browser window

47
Solution Worked in 9i But Not in 10gR2

• FILE item type solution worked well as is with
  Portal 9i portlets and Discoverer 9i (Desktop,
  Viewer Plus) . . . and even with Discoverer
  Desktop 10gR2
• 9i Portlets treated Tracking Link values as
  hyperlinks
• Solution did not work when migrated to Portal
  10gR2
• Solaris OS Server Needed to apply Patch 5231969
  for FILE item type hyperlink to work on 10gR2
  portlets
• Begged Oracle for 5 months for the Solaris patch
• Linux OS Server Patch 5231969 does not work for
  10gR2 portlets on a Linux server
• Waiting for 3 months for a Linux patch from
  Oracle
• Oracle bug 5713987

48
Alternative Functionality in 10gR2

• Drill Link to Internet URL
• New hyperlink functionality added to Discoverer
  Viewer Plus 10gR2
• Not added to 10gR2 portlets!
• Oracle bug 4969310
• To be resolved in Portal version 11.1.1

49
Additional Resources

• Configuring Application Server 10gR2 For
  Discoverer Portlets, Mark Rittman
  http//www.rittman.net/2005/03/04/configuring-app
  lication-server-10gr2-for-discoverer-portlets/
• Oracle Business Intelligence Discoverer
  Administration Guide, Chapter 18, Using
  Discoverer with components of Oracle Application
  Server Oracle Discoverer 10g Release 2 doc
  B13916-04
• OracleAS Web Cache 10g (9.0.4) FAQ Metalink
  note 270154.1
• How To Tune Discoverer 10g (9.0.4) Portlets To
  Avoid Refresh Problems Metalink note 266540.1

50
Additional Resources (continued)

• Discoverer Portlets Fail To Refresh With Error
  'Could not get a free session In PTM5_CACHE
  Table Metalink note 303231.1
• Oracle 10g Discoverer (9.0.4) Best Practices for
  Performance and Scalability Information
  Metalink note 266818.1
• Oracle Business Intelligence Discoverer Plus
  User's Guide 10g Release 2 (10.1.2.1), Chapter
  10, How to create drill links to Internet URLs
  Oracle Discoverer 10g Release 2 doc B13915-04