Thursday, December 13, 2007

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"?>

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 ...

Wednesday, December 12, 2007

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
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 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

Tuesday, December 11, 2007

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
 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'">
If it works use it!

Monday, December 10, 2007

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!

Saturday, November 24, 2007

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

The detailed error message was:

Error Signature, AppName: iexplore.exe AppVer: 7.0.6000.16544 ModName: jvm.dll, ModVer: 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...

Wednesday, November 21, 2007

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.

Tuesday, October 16, 2007

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!

Thursday, October 11, 2007

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.

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
fnd_global.apps_initialize(0,20420,1); -- SYSADMIN, System Administrator
( X_entity_name                  => l_entity_name
, X_pk1_value                    => l_pk1_value
, X_delete_document_flag         => l_delete_document_flag
Nice, but use with caution!

Tuesday, October 09, 2007

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
    procedure main
    ( errbuf      out varchar2
    , retcode     out varchar2
    , p_date_from in  varchar2
    , p_date_to   in  varchar2
    create or replace package body XXXV8_USERS_SYLK_PKG
    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;
      l_date_from := fnd_date.canonical_to_date(p_date_from);
      l_date_to   := fnd_date.canonical_to_date(p_date_to);
            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 =>
    end main;
  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
  5. update fnd_mime_types_tl
    set    mime_type = 'application/'
    ,      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';

  6. 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!


Thursday, September 27, 2007

BI Publisher Color list and cell highlighting (colours)

Nice question from a reader, thought it was time for more colour in my life ;-)

What is the list of colors (colours) supported by BI Publisher (BIP / XML Publisher / XMLP) for cell highlighting?

Firstly, how can we specify a color, say "red":

  1. By Name, e.g. red
  2. By Hex value, e.g. #FF0000
  3. By Decimal value, e.g. rgb(255,0,0)
  4. By HSL (Hue, Saturation, Light) value e.g. hsl(30,100,50). NB: Couldn't get this one to work.

Secondly, where can we specify a value?

  1. As the regular RTF borders & shading for a cell
  2. Coded as an XSL attribute

So back to the question, what is the list of colors? Well, that depends on how you specify the color, and what your target output format is.

If you want to specify by name, I think the list is as follows (HTML4 color list):

Color names and sRGB (Hex) values
Black = #000000Green = #008000
Silver = #C0C0C0 Lime = #00FF00
Gray = #808080 Olive = #808000
White = #FFFFFFYellow = #FFFF00
Maroon = #800000Navy = #000080
Red = #FF0000Blue = #0000FF
Purple = #800080Teal = #008080
Fuchsia = #FF00FFAqua = #00FFFF

If you specify the color by Hex or Decimal value, then your list is pretty big, but bear in mind that the device the user is using to view your output, plus your target output format could be limiting your effective range of colours, hence the short list of colours and the existence of "safe" colors.

Okay, I like trying out these sorts of things, so gave it a go with my Active Users - BIP template.

So threw a few colors at the RTF template:

Where the form fields (grey highlight) are as follows:

YELLOWSafe<?if:../../USER_NAME ="GROBERTS"?><xsl:attribute xdofo:ctx="block" name="background-color">yellow</xsl:attribute><?end if?>
6600CCNot safe <xsl:attribute xdofo:ctx="block" name="background-color">#6600cc</xsl:attribute>
rgb(210,105,30) chocolateNot safe <xsl:attribute xdofo:ctx="block" name="background-color">rgb(210,105,30)</xsl:attribute>
hsl(30,100,50) orangeNot safe <xsl:attribute xdofo:ctx="block" name="background-color">hsl(30,100%,50%)</xsl:attribute>

How did it turn out? Heres a shot of the PDF output, nice! ... except for the HSL

Here's a shot of the RTF output, similar to PDF.

Here's a shot of the Excel output ... not so close to the chocolate and purple I was hoping for.

So there you have it. If you care alot about your colours, test them out before your release them.

Happy highlighting!

PS. If you're looking to do any templating with BI Publisher, read the Oracle XML Publisher Report Designers Guide Release 12. It's well worth it!

Monday, September 17, 2007

Query: Monthly billing on specific day of month

Just a quick challenge.
Had a minor brain overload last Friday, eventually came up with (I think) a sub-optimal query ...
Basic scenario is that I want a billing event to happen on the monthly anniversary of a event, and want a single SQL statement that, given the original and current date, will tell me whether I should be billing on that day.
Problem is that e.g. if original date if 31-Jan-07 then I should bill on the 28-Feb-07. Moreover if the original date is 28-Feb-07 then I should bill on 28-Jan-07. However, the when the original date is 28-Feb-07 the Oracle months_between function isn't suitable as it returns 1 for both the 28-Mar-07 and 31-Mar-07, as follows:

select months_between(to_date('28-JUL-2007'),to_date('28-FEB-2007')) m1
,      months_between(to_date('31-JUL-2007'),to_date('28-FEB-2007')) m2
from   dual;

        M1         M2
---------- ----------
         5          5

1 row selected.
Okay, so here's my solution ... with a loop to test for a year. Anyone got a better alternative to the core query?
set serverout on size 1000000
 org_date date := '28-JAN-03';
 cur_date date := '01-JAN-04';
 l_status varchar2(1);
 l_count number := 0;
 l_pays  number := 0;
 while true loop
  l_count := l_count+1;
   select 'Y' yes
   into   l_status
   from   dual
        (to_number(to_char(org_date,'DD')) <= 28 and
         to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
        ( to_number(to_char(org_date,'DD')) = 29 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            ( to_number(to_char(last_day(cur_date),'DD')) < 29 and
              to_number(to_char(last_day(cur_date),'DD')) >= 28 and
              cur_date = last_day(cur_date)
        ( to_number(to_char(org_date,'DD')) = 30 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            ( to_number(to_char(last_day(cur_date),'DD')) < 30 and
              to_number(to_char(last_day(cur_date),'DD')) >= 28 and
              cur_date = last_day(cur_date)
        ( to_number(to_char(org_date,'DD')) = 31 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            ( to_number(to_char(last_day(cur_date),'DD')) < 31 and
              to_number(to_char(last_day(cur_date),'DD')) >= 28 and
              cur_date = last_day(cur_date)
   dbms_output.put_line('Billing org_date='||org_date||' cur_date='||cur_date);
   l_pays := l_pays + 1;
  when no_data_found then
  if l_count > 366 then
  end if;
  cur_date := cur_date + 1;
 end loop;
 dbms_output.put_line('Payments = '||l_pays);
Comments more than welcome!

Friday, September 14, 2007

Standard Report to CSV File via BI Publisher

Update: Added screenshots, and info on Internet Explorer 7 / Excel not opening .xls file when trying to view concurrent request output.

Gee - what a long post! WAIT! Its quick work and worth it. If you're not there already, get to a recent version of BI Publisher e.g. 5.6.3 or ATG RUP5.

We are going to:

  • Take the "Active Users" standard report, copy the program definition.
  • Setup and create a BI Publisher (BIP) Template a.k.a XML Publisher (XMLP)
  • Produce a CSV (comma separated values) output from the BIP Excel output format

All this without affecting the standard program and only a teeny tiny bit of technie stuff. This technique applies to any concurrent request that executes via Oracle Reports. The only major difference in the process is the content of the (RTF) Template, plus changing a few names for your report.

1. Copy concurrent program you want funky output from
System Administrator > Concurrent > Program > Define
Query Program "Active Users"
Click the "Copy To" Button
Program: Active Users - BIP
Application: Application Object Library
Check Include Incompatible Programs
Check Include Parameters
Click OK
Change the Output Format to XML

2. Add your new concurrent program to your required request group
System Administrator > Security > Responsibility > Request
Group: System Administrator Reports
Application: Application Object Library
Add new Request:
Program: Active Users - BIP

3. Run the request
System Administrator > Requests > Run
Name: Active Users - BIP
If you view the output, you can see the XML Source

4. In Microsoft Word create a file with the following contents save as RTF file, say ActiveUsersBIP.rtf.
Note: Nice to use BIP Template Builder, and form fields, but here I display the real tags for clarity. Software for (BI Template Builder - Patch 5887917 & Microsoft .Net 2.0 Framework).

<?for-each: G_RESPS?><?../../USER_NAME?><?RESPONSIBILITY_NAME?><?START_DATE?><?END_DATE?> <?end for-each?>

5. Define XML Publisher Data Definition and Template
XML Publisher Administrator > Data Definitions
Click Create Data Definition
Name: Active Users - BIP
Note: the code must match the concurrent program short name
Application: Application Object Library
Note: match the Application to the concurrent program application
Leave defaults for remaining fields
Click Apply

XML Publisher Administrator > Templates
Click Create Template
Name: Active Users - BIP
Note: the code must match the concurrent program short name
Application: Application Object Library
Note: match the Application to the concurrent program application
Data Definition: Active Users - BIP
Type: RTF
Click File, Browse and upload your RTF template file ActiveUsersBIP.rtf created earlier
Language: English
Click Apply

6. Run the request
System Administrator > Requests > Run
Name: Active Users - BIP
Notice the Layout has been set by default to Active Users - BIP
Click the "Layout" button and change the Format to Excel
Add, OK.

7. View request output and save as CSV
View > Requests
Click "View Output" from your concurrent request
At the Open or Save, choose Open
Note: Excel should open with the output from your file. If your Internet Explorer window pops up then disappears, you need to add your eBusiness Server to your Trusted hosts. In IE 6 this is under Tools, Internet Options, Security, click the "Sites" button under to Trusted Sites, uncheck "Require server verification (https)...", put your fully qualified hostname under "add this web site" then click OK. For Internet Explorer 7 or 6 add your site to Local Intranet Zone. Tools, Internet Options, Security, click Local Intranet, Sites, Advanced, Add your fully qualified hostname, e.g.

Click File (or Page) > Save As and choose Save as type: "CSV (Comma delimited) *.csv"

All done! Well, some screenshots might be nice... UPDATE: Added screenshots!

Now just waiting for Excel Analyzer to arrive with the eBusiness Suite

Thursday, September 13, 2007

Query: Find scheduled or on hold concurrent requests

I've noticed a number of requests for finding scheduled concurrent requests via a single query. But there are various combinations of phase_code, status_code, hold_flag, requested_start_date, etc on the fnd_concurrent_requests table and others that determine the "real" Phase and Status as displayed in the View Requests form.

No worries, here's how we find the Scheduled Requests:

select request_id
from   fnd_concurrent_requests
where  status_code in ('Q','I')
and    requested_start_date > SYSDATE
and    hold_flag = 'N';

Or for a more detailed version, check out the following:

select fcp.concurrent_program_name
,      fcpt.user_concurrent_program_name
,      fcr.description
,      fcr.request_id
,      decode(fcr.phase_code
             ) phase
,      decode(fcr.phase_code
                        ,'Y','On Hold'
                        ,decode(sign(fcr.requested_start_date - sysdate)
             ) status
,      fcr.requested_start_date
from   fnd_concurrent_requests fcr
,      fnd_concurrent_programs fcp
,      fnd_concurrent_programs_tl fcpt
,      fnd_lookups fl_p
,      fnd_lookups fl_s
where  1=1
and    fcr.phase_code = fl_p.lookup_code
and    fl_p.lookup_type = 'CP_PHASE_CODE'
and    fcr.status_code = fl_s.lookup_code
and    fl_s.lookup_type = 'CP_STATUS_CODE'
and    fcr.requested_start_date > sysdate
and    fcr.concurrent_program_id = fcp.concurrent_program_id
and    fcr.program_application_id = fcp.application_id
and    fcp.concurrent_program_id = fcpt.concurrent_program_id
and    fcpt.language = 'US'
and    fcp.application_id = fcpt.application_id
order by fcr.request_id desc;

Thanks to form FNDRSRUN.fmb, Trace/Tkprof and the following Metalink notes:

Now statuses and phases will be sorted!

Monday, September 10, 2007

Query: Concurrent Programs by User and Responsibility

Sometimes its a tough job putting all the entities together in Oracle Applications. The following query is a combination SQL behind the "Reponsibilities" for a given user on the Users form, and the list of values for the Request Name (Concurrent Program) on the "Submit Requests" form, for version (11i.10.2). This can be useful to provide an "Audit" of what concurrent requests/programs a given Oracle eBusiness Suite user or Responsibility has access to.

select distinct
,      r.responsibility_name
,      p.user_concurrent_program_name
,      a.application_name
,      a.application_short_name
,      p.concurrent_program_name
,      p.concurrent_program_id
,      p.application_id program_application_id
from fnd_concurrent_programs_vl p
,    fnd_application_vl a
,    fnd_request_group_units u
,    fnd_responsibility_vl r
,    fnd_user fu
,   (select user_id
     ,      responsibility_id
     ,      responsibility_application_id -- ,start_date,end_date
     from   fnd_user_resp_groups_indirect
     where (responsibility_id,responsibility_application_id)
        in (select responsibility_id, application_id
            from   fnd_responsibility
            where (version = '4' or 
                   version = 'W' or
                   version = 'M' or
                   version = 'H')
     and nvl(start_date,sysdate-1) <= sysdate
     and nvl(end_date,sysdate+1) > sysdate
     select user_id
     ,      responsibility_id
     ,      responsibility_application_id
     from   fnd_user_resp_groups_direct
     where (responsibility_id,responsibility_application_id)
        in (select responsibility_id, application_id
            from   fnd_responsibility
            where (version = '4' or
                   version = 'W' or
                   version = 'M' or
                   version = 'H'))
     and nvl(start_date,sysdate-1) <= sysdate
     and nvl(end_date,sysdate+1) > sysdate
     ) user_resps
where p.srs_flag in ('Y', 'Q')
and   p.enabled_flag = 'Y'
and   request_set_flag = 'N'
and (
      (a.application_id = u.unit_application_id
       and u.application_id = r.group_application_id
       and u.request_group_id = r.request_group_id
       and u.request_unit_type = 'A')
   or (p.application_id = u.unit_application_id
       and p.concurrent_program_id = u.request_unit_id
       and u.application_id = r.group_application_id
       and u.request_group_id = r.request_group_id
       and u.request_unit_type = 'P'))
and p.application_id = a.application_id
and user_resps.user_id = fu.user_id
and user_resps.responsibility_id = r.responsibility_id
and user_resps.responsibility_application_id = r.application_id
order by 1,2,4;
PS. This was my response to a question posted on Oracle Forums, a very handy place for information, and if you're searching for something, worthwhile doing a " xxxx" on Google!

Wednesday, September 05, 2007

Oracle E-Business Suite Performance World Record

Just picked up on an informative place for information on Oracle Applications performance benchmarks. The news article that caught my attention was that SGI Sets World Record for Oracle E-Business Suite Performance. I was particularly keen to note that:

The online benchmark exercises the user interface flows most frequently used by Oracle customers.

Looking at the detailed report shows the breakdown of areas put under pressure. Now every customer will be different, but its interesting to see what Oracle sees as its primary areas of use for the E-Business Suite.

Nice reading.

Tuesday, September 04, 2007

Top 8 Uses for Help Diagnostics Examine in eBiz

The Oracle eBusiness Suite / Applications provides a number of useful diagnostic and interrogative tools accessible from the front end. One of these tools is Examine accessible via Help > Diagnostics > Examine from the Oracle Forms interface.

My Top 8 Uses for Examine functionality:

1. Where does that field go? BLOCK.FIELD shows VALUE

The first thing you see on Examine is the Block, Field and Value of the cursor item. This is useful for working out which column in a table/view the field maps to (guess based on name of field), useful in conjunction with Help > About this record, and of course the value to confirm you've identified the right field

2. Let me change it! BLOCK.FIELD edit VALUE

Not only does Examine enable you to see a value, it also allows you to overwrite the value! Examine and get the BLOCK and FIELD you want to change, change it, Okay, and Save. Of course this will bypass any validation and may cause a lovely FRM error and break something, but if you really have the need and it works, then hey, I'm not going to stop you. Oracle Support might not like you anymore but that's your decision.

3. What the DFF? BLOCK.*_DF

If you've every wondered what the name of that descriptive field was on the item component widget handle, or whatever form, then wonder no more! Go to the block where your DFF is, Examine and chose the *_DF field in the same block. Voila! There's your descriptive flexfield name (and Application name in brackets).

4. What do I need to reference? BLOCK.FIELD

Context fields in a descriptive flexfield (DFF) require a reference to BLOCK.FIELD. No you've got an easy way to work it out.

5. What's that hidden value? BLOCK.FIELD (Hidden)

If a field is not displayed on the form, then Examine enables you to see the value. This is especially useful for developers when you want to view the value of a lookup code or similar field that you can't see on the front end.

6. What did I just do? SYSTEM.LAST_QUERY

This displays the last query run. Not always useful, but occasionally saves doing a trace via Help > Diagnostics > Trace.

7. What value was that profile option set to? $PROFILES$.PROFILE_OPTION_NAME

Examine and selecting block $PROFILES$ and then dropping down the FIELD list of values displays the internal names of the profile options set for your session. Helpful for checking the value of a profile option if you know what you're looking for.

8. What am I connected to? $ENVIRONMENT$.TWO_TASK

This shows you the Oracle SID of the database you are connected to.

Also worth a look:

Help > About this record: Shows the "Row Blame" information and of course the table/view that the block is based on.

Help > Diagnostics > Trace: Crucial in troubleshooting errors and performance issues as a separate weapon to profile option based trace enable.

Help > Properties > Item: Handy when you're looking to use Forms Personalizations

Help > Properties > Custom Code > On/Off: Very useful when you've developed extensions/customizations using CUSTOM.pll

Happy Examining!

Saturday, August 18, 2007

Document Attachments: Private Stuff

Noticed a few posts on Oracle forums about FND Attached Documents i.e. Oracle Applications/eBusiness Suite Attachments. Thought I'd post some unsupported code here just for reference, and a query for getting the attachments (text/documents) themselves.

How to attach a "Web Page" URL attachment to a Payables Invoice via FND Document Attachment Private API.

To use this code there are a few things that you'll need to change:
  1. In the fnd_global call change the variables to your required user_id, resp_id, resp_appl_id, or comment the call if not needed see my post if you're unsure of this
  2. The l_pk1_value which is ap_invoices_all.invoice_id
  3. The l_filename and l_description variables
  4. Possibly l_category_id: create an real example, check out examine and find the category_id you need to use.
  5. Remember to commit!
  6. Updated 6-Mar-2012: This code has been tested against, 12.0 and 12.1 (code differences noted in comments - only queries tested for R12.0/12.1), if you have a different version this may have changed. Note this is a customization via a private API so is unsupported.
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_media_id number;
l_category_id number := 291; -- Invoice Internal
l_pk1_value fnd_attached_documents.pk1_value%TYPE := '63227';
l_description fnd_documents_tl.description%TYPE   := 'Gareth Document Attach Demo';
l_filename fnd_documents_tl.file_name%TYPE := '';
l_seq_num number;
-- Setup applications session
-- Resp = Payables, Vision Operations
-- App  = Payables

select FND_DOCUMENTS_S.nextval
into   l_document_id
from   dual;

into   l_attached_document_id
from   dual;

select nvl(max(seq_num),0) + 10
into   l_seq_num
from   fnd_attached_documents
where  pk1_value = l_pk1_value
and    entity_name = 'AP_INVOICES';

( X_ROWID                        => l_rowid
, X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN            => fnd_profile.value('LOGIN_ID')
, X_DATATYPE_ID                  => 5 -- Web Page
, X_CATEGORY_ID                  => l_category_id
, X_SECURITY_TYPE                => 2
, X_PUBLISH_FLAG                 => 'Y'
, X_USAGE_TYPE                   => 'O'
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id

( X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN            => fnd_profile.value('LOGIN_ID')
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id

( X_ROWID                        => l_rowid
, X_ATTACHED_DOCUMENT_ID         => l_attached_document_id
, X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN            => fnd_profile.value('LOGIN_ID')
, X_SEQ_NUM                      => l_seq_num
, X_ENTITY_NAME                  => 'AP_INVOICES'
, X_COLUMN1                      => null
, X_PK1_VALUE                    => l_pk1_value
, X_PK2_VALUE                    => null
, X_PK3_VALUE                    => null
, X_PK4_VALUE                    => null
, X_PK5_VALUE                    => null
, X_DATATYPE_ID                  => 5
, X_CATEGORY_ID                  => l_category_id
, X_SECURITY_TYPE                => 2
, X_PUBLISH_FLAG                 => 'Y'
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id

Where to the actual attachments/documents get stored for attachments?

Here are some queries to get back the Payables Invoice attachments of category "Invoice Internal" for a given invoice. Attachments of type Web Page (URL or Filename)
select fad_f.seq_num
,      fdt_f.file_name media_data_text
from   fnd_attached_documents fad_f
,      fnd_documents fd_f
,      fnd_documents_tl fdt_f
,      fnd_document_categories_tl fdct_f
where  1 = 1
and    fad_f.document_id = fd_f.document_id
and    fad_f.document_id = fdt_f.document_id
and    fdct_f.category_id = fd_f.category_id
and    fdct_f.user_name = 'Invoice Internal'
and    fd_f.datatype_id = 5
and    fad_f.entity_name = 'AP_INVOICES'
and    fad_f.pk1_value = '63227';
Attachments of type "Long Text"
select fad_f.seq_num
,      fdlt_f.media_id   media_id
,      fdlt_f.long_text media_data_text
from   fnd_attached_documents fad_f
,      fnd_documents fd_f
,      fnd_documents_tl fdt_f
,      fnd_document_categories_tl fdct_f
,      fnd_documents_long_text fdlt_f
where  1 = 1
and    fad_f.document_id = fd_f.document_id
and    fad_f.document_id = fdt_f.document_id
and    fdct_f.category_id = fd_f.category_id
and    fdt_f.media_id = fdlt_f.media_id /* R11i */
-- and    fd_f.media_id = fdlt_f.media_id /* R12 */
and    fdct_f.user_name = 'Invoice Internal'
and    fad_f.entity_name = 'AP_INVOICES'
and    fad_f.pk1_value = '63227';
Attachments of type "Short Text"
select fad_f.seq_num
,      fdst_f.media_id   media_id
,      fdst_f.short_text media_data_text
from   fnd_attached_documents fad_f
,      fnd_documents fd_f
,      fnd_documents_tl fdt_f
,      fnd_document_categories_tl fdct_f
,      fnd_documents_short_text fdst_f
where  1 = 1
and    fad_f.document_id = fd_f.document_id
and    fad_f.document_id = fdt_f.document_id
and    fdct_f.category_id = fd_f.category_id
and    fdt_f.media_id = fdst_f.media_id /* R11i */
-- and    fd_f.media_id = fdst_f.media_id /* R12 */
and    fdct_f.user_name = 'Invoice Internal'
and    fad_f.entity_name = 'AP_INVOICES'
and    fad_f.pk1_value = '63227';
Attachments of type "File"
select fad_l.seq_num
,      fl_l.file_id      media_id
,      fl_l.file_data    media_data_blob
from   fnd_attached_documents fad_l
,      fnd_documents fd_l
,      fnd_documents_tl fdt_l
,      fnd_document_categories_tl fdct_l
,      fnd_lobs fl_l
where  1 = 1
and    fad_l.document_id = fd_l.document_id
and    fad_l.document_id = fdt_l.document_id
and    fdct_l.category_id = fd_l.category_id
and    fdt_l.media_id = fl_l.file_id /* Release 11i */
-- and fd_l.media_id = fl_l.file_id /* Release 12 */
and    fad_l.entity_name = 'AP_INVOICES'
and    fdct_l.user_name = 'Invoice Internal'
and    fad_l.pk1_value = '63227';
Update: Added extra line to file query for R12. Update: In case anyone needs to delete any attachments here's an example (thanks to Mark L):
l_entity_name varchar2(20):= 'PO_LINES';  -- This defines the PO_LINES entity
l_pk1_value varchar2 (20) := '200487';    -- PO_LINE_ID items are attached to
l_delete_document_flag varchar2 (1) := 'Y';    -- Delete the document
--fnd_global.apps_initialize(0,20420,1); -- SYSADMIN System Administrator
( X_entity_name                  => l_entity_name
, X_pk1_value                    => l_pk1_value
, X_delete_document_flag         => l_delete_document_flag
Happy Attaching!

Thursday, August 16, 2007

Best Practices Solutions: choose your moving parts carefully

Working with the Oracle eBusiness beast is a tad challenging given the breadth and complexity of its components. It uses a large majority of Oracle's core technology, both built and bought by Oracle. Integration throughout the technologies is fundamentally good and bound to get better with the whole Fusion soup. If there's something you need to provide a solution for, you have a myriad of options available to you without looking to that latest and greatest 3rd Party shiny thing. Or even that latest and greatest shiny thing within the tech stack.

The biggest workhorse with regards to language is PL/SQL but Java is making its presence felt. The number of Oracle Applications "Products" in use and available is increasing - both from a core technology stack perspective and an End User Application perspective.

This growth of moving parts leads to a necessity of choice:
What tool/language do I use given a custom development requirement?

Given my affinity to the intelligent or "thick" database paradigm, wherever possible I'll try to use the tool closest to the database.

What does this mean for me?
a) use SQL
b) use PL/SQL
c) use something else if you really have to

If you use SQL and PL/SQL wherever you can:

  • Identification of performance issues should be easier due to the wealth of excellent tools at this level, e.g. AWR, statspack, dbms_profiler
  • Integration issues should be easier to identify as you primarily have one place to look - the database
  • The "disconnects" between components can be avoided (your session context is preserved)
  • Code should be able to be consolidated to reduce complexity
  • The number of product experts needed should be reduced
  • Your licensing jigsaw puzzle should be simpler to arrange

Of course I'm over simplifying things here. There are situations where you can and must use another tool. However, the chances are that there will be something available to suit your needs in the Oracle eBusiness Suite Technology Stack. My point is even with additional tools, keep it simple and keep as much as possible in the database.

When you need to choose another tool, use the tool that:

  • Is already in use performing a task similar to what you need to do
  • Centralizes or consolidates the code and business rules
  • Implements a simple rather than a complex solution

Lets take a few rules I use relating to other tools, with a caveat that there will be times when these rules are contradicted. I use these examples as the red flag in front of the eBusiness bull:

  1. If you need to write a concurrent program, use PL/SQL
  2. If you need to schedule something, use the Concurrent Manager calling your PL/SQL program
  3. If you need a user interface, use the existing tech stack with business logic in the database wherever possible (I'll contradict myself upfront and say occasionally use Oracle APEX)
  4. If you need to write a report*, use BI Publisher (BIP/XMLP) or Oracle Reports, with the business rules in PL/SQL
  5. If you need an adhoc report*, use Discoverer
  6. If you need to send an email, use PL/SQL and the Workflow Notification Mailer (Java Mailer) API

* = check existing reporting capabilities before making this decision - standard reports, FSGs, RX reports, etc.

Another point I want to make in this post is a quick reference to hardcoding. In the past I was a very strong advocate on avoiding hardcoding, but a number of recent experiences have softened my view on this. Now I'd recommend avoiding hardcoding only when necessary ... another one of those contentious issues, a read of this is recommended.

I can hear you saying "Alright, guv, where you going with this then, ay?".

Well, I'm writing this both as a point of reference and a point of reflection. Originally I wanted to include a comment on open source, but it just didn't surface.

Keep an eye out though ;-)

PS. I'm not an Oracle employee, so I get no direct benefit from the inherently Oracle bias to this post. I just like keeping my ducks in a row ;-)

Wednesday, August 15, 2007

Audit Trail Must Do: Bank Accounts

If you are paying suppliers and other parties out of the Oracle eBusiness Suite via Oracle Payables and haven't performed the steps documented in Part 1 of this post, do it now! Even if you don't fully understand ...

This post details the steps required to implement the auditing of Banks, Bank Accounts and Bank Account Uses (Supplier to Bank Account Assignment), using standard Oracle Applications AuditTrail functionality. Auditing in this context means create, update and delete actions performed by authenticated Oracle eBusiness Suite Users. Specific columns only are tracked, with a full history from the completion of the setup steps.

The impact of performing these steps is:

  • You will be able to run AuditTrail Reports against the columns configured for auditing
  • New tables will be created: ap_bank_acccounts_all_a, ap_bank_branches_a, ap_bank_account_uses_all_a
  • New triggers will be created on the tables ap_bank_accounts_all, ap_bank_branches and ap_bank_account_uses_all that insert into the _all tables
  • There will be a very small storage and performance impact, but in the realm of your entire Oracle eBusiness Suite this impact is negligible and from where I sit, auditing of the items mentioned is a fundamental requirement.

Okay, enough mumble, onto the steps.

Part 1. Setup the AuditTrail Data Capture and Group

Note: Replace references to "Virtuate" with your company name/abbreviation - if you like ;-)

  1. System Administrator > Security > AuditTrail > Groups

  2. Create Group Virtuate Bank Accounts
    Application Name: Payables
    Audit Group: Virtuate Bank Accounts
    Group State: Enabled
    Add tables:
  3. System Administrator > Security > AuditTrail > Tables

  4. Query table name AP_BANK_ACCOUNTS_ALL
    Add row for column BANK_ACCOUNT_NUM
    Add row for column BANK_ACCOUNT_NAME
    Add rows for any other columns that you store critical bank account information in

    Query table name AP_BANK_BRANCHES
    Add row for column BANK_NUMBER
    Add row for column BANK_NUM
    Add rows for any other columns that you store critical bank account information in

    Query table name AP_BANK_ACCOUNT_USES%
    Add row for column EXTERNAL_BANK_ACCOUNT_ID
    Add row for column VENDOR_ID
    Add row for column VENDOR_SITE_ID
    Add row for column CUSTOMER_ID
    Add row for column CUSTOMER_SITE_USE_ID
    Add row for column START_DATE
    Add row for column END_DATE
    Add rows for any other columns that you store critical bank account information in

  5. System Administrator > Security > AuditTrail > Install

  6. Query User AP
    Check Audit Enabled if not already checked

  7. System Administrator > Requests > Run

  8. AuditTrail Update Tables
    (no parameters)
    Review Log to ensure no errors were encountered

  9. System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit

  10. Industry Template Create:
    Description: Virtuate Bank Accounts
    Functional Areas:
    Virtuate Bank Accounts
Please Note: If you subsequently make any changes to the above setup you need to rerun AuditTrail Update Tables

Part 2 (optional) Verify auditing of bank accounts is working

  1. Check rows in ap_bank_accounts_all_a

  2. sqlplus apps
    select * from ap_bank_accounts_all_a;

    should get no rows selected

  3. Payables > Setup > Payment > Banks

  4. Query an existing bank account, go to Bank Accounts
    Change bank account "Number" field and save

  5. Check rows in ap_bank_accounts_all_a

  6. sqlplus
    select * from ap_bank_accounts_all_a;

    Should see old value of changed record, plus other who and when columns etc.

  7. Payables > Setup > Payment > Banks

  8. Query the bank account from Step 7, go to Bank Accounts
    Revert the change made to bank account "Number" field in Step 2. and save

Part 3 Run Audit Report

  1. Run Test Report

  2. System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit Report
    Functional Group: Virtuate Bank Accounts
    Audit Table Name: AP_BANK_ACCOUNTS_ALL
    Transacted By: (your username)
    Click Select Columns
    Choose BANK_ACCOUNT_NUM in column 1
    Click Run Report
    View report to see the details of the changes made.

Other Notes

The way that Oracle stores audit information is to record only the changed columns, plus the who (username) when (timestamp) and type (create/update/delete). So getting the complete row information at a given point in time in history is a little tricky, as you need to tree walk back in time through the rows of the audit table.

The associated report is left to a future blog ... maybe a good starting point for a new BIP Report.

At least the Audit Hierarchy Navigator (System Administrator > AuditTrail > AuditTrail Reporting > Audit Hierarchy Navigator) is there to use for now albeit not that useful given the tracking based on primary key ID fields.

Now your auditors might be a bit happier, and if someone tries any trickery on the front end you'll have a history of it on the backend!