U9C SQL笔记1

修改用户密码

update Base_User set Password=upper(substring(sys.fn_VarBinToHexStr(hashbytes('MD5', CAST('zgv9FQWbaNZ2iIhNej0+jA==' as varchar(50)) + convert(char(36),base_user.salt))),3,32)) 
where Code = 'EA'

库存可用量查询

SELECT StoreQty - ResvStQty - ResvOccupyStQty FROM InvTrans_WhQoh where ItemInfo_ItemID = 1002404260138956

添加参数设置

-- 基础设置模块
INSERT Base_Profile (ID,CreatedOn,CreatedBy,ModifiedOn,ModifiedBy,ProfileValueType,SubTypeName,DefaultValue,Application,ControlScope,SensitiveType,SysVersion,Code,ShortName,ReferenceID,Sort,Hidden,ShowPecent,IsSend,IsModify)VALUES(20231220,GETDATE(),'系统管理员',GETDATE(),'系统管理员',3,'bool','False',3000,1,4,0,'SH_CBSManage','CBS管理',NULL,NULL,0,0,1,1)
INSERT Base_Profile_Trl (ID,SysMLFlag,ProfileGroup,Name,Description)VALUES(20231220,'zh-CN','CBS管理','CBS管理','CBS管理')

添加定时调度

If Not Exists(SELECT TOP 1 FullName From UBF_JOB_NoParaRunableBPSVList WHERE FullName='UFIDA.U9.SH.ScheduledTasksBP.GetCCIDatas')
begin
  INSERT INTO UBF_JOB_NoParaRunableBPSVList(ID,FullName,Memo)
  VALUES(NEWID(),'UFIDA.U9.SH.ScheduledTasksBP.GetCCIDatas','费控发票数据抓取')
END