How to create Excel with multiple worksheets

As many of you might have come across a requirement like business wants to open the report output in Excel file.

There is no brainier in doing this, generate the report output and save the file with .csv extension; Problem solved. Occasionally it becomes tricky when the user request the output in single Excel file with multiple worksheets. This can be achieved in many ways.

Here is a simple method which I follow to achieve this functionality; As I more cautious on cost :) 

We can achieve this functionality by generating the output in XML file format.
Let’s first see the how XML structure look like for a excel output. If you can generate the output file in the below structure, then you can achieve multiple worksheets in a single Excel file. The choice of programming language is up to you.

<?xml version="1.0"?>  
<?mso-application progid="Excel.Sheet"?> 
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
 xmlns:o="urn:schemas-microsoft-com:office:office" 
 xmlns:x="urn:schemas-microsoft-com:office:excel" 
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" 
 xmlns:html="http://www.w3.org/TR/REC-html40"> 
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> 
  <LastAuthor>Surendra</LastAuthor> 
  <Created>2009-05-11T06:30:00Z</Created> 
  <LastSaved>2009-06-16T00:50:38Z</LastSaved> 
  <Version>12.00</Version>   
 </DocumentProperties> 
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> 
  <WindowHeight>10005</WindowHeight> 
  <WindowWidth>10005</WindowWidth> 
  <WindowTopX>120</WindowTopX> 
  <WindowTopY>135</WindowTopY> 
  <ProtectStructure>False</ProtectStructure>  
  <ProtectWindows>False</ProtectWindows> 
</ExcelWorkbook> 
 <Styles> 
  <Style ss:ID="Default" ss:Name="Normal"> 
  <Alignment ss:Vertical="Bottom"/>  
  </Style> 
 </Styles>  
<Worksheet ss:Name="Sheet q"> 
<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">  
<Row ss:AutoFitHeight="0" ss:Height="13.5">
<Cell>
<Data> 
</Data>
</Cell>
</Row> 
    </Worksheet>
</Workbook>

Here is a sample SQL*Plus script, this script to print TNAME, TABTYPE from table TAB. To show the multiple worksheets concept, i printed the same output in both the worksheets. You can run the script as is.


This script output is Excel_Ouput.xml, double click this file to open in Excel, you should be able to view 2 worksheets name First Work Sheet, Second Work Sheet. As a bonus in this script I added few color coding tags too. 


Have a look at it and use it as you like.
/* Begin SQL*Plus script */

Set heading off
Set feedback off
Set verify off
Set linesize 1000
Set pagesize 0
Set space 0
Set newpage 1
Set trimspool on
Set Termout off


SPOOL  EXCEL_OUTPUT.xml;
SELECT
'<?xml version="1.0"?> ' 
||CHR(10)||
'<?mso-application progid="Excel.Sheet"?> '
||CHR(10)||
'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" '
||CHR(10)||
' xmlns:o="urn:schemas-microsoft-com:office:office" '
||CHR(10)||
' xmlns:x="urn:schemas-microsoft-com:office:excel" '
||CHR(10)||
' xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" '
||CHR(10)||
' xmlns:html="http://www.w3.org/TR/REC-html40"> '
||CHR(10)||
' <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"> '
||CHR(10)||
'  <LastAuthor>Wipro Technologies</LastAuthor> '
||CHR(10)||
'  <Created>2009-05-11T06:30:00Z</Created> '
||CHR(10)||
'  <LastSaved>2009-06-16T00:50:38Z</LastSaved> '
||CHR(10)||
'  <Version>12.00</Version>  ' 
||CHR(10)||
' </DocumentProperties> '
||CHR(10)||
' <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"> '
||CHR(10)||
'  <WindowHeight>10005</WindowHeight> '
||CHR(10)||
'  <WindowWidth>10005</WindowWidth> '
||CHR(10)||
'  <WindowTopX>120</WindowTopX> '
||CHR(10)||
'  <WindowTopY>135</WindowTopY> '
||CHR(10)||
'  <ProtectStructure>False</ProtectStructure>  '
||CHR(10)||
'  <ProtectWindows>False</ProtectWindows> '
||CHR(10)||
' </ExcelWorkbook> '
||CHR(10)||
' <Styles> '
||CHR(10)||
'  <Style ss:ID="Default" ss:Name="Normal"> '
||CHR(10)||
'   <Alignment ss:Vertical="Bottom"/>  '
||CHR(10)||
'  </Style> '
||'  <Style ss:ID="s77"> '
||CHR(10)||
'   <Interior ss:Color="#00B050" ss:Pattern="Solid"/> '
||CHR(10)||
'  </Style> '
||CHR(10)||
' </Styles> ' 
FROM DUAL;
/* Work Sheet 1 */
SELECT '<Worksheet ss:Name="First Work Sheet"> '
||CHR(10)||
'<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">  '
||CHR(10)||
'<Row ss:AutoFitHeight="0" ss:Height="13.5">'
||CHR(10)||
'<Cell><Data ss:Type="String">'||'TNAME'||'</Data></Cell>'||CHR(10)||
'<Cell><Data ss:Type="String">'||'TABTYPE'||'</Data></Cell>'||CHR(10)||
'</Row>' 
FROM Dual;
SELECT  '<Row ss:AutoFitHeight="0" ss:Height="13.5">'||CHR(10)||
'<Cell><Data ss:Type="String">'||A.TNAME||'</Data></Cell>'||CHR(10)||
'<Cell ss:StyleID="s77"><Data ss:Type="String">'||A.TABTYPE||'</Data></Cell>'||
'</Row>'
FROM (SELECT   TNAME,TABTYPE FROM  TAB) A;
SELECT '</Table> ' 
||CHR(10)||
'</Worksheet>'
FROM DUAL;
/* Work Sheet 2 */
SELECT '<Worksheet ss:Name="Second Work Sheet"> '
||CHR(10)||
'<Table x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">  '
||CHR(10)||
'<Row ss:AutoFitHeight="0" ss:Height="13.5">'
||CHR(10)||
'<Cell><Data ss:Type="String">'||'TNAME'||'</Data></Cell>'||CHR(10)||
'<Cell><Data ss:Type="String">'||'TABTYPE'||'</Data></Cell>'||CHR(10)||
'</Row>' 
FROM Dual;
SELECT  '<Row ss:AutoFitHeight="0" ss:Height="13.5">'||CHR(10)||
'<Cell><Data ss:Type="String">'||A.TNAME||'</Data></Cell>'||CHR(10)||
'<Cell><Data ss:Type="String">'||A.TABTYPE||'</Data></Cell>'||
'</Row>'
FROM (SELECT   TNAME,TABTYPE FROM  TAB) A;
SELECT '</Table> ' 
||CHR(10)||
'</Worksheet>'
FROM DUAL;
SELECT '</Workbook>'  FROM DUAL;
SPOOL OFF;
exit;

/* End SQL*Plus script */

Oracle EBS R12 Payments

I attended Oracle e-Seminar Release 12 Payments. It's Very nice, you can see the recording via the given below URL's.


OPN Link: http://oukc.oracle.com/static05/opn/login/?r=-1&c=633758419&t=checkusercookies


External Link: http://download.oracle.com/opndocs/americas/67053.html


Thanks.

Personalizing and Extending OA Framework Applications

I attended Oracle e-Seminar sometime back; Basically it covers Personalizing and Extending OA

Framework Applications. Very good seminar, you can see the recording via the given below URL's.

OPN Link: http://oukc.oracle.com/static05/opn/login/?r=-1&c=601040777&t=checkusercookies

External Link: http://download.oracle.com/opndocs/americas/65621.html

Thanks.

Few URL's for Quick reference

Here are few URL's I can use for Quick reference for ERP related stuff.



http://www.dba-oracle.com/ 
http://www.oracleappshub.com/
http://viratoracleapps.wordpress.com/
http://oracally.blogspot.com/
http://oracleapps88.blogspot.com/
http://oraclearea51.com


Will keep adding if found any useful stuff.

Java 7 - Programming Language Enhancements

Java lovers, Hope you got to see the Java 7 Launch event webcast on 7th July. What a co-incidence Java 7 lauchned on July 7th, 7-7-7.
Unlike Java 6, This time there were couple Java Programming Language Enhancements in Java SE 7. 
Here see below couple of intersting enhancements.

• Strings in switch
• Try-with-resources statement
• Diamond syntax for constructors
• Multi-catch and more precise rethrow

Let's see more in detail the first enhancement.


Strings in switch


switch statement is Java’s multiway branch statement. It provides an easy way to dispatch execution to different parts of your code based on the value of an expression. As such, it often provides a better alternative than a large series of if-else-if statements.


Syntax:
switch (expression) {
case value1:
// statement sequence
break;
case value2:
// statement sequence
break;
...
case valueN:
// statement sequence
break;
default:
// default statement sequence
}


Until Java SE 6, The expression must be of type byte, short, int, or char only; each of the values specified in the case statements must be of a type compatible with the expression. Each case value must be a unique literal.


Now in Java SE 7, you can use a String object in the expression of a switch statement. The switch statement compares the String object in its expression with the expressions associated with each case label as if it were using the String.equals method; consequently, the comparison of String objects in switch statements is case sensitive. 


The Java compiler generates generally more efficient bytecode from switch statements that use String objects than from chained if-then-else statements.


Example:


public String getTypeOfDayWithSwitchStatement(String dayOfWeekArg) {
     String typeOfDay;
     switch (dayOfWeekArg) {
         case "Monday":
             typeOfDay = "Start of work week";
             break;
         case "Tuesday":
         case "Wednesday":
         case "Thursday":
             typeOfDay = "Midweek";
             break;
         case "Friday":
             typeOfDay = "End of work week";
             break;
         case "Saturday":
         case "Sunday":
             typeOfDay = "Weekend";
             break;
         default:
             throw new IllegalArgumentException("Invalid day of the week: " + dayOfWeekArg);
     }
     return typeOfDay;
}


Let's discuss about the other enhancements in my next blog.

SQL Scripts to analyze Oracle eBS

While working in Oracle eBS, It will be very usefull to have your  set up.
Even though you have access to click through the available setup screens, it will be time consuming and also sometimes you may not have the right responsibilities. Here have I have posted some of the scripts, I have collected. Hope this helps and reduces your effort.

 1. Responsibilities Listing
 2. Menus Listing
 3. Submenu and Function Listing
 4. User and Assigned Responsibility Listing
 5. Responsibility and assigned request group listing
 6. Profile option with modification date and user
 7. Forms personalization Listing
 8. Patch Level Listing
 9. Request attached to responsibility listing
10. Request listing application wise
11. Count Module Wise Reports
12. Request Status Listing
13. User and responsibility listing
14. Applied Patch Listing


1. Responsibilities Listing
Purpose/Description: Retrieve a list of all responsibilities.

SELECT
(SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id)
application
, frt.responsibility_id
, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;


2. Menus Listing
Purpose/Description: To see the Menus associated with a given responsibility
 
SELECT DISTINCT
a.responsibility_name
, c.user_menu_name
FROM
apps.fnd_responsibility_tl a
, apps.fnd_responsibility b
, apps.fnd_menus_tl c
, apps.fnd_menus d
, apps.fnd_application_tl e
, apps.fnd_application f
WHERE
a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = 50103
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = ‘US’;

3. Submenu And Function Listing
Purpose/Description: By using this query you can check function and submenus attached to a specific menu

SELECT
c.prompt
, c.description
FROM
apps.fnd_menus_tl a
, fnd_menu_entries_tl c
WHERE
a.menu_id = c.menu_id
AND a.user_menu_name = ‘Navigator Menu – System Administrator GUI’;


4.User and Assigned Responsibility Listing
Purpose/Description: You can use this query to check responsibilities assigned to users.

SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE
g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY
SUBSTR (user_name, 1, 30)
, SUBSTR (a.application_name, 1, 50)
, SUBSTR (r.responsibility_name, 1, 60);


5. Responsibility and assigned request group listing
Purpose/Description: To find responsibility and assigned request groups. Every responsibility contains a request group (The request group is basis of submitting requests)

SELECT
responsibility_name responsibility
, request_group_name
, frg.description
FROM
fnd_request_groups frg
, fnd_responsibility_vl frv
WHERE
frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

6. Profile option with modification date and user
Purpose/Description: Query that can be used to audit profile options.

SELECT
t.user_profile_option_name
, profile_option_value
, v.creation_date
, v.last_update_date
, v.creation_date – v.last_update_date "Change Date"
, (SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By"
, (SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM
fnd_profile_options o
, fnd_profile_option_values v
, fnd_profile_options_tl t
WHERE
o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = ‘B’
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = ‘B’)
ORDER BY user_profile_option_name;

7. Forms personalization Listing
Purpose/Description: To get modified profile options.

SELECT
ffft.user_function_name "User Form Name"
, ffcr.SEQUENCE
, ffcr.description
, ffcr.rule_type
, ffcr.enabled
, ffcr.trigger_event
, ffcr.trigger_object
, ffcr.condition
, ffcr.fire_in_enter_query
, (SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By”
FROM
fnd_form_custom_rules ffcr
, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;


8. Patch Level Listing
Purpose/Description: Query that can be used to view the patch level status of all modules

SELECT
a.application_name
, DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status
, patch_level
FROM
apps.fnd_application_vl a
, apps.fnd_product_installations b
WHERE
a.application_id = b.application_id;


9. Request attached to responsibility listing
Purpose/Description: To see all requests attached to a responsibility

SELECT
responsibility_name
, frg.request_group_name
, fcpv.user_concurrent_program_name
, fcpv.description
FROM
fnd_request_groups frg
, fnd_request_group_units frgu
, fnd_concurrent_programs_vl fcpv
, fnd_responsibility_vl frv
WHERE
frgu.request_unit_type = ‘P’
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;


10. Request listing application wise
Purpose/Description: View all request types application wise
  
SELECT
fa.application_short_name
, fcpv.user_concurrent_program_name
, description
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, output_file_type
, program_type
, printer_name
, minimum_width
, minimum_length
, concurrent_program_name
, concurrent_program_id
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
ORDER BY description;
 
11. Count Reports per module
Purpose/Description: To Count Reports

SELECT
fa.application_short_name
, DECODE (fcpv.execution_method_code
,’B', ‘Request Set Stage Function’
,’Q', ‘SQL*Plus’
,’H', ‘Host’
,’L', ‘SQL*Loader’
,’A', ‘Spawned’
,’I', ‘PL/SQL Stored Procedure’
,’P', ‘Oracle Reports’
,’S', ‘Immediate’
,fcpv.execution_method_code) exe_method
, COUNT (concurrent_program_id) COUNT
FROM
fnd_concurrent_programs_vl fcpv
, fnd_application fa
WHERE
fcpv.application_id = fa.application_id
GROUP BY
fa.application_short_name
, fcpv.execution_method_code
ORDER BY 1;

12. Request Status Listing
Purpose/Description: This query returns report/request processing time

SELECT
f.request_id
, pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date
, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ‘ HOURS ‘ ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ‘ MINUTES ‘ ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ‘ SECS ‘ time_difference
, DECODE(p.concurrent_program_name
,’ALECDC’
,p.concurrent_program_name||’['||
f.description||']‘
,p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code
,’R',’Running’
,’C',’Complete’
,f.phase_code) Phase
, f.status_code
FROM
apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE
f.concurrent_program_id = p.concurrent_program_id
AND f.program_application_id = p.application_id
AND f.concurrent_program_id = pt.concurrent_program_id
AND f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
AND f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;


13. User and responsibility listing
Purpose/Description: Check responsibilities assigned to users

SELECT UNIQUE
u.user_id
, SUBSTR (u.user_name, 1, 30) user_name
, SUBSTR (r.responsibility_name, 1, 60) responsiblity
, SUBSTR (a.application_name, 1, 50) application
FROM
fnd_user u
, fnd_user_resp_groups g
, fnd_application_tl a
, fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
–AND a.application_name like ‘%Order Man%’
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

14. Applied Patch Listing
Purpose/Description: Check Current Applied Patches

SELECT
patch_name
, patch_type
, maint_pack_level
, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC

Java Code Examples

This site Example Depot holds all the examples from The Java Developers Almanac and more. Copy and paste these examples directly into your applications. Over a thousand useful examples can be found in this site.

Your Career in Management

During my free time, I do spend sometime on few management related sites. The below one looks very interesting, nice and simple.

Expert Advice for Your Career in Management.

Management trainee is a site that provides comprehensive information on moving into management. While many of the subjects are relevant and helpful for anyone in a management career, the contents on this site have been written with specific focus on the management trainee.

Enjoy reading.