EBS创建通用上传实现过程

--用ide编写如下Java文件，后缀为CUXPoExcelImport.java文件，上传到JAVA_HOME然后编译到oracle数据库loadjava -r -f -user apps/apps@ebsprod /u02/oracle/PROD/apps/apps_st/comn/java/classes/CUXPoExcelImport.java，再在form中调用即可

--需要准备jxl.jar文件，上传到JAVA_HOME然后编译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();
}
}

}

--以下为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);


  -- 获取服务器地址
  fnd_profile.get('APPS_WEB_AGENT', l_server_url);


  --获取网页地址
  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;


  -- 打开网页
  fnd_utilities.open_url(l_url);


  -- this action is 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
      --此时文件已上传到fnd_lobs表中，内容存放在file_data字段，blob格式
    ELSE
      l_gfm_id := 0; -- File is selected but 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('你上传的为' || f_name || '格式文件,请选择XLS格式文件重新上传');
      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;

--以下为pkg中包的写法

--package

  ----调用JAVA上传文件
  FUNCTION po_excel_import(i_fileid IN NUMBER) RETURN VARCHAR2;

--package body

  /**************************************************************
  *功能描述：
  *    此函数主要用于调用java类，实现文件导入
  **************************************************************/
  FUNCTION po_excel_import(i_fileid IN NUMBER) RETURN VARCHAR2 IS
    LANGUAGE JAVA NAME 'CUXExcelImport.ExcelImport(int) return String';