public boolean isDebug =false;
//public int uploadrst =1;//add 100301 -1 fail 0 mdsexits 1 successfull
public static final String RCS_ID ="$Header: uploadfile.jsp 115.1 2001/10/29 20:50:06 pkm ship $";
public static final boolean RCS_ID_RECORDED =VersionInfo.recordClassVersion(RCS_ID,"oa_html.jsp.fnd");
/*
* Helper function to print out messages.//contentType="text/html; charset=UTF-8"
*/
void msg ( JspWriter o, String message ) throws IOException
{
if (isDebug) o.println(message + "
");
System.out.println(message + "
");
}
* Helper function to print out messages.//contentType="text/html; charset=UTF-8"
*/
void msg ( JspWriter o, String message ) throws IOException
{
if (isDebug) o.println(message + "
");
System.out.println(message + "
");
}
/*
* Helper function to print out exceptions.
*/
void msgException ( JspWriter o, Exception e ) throws IOException
{
if (isDebug) {
o.println("
}
}
* Helper function to print out exceptions.
*/
void msgException ( JspWriter o, Exception e ) throws IOException
{
if (isDebug) {
o.println("
");");
e.printStackTrace(new PrintWriter(o));
o.println("
}
}
public String[] splitString(String source, char useChar) {
List list = new ArrayList();
String sub;
String[] result;
List list = new ArrayList();
String sub;
String[] result;
if (source.charAt(0) == useChar)
source ="nodata"+ source;
if (source.charAt(source.length() - 1) == useChar)
source = source.substring(0, source.length() - 1);
int start = 0;
int end = source.indexOf(useChar);
while (end > 0) {
sub = source.substring(start, end);
list.add(sub);
start = end + 1;
end = source.indexOf(useChar, start);
}
sub = source.substring(start, source.length());
list.add(sub);
result = new String[list.size()];
Iterator iter = list.iterator();
int i = 0;
while (iter.hasNext()) {
result[i++] = (String) iter.next();
}
return result;
}
source ="nodata"+ source;
if (source.charAt(source.length() - 1) == useChar)
source = source.substring(0, source.length() - 1);
int start = 0;
int end = source.indexOf(useChar);
while (end > 0) {
sub = source.substring(start, end);
list.add(sub);
start = end + 1;
end = source.indexOf(useChar, start);
}
sub = source.substring(start, source.length());
list.add(sub);
result = new String[list.size()];
Iterator iter = list.iterator();
int i = 0;
while (iter.hasNext()) {
result[i++] = (String) iter.next();
}
return result;
}
// some column content include comma ,such as "XXX,XX" ,need to process specially
public String[] specialSplitString(String source, char useChar) {
List list = new ArrayList();
String[] result;
int startPos=source.indexOf("\"");
int commaPos=source.indexOf(useChar);
int endPos=source.indexOf("\"",startPos+1);
String columnData=null;
while(source.length()>0)
{
if(startPos==0&&endPos>1)
{
columnData=source.substring(startPos+1,endPos);
String columnData=null;
while(source.length()>0)
{
if(startPos==0&&endPos>1)
{
columnData=source.substring(startPos+1,endPos);
list.add(columnData);
if(endPos {
source=source.substring(endPos+2);
}
else
{
source="";
}
}
else if(commaPos {
columnData=source.substring(0,commaPos);
if(endPos {
source=source.substring(endPos+2);
}
else
{
source="";
}
}
else if(commaPos {
columnData=source.substring(0,commaPos);
list.add(columnData);
source=source.substring(commaPos+1);
}
else if(commaPos!=-1)
{
columnData=source.substring(0,commaPos);
source=source.substring(commaPos+1);
}
else if(commaPos!=-1)
{
columnData=source.substring(0,commaPos);
source=source.substring(commaPos+1);
list.add(columnData);
}
else
{
columnData=source.substring(0);
list.add(columnData);
}
else
{
columnData=source.substring(0);
list.add(columnData);
source="";
}
startPos=source.indexOf("\"");
commaPos=source.indexOf(useChar);
endPos=source.indexOf("\"",startPos+1);
// System.out.println("startPos: "+startPos+", endPos: "+endPos+", commaPos: "+commaPos);
// System.out.println("line is "+line);
}
// for(int i=0;i // {
// System.out.println("list["+i+"]= "+list.get(i));
// }
source="";
}
startPos=source.indexOf("\"");
commaPos=source.indexOf(useChar);
endPos=source.indexOf("\"",startPos+1);
// System.out.println("startPos: "+startPos+", endPos: "+endPos+", commaPos: "+commaPos);
// System.out.println("line is "+line);
}
// for(int i=0;i // {
// System.out.println("list["+i+"]= "+list.get(i));
// }
result = new String[list.size()];
Iterator iter = list.iterator();
int i = 0;
while (iter.hasNext()) {
result[i++] = (String) iter.next();
}
return result;
}
Iterator iter = list.iterator();
int i = 0;
while (iter.hasNext()) {
result[i++] = (String) iter.next();
}
return result;
}
//Date/*
public static class StringOrDate {
public static String dateToString(Date date, String type) {
String str = null;
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
if (type.equals("SHORT")) {
// 07-1-18
format = DateFormat.getDateInstance(DateFormat.SHORT);
str = format.format(date);
} else if (type.equals("MEDIUM")) {
// 2007-1-18
format = DateFormat.getDateInstance(DateFormat.MEDIUM);
str = format.format(date);
} else if (type.equals("FULL")) {
// 2007年1月18日 星期四
format = DateFormat.getDateInstance(DateFormat.FULL);
str = format.format(date);
}
return str;
}
public static Date stringToDate(String str)
{
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
// Fri Feb 24 00:00:00 CST 2012
date = format.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
// 2012-02-24
date = java.sql.Date.valueOf(str);
return date;
}
public static void main(String[] args) {
Date date = new Date();
System.out.println(StringOrDate.dateToString(date, "MEDIUM"));
String str = "2012-2-24";
System.out.println(StringOrDate.stringToDate(str));
}
}
*/
//Date
%>
public static class StringOrDate {
public static String dateToString(Date date, String type) {
String str = null;
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
if (type.equals("SHORT")) {
// 07-1-18
format = DateFormat.getDateInstance(DateFormat.SHORT);
str = format.format(date);
} else if (type.equals("MEDIUM")) {
// 2007-1-18
format = DateFormat.getDateInstance(DateFormat.MEDIUM);
str = format.format(date);
} else if (type.equals("FULL")) {
// 2007年1月18日 星期四
format = DateFormat.getDateInstance(DateFormat.FULL);
str = format.format(date);
}
return str;
}
public static Date stringToDate(String str)
{
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
// Fri Feb 24 00:00:00 CST 2012
date = format.parse(str);
} catch (ParseException e) {
e.printStackTrace();
}
// 2012-02-24
date = java.sql.Date.valueOf(str);
return date;
}
public static void main(String[] args) {
Date date = new Date();
System.out.println(StringOrDate.dateToString(date, "MEDIUM"));
String str = "2012-2-24";
System.out.println(StringOrDate.stringToDate(str));
}
}
*/
//Date
%>
/*
* This example uses the WebRequestUtil.validateContext convenience
* method to take care of handling all the validate session calls
* for you.
*/
//Declare some arguments
int uploadrst =1;//add 100301 -1 fail 0 mdsexits 1 successfull
int LineNo;
int v_receipt_line_num= 0;
int v_supplier_num = 0;
int v_seq_num = 0;
/*
Float v_RECEIVED_QTY = 0;
Float v_PO_PRICE = 0;
Float v_amount = 0;*/
// float v_uph = 0;
//float v_workingday = 0;
/*
String v_receipt_num = null;
String v_supplier_site_code = null;
String v_invoice_num = null;
String v_custom_num = null;
*/
String v_TRXDateStr = null;
String v_InvoiceDateStr =null ;
String v_GLDateStr =null ;
int sOrgID = 0;
int sUserID = 0;
int sBatchNum = 0;
//Declare some arguments
//* only fit for oracle web container
try
{
WebAppsContext ctx = null;
// Call validateContext at the start of each page to make sure the
// session is still valid. If it is not, then return an error.
//
ctx = WebRequestUtil.validateContext(request, response);
if (ctx == null)
{
msg(out,"Failed to authenticate session");
return;
}
//
// If validation succeeds, first set the client encoding in order
// to ensure translateability. After that, get the database
// connection to use from the WebAppsContext.
//
WebRequestUtil.setClientEncoding(response, ctx);
Connection conn=ctx.getJDBCConnection();
//PreparedStatement statement = null;
ResultSet rs = null;
// session is still valid. If it is not, then return an error.
//
ctx = WebRequestUtil.validateContext(request, response);
if (ctx == null)
{
msg(out,"Failed to authenticate session");
return;
}
//
// If validation succeeds, first set the client encoding in order
// to ensure translateability. After that, get the database
// connection to use from the WebAppsContext.
//
WebRequestUtil.setClientEncoding(response, ctx);
Connection conn=ctx.getJDBCConnection();
//PreparedStatement statement = null;
ResultSet rs = null;
if ( conn==null )
{
msg(out,"Failed to get JDBC connection");
return;
}
{
msg(out,"Failed to get JDBC connection");
return;
}
//
// Your application-specific code goes here. In this simple
// example we just select the user and responsibility from dual.
//
PreparedStatement ps=null;
PreparedStatement ps_del=null;
CallableStatement cs=null;
try
{
String sql = "select fnd_global.user_name, fnd_global.resp_name, fnd_profile.VALUE ('BG_ORG_ID'), fnd_global.user_id , BG_AP_INVOICES_UPLOAD_S.NEXTVAL " + "from dual";
ps =conn.prepareStatement(sql);
// Your application-specific code goes here. In this simple
// example we just select the user and responsibility from dual.
//
PreparedStatement ps=null;
PreparedStatement ps_del=null;
CallableStatement cs=null;
try
{
String sql = "select fnd_global.user_name, fnd_global.resp_name, fnd_profile.VALUE ('BG_ORG_ID'), fnd_global.user_id , BG_AP_INVOICES_UPLOAD_S.NEXTVAL " + "from dual";
ps =conn.prepareStatement(sql);
rs = ps.executeQuery();
if ( rs.next() )
{
ResourceStore resStore = ctx.getResourceStore();
String userLabel = resStore.getResourceText("FND", "USER");
String respLabel = resStore.getResourceText("FND", "RESPONSIBILITY");
sOrgID=rs.getInt(3);
sUserID=rs.getInt(4);
sBatchNum=rs.getInt(5);
msg(out,"Successfully authenticated session!");
msg(out, "
");
msg(out, userLabel+ " - " + rs.getString(1));
msg(out, respLabel+ " - " + rs.getString(2));
msg(out, "Organization ID - " + sOrgID);
msg(out, "User ID - " + sUserID);
msg(out, "Batch Number - " + sBatchNum);
msg(out, "
");
}
// Main upload routine
Vector vLineData = new Vector(); // Record all data
String line;
String[] inData =null;
String tempString = null;
//request.setCharacterEncoding("GB2312");
BufferedReader in = new BufferedReader(new InputStreamReader(request.getInputStream(),"GBK"));//,"UTF-8"
Locale locale=null;
boolean existsData=false;
LineNo = 0;
// Close PS
if(ps!=null)
{
try
{
ps.close();
}
catch(Exception e1)
{}
}
/*//Delete data
ps_del =conn.prepareStatement("DELETE FROM bg_kpi_hcwh where organization_id = ? and bg_year = ? and bg_week = ? and version = ?");
ps_del.setInt(1, sOrgID);
ps_del.setInt(2, v_year);
ps_del.setInt(3, v_week);
ps_del.setInt(4, v_version);
if ( ps_del.executeUpdate() != 1)
{
msg(out, "Record Deletion Error!");
}
// Close ps_del
if(ps_del!=null)
{
try
{
ps_del.close();
}
catch(Exception e1)
{}
}
*/
if(ps_del!=null)
{
try
{
ps_del.close();
}
catch(Exception e1)
{}
}
*/
//
ps =conn.prepareStatement("INSERT INTO BG_UPLOAD_AP_INVOICE_TEMP(creation_date,created_by, last_update_date, last_updated_by, organization_id, RECEIPT_NUM, RECEIPT_LINE_NUM, TRANSACTION_DATE, SUPPLIER_NUM, SUPPLIER_SITE_CODE, PO_NUMBER, PR_NUMBER, SUB_PAR_NUMBER, ITEM_NUMBER, CUST_ITEM_NUMBER ," + "ACCOUNT_CODE, TRANSACTION_TYPE, RECEIVED_QTY, UOM_CODE, CURRENCY_CODE, PO_PRICE , AMOUNT, INVOICE_TAX, WAYBILL, SEQ, INVOICE_DATE, INVOICE_NUM , CUSTOM_NUM, PAYMENT_TERM, GL_DATE,row_num,BATCH_NUM,INVOICE_TYPE ) VALUES (SYSDATE,?,SYSDATE,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
//13 columns
while ( (line = in.readLine())!=null)
{
LineNo++;
msg(out, "line - " + LineNo + "-" + line);
if (LineNo >= 6)
{
if ( ((line.trim()).equals("")) == false )
{
inData = splitString(line, ',');
for(int p=0;p msg(out, "inData["+p+"]="+inData[p]);
ps =conn.prepareStatement("INSERT INTO BG_UPLOAD_AP_INVOICE_TEMP(creation_date,created_by, last_update_date, last_updated_by, organization_id, RECEIPT_NUM, RECEIPT_LINE_NUM, TRANSACTION_DATE, SUPPLIER_NUM, SUPPLIER_SITE_CODE, PO_NUMBER, PR_NUMBER, SUB_PAR_NUMBER, ITEM_NUMBER, CUST_ITEM_NUMBER ," + "ACCOUNT_CODE, TRANSACTION_TYPE, RECEIVED_QTY, UOM_CODE, CURRENCY_CODE, PO_PRICE , AMOUNT, INVOICE_TAX, WAYBILL, SEQ, INVOICE_DATE, INVOICE_NUM , CUSTOM_NUM, PAYMENT_TERM, GL_DATE,row_num,BATCH_NUM,INVOICE_TYPE ) VALUES (SYSDATE,?,SYSDATE,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
//13 columns
while ( (line = in.readLine())!=null)
{
LineNo++;
msg(out, "line - " + LineNo + "-" + line);
if (LineNo >= 6)
{
if ( ((line.trim()).equals("")) == false )
{
inData = splitString(line, ',');
for(int p=0;p msg(out, "inData["+p+"]="+inData[p]);
if (inData.length > 1) // i.e. not blank line or end of file
{
/*Delete data
if (LineNo == 7)
{
ps_del =conn.prepareStatement("DELETE FROM bg_kpi_hcwh where organization_id = ? and bg_year = ? and bg_week = ? and version = ?");
{
/*Delete data
if (LineNo == 7)
{
ps_del =conn.prepareStatement("DELETE FROM bg_kpi_hcwh where organization_id = ? and bg_year = ? and bg_week = ? and version = ?");
ps_del.setInt(1, sOrgID);
ps_del.setInt(2, v_year);
ps_del.setInt(3, v_week);
ps_del.setInt(4, v_version);
if ( ps_del.executeUpdate() != 1)
{
msg(out, "Record Deletion Error!");
}
ps_del.setInt(2, v_year);
ps_del.setInt(3, v_week);
ps_del.setInt(4, v_version);
if ( ps_del.executeUpdate() != 1)
{
msg(out, "Record Deletion Error!");
}
// Close ps_del
if(ps_del!=null)
{
try
{
ps_del.close();
}
catch(Exception e1)
{}
}
}
*/
if(ps_del!=null)
{
try
{
ps_del.close();
}
catch(Exception e1)
{}
}
}
*/
//get the available locale
/*
Locale[] availLocale=Locale.getAvailableLocales();
for(int h=0;h {
locale=availLocale[h];
// System.out.println("try locale: "+locale);
DateFormat formatter=new SimpleDateFormat("dd-MMM-yy",locale);
try{
formatter.parse(v_TRXDateStr);
formatter.parse(v_InvoiceDateStr);
formatter.parse(v_GLDateStr);
// System.out.println("Find: "+locale);
}
catch(Exception e)
{ }
}*/
// get the available locale
/* SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
Date b=sdf.parse(birthdate);
java.sql.Date b2=new java.sql.Date(b.getTime());
ps.setDate(6,b2);*/
//Validate data null or not //
DateFormat myDateFormat = new SimpleDateFormat("dd-MMM-yy",Locale.US);
float fqty=0;
float price=0;
float amount=0;
// Insert Record//
ps.setInt(1, sUserID);
ps.setInt(2, sUserID);
ps.setInt(3, sOrgID);
//Receipt number 4
if (inData[0].trim().equals("") == false)
{ ps.setString(4,inData[0]); }
else
{ ps.setNull(4, Types.VARCHAR);
}
//Receipt line number 5
if (inData[1].trim().equals("") == false)
{ v_receipt_line_num= Integer.parseInt(inData[1]);
ps.setInt(5,v_receipt_line_num);
}
else
{ ps.setNull(5, Types.INTEGER);}
//Transaction date 6
if (inData[2].trim().equals("") == false)
{ v_TRXDateStr = inData[2];
java.util.Date TRXDate= myDateFormat.parse(v_TRXDateStr);
java.sql.Date TRX_Date=new java.sql.Date(TRXDate.getTime());
ps.setDate(6, TRX_Date); //TRANSACTION_DATE
}
else
{
ps.setNull(6, Types.DATE);
}
//Supplier Num ?:7 indata[3]
if (inData[3].trim().equals("") == false)
{
ps.setString(7,inData[3]);
}
else
{ ps.setNull(7, Types.VARCHAR);}
//Supplier site code ?:8 indata[4]
if (inData[4].trim().equals("") == false)
{ ps.setString(8,inData[4]);
}
else
{ ps.setNull(8, Types.VARCHAR);}
//PO Number ?:9 indata[5]
if (inData[5].trim().equals("") == false)
{ ps.setString(9,inData[5]);
}
else
{ ps.setNull(9, Types.VARCHAR);}
//PR Number ?:10 indata[6]
if (inData[6].trim().equals("") == false)
{ ps.setString(10,inData[6]);
}
else
{ ps.setNull(10, Types.VARCHAR);}
//Sub Par Number ?:11 indata[7]
if (inData[7].trim().equals("") == false)
{ ps.setString(11,inData[7]);
}
else
{ ps.setNull(11, Types.VARCHAR);}
//Belton P/N ?:12 indata[8]
if (inData[8].trim().equals("") == false)
{ ps.setString(12,inData[8]);
}
else
{ ps.setNull(12, Types.VARCHAR);}
//Customer P/N ?:13 indata[9]
if (inData[9].trim().equals("") == false)
{ ps.setString(13,inData[9]);
}
else
{ ps.setNull(13, Types.VARCHAR);}
//Account code ?:14 indata[10]
if (inData[10].trim().equals("") == false)
{ ps.setString(14,inData[10]);
}
else
{ ps.setNull(14, Types.VARCHAR);}
//Transaction type ?:15 indata[11]
if (inData[11].trim().equals("") == false)
{ ps.setString(15,inData[11]);
}
else
{ ps.setNull(15, Types.VARCHAR);}
//Received Qty ?:16 indata[12]
if (inData[12].trim().equals("") == false)
{
//java.lang.Float//
fqty = Float.parseFloat(inData[12]);
ps.setFloat(16,fqty);
}
else
{ ps.setNull(16,Types.FLOAT);}
//UOM Code ?:17 indata[13]
if (inData[13].trim().equals("") == false)
{ ps.setString(17,inData[13]);
}
else
{ ps.setNull(17, Types.VARCHAR);}
//Currency Code ?:18 indata[14]
if (inData[14].trim().equals("") == false)
{ ps.setString(18,inData[14]);
}
else
{ ps.setNull(18, Types.VARCHAR);}
//PO Price ?:19 indata[15]
if (inData[15].trim().equals("") == false)
{
price= Float.parseFloat(inData[15]);
ps.setFloat(19,price);
}
else
{ ps.setNull(19,Types.FLOAT);}
//Amount ?:20 indata[16]
if (inData[16].trim().equals("") == false)
{
amount= Float.parseFloat(inData[16]);
ps.setFloat(20,amount);
}
else
{ ps.setNull(20,Types.FLOAT);}
//Invoice Tax ?:21 indata[17]
if (inData[17].trim().equals("") == false)
{ ps.setString(21,inData[17]);
}
else
{ ps.setNull(21, Types.VARCHAR);}
//Waybill ?:22 indata[18]
if (inData[18].trim().equals("") == false)
{ ps.setString(22,inData[18]);
}
else
{ ps.setNull(22, Types.VARCHAR);}
//Seq Number ?:23 indata[19]
if (inData[3].trim().equals("") == false)
{ int seq_num= Integer.parseInt(inData[19]);
ps.setInt(23,seq_num);
}
else
{ ps.setNull(23, Types.INTEGER);}
//Invoice Date ?:24 indata[20]
if (inData[20].trim().equals("") == false)
{ v_InvoiceDateStr = inData[20];
java.util.Date InvoiceDate = myDateFormat.parse(v_InvoiceDateStr);
java.sql.Date Invoice_date=new java.sql.Date(InvoiceDate.getTime());
ps.setDate(24, Invoice_date);
}
else
{
ps.setNull(24, Types.DATE);
}
//Invoice Number ?:25 indata[21]
if (inData[21].trim().equals("") == false)
{ ps.setString(25,inData[21]);
}
else
{ ps.setNull(25, Types.VARCHAR);}
//Custom Number ?:26 indata[22]
if (inData[22].trim().equals("") == false)
{ ps.setString(26,inData[22]);
}
else
{ ps.setNull(26, Types.VARCHAR);}
//Payment Term ?:27 indata[23]
if (inData[23].trim().equals("") == false)
{ ps.setString(27,inData[23]);
}
else
{ ps.setNull(27, Types.VARCHAR);}
//GL Date ?:28 indata[24]
if (inData[24].trim().equals("") == false)
{ v_GLDateStr= inData[24];
java.util.Date GLDate= myDateFormat.parse(v_GLDateStr);
java.sql.Date GL_Date=new java.sql.Date(GLDate.getTime());
ps.setDate(28, GL_Date);
}
else
{
ps.setNull(28, Types.DATE);
}
ps.setInt(29,LineNo);
ps.setInt(30,sBatchNum);
//Invoice Type ?:31 indata[25]
if (inData[25].trim().equals("") == false)
{ ps.setString(31,inData[25]);
}
else
{ ps.setNull(31, Types.VARCHAR);}
//Validate data null or not //
if ( ps.executeUpdate() != 1)
{
msg(out, "Record Insert Error!");
}
}
Date b=sdf.parse(birthdate);
java.sql.Date b2=new java.sql.Date(b.getTime());
ps.setDate(6,b2);*/
//Validate data null or not //
DateFormat myDateFormat = new SimpleDateFormat("dd-MMM-yy",Locale.US);
float fqty=0;
float price=0;
float amount=0;
// Insert Record//
ps.setInt(1, sUserID);
ps.setInt(2, sUserID);
ps.setInt(3, sOrgID);
//Receipt number 4
if (inData[0].trim().equals("") == false)
{ ps.setString(4,inData[0]); }
else
{ ps.setNull(4, Types.VARCHAR);
}
//Receipt line number 5
if (inData[1].trim().equals("") == false)
{ v_receipt_line_num= Integer.parseInt(inData[1]);
ps.setInt(5,v_receipt_line_num);
}
else
{ ps.setNull(5, Types.INTEGER);}
//Transaction date 6
if (inData[2].trim().equals("") == false)
{ v_TRXDateStr = inData[2];
java.util.Date TRXDate= myDateFormat.parse(v_TRXDateStr);
java.sql.Date TRX_Date=new java.sql.Date(TRXDate.getTime());
ps.setDate(6, TRX_Date); //TRANSACTION_DATE
}
else
{
ps.setNull(6, Types.DATE);
}
//Supplier Num ?:7 indata[3]
if (inData[3].trim().equals("") == false)
{
ps.setString(7,inData[3]);
}
else
{ ps.setNull(7, Types.VARCHAR);}
//Supplier site code ?:8 indata[4]
if (inData[4].trim().equals("") == false)
{ ps.setString(8,inData[4]);
}
else
{ ps.setNull(8, Types.VARCHAR);}
//PO Number ?:9 indata[5]
if (inData[5].trim().equals("") == false)
{ ps.setString(9,inData[5]);
}
else
{ ps.setNull(9, Types.VARCHAR);}
//PR Number ?:10 indata[6]
if (inData[6].trim().equals("") == false)
{ ps.setString(10,inData[6]);
}
else
{ ps.setNull(10, Types.VARCHAR);}
//Sub Par Number ?:11 indata[7]
if (inData[7].trim().equals("") == false)
{ ps.setString(11,inData[7]);
}
else
{ ps.setNull(11, Types.VARCHAR);}
//Belton P/N ?:12 indata[8]
if (inData[8].trim().equals("") == false)
{ ps.setString(12,inData[8]);
}
else
{ ps.setNull(12, Types.VARCHAR);}
//Customer P/N ?:13 indata[9]
if (inData[9].trim().equals("") == false)
{ ps.setString(13,inData[9]);
}
else
{ ps.setNull(13, Types.VARCHAR);}
//Account code ?:14 indata[10]
if (inData[10].trim().equals("") == false)
{ ps.setString(14,inData[10]);
}
else
{ ps.setNull(14, Types.VARCHAR);}
//Transaction type ?:15 indata[11]
if (inData[11].trim().equals("") == false)
{ ps.setString(15,inData[11]);
}
else
{ ps.setNull(15, Types.VARCHAR);}
//Received Qty ?:16 indata[12]
if (inData[12].trim().equals("") == false)
{
//java.lang.Float//
fqty = Float.parseFloat(inData[12]);
ps.setFloat(16,fqty);
}
else
{ ps.setNull(16,Types.FLOAT);}
//UOM Code ?:17 indata[13]
if (inData[13].trim().equals("") == false)
{ ps.setString(17,inData[13]);
}
else
{ ps.setNull(17, Types.VARCHAR);}
//Currency Code ?:18 indata[14]
if (inData[14].trim().equals("") == false)
{ ps.setString(18,inData[14]);
}
else
{ ps.setNull(18, Types.VARCHAR);}
//PO Price ?:19 indata[15]
if (inData[15].trim().equals("") == false)
{
price= Float.parseFloat(inData[15]);
ps.setFloat(19,price);
}
else
{ ps.setNull(19,Types.FLOAT);}
//Amount ?:20 indata[16]
if (inData[16].trim().equals("") == false)
{
amount= Float.parseFloat(inData[16]);
ps.setFloat(20,amount);
}
else
{ ps.setNull(20,Types.FLOAT);}
//Invoice Tax ?:21 indata[17]
if (inData[17].trim().equals("") == false)
{ ps.setString(21,inData[17]);
}
else
{ ps.setNull(21, Types.VARCHAR);}
//Waybill ?:22 indata[18]
if (inData[18].trim().equals("") == false)
{ ps.setString(22,inData[18]);
}
else
{ ps.setNull(22, Types.VARCHAR);}
//Seq Number ?:23 indata[19]
if (inData[3].trim().equals("") == false)
{ int seq_num= Integer.parseInt(inData[19]);
ps.setInt(23,seq_num);
}
else
{ ps.setNull(23, Types.INTEGER);}
//Invoice Date ?:24 indata[20]
if (inData[20].trim().equals("") == false)
{ v_InvoiceDateStr = inData[20];
java.util.Date InvoiceDate = myDateFormat.parse(v_InvoiceDateStr);
java.sql.Date Invoice_date=new java.sql.Date(InvoiceDate.getTime());
ps.setDate(24, Invoice_date);
}
else
{
ps.setNull(24, Types.DATE);
}
//Invoice Number ?:25 indata[21]
if (inData[21].trim().equals("") == false)
{ ps.setString(25,inData[21]);
}
else
{ ps.setNull(25, Types.VARCHAR);}
//Custom Number ?:26 indata[22]
if (inData[22].trim().equals("") == false)
{ ps.setString(26,inData[22]);
}
else
{ ps.setNull(26, Types.VARCHAR);}
//Payment Term ?:27 indata[23]
if (inData[23].trim().equals("") == false)
{ ps.setString(27,inData[23]);
}
else
{ ps.setNull(27, Types.VARCHAR);}
//GL Date ?:28 indata[24]
if (inData[24].trim().equals("") == false)
{ v_GLDateStr= inData[24];
java.util.Date GLDate= myDateFormat.parse(v_GLDateStr);
java.sql.Date GL_Date=new java.sql.Date(GLDate.getTime());
ps.setDate(28, GL_Date);
}
else
{
ps.setNull(28, Types.DATE);
}
ps.setInt(29,LineNo);
ps.setInt(30,sBatchNum);
//Invoice Type ?:31 indata[25]
if (inData[25].trim().equals("") == false)
{ ps.setString(31,inData[25]);
}
else
{ ps.setNull(31, Types.VARCHAR);}
//Validate data null or not //
if ( ps.executeUpdate() != 1)
{
msg(out, "Record Insert Error!");
}
}
}
}
}
}
}
//Call procedure for Import Receords to AP invoice interface //
cs =conn.prepareCall("{call APXINVIMP_BG_1(?)}");
cs.setInt(1, sBatchNum);
// cs.setInt(2, v_week);
cs.execute();
cs.close();
cs.setInt(1, sBatchNum);
// cs.setInt(2, v_week);
cs.execute();
cs.close();
//Call procedure for Import Receords to AP invoice interface //
// Print out imported records
out.println("The following records have been imported to the system.
");
// out.println("Version: " + Integer.toString(v_version));
out.print("
ps.setInt(1, sBatchNum);
rs = null;
rs = ps.executeQuery();
//Print Data//
out.println("The following records have been imported to the system.
");
// out.println("Version: " + Integer.toString(v_version));
out.print("
Receipt Number | Receipt line Number | Transaction Date | Supplier Name | Supplier Number | Supplier Site Code | PO Number | PR Number | Sub Par Number | Belton P/N | Customer P/N | Item Description | Account Code | Transaction Type | Received Qty | UOM | Currency | PO Price | Amount | Invoice Tax | Waybill | Seq | Invoice Date | Invoice Number | Custom Number | Payment Term | GL Date | Invoice Type | Status | Message |
//Print Data//
ps =conn.prepareStatement("select m.RECEIPT_NUM,to_char(m.RECEIPT_LINE_NUM) ,to_char(m.TRANSACTION_DATE ,'DD-Mon-YY'), p.vendor_name , m.SUPPLIER_NUM ,m.SUPPLIER_SITE_CODE ,m.PO_NUMBER ,m.PR_NUMBER ,m.SUB_PAR_NUMBER ,m.ITEM_NUMBER, m.CUST_ITEM_NUMBER , m.ACCOUNT_CODE, m.TRANSACTION_TYPE , to_char(m.RECEIVED_QTY) , m.UOM_CODE , m.CURRENCY_CODE ,to_char(m.PO_PRICE) ,to_char(m.AMOUNT) ,m.INVOICE_TAX ,m.WAYBILL, to_char(m.SEQ), to_char(m.INVOICE_DATE,'DD-Mon-YY'), m.INVOICE_NUM , m.CUSTOM_NUM, m.payment_term, to_char(m.GL_DATE ,'DD-Mon-YY'),m.INVOICE_TYPE from "
+" BG_UPLOAD_AP_INVOICE_TEMP m ,po_vendors p " +
" where m.BATCH_NUM = ? and p.segment1=m.supplier_num "+" order by m.row_num asc " );
ps =conn.prepareStatement("select m.RECEIPT_NUM,to_char(m.RECEIPT_LINE_NUM) ,to_char(m.TRANSACTION_DATE ,'DD-Mon-YY'), p.vendor_name , m.SUPPLIER_NUM ,m.SUPPLIER_SITE_CODE ,m.PO_NUMBER ,m.PR_NUMBER ,m.SUB_PAR_NUMBER ,m.ITEM_NUMBER, m.CUST_ITEM_NUMBER , m.ACCOUNT_CODE, m.TRANSACTION_TYPE , to_char(m.RECEIVED_QTY) , m.UOM_CODE , m.CURRENCY_CODE ,to_char(m.PO_PRICE) ,to_char(m.AMOUNT) ,m.INVOICE_TAX ,m.WAYBILL, to_char(m.SEQ), to_char(m.INVOICE_DATE,'DD-Mon-YY'), m.INVOICE_NUM , m.CUSTOM_NUM, m.payment_term, to_char(m.GL_DATE ,'DD-Mon-YY'),m.INVOICE_TYPE from "
+" BG_UPLOAD_AP_INVOICE_TEMP m ,po_vendors p " +
" where m.BATCH_NUM = ? and p.segment1=m.supplier_num "+" order by m.row_num asc " );
ps.setInt(1, sBatchNum);
rs = null;
rs = ps.executeQuery();
while ( rs.next() )
{
out.println(" ");
out.println(" " + rs.getString(1) + "");
out.println(" " + rs.getString(2) + "");
out.println(" " + rs.getString(3) + "");
out.println(" " + rs.getString(4) + "");
out.println(" " +rs.getString(5) + "");
out.println(" " + rs.getString(6) + "");
out.println(" " + rs.getString(7) + "");
out.println(" " + rs.getString(8) + "");
out.println(" " + rs.getString(9) + "");
out.println(" " + rs.getString(10) + "");
out.println(" " + rs.getString(11) + "");
out.println(" " + rs.getString(12) + "");
out.println(" " + rs.getString(13) + "");
out.println(" " + rs.getString(14) + "");
out.println(" " + rs.getString(15) + "");
out.println(" " + rs.getString(16) + "");
out.println(" " + rs.getString(17) + "");
out.println(" " + rs.getString(18) + "");
out.println(" " + rs.getString(19) + "");
out.println(" " + rs.getString(20) + "");
out.println(" " + rs.getString(21) + "");
out.println(" " + rs.getString(22) + "");
out.println(" " + rs.getString(23) + "");
out.println(" " + rs.getString(24) + "");
out.println(" " + rs.getString(25) + "");
out.println(" " + rs.getString(26) + "");
out.println(" " + rs.getString(27) + "");
// out.println(" " + rs.getString(28) + "");, m.status , m.message
// out.println(" " + rs.getString(29) + "");
// out.println(" " + rs.getFloat(10) + "");
out.println(" ");
}
{
out.println(" ");
out.println(" " + rs.getString(1) + "");
out.println(" " + rs.getString(2) + "");
out.println(" " + rs.getString(3) + "");
out.println(" " + rs.getString(4) + "");
out.println(" " +rs.getString(5) + "");
out.println(" " + rs.getString(6) + "");
out.println(" " + rs.getString(7) + "");
out.println(" " + rs.getString(8) + "");
out.println(" " + rs.getString(9) + "");
out.println(" " + rs.getString(10) + "");
out.println(" " + rs.getString(11) + "");
out.println(" " + rs.getString(12) + "");
out.println(" " + rs.getString(13) + "");
out.println(" " + rs.getString(14) + "");
out.println(" " + rs.getString(15) + "");
out.println(" " + rs.getString(16) + "");
out.println(" " + rs.getString(17) + "");
out.println(" " + rs.getString(18) + "");
out.println(" " + rs.getString(19) + "");
out.println(" " + rs.getString(20) + "");
out.println(" " + rs.getString(21) + "");
out.println(" " + rs.getString(22) + "");
out.println(" " + rs.getString(23) + "");
out.println(" " + rs.getString(24) + "");
out.println(" " + rs.getString(25) + "");
out.println(" " + rs.getString(26) + "");
out.println(" " + rs.getString(27) + "");
// out.println(" " + rs.getString(28) + "");, m.status , m.message
// out.println(" " + rs.getString(29) + "");
// out.println(" " + rs.getFloat(10) + "");
out.println(" ");
}
out.println("
"); //Print Data//
}
catch ( SQLException sqle )
{
msgException(out, sqle);
out.println(sqle);
}
finally
{ // Close PS
if(ps!=null)
{
try{
ps.close();
}
catch(Exception e1)
{}
}
if ( ctx != null ) ctx.freeWebAppsContext();
/*
if (conn != null)
conn.close();
*/
}
catch ( SQLException sqle )
{
msgException(out, sqle);
out.println(sqle);
}
finally
{ // Close PS
if(ps!=null)
{
try{
ps.close();
}
catch(Exception e1)
{}
}
if ( ctx != null ) ctx.freeWebAppsContext();
/*
if (conn != null)
conn.close();
*/
}
}
catch ( Exception e )
{
out.println("File uploaded Failed! Please check the input data source.");
out.println(e);
}
%>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9182041/viewspace-708075/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9182041/viewspace-708075/