How to enable and disable a table column based on another table column value in OAF

How to enable and disable a table column based on another table column value in OAF

 

Today I am going to tell you how to enable and disable a table column based on another table column value in OAF. E.g. We have one scenario where we have one column as recommended reason which is a drop down. There are 4  values in it. We have to fulfill below conditions:

 

1) If reason is selected as TONER from drop down, then RMA Number and Customer comments field should be disabled.

2) If reason is selected as RETURN from drop down, then RMA Number/Copies column should be enabled and required field with a tip message as "Please Enter RMA Number".

3) If reason is selected as SPLCPOY from drop down, then RMA Number/Copies column should be enabled and required field with a tip message as "Please Enter No. of Units".

4) If reason is selected as OTHERS from drop down, then customer comments column should be enabled with some default value and its required and RMA number/Copies field will be disabled.

5) If "RETURN" value selected and some values entered into the RMA number/copies field, and after that if drop down value is being changed, then RMA number/Copies field value should be cleared out and it should show the new tip for SPLCOPY only.

 

Very first we have to add 4 transient attributes to the VO as below. one is for RMA number and other for customer comments enable/disable:one is for RMA number and other for customer comments required/non required

 

 

 

 

Now First we will see how the page will be created:

1) Recommended reason property should be set as below:

 

 

 

 

2) As we have to set a tip conditionally on the same field, first we will create flow layout. see the below property:

 

3) under flowlayout, again we will create stack layout.

4) under stack layout we will create textinput item for RMA number/Copies field as below:

5) under stack layout, now we will create switcher which will be having two tip items. For switcher, we have to create different view attribute as well.

 

 

6) Now we will create two tips for RETURN and SPLCOPY case as below:

 

 

 

7) Now we have to write a logic in controller to get the reason value, after selecting the drop down value:

 

           if ("recReasonEvent".equals(pageContext.getParameter(OAWebBeanConstants.EVENT_PARAM))) {
                       pageContext.writeDiagnostics(this, "recReasonEvent event start", 1);
                           String rowReference = pageContext.getParameter(OAWebBeanConstants.EVENT_SOURCE_ROW_REFERENCE);
                  Serializable[] paramEnableCustCommentRma = { rowReference };
                  pageContext.writeDiagnostics(this, "before enableCustCommentRma method", 1);
                  am.invokeMethod("enableCustCommentRma",paramEnableCustCommentRma);
                  pageContext.writeDiagnostics(this, "after enableCustCommentRma method", 1);
           }

 

8) Now we have to write method in AM as below:

 

       public void enableCustCommentRma(String rowReference)
       {
           Row currentRow = findRowByRef(rowReference);
           String strRecommMeaning = (String)currentRow.getAttribute("Recomreason");
          getOADBTransaction().writeDiagnostics(this,"enableCustCommentRma :strRecommMeaning=" + strRecommMeaning, 1);
                           if (!(("").equals(strRecommMeaning) &&
                                 strRecommMeaning == null)) {

                               if (("OTHER").equals(strRecommMeaning)) {
                                   currentRow.setAttribute("IsCustCommentsDisabled", false);
                                   currentRow.setAttribute("IsRmaSplCopiesDisabled", true);
                                   currentRow.setAttribute("RmaSplCopies", null);
                                   currentRow.setAttribute("CustComments", "Explain why you need more than the Recommended Quantity of the item(s) highlighted below. There is 230 character limit");
                                   currentRow.setAttribute("IsRmaNumberRequired", "no");
                                   currentRow.setAttribute("IsCustomerCmtRequired", "yes");
                               }
                               else if (("RETURN").equals(strRecommMeaning) || ("SPLCOPY").equals(strRecommMeaning)) {
                                   currentRow.setAttribute("IsCustCommentsDisabled", true);
                                   currentRow.setAttribute("IsRmaSplCopiesDisabled", false);
                                   currentRow.setAttribute("IsRmaNumberRequired", "yes");
                                   currentRow.setAttribute("IsCustomerCmtRequired", "no");
                                   currentRow.setAttribute("RmaSplCopies", null);
                                   currentRow.setAttribute("CustComments",null);
                                   if(("RETURN").equals(strRecommMeaning))
                                   {
                                   currentRow.setAttribute("RmaSplTip", "rmaTip");
                                   }
                                   else
                                   {
                                       currentRow.setAttribute("RmaSplTip", "splTip");
                                   }
                               }
                               else if (("TONER").equals(strRecommMeaning)) {
                                   currentRow.setAttribute("IsCustCommentsDisabled", true);
                                   currentRow.setAttribute("IsRmaSplCopiesDisabled", true);
                                   currentRow.setAttribute("RmaSplCopies", null);
                                   currentRow.setAttribute("CustComments",null);
                                   currentRow.setAttribute("IsRmaNumberRequired", "no");
                                   currentRow.setAttribute("IsCustomerCmtRequired", "no");
                               }
                           }
       }

9) Now we will deploy all these objects on server, and bounce the server then test the scenarios:

 

first we select the different reasons for each line and see.

 

 

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