oracle刚开始接触，第一次写oracle的存储过程，在升级上线的时候又做了一些调整，功能为汽车总装生产线中生成拣配单和强制生单的两个功能
生成拣配单功能：
create or replace procedure PDA_CREATEPICKBILL(in_scandate in VARCHAR2, --扫描时间
in_realorder in INTEGER, --上线顺序
in_pullpoint in VARCHAR2, --拉动点
in_carbatchno in VARCHAR2, --整车批次号
in_userID in VARCHAR2, --用户
in_produceLine in VARCHAR2, --生产线
out_error out varchar2 --空为正常
) AS
-- vusingqty varchar2(10); --占配数
vPICKSEQ varchar2(10); --拣配顺序
VOutOrderID VARCHAR2(50); ---出库单号
VOutBillID VARCHAR2(50);
VPickID VARCHAR2(50); ---拣配单号
RowNumber integer; ---记录数
V_realorder integer; ---当前项次
v_Flag integer;
v_PlanDate VARCHAR2(50);
begin
out_error := '';
RowNumber := 0;
select count(1)
into RowNumber
from bo_orderdetail
where carBATCHNO = in_carbatchno
and state <> '1';
if (RowNumber < 1) then
out_error := '物料未分配，不允许扫描';
return;
end if;
---------------生成出库单号------------------------wtj 2013.11.10.1846
vOutOrderID := SP_CREATEBILLNO.FC_GetOutOrderID_Four('DC', '2', in_userID);
IF vOutOrderID is null THEN
out_error := ('出库单号生成失败');
return;
END IF;
vPICKSEQ := 0;
------------------取出当前生产线的最大统计项次
---如果没拉动点添加拉动点
RowNumber := 1;
select count(1)
into RowNumber
from Z_PULLPOINT_REALORDER
where PULLPOINT = in_pullpoint
and PRODUCELINE = in_produceLine;
if (RowNumber < 1) then
out_error := '没有维护z_pullpoint_realorder生产线拉动点计数表';
return;
end if;
---如果跨月清0 数据
select plandate
into v_PlanDate
from bo_pickorder
where batchno = in_carbatchno;
RowNumber := 1;
select count(1)
into RowNumber
from Z_PULLPOINT_REALORDER
where plandate = substr(v_PlanDate, 0, 6);
if (RowNumber < 1) then
update Z_PULLPOINT_REALORDER
set REALORDER = 0, plandate = substr(v_PlanDate, 0, 6);
end if;
---将该拉动点的数据加1
update Z_PULLPOINT_REALORDER
set REALORDER = REALORDER + 1
where PULLPOINT = in_pullpoint
and PRODUCELINE = in_produceLine;
--获得该拉动点的最大项次
select realorder
into V_realorder
from Z_PULLPOINT_REALORDER
where PULLPOINT = in_pullpoint
and PRODUCELINE = in_produceLine;
---------筐栏件 生成单规则
FOR cur_OnePickBill in (select WORKPLACE, ---工位
WAREHOUSEID, ---库房
TOOLID, ---箱号
KEEPER, ---保管员
CPICKER, ---拣配员
MAX(LINEID) AS LINEID, ---生产线
DEPTID, ---部门
deskcopies, ---器具容量
Max(PLANDATE) as PLANDATE ---计划上线日期
--Max(realorder) as realorder
from bo_outdetail_pull A
WHERE PICKBILLSTATE = '0'
and pullpoint = in_pullpoint
and sendway = '04'
and ((Scandate = in_scandate and
REALORDER <= in_realorder) or
Scandate < in_scandate)
and exists
(select 1
from alxmz.z_ppwscode@cmmpzs B
where A.Lineid = B.productline
and B.produceline = in_produceLine
and A.Deptid = B.WSCODE)
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
deskcopies)
/*筛选条件为：配送方式为排序，生成简配单状态为0，当前拉动点，扫描时间和项次小于当前*/
loop
vPICKSEQ := vPICKSEQ + 1;
VPickID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
v_Flag := 0;
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
v_Flag := 0;
FOR cur_TwoPickBill in (select detailid, deskcopies
from bo_outdetail_pull A
where WORKPLACE = cur_OnePickBill.Workplace
and WAREHOUSEID = cur_OnePickBill.Warehouseid
and TOOLID = cur_OnePickBill.Toolid
and KEEPER = cur_OnePickBill.KEEPER
and CPICKER = cur_OnePickBill.CPICKER
and DEPTID = cur_OnePickBill.DEPTID
and deskcopies = cur_OnePickBill.deskcopies
and sendway = '04'
and PICKBILLSTATE = '0'
and pullpoint = in_pullpoint
and ((Scandate = in_scandate and
REALORDER <= in_realorder) or
Scandate < in_scandate)
and exists
(select 1
from alxmz.z_ppwscode@cmmpzs B
where A.Lineid = B.productline
and B.produceline = in_produceLine
and A.Deptid = B.WSCODE)
ORDER BY Scandate, REALORDER asc) loop
update bo_outdetail_pull
set PICKID = VPickID;
where detailid = cur_TwoPickBill.detailid;
if (MOD(V_realorder, cur_TwoPickBill.deskcopies) = 0) then
update bo_outdetail_pull
set PICKID = VOutBillID
where detailid = cur_TwoPickBill.detailid;
v_Flag := v_Flag + 1;
end if;
END LOOP;
if (v_Flag > 0) then
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
deskcopies,
PLANDATE,
sendway,
PRODUCELINE)
VALUES
(VOutBillID,
cur_OnePickBill.WAREHOUSEID,
cur_OnePickBill.LINEID,
cur_OnePickBill.DEPTID,
cur_OnePickBill.KEEPER,
cur_OnePickBill.CPICKER,
cur_OnePickBill.WORKPLACE,
cur_OnePickBill.TOOLID,
cur_OnePickBill.deskcopies,
substr(cur_OnePickBill.PLANDATE, 0, 6),
'04',
in_produceLine);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
end if;
END LOOP;
--------排序件 生成单规则
FOR cur_OnePickBill in (select WORKPLACE, ---工位
WAREHOUSEID, ---库房
TOOLID, ---箱号
KEEPER, ---保管员
CPICKER, ---拣配员
MAX(LINEID) AS LINEID, ---生产线
DEPTID, ---部门
deskcopies, ---器具容量
Max(PLANDATE) as PLANDATE ---计划上线日期
from bo_outdetail_pull A
WHERE PICKBILLSTATE = '0'
and pullpoint = in_pullpoint
and sendway = '02'
and ((Scandate = in_scandate and
REALORDER <= in_realorder) or
Scandate < in_scandate)
and exists
(select 1
from alxmz.z_ppwscode@cmmpzs B
where A.Lineid = B.productline
and B.produceline = in_produceLine
and A.Deptid = B.WSCODE)
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
deskcopies)
/*筛选条件为：配送方式为排序，生成简配单状态为0，当前拉动点，扫描时间和项次小于当前*/
loop
vPICKSEQ := vPICKSEQ + 1;
VPickID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
v_Flag := 0;
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
v_Flag := 0;
FOR cur_TwoPickBill in (select detailid, deskcopies
from bo_outdetail_pull A
where WORKPLACE = cur_OnePickBill.Workplace
and WAREHOUSEID = cur_OnePickBill.Warehouseid
and TOOLID = cur_OnePickBill.Toolid
and KEEPER = cur_OnePickBill.KEEPER
and CPICKER = cur_OnePickBill.CPICKER
and DEPTID = cur_OnePickBill.DEPTID
and deskcopies = cur_OnePickBill.deskcopies
and sendway = '02'
and PICKBILLSTATE = '0'
and pullpoint = in_pullpoint
and ((Scandate = in_scandate and
REALORDER <= in_realorder) or
Scandate < in_scandate)
and exists
(select 1
from alxmz.z_ppwscode@cmmpzs B
where A.Lineid = B.productline
and B.produceline = in_produceLine
and A.Deptid = B.WSCODE)
ORDER BY Scandate, REALORDER asc) loop
update bo_outdetail_pull
set PICKID = VPickID;
where detailid = cur_TwoPickBill.detailid;
if (MOD(V_realorder, cur_TwoPickBill.deskcopies) = 0) then
update bo_outdetail_pull
set PICKID = VOutBillID
where detailid = cur_TwoPickBill.detailid;
v_Flag := v_Flag + 1;
end if;
END LOOP;
if (v_Flag > 0) then
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
deskcopies,
PLANDATE,
sendway,
PRODUCELINE)
VALUES
(VOutBillID,
cur_OnePickBill.WAREHOUSEID,
cur_OnePickBill.LINEID,
cur_OnePickBill.DEPTID,
cur_OnePickBill.KEEPER,
cur_OnePickBill.CPICKER,
cur_OnePickBill.WORKPLACE,
cur_OnePickBill.TOOLID,
cur_OnePickBill.deskcopies,
substr(cur_OnePickBill.PLANDATE, 0, 6),
'02',
in_produceLine);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
end if;
END LOOP;
-------------------------工位更改生成拣配单-----------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from alxmz.S_STATION_ITEM@cmmpzs B
where A.PARTCODE = B.ITEMCODE
and A.WORKPLACE = B.STATIONCODE
and A.deptid = B.WORKSHOPCODE)
group by PICKID, Warehouseid) loop
--工位字段与CMMP中S_STATION_ITEM工位物料对照表相匹配（零件号）PARTCODE对应ITEMCODE
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
FOR cur_TwoPickBill in (select DETAILID, WORKPLACE, Partcode, DEPTID
from bo_outdetail_pull
where PICKID = cur_OnePickBill.PICKID) loop
RowNumber := 1;
select count(1)
into RowNumber
from alxmz.S_STATION_ITEM@cmmpzs
where cur_TwoPickBill.PARTCODE = ITEMCODE
and cur_TwoPickBill.WORKPLACE = STATIONCODE
and cur_TwoPickBill.deptid = WORKSHOPCODE;
if (RowNumber < 1) then
update bo_outdetail_pull
set PICKID = VOutBillID
where DETAILID = cur_TwoPickBill.DETAILID;
end if;
END LOOP;
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select VOutBillID,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'WORKPLACE',
in_produceLine
from bo_outdetail_pull
where PICKID = VOutBillID
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
END LOOP;
-------------------------库房更改生成拣配单------------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from base_material B
where A.Materialid = B.Materialid
and A.Warehouseid = B.Warehouseid)
group by PICKID, Warehouseid) loop
--库房字段与WMS物料信息表(Base_Material)，物料相对应的库房不匹配生成异常拣配单
--只将该拣配单号下的异常物料生成新的拣配单
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
FOR cur_TwoPickBill in (select DETAILID, Warehouseid, Materialid
from bo_outdetail_pull
where PICKID = cur_OnePickBill.PICKID) loop
RowNumber := 1;
select count(1)
into RowNumber
from base_material
where cur_TwoPickBill.Materialid = Materialid
and cur_TwoPickBill.Warehouseid = Warehouseid;
if (RowNumber < 1) then
update bo_outdetail_pull
set PICKID = VOutBillID
where DETAILID = cur_TwoPickBill.DETAILID;
end if;
END LOOP;
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select VOutBillID,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'WAREHOUSEID',
in_produceLine
from bo_outdetail_pull
where PICKID = VOutBillID
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
END LOOP;
-------------------------保管员更改生成拣配单-----------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from base_material B
where A.Materialid = B.Materialid
and A.KEEPER = B.KEEPER)
group by PICKID, Warehouseid) loop
--保管员字段与WMS物料信息表(Base_Material)，物料相对应的保管员不匹配生成异常拣配单
--只将该拣配单号下的异常物料生成新的拣配单
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
FOR cur_TwoPickBill in (select DETAILID, KEEPER, Materialid
from bo_outdetail_pull
where PICKID = cur_OnePickBill.PICKID) loop
RowNumber := 1;
select count(1)
into RowNumber
from base_material
where cur_TwoPickBill.Materialid = Materialid
and cur_TwoPickBill.KEEPER = KEEPER;
if (RowNumber < 1) then
update bo_outdetail_pull
set PICKID = VOutBillID
where DETAILID = cur_TwoPickBill.DETAILID;
end if;
END LOOP;
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select VOutBillID,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'KEEPER',
in_produceLine
from bo_outdetail_pull
where PICKID = VOutBillID
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
END LOOP;
-------------------------拣配员更改生成拣配单-----------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from base_material B
where A.Materialid = B.Materialid
and A.CPICKER = B.PICKER)
group by PICKID, Warehouseid) loop
--拣配员字段与WMS物料信息表(Base_Material)，物料相对应的拣配员不匹配生成异常拣配单
--只将该拣配单号下的异常物料生成新的拣配单
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
FOR cur_TwoPickBill in (select DETAILID, CPICKER, Materialid
from bo_outdetail_pull
where PICKID = cur_OnePickBill.PICKID) loop
RowNumber := 1;
select count(1)
into RowNumber
from base_material
where cur_TwoPickBill.Materialid = Materialid
and cur_TwoPickBill.CPICKER = PICKER;
if (RowNumber < 1) then
update bo_outdetail_pull
set PICKID = VOutBillID
where DETAILID = cur_TwoPickBill.DETAILID;
end if;
END LOOP;
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select distinct VOutBillID,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'PICKER',
in_produceLine
from bo_outdetail_pull
where PICKID = VOutBillID
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
END LOOP;
--------------------------箱号更改生成拣配单-------------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from base_toolwp B
where A.Toolid = B.TOOLID
and A.WORKPLACE = B.WORKPLACE)
group by PICKID, Warehouseid) loop
--箱号与WMS器具与工位关系对照表（base_toolwp），工位相对应的箱号不匹配生成异常拣配单
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select cur_OnePickBill.Pickid,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'TOOLID',
in_produceLine
from bo_outdetail_pull
where PICKID = cur_OnePickBill.Pickid
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies
);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = cur_OnePickBill.PICKID;
end loop;
--------------------------箱号容量更改生成拣配单----------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from base_toolwp B
where A.Toolid = B.TOOLID
and A.WORKPLACE = B.WORKPLACE
and A.Deskcopies = B.Deskcopies)
group by PICKID, Warehouseid) loop
--箱号容量与WMS器具与工位关系对照表（base_toolwp），工位、箱号相对应的箱号容量不匹配生成异常拣配单
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select cur_OnePickBill.Pickid,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'DESKCOPIES',
in_produceLine
from bo_outdetail_pull
where PICKID = cur_OnePickBill.Pickid
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = cur_OnePickBill.PICKID;
end loop;
-------------------------拉动点更改生成拣配单--------------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
where PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and not exists
(select 1
from alxmz.z_drivingpoint@cmmpzs B
where A.pullpoint = B.SPOINT
and A.WORKPLACE = B.STATIONCODE)
group by PICKID, Warehouseid) loop
--拉动点与CMMP中z_drivingpoint相对应，工位相对应拉动点不匹配生成异常拣配单
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select cur_OnePickBill.Pickid,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'PULLPOINT',
in_produceLine
from bo_outdetail_pull
where PICKID = cur_OnePickBill.Pickid
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = cur_OnePickBill.PICKID;
end loop;
------------------------拉动方式更改生成拣配单-------------------------
FOR cur_OnePickBill in (select PICKID, Warehouseid
from bo_outdetail_pull A
WHERE PICKBILLSTATE = '0'
and PICKID is not null
and pullpoint = in_pullpoint
and vendorid <> 'A2080'
and not exists
(select 1
from alxmz.b_send_workshopflag@cmmpzs B
where A.sendway = B.sendflag
and A.Partcode = B.partcode
and A.deptid = B.workshopcode)
group by PICKID, Warehouseid)
loop
--拉动方式与CMMP中b_send_workshopflag相对应，部门，零件号相对应拉动点不匹配生成异常拣配单
----只将该拣配单号下的异常物料生成新的拣配单
vPICKSEQ := vPICKSEQ + 1;
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
FOR cur_TwoPickBill in (select DETAILID, sendway, Partcode, DEPTID
from bo_outdetail_pull
where PICKID = cur_OnePickBill.PICKID) loop
RowNumber := 1;
select count(1)
into RowNumber
from alxmz.b_send_workshopflag@cmmpzs
where cur_TwoPickBill.sendway = sendflag
and cur_TwoPickBill.Partcode = partcode
and cur_TwoPickBill.deptid = workshopcode;
if (RowNumber < 1) then
update bo_outdetail_pull
set PICKID = VOutBillID
where DETAILID = cur_TwoPickBill.DETAILID;
end if;
END LOOP;
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
PLANDATE,
deskcopies,
sendway,
ERROCOLUMN,
PRODUCELINE)
(select distinct VOutBillID,
WAREHOUSEID,
MAX(LINEID),
DEPTID,
KEEPER,
CPICKER,
WORKPLACE,
TOOLID,
max(substr(PLANDATE, 0, 6)),
deskcopies,
sendway,
'SENDWAY',
in_produceLine
from bo_outdetail_pull
where PICKID = VOutBillID
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
sendway,
deskcopies);
update bo_outdetail_pull
set PICKBILLSTATE = '2'
where PICKID = VOutBillID;
END LOOP;
------------------------插入WMS数据-------------------------
---------插入BO_PICKBILL表,将STATE作为标志，先将符合条件的state更改为0---------
update BO_PICKBILL_PULL
set state = '0'
where WMSSTATE = '0'
and PLANDATE <= TO_CHAR(SYSDATE, 'yyyymm');
insert into BO_PICKBILL
(PICKID,
WAREHOUSEID,
--LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
DDATE,
BPRINT,
PRINTCOUNT,
orderid,
outid)
select A.pickID,
A.WAREHOUSEID,
--A.LINEID,
A.DEPTID,
A.KEEPER,
A.PICKER,
A.WORKPLACE,
A.TOOLID,
A.DDATE,
1,
99999,
'LaDongOrderId',
'LaDongOutId'
from BO_PICKBILL_PULL A
where A.state = '0'
and exists (select 1
from bo_outdetail_pull d
where a.pickid = d.pickid
and d.ifemptybill <> '1');
update BO_PICKBILL_PULL
set WMSSTATE = '1'
where WMSSTATE = '0'
and state = '0';
----------插入BO_PICKBILL后将WMSSTATE更改标志
insert into BO_OUTDETAIL
(DETAILID,
OUTID,
VENDORID,
MATERIALID,
BATCHNO,
REQQTY,
USINGQTY,
UNIT,
BINID,
WAREHOUSEID,
AREAID,
SHELFID,
ROWNO,
COLNO,
STATE,
ASSIGNER,
ASSIGNDATE,
PICKER,
PICKDATE,
OUTPERSON,
OUTDATE,
VERIFYMAN,
VERIFYDATE,
SOURCEID,
LINEID,
VALID,
OPERDATE,
IP,
OUTDETAILID,
OPERSTATE,
MATERIALSTATE,
REASONID,
WORKPLACE,
DAYCLEAR,
PRINTSTATE,
PRINTIP,
PERIOD,
PICKQTY,
OUTQTY,
CONFIRMQTY,
CARCODE,
CARORDERNO,
CARBATCHNO,
CARSEQUENCE,
OLDVENDORID,
BMODIFY,
TRANSFER,
CPICKER,
CHECKYEAR,
CHECKMONTH,
SENDWAY,
SEEBORDNO,
SEEBORDCODE,
SENDWAYCODE,
TOOLID,
DESKCOPIES,
PICKSEQ,
PICKNO,
KEEPER,
ISCOMPLETE,
CMPWORKPLACE,
OPERATOR,
EXPORTSFLAG,
DEPTID,
PICKID,
BRETURN,
BILLTYPE,
TIMEZONE)
select DETAILID,
OUTID,
VENDORID,
MATERIALID,
BATCHNO,
REQQTY,
USINGQTY,
UNIT,
BINID,
WAREHOUSEID,
AREAID,
SHELFID,
ROWNO,
COLNO,
STATE,
ASSIGNER,
ASSIGNDATE,
PICKER,
PICKDATE,
OUTPERSON,
OUTDATE,
VERIFYMAN,
VERIFYDATE,
SOURCEID,
LINEID,
VALID,
OPERDATE,
IP,
OUTDETAILID,
OPERSTATE,
MATERIALSTATE,
REASONID,
WORKPLACE,
DAYCLEAR,
PRINTSTATE,
PRINTIP,
PERIOD,
PICKQTY,
OUTQTY,
CONFIRMQTY,
CARCODE,
CARORDERNO,
CARBATCHNO,
CARSEQUENCE,
OLDVENDORID,
BMODIFY,
TRANSFER,
CPICKER,
CHECKYEAR,
CHECKMONTH,
SENDWAY,
SEEBORDNO,
SEEBORDCODE,
SENDWAYCODE,
TOOLID,
DESKCOPIES,
PICKSEQ,
PICKNO,
KEEPER,
ISCOMPLETE,
CMPWORKPLACE,
OPERATOR,
EXPORTSFLAG,
DEPTID,
PICKID,
BRETURN,
BILLTYPE,
TIMEZONE
from bo_outdetail_pull A
where A.Ifemptybill <> '1'
and A.Pickbillstate = '2'
and exists (select 1
from BO_PICKBILL_PULL B
where B.state = '0'
and B.Wmsstate = '1'
and A.Pickid = B.Pickid);
-------------生成拣配单并将BO_OUTDETAIL插入原表后，将BO_OUT_PULL的CREATEFLAG更改
update bo_out_pull c
set c.CREATEFLAG = '1'
where exists (select 1
from Bo_Outdetail_Pull A
join BO_PICKBILL_PULL B
on A.PICKID = B.PICKID
WHERE A.PICKBILLSTATE = '2'
AND B.STATE = '0'
AND B.WMSSTATE = '1'
AND c.OUTID = A.OUTID);
-------------------
insert into BO_OUT
(OUTID,
REFNO,
DEPTID,
WAREHOUSEID,
OUTTYPE,
LOGICHOUSEID,
STATE,
LINEID,
VALID,
REMARK,
OPERATOR,
OPERDATE,
IP,
DOOR,
KEEPER,
PICKER,
WORKPLACE,
PLANDATE,
TIMEZONE,
PRINTSTATE,
PRINTIP,
CREATEFLAG)
select OUTID,
REFNO,
DEPTID,
WAREHOUSEID,
OUTTYPE,
LOGICHOUSEID,
STATE,
LINEID,
VALID,
REMARK,
OPERATOR,
OPERDATE,
IP,
DOOR,
KEEPER,
PICKER,
WORKPLACE,
PLANDATE,
TIMEZONE,
PRINTSTATE,
PRINTIP,
CREATEFLAG
from bo_out_pull
where CREATEFLAG = '1'
and ifadd = '0';
update bo_out_pull
set ifadd = '1'
where CREATEFLAG = '1'
AND ifadd = '0';
update BO_PICKBILL_PULL
set state = '1'
where WMSSTATE = '1'
and state = '0';
end PDA_CREATEPICKBILL;
强制生单功能：
create or replace procedure PDA_Forced_Createpickbill(in_deptid in VARCHAR2, --车间
in_produceLine in VARCHAR2, --生产线
in_userID in VARCHAR2, --用户
out_success out varchar2 --成功 1成功 0失败
) as
VOutOrderID VARCHAR2(50); ---出库单号
VOutBillID VARCHAR2(50); ---拣配单号
vPICKSEQ varchar2(10); --拣配顺序
v_ErroText varchar2(200); --错误信息
v_Sysdate varchar2(10); --时间
v_Row integer;
v_Count integer;
v_Flag integer;
begin
vPICKSEQ := 0;
vOutOrderID := SP_CREATEBILLNO.FC_GetOutOrderID_Four('DC', '2', in_userID);
IF vOutOrderID is null THEN
raise_application_error('-20002', '出库单号生成失败');
END IF;
v_Sysdate := To_char(sysdate, 'yyyymmdd');
v_ErroText := null;
---------------将bo_outdetail_pull中批次号的扫描点信息插入到强制生单临时表---------------
insert into alxmz.TMP_FORCEDCREATE@cmmpzs
(BARCODE, SCANPOINT)
(select distinct barcode, scanpoint
from ((select distinct barcode, scanpoint
from alxmz.z_pdainfolog@cmmpzs A
where exists (select 1
from bo_outdetail_pull C
where C.pickbillstate <> '2'
and C.realorder is not null
and A.barcode = C.carbatchno)) union
(select barcode, scanpoint
from alxmz.z_pdainfo@cmmpzs B
where exists (select 1
from bo_outdetail_pull C
where C.pickbillstate <> '2'
and C.realorder is not null
and B.barcode = C.carbatchno)))
);
v_Row := 0;
--------------------------强制拉动-----------------wtj2013.11.10.1842更新
FOR cur_OnePickBill in (select WORKPLACE, ---工位
WAREHOUSEID, ---库房
TOOLID, ---箱号
KEEPER, ---保管员
CPICKER, ---拣配员
SENDWAY,
pullpoint,
MAX(LINEID) as LINEID, ---生产线
DEPTID, ---部门
deskcopies, ---器具容量
MAX(PLANDATE) AS PLANDATE ---计划上线日期
from bo_outdetail_pull A
where --(sendway = '02' or sendway = '04')取消判断 and
PICKBILLSTATE = '0'
--and Ifemptybill <> '1' 空的也强制生单
and Scandate < = v_Sysdate --变成小于等于
and DEPTID = in_deptid
and REALORDER is not null
and exists (select 1
from alxmz.z_ppwscode@cmmpzs B
where A.Lineid = B.productline
and B.produceline = in_produceLine
and A.Deptid = B.WSCODE)
group by WORKPLACE,
TOOLID,
WAREHOUSEID,
KEEPER,
CPICKER,
DEPTID,
deskcopies,
sendway,
pullpoint)
/*筛选条件为：生成简配单状态为0，当前拉动点，扫描时间和项次小于当前*/
loop
----拣配顺序加
vPICKSEQ := vPICKSEQ + 1;
--生成拣配单耗--
VOutBillID := vOutOrderID || '_' || LPAD(vPICKSEQ, 3, '0');
v_Flag:=0;
FOR cur_TwoPickBill in (select DETAILID, carbatchno
from bo_outdetail_pull A
where WORKPLACE = cur_OnePickBill.Workplace
and WAREHOUSEID = cur_OnePickBill.Warehouseid
and TOOLID = cur_OnePickBill.Toolid
and KEEPER = cur_OnePickBill.KEEPER
and CPICKER = cur_OnePickBill.CPICKER
and DEPTID = cur_OnePickBill.DEPTID
and sendway = cur_OnePickBill.SENDWAY
and pullpoint = cur_OnePickBill.pullpoint
and PICKBILLSTATE = '0'
--and Ifemptybill <> '1' 同上
and Scandate <= v_Sysdate --同上
and REALORDER is not null
and exists
(select 1
from alxmz.z_ppwscode@cmmpzs B
where A.Lineid = b.productline
and B.produceline = in_produceLine
and A.Deptid = B.WSCODE)
ORDER BY Scandate, REALORDER asc) loop
------判断该物料的所在批次所在的扫描点，与该物料的扫描点相符将处罚改拉动点物料的扫描
v_Count := 0;
select count(1)
into v_Count
from alxmz.TMP_FORCEDCREATE@cmmpzs A
where A.BARCODE = cur_TwoPickBill.carbatchno
and A.SCANPOINT = cur_OnePickBill.Pullpoint;
if (v_Count > 0) then
update bo_outdetail_pull
set PICKID = VOutBillID, PICKBILLSTATE = '2'
where DETAILID = cur_TwoPickBill.DETAILID;
v_Flag:=v_Flag+1;
end if;
END LOOP;
if(v_Flag>0) then
insert into BO_PICKBILL_PULL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
deskcopies,
sendway,
PLANDATE,
ERROCOLUMN,
PRODUCELINE)
VALUES
(VOutBillID,
cur_OnePickBill.WAREHOUSEID,
cur_OnePickBill.LINEID,
cur_OnePickBill.DEPTID,
cur_OnePickBill.KEEPER,
cur_OnePickBill.CPICKER,
cur_OnePickBill.WORKPLACE,
cur_OnePickBill.TOOLID,
cur_OnePickBill.deskcopies,
cur_OnePickBill.sendway,
substr(cur_OnePickBill.PLANDATE, 0, 6),
'Forced',
in_produceLine);
v_row := v_row + 1;
end if;
END LOOP;
if v_row = 0 then
out_success := '2';
return;
end if;
------------------------插入WMS数据-------------------------
---------插入BO_PICKBILL表,将STATE作为标志，先将符合条件的state更改为0---------
update BO_PICKBILL_PULL
set state = '0'
where WMSSTATE = '0'
and PLANDATE <= TO_CHAR(SYSDATE, 'yyyymm');
insert into BO_PICKBILL
(PICKID,
WAREHOUSEID,
LINEID,
DEPTID,
KEEPER,
PICKER,
WORKPLACE,
TOOLID,
DDATE,
BPRINT,
PRINTCOUNT)
select A.pickID,
A.WAREHOUSEID,
A.LINEID,
A.DEPTID,
A.KEEPER,
A.PICKER,
A.WORKPLACE,
A.TOOLID,
A.DDATE,
1,
99999
from BO_PICKBILL_PULL A
where A.state = '0'
and exists (select 1
from bo_outdetail_pull d
where a.pickid = d.pickid
and d.ifemptybill <> '1');
update BO_PICKBILL_PULL
set WMSSTATE = '1'
where WMSSTATE = '0'
and state = '0';
----------插入BO_PICKBILL后将WMSSTATE更改标志
insert into BO_OUTDETAIL
(DETAILID,
OUTID,
VENDORID,
MATERIALID,
BATCHNO,
REQQTY,
USINGQTY,
UNIT,
BINID,
WAREHOUSEID,
AREAID,
SHELFID,
ROWNO,
COLNO,
STATE,
ASSIGNER,
ASSIGNDATE,
PICKER,
PICKDATE,
OUTPERSON,
OUTDATE,
VERIFYMAN,
VERIFYDATE,
SOURCEID,
LINEID,
VALID,
OPERDATE,
IP,
OUTDETAILID,
OPERSTATE,
MATERIALSTATE,
REASONID,
WORKPLACE,
DAYCLEAR,
PRINTSTATE,
PRINTIP,
PERIOD,
PICKQTY,
OUTQTY,
CONFIRMQTY,
CARCODE,
CARORDERNO,
CARBATCHNO,
CARSEQUENCE,
OLDVENDORID,
BMODIFY,
TRANSFER,
CPICKER,
CHECKYEAR,
CHECKMONTH,
SENDWAY,
SEEBORDNO,
SEEBORDCODE,
SENDWAYCODE,
TOOLID,
DESKCOPIES,
PICKSEQ,
PICKNO,
KEEPER,
ISCOMPLETE,
CMPWORKPLACE,
OPERATOR,
EXPORTSFLAG,
DEPTID,
PICKID,
BRETURN,
BILLTYPE,
TIMEZONE)
select DETAILID,
OUTID,
VENDORID,
MATERIALID,
BATCHNO,
REQQTY,
USINGQTY,
UNIT,
BINID,
WAREHOUSEID,
AREAID,
SHELFID,
ROWNO,
COLNO,
STATE,
ASSIGNER,
ASSIGNDATE,
PICKER,
PICKDATE,
OUTPERSON,
OUTDATE,
VERIFYMAN,
VERIFYDATE,
SOURCEID,
LINEID,
VALID,
OPERDATE,
IP,
OUTDETAILID,
OPERSTATE,
MATERIALSTATE,
REASONID,
WORKPLACE,
DAYCLEAR,
PRINTSTATE,
PRINTIP,
PERIOD,
PICKQTY,
OUTQTY,
CONFIRMQTY,
CARCODE,
CARORDERNO,
CARBATCHNO,
CARSEQUENCE,
OLDVENDORID,
BMODIFY,
TRANSFER,
CPICKER,
CHECKYEAR,
CHECKMONTH,
SENDWAY,
SEEBORDNO,
SEEBORDCODE,
SENDWAYCODE,
TOOLID,
DESKCOPIES,
PICKSEQ,
PICKNO,
KEEPER,
ISCOMPLETE,
CMPWORKPLACE,
OPERATOR,
EXPORTSFLAG,
DEPTID,
PICKID,
BRETURN,
BILLTYPE,
TIMEZONE
from bo_outdetail_pull A
where A.Ifemptybill <> '1'
and A.Pickbillstate = '2'
and exists (select 1
from BO_PICKBILL_PULL B
where B.state = '0'
and B.Wmsstate = '1'
and A.Pickid = B.Pickid);
-------------生成拣配单并将BO_OUTDETAIL插入原表后，将BO_OUT_PULL的CREATEFLAG更改
update bo_out_pull c
set c.CREATEFLAG = '1'
where exists (select 1
from Bo_Outdetail_Pull A
join BO_PICKBILL_PULL B
on A.PICKID = B.PICKID
WHERE A.PICKBILLSTATE = '2'
AND B.STATE = '0'
AND B.WMSSTATE = '1'
AND c.OUTID = A.OUTID);
-------------------
insert into BO_OUT
(OUTID,
REFNO,
DEPTID,
WAREHOUSEID,
OUTTYPE,
LOGICHOUSEID,
STATE,
LINEID,
VALID,
REMARK,
OPERATOR,
OPERDATE,
IP,
DOOR,
KEEPER,
PICKER,
WORKPLACE,
PLANDATE,
TIMEZONE,
PRINTSTATE,
PRINTIP,
CREATEFLAG)
select OUTID,
REFNO,
DEPTID,
WAREHOUSEID,
OUTTYPE,
LOGICHOUSEID,
STATE,
LINEID,
VALID,
REMARK,
OPERATOR,
OPERDATE,
IP,
DOOR,
KEEPER,
PICKER,
WORKPLACE,
PLANDATE,
TIMEZONE,
PRINTSTATE,
PRINTIP,
CREATEFLAG
from bo_out_pull
where CREATEFLAG = '1'
and ifadd = '0';
update bo_out_pull
set ifadd = '1'
where CREATEFLAG = '1'
AND ifadd = '0';
update BO_PICKBILL_PULL
set state = '1'
where WMSSTATE = '1'
and state = '0';
insert into alxmz.Z_PDASCANLOG@cmmpzs
(SCANTIME, USERID, PRODUCTLINE, SUCCESS)
values
(SYSDATE, in_userID, in_produceLine, '是');
out_success := '1';
EXCEPTION
WHEN OTHERS THEN
v_ErroText := SUBSTR(SQLERRM, 1, 200);
if (v_ErroText is not null) then
insert into alxmz.Z_PDASCANLOG@cmmpzs
(SCANTIME, USERID, PRODUCTLINE, SUCCESS, ERRMSG)
values
(SYSDATE, in_userID, in_produceLine, '否', v_ErroText);
out_success := '0';
end if;
end PDA_Forced_Createpickbill;
821
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
