U9C SQL

1、枚举值查询

?

1 2 3 4 5

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、公共扩展段查询

?

1 2 3 4 5

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. 查询凭证

?

1 2	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. 查询料品仓库

?

1 2 3 4 5

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. 更新料品档案的计量单位

?

1 2 3 4 5 6 7

--查询计量单位的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. 查询值集

值集类别

?

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

 　　

?

1 2 3 4 5 6

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'

 

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

?

1	select * from Base_ValueSetDef

 

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

?

1 2 3 4 5 6

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. 删除备料工作台单子　　

?

1	delete from MO_PullList where DocNo='PLT-0003'

9. 删除调入单

delete from InvDoc_TransferIn where DocNo='Tra2022060001'

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

?

1 2 3

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. 查询联产品相关数量

?

1 2 3

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. 查询产量信息

?

1 2 3 4 5 6

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. 关系企业抛转查询 根据采购订单号查销售订单号、贸易路径

?

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

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

?

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