U9C SQL

1、枚举值查询

select h.UID,g.EValue ,g.Code,i.Name  from   UBF_Sys_ExtEnumValue as g
inner join UBF_Sys_ExtEnumType as h on g.ExtEnumType =h.ID
inner join  UBF_Sys_ExtEnumValue_Trl as i on g.ID =i.ID
where h.Code ='DocType'
--DocType 为枚举值类型 如：UFIDA.U9.InvDoc.TransferOut.SrcDocTypeForTransOutEnum

2、公共扩展段查询

select Base_DefineValue.Code,Name
from Base_DefineValue_Trl
inner join Base_DefineValue on Base_DefineValue.ID=Base_DefineValue_Trl.ID
inner join Base_ValueSetDef on Base_ValueSetDef.ID=ValueSetDef
where Base_ValueSetDef.Code='XXX'   ---XXX是扩展字段值集的编码

3. 查询凭证

select b.Name,DocNo,VoucherStatus,CreatedBy,Poster,PostDate from GL_Voucher a join Base_Organization_Trl b on a.Org=b.ID 
where b.Name like '%印尼%'

4. 查询料品仓库

select a.id,a.code,a.name,C.Code,D.Name from
((CBO_ItemMaster as a inner join CBO_InventoryInfo as b on a.id=b.ItemMaster) 
inner join CBO_Wh as c on b.Warehouse=c.ID)
inner join  CBO_Wh_Trl as d on C.ID=d.ID
where a.Code='801001'

5. 更新料品档案的计量单位

--查询计量单位的ID
select a.ID,a.code,b.Name from Base_UOM as a inner join Base_UOM_Trl as b on a.id=b.id where b.Name like '%pcs%'
--修改料号2110100001的计量单位为pcs
update CBO_ItemMaster set InventoryUOM = '1002111212534667',InventorySecondUOM = '1002111212534667',SalesUOM = '1002111212534667',PurchaseUOM = '1002111212534667',PriceUOM = '1002111212534667',ManufactureUOM = '1002111212534667',MaterialOutUOM = '1002111212534667',CostUOM = '1002111212534667',WeightUom= '1002111212534667',BulkUom = '1002111212534667'where code='2110100001'
--删除多余的计量单位
delete from Base_UOM where Code ='cs01'
delete a from Base_UOM as a inner join Base_UOM_Trl as b on a.code ='cs01' 

6. 查询值集

值集类别

select code,name from Base_ValueSetDef a join Base_ValueSetDef_Trl b on a.id=b.id

 　　

select a.ID,A.Code CategoryCode,B.Name CategoryName,c.code,d.name,e.Code ParentCode,c.ID,c.ParentNode from
((Base_ValueSetDef as A inner join Base_ValueSetDef_Trl as B on B.SysMlFlag='zh-CN' and A.ID = B.ID)
inner join Base_DefineValue as c on b.id=c.ValueSetDef)
inner join Base_DefineValue_Trl as d on c.id=d.id and d.SysMlFlag='zh-CN'
left join Base_DefineValue e on e.ID=c.ParentNode
where A.code='Z001'

 

枚举、下拉列表，弹性域的定义

select * from Base_ValueSetDef

 

7. 根据销售订单号查询业务员及部门

select a.DocNo,b.code,c.name,d.Code,e.Name,* from
(((SM_SO as a JOIN CBO_Department as b on a.SaleDepartment=b.ID)
join CBO_Department_Trl as c on b.id=c.id )
join CBO_Operators as d on a.Seller=d.ID)
join CBO_Operators_Trl as e on d.id=e.id
where a.DocNo='30SO220505007' and c.SysMLFlag='zh-cn' and e.SysMLFlag='zh-cn'

8. 删除备料工作台单子　　

delete from MO_PullList where DocNo='PLT-0003'

9. 删除调入单

delete from InvDoc_TransferIn where DocNo='Tra2022060001'

10. 查询生产订单相关数量

select DocNo,b.Code,b.name,ProductQty 生产数量,TotalStartQty 开工数量,TotalCompleteQty 完工数量,TotalEligibleQty 合格数量,TotalRcvQty 入库数量,TotalReworkingQty 返工数量,TotalScrapQty 报废数量
from MO_MO a join CBO_ItemMaster b on a.ItemMaster=b.id
where DocNo='30MO2022-00066'

11. 查询联产品相关数量

select c.DocNo,b.code,b.Name,PlanOutputQty 预计产出数量,ActualCompleteQty 完工数量,ActualRcvQty 入库数量
from (mo_mooutput a join CBO_ItemMaster b on a.Item=b.ID) join MO_MO c on a.MO=c.ID
where OutputType=2 and c.DocNo='30MO2022-00066'

12. 查询产量信息

declare @mo bigint
select @mo=id from mo_mo where docno='LY2111133-ZT－FG01'
select TotalCompleteQty,TotalEligibleQty,TotalRcvQty,TotalEligibleRcvQty from mo_mo where id=@mo
select ID,ActualCompleteQty,ActualEligibleQty,ActualEligibleRcvingQty,ActualEligibleRcvedQty from mo_mooutput where mo=@mo
select id,CompleteQty,CompleteQtyCostUOM,EligibleQty,EligibleQtyCostUOM,RcvQty,RcvQtyUOM from MO_MOPeriodQtyTotal where mo=@mo
select id,CompleteQty,CompleteQtyCostUOM,EligibleQty,EligibleQtyCostUOM,RcvQty,RcvQtyCostUOM from MO_MOPeriodQtyDetail where mo=@mo

13. 关系企业抛转查询 根据采购订单号查销售订单号、贸易路径

select b.code 贸易路径编码,DocNo 销售订单号,CustomerPONo 采购订单号 from SM_SO a join CBO_TradePath b on TradePath=b.id 
where CustomerPONo='30PO220905002'--id 1002210270018594

14. 查询采购订单抛转状态

--CooperateType  已抛转0 未抛转-1 
select DocNo,CooperateType from PM_PurchaseOrder where DocNo='30PO220905002'