EBS creates a common upload implementation process

--Use the IDE to write the following Java file, suffix Cuxpoexcelimport.java file, upload to Java_home and compile to Oracle database Loadjava-r-f-user apps/apps@ebsprod/u02/ Oracle/prod/apps/apps_st/comn/java/classes/cuxpoexcelimport.java, and then call it in the form

--Need to prepare Jxl.jar file, upload to Java_home and compile Loadjava-r-f-user apps/apps@ebsprod/u02/oracle/prod/apps/apps_st/comn/java/ Classes/jxl.jar

Import Java.sql.Blob;
Import java.sql.Connection;
Import Java.sql.ResultSet;
Import java.sql.Statement;
Import Java.text.DecimalFormat;
Import JXL. Cell;
Import JXL. Numbercell;
Import JXL. Sheet;
Import JXL. Workbook;
Import Oracle.jdbc.driver.OracleDriver;


public class Cuxexcelimport {


public static String excelimport (int fileID) {
try {
Connection C = new Oracledriver (). DefaultConnection ();
Statement s = c.createstatement ();
ResultSet rs = s
. ExecuteQuery ("Select File_data from fnd_lobs WHERE file_id ="
+ FileID);
Rs.next ();


Blob blob = Rs.getblob ("File_data");
Workbook wb = Workbook.getworkbook (Blob.getbinarystream ());


Sheet bomsheet = wb.getsheet (0);


int bomrows = Bomsheet.getrows ();
int bomcolumn = Bomsheet.getcolumns ();


for (int i = 1; i < bomrows; i++) {
String ItemNumber = Bomsheet.getcell (1, i). getcontents ();
String Uomcode = Bomsheet.getcell (2, I). getcontents ();


Cell Ci2 = Bomsheet.getcell (3, I);
Numbercell NC2 = (Numbercell) ci2;
Double D2 = Nc2.getvalue ();
DecimalFormat Decimal2 = new DecimalFormat ("#.##");
String Quantity = Decimal2.format (D2);


Cell Ci3 = Bomsheet.getcell (4, I);
Numbercell nc3 = (Numbercell) ci3;
Double d3 = Nc3.getvalue ();
DecimalFormat Decimal3 = new DecimalFormat ("#.##");
String UnitPrice = Decimal3.format (D3);


String Allowflag = Bomsheet.getcell (5, I). getcontents ();


s = c.createstatement ();
ResultSet Inputidrs = s
. ExecuteQuery ("Select Cux_upload_s.nextval inputID from dual");
int inputid = 0;
while (Inputidrs.next ()) {
inputID = Inputidrs.getint ("inputID");
}
Statement S1 = c.createstatement ();
ResultSet Inputuserid = S1
. ExecuteQuery ("Select fnd_global.user_id user_id from dual");
int user_id = 0;
while (Inputuserid.next ()) {
user_id = Inputuserid.getint ("user_id");
}


String query = "INSERT into cux_upload_input_t (segment1,primary_uom_code,quantity,unit_price,allow_flag,input_id, USER_ID) "
+ "VALUES ('")
+ ItemNumber
+ "','"
+ Uomcode
+ "',"
+ Quantity
+ ","
+ UnitPrice
+ ",'"
+ Allowflag
+ "',"
+ inputID + "," + user_id + ")";
S.executeupdate (query);
}
C.commit ();
Wb.close ();
Rs.close ();
S.close ();
C.close ();
Return "Succeed";
} catch (Exception e) {
return E.getmessage ();
}
}

}

--The following is a call to upload a program unit in a form

PROCEDURE Upload_file is
ACCESS_ID number;
L_server_url VARCHAR2 (255);
L_url VARCHAR2 (255);
L_GFM_ID number;
Button_choice number;
F_name VARCHAR2 (10);
V_col_flag VARCHAR2 (20);


BEGIN
--Get Process ID
access_id: = Fnd_gfm.authorize (NULL);


--Get server address
Fnd_profile.get (' apps_web_agent ', l_server_url);


--Get web address
L_url: = RTRIM (L_server_url, '/') | |
'/fnd_file_upload.displaygfmform?access_id= ' | |
To_char (access_id) | | Chr (38) | | ' L_server_url= ' | | L_server_url;


IF (L_url is NULL) then
RAISE form_trigger_failure;
END IF;


--Open Web page
Fnd_utilities.open_url (L_url);


--This action was waiting for user select file, and check upgrade
Fnd_message.set_name (' fnd ', ' atchmt-file-upload-complete ');


Button_choice: = fnd_message.question (button1 = "YES",
Button2 = NULL,
Button3 = ' NO ',
DEFAULT_BTN = 1,
CANCEL_BTN = 3,
icon = ' question ');


--Get File ID
l_gfm_id: = fnd_gfm.get_file_id (access_id);


IF l_gfm_id is not NULL and
l_gfm_id > 0 Then
IF Button_choice = 1 Then
l_gfm_id: = l_gfm_id; --File is selected and upgrade
--The file is now uploaded to the Fnd_lobs table, the contents are stored in the File_data field, and the BLOB format
ELSE
L_GFM_ID: = 0; --File is selected and not upgrade
END IF;
ELSE
L_GFM_ID: =-1; --File is not selected
END IF;


--fnd_message.debug (l_gfm_id);


IF l_gfm_id > 0 Then
SELECT Upper (Substr (file_name, InStr (file_name, '. ', 1, 1) + 1))
Into F_name
From Fnd_lobs
WHERE file_id = l_gfm_id;

IF f_name <> ' XLS ' then
Fnd_message.debug (' You upload for ' | | f_name | | ' Format file, please choose XLS format file re-upload ');
RAISE form_trigger_failure;
ELSE
GOTO Upload_flag;
END IF;
<<upload_flag>>
V_col_flag: = Cux_po_handle_util_pkg.po_excel_import (l_gfm_id);
FORMS_DDL (' commit ');


END IF;


END;

--The following is the wording of the package in Pkg

--package

----calling Java to upload files
FUNCTION Po_excel_import (I_fileid in number) RETURN VARCHAR2;

--package body

/**************************************************************
* Function Description:
* This function is primarily used to invoke Java classes to implement file import
**************************************************************/
FUNCTION Po_excel_import (I_fileid in number) RETURN VARCHAR2 is
LANGUAGE JAVA NAME ' cuxexcelimport.excelimport (int) return String ';