表说明
GL_Voucher 凭证
Base_Organization、Base_Organization_Trl 组织
CBO_ItemMaster、CBO_ItemMaster_Trl 料品
CBO_InventoryInfo 料品库存相关
CBO_Wh、CBO_Wh_Trl 存储地点
Base_UOM、Base_UOM_Trl 计量单位
Base_ValueSetDef、Base_ValueSetDef_Trl 值集
Base_DefineValue、Base_DefineValue_Trl 值集值
SM_SO 销售订单
CBO_Department_Trl、CBO_Department 部门
CBO_Operators_Trl、CBO_Operators 业务员
PM_PurchaseOrder 采购订单
MO_PullList 倒扣料补货单/备料工作台
MO_MO 生产订单
MO_MOOutput 生产订单产出
InvDoc_TransferIn 调入单
InvDoc_TransferOut 调出单
MO_MOPeriodQtyDetail 生产订单期间数量明细（显示主产品联产品）
MO_MOPeriodQtyTotal 生产订单期间数量汇总（只显示主产品）
MRP_PlanOrder 计划订单
CBO_Project 项目
InvTrans_WhQoh 库存在手量
CBO_BOMMaster BOM母项
CBO_BOMComponent BOM子项
CBO_NaturalAccount 标准科目
CBO_NaturalAccountSet 标准科目表
Base_Application 模块列表
CBO_Category 料品分类
CBO_CategoryType 分类体系
CBO_CostField 成本域
CBO_CostType 成本类型
CBO_ItemCostType 料品成本类型
CBO_OrgBusinessRelation 组织业务关系
Base_SetofBooks 账簿
CBO_SOBSet 总账账簿
Base_AccountingPeriod 会计期间
Complete_RcvRptDoc 入库单
Complete_RcvRptDocLine 入库单行
PM_Receivement 标准收货
PM_RcvLine 收货行
CBO_TradePath 贸易路径
AR_ARBillHead 应收单头（IsPeriodBegin=1为期初）
AR_ARBillLine 应收单行 （IsPeriodBegin=1为期初）
AP_APBillHead 应付单头（IsPeriodBegin=1为期初）
AP_APBillLine 应付单行 （IsPeriodBegin=1为期初）
查询凭证
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 '%印尼%'
查询料品仓库
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'
更新料品档案的计量单位
--查询计量单位的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'
查询值集
select A.ID,A.Code,B.Name,c.code,d.name 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'
where A.code='Z026'
根据销售订单号查询业务员及部门
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'
删除备料工作台单子
delete from MO_PullList where DocNo='PLT-0003'
删除调入单
delete from InvDoc_TransferIn where DocNo='Tra2022060001'
查询生产订单相关数量
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'
查询联产品相关数量
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'
查询产量信息
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
关系企业抛转查询 根据采购订单号查销售订单号、贸易路径
select b.code 贸易路径编码,DocNo 销售订单号,CustomerPONo 采购订单号 from SM_SO a join CBO_TradePath b on TradePath=b.id where CustomerPONo='30PO220905002'--id 1002210270018594
查询采购订单抛转状态
select DocNo,CooperateType from PM_PurchaseOrder where DocNo='30PO220905002'
--CooperateType 已抛转0 未抛转-1
