How To Create Multi Tab Excel Report in BI Publisher

Hello Friends,

Today I am going to show you how to create multi sheet Excel Report in BI Publisher.

1)  First go to the below path:

C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\samples\Excel templates. Here you will get an excel file named as "
BlankExcelTemplate"  in which we have to create template.

2) Create a Data template file as below:

3) Now we will create all fields and tags in excel file as below:

 

4) It will show you tag as XDO_?EMP_NAME? for field name and for group it is XDO_GROUP_?EMPS?
These tags can be found under Formula-> Name Manager

5) Now go to the next sheet XDO_METADATA where we will write the logic to split the data for every department.

6) There should be mapping of column A and Column B.

XDO_SHEET_?          <?.//DEPT?>
XDO_SHEET_NAME_?         <?concat(.//DEPARTMENT_NAME,'-',count(.//EMP_NAME))?>

Save the file as .xls only. If you save it as .xlsx it won't work.





 

How to remove preview data file, Bursting Control file or data template file from Data definitions

Hello Friends,

Today I am going to show you How to remove preview data file, Bursting Control file or data template file from Data definitions:

1) Remove Preview Data file:
delete
 FROM XDO_LOBS
 WHERE LOB_CODE='TEST'
 AND LOB_TYPE='XML_SAMPLE';

2) Bursting Control file:
delete
 FROM XDO_LOBS
 WHERE LOB_CODE='TEST'
AND LOB_TYPE='BURSTING_FILE';

3) Data Template:
delete
 FROM XDO_LOBS
 WHERE LOB_CODE='TEST'
AND LOB_TYPE='DATA_TEMPLATE';

4) If you want to delete template file from Templates in oracle apps, Template can be xls, rtf etc. use below query:

DELETE
 FROM XDO_LOBS
 WHERE LOB_CODE='TEST'
AND LOB_TYPE='TEMPLATE';

If you like this article hit like and comment. If you get any difficulty, please put in comments, I will try to help you.

Query to extract New hire and terminated employees for a particualr duration

To get the list of the employees who has joined in a particular period

SELECT   DISTINCT
          papf.employee_number,
          papf.full_name,
          TO_CHAR (ppos.date_start, 'DD-MON-YYYY') date_start
   FROM   per_all_people_f papf,
          per_all_assignments_f paaf,
          per_periods_of_service ppos
  WHERE   ppos.date_start BETWEEN TO_DATE ('01-AUG-2018', 'DD-MON-YYYY')
                              AND  TO_DATE ('31-AUG-2018', 'DD-MON-YYYY')
          AND papf.person_id = paaf.person_id
          AND papf.person_id = ppos.person_id;
         
 To get the list of the employees who has terminated in a particular period

SELECT   PAAF.ASSIGNMENT_ID,
          PAPF.PERSON_ID AS PERSON_ID2,
          PAPF.FIRST_NAME AS FIRST_NAME,
          PAPF.LAST_NAME AS LAST_NAME,
          PAPF.EMAIL_ADDRESS AS EMAIL_ADDRESS,
          TO_CHAR (PPS.ACTUAL_TERMINATION_DATE) AS ACTUAL_TERMINATION_DATE,
          TO_CHAR (PAPF.EFFECTIVE_START_DATE) AS EFFECTIVE_START_DATE,
          PAPF.EMPLOYEE_NUMBER AS EMPLOYEE_NUMBER,
          TO_CHAR (PAPF.EFFECTIVE_END_DATE) AS EFFECTIVE_END_DATE,
          PAPF.BUSINESS_GROUP_ID AS BUSINESS_GROUP_ID,
          PAAF.SUPERVISOR_ID AS SUPERVISOR_ID,
          PAPF.LAST_UPDATE_DATE papf_update_date,
          PAAF.LAST_UPDATE_DATE paaf_update_date,
          ppt.user_person_type
   FROM   PER_ALL_PEOPLE_F PAPF,
          PER_ALL_ASSIGNMENTS_F PAAF,
          PER_PERIODS_OF_SERVICE PPS,
          hr.per_person_type_usages_f pptu,
          hr.per_person_types ppt
  WHERE   PAAF.PERSON_ID = PAPF.PERSON_ID
          AND PAAF.PRIMARY_FLAG = 'Y'
          AND paaf.assignment_type != 'B'
          AND pptu.effective_start_date BETWEEN papf.effective_start_date
                                            AND  papf.effective_end_date
          AND papf.person_id = pptu.person_id
          AND papf.person_type_id = pptu.person_type_id
          AND pptu.person_type_id = ppt.person_type_id
          AND papf.person_type_id = ppt.person_type_id
          AND papf.business_group_id = ppt.business_group_id
          AND PAAF.period_of_service_id = PPS.period_of_service_id
          AND papf.person_id = pps.person_id
          AND ( (ppt.user_person_type LIKE 'Ex-employee%'
                 AND PPS.ACTUAL_TERMINATION_DATE BETWEEN paaf.effective_start_date
                                                     AND  PAAF.EFFECTIVE_END_DATE)
               OR (ppt.user_person_type LIKE 'Employee%'
                   AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
                                           AND  PAAF.EFFECTIVE_END_DATE))
          AND ( (ppt.user_person_type LIKE 'Ex-employee%'
                 AND PPS.ACTUAL_TERMINATION_DATE BETWEEN papf.effective_start_date
                                                     AND  PAPF.EFFECTIVE_END_DATE)
               OR (ppt.user_person_type LIKE 'Employee%'
                   AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                           AND  PAPF.EFFECTIVE_END_DATE))