Oracle Applications

SCM Course to Learn

2007-10-01T23:56:00.000-07:00

A. Supply chain management

1.Inventory Overview

Receipt to Issue life cycle - Receiving Inventory
Transferring Inventory - Issuing Inventory
Oracle Inventory Application Integration
Flexfield - Key Flexfield - Discriptive Flexfield

2.Defining and Maintaining Items Fundamentals

Inventory Organizations - Setting Up Locations
Organization Set up - Defining Item Item Attributes - Item Templates
Item Catogeries - Item Catalogue
Organization Assignment & Organization Item - Unit Of Measurements
Item Statuses - Item Profile Options

3.Inventory Controls Fundamentals

Inventory Control Overview - Subinventories - Locator Control
Revision Control - Lot Control - Serial Number

4.Issue and Transfer Material

Inventory Transactions - Sub Inventory Transfer
Miscellaneous Transaction - Interorganization Direct Shipment
Interorganization Intransit Shipment - Managing Receipts
Direct Receipts - Inspection Receipts - Move Order Process Flow
Transact Move Order - Managing Shipments

5.Replenishment Fundamentals

Focus Forecasting - Reorder- Point Planning
Reorder- Point Planning : Safety Stock - Min-Max Planning
Uses Of Replenishment Counting - Kanban Cards
Kanban Supply Status - Available to Promise

6.Issue and Transfer Material

ABC Analysis - Cycle Counting - Physical Inventory

B. Oracle Purchasing Fundamentals

1.Procure to Pay Lifecycle Overview

Procure to Pay Life Cycle - RFQs and Quotations
Requisitions - Supplier - Purchase Orders - Receiving
Invoicing - Payment - Organizations - Locations
RFQs and Questions
RFQs Types - Quotation Types - Purchasing Options
Approved Supplier List - Sourcing Rules - Supplier commodity
Requisitions - Approving Requisitions - Purchasing Options

2. Purchase Order

Purchase Order - Purchase Order Types
Standard Purchase Order - Standard P.O terms & conditions
Approve Standard Purchase Order - Blanket Purchase Agreement
Blanket Releases - Purchasing Options - Financial Options

3. Receiving

Receiving - Overview of Receiving Locations
Overview of Receipt Routing - Standard Receipt
Inspection Required - Receiving Options

4.Setup Steps

Setting Up Procure to Pay - Define Locations
Define Organizations - Define Responsibility
Define Job Positions & Position Hierarchies
Define Category Codes and Category Sets - Define Catalog Groups

C. Oracle Order Management

1. Order To Cash Lifecycle Overview
Order Life Cycle - Customer Entry Methods - Business Purposes
Order Management Process - Viewing and Managing Orders
Pricing Process

2. Over View of Oracle Management Process

Oracle Order Management Process - Oracle management Integration
Customer Relationships - Order Entry - Overview of Managing Orders

3. Inventory to Organization

Inventory Organizations - Define UOM - Defining Locations
Inventory Organizations Setup - Sub Inventory

4. Manage Parties and Customers Accounts

Manage Parties & Customer Accounts Party Model
Managing Parties - Party Sites - Customer Accounts
Using Standard and Quick Entry
Payment Terms

5. Enter Orders

Order creation Methods - Order Management Workflow
Defaulting Rules - Order Header - Line Transaction Type
Line Statuses - Entering Line Information - Scheduling
Scheduling- Reserve - Booking Orders

6. Managing Orders And Pricing

Managing Orders - Order Organizer Overview
Update Orders and Order Lines - Workflow Notification Overview
Apply Hold - Cancel Orders - Pricing - Order to Cash Process
Pricing Engine - Price List - Basic Pricing

7. Oracle Shipping Execution
Shipping Execution - Shipping Process - Pick Release
Delivery Management - Shipping - Ship Order
Pick Release Parameters - Ship Confirm
Shipping Execution - Release Sequence Rules
Shipping Documents Sets

Create Primary Key For Oracle Apps Table

2007-09-17T05:07:00.000-07:00

SELECT DISTINCT(PRIMARY_KEY_TYPE) FROM FND_PRIMARY_KEYS;

D - Developer or D-Surrogate

SQL> BEGIN
2 AD_DD.REGISTER_PRIMARY_KEY('INV','DEPT_PK','DEPT','Primary Key for Deparment','S','Y','Y');
3 END;
4 /

Create Oracle Apps Table with columns

2007-09-17T04:48:00.000-07:00

Creating a Dept Table as in Scott Schema

BEGIN
AD_DD.REGISTER_COLUMN('INV','DEPT','DEPTNO',1,'NUMBER',5,'Y','N');
END;
/
BEGIN
AD_DD.REGISTER_COLUMN('INV','DEPT','DNAME',2,'VARCHAR2',20,'Y','N');
END;
/
BEGIN
AD_DD.REGISTER_COLUMN('INV','DEPT','DLOC',3,'VARCHAR2',20,'Y','N');
END;
/

Create Oracle Apps Table

2007-09-17T04:43:00.000-07:00

BEGIN
AD_DD.REGISTER_TABLE('INV','DEPT','T');
END;
/

Select all Columns for a record Type

2007-08-16T23:27:00.000-07:00

SELECT * FROM user_arguments WHERE object_name LIKE 'CREATE_MOVE_ORDER_LINES%' AND package_name LIKE 'INV_MOVE_ORDER%' AND
SEQUENCE between
( SELECT SEQUENCE FROM user_arguments WHERE object_name LIKE 'CREATE_MOVE_ORDER_LINES%' AND package_name LIKE 'INV_MOVE_ORDER%' AND argument_name='P_TROLIN_TBL' AND data_level=0
AND
position = (SELECT position FROM user_arguments WHERE object_name LIKE 'CREATE_MOVE_ORDER_LINES%' AND package_name LIKE 'INV_MOVE_ORDER%' AND argument_name='P_TROLIN_TBL' AND data_level=0))
and
(SELECT SEQUENCE FROM user_arguments WHERE object_name LIKE 'CREATE_MOVE_ORDER_LINES%' AND package_name LIKE 'INV_MOVE_ORDER%' and
position =(SELECT position+1 FROM user_arguments WHERE object_name LIKE 'CREATE_MOVE_ORDER_LINES%' AND package_name LIKE 'INV_MOVE_ORDER%' AND argument_name='P_TROLIN_TBL' AND data_level=0) AND data_level=0) ;

eLite API/Tables UI

2007-08-14T06:34:00.000-07:00

<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>

Display Procedures Parameters expanding Types if any

2007-08-14T06:06:00.000-07:00

//Display Procedure with arguments expanding the types within it

SELECT
argument_name,
in_out,
data_type,
data_length,
sequence,
default_value
FROM
user_arguments
WHERE
object_name = 'CREATE_MOVE_ORDER_LINES'
and
package_name ='INV_MOVE_ORDER_PUB'
order by sequence;

// Display only type names without expanding record types
SELECT
argument_name,
in_out,
data_type,
data_length,
sequence,
default_value
FROM
user_arguments
WHERE
object_name = 'CREATE_MOVE_ORDER_LINES'
and
package_name ='INV_MOVE_ORDER_PUB'
and data_level=0
order by sequence;

Note : data_level=0 - says that display only plain data types

Read the source code for Packages

2007-08-13T23:38:00.000-07:00

// general format
SELECT *
FROM
user_source
WHERE rownum <10;

// Find the source for Method INV_LOT_API_PUB specification
SELECT *
FROM
user_source
WHERE NAME='INV_LOT_API_PUB';

// Get the types within Package Specification
SELECT *
FROM
user_source
WHERE NAME='INV_LOT_API_PUB'
AND
TEXT LIKE (' TYPE%');

Find arguments for a Procedure/Function within Package

2007-08-13T22:47:00.000-07:00

//Find arguments to be Passed for a Procedure/Function within Package
// Package Name : INV_LOT_API_PUB
// Object Name (Method Name ): INSERTLOT
SELECT
*
FROM
user_arguments
WHERE
package_name LIKE 'INV_LOT_API_PUB'
AND
object_name LIKE 'INSERTLOT';

Find Methods for known Package Name

2007-08-13T22:43:00.000-07:00

// Lists the methods and functions within the package

SELECT
distinct object_name
FROM
user_arguments
WHERE
package_name LIKE 'INV_LOT_API_PUB';

Find the PUB Packages for a Product in APPS

2007-08-13T22:40:00.000-07:00

// Find the Public Packages available in APPS for Product Inventory(Product Code : INV)

SELECT * FROM
user_objects
WHERE
object_name LIKE 'INV%PUB'
AND
object_type='PACKAGE';

2007-08-05T01:30:00.000-07:00

Welcome one and all.

I have decided to share the knowledge on Oracle Applications Inventory.
Will keep u updated with the postings.

Thanks,
Ajith