OraFAQ Forum: Forms » Help in query Formating

Home » Developer & Programmer » Forms » Help in query Formating

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help in query Formating [message #239279]

Mon, 21 May 2007 22:24

u263066
Messages: 47
Registered: March 2007

Member

I am trying to bulit, a dyanamic list based of example, and in query I am passing a which is not formatted.

when I am calling this from NEW-FORM-INSTANCE then i will be getting FRM-41072, which itself mention wrong query.

here is the code:

PROCEDURE Maak_Lijst( P_ItemNaam Varchar2)--, P_SqlString varchar2)
 IS
  list_id     ITEM; 
  rg_id       RecordGroup; 
  rg_naam Varchar2(40) := 'RG' || to_char(abs(dbms_random.random)) /* generate a comletely random name*/; 
  errcode   Number;
  SqlString varchar2(2000);
  RESP_APPL_ID Varchar2(40);
  GL_SET_OF_BKS_ID Varchar2(40);
BEGIN 
  rg_id := Find_Group( rg_naam); 
  SqlString :=   'select period_name ||'', '' || set_of_books_id ||
		 from gl_period_statuses 
		 where closing_status =' ||''' ''||''O''||'''''||
		 'AND  adjustment_period_flag'||' = '||'''''||''N''||'''''||
		 'AND application_id '||'='||'RESP_APPL_ID'||
		 'AND set_of_books_id '||'='||'GL_SET_OF_BKS_ID';
	
	FND_MESSAGE.SET_STRING(SqlString);	
  FND_MESSAGE.SHOW;
  	 
  RESP_APPL_ID :=TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID'));
  GL_SET_OF_BKS_ID :=TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
  
  if id_Null(rg_id) then 
    rg_id := Create_Group_From_Query( rg_naam, SqlString); 
  end if; 
  -- 
  

 errcode := Populate_Group(rg_id); 
 -- 
 Populate_List( P_ItemNaam ,rg_id); 
exception 
  when others then 
  null;  -- handle your error here
END;

and here is part of query which is required to pass

select period_name 
		 from gl_period_statuses 
		 where closing_status = 'O'
		 AND  adjustment_period_flag = 'N'
		 AND application_id = RESP_APPL_ID
		 AND set_of_books_id =GL_SET_OF_BKS_ID

can anyone help where it goes wrong

thanks in advance

Report message to a moderator

Re: Help in query Formating [message #239477 is a reply to message #239279]

Tue, 22 May 2007 08:35

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

Hi,

I don't know if it will solve your problem but I did notice that your Sqlstring is not formatted correctly?

Currently it will be constructed literally as

select period_name ||', ' || set_of_books_id || 
from gl_period_statuses
where closing_status =' '||'O'||''
AND  adjustment_period_flag = ''||'N'||''
AND application_id =RESP_APPL_IDAND set_of_books_id =GL_SET_OF_BKS_ID

You might want to redo it - perhaps like this

SqlString :=   
     'select period_name, set_of_books_id
     from gl_period_statuses 
     where closing_status = ''O'' 
     AND  adjustment_period_flag = ''N''
     AND application_id = '|| RESP_APPL_ID||
     ' AND set_of_books_id = '||GL_SET_OF_BKS_ID;

Report message to a moderator

Previous Topic:	Control Item help
Next Topic:	Focus problem while opening new browser Forms 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 27 00:17:59 CDT 2024