mes-client/UAS-出货标签管理(CKD)/SQL/SQL.txt

/*上传数据的存储过程*/
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:    <zhangz>
-- 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;
            <<Exitlabel>>
            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');