1.找登录账号密码
http://124.71.33.56/Content/sys/UserMessage.aspx
ip换客户IP，查看网页请求
http://124.71.33.56/content/sys/menu.aspx#
2.找某工单线体车间等信息
select top 9999 W.NAME,l.name '线体',s.name 工站,r.name 工序 from CT_ROUTESTEP_STATIONS c
left join CT_STATIONS s on c.station_id=s.station_id
left join ct_lines l on s.line_id=l.line_id
LEFT JOIN CT_WORK_CENTERS W ON W.WORK_CENTER_ID=L.WORK_CENTER_ID
left join QS_ROUTE_STEPS r on c.route_step_id=r.route_step_id
LEFT JOIN QS_ROUTES_DEFINITIONS RT ON RT.ROUTE_STEP_ID=R.route_step_id
where
RT.route_id<>''
and RT.route_id=(select p.route_id from [Udit.SFC].[dbo].[qs_lots] lot
left join [Udit.SFC].[dbo].[qs_products] p on lot.product_id=p.product_id
where lot_name='JGTEST2332711')3.综合查询相关查询
-- 工单条码
select SERIAL_NUMBER,QTY, '下一站'=isnull(r.name,'完工') from [Udit.sfc].[dbo].[qs_serial_numbers] s
left join [Udit.sfc].[dbo].[qs_route_steps] r on s.next_step_id=r.route_step_id
where lot_name=@lot;
--工艺流程
select b.name '工艺名',c.name '工序名',a.seq,a.IS_OFFLINE from QS_ROUTES_DEFINITIONS a
left join QS_ROUTES b on a.ROUTE_ID= b.ROUTE_ID
left join QS_ROUTE_STEPS c on a.ROUTE_STEP_ID=c.ROUTE_STEP_ID
where a.route_id<>''
and a.route_id=(select p.route_id from [Udit.SFC].[dbo].[qs_lots] lot
left join [Udit.SFC].[dbo].[qs_products] p on lot.product_id=p.product_id
where lot_name=@lot)
order by a.route_id
工单所在车间线体一览
select top 9999 W.NAME,l.name '线体',s.name 工站,r.name 工序 from CT_ROUTESTEP_STATIONS c
left join CT_STATIONS s on c.station_id=s.station_id
left join ct_lines l on s.line_id=l.line_id
LEFT JOIN CT_WORK_CENTERS W ON W.WORK_CENTER_ID=L.WORK_CENTER_ID
left join QS_ROUTE_STEPS r on c.route_step_id=r.route_step_id
LEFT JOIN QS_ROUTES_DEFINITIONS RT ON RT.ROUTE_STEP_ID=R.route_step_id
where
RT.route_id<>''
and RT.route_id=(select p.route_id from [Udit.SFC].[dbo].[qs_lots] lot
left join [Udit.SFC].[dbo].[qs_products] p on lot.product_id=p.product_id
where lot_name='JGTEST2332711')
--工单wip
select lot.Lot_Name 工单,[ProductName] 产品,lot.QUANTITY 工单数量 ,[Station_Name] 工序站点,[Route_Step_Name] 工艺名称,[PASS] 通过数量,[Fail] 不良数,(lot.QUANTITY -[PASS]) 剩余板数,PASS_OUT 委外通过数,FAIL_OUT 委外不良数 from [Udit.SFC].[dbo].[CT_LotRouteStatus] (nolock) ctlot join [Udit.SFC].[dbo].[QS_LOTS] (nolock) lot on ctlot.Lot_Name =lot.LOT_NAME where lot.Lot_Name=@lot ;
--上料记录
SELECT DISTINCT 工单,产品,工位,贴片面,机器号,模组,站位,UID,R.MATERIAL_NO 料号, R.LOT_NO 批次,''规格,Datetime 时间,StatusCode 类型,AA.Operator 操作员 ,AA.QUANTITY 数量 FROM( select distinct OrderName 工单, SetupName 产品, line 工位, isnull(Face, '') 贴片面, station 机器号, Tableno 模组, pickup 站位, CASE WHEN UID = '' THEN REVERSE(SUBSTRING(REVERSE(Left(LoadedRefillUID, charindex(':', LoadedRefillUID))), 2, 50)) ELSE UID END UID, b.MATERIAL_NO 料号, b.LOT_NO 批次, b.SupplierPartNumber 规格,a.Datetime,StatusCode,Operator,a.QUANTITY FROM[Udit.CHC].[dbo].[TraceData](nolock) a left join[Udit.Mms].[dbo].[mms_reels](nolock) b on a.uid = b.REEL_BARCODE where ordername = @lot and StatusCode in('SetupCheck_True','SetdownCheck_True') and StatusCode != 'IPQC_True' )AA LEFT JOIN[Udit.Mms].[dbo].[mms_reels]R(nolock) ON AA.UID = R.REEL_BARCODE ORDER BY 时间,机器号,工位 DESC, 站位, R.MATERIAL_NO
-- 不良记录
select * from( select ROW_NUMBER() OVER(ORDER BY def.INSPECT_TIME asc) a, def.serial_number 条码,CONVERT(varchar(12) , def.INSPECT_TIME, 102 ) +' '+ CONVERT(varchar(12) , def.INSPECT_TIME, 108 ) 记录时间,def.inspect_user 不良记录着,CONVERT(varchar(12) , def.REPAIR_TIME, 102 ) +' '+ CONVERT(varchar(12) , def.REPAIR_TIME, 108 ) 维修时间,def.defect_type 缺陷分类,def.DEFECT_LABEL 缺陷类型,def.DEFECT_LOCATION 缺陷位置,(case when repair_status=1 and rep.REPAIR_ACTION=1 then '已修' when rep.REPAIR_ACTION=2 then '报废' when repair_status=2 then '误测' else '未修' end) 状态,isnull(rep.REPAIR_DESC,'') 维修备注,rep.defectAnalysis 不良原因分析, rep.defectCategory 不良类别, def.REPAIR_USER 维修者, org.OrganizeName 责任部门,(case rep.REPAIR_ACTION when 1 then '正常维修' when 2 then '报废' else '' end ) 维修类型, def.QTY 数量 from [Udit.SFC].[dbo].[QS_DEFECTS] (nolock) def left join [Udit.SFC].[dbo].[QS_REPAIR_INFO] (nolock) rep on def.DEFECT_ID=rep.DEFECT_ID left join [Udit.Sys].[dbo].[sys_organize] (nolock) org on rep.dutyDept=org.OrganizeCode where def.lot_name=@lot ) s where 1=1
-- 绑定关联
select LOT_NAME,SERIAL_NUMBER,STATION_NAME,MATERIEL_NO,ASSEMBLY_SN,ASSEMBLY_TIME,ASSEMBLY_USER from [Udit.sfc].[dbo].[QS_ASSEMBLY] where lot_name=@lot
--工艺流程
select b.name '工艺名',c.name '工序名',a.seq,a.IS_OFFLINE from QS_ROUTES_DEFINITIONS a
left join QS_ROUTES b on a.ROUTE_ID= b.ROUTE_ID
left join QS_ROUTE_STEPS c on a.ROUTE_STEP_ID=c.ROUTE_STEP_ID
where a.route_id<>''
and a.route_id=(select p.route_id from [Udit.SFC].[dbo].[QS_SERIAL_NUMBERS] s
left join [Udit.SFC].[dbo].[qs_products] p on s.MODEL_NO=p.name
where s.serial_number='22100003')
order by a.route_id--条码状态
SEELCT
--过站记录
select w.SERIAL_NUMBER,w.LOT_NAME lot_name, w.MODEL_NO model_no,w.reelBarcode reelBarcode, CONVERT(varchar(12) ,
w.START_TIME, 102 ) +' '+ CONVERT(varchar(12) , w.START_TIME, 108 ) start_time,
w.XUSER xuser,(select name from [dbo].[QS_ROUTE_STEPS] (nolock) where route_step_id=w.route_step_id) droute,sysline.ShortName , isnull((select name from [dbo].[QS_ROUTE_STEPS] (nolock) where route_step_id=w.next_step_id),'完工') nroute, (case w.WIP_STATE when 0 then 'PASS' else 'Fail'end) WIP_STATE , (case SCAN_TYPE when 0 then '扫码过站' when 22 then '接口过站' when 44 then '维修过站' when 45 then '跳站过站' when 70 then '解绑操作' when 11 then '拆箱操作' when 99 then 'DCP数据采集' when 21 then '误判' when 91 then '条码导入' when 77 then '误测' when 23 then 'RT过站' when 12 then 'BL快修' when 13 then 'BL交换' when 14 then 'BL维修' when 24 then '委外过站' else '其它操作' end ) SCAN_TYPE, (select L.NAME from [dbo].[CT_STATIONS] (nolock) s join [dbo].[CT_LINES] (nolock) l on s.LINE_ID=l.LINE_ID where s.STATION_ID=w.STATION_ID) 线别,n.level 等级 FROM [Udit.SFC].[dbo].[view_wip] (nolock) w join [Udit.Sys].[dbo].[Sys_Resource] (nolock) sysline on w.station_id=sysline.ResourceId left join [Udit.SFC].[dbo].QS_SERIAL_NUMBERS(nolock) n on w.SERIAL_NUMBER=n.SERIAL_NUMBER
where w.serial_number = @sn ;
--箱号查询
select box.box_number,box.box_type,box.box_total,box.box_weight,box.lot_name,box.product_name,item.route_step_name ,item.create_user_name,item.create_time ,l.name line_name
from ct_boxs box
left join CT_BOX_ITEMS item on box.box_number=item.box_number
left join [dbo].[CT_STATIONS] (nolock) s on s.STATION_ID=box.STATION_ID
left join [dbo].[CT_LINES] (nolock) l on s.LINE_ID=l.LINE_ID
where item.serial_number='22100003'
--载具查询
select car.lot_name ,car.product_name,car.car_number,car.car_size,item.create_time,item.create_user_name
from CT_CARS_HISTORY car
left join CT_CAR_ITEMS_HISTORY item on item.car_number=car.car_number
where item.serial_number='22100003'
--拼版查询
select distinct serial_number,serial_number_id ,pb, PANEL_INDEX from(select serial_number,serial_number_id ,pb='子板',PANEL_INDEX from [Udit.SFC].DBO.QS_SERIAL_NUMBERS (nolock) where SERIAL_NUMBER_ID in(select ChildItemInventoryID from [Udit.SFC].DBO.DW_PanelLinks (nolock) where ParentItemInventoryID=(select top 1 ParentItemInventoryID from [Udit.SFC].DBO.DW_PanelLinks (nolock) where ChildItemInventoryID=(select top 1 SERIAL_NUMBER_ID from [Udit.SFC].DBO.QS_SERIAL_NUMBERS (nolock) where
SERIAL_NUMBER = @sn)))
union all select serial_number,serial_number_id ,pb='母板',PANEL_INDEX from [Udit.SFC].DBO.QS_SERIAL_NUMBERS (nolock) where SERIAL_NUMBER_ID= (select top 1 ParentItemInventoryID from [Udit.SFC].DBO.DW_PanelLinks (nolock) where ChildItemInventoryID=(select top 1 SERIAL_NUMBER_ID from [Udit.SFC].DBO.QS_SERIAL_NUMBERS (nolock) where SERIAL_NUMBER = @sn))
union all select serial_number,serial_number_id ,pb='子板',PANEL_INDEX from [Udit.SFC].DBO.QS_SERIAL_NUMBERS (nolock) where SERIAL_NUMBER_ID in(select ChildItemInventoryID from [Udit.SFC].DBO.DW_PanelLinks (nolock) where ParentItemInventoryID=(select top 1 SERIAL_NUMBER_ID from [Udit.SFC].DBO.QS_SERIAL_NUMBERS where SERIAL_NUMBER = @sn)) ) dd order by PANEL_INDEX;
--绑定关联
select LOT_NAME,SERIAL_NUMBER,STATION_NAME,MATERIEL_NO,ASSEMBLY_SN,ASSEMBLY_TIME,ASSEMBLY_USER from [Udit.sfc].[dbo].[QS_ASSEMBLY] where serial_number=@sn
SQL
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;
ALTER TABLE table_name
ADD column_name data_type [NULL | NOT NULL] [DEFAULT default_value]
--备料，调拨，其他单据
select a.[BillNo]
,a.[ContractCode]
,a.[inWarehouseCode] 仓库编码
,w.[WarehouseName] 备料仓名
,b.[RowId] 备料项次
,(b.[Num]-b.[RemainNum]) 已发数
,b.[Num] 需求数
,b.[MaterialCode]
,c.[REEL_BARCODE]
,c.[LOCATION]
,color.[name]
,color.[color] 需要颜色
,d.[color] 亮灯颜色
FROM [Udit.Mms].[dbo].[mms_bills] a
left join [Udit.Mms].[dbo].[mms_aibin_lightcolor] color on color.[pick_id]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_billsdetail] b on b.[BillNo]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_REELS] c on c.[MATERIAL_NO]=b.[MaterialCode] and c.STATUS=1 and c.[WAREHOUSE]=a.[inWarehouseCode]
left join [Udit.Mms].[dbo].[mms_aibins] d on d.[bin]=c.[LOCATION]
left join [Udit.Mms].[dbo].[mms_warehouse] w on w.[WarehouseCode]=a.[inWarehouseCode]
where a.billNO='PD221207000003'
union all
select a.[BillNo]
,a.[BillNo]
,a.[WarehouseCode] 仓库编码
,w.[WarehouseName] 备料仓名
,b.[RowId] 备料项次
,(r.[Num]-b.[Qty]) 已发数
,r.[Num] 需求数
,b.[Material_Code]
,c.[REEL_BARCODE]
,c.[LOCATION]
,color.[name]
,color.[color] 需要颜色
,d.[color] 亮灯颜色
FROM [Udit.Mms].[dbo].[mms_transfer] a
left join [Udit.Mms].[dbo].[mms_aibin_lightcolor] color on color.[pick_id]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_transferreels] b on b.[BillNo]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_transferDetail] r on r.[BillNo]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_REELS] c on c.[MATERIAL_NO]=b.[Material_Code] and c.STATUS=1 and c.[WAREHOUSE]=a.[WarehouseCode]
left join [Udit.Mms].[dbo].[mms_aibins] d on d.[bin]=c.[LOCATION]
left join [Udit.Mms].[dbo].[mms_warehouse] w on w.[WarehouseCode]=a.[WarehouseCode]
where a.billNO='PD221207000003'
union all
SELECT a.[BillNo]
,a.[ContractCode]
,b.[WarehouseCode] 仓库编码
,w.[WarehouseName] 备料仓名
,b.[RowId] 备料项次
,(b.[Num]-b.[RemainNum]) 已发数
,b.[Num] 需求数
,b.[MaterialCode]
,c.[REEL_BARCODE]
,c.[LOCATION]
,color.[name]
,color.[color] 需要颜色
,d.[color] 亮灯颜色
FROM [Udit.Mms].[dbo].[mms_pick] a
left join [Udit.Mms].[dbo].[mms_aibin_lightcolor] color on color.[pick_id]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_pickDetail] b on b.[BillNo]=a.[BillNo]
left join [Udit.Mms].[dbo].[mms_REELS] c on c.[MATERIAL_NO]=b.[MaterialCode] and c.STATUS=1 and c.[WAREHOUSE]=b.[WarehouseCode]
left join [Udit.Mms].[dbo].[mms_aibins] d on d.[bin]=c.[LOCATION]
left join [Udit.Mms].[dbo].[mms_warehouse] w on w.[WarehouseCode]=b.[WarehouseCode]
where [ContractCode] like '%PD221207000003'
and b.[RemainNum] >0
order by b.[RowId] asc
--包装设置
select top 100 * from
SYS_PACK_SETTING --跨库查询
select top 100* from
mms_reels(nolock) a
left join
openrowset('SQLOLEDB','124.71.33.56';'sa';'password.',[Udit.Mms].[dbo].[mms_reels]) b
on a.reel_barcode=b.reel_barcode
where a.reel_barcode=b.reel_barcode-- IQC检验查询
select top 200
'检验结果'=case when a.ApproveState='1' then '提交' when a.ApproveState='0' then '未提交' end
,a.BillNo '检验单号',a.Receive_BillNo '收料单号'
,d.MaterialCode '物料编码',d.MaterialName '物料名称'
,s2.Merchantsname '供应商名称'
,d.Receive_Qty '来料数量',d.Sample_Qty '抽检数量'
,iq.attribute_value '扫描数量',iq1.attribute_value '合格数量',iq2.attribute_value '不合格数量'
,s.username '检验人',s.createDate '检验时间',s1.username '批准人',a.ApproveDate '批准时间'
from
mms_IQC_Orico_Master(nolock) a
left join [Udit.Sys].dbo.sys_user(nolock) s on a.createperson=s.usercode
left join [Udit.Sys].dbo.sys_user(nolock) s1 on a.ApprovePerson=s1.usercode
left join mms_IQC_Orico_Detail(nolock) d on d.billno=a.billno
left join mms_receive(nolock) r on d.Receive_BillNo=r.billno
left join mms_merchants(nolock) s2 on r.SupplierCode=s2.MerchantsCode
left join mms_IQC_Result(nolock) re on r.billno=re.rebillno and d.rowid=re.rowid
left join mms_iqc_formcontents(nolock) iq on iq.id=a.billno and iq.[attribute_key]='SmokeNum'
left join mms_iqc_formcontents(nolock) iq1 on iq1.id=a.billno and iq1.[attribute_key]='GoodNum'
left join mms_iqc_formcontents(nolock) iq2 on iq2.id=a.billno and iq2.[attribute_key]='BadNum'
order by a.BillNo desc-- 锁表查询
select request_session_id spid,OBJECT_NAME(resource_associated_entity_id) tableName
from sys.dm_tran_locks where resource_type='OBJECT'
--杀死锁表进程
declare @spid int
Set @spid = 65 --锁表进程
declare @sql varchar(1000)
set @sql='kill '+cast(@spid as varchar)
exec(@sql)SELECT a.billNo,a.equipment,a.createDate,b.*
FROM [Udit.CHC].[dbo].[Tooling_SpotCheckMaster] A
JOIN [Udit.CHC].[dbo].[Tooling_SpotCheck] b ON b.pid = A.id
WHERE A.equipment='Q001693'
ORDER BY A.createUser DESC SELECT rt.name,s.name,r.seq FROM
QS_ROUTES_DEFINITIONS r
right join QS_ROUTE_STEPS s on s.route_step_id=r. route_step_id
right join qs_routes rt on rt.route_id=r.route_id
WHERE r.ROUTE_ID =(
SELECT ROUTE_ID FROM QS_PRODUCTS WHERE PRODUCT_ID=
(SELECT PRODUCT_ID FROM QS_LOTS WHERE LOT_NAME='MO006958')
)
order by seq asc收料
declare @date varchar(20)
set @date = '{date}'
if(@date='')
begin
set @date = getdate()
--set @date = '2020-12-28'
end
SELECT
re.BillNo 收料单号
,re.ApprovePerson 操作员
,CASE WHEN re.OriginalNum like '%OT%' THEN re.OriginalNum ELSE '' END 送货单号
, convert(varchar(12),re.CreateDate,111) 时间
,rt.MaterialCode 物料编码
,CEILING(rt.Num) 送货数量
,rt.SrcBillNo 采购单号
,rt.MaterialName 名称
,mer.MerchantsName 供应商
,case when re.OriginalNum like '%OT%' THEN '供应商送货' ELSE '内部收料' END 收料类型
FROM [Udit.Mms].[dbo].[mms_receive] re --收料单主表
join [Udit.Mms].[dbo].[mms_receivedetail] rt ON rt.BillNo = re.BillNo --收料单详情
join [Udit.Mms].[dbo].[mms_merchants] mer ON mer.MerchantsCode = re.SupplierCode --供应商目录
where datediff (day,ApproveDate,GETDATE()) = 0 order by ApproveDate asc
--where CONVERT(varchar(100), ApproveDate, 23) = '2020-12-29' order by ApproveDate asc查询收料状态
ALTER PROCEDURE [dbo].[receve_status_search]
@billno varchar(100),
@MaterialCode varchar(100),
@percharbill varchar(100),
@bill_status varchar(100),
@page int,
@row int
as
begin
declare @start_seq int,@end_seq int
set @start_seq = (@page - 1)*@row + 1
set @end_seq =@page*@row
select * from (
select ROW_NUMBER() OVER(ORDER BY BillNo desc) erq, * from (select V2.BillNo, V2.RowId, V2.SrcRowId ,V1.SupplierCode, V2.SRCBILLNO, V2.MaterialCode, v2.materialname, v2.Model, v2.num, v2.checknum, v1.contractcode, (d2.num - d2.remainnum) d2num, d1.erpbillno, v1.approvestate, isnull(d1.BillNo, '') cBillNo, isnull(d1.ERPBillNo, '') cERPBillNo, case when v1.ApproveState = 0 then '收货中' when(v1.ApproveState = 1 and v2.CheckNum is null) then '待IQC检验' when(d1.BillNo is null and v2.CheckNum > 0) then '待入库' when( v2.CheckNum = 0 and d1.BillType='Check_and_return') then '已验退' when(not d1.BillNo is null and d1.ApproveState = 0) then '入库中' when(not d1.BillNo is null and d1.ApproveState = 1 and v2.CheckNum > 0 and((d2.num - d2.remainnum) != 0 or(d2.num - d2.remainnum) is null)) then '已入库' when(not d1.BillNo is null and((d2.num - d2.remainnum) = 0 or(d2.num - d2.remainnum) is null)) then '待入库' else '其它' end 状态
from mms_receive (nolock)v1
inner join mms_receivedetail(nolock) v2 on v1.BillNo = v2.BillNo
left join mms_billsdetail(nolock) d2 on v2.BillNo = d2.SrcBillNo and v2.RowId = d2.SrcRowId and v2.MaterialCode = d2.MaterialCode
left join mms_bills(nolock) d1 on d2.BillID = d1.id
where v2.BillNo like '%'+ @billno +'%'
and v2.MaterialCode like '%'+ @MaterialCode +'%'
and v2.SrcBillNo like '%'+ @percharbill +'%'
--and( ( ( ((d2.num - d2.remainnum) != (case when exists(select * from mms_billsdetail where SrcBillNo = d2.SrcBillNo and RowID = d2.RowID and (num - remainnum) != 0)then 0 else 1 end)))) or (d1.ApproveState = 0 and((d2.num - d2.remainnum) != 0 or(d2.num - d2.remainnum) is null)) )
) h where 状态 like '%'+@bill_status+'%')
k where erq >= @start_seq AND erq <= @end_seq
end备份语句
备份语句
BACKUP DATABASE [Udit.Mms]
TO
DISK = N'D:\test\mms_bak\mms_bak.bak'
WITH
NAME = N'Udit.Mms - Backup',
NOFORMAT, NOINIT, SKIP,
STATS = 5
还原语句
RESTORE DATABASE [Udit.Mms_test]
FROM
DISK = N'D:\test\mms_bak\mms_bak.bak'
WITH
FILE = 1,
RECOVERY,
STATS = 5;标签取值
select
max(case item.CKEY when 'customer_material_no' then item.CVALUE else ''end ) 'customer_material_no'
,max(case item.CKEY when 'customer_material_name' then item.CVALUE else ''end ) 'customer_material_name'
from
[Udit.SFC].dbo.SYS_PRODUCT_BASEINFO_ITEMS(nolock) item
left join [Udit.SFC].dbo.SYS_PRODUCT_BASEINFO(nolock) s on item.PID=s.id
where
s.PRODUCT_ID =(
select PRODUCT_ID from qs_products(nolock) where
name=(select model_no from qs_serial_numbers(nolock) where serial_number='{serial}')
)
and item.CKEY in('customer_material_no','customer_material_name')
校验装箱时水排码顺序扫描（水冷行业定制校验）
ALTER PROCEDURE [dbo].[CHECK_PSN_IS_ORDER_SCAN]
@productId nvarchar(100),
@lotId nvarchar(100),
@stationId nvarchar(100),
@routeStepId nvarchar(100),
@userId nvarchar(100),
@boxId nvarchar(100),
@serialNumberID nvarchar(100),
@serialNumber VARCHAR(100)
AS
BEGIN
--insert into [Udit.sys].[dbo].[sys_public]([key],[val]) values('CHECK_PSN_IS_ORDER_SCAN',@lotId+@serialNumber)
declare @last_psn varchar(100)
declare @first_psn varchar(100)
DECLARE @PSN varchar(100)
declare @lot_name varchar(100)
declare @msg varchar(100)
declare @error varchar(100)
-- 找到该工单最后一个装箱的条码的客户码
-- 找到工单
select @lot_name=lot_name from qs_lots(nolock) where lot_id=@lotId
-- 找到客户水排码
select @PSN=ASSEMBLY_SN from QS_ASSEMBLY(nolock) where serial_Number=@serialNumber
--set @PSN=@serialNumber
-- 找到本工单未装箱的第一个水排码
select top 1 @first_psn=customer_sn from [udit.csr].dbo.AP_CUSTOMER_SN(NOLOCK) where po=@lot_name order by customer_sn asc
select top 1 @last_psn=ASSEMBLY_SN from QS_ASSEMBLY(nolock) bing
left join CT_BOX_ITEMS(nolock) item on item.SERIAL_NUMBER=bing.serial_number
where bing.lot_id=@lotId
and item.serial_number is null
order by ASSEMBLY_SN asc
if (@last_psn is null)
begin
if(@PSN=@first_psn)
begin
set @msg='验证通过！'
set @error=0
--SELECT 0 AS errorcode, '验证通过！' AS msg
end
else
begin
set @msg='请从第一个开始扫描'
set @error=1
--SELECT 1 AS errorcode, '请从第一个开始扫描！' AS msg
end
select @error AS errorcode ,@msg AS msg
end
else
begin
-- 找到下一个水排码，如果和扫描的不一致，则提示
if(@PSN=@last_psn)
begin
set @msg='验证通过！'
set @error=0
--SELECT 0 AS errorcode, '验证通过！' AS msg
end
else
begin
set @msg='未按客户水排码顺序扫描，请更换！应扫描 :'+convert(varchar(100),@last_psn)
set @error=1
--SELECT 1 AS errorcode, '未按客户水排码顺序扫描，请更换！应扫描 :'+convert(varchar(100),(@last_psn+1)) AS msg
end
select @error AS errorcode ,@msg AS msg
end
END扣料记录查询
select top 200 b.Identifier 条码,a.uid 物料,b.WorkOrder 工单,r.MATERIAL_NO 料号,t.PanelUse 用量
--,(CONVERT(decimal(18,3) ,b.Identifier)*t.PanelUse) 扣数
from ItemTraceData a
left join ItemTraceHistories b on a.guid=b.PCheckingProductUID
left join [Udit.Mms].dbo.mms_reels(nolock) r on a.uid=r.REEL_BARCODE
left join ItemTraceData(nolock) t on a.uid=t.uid
where 1=1
and b.WorkOrder='15111-2407001'
--and t.PanelUse>0
group by b.Identifier,a.uid ,b.WorkOrder ,r.MATERIAL_NO ,t.PanelUse
关联拼版历史条码查询扣数记录
select distinct top 200
(SELECT STUFF((
SELECT ',' + serial_number
FROM qs_serial_numbers(nolock)
where serial_number_id in(
select distinct ChildItemInventoryID From DW_PanelLinkHistories(nolock) where ParentItemInventoryID =(
select distinct ParentItemInventoryID From DW_PanelLinkHistories(nolock) where
ChildItemInventoryID=(select serial_number_id from qs_serial_numbers(nolock) where serial_number=b.Identifier)
)
)
FOR XML PATH('')
), 1, 1, '') AS ConcatenatedRow) 拼版关联条码,
b.Identifier 条码,a.uid 物料,b.WorkOrder 工单,r.MATERIAL_NO 料号,t.PanelUse 用量
--,(CONVERT(decimal(18,3) ,b.Identifier)*t.PanelUse) 扣数
from [Udit.CHC].dbo.ItemTraceData a
left join [Udit.CHC].dbo.ItemTraceHistories b on a.guid=b.PCheckingProductUID
left join [Udit.Mms].dbo.mms_reels(nolock) r on a.uid=r.REEL_BARCODE
left join [Udit.CHC].dbo.ItemTraceData(nolock) t on a.uid=t.uid
where 1=1
and b.Identifier='04060-02910000EK24482002042'
--and b.WorkOrder='15111-2407001'
--and t.PanelUse>0
group by b.Identifier,a.uid ,b.WorkOrder ,r.MATERIAL_NO ,t.PanelUse
结果示例
作者：王世杰 创建时间：2023-09-04 09:41
最后编辑：王世杰 更新时间：2025-02-11 14:25
最后编辑：王世杰 更新时间：2025-02-11 14:25
