GO
/****** Object: StoredProcedure [dbo].[Pro_DistributionBatch] Script Date: 2019-11-20 13:58:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- exec [Pro_DistributionBatch]
ALTER PROCEDURE [dbo].[Pro_DistributionBatch]
AS
BEGIN
DECLARE @sumPartNo varchar(50)
DECLARE @sumQty decimal --总可用数量
DECLARE @useQty decimal --总已使用数量
DECLARE @okQty decimal --总可用数量
DECLARE @countQty decimal
DECLARE @id varchar(50)
DECLARE moinfo CURSOR
FOR
--获取生产任务单第一个工艺 查询当天信息
select mbomp_material.unode,mo_rmb.mo_id,mo.mo_no,mbomp_material.mtr_part_id,
p.part_no, p.part_name AS material_part_name,p.part_spec AS material_part_spec,
mo_rmb.wkp_id,wkp.wkp_no,wkp.wkp_name,p.part_unit,u.unit_name AS mtr_qty_unit_name,
mbomp_material.mtr_qty*mo_rmb.wkn_req_qty as llQty, --用量*生产任务单数量 = 领料数量
mo.part_id as mo_part_id,mo.part_no as mo_part_no,mo.part_name as mo_part_name,mo.part_spec as mo_part_spec
FROM mo_rmb_mtr mbomp_material
INNER JOIN mo_rmb ON mbomp_material.unode = mo_rmb.unode
LEFT OUTER JOIN pdm_part p ON mbomp_material.mtr_part_id = p.part_id and p.part_no like '01.%' --暂时只处理 01开头物料
inner join mo mo on mo.mo_id = mo_rmb.mo_id AND mo.mo_id like 'WORK%'
left outer join pdm_wkp wkp on wkp.wkp_id = mo_rmb.wkp_id
left outer join av_bas_unit_13 u ON p.part_unit=u.cdvl_id
where datediff(dd,mo.crt_time,GETDATE())=0 --and mo_rmb.mo_id='WORK215514'
and mo.rmk12 is null
OPEN moinfo
DECLARE @unode varchar(50), @moId varchar(50),@moNo varchar(50), @partId varchar(50),@partNo varchar(50),@partName varchar(50),
@partSpec varchar(50),@wkpId varchar(50),@wkpNo varchar(50),@wkpName varchar(50),@unit int,@unitName varchar(50),
@llQty decimal,@moPartId varchar(50),@moPartNo varchar(50),@moPartName varchar(50),@moPartSpec varchar(50)
FETCH NEXT FROM moinfo INTO @unode, @moId,@moNo, @partId,@partNo,@partName,@partSpec,@wkpId,@wkpNo,@wkpName,@unit,@unitName,@llQty,@moPartId,@moPartNo,@moPartName,@moPartSpec
WHILE @@FETCH_STATUS =0
BEGIN
if(@partNo is not null)
BEGIN
SET @okQty = 0
SET @sumPartNo = ''
SET @useQty = 0
SET @countQty =0
SET @id = ''
select @sumPartNo=part_no,@okQty=sum(CONVERT(decimal,POH_RMK6))-sum(CONVERT(decimal,POH_RMK5)),@useQty=sum(cast(POH_RMK5 as decimal)),
@sumQty = sum(cast(POH_RMK6 as decimal))
from srm_po_dlv
where part_no=@partNo and si_conclusion = 10 group by part_no having sum(CONVERT(decimal,POH_RMK6))>sum(CONVERT(decimal,POH_RMK5))
print(isnull(@okQty,0)) print(@moId)
BEGIN TRY
BEGIN TRAN
if(@okQty>=@llQty)
BEGIN
SET @id = newid()
insert into mes_mtr_use_h (mo_no,mo_id,crt_host,crt_time,crt_user,mes_mtr_use_h_datetime,mes_mtr_use_h_no,mes_mtr_use_hid,part_id,
part_name,part_spec,part_no,upd_host,upd_time,upd_user,mes_mtr_use_h_status,mes_mtr_use_h_io_type,wkc_id,wkc_name,wkc_no,wkn)
values(@moId,@moNo,'sqlserver',getdate(),'sqlserver',getdate(),@moNo,@id,@moPartId,@moPartName,@moPartSpec,@moPartNo,
'sqlserver',getdate(),'sqlserver',110,0,'sqlserver','sqlserver','sqlserver','sqlserver')
DECLARE batchs CURSOR
FOR
select a.po_id,case when b.barcode_id is null then a.rmk1 else b.barcode_id END as batch,cast(a.POH_RMK6 as decimal)- cast(a.POH_RMK5 as decimal) as okQty1,cast(a.POH_RMK5 as decimal) as useQty,a.part_id,a.part_no,a.part_name,a.part_spec,a.po_no,a.rmk2 as poEntity
from srm_po_dlv a
left join av_srm_po_b3 b on a.po_id = b.po_hid
where a.part_no =@partNo
and si_conclusion = 10
and CONVERT(decimal,a.POH_RMK5)<CONVERT(decimal,a.POH_RMK6) order by a.crt_time asc
OPEN batchs
DECLARE @poId varchar(50),@batch varchar(50),@okQty1 decimal, @useQty1 decimal,@partId1 varchar(50),@partNo1 varchar(50),@partName1 varchar(50),@partSpec1 varchar(50),@po_no varchar(50),@poEntity varchar(10)
FETCH NEXT FROM batchs INTO @poId,@batch, @okQty1,@useQty1,@partId1,@partNo1,@partName1,@partSpec1,@po_no,@poEntity
WHILE @@FETCH_STATUS =0
BEGIN
print(@poId) print(@batch) print(@okQty1) print(@useQty1) print(@partNo1) print(@partName1) print(@po_no)
--SET @countQty = isnull(@countQty,0)
if(@partNo1=@sumPartNo)
BEGIN
if(@llQty<=@okQty1)
BEGIN
update srm_po_dlv SET POH_RMK5 = @useQty1+@llQty-isnull(@countQty,0) where po_id=@poId
update mo SET rmk12 = 1 where mo_id = @moId --分配批次号数据做上标记防止重复分配
insert into mes_mtr_use_b(mtr_lot_no, mes_mtr_use_bid, mes_mtr_use_hid, mes_mtr_use_b_status, mes_mtr_use_b_datetime,
mtr_qty, crt_user, crt_host, crt_time, upd_user, upd_host, upd_time,upd_user_name,mtr_part_id,mtr_part_no,mtr_part_name,mtr_part_spec)
values(@batch,newid(),@id,'sqlserver',getdate(),
@llQty-isnull(@countQty,0), 'sqlserver','sqlserver',getdate(),'sqlserver','sqlserver',getdate(),'sqlserver',@partId1,@partNo1,@partName1,@partSpec1)
break
fetch next from moinfo
END
if(@llQty>@okQty1)
BEGIN
SET @countQty =@countQty + @okQty1
if(@countQty>=@llQty)
BEGIN
update srm_po_dlv SET POH_RMK5 = @useQty1+(@llQty-(@countQty-@okQty1)) where po_id=@poId
update mo SET rmk12 = 1 where mo_id = @moId --分配批次号数据做上标记防止重复分配
insert into mes_mtr_use_b(mtr_lot_no, mes_mtr_use_bid, mes_mtr_use_hid, mes_mtr_use_b_status, mes_mtr_use_b_datetime,
mtr_qty, crt_user, crt_host, crt_time, upd_user, upd_host, upd_time,upd_user_name,mtr_part_id,mtr_part_no,mtr_part_name,mtr_part_spec)
values(@batch,newid(),@id,'sqlserver',getdate(),
@llQty-(@countQty-@okQty1), 'sqlserver','sqlserver',getdate(),'sqlserver','sqlserver',getdate(),'sqlserver',@partId1,@partNo1,@partName1,@partSpec1)
break
fetch next from moinfo
--fetch next from batchs continue
END
else
BEGIN
update srm_po_dlv SET POH_RMK5 = @useQty1+@okQty1 where po_id=@poId
update mo SET rmk12 = 1 where mo_id = @moId --分配批次号数据做上标记防止重复分配
insert into mes_mtr_use_b(mtr_lot_no, mes_mtr_use_bid, mes_mtr_use_hid, mes_mtr_use_b_status, mes_mtr_use_b_datetime,
mtr_qty, crt_user, crt_host, crt_time, upd_user, upd_host, upd_time,upd_user_name,mtr_part_id,mtr_part_no,mtr_part_name,mtr_part_spec)
values(@batch,newid(),@id,'sqlserver',getdate(),
@okQty1, 'sqlserver','sqlserver',getdate(),'sqlserver','sqlserver',getdate(),'sqlserver',@partId1,@partNo1,@partName1,@partSpec1)
END
END
END
FETCH NEXT FROM batchs INTO @poId,@batch, @okQty1,@useQty1,@partId1,@partNo1,@partName1,@partSpec1,@po_no,@poEntity
END
CLOSE batchs
DEALLOCATE batchs
END
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
print('异常----------------------------------------------回滚事务')
ROLLBACK TRAN
END
END CATCH
END
FETCH NEXT FROM moinfo INTO @unode, @moId,@moNo, @partId,@partNo,@partName,@partSpec,@wkpId,@wkpNo,@wkpName,@unit,@unitName,@llQty,@moPartId,@moPartNo,@moPartName,@moPartSpec
END
CLOSE moinfo
DEALLOCATE moinfo
END