You will get query output in an Excel format using PLSQL package/procedure.
For this you need to follow below steps and get an Excel output:
Also need Oracle application to register created package/procedure.
--Step 01) Write a query:
Here I have wrote Employee Details related query.
Below query used in Cursor of procedure.
Exmaple Query:
SELECT fu.user_name
, emp.title
, emp.first_name
, emp.last_name
, emp.full_name
, pptt.user_person_type
, pbg.name business_group_name
, emp.person_id
, emp.effective_start_date
, emp.effective_end_date
, emp.email_address
, emp.date_of_birth
, emp.employee_number
, emp.internal_location
FROM per_all_people_f emp
, fnd_user fu
, per_person_types_tl pptt
, per_business_groups pbg
WHERE emp.person_type_id = pptt.person_type_id
AND emp.business_group_id = pbg.business_group_id
AND emp.person_id = fu.employee_id
AND pptt.language = 'US'
AND (pbg.date_to IS NULL OR pbg.date_to > SYSDATE)
AND (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND emp.person_id = NVL(p_employee_number,emp.person_id);
Note: p_employee_number -- is a optional Parameter, used in Concurrent Program.
-- If you need to get data of employee wise then enter employee number.
-- Otherwise you will get data of all active employees.
--Step 02) Write Package Specification:
CREATE OR REPLACE PACKAGE xx_excel_utility_pkg
IS
PROCEDURE print_header_tag_proc(p_mode IN VARCHAR2);
PROCEDURE print_worksheet_tags_proc(p_mode IN VARCHAR2,
p_col_count IN NUMBER,
p_sheetname IN VARCHAR2
);
PROCEDURE begin_row_proc;
PROCEDURE end_row_proc;
PROCEDURE print_data_proc(p_datatype IN VARCHAR2,
p_data IN VARCHAR2,
p_mode IN VARCHAR2 DEFAULT 'N'
);
PROCEDURE print_doc_properties_tag_proc;
PROCEDURE print_workbook_tag_proc;
PROCEDURE print_style_proc;
PROCEDURE print_style1_proc;
--
PROCEDURE xx_employee_data_excel_proc(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_employee_number IN VARCHAR2
);
END xx_excel_utility_pkg;
/
--Step 03) Write Package Body:
CREATE OR REPLACE PACKAGE BODY xx_excel_utility_pkg
IS
g_file UTL_FILE.file_type;
g_report_output VARCHAR2(10):= 'EXCEL';
g_delimiter VARCHAR2(1) := '|';
PROCEDURE print_header_tag_proc(p_mode IN VARCHAR2)
IS
BEGIN
IF (p_mode = 'H')
THEN
fnd_file.put_line(fnd_file.output,'<?xml version="1.0"?>');
fnd_file.put_line(fnd_file.output,'<?mso-application progid="Excel.Sheet"?>');
fnd_file.put_line(fnd_file.output,'<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"');
fnd_file.put_line(fnd_file.output,'xmlns:o="urn:schemas-microsoft-com:office:office"');
fnd_file.put_line(fnd_file.output,'xmlns:x="urn:schemas-microsoft-com:office:excel"');
fnd_file.put_line(fnd_file.output,'xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"');
fnd_file.put_line(fnd_file.output,'xmlns:html="http://www.w3.org/TR/REC-html40">');
ELSE
fnd_file.put_line (fnd_file.output,'</Workbook>');
END IF;
END;
-------------------------------
PROCEDURE print_worksheet_tags_proc(
p_mode IN VARCHAR2,
p_col_count IN NUMBER,
p_sheetname IN VARCHAR2
)
IS
BEGIN
IF p_mode = 'H' THEN
fnd_file.put_line(fnd_file.output,'<Worksheet ss:Name="' || p_sheetname || '">');
fnd_file.put_line(fnd_file.output,'<Table ss:ExpandedColumnCount="'
|| p_col_count|| '" x:FullColumns="1" x:FullRows="1">'
);
ELSE
fnd_file.put_line(fnd_file.output, '</Table>');
fnd_file.put_line(fnd_file.output, '</Worksheet>');
END IF;
END;
-------------------------------
PROCEDURE begin_row_proc
IS
BEGIN
IF g_report_output = 'EXCEL'
THEN
fnd_file.put_line (fnd_file.output, '<Row>');
END IF;
END;
-------------------------------
PROCEDURE end_row_proc
IS
BEGIN
IF g_report_output = 'EXCEL'
THEN
fnd_file.put_line (fnd_file.output, '</Row>');
ELSE
fnd_file.new_line (fnd_file.output);
END IF;
END;
-------------------------------
PROCEDURE print_data_proc (
p_datatype IN VARCHAR2,
p_data IN VARCHAR2,
p_mode IN VARCHAR2 DEFAULT 'N'
)
IS
BEGIN
IF g_report_output = 'EXCEL' THEN
IF p_mode = 'N' THEN
fnd_file.put_line (fnd_file.output, '<Cell><Data ss:Type="'
|| p_datatype
|| '">' || TRANSLATE (p_data, '<>', '()')
|| '</Data></Cell>'
);
ELSE
IF p_mode = 'S2' THEN
fnd_file.put_line (fnd_file.output,
'<Cell ss:StyleID="s21"><Data ss:Type="'
|| p_datatype || '">'
|| TRANSLATE (p_data, '<>', '()')
|| '</Data></Cell>'
);
ELSE
fnd_file.put_line (fnd_file.output,
'<Cell ss:StyleID="s24"><Data ss:Type="'
|| p_datatype || '">'
|| TRANSLATE (p_data, '<>', '()')
|| '</Data></Cell>'
);
END IF;
END IF;
ELSE
fnd_file.put (fnd_file.output, p_data || g_delimiter);
END IF;
END;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE print_doc_properties_tag_proc
IS
v_user_name VARCHAR2 (240);
BEGIN
BEGIN
SELECT full_name INTO v_user_name
FROM per_all_people_f
WHERE person_id = fnd_global.employee_id;
EXCEPTION
WHEN OTHERS THEN
v_user_name := NULL;
END;
--
fnd_file.put_line (fnd_file.output,
'<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">'
);
fnd_file.put_line (fnd_file.output, '<Author>'
||v_user_name||' ,'
||fnd_global.user_name
||'</Author>'
);
fnd_file.put_line (fnd_file.output, '<LastAuthor>'
||v_user_name|| ' ,'
||fnd_global.user_name
||'</LastAuthor>'
);
fnd_file.put_line (fnd_file.output, '<Created>'
||TO_CHAR (SYSDATE, 'YYYY-MM-DD')
||'T'
||TO_CHAR (SYSDATE, 'HH24:MI:SS')
||'Z'
||'</Created>'
);
fnd_file.put_line (fnd_file.output, '<Company>Cummins Inc.</Company>');
fnd_file.put_line (fnd_file.output,
'<Description>'
|| 'Report run on '
|| SYSDATE
|| ' by '
|| v_user_name
|| '</Description>'
);
fnd_file.put_line (fnd_file.output, '<Version>11.5606</Version>');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '</DocumentProperties>');
END;
------------------------------------------------------------------------------------------------------------------
PROCEDURE print_workbook_tag_proc
IS
BEGIN
fnd_file.put_line
(fnd_file.output,
'<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">'
);
fnd_file.put_line (fnd_file.output, '<WindowHeight>9855</WindowHeight>');
fnd_file.put_line (fnd_file.output, '<WindowWidth>15180</WindowWidth>');
fnd_file.put_line (fnd_file.output, '<WindowTopX>480</WindowTopX>');
fnd_file.put_line (fnd_file.output, '<WindowTopY>45</WindowTopY>');
fnd_file.put_line (fnd_file.output, '<ActiveSheet>1</ActiveSheet>');
fnd_file.put_line (fnd_file.output,
'<ProtectStructure>False</ProtectStructure>'
);
fnd_file.put_line (fnd_file.output,
'<ProtectWindows>False</ProtectWindows>'
);
fnd_file.put_line (fnd_file.output, '</ExcelWorkbook>');
END;
------------------------------------------------------------------------------------------------------------------
PROCEDURE print_style_proc
IS
BEGIN
fnd_file.put_line (fnd_file.output,'<Styles>');
fnd_file.put_line (fnd_file.output,'<Style ss:ID="Default" ss:Name="Normal">');
fnd_file.put_line (fnd_file.output,'<Alignment ss:Vertical="Bottom"/>');
fnd_file.put_line (fnd_file.output,'<Borders/>');
fnd_file.put_line (fnd_file.output,'<Font/>');
fnd_file.put_line (fnd_file.output,'<Interior/>');
fnd_file.put_line (fnd_file.output,'<NumberFormat/>');
fnd_file.put_line (fnd_file.output,'<Protection/>');
fnd_file.put_line (fnd_file.output,'</Style>');
fnd_file.put_line (fnd_file.output,'<Style ss:ID="s23">');
fnd_file.put_line (fnd_file.output,'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>');
fnd_file.put_line (fnd_file.output,'<Font x:Family="Swiss" ss:Bold="1"/>' );
-- fnd_file.put_line (fnd_file.output,'<Font ss:FontName="Times New Roman" x:Family="Roman" ss:SIZE="25" ss:Bold="1"/>');
fnd_file.put_line (fnd_file.output,'<Interior ss:Color="#ffb3b3" ss:Pattern="Solid"/>');
fnd_file.put_line (fnd_file.output,'</Style>');
fnd_file.put_line (fnd_file.output,'<Style ss:ID="s21">');
fnd_file.put_line (fnd_file.output,'<Font ss:SIZE="9"/>');
fnd_file.put_line (fnd_file.output,'</Style>');
FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'</Styles>');
END;
-------------------------------------------------------------------------------------------------------------------
PROCEDURE print_style1_proc
IS
BEGIN
fnd_file.put_line(fnd_file.output,'<Styles>');
fnd_file.put_line(fnd_file.output,'<Style ss:ID="Default" ss:Name="Normal">');
fnd_file.put_line(fnd_file.output,'<Alignment ss:Vertical="Bottom"/>');
fnd_file.put_line(fnd_file.output,'<Borders/>');
fnd_file.put_line(fnd_file.output,'<Font/>');
fnd_file.put_line(fnd_file.output,'<Interior/>');
fnd_file.put_line(fnd_file.output,'<NumberFormat/>');
fnd_file.put_line(fnd_file.output,'<Protection/>');
fnd_file.put_line(fnd_file.output,'</Style>');
fnd_file.put_line(fnd_file.output,'<Style ss:ID="s24">');
fnd_file.put_line(fnd_file.output,'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>');
fnd_file.put_line(fnd_file.output,'<Font x:Family="Swiss" ss:Bold="1"/>');
fnd_file.put_line(fnd_file.output,'<Interior ss:Color="#ffb3b3" ss:Pattern="Solid"/>');
fnd_file.put_line(fnd_file.output,'</Style>');
fnd_file.put_line(fnd_file.output,'<Style ss:ID="s21">');
fnd_file.put_line(fnd_file.output,'<Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>');
fnd_file.put_line(fnd_file.output,'<Font x:Family="Swiss" ss:Bold="1"/>');
fnd_file.put_line(fnd_file.output,'<Interior ss:Color="#9999CC" ss:Pattern="Solid"/>');
fnd_file.put_line(fnd_file.output,'</Style>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</Styles>');
END;
--
PROCEDURE xx_employee_data_excel_proc
(errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_employee_number IN VARCHAR2
)
IS
CURSOR lcur_employee_details
IS
SELECT fu.user_name
, emp.title
, emp.first_name
, emp.last_name
, emp.full_name
, pptt.user_person_type
, pbg.name business_group_name
, emp.person_id
, emp.effective_start_date
, emp.effective_end_date
, emp.email_address
, emp.date_of_birth
, emp.employee_number
, emp.internal_location
FROM per_all_people_f emp
, fnd_user fu
, per_person_types_tl pptt
, per_business_groups pbg
WHERE emp.person_type_id = pptt.person_type_id
AND emp.business_group_id = pbg.business_group_id
AND emp.person_id = fu.employee_id
AND pptt.language = 'US'
AND (pbg.date_to IS NULL OR pbg.date_to > SYSDATE)
AND (fu.end_date IS NULL OR fu.end_date > SYSDATE)
AND emp.person_id = NVL(p_employee_number,emp.person_id);
--
BEGIN
apps.fnd_file.put_line(apps.fnd_file.LOG,'------- Employee Details Report ---------');
apps.fnd_file.put_line(apps.fnd_file.LOG,' ');
apps.fnd_file.put_line(apps.fnd_file.LOG,'Report Run Date and Time : '|| TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));
xx_excel_utility_pkg.print_header_tag_proc ('H');
xx_excel_utility_pkg.print_doc_properties_tag_proc;
xx_excel_utility_pkg.print_workbook_tag_proc;
xx_excel_utility_pkg.print_style1_proc;
xx_excel_utility_pkg.print_worksheet_tags_proc ('H', 500, 'REPORT_DATA');
xx_excel_utility_pkg.begin_row_proc;
xx_excel_utility_pkg.print_data_proc ('String', 'User Name', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Title', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'First Name', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Last Name', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Full Name', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'User Person Type', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Business Group Name', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Person ID', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Effective Start Date', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Effective End Date', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Email Address', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Date Of Birth', 'S');
xx_excel_utility_pkg.print_data_proc ('String', 'Internal Location','S');
xx_excel_utility_pkg.end_row_proc;
FOR lreq_rec IN lcur_employee_details
LOOP
fnd_file.put_line(fnd_file.LOG,'Employee Number: '||p_employee_number);
xx_excel_utility_pkg.begin_row_proc;
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.user_name);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.title);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.first_name);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.last_name);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.full_name);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.user_person_type);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.business_group_name);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.person_id);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.effective_start_date);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.effective_end_date);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.email_address);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.date_of_birth);
xx_excel_utility_pkg.print_data_proc ('String', lreq_rec.internal_location);
xx_excel_utility_pkg.end_row_proc;
END LOOP;
--
xx_excel_utility_pkg.print_worksheet_tags_proc ('F', NULL, 'REPORT_DATA');
xx_excel_utility_pkg.print_header_tag_proc ('F');
END xx_employee_data_excel_proc;
END xx_excel_utility_pkg;
/
Note: Compile Package specification and body without error and register in Oracle application.
--Step 04) Create Concurrent Executable, Program and attach to respected responsibility Group to Run program:
--1) Create Executable:
--2) Create Program:
Note: Program Output is HTML
--3) Program assign to Responsibility Group.
--4) Run Program and check program Output.
Thanks....
Thank you
ReplyDelete