InList Function PLSQL

CREATE OR REPLACE TYPE t_in_list_tab AS TABLE OF VARCHAR2 (4000);
/

CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
  RETURN t_in_list_tab PIPELINED
AS
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN;
END;

SELECT *
                         FROM   emp
                         WHERE  empno IN (SELECT * FROM TABLE(in_list(:empno)))
                         ORDER BY ename
Value of :empno is 7876,7934,7782

JavaScript In OA Framework

The exact lines of Developers’ guide for use JavaScript are as follows:
“UIX and the OA Framework are rapidly adding new features to provide a more interactive user experience
(partial page rendering, automatic table totaling, and so on). You are certainly encouraged to leverage these
features as they are released, however, you should not attempt to implement them yourself before they're
ready.
In short, JavaScript is prohibited without explicit permission from the OA Framework team. Furthermore, you
must have confirmation from the corporate UI design team that a Javascript implementation is essential for
your product.”

But this is really funny, if you are developing some web pages for your application, so expect all web features in the framework, and if they are missing definitely you have to use JavaScript to implement them, you cannot wait , for the framework team to implement them.

So, if you are developing some custom pages for your application, you can go ahead with JavaScript. I will advice to refrain from JavaScript if your extending standard OA pages, because, Oracle may not support it, in that case.
In case of custom pages, anyways oracle will not support customization, so you dam care for support.

Another interesting thing in OA Framework is that you will methods for JavaScript usage method in almost every UIX beans; in fact, framework team must be using them in development of various features in OA Framework.

What is JavaScript? 

JavaScript is the Netscape-developed object scripting language used in millions of web pages and server applications worldwide. Netscape's JavaScript is a superset of the ECMA-262 Edition 3 (ECMAScript) standard scripting language, with only mild differences from the published standard.

Contrary to popular misconception, JavaScript is not "Interpretive Java". In a nutshell, JavaScript is a dynamic scripting language supporting prototype based object construction.

For those who are interested and new to concept of javascript, should explore this link
http://www.javascriptmall.com/learn/

I will like to stress on one point before starting the various scenarios where JavaScript can be used, is that JavaScript is a browser specific script and may behave different in different browsers, so you must test your application code on various browsers like internet explorer, mozilla firefox, Netscape etc.

Scenarios in OA Framework, where you are bound to use javascript.
Various Pop Up Window scenarios
Closing a Window
Timely refresh of a OA Page
Client side events on different beans

Various Pop Up Window scenarios: 
This is the most common scenario in OA Framework, as framework does not provide anything for pop windows, except standard LOVs, which may or may not suit your requirement.I think I have replied this thread several times on OA Framework forum, in fact, thought of writing this article, after I replied the thread so many times J.
Lets, take the most common scenario:
You need to fire a custom page as a pop up window , do some processing(say add some new values in one of the VO) and on returning back to base page, reflect the changes on base page(say the base page VO should reflect the changes done on pop up page).
Follow these steps for the solution:
1. To open a custom page..... as a modal window.... u can have a link or button(button of type button and not submit button) on base page with destination url property as following javascript:

javascript:var a = window.open('OA.jsp?page=/XXX/oracle/apps/xxx/......&retainAM=Y', 'a','height=500,width=900,status=yes,toolbar=no,menubar=no,location=no'); a.focus();

Meaning of each parameter in this javascript:
· RetainAM:If this is equal to “Y”, AM will be retained when child window is opened.
· Height: Height of the pop up or child window.
· Width: Width of pop up or child window
· Status: String, Specifies a priority or transient message in the window's status bar (This property is not readable.).
· Toolbar: Object, Represents the browser window's tool bar.
· Menubar: Object, Represents the browser window's
menu bar.
· Location:Location of window on the sceen
· Resizable: if resizable=yes, is included in above api, then child window size can be maximized.By default the child window size is fixed user cannot, increase or decrease the size.
· Scrollbar: if scrollbar=yes, is included in above api, scrollbars will appear in the child window.

Please Note : While trying to run a pop up page you get errors like:
"You are trying to access a page which is no longer active....."

The root cause of this error are the profile options:
1) FND_VALIDATION_LEVEL - None
2) FND_FUNCTION_VALIDATION_LEVEL - None
3)Framework Validation Level- None
They should be none in order to avoid the error.The root cause of the error is MAC key url validation, which is governed by the above 3 profiles.There can be scenarios when your DBA/Admin would not allow you to set these profiles at site level in order to maintain security of Apps from cross site scripting attacks.

In such scenarios, or I will say in all scenarios, its better to use a utility class in OAF called OABoundValueEmbedURL which secures your js function by bypassing the MAC key validation.You can use following code to attach js url programatically to a button/image bean :
OAImageBean img = (OAImageBean) webBean.findChildRecursive("");
//url of the page
String url ="/XXX/oracle/apps/xxx/......"
//retain am parameter
string amMode ="true";
//additional parameters i want to pass to to pop up page
String params="order_id={@workorder_id}";

//Computing page_url
String page = 
url + "&retainAM=" + amMode + "&fndOAJSPinEmbeddedMode=y" + "&" + 
params;
//Computing final url
String destURL = 
OAWebBeanConstants.APPS_HTML_DIRECTORY + OAWebBeanConstants.APPLICATION_JSP + 
"?" + OAWebBeanConstants.JRAD_PAGE_URL_CONSTANT + "=" + page;

//Securing js function from MAC key validation 
OABoundValueEmbedURL jsBound = 
new OABoundValueEmbedURL(img, "openWindow(self, '", destURL, 
"' , 'longTipWin', {width:" + width + 
", height:" + height + 
"}, true); return false;");

//Attaching bound value embed url to on click of image.
img.setAttributeValue(OAWebBeanConstants.ON_CLICK_ATTR, jsBound);

There are 3 important points to understand here :
1) This code has to be written in process request and not process form request, because as per OA Developer standards bean properties can only be set in process request and not process form request.

2)The additional parameters you want to pass can be passed at runtime, this is especially in case of tables/hgrids where lets say a particular column has link and each row has different parameter values to pass, you can send VO attributes as parameters like @Vo_attr_name.This will make your pop window link to be dynamic as each row js url will have different parameter values depending upron row of VO.

3)If you see the final url computed here I have used an additional parameter like
fndOAJSPinEmbeddedMode=y, this is especially in case of pages which use jtfchrome.jsp where in the JTF/JTT and OAF flows are integrated.This parameter will take care that the JS URL OF LOV page, should not be changed to jtfcrmchrome.jsp from OA.jsp which results in error of lov page. 

In one of the 2 approaches you would be able to open the pop up page. 

2. Now, you can do your pop up page processing by the pop page controller.
3. Now your child window should have two buttons, one for select(submit button) and another for close(button).On press of select button you will have all vaues in your trasient VO, and you can also show user a message like, " values created successfully....".The second button will be of type "button", whose destination url property will be

javascript:opener.submitForm('DefaultFormName',0,{XXX:' abc'});window.close();
(In this api you can pass n number of attributes, the second parameter will be correspondingly n-1, e.g you have to pass three attr, then second parameter of api will be 2
javascript:opener.submitForm('DefaultFormName',2,{'XXX':' abc','XXX1':'efg','XXX2':'ijk'});window.close(); )
Now in the process request of the base page CO, u can get parametre 'XXX' from the pagecontext....so
if((("abc").equals(pageContext.getParameter("XXX"))))
{///LOGIC to initialize or not to intialize some components of base page}

Please Note :
1)From Apps R12.1, the framework is also validating parameter security, so getting js parameter values by pageContext.getParameter("XXX"), can throw security exception error
FND_FORM_POST_SECURITY_FAILED. To bypass this parameter security validation , you need to take js parameter values directly from http request using following code:
pageContext.getRenderingContext().getServletRequest().getParameter("XXX");

2)Also, if you are getting error in doing a page submit on pop up page confirm:
FND: Framework Compatibility Mode profile option should have value greater than or equal to "11.5.10".

Closing a Window 
To open a custom page..... as a modal window.... u can have a link or button(button of type button and not submit button) on base page with destination url property as following javascript:window.close();

Timely refresh of a OA Page 
Lets, consider a scenario when you want a page refresh after every 10 seconds.Here is javascript for it:javascript:setTimeout(window.location.reload(),10000);where 10,000-->10 sec(10,000 milli sec)

What you need to do is attach with onLoad method of OABody bean, so that when the page is rendered, this javascript is triggered.You can code like this:

OABodyBean bodyBean = (OABodyBean) oapagecontext.getRootWebBean();

String javaS = “javascript:setTimeout('window.location.reload()',10000)”;
bodyBean.setOnLoad(javaS);

Client side events on different beans: 
Above I have given a few of million possible situations of javascript in OA Framework.In almost all ui beans u will find methods like setOnLoad *, setOnClick* etc., where you can attach a javascript function to the bean.
Will be back with more examples of javascript in OA Framework, soon!

Retrieving Script Engines in Java

A script engine registers alias names with the scripting framework.

For example, the built-in Rhino scripting registers the following names with the scripting framework:

1. js
2. rhino
3. JavaScript
4. javascript
5. ECMAScript
6. ecmascript

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;

public class GetEngineByName {
  public static void main(String[] args) {
    ScriptEngineManager manager = new ScriptEngineManager();
    /* Retrieve a ScriptEngine registered with the rhino name */
    ScriptEngine jsEngine = manager.getEngineByName("rhino");
    if (!(jsEngine == null)) {
      System.out.println(jsEngine);
    } else {
      System.out.println("\nNo supported script engine foundregistered as Rhino.");
    }
  }
}

Java Code Examples for javax.script.ScriptEngineManager.getEngineFactories()

The following are Jave code examples for showing how to use getEngineFactories() of the javax.script.ScriptEngineManager class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.

private boolean isJavaScriptAvailable() {
    if(isJSAvailableChecked) {
        return isJSAvailable;
    }
    ScriptEngineManager mgr = new ScriptEngineManager();
    List<ScriptEngineFactory> factories = mgr.getEngineFactories();
    for (ScriptEngineFactory factory: factories) {
        List<String> engNames = factory.getNames();
        for(String name: engNames) {
            if(name.equalsIgnoreCase("js") || name.equalsIgnoreCase("javascript")) { //NOI18N
                isJSAvailableChecked = true;
                isJSAvailable = true;
                return isJSAvailable;
            }
        }
    }
    isJSAvailableChecked = true;
    isJSAvailable = false;
    return isJSAvailable;
}

OAF:

Example

try { ScriptEngineManager mgr = new ScriptEngineManager(); ScriptEngineFactory[] factories = (ScriptEngineFactory[]) mgr.getEngineFactories(); for (ScriptEngineFactory factory: factories) { String[] engNames = factory.getNames(); for(String name: engNames) { System.out.println(name); } } } catch (Exception e) { }

System Variables In Oracle Forms list

List Of System Varible That are use iN oracle Forms

System variable

SYSTEM.BLOCK_STATUS

SYSTEM.COORDINATION_OPERATION

SYSTEM.CURRENT_BLOCK

SYSTEM.CURRENT_DATETIME

SYSTEM.CURRENT_FORM

SYSTEM.CURRENT_ITEM

SYSTEM.CURRENT_VALUE

SYSTEM.CURSOR_BLOCK

SYSTEM.CURSOR_ITEM

SYSTEM.CURSOR_RECORD

SYSTEM.CURSOR_VALUE

SYSTEM.DATE_THRESHOLD*

SYSTEM.EFFECTIVE_DATE*

SYSTEM.EVENT_WINDOW

SYSTEM.FORM_STATUS

SYSTEM.LAST_FORM

SYSTEM.LAST_QUERY

SYSTEM.LAST_RECORD

SYSTEM.MASTER_BLOCK

SYSTEM.MESSAGE_LEVEL*

SYSTEM.MODE

SYSTEM.MOUSE_BUTTON_PRESSED

SYSTEM.MOUSE_BUTTON_SHIFT_STATE

SYSTEM.MOUSE_ITEM

SYSTEM.MOUSE_CANVAS

SYSTEM.MOUSE_X_POS

SYSTEM.MOUSE_Y_POS

SYSTEM.MOUSE_RECORD

SYSTEM.MOUSE_RECORD_OFFSET

SYSTEM.RECORD_STATUS

SYSTEM.RIGHT_MOUSE_TRIGGER_NODE

SYSTEM.SUPPRESS_WORKING*

SYSTEM.TAB_NEW_PAGE

SYSTEM.TAB_PREVIOUS_PAGE

SYSTEM.TRIGGER_BLOCK

SYSTEM.TRIGGER_ITEM

SYSTEM.TRIGGER_MENUOPTION

SYSTEM.TRIGGER_NODE

SYSTEM.TRIGGER_NODE_SELECTED

SYSTEM.TRIGGER_RECORD

Decode Function

DECODE

Syntax

Purpose

DECODE compares expr to each search value one by one.

If expr is equal to a search,

Then Oracle Database returns the corresponding result.

If no match is found, then

Oracle returns default. If default is omitted, then Oracle returns null.

The arguments can be any of the numeric types (NUMBER, BINARY_FLOAT, or BINARY_

DOUBLE) or character types.

The maximum number of components in the DECODE function, including expr,

Searches, results, and default, is 255.

DECODE ( expr , search , result

,

, default

)

Test Case

Create table plch_employees (

   Id    integer primary key

, name varchar2 (40)

, gender char (1)       

)

/

Insert into plch_employees values (100, 'Mr. John Doe','M')

/

Insert into plch_employees values (200, 'Mrs. Jane Doe','F')

/

Insert into plch_employees values (300, 'Ms. Julie Doe','F')

/

Insert into plch_employees values (400, 'Mr. Jack Doe','M')

/

Insert into plch_employees values (500, 'Dr. James Doe','M')

/

Insert into plch_employees values (600, 'Jonathan Doe','M')

/

Insert into plch_employees values (700, 'Jeff Jr. Doe','M')

/

Commit

/

select id, name, decode(gender, 'M', 'MALE', 'F', 'FEMALE', gender)

  from plch_employees;

Test Case:

/*

User have requirement ( if parameter value is 100 then query only show the result of those employee who have id no 100

Else query will show all employees

Expect 100 no ID)

*/

---we can handle it from our decode function 

Select id, name, and decode (gender, ‘M’, MALE, ‘F’, ‘FEMALE’, gender)

  From plch_employees

  Where decode (id, 100,'Y','F') =decode (:v_id, 100,'Y','F');