Oracle Applications: eLite API/Tables UI

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE></TITLE>

</HEAD>

<BODY bgcolor=LightGoldenrodYellow>
<%@ page language="java" import="java.sql.*,java.io.*,java.util.*" %>

<%
//Get all the variables in the variable block into array
// Trying to get the values and display after which it will be posted to the database
//Getting all param Names
Enumeration enum_param=request.getParameterNames();
Enumeration var_enum_param=request.getParameterNames();
String v_tmp_param_name=request.getParameter("v_var_name");
String v_tmp_param_value=request.getParameter("v_var_value");
String v_tmp_param_type=request.getParameter("v_var_type");
//Get Hidden field count
String v_tmp_hidden_glo_var=request.getParameter("v_glo_var_val");
if(v_tmp_hidden_glo_var==null)
{
v_tmp_hidden_glo_var="1";
}
//Get Parameters being passed
String v_object_name=request.getParameter("Obj_Names");
// Sub list for procedures of a package
String v_Obj_names_proc=request.getParameter("Obj_names_proc");
String v_object_type;
//String str;
v_object_type=request.getParameter("Obj_types");
Connection con=null;
try
{
//Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Class.forName("oracle.jdbc.driver.OracleDriver");

//con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:AMOORCL","hr","hr");
//con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:AMOORCL","scott","tiger");
//con=DriverManager.getConnection("jdbc:oracle:thin:@ap6267rt.us.oracle.com:1612:PQD12MS1","apps","pqeapps");
con=DriverManager.getConnection("jdbc:oracle:thin:@ap6264rt.us.oracle.com:1617:PQD12MS1","apps","pqeapps");

PreparedStatement stmt;
ResultSet rs;
ResultSetMetaData rsmd;

%>

<script type="text/javascript" language="javascript">
// object name should be nullfied
var v_flag=0;

function setObjNameNull()
{
document.frm_Objects.Obj_Names.value=null;
//document.frm_Objects.usr_file_name.value=null;
//document.frm_Objects.usr_pre_qry_val.value=null;
//document.frm_Objects.usr_post_qry_val.value=null;

//alert(document.frm_Objects.v_var_name==null||Obj_Names.value);
// If variable declaration block has null values
//if(typeof() == ‘undefined’)
/*
if(document.frm_Objects.v_glo_var_val.value>1)
{

for(var i=1;i<=(document.frm_Objects.v_glo_var_val.value-1);i++)
{
//Alerting Blank value -- Use flag to submit it.

var v_name="document.frm_Objects.v_var_name";
var v_value="document.frm_Objects.v_var_value";
var v_type="document.frm_Objects.v_var_type";
// -1 as default value is 1 and other boxes should be count-1;
v_name=v_name+(document.frm_Objects.v_glo_var_val.value -1)+".value";
v_value=v_value+(document.frm_Objects.v_glo_var_val.value -1)+".value";
v_type=v_type+(document.frm_Objects.v_glo_var_val.value -1)+".value";
//alert(v_name);
if(v_name==null||v_value==null||v_type==null)
{
alert('Null values present');
v_flag=0;
}
else
{
v_flag=1;
}

}//for

if(v_flag==1)
{
document.frm_Objects.submit();
}
else
{
//Dont Submit;
}
}
else
{
document.frm_Objects.submit();
}
*/

//Form Submission

document.frm_Objects.submit();
stmt=null;
rs=null;
}

function setPkgObjNameNull()
{
if(document.frm_Objects.Obj_names_proc==null)
{

}else
{
document.frm_Objects.Obj_names_proc.value=null;
}
//document.frm_Objects.Obj_Names.value=null;

//alert(document.frm_Objects.Obj_Names.value);
document.frm_Objects.submit();

}

function AddField()
{
// Step 0 : Add check box if needed to delete the Variable
//Step 1 : Create a Variable Field
//Step 2 : Create a Value Field
//Step 3 : Create DataType Field
//////////////////////////////////////
//
//CODE CHANGE
//
/////////////////////////////////////
/*
//var_area.innerHTML=var_area.innerHTML+"Variable Name : ";
//var new_element=document.createElement("<INPUT TYPE='text' value='TextField' name='text1' />");
var cnt=document.frm_Objects.v_glo_var_val.value;

var new_element=document.createElement("<INPUT TYPE='checkbox' name='v_var_chk_name"+cnt+"' />");
var_area.appendChild(new_element);

//Step 1 : Create a Variable Field
var_area.innerHTML=var_area.innerHTML+"Variable Name : ";
//var new_element=document.createElement("<INPUT TYPE='text' value='TextField' name='text1' />");
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_name"+cnt+"' />");
var_area.appendChild(new_element);
//var_area.innerHTML=var_area.innerHTML+"</br>";

//Step 2 : Create a Value Field
var_area.innerHTML=var_area.innerHTML+"Variable Value : ";
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_value"+cnt+"' />");
var_area.appendChild(new_element);
//var_area.innerHTML=var_area.innerHTML+"</br>";

//Step 3 : Create DataType Field
var_area.innerHTML=var_area.innerHTML+"Variable Type : ";
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_type"+cnt+"' />");
var_area.appendChild(new_element);
var_area.innerHTML=var_area.innerHTML+"</br>";

*/
//var_area.innerHTML=var_area.innerHTML+"Variable Name : ";
//var new_element=document.createElement("<INPUT TYPE='text' value='TextField' name='text1' />");
////////////////////////////////////////////
//
//
//OLD CODE THAT WORKED
//
//
///////////////////////////////////////////////
//var cnt=document.frm_Objects.v_glo_var_val.value;

var new_element=document.createElement("<INPUT TYPE='checkbox' name='v_var_chk_name' />");
var_area.appendChild(new_element);

//Step 1 : Create a Variable Field
var_area.innerHTML=var_area.innerHTML+"Variable Name : ";
//var new_element=document.createElement("<INPUT TYPE='text' value='TextField' name='text1' />");
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_name' />");
var_area.appendChild(new_element);
//var_area.innerHTML=var_area.innerHTML+"</br>";

//Step 2 : Create a Value Field
var_area.innerHTML=var_area.innerHTML+"Variable Value : ";
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_value' />");
var_area.appendChild(new_element);
//var_area.innerHTML=var_area.innerHTML+"</br>";

//Step 3 : Create DataType Field
var_area.innerHTML=var_area.innerHTML+"Variable Type : ";
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_type' />");
var_area.appendChild(new_element);
var_area.innerHTML=var_area.innerHTML+"</br>";

document.frm_Objects.v_glo_var_val.value=++document.frm_Objects.v_glo_var_val.value;
}

function AddField3(one,two,three)
{
// Step 0 : Add check box if needed to delete the Variable
//Step 1 : Create a Variable Field
//Step 2 : Create a Value Field
//Step 3 : Create DataType Field

//var_area.innerHTML=var_area.innerHTML+"Variable Name : ";
//var new_element=document.createElement("<INPUT TYPE='text' value='TextField' name='text1' />");
var new_element=document.createElement("<INPUT TYPE='checkbox' name='v_var_chk_name' />");
var_area.appendChild(new_element);

//Step 1 : Create a Variable Field
var_area.innerHTML=var_area.innerHTML+"Variable Name : ";
//var new_element=document.createElement("<INPUT TYPE='text' value='TextField' name='text1' />");
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_name' value="+one+" />");
var_area.appendChild(new_element);
//var_area.innerHTML=var_area.innerHTML+"</br>";

//Step 2 : Create a Value Field
var_area.innerHTML=var_area.innerHTML+"Variable Value : ";
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_value' value="+two+" />");
var_area.appendChild(new_element);
//var_area.innerHTML=var_area.innerHTML+"</br>";

//Step 3 : Create DataType Field
var_area.innerHTML=var_area.innerHTML+"Variable Type : ";
var new_element=document.createElement("<INPUT TYPE='text' name='v_var_type' value="+three+" />");
var_area.appendChild(new_element);
var_area.innerHTML=var_area.innerHTML+"</br>";
}

</script>

<form name="frm_Objects" action="FirstPage_Table.jsp" method="POST">


<%= "<input type='hidden' name='v_glo_var_val' value="+v_tmp_hidden_glo_var+" />" %>
<fieldset>
<legend>VARIABLE DECLARATION</legend>


<div id="var_area">



<%
// Added code to handle if no Variables are declared yet
if(v_tmp_param_name!=null||v_tmp_param_value!=null||v_tmp_param_type!=null)
{
//Retrieved object from enumeration, took it into a string and printed it
// Getting Parameter Names and Printing their Values
// First 3 variables will be of the type (var name, var value,var type)
int count=0;
//Enumeration e_temp=var_enum_param;
Enumeration e_temp=request.getParameterNames();
while(e_temp.hasMoreElements())
{
count=count+1;
Object obj=e_temp.nextElement();
}

// Counting number of parameters
String[] v_get_param_value_tmp=new String[count];
String[] v_get_param_value=new String[count];
// Adding code to handle if there are no check boxes

count=0;
String v_get_param_name;
while(var_enum_param.hasMoreElements())
{
Object obj=var_enum_param.nextElement();
v_get_param_name=obj.toString();
//count used for every 3 values submit

// If param value is v_value,type display it and its values
//get ParameterValues(String);
if(v_get_param_name.equals("v_var_name")||v_get_param_name.equals("v_var_value")||v_get_param_name.equals("v_var_type"))
{
//Get Parameter value

v_get_param_value=request.getParameterValues(v_get_param_name);

//addding values to array - Length would be 2
// Length of array is the set's of variables present ( 3 sets of name value and type )
// Order of adding is always 2 var name,2 value, 2 type
for(int i=0;i<v_get_param_value.length;i++)
{
v_get_param_value_tmp[count]=v_get_param_value[i];
count=count+1;
// Display Value
}
} // end of if
}//end of while
//}// end of if

for(int i=0;i<v_get_param_value.length;i++)
{
//{ Based on length array is divided .. retrieve based on order of (i+length+length,i+length, i)
%>

<%="<script language=\"javascript\">AddField3("+v_get_param_value_tmp[i+v_get_param_value.length+v_get_param_value.length]+","+v_get_param_value_tmp[i+v_get_param_value.length]+","+v_get_param_value_tmp[i]+");</script>"%>
<%
//}
}
}
%>

</div>

<table border=1>
<tr>
<td>
<input type="button" name="createTextField" value="Add Variable" onclick=AddField() >
</td>

<td>
<input type="button" name="delTextField" value="Delete Variable" onclick=DeleteField() >
</td>

<td>
<input type="button" name="ShowValues" value="PrintValues" onclick=printValues() >
</td>


</tr>
</table>


</fieldset>

<fieldset>
<legend>PRE PROCESSING BLOCK</legend>

<TEXTAREA name=usr_pre_qry_val rows=6 cols=150></TEXTAREA>


</fieldset>


<fieldset>
<legend>Object Details </legend>


Object Type :

<select onchange="setObjNameNull()" name=Obj_types>
<%
if(v_object_type==null)
{
%>
<option value="NULL" selected></option>
<option value="TABLE">TABLE</option>
<option value="PROCEDURE">PROCEDURE</option>
<option value="FUNCTION">FUNCTION</option>
<option value="PACKAGE">PACKAGE</option>
<%
}
else if(v_object_type.equals("TABLE"))
{
%>
<option value="NULL"></option>
<option value="TABLE" selected>TABLE</option>
<option value="PROCEDURE">PROCEDURE</option>
<option value="FUNCTION">FUNCTION</option>
<option value="PACKAGE">PACKAGE</option>
<%
}
else if(v_object_type.equals("PROCEDURE"))
{
%>
<option value="NULL"></option>
<option value="TABLE">TABLE</option>
<option value="PROCEDURE" selected>PROCEDURE</option>
<option value="FUNCTION">FUNCTION</option>
<option value="PACKAGE">PACKAGE</option>
<%
}
else if(v_object_type.equals("FUNCTION"))
{
%>
<option value="NULL"></option>
<option value="TABLE">TABLE</option>
<option value="PROCEDURE" >PROCEDURE</option>
<option value="FUNCTION" selected>FUNCTION</option>
<option value="PACKAGE">PACKAGE</option>
<%
}
else if(v_object_type.equals("PACKAGE"))
{
%>
<option value="NULL"></option>
<option value="TABLE">TABLE</option>
<option value="PROCEDURE" >PROCEDURE</option>
<option value="FUNCTION">FUNCTION</option>
<option value="PACKAGE" selected>PACKAGE</option>
<%
}
else
{
%>
<option value="NULL"></option>
<option value="TABLE">TABLE</option>
<option value="PROCEDURE">PROCEDURE</option>
<option value="FUNCTION">FUNCTION</option>
<option value="PACKAGE">PACKAGE</option>
<%
}
%>
</select>
<br>
Object Names :




<select name=Obj_Names onchange="setPkgObjNameNull()" >

<%="<option value='' selected></option>" %>
<%
if(v_object_type!=null)
{
stmt=con.prepareStatement("SELECT 1 FROM dual");
int flag=1;
if(v_object_type.equals("TABLE"))
{
stmt=con.prepareStatement("SELECT distinct object_name FROM user_objects where (object_name like 'WSH%' or object_name like 'MTL%' or object_name like 'INV%' or object_name like 'OM%') and object_type IN('SYNONYM')");
//stmt=con.prepareStatement("SELECT object_name FROM user_objects where object_type IN('"+v_object_type+"','SYNONYM')");
}
else if(v_object_type.equals("PROCEDURE")||v_object_type.equals("FUNCTION"))
{
//stmt=con.prepareStatement("SELECT 2 FROM dual");

stmt=con.prepareStatement("SELECT object_name FROM user_objects where object_type = '"+v_object_type+"'");
}
else if(v_object_type.equals("PACKAGE"))
{
//stmt=con.prepareStatement("SELECT 4 FROM dual");
// Resultset will be handled by code itself
flag=0;
//stmt=con.prepareStatement("SELECT DISTINCT PACKAGE_name FROM user_arguments WHERE package_name IS NOT null ");
stmt=con.prepareStatement("SELECT DISTINCT PACKAGE_name FROM user_arguments WHERE (package_name like 'WSH%' or package_name like 'OM%' or package_name like 'FND%' or package_name like 'INV%' or package_name like 'OM%')");
// Select the List of values in Object Names Packages-
// DISPLAY PACKAGES
rs=stmt.executeQuery();
while(rs.next())
{
String v_obj_pkg=rs.getString(1);
if (v_object_name!=null && v_object_name.equals(v_obj_pkg))
{
%>
<%="<option value="+v_obj_pkg+" selected>"+v_obj_pkg+"</option>" %>
<%
}
else
{
%>
<%="<option value="+v_obj_pkg+">"+v_obj_pkg+"</option>" %>
<%
}
}
rs.close();
// CREATE OPTION CLASS that will contain procedures for the package selected.
// Procedure names derived from Package Name
// Note Object Names Used here
// CLOSE SELECT LIST AFTER FILLING IN PACKAGES
// CREATE NEW SELECT LIST FOR SELECTING PROCEDURES ONLY FOR THE OPTION PACKAGES
stmt=con.prepareStatement("SELECT distinct object_name FROM user_arguments WHERE package_name ='"+v_object_name+"'");

%>
<%="</select>"%>
<select name="Obj_names_proc" onchange="javascript:document.frm_Objects.submit();">"

<%
// CODE FOR FILLING THE NEW SELECT LIST WITH PROCEDURES RELATED TO THE PACKAGE SELECTED
rs=stmt.executeQuery();
%>
<%="<option value=\"\"</option>" %>
<%
while(rs.next())
{
String v_obj_proc_lst_name=rs.getString(1);
//v_obj_proc_lst is the the select list created on the fly.

if (v_object_name!=null && v_Obj_names_proc!=null && v_Obj_names_proc.equals(v_obj_proc_lst_name))
{
// Select the option is it matches the list of values present to be highlighted, else select none
%>
<%="<option value="+v_obj_proc_lst_name+" selected>"+v_obj_proc_lst_name+"</option>" %>
<%
}
else
{
%>
<%="<option value="+v_obj_proc_lst_name+">"+v_obj_proc_lst_name+"</option>" %>
<%
}
}
rs.close();
//CLOSING SELECT LIST FOR PROCEDURES WITHIN PACAKGE
%>

<%= "</select>" %>
<%

}
// Package has its own result set for others this result set logic will work
// This logic is for displaying TABLES,FUNCTIONS and PROCEDURES handled using flag value
if (flag==1)
{
rs=stmt.executeQuery();
while(rs.next())
{
String v_obj_lst_name=rs.getString(1);
if (v_object_name!=null && v_object_name.equals(v_obj_lst_name))
{
%>
<%="<option value="+v_obj_lst_name+" selected>"+v_obj_lst_name+"</option>" %>
<%
}
else
{
%>
<%="<option value="+v_obj_lst_name+">"+v_obj_lst_name+"</option>" %>
<%
}
}
rs.close();
}

}// end of if i think
%>

</select>
<hr>
<br>

<p> </p>
<table border=0 width="100%">

<%

// Adding code for table columns and parameters
if(v_object_type!=null && v_object_name!=null)
{
int flag=1;
stmt=con.prepareStatement("Select 1 from dual");
//stmt=null;
if(v_object_type.equals("TABLE"))
{
// Table should display
// Column Name - Value - Data Type - Length - Precision - Nullable or not
// Select Statement - SELECT column_name,data_type,data_length,data_precision,Nullable
stmt=con.prepareStatement("SELECT column_name,data_type,data_length,data_precision,nullable FROM all_tab_cols where table_name = '"+v_object_name+"'");
flag=0;
rs=stmt.executeQuery();
//Printing volumn names using meta data
rsmd=rs.getMetaData();
int numcols=rsmd.getColumnCount();
%>
<%= "<TR>" %>
<%
for(int i=1;i<=numcols;i++)
{
%>
<%= "<TH align='center'> <font color=blue>"+rsmd.getColumnLabel(i)+"</font></TH>" %>
<%
}
%>
<%= "<TH aligh='center'> <font color=blue>ENTER VALUE</font></TH></TR>" %>
<%
// Closing result set meta data

// Printing Argument Names
while(rs.next())
{
String getColumnName= rs.getString(1);
String getDataType=rs.getString(2);
String getDataLength=rs.getString(3);
String getPrecision=rs.getString(4);
String getNullable=rs.getString(5);
%>
<%= "<tr align='center'><td>"+getColumnName+"</td>"%>
<%= "<td>"+getDataType+"</td>"%>
<%= "<td>"+getDataLength+"</td>"%>
<%= "<td>"+getPrecision+"</td>"%>
<%= "<td>"+getNullable+"</td>"%>
<%= "<td><input type='text' name="+getColumnName+" /></td></tr>"%>
<% // "<td><input type='text' name=\""+getArgRetType+"\" value=\""+getArgRetType+"\" /></td></tr>" %>
<%

}
rs.close();
}
else if(v_object_type.equals("PROCEDURE") || v_object_type.equals("FUNCTION")|| v_object_type.equals("PACKAGE"))
{
// Arguments should display
// Argument Name - Value- in_out-Data type - Data length - sequence of Reference - Default Value
// Select Clause order by sequence of the arguments
if(v_object_type.equals("PACKAGE"))
{
//stmt=con.prepareStatement("SELECT argument_name,in_out,data_type,data_length,sequence,default_value FROM user_arguments WHERE object_name = '"+v_Obj_names_proc+"' order by sequence");
//Package Name Procedure Name
//stmt=con.prepareStatement("SELECT argument_name,in_out,data_type,data_length,sequence,default_value FROM user_arguments WHERE object_name = '"+v_Obj_names_proc+"'and package_name='"+v_object_name+"' order by sequence");
//Aug 14th Commented
stmt=con.prepareStatement("SELECT argument_name,in_out,data_type,data_length,sequence,default_value FROM user_arguments WHERE object_name = '"+v_Obj_names_proc+"' and package_name ='"+v_object_name+"' and data_level=0 order by sequence");
}
else
{
//Procedure and function
//stmt=con.prepareStatement("SELECT argument_name,in_out,data_type,data_length,sequence,default_value FROM user_arguments WHERE object_name = '"+v_object_name+"' order by sequence");
// Changing on 14-Aug refining types query
// Obj_names_proc
stmt=con.prepareStatement("SELECT argument_name,in_out,data_type,data_length,sequence,default_value FROM user_arguments WHERE object_name = '"+v_Obj_names_proc+"' and package_name ='"+v_object_name+"' and data_level=0 order by sequence");
}
flag=0;
rs=stmt.executeQuery();
//Printing volumn names using meta data
rsmd=rs.getMetaData();
int numcols=rsmd.getColumnCount();
%>
<%= "<TR>" %>
<%
for(int i=1;i<=numcols;i++)
{
%>
<%= "<TH align='center'> <font color=blue>"+rsmd.getColumnLabel(i)+"</font></TH>" %>
<%
}
%>
<%= "<TH align='center'> <font color=blue> ENTER VALUE </font></TH></TR>" %>
<%
// Closing result set meta data

// Printing Argument Names
while(rs.next())
{
String getArgName= rs.getString(1);
String getArgRetType=rs.getString(2);
String getDataType=rs.getString(3);
String getDataLength=rs.getString(4);
String getsequence=rs.getString(5);
String getDefaultValue=rs.getString(6);
%>
<%//<%= "<tr align='center'><td>"+getArgName+"</td>" %>
<%= "<tr align='center'><td>"+getArgName+"</td>"%>
<%= "<td>"+getArgRetType+"</td>"%>
<%= "<td>"+getDataType+"</td>"%>
<%= "<td>"+getDataLength+"</td>"%>
<%= "<td>"+getsequence+"</td>"%>
<%= "<td>"+getDefaultValue+"</td>"%>
<%= "<td><input type='text' name="+getArgName+" /></td></tr>"%>
<% // "<td><input type='text' name=\""+getArgRetType+"\" value=\""+getArgRetType+"\" /></td></tr>" %>
<%

}
rs.close();
//flag=1;
}
else if(v_object_type.equals("PACKAGE"))
{

//CREATE A SELECT BOX to Take list of values that will POPULATE LIST OF PROCEDURES in a package
//stmt=con.prepareStatement("SELECT DISTINCT PACKAGE_name FROM user_arguments WHERE package_name IS NOT null");

}
// Flag has been introduced to avoid problems with procedure and table handling
if(flag==1)
{
rs=stmt.executeQuery();
while(rs.next())
{
%>
<%= "<tr><td>"+rs.getString(1)+"</td>"%>
<%= "<td><input type='text' name="+rs.getString(1)+" /></td></tr>"%>
<%
}
rs.close();
}

}

}
catch(IOException e)
{
out.println("1 IOException Occurred : "+e);
}
catch(SQLException s)
{
out.println("2 SQLException Occurred : "+s);
}
%>
</table align=center>
<hr>
<br>



</fieldset>



<fieldset>
<legend>POST PROCESSING BLOCK</legend>


<TEXTAREA name=usr_post_qry_val rows=6 cols=150></TEXTAREA>


</fieldset>
<p></p>
<center>
ENTER FILE NAME :
<input type="text" name="usr_file_name" >
Enter File Description<input type="text" name="usr_file_name" >
<input type="submit" name="usr_query_bttn" value="Save Step Details" onclick="setObjNameNull()">
</center>

</form>
</BODY>
</HTML>

Oracle Applications

Labels

Blog Archive

About Me

Tuesday, August 14, 2007

eLite API/Tables UI

0 comments: