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