import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.
HibernateTransactionManager;
public class ScreenMethodSessionImp extends DefaultBaseManager
implements ScreenMethodSession{
/**50*/
public static Integer PAGE_NUMBER = 50;
//预到货预警-初始化数据-fdj-001-4
public void screenAsnPre(){
HibernateTransactionManager t = (HibernateTransactionManager)
applicationContext.getBean("transactionManager");
Session session = t.getSessionFactory().getCurrentSession();
String produce = "call insert_screen_asn_pre(?,?,?)";
CallableStatement call = null;
String mes = "success";
try {
call = session.connection().prepareCall(produce);
call.setInt(1, PAGE_NUMBER);
call.registerOutParameter(2, Types.INTEGER);
call.registerOutParameter(3, Types.VARCHAR);
call.execute();
if(call.getInt(2)!=0){
mes = call.getString(3);
mes = MyUtils.font(mes);
}
} catch (HibernateException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally{
if(call!=null){
try {
call.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
LocalizedMessage.addLocalizedMessage(mes);
}
}
create or replace procedure insert_screen_asn_pre
(PAGE_NUMBER IN int,ISERROR OUT int,errorMes OUT varchar2)
as
type cursors is ref cursor;
curs cursors;
V_SUP_NAME SCREEN_ASN_PRE.Sup_Name%type;
V_ASN_CODE SCREEN_ASN_PRE.Asn_Code%type;
V_ARRIVAL_PRE SCREEN_ASN_PRE.Arrival_Pre%type;
nums int:=0;
begin
open curs for select sup.name,a.code,a.estimate_date from wms_asn a
left join wms_organization sup on sup.id = a.supplier_id
where a.status in ('OPEN','ACTIVE') and a.estimate_date is not null
and not exists(select 1 from SCREEN_ASN_PRE sc where sc.asn_code = a.code)
;
loop
fetch curs into V_SUP_NAME,V_ASN_CODE,V_ARRIVAL_PRE;
exit when curs%notfound;
insert into SCREEN_ASN_PRE(TYPE,Sup_Name,ASN_CODE,ARRIVAL_PRE)
values('预到货预警',V_SUP_NAME,V_ASN_CODE,V_ARRIVAL_PRE);
nums:= nums+1;
--dbms_output.put_line('loop:'||nums);
if nums >= PAGE_NUMBER
then
commit;
nums:=0;
end if;
end loop;
--dbms_output.put_line('end loop:'||nums);
if nums >0
then
commit;
nums:=0;
end if;
ISERROR:=0;
--dbms_output.put_line('end if:'||nums);
exception
when others
then
ISERROR:=1;
errorMes:=sqlerrm;
dbms_output.enable(buffer_size => null);
dbms_output.put_line(dbms_utility.format_error_backtrace);
--dbms_output.put_line('sqlcode:'||sqlcode);
dbms_output.put_line('sqlerrm:'||sqlerrm);
rollback;
close curs;
end;