表說明
GL_Voucher? ?憑證
Base_Organization瞻润、Base_Organization_Trl? ?組織
CBO_ItemMaster滑频、CBO_ItemMaster_Trl ?料品
CBO_InventoryInfo? ?料品庫存相關(guān)
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? 業(yè)務(wù)員
PM_PurchaseOrder? ?采購訂單
MO_PullList??倒扣料補貨單/備料工作臺
MO_MO??生產(chǎn)訂單
MO_MOOutput? 生產(chǎn)訂單產(chǎn)出
InvDoc_TransferIn? 調(diào)入單
InvDoc_TransferOut? 調(diào)出單
MO_MOPeriodQtyDetail? 生產(chǎn)訂單期間數(shù)量明細(xì)（顯示主產(chǎn)品聯(lián)產(chǎn)品）
MO_MOPeriodQtyTotal? ? 生產(chǎn)訂單期間數(shù)量匯總（只顯示主產(chǎn)品）
MRP_PlanOrder? 計劃訂單
CBO_Project? 項目
InvTrans_WhQoh? 庫存在手量
CBO_BOMMaster? BOM母項
CBO_BOMComponent? BOM子項
CBO_NaturalAccount? 標(biāo)準(zhǔn)科目
CBO_NaturalAccountSet? 標(biāo)準(zhǔn)科目表
Base_Application? 模塊列表
CBO_Category? 料品分類
CBO_CategoryType? 分類體系
CBO_CostField? 成本域
CBO_CostType? 成本類型
CBO_ItemCostType? 料品成本類型
CBO_OrgBusinessRelation? 組織業(yè)務(wù)關(guān)系
Base_SetofBooks? 賬簿
CBO_SOBSet? 總賬賬簿
Base_AccountingPeriod? ?會計期間
Complete_RcvRptDoc? 入庫單
Complete_RcvRptDocLine? 入庫單行
PM_Receivement? 標(biāo)準(zhǔn)收貨
PM_RcvLine? 收貨行
CBO_TradePath? 貿(mào)易路徑
AR_ARBillHead? 應(yīng)收單頭（IsPeriodBegin=1為期初）
AR_ARBillLine? 應(yīng)收單行 （IsPeriodBegin=1為期初）
AP_APBillHead? 應(yīng)付單頭（IsPeriodBegin=1為期初）
AP_APBillLine? ?應(yīng)付單行?（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'
根據(jù)銷售訂單號查詢業(yè)務(wù)員及部門
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'
刪除調(diào)入單
delete from InvDoc_TransferIn where DocNo='Tra2022060001'
查詢生產(chǎn)訂單相關(guān)數(shù)量
select DocNo,b.Code,b.name,ProductQty 生產(chǎn)數(shù)量,TotalStartQty 開工數(shù)量,TotalCompleteQty 完工數(shù)量,TotalEligibleQty 合格數(shù)量,TotalRcvQty 入庫數(shù)量,TotalReworkingQty 返工數(shù)量,TotalScrapQty 報廢數(shù)量
from MO_MO a join CBO_ItemMaster b on a.ItemMaster=b.id
where DocNo='30MO2022-00066'
查詢聯(lián)產(chǎn)品相關(guān)數(shù)量
select c.DocNo,b.code,b.Name,PlanOutputQty 預(yù)計產(chǎn)出數(shù)量,ActualCompleteQty 完工數(shù)量,ActualRcvQty 入庫數(shù)量
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'
查詢產(chǎn)量信息
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
關(guān)系企業(yè)拋轉(zhuǎn)查詢 根據(jù)采購訂單號查銷售訂單號、貿(mào)易路徑
select b.code 貿(mào)易路徑編碼,DocNo 銷售訂單號,CustomerPONo 采購訂單號 from SM_SO a join CBO_TradePath b on TradePath=b.id where CustomerPONo='30PO220905002'--id 1002210270018594
查詢采購訂單拋轉(zhuǎn)狀態(tài)
select DocNo,CooperateType from PM_PurchaseOrder where DocNo='30PO220905002'?
--CooperateType? 已拋轉(zhuǎn)0 未拋轉(zhuǎn)-1?
