博客摘录「 K3Cloud 常用表&amp；查询语句（持续更新）」2024年6月12日

K3Cloud 常用表&查询语句

--查询数据表select * from(select  convert(varchar(4000),t1.FKERNELXML.query('//TableName')) as  'Item',t2.FNAME, t1.* from T_META_OBJECTTYPE t1 left join T_META_OBJECTTYPE_L t2 on t1.FID=t2.FID--where t1.fid='SAL_SaleOrder') twhere Item<>'' and FNAME like '%收料通知单%'通过表 T_META_OBJECTTYPE 的 FKERNNELXML 字段进行xml查找 库存状态列表select t1.FSTOCKSTATUSID,t1.FNUMBER,t2.FNAME from T_BD_STOCKSTATUS t1left join  T_BD_STOCKSTATUS_L t2 on t1.FSTOCKSTATUSID=t2.FSTOCKSTATUSID单据类型select t1.FBILLTYPEID,t1.FNUMBER,t2.FNAME from T_BAS_BILLTYPE t1left join T_BAS_BILLTYPE_L t2 on t1.FBILLTYPEID=t2.FBILLTYPEID where t1.FBILLFORMID='QM_InspectBill'  --单据类型--检验单 基础资料T_ORG_ORGANIZATIONS  组织表T_ORG_ORGANIZATIONS_L   组织表T_BAS_ASSISTANTDATA_L 辅助资料分类T_BAS_ASSISTANTDATAENTRY  辅助资料列表T_BAS_ASSISTANTDATAENTRY_L  辅助资料列表select t1.FID,t1.FNAME,t2.FNUMBER,t3.FDATAVALUE,t3.FDESCRIPTION,* from T_BAS_ASSISTANTDATA_L t1 --where FNAME = '销售物料类别' join T_BAS_ASSISTANTDATAENTRY t2 on t1.FID = t2.FID--where FID = '5d771acfb0108a'join T_BAS_ASSISTANTDATAENTRY_L t3 on t3.FENTRYID = t2.FENTRYIDt_bd_accountbook_l（账簿多语言）t_bd_accountbook（账簿）t_bd_material(物料)t_bd_material_l(物料多语言)T_BD_OPERATOR(业务员) T_BD_OPERATOR_L(业务员多语言)T_BD_STAFF(员工表)t_BD_Stock(仓库)t_bd_supplier(供应商)T_BD_UNIT & T_BD_UNIT_L 单位select * from T_BD_UNIT_L uljoin T_BD_UNIT u on u.FUNITID = u.FUNITIDwhere ul.FNAME = '平方米'T_BD_CUSTOMER & T_BD_CUSTOMER_L 客户select * from T_BD_CUSTOMER ljoin T_BD_CUSTOMER_L  le on le.FCUSTID = l.FCUSTID ----and l.FCUSTID = 103387--and l.FNumber = 'C1001'T_BD_EXPENSE & T_BD_EXPENSE_L 费用项目select cl.FNAME,c.FNUMBER,cl.FEXPID,* from T_BD_EXPENSE cjoin T_BD_EXPENSE_L cl on c.FEXPID = cl.FEXPID 财务t_AP_payable(应付单表头) T_AP_PAYBILL(付款单) t_AR_receivable(应收单表头) T_AR_RECEIVABLEENTRY(应收单单据体) t_AR_RECEIVABLEENTRY_L(应收单单据体扩展) T_AR_RECEIVABLEENTRY_O(应收单单据体分表) T_IV_PURCHASEIC(采购发票) T_IV_SALESIC(销售发票)T_AR_RECEIVEBILL 收款单T_AR_RECEIVEBILLENTRY 收款单明细T_GL_VOUCHER & T_GL_VOUCHERENTRY 凭证 ------------ 在凭证上面取数 select le.FDEBIT --借方金额,le.FCREDIT --贷方金额,le.FEXPLANATION,le.FDETAILID--,l.FACCOUNTBOOKID--,l.FCHECKED--,l.FVOUCHERGROUPID--,l.FVOUCHERGROUPNO--,l.FDOCUMENTSTATUS--,l.FINVALID--,kml.FFULLNAME--,l.FYEAR--,l.FPERIOD from T_GL_VOUCHER l join T_GL_VOUCHERENTRY le on   l.FVOUCHERID = le.FVOUCHERID and l.FYEAR = YEAR('2019-10-01') and l.FPERIOD = MONTH('2019-10-01')and l.FINVALID =0 and l.FDOCUMENTSTATUS = 'C' --已审核join T_BD_ACCOUNT_L kml on le.FACCOUNTID = kml.FACCTID join T_BD_ACCOUNT km on kml.FACCTID = km.FACCTID --and km.FNUMBER = '8201.01'and kml.FNAME = '等待发票中折扣'join T_BD_FLEXITEMDETAILV lv on lv.FID = le.FDETAILID  --核算维度,0是不指定客户，否则，需要在T_BD_FLEXITEMDETAILV 查 FFLEX6，与客户T_BD_CUSTOMER  FCUSTID对应and lv.FFLEX6 = 103387 ---过滤客户ID=103387T_GL_Balance 科目余额表--取某科目的 科目余额 select t1.FYEAR,t1.FPERIOD,kml.FNAME,kml.FFULLNAME, t1.FDEBITFOR --本期发生 借方原币 ,t1.FDEBIT --本期发生 借方本位币 ,t1.FCREDITFOR --本期发生 贷方原币 ,t1.FCREDIT --本期发生 贷方本位币 ,t1.FBEGINBALANCE --期初本位币 ,t1.FBEGINBALANCEFOR --期初原币 ,t1.FENDBALANCE --期末本位币 ,t1.FENDBALANCEFOR --期末原币 from T_GL_BALANCE t1join T_BD_ACCOUNT km on t1.FACCOUNTID = km.FACCTID join T_BD_ACCOUNT_L kml on km.FACCTID = kml.FACCTID and kml.FNAME = '等待发票中折扣'where FYEAR = YEAR('2019-10-01') and FPERIOD = MONTH('2019-10-01')and FACCOUNTBOOKID = 103976 --副账簿 and FDETAILID = 0 and FCURRENCYID = 1 T_BD_ACCOUNT &  T_BD_ACCOUNT_L 科目供应链T_PUR_POORDER(采购订单) T_PUR_PRICELIST （价目表） T_PUR_RECEIVE(采购收料单) T_PUR_REQUISITION(采购申请) T_SAL_DELIVERYNOTICE(销售发货通知单)T_SAL_ORDER(销售订单) T_SAL_OUTSTOCK(销售出库单) T_SAL_RETURNSTOCK(销售退货单) T_STK_INSTOCK(入库单) T_STK_InvBal(库存余额表) T_STK_INVENTORY(即时库存表) 4.制造T_ENG_BOM(物料清单) T_PRD_INSTOCK(生产入库单) T_PRD_MO(生产订单) T_PRD_MORPT(生产汇报单) T_PRD_PICKMTRL(生产领料单) T_PRD_PPBOM(生产用料清单) T_PRD_PREPAREMTRL(生产备料单据头) T_PRD_RETURNMTRL(生产退料单) T_SUB_FEEDMTRL(委外补料单) T_SUB_PICKMTRL(委外领料单) T_SUB_PPBOM(委外用料清单) T_SUB_REQORDER(委外订单) T_SUB_RETURNMTRL(委外退料单) --物料名称select m.fmaterialid,m.fmasterid, fname,m.fnumber from t_bd_material m join t_bd_material_l l on l.fmaterialid=m.fmaterialidwhere 1=1--and m.fmaterialid=147002and m.fnumber='XACPWM0067'--and l.fname like '%扯面%'order by m.fmaterialid desc --组织select V_SCM_OWNERORG_L.FNAME,* from V_SCM_OWNERORG left join V_SCM_OWNERORG_L on V_SCM_OWNERORG.FORGID=V_SCM_OWNERORG_L.FORGIDselect l.FNAME,* from T_ORG_ORGANIZATIONS ojoin T_ORG_ORGANIZATIONS_L lon l.FORGID=o.FORGID--元数据select * from t_meta_objecttype_l ml,t_meta_objecttype m where fname like '%工序汇报%' and ml.fid=m.fid--部门select b.FNAME,* from T_BD_DEPARTMENT a join T_BD_DEPARTMENT_L b on a.FDEPTID=b.FDEPTID--单据转换select * from T_META_CONVERTRULE where fid='PlanOrder_PPBom'--通过元数据唯一id查询菜单select * from T_META_CONSOLEDETAIL where FOBJECTID like '%stk_%'--反写规则select rl.FNAME, * from T_BF_WRITEBACKRULE rleft join T_BF_WRITEBACKRULE_L rl on rl.FID=r.FID and rl.FLOCALEID=2052left join T_BF_WRITEBACKRULECUST rc on r.FID=rc.FIDwhere (rl.FNAME like '%生产线生产%' or rl.FID like '%sfc_%') and rc.FFORBIDSTATUS='A'--转换规则select * from T_META_CONVERTRULE where FID like '%rem_%'select * from T_META_CONVERTRULE_L--反写规则select * from T_BF_WRITEBACKRULE where fsourceformid like '%SFC_%' and ftargetformid like '%SFC_%'--参数select * from t_bas_sysparameter--枚举值select * from T_META_FORMENUM ejoin T_META_FORMENUM_L el on el.FID=e.FIDwhere FNAME like '%生产线领料单%'--单据类型select * from T_BAS_BILLTYPE where FBILLFORMID like '%REM_INSTOCK%'select t.FBILLFORMID,t.FBILLTYPEID,t.FNUMBER,te.FNAME,t.FDOCUMENTSTATUS,t.FFORBIDSTATUSfrom T_BAS_BILLTYPE tjoin T_BAS_BILLTYPE_L te on t.FBILLTYPEID = te.FBILLTYPEID and t.FBILLFORMID = 'AR_receivable'  --应收单and (te.FNAME = '标准应收单' or te.FNAME = '费用应收单')and FDOCUMENTSTATUS = 'C' and FFORBIDSTATUS = 'A'--菜单操作对应类名select * from T_MDL_FORMOPERATIONTYPE--操作里面的服务select * from T_MDL_FORMBUSINESS_L where FDESC like '%更新即时库存%'select * from T_MDL_FORMBUSINESS where FACTIONID=45--保存界面布局select * from T_BAS_FormParameterwhere FPARAOBJID like '%SFC_OperationPlanning%'--用户select * from T_SEC_USER --用户参数select * FROM T_BAS_UserParameter where FPARAMETEROBJID like '%rem_%'--IDE函数select fl.FNAME,f.FAPPEARANCECLASS,f.FELEMENTCLASS, * from T_MDL_ELEMENTTYPE f join T_MDL_ELEMENTTYPE_L fl on f.FID=fl.FIDwhere FELEMENTCLASS like '%OPERATIONSTATUS%' or FAPPEARANCECLASS like '%OPERATIONSTATUS%'--枚举select * from T_META_FORMENUM_L where fname ='REM_业务类型'select * from t_Meta_Formenumitem where fid='c4a9508f-7af8-4edb-bb2f-fc21c866ee41'--权限对象，权限项select * from T_SEC_PERMISSIONOBJECTselect * from T_SEC_PERMISSIONOBJECTENTRY--通过菜单查找元数据对象，查找表名（在xml中搜t_）select * from T_META_OBJECTTYPE h join T_META_OBJECTTYPE_L l on l.FID=h.FID where FNAME like '%运算日志%'--单位select l.FNAME, * from T_BD_UNIT h join T_BD_UNIT_L l on h.FUNITID=l.FUNITID where l.FNAME like '%千克%'  --单据转换、业务流程、反写规则SELECT * FROM T_BF_DEFVERSIONSELECT * FROM T_BF_DEFVERSION_LSELECT * FROM T_BF_DEFVERSIONLOOKUPSELECT * FROM T_BF_INSTANCESELECT * FROM T_BF_INSTANCEAMOUNTSELECT * FROM T_BF_INSTANCEAMOUNTHISSELECT * FROM T_BF_INSTANCEENTRYSELECT * FROM T_BF_INSTANCEENTRYHISSELECT * FROM T_BF_INSTANCEHISSELECT * FROM T_BF_INSTANCESNAPSELECT * FROM T_BF_INSTANCESNAPHISSELECT * FROM T_BF_INSTANCETRACKSELECT * FROM T_BF_MYFAVOURITESELECT * FROM T_BF_PROCDEFSELECT * FROM T_BF_PROCDEF_LSELECT * FROM T_BF_PROCESSTYPESELECT * FROM T_BF_PROCESSTYPE_LSELECT * FROM T_BF_PUBLISHSELECT * FROM T_BF_PUBLISH_LSELECT * FROM T_BF_PUBLISHENTRYSELECT * FROM T_BF_TABLEDEFINESELECT * FROM T_BF_TRACKBACKUPLOGSELECT * FROM T_BF_WRITEBACKRULESELECT * FROM T_BF_WRITEBACKRULE_LSELECT * FROM T_BF_WRITEBACKRULECUSTSELECT * FROM T_META_CONVERTRULESELECT * FROM T_META_CONVERTRULE_L--SQL SERVER单据转换、业务流程、反写规则CREATE TABLE TMP_COSTCALTABLE (FTABLENAME VARCHAR(30));</P><P>INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_DEFVERSION');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_DEFVERSION_L');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_DEFVERSIONLOOKUP');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCE');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEAMOUNT');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEAMOUNTHIS');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEENTRY');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEENTRYHIS');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEHIS');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCESNAP');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCESNAPHIS');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCETRACK');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_MYFAVOURITE');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCDEF');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCDEF_L');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCESSTYPE');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCESSTYPE_L');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PUBLISH');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PUBLISH_L');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PUBLISHENTRY');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_TABLEDEFINE');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_TRACKBACKUPLOG');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_WRITEBACKRULE');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_WRITEBACKRULE_L');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_WRITEBACKRULECUST');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_META_CONVERTRULE');INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_META_CONVERTRULE_L'SELECT * FROM TMP_COSTCALTABLE--查看系统所有表占用的空间情况create table tmpspace (Fname varchar(50),Frows int,Freserved varchar(50),Fdata varchar(50),Findex_size varchar(50),Funused varchar(50));--插入所有表数据大小insert into tmpspace (Fname,Frows,Freserved, Fdata,Findex_size,Funused) exec sp_msforeachTable @Command1="sp_spaceused '?'--查询WITH FCBCOSTSPACE AS (select CONVERT(DECIMAL,replace(sp.fdata,'KB',''))/1024 SPACE_MB,sp.* from tmpspace spinner join TMP_COSTCALTABLE cb on cb.FTABLENAME=sp.Fname)SELECT * FROM FCBCOSTSPACEORDER BY SPACE_MB DESC--临时表占用的总大小（M）select SUM(CONVERT(DECIMAL,replace(sp.fdata,'KB','')))/1024 M from tmpspace spinner join TMP_COSTCALTABLE cb on cb.FTABLENAME=sp.Fname;--drop table tmpspace;--DROP TABLE TMP_COSTCALTABLE;</P>----------------其他核算维度 T_BD_FLEXITEMDETAILV表内常用字段：{    费用项目：FFLEX9    供应商：FFLEX4    部门：FFLEX5    员工：FFLEX7    物料：FFLEX8    资产类别：FFLEX10}凭证 T_GL_VOUCHER & T_GL_VOUCHERENTRY字段说明：{ FACCOUNTBOOKID  账簿 FCURRENCYID 币别   1==RMB FDETAILID --核算维度 0是不指定客户，为合计，     不为0是，需要在核算维度T_BD_FLEXITEMDETAILV 中查 FFLEX6，与客户T_BD_CUSTOMER  FCUSTID对应}金税开票单 T_IV_GTINVOICE字段说明：{ FINVOICEDATE 开票日期 FBILLNO 单据编号 FINVOICEKIND --发票种类 0 = 专用，2=普通 FINVOICETYPE --发票类型 1= 红 ，0 =蓝 FDOCUMENTSTATUS 'C'=已审核 A=创建FPURNAME --购货方FSALENAME --销售方名称FIVCODE --发票代码FIVNUMBER --发票号码FTYPE --开票类型 0=纸质， 1 = 电子FSUMAMOUNT 不含税金额FSUMTAXAMOUNT 税额FSUMALLAMOUNT 价税合计}