Using XLS template for BI Publisher rather than RTF

We can create XLS template rather than RTF template. This is very useful if we are trying to develop BI publisher reports with huge number of columns.

Limitations of Excel Templates

The following are limitations of Excel templates:

  1. For reports that split the data into multiple sheets, images are not supported. If the template sheet includes images, when the data is split into multiple sheets, the images will show only on the first sheet.
  2. There is no tool to facilitate the markup of the template with BI Publisher tags; all tags must be manually coded. Some features require the use of XSL and XSL Transformation (XSLT) specifications

Prerequisites

Following are prerequisites for designing Excel templates:

  • Microsoft Excel 2003 or later. The template file must be saved as Excel 97-2003 Workbook binary format (*.xls).
  • To use some of the advanced features, the report designer will need knowledge of XSL and XSLT.
  • The report data model has been created.

To code this design as a template, mark up the cells with the XDO_ defined names to map them to data elements. The cells must be named according to the following format:

  • Data elements: XDO_?element_name?

    where

    XDO_ is the required prefix and

    ?element_name? is either:

    • the XML tag name from your data delimited by “?”
    • a unique name that you will use to map a derived value to the cell

    For example: XDO_?EMPLOYEE_ID?

  • Data groups: XDO_GROUP_?group_name?

    where

    XDO_GROUP_ is the required prefix and

    • ?group_name? is the XML tag name for the parent element in your XML data delimited by “?”.
    • a unique name that you will use to define a derived grouping logic

    For example: XDO_GROUP_?DEPT?

    Note that the question mark delimiter, the group_name, and the element_name are case sensitive.

Applying a Defined Name to a Cell

  1. Click the cell in the Excel worksheet.
  2. Click the Name box at the left end of the formula bar. The default name will display in the Name box. By default, all cells are named according to position, for example: A8.
  3. In the Name box, enter the name using the XDO_ prefix and the tag name from your data. For example: XDO_?EMP_NAME?
  4. Press Enter.

    The following figure shows the defined name for the Employee Name field entered in the Name box:

the picture is described in the document text

  1. For the total salary field, a calculation will be mapped to that cell. For now, name that cell XDO_?TOTAL_SALARY?. The calculation will be added later.

After you have entered all the fields, you can review the names and make any corrections or edits using the Name Manager feature of Excel. Access the Name Manager from the Formulas tab in Excel as shown:

the picture is described in the document text

After you have named all the cells for this example, the Name Manager dialog will appear as shown:

the picture is described in the document text

Understanding Groups

A group is a set of data that repeats for each occurrence of a particular element. In the sample template design, there are two groups:

  • For each occurrence of the <EMPS> element, , the employee’s data (name, e-mail, telephone, salary) will display in the worksheet.
  • For each occurrence of the <DEPT> element, the department name and the list of employees belonging to that department will display.

In other words, the employees are “grouped” by department and each employee’s data is “grouped” by the employee element. To achieve this in the final report, add grouping tags around the cells that are to repeat for each grouping element.

Note that your data must be structured according to the groups you want to create in your template. The structure of the data for this example

<DATA> 
   <DEPT>
      <EMPS>

establishes the grouping desired for the report.

To Create Groups in the Template

  1. Highlight the cells that make up the group. In this example the cells are A8 – E8.
  2. Click the Name box at the left end of the formula bar and enter the name using the XDO_GROUP_ prefix and the tag name for the group from your data. For example: XDO_GROUP_?EMPS?
  3. Press Enter.

The following figure shows the XDO_GROUP_ defined named entered for the Employees group. Note that just the row of employee data is highlighted. Do not highlight the headers. Note also that the total cell XDO_?TOTAL_SALARY? is not highlighted.

the picture is described in the document text

Step 6: Test the template

If you have installed the Template Builder for Excel, the BI Publisher tab will appear on the ribbon menu as shown in the following figure:

the picture is described in the document text

To preview your report using sample data:

  1. Click Sample XML. You will be prompted to select the sample data file.
  2. Click Preview.

    The sample data will be applied to your template and the output document will be opened in a new workbook. The following figure shows the preview of the template with the sample data:

    the picture is described in the document text

Employee Bank Details API

For updating employee bank details following code can be used:

PROCEDURE XXHR_BANK_DETAILS
(ERRBUF OUT NOCOPY varchar2,
RETCODE OUT NOCOPY VARCHAR2 )
/* =====================================================================
— NAME : XXHR_BANK_DETAILS

— PROGRAM TYPE : Procedure

— DESCRIPTION : This is used to capture Bank Details
— INPUTS : None
— OUTPUTS : None
— ===================================================================*/

AS

CURSOR CUR_BANK
is
SELECT
EUREHKA_NUMBER,
START_DATE,
BANK_CODE,
BANK_ACC_NUMBER,
BANK_COUNTRY,
PAYMENT_CURRENCY,
IBAN,
SWIFT_CODE,
— BENEFICIARY,
— PURCHASE_ORDER_NUMBER,
— SUPPLIER_NAME,
— PURCHASE_ORDER_LINE,
RECORD_NO
FROM
XXHR_EMP_BANK_STG
WHERE
PROCESS_FLAG IS NULL;
— and EUREHKA_NUMBER = ‘91400000’;

l_num_record_no number;
l_var_process varchar2(100);
l_num_priority number;
l_num_assign_id number;
l_var_pay_method varchar2(100);
l_num_pay_method_id number;
l_var_bank_country varchar2(100);
l_var_bank_currency varchar2(100);
l_err_msg varchar2(32767);
l_num_per_payment_method_id number;
l_num_external_account_id number;
l_num_object_version_number number;
l_dat_effective_start_date DATE;
l_dat_effective_end_date date;
l_num_comment_id number;
l_num_person_id number;
l_var_person_type varchar2(100);
l_num_vendor_id number;
l_num_vendor_site_id number;
l_num_po_header_id number;
l_num_line_id number;
l_bool_org_no_manager_warning boolean;
l_dte_effective_start_date date;
l_dte_effective_end_date date;
l_num_comment_id1 number;
l_bool_no_managers_warning BOOLEAN;
l_bool_other_manager_warning boolean;
l_num_soft_coding_keyflex_id number;
l_var_concatenated_segments varchar2(100);
l_bool_hourly_salaried_warning boolean;
l_num_id_flex_num number;
sql_err varchar2(30000);
l_num_person_analysis_id number;
— l_num_id_flex_num number;
l_num_analysis_criteria_id number;
l_num_pea_ovn NUMBER;
l_var_payroll_present VARCHAR2(100);
l_num_payroll_id NUMBER;
l_dat_hire_date DATE;
L_NUM_FAILED_RECORDS NUMBER;
l_num_successful_records number;
l_dat_effective date;

BEGIN
FOR R_BANK IN CUR_BANK
loop
l_num_record_no :=NULL;
l_var_process :=NULL;
l_num_priority :=NULL;
l_num_assign_id :=NULL;
l_var_pay_method :=NULL;
l_num_pay_method_id :=NULL;
l_var_bank_country :=NULL;
l_var_bank_currency :=NULL;
l_err_msg :=NULL;
l_num_per_payment_method_id :=NULL;
l_num_external_account_id :=NULL;
l_num_object_version_number :=NULL;
l_dat_effective_start_date :=NULL;
l_dat_effective_end_date :=NULL;
l_num_comment_id :=NULL;
l_num_person_id :=NULL;
l_var_person_type :=NULL;
l_num_vendor_id :=NULL;
l_num_vendor_site_id :=NULL;
l_num_po_header_id :=NULL;
l_num_line_id :=NULL;
l_bool_org_no_manager_warning :=NULL;
l_dte_effective_start_date :=NULL;
l_dte_effective_end_date :=NULL;
l_num_comment_id1 :=NULL;
l_bool_no_managers_warning :=NULL;
l_bool_other_manager_warning :=NULL;
l_num_soft_coding_keyflex_id :=NULL;
l_var_concatenated_segments :=NULL;
l_bool_hourly_salaried_warning :=NULL;
l_num_id_flex_num :=NULL;
sql_err :=NULL;
l_num_person_analysis_id :=NULL;
— l_num_id_flex_num :=NULL;
l_num_analysis_criteria_id :=NULL;
l_num_pea_ovn :=NULL;
l_var_payroll_present :=NULL;
l_num_payroll_id :=NULL;
l_dat_hire_date :=null;
l_dat_effective :=NULL;
l_num_record_no := r_bank.record_no;

BEGIN
SELECT paaf.assignment_id, papf.person_id , paaf.object_version_number, payroll_id , papf.start_date
INTO l_num_assign_id ,l_num_person_id, l_num_object_version_number, l_num_payroll_id, l_dat_hire_date
from
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND papf.attribute1 = r_bank.EUREHKA_NUMBER
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
AND trunc(SYSDATE) BETWEEN trunc(paaf.effective_start_date) AND trunc(paaf.effective_end_date);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to fetch EMP’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
l_var_process := ‘N’;
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Emp’ );
END;

/*effective date– since some emp do not have their payroll attached date from date of hire. for attaching bank details to emp
payroll is mandatory*/
begin
select min(paaf.effective_start_date)
INTO l_dat_effective
from
per_all_people_f papf,
per_all_assignments_f paaf
WHERE
papf.person_id = paaf.person_id
AND papf.attribute1 = r_bank.EUREHKA_NUMBER
and trunc(sysdate) between trunc(papf.effective_start_date) and trunc(papf.effective_end_date)
and payroll_id is not null;
— AND trunc(SYSDATE) BETWEEN trunc(paaf.effective_start_date) AND trunc(paaf.effective_end_date);
EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’unable to fetch effective date’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
l_var_process := ‘N’;
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Emp’ );
END;

/*
================================================================================
–Get Person Type
================================================================================
*/

BEGIN
select HR_PERSON_TYPE_USAGE_INFO.get_user_person_type(l_dat_hire_date,l_num_person_id)
into l_var_person_type
from dual;
exception when others then
fnd_file.put_line (fnd_file.log, ‘Unable to fetch Person_type’ );
l_var_process := ‘N’;
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘E’,
error_msg = error_msg ||’–‘||’unable to fetch Person Type’
where record_no = l_num_record_no;

END;

fnd_file.put_line (fnd_file.log, ‘l_var_person_type’ ||l_var_person_type);
/*
================================================================================
–Get Bank Country
================================================================================
*/
— IF r_bank.bank_country = ‘MY’ THEN
— l_num_priority := 1;
— ELSE
— l_num_priority := 2;
— END IF;

begin
— SELECT DECODE(upper(r_bank.BANK_COUNTRY),’MY’,’Technip MY Primary’,’Technip MY Secondary’ )
— INTO l_var_pay_method
— FROM dual;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘R_BANK.BANK_COUNTRY’||R_BANK.BANK_COUNTRY );
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘R_BANK.PAYMENT_CURRENCY’||R_BANK.PAYMENT_CURRENCY );
IF (UPPER(R_BANK.BANK_COUNTRY) =’MY’ AND upper(R_BANK.PAYMENT_CURRENCY) =’MYR’)
THEN
L_VAR_PAY_METHOD :=’Technip MY Primary’;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘primary’);
l_num_priority := ‘1’;
ELSE
L_VAR_PAY_METHOD := ‘Technip MY Secondary’;
— FND_FILE.PUT_LINE (FND_FILE.LOG, ‘secondary’);
l_num_priority := ‘2’;
END IF;

EXCEPTION
WHEN OTHERS THEN
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’unable to fetch pay method’
WHERE RECORD_NO = l_num_record_no;
fnd_file.put_line (fnd_file.log, ‘issue in getting pay method name’ );
l_var_process := ‘N’;
end;
/*
================================================================================
–Get Pay method Id
================================================================================
*/
BEGIN
SELECT org_payment_method_id
INTO l_num_pay_method_id
FROM pay_org_payment_methods_f
WHERE org_payment_method_name = l_var_pay_method ;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.log, ‘issue in getting pay method ID’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’issue in getting pay method ID’
WHERE record_no = l_num_record_no;
l_var_process := ‘N’;
END;
/*
================================================================================
–Get Bank Country
================================================================================
*/
begin
SELECT TERRITORY_CODE
into l_var_bank_country
FROM
FND_TERRITORIES
where TERRITORY_CODE = r_bank.bank_country;
exception
when others then
fnd_file.put_line (fnd_file.log, ‘Bank Country not found’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’Bank Country not found’
where record_no = l_num_record_no;
l_var_process := ‘N’;

end;
/*
================================================================================
–Get Currency
================================================================================
*/
begin
select ffv.flex_value
into l_var_bank_currency
from
fnd_flex_value_sets ffvs,
fnd_flex_values ffv
where
ffvs.flex_value_set_name =’XXHR_MY_BANK_CURRENCY’
and ffv.flex_value_set_id = ffvs.flex_value_set_id
and ffv.flex_value = r_bank.PAYMENT_CURRENCY;
exception
when others then
fnd_file.put_line (fnd_file.log, ‘Bank Currency not found’ );
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
error_msg = error_msg
||’–‘
||’Bank Currency not found’
where record_no = l_num_record_no;
l_var_process := ‘N’;

END;
if nvl(l_var_process,’Y’)!= ‘N’
THEN
if upper(l_var_person_type) =’EMPLOYEE’or upper(l_var_person_type) =’EX-EMPLOYEE’
THEN
fnd_file.put_line (fnd_file.log, ’employee’ );

/*
================================================================================
–Get Payroll
================================================================================
*/
BEGIN
SELECT payroll_id
into l_var_payroll_present
FROM
pay_all_payrolls_f
WHERE
payroll_id = l_num_payroll_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE XXHR_EMP_BANK_STG
SET process_flag = ‘E’,
ERROR_MSG = ERROR_MSG ||’–‘||’No Payroll’
WHERE RECORD_NO = l_num_record_no;

COMMIT;
— l_var_process := ‘N’;
fnd_file.put_line (fnd_file.LOG, ‘No Payroll’ );
END;

BEGIN
–fnd_file.put_line (fnd_file.log, ‘EMP’);
hr_personal_pay_method_api.create_personal_pay_method
( — Input data elementds
— ——————–
p_validate => FALSE,
p_effective_date => l_dat_effective,
p_assignment_id => l_num_assign_id,
p_org_payment_method_id => l_num_pay_method_id,
p_priority => l_num_priority,
p_percentage => 100,
p_segment1 => replace(replace(replace(replace(r_bank.bank_code, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment2 => replace(replace(replace(replace(r_bank.bank_acc_number, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment3 => l_var_bank_country,
p_segment4 => l_var_bank_currency,
p_segment5 => replace(replace(replace(replace(r_bank.iban, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_segment6 => replace(replace(replace(replace(r_bank.swift_code, chr(10), ”), chr(13), ”), chr(09), ”),’ ‘,”),
p_territory_code => ‘MY’,
— Output data elementds
— ———————
p_personal_payment_method_id => l_num_per_payment_method_id,
p_external_account_id => l_num_external_account_id,
p_object_version_number => l_num_object_version_number,
p_effective_start_date => l_dat_effective_start_date,
p_effective_end_date => l_dat_effective_end_date,
p_comment_id => l_num_comment_id
);
UPDATE XXHR_EMP_BANK_STG
set process_flag = ‘S’
WHERE record_no = r_bank.record_no;

L_NUM_SUCCESSFUL_RECORDS:= L_NUM_SUCCESSFUL_RECORDS + 1;

commit;
EXCEPTION
WHEN OTHERS
THEN
l_err_msg := SQLERRM;
— l_var_process := ‘N’;
— DBMS_OUTPUT.put_line (‘Inner Exception: ‘ || l_err_msg);
UPDATE XXHR_EMP_BANK_STG
SET PROCESS_FLAG = ‘E’,
error_msg =l_err_msg
where record_no = r_bank.record_no;
fnd_file.put_line (fnd_file.log, ‘Bank Details not created for ‘||r_bank.eurehka_number||
‘error: ‘ ||l_err_msg);
commit;
end;
— END IF;END IF;

END IF;

IF L_VAR_PROCESS = ‘N’
THEN
L_NUM_FAILED_RECORDS :=L_NUM_FAILED_RECORDS +1;
END IF;

commit;
END loop;
FND_FILE.PUT_LINE (FND_FILE.LOG, ‘Number of Successful records :’||L_NUM_SUCCESSFUL_RECORDS );
fnd_file.put_line (fnd_file.log, ‘Number of Failed records :’||l_num_failed_records );
commit;
end XXHR_BANK_DETAILS;