Home » Developer & Programmer » Forms » Standard LOV Customization through CUSTOM.pll HELP
Standard LOV Customization through CUSTOM.pll HELP [message #189180] Wed, 23 August 2006 09:25 Go to next message
nazir.txstate
Messages: 4
Registered: February 2006
Location: CHICAGO
Junior Member
Hello,

I am currently trying to override the LOV dropdown of an Oracle 11.5.10 form using the custom.pll. The example I am using is as follows:

procedure event(event_name varchar2) is

form_name varchar2(30) := name_in('system.current_form'); --added by Nazir
block_name varchar2(30) := name_in('system.cursor_block'); --added by Nazir
item_name varchar2(100) := name_in('system.current_item');

v_sql varchar2(5000);
result Number;
rec_id RecordGroup;
v_login_company VARCHAR2(50);


begin
if(event_name = 'WHEN-NEW-ITEM-INSTANCE') then
if (form_name = 'ARXTWMAI' and block_name = 'TGW_HEADER') then
if( item_name = 'CTT_TYPE_NAME_MIR') then

v_login_company := fnd_profile.value('LN_LOGIN_COMPANY');

if (v_login_company = '0000') then
--fnd_message.set_string('Please enter description'); --Testing fnd message. This works fine. Message displayed.
--fnd_message.show;


v_sql := 'select ctt.cust_trx_type_id cust_trx_type_id, ctt.name name, ctt.description description, ctt.type class, arl_class.meaning class_meaning, ctt.accounting_affect_flag open_receivables_flag, ctt.post_to_gl post_to_gl_flag, ctt.allow_freight_flag allow_freight_flag' ||
',ctt.creation_sign creation_sign, ctt.allow_overapplication_flag allow_overapplication_flag, ctt.natural_application_only_flag natural_application_only_flag, ctt.tax_calculation_flag tax_calculation_flag, arl_status.meaning default_status' ||
',arl_print.meaning default_printing_option, rat.name default_term from ar_lookups arl_print, ar_lookups arl_status, ar_lookups arl_class, ra_terms rat, ra_cust_trx_types ctt ' ||
'where '||''''||'INVOICE_PRINT_OPTIONS'||'''' || '= arl_print.lookup_type ' ||
' and ctt.default_printing_option = arl_print.lookup_code' ||
' and' || ''''||'INVOICE_TRX_STATUS'||'''' ||'= arl_status.lookup_type' ||
' and ctt.default_status = arl_status.lookup_code and ctt.default_term = rat.term_id(+) ' ||
' and' || ''''||'INV/CM'||'''' || '= arl_class.lookup_type ' ||
' and ctt.type = arl_class.lookup_code' ||
' and ctt.type in ( ' || '''' || 'DEP' || '''' || ',' || '''' || 'GUAR' || '''' || ',' || '''' || 'INV' || '''' || ',' || '''' || 'CM' || '''' || ',' || '''' || 'DM' || '''' || ')' ||
' and ( ctt.type not in (' || '''' || 'DEP' || '''' || ',' || '''' || 'GUAR' || '''' || ') or ctt.accounting_affect_flag =' || '''' || 'Y' || '''' || ')' ||
' order by ctt.name' ;



rec_id := CREATE_GROUP_FROM_QUERY( 'TRANS_TYPE_LNCUSTOM', v_sql );
result := POPULATE_GROUP(FIND_GROUP( 'TRANS_TYPE_LNCUSTOM'));
SET_LOV_PROPERTY('ARXTWTGW_TRANSACTION_TYPE', GROUP_NAME, 'TRANS_TYPE_LNCUSTOM' );
end if;
end if;
end if;
end if;
end event;

I am getting following errors:

FRM-41072: Cannot create group TRANS_TYPE_LNCUSTOM

I checked my query with DBMS outputs and then copy that output and ran it as a query in toad and it worked fine.

Has anyone done this before that could help me resolve the problem??

Thanks,
Nazir.
Re: Standard LOV Customization through CUSTOM.pll HELP [message #189311 is a reply to message #189180] Thu, 24 August 2006 02:34 Go to previous messageGo to next message
sameer_am2002
Messages: 129
Registered: September 2002
Senior Member
rec_id := CREATE_GROUP_FROM_QUERY( 'TRANS_TYPE_LNCUSTOM', v_sql );
..
Before this statement you first do something like this..
create a dummy table wd col1 varchar2(4000) ;
then
insert into dummy values (v_sql) ;

check this statement in toad then..

Try to find if there is any misquotes..or if space is required between something .which u didnt provide..
You can only trace by this method..coz in this way you will actually be seeing..ur bind variable values..
Re: Standard LOV Customization through CUSTOM.pll HELP [message #189467 is a reply to message #189180] Thu, 24 August 2006 13:42 Go to previous message
nazir.txstate
Messages: 4
Registered: February 2006
Location: CHICAGO
Junior Member
Hello,

I figured out the problem and it works perfectly fine. Basically, it does create a record group but once I move out of the item and come back to the item, it was trying to create a record group with the same name again. So I had to add an extra check in my statement:


if id_null('record group')
then only create the group;


Thanks for your input Sameer.

Thanks Guys,
Nazir.
Previous Topic: Informatica Repository Creation
Next Topic: Icons not displayed on forms Oracle 9i when running
Goto Forum:
  


Current Time: Fri Dec 02 18:24:05 CST 2016

Total time taken to generate the page: 0.17675 seconds