K3Cloud 数据库表结构分析

一、背景

在日常使用K3Cloud ERP系统时, 经常需要查询数据库表, 这里记录一下常用的表结构;

二、客户基础信息

2.1 客户表 (T_BD_Customer)

SELECT 
    FCUSTID AS '客户内码',
    FNUMBER AS '客户编码',
    FNAME AS '客户名称',
    FSimpleName AS '客户简称',
    FCONTACT AS '联系人',
    FPHONE AS '电话',
    FMOBILE AS '手机',
    FADDRESS AS '地址',
    FDESCRIPTION AS '备注'
FROM T_BD_Customer
WHERE FDocStatus = 'C'  -- 已审核
AND FForbidStatus = 'A'  -- 未禁用

2.2 客户扩展信息表

SELECT 
    c.FCUSTID,
    c.FNUMBER,
    c.FNAME,
    e.F_ZX_CustomerType AS '客户类型',
    e.F_ZX_BusinessType AS '业务类型'
FROM T_BD_Customer c
LEFT JOIN T_BD_CustomerEx e ON c.FCUSTID = e.FCUSTID

三、供应商基础信息

3.1 供应商表 (T_BD_Supplier)

SELECT 
    FSUPPLIERID AS '供应商内码',
    FNUMBER AS '供应商编码',
    FNAME AS '供应商名称',
    FSimpleName AS '供应商简称',
    FCONTACT AS '联系人',
    FPHONE AS '电话',
    FMOBILE AS '手机',
    FADDRESS AS '地址'
FROM T_BD_Supplier
WHERE FDocStatus = 'C'
AND FForbidStatus = 'A'

四、销售订单表结构

4.1 销售订单主表 (T_SAL_ORDER)

SELECT 
    FSALORDERID AS '订单内码',
    FBILLNO AS '订单编号',
    FDATE AS '订单日期',
    FCUSTID AS '客户内码',
    FTOTALAMOUNT AS '价税合计',
    FDEPTID AS '销售部门内码',
    FSALERID AS '销售员内码',
    FCLOSESTATUS AS '关闭状态',
    FSTATUS AS '单据状态'
FROM T_SAL_ORDER
WHERE FBILLNO = 'SAL202112300001'

4.2 销售订单明细表 (T_SAL_ORDERENTRY)

SELECT 
    FSALORDERENTRYID AS '明细内码',
    FSALORDERID AS '订单内码',
    FMATERIALID AS '物料内码',
    FUNITID AS '单位内码',
    FQTY AS '销售数量',
    FPRICE AS '单价',
    FTAXPRICE AS '含税单价',
    FALLAMOUNT AS '价税合计',
    FTAXAMOUNT AS '税额'
FROM T_SAL_ORDERENTRY

五、采购订单表结构

5.1 采购订单主表 (T_PUR_POORDER)

SELECT 
    FPOORDERID AS '订单内码',
    FBILLNO AS '订单编号',
    FDATE AS '订单日期',
    FSUPPLIERID AS '供应商内码',
    FTOTALAMOUNT AS '价税合计',
    FDEPTID AS '采购部门内码',
    FPURCHASERID AS '采购员内码'
FROM T_PUR_POORDER
WHERE FBILLNO LIKE 'PO%'

六、库存相关表

6.1 即时库存表 (T_STK_INVENTORY)

SELECT 
    FMaterialID AS '物料内码',
    FBOMID AS 'BOM版本',
    FSTOCKID AS '仓库内码',
    FLOTID AS '批号',
    FSTOCKLOCID AS '仓位',
    FQTY AS '库存数量',
    FAVGQTY AS '可用数量'
FROM T_STK_INVENTORY
WHERE FMaterialID = @MaterialID
AND FStockID = @StockID

七、财务相关表

7.1 应收款表 (T_AR_RECEIVEBILL)

SELECT 
    FRECEIVEBILLID AS '应收单内码',
    FBILLNO AS '单据编号',
    FDATE AS '单据日期',
    FCUSTID AS '客户内码',
    FTOTALAMOUNT AS '价税合计',
    FPAIDAMOUNT AS '已收款金额',
    FUNPAIDAMOUNT AS '未收款金额'
FROM T_AR_RECEIVEBILL
WHERE FCUSTID = @CustID

八、常用关联查询

8.1 销售订单完整信息查询

SELECT 
    o.FBILLNO AS '订单编号',
    o.FDATE AS '订单日期',
    c.FNAME AS '客户名称',
    m.FNUMBER AS '物料编码',
    m.FNAME AS '物料名称',
    e.FQTY AS '销售数量',
    e.FTAXPRICE AS '含税单价',
    e.FALLAMOUNT AS '价税合计'
FROM T_SAL_ORDER o
INNER JOIN T_SAL_ORDERENTRY e ON o.FSALORDERID = e.FSALORDERID
INNER JOIN T_BD_CUSTOMER c ON o.FCUSTID = c.FCUSTID
INNER JOIN T_BD_MATERIAL m ON e.FMATERIALID = m.FMATERIALID
WHERE o.FDOCSTATUS = 'C'
ORDER BY o.FDATE DESC

8.2 客户应收款汇总查询

SELECT 
    c.FNUMBER AS '客户编码',
    c.FNAME AS '客户名称',
    SUM(r.FTOTALAMOUNT) AS '应收总额',
    SUM(r.FPAIDAMOUNT) AS '已收金额',
    SUM(r.FUNPAIDAMOUNT) AS '未收金额'
FROM T_AR_RECEIVEBILL r
INNER JOIN T_BD_CUSTOMER c ON r.FCUSTID = c.FCUSTID
WHERE r.FDOCSTATUS = 'C'
GROUP BY c.FNUMBER, c.FNAME
HAVING SUM(r.FUNPAIDAMOUNT) > 0

九、字段状态说明

9.1 单据状态 (FDocStatus)

• A: 暂存
• B: 创建
• C: 审核
• D: 作废

9.2 禁用状态 (FForbidStatus)

• A: 未禁用
• B: 已禁用

9.3 关闭状态 (FCloseStatus)

• A: 未关闭
• B: 已关闭

十、常用基础资料表

十一、注意事项

1. 查询时注意添加单据状态和禁用状态的过滤条件
2. 金额字段通常为精度4位的小数
3. 日期字段需要根据实际需求进行格式化
4. 内码字段为整型，外键关联使用
5. 涉及财务数据时请注意数据安全和权限控制

十二、参考文档

• 金蝶K3Cloud数据库字典
• T+系统数据库设计文档

作者: CLP ; 日期: 2022-9-3 ; 地点: 武汉; 天气: 晴

QQ: 53258372; Mail : 53258372@QQ.com

微信: