I'm IT - I've been OraBlog tagged.

Tim Dexter tagged me in the current round of OraBlog tag, so as per the rules here are the 8 things you might not know about me:

1. Pianist? nope, Computer Science. My career could have been as a musician. When it came to University I auditioned and got accepted into a piano performance spot, so my choice was Computer Science or Musician. Either way I'd be tapping a keyboard so my $$ motivated decision meant Comp Sci was it. Plus my brother is better on the music front ;-)
2. Mmmm, Pork Chops Chocolate. If you leave chocolate near me you won't see it again.
3. First Day Blues. As a graduate intake at Oracle New Zealand back in 1995, on my first day no one expected me there, so my first week was spent with a SQL*Plus and DBA training manual.
4. Pickled. I once worked in a pickle factory in Japan.
5. The Beach. I grew up near, or should that be on, the beach - nothing better than salt water and fresh warm air. It's a shame I'm not a windsurfer in Windy Wellington though.
6. Patchsets. I'm behind the R11i and R12 patchsets blogs that some Oracle Apps folks might find useful.
7. Games. Another influence when I was young was Rally-X. So much so a couple of years back I put together an arcade style MAME machine that included hacking a keyboard and wiring up a custom joystick port.
8. Adrenalin Rocks. Every once in a while I gotta have a bit of a thrill and there ain't much better than in New Zealand. If you're in the "posh part of down under" as Tim says, I'll be more than happy to help you out with the 134m Nevis Bungy, 7m waterfall Kaituna Rafting, Kayaking Aniwhenua Falls or Mountain Biking the Queen Charlotte Walkway.

Now to the tagging, bound to tag someone already tagged, but hey, will try anyway!

1. Anil Passi
2. Atul Kumar
3. Bas Klaassen
4. Fadi Hasweh
5. Jornica
6. Lucas Jellema
7. Patrick Wolf
8. Sam

Null XML elements and no NVL function BI Publisher RTF? Try xdofx decode

Where did that nvl() function go when creating BI Publisher Templates?

Lets say you've got this data:

<?xml version="1.0" encoding="UTF-8"?>
<rowset>
<row>
<g1>1</g1>
<g2></g2>
<g3>3</g3>
<g4></g4>
</row>
</rowset>

And in your template you put:

Sum: <?G1+G2+G3+G4?>

Uh-oh, you get the lovely number NaN or a nice long java error including:

Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert  to number.

What to do? Well, you could rightly put nvl(G1,0) functions around whatever SQL query is generating your XML data, but if not then where's the nvl in BI Publisher lingo? Oh here it is:

Sum: <?xdofx:decode(G1,'',0,G1) + decode(G2,'',0,G2) + decode(G3,'',0,G3) + decode(G4,'',0,G4)?>

Well, not that lovely after all, but if it works ...

Oracle eBusiness Suite Product and Acronym Listing

Couldn't find anything like this anywhere, so here we go!

Oracle eBusiness Suite / Applications Products with Acronyms and comparison of Release 11i and Release 12.

Acronym	Product	Release	Notes
ABM	Activity Based Management	R11i, R12	Obsolete R12
AD	Applications DBA	R11i, R12
ADS	Applications Demonstration Services	R11i, R12
ADS_DEV	ADS Development	R11i, R12
ADX	Rapid Install	n/a	Psuedo Product
AGIS	Advanced Global Intercompany System	R12	Sub-product of FUN
AHL	Complex Maintenance Repair and Overhaul	R11i, R12
AHM	Hosting Manager	R11i, R12	Obsolete R12
AK	Common Modules-AK	R11i, R12
ALR	Alert	R11i, R12
AME	Approvals Management	R11i, R12
AMF	Fulfillment Services	R11i, R12	Obsolete R12
AML	Leads Management	n/a	Psuedo Product
AMS	Marketing	R11i, R12
AMV	Marketing Encyclopedia System	R11i, R12
AMW	Internal Controls Manager	R11i, R12
AN	Sales Analysis	R11i, R12
APPLCSF	Applications Common Support/Script Files	R11i, R12	Acronym - not product or psuedo product.
AR	Receivables	R11i, R12
AS	Sales Foundation	R11i, R12
ASF	Sales Online	R11i, R12
ASG	CRM Gateway for Mobile Devices	R11i, R12
ASL	Sales Offline	R11i, R12
ASN	Sales	R11i, R12
ASO	Order Capture	R11i, R12
ASP	Oracle Sales for Handhelds	R11i, R12	Renamed from Field Sales/Palm Devices
AST	TeleSales	R11i, R12
ATG	Applications Technology	n/a	Psuedo Product
AU	Application Utilities	R11i, R12
AX	Global Accounting Engine	R11i, R12	Obsolete R12
AZ	Application Implementation	R11i, R12
BEN	Advanced Benefits	R11i, R12
BIC	Customer Intelligence	R11i, R12	Obsolete R12
BIE	eCommerce Intelligence	R11i, R12
BIL	Sales Intelligence	R11i, R12
BIM	Marketing Intelligence	R11i, R12
BIN	Communications Intelligence	R11i, R12
BIS	Applications BIS	R11i, R12	AKA Business Intelligence
BIV	Service Intelligence	R11i, R12
BIX	Interaction Center Intelligence	R11i, R12
BIY	Systems Intelligence	R11i, R12
BLC	Utility Billing	R11i, R12
BNE	Web Applications Desktop Integrator	R11i, R12
BOM	Bills of Material	R11i, R12
BPA	Bill Presentment Architecture	n/a	Psuedo Product
BSC	Balanced Scorecard	R11i, R12
CAC	Common Application Calendar	n/a	Psuedo Product
CC	Interaction Center	n/a	Psuedo Product
CCT	Telephony Manager	R11i, R12
CDR	Oracle Clinical Data Repository	R12	AKA Life Sciences Data Hub
CE	Cash Management	R11i, R12
CHV	Supplier Scheduling	R11i, R12
CLA	APAC Consulting Localizations	R12
CLE	EMEA Consulting Localizations	R12
CLJ	Japan Consulting Localizations	R12
CLL	LAD Consulting Localizations	R12
CLN	Supply Chain Trading Connector for RosettaNet	R11i, R12
CLR	Culinary Application	R11i, R12
CN	Incentive Compensation	R11i, R12
COM	Communications	n/a	Psuedo Product
CPGC	CPG - CDOA	R11i, R12
CRP	Capacity	R11i, R12
CS	Service	R11i, R12
CSC	Customer Care	R11i, R12
CSD	Depot Repair	R11i, R12
CSE	Asset Tracking	R11i, R12	Renamed from Enterprise Install Base
CSF	Field Service	R11i, R12
CSI	Install Base	R11i, R12
CSK	Knowledge Base	n/a	Psuedo Product
CSL	Field Service/Laptop	R11i, R12
CSM	Field Service/Palm	R11i, R12
CSN	Call Center	R12
CSP	Spares Management	R11i, R12
CSR	Scheduler	R11i, R12
CSS	Support	R11i, R12	Obsolete R12
CST	Cost Management	R11i, R12
CSZ	Customer Support	n/a	Psuedo Product
CTB	Clinical Transaction Base	R11i, R12
CUA	Capital Resource Logistics - Assets	R11i, R12
CUC	Revenue Accounting	R11i, R12
CUE	Billing Connect	R11i, R12	Obsolete R12
CUF	Capital Resource Logistics - Financials	R11i, R12
CUG	Citizen Interaction Center	R11i, R12
CUI	Network Logistics - Inventory	R11i, R12
CUN	Network Logistics - NATS	R11i, R12	Obsolete R12
CUP	Network Logistics - Purchasing	R11i, R12
CUR	Mass Market Receivables for Comms	R11i, R12
CUS	Network Logistics	R11i, R12
CUSTOM	Custom Development	R11i, R12
CZ	Configurator	R11i, R12
DDD	CADView-3D	R11i, R12
DDR	Demand Signal Repository	R12.1
DEM	Demo Order Entry (AOL Class)	R11i, R12
DEM01	Team 01 Order Entry Demo	R12
DMF	Discrete Manufacturing	n/a	Psuedo Product
DNA	Development	R11i, R12
DOM	Document Managment and Collaboration	R11i, R12
DPP	Price Protection	n/a	Psuedo Product
DT	DateTrack	R11i, R12
DUMMY_GMO	Obsolete Process Operations	R12
EAA	SEM Exchange	R11i, R12	Obsolete R12
EAM	Enterprise Asset Management	R11i, R12
EC	e-Commerce Gateway	R11i, R12
ECX	XML Gateway	R11i, R12
EDR	E-Records	R11i, R12
EDW	Enterprise Data Warehouse	n/a	Psuedo Product
EGO	Advanced Product Catalog	R11i, R12
EMS	Environment Management System	R11i, R12
ENG	Engineering	R11i, R12
ENI	Product Development Intelligence	R11i, R12
EVM	Value Based Management	R11i, R12
EWS	Enterprise Warehouse Source	n/a	Psuedo Product
EXCHG	Exchange	n/a	Psuedo Product
FEM	Enterprise Performance Foundation	R11i, R12	Renamed from Strategic Enterprise Management
FF	FastFormula	R11i, R12
FII	Financial Intelligence	R11i, R12
FIN	Financials	n/a	Psuedo Product
FLM	Flow Manufacturing	R11i, R12
FND	Application Object Library, FND=FouNDation	R11i, R12
FPA	Project Portfolio Analysis	R11i, R12	Renamed from Portfolio Analyzer
FPT	Banking Center	R11i, R12	Obsolete R12
FRM	Report Manager	R11i, R12
FTE	Transportation Execution	R11i, R12
FTP	Transfer Pricing	R11i, R12
FUN	Financials Common Modules	R12
FV	Federal Financials	R11i, R12
FWK	Applications Framework	n/a	Psuedo Product
GCS	Financial Consolidation Hub	R11i, R12	Renamed from Global Consolidation System
GIS	Global Intercompany System	R11i
GHR	US Federal Human Resources	R11i, R12
GMA	Process Manufacturing Systems	R11i, R12
GMD	Process Manufacturing Product Development	R11i, R12
GME	Process Manufacturing Process Execution	R11i, R12
GMF	Process Manufacturing Financials	R11i, R12
GMI	Process Manufacturing Inventory	R11i, R12
GML	Process Manufacturing Logistics	R11i, R12
GMO	Manufacturing Execution System for Process Manufacturing	R12
GMP	Process Manufacturing Process Planning	R11i, R12
GMS	Grants Accounting	R11i, R12
GMW	Process Manufacturing Portal	R11i, R12
GNI	Genealogy Intelligence	R11i, R12
GR	Process Manufacturing Regulatory Management	R11i, R12
HC	Healthcare	n/a	Psuedo Product
HCA	Healthcare	R11i, R12
HCC	iHCConnect	R11i, R12
HCN	iHCIntegrate	R11i, R12
HCP	Healthcare Intelligence	R11i, R12	Renamed from Healthcare Portal
HCT	Healthcare Terminology Server	R11i, R12
HR	Human Resources	n/a	Psuedo Product
HRI	Human Resources Intelligence	R11i, R12
HXC	Time and Labor Engine	R11i, R12
HXT	Time and Labor	R11i, R12
HZ	Trading Community	n/a	Psuedo Product
IA	iAssets	R11i, R12
IAM	Digital Asset Management	R11i, R12
IBA	iMarketing	R11i, R12	Obsolete R12
IBC	Content Manager	R11i, R12
IBE	iStore	R11i, R12
IBP	Bill Presentment & Payment	R11i, R12
IBT	iAuction	R11i, R12
IBU	iSupport	R11i, R12
IBW	Oracle Web Analytics	R12
IBY	Payments	R11i, R12	Renamed from iPayments
ICX	Oracle iProcurement	R11i, R12	Renamed from Self_Service Web Applications
IEB	Interaction Blending	R11i, R12
IEC	Advanced Outbound Telephony	R11i, R12
IEM	Email Center	R11i, R12
IEO	Interaction Center Technology	R11i, R12
IEP	Predictive	R11i, R12
IES	Scripting	R11i, R12
IET	Call Center Connectors	R11i, R12
IEU	Universal Work Queue	R11i, R12
IEV	IVR Integrator	R11i, R12
IEX	Collections	R11i, R12
IGC	Contract Commitment	R11i, R12
IGF	Financial Aid	R11i, R12
IGI	Public Sector Financials International	R11i, R12
IGP	Personal Portfolio	n/a	Psuedo Product
IGR	Student Recruiting	n/a	Psuedo Product
IGS	Student System	R11i, R12	Renamed from Student Systems
IGW	Grants Proposal	R11i, R12
IMC	Customers Online	R11i, R12
IMT	iMeeting	R11i, R12	Obsolete R12
INV	Inventory	R11i, R12
INL	Oracle Landed Cost Management	R12.1
IPA	Capital Resource Logistics - Projects	R11i, R12
IPATCH	Oracle.com iPatch	n/a	Psuedo Product
IPD	Product Development	R11i, R12	Obsolete R12
IPM	Oracle Imaging Process Management	R12
IRC	iRecruitment	R12
ISC	Supply Chain Intelligence	R11i, R12
ISX	iSettlement	R11i, R12
ITA	Information Technology Audit	R12
ITG	Internet Procurement Enterprise Connector	R11i, R12
ITM	Item Master	n/a	Psuedo Product
IZU	Diagnostics	n/a	Psuedo Product
JA	Asia/Pacific Localizations	R11i, R12
JAI	Financials for India	n/a	Psuedo Product
JE	European Localizations	R11i, R12
JG	Regional Localizations	R11i, R12
JL	Latin America Localizations	R11i, R12
JMF	Supply Chain Localizations	R12
JTA	CRM Applications Foundation	n/a	Psuedo Product
JTF	CRM Foundation	R11i, R12
JTH	Interaction History	n/a	Psuedo Product
JTM	Mobile Application Foundation	R11i, R12
JTO	One-to-one Fulfillment	n/a	Psuedo Product
JTS	CRM Self Service Administration	R11i, R12
JTT	CRM Technology Foundation	n/a	Psuedo Product
JTU	CRM Utilities	n/a	Psuedo Product
JTY	Territories	n/a	Psuedo Product
LNS	Loans	R11i, R12
MAS	Marketing and Sales	n/a	Psuedo Product
ME	Controlled Availability Product	R11i, R12	Obsolete R12
MFG	Manufacturing	R11i, R12
MIA	Mobile Applications for Inventory Management	R11i, R12
MIV	Media Interactive	R11i, R12
MKT	Marketing	n/a	Psuedo Product
MQA	Mobile Quality Applications	R11i, R12
MRP	Master Scheduling/MRP	R11i, R12
MSC	Advanced Supply Chain Planning	R11i, R12
MSD	Demand Planning	R11i, R12
MSE	Manufacturing Development Operations	n/a	Psuedo Product
MSO	Constraint Based Optimization	R11i, R12
MSR	Inventory Optimization	R11i, R12
MST	Transportation Planning	R11i, R12
MSX	Supply Chain Exchange	n/a	Psuedo Product
MTH	Manufacturing Development Operations	n/a	Psuedo Product
MWA	Mobile Applications	R11i, R12
OAM	Oracle Applications Manager	R11i, R12
OCM	Credit Management	n/a	Psuedo Product
ODQ	Data Query	R11i, R12
OE	Order Entry	R11i, R12
OFA	Assets	R11i, R12	AKA FA
OIE	iExpenses	n/a	Psuedo Product
OIR	iReceivables	n/a	Psuedo Product
OIT	Internet Time	n/a	Psuedo Product
OK	Contracts Suite	n/a	Psuedo Product
OKB	Contracts for Subscriptions	R11i, R12	Obsolete R12
OKC	Contracts Core	R11i, R12
OKE	Project Contracts	R11i, R12
OKI	Contracts Intelligence	R11i, R12
OKL	Lease Management	R11i, R12
OKO	Contracts for Sales	R11i, R12	Obsolete R12
OKP	Contracts for Procurement	R11i, R12	Obsolete R12
OKR	Contracts for Rights	R11i, R12	Obsolete R12
OKS	Service Contracts	R11i, R12
OKT	Royalty Management	R11i, R12
OKX	Contracts Integration	R11i, R12
OM	Order Management	n/a	Psuedo Product
ONT	Order Management	R11i, R12
OPI	Operations Intelligence	R11i, R12
OPM	Process Manufacturing	n/a	Psuedo Product
OTA	Learning Management	R11i, R12
OUC	University Curriculum	R12
OWF	Workflow	n/a	Psuedo Product
OZF	Trade Management	R11i, R12
OZP	Trade Planning	R11i, R12	Obsolete R12
OZS	iClaims	R11i, R12	Obsolete R12
PA	Projects	R11i, R12
PAY	Payroll	R11i, R12
PBR	Budgeting and Planning	R11i, R12
PER	Human Resources	R11i, R12
PFT	Oracle Profitability Manager	R11i, R12	Renamed from Performance Analyzer
PJ	Projects	n/a	Psuedo Product
PJB	Project Billing	R11i, R12	Psuedo Product
PJC	Project Costing	R11i, R12	Psuedo Product
PJF	Project Foundation	R11i, R12	Psuedo Product
PJI	Project Intelligence	R11i, R12
PJL	Project Collaboration	R11i, R12	Psuedo Product
PJM	Project Manufacturing	R11i, R12
PJR	Project Resource Management	R11i, R12	Psuedo Product
PJT	Project Management	R11i, R12	Psuedo Product
PLM	Product Lifecycle Management	n/a	Psuedo Product
PMI	Process Manufacturing Intelligence	R11i, R12
PN	Property Manager	R11i, R12
PO	Purchasing	R11i, R12
POA	Purchasing Intelligence	R11i, R12
POM	Exchange	R11i, R12
PON	Sourcing	R11i, R12
POS	iSupplier Portal	R11i, R12
POV	Exchange Marketplace - Core	n/a	Psuedo Product
PQH	Public Sector HR	R11i, R12
PQP	Public Sector Payroll	R11i, R12
PRC	Process Connect	n/a	Psuedo Product
PRGC	Progress Custom	R11i, R12
PRP	Proposals	R11i, R12
PSA	Public Sector Financials	R11i, R12
PSB	Public Sector Budgeting	R11i, R12
PSP	Labor Distribution	R11i, R12
PSR	Public Sector Receivables	R12
PTX	Patch Tracking System	R11i, R12
PV	Partner Management	R11i, R12
QA	Quality	R11i, R12
QOT	Quoting	R11i, R12
QP	Advanced Pricing	R11i, R12
QPR	Oracle Deal Management	R12.1
QRM	Risk Management	R11i, R12
RCI	Regulatory Compliance Intelligence	n/a	Psuedo Product
RCM	Regulatory Capital Manager	R11i, R12	Obsolete R12
RG	Application Report Generator	R11i, R12
RHX	Advanced Planning Foundation	R11i, R12	Obsolete R12
RLA	Release Management Integration Kit	R11i, R12	Obsolete R12
RLM	Release Management	R11i, R12
RMG	Risk Manager	R11i, R12
RRC	Retail Core	R12
RRS	Site Management	R12
SCM	Supply Chain Management	n/a	Psuedo Product
SCP	Advanced Planning	n/a	Psuedo Product
SEM	Financials Services Applications	n/a	Psuedo Product
SHT	Applications Shared Technology	R11i, R12
SLS	Sales Suite	n/a	Psuedo Product
SQLAP	Payables	R11i, R12	AKA AP
SQLGL	General Ledger	R11i, R12	AKA GL
SRV	Service Suite	n/a	Psuedo Product
SSP	HRMS (UK)	R11i, R12
SYSADMIN	System Administration	R11i, R12
TXK	Teckstack	n/a	Psuedo Product
UMX	User Management	n/a	Psuedo Product
UNV	Student System	n/a	Psuedo Product
VEA	Automotive	R11i, R12
VEH	Automotive Integration Kit	R11i, R12	Obsolete R12
WIP	Work in Process	R11i, R12
WMA	Manufacturing Mobile Applications	R11i, R12
WMS	Warehouse Management	R11i, R12
WPS	Manufacturing Scheduling	R11i, R12
WSH	Shipping Execution	R11i, R12
WSM	Shop Floor Management	R11i, R12
XBOL	Business Online	R11i, R12
XDO	XML Publisher / BI Publisher, XDO=eXtended Document Object	R11i, R12
XDP	Provisioning	R11i, R12
XLA	Subledger Accounting	R12
XLE	Legal Entity Configurator	R12
XNA	Service Assurance for Communications	R11i, R12
XNB	Oracle Telecommunications Billing Integrator	R11i, R12	Renamed from eBusiness Billing
XNC	Sales for Communications	R11i, R12	Obsolete R12
XNI	Install Base Intelligence	R11i, R12	Obsolete R12
XNM	Marketing for Communications	R11i, R12	Obsolete R12
XNP	Number Portability	R11i, R12
XNS	Service for Communications	R11i, R12	Obsolete R12
XNT	TeleBusiness for Telecom/Utilities	R11i, R12
XTR	Treasury	R11i, R12
XXV8	Virtuate	R11i, R12
ZFA	Financial Analyzer	R11i, R12
ZPB	Enterprise Planning and Budgeting	R11i, R12
ZSA	Sales Analyzer	R11i, R12
ZX	E-Business Tax	R12

BI Publisher IF statement Logical OR syntax in RTF Template

A question came through today: what is the syntax for a logical OR in a BI/XML Publisher IF statement?

The standard BI Publisher "IF" statement doesn't seem to cut it.

Lets say I want a test:

if (a=1 or a=2) and b=3 then output_my_stuff end if

I don't want to have to repeat output/logic like:

if a=1 then
 if b=3 then
  output_my_stuff
 end if
end if
if a=2 then
 if b=3 then
  output_my_stuff (again)
 end if
end if

A solution is to use form fields with an xsl if statement structure. So we have:

<xsl:if test="(A='1' or A='2') and B='3'">
 output_my_stuff
</xsl:if>

If it works use it!

Development Standards - who are ya?

What is your Oracle eBusiness Suite development standards level?

1. Ignorance: Guidelines? Standards? Couldn't find those words in the dictionary...
2. Awareness: Hmm, where'd I put that manual?
3. Choice: Okay, let's attempt to go with these rules/naming standards/architectural guidelines/rules, what were they again?
4. Documentation: Team, here's our interpretation of the rules so follow them if ya want to.
5. Enforcement: Sweet, developers are aware of and following the chosen rules

Obviously its a whole lot better and easier in the long run with option 5, despite the groundwork. Unfortunately ain't too many businesses that make it that far.

When customizing the Oracle eBusiness Suite there are a number of guidelines/standards/rules to consider. Some pointers:

• Oracle Applications Developer's Guide R11i R12
• Oracle Applications User Interface Standards for Forms-Based Products R11i R12
• Oracle Application Framework Personalization Guide R11i R12

Now we all know that we live in a world where rules made are destined to be broken!

When it comes to technology, if the rules are broken, then its a great idea to acknowledge the indiscretion and document it. That way eventually your chosen search tool will provide a reference to it at a quick double ctrl click (or similar). Hey might even end up on a forum.

For some of us, many of the "rules" are pre-programmed into our work methodology, but with changes technology, environment, weather, both seasoned developers and newbies alike should occasionally stop to look at the rule book!

Internet Explorer 7 crash on jvm.dll with Oracle JInitiator - Applications Forms - Windows Live Sign-In Helper

One of those sinking feelings set in yesterday, after deciding to accept Windows XP's suggestion to upgrade from IE6 to IE7, IE crashed when trying to start an Oracle Forms session via Oracle JInitiator version 1.3.1 (Oracle Applications Release 11.5.10.2).

The detailed error message was:

Error Signature, AppName: iexplore.exe AppVer: 7.0.6000.16544 ModName: jvm.dll, ModVer: 0.0.0.0 Offset: 0000e348.

Couple of Googles later, I found that upgrading to Sun JRE could solve the problem. Or replacing jvm.dll in the hotspot directory of Jinitiator with a later version, like j2re1.4.2_06... but thats too much work late at night.

So to Metalink, found Note:399635.1 which referred to disabling add-on Windows Live Toolbar in IE6 ... but that add-on wasn't there in IE7. So took a guess at one of the other Windows Live add-ons, and hey presto, all good again, solution:

In IE7:
Tools, Manage Add-ons, Enable or Disable Add-ons
Show: Add-ons that have been used by Internet Explorer
Click on "Windows Live Sign-In Helper"
Click Disable, Okay
Restart Internet Explorer

Update: As commented by Paul, a couple of other add-ons could be causing this, so if disabling "Windows Live Sign-In Helper" doesn't fix it, try:

• Shockwave Flash Object (Flash9e.ocx)
• Google Toolbar (googletoolbar1.dll)

Not sure the impact to Windows Live et al... will face that if I need it...

Secure storage of passwords in Oracle Applications via Encryption of Profile Option Values using dbms_obfuscation_toolkit and Forms Personalization

Have you ever had the need to store a password for accessing external systems in an Oracle Applications field such as Profile Option Values? In a few instances I've seen plain text passwords stored in Profile Option Values both at Site or User levels for scenarios such as connections to external FTP servers and the like. This raises a number of security concerns:

1. Anyone with System Administrator access can see the passwords, or worse:
2. Anyone with access to Profile Option Values can see the password(s) if the "visibility" on the profile option is not configured appropriately
3. Anyone with "read" access to the base table e.g. fnd_profile_option_values - at the database level can select the value from the table
4. Anybody watching over your shoulder can see the password as you enter it!

Well, now those prying eyes can be kept at bay, with a mix of dbms_obfuscation_toolkit, hex/raw conversions and Forms Personalization trickery. This is especially nice considering that now the Oracle Applications User passwords are also able to be made secure.

Our goals here are:

• Provide a way to securely input a password on the Oracle eBusiness Suite User interface using User Level Profile Option Values
• Encrypt the password on save
• Convert encrypted password to hex and for more friendly viewing on screen if necessary
• Ensure the encrypted hex version of the password has specific content so we know what is an encrypted value and what isn't
• Ensure when the User is entering the password it can't be seen on screen
• Provide a secure decryption routine for backend processes

Here's how we achieve this:

1. A PL/SQL package providing the encrypt and decrypt routines including the hex conversion with a marker so we know what is an encrypted password. The decrypt routine will only work for the "APPS" user.
2. A Profile Option to store the password
3. A Forms Personalization to call encryption routine, overwrite the Profile Option value, plus conceal the data entry field. Note custom.pll could be used for installations prior to 11.5.10.

Okay, so lets get to it.

1. PL/SQL Package.

I'll just provide an extract of the key psuedo code here, if anyone wants a sample package see my profile for contact information.

function encrypt ...
  l_enc := dbms_obfuscation_toolkit.desencrypt( input_string => p_value_in_multiple_of_8_bytes, key_string => my_private_key);
  return rawtohex(utl_raw.cast_to_raw(l_enc));
...
end encrypt;

function decrypt ...
  if user = 'APPS' then
    l_str := utl_raw.cast_to_varchar2(hextoraw(p_value));
    l_str  := dbms_obfuscation_toolkit.desdecrypt( input_string => l_str, key_string => my_private_key );
  end if;
  return l_str
...
end decrypt;

2. Profile Option

Navigate to Application Developer > Profile

3. Forms Personalization

Setup the "conceal" data entry when the row is for our password profile option

Setup the call to encrypt and overwrite the visible value

Copy visible value to backend column

4. Do the same Forms Personalization steps as above for the Personal Profile Values form (function FND_FNDPOMSV)

Sweet! All sorted.

Must have: ATG RUP 6 Patch 5972626 Security Update

Following on from the excellent ATG RUP 5 (5473858) which was a must have in terms of pure number of new technical components, ATG RUP 6 (5903765) is a must have with respect to security.

In my opinion, one of the biggest security holes in the Oracle eBusiness Suite looks to have a fix. Non reversible one way hashed passwords have been missing since day dot. Oracle has been criticized for this gap. Published password decryption methods are publicly available ... but with the release of ATG RUP 6, it seems like a solution is here today!

See the usage of FNDCPASS USERMIGRATE in Note 457166.1 to convert to one way hashed (SHA) passwords. Be aware that desktop smart client software authenticating to the eBusiness Suite may need to be patched, e.g. Discoverer, ADI, Balanced Scorecard, Files Online.

For any of you System Administrator types that have often had Workflow errors occur for a particular reason and then had to sit there and retry each workflow one by one will be glad to know there is now a Retry Errored Workflow Activities program. Nice.

Download ATG RUP 6 now!

Deleting Document Attachments via fnd_attached_documents2_pkg - PO Lines

A quick correspondence with reader Mark L came up with the following to delete PO Line attachments. This is in relation to my blog on document attachments.

declare
l_entity_name varchar2(20):= 'PO_LINES';  -- PO_LINES entity for the package
l_pk1_value varchar2 (20) := '200487';    -- PO_LINE_ID attachments are attached to
l_delete_document_flag varchar2 (1) := 'Y';    -- Delete document
begin
fnd_global.apps_initialize(0,20420,1); -- SYSADMIN, System Administrator
fnd_attached_documents2_pkg.delete_attachments
( X_entity_name                  => l_entity_name
, X_pk1_value                    => l_pk1_value
, X_delete_document_flag         => l_delete_document_flag
);
end;
/
commit;

Nice, but use with caution!

Excel file output from Oracle Applications concurrent request using SYLK. aka Look Ma, no BIP!

Need to create Microsoft Excel style files directly openable from Oracle Applications concurrent request output ... without using BI Publisher?

A little know file format with acronym SYLK is a handy tool for create files readable in Microsoft Excel. Since I posted about Excel / CSV output from a concurrent request using Oracle BI Publisher, I figured those people that aren't quite up to the latest versions of BI Publisher / XML Publisher might find this post handy. It requires little more than PL/SQL and earlier versions of Oracle eBusiness Suite. Not only that but also aligns nicely to my thick database inclination!

So, without further ado, here's the recipe for a very simple Excel (SYLK) output concurrent program for an FND_USERS listing with creation date from/to parameters.

1. Take a PL/SQL package based on the Oracle provided OWA_SYLK package (owasylk.sql / owa_sylk.sql) and make some changes:
• rename it to owa_sylk_apps
• Change it to use fnd_file.put_line(fnd_file.output instead of utl_file.put_line(g_file
• Remove parameters for p_file
2. Create a PL/SQL package for the concurrent program that outputs SYLK file to the concurrent request output.

   create or replace package XXXV8_USERS_SYLK_PKG AUTHID CURRENT_USER
   AS
   procedure main
   ( errbuf      out varchar2
   , retcode     out varchar2
   , p_date_from in  varchar2
   , p_date_to   in  varchar2
   );
   end XXXV8_USERS_SYLK_PKG;
   /
   
   create or replace package body XXXV8_USERS_SYLK_PKG
   AS
   procedure main
   ( errbuf      out varchar2
   , retcode     out varchar2
   , p_date_from in  varchar2
   , p_date_to   in  varchar2
   ) as
     l_date_from date;
     l_date_to   date;
   begin
     l_date_from := fnd_date.canonical_to_date(p_date_from);
     l_date_to   := fnd_date.canonical_to_date(p_date_to);
     owa_sylk_apps.show(
           p_query => 'select user_id user_id, user_name user_name, '
                      '       description description, creation_date created '
                      'from fnd_user '
                      'where trunc(creation_date) >  :DATE_FROM '
                      'and   trunc(creation_date) <= :DATE_TO ',
           p_parm_names =>
                    owa_sylk_apps.owaSylkArray( 'DATE_FROM', 'DATE_TO'),
           p_parm_values =>
                    owa_sylk_apps.owaSylkArray(l_date_from ,l_date_to),
           p_widths =>
                    owa_sylk_apps.owaSylkArray(20,20,20,20)
                    );
   end main;
   
   END XXXV8_USERS_SYLK_PKG;
   /
   

3. Setup the concurrent program (note the Output Format PCL)

   

   

   

   

4. Hijack one of the little used Viewer Options (PCL) so that we can get the Concurrent Request output browser to automatically open Microsoft Excel. Note this is optional, you can just create a new Viewer Option but then you may get the "Choose Viewer" box when viewing concurrent request output. NB: Navigation path is System Administrator, Install, Viewer Options

update fnd_mime_types_tl
set    mime_type = 'application/vnd.ms-excel'
,      description = 'Excel (SYLK) used to be application/vnd.hp-PCL: Printer Control Language'
,      last_updated_by = 0
,      last_update_date = sysdate
where  file_format_code = 'PCL'
and    mime_type = 'application/vnd.hp-PCL';

commit;



5. Assign the concurrent program to the appropriate request group (e.g. System Administrator, All Reports, Application Object Library) and run the concurrent program to test it all out!

   

   

And there you have it - Excel style output direct from concurrent request generated by PL/SQL!

References:

• Oracle Forum that triggered my interest
• Note: 178133.1
• AskTom article on SYLK