记录金蝶云常用表及关联表

记录金蝶中常用表及关联表（持续更新…）

---

1.基础资料

1. 物料

   SELECT T1.FNUMBER,T2.FNAME,T2.FSPECIFICATION,* 
   FROM T_BD_MATERIAL    T1  
   JOIN T_BD_MATERIAL_L  T2 ON T1.FMATERIALID = T2.FMATERIALID AND T2.FLOCALEID = 2052
   

2. 组织机构 T_ORG_ORGANIZATIONS
3. 供应商 T_BD_SUPPLIER
4. 客户 T_BD_CUSTOMER
5. 采购员 V_BD_BUYER
6. 销售员 V_BD_SALESMAN
7. 部门 T_BD_DEPARTMENT
8. 币别 T_BD_CURRENCY
9. 仓库 T_BD_STOCK
10. 单位 T_BD_UNIT

2.单据

1. 销售出库单

   select dn.FBILLNO,so.FBILLNO,* from T_SAL_OUTSTOCK          bill
   join T_SAL_OUTSTOCKFIN       fin     on bill.FID = fin.FID  -- 财务信息
   join T_SAL_OUTSTOCKENTRY     bentry  on bill.FID = bentry.FID --明细
   join T_SAL_OUTSTOCKENTRY_F   ef      on bentry.FENTRYID = ef.FENTRYID --明细_财务信息
   join T_SAL_OUTSTOCKENTRY_R   er      on bentry.FENTRYID = er.FENTRYID --明细_关联信息
   join T_SAL_OUTSTOCKENTRY_LK  elk     on bentry.FENTRYID = elk.FENTRYID and elk.FSTABLENAME='T_SAL_DELIVERYNOTICEENTRY'--细关联表
   ----select distinct FSTABLENAME from T_SAL_OUTSTOCKENTRY_LK  
   --发货通知单 关联上游单据
   join T_SAL_DELIVERYNOTICE			dn			on	elk.FSBILLID=dn.FID
   join T_SAL_DELIVERYNOTICEENTRY		dne			on elk.FSID=dne.FENTRYID
   join T_SAL_DELIVERYNOTICEENTRY_LK	dne_lk		on dne.FENTRYID=dne_lk.FENTRYID and dne_lk.FSTABLENAME='T_SAL_ORDERENTRY'
   --销售订单 关联上游单据
   join T_SAL_ORDER					so			on	dne_lk.FSBILLID=so.FID		--FSBILLID	上游单据的表头ID
   join T_SAL_ORDERENTRY				soe			on dne_lk.FSID=soe.FENTRYID	--FSID		上游单据的表体ID
   

2. 采购订单 T_PUR_POOEDER
3. 采购申请单 T_PUR_REQUISITION
4. 收料通知单 T_PUR_RECEIVE
5. 采购入库单 T_STK_INSTOCK
6. 采购退料单 T_PUR_MRB
7. 应付单 T_AP_PAYABLE
8. 付款单 T_AP_PAYBILL
9. 销售订单 T_SAL_ORDER
10. 发货通知单 T_SAL_DELIVERYNOTICE
11. 销售出库单 T_SAL_OUTSTOCK
12. 销售退货单 T_SAL_RETURNSTOCK
13. 应收单 T_AR_RECEIVABLE
14. 收款单 T_AR_RECEIVEBILL
15. 生产订单 T_PRD_MO
16. 物料清单（BOM）T_ENG_BOM
17. 用料清单 T_PRD_PPBOM
18. 生产领料单 T_PRD_PICKMTRL
19. 生产退料单 T_PRD_RETURNMTRL
20. 生产入库单 T_PRD_INSTOCK
21. 生产汇报单 T_PRD_MORPT
22. 受托加工材料入库单 T_STK_OEMINSTOCK
23. 受托加工材料退料单 T_STK_OEMINSTOCKRTN
24. 直接调拨单 T_STK_STKTRANSFERIN
25. 分步式调出单 T_STK_STKTRANSFEROUT
26. 分步式调入单 T_STK_STKTRANSFERIN
27. 其他出库单 T_STK_MISDELIVERY
28. 其他入库单 T_STK_MISCELLANEOUS
29. 委外订单 T_SUB_REQORDER
30. 委外领料单 T_SUB_PICKMTRL
31. 委外退料单 T_SUB_RETURNMTRL

3.财务相关

1. 科目

   select T1.FNUMBER,T2.FNAME,T2.FFULLNAME,* from T_BD_ACCOUNT T1
   join T_BD_ACCOUNT_L T2 on T1.FACCTID = T2.FACCTID
   

2. 核算维度 T_BD_FLEXITEMDETAILV

   select * from T_BD_FLEXITEMDETAILV
   --FFLEX9 费用项目--FFLEX11 组织机构--14 银行--15 银行账号--16 其他往来单位--4 供应商--5 部门--6 客户 --7 员工--8 物料
   

3. 总账凭证

   select * from t_gl_voucher q1 --财务凭证
   left join t_gl_voucherentry q2 on q1.FVoucherID=q2.FVoucherID --凭证分录
   

4. 业务凭证

   select * from T_BAS_VOUCHER   bill --业务凭证
   left join T_BAS_VOUCHERENTRY  billentry  on bill.FVOUCHERID =billentry.FVOUCHERID
   

5. 凭证关联单据

   select bv.FSOURCEBILLNO, --关联单据
   *  
   from T_GL_VOUCHER t1 
   join T_GL_VOUCHERENTRY       t2           on t1.FVOUCHERID = t2.FVOUCHERID
   left join T_BAS_VOUCHER      bv           on t1.FVOUCHERID = bv.FGLVOUCHERID and t1.FACCOUNTBOOKID = bv.FACCOUNTBOOKID 
   join T_BD_ACCOUNT            account      on account.FACCTID = t2.FACCOUNTID
   join T_BD_ACCOUNT_L          account_l    on account.FACCTID = account_l.FACCTID 
   where t1.FDOCUMENTSTATUS <> 'Z' 
   and t1.FYEAR = 2024 and t1.FPERIOD = 03 
   and FISADJUSTVOUCHER = 0 -- 不是调整期
   and account.FNUMBER like '5301%'
   

6. 科目余额表 T_GL_BALANCE
7. 调整期间科目余额表T_GL_BALANCEADJUST
8. 期末调汇T_GL_ALLOCATEEXCHANGE

   select distinct FCURRENCYID,FEXCHANGERATE
   from T_GL_ALLOCATEEXCHANGE  ex
   left join T_BD_FLEXITEMDETAILV flex on FID = FDETAILID
   where FYEAR = 2024 and FPERIOD = 3 
   

4.其他

1. 枚举项查询

   select meta_l.FCAPTION ,meta.FVALUE from T_META_FORMENUM   menum
   left join T_META_FORMENUM_L menum_l on menum.FID = menum_l.FID and menum_l.FLOCALEID = 2052
   left join T_META_FORMENUMITEM meta  on menum.FID = meta.FID
   left join T_META_FORMENUMITEM_L meta_l on meta.FENUMID=meta_l.FENUMID AND meta_l.FLOCALEID=2052 
   where menum_l.FNAME = '成本核算来源' order by meta.FSEQ
   

2. 辅助资料

   select a.FNUMBER,b.FNAME,c.FNUMBER,d.FDATAVALUE,*
   from T_BAS_ASSISTANTDATA a 
   inner join T_BAS_ASSISTANTDATA_L b on a.fid=b.fid 
   inner join T_BAS_ASSISTANTDATAENTRY c on a.FID=c.FID
   inner join T_BAS_ASSISTANTDATAENTRY_L d on c.FENTRYID=d.FENTRYID
   where b.FNAME = '出库类型'