How to Enable BI Publisher Tab in Excel 2013

Hello Friends,


Today I am going to show how to enable BI Publisher tab in Excel 2013.


Pre-Requisite-> BI Publisher should be installed in your system.


1) Once BI Publisher is installed, first check if BI publisher tab is enabled or not in excel.

2)  go to File-> Excel Option -> Add Ins -> Select COM Add ins from manage drop down as shown in screen shot:

3) When you click on above Go button, then below screen will appear . select check box as shown in below screen shot.

4) Once you click on OK Button. You will see BI Publisher tab is enabled as shown below:

If you have any questions, put them in comment box. I will respond.

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))
         

java.sql.SQLException: Invalid column type in OAF

This blog is about how to resolve the above error.

This error is related to VO. If we are defining any dynamic parameters in VO like below then we might come across this error:

Eg.:
SELECT to_char(order_number) AS order_number, flow_status_code  AS order_status
FROM oe_order_headers_all WHERE ORIG_SYS_DOCUMENT_REF like :1 ||':'||'%'

Select Binding style as "Oracle Positional" and your problem will get resolved. Write your comments if you are still facing difficulty.

How to call another page on click on a button in OAF and pass the parameters from one page to another

Hello Friends,

Today I am going to tell you how to call another page on click on a button in OAF and pass the parameters from one page to another.

Step1 : Create a page with an item "Message Choice" as item style and give ID as "OrgCode". and create a submit button and  give ID as Submit in item property.

(You can choose any item style as per your requirement)

Step3 : Create a controller and write the below code in ProcessFormRequest Method.

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
      OAApplicationModule am = pageContext.getApplicationModule(webBean);
               if (pageContext.getParameter("Submit") != null)
                {  
      String strOrgCode = pageContext.getParameter("OrgCode");
           pageContext.forwardImmediately("OA.jsp?OAFunc=RAC_PLANNED_HOURS&OrgCode="+strOrgCode,
           null,
           OAWebBeanConstants.KEEP_MENU_CONTEXT,
           null,
           null,
           true, // retain AM
           OAWebBeanConstants.ADD_BREAD_CRUMB_NO);
           }
  }

 

How To Create Hyperlinks in OA Framework

Hello Friends,

Today I am going to tell you how to create hyperlinks in OA Framework. If you have any doubt, you can put your comments in comment box. If you like the post, please hit like button.

Step 1: Create an Item of "Message Styled Text" item style.

Step2: Go to item property, in Navigation section set the destination URL property as below:

OA.jsp?OAFunc=RAC_SHOP_CAP_MANAGEMENT&CCDate={@PlanDate}&OrgCode={OrganizationCode}

RAC_SHOP_CAP_MANAGEMENT  is OAF function to which we want to navigate from hyperlink.

If you don't have function then you can redirect to page directly as below:

OA.jsp?page=/xxcustom/oracle/apps/fnd/webui/HelloWorldPG&CCDate={@PlanDate}&OrgCode={OrganizationCode}


PlanDate and OrganizationCode are VO Attributes whose value we want to pass to another page.

CCDate and OrgCode are the parameters which holds value and we can get the values of these parameters in next page.

Step 3: See below screen shot for all the peoperty:




 

Message not found. Application: FND, Message Name: FND_VIEWOBJECT_NOT_FOUND.

Message not found. Application: FND, Message Name: FND_VIEWOBJECT_NOT_FOUND. Tokens: VONAME = RACOrganizationVO1


If you get this kind of error after deploying files to the server that means your View Object has not been added in your AM.xml file.

Before:

 

 

After:

 

 

 

After adding RACOrganizationVO1 in the AM.xml, this error got resolved. Let me know if you are facing some issues.

How to put total for Advance table column in OA Framework

go to advance table -> column-> column properties.

Set the property as below

make sure the item type should be selected as Number only other wise it wont work.

 

By doing these two above steps, you can see the total value calculated automatically. No need to write a code for the same.

 

Let me know if you have any questions. I will answer you.

 

OAF Error : unable to launch the java virtual machine located at path msvcr71.dll

Replication Of Issue: 

 

Go to path JDEV_Home ->jdev ->bin and try to open any 64 bit Jdeveloper

Reason: 

 

 

While Installing Jdeveloper, It install JDK (e.g. jdk160_24) inside Oracle1 -> Middleware and JDeveloper referes to this path as JavaHome. But if the Jdeveloper needs Higher version of JDK than the installed one (e.g JDev 11.1.1.6.0 needs jdk160_27 or above) then this error occurs.

 

 

Resolution:

 

1. Install JDK version compatible for the installed version

2. Go to JDEV_HOME\jdev\bin\jdev.conf and changed the set javahome as
 "SetJavaHome C:\Program Files\Java\<The New JDK Installed>

 

OAF: The program can't start because MSVCR71.dll is missing from your computer.

In one of my project, in one machine we were not able to open the Jdeveloper  (Version 10.1.3.5 - Patch 19170592 ) which we downloaded from the Metalink Note. This post is to help those who face this issue and don't know what to do to fix this.

OA Framework - How to Find the Correct Version of JDeveloper for OA Extensions to Use with E-Business Suite 11i or Release 12.x (Doc ID 416708.1)

After downloading p19170592_R12_GENERIC.zip, unzipped the contents into C:/oracle/Jdev10.1.3.5. After that when we tried to open the Jdeveloper using C:/oracle/Jdev10.1.3.5/jdevbin/jdev/bin/jdevW.exe, we were getting the below error.

"The program can't start because MSVCR71.dll is missing from your computer. Try reinstalling the program to fix this problem."

Fix:  You just need to copy the msvcr71.dll file from C:/oracle/Jdev10.1.3.5/jdevbin/jdk/bin to C:/oracle/Jdev10.1.3.5/jdevbin/jdev/bin.

After copying the file, Jdeveloper opened successfully.

How To Setup OA Framework in oracle applications 12.2.4

Task 1: Download and Extract the JDeveloper OA Extension zip file

1. Download Patch 19170592: 10G JDEVELOPER WITH OA EXTENSION ARU FOR R12.2.4
2. Extract the archive to c:\jdeveloper_1224
3. Make sure c:\jdeveloper_1224 has the following subdirectories:
jdev
jdevbin
jdevdoc
jdevhome

Task 2: Configure the JDEV_USER_HOME Environment Variable

On a Windows 7 Desktop, right click Computer icon > Properties > Advanced system settings > Environment Variables button > New button
Variable name = JDEV_USER_HOME
Variable value = c:\jdeveloper_1224\jdevhome\jdev

 

Task 3: Obtain the Database Connection file

Create the c:\jdeveloper_1224\dbc_files\secure directory
Ftp the dbc file located at $FND_SECURE to c:\jdeveloper_1224\dbc_files\secure directory

Task 4: Create a Desktop Short to JDeveloper

Right click on Windows desktop > New > Shortcut > Browse > c:\jdeveloper_1224\jdevbin\jdev\bin\jdevW.exe > Next > Finish

 

 

Task 5: Assign Toolbox Responsibilities to an Applications User

Assign the following responsibilities to a user:
- OA Framework ToolBox Tutorial
- OA Framework ToolBox Tutorial Labs

Task 6: Launch JDeveloper and Configure the Database Connection and the User

From JDeveloper main menu > File > Open and select c:\jdeveloper_1224\jdevhome\jdev\myprojects\toolbox.jws
Expand toolbox from the Applications Navigator tab

 

Select LabSolutions > right click > Project Properties > Oracle Applications > Runtime Connection
Set DBC File Name = c:\jdeveloper_1224\dbc_files\secure\<SID>.dbc
Set Username and Password to the user you assigned the 2 responsibilities

Select Tutorial > right click > Project Properties > Oracle Applications > Runtime Connection
Set DBC File Name = c:\jdeveloper_1224\dbc_files\secure\<SID>.dbc
Set Username and Password to the user you assigned the 2 responsibilities

Click Connections tab and expand Database

Right click Database > New Database Connection to open Connection Wizard > Next

Enter values for the following:
Connection Name
Username = apps
Password = <password>
Host Name
JDBC Port
Service Name
Click Next button > Test Connection > Make sure the Status indicates Success! > Next > Finish
Remove the existing fwk12dev > Right click > Delete

Click Applications Navigator tab
Right click LabSolutions > Project Properties > Business Components
Make sure Connection is set to the Connection Name you defined earlier > OK
Right click Tutorial > Project Properties > Business Components
Make sure Connection is set to the Connection Name you defined earlier > OK

Task 7: Test Your Setup

Right click LabSolutions > Rebuild > Make sure you get Compilation complete: 0 errors
Right click Tutorial > Rebuild > Make sure you get Compilation complete: 0 errors

Right click LabSolutions > Run
This should open this page in the browser: http://mkmoreno-au.au.oracle.com:8988/OA_HTML/test_fwklabsolutions.jsp
Right click Tutorial > Run
This should open this page in the browser: http://mkmoreno-au.au.oracle.com:8988/OA_HTML/test_fwktutorial.jsp

If XML report output is coming in XML format when output type is selected as EXCEL in template

1)      System Administration-> Concurrent -> programs

2)      Put “XX_OSB_PT_TRANS” in program field and click on go

3)      Click on update

4)      Go to onsite settings tab

Select the value from template field and click on Apply button.