金蝶（云星空) Cloud 常用 sql查询语句

---单据类型

select * from T_BAS_BILLTYPE  a inner join  T_BAS_BILLTYPE_l b on a.FBILLTYPEID=b.FBILLTYPEID
and b.FLOCALEID=2052
where  a.FBILLTYPEID like '%eacb50844fc84a10b03d7b841f3a6278%'

--物料名称
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 on

V_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 a.FID AS 枚举类型内码,b.FNAME AS 枚举类型名称,* FROM T_META_FORMENUM a LEFT JOIN T_META_FORMENUM_L b ON a.FID=
b.FID AND b.FLOCALEID=2052 WHERE b.FNAME=N'MFG_BOM子项类型'

select   FErpClsID,枚举项名称 FName  from t_BD_MaterialBase a
inner join 
(
SELECT a.FID AS 枚举类型内码,a.FENUMID AS 枚举项内码,b.FCAPTION AS 枚举项名称,a.FVALUE AS 枚举项值 FROM T_META_FORMENUMITEM a LEFT JOIN T_META_FORMENUMITEM_L b ON a.FENUMID=b.FENUMID AND b.FLOCALEID=2052 WHERE a.FID='ac14913e-bd72-416d-a50b-2c7432bbff63') b
on  a.FErpClsID =枚举项值
group by  FErpClsID,枚举项名称

--单据类型
select * from T_BAS_BILLTYPE where FBILLFORMID like '%REM_INSTOCK%'

--菜单操作对应类名
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 b1.FID,b1.FNumber,n1.FName,b2.FEntryID,b2.FNumber,b2.FSeq,n2.FDataValue,b2.FUseOrgID from T_BAS_AssistantData b1 inner join T_BAS_AssistantData_L n1 on b1.FID=n1.FID 
and n1.FLocaleID=2052 inner join T_BAS_AssistantDataEntry b2 on b1.FID=b2.FID inner join T_BAS_AssistantDataEntry_L n2 on b2.FEntryID=n2.FEntryID and n2.FLocaleID=2052
where n1.FName like '%国家%' order by b1.FNumber,b2.FNumber,b2.FSeq
--数据中心：

select t1.FDATACENTERID,t1.FNUMBER,t2.FNAME,t1.FDATABASENAME from T_BAS_DATACENTER t1 inner join T_BAS_DATACENTER_L t2 on t1.FDATACENTERID=t2.FDATACENTERID and t2.FLOCALEID=2052
---查询数据中心版本：

select FVERSION from T_BOS_INSTALLEDPACKAGE where FISVID = 'Kingdee' and FPKGID='K3Cloud_DC_VERSION'
 

--上下游单据关联查询说明：

---LK表中主要记录的是关联上游单据的信息。其中必有的关键信息如下：
--FSBillId：上游单据ID

--FSID:关联的上游单据明细ID

--FSTABLENAME:关联上游单据的实体表，也就是说FSID是FSTABLENAME这个表的主键。

---财务应付关联查询 暂估应付单

select  zg.* from  T_AP_PAYABLE a inner join T_AP_PAYABLEENTRY b on a.FID=b.FID
left  join T_AP_PAYABLE_LK lk on lk.FENTRYID=b.FENTRYID
left join T_AP_PAYABLEENTRY zg on zg.FENTRYID=lk.FSID