--查询数据表
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'
) t
where Item<>'' and FNAME like '%收料通知单%'
通过表 T_META_OBJECTTYPE 的 FKERNNELXML 字段进行xml查找
库存状态列表
select t1.FSTOCKSTATUSID,t1.FNUMBER,t2.FNAME from T_BD_STOCKSTATUS t1
left join T_BD_STOCKSTATUS_L t2 on t1.FSTOCKSTATUSID=t2.FSTOCKSTATUSID
单据类型
select t1.FBILLTYPEID,t1.FNUMBER,t2.FNAME from T_BAS_BILLTYPE t1
left 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.FENTRYID
t_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 ul
join T_BD_UNIT u on u.FUNITID = u.FUNITID
where ul.FNAME = '平方米'
T_BD_CUSTOMER & T_BD_CUSTOMER_L 客户select * from T_BD_CUSTOMER l
join 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 c
join 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=103387
T_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 t1
join 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.fmaterialid
where 1=1
--and m.fmaterialid=147002
and 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 onV_SCM_OWNERORG.FORGID=V_SCM_OWNERORG_L.FORGID
select l.FNAME,* from T_ORG_ORGANIZATIONS o
join T_ORG_ORGANIZATIONS_L l
on 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 r
left join T_BF_WRITEBACKRULE_L rl on rl.FID=r.FID and rl.FLOCALEID=2052
left join T_BF_WRITEBACKRULECUST rc on r.FID=rc.FID
where (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 e
join T_META_FORMENUM_L el on el.FID=e.FID
where FNAME like '%生产线领料单%'
--单据类型
select * from T_BAS_BILLTYPE where FBILLFORMID like '%REM_INSTOCK%'select t.FBILLFORMID,t.FBILLTYPEID,t.FNUMBER,te.FNAME,t.FDOCUMENTSTATUS,t.FFORBIDSTATUS
from T_BAS_BILLTYPE t
join 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_FormParameter
where 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.FID
where 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_PERMISSIONOBJECT
select * 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_DEFVERSION
SELECT * FROM T_BF_DEFVERSION_L
SELECT * FROM T_BF_DEFVERSIONLOOKUP
SELECT * FROM T_BF_INSTANCE
SELECT * FROM T_BF_INSTANCEAMOUNT
SELECT * FROM T_BF_INSTANCEAMOUNTHIS
SELECT * FROM T_BF_INSTANCEENTRY
SELECT * FROM T_BF_INSTANCEENTRYHIS
SELECT * FROM T_BF_INSTANCEHIS
SELECT * FROM T_BF_INSTANCESNAP
SELECT * FROM T_BF_INSTANCESNAPHIS
SELECT * FROM T_BF_INSTANCETRACK
SELECT * FROM T_BF_MYFAVOURITE
SELECT * FROM T_BF_PROCDEF
SELECT * FROM T_BF_PROCDEF_L
SELECT * FROM T_BF_PROCESSTYPE
SELECT * FROM T_BF_PROCESSTYPE_L
SELECT * FROM T_BF_PUBLISH
SELECT * FROM T_BF_PUBLISH_L
SELECT * FROM T_BF_PUBLISHENTRY
SELECT * FROM T_BF_TABLEDEFINE
SELECT * FROM T_BF_TRACKBACKUPLOG
SELECT * FROM T_BF_WRITEBACKRULE
SELECT * FROM T_BF_WRITEBACKRULE_L
SELECT * FROM T_BF_WRITEBACKRULECUST
SELECT * FROM T_META_CONVERTRULE
SELECT * 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 sp
inner join TMP_COSTCALTABLE cb on cb.FTABLENAME=sp.Fname)
SELECT * FROM FCBCOSTSPACE
ORDER BY SPACE_MB DESC
--临时表占用的总大小(M)
select SUM(CONVERT(DECIMAL,replace(sp.fdata,'KB','')))/1024 M from tmpspace sp
inner 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 价税合计
}