/*上传数据的存储过程*/ create or replace procedure sp_uploadbarcode(barcode in BarCodeUpload_arr) as begin for i in 1 .. barcode.count loop update prodiobarcode set pib_lotno=barcode(i).pib_lotno, pib_datecode=barcode(i).pib_datecode,pib_outboxcode1=barcode(i).pib_outboxcode1, pib_outboxcode2=barcode(i).pib_outboxcode2,pib_custmidboxcode=barcode(i).pib_custmidboxcode, pib_custoutboxcode=barcode(i).pib_custoutboxcode, pib_ifupload=barcode(i).pib_ifupload,pib_ifpick=barcode(i).pib_ifpick, pib_ifprint=barcode(i).pib_ifprint where pib_id=barcode(i).pib_id; end loop; end; /*创建上传的类型*/ create or replace type BarCodeUpload_arr as table of BarCodeUpload; /*上传的对象实体*/ create or replace type BarCodeUpload as object( pib_id varchar2(20), pib_outboxcode1 varchar2(20), pib_outboxcode2 varchar2(20), pib_custmidboxcode varchar2(20), pib_custoutboxcode varchar2(20), pib_lotno varchar2(20), pib_datecode varchar2(20), pib_ifupload varchar2(20), pib_ifpick varchar2(20), pib_ifprint varchar2(20) ); /*获取数据的视图*/ CREATE OR REPLACE FORCE VIEW "PRODIOBARCODE_VIEW" as select 0 choose,pib_ordercode,pib_weight,pib_prdetail,pib_size,pd_ordercode,nvl(pr_midboxcapacity_user,0)pr_midboxcapacity_user,pi_cardcode,pr_madein,pr_vendcode, pib_year,pib_month,pib_day,pd_whcode,pd_batchcode,pr_barcode,pd_pdno,pr_brand,pr_detail,pd_orderdetno,pib_custoutboxcode,pib_datecode1,pd_remark,pib_custmidboxcode, pr_orispeccode,pi_maitou_user,pd_custprodcode,pib_indate,pi_date,pib_inoutno,pd_custprodspec,nvl(pib_ifrecheck,0)pib_ifrecheck,nvl(pib_ifupload,0)pib_ifupload, nvl(pib_ifmodify,0)pib_ifmodify,pd_pocode,pib_madein,pib_custbarcode,pib_id,pib_pdid,pib_piid,pib_pdno,pib_prodcode,nvl(nvl(pd_brand,pib_brand),pr_brand)pib_brand, pr_vendprodcode,pib_lotno,pib_datecode,pib_qty,pr_spec,pi_title,pr_zxbzs,pr_unit,pib_barcode,pib_outboxcode1,pib_outboxcode2,nvl(pib_ifpick,0)pib_ifpick,nvl (pib_ifprint,0)pib_ifprint,pd_outerboxgw,pd_outerboxnw from prodiobarcode left join prodinout on pib_piid=pi_id left join prodiodetail on pib_piid=pd_piid and pd_id=pib_pdid left join product on pr_code=pib_prodcode left join sale on sa_code=pib_ordercode order by to_number(pib_outboxcode2),pib_custoutboxcode; /*自动生成条码的存储过程*/ create or replace PROCEDURE "GETCUSTBARCODE" -- ============================================= -- Author: -- Create date: <2018-06-12> -- Description: <出货单-过账-自动生成条码-自定义客户条码> -- ============================================= (KEYVALUE in varchar2,ICID in varchar2,V_ERRORMSG OUT varchar2) AS v_custcode varchar2(50); --客户编号 v_nrcode varchar2(20); --编码规则编号 v_nrddetno number; --编码明细序号 v_nrdname varchar(20); --编码明细名称 v_nrdtype varchar(20); --编码明细类型 v_nrdsql varchar(500); --编码明细SQL v_serialindex number; --流水所在索引 v_nrdlength number; --编码明细流水长度 v_seriallen number; --流水长度 v_sqltemp varchar(50); --SQL查询出来的临时变量 v_prefix varchar(50); --流水前面的前缀(临时) v_v_prefix varchar(50); --拼接时前缀 v_suffix varchar(50); --流水的后缀 v_maxnum varchar(50); --当前编号+前缀最大的流水 v_inoutno varchar(30); --出货单号 v_getcustrule number:=0; --是否维护了客户自定义规则 v_pi_id ProdInout.pi_id%type; v_p_piclass ProdInout.pi_class%type; v_p_piinoutno ProdInout.pi_inoutno%type; v_p_commitname EMPLOYEE.EM_NAME%TYPE; v_pi_inoutman ProdInout.pi_inoutman%type; v_pd_ordercode prodiodetail.pd_ordercode%type; v_pd_prodCode prodiodetail.pd_prodcode%type; v_pd_thisqty prodiodetail.pd_outqty%type; v_pd_outqty prodiodetail.pd_outqty%type; v_pd_id ProdioDetail.pd_id%type; v_pd_piclass ProdioDetail.pd_piclass%type; v_pd_pdno ProdioDetail.pd_pdno%type; v_max_barcode prodiobarcode.pib_barcode%type; v_max_outboxcode prodiobarcode.pib_outboxcode1%type; v_max_prodcode prodiobarcode.pib_prodcode%type; v_max_custpo prodiobarcode.PIB_CUSTPO%type; v_pib_barcode prodiobarcode.pib_barcode%type; v_pib_custbarcode prodiobarcode.pib_custbarcode%type; v_pr_zxbzs product.pr_zxbzs%type; v_pr_id product.pr_id%type; v_sa_pocode sale.sa_pocode%type; v_bar_num NUMBER(8,0); v_box_num NUMBER(8,0); v_barcount NUMBER:=0; --条码数量 v_restqty FLOAT:=0; --尾盘数量 -- 默认箱内容量 ：3,7,10. 默认最优分配箱内容量 --最小包装数为1的暂时不自动生成条码 CURSOR c_PDIOBarCur IS select pd_id,pd_pdno,pd_prodcode,pd_totalqty pd_outqty,pr_zxbzs,pd_qty pd_thisqty ,pr_id,pd_piclass, pd_ordercode,sa_pocode from PRODIOBARCODE_VIEW left join sale on sa_code=pd_ordercode where pd_piid=v_pi_id and pd_qty>0 and pr_zxbzs>1 order by pd_prodcode,pd_id; --存在客户编号的时候 CURSOR NR IS select nrd_detno,nrd_name,nrd_type,nrd_sql,nrd_length,nr_code from NoRuleDetail left join norule on nrd_nrid=nr_id where nr_custcode=v_custcode order by nrd_detno; --不存在客户编号取默认公用规则 CURSOR NR_Default IS select nrd_detno,nrd_name,nrd_type,nrd_sql,nrd_length,nr_code from NoRuleDetail left join norule on nrd_nrid=nr_id where nr_custcode is null and nr_isdefault <> 0 order by nrd_detno; BEGIN BEGIN select pi_id,pi_inoutno,pi_class,pi_inoutman,pi_cardcode into v_pi_id,v_p_piinoutno,v_p_piclass,v_pi_inoutman,v_custcode from prodinout where pi_id=KEYVALUE; EXCEPTION WHEN NO_DATA_FOUND THEN V_ERRORMSG := '没有当前出入库记录!' ; RETURN; END; select pi_inoutno into v_inoutno from prodinout where pi_id=KEYVALUE; IF(v_p_piclass='出货单') THEN --目前只先针对出货单自动生成条码 --单据不存在判断 /*SELECT count(1) into v_recordcount FROM prodiodetail WHERE pd_piid=v_pi_id and pd_status=0; IF v_recordcount >0 THEN v_return_string := '数据错误，单据未过账成功!' ; RETURN; END IF; --有过一条记录就不给生成 select count(1) into v_recordcount from PRODIOBARCODE where PIB_PIID=v_pi_id; IF v_recordcount >0 THEN RETURN; END IF;*/ --修改20170724 根据客户需求如果记录清空，重新生成，以免用户生成之后又修改数据，导致打印不了 delete from PRODIOBARCODE where PIB_PIID=v_pi_id; OPEN NR; LOOP FETCH NR INTO v_nrddetno,v_nrdname,v_nrdtype,v_nrdsql,v_nrdlength,v_nrcode; IF NR%ROWCOUNT =0 THEN v_getcustrule:=1; END IF; --使用用户指定的规则 EXIT WHEN NR%NOTFOUND; IF v_nrdtype='SQL' THEN --如果是SQL则取出 BEGIN v_nrdsql:=regexp_replace(v_nrdsql,'{\w+}',''''||v_inoutno||''''); execute IMMEDIATE v_nrdsql into v_sqltemp; v_prefix:=v_prefix || v_sqltemp; v_suffix:=v_suffix || v_sqltemp; EXCEPTION WHEN OTHERS THEN BEGIN v_sqltemp:=''; v_prefix:=v_prefix || v_sqltemp; v_suffix:=v_suffix || v_sqltemp; END; END; END IF; IF v_nrdtype='常量' THEN v_prefix :=v_prefix || v_nrdsql; v_suffix :=v_suffix || v_nrdsql; END IF; IF v_nrdtype='流水' THEN --前缀和后缀正常拼接，如果中间含有流水则清除后缀的值，拼接后缀的值 v_nrdsql :=''; v_serialindex :=v_nrddetno; v_suffix :=''; v_v_prefix :=v_prefix; v_seriallen:=v_nrdlength; END IF; END LOOP; CLOSE NR; IF v_getcustrule<>0 THEN OPEN NR_Default; LOOP FETCH NR_Default INTO v_nrddetno,v_nrdname,v_nrdtype,v_nrdsql,v_nrdlength,v_nrcode; --使用用户指定的规则 EXIT WHEN NR_Default%NOTFOUND; IF v_nrdtype='SQL' THEN --如果是SQL则取出 BEGIN v_nrdsql:=regexp_replace(v_nrdsql,'{\w+}',''''||v_inoutno||''''); execute IMMEDIATE v_nrdsql into v_sqltemp; v_prefix:=v_prefix || v_sqltemp; v_suffix:=v_suffix || v_sqltemp; EXCEPTION WHEN OTHERS THEN BEGIN v_sqltemp:=''; v_prefix:=v_prefix || v_sqltemp; v_suffix:=v_suffix || v_sqltemp; END; END; END IF; IF v_nrdtype='常量' THEN v_prefix :=v_prefix || v_nrdsql; v_suffix :=v_suffix || v_nrdsql; END IF; IF v_nrdtype='流水' THEN --前缀和后缀正常拼接，如果中间含有流水则清除后缀的值，拼接后缀的值 v_nrdsql :=''; v_serialindex :=v_nrddetno; v_suffix :=''; v_v_prefix :=v_prefix; v_seriallen:=v_nrdlength; END IF; END LOOP; CLOSE NR_Default; END IF; BEGIN select rmn_maxnumber into v_maxnum from RuleMaxNum where rmn_nrcode=v_nrcode and rmn_prefix=v_prefix; EXCEPTION WHEN OTHERS THEN BEGIN v_maxnum:=1; insert into RuleMaxNum(rmn_id,rmn_maxnumber,rmn_nrcode,rmn_prefix)values(RuleMaxNum_seq.nextval,1,v_nrcode,v_prefix); END; END; OPEN c_PDIOBarCur ; LOOP FETCH c_PDIOBarCur into v_pd_id,v_pd_pdno,v_pd_prodcode,v_pd_outqty,v_pr_zxbzs,v_pd_thisqty,v_pr_id,v_pd_piclass,v_pd_ordercode,v_sa_pocode ; exit when c_PDIOBarCur%NOTFOUND; -- 查询当前物料的最大的条码和箱号流水 select max(pib_barcode) into v_max_barcode from PRODIOBARCODE where pib_piid=v_pi_id and pib_prodcode=v_pd_prodcode; --获取该单据的最大箱号（在同一个单据内中盒号是唯一的） select max(to_number(pib_outboxcode1))into v_max_outboxcode from prodiobarcode where pib_piid=v_pi_id; IF(nvl(v_max_outboxcode,' ')<>' ')THEN select max(PIB_PRODCODE),MAX(PIB_CUSTPO) INTO v_max_prodcode,v_max_custpo from prodiobarcode where pib_piid=v_pi_id and pib_outboxcode1=v_max_outboxcode; END IF; if(nvl(v_max_barcode,' ')<>' ') then --计算条码流水 v_bar_num := to_number(LTRIM(substr(v_max_barcode,-4),'0'))+1; --截取后四位，去掉前面的0,转成数字+1，可能会抛异常，无法转成数字 else v_bar_num := 1; end if; --出货单最大箱号 if(nvl(v_max_outboxcode,' ')<>' ' and (v_max_prodcode=v_pd_prodcode and nvl(v_max_custpo,' ')=nvl(v_sa_pocode,' '))) then --箱号 v_box_num := to_number(v_max_outboxcode); else if (nvl(v_max_outboxcode,' ')<>' ') then v_box_num := to_number(v_max_outboxcode)+1; else v_box_num := 1; end if; end if; v_barcount := floor(v_pd_thisqty/v_pr_zxbzs); ---获取最小包装数整数倍的条码个数 v_restqty := v_pd_thisqty - v_barcount*v_pr_zxbzs; -- 取尾数箱条码数量 if(v_restqty >0) then v_barcount := v_barcount+1; end if ; for i in 1..v_barcount Loop --判断一盒是否已经装满 ,循环查询速度很慢 /*select count(1) into v_recordcount from prodiobarcode where pib_piid=v_pi_id and PIB_OUTBOXCODE1=v_box_num ;--and pib_prodcode<>v_pd_prodcode; if(v_recordcount=10) then v_box_num := v_box_num+1; end if;*/ v_pib_barcode := v_pd_id||'-'||v_pr_id||'-'||trim(to_char(v_bar_num,'0999')); --流水四位 pd_id-pr_id-四位流水（0001） 如 123-2345-0002格式 select replace(lpad(v_maxnum,v_seriallen),' ','0') into v_maxnum from dual ; v_pib_custbarcode := v_v_prefix || v_maxnum || v_suffix; if(i = v_barcount and v_restqty>0) then --尾数盒 insert into prodiobarcode(PIB_ID,PIB_PRODCODE,PIB_INDATE,PIB_INOUTNO,PIB_PIID,PIB_PDNO, PIB_PDID,PIB_PICLASS, PIB_BARCODE,PIB_CUSTBARCODE,PIB_QTY,PIB_PRODID,PIB_OUTBOXCODE1,PIB_IFPRINT,PIB_IFPICK,PIB_ORDERCODE,PIB_CUSTPO) values(prodiobarcode_seq.nextval,v_pd_prodcode,sysdate,v_p_piinoutno,v_pi_id,v_pd_pdno,v_pd_id,v_pd_piclass, v_pib_barcode,v_pib_custbarcode,v_restqty,v_pr_id,v_box_num,0,0,v_pd_ordercode,v_sa_pocode); else insert into prodiobarcode(PIB_ID,PIB_PRODCODE,PIB_INDATE,PIB_INOUTNO,PIB_PIID,PIB_PDNO, PIB_PDID,PIB_PICLASS, PIB_BARCODE,PIB_CUSTBARCODE,PIB_QTY,PIB_PRODID,PIB_OUTBOXCODE1,PIB_IFPRINT,PIB_IFPICK,PIB_ORDERCODE,PIB_CUSTPO) values(prodiobarcode_seq.nextval,v_pd_prodcode,sysdate,v_p_piinoutno,v_pi_id,v_pd_pdno,v_pd_id,v_pd_piclass, v_pib_barcode,v_pib_custbarcode,v_pr_zxbzs,v_pr_id,v_box_num,0,0,v_pd_ordercode,v_sa_pocode); end if; if(MOD(i,10) = 0 and i<>v_barcount)then --箱号加1 v_box_num := v_box_num+1; end if; v_bar_num := v_bar_num+1 ; --条码号加1 v_maxnum := v_maxnum+1; end loop; END LOOP; <> CLOSE c_PDIOBarCur; update RuleMaxNum set rmn_maxnumber=v_maxnum where rmn_nrcode=v_nrcode and rmn_prefix=v_prefix; --记录操作日志 insert into messagelog (ml_id,ml_date,ml_man,ml_content,ml_result,ml_search) values(messagelog_seq.nextval,sysdate,v_p_commitname,'过账自动生成条码','生成条码成功','ProdInOut!Sale|pi_id='||v_pi_id); END IF; EXCEPTION WHEN OTHERS THEN V_ERRORMSG :=SQLERRM || dbms_utility.format_error_backtrace(); END; /*自定义代码，根据不同客户进行页面设置*/ create or replace PROCEDURE sp_custinit( KeyValue in varchar2, content out varchar2 )as begin declare v_custcode varchar2(20); begin select pi_cardcode into v_custcode from prodinout where pi_id=KeyValue; content:=' using System; using System.Windows.Forms; namespace CustInitSpace { public class CustInitClass { public void CustInit() { Form f=Form.ActiveForm; if(f.Controls["cu_code"].Text=="C000027") { (f.Controls["OutBoxLabel"].Controls["DiffCustOutBoxCode"] as CheckBox).Checked = false; }else { (f.Controls["OutBoxLabel"].Controls["DiffCustOutBoxCode"] as CheckBox).Checked = true; } } } }'; end; end; /*获取数据页数据的SQL*/ create or replace PROCEDURE sp_getDataSql( v_inoutno in varchar2, v_text in varchar2, v_sql out varchar2 )as begin v_sql:='select' ||v_text|| ' from prodinout left join prodiodetail on pi_id=pd_piid left join chip_in_out on io_piid=pi_id and pd_id=io_pdid left join chip on ch_code=io_chipcode left join CHIP_WAFERTEST_VIEW on ch_code=chw_chipcode left join CHIP_EDCDATA_VIEW on che_chipcode=ch_code left join product on pr_code=pd_prodcode left join prodiobarcode on pib_piid=pi_id and pib_custbarcode=ch_code where pi_inoutno='''||v_inoutno||''' order by ch_code'; end; insert into configs(id,caller,code,data) values(configs_seq.nextval,'Prodinout!Down','DownLoadLink','http://sisemi03.zicp.io/ERP');