WorkFlow : ORA-04061: existing state of has been invalidated ORA-04061: existing state of package

RA-04061: existing state of has been invalidated ORA-04061: existing state of package

Compile Package for Error  ORA-04061 in WorkFlow

alter package      Wf_Engine_Util  compile;
alter package      wf_engine       compile;
alter package      wf_notification compile;
alter package      wf_standard     compile;

Print Function for PLSQL Testing

For tracing PLSQL code , Print function  show output in DBMS Output as per Arguments.

1) For implementation of  this function simply add G_Debug_Enabled Variable and Function Specification in your package Specification. For enable tracing set G_Debug_Enabled as True before execute your code.

  G_Debug_Enabled Boolean default false;
    procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                );

procedure test_Print_function;

2) Add Function Body in Package Specification Body

procedure print(pString in varchar2

                , pText_1 in varchar2 default null

                , pValue1 in varchar2 default null

                , pText_2 in varchar2 default null

                , pValue2 in varchar2 default null

                , pText_3 in varchar2 default null

                , pValue3 in varchar2 default null

                , pText_4 in varchar2 default null

                , pValue4 in varchar2 default null

                , pText_5 in varchar2 default null

                , pValue5 in varchar2 default null

                , pText_6 in varchar2 default null

                , pValue6 in varchar2 default null

                , pText_7 in varchar2 default null

                , pValue7 in varchar2 default null

                , pText_8 in varchar2 default null

                , pValue8 in varchar2 default null

                , pText_9 in varchar2 default null

                , pValue9 in varchar2 default null

                )

    is

    begin

            if G_Debug_Enabled then

               Dbms_output.put_line('$ '||pString||' : '||pText_1||'  => '||pValue1||' '||pText_2||' => '||pValue2||' '||pText_3||' => '||pValue3||' '||pText_4||' => '||pValue4||' '||pText_5||' => '||pValue5||' '||pText_6||' => '||pValue6||' '||pText_7||' => '||pValue7||' '||pText_8||' => '||pValue8||' '||pText_9||' => '||pValue9);

              end if;

      end;

procedure test_Print_function

  is

  begin

          print('Function Test','TransactionID',10,'EmployeeName','mFaisal');

    end;

3) Now test test_print_function

begin

  -- Call the procedure

  PKG_wf_api.G_Debug_Enabled:=true;

  PKG_wf_api.test_print_function;

end;

Output: 

$ Function Test : TransactionID  => 10 EmployeeName => mFaisal  

first_rule of "The Forty Rules of Love"

#first_rule

"How we see God is a direct reflection of how we see ourselves.
If God brings to mind mostly fear and blame, it means there is too much fear and blame welled inside
us. If we see God as full of love and compassion, so are we.”

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

Enable / Disable Field in Oracle Forms

Case Study :

There are multiple columns in Oracle tabular form and they are depend on each other as Numeric sequence. User enter cannot enter value in columns if parent  of current item is null or child of current item is not null.

Add procedure in form and enter your your fields name in array as sequence you want .
after this call this code

begin 
when_New_item_instance;
end;

in When-new-item-instance trigger.


PROCEDURE when_New_item_instance IS

  type Items_List_t is varray(3) of Varchar2(200);

  l_Items_List Items_List_t := Items_List_t('BlockName.Item1',
                                            'BlockName.Item2',
                                            'BlockName.Item3');

  l_triggered_Item varchar2(200) default :system.trigger_item;

  l_item_enabled boolean default true;

  l_triggered_item_ind number;

BEGIN

  for ldx in 1 .. l_Items_List.count loop
 
    if l_triggered_Item = l_Items_List(ldx)
   
     then
   
      l_triggered_item_ind := ldx;
   
    end if;
 
  end loop;


if l_triggered_item_ind  is null then
null;
end;
  ---Note

  -- Exception overriding  is requeired in inner block so Exception when Others is Replaced with if-Else condition.

  Begin
 
    if name_in(l_Items_List(l_triggered_item_ind - 1)) is null
   
     then
   
      l_item_enabled := false;
   
    end if;
 
  exception
    when others
   
     then
      null;
   
      l_item_enabled := true;
   
  end;

  ---Note

  -- Exception overriding  is requeired in inner block so Exception when Others is Replaced with if-Else condition.

  Begin
 
    if name_in(l_Items_List(l_triggered_item_ind + 1)) is not null
   
     then
   
      l_item_enabled := false;
   
    end if;
 
  exception
    when others
   
     then
      null;
   
      l_item_enabled := true;
   
  end;

  if l_item_enabled then
 
    app_item_property.set_property(l_triggered_Item, enabled, property_on);
 
  else
 
    app_item_property.set_property(l_triggered_Item, enabled, property_off);
 
  end if;

END;





Note: 
Incase of non-EBS application 
replace 
app_item_property.set_property(l_triggered_Item, enabled, property_on);
with 
set_item_property(l_triggered_Item, enabled, property_on);

Alert Using JavaScript in OAF

if(pageContext.getParameter("SubmitButton")!=null)
    {
      StringBuffer l_buffer = new StringBuffer();
      l_buffer.append("javascript:alert('hello')");
      pageContext.putJavaScriptFunction("SomeName",l_buffer.toString());
    }


Here SubmitButton is the id name of submitbutton

l_buffer is the variable name of StringBuffer

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!