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

改昵称比敲代码难

1360人浏览 · 2024-04-01 17:30:58

改昵称比敲代码难 · 2024-04-01 17:30:58 发布

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

索引

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

组织机构 T_ORG_ORGANIZATIONS
供应商 T_BD_SUPPLIER
客户 T_BD_CUSTOMER
采购员 V_BD_BUYER
销售员 V_BD_SALESMAN
部门 T_BD_DEPARTMENT
币别 T_BD_CURRENCY
仓库 T_BD_STOCK
单位 T_BD_UNIT

2.单据

销售出库单

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

采购订单 T_PUR_POOEDER
采购申请单 T_PUR_REQUISITION
收料通知单 T_PUR_RECEIVE
采购入库单 T_STK_INSTOCK
采购退料单 T_PUR_MRB
应付单 T_AP_PAYABLE
付款单 T_AP_PAYBILL
销售订单 T_SAL_ORDER
发货通知单 T_SAL_DELIVERYNOTICE
销售出库单 T_SAL_OUTSTOCK
销售退货单 T_SAL_RETURNSTOCK
应收单 T_AR_RECEIVABLE
收款单 T_AR_RECEIVEBILL
生产订单 T_PRD_MO
物料清单（BOM）T_ENG_BOM
用料清单 T_PRD_PPBOM
生产领料单 T_PRD_PICKMTRL
生产退料单 T_PRD_RETURNMTRL
生产入库单 T_PRD_INSTOCK
生产汇报单 T_PRD_MORPT
受托加工材料入库单 T_STK_OEMINSTOCK
受托加工材料退料单 T_STK_OEMINSTOCKRTN
直接调拨单 T_STK_STKTRANSFERIN
分步式调出单 T_STK_STKTRANSFEROUT
分步式调入单 T_STK_STKTRANSFERIN
其他出库单 T_STK_MISDELIVERY
其他入库单 T_STK_MISCELLANEOUS
委外订单 T_SUB_REQORDER
委外领料单 T_SUB_PICKMTRL
委外退料单 T_SUB_RETURNMTRL

3.财务相关

科目

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

核算维度 T_BD_FLEXITEMDETAILV

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

总账凭证

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

业务凭证

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

凭证关联单据

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%'

科目余额表 T_GL_BALANCE
调整期间科目余额表T_GL_BALANCEADJUST

期末调汇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.其他

枚举项查询

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

辅助资料

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 = '出库类型'