用友U9-SQL查询语句汇总

本文汇总了在用友U9系统中进行各种数据查询的SQL语句，包括组织、部门、业务员、存储地点、料品信息、供应商信息、客户信息、会计科目等多个方面的查询实例。这些查询涵盖了不同业务场景，例如获取有效组织、部门、业务员信息，查询存储地点及排除特定编码，获取料品的规格和名称，以及供应商的开户行信息等。

组织---
select * from (select a.ID,a.Code,b.Name from Base_Organization a
inner join Base_Organization_Trl b on a.ID=b.ID
where a.Code not in ('VPOrg','CPOrg',2001,2002) and a.Effective_IsEffective=1) a
---部门---
select * from (select a.ID,a.Code,b.Name from CBO_Department a
inner join CBO_Department_Trl b on a.ID=b.ID
where a.Org in (1001706090000058) and a.Effective_IsEffective=1) a
---业务员---
select * from (select a.ID,a.Code,b.Name from CBO_Operators a
inner join CBO_Operators_Trl b on a.ID=b.ID
where a.Effective_IsEffective=1 and a.Org in (1001706090000058) ) a
---存储地点----
--select * from CBO_Wh
select b.Code,a.Name,c.Name as Name1 from CBO_Wh_Trl a
inner join CBO_Wh b on a.ID=b.ID
inner join CBO_Operators_Trl c on b.Manager=c.ID
where b.Org=1001706090000058 and b.Effective_IsEffective=1
and b.Code not in ('K100101','K100102','K100103','K100201','K100104','K200801','K20601','K200700','K305002','K200802','K100202','K200400')
--料品信息-料号-名称-规格---------办公用品--------
select ID,Code,Name,SPECS from CBO_ItemMaster where Org=1001706090000058 and Effective_IsEffective=1 and Code like '9903%'
---资产卡片信息---
--select ID, * from FA_AssetCard_Trl
--select AssetCard ,* from FA_AssetTag

select a.ID,a.Code,a.Style,c.AssetName,d.CreateDate as 建卡日期,d.AccumulateUsedPeriods as 已使用年限月,
d.OriginalValue as 原值,d.NetValue as 净值,d.AccumulateDepreciation as 累积折旧 from FA_AssetTag a
inner join FA_AssetCard b on b.ID=a.AssetCard
inner join FA_AssetCard_Trl c on c.ID=b.ID
inner join FA_AssetCardAccountInformation d on b.DocNo=d.AssetCardCode

where b.Org=1001706090000058 and d.AssetCardCode='KP-2017040002' order by a.ID Desc
---供应商信息--开户行信息
--select TJOA,* from CBO_Supplier_Trl
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1,a.DescFlexField_PrivateDescSeg1,a.DescFlexField_PrivateDescSeg2 from CBO_Supplier a
full join CBO_Supplier_Trl b on a.ID=b.ID
full join CBO_BankAccount c on a.ID=c.Supplier
full join CBO_BankAccount_Trl d on c.ID=d.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1) c
---供应商信息--开户行信息---联系人---联系人电话--立账条件-到期日
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1,a.DescFlexField_PrivateDescSeg1,a.DescFlexField_PrivateDescSeg2,
e.Name as Name2,f.MaturityDateFirm_OffsetDays
from CBO_Supplier a
inner join CBO_Supplier_Trl b on a.ID=b.ID
inner join CBO_BankAccount c on a.ID=c.Supplier
inner join CBO_BankAccount_Trl d on c.ID=d.ID
inner join CBO_APConfirmTerm_Trl e on e.ID=a.APConfirmTerm--立账条件
inner join CBO_InstalmentTerm f on f.APConfirmTerm=e.ID--到期日
where a.Org=1001706090000058 and a.Effective_IsEffective=1) G

---供应商分类信息----
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1 from CBO_Supplier a
inner join CBO_Supplier_Trl b on a.ID=b.ID
inner join CBO_BankAccount c on a.ID=c.Supplier
inner join CBO_BankAccount_Trl d on c.ID=d.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1) c
---客户信息--银行--联系人---地址
select * from (select a.ID,a.Code,b.Name,c.Code as code1,d.Name as name1,a.DescFlexField_PrivateDescSeg1,a.DescFlexField_PrivateDescSeg2 from CBO_Customer a
inner join CBO_Customer_Trl b on a.ID=b.ID
inner join CBO_CustomerBankAccount c on a.ID=c.Customer
inner join CBO_CustomerBankAccount_Trl d on c.ID=d.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1) c
---物料信息--编码-料号-名称-MPN-规格-描述-单位---
select a.ID,a.Code,a.Name,a.DescFlexField_PrivateDescSeg1,a.SPECS,b.Name,c.Description as 描述 from CBO_ItemMaster a
inner join Base_UOM_Trl b on a.InventorySecondUOM=b.ID
inner join CBO_ItemMaster_Trl c on a.ID=c.ID
where Org=1001706090000058 and Effective_IsEffective=1 and a.Name not like '零星物料%'
---杂发单号---
select ID,DocNo from InvDoc_MiscShip where Org=1001706090000058
---会计科目---
select a.Code,b.Name from CBO_NaturalAccount_Trl b
inner join CBO_NaturalAccount a on a.ID=b.ID where a.IsLeaf=1 and b.Name like '%差旅费%'
---业务员+部门+组织---
--select Code,* from CBO_Operators
select * from (select a.ID,a.Code,b.Name,c.Code as Code1,d.Name as Name1,e.Name as Name2 from CBO_Operators a
inner join CBO_Operators_Trl b on a.ID=b.ID
inner join CBO_Department c on a.Dept=c.ID
inner join CBO_Department_Trl d on c.ID=d.ID
inner join Base_Organization_Trl e on a.Org=e.ID
where a.Org in (1001706090000058) and a.Effective_IsEffective=1 and c.Effective_IsEffective=1 and a.Code=00129) b
---项目---
select * from (select a.ID,a.Code,b.Name from CBO_Project a
inner join CBO_Project_Trl b on a.ID=b.ID
where a.Org in (1001706090000058) and a.Effective_IsEffective=1) a
---部门类别---
---select * from Base_ValueSetDef
---select * from Base_ValueSetDef_Trl
select * from (select A.[ID], A.[Code], A1.[Name] from Base_DefineValue as A
left join [Base_DefineValue_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID])
where (A.[ValueSetDef] = 1001706230006079)) a
-------部门+类别------------------------------------------------------------------------------------
select * from (select a.Code,b.Name,a.DescFlexField_PubDescSeg3,e.Name as Name1
from CBO_Department a inner join CBO_Department_Trl b on a.ID=b.ID
full join Base_DefineValue c on a.DescFlexField_PubDescSeg3=c.Code
full join Base_ValueSetDef d on c.ValueSetDef=d.ID
full join Base_DefineValue_trl e on c.ID=e.ID
where a.Effective_IsEffective=1 and a.Org=1001706090000058 and d.Code='bmlb') k
--------------------------------------------------------------------------------------
---
----2018年9月6日18:04:16
--------------------------------------------------------------------------------------
----------杂发单-单号-成本数量-单价-成本------
select top 100 * from InvDoc_MiscShip where DocNo='Mis1808160053'
select top 100 * from InvDoc_MiscShipL where MiscShip=1001808160079356

select a.DocNo as 杂发单号,b.ItemInfo_ItemCode as 料号,b.ItemInfo_ItemName as 物料名称,b.CostUOMQty as 成本数量,b.CostPrice as 单价,b.CostMny as 成本 from InvDoc_MiscShip a
inner join InvDoc_MiscShipL b on a.ID=b.MiscShip
------------------------------------------------------
-----价目表-取料品单价-有效--------
select * from PPR_PurPriceList where Code='PPL2017100013'
select * from PPR_PurPriceLine

select * from (select b.ItemInfo_ItemCode as 料号,b.ItemInfo_ItemName as 物料名称,c.DescFlexField_PrivateDescSeg1 as MPN,Round(b.Price,4) as 单价,b.FromDate as 开始时间,b.ToDate as 结束时间 from PPR_PurPriceList a
inner join PPR_PurPriceLine b on a.ID=b.PurPriceList
inner join CBO_ItemMaster c on b.ItemInfo_ItemCode=c.Code
where b.ToDate > GETDATE() and a.Cancel_Canceled=0 and a.Status=2 ) f
----结束时间大于当前日期，终止状态为否，审核状态为已审核--

-----------------料品--价目表---=-------
select * from (select a.ID,a.Code,a.DescFlexField_PrivateDescSeg1 as MPN,a.Name,b.Name as Name1,c.Price,c.FromDate from CBO_ItemMaster a
inner join Base_UOM_Trl b on a.InventorySecondUOM=b.ID
inner join PPR_PurPriceLine c on a.Code=c.ItemInfo_ItemCode
inner join PPR_PurPriceList d on d.ID=c.PurPriceList
where a.Org=1001706090000058 and Effective_IsEffective=1 and a.Name not like '零星物料%' and c.ToDate>GETDATE() and d.Cancel_Canceled=0 and d.Status=2 ) c order by FromDate desc
-----------------------------------------------------


--------资产位置--------------------------------------
select a.Code,b.Name from FA_Location a
inner join FA_Location_Trl b on a.ID=b.ID
where a.Org=1001706090000058 and a.Effective_IsEffective=1

---------期间物料成本数据------------
select a.ID,a.ElementTypeCost_MaterialCurrentCost as Price,b.Code,b.Name,b.DescFlexField_PrivateDescSeg1 as MPN,c.DisplayName from IC_ItemCost a
full join CBO_ItemMaster b on a.ItemMaster=b.ID
full join Base_SOBAccountingPeriod c on a.SOBPeriod=c.ID
where a.Org=1001706090000058
and c.Year='2018'
and c.DisplayName='2018-10'

2022年10月26日15:30:06
---------------------------------------------------------------------
----------取料品档案的采购业务员------------------------------
select a.code,c.code,d.name from CBO_ItemMaster a
inner join CBO_PurchaseInfo b on a.id=b.ItemMaster
inner join CBO_Operators c on b.Buyer=c.id
inner join CBO_Operators_Trl d on c.id=d.id
where a.code='LCZ-0000376'

------取料品的最新期间成本--------------

select top 1 (a.ElementTypeCost_MaterialCurrentCost+a.ElementTypeCost_SubcontractCurrentCost+a.ElementTypeCost_MaterialPriorCost) as cost
from IC_ItemCost a
inner join CBO_ItemMaster b on a.Itemmaster=b.id
where b.code='料号'
order by a.CreatedOn desc

-------------取料品扩展字段的值集值---------

select b.Code,c.Name,* from CBO_ItemMaster a
left join Base_DefineValue b on b.Code= a.DescFlexField_PrivateDescSeg2
left join Base_DefineValue_Trl c on c.ID = b.ID and c.SysMlFlag = 'zh-CN'
left join Base_ValueSetDef d on d.ID = b.ValueSetDef
where a.Code ='料号' and d.Code ='值集编码'

select * from PM_RcvLine
select * from PM_Receivement
select * from PM_PurchaseOrder
select * from PM_POLine
select * from PM_POShipLine -- 采购计划行

--- 收货单列表数据包含采购交期-----------------
select
a.CreatedOn as 创建时间,
b.DocNo as 收货单号,
b.Supplier_Code as 供应商编号,
d.Name as 供应商名称,
a.DocLineNo as 收货单行号,
a.ItemInfo_ItemCode as 料号,
a.ItemInfo_ItemName as 物料名称,
c.Name as 仓库,
a.RcvQtyCU as 入库数量,
a.ConfirmDate as 入库确认日期,
a.SrcPO_SrcDocNo as 采购订单号,
a.SrcPO_SrcDocLineNo as 采购订单行号,
g.DeliveryDate as 要求交货日期,
DATEDIFF(DAY,a.ConfirmDate,g.DeliveryDate) as 延期天数,
a.Status as 状态
from PM_RcvLine a
inner join PM_Receivement b on a.Receivement=b.ID
inner join CBO_Wh_Trl c on a.Wh=c.ID
inner join CBO_Supplier_Trl d on b.Supplier_Supplier=d.ID
inner join PM_PurchaseOrder e on a.SrcPO_SrcDocNo=e.DocNo
inner join PM_POLine f on e.ID=f.PurchaseOrder
inner join PM_POShipLine g on f.id=g.POLine
where a.Status=5 and a.SrcDocType=1

--------BOM版本为空时弃审BOM报空引用的处理方法--------

1.在数据库中，查询该BOM的ID，
select b.id BOM的ID,b.BOMVersion BOM版本的ID from CBO_BOMMaster b
left join CBO_ItemMaster i on i.id=b.ItemMaster
left join CBO_BOMVersion v on b.BOMVersion=v.ID
where i.Code='BOM母项料号'

2.在BOM版本表中根据版本ID查询版本信息
select * from CBO_BOMVersion where id ='BOM版本的ID'

3.若第二步中，没有查询到任何BOM信息，则执行以下sql
select BOMVersion,status,* from CBO_BOMMaster where ID='BOM的ID'
update CBO_BOMMaster set BOMVersion=null,status=0 where ID='BOM的ID'

----操作日志查询--------------

SELECT distinct A3.[Name] as [LoginUser_Name], A.[OccurrenceTime] as [OccurrenceTime], A8.DisplayName, A7.[ControlName] as [ControlName],A.description, A.[IsSuccess] as [IsSuccess], A.[ErrorMessage] as [ErrorMessage], A1.[Name] as [Menu_Name],A.[MachineIP] as [MachineIP], A.[SessionID] as [SessionID],A.[ActionType] as [ActionType]
,A.* FROM UBF_Log_OperateLog as A left join UBF_Assemble_Menu as A1 on (A1.[Code] = A.[Menu])
left join [Base_User] as A3 on (A.[LoginUser] = A3.[ID])
left join [Base_Organization_Trl] as A5 on (A5.SysMlFlag = 'zh-CN')
inner join [UBF_Log_OperateLog_Trl] as A7 on (A7.SysMlFlag = 'zh-CN')
and (A.[ID] = A7.[ID])
inner join UBF_MD_UIForm as A9 on A9.UID=A.Form
inner join [UBF_MD_UIForm_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and A9.ID

=A8.id

WHERE A.[OccurrenceTime] >='2019-08-01 14:19:35.000' and A.[OccurrenceTime] <='2019-09-19 13:41:33.000'
and
ControlName!='登录' --and a3.Name ='周晶'
and A7.[ControlName] not in ('进入菜单','隐藏控件取消')
and A.description like '%采购订单%'
and A.Org='1001712110005500'
order by A.[OccurrenceTime]

---查看流程相关信息-------
select top 1000 prodessdef.Title
, task.Title
, applicant.Name as Applicant
, process.StartTime
, task.DoTime
, u.Name as Operator
,case task.Operation when 1 then '挂起' when 2 then '重启 ' when 3 then '操作' when 4 then '提交' when 5 then '转发' when 6 then '退回' end as OperationResult
, task.Remark
, task.*
from CS_Workflow_FlowInstance process
left join CS_Workflow_ProcessTrackLog task on task.FlowInstance = process.ID
--left join CS_Workflow_ProcessTrack track on track.ID = task.ProcessTrack
left join Base_User u on u.ID = task.OperateUser
left join Base_User applicant on applicant.ID = process.StartUser
left join CS_Workflow_WorkflowDefine_Trl prodessdef on prodessdef.ID = process.WorkflowDefine
--where process.SourceOrg=1001611111213
--order by process.ID, task.ID

--去掉采购订单价格含税的勾
update a set IsPriceIncludeTax=b.IsIncludeTax
from PM_PurchaseOrder a,PPR_PurPriceList b
where b.ID=a.PriceList
and b.IsIncludeTax <> a.IsPriceIncludeTax
and a.DocNo='PO01712030062'

-------查询bom版本表=空的错误.txt-----
select b.Code,a.bomversion,* from CBO_BOMMaster a inner join cbo_itemmaster b on a.itemmaster=b.id
left join cbo_bomversion c on a.bomversion=c.id
where a.bomversion>0 and c.id is null

---处理委外订单已收货完成，新增备料，无法发料异常----
select pl.DocLineNo, po.Status,pl.Status,po.IsFIClose,pl.IsFIClose,pl.IsBizClosed,
* from PM_PurchaseOrder po join PM_Poline pl on po.id=pl.purchaseorder where docno='WPO1807030001'-- WPO1810250003

update pl set pl.IsFIClose=pl.IsBizClosed
from PM_PurchaseOrder po join PM_Poline pl on po.id=pl.purchaseorder where docno='WPO1807030001' and DocLineNo=70

--update po set po.IsFIClose=0
--from PM_PurchaseOrder po join PM_Poline pl on po.id=pl.purchaseorder where docno='WPO1807030001' and DocLineNo=70
--update pl set pl.Status=2
--from PM_PurchaseOrder po join PM_Poline pl on po.id=pl.purchaseorder where docno='WPO1807030001' and DocLineNo=70

更新不符合plmid规则和为空的数据.sql

--更新BOM母项PLMID
UPDATE a SET a.PLMID=b.Code+'###'+a.BOMVersionCode FROM dbo.CBO_BOMMaster a INNER JOIN dbo.CBO_ItemMaster b ON a.ItemMaster=b.ID
WHERE a.PLMID IS NULL OR a.PLMID='' OR (a.PLMID IS NOT NULL AND a.PLMID<>(b.Code+'###'+a.BOMVersionCode));
--更新BOM子项PLMID
UPDATE a SET a.PLMID=d.Code+'###'+b.BOMVersionCode+'###'+c.Code
FROM dbo.CBO_BOMComponent a INNER JOIN dbo.CBO_BOMMaster b ON a.BOMMaster=b.ID
LEFT JOIN dbo.CBO_ItemMaster c ON a.ItemMaster=c.ID
LEFT JOIN dbo.CBO_ItemMaster d ON b.ItemMaster=d.ID
WHERE a.PLMID IS NULL OR a.PLMID='' OR (a.PLMID IS NOT NULL AND a.PLMID<>(d.Code+'###'+b.BOMVersionCode+'###'+c.Code));
--更新工艺路线PLMID
UPDATE a SET a.PLMID=b.Code+'###'+a.RoutingVersionCode
FROM dbo.CBO_Routing a INNER JOIN dbo.CBO_ItemMaster b ON a.ItemMaster=b.ID
WHERE a.PLMID IS NULL OR a.PLMID='' OR (a.PLMID IS NOT NULL AND a.PLMID<>(b.Code+'###'+a.RoutingVersionCode));
--更新工艺路线工序PLMID
UPDATE a SET a.PLMID=c.Code+'###'+b.RoutingVersionCode+'###'+a.Sequence
FROM dbo.CBO_Operation a INNER JOIN dbo.CBO_Routing b ON a.Routing=b.ID
LEFT JOIN dbo.CBO_ItemMaster c ON b.ItemMaster=c.ID
WHERE a.PLMID IS NULL OR a.PLMID='' OR (a.PLMID IS NOT NULL AND a.PLMID<>(c.Code+'###'+b.RoutingVersionCode+'###'+a.Sequence));
--更新工艺路线工序资源PLMID
UPDATE a SET a.PLMID=d.Code+'###'+c.RoutingVersionCode+'###'+b.Sequence+'###'+e.Code
FROM dbo.CBO_OpResource a INNER JOIN dbo.CBO_Operation b ON a.Operation=b.ID
INNER JOIN dbo.CBO_Routing c ON b.Routing=c.ID
LEFT JOIN dbo.CBO_ItemMaster d ON c.ItemMaster=d.ID
LEFT JOIN dbo.CBO_Resource e ON a.Resource=e.ID
WHERE a.PLMID IS NULL OR a.PLMID='' OR (a.PLMID IS NOT NULL AND a.PLMID<>(d.Code+'###'+c.RoutingVersionCode+'###'+b.Sequence+'###'+e.Code));

-----itemmaster
UPDATE CBO_ItemMaster
SET PLMID = Code
WHERE PLMID IS NULL OR PLMID = ''

-----去掉收货单价格含税的勾------

update a set IsPriceIncludeTax=0
from PM_Receivement a,PM_RcvLine b
where b.Receivement=b.ID
and a.DocNo=''

update b set IsPriceIncludeTax=0
from PM_Receivement a,PM_RcvLine b
where b.Receivement=b.ID
and a.DocNo=''

---------收货单添加价格含税的勾-------
update a set IsPriceIncludeTax=0
from PM_Receivement a,PM_RcvLine b
where b.Receivement=b.ID
and a.DocNo=''

update b set IsPriceIncludeTax=0
from PM_Receivement a,PM_RcvLine b
where b.Receivement=b.ID
and a.DocNo=''

---调入单信息---
select * from InvDoc_TransferIn where DocNo='Tra2018120002'
select DocLineNo,TransferIn,* from InvDoc_TransInLine

select * from Base_Organization_Trl
--料品信息-料号-名称-规格---
select ID,Code,Name,SPECS from CBO_ItemMaster where Org=1001706090000058 and Effective_IsEffective=1 and Code like '9903%'
---------------------------------------------------------------------------------
select a.DocNo,b.DocLineNo,b.ItcemInfo_ItemCode,b.ItemInfo_ItemName,c.SPECS,c.DescFlexField_PrivateDescSeg1 from InvDoc_TransferIn a
inner join InvDoc_TransInLine b on a.ID=b.TransferIn
inner join CBO_ItemMaster c on b.ItemInfo_ItemCode=c.Code
where a.Org=1001706090000058
1001712110005500
---调入单信息---
select b.CreatedOn as 制单时间,b.BusinessDate as 业务日期,b.docno as 调入单号,a.DocLineNo as 调入行号,c.DocLineNo as 调出行号,a.ItemInfo_ItemCode as 料号,a.ItemInfo_ItemName as 品名,d.SPECS as 规格,
d.DescFlexField_PrivateDescSeg1 as MPN,c.LotInfo_LotCode as 批号,c.StoreUOMQty as 调入数量,a.StoreUOMQty as 调出数量
From InvDoc_TransInLine a
inner join InvDoc_TransferIn b on a.TransferIn=b.ID
inner join InvDoc_TransInSubLine c on a.ID=c.TransInLine
inner join CBO_ItemMaster d on a.iteminfo_itemid=d.id
where a.Org=1001706090000058

--解决委外不能收货问题---
Exec PM_P_PO_RebuildRcvUpdateInfo '委外单号','行号','计划行ID'

--解决外协订单无法拆行--原因:手工修改了实际需求量，但系统只取系统默认的----
declare @poDocNo varchar(200)
declare @polineNo varchar(100)
declare @pickLineNo varchar(100)
declare @itemCode varchar(100)

set @poDocNo='WPO11804020004' --订单号
set @polineNo='20' --订单行号
set @itemCode='10040300003' --备料料号
set @pickLineNo='230' --备料行号

select p.PickLineNo, p.ItemInfo_ItemCode,p.IsAutoCreate,p.BOMComponent from CBO_SCMPickHead h join CBO_SCMPickList p on h.id=p.PicKHead
where h.poline in (select pl.id from PM_poline pl join PM_purchaseorder po on pl.purchaseorder=po.id
where po.docno=@poDocNo and pl.DocLineNo=@polineNo) and p.ItemInfo_ItemCode=@itemCode and p.PickLineNo=@pickLineNo

update p
set p.IsAutoCreate=0,p.BOMComponent=null
from CBO_SCMPickHead h join CBO_SCMPickList p on h.id=p.PicKHead
where h.poline in (select pl.id from PM_poline pl join PM_purchaseorder po on pl.purchaseorder=po.id
where po.docno=@poDocNo and pl.DocLineNo=@polineNo) and p.ItemInfo_ItemCode=@itemCode and p.PickLineNo=@pickLineNo