Discoverer Portlets for Sharing 11 i OM Shipping

Discoverer Portlets for Sharing 11 i OM & Shipping Data with Customers Nor. Cal OAUG 2007 Training Day Event January 17, 2007 Eric Guether Opnext, Inc. eguether@opnext. com

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

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

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 11 i – 11. 5. 10. 2 (CU 2) American English with Japanese NLS – Financials, OM, Inventory, Purchasing, Cost Mgmt. , Discrete MFG & Master Scheduling/MRP

Examples of Opnext Products Laser Diode Modules Transceivers Transmitters 10 Gb. E XENPAK Modules Receivers

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

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

Business Case: IT Dept. Response • Denied the initial request – External users would need a Discoverer client installation and VPN access to Opnext’s network – Support burden for Opnext’s IT staff – Security concerns about customer access to other EBS 11 i data • Proposed an alternative solution using Oracle Portal – Originally Portal 9 i – Subsequently upgraded to Portal 10 g. R 2 (v 10. 1. 2. 1)

Business Case: Proposed Solution • Public website built from Oracle Portal 9 i – 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 9 i 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

Encrypted User Access from Internet Certificate Authority (CA) validation Verisign SSL-128 bit Encryption Internet Client [Desktop or Laptop] Firewall Encryption Key Secure policy / NAT translation To Portal

Original Application Server (AS) Architecture

Portal Login Page

Portal Page # 1: Open Sales Orders

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 11 i tables or views include: – ONT. OE_ORDER_HEADERS_ALL – ONT. OE_ORDER_LINES_ALL – APPS. MTL_CUSTOMER_ITEM_XREFS_V

Portal Page # 2: Current Month Shipments

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 Auto. Invoice runs) • Queried EBS 11 i tables include: – WSH_NEW_DELIVERIES – WSH_DELIVERY_DETAILS – AR. RA_CUSTOMER_TRX_ALL

Portal Page # 3: Prior Month Shipments

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 11 i database’s Discoverer 9 i End User Layer (EUL) • Select Discoverer portlet refresh options

Highlight Region on Portal Page

Add Discoverer Portlet • Creates an undefined portlet (no worksheet selected)

Select User-Defined Connection

Select Worksheet from EBS Database EUL • Lists all workbooks to which Portal ID has access via the selected user-defined connection

Edit Portlet Defaults

Select Portlet Refresh Options

Issue: Export from Portal Page to Excel • Portal offered no way to export the result set table • Browser’s “Save As” functionality was inadequate • Highlighting the table and copying into Excel produced weird results • Critical enough to kill the project if not resolved

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 10 g. R 2: Analyze § Link in Portal 9 i: View Worksheet – Opens a Viewer page and runs a real-time query of the Discoverer worksheet used by the portlet

“Analyze” Link on Portal Page. . .

. . . Opens Viewer & Reruns Sheet Query

Export Action in Viewer. . .

. . . Gets the Result Set into Excel

Issue: Restricting User Access in Viewer • Risk # 1: Could the user remove a worksheet’s 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 9 i users who accessed Viewer 9 i via the portlet’s View Worksheet link could open any workbook in Viewer to which their connection had access (not just the worksheet used by the portlet) – Opnext’s implemented security relationship limited the user to only the workbook used by his/her 3 portlets

Solution: Security Relationships EBS 11 i ID PORTAL_USER Responsibility Workbook Portal ID US – Portal for Demo PORTAL – DEMO US – Portal for Customer 1 PORTAL – CUSTOMER 1 US – Portal for Customer 2 PORTAL – CUSTOMER 2 Assign Responsibility Grant Workbook Access Key to Solution User-Defined Connection linking Portal ID to Responsibility

Create One EBS Account for All Portal IDs • An EBS user account, PORTAL_USER, created only once – not once per Portal ID

Create New EBS 11 i Responsibility • One unique responsibility for each Portal ID • Responsibility only has access to Preferences menu

Assign New Responsibility • Assigned to shared EBS user account: PORTAL_USER • Used to grant access to Discoverer workbook

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

Create User-Defined Connection “PORTAL_USER” is the EBS 11 i user name (EBS ID)

Create User-Defined Connection (cont. ) “US – Portal for DEMO” is the EBS responsibility

Issue: Shipment Tracking Hyperlink • On Current Month and Prior Month Shipments portlets, change the Fed. Ex, UPS, or DHL tracking number in the “Waybill #” column to a hyperlink to the shipping carrier’s 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?

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 = <None>] • Data type set to Varchar • Formula: – Uses “Ship Method Code” in DECODE to determine external web address – Concatenates tracking # from “Waybill” column

FILE Item Type in Discoverer Admin

“Tracking Link” Item Formula DECODE(Ship Method Code, 'UPS', 'http: //wwwapps. ups. com/Web. Tracking/process. Inpu t. Request? HTMLVersion=5. 0&sort_by=status&tracknums_d isplayed=5&Type. Of. Inquiry. Number=T&Inquiry. Number 1='||" Waybill"||'&Agree. To. Terms. And. Conditions=yes&track. x=42& track. y=11', 'FEDEX', 'http: //www. fedex. com/Tracking? tracknumbers='||" Waybill"||'', 'DHL', 'http: //track. dhlusa. com/Track. By. Nbr. asp? Shipment. Number='||"Waybill"||'', NULL)

Desktop Workbook with Tracking Link

Portlet with Tracking Link • Click triangle (1 st click)

Portlet with Tracking Link (continued) • Mouse over URL and click (2 nd click)

Portlet with Tracking Link (continued) • Tracking website pops up in new browser window

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

Alternative Functionality in 10 g. R 2 • Drill Link to Internet URL – New hyperlink functionality added to Discoverer Viewer & Plus 10 g. R 2 – Not added to 10 g. R 2 portlets! D Oracle bug 4969310 D To be resolved in Portal version 11. 1. 1

Additional Resources • Configuring Application Server 10 g. R 2 For Discoverer Portlets, Mark Rittman [http: //www. rittman. net/2005/03/04/configuring-applicationserver-10 gr 2 -for-discoverer-portlets/] • Oracle Business Intelligence Discoverer Administration Guide, Chapter 18, “Using Discoverer with components of Oracle Application Server” [Oracle Discoverer 10 g Release 2 doc # B 13916 -04] • Oracle. AS Web Cache 10 g (9. 0. 4) FAQ [Metalink note # 270154. 1] • How To Tune Discoverer 10 g (9. 0. 4) Portlets To Avoid Refresh Problems [Metalink note # 266540. 1]

Additional Resources (continued) • Discoverer Portlets Fail To Refresh With Error 'Could not get a free session‘ In PTM 5_CACHE Table [Metalink note # 303231. 1] • Oracle 10 g Discoverer (9. 0. 4) Best Practices for Performance and Scalability Information [Metalink note # 266818. 1] • Oracle Business Intelligence Discoverer Plus User's Guide – 10 g Release 2 (10. 1. 2. 1), Chapter 10, “How to create drill links to Internet URLs” [Oracle Discoverer 10 g Release 2 doc # B 13915 -04]