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 = #000000		Green = #008000
	Silver = #C0C0C0		Lime = #00FF00
	Gray = #808080		Olive = #808000
	White = #FFFFFF		Yellow = #FFFF00
	Maroon = #800000		Navy = #000080
	Red = #FF0000		Blue = #0000FF
	Purple = #800080		Teal = #008080
	Fuchsia = #FF00FF		Aqua = #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:

YELLOW	Safe	<?if:../../USER_NAME ="GROBERTS"?><xsl:attribute xdofo:ctx="block" name="background-color">yellow</xsl:attribute><?end if?>
6600CC	Not safe	<xsl:attribute xdofo:ctx="block" name="background-color">#6600cc</xsl:attribute>
rgb(210,105,30) chocolate	Not safe	<xsl:attribute xdofo:ctx="block" name="background-color">rgb(210,105,30)</xsl:attribute>
hsl(30,100,50) orange	Not 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!

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
declare
 org_date date := '28-JAN-03';
 cur_date date := '01-JAN-04';
 l_status varchar2(1);
 l_count number := 0;
 l_pays  number := 0;
begin
 while true loop
  l_count := l_count+1;
  begin
   select 'Y' yes
   into   l_status
   from   dual
   where 
      (
        (to_number(to_char(org_date,'DD')) <= 28 and
         to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
        )
        or
        ( to_number(to_char(org_date,'DD')) = 29 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            or
            ( 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)
            )
          )
        )
        or
        ( to_number(to_char(org_date,'DD')) = 30 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            or
            ( 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)
            )
          )
        )
        or
        ( to_number(to_char(org_date,'DD')) = 31 and
          ( to_number(to_char(org_date,'DD')) = to_number(to_char(cur_date,'DD'))
            or
            ( 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;
  exception
  when no_data_found then
   null;
  end;
  if l_count > 366 then
   exit;
  end if;
  cur_date := cur_date + 1;
 end loop;
 dbms_output.put_line('Payments = '||l_pays);
end;
/

Comments more than welcome!

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
Short Name: XXXX_FNDSCURS
Application: Application Object Library
Check Include Incompatible Programs
Check Include Parameters
Click OK
Change the Output Format to XML
Save

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

3. Run the request
System Administrator > Requests > Run
Name: Active Users - BIP
Submit
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).

Username	Resp	Start	End
<?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
Code: XXXX_FNDSCURS
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
Code: XXXX_FNDSCURS
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.
Submit

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. http://myappserver.mydomain.com

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

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
             ,'P',decode(fcr.hold_flag
                        ,'Y','Inactive'
                        ,fl_p.meaning
                        )
             ,fl_p.meaning
             ) phase
,      decode(fcr.phase_code
             ,'P',decode(fcr.hold_flag
                        ,'Y','On Hold'
                        ,decode(sign(fcr.requested_start_date - sysdate)
                               ,1,'Scheduled'
                               ,fl_s.meaning)
                  )
             ,fl_s.meaning
             ) 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:

• ANALYZEREQ.SQL 134035.1
• ANALYZEPENDING.SQL 134033.1
• Status Code ... in FND_CONCURRENT_REQUESTS 1054419.6

Now statuses and phases will be sorted!

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 11.5.10.2 (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
       fu.user_name
,      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
     union
     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 "site:forums.oracle.com xxxx" on Google!

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.

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!