Thursday, September 6, 2007

Dependent Dynamic message choicelists

This is one of the most common scenario in any web application page.Basically here i am just giving an idea how to code for dependent message choicelists in OA Framework page both at page level or in a collective ui feature like table or hgrid.

3 Message Choicelists in a Page :

Lets consider the first scenario, where we have 3 dependent message choicelists(mc1,mc2,mc3), these can be n in number, just follow the same approach.Here mc1,mc2 and mc3 are the item ids of the message choice lists in the page UIX file, which are attached to vo instances mc1VO1 and mc2VO1 and mc3vo1 respectively.Assuming "Intial value" property value is blank and "Add blank value" is "true" in property inspector for all three poplists.
Lets start from controller code, please read comments, as they explain each and every line..

Controller Code

/*Code in Controller*/
import oracle.apps.fnd.framework.webui.beans.message.OAMessageChoiceBean;

//Code in process request
OAMessageChoiceBean mc1 = (OAMessageChoiceBean)webBean.findChildRecursive("mc1");
/*The poplist data object that OA Framework creates the first
* time is cached in the JVM and reused
*until you explicitly disable caching
*Hence, use setPickListCacheEnabled API to stop JVM
* from caching Poplist values, this API needs to be called on any Poplist
* where the VO where clause keeps on changing
*/
mc1.setPickListCacheEnabled(false);
//Similarly for second Poplist
OAMessageChoiceBean mc2 = (OAMessageChoiceBean)webBean.findChildRecursive("mc1");
mc2.setPickListCacheEnabled(false);

//Similarly for third Poplist
OAMessageChoiceBean mc3 = (OAMessageChoiceBean)webBean.findChildRecursive("mc3");
mc3.setPickListCacheEnabled(false);

//Code in process form request
/*If a value is selected in mc1
*"update" is the name of PPR event
* attached to poplist mc1
*/
if("update".equals(pageContext.getParamete(OAWebBeanConstants.EVENT_PARAM))) {
String value_selected=pageContext.getParameter("mc1");
//Priniting the value selected
System.out.println("value_selected in mc1>>"+value_selected);
//if the selected value is not null
if(!(("".equals(value_selected)) (value_selected==null)))
{
//then calling the method in AM which will reinitialise VO query
// in second message choicelist and not third because
//the value selected in seocond will null
//as we have turned Add blank value to true
//and initial value is null in property inspector
Serializable[] param = {value_selected};
am.invokeMethod("initmc2VOQuery", param);
// if user selects some value in first choicelist
// pass some dummy_value in mc3vo query so that it returns 0 records
//or you can put some condition in vo query like where 5=6
Serializable[] param = {dummy_value};
am.invokeMethod("initmc3VOQuery", param);
}
else
{
// if user selects null in first choicelist
// pass some dummy_value in mc2vo query so that it returns 0 records
//or you can put some condition in vo query like where 5=6
//doing this for mc2vo1 and mc3vo1
Serializable[] param = {dummy_value};
am.invokeMethod("initmc2VOQuery", param);
am.invokeMethod("initmc3VOQuery", param);
}
}

/*If a value is selected in mc2
*"update1" is the name of PPR event
* attached to poplist mc2
*/
if("update1".equals(pageContext.getParamet(OAWebBeanConstants.EVENT_PARAM)))
{
String value_selected=pageContext.getParameter("mc2");
//Priniting the value selected
System.out.println("value_selected in mc2>>"+value_selected);
//if the selected value is not null
if(!(("".equals(value_selected)) (value_selected==null)))
{
//then calling the method in AM which will reinitialise VO query
// in third message choicelist
Serializable[] param = {value_selected};
am.invokeMethod("initmc3VOQuery", param);
}
else
{
// if user selects null in second choicelist
// pass some dummy_value in mc3vo query so that it returns 0 records
//or you can put some condition in vo query like where 5=6
//doing this only for mc3vo1
Serializable[] param = {dummy_value};
am.invokeMethod("initmc3VOQuery", param);
}
}

Dependent message choicelists in Table

Controller Code :

/*Cosider two message choicelist items HeaderPoplistItem and LinePoplistItem in table with item id "XXX".We will use setListVOBoundContainerColumn api,which is typically used for containers that repeat it's content multiple times and want to attach different set of records for each iteration, eg table or hgrid.*/

OATableBean XXX = (OATableBean)webBean.findChildRecursive("XXX");

/*LinePoplistItem is a poplist item, in which view def is filled and not view instance this is based on initial value in HeaderPoplistItem which is a poplistwith view instance name*/
OAMessageChoiceBean LinePoplistItem = (OAMessageChoiceBean)XXX.findChildRecursive("LinePoplistItem");
//
LinePoplistItem.setListVOBoundContainerColumn(0, XXX,"HeaderPoplistItem");


AM code
//Also note for runtime dependency of table of hgrid poplists, you have attach PPR in the LinePoplistItem and in your event method in AM write:
getVO().setWhereClauseParam(0,);
getVO().executeQuery();

In this article , i have covered the Dependent Dynamic message choicelists in tables and independent poplists on page. Hope this scenario is now clear.


14 comments:

Hemu said...

hi mukul,
I really enjoyed reading your blog and posts in forum. Thanks for sharing your valuable knowledge.

-Hem

kalis said...

Very good blog. And very useful to others. Interesting threads.
With Regards,
Kali.
OSSI.

myathsu said...

hi. i would like to ask u . can control the message choice in advanced search panel. if yes, pls let me know.thank and regards. eniac

eniac said...

i already tested for normal message choice like the one u posted in ur blog..
it is not working for message choice in advanced search panel although it is working in normal one.

Suyash Joshi said...

Hi,

I am working in Oracle IDC, Hyderabad with the Oracle Ebiz - Procurement Team and I'm facing an issue related to a similar problem.

The thing is, I'm having a table with a poplist which is dependent on another poplist in the table.
I'm able to populate the second poplist , however, the results are not getting populated at row level i.e. all the rows are getting populated.

Hence, now I'm trying to use the Dynamic Poplist method. However, using this method, I'll need to change the VO used for the poplist altogether since the query already has a bind parameter and setListVOBoundContainerColumn ( ) is not helpful with the same.

Can you please demonstrate an example for how to deal with such a VO ?

if possible, please try to contact me on my gmail ID or gtalk in your spare time...

thanks and Regards,

Suyash Joshi

Mukul said...

Suyash,
In case your second dependent poplist VO entire query is changing at runtime, you would have to do a set query on the fire action of messagechoice1.

The code may be a little complicated...instead of messagechoice, y don't u use a combination of messagechoice and a LOV, OR 2 lovS. It would be very straight forward in case of LOvs.
--Mukul

Mukul said...

Let me know, if this resolves your problem of not!

Raj said...

Hi Mukul,

I have an issue with the 2nd pop list. I am getting the ORA-01008: not all variables bound. 1st one is working fine. For the 2nd poplist, 2 bind variables required of same value.

Used the below code:

OAMessageChoiceBean mc3 = (OAMessageChoiceBean)pTable.findChildRecursive("TaskMC");
mc3.setPickListCacheEnabled(false);
mc3.setListVOBoundContainerColumn(0,pTable,"Project");
mc3.setListVOBoundContainerColumn(1,pTable,"Project");

Please assist me in this case.

-Rajendra

chowdary said...

hi,
1) my requirement is to develop a "Vendor Workbench Page" that consists two lov items Vendor Name and Vendor Number which are dependent on each other.
That is when i select vendor name from vendor Name LOV , i should get corresponding vendor number from Vendor Number LOV. OR
When i select vendor number from Vendor Number LOV, i should get corresponding vendor name from vendor Name LOV

2) when i press clear button the dependent data on both items have to be cleared and they should be ready to show all the data without dependencies ,
why bcoz ,the user is reliable to get data either knowing any one of vendor name or vendor number

plese reply ASAP

--
Thanks,
Chowdary

Su said...

Thank you very much for your wonderful blog & Revolving maps!

Zshan said...

Step 1
======

Create Dept_Pop_VO having Following SQL

Select Deptno,Deptno
from Dept

Step 2
======

Create Emp_Pop_VO having Following SQL

Select Empno,Empno,Deptno
from Emp


Step 3
======

Assign Emp_Pop_VO to your page's Associated AM


Step 4
======

Create messageChoicBean Namely Dept_PopList and Assign Following Properties

Picklist View Defination: oracle.apps.(your Dept_Pop_VO path).Dept_Pop_VO
Picklist Display Attribute: Deptno
Picklist Value Attribute: Deptno

Step 5
======

Create messageChoicBean Namely Emp_PopList and Assign Following Properties

Picklist View Instance: Emp_Pop_VO1
Picklist Display Attribute: Empno
Picklist Value Attribute: Empno


Step 6
======

Create PPR on Dept_PopList

Step 7
======

in ProcessFormRequest On Specified PPR get the value of Dept_PopList i.e Deptno in a variable i.e var_Deptno

Step 8
======

set where clause of Emp_Pop_VO like this

OAViewObject var_vo = (OAViewObject) am.findViewObject("Emp_Pop_VO1");
var_vo.setwhereClause("Deptno = " + var_Deptno);
var_vo.executeQuery();

Regards
Zeeshan Hussain
Sr Technical Consultant

Григорий Мудрагель said...

Thank you very much, it`s helped me.

Mohamed said...

Thank you

Raghav Verma said...

Hi Mukul,

I am trying to design dependent picklist but getting error - "java.sql.sqlexception missing in or out parameter at index 1".

Below Query used in VO -

select fnd.user_name,fnd.user_id from fnd_user fnd where
fnd.employee_id=nvl(:1,fnd.employee_id)

And i have below code in AM. But not able to root cause it.
vo.setWhereClauseParam(0,title);
vo.executeQuery();

Can you please help help me with the same.

Thanks in advance..!!