Declare
	  Sqltxt clob;
	  Lnth  Number;
Begin
	  Select Sql_text into sqltxt
	    From Sql_Collection where Rtrim(Short_Topic) = RTrim(:Replst1);
	    	   
	   Fetch_param(sqltxt);	   	   
	   Show_Window('ParamWin');
End;

PROCEDURE Fetch_Param(sqltxt Clob) IS
Pos1 Number(5) := 1;
pos2 Number(5);
partstr Varchar(32767);
Val  Varchar2(50);
i Number := 1;
Type P_arry is table of Varchar2(50) index by Binary_Integer;
Vp_arry P_arry;
Found Boolean;
BEGIN
	   Go_Block('Param');
	   Next_item;	   
	   Loop	  
	   	  Found := False; 	  
        Pos1 := dbms_lob.Instr(sqltxt,':',1,i);
        Exit When Pos1 = 0;            
        Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;
        Vp_arry(i) := dbms_lob.substr(sqltxt,pos1,pos2);
        If Vp_arry.count is not null and i > 1 Then
        	For j in 1..Vp_arry.count-1 Loop
        		If Vp_arry(j) = dbms_lob.substr(sqltxt,pos1,pos2) Then
        			 Found := True;
        			 exit;
        		End If;
          End Loop;
        End If;
        If not Found Then
           set_item_property(:System.Cursor_item,prompt_text,dbms_lob.substr(sqltxt,pos1,pos2));
           set_item_property(:System.Cursor_item,Prompt_font_style,Font_Underline);          
           next_item;
        End If;                        
        i := i + 1;        
    End Loop;
END;

 Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;

 If Vp_arry.count is not null and i > 1 Then 

 If Vp_arry.count is not null and i >= 1 Then 

Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;

EXCEPTION
  WHEN Others THEN
  Message(sqlerrm);

Pos1 := dbms_lob.Instr(sqltxt,'[B]:[/B]',1,i);

   select sql_text into sqltxt from sql_collection where sql_id = 1

SELECT a.custcode,
       a.custname,
       a.invoiceno,
       a.invoicedt,
       basicrate,
       a.chassisno,
       a.engineno,
       a.documentno,
       b.documentdate
  FROM (SELECT a.custcode,
               c.custname,
               a.invoiceno,
               a.invoicedt,
               b.chassisno,
               b.engineno,
               d.unitid,
               d.periodid,
               d.documentno,
               basicrate
          FROM invoice a, invoiceitem b, custmast c, chassisitem d
         WHERE a.unitid = :Unit_id
           AND a.invoicedt BETWEEN :Invoice_From_dt AND :Invoice_To_Dt
           AND a.unitid = b.unitid
           AND a.periodid = b.periodid
           AND a.invoiceno = b.invoiceno
           AND a.custcode = c.custcode
           AND c.private_customer = ''Y''
           AND b.chassisno = d.chassis_no
           AND b.engineno = d.engine_no
           AND a.unitid = d.unitid
           AND b.unitid = d.unitid) a,
       chassisarrival b
 WHERE a.unitid = b.unitid AND a.periodid = b.periodid AND a.documentno = b.documentno

Type P_arry is table of Varchar2(50) index by Binary_Integer;
Vp_arry P_arry;

Pos2 := Instr(dbms_lob.substr(sqltxt,Pos1,100),' ',1,1)-1;

:Invoice_From_dt 

  Select Sql_text into sqltxt
	    From Sql_Collection where Rtrim(Short_Topic) = RTrim(:Replst1);

  Select dbms_lob.substr(Sql_text) into sqltxt
	    From Sql_Collection where Rtrim(Short_Topic) = RTrim(:Replst1);