前言
客户升级ERP 从U8升级到U9C,回写部分采用OpenApi，数据对接采用SQL直接对接数据库同步，网上资料较少，这里做下记录给有需要的人
数据库对接
公共说明
U9Cloud的表分 一个主表和一个 扩展表(我理解的) 例如 料品分类：CBO_Category 和 CBO_Category_Trl
注意点：
- U9Cloud多组织功能对接数据需要区分组织
基础信息关键字段
- Code：编码字段
- Name：名称字段(一般存在Trl表里面的)
- Org：组织ID字段
- Effective_IsEffective：状态 有效性1=正常，0=失效
单据关键字段
- DocNo：单号
Status: 0=开立，1=审核中，2=已核准 (采购)(经验证不同单据字段名一样Value不一样)IsBizClosed：业务关闭 0=否，1=是- IsFIClose：财务关闭 0=否，1=是
- IsHolded：是否挂起 0=否，1=是
- 关于单位：PU/计价单位 TU
注：有点离谱每个表字段字典值含义不一样
通用字段
- ID：主键ID
- CreatedOn：创建时间
- CreatedBy：创建人
- ModifiedOn：修改时间
- ModifiedBy：修改人
对接SQL
常用表说明
- 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为期初）
基础信息
注意下面的 SQL有组织的都关联了
料品分类
SELECT
a.Code,
b.Name
FROM
CBO_Category a
INNER JOIN CBO_Category_Trl b ON a.ID= b.ID
INNER JOIN Base_Organization c ON c.ID= a.Org
WHERE
c.Code = '组织编码'
客户信息
SELECT a.Code,
b.Name,
CASE
WHEN a.Effective_IsEffective = 1 THEN
0
ELSE 1
END AS IsEffective
FROM CBO_Customer a
INNER JOIN CBO_Customer_Trl b ON a.ID = b.ID
INNER JOIN Base_Organization c ON c.ID = a.Org
WHERE c.Code = '组织编码'
部门信息
SELECT
a.Code,
b.Name,
d.Code AS ParentNodeCode
FROM
CBO_Department a
INNER JOIN CBO_Department_Trl b ON a.ID= b.ID
INNER JOIN Base_Organization c ON c.ID= a.Org
LEFT JOIN CBO_Department AS d ON d.ID = a.ParentNode
WHERE
a.Effective_IsEffective= 1
AND c.Code = '组织编码'
料品信息
SELECT
a.Code,
a.Name,
a.SPECS,
b.Name AS UnitName,
b1.Code AS UnitCode,
e.Code AS CategoryCode
FROM
CBO_ItemMaster a
INNER JOIN Base_UOM_Trl b ON a.InventorySecondUOM= b.ID
INNER JOIN Base_UOM b1 ON a.InventorySecondUOM= b1.ID
INNER JOIN CBO_ItemMaster_Trl c ON a.ID= c.ID
INNER JOIN Base_Organization d ON d.ID= a.Org
INNER JOIN CBO_Category e ON a.MainItemCategory= e.ID
WHERE
a.Effective_IsEffective = 1
AND d.Code= '组织'
单位信息
SELECT
a.Code,
b.Name,
CASE
WHEN Effective_IsEffective = 1 THEN
0 ELSE 1
END AS IsEffective
FROM
Base_UOM a
INNER JOIN Base_UOM_Trl b ON a.ID = b.ID;
用户信息
SELECT
a.Code,
a.Name,
CASE
WHEN a.Effective_IsEffective = 1 THEN
0 ELSE 1
END AS IsEffective
FROM
Base_User a
INNER JOIN Base_User_Trl b ON a.ID= b.ID
WHERE
Code NOT LIKE '%admin%'
AND Code NOT LIKE '%ea%'
供应商信息
SELECT
a.Code,
b.Name,
CASE
WHEN a.Effective_IsEffective = 1 THEN
0 ELSE 1
END AS IsEffective
FROM
CBO_Supplier a
INNER JOIN CBO_Supplier_Trl b ON a.ID= b.ID
INNER JOIN Base_Organization c ON c.ID= a.Org
WHERE
c.Code = '组织编码'
存储地点(仓库)
SELECT
b.Code,
a.Name,
c.Name AS UserName
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
INNER JOIN Base_Organization d ON d.ID= b.Org
WHERE
d.Code= '组织编码'
AND b.Effective_IsEffective= 1
工艺路线
SELECT A1.ID AS ID,
A.ID AS MainID,
A9.Description AS Description,
A2.Code AS ItemMaster_Code,
A.AlternateType AS AlternateType,
A.EffectiveDate AS EffectiveDate,
A.DisableDate AS DisableDate,
A.Status AS Status,
A1.Sequence AS Operations_Sequence,
row_number() over ( ORDER BY A1.Sequence ) AS Operations_SeqNum,
A7.Code AS Operations_StdOperation_Code,
A8.Description AS Operations_Description,
A1.IsSubcOperation AS Operations_IsSubcOperation
FROM CBO_Routing AS A
LEFT JOIN CBO_Operation AS A1 ON (A.ID = A1.Routing)
LEFT JOIN CBO_ItemMaster AS A2 ON (A.ItemMaster = A2.ID)
LEFT JOIN CBO_RoutingVersion AS A3 ON (A.RoutingVersion = A3.ID)
LEFT JOIN Base_Organization AS A4 ON (A.Org = A4.ID)
LEFT JOIN Base_Language AS A6 ON (A6.Code = 'zh-CN') AND (A6.Effective_IsEffective = 1)
LEFT JOIN CBO_StdOperation AS A7 ON (A1.StdOperation = A7.ID)
LEFT JOIN CBO_Operation_Trl AS A8 ON (A8.SysMlFlag = 'zh-CN') AND (A8.SysMlFlag = A6.Code) AND (A1.ID = A8.ID)
LEFT JOIN CBO_Routing_Trl AS A9 ON (A9.SysMlFlag = 'zh-CN') AND (A9.SysMlFlag = A6.Code) AND (A.ID = A9.ID)
WHERE (A4.Code = '101')
AND A.EffectiveDate <= GETDATE()
AND A.DisableDate >= GETDATE();
标准工序
SELECT A.ID as ID,
A.Code as Code,
A3.Description as Description,
A3.Name as Name,
A5.Code as WorkCenter_Code,
A6.Name as WorkCenter_Name,
A.Effective_IsEffective as Effective_IsEffective,
A.Effective_EffectiveDate as Effective_EffectiveDate,
A.Effective_DisableDate as Effective_DisableDate
FROM CBO_StdOperation as A
LEFT JOIN Base_UOM as A1 on A.TimeUOM = A1.ID
LEFT JOIN Base_Organization as A2 on A.Org = A2.ID
LEFT JOIN Base_Language as A4 on A4.Code = 'zh-CN' and A4.Effective_IsEffective = 1
LEFT JOIN CBO_StdOperation_Trl as A3 on A3.SysMlFlag = 'zh-CN' and A3.SysMlFlag = A4.Code and A.ID = A3.ID
LEFT JOIN CBO_WorkCenter as A5 on A.WorkCenter = A5.ID
LEFT JOIN CBO_WorkCenter_Trl as A6 on A6.SysMlFlag = 'zh-CN' and A6.SysMlFlag = A4.Code and A5.ID = A6.ID
WHERE A2.Code = '101'
单据信息
采购订单
注意：采购订单这里 的备注和送货日期在U9里面是单独拆成了表存储的
-- 主表
SELECT a.id AS erp_id,
a.DocNo AS purchase_order,
a.BusinessDate AS purchase_date,
f.Code AS purchase_user,
d.Code AS purchase_dept_code,
Supplier_Code AS purchase_supplier,
e.Code AS purchase_category,
a.CreatedOn AS purchase_create_date,
a.CreatedBy AS purchase_creator,
a.IsBizClosed,
a.Status,
(SELECT TOP 1 PM_POMemo_Trl.Description
FROM PM_POMemo
JOIN PM_POMemo_Trl ON PM_POMemo_Trl.ID = PM_POMemo.ID
WHERE PM_POMemo.PurchaseOrder = a.ID) AS purchaseRemark,
a.IsFIClose,
a.Version
FROM PM_PurchaseOrder AS a
JOIN Base_Organization c ON c.ID = a.Org
LEFT JOIN CBO_Department d ON d.ID = a.PurDept
LEFT JOIN PM_PODocType AS e ON e.ID = a.DocumentType
LEFT JOIN CBO_Operators AS f ON a.PurOper = f.ID
WHERE 1=1
-- 子表
SELECT b.id AS erp_id,
a.DocNo AS order_item_order,
b.DocLineNo AS order_item_no,
b.ItemInfo_ItemName AS order_item_product_name,
b.ItemInfo_ItemCode AS order_item_product_no,
j.Code AS order_item_product_unit,
PurQtyPU AS order_item_order_qty,
SupplierConfirmQtyTU,
TotalRecievedQtyPU AS order_item_qty_receipt,
'' AS order_item_product_spec,
(SELECT MAX(DeliveryDate)
FROM PM_POShipLine
WHERE POLine = b.ID) AS order_item_delivery_date,
(SELECT TOP 1 PM_POMemo_Trl.Description
FROM PM_POMemo
JOIN PM_POMemo_Trl ON PM_POMemo_Trl.ID = PM_POMemo.ID
WHERE PM_POMemo.POLine = b.ID) AS order_item_remark,
OrderPriceTC AS unit_price,
b.IsBizClosed,
b.IsFIClose,
TotalTaxTC AS sum_money
FROM PM_POLine AS b
JOIN PM_PurchaseOrder AS a ON a.id = b.PurchaseOrder
JOIN Base_Organization c ON c.ID = a.Org
LEFT JOIN Base_UOM AS j ON b.PriceUOM = j.ID
WHERE a.ID = '主表ID'
生产订单
select DocNo AS deliveryOrderOrder,
DocNo AS deliveryOrderItemOrder,
1 AS deliveryOrderItemItem,
e.Code AS deliveryOrderCategory,
a.BusinessDate AS moDate,
b.Code AS deliveryOrderItemMaterial,
b.name AS deliveryOrderItemName,
j.Code AS deliveryOrderItemUnit,
ProductQty AS deliveryOrderItemQrderQty,
TotalCompleteQty AS deliveryOrderItemQtyUnissued,
StartDate,
CompleteDate AS deliveryOrderItemDeliveryDate,
(SELECT TOP 1 MO_MOMemo_Trl.Description
FROM MO_MOMemo
JOIN MO_MOMemo_Trl ON MO_MOMemo_Trl.ID = MO_MOMemo.ID
WHERE MO_MOMemo.MO = b.ID) AS deliveryOrderItemRemark,
(SELECT TOP 1 MO_MOMemo_Trl.Description
FROM MO_MOMemo
JOIN MO_MOMemo_Trl ON MO_MOMemo_Trl.ID = MO_MOMemo.ID
WHERE MO_MOMemo.MO = b.ID) AS deliveryOrderRemark,
a.CreatedOn AS deliveryOrderCreatedate,
a.CreatedBy AS deliveryOrderCreator,
DocState
from MO_MO a
JOIN Base_Organization c ON c.ID = a.Org
LEFT JOIN CBO_ItemMaster b on a.ItemMaster = b.id
LEFT JOIN Base_UOM AS j ON a.ProductUOM = j.ID
LEFT JOIN MO_MODocType AS e ON e.ID = a.MODocType
WHERE c.Code = '组织编码'
生产单BOM
生产单的用料BOM 用工单号查询
select A.ID,
B.DocNo AS deliveryBomOrder,
1 AS deliveryBomItem,
b1.Code AS deliveryBomMaterial,
A2.Code AS deliveryBomMaterialNo,
B.ProductQty AS deliveryBomOrderUseQty,
A.ActualReqQty AS deliveryBomOrderQty,
WasteRate AS deliveryBomWastageRate,
A.ActualReqQty AS deliveryBomReceiveQty,
OperationNum,
1 AS childQty,
1 AS parentQty,
'' AS deliveryBomLocation,
A1.Remark AS deliveryBomRemark,
A3.Code AS deliveryBomUnit
from MO_MOPickList as A
LEFT JOIN MO_MOPickList_Trl as A1 ON A.ID = A1.ID
LEFT JOIN CBO_ItemMaster as A2 ON A.ItemMaster = A2.ID
LEFT JOIN Base_UOM as A3 ON A.IssueUOM = A3.ID
LEFT JOIN MO_MO as B ON A.MO = B.ID
LEFT JOIN CBO_ItemMaster b1 on B.ItemMaster = b1.id
where B.DocNo = '101--24010001'
order by A.MO asc, A.DocLineNO asc
select A.[ID] as [ID],
A.[MO] as [MO],
A.[OperationNum] as [OperationNum],
A.[ItemMaster] as [ItemMaster],
A1.[IsSpecialItem] as [ItemMaster_IsSpecialItem],
A.[ItemVersion] as [ItemVersion],
A2.[Version] as [ItemVersion_Version],
A3.[Description] as [ItemVersion_Description],
A.[BOMReqQty] as [BOMReqQty],
A.[ActualReqQty] as [ActualReqQty],
A.[IssuedQty] as [IssuedQty],
A.[IsCheckATP] as [IsCheckATP],
A.[PlanReqDate] as [PlanReqDate],
A.[ActualReqDate] as [ActualReqDate],
A.[ActualIssueDate] as [ActualIssueDate],
A.[SubstitutedItem] as [SubstitutedItem],
A.[IsSubstitute] as [IsSubstitute],
A.[FromGrade] as [FromGrade],
A.[ToGrade] as [ToGrade],
A.[FromElement] as [FromElement],
A.[ToElement] as [ToElement],
A.[IsCalcCost] as [IsCalcCost],
A.[CostElement] as [CostElement],
A5.[Code] as [CostElement_Code],
A6.[Name] as [CostElement_Name],
A.[LatestECNDoc_EntityID] as [LatestECNDoc_EntityID],
A.[LatestECNDoc_EntityType] as [LatestECNDoc_EntityType],
A.[LatestECNExeDate] as [LatestECNExeDate],
A.[QtyType] as [QtyType],
A.[QPA] as [QPA],
A.[WasteRate] as [WasteRate],
A.[IssueStyle] as [IssueStyle],
A.[IssueUOM] as [IssueUOM],
A7.[Code] as [IssueUOM_Code],
A8.[Name] as [IssueUOM_Name],
A.[RcvUOM] as [RcvUOM],
A9.[Code] as [RcvUOM_Code],
A10.[Name] as [RcvUOM_Name],
A.[IsOverIssue] as [IsOverIssue],
A.[SupplyStyle] as [SupplyStyle],
A.[SupplyOrg] as [SupplyOrg],
A11.[Code] as [SupplyOrg_Code],
A12.[Name] as [SupplyOrg_Name],
A.[Subcontractor] as [Subcontractor],
A.[SupplyWh] as [SupplyWh],
A.[SupplyBin] as [SupplyBin],
A.[IsControlPos] as [IsControlPos],
A.[IsControlSupplier] as [IsControlSupplier],
A.[IsAutoCreate] as [IsAutoCreate],
A13.[Code] as [IssueBaseUOM_Code],
A14.[Name] as [IssueBaseUOM_Name],
A15.[Code] as [CostUOM_Code],
A16.[Name] as [CostUOM_Name],
A17.[Code] as [CostBaseUOM_Code],
A18.[Name] as [CostBaseUOM_Name],
A19.[Code] as [StoreBaseUOM_Code],
A20.[Name] as [StoreBaseUOM_Name],
A21.[DocNo] as [MO_DocNo],
A21.[Version] as [MO_Version],
A7.[Round_Precision] as [IssueUOM_Round_Precision],
A7.[Round_RoundType] as [IssueUOM_Round_RoundType],
A7.[Round_RoundValue] as [IssueUOM_Round_RoundValue],
A.[ReserveQty] as [ReserveQty],
A.[ReserveExeQty] as [ReserveExeQty],
A.[DocLineNO] as [DocLineNO],
A.[IssueNotDeliverQty] as [IssueNotDeliverQty],
A.[LatestECNDocNO] as [LatestECNDocNO],
A9.[Round_Precision] as [RcvUOM_Round_Precision],
A9.[Round_RoundType] as [RcvUOM_Round_RoundType],
A9.[Round_RoundValue] as [RcvUOM_Round_RoundValue],
A.[IsReserve] as [IsReserve],
A.[PrevMO] as [PrevMO],
A.[IsCheckUTE] as [IsCheckUTE],
A.[IsSCV] as [IsSCV],
A.[IsDisable] as [IsDisable],
A23.[Round_Precision] as [BOMComponent_IssueUOM_Round_Precision],
A23.[Round_RoundType] as [BOMComponent_IssueUOM_Round_RoundType],
A23.[Round_RoundValue] as [BOMComponent_IssueUOM_Round_RoundValue],
A25.[LotProduceTime] as [ItemMaster_InventoryInfo_LotParam_LotProduceTime],
A25.[LotControlType] as [ItemMaster_InventoryInfo_LotParam_LotControlType],
A26.[SnProduceTime] as [ItemMaster_InventoryInfo_SnParam_SnProduceTime],
A26.[SnControlType] as [ItemMaster_InventoryInfo_SnParam_SnControlType],
A15.[Round_Precision] as [CostUOM_Round_Precision],
A15.[Round_RoundType] as [CostUOM_Round_RoundType],
A15.[Round_RoundValue] as [CostUOM_Round_RoundValue],
A.[EditableWhenPull] as [EditableWhenPull],
A.[PickListStyle] as [PickListStyle],
A.[TransOutWareHouse] as [TransOutWareHouse],
A.[TransOutBin] as [TransOutBin],
A.[TransferedOutAmount] as [TransferedOutAmount],
A27.[IssueMaterialBatchQty] as [ItemMaster_MfgInfo_IssueMaterialBatchQty],
A27.[MinSupplyMaterialQty] as [ItemMaster_MfgInfo_MinSupplyMaterialQty],
A28.[ID] as [ItemMaster_MaterialOutUOM_ID],
A29.[LocationType] as [SupplyWh_LocationType],
A24.[IsLimitWarehouse] as [ItemMaster_InventoryInfo_IsLimitWarehouse],
A24.[IsLimitBin] as [ItemMaster_InventoryInfo_IsLimitBin],
A.[JIT] as [JIT],
A25.[LotPOVType] as [ItemMaster_InventoryInfo_LotParam_LotPOVType],
A.[SubcItemSrcType] as [SubcItemSrcType],
A.[TransferStyle] as [TransferStyle],
A27.[IsAllowExcessMaterial] as [ItemMaster_MfgInfo_IsAllowExcessMaterial],
A.[WholeSet] as [WholeSet],
A.[Project] as [Project],
A31.[Code] as [Project_Code],
A32.[Name] as [Project_Name],
A.[Task] as [Task],
A33.[Code] as [Task_Code],
A34.[Name] as [Task_Name],
A.[MOStartSetCheck] as [MOStartSetCheck],
A.[MOCompleteSetCheck] as [MOCompleteSetCheck],
A.[OPStartSetCheck] as [OPStartSetCheck],
A.[OPCompleteSetCheck] as [OPCompleteSetCheck],
A.[StandardMaterialScale] as [StandardMaterialScale],
A1.[IsMultyUOM] as [ItemMaster_IsMultyUOM],
A.[IsSpecialUseItem] as [IsSpecialUseItem],
A.[ConsignProcessItemSrc] as [ConsignProcessItemSrc],
A35.[IsTotalAgreementPurchase] as [ItemMaster_PurchaseInfo_IsTotalAgreementPurchase],
A.[IsCoupleIssue] as [IsCoupleIssue],
A24.[ReserveMode] as [ItemMaster_InventoryInfo_ReserveMode],
A.[FixedScrap] as [FixedScrap],
A.[ScrapType] as [ScrapType],
A.[IsDiffentBatchCtl] as [IsDiffentBatchCtl],
A27.[IsMixLotControl] as [ItemMaster_MfgInfo_IsMixLotControl],
A22.[JIT] as [BOMComponent_JIT],
A.[IssuedSetableQty] as [IssuedSetableQty],
A.[STDReqQty] as [STDReqQty],
A30.[Remark] as [Remark],
A.[MaterialType] as [MaterialType],
A.[FixedMaterialNum] as [FixedMaterialNum],
A.[ApplySetQty] as [ApplySetQty],
A22.[SubSeq] as [BOMComponent_SubSeq],
A.[SubstitutedPick] as [SubstitutedPick],
A22.[UsageQty] as [BOMComponent_UsageQty],
A36.[Round_Precision] as [MO_ProductUOM_Round_Precision],
A36.[Round_RoundType] as [MO_ProductUOM_Round_RoundType],
A36.[Round_RoundValue] as [MO_ProductUOM_Round_RoundValue],
A.[IsIssueOrgFixed] as [IsIssueOrgFixed],
A.[SpecialIssuedQty] as [SpecialIssuedQty],
A1.[Code] as [ItemMaster_Code],
A1.[Name] as [ItemMaster_Name],
A37.[Code] as [SubstitutedItem_Code],
A37.[Name] as [SubstitutedItem_Name],
A38.[Code] as [Subcontractor_Code],
A39.[Name] as [Subcontractor_Name],
A29.[Code] as [SupplyWh_Code],
A40.[Name] as [SupplyWh_Name],
A41.[Code] as [SupplyBin_Code],
A42.[Name] as [SupplyBin_Name],
A1.[IsGradeControl] as [ItemMaster_IsGradeControl],
A1.[StartGrade] as [ItemMaster_StartGrade],
A1.[EndGrade] as [ItemMaster_EndGrade],
A1.[IsPotencyControl] as [ItemMaster_IsPotencyControl],
A1.[StartPotency] as [ItemMaster_StartPotency],
A1.[EndPotency] as [ItemMaster_EndPotency],
A1.[StandardGrade] as [ItemMaster_StandardGrade],
A1.[StandardPotency] as [ItemMaster_StandardPotency],
A1.[ItemFormAttribute] as [ItemMaster_ItemFormAttribute],
A.[IssueBaseUOM] as [IssueBaseUOM],
A.[IUToIBURate] as [IUToIBURate],
A.[CostUOM] as [CostUOM],
A.[CostBaseUOM] as [CostBaseUOM],
A.[CUToCBURate] as [CUToCBURate],
A.[IBUToCBURate] as
[IBUToCBURate],
A.[StoreBaseUOM] as [StoreBaseUOM],
A.[SUToSBURate] as [SUToSBURate],
A.[IBUToSBURate] as [IBUToSBURate],
A1.[IsTrademark] as [ItemMaster_IsTrademark],
A7.[UOMClass] as [IssueUOM_UOMClass],
A37.[Version] as
[SubstitutedItem_Version],
A1.[IsVersionQtyControl] as [ItemMaster_IsVersionQtyControl],
A1.[ConverRatioRule] as [ItemMaster_ConverRatioRule],
A1.[IsVarRatio] as [ItemMaster_IsVarRatio],
A27.[BomControlMode] as
[ItemMaster_MfgInfo_BomControlMode],
A.[BOMComponent] as [BOMComponent],
A22.[IssueUOM] as [BOMComponent_IssueUOM],
A24.[IsReservable] as [ItemMaster_InventoryInfo_IsReservable],
A1.[IsInventoryEnable] as
[ItemMaster_IsInventoryEnable],
A43.[IsATPCheck] as [ItemMaster_SaleInfo_IsATPCheck],
A29.[IsBin] as [SupplyWh_IsBin],
A29.[IsLot] as [SupplyWh_IsLot],
A29.[IsSerial] as [SupplyWh_IsSerial],
A44.[IsBin] as
[TransOutWareHouse_IsBin],
A29.[DepositType] as [SupplyWh_DepositType],
A29.[OutboundType] as [SupplyWh_OutboundType],
A41.[IsVMI] as [SupplyBin_IsVMI],
A29.[Supplier] as [SupplyWh_Supplier],
A45.[Code] as
[SupplyWh_Supplier_Code],
A46.[Name] as [SupplyWh_Supplier_Name],
A24.[LotParam] as [ItemMaster_InventoryInfo_LotParam],
A24.[SnParam] as [ItemMaster_InventoryInfo_SnParam],
A44.[Code] as [TransOutWareHouse_Code],
A47.[Name]
as [TransOutWareHouse_Name],
A48.[Code] as [TransOutBin_Code],
A49.[Name] as [TransOutBin_Name],
A.[IsParentMOPick] as [ParentMOCoOpPick],
A21.[IsStartQtyPicking] as [MO_IsStartQtyPicking],
A50.[Code] as [BOMComponent_ItemMaster_Code],
A50.[Name] as [BOMComponent_ItemMaster_Name],
A9.[RatioToBase] as [RcvUOM_RatioToBase],
A.[ReplaceRatio] as [ReplaceRatio],
A.[IsKeyItem] as [IsKeyItem],
A.[GroupSchemeNo] as [GroupSchemeNo],
A.[SubstituteStyle] as [SubstituteStyle],
A.[CreatedOn] as [CreatedOn],
A.[CreatedBy] as [CreatedBy],
A.[ModifiedOn] as [ModifiedOn],
A.[ModifiedBy] as [ModifiedBy],
A.[IsPhantomPart] as [IsPhantomPart],
A27.[MixTradeMark] as [ItemMaster_MfgInfo_MixTradeMark],
A.[IsTaskControl] as
[IsTaskControl],
A.[IsDiffentSuppliersCtl] as [IsDiffentSuppliersCtl],
A.[BFOpportunity] as [BFOpportunity],
A.[BomItemChange] as [BomItemChange],
A.[IssueApplyQty] as [IssueApplyQty],
A.[IssueConfirmQty] as
[IssueConfirmQty],
A.[RecedeApplyQty] as [RecedeApplyQty],
A.[RecedeConfirmQty] as [RecedeConfirmQty],
A.[IssueOverApplyQty] as [IssueOverApplyQty],
A.[RecedeOverApplyQty] as [RecedeOverApplyQty],
A.[RecedeOverConfirmQty] as
[RecedeOverConfirmQty],
A.[IssueOverConfirmQty] as [IssueOverConfirmQty],
A27.[IsInheritBomMasterNo] as [ItemMaster_MfgInfo_IsInheritBomMasterNo]
from MO_MOPickList as A
inner join [CBO_ItemMaster] as A1 on (A.[ItemMaster] = A1.[ID])
left join [CBO_ItemMasterVersion] as A2 on (A.[ItemVersion] = A2.[ID])
left join Base_Language as A4 on (A4.Effective_IsEffective = 1)
left join [CBO_ItemMasterVersion_Trl] as A3 on (A3.SysMlFlag = A4.Code) and (A2.[ID] = A3.[ID])
left join [CBO_CostElement] as A5 on (A.[CostElement] = A5.[ID])
left join [CBO_CostElement_Trl] as A6 on (A6.SysMlFlag = A4.Code) and (A5.[ID] = A6.[ID])
left join [Base_UOM] as A7 on (A.[IssueUOM] = A7.[ID])
left join [Base_UOM_Trl] as A8 on (A8.SysMlFlag = A4.Code) and (A7.[ID] = A8.[ID])
left join [Base_UOM] as A9 on (A.[RcvUOM] = A9.[ID])
left join [Base_UOM_Trl] as A10 on (A10.SysMlFlag = A4.Code) and (A9.[ID] = A10.[ID])
left join [Base_Organization] as A11 on (A.[SupplyOrg] = A11.[ID])
left join [Base_Organization_Trl] as A12 on (A12.SysMlFlag = A4.Code) and (A11.[ID] = A12.[ID])
left join [Base_UOM] as A13 on (A.[IssueBaseUOM] = A13.[ID])
left join [Base_UOM_Trl] as A14 on (A14.SysMlFlag = A4.Code) and (A13.[ID] = A14.[ID])
left join [Base_UOM] as A15 on (A.[CostUOM] = A15.[ID])
left join [Base_UOM_Trl] as A16 on (A16.SysMlFlag = A4.Code) and (A15.[ID] = A16.[ID])
left join [Base_UOM] as A17 on (A.[CostBaseUOM] = A17.[ID])
left join [Base_UOM_Trl] as A18 on (A18.SysMlFlag = A4.Code) and (A17.[ID] = A18.[ID])
left join [Base_UOM] as A19 on (A.[StoreBaseUOM] = A19.[ID])
left join [Base_UOM_Trl] as A20 on (A20.SysMlFlag = A4.Code) and (A19.[ID] = A20.[ID])
left join [MO_MO] as A21 on (A.[MO] = A21.[ID])
left join [CBO_BOMComponent] as A22 on (A.[BOMComponent] = A22.[ID])
left join [Base_UOM] as A23 on (A22.[IssueUOM] = A23.[ID])
left join [CBO_InventoryInfo] as A24 on (A1.[InventoryInfo] = A24.[ID])
left join [CBO_LotParameter] as A25 on (A24.[LotParam] = A25.[ID])
left join [CBO_SnParameter] as A26 on (A24.[SnParam] = A26.[ID])
left join [CBO_MfgInfo] as A27 on (A1.[MfgInfo] = A27.[ID])
left join [Base_UOM] as A28 on (A1.[MaterialOutUOM] = A28.[ID])
left join [CBO_Wh] as A29 on (A.[SupplyWh] = A29.[ID])
left join [MO_MOPickList_Trl] as A30 on (A30.SysMlFlag = A4.Code) and (A.[ID] = A30.[ID])
left join [CBO_Project] as A31 on (A.[Project] = A31.[ID])
left join [CBO_Project_Trl] as A32 on (A32.SysMlFlag = A4.Code) and (A31.[ID] = A32.[ID])
left join [CBO_Task] as A33 on (A.[Task] = A33.[ID])
left join [CBO_Task_Trl] as A34 on (A34.SysMlFlag = A4.Code) and (A33.[ID] = A34.[ID])
left join [CBO_PurchaseInfo] as A35 on (A1.[PurchaseInfo] = A35.[ID])
left join [Base_UOM] as A36 on (A21.[ProductUOM] = A36.[ID])
left join [CBO_ItemMaster] as A37 on (A.[SubstitutedItem] = A37.[ID])
left join [CBO_Supplier] as A38 on (A.[Subcontractor] = A38.[ID])
left join [CBO_Supplier_Trl] as A39 on (A39.SysMlFlag = A4.Code) and (A38.[ID] = A39.[ID])
left join [CBO_Wh_Trl] as A40 on (A40.SysMlFlag = A4.Code) and (A29.[ID] = A40.[ID])
left join [CBO_Bin] as A41 on (A.[SupplyBin] = A41.[ID])
left join [CBO_Bin_Trl] as A42 on (A42.SysMlFlag = A4.Code) and (A41.[ID] = A42.[ID])
left join [CBO_SaleInfo] as A43 on (A1.[SaleInfo] = A43.[ID])
left join [CBO_Wh] as A44 on (A.[TransOutWareHouse] = A44.[ID])
left join [CBO_Supplier] as A45 on (A29.[Supplier] = A45.[ID])
left join [CBO_Supplier_Trl] as A46 on (A46.SysMlFlag = A4.Code) and (A45.[ID] = A46.[ID])
left join [CBO_Wh_Trl] as A47 on (A47.SysMlFlag = A4.Code) and (A44.[ID] = A47.[ID])
left join [CBO_Bin] as A48 on (A.[TransOutBin] = A48.[ID])
left join [CBO_Bin_Trl] as A49 on (A49.SysMlFlag = A4.Code) and (A48.[ID] = A49.[ID])
left join [CBO_ItemMaster] as A50 on (A22.[ItemMaster] = A50.[ID])
left join [CBO_SeibanMaster] as A51 on (A.[Seiban] = A51.[ID])
where ((A21.[DocNo] = '101--24010001') and
(((A.[IsPhantomPart] != 1) or A.[SelfBOMMaster] is null) or (A.[SelfBOMMaster] < 0)))
order by A.[DocLineNO] asc, (A.[ID] + 17) asc;
生产单工序
SELECT A.[ID] as [ID],
A.[OperationNum] as [OperationNum],
A1.[OpDescription] as [OpDescription],
A2.[ID] as [WorkCenter_ID],
A2.[Code] as [WorkCenter_Code],
A3.[Name] as [WorkCenter_Name],
A.[PlanStartDate] as [PlanStartDate],
A.[PlanCompleteDate] as [PlanCompleteDate],
A.[IsQC] as [IsQC],
A4.[ID] as [MO_ID],
A5.[ID] as [OpOrg_ID],
A5.[Code] as [OpOrg_Code],
A6.[Name] as [OpOrg_Name],
A7.[ID] as [MO_ProductBaseUOM_ID],
A7.[Code] as [MO_ProductBaseUOM_Code],
A8.[Name] as [MO_ProductBaseUOM_Name],
A4.[PUToPBURate] as [MO_PUToPBURate],
A.[WIPRcvQty] as [WIPRcvQty],
A.[IsRcvDirectly] as [IsRcvDirectly],
A.[SysVersion] as [SysVersion],
A.[ID] as [MainID],
A4.[ProductUOM] as [MO_ProductUOM],
A9.[Round_Precision] as [MO_ProductUOM_Round_Precision],
A9.[Round_RoundType] as [MO_ProductUOM_Round_RoundType],
A9.[Round_RoundValue] as [MO_ProductUOM_Round_RoundValue]
FROM MO_MOOperation as A
left join [MO_MOOperation_Trl] as A1 on (A1.SysMlFlag = 'zh-CN') and (A.[ID] = A1.[ID])
left join [CBO_WorkCenter] as A2 on (A.[WorkCenter] = A2.[ID])
left join [CBO_WorkCenter_Trl] as A3 on (A3.SysMlFlag = 'zh-CN') and (A2.[ID] = A3.[ID])
left join [MO_MO] as A4 on (A.[MO] = A4.[ID])
left join [Base_Organization] as A5 on (A.[OpOrg] = A5.[ID])
left join [Base_Organization_Trl] as A6 on (A6.SysMlFlag = 'zh-CN') and (A5.[ID] = A6.[ID])
left join [Base_UOM] as A7 on (A4.[ProductBaseUOM] = A7.[ID])
left join [Base_UOM_Trl] as A8 on (A8.SysMlFlag = 'zh-CN') and (A7.[ID] = A8.[ID])
left join [Base_UOM] as A9 on (A4.[ProductUOM] = A9.[ID])
WHERE A4.DocNo = '101--24010002'
销售订单
SELECT a.id AS erp_id,
a.DocNo AS so_bill,
a.BusinessDate AS so_date,
f.Code AS so_user,
d.Code AS so_dept,
OrderBy_Code AS so_client,
e.Code AS so_business_type,
a.CreatedOn AS create_time,
a.CreatedBy AS create_by,
a.BizClose, -- 0未关闭1关闭
a.Status, -- 1=开立 2=核准中 3=已核准
(SELECT TOP 1 SM_SOMemo_Trl.Description
FROM SM_SOMemo
JOIN SM_SOMemo_Trl ON SM_SOMemo_Trl.ID = SM_SOMemo.ID
WHERE SM_SOMemo.SO = a.ID) AS remark,
a.Version AS version
FROM SM_SO AS a
JOIN Base_Organization c ON c.ID = a.Org
LEFT JOIN CBO_Department d ON d.ID = a.SaleDepartment
LEFT JOIN SM_SODocType AS e ON e.ID = a.DocumentType
LEFT JOIN CBO_Operators AS f ON a.Seller = f.ID
-- 子表
SELECT b.id AS erp_id,
a.DocNo AS so_detail_bill,
b.DocLineNo AS so_detail_item,
b.ItemInfo_ItemCode AS so_detail_product,
j.Code AS so_detail_unit,
OrderByQtyTU AS so_detail_qty,
SOLineSumInfo_SumShipQtyTU AS so_detail_complete_qty,
SOLineSumInfo_SumRFQtyTU AS so_detail_return_qty,
(SELECT MAX(DeliveryDate)
FROM SM_SOShipline
WHERE SOLine = b.ID) AS so_detail_delivery_date,
(SELECT TOP 1 SM_SOMemo_Trl.Description
FROM SM_SOMemo
JOIN SM_SOMemo_Trl ON SM_SOMemo_Trl.ID = SM_SOMemo.ID
WHERE SM_SOMemo.SOLine = b.ID) AS remark,
OrderPriceTC AS unit_price,
b.IsFIClose,
TotalTaxTC AS sum_money,
b.Status -- 1=开立 2=核准中 3=已核准 4=自然关闭 5=短缺关闭
FROM SM_SOLine AS b
JOIN SM_SO AS a ON a.id = b.SO
JOIN SM_SOShipline AS e ON e.SOLine = b.ID
JOIN Base_Organization c ON c.ID = a.Org
LEFT JOIN Base_UOM AS j ON e.TU = j.ID
WHERE a.ID = '主表ID'
标准出货
-- 主表
SELECT a.id AS erp_id,
a.DocNo AS shippingBill,
a.BusinessDate AS shippingDate,
f.Code AS shippingUser,
OrderBy_Code AS shippingClient,
1 AS shippingType,
ShipMode AS shippingMethod,
'' AS shippingWarehouse,
e.Code AS shippingBusinessType,
a.CreatedOn AS create_time,
a.CreatedBy AS create_by,
a.FinanceCloseFlag,
a.Status, -- 1=开立,2=核准中 3=已核准
g.ShipMemo AS remark,
a.Version AS version
FROM SM_Ship AS a
JOIN Base_Organization c ON c.ID = a.Org
LEFT JOIN SM_Ship_Trl g ON g.ID = a.ID
LEFT JOIN CBO_Department d ON d.ID = a.SaleDept
LEFT JOIN SM_ShipDocType AS e ON e.ID = a.DocumentType
LEFT JOIN CBO_Operators AS f ON a.Seller = f.ID
WHERE 1 = 1;
-- 子表
SELECT b.id AS erp_id,
a.DocNo AS shippingDetailBill,
b.DocLineNo AS shippingDetailItem,
b.ItemInfo_ItemCode AS shippingDetailProduct,
j.Code AS so_detail_unit,
d.Code AS shippingDetailWarehouse,
b.SONo AS salesOrderBill,
b.SOLineNo AS salesOrderItem,
ShipQtyInvAmount AS shippingDetailQty,
e.ShipLineMemo AS remark,
b.IsCloseSO,
b.Status
FROM SM_ShipLine AS b
JOIN SM_Ship AS a ON a.id = b.Ship
LEFT JOIN SM_ShipLine_Trl AS e ON e.ID = b.ID
LEFT JOIN CBO_Wh AS d ON b.WH = d.ID
LEFT JOIN Base_UOM as j ON b.InvMainUOM = j.ID
LEFT JOIN Base_Organization c ON c.ID = a.Org
WHERE a.ID = '主表ID';
其它信息
即时库存
select t1.ID AS cdefine1,
t4.code as productCode,
t4.Name As productName,
t4.SPECS As productSpec,
t2.code As warehouseCode,
t6.Name AS warehouseName,
t7.Code As unitCode,
t3.name As unitName,
t7.Code As baseUnitCode,
t3.name As baseUnitName,
ISNULL(StoreQty, 0) AS qty,
ISNULL(StoreQty, 0) AS baseQty
from InvTrans_WhQoh t1
left join CBO_ItemMaster t4 on t4.id = t1.ItemInfo_ItemID
left join CBO_Wh t2 on t2.ID = t1.Wh
left join CBO_Wh_Trl t6 on t6.ID = t2.ID
left join Base_UOM t7 on t7.id = t1.storeuom
left join Base_UOM_trl t3 on t3.id = t1.storeuom
left join Base_Organization org on t1.ItemOwnOrg = org.ID
where org.Code = '101'
操作日志
同步ERP的删除日志做同步删除
SELECT distinct A8.DisplayName
, A.description
FROM UBF_Log_OperateLog as A
JOIN Base_Organization c ON c.ID = a.Org
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 c.Code = '{}'
and ControlName LIKE '删除'
AND A.IsSuccess = 1 AND OccurrenceTime >= '{}'
参考资料
- https://www.cnblogs.com/shihua513/p/17276283.html
(用友U9-SQL查询语句汇总) - U9数据库文档：http://xxxx/ClassView/ (让U9服务商部署即可)
- U9怎么看数据库字典：https://www.bilibili.com/video/BV1JK4y1o7mU/
- U9论坛：https://www.oyonyou.com/forum-u9-1.html
- 关于如何进行ERP数据库开发可以参考：https://blog.csdn.net/u014287572/article/details/135690598
5172
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
