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

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

To Get text from Data source

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

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

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;

Knowlege Modules On Different Contextes (IKM,LKM,CKM,JKM)

SCD Type 3 (Slowly Changing Dimension )

CDC -- Change Data Capture using scd type 3

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

https://oracenter.blogspot.com/2016/06/create-oaf-search-page-1.html

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