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

  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!

References:

93 comments:

Unknown said...

Sweeeeeet. Will add this to my bag of tricks, fantastic and easy to use tool.

Arivazhagan said...

Great. I did all the steps mentioned by you. But when i 'View Output' it opens in default text viewer. When i do Tools>Copy File the browser pops only to close immediately. Are there any other setups (Viewer Options, Client Setup) that i need to perform.

Ari

Gareth said...

Hi Ari,
Set your profile option "Viewer - Text" to browser and ensure your Oracle Applications server is in your IE Security "Local Intranet" zone or switch off popup blocking.
Gareth

Arivazhagan said...

Hi Gareth
Amazing solution this. I had already disabled popup blocking, included the App Server in my trusted sites. Now it works fine.
Thanks a million
Ari

Unknown said...

Great! Question -
When I open the csv output in Excel, it is showing data for all columns in one single column.

If I choose to save and then rename the file as .csv, Excel opens it fine again.

This might be because the file still has .txt as extention to start with. Is there a way to over write the output file name extension as .csv?

Arivazhagan said...

Hi Gareth
While i am able to get Excel output without BIP(using sylk), i am uanble to achieve the same with BIP. We have XMLP 5.6.3 EBS 11.5.10.2
I get different error in different dev instances
1. No Data found error upon clicking view output button
2. Cannot view XML input using XSL style sheet

What is even more puzzling is, i did manage to get excel output with BIP in the 1st dev instance where now i get a no data found error
Appreciate your help
ARI

Gareth said...

Hi Ariv,
If you've got it working in one instance, check the setup and patches applied.
1. No data found issue: check you've got your template setup correctly with same code as data definition.
2. Not sure what the problem is with your stylesheet.
Regards,
Gareth

Gareth said...

Hi Basky,
The SYLK solution here is not CSV here but XLS file extension. Make sure you've run the update to set the mime type to 'application/vnd.ms-excel' then the file should be a .xls file and open correctly.
Regards,
Gareth

Arivazhagan said...

Hello Gareth
Things are fine after applying ATG.6.0 Patch. Same Report, Same Template.
Thanks for your help
Ariv

Unknown said...

Hi,
I did all the steps and getting an Error Like this:
Cause: FDPSTP failed due to ORA-06533: Subscript beyond count
ORA-06512: at "APPS.OWA_SYLK_APPS", line 30
ORA-06512: at "APPS.OWA_SYLK_APPS", line 264
ORA-06512: at "APPS.XXXV8_USERS_SYLK_PKG", line 14
ORA-0651
Any Idea...How to solve this or where to fix this...

Gareth said...

Hi Nagarjun,
As discussed caused by error in your owa_sylk_apps.sql.
Gareth

Badma Prakash said...

Hi all,
I did all the steps as mentioned above. I'm getting following error.

Cause: FDPSTP failed due to ORA-06533: Subscript beyond count
ORA-06512: at "APPS.OWA_SYLK_APPS", line 30
ORA-06512: at "APPS.OWA_SYLK_APPS", line 264
ORA-06512: at "APPS.XXXV8_USERS_SYLK_PKG", line 14
ORA-0651

I did all the above 3 three steps change in OWA_SYLK_APPS.sql.

o rename it to owa_sylk_apps
o Change it to use fnd_file.put_line(fnd_file.output instead of utl_file.put_line(g_file
o Remove parameters for p_file

pl help me out.

thanks

badma

Gareth said...

Hi Badma,

As per previous comment, caused by error in your owa_sylk_apps.sql

Get the source here:
owa_sylk_apps.sql

Regards,
Gareth

Badma Prakash said...

Hi gareth,

Amazing Solution. It worked.

Thanks a lot for your support.

Wih regards,
badma

Suthan said...

Hi Gareth,
Thanks for a useful post.

When we open the output of the concurrent program, it is providing the option only to save and cancel.
What needs to be done in order to get the open option?

Thanks,
Suthan,

Gareth said...

Hi Suthan,

Make sure you have .xls setup with "Open" option. It should give you this open by default, but maybe your administrator has restricted this. In Windows Explorer go to Tools, Folder Options and click on the File Types tab. Scroll down until you see XLS and make sure the application associated with it is Excel. After clicking on Advanced, included in my list of Actions I have Open, and if I click on that and choose Edit I have:
Action: &Open
Application: "C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE" /e
Use DDE: checked
DDE Message: [open("%1")]
Application: Excel

Gareth

Suthan said...

Hi Gareth,
Thanks. I could open the pcl file in excel.
However, I was required to set the file type option for .PCL, since the output type is set as PCL.
Wonder, what makes your output to be generated as .xls.
Only difference from your setting is I have two viewer options for PCL (Excel and HP..)

Thanks,
Suthan,

Gareth said...

Hi Suthan,

That was the point of updating the mime type:
set mime_type = 'application/vnd.ms-excel'

Think you should remove the excel one and update the HP PCL one to be excel. But only if you're not using the HP PCL one!

Gareth

Unknown said...

Hi,
I did all the steps and getting an Error Like this:
Cause: FDPSTP failed due to ORA-00923: FROM keyword not found where expected
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "APPS.OWA_SYLK_APPS", line 22
ORA-06512


pl help me out...

Gareth said...

Hi Swapnali,
Something wrong in your code select statement.
Drop me the code if you want and I'll take a look.
Gareth

Unknown said...
This comment has been removed by the author.
Gareth said...

Hi Swapnali,
Use '' or ''' to make one ' in the code depending on how you're writing it. Try it out.
Gareth

Unknown said...

Thanks Gareth...
Code is working fine using ''.

Unknown said...

Hi Gareth,
Sorry to bother u again.
I am getting error:
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1567
ORA-06512: at "SYS.DBMS_SQL", line 621
ORA-06512: at "APPS.OWA_SYLK_APPS", line 265

Can u please help me in this.
My query is:




'SELECT '||
'msib.organization_id, '||
'mc.segment1 item_category, '||
'papf.full_name buyer, '||
'pov.vendor_name, '||
'poh.closed_code po_status, '||
'msib.segment1 mfg_code, '||
'msib.segment2 part_number, '||
'pol.unit_meas_lookup_code pack_code_uom, '||
'poh.segment1 po_number, '||
'poh.creation_date po_date, '||
'pllp.due_date, '||
'pol.quantity quantity_ordered, '||
'pola.quantity_received, '||
'(pol.quantity-pola.quantity_received) back_order_qty, '||
'NULL open_qty, '||
'poh.attribute1 Source, '||
'haou.name org_dc, '||
'(SELECT COUNT(1) no_of_rec FROM RCV_TRANSACTIONS abc '||
'WHERE transaction_type = ''RECEIVE'' '||
'AND source_document_code = ''PO'' '||
'AND abc.po_header_id =poh.po_header_id '||
'AND abc.po_line_id = pol.po_line_id '||
'GROUP BY po_header_id) no_of_rec, '||
'(SELECT MIN(TRUNC(TRANSACTION_DATE)) FROM RCV_TRANSACTIONS abc1 '||
'WHERE transaction_type = ''RECEIVE'' '||
'AND source_document_code = ''PO'' '||
'AND abc1.po_header_id =poh.po_header_id '||
'AND abc1.po_line_id = pol.po_line_id '||
'GROUP BY po_header_id) first_rec_date, '||
'(SELECT MAX(TRUNC(TRANSACTION_DATE)) FROM RCV_TRANSACTIONS abc1 '||
'WHERE transaction_type = ''RECEIVE'' '||
'AND source_document_code = ''PO'' '||
'AND abc1.po_header_id =poh.po_header_id '||
'AND abc1.po_line_id = pol.po_line_id '||
'GROUP BY po_header_id) last_rec_date, '||
'(pola.creation_date - pola.need_by_date) exp_lead_time, '||
'(pola.creation_date - (SELECT MIN(TRUNC(TRANSACTION_DATE)) FROM RCV_TRANSACTIONS abc1 '||
' WHERE transaction_type = ''RECEIVE'' '||
' AND source_document_code = ''PO'' '||
' AND abc1.po_header_id =poh.po_header_id '||
' AND abc1.po_line_id = pol.po_line_id '||
' GROUP BY po_header_id)) act_lead_time, '||
'(pola.quantity_received/pol.quantity * 100) percentage_received, '||
'(pol.quantity * pol.unit_price) dollars_amt_ordered, '||
'(pola.quantity_received * pol.unit_price) dollar_amt_received, '||
'((pola.quantity_received * pol.unit_price)/(pol.quantity * pol.unit_price) * 100) '||
' dollar_percentage_received '||
'FROM '||
'po_headers poh, '||
'po_lines pol, '||
'po_agents poa, '||
'mtl_system_items_b msib, '||
'hr_organization_units haou, '||
'mtl_item_categories mic, '||
'mtl_categories mc, '||
'mtl_category_sets mcs, '||
'po_vendors pov, '||
'per_people_f papf, '||
'po_line_locations pola, '||
'po_line_locations_print pllp '||
'WHERE poh.po_header_id = pol.po_header_id '||
'AND pol.item_id = msib.inventory_item_id '||
'AND poh.agent_id = poa.agent_id '||
'AND msib.inventory_item_id = mic.inventory_item_id '||
'AND mic.category_id = mc.category_id '||
'AND mc.structure_id = mcs.structure_id '||
'AND pola.PO_HEADER_ID = poh.po_header_id '||
'AND pola.po_line_id = pol.po_line_id '||
'AND mcs.category_set_name LIKE ''Inventory'' '||
'AND poh.vendor_id = pov.vendor_id '||
'AND poa.agent_id = papf.person_id '||
'AND pllp.po_header_id = poh.po_header_id '||
'AND pllp.po_line_id = pol.po_line_id '||
'AND haou.ORGANIZATION_ID = msib.organization_id '||
'AND mic.organization_id = msib.organization_id '||
'AND msib.organization_id = NVL(p_organization, msib.organization_id) '||
'AND mc.segment1 = NVL(p_item_category, mc.segment1) '||
'AND papf.full_name = NVL(p_byer_name, papf.full_name) '||
'AND pov.vendor_name = NVL(p_vendor_name, pov.vendor_name) '||
'AND poh.attribute1 = p_source '||
'AND poh.creation_date BETWEEN p_creation_date_from AND NVL(p_creation_date_to, SYSDATE) '||
'AND poh.closed_code = p_status '

Unknown said...

hi gareth,
I have a doubt regarding passing the parametres from owa_sylk.show()to my SQL query.

Is there any limitation to pass the parameters because If I pass more than 4 parameters it shows nothing in EXCEL file.

Will u please look in to this?

Gareth said...

Hi Swapnali,

You need to study and understand the way the PL/SQL and output is working. There isn't any imposed limit on parameters. If there was you have the source and could change it.

In your prior comment you have lines like:
'AND poh.closed_code = p_status '
In this case p_status is a parameter and so you need to put the bind parameter in its place, like:
'AND poh.closed_code = :P_STATUS '
and then ensure P_STATUS is in the parameters array passed to owa_sylk_apps like:
p_parm_names => owa_sylk_apps.owaSylkArray( 'P_STATUS' ),
p_parm_values => owa_sylk_apps.owaSylkArray('CLOSED'),

Gareth

Unknown said...

Hi
I am getting the following error
ORACLE error 1003 in FDPSTP

Cause: FDPSTP failed due to ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1657
ORA-06512: at "SYS.DBMS_SQL", line 621
ORA-06512: at "APPS.OWA_SYLK_APPS", line 234
ORA-06512: at "APPS.OWA_SYLK_APPS", li

--
My code is


owa_sylk_apps.show(
p_query => 'SELECT
gcc.segment4 "Product",
FFV.Description "Product Description",
gcc.segment2 "Revaluation Account",
cch.padded_item_number "Item",
cch.update_description "Update Description",
cch.standard_cost "Unit Cost",
cch.MATERIAL,
cch.MATERIAL_OVERHEAD "Material Overhead",
cch.RESOURCE_COST "Resource",
cch.OUTSIDE_PROCESSING "Outside Processing",
(cch.standard_cost- cch.INV_ADJ_VAL_C) "Old Item Cost",
cch.INV_ADJ_VAL_C "Inventory(Adj.Value)",
cch.Intransit_ADJ_VAL_C "Intransit(Adj. Value)",
cch.WIP_ADJ_VAL_C "WIP(Adj. Value)",
cch.INV_ADJ_qty_C "Inventory(Adj. Qty)",
cch.Intransit_ADJ_qty_C "Intransit(Adj. Qty)",
cch.WIP_ADJ_qty_C "WIP(Adj. Qty)"
from CST_COST_HISTORY_V cch,
GL_CODE_COMBINATIONS gcc,
MTL_SYSTEM_ITEMS_B msi,
FND_FLEX_VALUES_VL FFV
where cch.inventory_item_id = :l_item
and cch.inventory_item_id = msi.INVENTORY_ITEM_ID
and cch.organization_id = msi.organization_id
and cch.organization_id = :l_organization_id
And gcc.code_combination_id = msi.cost_of_sales_account
and ffv.FLEX_VALUE_MEANING = gcc.segment4',
p_parm_names =>
owa_sylk_apps.owaSylkArray( 'l_organization_id','l_item'),
p_parm_values =>
owa_sylk_apps.owaSylkArray(p_organization_id,p_item));

Thanks & Regards
Navaz

Venky said...

Hi Gareth,

This is an amazing post. I have actually come across the need to create excel output from non-BIP CPs and this article seesm to be the one I'm looking for !! I havent tried the steps yet !!! Will bug you once I get my hands dirty !! [:)] ..Thanks very much for the help in advance!!

vidhyasaras said...

Hi Gareth,

this is a totally different question. My CP can have multiple output types (based on the XMLP template given).
So setting output type = PDF in concurrent program definition gives the error 'file does not begin with PDF' when trying to view text output and vice-versa.
This is in EBS R12.

Please help.

Gareth said...

Hi Vidhya,

Can you explain more, not sure I understand. Drop me an email if you want, see my profile.

Regards,
Gareth

劉雲 said...
This comment has been removed by the author.
劉雲 said...

Hi.

You are so nice.

it's good and easy solution.

but,why the solution limit row <= 10000?!

It's excel limit?! or other reson?!

Best Regards,
ArseneFang

Gareth said...

Good question ... not sure of the answer - increase the max_rows and let us know how you get on!

Gareth

劉雲 said...

Yes,I try modify the max_rows = 20000,but it always got 10000 records. :-(

劉雲 said...

Hi,I was stupid and should not be modified in owa_sylk_apps PACKAGE p_max_rows DEFAULT = 20000, should be specified when the call owa_sylk_apps.show p_max_rows it. Ha ha!

Martin said...

when i click in 'View Output' button it opens in the browser directly. I want open it in the default text viewer.

Gareth said...

Hi Martin,

For excel, output needs to open in the browser.

Gareth

Martin said...

Hi Gareth, I need to open the output in the default viewer, but this opening in the browser.
Thanks.

Gareth said...

Hi martinfcosta,
Why? If you want to view text only change the Viewer: Text profile option.

Gareth

Martin said...

The "Viewer: Text" profile option is blank at all levels.

Dmitri Grishin said...

Another way to do it.
For example: Output file ZIP, mime type 'application/zip'

Just create trigger "before insert" type for table FND_FILE_TEMP

CREATE OR REPLACE TRIGGER xx_fnd_file_temp_bi
BEFORE INSERT ON fnd_file_temp
FOR EACH ROW
DECLARE
BEGIN
IF upper(:NEW.MIME_TYPE) = upper('application/zip')
THEN
:NEW.TRANSFER_MODE := 'BINARY';
END IF;
END;
/

vamsi said...

how can i get excel output format from oracle apps reports without using xml/bi publisher.is there any easy method.plz its urgent

Gareth said...

Hi Vamsi,

Did you read this post? It is outputting Excel without using BIP.

Regards,
Gareth

Unknown said...

I getting following error
Cause: FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.OWA_SYLK_APPS", line 48

I created table
create table xxkns.XXKNSARR0050_AGING_ADHOC
(customer_name varchar2(50),
organization_name varchar2(30),
invoice_Number varchar2(30),
country varchar2(80),
customer_number varchar2(30),
invoice_date varchar2(8),
invoice_currency varchar2(15),
Original_Invoice_Amt number,
Inv_Exchange_Rate number,
Invoice_amt number,
Business_Unit varchar2(20),
Po_Number varchar2(15),
Credit_Limit number,
Payment_Term varchar2(15),
Due_Date varchar2(8),
sales_person varchar2(20),
Date_Late number,
Balance_Due_USD number,
Current_USD number,
Days_1_30_USD number,
Days_31_60_USD number,
Days_61_90_USD number,
Days_90_120_USD number,
Days_121_180_USD number,
Days_181_365_USD number,
Days_G365_USD number,
ACCOUNT_no varchar2(50))

..

Expected output rows more than 7000

Unknown said...

Hi

I getting the following error

Cause: FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.OWA_SYLK_APPS", line 48
ORA-06512: at "APPS.OWA_SYLK_APPS", line 144
ORA-06512: at "APPS.OWA_SYLK_AP



owa_sylk_apps.show(
p_query => 'SELECT customer_name ,
organization_name ,
invoice_Number ,
country,
customer_number "Customer#",
invoice_date,
invoice_currency Cur_Code,
Original_Invoice_Amt,
Inv_Exchange_Rate ,
Invoice_amt,
Business_Unit BU,
Po_Number "PO#",
Credit_Limit,
Payment_Term ,
Due_Date ,
sales_person ,
Date_Late ,
Balance_Due_USD "Balance_Due(USD)",
Current_USD "Current(USD)",
Days_1_30_USD "Days_1_30(USD)",
Days_31_60_USD "Days_31_60(USD)",
Days_61_90_USD "Days_61_90(USD)",
Days_90_120_USD "Days_90_120(USD)",
Days_121_180_USD "Days_121_180(USD)",
Days_181_365_USD "Days_181_365(USD)",
Days_G365_USD "> 365 Days(USD)",
ACCOUNT_no "Account No"
from XXKNSARR0050_AGING_ADHOC
where rownum < 1500
order by 2,1,3',
p_parm_names => owa_sylk_apps.owaSylkArray( 'l_organization_id'),
p_parm_values => owa_sylk_apps.owaSylkArray(p_org_id));
End;



How to fix

Gareth said...

Hi md,

Make sure you're not hitting line length maximum, or varchar maximum (32767).

Number of rows shouldn't be a problem, as it will be pushing each line out to file.

Of course be aware of Excels row limit in earlier versions.

Regards,
Gareth

Unknown said...

Hi

Thanks for your update.
line length and number of rows is not problem.

Only problem when adding the following cloumn

po_number varchar2(50)

Any restriction for special character in data?



Cause: FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.OWA_SYLK_APPS", line 48


Please advise

Gareth said...

Hi md,

OK, good to hear you solved your problem due to your custom code.

For special characters, you'll have to give it a try. Check the SYLK defintion for details, but it's probably similar to RTF, so should be okay.

Regards,
Gareth

Unknown said...

Hi

I have problem adding the following cloumn but the data have a special character

po_number varchar2(50)




Cause: FDPSTP failed due to ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "APPS.OWA_SYLK_APPS", line 48

please advise

Gareth said...

Hi md,

Replace the special character using replace or substring or translate in your SQL.

Regards,
Gareth

Unknown said...

Hi Gareth,

Thanks for great solution.
I am having on queston.
Instead of updating "Viewer Option" of PCL, can we create our own viewer option.So that whenver i need output in excel format i need to add that output format to my concurrent request.
If yes please suggetest how.

Swati

Gareth said...

Hi Swati,
I'm not sure you can create new output formats. Give it a try and let us know! Create the viewer as copy of PCL output format, then set output of your concurrent request to that output, and try!
Regards,
Gareth

Unknown said...

Hi Gareth,

The solution works for me. Only problem faced is I am getting one additional blank column at the starting of the output. Tell me how to modify the code to avoid that.

Unknown said...

Hello Gareth,
thank you for your script,but i met a problem about the character set ,my company 's ebs 's character is chinese ,
so when i run code you gave,it could not show right the data,how can i do it?

Gareth said...

Hi,

I haven't tried with Chinese characterset, guess you'd be better to use XML Publisher which has full language, translation via xliff etc.

Regards,
Gareth

Alfred Martin said...

Hi Gareth,
Excellent tip!.I have a similar requirement.We have several CSV reports which are built based on oracle reports.Basically they output data with data delimited by ';'.The user runs the report and opens the o/p and saves on his PC as a txt file.Then they open the file using excel manually and excel uses the delimiter to seperate the data into various columns in excel.

Now the users want excel to automatically open the CP o/p.I tried changing the CP definition output type as PCL(did'nt do anyting with the SYLK) and updated the fnd_mime_types_tl.PCL record to have the mime_type as 'application/vnd.ms-excel'.But it does'nt work...Can you think of a means of doing this?

Thanks
Alfred

Gareth said...

Hi Alfred,

Does your browser open other Excel documents directly in Excel? If not you might need to associate application/vnd.ms-excel with Excel.

Regards,
Gareth

Alfred Martin said...

Hi Gareth,
Yes the browser opens other excel documents and I do indeed have the
association application/vnd.ms-excel with Excel.

On another front I had tried your SYLK approach on a new report that needs to open up in excel and it sort of works...in the sense when i click on 'view output' the file save dialog box opens up and it tries to save the file with a .pcl extension(with the application as ms-excel).But no matter whether i save it as PCL or.xls it successfully opens the file in excel, which is good!.But i was just wondering why it tries to save the file as .PCL instead of .xls. Is it because the concurrent program definition has its output type set as PCL?

Thanks
Alfred

Gareth said...

Hi Alfred,

I think in the comments above Suthan had same issue when both the HP PCL and Excel options present - check out removing one of them.

Regards,
Gareth

Alfred Martin said...

Hi Gareth,
I just have one entry for PCL and it points to ms-excel.Guess I can live with it,but ideally the users would like to see the o/p file with a .xls extension rather than a.pcl one.

Thanks
Alfred

Utopia said...

Hey Gareth, that was a great solution , it worked fine with me but :) ..
i tested the solution on a Matrix Report but the output wasn't as desired .. so please if you have any clue regarding the Matrix reports not just the tabular ones , i would be very thankful..
Thank You in Advance...
Amr

Alfred Martin said...

Hi Gareth,
One of the columns in the report output column is a number field.When I open the output pcl file in excel and try and manipulate the data such as do a sum on the number column it always returns zero.However if I save the file as .csv and try and open it in excel and then do a sum/avg of the columns it returns correct values.Looks like something that the SYLK format is messing with.I have even tried to_number against the column in the query and even format cells in excel to use number does'nt help.Any ideas?
Thanks
Alfred

Gareth said...

Hi Alfred,

A couple of possibilities:
1. There is a space in the field, do an ltrim(rtrim(to_number(x))). Whenever you move data across systems in files such as this solution the numbers will always be in their text representation.
2. Probably not the issue, but check that in Excel you have automatic calculations on, Tools, Options, Calculations, Automatic.

Regards,
Gareth

Alfred Martin said...

Hi Gareth,
I tried both options to no avail.
did you have similar problems on numeric columns when u generate excel output using SYLK/.pcl?

Thanks
Alfred

Gareth said...

Hi Alfred,

Try saving the file as Excel Workbook .xls after it opens in Excel then reattempt calculations - haven't specifically tried.

Regards,
Gareth

Unknown said...

Hi,

As mentioned in this blog we have created concurrent program wit output type as PCL and all the other steps including UPDATE MIME TYPE to get the output in .XLS.

But we have a problem here,
We are able to open the output as .pcl.xls format, but when we save the file the format is in .PCL only.
What could be the reason for this.?
How to resolve this issue? We need the output should be saved in .XLS instead of .PCL format.

Thanks
Alaka

none said...

I avoided the update to the seeded PCL entry. Instead, I created a XLS viewer type with mime type = application/vnd.ms-excel, and added an entry to fnd lookup type = "CP OUTPUT FILE TYPE". By adding a entry in the fnd lookup type, it shows up in the conc. prg definition output format LOV. With all this, the file opens up with the .xls entension.

Note: This works well in EBS R12.0.6

Alexis said...

When I was filling one table something happened...And all data in this file was deleted.I didn't know what to do and accidentally used google and found-.xlsx recovery.Tool solved these problems in 30 seconds and made it for free.Besides program demonstrated recover all these graphics, interviews, tables and reports in Microsoft Excel format.

Unknown said...

Hi Gareth

One of the column in the report is japanese data . when i open the excel it wiil appear some junk characters are appearing meaning( In Japanese Data, some junk characters are appearing)

Gareth said...

Hi md,
Please refer to this page specifically where it states: Note that even if a SYLK file is created by an application that supports Unicode (for example Microsoft Excel), the SYLK file will be encoded in the current system's ANSI code page, not in Unicode. If the application contained characters that were displayable in Unicode but have no codepoint in the current system's code page, they will be converted to question marks ('?') in the SYLK file.

You should check your windows code page. I think I read this as meaning you need to be running Japanese code page, ie Japanese Windows, to see Japanese characters.

Regards,
Gareth

Unknown said...

Hi Gareth,

Thanks a bunch for this tool, we've been working with it for alittle over 6 months now.
However we've got some problemes wich i can't seem to tackle, maybe you've got some ideas.
Every now and then the output is missing the first 50 something records. Like these ;
ID;ORACLE
P;FCourier New;M200
P;FCourier New;M200;SB
P;FCourier New;M200;SUB
F;C1;FG0R;SM1
F;C2;FG0R;SM0
F;C3;FG0R;SM0
F;C4;FG0R;SM0
....
....
C;X23;K"RETURN_TO_AP"
C;X24;K"REJECTED"
C;X25;K"NR_DAYS_OPEN_RESP"
C;X26;K"COMMENTS"
C;Y3

and then starts somewhere in the middle of a record returning results.

Now "normal output looks like this:
C;X3;K"Bijlhout, I.A. - RT(62090117)"
C;X4;K"2851"
C;X5;K"12524504"
C;X6;K""
C;X7;K""
C;X8;K""
C;X9;K"A"
C;X10;K""
C;X11;K""
C;X12;K"4775000"
C;X13;K"PAINT IT BLUE MAGAZINES"

and the falty returns :

"C;X5;K""12540039"""
"C;X6;K"""""
"C;X7;K"""""
"C;X8;K"""""
"C;X9;K""A"""
"C;X10;K"""""
"C;X11;K"""""
"C;X12;K""3700000"""

So there's something with the " but i don't know where they are comming from.

Do you have any suggestions ?

Regards,
Rob.

Gareth said...

Hi Rob,

Haven't heard of this. If its reproducible then that should be easy to debug and work out what is happening. Maybe the file is getting a piece taken out in transit - have you checked the file contents are the same as what the database thinks its writing? (Copy contents to a table or something)...

Sorry, not sure if this helps, drop me an email with more details if you want.

Regards,
Gareth

Kamlesh said...

I have tried same example in Oracle 11i For some reason I am getting Following error.

**Starts**02-JUN-2011 15:59:27
ORACLE error 6533 in FDPSTP

Cause: FDPSTP failed due to ORA-06533: Subscript beyond count
ORA-06512: at "APPS.CBFIN_OWA_SYLK", line 26
ORA-06512: at "APPS.CBFIN_OWA_SYLK", line 260
ORA-06512: at "APPS.CBFIN_USERS_SYLK_PKG", line 14
ORA-06512: at line 1

I have seen earlier post but I did not get any answer of that.

procedure show(
p_query in varchar2,
p_parm_names in owaSylkArray default owaSylkArray(),
p_parm_values in owaSylkArray default owaSylkArray(),
p_sum_column in owaSylkArray default owaSylkArray(),
p_max_rows in number default 10000,
p_show_null_as in varchar2 default null,
p_show_grid in varchar2 default 'YES',
p_show_col_headers in varchar2 default 'YES',
p_font_name in varchar2 default 'Courier New',
p_widths in owaSylkArray default owaSylkArray(),
p_titles in owaSylkArray default owaSylkArray(),
p_strip_html in varchar2 default 'YES' ) is
begin
show( p_cursor => build_cursor( p_query,
p_parm_names,
p_parm_values ),
p_sum_column => p_sum_column,
p_max_rows => p_max_rows,
p_show_null_as => p_show_null_as,
p_show_grid => p_show_grid,
p_show_col_headers => p_show_col_headers,
p_font_name => p_font_name,
p_widths => p_widths,
p_titles => p_titles,
p_strip_html => p_strip_html );
end show;
--
end cbfin_owa_sylk;
/
show error

Grateful Pilgrim said...

hi Gareth, With this excel output can be viewed from the Internet Explorer, it will work if the URL of the Oracle Applications is added to the Internet Options -> Local Intranet site.

What if we don't want to define the Local Intranet site so that our user does not have to change their internet options for security purpose? What is the alternative to this?

Gareth said...

Hi Artistic Enthusiast,
Not sure on the options, but do they get an option to download the file? If so then download and then open from Excel.

Regards,
Gareth

N N RAJU said...

Hi,

How to get all the rows in Excel output for XML report, even it has million records.
At present am able to get around 1 lakh records. but my requirement is to print all the records which are around one million records.

N N Raju

MLV said...

How to Set Excel as Concurrent Report Output Format in FORM FNDCPMCP? [ID 1298657.1]

Check out this note it talks about Setting up XLS without hi jacking PCL

SAI said...

Hi Gareth

Great post.., however im being limited to only 123 rows in excel for some reason.

Any ideas?

Many thanks,
Sai

Unknown said...

Hi
How to accept the special character & Spanish character(DiƱas) while creating excel file,Please guide me .

Rds
Vicky

Unknown said...

Hi
Please guide me how can i generate the report along with special characters & Spanish .
rds
vicky

Gareth said...

Hi Vigneswaran S,

Ensure your database is running UTF8. Not sure if SYLK can handle multibyte characters - if not, try BI Publisher Excel Template.

Regards,
Gareth

Unknown said...

Thank you Mr.Gareth for reply
Our database is running UTF8 only.I thing owa_sylk_apps PCKG not allowing. BI Publisher taking long time to run & file size is big , that is the reason we are moved to SYLK solution ,
its working for me for all other reports , But only employee report got failed due to Spanish letter & Special char, But we need to include both characters in our reports . please guide me.

Gareth said...

Hi Vicky,
Please try BI Publisher *native* excel template, or ETEXT and you should find filesize is fine. Not RTF / Excel - that is XHTML which will be big.

Regards,
Gareth

Zafar said...

Hi Gareth,

The above post on Excel file output from Oracle Applications concurrent request using SYLK. aka Look Ma, no BIP!" has been quite useful.

I have an another question here, when we say view log or view output in oracle applications, the log file opens up in a window(same as srs window)

What setup we need to do so that the view log or view output will automatically open the output in a browser.

pls advise.

Thanks,
Zafar.

Zafar said...

Pls ignore the earlier question, got the answer in the earlier post above.

"Set your profile option "Viewer - Text" to browser"

SenthilNathan Ranganathan said...
This comment has been removed by the author.
SenthilNathan Ranganathan said...

Hi Gareth,

Is there any way using this same logic to print data in excel with seperate sheet. In this u have mentioned how to print in single tab, but we have to do in seperate tabs in a single excel. Is there any way?

Thanks,
Senthilnathan R

Gareth said...

Hi Senthilnathan R,
BI Publisher has ability to put data to separate worksheets.
I'm not sure about SYLK - would need to check the definition.

Regards,
Gareth

Zafar said...

Pasting the related link for BI Publisher related to multiple sheets
https://blogs.oracle.com/xmlpublisher/entry/multi_sheet_excel_output

Unknown said...

Hi,

I have one issue when i am generating excel output from PLSQL code. Output is getting generated properly and we are to view it too but when we are sending the same as email attachment to user using delivery options in SRS window, the output is going as .out file. Is there a way we can send .xls/.xml output from delivery options directly to user instead of .out file.

Regards,
Vihang

Gareth said...

Hi Vihang,
The mime type setting in step 4 should handle that.
Regards,
Gareth

Unknown said...

Hi Gareth,

I tried step 4 but it doesn't help. We have already added one viewer option and we are able to get the excel output when clicked on View Output. Issue is when we send mail using Delivery Options in SRS window, in the attachment of the mail it comes as .out file instead of xml/xls file and so we are not able to open the attachment just by double click. We need to always do open with Microsoft Excel and view it.

We added viewer option as - File Format Code - XML, MIME_TYPE - application/vnd.ms-excel

Regards,
Vihang

Gareth said...

Hi Vihang,

Delivery options will send the file as created by the concurrent program, so you'll need to change the approach. I suggest using XML Publisher native Excel template instead of the SYLK method.

Regards,
Gareth