Wednesday, December 13, 2017

Cummulative process in rtf using set and get variable

Declare varable:
<?xdoxslt:set_variable($_XDOCTX, ‘c1’, 0)?>


Set variable(In for loop to repected loop) :

<?xdoxslt:set_variable($_XDOCTX, ‘c1’, xdoxslt:get_variable($_XDOCTX, ‘c1’) + EHRS_CUM)?>


Get Variable:
<?xdoxslt:get_variable($_XDOCTX, ‘c1’)?>

Friday, November 17, 2017

Date parameter in Standed Date in app

<?xdofx:to_date(replace(substr(P_FROM_DATE,1,10),’/’,’-’)||’T00:00:00.000-06:00’,'DD-MON-YYYY')?>

Tuesday, November 14, 2017

To Get text from Data source

select *
from user_source
where upper(text) like upper('%Enter_text_hear%');

Wednesday, November 8, 2017

Set Views in Obiee

BEGIN
  GLR03300_PKG.set_criteria (to_number(NULL),to_number(NULL),to_number(NULL),to_number(NULL),to_number(NULL), 'ALL123456789012345',NULL,NULL,NULL,'A',to_number(NULL), NULL,'ALL123456789012345');
END;

Tags in rtf

<?format-date:DUEBY_DT;’DD-MM-YYYY’?>  ---DATE FORMATE
<?xdofx:substr(DUEBY_DT,1,10)?>         --SUBSTRING
<?format-number:T_BILLABLE_HOURS;'999G999D999'?>  --decimal

Tuesday, November 7, 2017

Cursors

DECLARE
  v_ename VARCHAR2(300):=NULL;
  v_job   VARCHAR2(300):=NULL;
BEGIN
  BEGIN
    SELECT ename
    INTO v_ename
    FROM emp
    WHERE empno='7839';
    /* Implicit Cursor with one row*/
    dbms_output.put_line('Implicit Cursor one row : '||v_ename);
  END;
  BEGIN
    FOR i IN
    (SELECT ename,JOB FROM emp WHERE empno IN ('7839','7698')
    )
    /* Explicit Cursor  */
    LOOP
      dbms_output.put_line(' Explicit Cursor retving more then on row ---->'||i.ename||'          '||i.job);
      FOR j IN
      (SELECT ename,JOB FROM emp WHERE ename=i.ename AND JOB=i.JOB
      )
      LOOP
        dbms_output.put_line(' Passing i values in j cursor retving more then on row ---->'||j.ename||'          '||j.JOB);
      END LOOP;
    END LOOP;
  END;
  BEGIN
    SELECT ename INTO v_ename FROM emp;
    /* Implicit Cursor */
    dbms_output.put_line('Implicit Cursor : '||v_ename);
  END;
EXCEPTION
WHEN too_many_rows THEN
  dbms_output.put_line('v_ename Implicit Cursor return Too many Rows');
END;

Friday, October 27, 2017

Message Choice @OAF


Message Choice

In this lesson we are going to learn how to create Message Choice item in the OAF page.
The bean for message Choice is item is “OAMessageChoiceBean”.
Step 1: First create Item in the page and select Item Style as MessageChoice in Property Inspector.
Step 2: After creating the page create the VO (View Object) in respective BC4J and attach VO to AM (Application Module).
Creating VO process:
Right Click on Resources folder of project and then select New View Object…  as shown in the below figure:


After that a welcome page to create a View Object wizard window will open as shown below:



Click on Next button so that first step to create a VO will come and in that :
Package : Select the proper package name which we created for a project.
Name : give the name to the VO note that all the View objects ends with VO.



Click on Next button:


Click on Next Button, no need to select any package or attributes here:
 Click on Next button without any changes:


Click on Next button without any changes so that we can see the Query area to enter SQL query to retrieve the data, write the SQL Query in the Query Statement



After writing the Query statement check whether the written Query is valid or not by clicking on Test so that if the query is valid then it shows a message that Query is valid. After that click on Next button.



Click on next button to step 8:


Click on next button and click on Finish button:



After creating VO (view object) we need to attach VO to AM (application Module). Let us see how to attach VO to AM:
Attaching VO to AM:
Edit the AM which we created in the project, right click on AM and then select Edit <AM Name> as shown in the below figure:


Select Data Model, extract the package in which VO is created select the VO after selecting the proper VO click on the Greaterthan arrow symbol which is shown in below figure:

After that we can see the VO is attached to the AM as shown in the below figure:


Click on Apply button and then on OK button so that the attachment process of VO to AM is over.

Step 3: Select the item properties in that do the following:

Picklist View Instance : Give the VO name
View Instance : Attribute Name, the displayed list values.  (Column Name, in VO columns are converted into                                              attributes).
View Attribute : Attribute Name

Now run the page so that we can see the output with drop down list to pick some values. The below image shows the output of Message Choice along with other items in OAF page.

Note that a drop down list can handle a maximum of 200 values.






Update records in OAF Page

1. Create a Search Page to Create a page please go through the following link



2. Implement Update Action in SearchPG
Right click on ResultTable in SearchPG > New > Item

Set following properties for New Item

Attribute
Property
ID
UpdateAction
Item Style
image
Image URI
updateicon_enabled.gif
Atribute Set
/oracle/apps/fnd/attributesets/Buttons/Update
Prompt
Update
Additional Text
Update record
Height
24
Width
24
Action Type
fireAction
Event
update
Submit
True
Parameters
Name – PColumn1
Value -- ${oa.SearchVO1.Column1}
Name – PColumn2
Value -- ${oa.SearchVO1.Column2}
3. Create a Update Page
Right click on SearchDemo > New > Web Tier > OA Components > Page
Name – UpdatePG
Package – search.oracle.apps.fnd.searchdemo.webui

4. Select the UpdatePG and go to the strcuture pane where a default region has been created

5. Select region1 and set the following properties:

Attribute
Property
ID
PageLayoutRN
Region Style
PageLayout
AM Definition
prajkumar.oracle.apps.fnd.searchdemo.server.SearchAM
Window Title
Update Page Window
Title
Update Page
Auto Footer
True

6. Create the Second Region (Main Content Region)
Select PageLayoutRN right click > New > Region
ID – MainRN
Region Style – messageComponentLayout

7. Create first Item (Empty Field)
MainRN > New > messageTextInput

Attribute
Property
ID
Column1
Style Property
messageTextInput
Prompt
Column1
Data Type
VARCHAR2
Length
20
Maximum Length
100
View Instance
SearchVO1
View Attribute
Column1

8. Create second Item (Empty Field)
MainRN > New > messageTextInput

Attribute
Property
ID
Column2
Style Property
messageTextInput
Prompt
Column2
Data Type
VARCHAR2
Length
20
Maximum Length
100
View Instance
SearchVO1
View Attribute
Column2

9. Create a container Region for Apply and Cancel Button in UpdatePG
Select MainRN of UpdatePG
MainRN > messageLayout

Attribute
Property
Region
ButtonLayout

10. Create Apply Button
Select ButtonLayout > New > Item

Attribute
Property
ID
Apply
Item Style
submitButton
Attribute
/oracle/apps/fnd/attributesets/Buttons/Apply

11. Create Cancel Button
Select ButtonLayout > New > Item

Attribute
Property
ID
Cancel
Item Style
submitButton
Attribute
/oracle/apps/fnd/attributesets/Buttons/Cancel

12. Add Page Controller for SearchPG
Right Click on PageLayoutRN of SearchPG > Set New Controller
Name – SearchCO
Package -- search.oracle.apps.fnd.searchdemo.webui

Add Following code in Search Page controller SearchCO 

import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.webui.beans.layout.OAQueryBean;
  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    
    OAQueryBean QB=(OAQueryBean)webBean.findChildRecursive("QueryRN");
    QB.clearSearchPersistenceCache(pageContext);
    
  }

  /**
   * Procedure to handle form submissions for form elements in
   * a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    if("update".equals(pageContext.getParameter(EVENT_PARAM)))
    {
      pageContext.setForwardURL("OA.jsp?page=/search/oracle/apps/PO/searchdemoPRJ/webui/updatePG", 
                                          null, 
                                          OAWebBeanConstants.KEEP_MENU_CONTEXT,                             
                                          null,                                                    
                                          null, 
                                          true,                             
                                          OAWebBeanConstants.ADD_BREAD_CRUMB_NO, 
                                          OAWebBeanConstants.IGNORE_MESSAGES); 
    }
  }


13. Add Page Controller for UpdatePG
Right Click on PageLayoutRN of UpdatePG > Set New Controller
Name – UpdateCO
Package -- prajkumar.oracle.apps.fnd.searchdemo.webui

Add Following code in Update Page controller UpdateCO 

import oracle.apps.fnd.framework.webui.OAPageContext;
import oracle.apps.fnd.framework.webui.beans.OAWebBean;
import oracle.apps.fnd.framework.webui.OAWebBeanConstants;
import oracle.apps.fnd.framework.OAApplicationModule;
import java.io.Serializable; 
  public void processRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processRequest(pageContext, webBean);
    OAApplicationModule am=pageContext.getApplicationModule(webBean);
    
    String Column1 = pageContext.getParameter("PColumn1"); 
     String Column2 = pageContext.getParameter("PColumn2"); 
     Serializable[] params = { Column1, Column2 }; 
     am.invokeMethod("updateRow", params); 
    }


  

  /**
   * Procedure to handle form submissions for form elements in
   * a region.
   * @param pageContext the current OA page context
   * @param webBean the web bean corresponding to the region
   */
  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)
  {
    super.processFormRequest(pageContext, webBean);
    searchAMImpl am=(searchAMImpl)pageContext.getApplicationModule(webBean);
          
    if (pageContext.getParameter("Apply") != null) 
    {  
     am.invokeMethod("apply"); 
     
    pageContext.forwardImmediately("OA.jsp?page=/search/oracle/apps/PO/searchdemoPRJ/webui/searchPG", 
                                               null, 
                                               OAWebBeanConstants.KEEP_MENU_CONTEXT, 
                                               null, 
                                               null, 
                                               false, // retain AM 
                                               OAWebBeanConstants.ADD_BREAD_CRUMB_NO); 
    
    } 
    else if (pageContext.getParameter("Cancel") != null) 
    {  
     am.invokeMethod("rollback"); 
     pageContext.forwardImmediately("OA.jsp?page=/search/oracle/apps/PO/searchdemoPRJ/webui/searchPG", 
                                            null, 
                                            OAWebBeanConstants.KEEP_MENU_CONTEXT, 
                                            null, 
                                            null, 
                                            false, // retain AM 
                                            OAWebBeanConstants.ADD_BREAD_CRUMB_NO); 
    } 
    }
14. Add following Code in SearchAMImpl

import oracle.apps.fnd.framework.server.OAApplicationModuleImpl;
import oracle.apps.fnd.framework.server.OAViewObjectImpl;
    
  public void apply() 
  { 
   getOADBTransaction().commit(); 
  }

  public void rollback() 
  { 
   getOADBTransaction().rollback(); 
  }
  public void updateRow(String Column1, String Column2) 
  { 
   searchVOImpl vo = (searchVOImpl)getsearchVO1(); 
   vo.initQuery(Column1,Column2);
  } 

15. Add following Code in SearchVOImpl

import oracle.apps.fnd.framework.server.OAViewObjectImpl;
    
  public void initQuery(String Column1, String Column2) 
  { 
   if ((Column1 != null) && (!("".equals(Column1.trim())))) 
   { 
    setWhereClause("column1 = :1 AND column2 = :2"); 
    setWhereClauseParams(null); // Always reset 
    setWhereClauseParam(0, Column1); 
    setWhereClauseParam(1, Column2); 
    executeQuery(); 
   } 
  }

16. Congratulation you have successfully finished. Run Your Search page and Test Your Work